[转载]EXcel导出(服务端无须安装Excel程序)
CarlosAg.ExcelXmlWriter是一三方dll控件，可以到http://www.carlosag.net/Tools/ExcelXmlWriter/Default.aspx下载。他使用起来非常简单。它生成的是xml形式的Excel。
一个workbook就是一个在内存中的Excel文件。您只需要对workbook进行操作，即可按您想要的形式构建一内存中的Excel文件。调用workbook的save方法就可以在内存中的Excel文件写入硬盘或http下载流中去。Workbook中主要有Styles集合用于添加Excel文档中的样式分格。Worksheets集合用于添加Excel文档的sheet对象。CarlosAg.ExcelXmlWrite的具体用法请官方网站http://www.carlosag.net/或网络上查询。
CarlosAg.ExcelXmlWriter是一三方dll控件，可以到http://www.carlosag.net/Tools/ExcelXmlWriter/Default.aspx下载。他使用起来非常简单。在C#中使用方法：
using CarlosAg.ExcelXmlWriter;
class TestApp {
static void Main(string[] args) {
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets.Add("Sample");
WorksheetRow row = sheet.Table.Rows.Add();
row.Cells.Add("Hello World");
book.Save(@"c: est.xls");
}
}
这样就生成了一简单的Excel文件就生成在C盘了。它生成的是xml形式的Excel。
一个workbook就是一个在内存中的Excel文件。您只需要对workbook进行操作，即可按您想要的形式构建一内存中的Excel文件。调用workbook的save方法就可以在内存中的Excel文件写入硬盘或http下载流中去。Workbook中主要有Styles集合用于添加Excel文档中的样式分格。Worksheets集合用于添加Excel文档的sheet对象。CarlosAg.ExcelXmlWrite的具体用法请官方网站http://www.carlosag.net/或网络上查询。
在.net web应用程序开发过程，经常会遇到Excel的导出功能，过去一直用微软的office组件，配置起来麻烦，而且服务端需要安装Excel程序，对环境的改变也会影响功能的正常运行。还会经常出现一些莫名其妙的问题。有了CarlosAg.ExcelXmlWriter这些问题都不会出现了。不需要任何配置东西。由于大多数是对DataTable数据的导出功能。所以我把CarlosAg.ExcelXmlWriter进行了二次封装，对于Web程序中的Excel导出功能做到了完全简化。如果要导出一个DataTable对象dt的数据到Excel中，只需要传入dt即可。当然Excel的表头就是dt中的列名。也可以对dt的列名进行自定义甚至实现多表头导出功能。下面我们来详细研究：
封装工作需要三个类来完成：ExcelBook,Excel主处理类。ExcelColumn，Excel表头字段类用于处理自定义表头时使用。ExcelColumnCollection，Excel表头字段集合，用于添加Excel表头。
注：以下类的原做者地址(http://www.codediy.net/bbs/viewthread.php?tid=7&extra=page%3D1)
经本人修改，加入了重载public ExcelBook(DataSet ds, string[] title) 支持DataSet多表导出
ExcelBook类:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Text;
using System.Collections.Generic;
using CarlosAg.ExcelXmlWriter;
/**//// <summary>
/// ExcelBook 导出功能主类 的摘要说明
/// </summary>
public class ExcelBook
{
private Workbook _book = new Workbook();
private DataTable _dataTable = null;
private string _title = "";
private Page _page = null;
private List<ExcelColumnCollection> _columnNamesCollection = new List<ExcelColumnCollection>();
private SortedList<string, int> _maxLengthOfField = new SortedList<string, int>();
private bool _isAutoFitWidth = true;
private string _Author = "";
private string _LastAuthor = "";
private string _Company = "";
private string _Version = "11.6408";
private DataSet _dataSet = null;
private System.Collections.ArrayList _sheetTitles = new System.Collections.ArrayList();
/**//// <summary>
/// 构造函数
/// </summary>
/// <param name="dt">DataTable形式的数据源</param>
/// <param name="title">Excel显示标题</param>
public ExcelBook(DataTable dt, string title)
{
Page page = (Page)HttpContext.Current.Handler;
if (dt == null)
{
throw new Exception("数据源为空");
}
_dataTable = dt;
_title = title;
_page = page;
}
/**/
/// <summary>
/// 构造函数
/// </summary>
/// <param name="dt">DataSet形式的数据源</param>
/// <param name="title">Excel显示各标题</param>
public ExcelBook(DataSet ds, string[] title)
{
Page page = (Page)HttpContext.Current.Handler;
if (ds == null || ds.Tables.Count==0)
{
throw new Exception("数据源为空");
}
_dataSet = ds;
if (ds.Tables.Count != title.Length)
{
//throw new Exception("标题个数与表个数不符");
//标题个数与表个数不符时采用默认表名
for (int i = 0; i < ds.Tables.Count; i++)
{
_sheetTitles.Add(ds.Tables[i].TableName.ToString());
}
}
else
{
for (int i = 0; i < title.Length; i++)
{
_sheetTitles.Add(title[i].ToString());
}
}
_page = page;
}
/**//// <summary>
/// 以GridView的Head为标题
/// </summary>
/// <param name="row">GridView表头行对象</param>
public void SetColumnNameFromGridViewHeadRow(GridViewRow row)
{
ExcelColumnCollection excelcols = new ExcelColumnCollection();
_columnNamesCollection.Add(excelcols);
foreach (TableCell cell in row.Cells)
{
excelcols.Add(new ExcelColumn(cell.Text));
}
}
/**//// <summary>
/// 初始化Excel Workbook
/// </summary>
/// <param name="book">book</param>
private void InitializeBook(Workbook book)
{
book.Properties.Author = Author;
book.Properties.LastAuthor = LastAuthor;
book.Properties.Created = DateTime.Now;
book.Properties.Company = Company;
book.Properties.Version = Version;
book.ExcelWorkbook.WindowHeight = 13500;
book.ExcelWorkbook.WindowWidth = 17100;
book.ExcelWorkbook.WindowTopX = 360;
book.ExcelWorkbook.WindowTopY = 75;
book.ExcelWorkbook.ProtectWindows = false;
book.ExcelWorkbook.ProtectStructure = false;
}
/**//// <summary>
/// 设置样式
/// </summary>
/// <param name="styles">样式集合</param>
private void SetStyles(WorksheetStyleCollection styles)
{
// -----------------------------------------------
// Default
// -----------------------------------------------
WorksheetStyle Default = styles.Add("Default");
Default.Name = "Normal";
Default.Font.FontName = "宋体";
Default.Font.Size = 12;
Default.Alignment.Vertical = StyleVerticalAlignment.Center;
// -----------------------------------------------
// TitleStyle
// -----------------------------------------------
WorksheetStyle TitleStyle = styles.Add("TitleStyle");
TitleStyle.Font.Bold = true;
TitleStyle.Font.FontName = "黑体";
TitleStyle.Font.Size = 14;
TitleStyle.Alignment.Horizontal = StyleHorizontalAlignment.Center;
TitleStyle.Alignment.Vertical = StyleVerticalAlignment.Center;
// -----------------------------------------------
// FieldStyle
// -----------------------------------------------
WorksheetStyle FieldStyle = styles.Add("FieldStyle");
FieldStyle.Font.Bold = true;
FieldStyle.Font.FontName = "宋体";
FieldStyle.Font.Size = 12;
FieldStyle.Alignment.Horizontal = StyleHorizontalAlignment.Center;
FieldStyle.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
FieldStyle.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
FieldStyle.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
FieldStyle.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
// -----------------------------------------------
// LastFieldStyle
// -----------------------------------------------
WorksheetStyle LastFieldStyle = styles.Add("LastFieldStyle");
LastFieldStyle.Font.Bold = true;
LastFieldStyle.Font.FontName = "宋体";
LastFieldStyle.Font.Size = 12;
LastFieldStyle.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
LastFieldStyle.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
LastFieldStyle.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
LastFieldStyle.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
// -----------------------------------------------
// DataStyle
// -----------------------------------------------
WorksheetStyle DataStyle = styles.Add("DataStyle");
DataStyle.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
DataStyle.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
DataStyle.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
DataStyle.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
}
/**//// <summary>
/// 设置Excel Sheet
/// </summary>
/// <param name="sheets">sheets集合</param>
private void SetSheels(WorksheetCollection sheets)
{
Worksheet sheet = sheets.Add(_title);
sheet.Table.DefaultRowHeight = 14.25F;
sheet.Table.DefaultColumnWidth = 54F;
sheet.Table.FullColumns = 1;
sheet.Table.FullRows = 1;
// -----------------------------------------------
WorksheetRow row = null;
WorksheetCell cell = null;
#region 大标题
row = sheet.Table.Rows.Add();
row.AutoFitHeight = true;
row.Height = 30;
cell = row.Cells.Add();
cell.StyleID = "TitleStyle";
cell.Data.Type = DataType.String;
cell.Data.Text = _title;
cell.MergeAcross = _dataTable.Columns.Count - 1;
#endregion
foreach (DataColumn dc in _dataTable.Columns)//初始化列宽度集合
{
_maxLengthOfField[dc.ColumnName] = 0;
}
//-----------------------------------------------字段
#region 字段标题行
if (_columnNamesCollection.Count != 0)
{
for (int i = 0; i < _columnNamesCollection.Count; i++)
{
row = sheet.Table.Rows.Add();
row.AutoFitHeight = true;
int j = 0;
foreach (ExcelColumn ec in _columnNamesCollection[i])
{
cell = row.Cells.Add();
cell.Data.Text = ec.Name;
cell.Data.Type = DataType.String;
if (i != _columnNamesCollection.Count - 1)
{
cell.MergeAcross = ec.MergeAcross;
cell.StyleID = "FieldStyle";
}
else//最下层标题行
{
cell.StyleID = "LastFieldStyle";
_maxLengthOfField[_dataTable.Columns[j].ColumnName] =
GetMaxLength(_maxLengthOfField[_dataTable.Columns[j].ColumnName], ec.Name);
j++;
}
}
}
}
else
{
row = sheet.Table.Rows.Add();
row.AutoFitHeight = true;
foreach (DataColumn dc in _dataTable.Columns)
{
cell = row.Cells.Add();
cell.Data.Text = dc.ColumnName;
cell.Data.Type = DataType.String;
cell.StyleID = "FieldStyle";
_maxLengthOfField[dc.ColumnName] = GetMaxLength(_maxLengthOfField[dc.ColumnName],dc.ColumnName);
}
}
#endregion
// -----------------------------------------------
#region 数据行
object dcValueO = null;
string dcValueS = null;
foreach (DataRow dr in _dataTable.Rows)
{
row = sheet.Table.Rows.Add();
row.AutoFitHeight = true;
foreach (DataColumn dc in _dataTable.Columns)
{
dcValueO = dr[dc];
if (dcValueO == DBNull.Value)
dcValueS = string.Empty;
else
dcValueS = dcValueO.ToString();
cell = row.Cells.Add();
cell.Data.Text = dcValueS;
cell.Data.Type = TypeConvert(dc.DataType);
cell.StyleID = "DataStyle";
if (_isAutoFitWidth || _columnNamesCollection.Count == 0)
{
_maxLengthOfField[dc.ColumnName] = GetMaxLength(_maxLengthOfField[dc.ColumnName], dcValueS);
}
}
}
#endregion
// -----------------------------------------------
#region 设置列
WorksheetColumn column = null;
if (!_isAutoFitWidth && _columnNamesCollection.Count != 0)
{
foreach (ExcelColumn ec in _columnNamesCollection[_columnNamesCollection.Count - 1])
{
column = new WorksheetColumn();
column.AutoFitWidth = false;
column.Width = ec.Width;
sheet.Table.Columns.Add(column);
}
}
else
{
foreach (DataColumn dc in _dataTable.Columns)
{
column = new WorksheetColumn();
column.AutoFitWidth = false;
column.Width = _maxLengthOfField[dc.ColumnName] * 7;
sheet.Table.Columns.Add(column);
}
}
#endregion
// Options
// -----------------------------------------------
sheet.Options.Selected = true;
sheet.Options.ProtectObjects = false;
sheet.Options.ProtectScenarios = false;
sheet.Options.Print.PaperSizeIndex = 9;
sheet.Options.Print.HorizontalResolution = 300;
sheet.Options.Print.VerticalResolution = 300;
sheet.Options.Print.ValidPrinterInfo = true;
}
/**//// <summary>
/// 向客户端发送Excel下载文档数据
/// </summary>
/// <param name="downloadFileName">下载时显示的文件名称</param>
public void WriteExcelToClient(string downloadFileName)
{
string fileName = string.IsNullOrEmpty(downloadFileName) ?
(string.IsNullOrEmpty(_title) ? "未命名文件" : _title) : downloadFileName;
//判断是传入的单个表还是DataSet
if (_dataTable == null)
{
InitializeBook(_book);
SetStyles(_book.Styles);
for (int i = 0; i < _dataSet.Tables.Count; i++)
{
_title = _sheetTitles[i].ToString();
_dataTable = _dataSet.Tables[i];
SetSheels(_book.Worksheets);
}
}
else
{
InitializeBook(_book);
SetStyles(_book.Styles);
SetSheels(_book.Worksheets);
}
_book.Save(_page.Response.OutputStream);
_page.Response.AppendHeader("Content-Disposition", "Attachment; FileName=" +
HttpUtility.UrlEncode(fileName, Encoding.UTF8) + ".xls;");
_page.Response.ContentEncoding = System.Text.Encoding.UTF8;
_page.Response.Charset = "UTF-8";
_page.Response.Flush();
_page.Response.End();
}
/**//// <summary>
/// 向客户端发送Excel下载文档数据
/// </summary>
public void WriteExcelToClient()
{
WriteExcelToClient(null);
}
/**//// <summary>
/// 数据类型转换
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
private DataType TypeConvert(Type type)
{
switch (type.Name)
{
case "Decimal":
case "Double":
case "Single":
return DataType.Number;
case "Int16":
case "Int32":
case "Int64":
case "SByte":
case "UInt16":
case "UInt32":
case "UInt64":
return DataType.Number;
case "String":
return DataType.String;
case "DateTime":
return DataType.String;
default:
return DataType.String;
}
}
/**//// <summary>
/// 添加标题行 集合
/// </summary>
/// <param name="exColumnCollection"></param>
public void AddColumnNamesCollection(ExcelColumnCollection exColumnCollection)
{
_columnNamesCollection.Add(exColumnCollection);
}
/**//// <summary>
/// 清楚标题行集合
/// </summary>
public void ClearColumnNamesCollection()
{
_columnNamesCollection.Clear();
}
/**//// <summary>
/// 列宽是否自适应
/// </summary>
public bool IsAutoFitWidth
{
get { return _isAutoFitWidth; }
set { _isAutoFitWidth = value; }
}
public string Author
{
get { return _Author; }
set { _Author = value; }
}
public string LastAuthor
{
get { return _LastAuthor; }
set { _LastAuthor = value; }
}
public string Company
{
get { return _Company; }
set { _Company = value; }
}
public string Version
{
get { return _Version; }
set { _Version = value; }
}
/**//// <summary>
/// 获取字段最大宽度函数
/// </summary>
/// <param name="oldLength">原来长度</param>
/// <param name="str">当前字符串</param>
/// <returns>最大值</returns>
private int GetMaxLength(int oldLength, string str)
{
if (str == null) str = "";
byte[] bs = System.Text.Encoding.Default.GetBytes(str.Trim());
int newLength = bs.Length;
if (oldLength > newLength)
return oldLength;
else
return newLength;
}
}
ExcelColumn Excel表头字段类
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/**/
/// <summary>
/// Excel列名ExcelColumn 的摘要说明
/// </summary>
public class ExcelColumn
{
private int _width = 150;//默认宽度
private string _name = string.Empty;//列名
private int _mergeAcross = 0;//跨越字段
/**/
/// <summary>
/// 构造函数
/// </summary>
/// <param name="width">列宽</param>
/// <param name="name">列名</param>
public ExcelColumn(int width, string name)
{
this._width = width;
this._name = name;
this._mergeAcross = 0;
}
/**/
/// <summary>
/// 构造函数
/// </summary>
/// <param name="name">列名</param>
/// <param name="colspan">合并列数</param>
public ExcelColumn(string name, int colspan)
{
this._name = name;
this._mergeAcross = colspan - 1;
}
/**/
/// <summary>
/// 构造函数
/// </summary>
/// <param name="name">列名</param>
public ExcelColumn(string name)
{
this._name = name;
}
public int Width
{
get { return _width; }
}
public string Name
{
get { return _name; }
}
public int MergeAcross
{
get { return _mergeAcross; }
}
}
ExcelColumnCollection，Excel表头字段集合类
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/**//// <summary>
/// ExcelColumnCollection 的摘要说明
/// </summary>
public class ExcelColumnCollection : System.Collections.CollectionBase
{
public ExcelColumnCollection()
{
}
/**//// <summary>
/// 将对象添加到 ExcelColumnCollection 的结尾处。
/// </summary>
/// <param name="value">要添加到 ExcelColumnCollection 的末尾处的 <see cref="ExcelColumn"/>。</param>
/// <returns>ExcelColumnCollection 索引，已在此处添加了 value。</returns>
public int Add(ExcelColumn value)
{
return (List.Add(value));
}
/**//// <summary>
/// 搜索指定的 <see cref="ExcelColumn"/>，并返回整个 ExcelColumnCollection 中第一个匹配项的从零开始的索引。
/// </summary>
/// <param name="value">要在 ExcelColumnCollection 中查找的 <see cref="ExcelColumn"/>。</param>
/// <returns>如果在整个 ExcelColumnCollection 中找到 value 的第一个匹配项，则为该项的从零开始的索引；否则为 -1。</returns>
public int IndexOf(ExcelColumn value)
{
return (List.IndexOf(value));
}
/**//// <summary>
/// 从 RoleCollection 中移除特定对象的第一个匹配项。
/// </summary>
/// <param name="value">要从 <see cref="ExcelColumnCollection"/> 移除的 <see cref="ExcelColumn"/>。</param>
///
/// <exception cref="System.ArgumentException">未在 ExcelColumnCollection 对象中找到 value 参数。</exception>
/// <exception cref="System.NotSupportedException">ExcelColumnCollection 为只读，或 ExcelColumnCollection 具有固定大小。
/// </exception>
public void Remove(ExcelColumn value)
{
List.Remove(value);
}
/**//// <summary>
/// 获取或设置指定索引处的元素。
/// </summary>
/// <param name="index">要获得或设置的元素从零开始的索引。</param>
/// <returns>指定索引处的元素。</returns>
/// <exception cref="System.ArgumentOutOfRangeException">index 小于零。
/// - 或 -
/// index 等于或大于 Count。
/// </exception>
public ExcelColumn this[int index]
{
get
{
return (ExcelColumn)List[index];
}
set
{
List[index] = value;
}
}
/**//// <summary>
/// 获取或设置指定 关键字 的元素。
/// </summary>
/// <param name="nodeText">要获得或设置的元素的关键字。</param>
/// <returns>如果在整个 ItemNodeCollection 中找到 关键字 的第一个匹配项，则为该项的元素；否则为 null。</returns>
/// <exception cref="System.ArgumentException">设置未成功，集合中未找到指定关键字的元素。</exception>
public ExcelColumn this[string name]
{
get
{
ExcelColumn excelColumn;
for (int i = 0; i < List.Count; i++)
{
excelColumn = (ExcelColumn)List[i];
if (excelColumn.Name == name)
{
return excelColumn;
}
}
return null;
}
set
{
ExcelColumn excelColumn;
for (int i = 0; i < List.Count; i++)
{
excelColumn = (ExcelColumn)List[i];
if (excelColumn.Name == name)
{
excelColumn = value;
return;
}
}
throw new ArgumentException("设置未成功，集合中未找到指定关键字的元素。");
}
}
}
使用方法:
直接导出DataTable dt的数据以dt的列名为表头名 ExcelBook eb = new ExcelBook(dt,"标题");
eb.IsAutoFitWidth = true;//自定适用列宽
eb.Author = "";//作者
eb.LastAuthor = "";//最后作者
eb.Company = "";//公司
eb.Version = "";//版本
eb.WriteExcelToClient("Excel文件名");//下载Excel文件
//eb.WriteExcelToClient();//以标题为下载文件名
自定义表头名称
ExcelBook eb = new ExcelBook(dt, "标题");
ExcelColumnCollection ec = new ExcelColumnCollection();
eb.AddColumnNamesCollection(ec);
ec.Add(new ExcelColumn("姓名"));
ec.Add(new ExcelColumn("性别"));
ec.Add(new ExcelColumn("学历"));
ec.Add(new ExcelColumn("政治面貌"));
eb.IsAutoFitWidth = true;//自定适用列宽
eb.WriteExcelToClient("Excel文件名");//下载Excel文件
//eb.WriteExcelToClient();//以标题为下载文件名
自定义多表头
ExcelBook eb = new ExcelBook(dt, "标题");
ExcelColumnCollection ec = new ExcelColumnCollection();//第一层表头
eb.AddColumnNamesCollection(ec);
ec.Add(new ExcelColumn("基本信息",2));
ec.Add(new ExcelColumn("高级信息",2));
ExcelColumnCollection ec2 = new ExcelColumnCollection();//第二层表头
eb.AddColumnNamesCollection(ec2);
ec2.Add(new ExcelColumn("姓名"));
ec2.Add(new ExcelColumn("性别"));
ec2.Add(new ExcelColumn("学历"));
ec2.Add(new ExcelColumn("政治面貌"));
eb.IsAutoFitWidth = true;//自定适用列宽
eb.WriteExcelToClient("Excel文件名");//下载Excel文件
以GridView的头为表
ExcelBook eb = new ExcelBook(dt, "标题");
eb.SetColumnNameFromGridViewHeadRow(gridView1.HeaderRow);
eb.IsAutoFitWidth = true;//自定适用列宽
eb.WriteExcelToClient("Excel文件名");//下载Excel文件
导出多表DataSet（导出后会在Excel文件中有多个工作薄）
ExcelBook eb = new ExcelBook(数据集DataSet, 标题数组string[]);
eb.IsAutoFitWidth = true;//自定适用列宽
eb.WriteExcelToClient("Excel文件名");//下载Excel文件导出多表DataSet（导出后会在Excel文件中有多个工作薄
邮箱：steven9801@163.com
QQ: 48039387
