解决思路:
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