using System; using System.IO; using System.Text; using System.Data; using System.Reflection; using System.Diagnostics; using System.Collections; using System.Windows.Forms; using Bn6.Common; using System.Data.OleDb; //using cfg = System.Configuration; /// <summary> /// 说 明：Excel输出打印模块 /// 暂时不提供操作Excel对象样式方法，样式可以在Excel模板中设置好 /// </summary> public class ExcelHelper { #region 成员变量 private string templetFile = null; private string outputFile = null; private object missing = Missing.Value; private DateTime beforeTime; //Excel启动之前时间 private DateTime afterTime; //Excel启动之后时间 Excel.Application app; 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 = "页"; enum ColumnName { A1 = 1, B1, C1, D1, E1, F1, G1, H1, I1, J1, K1, L1, M1, N1, O1, P1, Q1, R1, S1, T1, U1, V1, W1, X1, Y1, Z1 }; #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 ExcelHelper /// <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; //创建一个Application对象并使其可见 beforeTime = DateTime.Now; app = new Excel.ApplicationClass(); app.Visible = true; afterTime = DateTime.Now; //打开模板文件，得到WorkBook对象 workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); //得到WorkSheet对象 workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1); } /// <summary> /// 构造函数，打开一个已有的工作簿 /// </summary> /// <param name="fileName">Excel文件名</param> public ExcelHelper(string fileName) { if (!File.Exists(fileName)) throw new Exception("指定路径的Excel文件不存在！"); //创建一个Application对象并使其可见 beforeTime = DateTime.Now; app = new Excel.ApplicationClass(); app.Visible = true; afterTime = DateTime.Now; //打开一个WorkBook workBook = app.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); //得到WorkSheet对象 workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1); } /// <summary> /// 构造函数，新建一个工作簿 /// </summary> public ExcelHelper() { //创建一个Application对象并使其可见 beforeTime = DateTime.Now; app = new Excel.ApplicationClass(); //app.Visible = true; 不自动打开 afterTime = DateTime.Now; //新建一个WorkBook workBook = app.Workbooks.Add(Type.Missing); //得到WorkSheet对象 workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1); } #endregion #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; string[,] ss = new string[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.Value = 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列数 //利用二维数组批量写入 string[,] arr = new string[rowCount + 1, colCount + 1]; //for (int i = 0; i < colCount; i++) //{ // arr[0, i] = dt.Columns[i].ColumnName.ToString(); //} 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 + 1, colCount + 1); range.Value = 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; string[,] ss = new string[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.Value = 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(string[,] 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; string[,] ss = new string[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.Value = ss; } }//end ArrayToExcel /// <summary> /// 将二维数组数据写入Excel文件（不分页） /// </summary> /// <param name="arr">二维数组</param> /// <param name="top">行索引</param> /// <param name="left">列索引</param> public void ArrayToExcel(string[,] 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(string[,] 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.Value = 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(string[,] 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.Value = 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, string[,] arr, int top, int left) { if (sheetIndex > this.WorkSheetCount) { this.KillExcelProcess(); 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(string[,] 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; string[,] ss = new string[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.Value = 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(); throw new Exception("名称为\"" + sheetName + "\"的工作表不存在"); } } catch (Exception e) { this.KillExcelProcess(); throw e; } } /// <summary> /// 隐藏指定索引的工作表 /// </summary> /// <param name="sheetIndex"></param> public void HiddenWorkSheet(int sheetIndex) { if (sheetIndex > this.WorkSheetCount) { this.KillExcelProcess(); 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(); 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(); throw new Exception("名称为\"" + sheetName + "\"的工作表不存在"); } } catch (Exception e) { this.KillExcelProcess(); 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(); 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(); 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(); throw new Exception("名称为\"" + sheetName + "\"的工作表不存在"); } } catch (Exception e) { this.KillExcelProcess(); throw e; } } /// <summary> /// 根据索引删除工作表 /// </summary> /// <param name="sheetIndex"></param> public void DeleteWorkSheet(int sheetIndex) { if (sheetIndex > this.WorkSheetCount) { this.KillExcelProcess(); 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(); 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(); 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(); 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(); 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(); 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(); 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(); 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(); 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(); 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(); 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(); 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(); 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(); 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(); throw new Exception("向单元格[" + dic.Key + "]写数据出错！"); } } } else { this.KillExcelProcess(); throw new Exception("名称为\"" + sheetName + "\"的工作表不存在"); } } catch (Exception e) { this.KillExcelProcess(); 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.Value = 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(); 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.Value = 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(); 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); } } } catch (Exception e) { this.KillExcelProcess(); 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(); 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); } } catch (Exception e) { this.KillExcelProcess(); 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(); 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(); 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(); 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(); 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(); 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(); 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); } } } catch (Exception e) { this.KillExcelProcess(); 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(); 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); } } catch (Exception e) { this.KillExcelProcess(); 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(); 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(); 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(); 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(); 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(); 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(); throw e; } } #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(); 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(); 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(); throw new Exception("名称为\"" + sheetName + "\"的工作表不存在"); } } catch (Exception e) { this.KillExcelProcess(); 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.Value = "一月"; 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 ExcelHelper Kit /// <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 >= 2) { char c1 = letter.ToCharArray(0, 2)[0]; char c2 = letter.ToCharArray(0, 2)[1]; if (!char.IsLetter(c1) || !char.IsLetter(c2)) { throw new Exception("格式不正确，必须是字母！"); } c1 = char.ToUpper(c1); c2 = char.ToUpper(c2); int i = Convert.ToInt32(c1) - 64; int j = Convert.ToInt32(c2) - 64; n = i * 26 + j; } 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; } if (n > 256) throw new Exception("索引超出范围，Excel的列索引不能超过256！"); return n; } /// <summary> /// 将Excel列的整数索引值转换为字符索引值 /// </summary> /// <param name="n"></param> /// <returns></returns> public string IntToLetter(int n) { if (n > 256) throw new Exception("索引超出范围，Excel的列索引不能超过256！"); int i = Convert.ToInt32(n / 26); int j = n % 26; char c1 = Convert.ToChar(i + 64); char c2 = Convert.ToChar(j + 64); if (n > 26) return c1.ToString() + c2.ToString(); else if (n == 26) return "Z"; else return c2.ToString(); } #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); } catch (Exception e) { throw e; } finally { this.Dispose(); } } /// <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); break; } case "CSV": { workBook.SaveAs(outputFile, Excel.XlFileFormat.xlCSV, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing); break; } case "TEXT": { workBook.SaveAs(outputFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, 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); break; } } } catch (Exception e) { throw e; } finally { this.Dispose(); } } /// <summary> /// 保存文件 /// </summary> public void SaveFile() { try { workBook.Save(); } catch (Exception e) { throw e; } finally { this.Dispose(); } } /// <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); } catch (Exception e) { throw e; } finally { this.Dispose(); } } /// <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); break; } case "CSV": { workBook.SaveAs(outputFile, Excel.XlFileFormat.xlCSV, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing); break; } case "TEXT": { workBook.SaveAs(outputFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, 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); break; } } } catch (Exception e) { throw e; } finally { this.Dispose(); } } /// <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); } catch (Exception e) { throw e; } finally { this.Dispose(); } } /// <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); break; } case "CSV": { workBook.SaveAs(fileName, Excel.XlFileFormat.xlCSV, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing); break; } case "TEXT": { workBook.SaveAs(fileName, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, 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); break; } } } catch (Exception e) { throw e; } finally { this.Dispose(); } } #endregion #region 通过流的方式把字符串写到Excel /// <summary> /// 把DataTable记录导出生成Excel文件 /// </summary> /// <param name="dt">导出的数据表</param> /// <returns>返回bool</returns> public static bool DTSaveToExcel(string strFileName, DataTable dt) { StreamWriter stream = null; try { stream = new StreamWriter(strFileName, false, Encoding.Default); string sData = ""; int r = 0; int c = 0; //列 for (c = 0; c < dt.Columns.Count; c++) { if (dt.Columns[c].ColumnName.ToString()[0] != '_') { sData += dt.Columns[c].ColumnName.ToString() + "\t"; } } sData += "\r"; //数据项 for (r = 0; r < dt.Rows.Count; r++) { for (c = 0; c < dt.Columns.Count; c++) { if (dt.Columns[c].ColumnName.ToString()[0] != '_') { sData += "=T(\"" + dt.Rows[r][c].ToString() + "\")\t"; } } sData += "\r"; } stream.Write(sData); } catch (Exception ex) { return false; } finally { if (stream != null) stream.Close(); } return true; } /// <summary> /// 把gridview记录导出生成Excel文件,CVS类型。。 /// </summary> /// <param name="dt">导出的数据表</param> /// <returns>返回bool</returns> public static bool DGVSaveToExcel(string strFileName, DataGridView dv) { StreamWriter stream = null; try { stream = new StreamWriter(strFileName, false, Encoding.Default); string sData = ""; int r = 0; int c = 0; //列 for (c = 0; c < dv.ColumnCount; c++) { if (dv.Columns[c].HeaderText.ToString()[0] != '_') { sData += dv.Columns[c].HeaderText.ToString() + "\t"; } } sData += "\r"; //数据项 for (r = 0; r < dv.Rows.Count; r++) { for (c = 0; c < dv.ColumnCount; c++) { if (dv.Columns[c].HeaderText.ToString()[0] != '_') { //sData += "=T(\"" + dv[c,r].Value.ToString() + "\")\t"; sData += dv[c, r].Value == null ? "" + "\t" : dv[c, r].Value.ToString() + "\t"; } } sData += "\r"; } stream.Write(sData); } catch (Exception ex) { return false; } finally { if (stream != null) stream.Close(); } return true; } /// <summary> /// 把gridview记录导出生成Excel文件,可以设置格式。 /// </summary> /// <param name="dt">导出的数据表</param> /// <returns>返回bool</returns> public static bool DGVSaveToExcel_1(string strFileName, DataGridView dv) { StreamWriter stream = null; StringBuilder sb = new StringBuilder(); int r = 0; int c = 0; try { sb.AppendLine("<meta http-equiv=\"Content-Type\"content=\"text/html;charset=utf8\">"); sb.AppendLine("<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">"); //写出列名 sb.AppendLine("<tr style=\"font-weight:bold;white-space:nowrap;\">"); for (c = 0; c < dv.ColumnCount; c++) { if (dv.Columns[c].HeaderText.ToString()[0] != '_') { sb.AppendLine("<td>" + dv.Columns[c].HeaderText.ToString() + "</td>"); } } sb.AppendLine("</tr>"); //数据项 for (r = 0; r < dv.Rows.Count; r++) { sb.Append("<tr>"); for (c = 0; c < dv.ColumnCount; c++) { if (dv.Columns[c].HeaderText.ToString()[0] != '_') { string fieldtype = "System.String"; if (dv.Columns[c].DataPropertyName != "选择") { fieldtype = ((DataTable)dv.DataSource).Columns[dv.Columns[c].DataPropertyName].DataType.ToString(); } string svalue = dv[c, r].Value == null ? "" : dv[c, r].Value.ToString(); if (fieldtype == "System.String") { sb.Append("<td style = \"vnd.ms-excel.numberformat:@\">" + svalue + "</td>"); } else { sb.Append("<td>" + svalue + "</td>"); } } } sb.Append("</tr>"); } sb.AppendLine("</table>"); string tmp = sb.ToString(); stream = new StreamWriter(strFileName, false, Encoding.UTF8); stream.Write(tmp); } catch (Exception ex) { return false; } finally { if (stream != null) stream.Close(); } return true; } #endregion #region 通过对象方式把字符串导出到Excel /// <summary> /// 通过对象方式把字符串导出到Excel /// </summary> /// <param name="filePath">文件路径</param> /// <param name="dt">DataTable</param> /// <returns></returns> public static bool ExportToExcel(string filePath, DataTable dt) { Excel.Application oXL; Excel._Workbook oWB; Excel._Worksheet oSheet; Excel.Range oRng; object oMissing = System.Reflection.Missing.Value; //Start Excel and get Application object. oXL = new Excel.Application(); oXL.Visible = false; bool result = false; try { if (filePath.Trim() == "") { return false; } //文件不存在，则创建文件 if (!File.Exists(filePath)) { FileStream fs = File.Create(filePath); fs.Close(); fs.Dispose(); } //Get a new workbook. oWB = (Excel._Workbook)(oXL.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing)); oXL.DisplayAlerts = false; oSheet = (Excel._Worksheet)oWB.Worksheets.Add(oMissing, oMissing, 1, oMissing); oSheet.Name = dt.TableName.Trim(); int rowIndex = 1; int colIndex = 0; for (int j = 0; j < dt.Columns.Count; j++) { //Add table headers going cell by cell. if (dt.Columns[j].ColumnName.StartsWith("_")) { continue; } oSheet.Cells[1, colIndex + 1] = dt.Columns[j].ColumnName.Trim(); colIndex++; } string maxHeaderCell = NumberToChar(colIndex) + "1"; //Format A1:D1 as bold, vertical alignment = center. oSheet.get_Range("A1", maxHeaderCell).Font.Bold = true; oSheet.get_Range("A1", maxHeaderCell).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; // Create an array to multiple values at once. string[,] saNames = new string[dt.Rows.Count, dt.Columns.Count]; int contentCellIndex = 0; for (int n = 0; n < dt.Rows.Count; n++) { for (int m = 0; m < dt.Columns.Count; m++) { if (dt.Columns[m].ColumnName.StartsWith("_")) { continue; } saNames[n, contentCellIndex] = dt.Rows[n][dt.Columns[m].ColumnName.Trim()].ToString(); contentCellIndex++; } contentCellIndex = 0; rowIndex++; } oSheet.get_Range("A2", NumberToChar(colIndex) + rowIndex.ToString()).NumberFormatLocal = "@"; oSheet.get_Range("A2", NumberToChar(colIndex) + rowIndex.ToString()).Value2 = saNames; oRng = oSheet.get_Range("A1", maxHeaderCell); oRng.Cells.Columns.AutoFit(); oSheet.Cells.Columns.Select(); oSheet.Cells.Columns.AutoFit(); // 保存并关闭这个工作簿 oWB.Close(Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing); System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB); //Make sure Excel is visible and give the user control //of Microsoft Excel's lifetime. //oXL.Visible = true; //oXL.UserControl = true; result = true; } catch (Exception ex) { result = false; Msg.InfoBox(ex.Message); } finally { oXL.Quit(); //釋放Excel資源 System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL); oWB = null; oSheet = null; oRng = null; oXL = null; GC.Collect(); } return result; } private static string NumberToChar(int number) { if (1 <= number && 26 >= number) { int num = number + 64; System.Text.ASCIIEncoding asciiEncoding = new System.Text.ASCIIEncoding(); byte[] btNumber = new byte[] { (byte)num }; return asciiEncoding.GetString(btNumber); } else if (number > 26) { int NewNum = number % 26; int count = number / 26; string ss = NumberToChar(count); int num = NewNum + 64; System.Text.ASCIIEncoding asciiEncoding = new System.Text.ASCIIEncoding(); byte[] btNumber = new byte[] { (byte)num }; return ss + asciiEncoding.GetString(btNumber); } return "数字不在转换范围内"; } #endregion #region 通过对象方式把字符串导出到Excel /// <summary> /// 通过对象方式把字符串导出到Excel /// </summary> /// <param name="filePath">文件路径</param> /// <param name="dt">DataTable</param> /// <returns></returns> public static bool ExportToExcel(string filePath, DataGridView dv,string sheetname) { Excel.Application oXL; Excel._Workbook oWB; Excel._Worksheet oSheet; Excel.Range oRng; object oMissing = System.Reflection.Missing.Value; //Start Excel and get Application object. oXL = new Excel.Application(); oXL.Visible = false; bool result = false; try { if (filePath.Trim() == "") { return false; } //文件不存在，则创建文件 if (!File.Exists(filePath)) { FileStream fs = File.Create(filePath); fs.Close(); fs.Dispose(); } //Get a new workbook. oWB = (Excel._Workbook)(oXL.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing)); oXL.DisplayAlerts = false; oSheet = (Excel._Worksheet)oWB.Worksheets.Add(oMissing, oMissing, 1, oMissing); oSheet.Name = sheetname.Trim(); int rowIndex = 1; int colIndex = 0; for (int j = 0; j < dv.Columns.Count; j++) { //Add table headers going cell by cell. if (!dv.Columns[j].Visible) { continue; } oSheet.Cells[1, colIndex + 1] = dv.Columns[j].HeaderText.Trim(); colIndex++; } string maxHeaderCell = NumberToChar(colIndex) + "1"; //Format A1:D1 as bold, vertical alignment = center. oSheet.get_Range("A1", maxHeaderCell).Font.Bold = true; oSheet.get_Range("A1", maxHeaderCell).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; oSheet.get_Range("A1", maxHeaderCell).NumberFormatLocal = "@"; // Create an array to multiple values at once. string[,] saNames = new string[dv.Rows.Count, dv.Columns.Count]; int contentCellIndex = 0; for (int n = 0; n < dv.Rows.Count; n++) { for (int m = 0; m < dv.Columns.Count; m++) { if (!dv.Columns[m].Visible) { continue; } string svalue = dv[m, n].Value == null ? "" : dv[m, n].Value.ToString(); saNames[n, contentCellIndex] = svalue; contentCellIndex++; } contentCellIndex = 0; rowIndex++; } oSheet.get_Range("A2", NumberToChar(colIndex) + rowIndex.ToString()).NumberFormatLocal = "0_ "; oSheet.get_Range("A2", NumberToChar(colIndex) + rowIndex.ToString()).Value2 = saNames; oRng = oSheet.get_Range("A1", maxHeaderCell); oRng.Cells.Columns.AutoFit(); oSheet.Cells.Columns.Select(); oSheet.Cells.Columns.AutoFit(); // 保存并关闭这个工作簿 oWB.Close(Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing); System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB); //Make sure Excel is visible and give the user control //of Microsoft Excel's lifetime. //oXL.Visible = true; //oXL.UserControl = true; result = true; } catch (Exception ex) { result = false; Msg.InfoBox(ex.Message); } finally { oXL.Quit(); //釋放Excel資源 System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL); oWB = null; oSheet = null; oRng = null; oXL = null; GC.Collect(); } return result; } #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.Value = 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() { Process[] myProcesses; DateTime startTime; myProcesses = Process.GetProcessesByName("Excel"); //得不到Excel进程ID，暂时只能判断进程启动时间 foreach (Process myProcess in myProcesses) { startTime = myProcess.StartTime; if (startTime > beforeTime && startTime < afterTime) { myProcess.Kill(); } } } private void Dispose() { workBook.Close(null, null, null); app.Workbooks.Close(); app.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 (app != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(app); app = null; } GC.Collect(); this.KillExcelProcess(); }//end Dispose #endregion #region 通过对象方式把字符串导出到Excel(不导出第一列) /// <summary> /// 通过对象方式把字符串导出到Excel(不导出第一列) /// </summary> /// <param name="filePath">文件路径</param> /// <param name="dt">DataTable</param> /// <returns></returns> public static bool ExportToExcelNoFirst(string filePath, DataTable dt) { Excel.Application oXL; Excel._Workbook oWB; Excel._Worksheet oSheet; Excel.Range oRng; object oMissing = System.Reflection.Missing.Value; //Start Excel and get Application object. oXL = new Excel.Application(); oXL.Visible = false; bool result = false; try { if (filePath.Trim() == "") { return false; } //文件不存在，则创建文件 if (!File.Exists(filePath)) { FileStream fs = File.Create(filePath); fs.Close(); fs.Dispose(); } //Get a new workbook. oWB = (Excel._Workbook)(oXL.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing)); oXL.DisplayAlerts = false; oSheet = (Excel._Worksheet)oWB.Worksheets.Add(oMissing, oMissing, 1, oMissing); oSheet.Name = dt.TableName.Trim(); int rowIndex = 1; int colIndex = 0; for (int j = 1; j < dt.Columns.Count; j++) { //Add table headers going cell by cell. if (dt.Columns[j].ColumnName.StartsWith("_")) { continue; } oSheet.Cells[1, colIndex + 1] = dt.Columns[j].ColumnName.Trim(); colIndex++; } string maxHeaderCell = NumberToChar(colIndex) + "1"; //Format A1:D1 as bold, vertical alignment = center. oSheet.get_Range("A1", maxHeaderCell).Font.Bold = true; oSheet.get_Range("A1", maxHeaderCell).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; // Create an array to multiple values at once. string[,] saNames = new string[dt.Rows.Count, dt.Columns.Count]; int contentCellIndex = 0; for (int n = 0; n < dt.Rows.Count; n++) { for (int m = 1; m < dt.Columns.Count; m++) { if (dt.Columns[m].ColumnName.StartsWith("_")) { continue; } saNames[n, contentCellIndex] = dt.Rows[n][dt.Columns[m].ColumnName.Trim()].ToString(); contentCellIndex++; } contentCellIndex = 0; rowIndex++; } oSheet.get_Range("A2", NumberToChar(colIndex) + rowIndex.ToString()).NumberFormatLocal = "@"; oSheet.get_Range("A2", NumberToChar(colIndex) + rowIndex.ToString()).Value2 = saNames; oRng = oSheet.get_Range("A1", maxHeaderCell); //oRng.NumberFormatLocal = "@";//设置整个excel表格的单元格格式设置为文本 //oRng.NumberFormat = "@";//设置单元格为文本格式 oRng.Cells.Columns.AutoFit(); oSheet.Cells.Columns.Select(); oSheet.Cells.Columns.AutoFit(); // 保存并关闭这个工作簿 oWB.Close(Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing); System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB); //Make sure Excel is visible and give the user control //of Microsoft Excel's lifetime. //oXL.Visible = true; //oXL.UserControl = true; result = true; } catch (Exception ex) { result = false; Msg.InfoBox(ex.Message); } finally { oXL.Quit(); //釋放Excel資源 System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL); oWB = null; oSheet = null; oRng = null; oXL = null; GC.Collect(); } return result; } #endregion #region 导出EXCEL并排除纯数字时的科学计数法(数据源从第二列开始) /// <summary> /// 导出EXCEL并排除纯数字时的科学计数法(数据源从第二列开始) /// </summary> /// <param name="dt">数据集</param> /// <param name="filePath">路径</param> /// <returns></returns> public static bool DoExport(DataTable dt, string filePath) { bool result = false; try { if (filePath.Trim() == "") { return false; } Excel._Application xlapp = new Excel.ApplicationClass(); Excel.Workbook xlbook = xlapp.Workbooks.Add(true); Excel.Worksheet xlsheet = (Excel.Worksheet)xlbook.Worksheets[1]; Excel.Range range = xlsheet.get_Range(xlapp.Cells[1, 1], xlapp.Cells[1, dt.Columns.Count]); //range.NumberFormatLocal = "@"; range.NumberFormat = "@"; xlapp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter; //截取工作表名 string[] sheetName1 = filePath.Split('\\'); string[] sheetName2 = sheetName1[sheetName1.Length - 1].ToString().Split('.'); xlsheet.Name = sheetName2[0].ToString(); //开始写入每列的标题 int colIndex = 1; for (int k = 1; k < dt.Columns.Count; k++) { if (dt.Columns[k].ColumnName.StartsWith("_")) { continue; } xlsheet.Cells[1, colIndex] = dt.Columns[k].ColumnName.Trim(); colIndex++; } //开始写入内容 int rowIndex = 2; colIndex = 1; for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 1; j < dt.Columns.Count; j++) { if (dt.Columns[j].ColumnName.StartsWith("_")) { continue; } xlsheet.Cells[rowIndex, colIndex] = "'" + dt.Rows[i][j].ToString().Trim(); xlsheet.Cells.ColumnWidth = dt.Rows[i][j].ToString().Length; colIndex++; } rowIndex++; colIndex = 1; } //保存 xlsheet.SaveAs(filePath, Excel.XlFileFormat.xlTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing); xlbook.Close(false, Type.Missing, Type.Missing); xlapp.Quit(); GC.Collect(); result = true; } catch (Exception ex) { result = false; Msg.InfoBox(ex.Message); } return result; } #endregion #region 导出EXCEL并排除纯数字时的科学计数法(数据源从第一列开始) /// <summary> /// 导出EXCEL并排除纯数字时的科学计数法(数据源从第一列开始) /// </summary> /// <param name="filePath">路径</param> /// <param name="dt">数据集</param> /// <returns></returns> public static bool DoExportAll(string filePath, DataTable dt) { bool result = false; try { if (filePath.Trim() == "") { return false; } Excel._Application xlapp = new Excel.ApplicationClass(); Excel.Workbook xlbook = xlapp.Workbooks.Add(true); Excel.Worksheet xlsheet = (Excel.Worksheet)xlbook.Worksheets[1]; Excel.Range range = xlsheet.get_Range(xlapp.Cells[1, 1], xlapp.Cells[1, dt.Columns.Count]); //range.NumberFormatLocal = "@"; range.NumberFormat = "@"; xlapp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter; //截取工作表名 string[] sheetName1 = filePath.Split('\\'); string[] sheetName2 = sheetName1[sheetName1.Length - 1].ToString().Split('.'); xlsheet.Name = sheetName2[0].ToString(); //开始写入每列的标题 int colIndex = 1; for (int k = 0; k < dt.Columns.Count; k++) { if (dt.Columns[k].ColumnName.StartsWith("_")) { continue; } xlsheet.Cells[1, colIndex] = dt.Columns[k].ColumnName.Trim(); colIndex++; } //开始写入内容 int rowIndex = 2; colIndex = 1; for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { if (dt.Columns[j].ColumnName.StartsWith("_")) { continue; } xlsheet.Cells[rowIndex, colIndex] = "'" + dt.Rows[i][j].ToString().Trim(); colIndex++; } rowIndex++; colIndex = 1; } //保存 xlsheet.SaveAs(filePath, Excel.XlFileFormat.xlTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing); xlbook.Close(false, Type.Missing, Type.Missing); xlapp.Quit(); GC.Collect(); result = true; } catch (Exception ex) { result = false; Msg.InfoBox(ex.Message); } return result; } #endregion #region Excel数据导入 /// Excel数据导入 /// </summary> /// <param name="filePath">文件路径</param> /// <returns></returns> public static DataTable ImportExcelData(string filePath) { DataTable sheetNameDt = null; //之前 excel 2003 //string conStr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'" // , filePath); //现在 以下此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串) string conStr = string.Format("Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; " + "HDR=Yes; IMEX=1'", filePath); //备注： "HDR=yes;"是说Excel文件的第一行是列名而不是数据，"HDR=No;"正好与前面的相反。 //"IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。 OleDbConnection conn = new OleDbConnection(conStr); try { conn.Open(); sheetNameDt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); conn.Close(); } catch (Exception ex) { throw new ApplicationException(ex.Message); } string sql = string.Format("SELECT * FROM [{0}$]", sheetNameDt.Rows[0][1].ToString().Trim().Replace("'", "").TrimEnd('$')); OleDbCommand comm = conn.CreateCommand(); comm.CommandText = sql; OleDbDataAdapter adpt = new OleDbDataAdapter(); adpt.SelectCommand = comm; DataSet ds = new DataSet(); try { adpt.Fill(ds); return ds.Tables[0]; } catch (Exception ex) { throw new ApplicationException(ex.Message); } } #endregion }//end class
浙公网安备 33010602011771号