利用Aspose.Cells导出Datatable数据
面对一些的格式各样的到处数据的要求,自学了一点 Aspose.Cells中操作Excel知识,如下代码:
/// <summary> /// DataTable导出Excel /// </summary> /// <param name="page">当前页面</param> /// <param name="my_dt">DataTable</param> /// <param name="fileName">文件名</param> /// <param name="title">输入的抬头</param> public static void ExportExcel(Page page, DataTable my_dt, string fileName, string title = "") { Workbook my_wb = new Workbook(); Worksheet sheet = my_wb.Worksheets[0]; //title的样式 Aspose.Cells.Style t_style = my_wb.Styles[my_wb.Styles.Add()]; t_style.HorizontalAlignment = TextAlignmentType.Center;//居中 t_style.Font.IsBold = true; t_style.Font.Name = "宋体"; t_style.Font.Size = 20; t_style.Borders.SetStyle(CellBorderType.Thin);//边框样式 t_style.Borders.DiagonalStyle = CellBorderType.None;
//设置单元格背景色
t_style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0);
t_style.Pattern = BackgroundType.Solid;
//header 样式 Aspose.Cells.Style h_style = my_wb.Styles[my_wb.Styles.Add()]; h_style.HorizontalAlignment = TextAlignmentType.Center;//居中 h_style.Font.IsBold = true; h_style.Font.Name = "宋体"; h_style.Font.Size = 12; h_style.Borders.SetStyle(CellBorderType.Thin);//边框样式 h_style.Borders.DiagonalStyle = CellBorderType.None;//花纹 h_style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //Row样式 Aspose.Cells.Style r_style = my_wb.Styles[my_wb.Styles.Add()]; r_style.HorizontalAlignment = TextAlignmentType.Center;//居中 r_style.Font.IsBold = false; r_style.Font.Name = "宋体"; r_style.Font.Size = 12; r_style.Borders.SetStyle(CellBorderType.Thin);//边框样式 r_style.Borders.DiagonalStyle = CellBorderType.None; int tempRow = 0; if (title != "") { //标题的写入 sheet.Cells.Merge(0, 0, 1, my_dt.Columns.Count);//单元格从第几行第几列跨不跨行 Range range = sheet.Cells.CreateRange(0, 0, 1, my_dt.Columns.Count);//所在区域 Cell Cell = sheet.Cells[0, 0];//一个单元格 Cell.PutValue(title); range.Style = t_style; tempRow++; } //列的输入 for (var i = 0; i < my_dt.Columns.Count; i++) { sheet.Cells.Merge(tempRow, i, 1, 1);//单元格从第几行第几列跨不跨行 Cell m_Cell = sheet.Cells[tempRow, i];//一小格 m_Cell.PutValue(my_dt.Columns[i].ColumnName); m_Cell.SetStyle(h_style); } tempRow++; //行的输出 for (var i = 0; i < my_dt.Columns.Count; i++) { for (var j = 0; j < my_dt.Rows.Count; j++) { sheet.Cells[j + tempRow, i].PutValue(my_dt.Rows[j][i]); sheet.Cells[j + tempRow, i].SetStyle(r_style); } } sheet.Cells.SetRowHeight(0, 20);//设置行高 //sheet.Cells.SetColumnWidth(1, 20);//设置列宽 sheet.AutoFitColumns(); //列宽自动匹配,当列宽过长是收缩 fileName = System.Web.HttpUtility.UrlEncode(fileName + ".xls", System.Text.Encoding.UTF8);//文件名编码 my_wb.Save(fileName, FileFormatType.Excel2003, SaveType.OpenInExcel, page.Response); page.Response.End(); }
本文只是记录学习心得,如果有误请提示博主加以修正。。。
谢谢您那么帅气,还能看完小主的文言文!