C#生成Excel另辟蹊径的方法

想用c#生成excel，在网上搜一下，大多都是借助GridView，其实这种生成方式，生成的文件，根本就不是真正的Excel，而是html。
如果数字超过15位的话，会转化成科学计数法，就不准确了。
用com组件的话，也行，就是不太好用，速度还慢。
第三方类库ExcelLibrary，确实不错，但是它是gpl协议，还是舍弃了。
下面介绍一个另辟蹊径的方法。这个方法是借助LocalReport实现的。
导出的是真正的Excel文件，数据准确，如果数字长度尝过15位，自动转换成字符串。
其原理是动态的生成.rdlc本地报表文件，然后通过它生成excel文件。本地报表也可以生成pfd等其他文件。
下面是代码，出自我的开源项目，http://lmcommon.codeplex.com/
写的比较粗糙，仅供参考吧，不过还能用。
View Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Windows.Forms;
using System.Data;

namespace Lm.Common.Excel
{
    public class ExcelBuilder
    {
        protected DataTable dataTable { get; set; }

        public ExcelBuilder(DataTable dt)
        {
            this.dataTable = dt;
        }

        protected string GenerateRdlc()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
            sb.Append("<Report xmlns:rd=\"http://schemas.microsoft.com/SQLServer/reporting/reportdesigner\" xmlns=\"http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition\">");
            sb.Append("<DataSources>");
            sb.Append("<DataSource Name=\"DataSet1\">");
            sb.Append("<ConnectionProperties>");
            sb.Append("<DataProvider>System.Data.DataSet</DataProvider>");
            sb.Append("<ConnectString>/* Local Connection */</ConnectString>");
            sb.Append("</ConnectionProperties>");
            sb.Append("<rd:DataSourceID>" + Guid.NewGuid().ToString() + "</rd:DataSourceID>");
            sb.Append("</DataSource>");
            sb.Append("</DataSources>");
            sb.Append("<DataSets>");
            sb.Append("<DataSet Name=\"DataSet1\">");
            sb.Append("<Fields>");
            for (var i = 0; i < this.dataTable.Columns.Count; i++)
            {
                var c = this.dataTable.Columns[i];
                sb.Append("<Field Name=\"" + c.ColumnName + "\">");
                sb.Append("<DataField>" + c.ColumnName + "</DataField>");
                sb.Append("<rd:TypeName>" + c.DataType.ToString() + "</rd:TypeName>");
                sb.Append("</Field>");
            }
            sb.Append("</Fields>");
            sb.Append("<Query>");
            sb.Append("<DataSourceName>DataSet1</DataSourceName>");
            sb.Append("<CommandText>/* Local Query */</CommandText>");
            sb.Append("</Query>");
            //sb.Append("<rd:DataSetInfo>");
            //sb.Append("<rd:DataSetName>wmsTestDataSet</rd:DataSetName>");
            //sb.Append(@"<rd:SchemaPath>D:\我的文档\桌面\TestApp\rdlcTest\wmsTestDataSet.xsd</rd:SchemaPath>");
            //sb.Append("<rd:TableName>BNK</rd:TableName>");
            //sb.Append("<rd:TableAdapterFillMethod>Fill</rd:TableAdapterFillMethod>");
            //sb.Append("<rd:TableAdapterGetDataMethod>GetData</rd:TableAdapterGetDataMethod>");
            //sb.Append("<rd:TableAdapterName>BNKTableAdapter</rd:TableAdapterName>");
            //sb.Append("</rd:DataSetInfo>");
            sb.Append("</DataSet>");
            sb.Append("</DataSets>");
            sb.Append("<Body>");
            sb.Append("<ReportItems>");
            sb.Append("<Tablix Name=\"Tablix1\">");
            sb.Append("<TablixBody>");
            sb.Append("<TablixColumns>");
            for (var i = 0; i < this.dataTable.Columns.Count; i++)
            {
                var c = this.dataTable.Columns[i];
                sb.Append("<TablixColumn>");
                sb.Append("<Width>0.98425in</Width>");
                sb.Append("</TablixColumn>");
            }
            sb.Append("</TablixColumns>");
            sb.Append("<TablixRows>");
            sb.Append("<TablixRow>");
            sb.Append("<Height>0.23622in</Height>");
            sb.Append("<TablixCells>");
            for (var i = 0; i < this.dataTable.Columns.Count; i++)
            {
                var c = this.dataTable.Columns[i];
                sb.Append("<TablixCell>");
                sb.Append("<CellContents>");
                sb.Append("<Textbox Name=\"Textbox" + (i + 1).ToString() + "\">");
                sb.Append("<CanGrow>true</CanGrow>");
                sb.Append("<KeepTogether>true</KeepTogether>");
                sb.Append("<Paragraphs>");
                sb.Append("<Paragraph>");
                sb.Append("<TextRuns>");
                sb.Append("<TextRun>");
                sb.Append("<Value>" + c.ColumnName + "</Value>");
                sb.Append("<Style />");
                sb.Append("</TextRun>");
                sb.Append("</TextRuns>");
                sb.Append("<Style />");
                sb.Append("</Paragraph>");
                sb.Append("</Paragraphs>");
                sb.Append("<rd:DefaultName>Textbox" + (i + 1).ToString() + "</rd:DefaultName>");
                sb.Append("<Style>");
                sb.Append("<Border>");
                sb.Append("<Color>LightGrey</Color>");
                sb.Append("<Style>Solid</Style>");
                sb.Append("</Border>");
                sb.Append("<PaddingLeft>2pt</PaddingLeft>");
                sb.Append("<PaddingRight>2pt</PaddingRight>");
                sb.Append("<PaddingTop>2pt</PaddingTop>");
                sb.Append("<PaddingBottom>2pt</PaddingBottom>");
                sb.Append("</Style>");
                sb.Append("</Textbox>");
                sb.Append("</CellContents>");
                sb.Append("</TablixCell>");
            }
            sb.Append("</TablixCells>");
            sb.Append("</TablixRow>");
            sb.Append("<TablixRow>");
            sb.Append("<Height>0.23622in</Height>");
            sb.Append("<TablixCells>");

