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

(一). 概要

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

(二).运行效果图示例

      1. 要转换数据的XML文件

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

(三). 代码

  1 try
  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(" "1.0/"?>");
 18         writer.WriteLine(" "Excel.Sheet/"?>");
 19         writer.WriteLine(" "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("  "urn:schemas-microsoft-com:office:office/">");
 25         writer.WriteLine("   Automated Report Generator Example ");
 26         writer.WriteLine(string.Format("   {0}T{1}Z ", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
 27         writer.WriteLine("   Your Company Here ");
 28         writer.WriteLine("   11.6408 ");
 29         writer.WriteLine(" ");
 30         writer.WriteLine("  "urn:schemas-microsoft-com:office:excel/">");
 31         writer.WriteLine("   8955 ");
 32         writer.WriteLine("   11355 ");
 33         writer.WriteLine("   480 ");
 34         writer.WriteLine("   15 ");
 35         writer.WriteLine("   False ");
 36         writer.WriteLine("   False ");
 37         writer.WriteLine(" ");
 38         writer.WriteLine("  ");
 39         writer.WriteLine("   "Default/" ss:Name=/"Normal/">");
 40         writer.WriteLine("    "Bottom/"/>");
 41         writer.WriteLine("    ");
 42         writer.WriteLine("   ");
 43         writer.WriteLine("    ");
 44         writer.WriteLine("    ");
 45         writer.WriteLine("  ");
 46         writer.WriteLine("   "s21/">");
 47         writer.WriteLine("    "Bottom/" ss:WrapText=/"1/"/>");
 48         writer.WriteLine("  ");
 49         writer.WriteLine(" ");
 50         writer.WriteLine("  "MyReport/">");
 51         writer.WriteLine(string.Format("   "{0}/" ss:ExpandedRowCount=/"{1}/" x:FullColumns=/"1/"", cols.ToString(), rows.ToString()));
 52         writer.WriteLine("   x:FullRows=/"1/">");
 53
 54         //生成标题行
 55         writer.WriteLine(" ");
 56         foreach(DataColumn eachCloumn in dsBook.Tables[0].Columns)
 57         {           
 58            writer.Write(" "s21/"> "String/">");          
 59            writer.Write(eachCloumn.ColumnName.ToString());
 60            writer.WriteLine("");            
 61         }
 62         writer.WriteLine("");
 63
 64         //生成数据记录行
 65         foreach (DataRow eachRow in dsBook.Tables[0].Rows)
 66         {
 67            writer.WriteLine(" ");
 68            for(int currentRow = 0; currentRow != cols; currentRow++)
 69            {
 70               writer.Write(" "s21/"> "String/">");
 71               writer.Write(eachRow[currentRow].ToString());
 72               writer.WriteLine("");
 73            }
 74            writer.WriteLine("");
 75         }
 76         writer.WriteLine("  ");
 77         writer.WriteLine("   "urn:schemas-microsoft-com:office:excel/">");
 78         writer.WriteLine("    ");
 79         writer.WriteLine("    ");
 80         writer.WriteLine("     ");
 81         writer.WriteLine("      3 ");
 82         writer.WriteLine("      1 ");
 83         writer.WriteLine("    ");
 84         writer.WriteLine("   ");
 85         writer.WriteLine("    False ");
 86         writer.WriteLine("    False ");
 87         writer.WriteLine("  ");
 88         writer.WriteLine(" ");
 89         writer.WriteLine("  "Sheet2/">");
 90         writer.WriteLine("   "urn:schemas-microsoft-com:office:excel/">");
 91         writer.WriteLine("    False ");
 92         writer.WriteLine("    False ");
 93         writer.WriteLine("  ");
 94         writer.WriteLine(" ");
 95         writer.WriteLine("  "Sheet3/">");
 96         writer.WriteLine("   "urn:schemas-microsoft-com:office:excel/">");
 97         writer.WriteLine("    False ");
 98         writer.WriteLine("    False ");
 99         writer.WriteLine("  ");
100         writer.WriteLine(" ");
101         writer.WriteLine("");         
102         writer.Close();
103         Response.Write(" "javascript/">" + "alert('" + "转换成功! 转换后的Excel文件名为: " + ExcelFileName + "')" +"");
104      }
105      catch (Exception ex)
106      {
107         Response.Write(" "javascript/">" + "alert('" + "操作失败! 出错信息: " + ex.Message + "')" + "");
108      }

(四). 示例代码下载

         http://www.cnblogs.com/Files/ChengKing/XMLChangeToExcel.rar

Trackback: http://tb.blog.youkuaiyun.com/TrackBack.aspx?PostId=1045853