代码改变世界

c# excel

  观海看云  阅读(266)  评论(0)  编辑  收藏  举报
?
using System;  
using System.IO;  
using System.Text;  
using System.Data;  
using System.Reflection;  
using System.Diagnostics;  
using System.Collections;  
   
//using cfg = System.Configuration;  
   
namespace GDBI.Common  
{  
    /// <summary>  
    /// 说    明：Excel输出打印模块  
    ///           暂时不提供操作Excel对象样式方法，样式可以在Excel模板中设置好  
    /// 作    者：lingyun_k  
    /// 创建日期：2005－7－12  
    /// </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 = "页";    
        #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,colCount];  
   
            for(int j=0;j<rowCount;j++)  
            {  
                for(int k=0;k<colCount;k++)  
                {  
                    arr[j,k] = dt.Rows[j][k].ToString();  
                }  
            }  
   
            range = (Excel.Range)workSheet.Cells[top,left];  
            range = range.get_Resize(rowCount,colCount);  
            range.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 
  
        #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  
   
            
    }//end class  
}//end namespace 
 
 
 
 
 
 
 
 
C#操作EXCEL全解（代码）
提示：运行之前必须先引用Interop.Excel.dll模块
 
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Reflection;
using Excel;
 
namespace AnalysisSystem.DB
{
    public class ExcelOperation
    {
        private string _fileName;//保存路径名
        public ExcelOperation(string fileName)
        {
            _fileName = fileName;
        }
 
        private OleDbConnection GetConnection()
        {
            string connectString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0",_fileName);
            OleDbConnection myConn = new OleDbConnection(connectString);//建立链接
            return myConn;
        }
 
