Asp.Net中应用Aspose.Cells输出报表到Excel[转]
解决思路：
1、找个可用的Aspose.Cells（有钱还是买个正版吧，谁开发个东西也不容易）；
2、在.Net方案中引用此Cells；
3、写个函数ToExcel(传递一个DataTable)，可以另写个SetCellStyle的函数专门给各列设置样式。
4、在按钮的Click事件中调用此ToExcel()即可。
注：想更详细了解的到Aspose网站去，那里有很多在线文档，够你看的了。也可以下载个Demo程序研究。
部分代码贴在这里，代码还有需要改进的地方，暂时这么用着，给自己以后方便查找，再者给真正接触此控件的同志们抛个砖头：
ExportToExcel()的：
/// <summary> /// 利用Aspose.Cells对DataTable数据生成Excel文件 /// </summary> /// <returns>是否成功</returns> /// <param name="response">包含存储路径，直接用Response即可</param> /// <param name="dt">数据体</param> /// <param name="FileName">文件名</param> /// <param name="SheetName">sheet名</param> /// <param name="Title">表头</param> /// <param name="ColTitle">列标题，字符串数组</param> /// <param name="ColName">列名，字符串数组</param> /// <param name="ColWidth">列宽，整数数组</param> /// <param name="ColStyle">列样式，整数数组，1=居中文本、2=特殊字体Georgia、3=居左文本、4=整数(x)、5=2位小数(x,xxx.xx)、6=日期(yyyy-M-d不带时间)、7=百分数(0.123=12.3%)</param> /// <param name="ColTitleRow">列标题的开始行号，有title的设为1即可</param> /// <param name="err">返回的错误信息</param> public bool ExportToExcel(System.Web.HttpResponse response,DataTable dt, string FileName, string SheetName, string Title, ArrayList ColTitle, ArrayList ColName, ArrayList ColWidth, ArrayList ColStyle, int ColTitleRow, ref string err) { //先检查各数组是否个数一致 if (ColTitle.Count != ColName.Count || ColTitle.Count != ColWidth.Count || ColTitle.Count != ColStyle.Count) { err = "数据组个数不一致"; return false; } try { Workbook workbook = new Workbook(); //打开模版文件 // string path = System.Web.HttpContext.Current.Server.MapPath("~"); // path = path.Substring(0, path.LastIndexOf("//")); // path += @"/designer/Workbooks/NumberFormatting.xls"; // workbook.Open(path); //打开sheet workbook.Worksheets.Clear(); Worksheet worksheet = workbook.Worksheets.Add(SheetName); worksheet = workbook.Worksheets[0]; Cells cells = worksheet.Cells; //cells.ClearContents(0,0,60,30); //加入样式 ArrayList styles = new ArrayList(); styles = SetCellStyle(workbook, ColStyle); //加入Title即表头，类似“xxx汇总表”，其占居所有列数 Range w;//范围 if(ColTitleRow>0) { //Put数据到某个cell中 cells[0,0].PutValue(Title); //设置行高 cells.SetRowHeight(0, 35); //合并单元格 cells.Merge(0,0,1,(byte)ColName.Count); //CreateRange函数参数说明：cells.CreateRange(firstrow, firstcol, rownumber, colnumber) w=cells.CreateRange(0,0,1,(byte)ColName.Count); //设置该合并单元的Style //w.Style = (Aspose.Cells.Style)styles[ColStyle.Count]; w.Style = workbook.Styles["SheetTitle"]; } //给各列的标题行PutValue，类似“序号，类型，名称，价格，数量，合计” int currow = ColTitleRow; byte curcol = 0; foreach(string s in ColTitle) { cells[currow,curcol++].PutValue(s); cells.SetRowHeight(ColTitleRow, 25); } //设置列标题行的Style w=cells.CreateRange(currow,0,1,ColName.Count); w.Style = (Aspose.Cells.Style)styles[ColStyle.Count+1]; //上面这行也可以写成 //w.Style = workbook.Styles["ColTitle"];//ColTitle在函数SetCellStyle中设置了 currow++; //将数据体按顺序插入各cell for(int i=0; i<dt.Rows.Count; i++) { curcol = 0; for(int j=0; j<ColName.Count; j++) { object val = dt.Rows[i][ColName[j].ToString()].ToString().Trim(); switch (int.Parse(ColStyle[j].ToString())) { case 4://整数 if (val.ToString().Length>0) val = Int32.Parse(val.ToString()); else val = ""; break; case 5://2位小数 if (val.ToString().Length>0) val = Decimal.Parse(val.ToString()); else val = ""; break; case 6://日期 if (val.ToString().Length>0) val = DateTime.Parse(val.ToString()); else val = ""; break; case 7://百分数，1=100% if (val.ToString().Length>0) val = Decimal.Parse(val.ToString()); else val = ""; break; default: break; }//end switch cells[currow,curcol++].PutValue(val); }//end for j currow ++; } //end for i curcol = 0; //设置数据体Style for(int i=0; i<dt.Columns.Count; i++) { w = cells.CreateRange(ColTitleRow+1, i, dt.Rows.Count, 1); w.Style = (Aspose.Cells.Style)styles[i]; } //w=cells.CreateRange(ColTitleRow+1,col,currow,ColName.Count); //w.Style=excel.Styles["Data"]; //设置各列宽度 foreach(int s in ColWidth) cells.SetColumnWidth(curcol++, s); /* //********可参考的格式设置******************************************************* //Set number format with built-in index for (int i = 0; i < 37; i ++) { cells[i, 1].PutValue(1234.5); //int Number = cells[i, 0].IntValue; //Set the display number format cells[i, 1].Style.Number = i; } //Set number format with custom format string for (int i = 1; i < 4; i ++) { cells[i, 3].PutValue(1234.5); //Set the display custom number format cells[i, 3].Style.Custom = cells[i, 2].StringValue; } //********可参考的格式设置******************************************************* */ //workbook.Save(FileName, FileFormatType.Default, SaveType.OpenInExcel, System.Web.HttpContext.Current.Response); workbook.Save(FileName, FileFormatType.Default, SaveType.OpenInExcel, response); return true; } catch (Exception ex) { err = ex.Message; return false; } }//end ExportToExcel() #endregion
SetStyle()的：
#region SetCellStyle()设置格式，如果需要增加新的格式，请在case后面增加，不要修改前面的 public ArrayList SetCellStyle(Workbook workbook, ArrayList styleindex) { //通用设置样式的 ArrayList CellStyle = new ArrayList(styleindex.Count + 2); Aspose.Cells.Style style = null; for (int i=0; i<styleindex.Count; i++) { int index = workbook.Styles.Add(); style = workbook.Styles[index]; style.Name = "Custom_Style" + ((int)(i + 1)).ToString(); style.ForegroundColor = Color.White; style.HorizontalAlignment = TextAlignmentType.Center; style.VerticalAlignment = TextAlignmentType.Center; style.Font.Name = "宋体"; style.Font.Size = 10; style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; switch((int)styleindex[i]) { case 1://居中文本 style.HorizontalAlignment = TextAlignmentType.Center; style.VerticalAlignment = TextAlignmentType.Center; break; case 2://特殊字体Georgia style.Font.Name = "Georgia"; break; case 3://文本列 style.HorizontalAlignment = TextAlignmentType.Left; break; case 4://整数列 1 Decimal 0 style.HorizontalAlignment = TextAlignmentType.Center; style.Number = 1; break; case 5://2位小数 39={Currency #,##0.00;-#,##0.00} 40={#,##0.00;[Red]-#,##0.00} style.HorizontalAlignment = TextAlignmentType.Right; style.Number = 40; break; case 6://日期列 14 Date yyyy-m-d style.HorizontalAlignment = TextAlignmentType.Center; style.Number = 14;//这个格式不是太好，还需要调整 break; case 7://百分比% 10 Percentage 0.00% style.HorizontalAlignment = TextAlignmentType.Center; style.Number = 10; break; default: break; }//end switch CellStyle.Add(style); } //特别增加一个用于表头的style.Name = "SheetTitle"; int sindex = workbook.Styles.Add(); style = workbook.Styles[sindex]; style.Name = "SheetTitle"; style.Font.Size = 14; style.Font.IsBold = true; style.Font.Name = "楷体_GB2312"; style.HorizontalAlignment = TextAlignmentType.Center; style.VerticalAlignment = TextAlignmentType.Center; style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; CellStyle.Add(style); //特别增加一个用于列标题的style.Name = "ColTitle"; sindex = workbook.Styles.Add(); style = workbook.Styles[sindex]; style.Name = "ColTitle"; style.Font.Size = 12; style.Font.IsBold = true; style.Font.Name = "宋体"; style.HorizontalAlignment = TextAlignmentType.Center; style.VerticalAlignment = TextAlignmentType.Center; style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; CellStyle.Add(style); //返回样式数组 return CellStyle; }//end SetCellStyle #endregion
