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 " );
}
}
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
表头。
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;
namespace ExcelExp
... {
/**//// <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";
/**//// <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>
/// 以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);
}
}
}
}
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;
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;
}
}
}
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;
namespace ExcelExp
... {
/**//// <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";
/**//// <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>
/// 以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);
}
}
}
}
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;
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;
namespace ExcelExp
... {
/**//// <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; }
}
}
}
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;
namespace ExcelExp
... {
/**//// <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;
namespace ExcelExp
... {
/**//// <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("设置未成功,集合中未找到指定关键字的元素。");
}
}
}
}
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;
namespace ExcelExp
... {
/**//// <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文件
<script type="text/javascript">var a7426tf="51la";var a7426pu="";var a7426pf="51la";var a7426su=window.location;var a7426sf=document.referrer;var a7426of="";var a7426op="";var a7426ops=1;var a7426ot=1;var a7426d=new Date();var a7426color="";if (navigator.appName=="Netscape"){a7426color=screen.pixelDepth;} else {a7426color=screen.colorDepth;}</script> <script type="text/javascript">a7426tf=top.document.referrer;</script> <script type="text/javascript">a7426pu =window.parent.location;</script> <script type="text/javascript">a7426pf=window.parent.document.referrer;</script> <script type="text/javascript">a7426ops=document.cookie.match(new RegExp("(^| )AJSTAT_ok_pages=([^;]*)(;|$)"));a7426ops=(a7426ops==null)?1: (parseInt(unescape((a7426ops)[2]))+1);var a7426oe =new Date();a7426oe.setTime(a7426oe.getTime()+60*60*1000);document.cookie="AJSTAT_ok_pages="+a7426ops+ ";path=/;expires="+a7426oe.toGMTString();a7426ot=document.cookie.match(new RegExp("(^| )AJSTAT_ok_times=([^;]*)(;|$)"));if(a7426ot==null){a7426ot=1;}else{a7426ot=parseInt(unescape((a7426ot)[2])); a7426ot=(a7426ops==1)?(a7426ot+1):(a7426ot);}a7426oe.setTime(a7426oe.getTime()+365*24*60*60*1000);document.cookie="AJSTAT_ok_times="+a7426ot+";path=/;expires="+a7426oe.toGMTString();</script> <script type="text/javascript">a7426of=a7426sf;if(a7426pf!=="51la"){a7426of=a7426pf;}if(a7426tf!=="51la"){a7426of=a7426tf;}a7426op=a7426pu;try{lainframe}catch(e){a7426op=a7426su;}document.write('
');</script>