NOPI 下载地址 http://npoi.codeplex.com/
它是 免费的 NET平台 导出 导入 Excel 组件
优点 :无需安装office ,包含了office 绝大多数功能(单元格样式,数据格式,公式等等)
1. 创建 工作表 输出Excel
/// </summary> 带模板导出Excel文件 /// <param name="fileName">导出Excel文件名</param> /// <param name="sheetName">sheet名</param> private void ExportDataSetToExcel(string strFileName, string strSheetName) { //复制模板文件 string filePath = Server.MapPath("~/UploadExcel/" + Guid.NewGuid().ToString() + DateTime.Now.Millisecond + ".xls"); File.Copy(Server.MapPath("~/UploadExcel/価格表.xls"), filePath); HSSFWorkbook workbook;
// 以流的形式 打开 模板文件 using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite)) {
//创建工作表 workbook = new HSSFWorkbook(file);
//sheet 名赋值 workbook.SetSheetName(0, strSheetName) } //返回流形式的文件 MemoryStream ms = ExportToExcel(workbook, strSheetName) as MemoryStream; File.Delete(filePath);
//通知浏览器下载文件 HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8));
//以数组的形式输出Excel HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.End(); ms.Close(); ms = null; }
2. Excel数据绑定
/// <summary> /// 设置Excel内容 /// </summary> /// <param name="workbook">工作表</param> /// <param name="strSheetName">sheet名</param> /// <returns>Excel文件流</returns> private Stream ExportToExcel(HSSFWorkbook workbook, string strSheetName) { MemoryStream ms = new MemoryStream(); //获取数据 DataTable dtJAN = new DataTable(); if (Session["ExcelProductInfo"] != null) { dtJAN = (DataSet)Session["ExcelProductInfo"].Tables[0]; } // 定义单元格样式 HSSFCellStyle StyleDataRight = GetCellStyle(workbook, "DataRight"); //根据sheet名获取sheet HSSFSheet sheetCompute = (HSSFSheet)workbook.GetSheet(strSheetName); //设定数据开始行 int intComputeStartRow = 4; //获取数据总行数 int intJANComputeCount = dtJAN.Rows.Count; //循环数据 for (int i = 0; i < intJANComputeCount; i++) { //创建数据行 HSSFRow row = sheetCompute.CreateRow(intComputeStartRow); //单元格设定公式 row.CreateCell(1).SetCellFormula(String.Format(ct.GetXmlFormula("Formula1PFee"), intComputeStartRow + 1)); // row.GetCell(1).CellStyle = SetCellStyle(workbook, StyleDataRight, true); //1Pその他経費 row.CreateCell(2).SetCellFormula(String.Format(ct.GetXmlFormula("Formula1PPrice"), intComputeStartRow + 1)); row.GetCell(2).CellStyle = SetCellStyle(workbook, StyleDataRight, true); //1P店着原価 row.CreateCell(3).SetCellValue("值设定"); row.GetCell(3).CellStyle = SetCellStyle(workbook, StyleDataRight, true); } //excel 数据重算 sheetCompute.ForceFormulaRecalculation = true; //返回流 workbook.Write(ms); ms.Flush(); ms.Position = 0; return ms; }
3 单元格样式设置
//设定单元格样式 private HSSFCellStyle GetCellStyle(HSSFWorkbook workbook, string StyleName) { //创建字体 HSSFFont fContent = (HSSFFont)workbook.CreateFont(); fContent.FontName = "MS Pゴシック"; fContent.FontHeightInPoints = 11; //创建样式 并设定 HSSFCellStyle bigTitleStyle = workbook.CreateCellStyle(); bigTitleStyle.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER; bigTitleStyle.BorderBottom = HSSFCellStyle.BORDER_THIN; bigTitleStyle.BorderLeft = HSSFCellStyle.BORDER_THIN; bigTitleStyle.BorderRight = HSSFCellStyle.BORDER_THIN; bigTitleStyle.BorderTop = HSSFCellStyle.BORDER_THIN; switch (StyleName) { case "DataLeft": bigTitleStyle.Alignment = HSSFCellStyle.ALIGN_LEFT; bigTitleStyle.SetFont(fContent); break; case "DataRight": bigTitleStyle.Alignment = HSSFCellStyle.ALIGN_RIGHT; bigTitleStyle.SetFont(fContent); break; default: break; } return bigTitleStyle; } #endregion /// <summary> /// 单元格设置统一样式(如背景色等) /// </summary> /// <param name="workbook">工作表</param> /// <param name="CellStyle">原单元格样式</param> /// <param name="IsChangeProudct">是否需要统一设置</param> /// <returns>返回新样式</returns> public HSSFCellStyle SetCellStyle(HSSFWorkbook workbook, HSSFCellStyle CellStyle, bool IsChangeProudct) { HSSFCellStyle NewCellStyle = null; if (IsChangeProudct) { //创建样式 NewCellStyle = workbook.CreateCellStyle(); //复制旧样式 NewCellStyle.CloneStyleFrom(CellStyle); //追加新样式 NewCellStyle.FillBackgroundColor = HSSFColor.TAN.index; NewCellStyle.FillPattern = HSSFCellStyle.BORDER_THIN; NewCellStyle.FillForegroundColor = HSSFColor.TAN.index; } else { NewCellStyle = CellStyle; } return NewCellStyle; }