用MyXls生成Excel报表（C#）总结

总结了一下MyXls导出Excle，分页之后导出全部数据。当然还有一些简单的方法。但是功能就局限了。

一：（网上搜集）

MyXLS 是一个快速和简单的读写 Excel 文件的 .NET 组件，可用在 ASP.NET 网站和 .NET 应用程序中，无需安装 Excel 程序，支持 Excel 97 以及以后的版本。

目前MyXls已经实现了单元格（cell）的格式设置，包括文本颜色、文本大小、字体、单位格边框、底色、列宽、行高，合并单元格，多个sheet页等功能。以下是MyXLS组件的一些用法：

1.创建一个Excel文档：

XlsDocument xls =new XlsDocument();

2.创建一个WorkSheet：

Worksheet ws = xls.Workbook.Worksheets.Add("WorkSheet1");

3.指定列格式：

ColumnInfo colInfo =new ColumnInfo(xls, ws);

colInfo.ColumnIndexStart =;

colInfo.ColumnIndexEnd =17;

colInfo.Width =15*256;

ws.AddColumnInfo(colInfo);

列格式必须每次都要重新定义，一个列格式不能重复使用。

4.指定单元格样式：

XF xf = xls.NewXF();

xf.HorizontalAlignment = HorizontalAlignments.Centered;

xf.VerticalAlignment = VerticalAlignments.Centered;

xf.Pattern =1;

xf.PatternColor = Colors.Default30;

xf.UseBorder =true;

xf.TopLineStyle =1;

xf.TopLineColor = Colors.Black;

xf.BottomLineStyle =1;

xf.BottomLineColor = Colors.Black; xf.LeftLineStyle =1;

xf.LeftLineColor = Colors.Black;

xf.RightLineStyle =1;

xf.RightLineColor = Colors.Black;

xf.Font.Bold =true;

xf.Font.Height =11*20;

xf.Font.ColorIndex =1;

5.给单元格赋值：

ws.Cells.Add(2, 3, "金额(万元)", xf);

6.合并单元格：

ws.Cells.Merge(1, 2, 2, 2); //或者ws.AddMergeArea(new MergeArea(1, 2, 1, 1));

7.MyXls合并单元格有个bug，就是合并后只是第一个单元格有样式，其余的样式丢失。所以写了个函数来合并：

MergeRegion(ref ws, xf, "机构", 1, 1, 2, 1);

publicvoid MergeRegion(ref Worksheet ws, XF xf, string title, int startRow, int startCol, int endRow, int endCol)

