摘要:本文主要介绍了c#编程实例的导出数据至excel模板中,希望对大家学习c#编程实例有所帮助。
c#导出数据至excel模板中,可分页
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Runtime.InteropServices; using System.Reflection; using System.Data; using System.Diagnostics; using System.IO; using Aspose.Cells; namespace DBHelper { public class ExcelHelper : IDisposable { private string templetFile = String.Empty; private string outputFile = String.Empty; private DateTime beforeTime; //Excel启动之前时间 private DateTime afterTime; //Excel启动之后时间 Workbook workBook; public Worksheet workSheet; Range range; private int sheetCount = 1; //WorkSheet数量 private string sheetPrefixName = "Sheet"; #region 公共属性 public string SheetPrefixName { set { this.sheetPrefixName = value; } } public int WorkSheetCount { get { return workBook.Worksheets.Count; }//.Sheets.Count; } } public string OutputFilePath { set { this.outputFile = value; } } #endregion #region CreateExcelFile /// <summary> /// 构造函数,将一个已有Excel工作簿作为模板,并指定输出路径 /// </summary> ///<param name="templetFilePath">Excel模板文件路径 ///<param name="outputFilePath">输出Excel文件路径 public ExcelHelper(string templetFilePath, string outputFilePath) { if (templetFilePath == String.Empty) throw new Exception("Excel模板文件路径不能为空!"); if (outputFilePath == String.Empty) throw new Exception("输出Excel文件路径不能为空!"); if (!File.Exists(templetFilePath)) throw new Exception("指定路径的Excel模板文件不存在!"); this.templetFile = templetFilePath; this.outputFile = outputFilePath; //创建一个Application对象并使其可见 beforeTime = DateTime.Now; afterTime = DateTime.Now; //打开模板文件,得到WorkBook对象 //workBook = new Workbook(templetFilePath, new LoadOptions(LoadFormat.Unknown)); workBook = new Workbook(templetFilePath, new LoadOptions()); workSheet = workBook.Worksheets[0]; } /// <summary> /// 构造函数,打开一个已有的工作簿 /// </summary> ///<param name="fileName">Excel文件名 public ExcelHelper(string fileName) { if (!File.Exists(fileName)) throw new Exception("指定路径的Excel文件不存在!"); //创建一个Application对象并使其可见 beforeTime = DateTime.Now; afterTime = DateTime.Now; //打开一个WorkBook workBook = new Workbook(fileName, new LoadOptions()); workSheet = workBook.Worksheets[0]; } /// <summary> /// 构造函数,新建一个工作簿 /// </summary> public ExcelHelper() { //创建一个Application对象并使其可见 beforeTime = DateTime.Now; afterTime = DateTime.Now; workBook = new Workbook(); workSheet = workBook.Worksheets[0]; } #endregion #region WorkSheet Methods /// <summary> /// 改变当前工作表 /// </summary> ///<param name="sheetIndex">工作表索引 public void ChangeCurrentWorkSheet(int sheetIndex) { //若指定工作表索引超出范围,则不改变当前工作表 if (sheetIndex < 1) return; if (sheetIndex > this.WorkSheetCount) return; this.workSheet = workBook.Worksheets[sheetIndex];//(Excel.Worksheet)this.workBook.Sheets.get_Item(sheetIndex); } /// <summary> /// 隐藏指定名称的工作表 /// </summary> ///<param name="sheetName">工作表名称 public void HiddenWorkSheet(string sheetName) { try { Worksheet sheet = null; for (int i = 1; i <= this.WorkSheetCount; i++) { workSheet = (Worksheet)workBook.Worksheets[i];//.Sheets.get_Item(i); if (workSheet.Name == sheetName) sheet = workSheet; } if (sheet != null) sheet.VisibilityType = VisibilityType.Hidden;//.Visible = Excel.XlSheetVisibility.xlSheetHidden; else { throw new Exception("名称为\"" + sheetName + "\"的工作表不存在"); } } catch (Exception e) { throw e; } } /// <summary> /// 隐藏指定索引的工作表 /// </summary> ///<param name="sheetIndex"> public void HiddenWorkSheet(int sheetIndex) { if (sheetIndex > this.WorkSheetCount) { throw new Exception("索引超出范围,WorkSheet索引不能大于WorkSheet数量!"); } try { Worksheet sheet = null; sheet = (Worksheet)workBook.Worksheets[sheetIndex];//.Sheets.get_Item(sheetIndex); sheet.VisibilityType = VisibilityType.Hidden;// Excel.XlSheetVisibility.xlSheetHidden; } catch (Exception e) { throw e; } } /// <summary> /// 在指定名称的工作表后面拷贝指定个数的该工作表的副本,并重命名 /// </summary> ///<param name="sheetName">复制源【工作表名称】 ///<param name="sheetCount">工作表个数 public void CopyWorkSheets(string sheetName, int sheetCount) { try { Worksheet sheet = null; int sheetIndex = 0; if (!string.IsNullOrWhiteSpace(sheetName)) { for (int i = 0; i < this.WorkSheetCount; i++) { workSheet = workBook.Worksheets[i];//.Sheets.get_Item(i); if (workSheet.Name == sheetName) { sheet = workSheet; sheetIndex = workSheet.Index; } } } else { sheet = workSheet; } if (sheet != null) { int countWorkSheet = WorkSheetCount + sheetCount; for (int i = WorkSheetCount; i < countWorkSheet; i++) { string name = sheetName + "-" + Convert.ToString(i - sheetIndex + 1); Worksheet sheetTMP = workBook.Worksheets.Add(name); sheetTMP.Copy(sheet); } } else { throw new Exception("名称为\"" + sheetName + "\"的工作表不存在"); } } catch (Exception e) { throw e; } } public string lastErrorMSG = ""; /// <summary> /// 将一个工作表拷贝到另一个工作表后面 /// </summary> ///<param name="srcSheetIndex">拷贝源工作表索引 ///<param name="newSheetName">新工作表名称 /// <returns></returns> public bool CopyWorkSheet(int srcSheetIndex, string newSheetName) { if (srcSheetIndex > this.WorkSheetCount || srcSheetIndex < 0) { lastErrorMSG = "索引位置错误"; return false; } try { foreach (Worksheet sheet in workBook.Worksheets) { if (sheet.Name == newSheetName) { lastErrorMSG = "工作表中已经存在该名称,请选择其他名称"; return false; } } Worksheet srcSheet = (Worksheet)workBook.Worksheets[srcSheetIndex];//.Sheets.get_Item(srcSheetIndex); Worksheet aimSheet = (Worksheet)workBook.Worksheets.Add(newSheetName);//[aimSheetIndex];//Sheets.get_Item(aimSheetIndex); srcSheet.Copy(aimSheet);//(this.missing, aimSheet); return true; } catch (Exception e) { lastErrorMSG = e.Message; } return false; } /// <summary> /// 根据名称删除工作表 /// </summary> ///<param name="sheetName">工作表名称 /// <returns></returns> public bool DeleteWorkSheet(string sheetName) { try { Worksheet sheet = null; bool isHave = false; //找到名称位sheetName的工作表 for (int i = 1; i <= this.WorkSheetCount; i++) { workSheet = (Worksheet)workBook.Worksheets[i];//.Sheets.get_Item(i); if (workSheet.Name == sheetName) { sheet = workSheet; isHave = true; } } if (!isHave) { return true; } if (sheet != null) { workBook.Worksheets.RemoveAt(sheetName); return true; } else { return false; } } catch (Exception e) { lastErrorMSG = e.Message; } return false; } /// <summary> /// 根据索引删除工作表 /// </summary> ///<param name="sheetIndex">工作薄索引 /// <returns></returns> public bool DeleteWorkSheet(int sheetIndex) { if (sheetIndex > this.WorkSheetCount) { lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!"; return false; } try { workBook.Worksheets.RemoveAt(sheetIndex); return true; } catch (Exception e) { lastErrorMSG = e.Message; return false; } } #endregion /// <summary> /// 将指定索引列的数据相同的行合并,对每个WorkSheet操作 /// </summary> ///<param name="rowIndex">开始列索引 ///<param name="columnIndex">开始行索引 ///<param name="count">跨行个数 /// <returns></returns> public bool MergeRows(int rowIndex, int columnIndex, int count) { if (workSheet != null) { Cells cells = workSheet.Cells; cells.Merge(rowIndex, columnIndex, 1, count);//(rowIndex, columnIndex, count, 1);//, 1, endRowIndex - columnIndex); return true; } return false; } /// <summary> /// 合并行 /// </summary> ///<param name="sheetIndex">单元薄索引 ///<param name="rowIndex">行索引 ///<param name="columnIndex">列索引 ///<param name="count">合并行数 /// <returns></returns> public bool MergeRows(int sheetIndex, int rowIndex, int columnIndex, int count) { if (sheetIndex > this.WorkSheetCount) { lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!"; return false; } MergeRows(rowIndex, columnIndex, count); return true; } /// <summary> /// 合并列 /// </summary> ///<param name="rowIndex">开始行索引 ///<param name="columnIndex">开始列索引 ///<param name="count">合并列数 /// <returns></returns> public bool MergeColumns(int rowIndex, int columnIndex, int count) { if (workSheet != null) { Cells cells = workSheet.Cells; cells.Merge(rowIndex, columnIndex, count, 1); } return false; } /// <summary> /// 合并列 /// </summary> ///<param name="sheetIndex">单元薄索引 ///<param name="rowIndex">开始行索引 ///<param name="columnIndex">开始列索引 ///<param name="count">合并列数 /// <returns></returns> public bool MergeColumns(int sheetIndex, int rowIndex, int columnIndex, int count) { if (sheetIndex > this.WorkSheetCount) { lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!"; return false; } workSheet = workBook.Worksheets[sheetIndex]; return MergeColumns(rowIndex, columnIndex, count); // true; } /// <summary> /// /// </summary> ///<param name="rowIndex">开始行索引 ///<param name="columnIndex">开始列索引 ///<param name="countRow">合并行数 ///<param name="countColumn">合并列数 /// <returns></returns> public bool MergeRowsAndColumns(int rowIndex, int columnIndex, int countRow, int countColumn) { if (workSheet == null) { lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!"; return false; } if (countRow < 1 || countColumn < 1) { lastErrorMSG = "合并行和列数目设置有错"; return false; } Cells cells = workSheet.Cells; cells.Merge(rowIndex, columnIndex, countRow, countColumn); //count, 1); return true; } /// <summary> /// 合并行和列 /// </summary> ///<param name="sheetIndex">单元薄索引 ///<param name="rowIndex">开始行索引 ///<param name="columnIndex">开始列索引 ///<param name="countRow">合并行数 ///<param name="countColumn">合并列数 /// <returns></returns> public bool MergeRowsAndColumns(int sheetIndex, int rowIndex, int columnIndex, int countRow, int countColumn) { if (sheetIndex > this.WorkSheetCount) { lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!"; return false; } workSheet = workBook.Worksheets[sheetIndex]; return MergeRowsAndColumns(rowIndex, columnIndex, countRow, countColumn); } /// <summary> /// 插行(在指定行下面插入指定数量行) /// </summary> ///<param name="rowIndex">行号索引从0开始 ///<param name="count">插入行数 /// <returns></returns> public bool InsertRows(int rowIndex, int count) { try { rowIndex = rowIndex + 1; if (rowIndex < 0 || count < 0) { lastErrorMSG = "行号或行数错误"; return false; } workSheet.Cells.InsertRows(rowIndex, count); return true; } catch (Exception e) { lastErrorMSG = e.Message; return false; } } /// <summary> /// 插行(在指定行下面插入指定数量行) /// </summary> ///<param name="sheetIndex"> ///<param name="rowIndex"> ///<param name="count"> /// <returns></returns> public bool InsertRows(int sheetIndex, int rowIndex, int count) { if (sheetIndex > this.WorkSheetCount) { lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!"; return false; } try { workSheet = workBook.Worksheets[sheetIndex]; return InsertRows(rowIndex, count); } catch (Exception e) { lastErrorMSG = e.Message; return false; } } /// <summary> /// 复制行 /// </summary> ///<param name="sourceRowIndex">源行号索引 ///<param name="targetRowIndex">目标行索引 public bool CopyRow(int sourceRowIndex, int targetRowIndex) { if (sourceRowIndex < 0 || (targetRowIndex) < 0) { lastErrorMSG = "源行索引或目标行索引错误"; return false; } Cells cells = workSheet.Cells; workSheet.Cells.CopyRow(cells, sourceRowIndex, targetRowIndex); return true; } /// <summary> /// 复制多行 /// </summary> ///<param name="sheetIndex">单元薄索引 ///<param name="rowstartIndex">开始行索引 ///<param name="rowCount">(从开始行索引 )行数 ///<param name="targetRowIndex">目标行索引 /// <returns></returns> public bool CopyRows(int sheetIndex, int rowstartIndex, int rowCount, int targetRowIndex) { if (sheetIndex > this.WorkSheetCount) { lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!"; return false; } workSheet = workBook.Worksheets[sheetIndex]; try { Cells cells = workSheet.Cells; workSheet.Cells.CopyRows(cells, rowstartIndex, targetRowIndex, rowCount); return true; } catch (Exception e) { lastErrorMSG = e.Message; return false; } } /// <summary> /// 删除行 /// </summary> ///<param name="rowIndex">行索引 /// <returns></returns> public bool DeleteRow(int rowIndex) { try { workSheet.Cells.DeleteRow(rowIndex); return true; } catch (Exception e) { lastErrorMSG = e.Message; } return false; } /// <summary> /// 删除行 /// </summary> ///<param name="sheetIndex">单元薄索引 ///<param name="rowIndex">开始行索引 /// <returns></returns> public bool DeleteRow(int sheetIndex, int rowIndex) { if (sheetIndex > this.WorkSheetCount) { lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!"; return false; } try { workSheet = workBook.Worksheets[sheetIndex]; workSheet.Cells.DeleteRow(rowIndex); return true; } catch (Exception e) { lastErrorMSG = e.Message; } return false; } /// <summary> /// 删除多行 /// </summary> ///<param name="rowIndex">开始行索引 ///<param name="count">行数 /// <returns></returns> public bool DeleteRows(int rowIndex, int count) { try { workSheet.Cells.DeleteRows(rowIndex, count); return true; } catch (Exception e) { lastErrorMSG = e.Message; } return false; } /// <summary> /// 删除多行 /// </summary> ///<param name="sheetIndex">单元薄索引 ///<param name="rowIndex">开始行索引 ///<param name="count">行数 /// <returns></returns> public bool DeleteRows(int sheetIndex, int rowIndex, int count) { if (sheetIndex > this.WorkSheetCount) { lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!"; return false; } try { workSheet = workBook.Worksheets[sheetIndex]; return DeleteRows(rowIndex, count); } catch (Exception e) { lastErrorMSG = e.Message; } return false; } /// <summary> /// 插列 列的左侧 /// </summary> ///<param name="columnIndex">插入索引 /// <returns></returns> public bool InsertColumn(int columnIndex) { try { workSheet.Cells.InsertColumn(columnIndex);//.DeleteColumn(columnIndex); return true; } catch (Exception e) { lastErrorMSG = e.Message; return false; } } /// <summary> /// 插列 列的左侧 /// </summary> ///<param name="sheetIndex">单元薄索引 ///<param name="columnIndex">列索引 /// <returns></returns> public bool InsertColumn(int sheetIndex, int columnIndex) { if (sheetIndex > this.WorkSheetCount) { lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!"; return false; } workSheet = workBook.Worksheets[sheetIndex]; try { workSheet.Cells.InsertColumn(columnIndex);//.DeleteColumn(columnIndex); return InsertColumn(columnIndex); } catch (Exception e) { lastErrorMSG = e.Message; return false; } } /// <summary> /// 插列 列的左侧 /// </summary> ///<param name="columnIndex">列索引 ///<param name="count">插入列数 /// <returns></returns> public bool InsertColumns(int columnIndex, int count) { try { workSheet.Cells.InsertColumns(columnIndex, count); return true; } catch (Exception e) { lastErrorMSG = e.Message; return false; } } /// <summary> /// 插列 列的左侧 /// </summary> ///<param name="sheetIndex">单元薄索引 ///<param name="columnIndex">列索引 ///<param name="count">列个数 /// <returns></returns> public bool InsertColumns(int sheetIndex, int columnIndex, int count) { try { if (sheetIndex > this.WorkSheetCount) { lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!"; return false; } workSheet = workBook.Worksheets[sheetIndex]; //workSheet.Cells.InsertColumns(columnIndex, count); return InsertColumns(columnIndex, count); } catch (Exception e) { lastErrorMSG = e.Message; return false; } } /// <summary> /// 复制列 /// </summary> ///<param name="columnIndex"> ///<param name="count"> public bool CopyColumn(int columnIndex, int columnTargetIndex) { try { Cells cells = workSheet.Cells; workSheet.Cells.CopyColumn(cells, columnIndex, columnTargetIndex); return true; } catch (Exception e) { lastErrorMSG = e.Message; return false; } } /// <summary> /// 复制列 /// </summary> ///<param name="sheetIndex">单元薄索引 ///<param name="columnIndex">列索引 ///<param name="columnTargetIndex">复制目标列索引 /// <returns></returns> public bool CopyColumn(int sheetIndex, int columnIndex, int columnTargetIndex) { try { if (sheetIndex > this.WorkSheetCount) { lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!"; return false; } return CopyColumn(columnIndex, columnTargetIndex); } catch (Exception e) { lastErrorMSG = e.Message; return false; } } /// <summary> /// 复制多列 /// </summary> ///<param name="startIndex">列索引 ///<param name="count">列数目 ///<param name="targetIndex">目标索引 /// <returns></returns> public bool CopyColumns(int startIndex, int count, int targetIndex) { try { Cells cells = workSheet.Cells; workSheet.Cells.CopyColumns(cells, startIndex, targetIndex, count); return true; } catch (Exception e) { lastErrorMSG = e.Message; return false; } } /// <summary> /// 复制多列 /// </summary> ///<param name="sheetIndex">单元薄索引 ///<param name="startIndex">源列索引 ///<param name="count">列数目(源列向后的列) ///<param name="targetIndex">目标列索引 /// <returns></returns> public bool CopyColumns(int sheetIndex, int startIndex, int count, int targetIndex) { try { if (sheetIndex > this.WorkSheetCount) { lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!"; return false; } workSheet = workBook.Worksheets[sheetIndex]; return CopyColumns(startIndex, count, targetIndex); } catch (Exception e) { lastErrorMSG = e.Message; return false; } } /// <summary> /// 删除列 /// </summary> ///<param name="columnIndex">列索引 /// <returns></returns> public bool DeleteColumn(int columnIndex) { try { workSheet.Cells.DeleteColumn(columnIndex); return true; } catch (Exception excelHelper) { lastErrorMSG = excelHelper.Message; return false; } } /// <summary> /// 删除列 /// </summary> ///<param name="sheetIndex">单元薄索引 ///<param name="columnIndex">列索引 /// <returns></returns> public bool DeleteColumn(int sheetIndex, int columnIndex) { try { if (sheetIndex > this.WorkSheetCount) { lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!"; return false; } workSheet = workBook.Worksheets[sheetIndex]; return DeleteColumn(columnIndex); } catch (Exception ex) { lastErrorMSG = ex.Message; return false; } } /// <summary> /// 删除多列 /// </summary> ///<param name="startIndex">列 ///<param name="count">数目 /// <returns></returns> public bool DeleteColumns(int startIndex, int count) { try { workSheet.Cells.DeleteColumns(startIndex, count, false); return true; } catch (Exception e) { lastErrorMSG = e.Message; return false; } } /// <summary> /// 删除多列 /// </summary> ///<param name="sheetIndex">单元薄 ///<param name="startIndex">列索引 ///<param name="count">数目 /// <returns></returns> public bool DeleteColumns(int sheetIndex, int startIndex, int count) { try { if (sheetIndex > this.WorkSheetCount) { lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!"; return false; } workSheet = workBook.Worksheets[sheetIndex]; return DeleteColumns(startIndex, count); } catch (Exception ex) { lastErrorMSG = ex.Message; return false; } } /// <summary> /// 将Excel列的字母索引值转换成整数索引值 /// </summary> ///<param name="letter"> /// <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"> /// <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(); } /// <summary> /// 计算WorkSheet数量 /// </summary> ///<param name="rowCount">记录总行数 ///<param name="rows">每WorkSheet行数 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> ///<param name="fileName">文件名 ///<param name="format"> /// <returns></returns> public bool SaveAsFile(string fileName, SaveFormat format) { try { workBook.Save(fileName, format); return true; } catch (Exception e) { lastErrorMSG = e.Message; return false; } } /// <summary> /// 保存Excel /// </summary> ///<param name="fileName"> /// <returns></returns> public bool SaveAsFile(string fileName) { try { return SaveAsFile(fileName, SaveFormat.Xlsx); } catch (Exception e) { lastErrorMSG = e.Message; return false; //throw; } } public bool SaveAsFile() { if (!string.IsNullOrWhiteSpace(outputFile)) { return SaveAsFile(outputFile); } else { return false; } } /// <summary> /// 将DataTable数据写入Excel文件(不分页) /// </summary> ///<param name="dt">DataTable ///<param name="top">表格数据起始行索引 ///<param name="left">表格数据起始列索引 public void DataTableToExcel(DataTable dt, int rowIndex, int columnIndex) { int rowCount = dt.Rows.Count; //DataTable行数 int colCount = dt.Columns.Count; //DataTable列数 for (int i = 0; i < dt.Columns.Count; i++) { workSheet.Cells[rowIndex, columnIndex + i].PutValue(dt.Columns[i].ColumnName); // workSheet.Cells[top, left + i] = "'" + dt.Columns[i].ColumnName; } for (int j = 1; j < rowCount; j++) { for (int k = 0; k < colCount; k++) { workSheet.Cells[j + rowIndex, k + columnIndex].PutValue(dt.Rows[j][k].ToString()); } } } /// <summary> /// 将DataTable数据写入Excel文件(自动分页) /// </summary> ///<param name="dt">DataTable ///<param name="count">每个WorkSheet写入多少行数据 ///<param name="rowIndex">行索引 ///<param name="columnIndex">列索引 public void DataTableToExcel(DataTable dt, int count, int rowIndex, int columnIndex) { int rowCount = dt.Rows.Count; //源DataTable行数 int colCount = dt.Columns.Count; //源DataTable列数 sheetCount = this.GetSheetCount(rowCount, count); //WorkSheet个数 //复制sheetCount-1个WorkSheet对象 for (int i = 1; i < sheetCount; i++) { workSheet = (Worksheet)workBook.Worksheets[0];//.get_Item(i); string name = sheetPrefixName + "-" + i.ToString(); Worksheet sheetTMP = workBook.Worksheets.Add(name); sheetTMP.Copy(workSheet); } int dataIndex = 0; for (int i = 0; i < sheetCount; i++) { int startRow = i * count + rowIndex; //记录起始行索引 int endRow = (i + 1) * count + rowIndex; //记录结束行索引 int row = endRow - startRow; workSheet = workBook.Worksheets[i]; //将dt中的数据写入WorkSheet //for (int j = 0; j < dt.Columns.Count; j++) //{ // workSheet.Cells[rowIndex, columnIndex + j].PutValue(dt.Columns[j].ColumnName); //} for (int j = 0; j < row; j++) { for (int k = 0; k < colCount; k++) { if (dataIndex < dt.Rows.Count) { workSheet.Cells[j + rowIndex + 1, k + columnIndex].PutValue(dt.Rows[dataIndex][k].ToString()); } } dataIndex++; } } } /// <summary> /// 设置单元格值 /// </summary> ///<param name="rowNum"> ///<param name="colNum"> ///<param name="value"> public void SetCellValue(int rowIndex, int colIndex, string value) { workSheet.Cells[rowIndex, colIndex].PutValue(value); } /// <summary> /// 设置单元薄为字符串 /// </summary> public void SetSheetFormatString() { workSheet.Cells.PreserveString = true; } /// <summary> /// 将二维数组数据写入Excel文件(不分页) /// </summary> ///<param name="arr">二维数组 ///<param name="rowIndex">行索引 ///<param name="columnIndex">列索引 /// <returns></returns> public bool ArrayToExcel(string[,] arr, int rowIndex, int columnIndex) { int rowCount = arr.GetLength(0); //二维数组行数(一维长度) int colCount = arr.GetLength(1); //二维数据列数(二维长度) if (rowCount <= 0 || colCount <= 0) { lastErrorMSG = "二位数组长度设置有错"; return false; } try { for (int i = 0; i < rowCount; i++)//行 { for (int k = 0; k < colCount; k++)//列 { workSheet.Cells[rowIndex + i, columnIndex + k].PutValue(arr[i, k]); } } return true; } catch (Exception ex) { lastErrorMSG = ex.Message; return false; } }//end ArrayToExcel /// <summary> /// 将二维数组数据写入Excel文件(不分页) /// </summary> ///<param name="arr">二维数组 ///<param name="rowIndex">行索引 ///<param name="columnIndex">列索引 /// <returns></returns> public bool ArrayObjectToExcel(object[,] arr, int rowIndex, int columnIndex) { int rowCount = arr.GetLength(0); //二维数组行数(一维长度) int colCount = arr.GetLength(1); //二维数据列数(二维长度) if (rowCount <= 0 || colCount <= 0) { lastErrorMSG = "二位数组长度设置有错"; return false; } try { for (int i = 0; i < rowCount; i++)//行 { for (int k = 0; k < colCount; k++)//列 { workSheet.Cells[rowIndex + i, columnIndex + k].PutValue(arr[i, k]); } } return true; } catch (Exception ex) { lastErrorMSG = ex.Message; return false; } } /// <summary> /// 将二维数组数据写入Excel文件(分页) /// </summary> ///<param name="arr">二位数组 ///<param name="rowNumber">每页显示行数 ///<param name="rowIndex">开始行索引 ///<param name="columnIndex">开始列索引 /// <returns></returns> public bool ArrayToExcel(string[,] arr, int rowNumber, int rowIndex, int columnIndex) { int rowCount = arr.GetLength(0); //二维数组行数(一维长度) int colCount = arr.GetLength(1); //二维数据列数(二维长度) if (rowCount <= 0 || colCount <= 0) { lastErrorMSG = "二位数组错误"; return false; } sheetCount = this.GetSheetCount(rowCount, rowNumber); //WorkSheet个数 try { //复制sheetCount-1个WorkSheet对象 for (int i = 1; i < sheetCount; i++) { workSheet = (Worksheet)workBook.Worksheets[0];//.get_Item(i); string name = sheetPrefixName + "-" + i.ToString(); Worksheet sheetTMP = workBook.Worksheets.Add(name); sheetTMP.Copy(workSheet); } int dataRow = 0; //将二维数组数据写入Excel for (int i = 0; i < sheetCount; i++) { workSheet = workBook.Worksheets[i]; for (int j = 0; j < rowNumber; j++) { for (int k = 0; k < colCount; k++) { if (dataRow < rowCount) { workSheet.Cells[j + rowIndex, k + columnIndex].PutValue(arr[dataRow, k]); } } dataRow++; } } return true; } catch (Exception e) { lastErrorMSG = e.Message; return false; } }//end ArrayToExcel /// <summary> /// 将二维数组数据写入Excel文件(不分页) /// </summary> ///<param name="sheetIndex">工作表索引 ///<param name="arr">二维数组 ///<param name="rowIndex">行索引 ///<param name="columnIndex">列索引 /// <returns></returns> public bool ArrayToExcel(int sheetIndex, string[,] arr, int rowIndex, int columnIndex) { if (sheetIndex > this.WorkSheetCount) { lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!"; } int rowCount = arr.GetLength(0); //二维数组行数(一维长度) int colCount = arr.GetLength(1); //二维数据列数(二维长度) if (rowCount <= 0 || colCount <= 0) { return false; } // 改变当前工作表 this.workSheet = (Worksheet)this.workBook.Worksheets[sheetIndex]; return ArrayToExcel(arr, rowIndex, columnIndex); }//end ArrayToExcel public void Dispose() { } /// <summary> /// 设置文字水平对齐方式 /// </summary> ///<param name="rowIndex"> ///<param name="columnIndex"> /// <returns></returns> public bool SetCellHAlignment(int rowIndex, int columnIndex, HorizontalAlignment sy) { Style styleTitle = workBook.Styles[workBook.Styles.Add()]; styleTitle.HorizontalAlignment = (TextAlignmentType)(int)sy; Cells cells = workSheet.Cells; cells[rowIndex, columnIndex].SetStyle(styleTitle); return true; } /// <summary> /// 设置垂直对齐方式 /// </summary> ///<param name="rowIndex"> ///<param name="columnIndex"> ///<param name="sy"> /// <returns></returns> public bool SetCellVAlignment(int rowIndex, int columnIndex, VerticalAlignment sy) { Style styleTitle = workBook.Styles[workBook.Styles.Add()]; styleTitle.VerticalAlignment = (TextAlignmentType)(int)sy; Cells cells = workSheet.Cells; cells[rowIndex, columnIndex].SetStyle(styleTitle); return true; } /// <summary> /// 设置行的垂直对齐方式 /// </summary> ///<param name="rowIndex"> ///<param name="sy"> /// <returns></returns> public bool SetRowVAlignment(int rowIndex, VerticalAlignment sy) { Style styleTitle = workBook.Styles[workBook.Styles.Add()]; styleTitle.VerticalAlignment = (TextAlignmentType)(int)sy; Cells cells = workSheet.Cells; StyleFlag styleFlag = new StyleFlag(); styleFlag.All = true; cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag); return true; } /// <summary> /// 设置行的垂直对齐方式 /// </summary> ///<param name="sheetIndex">单元薄索引 ///<param name="rowIndex">行索引 ///<param name="sy"> /// <returns></returns> public bool SetRowVAlignment(int sheetIndex, int rowIndex, VerticalAlignment sy) { try { if (sheetIndex > this.WorkSheetCount) { lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!"; return false; } workSheet = workBook.Worksheets[sheetIndex]; return SetRowVAlignment(rowIndex, sy); } catch (Exception e) { lastErrorMSG = e.Message; return false; } } /// <summary> /// 设置列的充值对其方式 /// </summary> ///<param name="columnIndex">列索引 ///<param name="sy"> /// <returns></returns> public bool SetColumnVAlignment(int columnIndex, VerticalAlignment sy) { Cells cells = workSheet.Cells; Style styleTitle = workBook.Styles[workBook.Styles.Add()]; styleTitle.VerticalAlignment = (TextAlignmentType)(int)sy; StyleFlag styleFlag = new StyleFlag(); styleFlag.All = true; cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag); return true; } /// <summary> /// 设置文字水平对齐方式 /// </summary> ///<param name="rowIndex"> ///<param name="columnIndex"> /// <returns></returns> public bool SetCellHAlignment(int sheetIndex, int rowIndex, int columnIndex, HorizontalAlignment sy) { try { if (sheetIndex > this.WorkSheetCount) { lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!"; return false; } workSheet = workBook.Worksheets[sheetIndex]; return SetCellHAlignment(rowIndex, columnIndex, sy); } catch (Exception e) { lastErrorMSG = e.Message; return false; } } /// <summary> /// 设置垂直对齐方式 /// </summary> ///<param name="rowIndex"> ///<param name="columnIndex"> ///<param name="sy"> /// <returns></returns> public bool SetCellVAlignment(int sheetIndex, int rowIndex, int columnIndex, VerticalAlignment sy) { try { if (sheetIndex > this.WorkSheetCount) { lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!"; return false; } workSheet = workBook.Worksheets[sheetIndex]; return SetCellVAlignment(rowIndex, columnIndex, sy); } catch (Exception e) { lastErrorMSG = e.Message; return false; } } /// <summary> /// 设置行的S水平对齐方式 /// </summary> ///<param name="sheetIndex">单元薄索引 ///<param name="rowIndex">行索引 ///<param name="sy"> /// <returns></returns> public bool SetRowHAlignment(int sheetIndex, int rowIndex, HorizontalAlignment sy) { try { if (sheetIndex > this.WorkSheetCount) { lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!"; return false; } workSheet = workBook.Worksheets[sheetIndex]; return SetRowHAlignment(rowIndex, sy); } catch (Exception e) { lastErrorMSG = e.Message; return false; } } /// <summary> /// 设置行的水平对齐方式 /// </summary> ///<param name="rowIndex"> ///<param name="sy"> /// <returns></returns> public bool SetRowHAlignment(int rowIndex, HorizontalAlignment sy) { Cells cells = workSheet.Cells; Style styleTitle = workBook.Styles[workBook.Styles.Add()]; styleTitle.VerticalAlignment = (TextAlignmentType)(int)sy; StyleFlag styleFlag = new StyleFlag(); styleFlag.All = true; cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag); return true; } /// <summary> /// 设置列的水平对其方式 /// </summary> ///<param name="columnIndex">列索引 ///<param name="sy"> /// <returns></returns> public bool SetColumnHAlignment(int columnIndex, VerticalAlignment sy) { Cells cells = workSheet.Cells; Style styleTitle = workBook.Styles[workBook.Styles.Add()]; styleTitle.VerticalAlignment = (TextAlignmentType)(int)sy; StyleFlag styleFlag = new StyleFlag(); styleFlag.All = true; cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag); return true; } /// <summary> /// 设置单元格字体颜色 /// </summary> ///<param name="color"> ///<param name="rowIndex"> ///<param name="columnIndex"> /// <returns></returns> public bool SetCellFontColor(System.Drawing.Color color, int rowIndex, int columnIndex) { Style styleTitle = workBook.Styles[workBook.Styles.Add()]; styleTitle.Font.Color = color;//.VerticalAlignment = (TextAlignmentType)(int)sy; Cells cells = workSheet.Cells; cells[rowIndex, columnIndex].SetStyle(styleTitle); return true; } /// <summary> /// 设置行的字体颜色 /// </summary> ///<param name="color"> ///<param name="rowIndex"> /// <returns></returns> public bool SetRowFontColor(System.Drawing.Color color, int rowIndex) { Cells cells = workSheet.Cells; Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()]; styleTitle.Font.Color = color; StyleFlag styleFlag = new StyleFlag(); styleFlag.All = true; cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag); return true; } /// <summary> /// 设置列的字体颜色 /// </summary> ///<param name="color"> ///<param name="columnIndex"> /// <returns></returns> public bool SetColumnFontColor(System.Drawing.Color color, int columnIndex) { Cells cells = workSheet.Cells; Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()]; styleTitle.Font.Color = color; StyleFlag styleFlag = new StyleFlag(); styleFlag.All = true; cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag); return true; } /// <summary> /// 设置字体名称 /// </summary> ///<param name="name"> ///<param name="rowIndex"> ///<param name="columnIndex"> /// <returns></returns> public bool SetCellFontName(string name, int rowIndex, int columnIndex) { Style styleTitle = GetStyle; styleTitle.Font.Name = name;//.VerticalAlignment = (TextAlignmentType)(int)sy; Cells cells = workSheet.Cells; cells[rowIndex, columnIndex].SetStyle(styleTitle); return true; } /// <summary> /// 设置行字体名称 /// </summary> ///<param name="name"> ///<param name="rowIndex"> /// <returns></returns> public bool SetRowFontName(string name, int rowIndex) { Cells cells = workSheet.Cells; Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()]; styleTitle.Font.Name = name; StyleFlag styleFlag = new StyleFlag(); styleFlag.All = true; cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag); return true; } /// <summary> /// 设置列字体名称 /// </summary> ///<param name="name"> ///<param name="columnIndex"> /// <returns></returns> public bool SetColumnFontName(string name, int columnIndex) { Cells cells = workSheet.Cells; Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()]; styleTitle.Font.Name = name; StyleFlag styleFlag = new StyleFlag(); styleFlag.All = true; cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag); return true; } /// <summary> /// 设置字体大小 /// </summary> ///<param name="size"> ///<param name="rowIndex"> ///<param name="columnIndex"> /// <returns></returns> public bool SetCellFontSize(int size, int rowIndex, int columnIndex) { Style styleTitle = GetStyle; styleTitle.Font.Size = size;//.VerticalAlignment = (TextAlignmentType)(int)sy; Cells cells = workSheet.Cells; cells[rowIndex, columnIndex].SetStyle(styleTitle); return true; } /// <summary> /// 设置行字体大小 /// </summary> ///<param name="size"> ///<param name="rowIndex"> /// <returns></returns> public bool SetRowFontSize(int size, int rowIndex) { Cells cells = workSheet.Cells; Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()]; styleTitle.Font.Size = size; StyleFlag styleFlag = new StyleFlag(); styleFlag.All = true; cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag); return true; } /// <summary> /// 设置列的字体大小 /// </summary> ///<param name="size"> ///<param name="columnIndex"> /// <returns></returns> public bool SetColumnFontSize(int size, int columnIndex) { Cells cells = workSheet.Cells; Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()]; styleTitle.Font.Size = size; StyleFlag styleFlag = new StyleFlag(); styleFlag.All = true; cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag); return true; } /// <summary> /// 字体是否加粗 /// </summary> ///<param name="iSBold"> ///<param name="rowIndex"> ///<param name="columnIndex"> /// <returns></returns> public bool SetCellFontIsBold(bool iSBold, int rowIndex, int columnIndex) { Style styleTitle = GetStyle; styleTitle.Font.IsBold = iSBold;//.VerticalAlignment = (TextAlignmentType)(int)sy; Cells cells = workSheet.Cells; cells[rowIndex, columnIndex].SetStyle(styleTitle); return true; } /// <summary> /// 设置字体行是否加租 /// </summary> ///<param name="iSBold"> ///<param name="rowIndex"> /// <returns></returns> public bool SetRowFontIsBold(bool iSBold, int rowIndex) { Cells cells = workSheet.Cells; Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()]; styleTitle.Font.IsBold = iSBold; StyleFlag styleFlag = new StyleFlag(); styleFlag.All = true; cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag); return true; } /// <summary> /// 设置字体列是否加粗 /// </summary> ///<param name="iSBold"> ///<param name="columnIndex"> /// <returns></returns> public bool SetColumnFontIsBold(bool iSBold, int columnIndex) { Cells cells = workSheet.Cells; Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()]; styleTitle.Font.IsBold = iSBold; StyleFlag styleFlag = flag; cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag); return true; } public StyleFlag flag { get { StyleFlag styleFlag = new StyleFlag(); styleFlag.All = true; return styleFlag; } } /// <summary> /// 字体斜体 /// </summary> ///<param name="iSItalic"> ///<param name="rowIndex"> ///<param name="columnIndex"> /// <returns></returns> public bool SetCellIsItalic(bool iSItalic, int rowIndex, int columnIndex) { Style styleTitle = GetStyle; styleTitle.Font.IsItalic = iSItalic;//.VerticalAlignment = (TextAlignmentType)(int)sy; Cells cells = workSheet.Cells; cells[rowIndex, columnIndex].SetStyle(styleTitle); return true; } /// <summary> /// 设置行 字体斜体 /// </summary> ///<param name="iSItalic"> ///<param name="rowIndex"> /// <returns></returns> public bool SetRowIsItalic(bool iSItalic, int rowIndex) { Cells cells = workSheet.Cells; Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()]; styleTitle.Font.IsItalic = iSItalic; StyleFlag styleFlag = flag; cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag); return true; } /// <summary> /// 设置列 字体是否为斜体 /// </summary> ///<param name="iSItalic"> ///<param name="columnIndex"> /// <returns></returns> public bool SetColumnIsItali(bool iSItalic, int columnIndex) { Cells cells = workSheet.Cells; Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()]; styleTitle.Font.IsItalic = iSItalic; StyleFlag styleFlag = flag; cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag); return true; } /// <summary> /// 设置删除线 /// </summary> ///<param name="iStrike"> ///<param name="rowIndex"> ///<param name="columnIndex"> /// <returns></returns> public bool SetCellIsStrikeOut(bool iStrike, int rowIndex, int columnIndex) { Style styleTitle = GetStyle; styleTitle.Font.IsStrikeout = iStrike;//.VerticalAlignment = (TextAlignmentType)(int)sy; Cells cells = workSheet.Cells; cells[rowIndex, columnIndex].SetStyle(styleTitle); return true; } /// <summary> /// 设置行是否删除线 /// </summary> ///<param name="iStrike"> ///<param name="rowIndex"> /// <returns></returns> public bool SetRowIsStrikeOut(bool iStrike, int rowIndex) { Cells cells = workSheet.Cells; Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()]; styleTitle.Font.IsStrikeout = iStrike; StyleFlag styleFlag = flag; cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag); return true; } /// <summary> /// 设置列是否删除线 /// </summary> ///<param name="iStrike"> ///<param name="columnIndex"> /// <returns></returns> public bool SetColumnIsStrikeOut(bool iStrike, int columnIndex) { Cells cells = workSheet.Cells; Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()]; styleTitle.Font.IsStrikeout = iStrike; StyleFlag styleFlag = flag; cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag); return true; } /// <summary> /// 设置边框是否实线 /// </summary> ///<param name="rowIndex">行索引 ///<param name="columnIndex">列索引 ///<param name="left">左侧是否为实线 ///<param name="right">右侧是否为实线 ///<param name="top">上部是否为实线 ///<param name="bottom">下部是否为实线 /// <returns></returns> public bool SetCellBorder(int rowIndex, int columnIndex, bool left = false, bool right = false, bool top = false, bool bottom = false) { Cells cells = workSheet.Cells; Style styleTitle = GetStyle; StyleFlag styleFlag = flag; styleTitle.Borders[BorderType.LeftBorder].LineStyle = left == false ? CellBorderType.None : CellBorderType.Thin; styleTitle.Borders[BorderType.RightBorder].LineStyle = right == false ? CellBorderType.None : CellBorderType.Thin; styleTitle.Borders[BorderType.TopBorder].LineStyle = top == false ? CellBorderType.None : CellBorderType.Thin; styleTitle.Borders[BorderType.BottomBorder].LineStyle = bottom == false ? CellBorderType.None : CellBorderType.Thin; cells[rowIndex, columnIndex].SetStyle(styleTitle); return true; } /// <summary> /// 设置行边框是否为实线 /// </summary> ///<param name="rowIndex"> ///<param name="left"> ///<param name="right"> ///<param name="top"> ///<param name="bottom"> /// <returns></returns> public bool SetRowBorder(int rowIndex, bool left = false, bool right = false, bool top = false, bool bottom = false) { Cells cells = workSheet.Cells; Style styleTitle = GetStyle; StyleFlag styleFlag = flag; styleTitle.Borders[BorderType.LeftBorder].LineStyle = left == false ? CellBorderType.None : CellBorderType.Thin; styleTitle.Borders[BorderType.RightBorder].LineStyle = right == false ? CellBorderType.None : CellBorderType.Thin; styleTitle.Borders[BorderType.TopBorder].LineStyle = top == false ? CellBorderType.None : CellBorderType.Thin; styleTitle.Borders[BorderType.BottomBorder].LineStyle = bottom == false ? CellBorderType.None : CellBorderType.Thin; cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag); return true; } /// <summary> /// 设置列的边框是否为实线 /// </summary> ///<param name="columnIndex"> ///<param name="left"> ///<param name="right"> ///<param name="top"> ///<param name="bottom"> /// <returns></returns> public bool SetColumnBorder(int columnIndex, bool left = false, bool right = false, bool top = false, bool bottom = false) { Cells cells = workSheet.Cells; Style styleTitle = GetStyle; StyleFlag styleFlag = flag; styleTitle.Borders[BorderType.LeftBorder].LineStyle = left == false ? CellBorderType.None : CellBorderType.Thin; styleTitle.Borders[BorderType.RightBorder].LineStyle = right == false ? CellBorderType.None : CellBorderType.Thin; styleTitle.Borders[BorderType.TopBorder].LineStyle = top == false ? CellBorderType.None : CellBorderType.Thin; styleTitle.Borders[BorderType.BottomBorder].LineStyle = bottom == false ? CellBorderType.None : CellBorderType.Thin; cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag); return true; } /// <summary> /// 设置单元格背景色 为纯色 /// </summary> ///<param name="rowIndex"> ///<param name="columnIndex"> ///<param name="color"> /// <returns></returns> public bool SetCellBackColor(int rowIndex, int columnIndex, System.Drawing.Color color) { var s = workSheet.Cells[rowIndex, columnIndex].GetStyle(); s.ForegroundColor = color; s.Pattern = BackgroundType.Solid; workSheet.Cells[rowIndex, columnIndex].SetStyle(s); return true; } /// <summary> /// 设置行的背景色 为纯色 /// </summary> ///<param name="rowIndex"> ///<param name="color"> /// <returns></returns> public bool SetRowBackColor(int rowIndex, System.Drawing.Color color) { Cells cells = workSheet.Cells; Style styleTitle = GetStyle; styleTitle.ForegroundColor = color; styleTitle.Pattern = BackgroundType.Solid; StyleFlag styleFlag = flag; cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag); return true; } /// <summary> /// 设置列的背景色 为纯色 /// </summary> ///<param name="columnIndex"> ///<param name="color"> /// <returns></returns> public bool SetColumnBackColor(int columnIndex, System.Drawing.Color color) { Cells cells = workSheet.Cells; Style styleTitle = GetStyle; styleTitle.ForegroundColor = color; styleTitle.Pattern = BackgroundType.Solid; StyleFlag styleFlag = flag; cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag); return true; } public Style GetStyle { get { return workBook.Styles[workBook.Styles.Add()]; } } /// <summary> /// DataTable 导出至模板excel中 /// </summary> ///<param name="table">数据源 ///<param name="dataRowNum">每页数据的行数(无表头表尾,只是数据) ///<param name="pageRowNum">每页总行数(加上表头与表尾) ///<param name="firstTitleRowNum">首页标题的行数(表头) ///<param name="pageTiletRowNum">表头+表尾行数(分页时中间的行数就是第一页表尾+第二页表头) ///<param name="columnNum">要输出的列数(前几列) ///<param name="MergeColumns">合并列例如(2,3合并,输入3即可,如果多行:2,3,4合并,则输入3,4)' ///<param name="pageColumnIndex">分页列,例如以班组分页就写班组所在的列id[必须以该列进行排序] /// <returns></returns> public List<excelinfo> TableToExcel(DataTable table, int dataRowNum, int pageRowNum, int firstTitleRowNum, int pageTiletRowNum, int columnNum, int[] MergeColumns, int pageColumnIndex) { try { List<excelinfo> listBanzu = null; //分页标识(例如 班组不同分页) string pageflag = string.Empty; if (pageColumnIndex > 0) { pageflag = table.Rows[0][pageColumnIndex].ToString(); } //必须先分页. //总页数 int pagecount = 1; //分页行数 int fenge = firstTitleRowNum; int fenye = -1; for (int i = 0; i < table.Rows.Count; i++) { if (fenye == -1) fenye = i; //正常分页 if (fenye > 0 && fenye % dataRowNum == 0) { CopyRows(0, 0, pageRowNum, pageRowNum * pagecount); pagecount++; fenye = 0; } //遇到分页组分页 if (table.Rows[i][pageColumnIndex].ToString() != pageflag) { pageflag = table.Rows[i][pageColumnIndex].ToString(); CopyRows(0, 0, pageRowNum, pageRowNum * pagecount); pagecount++; fenye = 0; } fenye++; } if (pageColumnIndex > 0) { listBanzu = new List<excelinfo>(); pageflag = table.Rows[0][pageColumnIndex].ToString(); } fenye = -1; int id = 1; //3.插入数据 每一页 for (int i = 0; i < table.Rows.Count; i++) { if (fenye == -1) fenye = i; //正常分页 if (fenye > 0 && fenye % dataRowNum == 0) { if (listBanzu != null) listBanzu.Add(new ExcelInfo() { pageID = id, banzu = pageflag }); fenge += pageTiletRowNum; fenye = 0; id++; } //遇到分页组分页 if (table.Rows[i][pageColumnIndex].ToString() != pageflag) { if (listBanzu != null) listBanzu.Add(new ExcelInfo() { pageID = id, banzu = pageflag }); pageflag = table.Rows[i][pageColumnIndex].ToString(); fenge += dataRowNum - fenye + pageTiletRowNum; //每页行数-已有行数+表头表尾 fenye = 0; id++; } //合并行 int col = -1; for (int t = 0; t < columnNum; t++) { if (col == -1) { col = t; } if (MergeColumns != null) { for (int m = 0; m < MergeColumns.Count(); m++) { if (t == MergeColumns[m]) { col++; } } } SetCellValue(i + fenge, col, table.Rows[i][t].ToString()); col++; } fenye++; } if (listBanzu.Count < pagecount) { //增加最后一页 listBanzu.Add(new ExcelInfo() { pageID = pagecount, banzu = pageflag }); } #region //int total = table.Rows.Count; //int count = total / dataRowNum; //if (total % dataRowNum > 0) //{ // count += 1; //} ////2.分页复制模板 //for (int i = 0; i < count; i++) //{ // CopyRows(0, 0, pageRowNum, i * pageRowNum); //} //int fenge = firstTitleRowNum; //if (table.Columns.Count < columnNum) //{ // columnNum = table.Columns.Count; //} ////3.插入数据 每一页 //for (int i = 0; i < table.Rows.Count; i++) //{ // if (i > 0 && i % dataRowNum == 0) // { // fenge += pageTiletRowNum; // } // int col = -1; // for (int t = 0; t < columnNum; t++) // { // if (col == -1) // { // col = t; // } // if (MergeColumns != null) // { // for (int m = 0; m < MergeColumns.Count(); m++) // { // if (t == MergeColumns[m]) // { // col++; // } // } // } // SetCellValue(i + fenge, col, table.Rows[i][t].ToString()); // col++; // } //} #endregion return listBanzu; } catch (Exception ex) { lastErrorMSG = ex.Message; return null; } } } public class ExcelInfo { public int pageID; public string banzu; public string date; } /// <summary> /// 文字水平显示 /// </summary> public enum HorizontalAlignment { Bottom = 0, Center = 1, CenterAcross = 2, Distributed = 3, Fill = 4, General = 5, Justify = 6, Left = 7, Right = 8, Top = 9, } /// <summary> /// 垂直显示 /// </summary> public enum VerticalAlignment { Bottom = 0, Center = 1, CenterAcross = 2, Distributed = 3, Fill = 4, General = 5, Justify = 6, Left = 7, Right = 8, Top = 9, } } </excelinfo></excelinfo></excelinfo>
调用
var v = dt.DefaultView; v.Sort = "班组"; ExcelHelper exhelp = new ExcelHelper("templet\\kq.xlsx", "1"); List<excelinfo> banzu = exhelp.TableToExcel(v.ToTable(), 20, 28, 5, 8, 35, new int[] { 3 }, 35); if (banzu != null) { foreach (var item in banzu) { exhelp.SetCellValue((item.pageID - 1) * 28, 0, "考勤表"); exhelp.SetCellValue((item.pageID - 1) * 28 + 2, 0, "部门名称(全称): xx名称"); exhelp.SetCellValue((item.pageID - 1) * 28 + 2, 10, "班组:" + item.banzu); exhelp.SetCellValue((item.pageID - 1) * 28 + 2, 24, dt.Rows[0]["月份"].ToString()); exhelp.SetCellValue((item.pageID - 1) * 28 + 27, 0, "第" + item.pageID + "页,共" + banzu.Count + "页 o=缺勤 √ =出勤"); } SaveFileDialog sfd = new SaveFileDialog(); sfd.AddExtension = true; sfd.DefaultExt = "xlsx"; if (sfd.ShowDialog() == DialogResult.OK) { exhelp.SaveAsFile(sfd.FileName); } return true; } return false;</excelinfo>
本文由职坐标整理并发布,了解更多内容,请关注职坐标编程语言C#.NET频道!
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号