c#(NPOI)DataTable导出execl，execl(支持解析公式)导入DataTable
NPOI(C#)DataTable导出execl
using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using NPOI.SS.Util; namespace Common { /// <summary> /// 使用NPOI操作Excel，无需Office COM组件 /// </summary> public class ExcelHelper { /// <summary> /// DataTable转换成Excel文档流，并输出到客户端，如果设置表名请设置table.TableName="表名" /// </summary> /// <param name="table">DataTable</param> /// <param name="context">上下文对象</param> /// <param name="fileName">Excel文件名(为空以当前时间加随机数命名)</param> public static void RenderToExcel(DataTable table, HttpContext context, string fileName=null) { var name = ""; if (fileName==""&&fileName==null) { name = DateTime.Now.ToString("yyyyMMddhhmmss") + new Random(DateTime.Now.Second).Next(10000); } else { name = fileName + new Random(DateTime.Now.Second).Next(10000); } var path = context.Server.MapPath("~/xls_down/" + name + ".xls"); if (!Directory.Exists(context.Server.MapPath("~/xls_down"))) { Directory.CreateDirectory(context.Server.MapPath("~/xls_down")); } else { //DeleteAllFile(context.Server.MapPath("~/xls_down")); } TableToExcelForXLS(table, path, fileName); System.IO.FileInfo file = new System.IO.FileInfo(path); context.Response.ContentType = "application/ms-download"; context.Response.Clear(); context.Response.AddHeader("Content-Type", "application/octet-stream"); context.Response.Charset = "utf-8"; context.Response.AddHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(file.Name, System.Text.Encoding.UTF8)); context.Response.AddHeader("Content-Length", file.Length.ToString()); context.Response.WriteFile(file.FullName); context.Response.Flush(); context.Response.Clear(); context.Response.End(); } /// <summary> /// 将DataTable数据导出到Excel文件中(xls) /// </summary> /// <param name="dt"></param> /// <param name="file">路径</param> /// <param name="tablename">表名</param> public void TableToExcelForXLS(DataTable dt, string file,string tablename=null) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); if (tablename == null) tablename = "table1"; ISheet sheet = hssfworkbook.CreateSheet(tablename); int n = 0; //控制表名,在第一行(列名前面一行)显示 if (dt.TableName != null && dt.TableName!="") { //表名 //设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域 //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列 sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count)); IRow rowtitle = sheet.CreateRow(0); ICell celltitle = rowtitle.CreateCell(0); celltitle.SetCellValue(dt.TableName); //设置单元格样式时需要注意，务必创建一个新的样式对象进行设置，否则会将工作表所有单元格的样式一同设置，它们应该共享的是一个样式对象 ICellStyle style = hssfworkbook.CreateCellStyle(); //设置单元格的样式：水平对齐居中 style.Alignment = HorizontalAlignment.Center; //新建一个字体样式对象 IFont font = hssfworkbook.CreateFont(); //设置字体加粗样式 font.Boldweight = short.MaxValue; //使用SetFont方法将字体样式添加到单元格样式中 style.SetFont(font); //将新的样式赋给单元格 celltitle.CellStyle = style; n = 1; //添加表名之后置为1 } int[] ColumnWidthArray = new int[dt.Columns.Count]; //列宽数组(自适应列宽) //表头 IRow row = sheet.CreateRow(n); for (int i = 0; i < dt.Columns.Count; i++) { ICell cell = row.CreateCell(i); cell.SetCellValue(dt.Columns[i].ColumnName); //可以获取中文长度，中文占2个字符 ColumnWidthArray[i] = System.Text.Encoding.Default.GetBytes(dt.Columns[i].ColumnName).Length; } //数据 for (int i = 0; i < dt.Rows.Count; i++) { IRow row1 = sheet.CreateRow(i + n+1); for (int j = 0; j < dt.Columns.Count; j++) { ICell cell = row1.CreateCell(j); String cellValue=dt.Rows[i][j].ToString(); cell.SetCellValue(cellValue); if (System.Text.Encoding.Default.GetBytes(cellValue).Length > ColumnWidthArray[j]) { ColumnWidthArray[j] = System.Text.Encoding.Default.GetBytes(cellValue).Length; } } } for (int i = 0; i < dt.Columns.Count; i++) { sheet.SetColumnWidth(i, (ColumnWidthArray[i]+2) * 256); } //转为字节数组 MemoryStream stream = new MemoryStream(); hssfworkbook.Write(stream); var buf = stream.ToArray(); //保存为Excel文件 using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } } } }
execl(支持解析公式)导入DataTable
1 /// <summary> 2 /// 将Excel文件中的数据读出到DataTable中(xls) 3 /// </summary> 4 /// <param name="file">文件绝对路径</param> 5 /// <returns></returns> 6 public static DataTable ExcelToTableForXLS(string file, int sheetIndex = 0) 7 { 8 DataTable dt = new DataTable(); 9 using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read)) 10 { 11 HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs); 12 ISheet sheet = hssfworkbook.GetSheetAt(sheetIndex); 13 14 //表头 15 IRow header = sheet.GetRow(sheet.FirstRowNum); 16 List<int> columns = new List<int>(); 17 for (int i = 0; i < header.LastCellNum; i++) 18 { 19 object obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell); 20 if (obj == null || obj.ToString() == string.Empty) 21 { 22 dt.Columns.Add(new DataColumn("Columns" + i.ToString())); 23 //continue; 24 } 25 else 26 dt.Columns.Add(new DataColumn(obj.ToString())); 27 columns.Add(i); 28 } 29 //数据 30 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) 31 { 32 IRow row = sheet.GetRow(i); 33 34 if (row != null) 35 { 36 DataRow dr = dt.NewRow(); 37 bool hasValue = false; 38 foreach (int j in columns) 39 { 40 dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell); 41 #region 增加格式解析 42 if (row.GetCell(j) != null) 43 { 44 switch (row.GetCell(j).CellType) 45 { 46 case CellType.Numeric: 47 if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))//日期类型 48 { 49 dr[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd"); 50 } 51 else//其他数字类型 52 { 53 dr[j] = row.GetCell(j).NumericCellValue; 54 } 55 break; 56 case CellType.Blank: 57 dr[j] = string.Empty; 58 break; 59 case CellType.Formula: 60 if (Path.GetExtension(file).ToLower().Trim() == ".xlsx") 61 { 62 XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(hssfworkbook); 63 if (eva.Evaluate(row.GetCell(j)).CellType == CellType.Numeric) 64 { 65 if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))//日期类型 66 { 67 dr[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd"); 68 } 69 else//其他数字类型 70 { 71 dr[j] = row.GetCell(j).NumericCellValue; 72 } 73 } 74 else 75 { 76 dr[j] = eva.Evaluate(row.GetCell(j)).StringValue; 77 } 78 } 79 else 80 { 81 HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(hssfworkbook); 82 if (eva.Evaluate(row.GetCell(j)).CellType == CellType.Numeric) 83 { 84 if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))//日期类型 85 { 86 dr[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd"); 87 } 88 else//其他数字类型 89 { 90 dr[j] = row.GetCell(j).NumericCellValue; 91 } 92 } 93 else 94 { 95 dr[j] = eva.Evaluate(row.GetCell(j)).StringValue; 96 } 97 } 98 break; 99 default: 100 dr[j] = row.GetCell(j).StringCellValue; 101 break; 102 103 } 104 } 105 #endregion 106 107 if (dr[j] != null && dr[j].ToString() != string.Empty) 108 { 109 hasValue = true; 110 } 111 } 112 if (hasValue) 113 { 114 dt.Rows.Add(dr); 115 } 116 } 117 } 118 } 119 120 return dt; 121 }