        public System.Data.DataTable ExecuteTableResult(string strSql)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            try
            {
                OleDbConnection conn = this.GetConnection();
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);//执行相关SQL语句
                da.Fill(dt);
            }
            catch (System.Exception ex)
            {
                //do nothing
            }
            return dt;
        }
 
        public DataSet ExecuteSetResult(string strSql,string table_name)
        {
 
            DataSet ds = new DataSet();
            string temp_name = "[" + table_name + "$]";
            try
            {
                OleDbConnection conn = this.GetConnection();
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
                da.Fill(ds,temp_name);
            }
            catch (System.Exception ex)
            {
                //do nothing
            }
            return ds;
        }
 
        public string ExecuteOneResult(string strSql)
        {
            string result = "";
            System.Data.DataTable dt = new System.Data.DataTable();
            try
            {
                OleDbConnection conn = this.GetConnection();
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
                da.Fill(dt);
            }
            catch (System.Exception ex)
            {
                //do nothing
            }
            if (dt != null && dt.Rows.Count > 0)
            {
                result = dt.Rows[0][0].ToString();
            }
            return result;
        }
 
        public void ExecuteNonResult(string strSql)
        {
            try
            {
                OleDbConnection conn = this.GetConnection();
                OleDbCommand cmd = new OleDbCommand(strSql, conn);
                cmd.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                //do nothing
            }
        }
 
         
        private _Workbook W_B(Excel.Application app)
        {
            Workbooks workbooks = app.Workbooks;
            _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            return workbook;
        }
        private _Worksheet W_S(_Workbook work_book)
        {
            Sheets sheets = work_book.Worksheets;
            _Worksheet worksheet = (_Worksheet)sheets.get_Item(1);//获取选择第一个表，本身默认名称为Sheet1
            return worksheet;
        }
        //从DataGridView中导出数据到Excel表,单表导出
        public void Excel_out(DataGridView dataGridView1)
        {
            //建立Excel对象
            Excel.Application app = new Excel.Application();
            try
            {
                _Workbook workbook = this.W_B(app);
                _Worksheet worksheet = this.W_S(workbook);
 
                string sLen = "";
                //取得最后一列列名
                char H = (char)(64 + dataGridView1.ColumnCount / 26);
                char L = (char)(64 + dataGridView1.ColumnCount % 26);
                if (dataGridView1.ColumnCount < 26)
                {
                    sLen = L.ToString();
                }
                else
                {
                    sLen = H.ToString() + L.ToString();
                }
 
                //标题
                string sTmp = sLen + "1";
                Range ranCaption = worksheet.get_Range(sTmp, "A1");
                string[] asCaption = new string[dataGridView1.ColumnCount];
                for (int i = 0; i < dataGridView1.ColumnCount; i++)
                {
                    asCaption[i] = dataGridView1.Columns[i].HeaderText;
                }
                ranCaption.Value2 = asCaption;
                //数据
                object[] obj = new object[dataGridView1.Columns.Count];
                for (int r = 0; r < dataGridView1.RowCount - 1; r++)
                {
                    for (int l = 0; l < dataGridView1.Columns.Count; l++)
                    {
                        if (dataGridView1[l, r].ValueType == typeof(DateTime))
                        {
                            obj[l] = dataGridView1[l, r].Value.ToString();
                        }
                        else
                        {
                            obj[l] = dataGridView1[l, r].Value;
                        }
                    }
                    string cell1 = sLen + ((int)(r + 2)).ToString();
                    string cell2 = "A" + ((int)(r + 2)).ToString();
                    Range ran = worksheet.get_Range(cell1, cell2);
                    ran.Value2 = obj;
                }
                //保存
                workbook.SaveCopyAs(this._fileName);
                workbook.Saved = true;
            }
            finally
            {
                //关闭
                app.UserControl = false;
                app.Quit();
            }
        }
 
        /// <summary>
        /// 多表导出
        /// </summary>
        /// <param name="dataGridView">DataGridView列表集合</param>
        /// <param name="TableList">表名称集合</param>
        public void Excel_out_MulTable(List<DataGridView> dataGridView, string[] TableList)
        {
            //建立Excel对象
            Excel.Application app = new Excel.Application();
            try
            {
                Workbooks workbooks = app.Workbooks;//定义一个工作簿集合
                _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);//向工作簿添加一个新工作簿
                 
                Sheets sheets = workbook.Worksheets;//定义一个工作表集合
                _Worksheet worksheet ;
                int wnumber = 0;
                while (wnumber++ < (TableList.GetLength(0) - 1))
                {
                    sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);//向一个工作表集合添加一个新工作表
                }
 
                /*提醒：Missing类为命名空间System.Reflection中的类，所以记得引入*/
                 
                wnumber = 0;
                foreach (DataGridView dataGridView1 in dataGridView)
                {
                     
                    worksheet = null;
                    worksheet = (_Worksheet)sheets.get_Item(wnumber + 1);//取出需要进行操作的工作表
                    worksheet.Name = TableList[wnumber];//设置改工作表名称
                    if (wnumber != 0)
                        sheets.Select(wnumber);//选中操作表
                     
 
                    string sLen = "";
                    //取得最后一列列名
                    char H = (char)(64 + dataGridView1.ColumnCount / 26);
                    char L = (char)(64 + dataGridView1.ColumnCount % 26);
                    if (dataGridView1.ColumnCount < 26)
                    {
                        sLen = L.ToString();
                    }
                    else
                    {
                        sLen = H.ToString() + L.ToString();
                    }
 
                    //标题
                    string sTmp = sLen + "1";
                    Range ranCaption = worksheet.get_Range(sTmp, "A1");
                    string[] asCaption = new string[dataGridView1.ColumnCount];
                    for (int i = 0; i < dataGridView1.ColumnCount; i++)
                    {
                        asCaption[i] = dataGridView1.Columns[i].HeaderText;
                    }
                    ranCaption.Value2 = asCaption;
                    //数据
                    object[] obj = new object[dataGridView1.Columns.Count];
                    for (int r = 0; r < dataGridView1.RowCount - 1; r++)
                    {
                        for (int l = 0; l < dataGridView1.Columns.Count; l++)
                        {
                            if (dataGridView1[l, r].ValueType == typeof(DateTime))
                            {
                                obj[l] = dataGridView1[l, r].Value.ToString();
                            }
                            else
                            {
                                obj[l] = dataGridView1[l, r].Value;
                            }
                        }
                        string cell1 = sLen + ((int)(r + 2)).ToString();
                        string cell2 = "A" + ((int)(r + 2)).ToString();
                        Range ran = worksheet.get_Range(cell1, cell2);//设置单元格                       
                        ran.Value2 = obj;
                         
                    }      
                    wnumber++;
                }
                //保存               
                workbook.SaveCopyAs(this._fileName);
                workbook.Saved = true;
            }
            finally
            {
                //关闭
                app.UserControl = false;
                app.Quit();
            }
             
        }
 
    }
}
编辑推荐：
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
阅读排行：
· 周边上新：园子的第一款马克杯温暖上架
· Open-Sora 2.0 重磅开源！
· .NET周刊【3月第1期 2025-03-02】
· 分享 3 个 .NET 开源的文件压缩处理库，助力快速实现文件压缩解压功能！
· Ollama——大语言模型本地部署的极速利器
点击右上角即可分享
微信分享提示
SQL AI 助手