自定义Excel导出简易组件

1.组件原理

   excel的数据存储是以xml格式存储的，所以导出Excel文件可以通过生成XML来实现。当然XML必须符合一定的格式要求。

2.组件实现

（1）新建类库文件“MyExcel”

（2）添加类型“WorkBook”，这里指Excel的工作表。

  1 namespace MyExcel
  2 {
  3     public class WorkBook
  4     {
  5         private StringBuilder excelstr;
  6         private List<WorkSheet> sheets;
  7         public WorkBook()
  8         {
  9             this.excelstr = new StringBuilder();
 10             this.sheets = new List<WorkSheet>();
 11         }
 12         public WorkSheet CreateSheet(string title)
 13         {
 14             if (sheets == null)
 15             {
 16                 throw new Exception("只有先构建WorkBook对象后才能构建WorkSheet对象！");
 17             }
 18             foreach (WorkSheet sht in sheets)
 19             {
 20                 if (sht.Title.ToLower() == title.ToLower())
 21                 {
 22                     throw new Exception("已经有名称为：" + title + " 的Sheet");
 23                 }
 24             }
 25             WorkSheet wsheeet = new WorkSheet(title);
 26             this.sheets.Add(wsheeet);
 27             return wsheeet;
 28         }
 29         private string WriteWorkBook()
 30         {
 31             if (sheets == null || sheets.Count < 1)
 32             {
 33                 throw new Exception("只有先构建WorkBook对象后才能构建行对象！");
 34             }
 35             excelstr.Append("<?xml version="1.0"?>
");
 36             excelstr.Append("<?mso-application progid="Excel.Sheet"?>
");
 37             excelstr.Append("<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
");
 38             excelstr.Append("xmlns:o="urn:schemas-microsoft-com:office:office"
");
 39             excelstr.Append("xmlns:x="urn:schemas-microsoft-com:office:excel"
");
 40             excelstr.Append("xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
");
 41             excelstr.Append("xmlns:html="http://www.w3.org/TR/REC-html40">
");
 42             excelstr.Append("<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
");
 43             excelstr.Append("<Created>2006-09-16T00:00:00Z</Created>
");
 44             excelstr.Append("<LastSaved>2015-07-07T05:50:29Z</LastSaved>
");
 45             excelstr.Append("<Version>14.00</Version>
");
 46             excelstr.Append("</DocumentProperties>
");
 47             excelstr.Append("<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
");
 48             excelstr.Append("<AllowPNG/>
");
 49             excelstr.Append("<RemovePersonalInformation/>
");
 50             excelstr.Append("</OfficeDocumentSettings>
");
 51             excelstr.Append("<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
");
 52             excelstr.Append("<WindowHeight>8010</WindowHeight>
");
 53             excelstr.Append("<WindowWidth>14805</WindowWidth>
");
 54             excelstr.Append("<WindowTopX>240</WindowTopX>
");
 55             excelstr.Append("<WindowTopY>105</WindowTopY>
");
 56             excelstr.Append("<ProtectStructure>False</ProtectStructure>
");
 57             excelstr.Append("<ProtectWindows>False</ProtectWindows>
");
 58             excelstr.Append("</ExcelWorkbook>
");
 59             excelstr.Append("<Styles>
");
 60             excelstr.Append("<Style ss:ID="Default" ss:Name="Normal">
");
 61             excelstr.Append("<Alignment ss:Vertical="Bottom"/>
");
 62             excelstr.Append("<Borders/>
");
 63             excelstr.Append("<Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/>
");
 64             excelstr.Append("<Interior/>
");
 65             excelstr.Append("<NumberFormat/>
");
 66             excelstr.Append("<Protection/>
");
 67             excelstr.Append("</Style>
");
 68             excelstr.Append("<Style ss:ID="s64">
");
 69             excelstr.Append("<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
");
 70             excelstr.Append("<Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"
");
 71             excelstr.Append("ss:Bold="1"/>
");
 72             excelstr.Append("</Style>
");
 73             excelstr.Append("</Styles>
");
 74             foreach (WorkSheet item in sheets)
 75             {
 76                 excelstr.Append(item.WriteSheet());
 77             }
 78             excelstr.Append("</Workbook>
");
 79             return excelstr.ToString();
 80         }
 81         /// <summary>
 82         /// Write Data To Stream.
 83         /// </summary>
 84         /// <param name="ms"></param>
 85         public void WriteStream(Stream ms)
 86         {
 87             try
 88             {
 89                 string msg = WriteWorkBook();
 90                 byte[] buffer = System.Text.Encoding.UTF8.GetBytes(msg);
 91                 ms.Write(buffer, 0, buffer.Length);
 92 
 93             }
 94             catch (Exception)
 95             {
 96                 throw new Exception("写入流错误");
 97             }
 98 
 99         }
100     }
101 }
View Code

（3）添加类型“WorkSheet”，这里指Excel的工作簿。

 1 namespace MyExcel
 2 {
 3     public class WorkSheet
 4     {
 5         private List<Row> rowcollection;
 6         private string title = "";
 7         internal WorkSheet(string title)
 8         {
 9             this.title = title;
10             this.rowcollection = new List<Row>();
11         }
12         internal string Title
13         { get { return title; } }
14         public Row CreateRow()
15         {
16             Row _row = new Row();
17             if (this.rowcollection == null)
18             {
19                 throw new Exception("只有先构建Sheet对象后才能构建行对象！");
20             }
21             rowcollection.Add(_row);
22             return _row;
23         }
24 
25         internal string WriteSheet()
26         {
27             if (rowcollection == null || rowcollection.Count < 1)
28             {
29                 throw new Exception("未添加行(Row)对象");    
30             }
31             StringBuilder sb = new StringBuilder();
32             sb.Append(string.Format("<Worksheet ss:Name="{0}">
", title));
33             sb.Append("<Table ss:ExpandedColumnCount="200" ss:ExpandedRowCount="65535" x:FullColumns="1"
");
34             sb.Append("x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="13.5">
");
35 
36             foreach (Row _row in rowcollection)
37             {
38                 sb.Append(_row.WriteRow());
39             }
40             sb.Append("</Table>
");
41             sb.Append("<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
");
42             sb.Append("<PageSetup>
");
43             sb.Append("<Header x:Margin="0.3"/>
");
44             sb.Append("<Footer x:Margin="0.3"/>
");
45             sb.Append("<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
");
46             sb.Append("</PageSetup>
");
47             sb.Append("<Print>
");
48             sb.Append("<ValidPrinterInfo/>
");
49             sb.Append("<PaperSizeIndex>9</PaperSizeIndex>
");
50             sb.Append("<HorizontalResolution>600</HorizontalResolution>
");
51             sb.Append("<VerticalResolution>600</VerticalResolution>
");
52             sb.Append("</Print>
");
53             sb.Append("<Selected/>
");
54             sb.Append("<ProtectObjects>False</ProtectObjects>
");
55             sb.Append("<ProtectScenarios>False</ProtectScenarios>
");
56             sb.Append("</WorksheetOptions>
");
57             sb.Append("</Worksheet>
");
58             return sb.ToString();
59         }
60 
61     }
62 }
View Code

（4）添加类型“Row”，这里指Sheet中的行。

 1 namespace MyExcel
 2 {
 3     public class Row
 4     {
 5         private List<string> cells;
 6         private List<string> headers;
 7         internal Row()
 8         {
 9             this.cells = new List<string>();
10             this.headers = new List<string>();
11         }
12         /// <summary>
13         /// 添加列
14         /// </summary>
15         /// <param name="value"></param>
16         public void AddCell(string value)
17         {
18             cells.Add(value);
19         }
20         /// <summary>
21         /// 添加标题
22         /// </summary>
23         /// <param name="title"></param>
24         public void AddHeader(string title)
25         {
26             this.headers.Add(title);
27         }
28         internal string WriteRow()
29         {
30             if (cells == null)
31             {
32                 if (headers == null)
33                 {
34                     throw new Exception("无单元格,请创建单元格");
35                 }
36                 else
37                 {
38                     if (headers.Count < 1)
39                     {
40                         throw new Exception("无单元格,请创建单元格");
41                     }
42                 }
43             }
44             else
45             {
46                 if (headers == null)
47                 {
48                     if (cells.Count < 1)
49                     {
50                         throw new Exception("无单元格,请创建单元格");
51                     }
52                 }
53                 else
54                 {
55                     if (headers.Count < 1&&cells.Count<1)
56                     {
57                         throw new Exception("无单元格,请创建单元格");
58                     }
59                 }
60             }
61             StringBuilder sb = new StringBuilder();
62             if (headers!=null&&headers.Count>0)
63             {
64                 sb.Append("<Row>
");
65                 foreach (string item in headers)
66                 {
67                     sb.Append(string.Format("<Cell ss:StyleID="s64"><Data ss:Type="String">{0}</Data></Cell>
", item));
68                 }
69                 sb.Append("</Row>
");
70             }
71             if (cells != null && cells.Count > 0)
72             {
73                 sb.Append("<Row>
");
74                 foreach (string item in cells)
75                 {
76                     sb.Append(string.Format("<Cell><Data ss:Type="String">{0}</Data></Cell>
", item));
77                 }
78                 sb.Append("</Row>
");
79             }
80             return sb.ToString();
81         }
82 
83     }
84 
85 
86 }
View Code

（5）编译类库文件，生成“MyExcel.dll”文件。

3.组件使用

添加引用，"MyExcel.dll"

  （1）web程序使用

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Web;
 5 using System.Web.UI;
 6 using System.Web.UI.WebControls;
 7 using MyExcel;
 8 using System.IO;
 9 
10 
11 public partial class Default1 : System.Web.UI.Page
12 {
13     protected void Page_Load(object sender, EventArgs e)
14     {
15         Response.Clear();
16         Response.ClearHeaders();
17         Response.Buffer = true;
18         WorkBook wb = new WorkBook();//创建工作表
19         for (int j = 0; j < 5; j++)
20         {
21             WorkSheet ws = wb.CreateSheet("sheet"+(j+1));//创建工作簿
22             Row r = ws.CreateRow();//创建ws的行
23             r.AddHeader("Name");//添加标题列
24             r.AddHeader("Sex");//添加标题列
25             r.AddHeader("Address");//添加标题列
26 
27             for (int i = 0; i < 100; i++)
28             {
29                 r = ws.CreateRow();//又创建一新行
30                 r.AddCell("name" + (i + 1));//添加列
31                 r.AddCell("sex" + (i + 1));//添加列
32                 r.AddCell("address" + (i + 1));//添加列
33             }
34         }
35 
36         MemoryStream ms = new MemoryStream();
37         wb.WriteStream(ms);//将xml数据写入内存流
38         byte[] buffer = ms.ToArray();
39         ms.Close();
40         //文件下载代码
41         Response.AddHeader("Content-Disposition", "attachment;filename=aa.xls");
42         Response.AddHeader("Contnet-Length", buffer.Length.ToString());
43         Response.ContentType = "application/ms-excel;charset=utf-8";
44         Response.ContentEncoding = System.Text.Encoding.GetEncoding("shift-jis");
45         Response.BinaryWrite(buffer);
46     }
47 }
View Code

  （2）控制台程序

 1 namespace ConsoleApplication1
 2 {
 3     class Program
 4     {
 5         static void Main(string[] args)
 6         {
 7              //构造工作表对象
 8             WorkBook wb = new WorkBook();
 9             //创建当前工作表的工作簿对象
10             WorkSheet ws = wb.CreateSheet("sheet1");
11             //创建当前工作簿的行对象
12             Row r = ws.CreateRow();
13             //当前行添加标题列
14             r.AddHeader("Name");
15             r.AddHeader("Sex");
16             r.AddHeader("Address");
17             for (int i = 0; i < 100; i++)
18             {
19                //创建新行
20                 r = ws.CreateRow();
21                //添加新行的列
22                 r.AddCell("name" + (i + 1));
23                 r.AddCell("sex" + (i + 1));
24                 r.AddCell("address" + (i + 1));
25             }
26             MemoryStream ms = new MemoryStream();
27             //将数据写入内存流
28             wb.WriteStream(ms);
29             byte[] buffer = ms.ToArray();
30             FileStream fs = new FileStream(@"c:/aaa.xls", FileMode.Create, FileAccess.ReadWrite);
31             ms.Close();
32             fs.Write(buffer, 0, buffer.Length);
33             fs.Flush();
34             fs.Close();
35             Console.Read();
36 
37         }
38     }
39 }
View Code
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/net-study/p/4627346.html