Aspose.cell中的Excel模板导出数据//Excel模板导数据(Eexcel中根据DataTable中的个数，给多个Sheet中的模板赋值) dict中模板设置 &=$qyear datatable中设置 &=dtName.cloumnName public void DataSetToManyExcel(string fileName, string fileUrl, Page page, DataSet my_ds, Dictionary<string, string> dict = null, params int[] columns){WorkbookDesigner wbookdesiger = new WorkbookDesigner();wbookdesiger.Open(fileUrl); //打开模板try{if (my_ds.Tables.Count > 0){for (var i = 0; i < my_ds.Tables.Count; i++){//设置DataTablewbookdesiger.SetDataSource(my_ds.Tables[i]);//设置变量if (dict != null){foreach (var di in dict)wbookdesiger.SetDataSource(di.Key, di.Value);}wbookdesiger.Process(i, false);//清除数据源wbookdesiger.ClearDataSource();} if (columns.Length > 0) { Style style = wbookdesiger.Workbook.Styles[wbookdesiger.Workbook.Styles.Add()];//新增样式 style.IsTextWrapped = true;//⽂本换⾏样式\n columns.ForEach(c => { var cells = wbookdesiger.Workbook.Worksheets[0].Cells[c]; cells.SetStyle(style); }); }fileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ".xls";wbookdesiger.Save(fileName, SaveType.OpenInExcel, FileFormatType.Excel2003, page.Response);page.Response.End();}}catch (Exception ex){throw ex;}}//Excel模板导数据(Eexcel中根据DataTable中的个数，复制第⼀个Sheet中的模板，然后分别赋值)public void DataSetToOneExcel(string fileName, string fileUrl, Page page, DataSet my_ds, string tabName = "", Dictionary<int, string> my_dic = null){WorkbookDesigner my_designer = new WorkbookDesigner();my_designer.Open(fileUrl);//打开⽂件Workbook my_workbook = my_designer.Workbook;try{if (my_ds.Tables.Count > 0){//给表格赋值for (var i = 0; i < my_ds.Tables.Count; i++){var temp = i + 1 >= my_ds.Tables.Count ? i : i + 1;//TableNameif (i == 0)my_ds.Tables[i].TableName = tabName != "" ? tabName : "dt";elsemy_ds.Tables[temp].TableName = tabName != "" ? tabName + i : "dt" + temp;my_workbook.Worksheets.AddCopy(0);//复制前⼀个Sheet模板my_workbook.Worksheets[i + 1].Replace(my_ds.Tables[0].TableName, my_ds.Tables[temp].TableName);//替换当前Sheet的下个Sheet的TableName my_designer.SetDataSource(my_ds.Tables[i]);my_designer.Process(i, false);my_designer.ClearDataSource();}//删除最后⼀个sheetmy_workbook.Worksheets.RemoveAt(my_workbook.Worksheets.Count - 1);//给Sheet的Name赋值if (my_dic != null){for (var i = 0; i < my_workbook.Worksheets.Count; i++){foreach (var dd in my_dic){if (i == dd.Key)my_workbook.Worksheets[i].Name = dd.Value;}}}fileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ".xls";my_designer.Save(fileName, SaveType.OpenInExcel, FileFormatType.Excel2003, page.Response);page.Response.End();}}catch (Exception ex){throw ex;}}补充：当表格数据列是动态的时候，显然模板就不⼤适⽤了，这时候我们可以使⽤Apose.Cells的填充⽅法Aspose.Cells.WorkbookDesigner wbookdesiger = new Aspose.Cells.WorkbookDesigner();wbookdesiger.Open(fileUrl); //打开模板//wbookdesiger.SetDataSource(dt);//wbookdesiger.Process();//数据渲染var workbook = wbookdesiger.Workbook;//最后合成⼀个DT来，⼀个合成的动态数据表Aspose.Cells.Cell cell = workbook.Worksheets[0].Cells.FindString("#$DATATABLE$#", null, true);//模板中的列，找出模板中固定的单元格if (cell != null){workbook.Worksheets[0].Cells.ImportDataTable(dt, true, );}int temp = 0; //合并单元格while (temp < dt.Rows.Count){var rows = dt.Select(" 序号=" + dt.Rows[temp]["序号"]);workbook.Worksheets[0].Cells.Merge(1 + temp, 0, rows.Length, 1);workbook.Worksheets[0].Cells.Merge(1 + temp, 1, rows.Length, 1);workbook.Worksheets[0].Cells.Merge(1 + temp, 2, rows.Length, 1);workbook.Worksheets[0].Cells.Merge(1 + temp, 3, rows.Length, 1);workbook.Worksheets[0].Cells.Merge(1 + temp, 4, rows.Length, 1);workbook.Worksheets[0].Cells.Merge(1 + temp, 11, rows.Length, 1);workbook.Worksheets[0].Cells.Merge(1 + temp, 12, rows.Length, 1);workbook.Worksheets[0].Cells.Merge(1 + temp, 13, rows.Length, 1);temp += rows.Length;}var sheet = workbook.Worksheets[0];var cells = sheet.Cells;int columnCount = cells.MaxColumn; //获取表页的最⼤列数int rowCount = cells.MaxRow; //获取表页的最⼤⾏数//适应列宽for (int col = 0; col < columnCount; col++){sheet.AutoFitColumn(col, 0, rowCount);}for (int col = 0; col < columnCount; col++){cells.SetColumnWidthPixel(col, cells.GetColumnWidthPixel(col) + 30);}//表格线Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;style.Borders[Aspose.Cells.BorderType.TopBorder].Color = System.Drawing.Color.Black;style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle =Aspose.Cells. CellBorderType.Thin;style.Borders[Aspose.Cells.BorderType.BottomBorder].Color = System.Drawing.Color.Black;style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;style.Borders[Aspose.Cells.BorderType.LeftBorder].Color = System.Drawing.Color.Black;style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle =Aspose.Cells.CellBorderType.Thin;style.Borders[Aspose.Cells.BorderType.RightBorder].Color = System.Drawing.Color.Black;Aspose.Cells.Range wstrange = sheet.Cells.CreateRange(0, 0, sheet.Cells.MaxRow + 1, sheet.Cells.MaxColumn + 1);Aspose.Cells.StyleFlag stFlag = new Aspose.Cells.StyleFlag();stFlag.All = true;wstrange.ApplyStyle(style, stFlag);var stream = workbook.SaveToStream();return File(stream.ToArray(), "application/octet-stream", "xxxx.xls");。
