用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();

二、结合项目

实例：

        protected void btnExcel_Click(object sender, EventArgs e)
        {
            DataTable dt = (DataTable)ViewState["dt"];
            string item = "客户：" + name + " 从" + start + "至" + end + " 采购详单";
            ExcelExport(dt, item);
        }
        //先添加引用给出的DLL文件
        private void ExcelExport(DataTable dt, string item)
        {

            XlsDocument xls = new XlsDocument();
            xls.FileName = "采购详单.xls";//指定文件名

            Worksheet sheet = xls.Workbook.Worksheets.Add("Inventory");

            #region 设置各数据列的大小
            ColumnInfo colInfo = null;
            int a = 0;
            int b = 0;
            //15列
            for (int i = 0; i < 15; i++)
            {
                a = i == 0 ? 0 : (i + 1);
                b = i == 0 ? 1 : (i + 1);
                colInfo = new ColumnInfo(xls, sheet);
                colInfo.ColumnIndexStart = (ushort)a;
                colInfo.ColumnIndexEnd = (ushort)b;
                colInfo.Width = 13 * 256;
                sheet.AddColumnInfo(colInfo);
            }
            #endregion

            Cells cells = sheet.Cells;

            #region 合并单元格，得到报表标题
            //第一行标题
            MergeArea maTitle = new MergeArea(1, 2, 1, 15);
            sheet.AddMergeArea(maTitle);
            XF xfTitle = xls.NewXF();
            xfTitle.HorizontalAlignment = HorizontalAlignments.Centered;
            xfTitle.VerticalAlignment = VerticalAlignments.Centered;
            xfTitle.Font.FontName = "宋体";
            xfTitle.Font.Height = 16 * 20;
            xfTitle.Font.Bold = true;
            cells.Add(1, 1, "采购订单明细", xfTitle);
            #endregion

            //第二行描述
            MergeArea maTime1 = new MergeArea(3, 3, 1, 15);
            sheet.AddMergeArea(maTime1);
            XF xfTopBar = xls.NewXF();
            xfTopBar.Font.FontName = "宋体";
            //item为描述内容
            cells.Add(3, 1, item, xfTopBar);

            #region 设置Excel数据列标题的格式
            XF xfDataHead = xls.NewXF();
            xfDataHead.HorizontalAlignment = HorizontalAlignments.Centered;
            xfDataHead.VerticalAlignment = VerticalAlignments.Centered;
            xfDataHead.Font.FontName = "宋体";
            xfDataHead.Font.Bold = true;
            xfDataHead.UseBorder = true;
            xfDataHead.BottomLineStyle = 1;
            xfDataHead.BottomLineColor = Colors.Black;
            xfDataHead.TopLineStyle = 1;
            xfDataHead.TopLineColor = Colors.Black;
            xfDataHead.LeftLineStyle = 1;
            xfDataHead.LeftLineColor = Colors.Black;
            xfDataHead.RightLineStyle = 1;
            xfDataHead.RightLineColor = Colors.Black;
            #endregion

            #region 添加列标题
            cells.Add(4, 1, "单据号", xfDataHead);
            cells.Add(4, 2, "产品名称", xfDataHead);
            cells.Add(4, 3, "规格", xfDataHead);
            cells.Add(4, 4, "厚度", xfDataHead);
            cells.Add(4, 5, "等级", xfDataHead);
            cells.Add(4, 6, "单位", xfDataHead);
            cells.Add(4, 7, "数量", xfDataHead);
            cells.Add(4, 8, "过度", xfDataHead);
            cells.Add(4, 9, "包装", xfDataHead);
            cells.Add(4, 10, "销售区域", xfDataHead);
            cells.Add(4, 11, "颜色", xfDataHead);
            cells.Add(4, 12, "单位代码", xfDataHead);
            cells.Add(4, 13, "包装代码", xfDataHead);
            cells.Add(4, 14, "单价", xfDataHead);
            cells.Add(4, 15, "单价", xfDataHead);
            #endregion

            #region 设置各数据列的格式

            XF xfData = xls.NewXF();
            xfData.Font.FontName = "宋体";
            xfData.UseBorder = true;
            xfData.BottomLineStyle = 1;
            xfData.BottomLineColor = Colors.Black;
            xfData.TopLineStyle = 1;
            xfData.TopLineColor = Colors.Black;
            xfData.LeftLineStyle = 1;
            xfData.LeftLineColor = Colors.Black;
            xfData.RightLineStyle = 1;
            xfData.RightLineColor = Colors.Black;
            #endregion

            #region 填充数据

            int j = 5;//从第五行开始为数据行

            double provideTotal = 0;

            double useTotal = 0;

            double lossTotal = 0;

            foreach (DataRow dr in dt.Rows)
            {

                cells.Add(j, 1, dr["djno"], xfData);
                cells.Add(j, 2, dr["itemname"], xfData);
                cells.Add(j, 3, dr["specs"], xfData);
                cells.Add(j, 4, dr["ply"], xfData);
                cells.Add(j, 5, dr["rank"], xfData);
                cells.Add(j, 6, dr["ut"], xfData);
                cells.Add(j, 7, dr["num"], xfData);
                cells.Add(j, 8, dr["transcolor"], xfData);
                cells.Add(j, 9, dr["packname"], xfData);
                cells.Add(j, 10, dr["salesarea"], xfData);
                cells.Add(j, 11, dr["color"], xfData);
                cells.Add(j, 12, dr["utno"], xfData);
                cells.Add(j, 13, dr["packno"], xfData);
                cells.Add(j, 14, dr["zkprice"], xfData);
                cells.Add(j, 15, dr["zkamt"], xfData);
                j++;

            }
            #endregion
            string path = Server.MapPath("~/");
            xls.Save(path + "\\excel\\", true);
            string down = Server.MapPath("~/") + "\\excel\\" + xls.FileName;
            FileInfo DownloadFile = new FileInfo(down);
            Response.Clear();
            Response.ClearHeaders();
            Response.Buffer = false;
            Response.ContentType = "application/octet-stream";
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(xls.FileName, System.Text.Encoding.UTF8));
            Response.AppendHeader("Content-Length", DownloadFile.Length.ToString());
            Response.WriteFile(DownloadFile.FullName);
            Response.Flush();
            Response.End();
            //xls.Send();
        }

效果图下载：效果

下载：org.in2bits.MyXls