以前一直使用Infragistics的Web控件，其中导出Excel部分，有使用其自身的Excel导出的，为方便自已的使用，所以进行了再次的封装。
不知到还有没有同行使和该组件的，希望有用！
更全的源码，请访问下载
https://download.csdn.net/download/hyjchina/80481314
#region 命名空间
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using Infragistics.Documents.Excel;
#endregion
namespace XL.OFFIC.OfficeClass
{
#region 页面设置
/// <summary>
/// 页面设置(方向)
/// </summary>
public enum PageOrientation
{
/// <summary>
/// 横向
/// </summary>
Landscape,
/// <summary>
/// 纵向
/// </summary>
Portrait
}
/// <summary>
/// 纸张大小
/// </summary>
public enum PaperSize
{
/// <summary>
/// A2
/// </summary>
A2,
/// <summary>
/// A3
/// </summary>
A3,
/// <summary>
/// A4
/// </summary>
A4,
/// <summary>
/// A5
/// </summary>
A5,
/// <summary>
/// A6
/// </summary>
A6,
/// <summary>
/// B4
/// </summary>
B4JIS,
/// <summary>
/// B5
/// </summary>
B5JIS,
/// <summary>
/// JapanesePost
/// </summary>
JapanesePost
}
#endregion
#region 常用常量
static class ExcelDataConst
{
public const int colWidth = 256; //宽度转换比
public const int rowHeight = 20; //高度转换比
public const double pageMargin = 0.4; //边距转换比
}
#endregion
#region 对齐方式
/// <summary>
/// 对齐方式
/// </summary>
public enum ExcelHAlign
{
/// <summary>
/// 左对齐
/// </summary>
Left,
/// <summary>
/// 居中对齐
/// </summary>
Center,
/// <summary>
/// 右对齐
/// </summary>
Right
}
#endregion
/// <summary>
/// UltraExcel操作类
/// </summary>
public class UltraExcelClass
{
#region 私有成员
private Workbook wBook; //工作本
private Worksheet wSheet; //工作簿
private string templateFile = string.Empty; //临时文件全名称
private int defaultColumnsWidth = 10 * ExcelDataConst.colWidth; //默认列宽
private int defaultZoom = 100; //默认页面设置(缩放)
private int defaultRowsHeight = 10 * ExcelDataConst.rowHeight; //默认行高
private PageOrientation pageOrientation = PageOrientation.Portrait; //默认页面设置(方向)
private PaperSize paperSize = PaperSize.A4; //默认纸张
private bool centerHorizontally = false; //默认水平居中方式
private bool centerVertically = false; //默认垂直居中方式
private double pageLeftMargin = 1.9 * ExcelDataConst.pageMargin; //默认左边距
private double pageRightMargin = 1.9 * ExcelDataConst.pageMargin; //默认右边距
private double pageTopMargin = 2.5 * ExcelDataConst.pageMargin; //默认上边距
private double pageBottomMargin = 2.5 * ExcelDataConst.pageMargin; //默认下边距
private string columnNameList = "ABCDEFGHIJKLMNOPQRSTUVWSYZ";
#endregion
#region [属性]WorkBook
/// <summary>
/// WorkBook
/// </summary>
public Workbook WorkBook
{
get
{
return this.wBook;
}
set
{
wBook = value;
}
}
#endregion
#region [属性]WorkSheet
/// <summary>
/// WorkSheet
/// </summary>
public Worksheet WorkSheet
{
get
{
return this.wSheet;
}
set
{
wSheet = value;
}
}
#endregion
#region [属性]Excel临时文件名
/// <summary>
/// Excel临时文件名（只针对使用Excel模板导出方式）
/// </summary>
public string TemplateFile
{
get
{
return this.templateFile;
}
}
#endregion
#region [属性]默认列宽
/// <summary>
/// 默认列宽
/// </summary>
public int DefaultColumnsWidth
{
set
{
this.defaultColumnsWidth = value * ExcelDataConst.colWidth;
this.SetDefaultColWidth();
}
}
#endregion
#region [属性]页面设置（缩放）
/// <summary>
/// 页面设置（缩放）
/// </summary>
public int DefaultZoom
{
set
{
this.defaultZoom = value;
this.SetPageZoom();
}
}
#endregion
#region [属性]默认行号
/// <summary>
/// 默认行号
/// </summary>
public int DefaultRowsHeight
{
set
{
this.defaultRowsHeight = value * ExcelDataConst.rowHeight;
this.SetDefaultRowHeight();
}
}
#endregion
#region [属性]页面设置(方向)
/// <summary>
/// 页面设置(方向)
/// </summary>
public PageOrientation PageOrientation
{
set
{
this.pageOrientation = value;
this.SetPageOrientation();
}
}
#endregion
#region [属性]页面设置(纸张大小)
/// <summary>
/// 页面设置(纸张大小)
/// </summary>
public PaperSize PaperSize
{
set
{
this.paperSize = value;
this.SetPaperSize();
}
}
#endregion
#region [属性]页面设置(水平居中方式)
/// <summary>
/// 页面设置(水平居中方式)
/// </summary>
public bool CenterHorizontally
{
set
{
this.centerHorizontally = value;
this.SetCenterHorizontally();
}
}
#endregion
#region [属性]页面设置(垂直居中方式)
/// <summary>
/// 页面设置(垂直居中方式)
/// </summary>
public bool CenterVertically
{
set
{
this.centerVertically = value;
this.SetCenterVertically();
}
}
#endregion
#region [属性]页面设置(页边距)
/// <summary>
/// 页面设置(页边距)
/// </summary>
public double PageLeftMargin
{
set
{
this.pageLeftMargin = value * ExcelDataConst.pageMargin;
this.SetPageMargin();
}
}
#endregion
#region [属性]页面设置(页边距)
/// <summary>
/// 页面设置(页边距)
/// </summary>
public double PageRightMargin
{
set
{
this.pageRightMargin = value * ExcelDataConst.pageMargin;
this.SetPageMargin();
}
}
#endregion
#region [属性]页面设置(页边距)
/// <summary>
/// 页面设置(页边距)
/// </summary>
public double PageTopMargin
{
set
{
this.pageTopMargin = value * ExcelDataConst.pageMargin;
this.SetPageMargin();
}
}
#endregion
#region [属性]页面设置(页边距)
/// <summary>
/// 页面设置(页边距)
/// </summary>
public double PageBottomMargin
{
set
{
this.pageBottomMargin = value * ExcelDataConst.pageMargin;
this.SetPageMargin();
}
}
#endregion
#region [方法]字符串反转
/// <summary>
/// 字符串反转（如：ABC转为CBA）
/// </summary>
/// <param name="str">字符串</param>
/// <returns>结果字符串</returns>
private string StringReverse(string str)
{
return new string(str.ToCharArray().Reverse<char>().ToArray<char>());
}
#endregion
#region [方法]构造函数
/// <summary>
/// 构造函数
/// </summary>
/// <param name="tempFileFullName">临时全名称</param>
public UltraExcelClass(string tempFileFullName)
{
try
{
this.templateFile = tempFileFullName;
this.wBook = new Workbook();
this.wBook = Workbook.Load(this.templateFile);
this.wSheet = this.wBook.Worksheets[0];
this.wBook.WindowOptions.SelectedWorksheet = this.wSheet;
}
catch (Exception xe)
{
throw new Exception(xe.Message);
}
}
#endregion
#region [方法]设置默认列宽
/// <summary>
/// 设置默认列宽
/// </summary>
private void SetDefaultColWidth()
{
this.wSheet.DefaultColumnWidth = defaultColumnsWidth;
}
#endregion
#region [方法]设置默认行高
/// <summary>
/// 设置默认行高
/// </summary>
private void SetDefaultRowHeight()
{
this.wSheet.DefaultRowHeight = defaultRowsHeight;
}
#endregion
#region [方法]页面设置(方向)
/// <summary>
/// 页面设置(方向)
/// </summary>
private void SetPageOrientation()
{
if (pageOrientation == PageOrientation.Landscape)
{
this.wSheet.PrintOptions.Orientation = Infragistics.Documents.Excel.Orientation.Landscape;
}
else
{
this.wSheet.PrintOptions.Orientation = Infragistics.Documents.Excel.Orientation.Portrait;
}
}
#endregion
#region [方法]页面设置(纸张大小)
/// <summary>
/// 页面设置(纸张大小)
/// </summary>
private void SetPaperSize()
{
switch (paperSize)
{
case PaperSize.A2:
this.wSheet.PrintOptions.PaperSize = Infragistics.Documents.Excel.PaperSize.A2;
break;
case PaperSize.A3:
this.wSheet.PrintOptions.PaperSize = Infragistics.Documents.Excel.PaperSize.A3;
break;
case PaperSize.A4:
this.wSheet.PrintOptions.PaperSize = Infragistics.Documents.Excel.PaperSize.A4;
break;
case PaperSize.A5:
this.wSheet.PrintOptions.PaperSize = Infragistics.Documents.Excel.PaperSize.A5;
break;
case PaperSize.A6:
this.wSheet.PrintOptions.PaperSize = Infragistics.Documents.Excel.PaperSize.A6;
break;
case PaperSize.B4JIS:
this.wSheet.PrintOptions.PaperSize = Infragistics.Documents.Excel.PaperSize.B4JIS;
break;
case PaperSize.B5JIS:
this.wSheet.PrintOptions.PaperSize = Infragistics.Documents.Excel.PaperSize.B5JIS;
break;
case PaperSize.JapanesePost:
this.wSheet.PrintOptions.PaperSize = Infragistics.Documents.Excel.PaperSize.JapanesePostcard;
break;
}
}
#endregion
#region [方法]页面设置(水平居中方式)
/// <summary>
/// 页面设置(水平居中方式)
/// </summary>
private void SetCenterHorizontally()
{
this.wSheet.PrintOptions.CenterHorizontally = centerHorizontally;
}
#endregion
#region [方法]页面设置(垂直居中方式)
/// <summary>
/// 页面设置(垂直居中方式)
/// </summary>
private void SetCenterVertically()
{
this.wSheet.PrintOptions.CenterVertically = centerVertically;
}
#endregion
#region [方法]页面设置(页边距)
/// <summary>
/// 页面设置(页边距)
/// </summary>
private void SetPageMargin()
{
this.wSheet.PrintOptions.LeftMargin = this.pageLeftMargin;
this.wSheet.PrintOptions.RightMargin = this.pageRightMargin;
this.wSheet.PrintOptions.TopMargin = this.pageTopMargin;
this.wSheet.PrintOptions.BottomMargin = this.pageBottomMargin;
}
#endregion
#region [方法]页面设置(缩放)
/// <summary>
/// 页面设置(缩放)
/// </summary>
private void SetPageZoom()
{
this.wSheet.PrintOptions.ScalingType = ScalingType.UseScalingFactor;
this.wSheet.PrintOptions.ScalingFactor = this.defaultZoom;
}
#endregion
#region [方法]添加工作簿
/// <summary>
/// 添加工作簿
/// </summary>
public void AddSheet()
{
int num = this.wBook.Worksheets.Count + 1;
string sheetName = "Sheet" + num.ToString();
while (this.IsExistTheSheet(sheetName))
{
num++;
sheetName = "Sheet" + num.ToString();
}
this.wBook.WindowOptions.SelectedWorksheet = this.wBook.Worksheets.Add(sheetName);
this.wSheet = this.wBook.WindowOptions.SelectedWorksheet;
this.SetDefaultColWidth();
}
/// <summary>
/// 添加工作簿
/// </summary>
/// <param name="sheetName">sheet名称</param>
public void AddSheet(string sheetName)
{
if (this.IsExistTheSheet(sheetName))
{
throw new Exception("存在同名的Sheet页！");
}
this.wBook.WindowOptions.SelectedWorksheet = this.wBook.Worksheets.Add(sheetName);
this.wSheet = this.wBook.WindowOptions.SelectedWorksheet;
this.SetDefaultColWidth();
}
#endregion
#region [方法]是否存在相同的工作簿
/// <summary>
/// 是否存在相同的工作簿
/// </summary>
/// <param name="sheetName">sheet名称</param>
/// <returns>存在：true 不存在：false</returns>
private bool IsExistTheSheet(string sheetName)
{
for (int i = 0; i < this.wBook.Worksheets.Count; i++)
{
if (this.wBook.Worksheets[i].Name == sheetName)
{
return true;
}
}
return false;
}
#endregion
#region [方法]获取列索引
private int GetColumnIndex(string cellName)
{
double colIndex = 0;
string colName = string.Empty;
string colItem = string.Empty;
for (int iLoop = 0; iLoop < cellName.Length; iLoop++)
{
colItem = cellName.Substring(iLoop, 1);
int indexTxt = this.columnNameList.IndexOf(colItem);
if (indexTxt == -1)
{
break;
}
else
{
colName = cellName.Substring(0, iLoop + 1);
}
}
colName = this.StringReverse(colName);
for (int iLoop = 0; iLoop < colName.Length; iLoop++)
{
int indexTxt = this.columnNameList.IndexOf(colName.Substring(iLoop, 1));
if (indexTxt > -1)
{
colIndex += Math.Pow(this.columnNameList.Length, iLoop) * (indexTxt + 1);
}
else
{
break;
}
}
return int.Parse(colIndex.ToString()) - 1;
}
#endregion
#region [方法]单元格赋值
/// <summary>
/// 单元格赋值
/// </summary>
/// <param name="cellName">单元格名称(如：A1)</param>
/// <param name="cellValue">单元格值</param>
public void SetCellValueTxt(string cellName, string cellValue)
{
int rowIndex = this.GetRowIndex(cellName);
int colIndex = this.GetColumnIndex(cellName);
if (cellValue == null)
{
this.wSheet.Rows[rowIndex].Cells[colIndex].Value = string.Empty;
}
else
{
IWorksheetCellFormat formatObj = this.wSheet.Rows[rowIndex].Cells[colIndex].CellFormat;
this.wSheet.Rows[rowIndex].Cells[colIndex].Value = cellValue;
this.wSheet.Rows[rowIndex].Cells[colIndex].CellFormat.Alignment = formatObj.Alignment;
}
}
/// <summary>
/// 单元格赋值
/// </summary>
/// <param name="cellName">单元格名称(如：A1)</param>
/// <param name="cellValue">单元格值</param>
public void SetCellValueNum(string cellName, string cellValue)
{
int rowIndex = this.GetRowIndex(cellName);
int colIndex = this.GetColumnIndex(cellName);
if (cellValue != null)
{
this.wSheet.Rows[rowIndex].Cells[colIndex].Value = double.Parse(cellValue);
}
}
/// <summary>
/// 设置单元格样式
/// </summary>
/// <param name="startCellName">起始单元格(如：A1)</param>
/// <param name="endCellName">结束单元格(如：B1)</param>
/// <param name="isBold">加粗</param>
public void SetCellStyle(string startCellName, string endCellName, bool isBold)
{
int firstRow = this.GetRowIndex(startCellName);
int firstColumn = this.GetColumnIndex(startCellName);
int lastRow = this.GetRowIndex(endCellName);
int lastColumn = this.GetColumnIndex(endCellName);
int iLoop = firstRow > lastRow ? -1 : 1;
int jLoop = firstColumn > lastColumn ? -1 : 1;
int RowIndex = firstRow;
while (true)
{
int ColumnIndex = firstColumn;
while (true)
{
wSheet.Rows[RowIndex].Cells[ColumnIndex].CellFormat.Font.Bold = isBold ? ExcelDefaultableBoolean.True : ExcelDefaultableBoolean.False;
if (ColumnIndex == lastColumn) break;
ColumnIndex += jLoop;
}
if (RowIndex == lastRow) break;
RowIndex += iLoop;
}
}
/// <summary>
/// 设置单元格样式
/// </summary>
/// <param name="startCellName">起始单元格(如：A1)</param>
/// <param name="endCellName">结束单元格(如：B1)</param>
/// <param name="vAlign">对齐方式</param>
public void SetCellStyle(string startCellName, string endCellName, VerticalCellAlignment vAlign)
{
int firstRow = this.GetRowIndex(startCellName);
int firstColumn = this.GetColumnIndex(startCellName);
int lastRow = this.GetRowIndex(endCellName);
int lastColumn = this.GetColumnIndex(endCellName);
int iLoop = firstRow > lastRow ? -1 : 1;
int jLoop = firstColumn > lastColumn ? -1 : 1;
int RowIndex = firstRow;
while (true)
{
int ColumnIndex = firstColumn;
while (true)
{
wSheet.Rows[RowIndex].Cells[ColumnIndex].CellFormat.VerticalAlignment = vAlign;
if (ColumnIndex == lastColumn) break;
ColumnIndex += jLoop;
}
if (RowIndex == lastRow) break;
RowIndex += iLoop;
}
}
/// <summary>
/// 设置单元格样式
/// </summary>
/// <param name="cellName">单元格名称(如：A1)</param>
/// <param name="fontName">字体，如：宋体</param>
/// <param name="fontHeight">字号</param>
public void SetCellStyle(string cellName, string fontName, int fontHeight)
{
int rowIndex = this.GetRowIndex(cellName);
int colIndex = this.GetColumnIndex(cellName);
wSheet.Rows[rowIndex].Cells[colIndex].CellFormat.Font.Name = fontName;
wSheet.Rows[rowIndex].Cells[colIndex].CellFormat.Font.Height = fontHeight;
}
/// <summary>
/// 设置单元格样式
/// </summary>
/// <param name="cellName">单元格名称(如：A1)</param>
/// <param name="isBold">加粗</param>
public void SetCellStyle(string cellName, bool isBold)
{
int rowIndex = this.GetRowIndex(cellName);
int colIndex = this.GetColumnIndex(cellName);
wSheet.Rows[rowIndex].Cells[colIndex].CellFormat.Font.Bold = isBold ? ExcelDefaultableBoolean.True : ExcelDefaultableBoolean.False;
}
/// <summary>
/// 设置单元格样式
/// </summary>
/// <param name="cellName">单元格名称(如：A1)</param>
/// <param name="align">对齐方式</param>
public void SetCellStyle(string cellName, HorizontalCellAlignment align)
{
int rowIndex = this.GetRowIndex(cellName);
int colIndex = this.GetColumnIndex(cellName);
wSheet.Rows[rowIndex].Cells[colIndex].CellFormat.Alignment = align;
}
/// <summary>
/// 设置单元格样式
/// </summary>
/// <param name="cellName">单元格名称(如：A1)</param>
/// <param name="vAlign">对齐方式</param>
public void SetCellStyle(string cellName, VerticalCellAlignment vAlign)
{
int rowIndex = this.GetRowIndex(cellName);
int colIndex = this.GetColumnIndex(cellName);
wSheet.Rows[rowIndex].Cells[colIndex].CellFormat.VerticalAlignment = vAlign;
}
#endregion
#region [方法]设置行单元格样式
/// <summary>
/// 设置行单元格样式
/// </summary>
/// <param name="rowIndex">行号</param>
/// <param name="fontName">字体，如：宋体</param>
/// <param name="fontHeight">字号</param>
public void SetRowCellStyle(int rowIndex, string fontName, int fontHeight)
{
wSheet.Rows[rowIndex - 1].CellFormat.Font.Name = fontName;
wSheet.Rows[rowIndex - 1].CellFormat.Font.Height = fontHeight;
}
/// <summary>
/// 设置行单元格样式
/// </summary>
/// <param name="rowIndex">行号</param>
/// <param name="isBold">加粗</param>
public void SetRowCellStyle(int rowIndex, bool isBold)
{
wSheet.Rows[rowIndex - 1].CellFormat.Font.Bold = isBold ? ExcelDefaultableBoolean.True : ExcelDefaultableBoolean.False;
}
/// <summary>
/// 设置行单元格样式
/// </summary>
/// <param name="rowIndex">行号</param>
/// <param name="align">对齐方式</param>
public void SetRowCellStyle(int rowIndex, HorizontalCellAlignment align)
{
wSheet.Rows[rowIndex - 1].CellFormat.Alignment = align;
}
/// <summary>
/// 设置行单元格样式
/// </summary>
/// <param name="rowIndex">行号</param>
/// <param name="vAlign">对齐方式</param>
public void SetRowCellStyle(int rowIndex, VerticalCellAlignment vAlign)
{
wSheet.Rows[rowIndex - 1].CellFormat.VerticalAlignment = vAlign;
}
#endregion
#region [方法]设置列单元格样式
/// <summary>
/// 设置列单元格样式
/// </summary>
/// <param name="colName">列名</param>
/// <param name="fontName">字体，如：宋体</param>
/// <param name="fontHeight">字号</param>
public void SetColumnCellStyle(string colName, string fontName, int fontHeight)
{
int colIndex = this.GetColumnIndex(colName);
wSheet.Columns[colIndex].CellFormat.Font.Name = fontName;
wSheet.Columns[colIndex].CellFormat.Font.Height = fontHeight;
}
/// <summary>
/// 设置列单元格样式
/// </summary>
/// <param name="colName">列名</param>
/// <param name="isBold">加粗</param>
public void SetColumnCellStyle(string colName, bool isBold)
{
int colIndex = this.GetColumnIndex(colName);
wSheet.Columns[colIndex].CellFormat.Font.Bold = isBold ? ExcelDefaultableBoolean.True : ExcelDefaultableBoolean.False;
}
/// <summary>
/// 设置列单元格样式
/// </summary>
/// <param name="colName">列名</param>
/// <param name="align">对齐方式</param>
public void SetColumnCellStyle(string colName, HorizontalCellAlignment align)
{
int colIndex = this.GetColumnIndex(colName);
wSheet.Columns[colIndex].CellFormat.Alignment = align;
}
/// <summary>
/// 设置列单元格样式
/// </summary>
/// <param name="colName">列名</param>
/// <param name="vAlign">对齐方式</param>
public void SetColumnCellStyle(string colName, VerticalCellAlignment vAlign)
{
int colIndex = this.GetColumnIndex(colName);
wSheet.Columns[colIndex].CellFormat.VerticalAlignment = vAlign;
}
#endregion
#region [方法]设置行高
/// <summary>
/// 设置行高
/// </summary>
/// <param name="rowIndex">行号</param>
/// <param name="rowHeight">行高</param>
public void SetRowHeight(int rowIndex, int rowHeight)
{
wSheet.Rows[rowIndex - 1].Height = rowHeight;
}
#endregion
#region [方法]设置列宽
/// <summary>
/// 设置列宽
/// </summary>
/// <param name="colName">列名</param>
/// <param name="colWidth">宽度</param>
public void SetColWidth(string colName, int colWidth)
{
int colIndex = this.GetColumnIndex(colName);
wSheet.Columns[colIndex].Width = colWidth * ExcelDataConst.colWidth;
}
#endregion
#region [方法]合并单元格
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="startCellName">起始单元格(如：A1)</param>
/// <param name="endCellName">结束单元格(如：B1)</param>
public void MergedCells(string startCellName, string endCellName)
{
int firstRow = this.GetRowIndex(startCellName);
int firstColumn = this.GetColumnIndex(startCellName);
int lastRow = this.GetRowIndex(endCellName);
int lastColumn = this.GetColumnIndex(endCellName);
this.wSheet.MergedCellsRegions.Add(firstRow, firstColumn, lastRow, lastColumn);
}
#endregion
#region [方法]保护WorkSheet
/// <summary>
/// 保护WorkSheet
/// </summary>
/// <param name="isProtected">是否保护</param>
public void ProtectedWorkSheet(bool isProtected)
{
this.wSheet.Protected = isProtected;
}
#endregion
#region [方法]保护WorkBook
/// <summary>
/// 保护WorkBook
/// </summary>
/// <param name="isProtected">是否保护</param>
public void ProtectedWorkBook(bool isProtected)
{
this.wBook.Protected = isProtected;
}
#endregion
#region [方法]保存并关闭Excel文件
/// <summary>
/// 保存并关闭Excel文件
/// </summary>
public void SaveAndCloseFile()
{
this.wBook.Save(this.templateFile);
}
#endregion
}
}
扫一扫
3582
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