{

for (int i = startCol; i <= endCol; i++)

{

for (int j = startRow; j <= endRow; j++)

{

　　　　 ws.Cells.Add(j, i, title, xf);

}

ws.Cells.Merge(startRow, endRow, startCol, endCol); }

虽然效率不怎么样，但是对于出Excel报表，还OK。

8.指定单元格格式：

cell.Format = StandardFormats.Decimal_1;

具体更多请参考源代码的StandardFormats类。

9.保存或者发送Excel：

xls.Send(); //或者xls.Save();

二、结合项目

实例：

View Code

  1         protected void btnExcel_Click(object sender, EventArgs e)
  2         {
  3             DataTable dt = (DataTable)ViewState["dt"];
  4             string item = "客户：" + name + " 从" + start + "至" + end + " 采购详单";
  5             ExcelExport(dt, item);
  6         }
  7         //先添加引用给出的DLL文件
  8         private void ExcelExport(DataTable dt, string item)
  9         {
 10 
 11             XlsDocument xls = new XlsDocument();
 12             xls.FileName = "采购详单.xls";//指定文件名
 13 
 14             Worksheet sheet = xls.Workbook.Worksheets.Add("Inventory");
 15 
 16             #region 设置各数据列的大小
 17             ColumnInfo colInfo = null;
 18             int a = 0;
 19             int b = 0;
 20             //15列
 21             for (int i = 0; i < 15; i++)
 22             {
 23                 a = i == 0 ? 0 : (i + 1);
 24                 b = i == 0 ? 1 : (i + 1);
 25                 colInfo = new ColumnInfo(xls, sheet);
 26                 colInfo.ColumnIndexStart = (ushort)a;
 27                 colInfo.ColumnIndexEnd = (ushort)b;
 28                 colInfo.Width = 13 * 256;
 29                 sheet.AddColumnInfo(colInfo);
 30             }
 31             #endregion
 32 
 33             Cells cells = sheet.Cells;
 34 
 35             #region 合并单元格，得到报表标题
 36             //第一行标题
 37             MergeArea maTitle = new MergeArea(1, 2, 1, 15);
 38             sheet.AddMergeArea(maTitle);
 39             XF xfTitle = xls.NewXF();
 40             xfTitle.HorizontalAlignment = HorizontalAlignments.Centered;
 41             xfTitle.VerticalAlignment = VerticalAlignments.Centered;
 42             xfTitle.Font.FontName = "宋体";
 43             xfTitle.Font.Height = 16 * 20;
 44             xfTitle.Font.Bold = true;
 45             cells.Add(1, 1, "采购订单明细", xfTitle);
 46             #endregion
 47 
 48             //第二行描述
 49             MergeArea maTime1 = new MergeArea(3, 3, 1, 15);
 50             sheet.AddMergeArea(maTime1);
 51             XF xfTopBar = xls.NewXF();
 52             xfTopBar.Font.FontName = "宋体";
 53             //item为描述内容
 54             cells.Add(3, 1, item, xfTopBar);
 55 
 56             #region 设置Excel数据列标题的格式
 57             XF xfDataHead = xls.NewXF();
 58             xfDataHead.HorizontalAlignment = HorizontalAlignments.Centered;
 59             xfDataHead.VerticalAlignment = VerticalAlignments.Centered;
 60             xfDataHead.Font.FontName = "宋体";
 61             xfDataHead.Font.Bold = true;
 62             xfDataHead.UseBorder = true;
 63             xfDataHead.BottomLineStyle = 1;
 64             xfDataHead.BottomLineColor = Colors.Black;
 65             xfDataHead.TopLineStyle = 1;
 66             xfDataHead.TopLineColor = Colors.Black;
 67             xfDataHead.LeftLineStyle = 1;
 68             xfDataHead.LeftLineColor = Colors.Black;
 69             xfDataHead.RightLineStyle = 1;
 70             xfDataHead.RightLineColor = Colors.Black;
 71             #endregion
 72 
 73             #region 添加列标题
 74             cells.Add(4, 1, "单据号", xfDataHead);
 75             cells.Add(4, 2, "产品名称", xfDataHead);
 76             cells.Add(4, 3, "规格", xfDataHead);
 77             cells.Add(4, 4, "厚度", xfDataHead);
 78             cells.Add(4, 5, "等级", xfDataHead);
 79             cells.Add(4, 6, "单位", xfDataHead);
 80             cells.Add(4, 7, "数量", xfDataHead);
 81             cells.Add(4, 8, "过度", xfDataHead);
 82             cells.Add(4, 9, "包装", xfDataHead);
 83             cells.Add(4, 10, "销售区域", xfDataHead);
 84             cells.Add(4, 11, "颜色", xfDataHead);
 85             cells.Add(4, 12, "单位代码", xfDataHead);
 86             cells.Add(4, 13, "包装代码", xfDataHead);
 87             cells.Add(4, 14, "单价", xfDataHead);
 88             cells.Add(4, 15, "单价", xfDataHead);
 89             #endregion
 90 
 91             #region 设置各数据列的格式
 92 
 93             XF xfData = xls.NewXF();
 94             xfData.Font.FontName = "宋体";
 95             xfData.UseBorder = true;
 96             xfData.BottomLineStyle = 1;
 97             xfData.BottomLineColor = Colors.Black;
 98             xfData.TopLineStyle = 1;
 99             xfData.TopLineColor = Colors.Black;
100             xfData.LeftLineStyle = 1;
101             xfData.LeftLineColor = Colors.Black;
102             xfData.RightLineStyle = 1;
103             xfData.RightLineColor = Colors.Black;
104             #endregion
105 
106             #region 填充数据
107 
108             int j = 5;//从第五行开始为数据行
109 
110             double provideTotal = 0;
111 
112             double useTotal = 0;
113 
114             double lossTotal = 0;
115 
116             foreach (DataRow dr in dt.Rows)
117             {
118 
119                 cells.Add(j, 1, dr["djno"], xfData);
120                 cells.Add(j, 2, dr["itemname"], xfData);
121                 cells.Add(j, 3, dr["specs"], xfData);
122                 cells.Add(j, 4, dr["ply"], xfData);
123                 cells.Add(j, 5, dr["rank"], xfData);
124                 cells.Add(j, 6, dr["ut"], xfData);
125                 cells.Add(j, 7, dr["num"], xfData);
126                 cells.Add(j, 8, dr["transcolor"], xfData);
127                 cells.Add(j, 9, dr["packname"], xfData);
128                 cells.Add(j, 10, dr["salesarea"], xfData);
129                 cells.Add(j, 11, dr["color"], xfData);
130                 cells.Add(j, 12, dr["utno"], xfData);
131                 cells.Add(j, 13, dr["packno"], xfData);
132                 cells.Add(j, 14, dr["zkprice"], xfData);
133                 cells.Add(j, 15, dr["zkamt"], xfData);
134                 j++;
135 
136             }
137             #endregion
138             string path = Server.MapPath("~/");
139             xls.Save(path + "\\excel\\", true);
140             string down = Server.MapPath("~/") + "\\excel\\" + xls.FileName;
141             FileInfo DownloadFile = new FileInfo(down);
142             Response.Clear();
143             Response.ClearHeaders();
144             Response.Buffer = false;
145             Response.ContentType = "application/octet-stream";
146             Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(xls.FileName, System.Text.Encoding.UTF8));
147             Response.AppendHeader("Content-Length", DownloadFile.Length.ToString());
148             Response.WriteFile(DownloadFile.FullName);
149             Response.Flush();
150             Response.End();
151             //xls.Send();
152         }

效果图下载：效果

下载：org.in2bits.MyXls