服务端不装Office ASP.NET 导出Excel控件 MyXls(项目在用)

如果从快速生成Excel报表，不调用Excel组件角度讲，MyXls可能是一种最好的选择之一，当然使用Open Xml方式也是不错的选择。MyXls是一个用C#语言开发的生成Excel报表的优秀开源项目，在快速开发中我一直比较喜欢它。MyXls官方的解释：

Writes and now Reads Excel files quickly and easily, including formatting. Generate Excel files for ASP.NET sites or .NET applications. Doesn't require Excel on the server or any licensing $. Compatible with Excel versions >= 97

 

MyXls可以用在.NET平台的诸如Windows Form，Asp.NET项目中，当然Sharepoint项目中也可以使用，支持的Excel版本包裹2003，2007等等(Excel versions >= 97)。

大凡开源项目的作者，多半都是重口味者，MyXls开源组件基于的技术是Excel文件的二进制格式(BIFF)，    OpenOffice.org 发布过的俩个文档 Excel File Format (BIFF8) Specification 和 Microsoft Compound Document (OLE2) Format Specification 对 Excel 的二进制格式做了一个比较详细的说明，MyXls的作者正是凭借这些信息把它开发而成的。

MyXls的下载地址:MyXls

 

下面通过2个例子来使用这个开源组件

生成单个Worksheet：

                        XlsDocument doc =  new XlsDocument(); 
                        doc.FileName =  "MyXlsWebAppDemo.xls"; 
                        Worksheet sheet = doc.Workbook.Worksheets.Add( "Hello World Sheet"); 
                        Cell cell = sheet.Cells.Add(1, 1,  "Hello,MyXls!"); 
                         for ( int i = 2; i <= 10; i++) 
                        { 
                                cell = sheet.Cells.Add(i, 1,  "51CTO五岁了!"); 
                                cell.Font.Weight = FontWeight.Bold; 
                                cell.Font.ColorIndex =2; //白 红 绿 蓝 黄 粉红等等颜色，可以通过源代码了解颜色 
                        } 
                         
                        doc.Send();

 

生成的报表如下：

 

生成多个WorkSheet

 XlsDocument xls =  new XlsDocument(); //新建一个xls文档 
                        xls.FileName =  "MyXlsDemo.xls"; //设定Excel文件名 
 
                        xls.SummaryInformation.Author =  "Terry Li";  //填加Excel文件作者信息 
                        xls.SummaryInformation.Subject =  "MyXls Demo"; //填加文件主题信息 
                        xls.DocumentSummaryInformation.Company =  "in2bits.org"; //填加文件公司信息 
 
