导出DataTable数据到Excel文档(通过验证,但没有标题)

最新推荐文章于 2022-05-27 13:57:30 发布

xdfwsl

最新推荐文章于 2022-05-27 13:57:30 发布

阅读量853

点赞数

分类专栏： ASP.NET 文章标签： excel 文档 office alignment generator styles

ASP.NET 专栏收录该内容

124 篇文章 0 订阅

订阅专栏

试试

/// <summary>
/// 导出DataTable数据到Excel文档
/// </summary>
/// <param name="SourceTb">数据源Table</param>
/// <param name="ExportFilePath">导出路径</param>
/// <returns></returns>
public static bool ExportToExcel(DataTable SourceTb,string ExportFilePath)
{
    try
    {
int num         = 0;
int rowCount    = SourceTb.Rows.Count + 1;
int columnCount = SourceTb.Columns.Count;
StreamWriter writer1 = new StreamWriter(ExportFilePath, false);
StreamWriter writer2 = writer1;
writer2.WriteLine("<?xml version=/"1.0/"?>");
writer2.WriteLine("<?mso-application progid=/"Excel.Sheet/"?>");
writer2.WriteLine("<Workbook xmlns=/"urn:schemas-microsoft-com:office:spreadsheet/"");
writer2.WriteLine(" xmlns:o=/"urn:schemas-microsoft-com:office:office/"");
writer2.WriteLine(" xmlns:x=/"urn:schemas-microsoft-com:office:excel/"");
writer2.WriteLine(" xmlns:ss=/"urn:schemas-microsoft-com:office:spreadsheet/"");
writer2.WriteLine(" xmlns:html=/"http://www.w3.org/TR/REC-html40/">");
writer2.WriteLine(" <DocumentProperties xmlns=/"urn:schemas-microsoft-com:office:office/">");
writer2.WriteLine(" <Author>Automated Report Generator Example</Author>");
writer2.WriteLine(string.Format(" <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
writer2.WriteLine(" <Company>Your Company Here</Company>");
writer2.WriteLine(" <Version>11.6408</Version>");
writer2.WriteLine(" </DocumentProperties>");
writer2.WriteLine(" <ExcelWorkbook xmlns=/"urn:schemas-microsoft-com:office:excel/">");
writer2.WriteLine(" <WindowHeight>8955</WindowHeight>");
writer2.WriteLine(" <WindowWidth>11355</WindowWidth>");
writer2.WriteLine(" <WindowTopX>480</WindowTopX>");
writer2.WriteLine(" <WindowTopY>15</WindowTopY>");
writer2.WriteLine(" <ProtectStructure>False</ProtectStructure>");
writer2.WriteLine(" <ProtectWindows>False</ProtectWindows>");
writer2.WriteLine(" </ExcelWorkbook>");
writer2.WriteLine(" <Styles>");
writer2.WriteLine(" <Style ss:ID=/"Default/" ss:Name=/"Normal/">");
writer2.WriteLine("   <Alignment ss:Vertical=/"Bottom/"/>");
writer2.WriteLine("   <Borders/>");
writer2.WriteLine("   <Font/>");
writer2.WriteLine("   <Interior/>");
writer2.WriteLine("   <Protection/>");
writer2.WriteLine(" </Style>");
writer2.WriteLine(" <Style ss:ID=/"s21/">");
writer2.WriteLine("   <Alignment ss:Vertical=/"Bottom/" ss:WrapText=/"1/"/>");
writer2.WriteLine(" </Style>");
writer2.WriteLine(" </Styles>");
writer2.WriteLine(" <Worksheet ss:Name=/"MyReport/">");
writer2.WriteLine(string.Format(" <Table ss:ExpandedColumnCount=/"{0}/" ss:ExpandedRowCount=/"{1}/" x:FullColumns=/"1/"", columnCount.ToString(), rowCount.ToString()));
writer2.WriteLine("   x:FullRows=/"1/">");
foreach (DataRow row in SourceTb.Rows)
{
     writer2.WriteLine("<Row>");
     for (num = 0; num != columnCount; num++)
     {
  writer2.Write("<Cell ss:StyleID=/"s21/"><Data ss:Type=/"String/">");
  writer2.Write(row[num].ToString());
  writer2.WriteLine("</Data></Cell>");
     }
     writer2.WriteLine("</Row>");
}
writer2.WriteLine(" </Table>");
writer2.WriteLine(" <WorksheetOptions xmlns=/"urn:schemas-microsoft-com:office:excel/">");
writer2.WriteLine("   <Selected/>");
writer2.WriteLine("   <Panes>");
writer2.WriteLine("    <Pane>");
writer2.WriteLine("     <Number>3</Number>");
writer2.WriteLine("     <ActiveRow>1</ActiveRow>");
writer2.WriteLine("    </Pane>");
writer2.WriteLine("   </Panes>");
writer2.WriteLine("   <ProtectObjects>False</ProtectObjects>");
writer2.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
writer2.WriteLine(" </WorksheetOptions>");
writer2.WriteLine(" </Worksheet>");
writer2.WriteLine(" <Worksheet ss:Name=/"Sheet2/">");
writer2.WriteLine(" <WorksheetOptions xmlns=/"urn:schemas-microsoft-com:office:excel/">");
writer2.WriteLine("   <ProtectObjects>False</ProtectObjects>");
writer2.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
writer2.WriteLine(" </WorksheetOptions>");
writer2.WriteLine(" </Worksheet>");
writer2.WriteLine(" <Worksheet ss:Name=/"Sheet3/">");
writer2.WriteLine(" <WorksheetOptions xmlns=/"urn:schemas-microsoft-com:office:excel/">");
writer2.WriteLine("   <ProtectObjects>False</ProtectObjects>");
writer2.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
writer2.WriteLine(" </WorksheetOptions>");
writer2.WriteLine(" </Worksheet>");
writer2.WriteLine("</Workbook>");
writer2 = null;
writer1.Close();
return true;
    }
    catch (Exception error)
    {
throw (error);
    }
}