• DataExcel控件读取和保存excel xlsx 格式文件


    需要引用NPOI库  https://github.com/dotnetcore/NPOI
    调用Read 函数将excel读取到dataexcel控件
    调用Save 函数将dataexcel控件文件保存为excel文件
    1. using NPOI.HSSF.UserModel;
    2. using NPOI.HSSF.Util;
    3. using NPOI.SS.UserModel;
    4. using NPOI.SS.Util;
    5. using System;
    6. using System.Collections.Generic;
    7. using System.Drawing;
    8. using System.IO;
    9. using System.Text;
    10. using System.Windows.Forms;
    11. namespace Feng.DataTool
    12. {
    13. public class ExcelTools
    14. {
    15. public static void Save(Feng.Excel.DataExcel grid, string file)
    16. {
    17. HSSFWorkbook hssfworkbook = new HSSFWorkbook();
    18. ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
    19. List<CellStyleTemp> cellstyletemplist = new List<CellStyleTemp>();
    20. foreach (Feng.Excel.Interfaces.IRow gridrow in grid.Rows)
    21. {
    22. if (gridrow.Index < 1)
    23. continue;
    24. IRow row = sheet1.CreateRow(gridrow.Index-1);
    25. row.Height = (short)(gridrow.Height * 20);
    26. foreach (Feng.Excel.Interfaces.IColumn column in grid.Columns)
    27. {
    28. if (column.Index < 1)
    29. continue;
    30. Feng.Excel.Interfaces.ICell gridcell = gridrow[column];
    31. if (gridcell == null)
    32. continue;
    33. ushort width = (ushort)((column.Width) / 7 * 256);
    34. sheet1.SetColumnWidth(column.Index-1, width);
    35. ICell cell = row.CreateCell(column.Index-1);
    36. string text = gridcell.Text;
    37. cell.SetCellValue(text);
    38. bool bottomlinestylevisible = false;
    39. bool leftlinestylevisible = false;
    40. bool rightlinestylevisible = false;
    41. bool toplinestylevisible = false;
    42. bool isstrikeout = false;
    43. bool isbold = false;
    44. double fontheightinpoints = 29;
    45. string fontname = string.Empty;
    46. if (gridcell.BorderStyle != null)
    47. {
    48. if (gridcell.BorderStyle.BottomLineStyle != null)
    49. {
    50. if (gridcell.BorderStyle.BottomLineStyle.Visible)
    51. {
    52. bottomlinestylevisible = true;
    53. }
    54. }
    55. if (gridcell.BorderStyle.LeftLineStyle != null)
    56. {
    57. if (gridcell.BorderStyle.LeftLineStyle.Visible)
    58. {
    59. leftlinestylevisible = true;
    60. }
    61. }
    62. if (gridcell.BorderStyle.RightLineStyle != null)
    63. {
    64. if (gridcell.BorderStyle.RightLineStyle.Visible)
    65. {
    66. rightlinestylevisible = true;
    67. }
    68. }
    69. if (gridcell.BorderStyle.TopLineStyle != null)
    70. {
    71. if (gridcell.BorderStyle.TopLineStyle.Visible)
    72. {
    73. toplinestylevisible = true;
    74. }
    75. }
    76. }
    77. isstrikeout = gridcell.Font.Strikeout;
    78. isbold = gridcell.Font.Bold;
    79. fontheightinpoints = gridcell.Font.Size;
    80. fontname = gridcell.Font.Name;
    81. int alignment = GetHAlignment(gridcell.HorizontalAlignment);
    82. int verticalalignment = GetVAlignment(gridcell.VerticalAlignment);
    83. ICellStyle style = GetCellStyle(hssfworkbook, cellstyletemplist
    84. , bottomlinestylevisible, leftlinestylevisible
    85. , rightlinestylevisible, toplinestylevisible
    86. , isstrikeout, isbold
    87. , fontheightinpoints, fontname
    88. , alignment, verticalalignment);
    89. cell.CellStyle = style;
    90. }
    91. }
    92. foreach (Feng.Excel.Interfaces.IMergeCell item in grid.MergeCells)
    93. {
    94. sheet1.AddMergedRegion(new CellRangeAddress(item.MinCell.Row.Index-1,item.MaxRowIndex - 1, item.MinCell.Column.Index - 1, item.MaxColumnIndex - 1));
    95. //sheet1.AddMergedRegion(new CellRangeAddress(2, 7, 2, 7));
    96. }
    97. FileStream stream = new FileStream(file, FileMode.Create);
    98. hssfworkbook.Write(stream);
    99. stream.Close();
    100. }
    101. public static int GetHAlignment(StringAlignment alignment)
    102. {
    103. switch (alignment)
    104. {
    105. case StringAlignment.Near:
    106. return 1;
    107. case StringAlignment.Center:
    108. return 2;
    109. case StringAlignment.Far:
    110. return 3;
    111. default:
    112. break;
    113. }
    114. return 0;
    115. }
    116. public static int GetVAlignment(StringAlignment alignment)
    117. {
    118. switch (alignment)
    119. {
    120. case StringAlignment.Near:
    121. return 0;
    122. case StringAlignment.Center:
    123. return 1;
    124. case StringAlignment.Far:
    125. return 2;
    126. default:
    127. break;
    128. }
    129. return 0;
    130. }
    131. public class CellStyleTemp
    132. {
    133. public CellStyleTemp()
    134. {
    135. }
    136. public bool BottomLineStyleVisible { get; set; }
    137. public bool LeftLineStyleVisible { get; set; }
    138. public bool RightLineStyleVisible { get; set; }
    139. public bool TopLineStyleVisible { get; set; }
    140. public bool IsStrikeout { get; set; }
    141. public bool IsBold { get; set; }
    142. public double FontHeightInPoints { get; set; }
    143. public string FontName { get; set; }
    144. public int Alignment { get; set; }
    145. public int VerticalAlignment { get; set; }
    146. public ICellStyle style { get; set; }
    147. }
    148. public static ICellStyle GetCellStyle(HSSFWorkbook hssfworkbook, List<CellStyleTemp> styles
    149. , bool bottomlinestylevisible, bool leftlinestylevisible
    150. , bool rightlinestylevisible, bool toplinestylevisible
    151. , bool isstrikeout, bool isbold
    152. , double fontheightinpoints, string fontname
    153. , int alignment
    154. , int verticalalignment)
    155. {
    156. foreach (CellStyleTemp item in styles)
    157. {
    158. if (item.BottomLineStyleVisible != bottomlinestylevisible)
    159. {
    160. continue;
    161. }
    162. double upfontheightinpoints = fontheightinpoints + 0.1;
    163. double downfontheightinpoints = fontheightinpoints - 0.1;
    164. if (!((item.FontHeightInPoints > downfontheightinpoints) && (item.FontHeightInPoints < upfontheightinpoints)))
    165. {
    166. continue;
    167. }
    168. if (item.FontName != fontname)
    169. {
    170. continue;
    171. }
    172. if (item.IsBold != isbold)
    173. {
    174. continue;
    175. }
    176. if (item.IsStrikeout != isstrikeout)
    177. {
    178. continue;
    179. }
    180. if (item.LeftLineStyleVisible != leftlinestylevisible)
    181. {
    182. continue;
    183. }
    184. if (item.RightLineStyleVisible != rightlinestylevisible)
    185. {
    186. continue;
    187. }
    188. if (item.TopLineStyleVisible != toplinestylevisible)
    189. {
    190. continue;
    191. }
    192. if (item.Alignment != alignment)
    193. {
    194. continue;
    195. }
    196. if (item.VerticalAlignment != verticalalignment)
    197. {
    198. continue;
    199. }
    200. return item.style;
    201. }
    202. CellStyleTemp cellStyleTemp = new CellStyleTemp();
    203. cellStyleTemp.BottomLineStyleVisible = bottomlinestylevisible;
    204. cellStyleTemp.FontHeightInPoints = fontheightinpoints;
    205. cellStyleTemp.FontName = fontname;
    206. cellStyleTemp.IsBold = isbold;
    207. cellStyleTemp.IsStrikeout = isstrikeout;
    208. cellStyleTemp.LeftLineStyleVisible = leftlinestylevisible;
    209. cellStyleTemp.RightLineStyleVisible = rightlinestylevisible;
    210. cellStyleTemp.TopLineStyleVisible = toplinestylevisible;
    211. cellStyleTemp.Alignment = alignment;
    212. cellStyleTemp.VerticalAlignment = verticalalignment;
    213. ICellStyle style = CreateCellStyle(hssfworkbook
    214. , bottomlinestylevisible, leftlinestylevisible
    215. , rightlinestylevisible, toplinestylevisible
    216. , isstrikeout, isbold
    217. , fontheightinpoints, fontname, alignment
    218. , verticalalignment);
    219. cellStyleTemp.style = style;
    220. styles.Add(cellStyleTemp);
    221. return cellStyleTemp.style;
    222. }
    223. public static ICellStyle CreateCellStyle(HSSFWorkbook hssfworkbook
    224. , bool BottomLineStyleVisible, bool LeftLineStyleVisible
    225. , bool RightLineStyleVisible, bool TopLineStyleVisible
    226. , bool IsStrikeout, bool IsBold
    227. , double FontHeightInPoints, string FontName
    228. , int alignment
    229. , int verticalalignment)
    230. {
    231. ICellStyle style = hssfworkbook.CreateCellStyle();
    232. if (BottomLineStyleVisible)
    233. {
    234. style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
    235. }
    236. if (LeftLineStyleVisible)
    237. {
    238. style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
    239. }
    240. if (RightLineStyleVisible)
    241. {
    242. style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
    243. }
    244. if (TopLineStyleVisible)
    245. {
    246. style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
    247. }
    248. style.Alignment = (NPOI.SS.UserModel.HorizontalAlignment)alignment;
    249. style.VerticalAlignment = (NPOI.SS.UserModel.VerticalAlignment)verticalalignment;
    250. IFont font2 = hssfworkbook.CreateFont();
    251. font2.Color = HSSFColor.Black.Index;
    252. if (IsStrikeout)
    253. {
    254. font2.IsStrikeout = true;
    255. }
    256. if (IsBold)
    257. {
    258. font2.IsBold = true;
    259. }
    260. font2.FontHeightInPoints = FontHeightInPoints;
    261. font2.FontName = FontName;
    262. style.SetFont(font2);
    263. return style;
    264. }
    265. public static void Read(Feng.Excel.DataExcel grid, string file)
    266. {
    267. FileStream stream = new FileStream(file, FileMode.Open);
    268. HSSFWorkbook hssfworkbook = new HSSFWorkbook(stream);
    269. stream.Close();
    270. ISheet sheet = hssfworkbook.GetSheetAt(0);
    271. int rowcount = sheet.LastRowNum;
    272. bool hassetcolumnwidth = false;
    273. for (int rowindex = 0; rowindex < rowcount; rowindex++)
    274. {
    275. IRow row = sheet.GetRow(rowindex);
    276. if (row == null)
    277. continue;
    278. Feng.Excel.Interfaces.IRow gridrow = grid.GetRow(rowindex + 1);
    279. gridrow.Height = row.Height / 20;
    280. short columncount = row.LastCellNum;
    281. for (short columnindex = 0; columnindex < columncount; columnindex++)
    282. {
    283. Feng.Excel.Interfaces.IColumn gridcolumn = grid.GetColumn(columnindex + 1);
    284. int width = sheet.GetColumnWidth(columnindex);
    285. gridcolumn.Width = (width / 256) * 7;
    286. ICell cell = row.GetCell(columnindex);
    287. if (cell == null)
    288. continue;
    289. Feng.Excel.Interfaces.ICell gridcell = grid.GetCell(gridrow.Index, gridcolumn.Index);
    290. gridcell.BorderStyle = new Excel.Styles.CellBorderStyle();
    291. try
    292. {
    293. if (cell.CellType == CellType.String)
    294. {
    295. gridcell.Value = cell.StringCellValue;
    296. }
    297. if (cell.CellType == CellType.Numeric)
    298. {
    299. gridcell.Value = cell.NumericCellValue;
    300. }
    301. if (cell.CellType == CellType.Boolean)
    302. {
    303. gridcell.Value = cell.BooleanCellValue;
    304. }
    305. if (cell.CellType == CellType.Unknown)
    306. {
    307. gridcell.Value = cell.StringCellValue;
    308. }
    309. if (cell.CellStyle.BorderBottom == NPOI.SS.UserModel.BorderStyle.Thin)
    310. {
    311. gridcell.BorderStyle.BottomLineStyle = new Excel.Styles.LineStyle();
    312. gridcell.BorderStyle.BottomLineStyle.Visible = true;
    313. }
    314. if (cell.CellStyle.BorderLeft == NPOI.SS.UserModel.BorderStyle.Thin)
    315. {
    316. gridcell.BorderStyle.LeftLineStyle = new Excel.Styles.LineStyle();
    317. gridcell.BorderStyle.LeftLineStyle.Visible = true;
    318. }
    319. if (cell.CellStyle.BorderRight == NPOI.SS.UserModel.BorderStyle.Thin)
    320. {
    321. gridcell.BorderStyle.RightLineStyle = new Excel.Styles.LineStyle();
    322. gridcell.BorderStyle.RightLineStyle.Visible = true;
    323. }
    324. if (cell.CellStyle.BorderTop == NPOI.SS.UserModel.BorderStyle.Thin)
    325. {
    326. gridcell.BorderStyle.TopLineStyle = new Excel.Styles.LineStyle();
    327. gridcell.BorderStyle.TopLineStyle.Visible = true;
    328. }
    329. }
    330. catch (Exception ex)
    331. {
    332. Feng.Utils.TraceHelper.WriteTrace("SysTools", "ExcelTools", "Read", ex);
    333. }
    334. try
    335. {
    336. IFont font2 = cell.CellStyle.GetFont(hssfworkbook);
    337. System.Drawing.FontStyle fontstyle = System.Drawing.FontStyle.Regular;
    338. if (font2.IsStrikeout)
    339. {
    340. fontstyle = fontstyle | System.Drawing.FontStyle.Strikeout;
    341. }
    342. if (font2.IsBold)
    343. {
    344. fontstyle = fontstyle | System.Drawing.FontStyle.Bold;
    345. }
    346. font2.FontHeightInPoints = gridcell.Font.Size;
    347. font2.FontName = gridcell.Font.Name;
    348. gridcell.Font = new System.Drawing.Font(font2.FontName, (float)font2.FontHeightInPoints, fontstyle);
    349. }
    350. catch (Exception ex)
    351. {
    352. Feng.Utils.TraceHelper.WriteTrace("SysTools", "ExcelTools", "Read", ex);
    353. }
    354. try
    355. {
    356. if (cell.CellStyle.Alignment == NPOI.SS.UserModel.HorizontalAlignment.Center)
    357. {
    358. gridcell.HorizontalAlignment = StringAlignment.Center;
    359. }
    360. if (cell.CellStyle.Alignment == NPOI.SS.UserModel.HorizontalAlignment.Left)
    361. {
    362. gridcell.HorizontalAlignment = StringAlignment.Near;
    363. }
    364. if (cell.CellStyle.Alignment == NPOI.SS.UserModel.HorizontalAlignment.Right)
    365. {
    366. gridcell.HorizontalAlignment = StringAlignment.Far;
    367. }
    368. }
    369. catch (Exception ex)
    370. {
    371. Feng.Utils.TraceHelper.WriteTrace("SysTools", "ExcelTools", "Read", ex);
    372. }
    373. try
    374. {
    375. if (cell.CellStyle.VerticalAlignment == NPOI.SS.UserModel.VerticalAlignment.Center)
    376. {
    377. gridcell.VerticalAlignment = StringAlignment.Center;
    378. }
    379. if (cell.CellStyle.VerticalAlignment == NPOI.SS.UserModel.VerticalAlignment.Top)
    380. {
    381. gridcell.VerticalAlignment = StringAlignment.Near;
    382. }
    383. if (cell.CellStyle.VerticalAlignment == NPOI.SS.UserModel.VerticalAlignment.Bottom)
    384. {
    385. gridcell.VerticalAlignment = StringAlignment.Far;
    386. }
    387. }
    388. catch (Exception ex)
    389. {
    390. Feng.Utils.TraceHelper.WriteTrace("SysTools", "ExcelTools", "Read", ex);
    391. }
    392. }
    393. }
    394. List<CellRangeAddress> listmer = sheet.MergedRegions;
    395. if (listmer != null)
    396. {
    397. foreach (CellRangeAddress item in listmer)
    398. {
    399. grid.MergeCell(item.FirstRow + 1, item.FirstColumn + 1, item.LastRow + 1, item.LastColumn + 1);
    400. }
    401. }
    402. }
    403. }
    404. }

  • 相关阅读:
    python案例(更新中)
    机器学习实战:Python基于Ridge岭回归进行正则化(十三)
    综合练习
    Java中配置RabbitMQ基本步骤
    web前端期末大作业:青岛旅游网页主题网站设计——青岛民俗4页 HTML+CSS 民俗网页设计与制作 web网页设计实例作业 html实训大作业
    Linux内核源码分析 (B.3) 深入理解 Linux 物理内存分配全链路实现
    Harmonyos Next——图片上传与下载
    怎么将两个PDF合并成一个?这里有三个小妙招分享给你
    Java基于springboot+vue的儿童玩具销售购物网站 多商家
    山西电力市场日前价格预测【2023-09-30】
  • 原文地址:https://blog.csdn.net/zanfeng/article/details/133317079