分页导出Excel DataSet to EXcel
来源:互联网 发布:九大排序算法 编辑:程序博客网 时间:2024/05/21 06:03
/// <summary>
/// 使用XML方式导出Excel
/// </summary>
/// <param name="dsBook">需要导出的数据集</param>
/// <param name="path">Excel的存放路径</param>
/// <returns>EXCEL文件所在的地址</returns>
private String DataSetToExcel_XML(System.Data.DataSet dsBook, string path)
{
string ExcelFileName = path + "" + "test" + ".xls";//Excel的所在位置
if (File.Exists(ExcelFileName))
{
File.Delete(ExcelFileName);
}
StreamWriter writer = new StreamWriter(ExcelFileName, false);
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 (System.Data.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;
}
System.Data.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;
}
- 分页导出Excel DataSet to EXcel
- Dataset导出到excel
- DataGrid、DataSet导出Excel
- DataSet导出EXCEL
- DataSet导出为Excel
- DataSet导出Excel
- dataset 导出为 excel
- 通过DataSet导出Excel
- DataSet导出为Excel
- DataSet导出到Excel
- DataSet导出Excel
- 【转】DataSet导出EXCEL
- 通过DataSet导出Excel .
- DataSet导出EXcel
- dataset导出EXCEL文件
- dataset 导出到 excel
- dataset to excel
- DataSet To Excel&Chart
- 玩转tokyotyrant
- hdu_1.3.1_FatMouse' Trade,hdu_1.3.2_Tian Ji -- The Horse Racing
- LevelDB参考资料
- What is for dinner?
- Nginx+PHP
- 分页导出Excel DataSet to EXcel
- 记录oracle 10g 手工创建数据库
- SqlServer复制功能使用中遇到的问题点
- TOKYO TYRANT (TTSERVER)AND REDIS比较
- MySql中的InnoDB与MyISAM类型解释
- Linux中listen()系统调用的backlog参数分析
- delphi RTTI 2
- VB.NET操作Access数据库
- 快速开发一个PHP扩展