ExcelHelper(Excel2007)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using System.Diagnostics;
using System.IO;
using System.Collections;
using System.Data;
namespace Core.Helper {
/// <summary>
/// excel操作类
/// 开发环境：2007
/// </summary>
public class ExcelHelper : IDisposable {
#region 构造函数
/// <summary>
/// 构造函数，将一个已有Excel工作簿作为模板，并指定输出路径
/// </summary>
/// <param name="templetFilePath">Excel模板文件路径</param>
/// <param name="outputFilePath">输出Excel文件路径</param>
public ExcelHelper(string templetFilePath, string outputFilePath) {
if (templetFilePath == null)
throw new Exception("Excel模板文件路径不能为空！");
if (outputFilePath == null)
throw new Exception("输出Excel文件路径不能为空！");
if (!File.Exists(templetFilePath))
throw new Exception("指定路径的Excel模板文件不存在！");
this.templetFile = templetFilePath;
this.outputFile = outputFilePath;
excelApp = new Excel.ApplicationClass();
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
excelApp.AlertBeforeOverwriting = false;
//打开模板文件，得到WorkBook对象
workBook = excelApp.Workbooks.Open(templetFile, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, Type.Missing, Type.Missing);
//得到WorkSheet对象
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
}
DateTime beforeTime;
DateTime afterTime;
/// <summary>
/// 构造函数，打开一个已有的工作簿
/// </summary>
/// <param name="fileName">Excel文件名</param>
public ExcelHelper(string fileName) {
if (!File.Exists(fileName))
throw new Exception("指定路径的Excel文件不存在！");
//创建一个Application对象并使其可见
beforeTime = DateTime.Now;
excelApp = new Excel.ApplicationClass();
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
excelApp.AlertBeforeOverwriting = false;
//打开一个WorkBook
workBook = excelApp.Workbooks.Open(fileName,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
afterTime = DateTime.Now;
//得到WorkSheet对象
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
}
/// <summary>
/// 构造函数，新建一个工作簿
/// </summary>
public ExcelHelper() {
beforeTime = DateTime.Now;
excelApp = new Excel.ApplicationClass();
afterTime = DateTime.Now;
excelApp.Visible = false;
//设置禁止弹出保存和覆盖的询问提示框
excelApp.DisplayAlerts = false;
excelApp.AlertBeforeOverwriting = false;
//新建一个WorkBook
workBook = excelApp.Workbooks.Add(Type.Missing);
//得到WorkSheet对象
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
}
#endregion
#region 成员变量
private string templetFile = null;
private string outputFile = null;
private object missing = System.Reflection.Missing.Value;
Excel.Application excelApp;
Excel.Workbook workBook;
Excel.Worksheet workSheet;
Excel.Range range;
Excel.Range range1;
Excel.Range range2;
Excel.TextBox textBox;
private int sheetCount = 1; //WorkSheet数量
private string sheetPrefixName = "页";
#endregion
#region 公共属性
/// <summary>
/// WorkSheet前缀名，比如：前缀名为“页”，那么WorkSheet名称依次为“页-1，页-2...”
/// </summary>
public string SheetPrefixName {
set { this.sheetPrefixName = value; }
}
/// <summary>
/// WorkSheet数量
/// </summary>
public int WorkSheetCount {
get { return workBook.Sheets.Count; }
}
/// <summary>
/// Excel模板文件路径
/// </summary>
public string TempletFilePath {
set { this.templetFile = value; }
}
/// <summary>
/// 输出Excel文件路径
/// </summary>
public string OutputFilePath {
set { this.outputFile = value; }
}
#endregion
#region 公共方法
#region Data Export Methods
/// <summary>
/// 将DataTable数据写入Excel文件（自动分页）
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="rows">每个WorkSheet写入多少行数据</param>
/// <param name="top">表格数据起始行索引</param>
/// <param name="left">表格数据起始列索引</param>
public void DataTableToExcel(DataTable dt, int rows, int top, int left) {
int rowCount = dt.Rows.Count; //DataTable行数
int colCount = dt.Columns.Count; //DataTable列数
sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数
// StringBuilder sb;
//复制sheetCount-1个WorkSheet对象
for (int i = 1; i < sheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Copy(missing, workBook.Worksheets[i]);
}
for (int i = 1; i <= sheetCount; i++) {
int startRow = (i - 1) * rows; //记录起始行索引
int endRow = i * rows; //记录结束行索引
//若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
if (i == sheetCount)
endRow = rowCount;
//获取要写入数据的WorkSheet对象，并重命名
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Name = sheetPrefixName + "-" + i.ToString();
//将dt中的数据写入WorkSheet
// for(int j=0;j<endRow-startRow;j++)
// {
// for(int k=0;k<colCount;k++)
// {
// workSheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();
// }
// }
//利用二维数组批量写入
int row = endRow - startRow;
object[,] ss = new object[row, colCount];
for (int j = 0; j < row; j++) {
for (int k = 0; k < colCount; k++) {
ss[j, k] = dt.Rows[startRow + j][k].ToString();
}
}
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(row, colCount);
range.Value2 = ss;
#region 利用Windwo粘贴板批量拷贝数据（在Web下面行不通）
/*sb = new StringBuilder();
for(int j=0;j<endRow-startRow;j++)
{
for(int k=0;k<colCount;k++)
{
sb.Append( dt.Rows[startRow + j][k].ToString() );
sb.Append("\t");
}
sb.Append("\n");
}
System.Windows.Forms.Clipboard.SetDataObject(sb.ToString());
range = (Excel.Range)workSheet.Cells[top,left];
workSheet.Paste(range,false);*/
#endregion
}
}
/// <summary>
/// 将DataTable数据写入Excel文件（不分页）
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="top">表格数据起始行索引</param>
/// <param name="left">表格数据起始列索引</param>
public void DataTableToExcel(DataTable dt, int top, int left) {
int rowCount = dt.Rows.Count; //DataTable行数
int colCount = dt.Columns.Count; //DataTable列数
//利用二维数组批量写入
object[,] arr = new object[rowCount, colCount];
for (int j = 0; j < rowCount; j++) {
for (int k = 0; k < colCount; k++) {
arr[j, k] = dt.Rows[j][k].ToString();
}
}
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(rowCount, colCount);
range.Value2 = arr;
}
public void DataTableToExcel(DataTable dt, int top, int left, Excel.Worksheet objSheet) {
int rowCount = dt.Rows.Count; //DataTable行数
int colCount = dt.Columns.Count; //DataTable列数
//利用二维数组批量写入
object[,] arr = new object[rowCount, colCount];
for (int j = 0; j < rowCount; j++) {
for (int k = 0; k < colCount; k++) {
arr[j, k] = dt.Rows[j][k].ToString();
}
}
range = (Excel.Range)objSheet.Cells[top, left];
range = range.get_Resize(rowCount, colCount);
range.Value2 = arr;
}
/// <summary>
/// 将DataTable数据写入Excel文件（自动分页，并指定要合并的列索引）
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="rows">每个WorkSheet写入多少行数据</param>
/// <param name="top">表格数据起始行索引</param>
/// <param name="left">表格数据起始列索引</param>
/// <param name="mergeColumnIndex">DataTable中要合并相同行的列索引，从0开始</param>
public void DataTableToExcel(DataTable dt, int rows, int top, int left, int mergeColumnIndex) {
int rowCount = dt.Rows.Count; //源DataTable行数
int colCount = dt.Columns.Count; //源DataTable列数
sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数
// StringBuilder sb;
//复制sheetCount-1个WorkSheet对象
for (int i = 1; i < sheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Copy(missing, workBook.Worksheets[i]);
}
for (int i = 1; i <= sheetCount; i++) {
int startRow = (i - 1) * rows; //记录起始行索引
int endRow = i * rows; //记录结束行索引
//若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
if (i == sheetCount)
endRow = rowCount;
//获取要写入数据的WorkSheet对象，并重命名
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Name = sheetPrefixName + "-" + i.ToString();
//将dt中的数据写入WorkSheet
// for(int j=0;j<endRow-startRow;j++)
// {
// for(int k=0;k<colCount;k++)
// {
// workSheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();
// }
// }
//利用二维数组批量写入
int row = endRow - startRow;
object[,] ss = new object[row, colCount];
for (int j = 0; j < row; j++) {
for (int k = 0; k < colCount; k++) {
ss[j, k] = dt.Rows[startRow + j][k].ToString();
}
}
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(row, colCount);
range.Value2 = ss;
//合并相同行
this.MergeRows(workSheet, left + mergeColumnIndex, top, rows);
}
}
/// <summary>
/// 将二维数组数据写入Excel文件（自动分页）
/// </summary>
/// <param name="arr">二维数组</param>
/// <param name="rows">每个WorkSheet写入多少行数据</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
public void ArrayToExcel(object[,] arr, int rows, int top, int left) {
int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
int colCount = arr.GetLength(1); //二维数据列数（二维长度）
sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数
//复制sheetCount-1个WorkSheet对象
for (int i = 1; i < sheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Copy(missing, workBook.Worksheets[i]);
}
//将二维数组数据写入Excel
for (int i = sheetCount; i >= 1; i--) {
int startRow = (i - 1) * rows; //记录起始行索引
int endRow = i * rows; //记录结束行索引
//若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
if (i == sheetCount)
endRow = rowCount;
//获取要写入数据的WorkSheet对象，并重命名
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Name = sheetPrefixName + "-" + i.ToString();
//将二维数组中的数据写入WorkSheet
// for(int j=0;j<endRow-startRow;j++)
// {
// for(int k=0;k<colCount;k++)
// {
// workSheet.Cells[top + j,left + k] = arr[startRow + j,k];
// }
// }
//利用二维数组批量写入
int row = endRow - startRow;
object[,] ss = new object[row, colCount];
for (int j = 0; j < row; j++) {
for (int k = 0; k < colCount; k++) {
ss[j, k] = arr[startRow + j, k];
}
}
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(row, colCount);
range.Value2 = ss;
}
}//end ArrayToExcel
/// <summary>
/// 将二维数组数据写入Excel文件（不分页）
/// </summary>
/// <param name="arr">二维数组</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
public void ArrayToExcel(object[,] arr, int top, int left) {
int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
int colCount = arr.GetLength(1); //二维数据列数（二维长度）
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(rowCount, colCount);
range.FormulaArray = arr;
}//end ArrayToExcel
/// <summary>
/// 将二维数组数据写入Excel文件（不分页）
/// </summary>
/// <param name="arr">二维数组</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
/// <param name="isFormula">填充的数据是否需要计算</param>
public void ArrayToExcel(object[,] arr, int top, int left, bool isFormula) {
int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
int colCount = arr.GetLength(1); //二维数据列数（二维长度）
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(rowCount, colCount);
//注意：使用range.FormulaArray写合并的单元格会出问题
if (isFormula)
range.FormulaArray = arr;
else
range.Value2 = arr;
}//end ArrayToExcel
/// <summary>
/// 将二维数组数据写入Excel文件（不分页），合并指定列的相同行
/// </summary>
/// <param name="arr">二维数组</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
/// <param name="isFormula">填充的数据是否需要计算</param>
/// <param name="mergeColumnIndex">需要合并行的列索引</param>
public void ArrayToExcel(object[,] arr, int top, int left, bool isFormula, int mergeColumnIndex) {
int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
int colCount = arr.GetLength(1); //二维数据列数（二维长度）
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(rowCount, colCount);
//注意：使用range.FormulaArray写合并的单元格会出问题
if (isFormula)
range.FormulaArray = arr;
else
range.Value2 = arr;
this.MergeRows(workSheet, mergeColumnIndex, top, rowCount);
}//end ArrayToExcel
/// <summary>
/// 将二维数组数据写入Excel文件（不分页）
/// </summary>
/// <param name="sheetIndex">工作表索引</param>
/// <param name="arr">二维数组</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
public void ArrayToExcel(int sheetIndex, object[,] arr, int top, int left) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
// 改变当前工作表
this.workSheet = (Excel.Worksheet)this.workBook.Sheets.get_Item(sheetIndex);
int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
int colCount = arr.GetLength(1); //二维数据列数（二维长度）
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(rowCount, colCount);
range.Value2 = arr;
}//end ArrayToExcel
/// <summary>
/// 将二维数组数据写入Excel文件（自动分页，并指定要合并的列索引）
/// </summary>
/// <param name="arr">二维数组</param>
/// <param name="rows">每个WorkSheet写入多少行数据</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
/// <param name="mergeColumnIndex">数组的二维索引，相当于DataTable的列索引，索引从0开始</param>
public void ArrayToExcel(object[,] arr, int rows, int top, int left, int mergeColumnIndex) {
int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
int colCount = arr.GetLength(1); //二维数据列数（二维长度）
sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数
//复制sheetCount-1个WorkSheet对象
for (int i = 1; i < sheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Copy(missing, workBook.Worksheets[i]);
}
//将二维数组数据写入Excel
for (int i = sheetCount; i >= 1; i--) {
int startRow = (i - 1) * rows; //记录起始行索引
int endRow = i * rows; //记录结束行索引
//若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
if (i == sheetCount)
endRow = rowCount;
//获取要写入数据的WorkSheet对象，并重命名
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Name = sheetPrefixName + "-" + i.ToString();
//将二维数组中的数据写入WorkSheet
for (int j = 0; j < endRow - startRow; j++) {
for (int k = 0; k < colCount; k++) {
workSheet.Cells[top + j, left + k] = arr[startRow + j, k];
}
}
//利用二维数组批量写入
int row = endRow - startRow;
object[,] ss = new object[row, colCount];
for (int j = 0; j < row; j++) {
for (int k = 0; k < colCount; k++) {
ss[j, k] = arr[startRow + j, k];
}
}
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(row, colCount);
range.Value2 = ss;
//合并相同行
this.MergeRows(workSheet, left + mergeColumnIndex, top, rows);
}
}//end ArrayToExcel
#endregion
#region WorkSheet Methods
/// <summary>
/// 改变当前工作表
/// </summary>
/// <param name="sheetIndex">工作表索引</param>
public void ChangeCurrentWorkSheet(int sheetIndex) {
//若指定工作表索引超出范围，则不改变当前工作表
if (sheetIndex < 1)
return;
if (sheetIndex > this.WorkSheetCount)
return;
this.workSheet = (Excel.Worksheet)this.workBook.Sheets.get_Item(sheetIndex);
}
/// <summary>
/// 隐藏指定名称的工作表
/// </summary>
/// <param name="sheetName">工作表名称</param>
public void HiddenWorkSheet(string sheetName) {
try {
Excel.Worksheet sheet = null;
for (int i = 1; i <= this.WorkSheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == sheetName)
sheet = workSheet;
}
if (sheet != null)
sheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
else {
this.KillExcelProcess(false);
throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 隐藏指定索引的工作表
/// </summary>
/// <param name="sheetIndex"></param>
public void HiddenWorkSheet(int sheetIndex) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try {
Excel.Worksheet sheet = null;
sheet = (Excel.Worksheet)workBook.Sheets.get_Item(sheetIndex);
sheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 在指定名称的工作表后面拷贝指定个数的该工作表的副本，并重命名
/// </summary>
/// <param name="sheetName">工作表名称</param>
/// <param name="sheetCount">工作表个数</param>
public void CopyWorkSheets(string sheetName, int sheetCount) {
try {
Excel.Worksheet sheet = null;
int sheetIndex = 0;
for (int i = 1; i <= this.WorkSheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == sheetName) {
sheet = workSheet;
sheetIndex = workSheet.Index;
}
}
if (sheet != null) {
for (int i = sheetCount; i >= 1; i--) {
sheet.Copy(this.missing, sheet);
}
//重命名
for (int i = sheetIndex; i <= sheetIndex + sheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
workSheet.Name = sheetName + "-" + Convert.ToString(i - sheetIndex + 1);
}
}
else {
this.KillExcelProcess(false);
throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 将一个工作表拷贝到另一个工作表后面，并重命名
/// </summary>
/// <param name="srcSheetIndex">拷贝源工作表索引</param>
/// <param name="aimSheetIndex">参照位置工作表索引，新工作表拷贝在该工作表后面</param>
/// <param name="newSheetName"></param>
public void CopyWorkSheet(int srcSheetIndex, int aimSheetIndex, string newSheetName) {
if (srcSheetIndex > this.WorkSheetCount || aimSheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try {
Excel.Worksheet srcSheet = (Excel.Worksheet)workBook.Sheets.get_Item(srcSheetIndex);
Excel.Worksheet aimSheet = (Excel.Worksheet)workBook.Sheets.get_Item(aimSheetIndex);
srcSheet.Copy(this.missing, aimSheet);
//重命名
workSheet = (Excel.Worksheet)aimSheet.Next; //获取新拷贝的工作表
workSheet.Name = newSheetName;
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 根据名称删除工作表
/// </summary>
/// <param name="sheetName"></param>
public void DeleteWorkSheet(string sheetName) {
try {
Excel.Worksheet sheet = null;
//找到名称位sheetName的工作表
for (int i = 1; i <= this.WorkSheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == sheetName) {
sheet = workSheet;
}
}
if (sheet != null) {
sheet.Delete();
}
else {
this.KillExcelProcess(false);
throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 根据索引删除工作表
/// </summary>
/// <param name="sheetIndex"></param>
public void DeleteWorkSheet(int sheetIndex) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try {
Excel.Worksheet sheet = null;
sheet = (Excel.Worksheet)workBook.Sheets.get_Item(sheetIndex);
sheet.Delete();
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
#endregion
#region TextBox Methods
/// <summary>
/// 向指定文本框写入数据，对每个WorkSheet操作
/// </summary>
/// <param name="textboxName">文本框名称</param>
/// <param name="text">要写入的文本</param>
public void SetTextBox(string textboxName, string text) {
for (int i = 1; i <= this.WorkSheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
try {
textBox = (Excel.TextBox)workSheet.TextBoxes(textboxName);
textBox.Text = text;
}
catch {
this.KillExcelProcess(false);
throw new Exception("不存在ID为\"" + textboxName + "\"的文本框！");
}
}
}
/// <summary>
/// 向指定文本框写入数据，对指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">工作表索引</param>
/// <param name="textboxName">文本框名称</param>
/// <param name="text">要写入的文本</param>
public void SetTextBox(int sheetIndex, string textboxName, string text) {
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
try {
textBox = (Excel.TextBox)workSheet.TextBoxes(textboxName);
textBox.Text = text;
}
catch {
this.KillExcelProcess(false);
throw new Exception("不存在ID为\"" + textboxName + "\"的文本框！");
}
}
/// <summary>
/// 向文本框写入数据，对每个WorkSheet操作
/// </summary>
/// <param name="ht">Hashtable的键值对保存文本框的ID和数据</param>
public void SetTextBoxes(Hashtable ht) {
if (ht.Count == 0) return;
for (int i = 1; i <= this.WorkSheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
foreach (DictionaryEntry dic in ht) {
try {
textBox = (Excel.TextBox)workSheet.TextBoxes(dic.Key);
textBox.Text = dic.Value.ToString();
}
catch {
this.KillExcelProcess(false);
throw new Exception("不存在ID为\"" + dic.Key.ToString() + "\"的文本框！");
}
}
}
}
/// <summary>
/// 向文本框写入数据，对指定WorkSheet操作
/// </summary>
/// <param name="ht">Hashtable的键值对保存文本框的ID和数据</param>
public void SetTextBoxes(int sheetIndex, Hashtable ht) {
if (ht.Count == 0) return;
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
foreach (DictionaryEntry dic in ht) {
try {
textBox = (Excel.TextBox)workSheet.TextBoxes(dic.Key);
textBox.Text = dic.Value.ToString();
}
catch {
this.KillExcelProcess(false);
throw new Exception("不存在ID为\"" + dic.Key.ToString() + "\"的文本框！");
}
}
}
#endregion
#region Cell Methods
/// <summary>
/// 向单元格写入数据，对当前WorkSheet操作
/// </summary>
/// <param name="rowIndex">行索引</param>
/// <param name="columnIndex">列索引</param>
/// <param name="text">要写入的文本值</param>
public void SetCells(int rowIndex, int columnIndex, string text) {
try {
workSheet.Cells[rowIndex, columnIndex] = text;
}
catch {
this.KillExcelProcess(false);
throw new Exception("向单元格[" + rowIndex + "," + columnIndex + "]写数据出错！");
}
}
/// <summary>
/// 向单元格写入数据，对指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">工作表索引</param>
/// <param name="rowIndex">行索引</param>
/// <param name="columnIndex">列索引</param>
/// <param name="text">要写入的文本值</param>
public void SetCells(int sheetIndex, int rowIndex, int columnIndex, string text) {
try {
this.ChangeCurrentWorkSheet(sheetIndex); //改变当前工作表为指定工作表
workSheet.Cells[rowIndex, columnIndex] = text;
}
catch {
this.KillExcelProcess(false);
throw new Exception("向单元格[" + rowIndex + "," + columnIndex + "]写数据出错！");
}
}
/// <summary>
/// 向单元格写入数据，对每个WorkSheet操作
/// </summary>
/// <param name="ht">Hashtable的键值对保存单元格的位置索引（行索引和列索引用“,”隔开）和数据</param>
public void SetCells(Hashtable ht) {
int rowIndex;
int columnIndex;
string position;
if (ht.Count == 0) return;
for (int i = 1; i <= this.WorkSheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
foreach (DictionaryEntry dic in ht) {
try {
position = dic.Key.ToString();
rowIndex = Convert.ToInt32(position.Split(',')[0]);
columnIndex = Convert.ToInt32(position.Split(',')[1]);
workSheet.Cells[rowIndex, columnIndex] = dic.Value;
}
catch {
this.KillExcelProcess(false);
throw new Exception("向单元格[" + dic.Key + "]写数据出错！");
}
}
}
}
/// <summary>
/// 向单元格写入数据，对指定WorkSheet操作
/// </summary>
/// <param name="ht">Hashtable的键值对保存单元格的位置索引（行索引和列索引用“,”隔开）和数据</param>
public void SetCells(int sheetIndex, Hashtable ht) {
int rowIndex;
int columnIndex;
string position;
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
if (ht.Count == 0) return;
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
foreach (DictionaryEntry dic in ht) {
try {
position = dic.Key.ToString();
rowIndex = Convert.ToInt32(position.Split(',')[0]);
columnIndex = Convert.ToInt32(position.Split(',')[1]);
workSheet.Cells[rowIndex, columnIndex] = dic.Value;
}
catch {
this.KillExcelProcess(false);
throw new Exception("向单元格[" + dic.Key + "]写数据出错！");
}
}
}
/// <summary>
/// 设置单元格为可计算的
/// </summary>
/// <remarks>
/// 如果Excel的单元格格式设置为数字，日期或者其他类型时，需要设置这些单元格的FormulaR1C1属性，
/// 否则写到这些单元格的数据将不会按照预先设定的格式显示
/// </remarks>
/// <param name="arr">保存单元格的位置索引（行索引和列索引用“,”隔开）和数据</param>
public void SetCells(int sheetIndex, string[] arr) {
int rowIndex;
int columnIndex;
string position;
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
if (arr.Length == 0) return;
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
for (int i = 0; i < arr.Length; i++) {
try {
position = arr[i];
rowIndex = Convert.ToInt32(position.Split(',')[0]);
columnIndex = Convert.ToInt32(position.Split(',')[1]);
Excel.Range cell = (Excel.Range)workSheet.Cells[rowIndex, columnIndex];
cell.FormulaR1C1 = cell.Text;
}
catch {
this.KillExcelProcess(false);
throw new Exception(string.Format("计算单元格{0}出错！", arr[i]));
}
}
}
/// <summary>
/// 向单元格写入数据，对指定WorkSheet操作
/// </summary>
/// <param name="ht">Hashtable的键值对保存单元格的位置索引（行索引和列索引用“,”隔开）和数据</param>
public void SetCells(string sheetName, Hashtable ht) {
int rowIndex;
int columnIndex;
string position;
Excel.Worksheet sheet = null;
int sheetIndex = 0;
if (ht.Count == 0) return;
try {
for (int i = 1; i <= this.WorkSheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == sheetName) {
sheet = workSheet;
sheetIndex = workSheet.Index;
}
}
if (sheet != null) {
foreach (DictionaryEntry dic in ht) {
try {
position = dic.Key.ToString();
rowIndex = Convert.ToInt32(position.Split(',')[0]);
columnIndex = Convert.ToInt32(position.Split(',')[1]);
sheet.Cells[rowIndex, columnIndex] = dic.Value;
}
catch {
this.KillExcelProcess(false);
throw new Exception("向单元格[" + dic.Key + "]写数据出错！");
}
}
}
else {
this.KillExcelProcess(false);
throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 合并单元格，并赋值，对每个WorkSheet操作
/// </summary>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="beginColumnIndex">开始列索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="endColumnIndex">结束列索引</param>
/// <param name="text">合并后Range的值</param>
public void MergeCells(int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text) {
for (int i = 1; i <= this.WorkSheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]);
range.ClearContents(); //先把Range内容清除，合并才不会出错
range.MergeCells = true;
range.Value2 = text;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
}
/// <summary>
/// 合并单元格，并赋值，对指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">WorkSheet索引</param>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="beginColumnIndex">开始列索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="endColumnIndex">结束列索引</param>
/// <param name="text">合并后Range的值</param>
public void MergeCells(int sheetIndex, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]);
range.ClearContents(); //先把Range内容清除，合并才不会出错
range.MergeCells = true;
range.Value2 = text;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
#endregion
#region Row Methods
/// <summary>
/// 将指定索引列的数据相同的行合并，对每个WorkSheet操作
/// </summary>
/// <param name="columnIndex">列索引</param>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="endRowIndex">结束行索引</param>
public void MergeRows(int columnIndex, int beginRowIndex, int endRowIndex) {
if (endRowIndex - beginRowIndex < 1)
return;
for (int i = 1; i <= this.WorkSheetCount; i++) {
int beginIndex = beginRowIndex;
int count = 0;
string text1;
string text2;
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
for (int j = beginRowIndex; j <= endRowIndex; j++) {
range = (Excel.Range)workSheet.Cells[j, columnIndex];
text1 = range.Text.ToString();
range = (Excel.Range)workSheet.Cells[j + 1, columnIndex];
text2 = range.Text.ToString();
if (text1 == text2) {
++count;
}
else {
if (count > 0) {
this.MergeCells(workSheet, beginIndex, columnIndex, beginIndex + count, columnIndex, text1);
}
beginIndex = j + 1; //设置开始合并行索引
count = 0; //计数器清0
}
}
}
}
/// <summary>
/// 将指定索引列的数据相同的行合并，对指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">WorkSheet索引</param>
/// <param name="columnIndex">列索引</param>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="endRowIndex">结束行索引</param>
public void MergeRows(int sheetIndex, int columnIndex, int beginRowIndex, int endRowIndex) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
if (endRowIndex - beginRowIndex < 1)
return;
int beginIndex = beginRowIndex;
int count = 0;
string text1;
string text2;
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
for (int j = beginRowIndex; j <= endRowIndex; j++) {
range = (Excel.Range)workSheet.Cells[j, columnIndex];
text1 = range.Text.ToString();
range = (Excel.Range)workSheet.Cells[j + 1, columnIndex];
text2 = range.Text.ToString();
if (text1 == text2) {
++count;
}
else {
if (count > 0) {
this.MergeCells(workSheet, beginIndex, columnIndex, beginIndex + count, columnIndex, text1);
}
beginIndex = j + 1; //设置开始合并行索引
count = 0; //计数器清0
}
}
}
/// <summary>
/// 插行（在指定行上面插入指定数量行）
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void InsertRows(int rowIndex, int count) {
try {
for (int n = 1; n <= this.WorkSheetCount; n++) {
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
range = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 0; i < count; i++) {
range.Insert(Excel.XlDirection.xlDown, missing);
}
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 插行（在指定WorkSheet指定行上面插入指定数量行）
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void InsertRows(int sheetIndex, int rowIndex, int count) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try {
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
range = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 0; i < count; i++) {
range.Insert(Excel.XlDirection.xlDown, missing);
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 复制行（在指定行下面复制指定数量行）
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void CopyRows(int rowIndex, int count) {
try {
for (int n = 1; n <= this.WorkSheetCount; n++) {
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
range1 = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 1; i <= count; i++) {
range2 = (Excel.Range)workSheet.Rows[rowIndex + i, this.missing];
range1.Copy(range2);
}
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 复制行（在指定WorkSheet指定行下面复制指定数量行）
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void CopyRows(int sheetIndex, int rowIndex, int count) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try {
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
range1 = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 1; i <= count; i++) {
range2 = (Excel.Range)workSheet.Rows[rowIndex + i, this.missing];
range1.Copy(range2);
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 删除行
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void DeleteRows(int rowIndex, int count) {
try {
for (int n = 1; n <= this.WorkSheetCount; n++) {
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
range = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 0; i < count; i++) {
range.Delete(Excel.XlDirection.xlDown);
}
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 删除行
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void DeleteRows(int sheetIndex, int rowIndex, int count) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try {
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
range = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 0; i < count; i++) {
range.Delete(Excel.XlDirection.xlDown);
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
#endregion
#region Column Methods
/// <summary>
/// 插列（在指定列右边插入指定数量列）
/// </summary>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void InsertColumns(int columnIndex, int count) {
try {
for (int n = 1; n <= this.WorkSheetCount; n++) {
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
range = (Excel.Range)workSheet.Columns[this.missing, columnIndex];
for (int i = 0; i < count; i++) {
range.Insert(Excel.XlDirection.xlDown, missing);
}
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 插列（在指定WorkSheet指定列右边插入指定数量列）
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void InsertColumns(int sheetIndex, int columnIndex, int count) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try {
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
range = (Excel.Range)workSheet.Columns[this.missing, columnIndex];
for (int i = 0; i < count; i++) {
range.Insert(Excel.XlDirection.xlDown, missing);
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 复制列（在指定列右边复制指定数量列）
/// </summary>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void CopyColumns(int columnIndex, int count) {
try {
for (int n = 1; n <= this.WorkSheetCount; n++) {
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
// range1 = (Excel.Range)workSheet.Columns[columnIndex,this.missing];
range1 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex) + "1", this.IntToLetter(columnIndex) + "10000");
for (int i = 1; i <= count; i++) {
// range2 = (Excel.Range)workSheet.Columns[this.missing,columnIndex + i];
range2 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex + i) + "1", this.IntToLetter(columnIndex + i) + "10000");
range1.Copy(range2);
}
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 复制列（在指定WorkSheet指定列右边复制指定数量列）
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void CopyColumns(int sheetIndex, int columnIndex, int count) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try {
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
// range1 = (Excel.Range)workSheet.Columns[Type.Missing,columnIndex];
range1 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex) + "1", this.IntToLetter(columnIndex) + "10000");
for (int i = 1; i <= count; i++) {
// range2 = (Excel.Range)workSheet.Columns[Type.Missing,columnIndex + i];
range2 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex + i) + "1", this.IntToLetter(columnIndex + i) + "10000");
range1.Copy(range2);
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 删除列
/// </summary>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void DeleteColumns(int columnIndex, int count) {
try {
for (int n = 1; n <= this.WorkSheetCount; n++) {
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
range = (Excel.Range)workSheet.Columns[this.missing, columnIndex];
for (int i = 0; i < count; i++) {
range.Delete(Excel.XlDirection.xlDown);
}
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 删除列
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void DeleteColumns(int sheetIndex, int columnIndex, int count) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try {
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
range = (Excel.Range)workSheet.Columns[this.missing, columnIndex];
for (int i = 0; i < count; i++) {
range.Delete(Excel.XlDirection.xlDown);
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
#region 通过列数获取excel列名
public String GetColumnName(int num) {
String[] COL_NAME =
{
"A", "B", "C", "D", "E", "F", "G", "H", "I", "J",
"K", "L", "M", "N", "O", "P", "Q", "R", "S", "T",
"U", "V", "W", "X", "Y", "Z"
};
int a = 0, b = num;
String col = "";
if (b < 26) {
col = COL_NAME[b];
}
else {
while (b >= 26) {
a = b % 26;
b = b / 26;
col = COL_NAME[a] + col;
}
col = COL_NAME[b - 1] + col;
}
return col;
}
#endregion
#endregion
#region Range Methods
/// <summary>
/// 将指定范围区域拷贝到目标区域
/// </summary>
/// <param name="sheetIndex">WorkSheet索引</param>
/// <param name="startCell">要拷贝区域的开始Cell位置（比如：A10）</param>
/// <param name="endCell">要拷贝区域的结束Cell位置（比如：F20）</param>
/// <param name="targetCell">目标区域的开始Cell位置（比如：H10）</param>
public void RangeCopy(int sheetIndex, string startCell, string endCell, string targetCell) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try {
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
range1 = workSheet.get_Range(startCell, endCell);
range2 = workSheet.get_Range(targetCell, this.missing);
range1.Copy(range2);
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 将指定范围区域拷贝到目标区域
/// </summary>
/// <param name="sheetName">WorkSheet名称</param>
/// <param name="startCell">要拷贝区域的开始Cell位置（比如：A10）</param>
/// <param name="endCell">要拷贝区域的结束Cell位置（比如：F20）</param>
/// <param name="targetCell">目标区域的开始Cell位置（比如：H10）</param>
public void RangeCopy(string sheetName, string startCell, string endCell, string targetCell) {
try {
Excel.Worksheet sheet = null;
for (int i = 1; i <= this.WorkSheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == sheetName) {
sheet = workSheet;
}
}
if (sheet != null) {
for (int i = sheetCount; i >= 1; i--) {
range1 = sheet.get_Range(startCell, endCell);
range2 = sheet.get_Range(targetCell, this.missing);
range1.Copy(range2);
}
}
else {
this.KillExcelProcess(false);
throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 自动填充
/// </summary>
public void RangAutoFill() {
Excel.Range rng = workSheet.get_Range("B4", Type.Missing);
rng.Value2 = "星期一 ";
rng.AutoFill(workSheet.get_Range("B4", "B9"),
Excel.XlAutoFillType.xlFillWeekdays);
rng = workSheet.get_Range("C4", Type.Missing);
rng.Value2 = "一月";
rng.AutoFill(workSheet.get_Range("C4", "C9"),
Excel.XlAutoFillType.xlFillMonths);
rng = workSheet.get_Range("D4", Type.Missing);
rng.Value2 = "1";
rng.AutoFill(workSheet.get_Range("D4", "D9"),
Excel.XlAutoFillType.xlFillSeries);
rng = workSheet.get_Range("E4", Type.Missing);
rng.Value2 = "3";
rng = workSheet.get_Range("E5", Type.Missing);
rng.Value2 = "6";
rng = workSheet.get_Range("E4", "E5");
rng.AutoFill(workSheet.get_Range("E4", "E9"),
Excel.XlAutoFillType.xlFillSeries);
}
/// <summary>
/// 应用样式
/// </summary>
public void ApplyStyle() {
object missingValue = Type.Missing;
Excel.Range rng = workSheet.get_Range("B3", "L23");
Excel.Style style;
try {
style = workBook.Styles["NewStyle"];
}
// Style doesn't exist yet.
catch {
style = workBook.Styles.Add("NewStyle", missingValue);
style.Font.Name = "Verdana";
style.Font.Size = 12;
style.Font.Color = 255;
style.Interior.Color = (200 << 16) | (200 << 8) | 200;
style.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
}
rng.Value2 = "'Style Test";
rng.Style = "NewStyle";
rng.Columns.AutoFit();
}
#endregion
#region Set Style Methods
/// <summary>
/// 单元格背景色及填充方式
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="color">颜色索引</param>
public void CellsBackColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color) {
Excel.Range range = excelApp.get_Range(excelApp.Cells[startRow, startColumn], excelApp.Cells[endRow, endColumn]);
range.Interior.ColorIndex = color;
range.Interior.Pattern = Pattern.Solid;
}
/// <summary>
/// 单元格背景色及填充方式
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="color">颜色索引</param>
/// <param name="pattern">填充方式</param>
public void CellsBackColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color, Pattern pattern) {
Excel.Range range = excelApp.get_Range(excelApp.Cells[startRow, startColumn], excelApp.Cells[endRow, endColumn]);
range.Interior.ColorIndex = color;
range.Interior.Pattern = pattern;
}
/// <summary>
/// 设置行高
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="endRow">结束行</param>
/// <param name="height">行高</param>
public void SetRowHeight(int startRow, int endRow, int height) {
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)excelApp.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Rows[startRow.ToString() + ":" + endRow.ToString(), System.Type.Missing];
range.RowHeight = height;
}
/// <summary>
/// 自动调整行高
/// </summary>
/// <param name="columnNum">列号</param>
public void RowAutoFit(int rowNum) {
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)excelApp.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Rows[rowNum.ToString() + ":" + rowNum.ToString(), System.Type.Missing];
range.EntireColumn.AutoFit();
}
/// <summary>
/// 设置列宽
/// </summary>
/// <param name="startColumn">起始列(列对应的字母)</param>
/// <param name="endColumn">结束列(列对应的字母)</param>
/// <param name="width"></param>
public void SetColumnWidth(string startColumn, string endColumn, int width) {
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)excelApp.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[startColumn + ":" + endColumn, System.Type.Missing];
range.ColumnWidth = width;
}
/// <summary>
/// 设置列宽
/// </summary>
/// <param name="startColumn">起始列</param>
/// <param name="endColumn">结束列</param>
/// <param name="width"></param>
public void SetColumnWidth(int startColumn, int endColumn, int width) {
string strStartColumn = GetColumnName(startColumn);
string strEndColumn = GetColumnName(endColumn);
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)excelApp.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[strStartColumn + ":" + strEndColumn, System.Type.Missing];
range.ColumnWidth = width;
}
/// <summary>
/// 自动调整列宽
/// </summary>
/// <param name="columnNum">列号</param>
public void ColumnAutoFit(string column) {
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)excelApp.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[column + ":" + column, System.Type.Missing];
range.EntireColumn.AutoFit();
}
/// <summary>
/// 自动调整列宽
/// </summary>
/// <param name="columnNum">列号</param>
public void ColumnAutoFit(int columnNum) {
string strcolumnNum = GetColumnName(columnNum);
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)excelApp.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[strcolumnNum + ":" + strcolumnNum, System.Type.Missing];
range.EntireColumn.AutoFit();
}
/// <summary>
/// 字体颜色
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="color">颜色索引</param>
public void FontColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color) {
Excel.Range range = excelApp.get_Range(excelApp.Cells[startRow, startColumn], excelApp.Cells[endRow, endColumn]);
range.Font.ColorIndex = color;
}
/// <summary>
/// 字体样式(加粗,斜体,下划线)
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="isBold">是否加粗</param>
/// <param name="isItalic">是否斜体</param>
/// <param name="underline">下划线类型</param>
public void FontStyle(int startRow, int startColumn, int endRow, int endColumn, bool isBold, bool isItalic, UnderlineStyle underline) {
Excel.Range range = excelApp.get_Range(excelApp.Cells[startRow, startColumn], excelApp.Cells[endRow, endColumn]);
range.Font.Bold = isBold;
range.Font.Underline = underline;
range.Font.Italic = isItalic;
}
/// <summary>
/// 单元格字体及大小
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="fontName">字体名称</param>
/// <param name="fontSize">字体大小</param>
public void FontNameSize(int startRow, int startColumn, int endRow, int endColumn, string fontName, int fontSize) {
Excel.Range range = excelApp.get_Range(excelApp.Cells[startRow, startColumn], excelApp.Cells[endRow, endColumn]);
range.Font.Name = fontName;
range.Font.Size = fontSize;
}
#endregion
#region 列头转换
/// <summary>
/// 最大列数
/// 2007:16384
/// 2003:256
/// </summary>
int MaxColumnCount = 16384;
/// <summary>
/// 将Excel列的字母索引值转换成整数索引值
/// </summary>
/// <param name="letter"></param>
/// <returns></returns>
public int LetterToInt(string letter) {
int n = 0;
if (letter.Trim().Length == 0)
throw new Exception("不接受空字符串！");
if (letter.Length == 1) {
char c1 = letter.ToCharArray()[0];
if (!char.IsLetter(c1)) {
throw new Exception("格式不正确，必须是字母！");
}
c1 = char.ToUpper(c1);
n = Convert.ToInt32(c1) - 64;
}
else {
char[] chs = letter.ToCharArray();
for (int i = 0; i < chs.Length; i++) {
if (!char.IsLetter(chs[i])) {
throw new Exception("格式不正确，必须是字母！");
}
char ctemp = char.ToUpper(chs[i]);
int col = Convert.ToInt32(ctemp) - 64;
n += Convert.ToInt32(Math.Pow(26, (chs.Length - i - 1))) * col;
if (n > MaxColumnCount)
throw new Exception(string.Format("索引超出范围，Excel的列索引不能超过{0}！", MaxColumnCount));
}
}
return n;
}
/// <summary>
/// 将Excel列的整数索引值转换为字符索引值
/// </summary>
/// <param name="n"></param>
/// <returns></returns>
public string IntToLetter(int n) {
if (n > MaxColumnCount)
throw new Exception(string.Format("索引超出范围，Excel的列索引不能超过{0}！", MaxColumnCount));
String[] COL_NAME = {
"A", "B", "C", "D", "E", "F", "G", "H", "I", "J",
"K", "L", "M", "N", "O", "P", "Q", "R", "S", "T",
"U", "V", "W", "X", "Y", "Z" };
int a = 0, b = n;
String col = "";
if (b < 26) {
col = COL_NAME[b];
}
else {
while (b >= 26) {
a = b % 26;
b = b / 26;
col = COL_NAME[a] + col;
}
col = COL_NAME[b - 1] + col;
}
return col;
}
#endregion
#region Output File(注意：如果目标文件已存在的话会出错)
/// <summary>
/// 输出Excel文件并退出
/// </summary>
public void OutputExcelFile() {
if (this.outputFile == null)
throw new Exception("没有指定输出文件路径！");
try {
workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
}
catch (Exception e) {
throw e;
}
finally {
this.Quit();
}
}
/// <summary>
/// 输出指定格式的文件（支持格式：HTML，CSV，TEXT，EXCEL）
/// </summary>
/// <param name="format">HTML，CSV，TEXT，EXCEL，XML</param>
public void OutputFile(string format) {
if (this.outputFile == null)
throw new Exception("没有指定输出文件路径！");
try {
switch (format) {
case "HTML": {
workBook.SaveAs(outputFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
case "CSV": {
workBook.SaveAs(outputFile, Excel.XlFileFormat.xlCSV, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
case "TEXT": {
workBook.SaveAs(outputFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
// case "XML":
// {
// workBook.SaveAs(outputFile,Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// break;
//
// }
default: {
workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
}
}
catch (Exception e) {
throw e;
}
finally {
this.Quit();
}
}
/// <summary>
/// 保存文件
/// </summary>
public void SaveFile() {
try {
workBook.Save();
}
catch (Exception e) {
throw e;
}
finally {
this.Quit();
}
}
/// <summary>
/// 另存文件
/// </summary>
public void SaveAsFile() {
if (this.outputFile == null)
throw new Exception("没有指定输出文件路径！");
try {
workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
}
catch (Exception e) {
throw e;
}
finally {
this.Quit();
}
}
/// <summary>
/// 将Excel文件另存为指定格式
/// </summary>
/// <param name="format">HTML，CSV，TEXT，EXCEL，XML</param>
public void SaveAsFile(string format) {
if (this.outputFile == null)
throw new Exception("没有指定输出文件路径！");
try {
switch (format) {
case "HTML": {
workBook.SaveAs(outputFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
case "CSV": {
workBook.SaveAs(outputFile, Excel.XlFileFormat.xlCSV, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
case "TEXT": {
workBook.SaveAs(outputFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
// case "XML":
// {
// workBook.SaveAs(outputFile,Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// break;
// }
default: {
workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
}
}
catch (Exception e) {
throw e;
}
finally {
this.Quit();
}
}
/// <summary>
/// 另存文件
/// </summary>
/// <param name="fileName">文件名</param>
public void SaveFile(string fileName) {
try {
workBook.SaveAs(fileName, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
}
catch (Exception e) {
throw e;
}
finally {
this.Quit();
}
}
/// <summary>
/// 将Excel文件另存为指定格式
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="format">HTML，CSV，TEXT，EXCEL，XML</param>
public void SaveAsFile(string fileName, string format) {
try {
switch (format) {
case "HTML": {
workBook.SaveAs(fileName, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
case "CSV": {
workBook.SaveAs(fileName, Excel.XlFileFormat.xlCSV, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
case "TEXT": {
workBook.SaveAs(fileName, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
// case "XML":
// {
// workBook.SaveAs(fileName,Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// break;
// }
default: {
workBook.SaveAs(fileName, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
}
}
catch (Exception e) {
throw e;
}
finally {
this.Quit();
}
}
#endregion
#endregion
#region 私有方法
/// <summary>
/// 合并单元格，并赋值，对指定WorkSheet操作
/// </summary>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="beginColumnIndex">开始列索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="endColumnIndex">结束列索引</param>
/// <param name="text">合并后Range的值</param>
private void MergeCells(Excel.Worksheet sheet, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text) {
if (sheet == null)
return;
range = sheet.get_Range(sheet.Cells[beginRowIndex, beginColumnIndex], sheet.Cells[endRowIndex, endColumnIndex]);
range.ClearContents(); //先把Range内容清除，合并才不会出错
range.MergeCells = true;
range.Value2 = text;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
/// <summary>
/// 将指定索引列的数据相同的行合并，对指定WorkSheet操作
/// </summary>
/// <param name="columnIndex">要合并的列索引</param>
/// <param name="beginRowIndex">合并开始行索引</param>
/// <param name="rows">要合并的行数</param>
private void MergeRows(Excel.Worksheet sheet, int columnIndex, int beginRowIndex, int rows) {
int beginIndex = beginRowIndex;
int count = 0;
string text1;
string text2;
if (sheet == null)
return;
for (int j = beginRowIndex; j < beginRowIndex + rows; j++) {
range1 = (Excel.Range)sheet.Cells[j, columnIndex];
range2 = (Excel.Range)sheet.Cells[j + 1, columnIndex];
text1 = range1.Text.ToString();
text2 = range2.Text.ToString();
if (text1 == text2) {
++count;
}
else {
if (count > 0) {
this.MergeCells(sheet, beginIndex, columnIndex, beginIndex + count, columnIndex, text1);
}
beginIndex = j + 1; //设置开始合并行索引
count = 0; //计数器清0
}
}
}
/// <summary>
/// 计算WorkSheet数量
/// </summary>
/// <param name="rowCount">记录总行数</param>
/// <param name="rows">每WorkSheet行数</param>
public int GetSheetCount(int rowCount, int rows) {
int n = rowCount % rows; //余数
if (n == 0)
return rowCount / rows;
else
return Convert.ToInt32(rowCount / rows) + 1;
}
/// <summary>
/// 结束Excel进程
/// </summary>
public void KillExcelProcess(bool bAll) {
if (bAll) {
Process[] myProcesses;
myProcesses = Process.GetProcessesByName("Excel");
//得不到Excel进程ID，暂时只能判断进程启动时间
foreach (Process myProcess in myProcesses) {
myProcess.Kill();
continue;
}
}
else {
KillSpecialExcel();
}
}
[DllImport("user32.dll", SetLastError = true)]
static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId);
public void KillSpecialExcel() {
try {
if (excelApp != null) {
int lpdwProcessId;
GetWindowThreadProcessId((IntPtr)excelApp.Hwnd, out lpdwProcessId);
//c-s方式
if (lpdwProcessId > 0) {
System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
}
else {
Quit();
//excelApp.Quit();
//excelApp = null;
//b-s方式windows api失效，进程时间也报win32错误
//Process[] myProcesses;
//myProcesses = Process.GetProcessesByName("Excel");
//DateTime startTime;
//foreach (Process myProcess in myProcesses) {
// startTime = myProcess.StartTime;
// if (startTime > beforeTime && startTime < afterTime) {
// myProcess.Kill();
// }
// continue;
//}
}
}
}
catch { }
}
public void Quit() {
if (workBook != null)
workBook.Close(null, null, null);
if (excelApp != null) {
excelApp.Workbooks.Close();
excelApp.Quit();
}
if (range != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (range1 != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(range1);
range1 = null;
}
if (range2 != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(range2);
range2 = null;
}
if (textBox != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(textBox);
textBox = null;
}
if (workSheet != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
workSheet = null;
}
if (workBook != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
workBook = null;
}
if (excelApp != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;
}
GC.Collect();
}//end Quit
#endregion
#region get sheets
/// <summary>
/// 按Index得到WorkSheet
/// </summary>
/// <param name="index">序号</param>
public Excel.Worksheet GetSheet(int index) {
try {
Excel.Worksheet aimSheet = (Excel.Worksheet)workBook.Sheets.get_Item(index);
return aimSheet;
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
public Excel.Worksheet GetSheetByName(string strName) {
try {
for (int i = 1; i <= workBook.Sheets.Count; i++) {
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == strName)
return workSheet;
}
this.KillExcelProcess(false);
//throw new Exception("sheet not exist");
return null;
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
public List<string> GetSheets() {
List<string> listN = new List<string>();
for (int i = 1; i <= workBook.Sheets.Count; i++) {
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
listN.Add(workSheet.Name);
}
return listN;
}
#endregion
#region IDisposable 成员
void IDisposable.Dispose() {
Quit();
}
#endregion
}
#region Common Excel Enum Properties
/// <summary>
/// HTML，CSV，TEXT，EXCEL，XML
/// </summary>
enum SaveAsFileFormat {
HTML,
CSV,
TEXT,
EXCEL,
XML
}
/// <summary>
/// 常用颜色定义,对就Excel中颜色名
/// </summary>
public enum ColorIndex {
无色 = -4142,
自动 = -4105,
黑色 = 1,
褐色 = 53,
橄榄 = 52,
深绿 = 51,
深青 = 49,
深蓝 = 11,
靛蓝 = 55,
灰色80 = 56,
深红 = 9,
橙色 = 46,
深黄 = 12,
绿色 = 10,
青色 = 14,
蓝色 = 5,
蓝灰 = 47,
灰色50 = 16,
红色 = 3,
浅橙色 = 45,
酸橙色 = 43,
海绿 = 50,
水绿色 = 42,
浅蓝 = 41,
紫罗兰 = 13,
灰色40 = 48,
粉红 = 7,
金色 = 44,
黄色 = 6,
鲜绿 = 4,
青绿 = 8,
天蓝 = 33,
梅红 = 54,
灰色25 = 15,
玫瑰红 = 38,
茶色 = 40,
浅黄 = 36,
浅绿 = 35,
浅青绿 = 34,
淡蓝 = 37,
淡紫 = 39,
白色 = 2
}
/// <summary>
/// 水平对齐方式
/// </summary>
public enum ExcelHAlign {
常规 = 1,
靠左,
居中,
靠右,
填充,
两端对齐,
跨列居中,
分散对齐
}
/// <summary>
/// 垂直对齐方式
/// </summary>
public enum ExcelVAlign {
靠上 = 1,
居中,
靠下,
两端对齐,
分散对齐
}
/// <summary>
/// 线粗
/// </summary>
public enum BorderWeight {
极细 = 1,
细 = 2,
粗 = -4138,
极粗 = 4
}
/// <summary>
/// 线样式
/// </summary>
public enum LineStyle {
连续直线 = 1,
短线 = -4115,
线点相间 = 4,
短线间两点 = 5,
点 = -4118,
双线 = -4119,
无 = -4142,
少量倾斜点 = 13
}
/// <summary>
/// 下划线方式
/// </summary>
public enum UnderlineStyle {
无下划线 = -4142,
双线 = -4119,
双线充满全格 = 5,
单线 = 2,
单线充满全格 = 4
}
/// <summary>
/// 单元格填充方式
/// </summary>
public enum Pattern {
Automatic = -4105,
Checker = 9,
CrissCross = 16,
Down = -4121,
Gray16 = 17,
Gray25 = -4124,
Gray50 = -4125,
Gray75 = -4126,
Gray8 = 18,
Grid = 15,
Horizontal = -4128,
LightDown = 13,
LightHorizontal = 11,
LightUp = 14,
LightVertical = 12,
None = -4142,
SemiGray75 = 10,
Solid = 1,
Up = -4162,
Vertical = -4166
}
#endregion
}
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using System.Diagnostics;
using System.IO;
using System.Collections;
using System.Data;
namespace Core.Helper {
/// <summary>
/// excel操作类
/// 开发环境：2007
/// </summary>
public class ExcelHelper : IDisposable {
#region 构造函数
/// <summary>
/// 构造函数，将一个已有Excel工作簿作为模板，并指定输出路径
/// </summary>
/// <param name="templetFilePath">Excel模板文件路径</param>
/// <param name="outputFilePath">输出Excel文件路径</param>
public ExcelHelper(string templetFilePath, string outputFilePath) {
if (templetFilePath == null)
throw new Exception("Excel模板文件路径不能为空！");
if (outputFilePath == null)
throw new Exception("输出Excel文件路径不能为空！");
if (!File.Exists(templetFilePath))
throw new Exception("指定路径的Excel模板文件不存在！");
this.templetFile = templetFilePath;
this.outputFile = outputFilePath;
excelApp = new Excel.ApplicationClass();
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
excelApp.AlertBeforeOverwriting = false;
//打开模板文件，得到WorkBook对象
workBook = excelApp.Workbooks.Open(templetFile, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, Type.Missing, Type.Missing);
//得到WorkSheet对象
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
}
DateTime beforeTime;
DateTime afterTime;
/// <summary>
/// 构造函数，打开一个已有的工作簿
/// </summary>
/// <param name="fileName">Excel文件名</param>
public ExcelHelper(string fileName) {
if (!File.Exists(fileName))
throw new Exception("指定路径的Excel文件不存在！");
//创建一个Application对象并使其可见
beforeTime = DateTime.Now;
excelApp = new Excel.ApplicationClass();
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
excelApp.AlertBeforeOverwriting = false;
//打开一个WorkBook
workBook = excelApp.Workbooks.Open(fileName,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
afterTime = DateTime.Now;
//得到WorkSheet对象
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
}
/// <summary>
/// 构造函数，新建一个工作簿
/// </summary>
public ExcelHelper() {
beforeTime = DateTime.Now;
excelApp = new Excel.ApplicationClass();
afterTime = DateTime.Now;
excelApp.Visible = false;
//设置禁止弹出保存和覆盖的询问提示框
excelApp.DisplayAlerts = false;
excelApp.AlertBeforeOverwriting = false;
//新建一个WorkBook
workBook = excelApp.Workbooks.Add(Type.Missing);
//得到WorkSheet对象
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
}
#endregion
#region 成员变量
private string templetFile = null;
private string outputFile = null;
private object missing = System.Reflection.Missing.Value;
Excel.Application excelApp;
Excel.Workbook workBook;
Excel.Worksheet workSheet;
Excel.Range range;
Excel.Range range1;
Excel.Range range2;
Excel.TextBox textBox;
private int sheetCount = 1; //WorkSheet数量
private string sheetPrefixName = "页";
#endregion
#region 公共属性
/// <summary>
/// WorkSheet前缀名，比如：前缀名为“页”，那么WorkSheet名称依次为“页-1，页-2...”
/// </summary>
public string SheetPrefixName {
set { this.sheetPrefixName = value; }
}
/// <summary>
/// WorkSheet数量
/// </summary>
public int WorkSheetCount {
get { return workBook.Sheets.Count; }
}
/// <summary>
/// Excel模板文件路径
/// </summary>
public string TempletFilePath {
set { this.templetFile = value; }
}
/// <summary>
/// 输出Excel文件路径
/// </summary>
public string OutputFilePath {
set { this.outputFile = value; }
}
#endregion
#region 公共方法
#region Data Export Methods
/// <summary>
/// 将DataTable数据写入Excel文件（自动分页）
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="rows">每个WorkSheet写入多少行数据</param>
/// <param name="top">表格数据起始行索引</param>
/// <param name="left">表格数据起始列索引</param>
public void DataTableToExcel(DataTable dt, int rows, int top, int left) {
int rowCount = dt.Rows.Count; //DataTable行数
int colCount = dt.Columns.Count; //DataTable列数
sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数
// StringBuilder sb;
//复制sheetCount-1个WorkSheet对象
for (int i = 1; i < sheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Copy(missing, workBook.Worksheets[i]);
}
for (int i = 1; i <= sheetCount; i++) {
int startRow = (i - 1) * rows; //记录起始行索引
int endRow = i * rows; //记录结束行索引
//若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
if (i == sheetCount)
endRow = rowCount;
//获取要写入数据的WorkSheet对象，并重命名
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Name = sheetPrefixName + "-" + i.ToString();
//将dt中的数据写入WorkSheet
// for(int j=0;j<endRow-startRow;j++)
// {
// for(int k=0;k<colCount;k++)
// {
// workSheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();
// }
// }
//利用二维数组批量写入
int row = endRow - startRow;
object[,] ss = new object[row, colCount];
for (int j = 0; j < row; j++) {
for (int k = 0; k < colCount; k++) {
ss[j, k] = dt.Rows[startRow + j][k].ToString();
}
}
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(row, colCount);
range.Value2 = ss;
#region 利用Windwo粘贴板批量拷贝数据（在Web下面行不通）
/*sb = new StringBuilder();
for(int j=0;j<endRow-startRow;j++)
{
for(int k=0;k<colCount;k++)
{
sb.Append( dt.Rows[startRow + j][k].ToString() );
sb.Append("\t");
}
sb.Append("\n");
}
System.Windows.Forms.Clipboard.SetDataObject(sb.ToString());
range = (Excel.Range)workSheet.Cells[top,left];
workSheet.Paste(range,false);*/
#endregion
}
}
/// <summary>
/// 将DataTable数据写入Excel文件（不分页）
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="top">表格数据起始行索引</param>
/// <param name="left">表格数据起始列索引</param>
public void DataTableToExcel(DataTable dt, int top, int left) {
int rowCount = dt.Rows.Count; //DataTable行数
int colCount = dt.Columns.Count; //DataTable列数
//利用二维数组批量写入
object[,] arr = new object[rowCount, colCount];
for (int j = 0; j < rowCount; j++) {
for (int k = 0; k < colCount; k++) {
arr[j, k] = dt.Rows[j][k].ToString();
}
}
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(rowCount, colCount);
range.Value2 = arr;
}
public void DataTableToExcel(DataTable dt, int top, int left, Excel.Worksheet objSheet) {
int rowCount = dt.Rows.Count; //DataTable行数
int colCount = dt.Columns.Count; //DataTable列数
//利用二维数组批量写入
object[,] arr = new object[rowCount, colCount];
for (int j = 0; j < rowCount; j++) {
for (int k = 0; k < colCount; k++) {
arr[j, k] = dt.Rows[j][k].ToString();
}
}
range = (Excel.Range)objSheet.Cells[top, left];
range = range.get_Resize(rowCount, colCount);
range.Value2 = arr;
}
/// <summary>
/// 将DataTable数据写入Excel文件（自动分页，并指定要合并的列索引）
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="rows">每个WorkSheet写入多少行数据</param>
/// <param name="top">表格数据起始行索引</param>
/// <param name="left">表格数据起始列索引</param>
/// <param name="mergeColumnIndex">DataTable中要合并相同行的列索引，从0开始</param>
public void DataTableToExcel(DataTable dt, int rows, int top, int left, int mergeColumnIndex) {
int rowCount = dt.Rows.Count; //源DataTable行数
int colCount = dt.Columns.Count; //源DataTable列数
sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数
// StringBuilder sb;
//复制sheetCount-1个WorkSheet对象
for (int i = 1; i < sheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Copy(missing, workBook.Worksheets[i]);
}
for (int i = 1; i <= sheetCount; i++) {
int startRow = (i - 1) * rows; //记录起始行索引
int endRow = i * rows; //记录结束行索引
//若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
if (i == sheetCount)
endRow = rowCount;
//获取要写入数据的WorkSheet对象，并重命名
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Name = sheetPrefixName + "-" + i.ToString();
//将dt中的数据写入WorkSheet
// for(int j=0;j<endRow-startRow;j++)
// {
// for(int k=0;k<colCount;k++)
// {
// workSheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();
// }
// }
//利用二维数组批量写入
int row = endRow - startRow;
object[,] ss = new object[row, colCount];
for (int j = 0; j < row; j++) {
for (int k = 0; k < colCount; k++) {
ss[j, k] = dt.Rows[startRow + j][k].ToString();
}
}
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(row, colCount);
range.Value2 = ss;
//合并相同行
this.MergeRows(workSheet, left + mergeColumnIndex, top, rows);
}
}
/// <summary>
/// 将二维数组数据写入Excel文件（自动分页）
/// </summary>
/// <param name="arr">二维数组</param>
/// <param name="rows">每个WorkSheet写入多少行数据</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
public void ArrayToExcel(object[,] arr, int rows, int top, int left) {
int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
int colCount = arr.GetLength(1); //二维数据列数（二维长度）
sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数
//复制sheetCount-1个WorkSheet对象
for (int i = 1; i < sheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Copy(missing, workBook.Worksheets[i]);
}
//将二维数组数据写入Excel
for (int i = sheetCount; i >= 1; i--) {
int startRow = (i - 1) * rows; //记录起始行索引
int endRow = i * rows; //记录结束行索引
//若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
if (i == sheetCount)
endRow = rowCount;
//获取要写入数据的WorkSheet对象，并重命名
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Name = sheetPrefixName + "-" + i.ToString();
//将二维数组中的数据写入WorkSheet
// for(int j=0;j<endRow-startRow;j++)
// {
// for(int k=0;k<colCount;k++)
// {
// workSheet.Cells[top + j,left + k] = arr[startRow + j,k];
// }
// }
//利用二维数组批量写入
int row = endRow - startRow;
object[,] ss = new object[row, colCount];
for (int j = 0; j < row; j++) {
for (int k = 0; k < colCount; k++) {
ss[j, k] = arr[startRow + j, k];
}
}
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(row, colCount);
range.Value2 = ss;
}
}//end ArrayToExcel
/// <summary>
/// 将二维数组数据写入Excel文件（不分页）
/// </summary>
/// <param name="arr">二维数组</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
public void ArrayToExcel(object[,] arr, int top, int left) {
int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
int colCount = arr.GetLength(1); //二维数据列数（二维长度）
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(rowCount, colCount);
range.FormulaArray = arr;
}//end ArrayToExcel
/// <summary>
/// 将二维数组数据写入Excel文件（不分页）
/// </summary>
/// <param name="arr">二维数组</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
/// <param name="isFormula">填充的数据是否需要计算</param>
public void ArrayToExcel(object[,] arr, int top, int left, bool isFormula) {
int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
int colCount = arr.GetLength(1); //二维数据列数（二维长度）
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(rowCount, colCount);
//注意：使用range.FormulaArray写合并的单元格会出问题
if (isFormula)
range.FormulaArray = arr;
else
range.Value2 = arr;
}//end ArrayToExcel
/// <summary>
/// 将二维数组数据写入Excel文件（不分页），合并指定列的相同行
/// </summary>
/// <param name="arr">二维数组</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
/// <param name="isFormula">填充的数据是否需要计算</param>
/// <param name="mergeColumnIndex">需要合并行的列索引</param>
public void ArrayToExcel(object[,] arr, int top, int left, bool isFormula, int mergeColumnIndex) {
int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
int colCount = arr.GetLength(1); //二维数据列数（二维长度）
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(rowCount, colCount);
//注意：使用range.FormulaArray写合并的单元格会出问题
if (isFormula)
range.FormulaArray = arr;
else
range.Value2 = arr;
this.MergeRows(workSheet, mergeColumnIndex, top, rowCount);
}//end ArrayToExcel
/// <summary>
/// 将二维数组数据写入Excel文件（不分页）
/// </summary>
/// <param name="sheetIndex">工作表索引</param>
/// <param name="arr">二维数组</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
public void ArrayToExcel(int sheetIndex, object[,] arr, int top, int left) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
// 改变当前工作表
this.workSheet = (Excel.Worksheet)this.workBook.Sheets.get_Item(sheetIndex);
int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
int colCount = arr.GetLength(1); //二维数据列数（二维长度）
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(rowCount, colCount);
range.Value2 = arr;
}//end ArrayToExcel
/// <summary>
/// 将二维数组数据写入Excel文件（自动分页，并指定要合并的列索引）
/// </summary>
/// <param name="arr">二维数组</param>
/// <param name="rows">每个WorkSheet写入多少行数据</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
/// <param name="mergeColumnIndex">数组的二维索引，相当于DataTable的列索引，索引从0开始</param>
public void ArrayToExcel(object[,] arr, int rows, int top, int left, int mergeColumnIndex) {
int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
int colCount = arr.GetLength(1); //二维数据列数（二维长度）
sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数
//复制sheetCount-1个WorkSheet对象
for (int i = 1; i < sheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Copy(missing, workBook.Worksheets[i]);
}
//将二维数组数据写入Excel
for (int i = sheetCount; i >= 1; i--) {
int startRow = (i - 1) * rows; //记录起始行索引
int endRow = i * rows; //记录结束行索引
//若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
if (i == sheetCount)
endRow = rowCount;
//获取要写入数据的WorkSheet对象，并重命名
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
workSheet.Name = sheetPrefixName + "-" + i.ToString();
//将二维数组中的数据写入WorkSheet
for (int j = 0; j < endRow - startRow; j++) {
for (int k = 0; k < colCount; k++) {
workSheet.Cells[top + j, left + k] = arr[startRow + j, k];
}
}
//利用二维数组批量写入
int row = endRow - startRow;
object[,] ss = new object[row, colCount];
for (int j = 0; j < row; j++) {
for (int k = 0; k < colCount; k++) {
ss[j, k] = arr[startRow + j, k];
}
}
range = (Excel.Range)workSheet.Cells[top, left];
range = range.get_Resize(row, colCount);
range.Value2 = ss;
//合并相同行
this.MergeRows(workSheet, left + mergeColumnIndex, top, rows);
}
}//end ArrayToExcel
#endregion
#region WorkSheet Methods
/// <summary>
/// 改变当前工作表
/// </summary>
/// <param name="sheetIndex">工作表索引</param>
public void ChangeCurrentWorkSheet(int sheetIndex) {
//若指定工作表索引超出范围，则不改变当前工作表
if (sheetIndex < 1)
return;
if (sheetIndex > this.WorkSheetCount)
return;
this.workSheet = (Excel.Worksheet)this.workBook.Sheets.get_Item(sheetIndex);
}
/// <summary>
/// 隐藏指定名称的工作表
/// </summary>
/// <param name="sheetName">工作表名称</param>
public void HiddenWorkSheet(string sheetName) {
try {
Excel.Worksheet sheet = null;
for (int i = 1; i <= this.WorkSheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == sheetName)
sheet = workSheet;
}
if (sheet != null)
sheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
else {
this.KillExcelProcess(false);
throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 隐藏指定索引的工作表
/// </summary>
/// <param name="sheetIndex"></param>
public void HiddenWorkSheet(int sheetIndex) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try {
Excel.Worksheet sheet = null;
sheet = (Excel.Worksheet)workBook.Sheets.get_Item(sheetIndex);
sheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 在指定名称的工作表后面拷贝指定个数的该工作表的副本，并重命名
/// </summary>
/// <param name="sheetName">工作表名称</param>
/// <param name="sheetCount">工作表个数</param>
public void CopyWorkSheets(string sheetName, int sheetCount) {
try {
Excel.Worksheet sheet = null;
int sheetIndex = 0;
for (int i = 1; i <= this.WorkSheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == sheetName) {
sheet = workSheet;
sheetIndex = workSheet.Index;
}
}
if (sheet != null) {
for (int i = sheetCount; i >= 1; i--) {
sheet.Copy(this.missing, sheet);
}
//重命名
for (int i = sheetIndex; i <= sheetIndex + sheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
workSheet.Name = sheetName + "-" + Convert.ToString(i - sheetIndex + 1);
}
}
else {
this.KillExcelProcess(false);
throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 将一个工作表拷贝到另一个工作表后面，并重命名
/// </summary>
/// <param name="srcSheetIndex">拷贝源工作表索引</param>
/// <param name="aimSheetIndex">参照位置工作表索引，新工作表拷贝在该工作表后面</param>
/// <param name="newSheetName"></param>
public void CopyWorkSheet(int srcSheetIndex, int aimSheetIndex, string newSheetName) {
if (srcSheetIndex > this.WorkSheetCount || aimSheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try {
Excel.Worksheet srcSheet = (Excel.Worksheet)workBook.Sheets.get_Item(srcSheetIndex);
Excel.Worksheet aimSheet = (Excel.Worksheet)workBook.Sheets.get_Item(aimSheetIndex);
srcSheet.Copy(this.missing, aimSheet);
//重命名
workSheet = (Excel.Worksheet)aimSheet.Next; //获取新拷贝的工作表
workSheet.Name = newSheetName;
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 根据名称删除工作表
/// </summary>
/// <param name="sheetName"></param>
public void DeleteWorkSheet(string sheetName) {
try {
Excel.Worksheet sheet = null;
//找到名称位sheetName的工作表
for (int i = 1; i <= this.WorkSheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == sheetName) {
sheet = workSheet;
}
}
if (sheet != null) {
sheet.Delete();
}
else {
this.KillExcelProcess(false);
throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 根据索引删除工作表
/// </summary>
/// <param name="sheetIndex"></param>
public void DeleteWorkSheet(int sheetIndex) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try {
Excel.Worksheet sheet = null;
sheet = (Excel.Worksheet)workBook.Sheets.get_Item(sheetIndex);
sheet.Delete();
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
#endregion
#region TextBox Methods
/// <summary>
/// 向指定文本框写入数据，对每个WorkSheet操作
/// </summary>
/// <param name="textboxName">文本框名称</param>
/// <param name="text">要写入的文本</param>
public void SetTextBox(string textboxName, string text) {
for (int i = 1; i <= this.WorkSheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
try {
textBox = (Excel.TextBox)workSheet.TextBoxes(textboxName);
textBox.Text = text;
}
catch {
this.KillExcelProcess(false);
throw new Exception("不存在ID为\"" + textboxName + "\"的文本框！");
}
}
}
/// <summary>
/// 向指定文本框写入数据，对指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">工作表索引</param>
/// <param name="textboxName">文本框名称</param>
/// <param name="text">要写入的文本</param>
public void SetTextBox(int sheetIndex, string textboxName, string text) {
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
try {
textBox = (Excel.TextBox)workSheet.TextBoxes(textboxName);
textBox.Text = text;
}
catch {
this.KillExcelProcess(false);
throw new Exception("不存在ID为\"" + textboxName + "\"的文本框！");
}
}
/// <summary>
/// 向文本框写入数据，对每个WorkSheet操作
/// </summary>
/// <param name="ht">Hashtable的键值对保存文本框的ID和数据</param>
public void SetTextBoxes(Hashtable ht) {
if (ht.Count == 0) return;
for (int i = 1; i <= this.WorkSheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
foreach (DictionaryEntry dic in ht) {
try {
textBox = (Excel.TextBox)workSheet.TextBoxes(dic.Key);
textBox.Text = dic.Value.ToString();
}
catch {
this.KillExcelProcess(false);
throw new Exception("不存在ID为\"" + dic.Key.ToString() + "\"的文本框！");
}
}
}
}
/// <summary>
/// 向文本框写入数据，对指定WorkSheet操作
/// </summary>
/// <param name="ht">Hashtable的键值对保存文本框的ID和数据</param>
public void SetTextBoxes(int sheetIndex, Hashtable ht) {
if (ht.Count == 0) return;
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
foreach (DictionaryEntry dic in ht) {
try {
textBox = (Excel.TextBox)workSheet.TextBoxes(dic.Key);
textBox.Text = dic.Value.ToString();
}
catch {
this.KillExcelProcess(false);
throw new Exception("不存在ID为\"" + dic.Key.ToString() + "\"的文本框！");
}
}
}
#endregion
#region Cell Methods
/// <summary>
/// 向单元格写入数据，对当前WorkSheet操作
/// </summary>
/// <param name="rowIndex">行索引</param>
/// <param name="columnIndex">列索引</param>
/// <param name="text">要写入的文本值</param>
public void SetCells(int rowIndex, int columnIndex, string text) {
try {
workSheet.Cells[rowIndex, columnIndex] = text;
}
catch {
this.KillExcelProcess(false);
throw new Exception("向单元格[" + rowIndex + "," + columnIndex + "]写数据出错！");
}
}
/// <summary>
/// 向单元格写入数据，对指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">工作表索引</param>
/// <param name="rowIndex">行索引</param>
/// <param name="columnIndex">列索引</param>
/// <param name="text">要写入的文本值</param>
public void SetCells(int sheetIndex, int rowIndex, int columnIndex, string text) {
try {
this.ChangeCurrentWorkSheet(sheetIndex); //改变当前工作表为指定工作表
workSheet.Cells[rowIndex, columnIndex] = text;
}
catch {
this.KillExcelProcess(false);
throw new Exception("向单元格[" + rowIndex + "," + columnIndex + "]写数据出错！");
}
}
/// <summary>
/// 向单元格写入数据，对每个WorkSheet操作
/// </summary>
/// <param name="ht">Hashtable的键值对保存单元格的位置索引（行索引和列索引用“,”隔开）和数据</param>
public void SetCells(Hashtable ht) {
int rowIndex;
int columnIndex;
string position;
if (ht.Count == 0) return;
for (int i = 1; i <= this.WorkSheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
foreach (DictionaryEntry dic in ht) {
try {
position = dic.Key.ToString();
rowIndex = Convert.ToInt32(position.Split(',')[0]);
columnIndex = Convert.ToInt32(position.Split(',')[1]);
workSheet.Cells[rowIndex, columnIndex] = dic.Value;
}
catch {
this.KillExcelProcess(false);
throw new Exception("向单元格[" + dic.Key + "]写数据出错！");
}
}
}
}
/// <summary>
/// 向单元格写入数据，对指定WorkSheet操作
/// </summary>
/// <param name="ht">Hashtable的键值对保存单元格的位置索引（行索引和列索引用“,”隔开）和数据</param>
public void SetCells(int sheetIndex, Hashtable ht) {
int rowIndex;
int columnIndex;
string position;
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
if (ht.Count == 0) return;
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
foreach (DictionaryEntry dic in ht) {
try {
position = dic.Key.ToString();
rowIndex = Convert.ToInt32(position.Split(',')[0]);
columnIndex = Convert.ToInt32(position.Split(',')[1]);
workSheet.Cells[rowIndex, columnIndex] = dic.Value;
}
catch {
this.KillExcelProcess(false);
throw new Exception("向单元格[" + dic.Key + "]写数据出错！");
}
}
}
/// <summary>
/// 设置单元格为可计算的
/// </summary>
/// <remarks>
/// 如果Excel的单元格格式设置为数字，日期或者其他类型时，需要设置这些单元格的FormulaR1C1属性，
/// 否则写到这些单元格的数据将不会按照预先设定的格式显示
/// </remarks>
/// <param name="arr">保存单元格的位置索引（行索引和列索引用“,”隔开）和数据</param>
public void SetCells(int sheetIndex, string[] arr) {
int rowIndex;
int columnIndex;
string position;
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
if (arr.Length == 0) return;
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
for (int i = 0; i < arr.Length; i++) {
try {
position = arr[i];
rowIndex = Convert.ToInt32(position.Split(',')[0]);
columnIndex = Convert.ToInt32(position.Split(',')[1]);
Excel.Range cell = (Excel.Range)workSheet.Cells[rowIndex, columnIndex];
cell.FormulaR1C1 = cell.Text;
}
catch {
this.KillExcelProcess(false);
throw new Exception(string.Format("计算单元格{0}出错！", arr[i]));
}
}
}
/// <summary>
/// 向单元格写入数据，对指定WorkSheet操作
/// </summary>
/// <param name="ht">Hashtable的键值对保存单元格的位置索引（行索引和列索引用“,”隔开）和数据</param>
public void SetCells(string sheetName, Hashtable ht) {
int rowIndex;
int columnIndex;
string position;
Excel.Worksheet sheet = null;
int sheetIndex = 0;
if (ht.Count == 0) return;
try {
for (int i = 1; i <= this.WorkSheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == sheetName) {
sheet = workSheet;
sheetIndex = workSheet.Index;
}
}
if (sheet != null) {
foreach (DictionaryEntry dic in ht) {
try {
position = dic.Key.ToString();
rowIndex = Convert.ToInt32(position.Split(',')[0]);
columnIndex = Convert.ToInt32(position.Split(',')[1]);
sheet.Cells[rowIndex, columnIndex] = dic.Value;
}
catch {
this.KillExcelProcess(false);
throw new Exception("向单元格[" + dic.Key + "]写数据出错！");
}
}
}
else {
this.KillExcelProcess(false);
throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 合并单元格，并赋值，对每个WorkSheet操作
/// </summary>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="beginColumnIndex">开始列索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="endColumnIndex">结束列索引</param>
/// <param name="text">合并后Range的值</param>
public void MergeCells(int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text) {
for (int i = 1; i <= this.WorkSheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]);
range.ClearContents(); //先把Range内容清除，合并才不会出错
range.MergeCells = true;
range.Value2 = text;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
}
/// <summary>
/// 合并单元格，并赋值，对指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">WorkSheet索引</param>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="beginColumnIndex">开始列索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="endColumnIndex">结束列索引</param>
/// <param name="text">合并后Range的值</param>
public void MergeCells(int sheetIndex, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]);
range.ClearContents(); //先把Range内容清除，合并才不会出错
range.MergeCells = true;
range.Value2 = text;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
#endregion
#region Row Methods
/// <summary>
/// 将指定索引列的数据相同的行合并，对每个WorkSheet操作
/// </summary>
/// <param name="columnIndex">列索引</param>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="endRowIndex">结束行索引</param>
public void MergeRows(int columnIndex, int beginRowIndex, int endRowIndex) {
if (endRowIndex - beginRowIndex < 1)
return;
for (int i = 1; i <= this.WorkSheetCount; i++) {
int beginIndex = beginRowIndex;
int count = 0;
string text1;
string text2;
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
for (int j = beginRowIndex; j <= endRowIndex; j++) {
range = (Excel.Range)workSheet.Cells[j, columnIndex];
text1 = range.Text.ToString();
range = (Excel.Range)workSheet.Cells[j + 1, columnIndex];
text2 = range.Text.ToString();
if (text1 == text2) {
++count;
}
else {
if (count > 0) {
this.MergeCells(workSheet, beginIndex, columnIndex, beginIndex + count, columnIndex, text1);
}
beginIndex = j + 1; //设置开始合并行索引
count = 0; //计数器清0
}
}
}
}
/// <summary>
/// 将指定索引列的数据相同的行合并，对指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">WorkSheet索引</param>
/// <param name="columnIndex">列索引</param>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="endRowIndex">结束行索引</param>
public void MergeRows(int sheetIndex, int columnIndex, int beginRowIndex, int endRowIndex) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
if (endRowIndex - beginRowIndex < 1)
return;
int beginIndex = beginRowIndex;
int count = 0;
string text1;
string text2;
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
for (int j = beginRowIndex; j <= endRowIndex; j++) {
range = (Excel.Range)workSheet.Cells[j, columnIndex];
text1 = range.Text.ToString();
range = (Excel.Range)workSheet.Cells[j + 1, columnIndex];
text2 = range.Text.ToString();
if (text1 == text2) {
++count;
}
else {
if (count > 0) {
this.MergeCells(workSheet, beginIndex, columnIndex, beginIndex + count, columnIndex, text1);
}
beginIndex = j + 1; //设置开始合并行索引
count = 0; //计数器清0
}
}
}
/// <summary>
/// 插行（在指定行上面插入指定数量行）
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void InsertRows(int rowIndex, int count) {
try {
for (int n = 1; n <= this.WorkSheetCount; n++) {
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
range = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 0; i < count; i++) {
range.Insert(Excel.XlDirection.xlDown, missing);
}
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 插行（在指定WorkSheet指定行上面插入指定数量行）
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void InsertRows(int sheetIndex, int rowIndex, int count) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try {
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
range = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 0; i < count; i++) {
range.Insert(Excel.XlDirection.xlDown, missing);
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 复制行（在指定行下面复制指定数量行）
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void CopyRows(int rowIndex, int count) {
try {
for (int n = 1; n <= this.WorkSheetCount; n++) {
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
range1 = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 1; i <= count; i++) {
range2 = (Excel.Range)workSheet.Rows[rowIndex + i, this.missing];
range1.Copy(range2);
}
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 复制行（在指定WorkSheet指定行下面复制指定数量行）
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void CopyRows(int sheetIndex, int rowIndex, int count) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try {
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
range1 = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 1; i <= count; i++) {
range2 = (Excel.Range)workSheet.Rows[rowIndex + i, this.missing];
range1.Copy(range2);
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 删除行
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void DeleteRows(int rowIndex, int count) {
try {
for (int n = 1; n <= this.WorkSheetCount; n++) {
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
range = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 0; i < count; i++) {
range.Delete(Excel.XlDirection.xlDown);
}
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 删除行
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void DeleteRows(int sheetIndex, int rowIndex, int count) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try {
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
range = (Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 0; i < count; i++) {
range.Delete(Excel.XlDirection.xlDown);
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
#endregion
#region Column Methods
/// <summary>
/// 插列（在指定列右边插入指定数量列）
/// </summary>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void InsertColumns(int columnIndex, int count) {
try {
for (int n = 1; n <= this.WorkSheetCount; n++) {
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
range = (Excel.Range)workSheet.Columns[this.missing, columnIndex];
for (int i = 0; i < count; i++) {
range.Insert(Excel.XlDirection.xlDown, missing);
}
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 插列（在指定WorkSheet指定列右边插入指定数量列）
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void InsertColumns(int sheetIndex, int columnIndex, int count) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try {
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
range = (Excel.Range)workSheet.Columns[this.missing, columnIndex];
for (int i = 0; i < count; i++) {
range.Insert(Excel.XlDirection.xlDown, missing);
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 复制列（在指定列右边复制指定数量列）
/// </summary>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void CopyColumns(int columnIndex, int count) {
try {
for (int n = 1; n <= this.WorkSheetCount; n++) {
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
// range1 = (Excel.Range)workSheet.Columns[columnIndex,this.missing];
range1 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex) + "1", this.IntToLetter(columnIndex) + "10000");
for (int i = 1; i <= count; i++) {
// range2 = (Excel.Range)workSheet.Columns[this.missing,columnIndex + i];
range2 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex + i) + "1", this.IntToLetter(columnIndex + i) + "10000");
range1.Copy(range2);
}
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 复制列（在指定WorkSheet指定列右边复制指定数量列）
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void CopyColumns(int sheetIndex, int columnIndex, int count) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try {
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
// range1 = (Excel.Range)workSheet.Columns[Type.Missing,columnIndex];
range1 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex) + "1", this.IntToLetter(columnIndex) + "10000");
for (int i = 1; i <= count; i++) {
// range2 = (Excel.Range)workSheet.Columns[Type.Missing,columnIndex + i];
range2 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex + i) + "1", this.IntToLetter(columnIndex + i) + "10000");
range1.Copy(range2);
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 删除列
/// </summary>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void DeleteColumns(int columnIndex, int count) {
try {
for (int n = 1; n <= this.WorkSheetCount; n++) {
workSheet = (Excel.Worksheet)workBook.Worksheets[n];
range = (Excel.Range)workSheet.Columns[this.missing, columnIndex];
for (int i = 0; i < count; i++) {
range.Delete(Excel.XlDirection.xlDown);
}
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 删除列
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="columnIndex"></param>
/// <param name="count"></param>
public void DeleteColumns(int sheetIndex, int columnIndex, int count) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try {
workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
range = (Excel.Range)workSheet.Columns[this.missing, columnIndex];
for (int i = 0; i < count; i++) {
range.Delete(Excel.XlDirection.xlDown);
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
#region 通过列数获取excel列名
public String GetColumnName(int num) {
String[] COL_NAME =
{
"A", "B", "C", "D", "E", "F", "G", "H", "I", "J",
"K", "L", "M", "N", "O", "P", "Q", "R", "S", "T",
"U", "V", "W", "X", "Y", "Z"
};
int a = 0, b = num;
String col = "";
if (b < 26) {
col = COL_NAME[b];
}
else {
while (b >= 26) {
a = b % 26;
b = b / 26;
col = COL_NAME[a] + col;
}
col = COL_NAME[b - 1] + col;
}
return col;
}
#endregion
#endregion
#region Range Methods
/// <summary>
/// 将指定范围区域拷贝到目标区域
/// </summary>
/// <param name="sheetIndex">WorkSheet索引</param>
/// <param name="startCell">要拷贝区域的开始Cell位置（比如：A10）</param>
/// <param name="endCell">要拷贝区域的结束Cell位置（比如：F20）</param>
/// <param name="targetCell">目标区域的开始Cell位置（比如：H10）</param>
public void RangeCopy(int sheetIndex, string startCell, string endCell, string targetCell) {
if (sheetIndex > this.WorkSheetCount) {
this.KillExcelProcess(false);
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
try {
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
range1 = workSheet.get_Range(startCell, endCell);
range2 = workSheet.get_Range(targetCell, this.missing);
range1.Copy(range2);
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 将指定范围区域拷贝到目标区域
/// </summary>
/// <param name="sheetName">WorkSheet名称</param>
/// <param name="startCell">要拷贝区域的开始Cell位置（比如：A10）</param>
/// <param name="endCell">要拷贝区域的结束Cell位置（比如：F20）</param>
/// <param name="targetCell">目标区域的开始Cell位置（比如：H10）</param>
public void RangeCopy(string sheetName, string startCell, string endCell, string targetCell) {
try {
Excel.Worksheet sheet = null;
for (int i = 1; i <= this.WorkSheetCount; i++) {
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == sheetName) {
sheet = workSheet;
}
}
if (sheet != null) {
for (int i = sheetCount; i >= 1; i--) {
range1 = sheet.get_Range(startCell, endCell);
range2 = sheet.get_Range(targetCell, this.missing);
range1.Copy(range2);
}
}
else {
this.KillExcelProcess(false);
throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
}
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
/// <summary>
/// 自动填充
/// </summary>
public void RangAutoFill() {
Excel.Range rng = workSheet.get_Range("B4", Type.Missing);
rng.Value2 = "星期一 ";
rng.AutoFill(workSheet.get_Range("B4", "B9"),
Excel.XlAutoFillType.xlFillWeekdays);
rng = workSheet.get_Range("C4", Type.Missing);
rng.Value2 = "一月";
rng.AutoFill(workSheet.get_Range("C4", "C9"),
Excel.XlAutoFillType.xlFillMonths);
rng = workSheet.get_Range("D4", Type.Missing);
rng.Value2 = "1";
rng.AutoFill(workSheet.get_Range("D4", "D9"),
Excel.XlAutoFillType.xlFillSeries);
rng = workSheet.get_Range("E4", Type.Missing);
rng.Value2 = "3";
rng = workSheet.get_Range("E5", Type.Missing);
rng.Value2 = "6";
rng = workSheet.get_Range("E4", "E5");
rng.AutoFill(workSheet.get_Range("E4", "E9"),
Excel.XlAutoFillType.xlFillSeries);
}
/// <summary>
/// 应用样式
/// </summary>
public void ApplyStyle() {
object missingValue = Type.Missing;
Excel.Range rng = workSheet.get_Range("B3", "L23");
Excel.Style style;
try {
style = workBook.Styles["NewStyle"];
}
// Style doesn't exist yet.
catch {
style = workBook.Styles.Add("NewStyle", missingValue);
style.Font.Name = "Verdana";
style.Font.Size = 12;
style.Font.Color = 255;
style.Interior.Color = (200 << 16) | (200 << 8) | 200;
style.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
}
rng.Value2 = "'Style Test";
rng.Style = "NewStyle";
rng.Columns.AutoFit();
}
#endregion
#region Set Style Methods
/// <summary>
/// 单元格背景色及填充方式
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="color">颜色索引</param>
public void CellsBackColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color) {
Excel.Range range = excelApp.get_Range(excelApp.Cells[startRow, startColumn], excelApp.Cells[endRow, endColumn]);
range.Interior.ColorIndex = color;
range.Interior.Pattern = Pattern.Solid;
}
/// <summary>
/// 单元格背景色及填充方式
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="color">颜色索引</param>
/// <param name="pattern">填充方式</param>
public void CellsBackColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color, Pattern pattern) {
Excel.Range range = excelApp.get_Range(excelApp.Cells[startRow, startColumn], excelApp.Cells[endRow, endColumn]);
range.Interior.ColorIndex = color;
range.Interior.Pattern = pattern;
}
/// <summary>
/// 设置行高
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="endRow">结束行</param>
/// <param name="height">行高</param>
public void SetRowHeight(int startRow, int endRow, int height) {
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)excelApp.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Rows[startRow.ToString() + ":" + endRow.ToString(), System.Type.Missing];
range.RowHeight = height;
}
/// <summary>
/// 自动调整行高
/// </summary>
/// <param name="columnNum">列号</param>
public void RowAutoFit(int rowNum) {
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)excelApp.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Rows[rowNum.ToString() + ":" + rowNum.ToString(), System.Type.Missing];
range.EntireColumn.AutoFit();
}
/// <summary>
/// 设置列宽
/// </summary>
/// <param name="startColumn">起始列(列对应的字母)</param>
/// <param name="endColumn">结束列(列对应的字母)</param>
/// <param name="width"></param>
public void SetColumnWidth(string startColumn, string endColumn, int width) {
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)excelApp.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[startColumn + ":" + endColumn, System.Type.Missing];
range.ColumnWidth = width;
}
/// <summary>
/// 设置列宽
/// </summary>
/// <param name="startColumn">起始列</param>
/// <param name="endColumn">结束列</param>
/// <param name="width"></param>
public void SetColumnWidth(int startColumn, int endColumn, int width) {
string strStartColumn = GetColumnName(startColumn);
string strEndColumn = GetColumnName(endColumn);
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)excelApp.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[strStartColumn + ":" + strEndColumn, System.Type.Missing];
range.ColumnWidth = width;
}
/// <summary>
/// 自动调整列宽
/// </summary>
/// <param name="columnNum">列号</param>
public void ColumnAutoFit(string column) {
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)excelApp.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[column + ":" + column, System.Type.Missing];
range.EntireColumn.AutoFit();
}
/// <summary>
/// 自动调整列宽
/// </summary>
/// <param name="columnNum">列号</param>
public void ColumnAutoFit(int columnNum) {
string strcolumnNum = GetColumnName(columnNum);
//获取当前正在使用的工作表
Excel.Worksheet worksheet = (Excel.Worksheet)excelApp.ActiveSheet;
Excel.Range range = (Excel.Range)worksheet.Columns[strcolumnNum + ":" + strcolumnNum, System.Type.Missing];
range.EntireColumn.AutoFit();
}
/// <summary>
/// 字体颜色
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="color">颜色索引</param>
public void FontColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color) {
Excel.Range range = excelApp.get_Range(excelApp.Cells[startRow, startColumn], excelApp.Cells[endRow, endColumn]);
range.Font.ColorIndex = color;
}
/// <summary>
/// 字体样式(加粗,斜体,下划线)
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="isBold">是否加粗</param>
/// <param name="isItalic">是否斜体</param>
/// <param name="underline">下划线类型</param>
public void FontStyle(int startRow, int startColumn, int endRow, int endColumn, bool isBold, bool isItalic, UnderlineStyle underline) {
Excel.Range range = excelApp.get_Range(excelApp.Cells[startRow, startColumn], excelApp.Cells[endRow, endColumn]);
range.Font.Bold = isBold;
range.Font.Underline = underline;
range.Font.Italic = isItalic;
}
/// <summary>
/// 单元格字体及大小
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="fontName">字体名称</param>
/// <param name="fontSize">字体大小</param>
public void FontNameSize(int startRow, int startColumn, int endRow, int endColumn, string fontName, int fontSize) {
Excel.Range range = excelApp.get_Range(excelApp.Cells[startRow, startColumn], excelApp.Cells[endRow, endColumn]);
range.Font.Name = fontName;
range.Font.Size = fontSize;
}
#endregion
#region 列头转换
/// <summary>
/// 最大列数
/// 2007:16384
/// 2003:256
/// </summary>
int MaxColumnCount = 16384;
/// <summary>
/// 将Excel列的字母索引值转换成整数索引值
/// </summary>
/// <param name="letter"></param>
/// <returns></returns>
public int LetterToInt(string letter) {
int n = 0;
if (letter.Trim().Length == 0)
throw new Exception("不接受空字符串！");
if (letter.Length == 1) {
char c1 = letter.ToCharArray()[0];
if (!char.IsLetter(c1)) {
throw new Exception("格式不正确，必须是字母！");
}
c1 = char.ToUpper(c1);
n = Convert.ToInt32(c1) - 64;
}
else {
char[] chs = letter.ToCharArray();
for (int i = 0; i < chs.Length; i++) {
if (!char.IsLetter(chs[i])) {
throw new Exception("格式不正确，必须是字母！");
}
char ctemp = char.ToUpper(chs[i]);
int col = Convert.ToInt32(ctemp) - 64;
n += Convert.ToInt32(Math.Pow(26, (chs.Length - i - 1))) * col;
if (n > MaxColumnCount)
throw new Exception(string.Format("索引超出范围，Excel的列索引不能超过{0}！", MaxColumnCount));
}
}
return n;
}
/// <summary>
/// 将Excel列的整数索引值转换为字符索引值
/// </summary>
/// <param name="n"></param>
/// <returns></returns>
public string IntToLetter(int n) {
if (n > MaxColumnCount)
throw new Exception(string.Format("索引超出范围，Excel的列索引不能超过{0}！", MaxColumnCount));
String[] COL_NAME = {
"A", "B", "C", "D", "E", "F", "G", "H", "I", "J",
"K", "L", "M", "N", "O", "P", "Q", "R", "S", "T",
"U", "V", "W", "X", "Y", "Z" };
int a = 0, b = n;
String col = "";
if (b < 26) {
col = COL_NAME[b];
}
else {
while (b >= 26) {
a = b % 26;
b = b / 26;
col = COL_NAME[a] + col;
}
col = COL_NAME[b - 1] + col;
}
return col;
}
#endregion
#region Output File(注意：如果目标文件已存在的话会出错)
/// <summary>
/// 输出Excel文件并退出
/// </summary>
public void OutputExcelFile() {
if (this.outputFile == null)
throw new Exception("没有指定输出文件路径！");
try {
workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
}
catch (Exception e) {
throw e;
}
finally {
this.Quit();
}
}
/// <summary>
/// 输出指定格式的文件（支持格式：HTML，CSV，TEXT，EXCEL）
/// </summary>
/// <param name="format">HTML，CSV，TEXT，EXCEL，XML</param>
public void OutputFile(string format) {
if (this.outputFile == null)
throw new Exception("没有指定输出文件路径！");
try {
switch (format) {
case "HTML": {
workBook.SaveAs(outputFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
case "CSV": {
workBook.SaveAs(outputFile, Excel.XlFileFormat.xlCSV, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
case "TEXT": {
workBook.SaveAs(outputFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
// case "XML":
// {
// workBook.SaveAs(outputFile,Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// break;
//
// }
default: {
workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
}
}
catch (Exception e) {
throw e;
}
finally {
this.Quit();
}
}
/// <summary>
/// 保存文件
/// </summary>
public void SaveFile() {
try {
workBook.Save();
}
catch (Exception e) {
throw e;
}
finally {
this.Quit();
}
}
/// <summary>
/// 另存文件
/// </summary>
public void SaveAsFile() {
if (this.outputFile == null)
throw new Exception("没有指定输出文件路径！");
try {
workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
}
catch (Exception e) {
throw e;
}
finally {
this.Quit();
}
}
/// <summary>
/// 将Excel文件另存为指定格式
/// </summary>
/// <param name="format">HTML，CSV，TEXT，EXCEL，XML</param>
public void SaveAsFile(string format) {
if (this.outputFile == null)
throw new Exception("没有指定输出文件路径！");
try {
switch (format) {
case "HTML": {
workBook.SaveAs(outputFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
case "CSV": {
workBook.SaveAs(outputFile, Excel.XlFileFormat.xlCSV, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
case "TEXT": {
workBook.SaveAs(outputFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
// case "XML":
// {
// workBook.SaveAs(outputFile,Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// break;
// }
default: {
workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
}
}
catch (Exception e) {
throw e;
}
finally {
this.Quit();
}
}
/// <summary>
/// 另存文件
/// </summary>
/// <param name="fileName">文件名</param>
public void SaveFile(string fileName) {
try {
workBook.SaveAs(fileName, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
}
catch (Exception e) {
throw e;
}
finally {
this.Quit();
}
}
/// <summary>
/// 将Excel文件另存为指定格式
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="format">HTML，CSV，TEXT，EXCEL，XML</param>
public void SaveAsFile(string fileName, string format) {
try {
switch (format) {
case "HTML": {
workBook.SaveAs(fileName, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
case "CSV": {
workBook.SaveAs(fileName, Excel.XlFileFormat.xlCSV, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
case "TEXT": {
workBook.SaveAs(fileName, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
// case "XML":
// {
// workBook.SaveAs(fileName,Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// break;
// }
default: {
workBook.SaveAs(fileName, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
break;
}
}
}
catch (Exception e) {
throw e;
}
finally {
this.Quit();
}
}
#endregion
#endregion
#region 私有方法
/// <summary>
/// 合并单元格，并赋值，对指定WorkSheet操作
/// </summary>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="beginColumnIndex">开始列索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="endColumnIndex">结束列索引</param>
/// <param name="text">合并后Range的值</param>
private void MergeCells(Excel.Worksheet sheet, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text) {
if (sheet == null)
return;
range = sheet.get_Range(sheet.Cells[beginRowIndex, beginColumnIndex], sheet.Cells[endRowIndex, endColumnIndex]);
range.ClearContents(); //先把Range内容清除，合并才不会出错
range.MergeCells = true;
range.Value2 = text;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
/// <summary>
/// 将指定索引列的数据相同的行合并，对指定WorkSheet操作
/// </summary>
/// <param name="columnIndex">要合并的列索引</param>
/// <param name="beginRowIndex">合并开始行索引</param>
/// <param name="rows">要合并的行数</param>
private void MergeRows(Excel.Worksheet sheet, int columnIndex, int beginRowIndex, int rows) {
int beginIndex = beginRowIndex;
int count = 0;
string text1;
string text2;
if (sheet == null)
return;
for (int j = beginRowIndex; j < beginRowIndex + rows; j++) {
range1 = (Excel.Range)sheet.Cells[j, columnIndex];
range2 = (Excel.Range)sheet.Cells[j + 1, columnIndex];
text1 = range1.Text.ToString();
text2 = range2.Text.ToString();
if (text1 == text2) {
++count;
}
else {
if (count > 0) {
this.MergeCells(sheet, beginIndex, columnIndex, beginIndex + count, columnIndex, text1);
}
beginIndex = j + 1; //设置开始合并行索引
count = 0; //计数器清0
}
}
}
/// <summary>
/// 计算WorkSheet数量
/// </summary>
/// <param name="rowCount">记录总行数</param>
/// <param name="rows">每WorkSheet行数</param>
public int GetSheetCount(int rowCount, int rows) {
int n = rowCount % rows; //余数
if (n == 0)
return rowCount / rows;
else
return Convert.ToInt32(rowCount / rows) + 1;
}
/// <summary>
/// 结束Excel进程
/// </summary>
public void KillExcelProcess(bool bAll) {
if (bAll) {
Process[] myProcesses;
myProcesses = Process.GetProcessesByName("Excel");
//得不到Excel进程ID，暂时只能判断进程启动时间
foreach (Process myProcess in myProcesses) {
myProcess.Kill();
continue;
}
}
else {
KillSpecialExcel();
}
}
[DllImport("user32.dll", SetLastError = true)]
static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId);
public void KillSpecialExcel() {
try {
if (excelApp != null) {
int lpdwProcessId;
GetWindowThreadProcessId((IntPtr)excelApp.Hwnd, out lpdwProcessId);
//c-s方式
if (lpdwProcessId > 0) {
System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
}
else {
Quit();
//excelApp.Quit();
//excelApp = null;
//b-s方式windows api失效，进程时间也报win32错误
//Process[] myProcesses;
//myProcesses = Process.GetProcessesByName("Excel");
//DateTime startTime;
//foreach (Process myProcess in myProcesses) {
// startTime = myProcess.StartTime;
// if (startTime > beforeTime && startTime < afterTime) {
// myProcess.Kill();
// }
// continue;
//}
}
}
}
catch { }
}
public void Quit() {
if (workBook != null)
workBook.Close(null, null, null);
if (excelApp != null) {
excelApp.Workbooks.Close();
excelApp.Quit();
}
if (range != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (range1 != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(range1);
range1 = null;
}
if (range2 != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(range2);
range2 = null;
}
if (textBox != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(textBox);
textBox = null;
}
if (workSheet != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
workSheet = null;
}
if (workBook != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
workBook = null;
}
if (excelApp != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;
}
GC.Collect();
}//end Quit
#endregion
#region get sheets
/// <summary>
/// 按Index得到WorkSheet
/// </summary>
/// <param name="index">序号</param>
public Excel.Worksheet GetSheet(int index) {
try {
Excel.Worksheet aimSheet = (Excel.Worksheet)workBook.Sheets.get_Item(index);
return aimSheet;
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
public Excel.Worksheet GetSheetByName(string strName) {
try {
for (int i = 1; i <= workBook.Sheets.Count; i++) {
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
if (workSheet.Name == strName)
return workSheet;
}
this.KillExcelProcess(false);
//throw new Exception("sheet not exist");
return null;
}
catch (Exception e) {
this.KillExcelProcess(false);
throw e;
}
}
public List<string> GetSheets() {
List<string> listN = new List<string>();
for (int i = 1; i <= workBook.Sheets.Count; i++) {
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
listN.Add(workSheet.Name);
}
return listN;
}
#endregion
#region IDisposable 成员
void IDisposable.Dispose() {
Quit();
}
#endregion
}
#region Common Excel Enum Properties
/// <summary>
/// HTML，CSV，TEXT，EXCEL，XML
/// </summary>
enum SaveAsFileFormat {
HTML,
CSV,
TEXT,
EXCEL,
XML
}
/// <summary>
/// 常用颜色定义,对就Excel中颜色名
/// </summary>
public enum ColorIndex {
无色 = -4142,
自动 = -4105,
黑色 = 1,
褐色 = 53,
橄榄 = 52,
深绿 = 51,
深青 = 49,
深蓝 = 11,
靛蓝 = 55,
灰色80 = 56,
深红 = 9,
橙色 = 46,
深黄 = 12,
绿色 = 10,
青色 = 14,
蓝色 = 5,
蓝灰 = 47,
灰色50 = 16,
红色 = 3,
浅橙色 = 45,
酸橙色 = 43,
海绿 = 50,
水绿色 = 42,
浅蓝 = 41,
紫罗兰 = 13,
灰色40 = 48,
粉红 = 7,
金色 = 44,
黄色 = 6,
鲜绿 = 4,
青绿 = 8,
天蓝 = 33,
梅红 = 54,
灰色25 = 15,
玫瑰红 = 38,
茶色 = 40,
浅黄 = 36,
浅绿 = 35,
浅青绿 = 34,
淡蓝 = 37,
淡紫 = 39,
白色 = 2
}
/// <summary>
/// 水平对齐方式
/// </summary>
public enum ExcelHAlign {
常规 = 1,
靠左,
居中,
靠右,
填充,
两端对齐,
跨列居中,
分散对齐
}
/// <summary>
/// 垂直对齐方式
/// </summary>
public enum ExcelVAlign {
靠上 = 1,
居中,
靠下,
两端对齐,
分散对齐
}
/// <summary>
/// 线粗
/// </summary>
public enum BorderWeight {
极细 = 1,
细 = 2,
粗 = -4138,
极粗 = 4
}
/// <summary>
/// 线样式
/// </summary>
public enum LineStyle {
连续直线 = 1,
短线 = -4115,
线点相间 = 4,
短线间两点 = 5,
点 = -4118,
双线 = -4119,
无 = -4142,
少量倾斜点 = 13
}
/// <summary>
/// 下划线方式
/// </summary>
public enum UnderlineStyle {
无下划线 = -4142,
双线 = -4119,
双线充满全格 = 5,
单线 = 2,
单线充满全格 = 4
}
/// <summary>
/// 单元格填充方式
/// </summary>
public enum Pattern {
Automatic = -4105,
Checker = 9,
CrissCross = 16,
Down = -4121,
Gray16 = 17,
Gray25 = -4124,
Gray50 = -4125,
Gray75 = -4126,
Gray8 = 18,
Grid = 15,
Horizontal = -4128,
LightDown = 13,
LightHorizontal = 11,
LightUp = 14,
LightVertical = 12,
None = -4142,
SemiGray75 = 10,
Solid = 1,
Up = -4162,
Vertical = -4166
}
#endregion
}
