C#中NPOI操作excel之读取和写入excel数据

一、下载引用

下载需要引用的dll,即:NPOI.dll,NPOI.OOXML.dll,NPOI.OpenXml4Net.dll,ICSharpCode.SharpZipLib.dll(office2007版需要此dll)。

二、excel转datatable类

  1. using System;
  2. using System.Data;
  3. using System.IO;
  4. using NPOI.SS.UserModel;
  5. using NPOI.XSSF.UserModel;
  6. using NPOI.HSSF.UserModel;
  7. namespace NPOIOprateExcel
  8. {
  9. public class ExcelUtility
  10. {
  11. /// <summary>
  12. /// 将excel导入到datatable
  13. /// </summary>
  14. /// <param name="filePath">excel路径</param>
  15. /// <param name="isColumnName">第一行是否是列名</param>
  16. /// <returns>返回datatable</returns>
  17. public static DataTable ExcelToDataTable(string filePath, bool isColumnName)
  18. {
  19. DataTable dataTable = null;
  20. FileStream fs = null;
  21. DataColumn column = null;
  22. DataRow dataRow = null;
  23. IWorkbook workbook = null;
  24. ISheet sheet = null;
  25. IRow row = null;
  26. ICell cell = null;
  27. int startRow = 0;
  28. try
  29. {
  30. using (fs = File.OpenRead(filePath))
  31. {
  32. // 2007版本
  33. if (filePath.IndexOf(".xlsx") > 0)
  34. workbook = new XSSFWorkbook(fs);
  35. // 2003版本
  36. else if (filePath.IndexOf(".xls") > 0)
  37. workbook = new HSSFWorkbook(fs);
  38. if (workbook != null)
  39. {
  40. sheet = workbook.GetSheetAt(0);//读取第一个sheet,当然也可以循环读取每个sheet
  41. dataTable = new DataTable();
  42. if (sheet != null)
  43. {
  44. int rowCount = sheet.LastRowNum;//总行数
  45. if (rowCount > 0)
  46. {
  47. IRow firstRow = sheet.GetRow(0);//第一行
  48. int cellCount = firstRow.LastCellNum;//列数
  49. //构建datatable的列
  50. if (isColumnName)
  51. {
  52. startRow = 1;//如果第一行是列名,则从第二行开始读取
  53. for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
  54. {
  55. cell = firstRow.GetCell(i);
  56. if (cell != null)
  57. {
  58. if (cell.StringCellValue != null)
  59. {
  60. column = new DataColumn(cell.StringCellValue);
  61. dataTable.Columns.Add(column);
  62. }
  63. }
  64. }
  65. }
  66. else
  67. {
  68. for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
  69. {
  70. column = new DataColumn("column" + (i + 1));
  71. dataTable.Columns.Add(column);
  72. }
  73. }
  74. //填充行
  75. for (int i = startRow; i <= rowCount; ++i)
  76. {
  77. row = sheet.GetRow(i);
  78. if (row == null) continue;
  79. dataRow = dataTable.NewRow();
  80. for (int j = row.FirstCellNum; j < cellCount; ++j)
  81. {
  82. cell = row.GetCell(j);
  83. if (cell == null)
  84. {
  85. dataRow[j] = "";
  86. }
  87. else
  88. {
  89. //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
  90. switch (cell.CellType)
  91. {
  92. case CellType.Blank:
  93. dataRow[j] = "";
  94. break;
  95. case CellType.Numeric:
  96. short format = cell.CellStyle.DataFormat;
  97. //对时间格式(2015.12.5、2015/12/5、2015-12-5等)的处理
  98. if (format == 14 || format == 31 || format == 57 || format == 58)
  99. dataRow[j] = cell.DateCellValue;
  100. else
  101. dataRow[j] = cell.NumericCellValue;
  102. break;
  103. case CellType.String:
  104. dataRow[j] = cell.StringCellValue;
  105. break;
  106. }
  107. }
  108. }
  109. dataTable.Rows.Add(dataRow);
  110. }
  111. }
  112. }
  113. }
  114. }
  115. return dataTable;
  116. }
  117. catch (Exception)
  118. {
  119. if (fs != null)
  120. {
  121. fs.Close();
  122. }
  123. return null;
  124. }
  125. }
  126. }
  127. }
三、结果



四、写入excel类

  1. public static bool DataTableToExcel(DataTable dt)
  2. {
  3. bool result = false;
  4. IWorkbook workbook = null;
  5. FileStream fs = null;
  6. IRow row = null;
  7. ISheet sheet = null;
  8. ICell cell = null;
  9. try
  10. {
  11. if (dt != null && dt.Rows.Count > 0)
  12. {
  13. workbook = new HSSFWorkbook();
  14. sheet = workbook.CreateSheet("Sheet0");//创建一个名称为Sheet0的表
  15. int rowCount = dt.Rows.Count;//行数
  16. int columnCount = dt.Columns.Count;//列数
  17. //设置列头
  18. row = sheet.CreateRow(0);//excel第一行设为列头
  19. for (int c = 0; c < columnCount; c++)
  20. {
  21. cell = row.CreateCell(c);
  22. cell.SetCellValue(dt.Columns[c].ColumnName);
  23. }
  24. //设置每行每列的单元格,
  25. for (int i = 0; i <rowCount; i++)
  26. {
  27. row = sheet.CreateRow(i+1);
  28. for (int j = 0; j < columnCount; j++)
  29. {
  30. cell = row.CreateCell(j);//excel第二行开始写入数据
  31. cell.SetCellValue(dt.Rows[i][j].ToString());
  32. }
  33. }
  34. using (fs = File.OpenWrite(@"D:/myxls.xls"))
  35. {
  36. workbook.Write(fs);//向打开的这个xls文件中写入数据
  37. result = true;
  38. }
  39. }
  40. return result;
  41. }
  42. catch (Exception ex)
  43. {
  44. if (fs != null)
  45. {
  46. fs.Close();
  47. }
  48. return false;
  49. }
  50. }

展开阅读全文

没有更多推荐了,返回首页