EXcel导出(服务端无须安装Excel程序)

最新推荐文章于 2024-04-26 10:35:49 发布

吾非大神

最新推荐文章于 2024-04-26 10:35:49 发布

阅读量1.5k

点赞数

分类专栏： c# 文章标签： excel string exception null styles class

c# 专栏收录该内容

17 篇文章

订阅专栏

本文介绍了一个基于CarlosAg.ExcelXmlWriter的.NET Excel导出组件封装方案，支持自定义表头及多表头，提供了完整的示例代码。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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表头。

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 >DataTable形式的数据源</param>

/// <param >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 >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 >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 >样式集合</param>

private void SetStyles(WorksheetStyleCollection styles)

{

// -----------------------------------------------

// Default

// -----------------------------------------------

WorksheetStyle Default = styles.Add("Default");

Default.Name = "Normal";

Default.Font.F;

Default.Font.Size = 12;

Default.Alignment.Vertical = StyleVerticalAlignment.Center;

// -----------------------------------------------

// TitleStyle

// -----------------------------------------------

WorksheetStyle TitleStyle = styles.Add("TitleStyle");

TitleStyle.Font.Bold = true;

TitleStyle.Font.F;

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.F;

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.F;

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 >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)

{

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 >下载时显示的文件名称</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 ></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 ></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 >原来长度</param>

/// <param >当前字符串</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 >列宽</param>

/// <param >列名</param>

public ExcelColumn(int width, string name)

{

this._width = width;

this._name = name;

this._mergeAcross = 0;

}

/// <summary>

/// 构造函数

/// </summary>

/// <param >列名</param>

/// <param >合并列数</param>

public ExcelColumn(string name, int colspan)

{

this._name = name;

this._mergeAcross = colspan - 1;

}

/// <summary>

/// 构造函数

/// </summary>

/// <param >列名</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 >要添加到 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 >要在 ExcelColumnCollection 中查找的 <see cref="ExcelColumn"/>。</param>

/// <returns>如果在整个 ExcelColumnCollection 中找到 value 的第一个匹配项，则为该项的从零开始的索引；否则为 -1。</returns>

public int IndexOf(ExcelColumn value)

{

return (List.IndexOf(value));

}

/// <summary>

/// 从 RoleCollection 中移除特定对象的第一个匹配项。

/// </summary>

/// <param >要从 <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 >要获得或设置的元素从零开始的索引。</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 >要获得或设置的元素的关键字。</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;

if (excelColumn.Name == name)

{

return excelColumn;

}

return null;

}

set

{

ExcelColumn excelColumn;

for (int i = 0; i < List.Count; i++)

{

excelColumn = (ExcelColumn)List;

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文件