使用XML方式导出Excel(自动分页)

 /// <summary>
/// 使用XML方式导出Excel
/// </summary>
/// <param name="SQL"></param>
/// <param name="AdoConn">数据库连接字符</param>
/// <returns>EXCEL文件所在的地址</returns>
private String DataSetToExcel_XML(String SQL,String AdoConn)
{
    string ExcelFileName = base.WebPath + "" + GetFileName() + ".xls";
    if (File.Exists(ExcelFileName))
    {
        File.Delete(ExcelFileName);
    }
    StreamWriter writer = new StreamWriter(ExcelFileName, false);

    DataSet dsBook = new DataSet();
    dsBook = GetDataSet(SQL,AdoConn);
    int RowsCount = dsBook.Tables[0].Rows.Count;//总行数（不包括头）
    int ColumnsCount = dsBook.Tables[0].Columns.Count;//总列数
    int TableCount = RowsCount / 65535;//要生成的总表数
    int RowRemainder = RowsCount % 65535;
    if (RowRemainder != 0)
    {
        TableCount++;
    }

    int NowRow = 0;

    #region 头
    writer.WriteLine("<?xml version=\"1.0\"?>");
    writer.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
    writer.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
    writer.WriteLine(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
    writer.WriteLine(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
    writer.WriteLine(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
    writer.WriteLine(" xmlns:html=\"http://www.w3.org/TR/REC-html40/\">");
    writer.WriteLine(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
    writer.WriteLine(" <Author>Automated Report Generator Example</Author>");
    writer.WriteLine(string.Format(" <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
    writer.WriteLine(" <Company>揭阳华讯网络服务有限公司</Company>");
    writer.WriteLine(" <Version>11.6408</Version>");
    writer.WriteLine(" </DocumentProperties>");

    writer.WriteLine(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
    writer.WriteLine(" <WindowHeight>8955</WindowHeight>");
    writer.WriteLine(" <WindowWidth>11355</WindowWidth>");
    writer.WriteLine(" <WindowTopX>480</WindowTopX>");
    writer.WriteLine(" <WindowTopY>15</WindowTopY>");
    writer.WriteLine(" <ProtectStructure>False</ProtectStructure>");
    writer.WriteLine(" <ProtectWindows>False</ProtectWindows>");
    writer.WriteLine(" </ExcelWorkbook>");

    writer.WriteLine(" <Styles>");
    writer.WriteLine(" <Style ss:ID=\"Default\" ss:Name=\"Normal\">");
    writer.WriteLine("   <Alignment ss:Vertical=\"Bottom\"/>");
    writer.WriteLine("   <Borders/>");
    writer.WriteLine("   <Font/>");
    writer.WriteLine("   <Interior/>");
    writer.WriteLine("   <Protection/>");
    writer.WriteLine(" </Style>");
    writer.WriteLine(" <Style ss:ID=\"s21\">");
    writer.WriteLine("   <Alignment ss:Vertical=\"Bottom\" ss:WrapText=\"1\"/>");
    writer.WriteLine(" </Style>");
    writer.WriteLine(" </Styles>");
    #endregion

    #region 内容
    for (int i = 0; i < TableCount; i++)
    {
        writer.WriteLine(" <Worksheet ss:Name=\"Sheet" + i.ToString() + "\">");
        if (i < TableCount - 1 || RowRemainder==0)
        {
            writer.WriteLine(string.Format(" <Table ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\" x:FullColumns=\"1\"", ColumnsCount.ToString(), "65536"));
        }
        else
        {
            int rowc = RowsCount - i * 65535 + 1;
            writer.WriteLine(string.Format(" <Table ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\" x:FullColumns=\"1\"", ColumnsCount.ToString(), rowc.ToString()));
        }
        writer.WriteLine("   x:FullRows=\"1\">");

        //生成标题
        writer.WriteLine("<Row>");
        foreach (DataColumn eachCloumn in dsBook.Tables[0].Columns)
        {
            writer.Write("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">");

            writer.Write(eachCloumn.ColumnName.ToString());
            writer.WriteLine("</Data></Cell>");
        }
        writer.WriteLine("</Row>");


        for (int j = 0; j < 65536; j++)
        {
            if (NowRow == RowsCount-1)
            {
                break;
            }
            DataRow eachRow = dsBook.Tables[0].Rows[NowRow];

            writer.WriteLine("<Row>");

            for (int currentRow = 0; currentRow != ColumnsCount; currentRow++)
            {
                writer.Write("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">");
                writer.Write(eachRow[currentRow].ToString());
                writer.WriteLine("</Data></Cell>");
            }
            writer.WriteLine("</Row>");
            NowRow++;
        }
        writer.WriteLine(" </Table>");
        writer.WriteLine(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
        writer.WriteLine("   <Selected/>");
        writer.WriteLine("   <Panes>");
        writer.WriteLine("    <Pane>");
        writer.WriteLine("     <Number>3</Number>");
        writer.WriteLine("     <ActiveRow>1</ActiveRow>");
        writer.WriteLine("    </Pane>");
        writer.WriteLine("   </Panes>");
        writer.WriteLine("   <ProtectObjects>False</ProtectObjects>");
        writer.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
        writer.WriteLine(" </WorksheetOptions>");
        writer.WriteLine(" </Worksheet>");
    }

    writer.WriteLine("</Workbook>");
    #endregion

    writer.Close();
    return ExcelFileName;

}

转载于:https://www.cnblogs.com/QIAOYIJUN/archive/2013/05/02/3054068.html