Asp.net 2.0 装载XML文件中数据到Excel文件中(示例代码下载)

(一). 概要

         从读取XML文件数据,  生成Excel文件.

(二).运行效果图示例

      1. 要转换数据的XML文件

      2. 生成的Excel文件显示效果

(三). 代码

  1try
  2      {
  3         //要转换的XML文件
  4         string XMLFileName = Path.Combine(Request.PhysicalApplicationPath, "book.xml");
  5         DataSet dsBook = new DataSet();
  6         dsBook.ReadXml( XMLFileName );         
  7         int rows = dsBook.Tables[0].Rows.Count + 1;
  8         int cols = dsBook.Tables[0].Columns.Count;         
  9         
 10         //将要生成的Excel文件
 11         string ExcelFileName = Path.Combine(Request.PhysicalApplicationPath, "book.xls");
 12         if (File.Exists(ExcelFileName))
 13         {
 14             File.Delete(ExcelFileName);
 15         }
 16         StreamWriter writer = new StreamWriter(ExcelFileName, false);         
 17         writer.WriteLine("<?xml version=\"1.0\"?>");
 18         writer.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
 19         writer.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
 20         writer.WriteLine(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
 21         writer.WriteLine(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
 22         writer.WriteLine(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
 23         writer.WriteLine(" xmlns:html=\"http://www.w3.org/TR/REC-html40/\">");
 24         writer.WriteLine(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
 25         writer.WriteLine("  <Author>Automated Report Generator Example</Author>");
 26         writer.WriteLine(string.Format("  <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
 27         writer.WriteLine("  <Company>Your Company Here</Company>");
 28         writer.WriteLine("  <Version>11.6408</Version>");
 29         writer.WriteLine(" </DocumentProperties>");
 30         writer.WriteLine(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
 31         writer.WriteLine("  <WindowHeight>8955</WindowHeight>");
 32         writer.WriteLine("  <WindowWidth>11355</WindowWidth>");
 33         writer.WriteLine("  <WindowTopX>480</WindowTopX>");
 34         writer.WriteLine("  <WindowTopY>15</WindowTopY>");
 35         writer.WriteLine("  <ProtectStructure>False</ProtectStructure>");
 36         writer.WriteLine("  <ProtectWindows>False</ProtectWindows>");
 37         writer.WriteLine(" </ExcelWorkbook>");
 38         writer.WriteLine(" <Styles>");
 39         writer.WriteLine("  <Style ss:ID=\"Default\" ss:Name=\"Normal\">");
 40         writer.WriteLine("   <Alignment ss:Vertical=\"Bottom\"/>");
 41         writer.WriteLine("   <Borders/>");
 42         writer.WriteLine("   <Font/>");
 43         writer.WriteLine("   <Interior/>");
 44         writer.WriteLine("   <Protection/>");
 45         writer.WriteLine("  </Style>");
 46         writer.WriteLine("  <Style ss:ID=\"s21\">");
 47         writer.WriteLine("   <Alignment ss:Vertical=\"Bottom\" ss:WrapText=\"1\"/>");
 48         writer.WriteLine("  </Style>");
 49         writer.WriteLine(" </Styles>");
 50         writer.WriteLine(" <Worksheet ss:Name=\"MyReport\">");
 51         writer.WriteLine(string.Format("  <Table ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\" x:FullColumns=\"1\"", cols.ToString(), rows.ToString()));
 52         writer.WriteLine("   x:FullRows=\"1\">");
 53
 54         //生成标题行
 55         writer.WriteLine("<Row>");
 56         foreach(DataColumn eachCloumn in dsBook.Tables[0].Columns)
 57         {           
 58            writer.Write("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">");          
 59            writer.Write(eachCloumn.ColumnName.ToString());
 60            writer.WriteLine("</Data></Cell>");            
 61         }
 62         writer.WriteLine("</Row>");
 63
 64         //生成数据记录行
 65         foreach (DataRow eachRow in dsBook.Tables[0].Rows)
 66         {
 67            writer.WriteLine("<Row>");
 68            for(int currentRow = 0; currentRow != cols; currentRow++)
 69            {
 70               writer.Write("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">");
 71               writer.Write(eachRow[currentRow].ToString());
 72               writer.WriteLine("</Data></Cell>");
 73            }
 74            writer.WriteLine("</Row>");
 75         }
 76         writer.WriteLine("  </Table>");
 77         writer.WriteLine("  <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
 78         writer.WriteLine("   <Selected/>");
 79         writer.WriteLine("   <Panes>");
 80         writer.WriteLine("    <Pane>");
 81         writer.WriteLine("     <Number>3</Number>");
 82         writer.WriteLine("     <ActiveRow>1</ActiveRow>");
 83         writer.WriteLine("    </Pane>");
 84         writer.WriteLine("   </Panes>");
 85         writer.WriteLine("   <ProtectObjects>False</ProtectObjects>");
 86         writer.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
 87         writer.WriteLine("  </WorksheetOptions>");
 88         writer.WriteLine(" </Worksheet>");
 89         writer.WriteLine(" <Worksheet ss:Name=\"Sheet2\">");
 90         writer.WriteLine("  <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
 91         writer.WriteLine("   <ProtectObjects>False</ProtectObjects>");
 92         writer.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
 93         writer.WriteLine("  </WorksheetOptions>");
 94         writer.WriteLine(" </Worksheet>");
 95         writer.WriteLine(" <Worksheet ss:Name=\"Sheet3\">");
 96         writer.WriteLine("  <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
 97         writer.WriteLine("   <ProtectObjects>False</ProtectObjects>");
 98         writer.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
 99         writer.WriteLine("  </WorksheetOptions>");
100         writer.WriteLine(" </Worksheet>");
101         writer.WriteLine("</Workbook>");         
102         writer.Close();
103         Response.Write("<script language=\"javascript\">" + "alert('" + "转换成功! 转换后的Excel文件名为: " + ExcelFileName + "')" +"</script>");
104      }
105      catch (Exception ex)
106      {
107         Response.Write("<script language=\"javascript\">" + "alert('" + "操作失败! 出错信息: " + ex.Message + "')" + "</script>");
108      }

(四). 示例代码下载

         https://files.cnblogs.com/ChengKing/XMLChangeToExcel.rar