本文主要是介绍[NET][C#]操作Excel,套用模板并对数据进行分页,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
[NET][C#]操作Excel,套用模板并对数据进行分页
using System; using System.IO; using System.Data; using System.Reflection; using System.Diagnostics; using cfg = System.Configuration; using Excel; namespace ExcelHelperTest { /**/ /// <summary> /// 功能说明:套用模板输出Excel,并对数据进行分页 /// 作 者:Lingyun_k /// 创建日期:2005-7-12 /// </summary> public class ExcelHelper { protected string templetFile = null ; protected string outputFile = null ; protected object missing = Missing.Value; /**/ /// <summary> /// 构造函数,需指定模板文件和输出文件完整路径 /// </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; } /**/ /// <summary> /// 将DataTable数据写入Excel文件(套用模板并分页) /// </summary> /// <param name="dt"> DataTable </param> /// <param name="rows"> 每个WorkSheet写入多少行数据 </param> /// <param name="top"> 行索引 </param> /// <param name="left"> 列索引 </param> /// <param name="sheetPrefixName"> WorkSheet前缀名,比如:前缀名为“Sheet”,那么WorkSheet名称依次为“Sheet-1,Sheet-2” </param> public void DataTableToExcel(DataTable dt, int rows, int top, int left, string sheetPrefixName) { int rowCount = dt.Rows.Count; // 源DataTable行数 int colCount = dt.Columns.Count; // 源DataTable列数 int sheetCount = this .GetSheetCount(rowCount,rows); // WorkSheet个数 DateTime beforeTime; DateTime afterTime; if (sheetPrefixName == null || sheetPrefixName.Trim() == "" ) sheetPrefixName = " Sheet " ; // 创建一个Application对象并使其可见 beforeTime = DateTime.Now; Excel.Application app = new Excel.ApplicationClass(); app.Visible = true ; afterTime = DateTime.Now; // 打开模板文件,得到WorkBook对象 Excel.Workbook workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing, missing,missing,missing,missing,missing,missing,missing); // 得到WorkSheet对象 Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item( 1 ); // 复制sheetCount-1个WorkSheet对象 for ( int i = 1 ;i < sheetCount;i ++ ) { ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]); } 将源DataTable数据写入Excel #region 将源DataTable数据写入Excel for ( int i = 1 ;i <= sheetCount;i ++ ) { int startRow = (i - 1 ) * rows; // 记录起始行索引 int endRow = i * rows; // 记录结束行索引 // 若是最后一个WorkSheet,那么记录结束行索引为源DataTable行数 if (i == sheetCount) endRow = rowCount; // 获取要写入数据的WorkSheet对象,并重命名 Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i); sheet.Name = sheetPrefixName + " - " + i.ToString(); // 将dt中的数据写入WorkSheet for ( int j = 0 ;j < endRow - startRow;j ++ ) { for ( int k = 0 ;k < colCount;k ++ ) { sheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString(); } } // 写文本框数据 Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes( " txtAuthor " ); Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes( " txtDate " ); Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes( " txtVersion " ); txtAuthor.Text = " KLY.NET的Blog " ; txtDate.Text = DateTime.Now.ToShortDateString(); txtVersion.Text = " 1.0.0.0 " ; } #endregion // 输出Excel文件并退出 try { workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing); workBook.Close( null , null , null ); app.Workbooks.Close(); app.Application.Quit(); app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); workSheet = null ; workBook = null ; app = null ; GC.Collect(); } catch (Exception e) { throw e; } finally { 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(); } } } } /**/ /// <summary> /// 获取WorkSheet数量 /// </summary> /// <param name="rowCount"> 记录总行数 </param> /// <param name="rows"> 每WorkSheet行数 </param> private 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="arr"> 二维数组 </param> /// <param name="rows"> 每个WorkSheet写入多少行数据 </param> /// <param name="top"> 行索引 </param> /// <param name="left"> 列索引 </param> /// <param name="sheetPrefixName"> WorkSheet前缀名,比如:前缀名为“Sheet”,那么WorkSheet名称依次为“Sheet-1,Sheet-2” </param> public void ArrayToExcel( string [,] arr, int rows, int top, int left, string sheetPrefixName) { int rowCount = arr.GetLength( 0 ); // 二维数组行数(一维长度) int colCount = arr.GetLength( 1 ); // 二维数据列数(二维长度) int sheetCount = this .GetSheetCount(rowCount,rows); // WorkSheet个数 DateTime beforeTime; DateTime afterTime; if (sheetPrefixName == null || sheetPrefixName.Trim() == "" ) sheetPrefixName = " Sheet " ; // 创建一个Application对象并使其可见 beforeTime = DateTime.Now; Excel.Application app = new Excel.ApplicationClass(); app.Visible = true ; afterTime = DateTime.Now; // 打开模板文件,得到WorkBook对象 Excel.Workbook workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing, missing,missing,missing,missing,missing,missing,missing); // 得到WorkSheet对象 Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item( 1 ); // 复制sheetCount-1个WorkSheet对象 for ( int i = 1 ;i < sheetCount;i ++ ) { ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]); } 将二维数组数据写入Excel #region 将二维数组数据写入Excel for ( int i = 1 ;i <= sheetCount;i ++ ) { int startRow = (i - 1 ) * rows; // 记录起始行索引 int endRow = i * rows; // 记录结束行索引 // 若是最后一个WorkSheet,那么记录结束行索引为源DataTable行数 if (i == sheetCount) endRow = rowCount; // 获取要写入数据的WorkSheet对象,并重命名 Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i); sheet.Name = sheetPrefixName + " - " + i.ToString(); // 将二维数组中的数据写入WorkSheet for ( int j = 0 ;j < endRow - startRow;j ++ ) { for ( int k = 0 ;k < colCount;k ++ ) { sheet.Cells[top + j,left + k] = arr[startRow + j,k]; } } Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes( " txtAuthor " ); Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes( " txtDate " ); Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes( " txtVersion " ); txtAuthor.Text = " KLY.NET的Blog " ; txtDate.Text = DateTime.Now.ToShortDateString(); txtVersion.Text = " 1.0.0.0 " ; } #endregion // 输出Excel文件并退出 try { workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing); workBook.Close( null , null , null ); app.Workbooks.Close(); app.Application.Quit(); app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); workSheet = null ; workBook = null ; app = null ; GC.Collect(); } catch (Exception e) { throw e; } finally { 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(); } } } } } }
这篇关于[NET][C#]操作Excel,套用模板并对数据进行分页的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!