使用前，导入NPOI dll（测试使用版本：1.2.5.0
使用
1 try 2 { 3 string strExcelNamePart ="测试报表"; 4 string strBeginTime="2015-04-02 15:00:00"; 5 conn.Open(); 6 dt = SqlHelper.ExecuteDataTable(conn, CommandType.Text, "select * from tb"); 7 //格式化数据 8 dt = FormatData(dt, strExcelModel); 9 10 string filepath = Server.MapPath("/download") + "/" + strExcelNamePart + strBeginTime.Replace("-", "").Replace(":", "").Replace(" ", "") + ".xls"; 11 12 ExcelHelper.CreateExcel(dt, filepath, "xx报表"); 13 hlink.Visible = true; 14 hlink.Text = txtBeginTime.Text.Trim() + ".xls"; 15 hlink.NavigateUrl = filepath.Replace(Server.MapPath("/"), "/"); 16 17 } 18 catch (Exception e) 19 { 20 } 21 finally 22 { 23 conn.Close(); 24 }
导出excel的ExcelHelper 类
1 public class ExcelHelper 2 { 3 public static bool CreateExcel(DataTable dt, string path, string name) 4 { 5 List<string> exceltitlelist = new List<string>(); 6 foreach (DataColumn dc in dt.Columns) 7 { 8 exceltitlelist.Add(dc.ColumnName); 9 } 10 try 11 { 12 NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); 13 NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(name); 14 sheet.AutoSizeColumn(0); 15 var cellFont = workbook.CreateFont(); 16 var cellStyle = workbook.CreateCellStyle(); 17 var cellStyle2 = workbook.CreateCellStyle(); 18 ////- 加粗，白色前景色 19 cellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; 20 ////- 这个是填充的模式，可以是网格、花式等。如果需要填充单色，请使用：SOLID_FOREGROUND 21 ////cellStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND; 22 ////- 设置这个样式的字体，如果没有设置，将与所有单元格拥有共同字体！ 23 cellStyle.SetFont(cellFont); 24 cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; 25 cellStyle2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; 26 //CellStyle cellStyleDate = workbook.CreateCellStyle(); 27 //DataFormat format = workbook.CreateDataFormat(); 28 //cellStyleDate.DataFormat = format.GetFormat("yyyy年m月d日"); 29 30 string[] titles = exceltitlelist.ToArray(); 31 32 int rowIndex = 0; 33 NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex); 34 for (int i = 0; i < titles.Length; i++) //生成sheet第一行列名 35 { 36 NPOI.SS.UserModel.ICell celltmp = row.CreateCell(i); 37 celltmp.SetCellValue(titles[i]); 38 celltmp.CellStyle = cellStyle; 39 } 40 NPOI.SS.UserModel.ICell cell; 41 rowIndex++; 42 string tmp; 43 DataRow m; 44 45 for (int i = 0; i < dt.Rows.Count; i++) 46 { 47 try 48 { 49 m = dt.Rows[i]; 50 row = sheet.CreateRow(rowIndex); 51 52 for (int j = 0; j < titles.Length; j++) 53 { 54 cell = row.CreateCell(j); 55 cell.CellStyle = cellStyle2; 56 cell.SetCellValue(m.ItemArray[j].ToString()); 57 } 58 rowIndex++; 59 } 60 catch (Exception e1) 61 { 62 //logclass.Debug("===== 生成excel报错 =====" + e1.Message); 63 } 64 65 } 66 67 sheet.ForceFormulaRecalculation = true; 68 69 using (FileStream file = new FileStream(path, FileMode.Create)) 70 { 71 workbook.Write(file); //创建xls文件。 72 file.Close(); 73 } 74 } 75 catch (Exception e) 76 { 77 //policyframework.common.logclass.Debug("=====CreateExcel 生成excel报错 =====" + e.Message); 78 return false; 79 } 80 return true; 81 82 83 84 } 85 86 public static DataTable GetExcelDataASODBC(string path, string sql) 87 { 88 OdbcConnection Connnection = new OdbcConnection(); 89 Connnection.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" + path; 90 OdbcCommand cmd = new OdbcCommand(); 91 cmd.Connection = Connnection; 92 cmd.CommandText = sql; 93 OdbcDataAdapter oda = new OdbcDataAdapter(cmd); 94 DataTable dt = new DataTable(); 95 oda.Fill(dt); 96 Connnection.Close(); 97 return dt; 98 } 99 100 public static DataTable GetExcelDataAsTableNPOI(string fileName) 101 { 102 using (FileStream fs = new FileStream(fileName, FileMode.Open)) 103 { 104 HSSFWorkbook wb = new HSSFWorkbook(fs); 105 NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(0); 106 DataTable table = new DataTable(); 107 //由第一列取標題做為欄位名稱 108 NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(0); 109 int cellCount = headerRow.LastCellNum; 110 for (int i = headerRow.FirstCellNum; i < cellCount; i++) 111 //以欄位文字為名新增欄位，此處全視為字串型別以求簡化 112 table.Columns.Add( 113 new DataColumn(headerRow.GetCell(i).StringCellValue)); 114 115 NPOI.SS.UserModel.IRow row; 116 DataRow dataRow; 117 //略過第零列(標題列)，一直處理至最後一列 118 for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) 119 { 120 row = sheet.GetRow(i); 121 if (row == null) continue; 122 dataRow = table.NewRow(); 123 //依先前取得的欄位數逐一設定欄位內容 124 for (int j = row.FirstCellNum; j < cellCount; j++) 125 if (row.GetCell(j) != null) 126 //如要針對不同型別做個別處理，可善用.CellType判斷型別 127 //再用.StringCellValue, .DateCellValue, .NumericCellValue...取值 128 //此處只簡單轉成字串 129 dataRow[j] = row.GetCell(j).ToString(); 130 table.Rows.Add(dataRow); 131 } 132 return table; 133 } 134 } 135 136 }
格式化Datatable
1 /// <summary> 2 /// 处理table数据 3 /// </summary> 4 /// <param name="dt">要处理的DataTable</param> 5 ///<param name="strExcelModel">模板类型</param> 6 /// <returns></returns> 7 private DataTable FormatData(DataTable dt, string strExcelModel) 8 { 9 //设置表格格式 10 DataTable result = dt.Clone(); 11 result.Clear(); 12 //foreach (DataColumn item in result.Columns) 13 //{ 14 // item.DataType = typeof(String); 15 //} 16 #region 整理excel数据 17 switch (strExcelModel) 18 { 19 case "1": 20 { 21 #region 1qn 22 result.Columns["销售起始日期"].DataType = typeof(String); 23 result.Columns["销售结束日期"].DataType = typeof(String); 24 25 DataRow[] rowstmp = dt.Select("", "[销售结束日期],[销售结束日期] asc"); 26 decimal dlyprice = 0; 27 for (int i = 0; i < rowstmp.Length; i++) 28 { 29 if (decimal.TryParse(rowstmp[i]["票面价/折扣"].ToString().Trim(), out dlyprice)) 30 { 31 DataRow rowNew = result.NewRow(); 32 #region row数据 33 rowNew["票面价/折扣"] = (dlyprice + 100).ToString(); //票面价+100 34 rowNew["销售起始日期"] = Convert.ToDateTime(rowstmp[i]["销售起始日期"]).ToString("yyyy-MM-dd"); 35 rowNew["销售结束日期"] = Convert.ToDateTime(rowstmp[i]["销售结束日期"]).ToString("yyyy-MM-dd"); 36 #endregion row 37 result.Rows.Add(rowNew); 38 } 39 } 40 break; 41 #endregion 1qn 42 } 43 default: 44 break; 45 46 } 47 #endregion 整理excel数据 48 49 return result; 50 } 51 #endregion 导出excel 52 53 }
