当前位置: 代码迷 >> ASP.NET >> 一个dataSet有两个表,怎么把两个表导入excel的两个sheet中
  详细解决方案

一个dataSet有两个表,怎么把两个表导入excel的两个sheet中

热度:7252   发布时间:2013-02-25 00:00:00.0
一个dataSet有两个表,如何把两个表导入excel的两个sheet中?
asp.net

要求点击导入时 输入excel文件名,excel的两个sheet名称就是datatable的表名,

找了很多资料都搞不定

------解决方案--------------------------------------------------------
修正完整的版本
C# code
System.Data.DataSet ds = new System.Data.DataSet();System.Data.DataTable dataTable1 = new System.Data.DataTable("BlogUser");System.Data.DataRow dr;dataTable1.Columns.Add(new System.Data.DataColumn("UserId", typeof(System.Int32)));dataTable1.Columns.Add(new System.Data.DataColumn("UserName", typeof(System.String)));dataTable1.PrimaryKey = new System.Data.DataColumn[] { dataTable1.Columns["UserId"] };for (int i = 0; i < 8; i++){    dr = dataTable1.NewRow();    dr[0] = i;    dr[1] = "用户姓名【孟子E章】测试" + i.ToString();    dataTable1.Rows.Add(dr);}System.Data.DataTable dataTable2 = new System.Data.DataTable("BlogArticle");dataTable2.Columns.Add(new System.Data.DataColumn("ArticleId", typeof(System.Int32)));dataTable2.Columns.Add(new System.Data.DataColumn("Title", typeof(System.String)));dataTable2.Columns.Add(new System.Data.DataColumn("UserId", typeof(System.Int32)));dataTable2.PrimaryKey = new System.Data.DataColumn[] { dataTable1.Columns["ArticleId"] };Random rd = new Random();for (int i = 0; i < 20; i++){    dr = dataTable2.NewRow();    dr[0] = i;    dr[1] = "文章标题例子" + i.ToString();    dr[2] = rd.Next(0, 7);    dataTable2.Rows.Add(dr);}ds.Tables.Add(dataTable1);ds.Tables.Add(dataTable2);Response.ClearContent();Response.BufferOutput = true;Response.Charset = "utf-8";Response.ContentType = "application/ms-excel";Response.AddHeader("Content-Transfer-Encoding", "binary");Response.ContentEncoding = System.Text.Encoding.UTF8;String FileName = "孟宪会Excel表格测试";if (!String.IsNullOrEmpty(Request.UserAgent)){    // firefox 里面文件名无需编码。    if (!(Request.UserAgent.IndexOf("Firefox") > -1 && Request.UserAgent.IndexOf("Gecko") > -1))    {        FileName = Server.UrlEncode(FileName);    }}Response.AppendHeader("Content-Disposition", "attachment;filename=" + FileName + ".xls");Response.Write("<?xml version='1.0'?><?mso-application progid='Excel.Sheet'?>");Response.Write(@"<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel'xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:html='http://www.w3.org/TR/REC-html40'>");Response.Write(@"<DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'>");Response.Write(@"<Author>孟宪会</Author><LastAuthor>孟子E章</LastAuthor><Created>2010-09-08T14:07:11Z</Created><Company>mxh</Company><Version>1990</Version>");Response.Write("</DocumentProperties>");Response.Write(@"<Styles><Style ss:ID='Default' ss:Name='Normal'><Alignment ss:Vertical='Center'/><Borders/><Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/><Interior/><NumberFormat/><Protection/></Style>");//定义标题样式    Response.Write(@"<Style ss:ID='Header'><Borders><Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/><Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/><Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/><Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders><Font ss:FontName='宋体' x:CharSet='134' ss:Size='18' ss:Color='#FF0000' ss:Bold='1'/></Style>");//定义边框Response.Write(@"<Style ss:ID='border'><NumberFormat ss:Format='@'/><Borders><Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/><Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/><Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/><Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders></Style>");Response.Write("</Styles>");//SheetCount代表生成的 Sheet 数目。for (int i = 0; i < ds.Tables.Count; i++){    System.Data.DataTable dt = ds.Tables[i];    Response.Write("<Worksheet ss:Name='Sheet" + (i + 1) + "_" + ds.Tables[i].TableName + "'>");    Response.Write("<Table x:FullColumns='1' x:FullRows='1'>");    //输出标题    Response.Write("\r\n<Row ss:AutoFitHeight='1'>");    for (int j = 0; j < dt.Columns.Count; j++)    {        Response.Write("<Cell ss:StyleID='Header'><Data ss:Type='String'>" + dt.Columns[j].ColumnName + "</Data></Cell>");    }    Response.Write("\r\n</Row>");    for (int j = 0; j < dt.Rows.Count; j++)    {        Response.Write("<Row>");        for (int c = 0; c < dt.Columns.Count; c++)        {            //对于数字,采用Number数字类型            if (c > 1)            {                Response.Write("<Cell ss:StyleID='border'><Data ss:Type='Number'>" + dt.Rows[j][c].ToString() + "</Data></Cell>");            }            else            {                Response.Write("<Cell ss:StyleID='border'><Data ss:Type='String'>" + dt.Rows[j][c].ToString() + "</Data></Cell>");            }        }        Response.Write("</Row>");    }    Response.Write("</Table>");    Response.Write("</Worksheet>");}Response.Write("</Workbook>");Response.End();}
  相关解决方案