            for (var i = 0; i < this.dataTable.Columns.Count; i++)
            {
                var c = this.dataTable.Columns[i];
                sb.Append("<TablixCell>");
                sb.Append("<CellContents>");
                sb.Append("<Textbox Name=\"" + c.ColumnName + "\">");
                sb.Append("<CanGrow>true</CanGrow>");
                sb.Append("<KeepTogether>true</KeepTogether>");
                sb.Append("<Paragraphs>");
                sb.Append("<Paragraph>");
                sb.Append("<TextRuns>");
                sb.Append("<TextRun>");
                sb.Append("<Value>=Fields!" + c.ColumnName + ".Value</Value>");
                sb.Append("<Style />");
                sb.Append("</TextRun>");
                sb.Append("</TextRuns>");
                sb.Append("<Style />");
                sb.Append("</Paragraph>");
                sb.Append("</Paragraphs>");
                sb.Append("<rd:DefaultName>" + c.ColumnName + "</rd:DefaultName>");
                sb.Append("<Style>");
                sb.Append("<Border>");
                sb.Append("<Color>LightGrey</Color>");
                sb.Append("<Style>Solid</Style>");
                sb.Append("</Border>");
                sb.Append("<PaddingLeft>2pt</PaddingLeft>");
                sb.Append("<PaddingRight>2pt</PaddingRight>");
                sb.Append("<PaddingTop>2pt</PaddingTop>");
                sb.Append("<PaddingBottom>2pt</PaddingBottom>");
                sb.Append("</Style>");
                sb.Append("</Textbox>");
                sb.Append("</CellContents>");
                sb.Append("</TablixCell>");
            }
            sb.Append("</TablixCells>");
            sb.Append("</TablixRow>");
            sb.Append("</TablixRows>");
            sb.Append("</TablixBody>");
            sb.Append("<TablixColumnHierarchy>");
            sb.Append("<TablixMembers>");
            for (var i = 0; i < this.dataTable.Columns.Count; i++)
            {
                sb.Append("<TablixMember />");
            }
            sb.Append("</TablixMembers>");
            sb.Append("</TablixColumnHierarchy>");
            sb.Append("<TablixRowHierarchy>");
            sb.Append("<TablixMembers>");
            sb.Append("<TablixMember>");
            sb.Append("<KeepWithGroup>After</KeepWithGroup>");
            sb.Append("</TablixMember>");
            sb.Append("<TablixMember>");
            sb.Append("<Group Name=\"详细信息\" />");
            sb.Append("</TablixMember>");
            sb.Append("</TablixMembers>");
            sb.Append("</TablixRowHierarchy>");
            sb.Append("<DataSetName>DataSet1</DataSetName>");
            sb.Append("<Height>1.2cm</Height>");
            sb.Append("<Width>7.5cm</Width>");
            sb.Append("<Style>");
            sb.Append("<Border>");
            sb.Append("<Style>None</Style>");
            sb.Append("</Border>");
            sb.Append("</Style>");
            sb.Append("</Tablix>");
            sb.Append("</ReportItems>");
            sb.Append("<Height>2in</Height>");
            sb.Append("<Style />");
            sb.Append("</Body>");
            sb.Append("<Width>6.5in</Width>");
            sb.Append("<Page>");
            sb.Append("<PageHeight>29.7cm</PageHeight>");
            sb.Append("<PageWidth>21cm</PageWidth>");
            sb.Append("<LeftMargin>2cm</LeftMargin>");
            sb.Append("<RightMargin>2cm</RightMargin>");
            sb.Append("<TopMargin>2cm</TopMargin>");
            sb.Append("<BottomMargin>2cm</BottomMargin>");
            sb.Append("<ColumnSpacing>0.13cm</ColumnSpacing>");
            sb.Append("<Style />");
            sb.Append("</Page>");
            sb.Append("<rd:ReportID>" + Guid.NewGuid().ToString() + "</rd:ReportID>");
            sb.Append("<rd:ReportUnitType>Cm</rd:ReportUnitType>");
            sb.Append("</Report>");
            return sb.ToString();
        }

