/// <summary>
/// 导出为Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnExport_Click(object sender, EventArgs e)
{
ExportTest();
}
/// <summary>
/// 导出测试
/// </summary>
protected void ExportTest()
{
ExcelXML excel = new ExcelXML();
//excel.Sheets.Add("Sheet1");
//excel.Sheets.Add("Sheet5");
//excel.Sheets.Add("工作表1");
//excel.Sheets[0].Rows.AddTitle(new string[] { "编号", "部门", "日期" }, new ValueType[] { ValueType.Number, ValueType.String, ValueType.String });
//excel.Sheets[0].Frozen(0, 1);
//excel.Sheets[0].Rows.Add(new string[] { "1a", "财务部", "2009-1-5" });
//excel.Sheets[0].Rows.Add(new string[] { "02", "市场部", "2010-01-20 15:35" });
//excel.Sheets[0].Rows.Add(new string[] { "3", "销售部", "15:20:37" });
//excel.Sheets[0].Rows.Add(new string[] { "", "销售部", "15:20:37" });
//excel.Sheets[0].Rows.Add(new string[] { "0", "销售部", "15:20:37" });
//excel.Sheets[0].Rows.Add(new string[] { "1234567890", "销售部", "15:20:37" });
//excel.Sheets[0].Rows.Add(new string[] { "12345678901", "销售部", "15:20:37" });
//excel.Sheets[0].Rows.Add(new string[] { "123456789012", "销售部", "15:20:37" });
//excel.Sheets[1].Rows.Add(new string[] { "1", "2", "3" });
//excel.Sheets[1].Rows.Add(new string[] { "1", "测字测试", "3" });
//excel.Sheets[1].Frozen(1, 0);
//excel.Sheets[2].Rows.Add(new string[] { "1", "2", "3" });
//excel.Sheets[2].Rows.Add(new string[] { "1", "测字测试", "3" });
Sheet sheet = new Sheet("测试");
sheet.Rows.AddTitle(new string[] { "编号", "部门", "日期", "值" });
for (int i = 1; i < 100; i++)
{
sheet.Rows.Add(new string[] { i.ToString(), "部门_" + i % 3, DateTime.Today.AddDays(i % 5).ToString(), (i * 100).ToString() });
}
sheet.Frozen(2, 1);
excel.Sheets.Add(sheet);
excel.Export(DateTime.Now.ToString("yyyyMMdd-HHmmss_") + "Export");
}
===========================================================================================================
using System;
using System.Web;
using System.Collections;
using System.Collections.Generic;
using System.Text.RegularExpressions;
namespace ExportExcel
{
/// <summary>
/// 单元格值类型（日期作为文本处理）
/// </summary>
public enum ValueType
{
String = 0,
Number = 1
}
/// <summary>
/// 行
/// </summary>
public class Row
{
private static string _patten = @"^\d{1,15}$";
private static Regex _regex = new Regex(_patten);
private string[] _cellsValue;
public string[] CellsValue
{
get { return _cellsValue; }
set { _cellsValue = value; }
}
private string _rowText;
public string RowText
{
get { return _rowText; }
}
/// <summary>
/// 构造函数，生成一行
/// </summary>
/// <param name="values">各单元格值</param>
/// <param name="isAutoType">是否自动设置值类型</param>
public Row(string[] values, bool isAutoType)
{
if (values.Length > 256)
{
throw new Exception("Excel中不能超过256列！");
}
_cellsValue = values;
_rowText = "<Row>\n";
foreach (string cell in values)
{
ValueType vType = ValueType.String;
if (isAutoType)
{
if (_regex.Match(cell).Success)
{
vType = ValueType.Number;
}
}
_rowText += "<Cell><Data ss:Type=\"" + vType.ToString() + "\">" + cell + "</Data></Cell>\n";
}
_rowText += "</Row>\n";
}
/// <summary>
/// 构造函数，生成一行
/// </summary>
/// <param name="values">各单元格值</param>
/// <param name="valueTypes">各单元格值类型</param>
public Row(string[] values, ValueType[] valueTypes)
{
if (values.Length > 256 || valueTypes.Length > 256)
{
throw new Exception("Excel中不能超过256列！");
}
_cellsValue = values;
int i = 0;
_rowText = "<Row>\n";
foreach (string cell in values)
{
ValueType vType = ValueType.String;
if (i<valueTypes.Length)
{
vType = valueTypes[i];
if (vType == ValueType.Number)
{
if (!_regex.Match(cell).Success)
{
vType = ValueType.String;
}
}
}
_rowText += "<Cell><Data ss:Type=\"" + vType.ToString() + "\">" + cell + "</Data></Cell>\n";
i++;
}
_rowText += "</Row>\n";
}
}
/// <summary>
/// 行集合
/// </summary>
public class _rows : IEnumerable
{
private List<Row> _rowList = new List<Row>();
private bool _isAutoType = true;
public bool IsAutoType
{
get { return _isAutoType; }
set { _isAutoType = value; }
}
private ValueType[] _valueTypes;
public ValueType[] ValueTypes
{
get { return _valueTypes; }
set { _valueTypes = value; }
}
/// <summary>
/// 已使用行数
/// </summary>
public int Count
{
get { return _rowList.Count; }
}
/// <summary>
/// 添加标题行
/// </summary>
/// <param name="cells"></param>
/// <param name="valueTypes"></param>
/// <returns></returns>
public Row AddTitle(string[] cells)
{
Row row = new Row(cells, false);
_rowList.Add(row);
return row;
}
/// <summary>
/// 添加标题行并设置列格式
/// </summary>
/// <param name="cells"></param>
/// <param name="valueTypes"></param>
/// <returns></returns>
public Row AddTitle(string[] cells, ValueType[] valueTypes)
{
this._valueTypes = valueTypes;
Row row = new Row(cells, false);
_rowList.Add(row);
return row;
}
/// <summary>
/// 添加行
/// </summary>
/// <param name="cells"></param>
public Row Add(string[] cells)
{
if (this.Count >= 65536)
{
throw new Exception("已经达到了Excel允许的最大行！");
}
if (_valueTypes == null)
{
Row row = new Row(cells, _isAutoType);
_rowList.Add(row);
return row;
}
else
{
Row row = new Row(cells, _valueTypes);
_rowList.Add(row);
return row;
}
}
/// <summary>
/// 删除行
/// </summary>
/// <param name="index">行号</param>
public void Delete(int index)
{
if (index < 0 || index >= this.Count)
{
throw new Exception("下标超出范围！");
}
_rowList.RemoveAt(index);
}
/// <summary>
/// 获取行
/// </summary>
/// <param name="index">行号</param>
/// <returns></returns>
public Row this[int index]
{
get
{
if (index<0 || index >= this.Count)
{
throw new Exception("下标超出范围！");
}
return _rowList[index];
}
}
/// <summary>
/// 遍历行
/// </summary>
/// <returns></returns>
public IEnumerator GetEnumerator()
{
return _rowList.GetEnumerator();
}
}
/// <summary>
/// 工作表类
/// </summary>
public class Sheet
{
private string _sheetName;
public string SheetName
{
get { return _sheetName; }
set { _sheetName = value; }
}
private int _topRowBottomPane = 0;
public int TopRowBottomPane
{
get { return _topRowBottomPane; }
}
private int _leftColumnRightPane = 0;
public int LeftColumnRightPane
{
get { return _leftColumnRightPane; }
}
/// <summary>
/// 构造工作表
/// </summary>
/// <param name="sheetName">工作表名</param>
public Sheet(string sheetName)
{
this._sheetName = sheetName;
}
/// <summary>
/// 冻结窗格
/// </summary>
/// <param name="topRowBottomPane">冻结线上方行数</param>
/// <param name="leftColumnRightPane">冻结线左边行数</param>
public void Frozen(int topRowBottomPane, int leftColumnRightPane)
{
if (topRowBottomPane < 0 || topRowBottomPane >= 65536)
{
throw new Exception("索引超出范围！");
}
if (leftColumnRightPane < 0 || leftColumnRightPane >= 256)
{
throw new Exception("索引超出范围！");
}
this._topRowBottomPane = topRowBottomPane;
this._leftColumnRightPane = leftColumnRightPane;
}
public _rows Rows = new _rows();
}
/// <summary>
/// 工作表集合
/// </summary>
public class _sheets: IEnumerable
{
private List<Sheet> _sheetList = new List<Sheet>();
/// <summary>
/// 工作表数量
/// </summary>
public int Count
{
get { return _sheetList.Count; }
}
/// <summary>
/// 添加工作表
/// </summary>
/// <param name="sheetName">工作表名</param>
/// <returns>工作表对象</returns>
public Sheet Add(string sheetName)
{
foreach (Sheet sht in _sheetList)
{
if (sht.SheetName == sheetName)
{
throw new Exception("同一工作簿中工作表名不能相同！");
}
}
Sheet sheet = new Sheet(sheetName);
_sheetList.Add(sheet);
return sheet;
}
/// <summary>
/// 添加工作表
/// </summary>
/// <param name="sheet">工作表对象</param>
/// <returns>工作表对象</returns>
public Sheet Add(Sheet sheet)
{
foreach (Sheet sht in _sheetList)
{
if (sht.SheetName == sheet.SheetName)
{
throw new Exception("同一工作簿中工作表名不能相同！");
}
}
_sheetList.Add(sheet);
return sheet;
}
/// <summary>
/// 删除工作表
/// </summary>
/// <param name="index">工作表索引</param>
public void Delete(int index)
{
if (index < 0 || index >= this.Count)
{
throw new Exception("下标超出范围！");
}
_sheetList.RemoveAt(index);
}
/// <summary>
/// 获取工作表
/// </summary>
/// <param name="index">工作表索引</param>
/// <returns></returns>
public Sheet this[int index]
{
get
{
if (index < 0 || index >= this.Count)
{
throw new Exception("下标超出范围！");
}
return _sheetList[index];
}
}
/// <summary>
/// 遍历工作表
/// </summary>
/// <returns></returns>
public IEnumerator GetEnumerator()
{
return _sheetList.GetEnumerator();
}
}
/// <summary>
/// Excel XML工作簿类
/// </summary>
public class ExcelXML
{
public _sheets Sheets = new _sheets();
private static string _appHead = "<?xml version=\"1.0\"?> \n<?mso-application progid=\"Excel.Sheet\"?>\n";
private static string _workBookHead = "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" \n xmlns:o=\"urn:schemas-microsoft-com:office:office\" \n xmlns:x=\"urn:schemas-microsoft-com:office:excel\" \n xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" \n xmlns:html=\"http://www.w3.org/TR/REC-html40\"> \n\n";
#region 冻结窗格
/// <summary>
/// 设置工作表选项
/// </summary>
/// <param name="topRowBottomPane"></param>
/// <param name="leftColumnRightPane"></param>
/// <returns></returns>
private string GetWorksheetOptions(int topRowBottomPane, int leftColumnRightPane)
{
string s = "";
if (topRowBottomPane + leftColumnRightPane <= 0)
{
return s;
}
s += "<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">\n";
s += "<Selected/>";
s += "<FreezePanes/>\n";
s += "<FrozenNoSplit/>\n";
//冻结行
if (topRowBottomPane > 0 && leftColumnRightPane <= 0)
{
s += "<SplitHorizontal>" + topRowBottomPane + "</SplitHorizontal>\n";
s += "<TopRowBottomPane>" + topRowBottomPane + "</TopRowBottomPane>\n";
//s += "<ActivePane>2</ActivePane>\n";
//s += "<Panes>\n<Pane>\n<number>3</Number>\n</Pane>\n<Pane>\n<number>2</Number>\n</Pane>\n</Panes>\n";
}
//冻结列
else if (leftColumnRightPane > 0 && topRowBottomPane <= 0)
{
s += "<SplitVertical>" + leftColumnRightPane + "</SplitVertical>\n";
s += "<LeftColumnRightPane>" + leftColumnRightPane + "</LeftColumnRightPane>\n";
//s += "<ActivePane>2</ActivePane>\n";
//s += "<Panes>\n<Pane>\n<number>5</Number>\n</Pane>\n<Pane>\n<number>2</Number>\n</Pane>\n</Panes>\n";
}
//冻结行、列
else
{
s += "<SplitHorizontal>" + topRowBottomPane + "</SplitHorizontal>\n";
s += "<TopRowBottomPane>" + topRowBottomPane + "</TopRowBottomPane>\n";
s += "<SplitVertical>" + leftColumnRightPane + "</SplitVertical>\n";
s += "<LeftColumnRightPane>" + leftColumnRightPane + "</LeftColumnRightPane>\n";
s += "<ActivePane>0</ActivePane>\n";
s += "<Panes>\n<Pane>\n<Number>3</Number>\n</Pane>\n<Pane>\n<Number>1</Number>\n</Pane>\n<Pane>\n<Number>2</Number>\n</Pane>\n<Pane>\n<Number>0</Number>\n</Pane>\n</Panes>\n";
}
s += "<ProtectObjects>False</ProtectObjects>\n";
s += "<ProtectScenarios>False</ProtectScenarios>\n";
s += "</WorksheetOptions>\n";
return s;
}
#endregion
/// <summary>
/// 导出到文件
/// </summary>
/// <param name="fileName"></param>
public void Export(string fileName)
{
if (this.Sheets.Count < 1)
{
throw new Exception("没有工作表！");
}
string fName = System.Web.HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(fileName));
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fName + ".xls");
HttpContext.Current.Response.Charset = "UTF-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.ContentType = "application/ms-excel";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
HttpContext.Current.Response.Write(_appHead);
HttpContext.Current.Response.Write(_workBookHead);
//遍历工作表
foreach (Sheet sht in Sheets)
{
HttpContext.Current.Response.Write("<Worksheet ss:Name=\"" + sht.SheetName + "\">\n");
HttpContext.Current.Response.Write("<Table>\n");
//遍历行
foreach (Row row in sht.Rows)
{
HttpContext.Current.Response.Write("\n" + row.RowText);
}
HttpContext.Current.Response.Write("\n</Table>\n");
//冻结窗格选项
string sheetOptions = GetWorksheetOptions(sht.TopRowBottomPane, sht.LeftColumnRightPane);
HttpContext.Current.Response.Write(sheetOptions);
HttpContext.Current.Response.Write("</Worksheet>\n");
}
HttpContext.Current.Response.Write("</Workbook>\n");
HttpContext.Current.Response.End();
}
}
}