把excel另存为xml电子表格(*.xml)，格式复制生成的xml代码，放到view中，然后根据model去生成excel的单元格和列。
下面是生成excel和下载的代码。
Response.Clear(); Response.ClearContent(); Response.Buffer = true; Response.ContentEncoding = System.Text.Encoding.UTF8; Response.ContentType = "application/ms-excel"; string downLoadFileName = title; if (Request.UserAgent.ToLower().IndexOf("msie") > -1) { downLoadFileName = HttpUtility.UrlPathEncode(downLoadFileName); } else if (Request.UserAgent.ToLower().IndexOf("firefox") > -1) { Response.AddHeader("Content-Disposition", "attachment:filename=\"" + downLoadFileName + "\""); } else Response.AddHeader("Content-Disposition", "attachment:filename=" + downLoadFileName + ""); 这是另外一种
////// Excel导出 /// /// ///public static string ExportGoodsSale(DataSet set) { try { IWorkbook workbook = new XSSFWorkbook(); foreach (DataTable dt in set.Tables) { ISheet sheet1 = workbook.CreateSheet(dt.TableName); int CellCount = dt.Columns.Count;//列数 IRow RowHead = sheet1.CreateRow(0); //创建表头 //绑定字体样式到表头 IFont Headfont = workbook.CreateFont(); Headfont.FontName = "微软雅黑"; Headfont.Color = HSSFColor.BLACK.index; Headfont.FontHeightInPoints = 11; //绑定字体到样式上 ICellStyle Headstyle = workbook.CreateCellStyle(); Headstyle.VerticalAlignment = VerticalAlignment.CENTER; //垂直居中 Headstyle.Alignment = HorizontalAlignment.CENTER; //横向居中 //背景颜色 //Headstyle.FillPattern = FillPatternType.BIG_SPOTS; //Headstyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index; //Headstyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index; Headstyle.SetFont(Headfont); //边框颜色 Headstyle.BorderBottom = BorderStyle.DOTTED; Headstyle.BottomBorderColor = HSSFColor.GREY_40_PERCENT.index; Headstyle.BorderLeft = BorderStyle.DOTTED; Headstyle.LeftBorderColor = HSSFColor.GREY_40_PERCENT.index; Headstyle.BorderRight = BorderStyle.DOTTED; Headstyle.RightBorderColor = HSSFColor.GREY_40_PERCENT.index; Headstyle.BorderTop = BorderStyle.DOTTED; Headstyle.TopBorderColor = HSSFColor.GREY_40_PERCENT.index; //创建表头列 List SellerList = new List (); //门店 List IntervalList = new List (); //档期 foreach (DataColumn col in dt.Columns) { if (col.ColumnName == "PName" || col.ColumnName == "SellerInterval") continue; string[] colname = col.ColumnName.Split(new char[] { '_' }, StringSplitOptions.RemoveEmptyEntries); if (!SellerList.Contains(colname[0])) SellerList.Add(colname[0]); if (!IntervalList.Contains(colname[1])) IntervalList.Add(colname[1]); } ICell cell = RowHead.CreateCell(0); cell.SetCellValue("商品名"); cell.CellStyle = Headstyle; sheet1.SetColumnWidth(0, 30 * 256); int j=1; foreach(var seller in SellerList) { int s = j; foreach(string Interval in IntervalList) { cell = RowHead.CreateCell(j); cell.SetCellValue(seller); cell.CellStyle = Headstyle; sheet1.SetColumnWidth(j, 21 * 100); j++; } int e = j; sheet1.AddMergedRegion(new CellRangeAddress(0, 0, s, e-1)); } cell = RowHead.CreateCell(dt.Columns.Count-1); cell.SetCellValue("销售量"); cell.CellStyle = Headstyle; sheet1.SetColumnWidth(dt.Columns.Count - 1, 21 * 156); RowHead.Height = 25 * 20; IRow RowHeadt = sheet1.CreateRow(1); //创建表头 cell = RowHeadt.CreateCell(0); cell.SetCellValue(""); cell.CellStyle = Headstyle; sheet1.SetColumnWidth(0, 30 * 256); j = 1; foreach (var seller in SellerList) { foreach (string Interval in IntervalList) { cell = RowHeadt.CreateCell(j); cell.SetCellValue(Interval); cell.CellStyle = Headstyle; sheet1.SetColumnWidth(j, 21 * 100); j++; } } cell = RowHeadt.CreateCell(dt.Columns.Count - 1); cell.SetCellValue("销售量"); cell.CellStyle = Headstyle; sheet1.SetColumnWidth(dt.Columns.Count - 1, 21 * 156); RowHeadt.Height = 25 * 20; //合并头部单元格 sheet1.AddMergedRegion(new CellRangeAddress(0, 1, 0, 0)); sheet1.AddMergedRegion(new CellRangeAddress(0, 1, dt.Columns.Count - 1, dt.Columns.Count - 1)); //填充内容 //绑定字体样式到表格内容 IFont font = workbook.CreateFont(); //字体样式 font.FontName = "微软雅黑"; font.Color = HSSFColor.BLACK.index; font.FontHeightInPoints = 11; ICellStyle style = workbook.CreateCellStyle(); style.VerticalAlignment = VerticalAlignment.CENTER; style.Alignment = HorizontalAlignment.RIGHT; style.SetFont(font); //边框样式 style.BorderBottom = BorderStyle.DOTTED; style.BottomBorderColor = HSSFColor.GREY_40_PERCENT.index; style.BorderLeft = BorderStyle.DOTTED; style.LeftBorderColor = HSSFColor.GREY_40_PERCENT.index; style.BorderRight = BorderStyle.DOTTED; style.RightBorderColor = HSSFColor.GREY_40_PERCENT.index; style.BorderTop = BorderStyle.DOTTED; style.TopBorderColor = HSSFColor.GREY_40_PERCENT.index; for (int i = 0; i < dt.Rows.Count; i++) { IRow row = sheet1.CreateRow((i + 2)); for (int m = 0; m < CellCount; m++) { ICell Tcell = row.CreateCell(m); Tcell.SetCellValue(dt.Rows[i][m].ToString()); Tcell.CellStyle = style; } row.Height = 20 * 20; } } string path = Path.Combine("~/Uploads/" + DateTime.Now.Year + "/" + DateTime.Now.Month + "/" + DateTime.Now.Day + "/"); if (!Directory.Exists(HttpContext.Current.Server.MapPath(path))) { Directory.CreateDirectory(HttpContext.Current.Server.MapPath(path)); } string FileName = Guid.NewGuid()+".xlsx"; var fullPath = path + FileName; FileStream sw = File.Create(HttpContext.Current.Server.MapPath(fullPath)); workbook.Write(sw); sw.Close(); return fullPath; } catch (Exception ex) { throw ex; } } /// /// 文件下载 /// /// 文件路径 public static void DownLoad(string FilePath,string FileName) { FileInfo fileInfo = new FileInfo(HttpContext.Current.Server.MapPath(FilePath)); //以字符流的形式下载文件 FileStream fs = new FileStream(HttpContext.Current.Server.MapPath(FilePath), FileMode.Open); byte[] bytes = new byte[(int)fs.Length]; fs.Read(bytes, 0, bytes.Length); fs.Close(); HttpContext.Current.Response.ContentType = "application/octet-stream"; //通知浏览器下载文件而不是打开 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8)); HttpContext.Current.Response.BinaryWrite(bytes); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); }