正文

自定义Excel导出简易组件

IP云
IP云V管理员/3 阅读
09/16
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

1.组件原理

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

2.组件实现

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

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

namespace MyExcel
{
public class WorkBook
{
private StringBuilder excelstr;
private List<WorkSheet> sheets;
public WorkBook()
{
this.excelstr = new StringBuilder();
this.sheets = new List<WorkSheet>();
}
public WorkSheet CreateSheet(string title)
{
if (sheets == null)
{
throw new Exception("只有先构建WorkBook对象后才能构建WorkSheet对象！");
}
foreach (WorkSheet sht in sheets)
{
if (sht.Title.ToLower() == title.ToLower())
{
throw new Exception("已经有名称为：" + title + " 的Sheet");
}
}
WorkSheet wsheeet = new WorkSheet(title);
this.sheets.Add(wsheeet);
return wsheeet;
}
private string WriteWorkBook()
{
if (sheets == null || sheets.Count < )
{
throw new Exception("只有先构建WorkBook对象后才能构建行对象！");
}
excelstr.Append("<?xml version=\"1.0\"?>\r\n");
excelstr.Append("<?mso-application progid=\"Excel.Sheet\"?>\r\n");
excelstr.Append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n");
excelstr.Append("xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n");
excelstr.Append("xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\r\n");
excelstr.Append("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n");
excelstr.Append("xmlns:html=\"http://www.w3.org/TR/REC-html40\">\r\n");
excelstr.Append("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">\r\n");
excelstr.Append("<Created>2006-09-16T00:00:00Z</Created>\r\n");
excelstr.Append("<LastSaved>2015-07-07T05:50:29Z</LastSaved>\r\n");
excelstr.Append("<Version>14.00</Version>\r\n");
excelstr.Append("</DocumentProperties>\r\n");
excelstr.Append("<OfficeDocumentSettings xmlns=\"urn:schemas-microsoft-com:office:office\">\r\n");
excelstr.Append("<AllowPNG/>\r\n");
excelstr.Append("<RemovePersonalInformation/>\r\n");
excelstr.Append("</OfficeDocumentSettings>\r\n");
excelstr.Append("<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">\r\n");
excelstr.Append("<WindowHeight>8010</WindowHeight>\r\n");
excelstr.Append("<WindowWidth>14805</WindowWidth>\r\n");
excelstr.Append("<WindowTopX>240</WindowTopX>\r\n");
excelstr.Append("<WindowTopY>105</WindowTopY>\r\n");
excelstr.Append("<ProtectStructure>False</ProtectStructure>\r\n");
excelstr.Append("<ProtectWindows>False</ProtectWindows>\r\n");
excelstr.Append("</ExcelWorkbook>\r\n");
excelstr.Append("<Styles>\r\n");
excelstr.Append("<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n");
excelstr.Append("<Alignment ss:Vertical=\"Bottom\"/>\r\n");
excelstr.Append("<Borders/>\r\n");
excelstr.Append("<Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"11\" ss:Color=\"#000000\"/>\r\n");
excelstr.Append("<Interior/>\r\n");
excelstr.Append("<NumberFormat/>\r\n");
excelstr.Append("<Protection/>\r\n");
excelstr.Append("</Style>\r\n");
excelstr.Append("<Style ss:ID=\"s64\">\r\n");
excelstr.Append("<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>\r\n");
excelstr.Append("<Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"11\" ss:Color=\"#000000\"\r\n");
excelstr.Append("ss:Bold=\"1\"/>\r\n");
excelstr.Append("</Style>\r\n");
excelstr.Append("</Styles>\r\n");
foreach (WorkSheet item in sheets)
{
excelstr.Append(item.WriteSheet());
}
excelstr.Append("</Workbook>\r\n");
return excelstr.ToString();
}
/// <summary>
/// Write Data To Stream.
/// </summary>
/// <param name="ms"></param>
public void WriteStream(Stream ms)
{
try
{
string msg = WriteWorkBook();
byte[] buffer = System.Text.Encoding.UTF8.GetBytes(msg);
ms.Write(buffer, , buffer.Length); }
catch (Exception)
{
throw new Exception("写入流错误");
} }
}
}

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

namespace MyExcel
{
public class WorkSheet
{
private List<Row> rowcollection;
private string title = "";
internal WorkSheet(string title)
{
this.title = title;
this.rowcollection = new List<Row>();
}
internal string Title
{ get { return title; } }
public Row CreateRow()
{
Row _row = new Row();
if (this.rowcollection == null)
{
throw new Exception("只有先构建Sheet对象后才能构建行对象！");
}
rowcollection.Add(_row);
return _row;
} internal string WriteSheet()
{
if (rowcollection == null || rowcollection.Count < )
{
throw new Exception("未添加行(Row)对象");
}
StringBuilder sb = new StringBuilder();
sb.Append(string.Format("<Worksheet ss:Name=\"{0}\">\r\n", title));
sb.Append("<Table ss:ExpandedColumnCount=\"200\" ss:ExpandedRowCount=\"65535\" x:FullColumns=\"1\"\r\n");
sb.Append("x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"13.5\">\r\n"); foreach (Row _row in rowcollection)
{
sb.Append(_row.WriteRow());
}
sb.Append("</Table>\r\n");
sb.Append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">\r\n");
sb.Append("<PageSetup>\r\n");
sb.Append("<Header x:Margin=\"0.3\"/>\r\n");
sb.Append("<Footer x:Margin=\"0.3\"/>\r\n");
sb.Append("<PageMargins x:Bottom=\"0.75\" x:Left=\"0.7\" x:Right=\"0.7\" x:Top=\"0.75\"/>\r\n");
sb.Append("</PageSetup>\r\n");
sb.Append("<Print>\r\n");
sb.Append("<ValidPrinterInfo/>\r\n");
sb.Append("<PaperSizeIndex>9</PaperSizeIndex>\r\n");
sb.Append("<HorizontalResolution>600</HorizontalResolution>\r\n");
sb.Append("<VerticalResolution>600</VerticalResolution>\r\n");
sb.Append("</Print>\r\n");
sb.Append("<Selected/>\r\n");
sb.Append("<ProtectObjects>False</ProtectObjects>\r\n");
sb.Append("<ProtectScenarios>False</ProtectScenarios>\r\n");
sb.Append("</WorksheetOptions>\r\n");
sb.Append("</Worksheet>\r\n");
return sb.ToString();
} }
}

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

namespace MyExcel
{
public class Row
{
private List<string> cells;
private List<string> headers;
internal Row()
{
this.cells = new List<string>();
this.headers = new List<string>();
}
/// <summary>
/// 添加列
/// </summary>
/// <param name="value"></param>
public void AddCell(string value)
{
cells.Add(value);
}
/// <summary>
/// 添加标题
/// </summary>
/// <param name="title"></param>
public void AddHeader(string title)
{
this.headers.Add(title);
}
internal string WriteRow()
{
if (cells == null)
{
if (headers == null)
{
throw new Exception("无单元格,请创建单元格");
}
else
{
if (headers.Count < )
{
throw new Exception("无单元格,请创建单元格");
}
}
}
else
{
if (headers == null)
{
if (cells.Count < )
{
throw new Exception("无单元格,请创建单元格");
}
}
else
{
if (headers.Count < &&cells.Count<)
{
throw new Exception("无单元格,请创建单元格");
}
}
}
StringBuilder sb = new StringBuilder();
if (headers!=null&&headers.Count>)
{
sb.Append("<Row>\r\n");
foreach (string item in headers)
{
sb.Append(string.Format("<Cell ss:StyleID=\"s64\"><Data ss:Type=\"String\">{0}</Data></Cell>\r\n", item));
}
sb.Append("</Row>\r\n");
}
if (cells != null && cells.Count > )
{
sb.Append("<Row>\r\n");
foreach (string item in cells)
{
sb.Append(string.Format("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>\r\n", item));
}
sb.Append("</Row>\r\n");
}
return sb.ToString();
} } }

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

3.组件使用

添加引用，"MyExcel.dll"

（1）web程序使用

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MyExcel;
using System.IO; public partial class Default1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Response.Clear();
Response.ClearHeaders();
Response.Buffer = true;
WorkBook wb = new WorkBook();//创建工作表
for (int j = ; j < ; j++)
{
WorkSheet ws = wb.CreateSheet("sheet"+(j+));//创建工作簿
Row r = ws.CreateRow();//创建ws的行
r.AddHeader("Name");//添加标题列
r.AddHeader("Sex");//添加标题列
r.AddHeader("Address");//添加标题列 for (int i = ; i < ; i++)
{
r = ws.CreateRow();//又创建一新行
r.AddCell("name" + (i + ));//添加列
r.AddCell("sex" + (i + ));//添加列
r.AddCell("address" + (i + ));//添加列
}
} MemoryStream ms = new MemoryStream();
wb.WriteStream(ms);//将xml数据写入内存流
byte[] buffer = ms.ToArray();
ms.Close();
//文件下载代码
Response.AddHeader("Content-Disposition", "attachment;filename=aa.xls");
Response.AddHeader("Contnet-Length", buffer.Length.ToString());
Response.ContentType = "application/ms-excel;charset=utf-8";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("shift-jis");
Response.BinaryWrite(buffer);
}
}

（2）控制台程序

namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
//构造工作表对象
WorkBook wb = new WorkBook();
//创建当前工作表的工作簿对象
WorkSheet ws = wb.CreateSheet("sheet1");
//创建当前工作簿的行对象
Row r = ws.CreateRow();
//当前行添加标题列
r.AddHeader("Name");
r.AddHeader("Sex");
r.AddHeader("Address");
for (int i = ; i < ; i++)
{
//创建新行
r = ws.CreateRow();
//添加新行的列
r.AddCell("name" + (i + ));
r.AddCell("sex" + (i + ));
r.AddCell("address" + (i + ));
}
MemoryStream ms = new MemoryStream();
//将数据写入内存流
wb.WriteStream(ms);
byte[] buffer = ms.ToArray();
FileStream fs = new FileStream(@"c:/aaa.xls", FileMode.Create, FileAccess.ReadWrite);
ms.Close();
fs.Write(buffer, , buffer.Length);
fs.Flush();
fs.Close();
Console.Read(); }
}
}