string sheetName =  "第一个Sheet Demo"; #region    
 
                         string sheetName =  "第一个Sheet Demo"; 
                        Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName); //填加名为"第一个Sheet Demo"的sheet页 
                        Cells cells = sheet.Cells; //Cells实例是sheet页中单元格(cell)集合 
                         //单元格1-base 
                        Cell cell = cells.Add(2, 3,  "三"); //设定第2行，第3例单元格的值 
                        cell.HorizontalAlignment = HorizontalAlignments.Centered; //设定文字居中 
                        cell.Font.FontName =  "行楷"; //设定字体 
                        cell.Font.Height = 30 * 20; //设定字大小（字体大小是以 1/20 point 为单位的） 
                        cell.UseBorder =  true; //使用边框 
                        cell.BottomLineStyle = 2; //设定边框底线为粗线 
                        cell.BottomLineColor = Colors.Red; //设定颜色为红色 
                        cell.RightLineStyle = 2; 
                        cell.RightLineColor = Colors.Red; 
                         
                         
 
                         //cell的格式还可以定义在一个xf对象中 
                        XF cellXF = xls.NewXF(); //为xls生成一个XF实例（XF是cell格式对象） 
                        cellXF.HorizontalAlignment = HorizontalAlignments.Centered; //设定文字居中 
                        cellXF.Font.FontName =  "隶书"; //设定字体 
                        cellXF.Font.Height = 30 * 20; //设定字大小（字体大小是以 1/20 point 为单位的） 
                        cellXF.UseBorder =  true; //使用边框 
                        cellXF.BottomLineStyle = 2; //设定边框底线为粗线 
                        cellXF.BottomLineColor = Colors.Green; //设定颜色为绿色 
                        cellXF.LeftLineStyle = 2;  //设定边框左线为粗线 
                        cellXF.LeftLineColor = Colors.Green; 
 
                        cell = cells.Add(3, 3,  "国", cellXF); //以设定好的格式填加cell 
 
                        cellXF.Font.FontName =  "仿宋_GB2312"; 
                        cellXF.BottomLineStyle = 2;  //设定边框底线为粗线 
                        cellXF.BottomLineColor = Colors.Blue; //设定颜色为蓝色 
                        cellXF.RightLineStyle = 2; //设定边框右线为粗线 
                        cellXF.RightLineColor = Colors.Blue; //设定颜色为蓝色 
                        cellXF.LeftLineStyle = 0; 
                        cell = cells.Add(4, 3,  "志", cellXF); //格式可以多次使用 
 
                         //ColumnInfo colInfo = new ColumnInfo(xls, sheet);//生成列格式对象 
                         设定colInfo格式的起作用的列为第2列到第5列(列格式为0-base) 
                         //colInfo.ColumnIndexStart = 1;//起始列为第二列 
                         //colInfo.ColumnIndexEnd = 5;//终止列为第六列 
                         //colInfo.Width = 15 * 256;//列的宽度计量单位为 1/256 字符宽 
                         //sheet.AddColumnInfo(colInfo);//把格式附加到sheet页上（注：AddColumnInfo方法有点小问题，不给把colInfo对象多次附给sheet页） 
                         //colInfo.ColumnIndexEnd = 6;//可以更改列对象的值 
                         //ColumnInfo colInfo2 = new ColumnInfo(xls, sheet);//通过新生成一个列格式对象，才到能设定其它列宽度 
                         //colInfo2.ColumnIndexStart = 7; 
                         //colInfo2.ColumnIndexEnd = 8; 
                         //colInfo2.Width = 20 * 256; 
                         //sheet.AddColumnInfo(colInfo2); 
 
                        MergeArea meaA =  new MergeArea(2, 3, 5, 7); //一个合并单元格实例(合并第2行、第5例 到 第3行、第7例) 
                        sheet.AddMergeArea(meaA); //填加合并单元格 
                        cellXF.VerticalAlignment = VerticalAlignments.Centered; 
                        cellXF.Font.FontName =  "隶书"; 
                         //cellXF.Font.Height = 48 * 20; 
                         //cellXF.Font.Bold = true; 
                        cellXF.Pattern = 1; //设定单元格填充风格。如果设定为0，则是纯色填充(无色)，1代表没有间隙的实色 
                        cellXF.PatternBackgroundColor = Colors.Red; //填充的底色 
                        cellXF.PatternColor = Colors.Green; //设定填充线条的颜色 
                        cell = cells.Add(2, 5,  "晋/陈寿", cellXF); 
                        #endregion 
 
                        sheet.Cells.Merge(7, 9, 1, 4); 
                        cell = cells.Add(7, 1,  "MyXls 合并单元格 Demo"); 
                        cell.HorizontalAlignment = HorizontalAlignments.Centered; 
                        cell.VerticalAlignment = VerticalAlignments.Centered; 
 
                         for ( int sheetNumber = 1; sheetNumber <= 4; sheetNumber++) 
                        { 
                                sheetName =  "Sheet " + sheetNumber; 
                                 int rowMin = sheetNumber; 
                                 int rowCount = sheetNumber + 10; 
                                 int colMin = sheetNumber; 
                                 int colCount = sheetNumber + 10; 
                                sheet = xls.Workbook.Worksheets.Add(sheetName); 
                                cells = sheet.Cells; 
                                 for ( int r = 0; r < rowCount; r++) 
                                { 
                                         if (r == 0) 
                                        { 
                                                 for ( int c = 0; c < colCount; c++) 
                                                { 
                                                        cells.Add(rowMin + r, colMin + c,  "Column" + (c + 1)).Font.Bold =  true; 
                                                } 
                                        } 
                                         else 
                                        { 
                                                 for ( int c = 0; c < colCount; c++) 
                                                { 
                                                         int val = r + c; 
                                                        cell = cells.Add(rowMin + r, colMin + c, val+  ":51CTO五岁了!"); 
                                                         if (val % 2 != 0) 
                                                        { 
                                                                cell.HorizontalAlignment = HorizontalAlignments.Centered; 
                                                                cell.Font.FontName =  "Times New Roman"; 
                                                                cell.Font.Underline = UnderlineTypes.Double; 
                                                                cell.Font.ColorIndex = 2; 
                                                                cell.Rotation = 45;  //字符倾斜45度 
                                                        } 
                                                } 
                                        } 
                                } 
                        } 
 
                        xls.Send(); //XlsDocument.SendMethods.Inline