        /// <summary>
        /// 把DataTable生成excel表
        /// </summary>
        /// <param name="excelPath">要保存的路径</param>
        public void SaveExcel(string excelPath)
        {
            var rdlc = this.GenerateRdlc();
            Microsoft.Reporting.WinForms.LocalReport report = new Microsoft.Reporting.WinForms.LocalReport();
            report.LoadReportDefinition(new StringReader(rdlc));

            report.DataSources.Clear();
            report.DataSources.Add(new Microsoft.Reporting.WinForms.ReportDataSource("DataSet1", this.dataTable));
            var buf = report.Render("Excel");
            report.Dispose();

            FileStream fs = new FileStream(excelPath, FileMode.Create);
            fs.Write(buf, 0, buf.Length);
            fs.Flush();
            fs.Dispose();
        }

        /// <summary>
        /// 把excel生成一个字节数组
        /// </summary>
        /// <returns>excel</returns>
        public byte[] GenerateExcel()
        {
            var rdlc = this.GenerateRdlc();
            using (Microsoft.Reporting.WinForms.LocalReport report = new Microsoft.Reporting.WinForms.LocalReport())
            {
                report.LoadReportDefinition(new StringReader(rdlc));
                report.DataSources.Clear();
                report.DataSources.Add(new Microsoft.Reporting.WinForms.ReportDataSource("DataSet1", this.dataTable));
                var buf = report.Render("Excel");
                return buf;
            }
        }
    }
}
posted on 2012-09-13 08:46 开心的饭桶阅读(933) 评论(0) 编辑收藏举报
会员力量，点亮园子希望
刷新页面返回顶部
开心的饭桶

公告
