以xml格式将dataset导入到excel

using System;
using System.Data;
using System.Text;
using System.IO;
using System.Collections.Generic;

public class MyClass
{
    public static void Main()
    {
        //生成测试用数据集
        DataSet ds=CreateDataSet();
        //转为xml格式字符串
        string str=builderExcel(ds);
        //存为test.xls
        using(StreamWriter sw=new StreamWriter("C://Documents and Settings//Administrator//My Documents//test.xls",false,Encoding.Unicode)){
            sw.Write(str);
            sw.Flush();
            sw.Close();
        }
       
    }
   
    static string builderExcel(DataSet ds){
    //定义Workbook名字空间
    StringBuilder sb=new StringBuilder("<?xml version=/"1.0/"?>");
sb.Append("<?mso-application progid=/"Excel.Sheet/"?>");
sb.Append("<Workbook xmlns=/"urn:schemas-microsoft-com:office:spreadsheet/"");
sb.Append(" xmlns:o=/"urn:schemas-microsoft-com:office:office/"");
sb.Append(" xmlns:x=/"urn:schemas-microsoft-com:office:excel/"");
sb.Append(" xmlns:ss=/"urn:schemas-microsoft-com:office:spreadsheet/"");
sb.Append(" xmlns:html=/"http://www.w3.org/TR/REC-html40/">");
//文档信息
sb.Append("<DocumentProperties xmlns=/"urn:schemas-microsoft-com:office:office/">");
sb.Append("<Author>blackant QQ:17754266</Author>");
sb.AppendFormat("<Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-MM-dd"), DateTime.Now.ToString("HH:mm:SS"));
sb.Append("<Company>黑蚂蚁工作室</Company>");
sb.Append("<Version>11.6408</Version>");
sb.Append("</DocumentProperties>");
//定义Excel工作本
sb.Append("<ExcelWorkbook xmlns=/"urn:schemas-microsoft-com:office:excel/">");
sb.Append("<WindowHeight>8955</WindowHeight>");
sb.Append("<WindowWidth>11355</WindowWidth>");
sb.Append(" <WindowTopX>480</WindowTopX>");
sb.Append(" <WindowTopY>15</WindowTopY>");
sb.Append("<ProtectStructure>False</ProtectStructure>");
sb.Append("<ProtectWindows>False</ProtectWindows>");
sb.Append("</ExcelWorkbook>");
//依表导入
foreach(DataTable dt in ds.Tables){
DataView dv=dt.DefaultView;
sb.AppendFormat(" <Worksheet ss:Name=/"{0}/">",dt.TableName);
sb.AppendFormat("<Table ss:ExpandedColumnCount=/"{0}/" ss:ExpandedRowCount=/"{1}/"",
    dv.Table.Columns.Count,
    dv.Count+1);
sb.Append(" x:FullColumns=/"1/" x:FullRows=/"1/">");
//加注标头
sb.Append("<Row>");
for(int i=0;i<dv.Table.Columns.Count;i++){
sb.AppendFormat("<Cell><Data ss:Type=/"String/">{0}</Data></Cell>",dv.Table.Columns[i].ColumnName);
}
sb.Append("</Row>");
//注入数据
foreach(DataRowView drv in dv){
sb.Append("<Row>");
for(int i=0;i<dv.Table.Columns.Count;i++){
sb.AppendFormat("<Cell><Data ss:Type=/"String/">{0}</Data></Cell>",drv[i]);
}
sb.Append("</Row>");
}
sb.Append("</Table>");
//其它附加信息指示
sb.Append(" <WorksheetOptions xmlns=/"urn:schemas-microsoft-com:office:excel/">");
sb.Append(" <Selected/>");
sb.Append(" <Panes>");
sb.Append(" <Pane>");
sb.Append(" <Number>3</Number>");
sb.Append(" <ActiveRow>1</ActiveRow>");
sb.Append(" </Pane>");
sb.Append(" </Panes>");
sb.Append(" <ProtectObjects>False</ProtectObjects>");
sb.Append(" <ProtectScenarios>False</ProtectScenarios>");
sb.Append(" </WorksheetOptions>");
sb.Append(" </Worksheet>");
}
sb.Append("</Workbook>");

return sb.ToString();
    }
   
    static DataSet CreateDataSet(){
//主表
DataTable master=new DataTable("product");
master.Columns.Add(new DataColumn("product_id",typeof(int)));
master.Columns.Add(new DataColumn("product_name",typeof(int)));
master.Columns.Add(new DataColumn("product_no",typeof(int)));
master.Columns.Add(new DataColumn("client_c",typeof(int)));
//从表
DataTable detail=new DataTable("product_pack ");
detail.Columns.Add(new DataColumn("product_no",typeof(int)));
detail.Columns.Add(new DataColumn("client_c",typeof(int)));
for(int i=0;i<10;i++){
DataRow DRmaster=master.NewRow();
DRmaster[0]=i;
DRmaster[1]=i;
DRmaster[2]=i;
DRmaster[3]=i;
master.Rows.Add(DRmaster);
for(int j=0;j<3;j++){
DataRow DRdetail=detail.NewRow();
DRdetail[0]=i;
DRdetail[1]=i;
detail.Rows.Add(DRdetail);
}
}
//返回数据集
DataSet ds=new DataSet();
ds.Tables.Add(master);
ds.Tables.Add(detail);
return ds;

}
}