C# NPOI Excel

第一步：使用Nuget包管理器安装NPOI

第二步：如下所示

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196

using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks;   namespace MEAS.Common {     public class ExcelHelper : IDisposable     {         private string fileName = null; //文件名         private IWorkbook workbook = null;         private FileStream fs = null;         private bool disposed;           public ExcelHelper(string fileName)         {             this.fileName = fileName;             disposed = false;         }           /// <summary>         /// 将DataTable数据导入到excel中         /// </summary>         /// <param name="data">要导入的数据</param>         /// <param name="isColumnWritten">DataTable的列名是否要导入</param>         /// <param name="sheetName">要导入的excel的sheet的名称</param>         /// <returns>导入数据行数(包含列名那一行)</returns>         public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)         {             int i = 0;             int j = 0;             int count = 0;             ISheet sheet = null;               fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);             if (fileName.IndexOf(".xlsx") > 0) // 2007版本                 workbook = new XSSFWorkbook();             else if (fileName.IndexOf(".xls") > 0) // 2003版本                 workbook = new HSSFWorkbook();               try             {                 if (workbook != null)                 {                     sheet = workbook.CreateSheet(sheetName);                 }                 else                 {                     return -1;                 }                   if (isColumnWritten == true) //写入DataTable的列名                 {                     IRow row = sheet.CreateRow(0);                     for (j = 0; j < data.Columns.Count; ++j)                     {                         row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);                     }                     count = 1;                 }                 else                 {                     count = 0;                 }                   for (i = 0; i < data.Rows.Count; ++i)                 {                     IRow row = sheet.CreateRow(count);                     for (j = 0; j < data.Columns.Count; ++j)                     {                         row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());                     }                     ++count;                 }                 workbook.Write(fs); //写入到excel                 return count;             }             catch (Exception ex)             {                 Console.WriteLine("Exception: " + ex.Message);                 return -1;             }         }           /// <summary>         /// 将excel中的数据导入到DataTable中         /// </summary>         /// <param name="sheetName">excel工作薄sheet的名称</param>         /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>         /// <returns>返回的DataTable</returns>         public DataTable ExcelToDataTable(string filePath,string sheetName, bool isFirstRowColumn=true)         {             ISheet sheet = null;             DataTable data = new DataTable();             int startRow = 0;             try             {                 fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);                 if (filePath.IndexOf(".xlsx") > 0) // 2007版本                     workbook = new XSSFWorkbook(fs);                 else if (filePath.IndexOf(".xls") > 0) // 2003版本                     workbook = new HSSFWorkbook(fs);                   if (sheetName != null)                 {                     sheet = workbook.GetSheet(sheetName);                     if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet                     {                         sheet = workbook.GetSheetAt(0);                     }                 }                 else                 {                     sheet = workbook.GetSheetAt(0);                 }                 if (sheet != null)                 {                     IRow firstRow = sheet.GetRow(0);                     int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数                       if (isFirstRowColumn)                     {                         for (int i = firstRow.FirstCellNum; i < cellCount; ++i)                         {                             ICell cell = firstRow.GetCell(i);                             if (cell != null)                             {                                 string cellValue = cell.StringCellValue;                                 if (cellValue != null)                                 {                                     DataColumn column = new DataColumn(cellValue);                                     data.Columns.Add(column);                                 }                             }                         }                         startRow = sheet.FirstRowNum + 1;                     }                     else                     {                         startRow = sheet.FirstRowNum;                     }                       //最后一列的标号                     int rowCount = sheet.LastRowNum;                     for (int i = startRow; i <= rowCount; ++i)                     {                         IRow row = sheet.GetRow(i);                         if (row == null) continue; //没有数据的行默认是null　　　　　　　                           DataRow dataRow = data.NewRow();                         for (int j = row.FirstCellNum; j < cellCount; ++j)                         {                             if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null                                 dataRow[j] = row.Cells[j].ToString();                         }                         data.Rows.Add(dataRow);                     }                 }                   return data;             }             catch (Exception ex)             {                 Console.WriteLine("Exception: " + ex.Message);                 return null;             }         }           public void Dispose()         {             Dispose(true);             GC.SuppressFinalize(this);         }           protected virtual void Dispose(bool disposing)         {             if (!this.disposed)             {                 if (disposing)                 {                     if (fs != null)                         fs.Close();                 }                   fs = null;                 disposed = true;             }         }     } }

调用示例：

ExcelHelper excle = new ExcelHelper(file);
DataTable dt = excle.ExcelToDataTable(file,"检查项目信息");