NPOI json转Excel DataTable转Excel ，Excel转DataTable
JsonToExcel：
1 public static void JsonToExcel(List<Dictionary<string, object>> json, string fileName) 2 { 3 using (MemoryStream ms = new MemoryStream()) 4 { 5 6 IWorkbook workbook = null; 7 8 if (fileName.IndexOf(".xlsx") > 0) 9 workbook = new XSSFWorkbook(); 10 if (fileName.IndexOf(".xls") > 0) 11 workbook = new HSSFWorkbook(); 12 ISheet sheet = workbook.CreateSheet(); 13 IRow headerRow = sheet.CreateRow(0); 14 15 //// handling header. 16 //foreach (DataColumn column in table.Columns) 17 // headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value 18 19 20 var keys = json.FirstOrDefault().Keys.ToList(); 21 for (var i = 0; i < keys.Count(); i++) 22 { 23 headerRow.CreateCell(i).SetCellValue(keys[i]); 24 } 25 26 // handling value. 27 int rowIndex = 1; 28 29 for (var i = 0; i < json.Count(); i++) 30 { 31 IRow dataRow = sheet.CreateRow(rowIndex); 32 33 var values=json[i].Values.ToList(); 34 for (var j = 0; j <values.Count();j++) 35 { 36 dataRow.CreateCell(j).SetCellValue(values[j].ToString()); 37 } 38 39 rowIndex++; 40 } 41 42 workbook.Write(ms); 43 ms.Flush(); 44 45 using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write)) 46 { 47 byte[] data = ms.ToArray(); 48 49 fs.Write(data, 0, data.Length); 50 fs.Flush(); 51 52 data = null; 53 } 54 } 55 }
DataTableToExcel:
1 public static void dataTableToExcel(DataTable table, string fileName) 2 { 3 using (MemoryStream ms = new MemoryStream()) 4 { 5 6 IWorkbook workbook = null; 7 8 if (fileName.IndexOf(".xlsx") > 0) 9 workbook = new XSSFWorkbook(); 10 if (fileName.IndexOf(".xls") > 0) 11 workbook = new HSSFWorkbook(); 12 ISheet sheet = workbook.CreateSheet(); 13 IRow headerRow = sheet.CreateRow(0); 14 15 // handling header. 16 foreach (DataColumn column in table.Columns) 17 headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value 18 19 // handling value. 20 int rowIndex = 1; 21 22 foreach (DataRow row in table.Rows) 23 { 24 IRow dataRow = sheet.CreateRow(rowIndex); 25 26 foreach (DataColumn column in table.Columns) 27 { 28 dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); 29 } 30 31 rowIndex++; 32 } 33 34 workbook.Write(ms); 35 ms.Flush(); 36 37 using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write)) 38 { 39 byte[] data = ms.ToArray(); 40 41 fs.Write(data, 0, data.Length); 42 fs.Flush(); 43 44 data = null; 45 } 46 } 47 }
ExcelToDataTable:
/// <summary> /// 将excel中的数据导入到DataTable中 /// </summary> /// <param name="sheetName">excel工作薄sheet的名称</param> /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param> /// <returns>返回的DataTable</returns> public static DataTable ExcelToDataTable(string filePath, bool isColumnName, int sheetName) { DataTable dataTable = null; FileStream fs = null; DataColumn column = null; DataRow dataRow = null; IWorkbook workbook = null; ISheet sheet = null; IRow row = null; ICell cell = null; int startRow = 0; try { using (fs = File.OpenRead(filePath)) { // 2007版本 if (filePath.IndexOf(".xlsx") > 0) workbook = new XSSFWorkbook(fs); // 2003版本 else if (filePath.IndexOf(".xls") > 0) workbook = new HSSFWorkbook(fs); if (workbook != null) { sheet = workbook.GetSheetAt(sheetName);//读取第一个sheet，当然也可以循环读取每个sheet dataTable = new DataTable(); if (sheet != null) { int rowCount = sheet.LastRowNum;//总行数 if (rowCount > 0) { IRow firstRow = sheet.GetRow(0);//第一行 int cellCount = firstRow.LastCellNum;//列数 //构建datatable的列 if (isColumnName) { startRow = 1;//如果第一行是列名，则从第二行开始读取 for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { cell = firstRow.GetCell(i); if (cell != null) { if (cell.StringCellValue != null) { column = new DataColumn(cell.StringCellValue); dataTable.Columns.Add(column); } } } } else { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { column = new DataColumn("column" + (i + 1)); dataTable.Columns.Add(column); } } //填充行 for (int i = startRow; i <= rowCount; ++i) { row = sheet.GetRow(i); if (row == null) continue; dataRow = dataTable.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { cell = row.GetCell(j); if (cell == null) { dataRow[j] = ""; } else { //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,) switch (cell.CellType) { case CellType.Blank: dataRow[j] = ""; break; case CellType.Numeric: short format = cell.CellStyle.DataFormat; //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理 if (format == 14 || format == 31 || format == 57 || format == 58) dataRow[j] = cell.DateCellValue; else dataRow[j] = cell.NumericCellValue; break; case CellType.String: dataRow[j] = cell.StringCellValue; break; } } } dataTable.Rows.Add(dataRow); } } } } } return dataTable; } catch (Exception ex) { Console.WriteLine("exception:" + ex.ToString()); Console.ReadLine(); if (fs != null) { fs.Close(); } return null; } }