自定义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\"?>\r\n"); 36 excelstr.Append("<?mso-application progid=\"Excel.Sheet\"?>\r\n"); 37 excelstr.Append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n"); 38 excelstr.Append("xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n"); 39 excelstr.Append("xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\r\n"); 40 excelstr.Append("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n"); 41 excelstr.Append("xmlns:html=\"http://www.w3.org/TR/REC-html40\">\r\n"); 42 excelstr.Append("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">\r\n"); 43 excelstr.Append("<Created>2006-09-16T00:00:00Z</Created>\r\n"); 44 excelstr.Append("<LastSaved>2015-07-07T05:50:29Z</LastSaved>\r\n"); 45 excelstr.Append("<Version>14.00</Version>\r\n"); 46 excelstr.Append("</DocumentProperties>\r\n"); 47 excelstr.Append("<OfficeDocumentSettings xmlns=\"urn:schemas-microsoft-com:office:office\">\r\n"); 48 excelstr.Append("<AllowPNG/>\r\n"); 49 excelstr.Append("<RemovePersonalInformation/>\r\n"); 50 excelstr.Append("</OfficeDocumentSettings>\r\n"); 51 excelstr.Append("<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">\r\n"); 52 excelstr.Append("<WindowHeight>8010</WindowHeight>\r\n"); 53 excelstr.Append("<WindowWidth>14805</WindowWidth>\r\n"); 54 excelstr.Append("<WindowTopX>240</WindowTopX>\r\n"); 55 excelstr.Append("<WindowTopY>105</WindowTopY>\r\n"); 56 excelstr.Append("<ProtectStructure>False</ProtectStructure>\r\n"); 57 excelstr.Append("<ProtectWindows>False</ProtectWindows>\r\n"); 58 excelstr.Append("</ExcelWorkbook>\r\n"); 59 excelstr.Append("<Styles>\r\n"); 60 excelstr.Append("<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n"); 61 excelstr.Append("<Alignment ss:Vertical=\"Bottom\"/>\r\n"); 62 excelstr.Append("<Borders/>\r\n"); 63 excelstr.Append("<Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"11\" ss:Color=\"#000000\"/>\r\n"); 64 excelstr.Append("<Interior/>\r\n"); 65 excelstr.Append("<NumberFormat/>\r\n"); 66 excelstr.Append("<Protection/>\r\n"); 67 excelstr.Append("</Style>\r\n"); 68 excelstr.Append("<Style ss:ID=\"s64\">\r\n"); 69 excelstr.Append("<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>\r\n"); 70 excelstr.Append("<Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"11\" ss:Color=\"#000000\"\r\n"); 71 excelstr.Append("ss:Bold=\"1\"/>\r\n"); 72 excelstr.Append("</Style>\r\n"); 73 excelstr.Append("</Styles>\r\n"); 74 foreach (WorkSheet item in sheets) 75 { 76 excelstr.Append(item.WriteSheet()); 77 } 78 excelstr.Append("</Workbook>\r\n"); 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 }
（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}\">\r\n", title)); 33 sb.Append("<Table ss:ExpandedColumnCount=\"200\" ss:ExpandedRowCount=\"65535\" x:FullColumns=\"1\"\r\n"); 34 sb.Append("x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"13.5\">\r\n"); 35 36 foreach (Row _row in rowcollection) 37 { 38 sb.Append(_row.WriteRow()); 39 } 40 sb.Append("</Table>\r\n"); 41 sb.Append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">\r\n"); 42 sb.Append("<PageSetup>\r\n"); 43 sb.Append("<Header x:Margin=\"0.3\"/>\r\n"); 44 sb.Append("<Footer x:Margin=\"0.3\"/>\r\n"); 45 sb.Append("<PageMargins x:Bottom=\"0.75\" x:Left=\"0.7\" x:Right=\"0.7\" x:Top=\"0.75\"/>\r\n"); 46 sb.Append("</PageSetup>\r\n"); 47 sb.Append("<Print>\r\n"); 48 sb.Append("<ValidPrinterInfo/>\r\n"); 49 sb.Append("<PaperSizeIndex>9</PaperSizeIndex>\r\n"); 50 sb.Append("<HorizontalResolution>600</HorizontalResolution>\r\n"); 51 sb.Append("<VerticalResolution>600</VerticalResolution>\r\n"); 52 sb.Append("</Print>\r\n"); 53 sb.Append("<Selected/>\r\n"); 54 sb.Append("<ProtectObjects>False</ProtectObjects>\r\n"); 55 sb.Append("<ProtectScenarios>False</ProtectScenarios>\r\n"); 56 sb.Append("</WorksheetOptions>\r\n"); 57 sb.Append("</Worksheet>\r\n"); 58 return sb.ToString(); 59 } 60 61 } 62 }
（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>\r\n"); 65 foreach (string item in headers) 66 { 67 sb.Append(string.Format("<Cell ss:StyleID=\"s64\"><Data ss:Type=\"String\">{0}</Data></Cell>\r\n", item)); 68 } 69 sb.Append("</Row>\r\n"); 70 } 71 if (cells != null && cells.Count > 0) 72 { 73 sb.Append("<Row>\r\n"); 74 foreach (string item in cells) 75 { 76 sb.Append(string.Format("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>\r\n", item)); 77 } 78 sb.Append("</Row>\r\n"); 79 } 80 return sb.ToString(); 81 } 82 83 } 84 85 86 }
（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 }
（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 }
