转载：http://www.cnblogs.com/fellowcheng/archive/2010/08/21/1805158.html
ExcelHelper(Excel2007) Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ -->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(" "); } sb.Append(" "); } 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 }