表格导出之Aspose.Cells

该导出方法需要引用Aspose.Cells

  1         /// <summary>
  2         /// Excel导出通用
  3         /// </summary>
  4         /// <param name="ReportTitleName">报表名称</param>
  5         /// <param name="list">数据源</param>
  6         /// <param name="IsColumn">是否有表头 默认有</param>
  7         /// <returns></returns>
  8         public static MemoryStream DatabseToExcel(string ReportTitleName, DataTable list, int IsColumn = 1)
  9         {
 10             try
 11             {
 12                 Workbook wb = new Workbook();
 13                 Worksheet ws = wb.Worksheets[0];
 14                 Cells cell = ws.Cells;
 15                 //定义并获取导出的数据源
 16                 string[,] _ReportDt = new string[list.Rows.Count, list.Columns.Count];
 17                 for (int i = 0; i < list.Rows.Count; i++)
 18                 {
 19                     for (int j = 0; j < list.Columns.Count; j++)
 20                     {
 21                         _ReportDt[i, j] = list.Rows[i][j].ToString();
 22                     }
 23                 }
 24                 //合并第一行单元格
 25                 Range range = cell.CreateRange(0, 0, 1, list.Columns.Count);
 26                 range.Merge();
 27                 cell["A1"].PutValue(ReportTitleName); //标题
 28 
 29                 //设置行高
 30                 cell.SetRowHeight(0, 20);
 31 
 32                 //设置字体样式
 33                 Style style1 = wb.Styles[wb.Styles.Add()];
 34                 style1.HorizontalAlignment = TextAlignmentType.Center;//文字居中
 35                 style1.Font.Name = "宋体";
 36                 style1.Font.IsBold = true;//设置粗体
 37                 style1.Font.Size = 12;//设置字体大小
 38 
 39                 Style style2 = wb.Styles[wb.Styles.Add()];
 40                 style2.HorizontalAlignment = TextAlignmentType.Center;
 41                 style2.Font.Size = 10;
 42 
 43                 //给单元格关联样式
 44                 cell["A1"].SetStyle(style1); //报表名字 样式
 45 
 46                 //设置Execl列名
 47                 int posStart = 0;
 48                 if (IsColumn == 1)
 49                 {
 50                     for (int i = 0; i < list.Columns.Count; i++)
 51                     {
 52                         cell[1, i].PutValue(list.Columns[i].ColumnName);
 53                         cell[1, i].SetStyle(style2);
 54                     }
 55 
 56                     posStart = 2;
 57                 }
 58                 else
 59                 {
 60                     posStart = 1;
 61                 }
 62                 //设置单元格内容
 63                 for (int i = 0; i < list.Rows.Count; i++)
 64                 {
 65                     for (int j = 0; j < list.Columns.Count; j++)
 66                     {
 67                         cell[i + posStart, j].PutValue(_ReportDt[i, j].ToString());
 68                         cell[i + posStart, j].SetStyle(style2);
 69                     }
 70                 }
 71                 //自适应宽度
 72                 setColumnWithAuto(ws);
 73 
 74                 MemoryStream stream = new MemoryStream();
 75                 stream = wb.SaveToStream();
 76                 stream.Seek(0, SeekOrigin.Begin);
 77                 return stream;
 78             }
 79             catch (Exception e)
 80             {
 81 
 82                 return null;
 83             }
 84 
 85         }
 86         static void setColumnWithAuto(Worksheet sheet)
 87         {
 88             Cells cells = sheet.Cells;
 89             int columnCount = cells.MaxColumn;  //获取表页的最大列数
 90             int rowCount = cells.MaxRow;        //获取表页的最大行数
 91 
 92             for (int col = 0; col < columnCount; col++)
 93             {
 94                 sheet.AutoFitColumn(col, 0, rowCount);
 95             }
 96             for (int col = 0; col < columnCount; col++)
 97             {
 98                 cells.SetColumnWidthPixel(col, cells.GetColumnWidthPixel(col) + 30);
 99             }
100         }

 

posted @ 2018-04-26 17:42  JessieR  阅读(630)  评论(0)  编辑  收藏  举报