实例介绍
【实例截图】
【核心代码】
using System.Collections.Generic;
using GemBox.Spreadsheet;
using System.Data;
using System;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Drawing;
using System.Collections;
using System.Collections.ObjectModel;
#region TableDataSource
/// <summary>
/// DataTable导出Excel数据
/// </summary>
public class TableDataSource : baseDataSource {
private DataTable table;
private ReadOnlyCollection<ExportColumn> mappings;
/// <summary>
/// 构造器
/// </summary>
/// <param name="Table">DataTable表格对象</param>
/// <param name="Mappings">映射列数组</param>
public TableDataSource(DataTable Table, params ExportColumn[] Mappings) {
this.table = Table;
var list = new List<ExportColumn>(Mappings.Length);
list.AddRange(Mappings);
this.mappings = list.AsReadOnly();
}
/// <summary>
/// 构造器
/// </summary>
/// <param name="Table">DataTable表格对象</param>
/// <param name="Mappings">映射列数组</param>
public TableDataSource(DataTable Table, IEnumerable<ExportColumn> Mappings) {
this.table = Table;
this.mappings = new List<ExportColumn>(Mappings).AsReadOnly();
}
internal override void Export() {
if (this.sheet == null) throw new ArgumentNullException("请先初始化ExcelWorksheet对象。");
if (this.table == null) throw new ArgumentNullException("导出数据时数据源不能为空。");
if (this.mappings == null) throw new ArgumentNullException("导出样式不能为空。");
int indexNo;
CellStyle style;
this.InitializeExcelWorksheetStyle(this.mappings, out indexNo, out style);
//总列数
int columnCount = mappings.Count;
List<int> mapIndexs = new List<int>(columnCount);
List<int> tableColumns = new List<int>(columnCount);
for (int i = 0; i < mappings.Count; i ) {
int columnIndex = this.table.Columns.IndexOf<DataColumn>(p2 => p2.ColumnName.Equals(mappings[i].Property, StringComparison.OrdinalIgnoreCase));
if (columnIndex < 0) throw new ArgumentOutOfRangeException(string.Format("属性或字段 {0} 不存在。", mappings[i].Property));
mapIndexs.Add(i);
tableColumns.Add(columnIndex);
}
//开始转换数据
CellRange range;
foreach (DataRow row in this.table.Rows) {
range = this.sheet.Cells.GetSubrangeAbsolute(indexNo, 0, indexNo, columnCount);
for (int i = 0; i < columnCount; i ) {
range[i].Value = (mapIndexs.Contains(i) ? row[tableColumns[mapIndexs.IndexOf(i)]] : default(object));
range[i].Style = style;
range[i].Style.HorizontalAlignment = this.SetCellStyleAlignment(mappings[i].Alignment);
//格式化数据
if (!string.IsNullOrEmpty(mappings[i].NumberFormat)) range[i].Style.NumberFormat = mappings[i].NumberFormat;
}
this.sheet.Rows[indexNo].Height = Convert.ToInt32(1.6 * style.Font.Size);
indexNo ;
}
range = null;
//添加汇总行
this.AppendSumRow(mappings, indexNo, style);
}
}
#endregion
//2. 导出DataGridView数据为Excel数据
//先就放这两个导出功能。
//下面是用到的支持类：
#region TextAlignment
/// <summary>
/// 指定控件中的对象或文本如何相对于控件元素水平对齐。
/// </summary>
public enum TextAlignment {
/// <summary>
/// 对象或文本与控件元素的左侧对齐
/// </summary>
Left = 0,
/// <summary>
/// 对象或文本与控件元素的右侧对齐
/// </summary>
Right = 1,
/// <summary>
/// 对象或文本与控件元素的中心对齐
/// </summary>
Center = 2,
}
#endregion
#region ExportColumn
/// <summary>
/// 导出列设置
/// </summary>
public class ExportColumn {
/// <summary>
/// 获取或设置导出属性
/// </summary>
public string Property {
get;
set;
}
/// <summary>
/// 获取或设置列头标题
/// </summary>
public string HeaderText {
get;
set;
}
/// <summary>
/// 获取或设置列宽
/// </summary>
public int Width {
get;
set;
}
/// <summary>
/// 获取或设置对齐对象
/// </summary>
public TextAlignment Alignment {
get;
set;
}
/// <summary>
/// 获取或设置Excel单元格格式化字符串
/// </summary>
public string NumberFormat {
get;
set;
}
/// <summary>
/// 获取或设置是否自动汇总
/// </summary>
public bool AutoSum {
get;
set;
}
}
#endregion
#region ExcelExport
/// <summary>
/// Excel生成工厂
/// </summary>
public class ExcelExport {
private ExcelFile xlsFile = null;
/// <summary>
/// 获取或设置文件名
/// </summary>
public string FileName {
get;
set;
}
/// <summary>
/// 保存文件
/// </summary>
public void Save() {
this.Save(this.FileName);
}
/// <summary>
/// 保存文件
/// </summary>
/// <param name="fileName">文件名</param>
public void Save(string fileName) {
if (this.xlsFile == null) throw new ArgumentNullException("Xls文件未初始化。");
this.xlsFile.SaveXls(fileName);
}
/// <summary>
/// 保存到流
/// </summary>
/// <param name="stream"></param>
public void SaveToStream(System.IO.Stream stream) {
if (this.xlsFile == null) throw new ArgumentNullException("Xls文件未初始化。");
this.xlsFile.SaveXls(stream);
}
/// <summary>
/// 输出数据
/// </summary>
/// <param name="excelExportor"></param>
public void Export(baseDataSource excelExportor) {
if (excelExportor == null) throw new ArgumentNullException("导出对象不能为空。");
string sname = (!string.IsNullOrEmpty(excelExportor.SheetName) ? excelExportor.SheetName : string.Empty).Trim();
if (this.xlsFile == null) this.xlsFile = new ExcelFile();
if (this.xlsFile.Worksheets.Contains<ExcelWorksheet>(p2 => p2.Name.Equals(excelExportor.SheetName, StringComparison.OrdinalIgnoreCase))) {
throw new ArgumentOutOfRangeException(string.Format("已经存在同名 {0} 的ExcelWorksheet。", excelExportor.SheetName));
}
if (string.IsNullOrEmpty(sname)) {
const string sheetName = "Sheet{0}";
int indexNo = 1;
while (this.xlsFile.Worksheets.Contains<ExcelWorksheet>(p2 => p2.Name.Equals(string.Format(sheetName, indexNo), StringComparison.OrdinalIgnoreCase))) {
indexNo ;
}
sname = string.Format(sheetName, indexNo);
} else {
sname = sname.Replace("/", " ").Replace("?", " ").Replace("*", " ").Replace("[", " ").Replace("]", " ");
if (string.IsNullOrEmpty(sname) || (sname.Length > 31)) throw new ArgumentOutOfRangeException("ExcelWorksheet的名称不能为空，或者超过31个字符。");
}
excelExportor.SetExcelWorksheet(this.xlsFile.Worksheets.Add(sname));
excelExportor.Export();
}
}
#endregion
#region baseDataSource
/// <summary>
/// Excel输出器
/// </summary>
public abstract class baseDataSource : IDisposable {
#region 公共属性
/// <summary>
/// 获取或设置Sheet名称
/// 如果InsertCaption为真，则将作为表格标题插入标题行
/// </summary>
public string SheetName {
get;
set;
}
/// <summary>
/// 是否插入标题行
/// </summary>
public bool InsertCaption {
get;
set;
}
/// <summary>
/// 获取或设置标题字体（如果标题不为空且字体未设置，则默认和数据字体一样）
/// </summary>
public Font CaptionFont {
get;
set;
}
/// <summary>
/// 获取或设置列头字体
/// </summary>
public Font HeaderFont {
get;
set;
}
/// <summary>
/// 获取或设置数据字体
/// </summary>
public Font DataFont {
get;
set;
}
/// <summary>
/// 获取或设置是否自动添加汇总行
/// </summary>
public bool AppendSum {
get;
set;
}
#endregion
#region 私有保护方法
#region 保护方法
/// <summary>
/// ExcelWorksheet对象
/// </summary>
protected ExcelWorksheet sheet;
/// <summary>
/// 初始化表格样式
/// </summary>
/// <param name="mappings"></param>
/// <param name="indexNo"></param>
/// <param name="style"></param>
protected void InitializeExcelWorksheetStyle(ReadOnlyCollection<ExportColumn> mappings, out int indexNo, out CellStyle style) {
if (this.sheet == null) throw new ArgumentNullException("请先初始化ExcelWorksheet对象。");
if (mappings == null) throw new ArgumentNullException("导出列样式不能为空。");
if (this.DataFont == default(Font)) this.DataFont = GetDefaultFont();
CellRange range;
indexNo = 0;
//如果标题行不为空
if (InsertCaption && (!string.IsNullOrEmpty(this.SheetName))) {
this.sheet.Cells[0].Value = this.SheetName;
range = this.sheet.Cells.GetSubrangeAbsolute(0, 0, 0, mappings.Count - 1);
range.Merged = true;
Font capfont = ((this.CaptionFont != default(Font)) ? this.CaptionFont : ((this.HeaderFont != default(Font)) ? this.HeaderFont : this.DataFont));
this.SetCellStyleFont(range.Style, capfont);
range.Style.Font.Weight = ExcelFont.BoldWeight;
range.Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
range.Style.VerticalAlignment = VerticalAlignmentStyle.Center;
this.sheet.Rows[indexNo].Height = capfont.Height * 33;
capfont.Dispose();
indexNo ;
}
//开始处理列头
range = this.sheet.Cells.GetSubrangeAbsolute(indexNo, 0, indexNo, mappings.Count - 1);
Font headerFont = ((this.HeaderFont != default(Font)) ? this.HeaderFont : this.DataFont);
for (int i = 0; i < mappings.Count; i ) {
this.sheet.Columns[i].Width = mappings[i].Width * 264;
range[i].Value = mappings[i].HeaderText;
this.SetCellStyleFont(range[i].Style, headerFont);
range[i].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
range[i].Style.VerticalAlignment = VerticalAlignmentStyle.Center;
range[i].SetBorders(MultipleBorders.Top | MultipleBorders.Left | MultipleBorders.Bottom | MultipleBorders.Right, Color.Black, LineStyle.Thin);
}
this.sheet.Rows[indexNo].Height = Convert.ToInt32(20 * 1.6 * headerFont.Size);
headerFont.Dispose();
indexNo ;
//返回数据行列样式
style = new CellStyle();
style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
style.VerticalAlignment = VerticalAlignmentStyle.Center;
style.Font.Size = Convert.ToInt32(this.DataFont.Size * 20);
style.Borders.SetBorders(MultipleBorders.Top | MultipleBorders.Left | MultipleBorders.Bottom | MultipleBorders.Right, Color.Black, LineStyle.Thin);
style.WrapText = true;
this.SetCellStyleFont(style, this.DataFont);
}
/// <summary>
/// 设置单元格字体样式
/// </summary>
/// <param name="style">样式</param>
/// <param name="font">字体</param>
protected void SetCellStyleFont(CellStyle style, System.Drawing.Font font) {
style.Font.Color = Color.Black;
style.Font.Italic = font.Italic;
style.Font.Name = font.Name;
style.Font.Size = Convert.ToInt32(font.Size * 20);
style.Font.Strikeout = font.Strikeout;
style.Font.UnderlineStyle = (font.Underline ? UnderlineStyle.Single : UnderlineStyle.None);
}
/// <summary>
/// 设置单元文本水平对齐方式
/// </summary>
/// <param name="alignment">对齐方式</param>
/// <returns></returns>
protected HorizontalAlignmentStyle SetCellStyleAlignment(TextAlignment alignment) {
switch (alignment) {
case TextAlignment.Left:
return HorizontalAlignmentStyle.Left;
case TextAlignment.Center:
return HorizontalAlignmentStyle.Center;
case TextAlignment.Right:
return HorizontalAlignmentStyle.Right;
default:
return HorizontalAlignmentStyle.Left;
}
}
/// <summary>
/// 追加自动汇总行
/// </summary>
/// <param name="mappings">映射</param>
/// <param name="rowIndex">汇总行索引</param>
/// <param name="style">单元格样式</param>
protected void AppendSumRow(ReadOnlyCollection<ExportColumn> mappings, int rowIndex, CellStyle style) {
if (!this.AppendSum) return;
//是否存在标题行
bool existCaption = !string.IsNullOrEmpty(this.SheetName);
//开始填充数据
string SUMVALUE = (!existCaption ? "=SUM({0}2:{0}" rowIndex ")" : "=SUM({0}3:{0}" rowIndex ")");
int columnCount = mappings.Count;
CellRange range = this.sheet.Cells.GetSubrangeAbsolute(rowIndex, 0, rowIndex, columnCount - 1);
if (!mappings[0].AutoSum) range[0].Value = "总计";
for (int i = 0; i < columnCount; i ) {
if (mappings[i].AutoSum) range[i].Formula = string.Format(SUMVALUE, ToExcelColumnName(i));
range[i].Style = style;
range[i].Style.HorizontalAlignment = this.SetCellStyleAlignment(mappings[i].Alignment);
range[i].Style.Font.Weight = ExcelFont.BoldWeight;
//格式化数据
if (!string.IsNullOrEmpty(mappings[i].NumberFormat)) range[i].Style.NumberFormat = mappings[i].NumberFormat;
}
this.sheet.Rows[rowIndex].Height = Convert.ToInt32(1.6 * style.Font.Size);
}
#endregion
#region 私有方法
/// <summary>
/// 将列索引转换为A->Z
/// </summary>
/// <param name="index">索引</param>
/// <returns></returns>
private string ToExcelColumnName(int index) {
if (index < 0) throw new Exception("无效参数。");
List<string> chars = new List<string>();
do {
if (chars.Count > 0) index--;
chars.Insert(0, ((char)(index % 26 (int)'a')).ToString());
index = (int)((index - index % 26) / 26);
} while (index > 0);
return string.Join(string.Empty, chars.ToArray()).ToUpper();
}
private Font GetDefaultFont() {
return new Font("宋体", 10.5f, FontStyle.Regular, GraphicsUnit.Point, 0x86);
}
#endregion
#endregion
#region 公共方法
/// <summary>
/// 设置要填充数据ExcelWorksheet
/// </summary>
/// <param name="Worksheet"></param>
internal void SetExcelWorksheet(ExcelWorksheet Worksheet) {
this.sheet = Worksheet;
}
/// <summary>
/// 输出数据
/// </summary>
internal abstract void Export();
#endregion
#region IDisposable 成员
/// <summary>
/// 析构方法
/// </summary>
public void Dispose() {
if (this.CaptionFont != default(Font)) this.CaptionFont.Dispose();
if (this.HeaderFont != default(Font)) this.HeaderFont.Dispose();
if (this.DataFont != default(Font)) this.DataFont.Dispose();
}
#endregion
}
#endregion
/// <summary>
/// 序列扩展方法
/// </summary>
static class EnumerableExtensions {
/// <summary>
/// 确定 System.Collections.Generic.List<T> 是否包含与指定谓词所定义的条件相匹配的元素。
/// </summary>
/// <typeparam name="TElement">对象类型</typeparam>
/// <param name="enumerable">源序列</param>
/// <param name="predicate">匹配条件</param>
/// <returns></returns>
public static bool Contains<TElement>(this IEnumerable enumerable, Predicate<TElement> predicate) {
foreach (TElement item in enumerable) {
if (predicate(item)) return true;
}
return false;
}
/// <summary>
/// 搜索指定的对象，并返回整个 System.Collections.Generic.List<T> 中第一个匹配项的从零开始的索引。
/// </summary>
/// <typeparam name="TElement">对象类型</typeparam>
/// <param name="enumerable">源序列</param>
/// <param name="predicate">匹配条件</param>
/// <returns></returns>
public static int IndexOf<TElement>(this IEnumerable enumerable, Predicate<TElement> predicate) {
int index = 0;
foreach (TElement item in enumerable) {
if (predicate(item)) return index;
else index ;
}
return -1;
}
}
#region TypeSystem
static class TypeSystem {
internal static Type GetElementType(Type seqType) {
Type ienum = FindIEnumerable(seqType);
if (ienum == null) return seqType;
return ienum.GetGenericArguments()[0];
}
private static Type FindIEnumerable(Type seqType) {
if (seqType == null || seqType == typeof(string))
return null;
if (seqType.IsArray)
return typeof(IEnumerable<>).MakeGenericType(seqType.GetElementType());
if (seqType.IsGenericType) {
foreach (Type arg in seqType.GetGenericArguments()) {
Type ienum = typeof(IEnumerable<>).MakeGenericType(arg);
if (ienum.IsAssignableFrom(seqType)) {
return ienum;
}
}
}
Type[] ifaces = seqType.GetInterfaces();
if (ifaces != null && ifaces.Length > 0) {
foreach (Type iface in ifaces) {
Type ienum = FindIEnumerable(iface);
if (ienum != null) return ienum;
}
}
if (seqType.BaseType != null && seqType.BaseType != typeof(object)) {
return FindIEnumerable(seqType.BaseType);
}
return null;
}
}
#endregion
小贴士
感谢您为本站写下的评论，您的评论对其它用户来说具有重要的参考价值，所以请认真填写。
- 类似“顶”、“沙发”之类没有营养的文字，对勤劳贡献的楼主来说是令人沮丧的反馈信息。
- 相信您也不想看到一排文字/表情墙，所以请不要反馈意义不大的重复字符，也请尽量不要纯表情的回复。
- 提问之前请再仔细看一遍楼主的说明，或许是您遗漏了。
- 请勿到处挖坑绊人、招贴广告。既占空间让人厌烦，又没人会搭理，于人于己都无利。
关于好例子网
本站旨在为广大IT学习爱好者提供一个非营利性互相学习交流分享平台。本站所有资源都可以被免费获取学习研究。本站资源来自网友分享，对搜索内容的合法性不具有预见性、识别性、控制性，仅供学习研究，请务必在下载后24小时内给予删除，不得用于其他任何用途，否则后果自负。基于互联网的特殊性，平台无法对用户传输的作品、信息、内容的权属或合法性、安全性、合规性、真实性、科学性、完整权、有效性等进行实质审查；无论平台是否已进行审查，用户均应自行承担因其传输的作品、信息、内容而可能或已经产生的侵权或权属纠纷等法律责任。本站所有资源不代表本站的观点或立场，基于网友分享，根据中国法律《信息网络传播权保护条例》第二十二与二十三条之规定，若资源存在侵权或相关问题请联系本站客服人员，点此联系我们。关于更多版权及免责申明参见 版权及免责申明
网友评论
我要评论