c#编程实例之导出数据至excel模板中,可分页
Vivian 2018-05-22 来源 : 阅读 874 评论 0

摘要:本文主要介绍了c#编程实例的导出数据至excel模板中,希望对大家学习c#编程实例有所帮助。

c#导出数据至excel模板中,可分页 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Data;
using System.Diagnostics;
using System.IO;
using Aspose.Cells;
 
namespace DBHelper
{
    public class ExcelHelper : IDisposable
    {
        private string templetFile = String.Empty;
        private string outputFile = String.Empty;
        private DateTime beforeTime;            //Excel启动之前时间
        private DateTime afterTime;             //Excel启动之后时间     
        Workbook workBook;
        public Worksheet workSheet;
        Range range;
        private int sheetCount = 1;         //WorkSheet数量
        private string sheetPrefixName = "Sheet";
 
        #region 公共属性
        public string SheetPrefixName
        {
            set { this.sheetPrefixName = value; }
        }
        public int WorkSheetCount
        {
            get { return workBook.Worksheets.Count; }//.Sheets.Count; }
        }
        public string OutputFilePath
        {
            set { this.outputFile = value; }
        }
        #endregion
 
        #region CreateExcelFile
        /// <summary>
        /// 构造函数,将一个已有Excel工作簿作为模板,并指定输出路径
        /// </summary>
        ///<param name="templetFilePath">Excel模板文件路径
        ///<param name="outputFilePath">输出Excel文件路径
        public ExcelHelper(string templetFilePath, string outputFilePath)
        {
            if (templetFilePath == String.Empty)
                throw new Exception("Excel模板文件路径不能为空!");
 
            if (outputFilePath == String.Empty)
                throw new Exception("输出Excel文件路径不能为空!");
 
            if (!File.Exists(templetFilePath))
                throw new Exception("指定路径的Excel模板文件不存在!");
 
            this.templetFile = templetFilePath;
            this.outputFile = outputFilePath;
 
            //创建一个Application对象并使其可见
            beforeTime = DateTime.Now;
            afterTime = DateTime.Now;
 
            //打开模板文件,得到WorkBook对象
            //workBook = new Workbook(templetFilePath, new LoadOptions(LoadFormat.Unknown));
            workBook = new Workbook(templetFilePath, new LoadOptions());
            workSheet = workBook.Worksheets[0];
        }
 
        /// <summary>
        /// 构造函数,打开一个已有的工作簿
        /// </summary>
        ///<param name="fileName">Excel文件名
        public ExcelHelper(string fileName)
        {
            if (!File.Exists(fileName))
                throw new Exception("指定路径的Excel文件不存在!");
            //创建一个Application对象并使其可见
            beforeTime = DateTime.Now;
            afterTime = DateTime.Now;
 
            //打开一个WorkBook
            workBook = new Workbook(fileName, new LoadOptions());
            workSheet = workBook.Worksheets[0];
        }
 
        /// <summary>
        /// 构造函数,新建一个工作簿
        /// </summary>
        public ExcelHelper()
        {
            //创建一个Application对象并使其可见
            beforeTime = DateTime.Now;
            afterTime = DateTime.Now;
            workBook = new Workbook();
            workSheet = workBook.Worksheets[0];
        }
        #endregion
 
 
        #region WorkSheet Methods
 
        /// <summary>
        /// 改变当前工作表
        /// </summary>
        ///<param name="sheetIndex">工作表索引
        public void ChangeCurrentWorkSheet(int sheetIndex)
        {
            //若指定工作表索引超出范围,则不改变当前工作表
            if (sheetIndex < 1)
                return;
 
            if (sheetIndex > this.WorkSheetCount)
                return;
 
            this.workSheet = workBook.Worksheets[sheetIndex];//(Excel.Worksheet)this.workBook.Sheets.get_Item(sheetIndex);
        }
 
        /// <summary>
        /// 隐藏指定名称的工作表
        /// </summary>
        ///<param name="sheetName">工作表名称
        public void HiddenWorkSheet(string sheetName)
        {
            try
            {
                Worksheet sheet = null;
 
                for (int i = 1; i <= this.WorkSheetCount; i++)
                {
                    workSheet = (Worksheet)workBook.Worksheets[i];//.Sheets.get_Item(i);
 
                    if (workSheet.Name == sheetName)
                        sheet = workSheet;
                }
 
                if (sheet != null)
                    sheet.VisibilityType = VisibilityType.Hidden;//.Visible = Excel.XlSheetVisibility.xlSheetHidden;
                else
                {
                    throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }
 
        /// <summary>
        /// 隐藏指定索引的工作表
        /// </summary>
        ///<param name="sheetIndex">
        public void HiddenWorkSheet(int sheetIndex)
        {
            if (sheetIndex > this.WorkSheetCount)
            {
                throw new Exception("索引超出范围,WorkSheet索引不能大于WorkSheet数量!");
            }
 
            try
            {
                Worksheet sheet = null;
                sheet = (Worksheet)workBook.Worksheets[sheetIndex];//.Sheets.get_Item(sheetIndex);
 
                sheet.VisibilityType = VisibilityType.Hidden;// Excel.XlSheetVisibility.xlSheetHidden;
            }
            catch (Exception e)
            {
                throw e;
            }
        }
 
 
        /// <summary>
        /// 在指定名称的工作表后面拷贝指定个数的该工作表的副本,并重命名
        /// </summary>
        ///<param name="sheetName">复制源【工作表名称】
        ///<param name="sheetCount">工作表个数
        public void CopyWorkSheets(string sheetName, int sheetCount)
        {
            try
            {
                Worksheet sheet = null;
                int sheetIndex = 0;
                if (!string.IsNullOrWhiteSpace(sheetName))
                {
                    for (int i = 0; i < this.WorkSheetCount; i++)
                    {
                        workSheet = workBook.Worksheets[i];//.Sheets.get_Item(i);
 
                        if (workSheet.Name == sheetName)
                        {
                            sheet = workSheet;
                            sheetIndex = workSheet.Index;
                        }
                    }
                }
                else
                {
                    sheet = workSheet;
                }
 
                if (sheet != null)
                {
                    int countWorkSheet = WorkSheetCount + sheetCount;
                    for (int i = WorkSheetCount; i < countWorkSheet; i++)
                    {
                        string name = sheetName + "-" + Convert.ToString(i - sheetIndex + 1);
                        Worksheet sheetTMP = workBook.Worksheets.Add(name);
                        sheetTMP.Copy(sheet);
                    }
                }
                else
                {
                    throw new Exception("名称为\"" + sheetName + "\"的工作表不存在");
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }
 
        public string lastErrorMSG = "";
 
 
 
 
 
        /// <summary>
        /// 将一个工作表拷贝到另一个工作表后面
        /// </summary>
        ///<param name="srcSheetIndex">拷贝源工作表索引
        ///<param name="newSheetName">新工作表名称
        /// <returns></returns>
        public bool CopyWorkSheet(int srcSheetIndex, string newSheetName)
        {
            if (srcSheetIndex > this.WorkSheetCount || srcSheetIndex < 0)
            {
                lastErrorMSG = "索引位置错误";
                return false;
            }
 
            try
            {
                foreach (Worksheet sheet in workBook.Worksheets)
                {
                    if (sheet.Name == newSheetName)
                    {
                        lastErrorMSG = "工作表中已经存在该名称,请选择其他名称";
                        return false;
                    }
                }
                Worksheet srcSheet = (Worksheet)workBook.Worksheets[srcSheetIndex];//.Sheets.get_Item(srcSheetIndex);
                Worksheet aimSheet = (Worksheet)workBook.Worksheets.Add(newSheetName);//[aimSheetIndex];//Sheets.get_Item(aimSheetIndex);
 
                srcSheet.Copy(aimSheet);//(this.missing, aimSheet);
                return true;
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
            }
            return false;
        }
 
        /// <summary>
        /// 根据名称删除工作表
        /// </summary>
        ///<param name="sheetName">工作表名称
        /// <returns></returns>
        public bool DeleteWorkSheet(string sheetName)
        {
            try
            {
                Worksheet sheet = null;
                bool isHave = false;
                //找到名称位sheetName的工作表
                for (int i = 1; i <= this.WorkSheetCount; i++)
                {
                    workSheet = (Worksheet)workBook.Worksheets[i];//.Sheets.get_Item(i);
 
                    if (workSheet.Name == sheetName)
                    {
                        sheet = workSheet;
                        isHave = true;
                    }
                }
                if (!isHave)
                {
                    return true;
                }
 
                if (sheet != null)
                {
                    workBook.Worksheets.RemoveAt(sheetName);
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
            }
            return false;
        }
 
        /// <summary>
        /// 根据索引删除工作表
        /// </summary>
        ///<param name="sheetIndex">工作薄索引
        /// <returns></returns>
        public bool DeleteWorkSheet(int sheetIndex)
        {
            if (sheetIndex > this.WorkSheetCount)
            {
                lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
                return false;
            }
 
            try
            {
                workBook.Worksheets.RemoveAt(sheetIndex);
                return true;
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
                return false;
            }
        }
 
        #endregion
 
        /// <summary>
        /// 将指定索引列的数据相同的行合并,对每个WorkSheet操作
        /// </summary>
        ///<param name="rowIndex">开始列索引
        ///<param name="columnIndex">开始行索引
        ///<param name="count">跨行个数
        /// <returns></returns>
        public bool MergeRows(int rowIndex, int columnIndex, int count)
        {
            if (workSheet != null)
            {
                Cells cells = workSheet.Cells;
                cells.Merge(rowIndex, columnIndex, 1, count);//(rowIndex, columnIndex, count, 1);//, 1, endRowIndex - columnIndex);
                return true;
            }
            return false;
        }
        /// <summary>
        /// 合并行
        /// </summary>
        ///<param name="sheetIndex">单元薄索引
        ///<param name="rowIndex">行索引
        ///<param name="columnIndex">列索引
        ///<param name="count">合并行数
        /// <returns></returns>
        public bool MergeRows(int sheetIndex, int rowIndex, int columnIndex, int count)
        {
            if (sheetIndex > this.WorkSheetCount)
            {
                lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
                return false;
            }
            MergeRows(rowIndex, columnIndex, count);
            return true;
        }
        /// <summary>
        /// 合并列
        /// </summary>
        ///<param name="rowIndex">开始行索引
        ///<param name="columnIndex">开始列索引
        ///<param name="count">合并列数
        /// <returns></returns>
        public bool MergeColumns(int rowIndex, int columnIndex, int count)
        {
            if (workSheet != null)
            {
                Cells cells = workSheet.Cells;
                cells.Merge(rowIndex, columnIndex, count, 1);
            }
 
            return false;
        }
        /// <summary>
        /// 合并列
        /// </summary>
        ///<param name="sheetIndex">单元薄索引
        ///<param name="rowIndex">开始行索引
        ///<param name="columnIndex">开始列索引
        ///<param name="count">合并列数
        /// <returns></returns>
        public bool MergeColumns(int sheetIndex, int rowIndex, int columnIndex, int count)
        {
            if (sheetIndex > this.WorkSheetCount)
            {
                lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
                return false;
            }
            workSheet = workBook.Worksheets[sheetIndex];
            return MergeColumns(rowIndex, columnIndex, count);
            // true;
        }
        /// <summary>
        ///
        /// </summary>
        ///<param name="rowIndex">开始行索引
        ///<param name="columnIndex">开始列索引
        ///<param name="countRow">合并行数
        ///<param name="countColumn">合并列数
        /// <returns></returns>
        public bool MergeRowsAndColumns(int rowIndex, int columnIndex, int countRow, int countColumn)
        {
            if (workSheet == null)
            {
                lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
                return false;
            }
            if (countRow < 1 || countColumn < 1)
            {
                lastErrorMSG = "合并行和列数目设置有错";
                return false;
            }
            Cells cells = workSheet.Cells;
            cells.Merge(rowIndex, columnIndex, countRow, countColumn); //count, 1);
            return true;
        }
        /// <summary>
        /// 合并行和列
        /// </summary>
        ///<param name="sheetIndex">单元薄索引
        ///<param name="rowIndex">开始行索引
        ///<param name="columnIndex">开始列索引
        ///<param name="countRow">合并行数
        ///<param name="countColumn">合并列数
        /// <returns></returns>
        public bool MergeRowsAndColumns(int sheetIndex, int rowIndex, int columnIndex, int countRow, int countColumn)
        {
            if (sheetIndex > this.WorkSheetCount)
            {
                lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
                return false;
            }
            workSheet = workBook.Worksheets[sheetIndex];
            return MergeRowsAndColumns(rowIndex, columnIndex, countRow, countColumn);
        }
 
        /// <summary>
        /// 插行(在指定行下面插入指定数量行)
        /// </summary>
        ///<param name="rowIndex">行号索引从0开始
        ///<param name="count">插入行数
        /// <returns></returns>
        public bool InsertRows(int rowIndex, int count)
        {
            try
            {
                rowIndex = rowIndex + 1;
                if (rowIndex < 0 || count < 0)
                {
                    lastErrorMSG = "行号或行数错误";
                    return false;
                }
                workSheet.Cells.InsertRows(rowIndex, count);
                return true;
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
                return false;
            }
        }
 
 
 
 
 
        /// <summary>
        /// 插行(在指定行下面插入指定数量行)
        /// </summary>
        ///<param name="sheetIndex">
        ///<param name="rowIndex">
        ///<param name="count">
        /// <returns></returns>
        public bool InsertRows(int sheetIndex, int rowIndex, int count)
        {
            if (sheetIndex > this.WorkSheetCount)
            {
                lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
                return false;
            }
            try
            {
                workSheet = workBook.Worksheets[sheetIndex];
                return InsertRows(rowIndex, count);
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
                return false;
            }
        }
 
        /// <summary>
        /// 复制行
        /// </summary>
        ///<param name="sourceRowIndex">源行号索引
        ///<param name="targetRowIndex">目标行索引
        public bool CopyRow(int sourceRowIndex, int targetRowIndex)
        {
            if (sourceRowIndex < 0 || (targetRowIndex) < 0)
            {
                lastErrorMSG = "源行索引或目标行索引错误";
                return false;
            }
            Cells cells = workSheet.Cells;
            workSheet.Cells.CopyRow(cells, sourceRowIndex, targetRowIndex);
            return true;
        }
 
        /// <summary>
        /// 复制多行
        /// </summary>
        ///<param name="sheetIndex">单元薄索引
        ///<param name="rowstartIndex">开始行索引
        ///<param name="rowCount">(从开始行索引 )行数
        ///<param name="targetRowIndex">目标行索引
        /// <returns></returns>
        public bool CopyRows(int sheetIndex, int rowstartIndex, int rowCount, int targetRowIndex)
        {
            if (sheetIndex > this.WorkSheetCount)
            {
                lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
                return false;
            }
            workSheet = workBook.Worksheets[sheetIndex];
            try
            {
                Cells cells = workSheet.Cells;
                workSheet.Cells.CopyRows(cells, rowstartIndex, targetRowIndex, rowCount);
                return true;
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
                return false;
            }
        }
 
 
 
        /// <summary>
        /// 删除行
        /// </summary>
        ///<param name="rowIndex">行索引
        /// <returns></returns>
        public bool DeleteRow(int rowIndex)
        {
            try
            {
                workSheet.Cells.DeleteRow(rowIndex);
                return true;
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
            }
            return false;
        }
        /// <summary>
        /// 删除行
        /// </summary>
        ///<param name="sheetIndex">单元薄索引
        ///<param name="rowIndex">开始行索引
        /// <returns></returns>
        public bool DeleteRow(int sheetIndex, int rowIndex)
        {
            if (sheetIndex > this.WorkSheetCount)
            {
                lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
                return false;
            }
            try
            {
                workSheet = workBook.Worksheets[sheetIndex];
                workSheet.Cells.DeleteRow(rowIndex);
                return true;
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
            }
            return false;
        }
        /// <summary>
        /// 删除多行
        /// </summary>
        ///<param name="rowIndex">开始行索引
        ///<param name="count">行数
        /// <returns></returns>
        public bool DeleteRows(int rowIndex, int count)
        {
            try
            {
                workSheet.Cells.DeleteRows(rowIndex, count);
                return true;
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
            }
            return false;
        }
        /// <summary>
        /// 删除多行
        /// </summary>
        ///<param name="sheetIndex">单元薄索引
        ///<param name="rowIndex">开始行索引
        ///<param name="count">行数
        /// <returns></returns>
        public bool DeleteRows(int sheetIndex, int rowIndex, int count)
        {
            if (sheetIndex > this.WorkSheetCount)
            {
                lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
                return false;
            }
            try
            {
                workSheet = workBook.Worksheets[sheetIndex];
                return DeleteRows(rowIndex, count);
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
            }
            return false;
        }
        /// <summary>
        /// 插列 列的左侧
        /// </summary>
        ///<param name="columnIndex">插入索引
        /// <returns></returns>
        public bool InsertColumn(int columnIndex)
        {
            try
            {
                workSheet.Cells.InsertColumn(columnIndex);//.DeleteColumn(columnIndex);
                return true;
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
                return false;
            }
        }
        /// <summary>
        /// 插列  列的左侧
        /// </summary>
        ///<param name="sheetIndex">单元薄索引
        ///<param name="columnIndex">列索引
        /// <returns></returns>
        public bool InsertColumn(int sheetIndex, int columnIndex)
        {
            if (sheetIndex > this.WorkSheetCount)
            {
                lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
                return false;
            }
            workSheet = workBook.Worksheets[sheetIndex];
            try
            {
                workSheet.Cells.InsertColumn(columnIndex);//.DeleteColumn(columnIndex);
                return InsertColumn(columnIndex);
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
                return false;
            }
        }
        /// <summary>
        /// 插列 列的左侧
        /// </summary>
        ///<param name="columnIndex">列索引
        ///<param name="count">插入列数
        /// <returns></returns>
        public bool InsertColumns(int columnIndex, int count)
        {
            try
            {
                workSheet.Cells.InsertColumns(columnIndex, count);
                return true;
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
                return false;
            }
        }
        /// <summary>
        /// 插列 列的左侧
        /// </summary>
        ///<param name="sheetIndex">单元薄索引
        ///<param name="columnIndex">列索引
        ///<param name="count">列个数
        /// <returns></returns>
        public bool InsertColumns(int sheetIndex, int columnIndex, int count)
        {
            try
            {
                if (sheetIndex > this.WorkSheetCount)
                {
                    lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
                    return false;
                }
                workSheet = workBook.Worksheets[sheetIndex];
                //workSheet.Cells.InsertColumns(columnIndex, count);
                return InsertColumns(columnIndex, count);
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
                return false;
            }
        }
 
        /// <summary>
        /// 复制列
        /// </summary>
        ///<param name="columnIndex">
        ///<param name="count">
        public bool CopyColumn(int columnIndex, int columnTargetIndex)
        {
            try
            {
                Cells cells = workSheet.Cells;
                workSheet.Cells.CopyColumn(cells, columnIndex, columnTargetIndex);
                return true;
 
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
                return false;
            }
        }
        /// <summary>
        /// 复制列
        /// </summary>
        ///<param name="sheetIndex">单元薄索引
        ///<param name="columnIndex">列索引
        ///<param name="columnTargetIndex">复制目标列索引
        /// <returns></returns>
        public bool CopyColumn(int sheetIndex, int columnIndex, int columnTargetIndex)
        {
            try
            {
                if (sheetIndex > this.WorkSheetCount)
                {
                    lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
                    return false;
                }
 
                return CopyColumn(columnIndex, columnTargetIndex);
 
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
                return false;
            }
        }
        /// <summary>
        /// 复制多列
        /// </summary>
        ///<param name="startIndex">列索引
        ///<param name="count">列数目
        ///<param name="targetIndex">目标索引
        /// <returns></returns>
        public bool CopyColumns(int startIndex, int count, int targetIndex)
        {
            try
            {
                Cells cells = workSheet.Cells;
                workSheet.Cells.CopyColumns(cells, startIndex, targetIndex, count);
                return true;
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
                return false;
            }
        }
        /// <summary>
        /// 复制多列
        /// </summary>
        ///<param name="sheetIndex">单元薄索引
        ///<param name="startIndex">源列索引
        ///<param name="count">列数目(源列向后的列)
        ///<param name="targetIndex">目标列索引
        /// <returns></returns>
        public bool CopyColumns(int sheetIndex, int startIndex, int count, int targetIndex)
        {
            try
            {
                if (sheetIndex > this.WorkSheetCount)
                {
                    lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
                    return false;
                }
                workSheet = workBook.Worksheets[sheetIndex];
                return CopyColumns(startIndex, count, targetIndex);
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
                return false;
            }
        }
        /// <summary>
        /// 删除列
        /// </summary>
        ///<param name="columnIndex">列索引
        /// <returns></returns>
        public bool DeleteColumn(int columnIndex)
        {
            try
            {
                workSheet.Cells.DeleteColumn(columnIndex);
                return true;
            }
            catch (Exception excelHelper)
            {
                lastErrorMSG = excelHelper.Message;
                return false;
            }
        }
        /// <summary>
        /// 删除列
        /// </summary>
        ///<param name="sheetIndex">单元薄索引
        ///<param name="columnIndex">列索引
        /// <returns></returns>
        public bool DeleteColumn(int sheetIndex, int columnIndex)
        {
            try
            {
                if (sheetIndex > this.WorkSheetCount)
                {
                    lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
                    return false;
                }
                workSheet = workBook.Worksheets[sheetIndex];
                return DeleteColumn(columnIndex);
            }
            catch (Exception ex)
            {
                lastErrorMSG = ex.Message;
                return false;
            }
        }
        /// <summary>
        /// 删除多列
        /// </summary>
        ///<param name="startIndex">列
        ///<param name="count">数目
        /// <returns></returns>
        public bool DeleteColumns(int startIndex, int count)
        {
            try
            {
                workSheet.Cells.DeleteColumns(startIndex, count, false);
                return true;
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
                return false;
            }
        }
        /// <summary>
        /// 删除多列
        /// </summary>
        ///<param name="sheetIndex">单元薄
        ///<param name="startIndex">列索引
        ///<param name="count">数目
        /// <returns></returns>
        public bool DeleteColumns(int sheetIndex, int startIndex, int count)
        {
            try
            {
                if (sheetIndex > this.WorkSheetCount)
                {
                    lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
                    return false;
                }
                workSheet = workBook.Worksheets[sheetIndex];
                return DeleteColumns(startIndex, count);
            }
            catch (Exception ex)
            {
                lastErrorMSG = ex.Message;
                return false;
            }
        }
        /// <summary>
        /// 将Excel列的字母索引值转换成整数索引值
        /// </summary>
        ///<param name="letter">
        /// <returns></returns>
        public int LetterToInt(string letter)
        {
            int n = 0;
 
            if (letter.Trim().Length == 0)
                throw new Exception("不接受空字符串!");
 
            if (letter.Length >= 2)
            {
                char c1 = letter.ToCharArray(0, 2)[0];
                char c2 = letter.ToCharArray(0, 2)[1];
 
                if (!char.IsLetter(c1) || !char.IsLetter(c2))
                {
                    throw new Exception("格式不正确,必须是字母!");
                }
 
                c1 = char.ToUpper(c1);
                c2 = char.ToUpper(c2);
 
                int i = Convert.ToInt32(c1) - 64;
                int j = Convert.ToInt32(c2) - 64;
 
                n = i * 26 + j;
            }
 
            if (letter.Length == 1)
            {
                char c1 = letter.ToCharArray()[0];
 
                if (!char.IsLetter(c1))
                {
                    throw new Exception("格式不正确,必须是字母!");
                }
 
                c1 = char.ToUpper(c1);
 
                n = Convert.ToInt32(c1) - 64;
            }
 
            if (n > 256)
                throw new Exception("索引超出范围,Excel的列索引不能超过256!");
 
            return n;
        }
 
        /// <summary>
        /// 将Excel列的整数索引值转换为字符索引值
        /// </summary>
        ///<param name="n">
        /// <returns></returns>
        public string IntToLetter(int n)
        {
            if (n > 256)
                throw new Exception("索引超出范围,Excel的列索引不能超过256!");
 
            int i = Convert.ToInt32(n / 26);
            int j = n % 26;
 
            char c1 = Convert.ToChar(i + 64);
            char c2 = Convert.ToChar(j + 64);
 
            if (n > 26)
                return c1.ToString() + c2.ToString();
            else if (n == 26)
                return "Z";
            else
                return c2.ToString();
        }
 
        /// <summary>
        /// 计算WorkSheet数量
        /// </summary>
        ///<param name="rowCount">记录总行数
        ///<param name="rows">每WorkSheet行数
        public int GetSheetCount(int rowCount, int rows)
        {
            int n = rowCount % rows;
 
            if (n == 0)
                return rowCount / rows;
            else
                return Convert.ToInt32(rowCount / rows) + 1;
        }
 
        /// <summary>
        ///  将Excel文件另存为指定格式
        /// </summary>
        ///<param name="fileName">文件名
        ///<param name="format">
        /// <returns></returns>
        public bool SaveAsFile(string fileName, SaveFormat format)
        {
            try
            {
                workBook.Save(fileName, format);
                return true;
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
                return false;
            }
 
        }
        /// <summary>
        /// 保存Excel
        /// </summary>
        ///<param name="fileName">
        /// <returns></returns>
        public bool SaveAsFile(string fileName)
        {
            try
            {
                return SaveAsFile(fileName, SaveFormat.Xlsx);
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
                return false;
                //throw;
            }
        }
        public bool SaveAsFile()
        {
            if (!string.IsNullOrWhiteSpace(outputFile))
            {
                return SaveAsFile(outputFile);
            }
            else
            {
                return false;
            }
        }
 
        /// <summary>
        /// 将DataTable数据写入Excel文件(不分页)
        /// </summary>
        ///<param name="dt">DataTable
        ///<param name="top">表格数据起始行索引
        ///<param name="left">表格数据起始列索引
        public void DataTableToExcel(DataTable dt, int rowIndex, int columnIndex)
        {
            int rowCount = dt.Rows.Count;       //DataTable行数
            int colCount = dt.Columns.Count;    //DataTable列数
 
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                workSheet.Cells[rowIndex, columnIndex + i].PutValue(dt.Columns[i].ColumnName);
                // workSheet.Cells[top, left + i] = "'" + dt.Columns[i].ColumnName;
            }
            for (int j = 1; j < rowCount; j++)
            {
                for (int k = 0; k < colCount; k++)
                {
                    workSheet.Cells[j + rowIndex, k + columnIndex].PutValue(dt.Rows[j][k].ToString());
                }
            }
        }
 
        /// <summary>
        /// 将DataTable数据写入Excel文件(自动分页)
        /// </summary>
        ///<param name="dt">DataTable
        ///<param name="count">每个WorkSheet写入多少行数据
        ///<param name="rowIndex">行索引
        ///<param name="columnIndex">列索引
        public void DataTableToExcel(DataTable dt, int count, int rowIndex, int columnIndex)
        {
            int rowCount = dt.Rows.Count;       //源DataTable行数
            int colCount = dt.Columns.Count;    //源DataTable列数
            sheetCount = this.GetSheetCount(rowCount, count);   //WorkSheet个数
 
            //复制sheetCount-1个WorkSheet对象
            for (int i = 1; i < sheetCount; i++)
            {
                workSheet = (Worksheet)workBook.Worksheets[0];//.get_Item(i);
                string name = sheetPrefixName + "-" + i.ToString();
                Worksheet sheetTMP = workBook.Worksheets.Add(name);
                sheetTMP.Copy(workSheet);
            }
            int dataIndex = 0;
            for (int i = 0; i < sheetCount; i++)
            {
                int startRow = i * count + rowIndex;        //记录起始行索引
                int endRow = (i + 1) * count + rowIndex;            //记录结束行索引
 
                int row = endRow - startRow;
                workSheet = workBook.Worksheets[i];
 
                //将dt中的数据写入WorkSheet
                //for (int j = 0; j < dt.Columns.Count; j++)
                //{
                //    workSheet.Cells[rowIndex, columnIndex + j].PutValue(dt.Columns[j].ColumnName);
                //}
                for (int j = 0; j < row; j++)
                {
                    for (int k = 0; k < colCount; k++)
                    {
                        if (dataIndex < dt.Rows.Count)
                        {
                            workSheet.Cells[j + rowIndex + 1, k + columnIndex].PutValue(dt.Rows[dataIndex][k].ToString());
                        }
                    }
                    dataIndex++;
                }
            }
        }
        /// <summary>
        /// 设置单元格值
        /// </summary>
        ///<param name="rowNum">
        ///<param name="colNum">
        ///<param name="value">
        public void SetCellValue(int rowIndex, int colIndex, string value)
        {
            workSheet.Cells[rowIndex, colIndex].PutValue(value);
        }
        /// <summary>
        /// 设置单元薄为字符串
        /// </summary>
        public void SetSheetFormatString()
        {
            workSheet.Cells.PreserveString = true;
        }
 
        /// <summary>
        /// 将二维数组数据写入Excel文件(不分页)
        /// </summary>
        ///<param name="arr">二维数组
        ///<param name="rowIndex">行索引
        ///<param name="columnIndex">列索引
        /// <returns></returns>
        public bool ArrayToExcel(string[,] arr, int rowIndex, int columnIndex)
        {
            int rowCount = arr.GetLength(0);        //二维数组行数(一维长度)
            int colCount = arr.GetLength(1);    //二维数据列数(二维长度)
            if (rowCount <= 0 || colCount <= 0)
            {
                lastErrorMSG = "二位数组长度设置有错";
                return false;
            }
            try
            {
                for (int i = 0; i < rowCount; i++)//行
                {
                    for (int k = 0; k < colCount; k++)//列
                    {
                        workSheet.Cells[rowIndex + i, columnIndex + k].PutValue(arr[i, k]);
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                lastErrorMSG = ex.Message;
                return false;
            }
 
        }//end ArrayToExcel
        /// <summary>
        /// 将二维数组数据写入Excel文件(不分页)
        /// </summary>
        ///<param name="arr">二维数组
        ///<param name="rowIndex">行索引
        ///<param name="columnIndex">列索引
        /// <returns></returns>
        public bool ArrayObjectToExcel(object[,] arr, int rowIndex, int columnIndex)
        {
            int rowCount = arr.GetLength(0);        //二维数组行数(一维长度)
            int colCount = arr.GetLength(1);    //二维数据列数(二维长度)
            if (rowCount <= 0 || colCount <= 0)
            {
                lastErrorMSG = "二位数组长度设置有错";
                return false;
            }
            try
            {
                for (int i = 0; i < rowCount; i++)//行
                {
                    for (int k = 0; k < colCount; k++)//列
                    {
                        workSheet.Cells[rowIndex + i, columnIndex + k].PutValue(arr[i, k]);
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                lastErrorMSG = ex.Message;
                return false;
            }
 
        }
 
        /// <summary>
        /// 将二维数组数据写入Excel文件(分页)
        /// </summary>
        ///<param name="arr">二位数组
        ///<param name="rowNumber">每页显示行数
        ///<param name="rowIndex">开始行索引
        ///<param name="columnIndex">开始列索引
        /// <returns></returns>
        public bool ArrayToExcel(string[,] arr, int rowNumber, int rowIndex, int columnIndex)
        {
            int rowCount = arr.GetLength(0);        //二维数组行数(一维长度)
            int colCount = arr.GetLength(1);    //二维数据列数(二维长度)
            if (rowCount <= 0 || colCount <= 0)
            {
                lastErrorMSG = "二位数组错误";
                return false;
            }
            sheetCount = this.GetSheetCount(rowCount, rowNumber);   //WorkSheet个数
            try
            {
                //复制sheetCount-1个WorkSheet对象
                for (int i = 1; i < sheetCount; i++)
                {
                    workSheet = (Worksheet)workBook.Worksheets[0];//.get_Item(i);
                    string name = sheetPrefixName + "-" + i.ToString();
                    Worksheet sheetTMP = workBook.Worksheets.Add(name);
                    sheetTMP.Copy(workSheet);
                }
                int dataRow = 0;
                //将二维数组数据写入Excel
                for (int i = 0; i < sheetCount; i++)
                {
                    workSheet = workBook.Worksheets[i];
                    for (int j = 0; j < rowNumber; j++)
                    {
                        for (int k = 0; k < colCount; k++)
                        {
                            if (dataRow < rowCount)
                            {
                                workSheet.Cells[j + rowIndex, k + columnIndex].PutValue(arr[dataRow, k]);
                            }
                        }
                        dataRow++;
                    }
                }
                return true;
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
                return false;
            }
 
        }//end ArrayToExcel
 
 
        /// <summary>
        /// 将二维数组数据写入Excel文件(不分页)
        /// </summary>
        ///<param name="sheetIndex">工作表索引
        ///<param name="arr">二维数组
        ///<param name="rowIndex">行索引
        ///<param name="columnIndex">列索引
        /// <returns></returns>
        public bool ArrayToExcel(int sheetIndex, string[,] arr, int rowIndex, int columnIndex)
        {
            if (sheetIndex > this.WorkSheetCount)
            {
 
                lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
            }
            int rowCount = arr.GetLength(0);        //二维数组行数(一维长度)
            int colCount = arr.GetLength(1);    //二维数据列数(二维长度)
            if (rowCount <= 0 || colCount <= 0)
            {
                return false;
            }
            // 改变当前工作表
            this.workSheet = (Worksheet)this.workBook.Worksheets[sheetIndex];
            return ArrayToExcel(arr, rowIndex, columnIndex);
        }//end ArrayToExcel      
 
        public void Dispose()
        {
 
        }
        /// <summary>
        /// 设置文字水平对齐方式
        /// </summary>
        ///<param name="rowIndex">
        ///<param name="columnIndex">
        /// <returns></returns>
        public bool SetCellHAlignment(int rowIndex, int columnIndex, HorizontalAlignment sy)
        {
            Style styleTitle = workBook.Styles[workBook.Styles.Add()];
            styleTitle.HorizontalAlignment = (TextAlignmentType)(int)sy;
            Cells cells = workSheet.Cells;
            cells[rowIndex, columnIndex].SetStyle(styleTitle);
            return true;
        }
        /// <summary>
        /// 设置垂直对齐方式
        /// </summary>
        ///<param name="rowIndex">
        ///<param name="columnIndex">
        ///<param name="sy">
        /// <returns></returns>
        public bool SetCellVAlignment(int rowIndex, int columnIndex, VerticalAlignment sy)
        {
            Style styleTitle = workBook.Styles[workBook.Styles.Add()];
            styleTitle.VerticalAlignment = (TextAlignmentType)(int)sy;
            Cells cells = workSheet.Cells;
            cells[rowIndex, columnIndex].SetStyle(styleTitle);
            return true;
        }
        /// <summary>
        /// 设置行的垂直对齐方式
        /// </summary>
        ///<param name="rowIndex">
        ///<param name="sy">
        /// <returns></returns>
        public bool SetRowVAlignment(int rowIndex, VerticalAlignment sy)
        {
            Style styleTitle = workBook.Styles[workBook.Styles.Add()];
            styleTitle.VerticalAlignment = (TextAlignmentType)(int)sy;
            Cells cells = workSheet.Cells;
            StyleFlag styleFlag = new StyleFlag();
            styleFlag.All = true;
            cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag);
            return true;
        }
        /// <summary>
        /// 设置行的垂直对齐方式
        /// </summary>
        ///<param name="sheetIndex">单元薄索引
        ///<param name="rowIndex">行索引
        ///<param name="sy">
        /// <returns></returns>
        public bool SetRowVAlignment(int sheetIndex, int rowIndex, VerticalAlignment sy)
        {
            try
            {
                if (sheetIndex > this.WorkSheetCount)
                {
                    lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
                    return false;
                }
                workSheet = workBook.Worksheets[sheetIndex];
                return SetRowVAlignment(rowIndex, sy);
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
                return false;
            }
        }
        /// <summary>
        /// 设置列的充值对其方式
        /// </summary>
        ///<param name="columnIndex">列索引
        ///<param name="sy">
        /// <returns></returns>
        public bool SetColumnVAlignment(int columnIndex, VerticalAlignment sy)
        {
            Cells cells = workSheet.Cells;
            Style styleTitle = workBook.Styles[workBook.Styles.Add()];
            styleTitle.VerticalAlignment = (TextAlignmentType)(int)sy;
            StyleFlag styleFlag = new StyleFlag();
            styleFlag.All = true;
            cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag);
            return true;
        }
        /// <summary>
        /// 设置文字水平对齐方式
        /// </summary>
        ///<param name="rowIndex">
        ///<param name="columnIndex">
        /// <returns></returns>
        public bool SetCellHAlignment(int sheetIndex, int rowIndex, int columnIndex, HorizontalAlignment sy)
        {
            try
            {
                if (sheetIndex > this.WorkSheetCount)
                {
                    lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
                    return false;
                }
                workSheet = workBook.Worksheets[sheetIndex];
                return SetCellHAlignment(rowIndex, columnIndex, sy);
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
                return false;
            }
        }
        /// <summary>
        /// 设置垂直对齐方式
        /// </summary>
        ///<param name="rowIndex">
        ///<param name="columnIndex">
        ///<param name="sy">
        /// <returns></returns>
        public bool SetCellVAlignment(int sheetIndex, int rowIndex, int columnIndex, VerticalAlignment sy)
        {
            try
            {
                if (sheetIndex > this.WorkSheetCount)
                {
                    lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
                    return false;
                }
                workSheet = workBook.Worksheets[sheetIndex];
                return SetCellVAlignment(rowIndex, columnIndex, sy);
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
                return false;
            }
        }
 
 
        /// <summary>
        /// 设置行的S水平对齐方式
        /// </summary>
        ///<param name="sheetIndex">单元薄索引
        ///<param name="rowIndex">行索引
        ///<param name="sy">
        /// <returns></returns>
        public bool SetRowHAlignment(int sheetIndex, int rowIndex, HorizontalAlignment sy)
        {
            try
            {
                if (sheetIndex > this.WorkSheetCount)
                {
                    lastErrorMSG = "索引超出范围,WorkSheet索引不能大于WorkSheet数量!";
                    return false;
                }
                workSheet = workBook.Worksheets[sheetIndex];
                return SetRowHAlignment(rowIndex, sy);
            }
            catch (Exception e)
            {
                lastErrorMSG = e.Message;
                return false;
            }
        }
        /// <summary>
        /// 设置行的水平对齐方式
        /// </summary>
        ///<param name="rowIndex">
        ///<param name="sy">
        /// <returns></returns>
        public bool SetRowHAlignment(int rowIndex, HorizontalAlignment sy)
        {
            Cells cells = workSheet.Cells;
            Style styleTitle = workBook.Styles[workBook.Styles.Add()];
            styleTitle.VerticalAlignment = (TextAlignmentType)(int)sy;
            StyleFlag styleFlag = new StyleFlag();
            styleFlag.All = true;
            cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag);
            return true;
        }
        /// <summary>
        /// 设置列的水平对其方式
        /// </summary>
        ///<param name="columnIndex">列索引
        ///<param name="sy">
        /// <returns></returns>
        public bool SetColumnHAlignment(int columnIndex, VerticalAlignment sy)
        {
            Cells cells = workSheet.Cells;
            Style styleTitle = workBook.Styles[workBook.Styles.Add()];
            styleTitle.VerticalAlignment = (TextAlignmentType)(int)sy;
            StyleFlag styleFlag = new StyleFlag();
            styleFlag.All = true;
            cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag);
            return true;
        }
        /// <summary>
        /// 设置单元格字体颜色
        /// </summary>
        ///<param name="color">
        ///<param name="rowIndex">
        ///<param name="columnIndex">
        /// <returns></returns>
        public bool SetCellFontColor(System.Drawing.Color color, int rowIndex, int columnIndex)
        {
            Style styleTitle = workBook.Styles[workBook.Styles.Add()];
            styleTitle.Font.Color = color;//.VerticalAlignment = (TextAlignmentType)(int)sy;
            Cells cells = workSheet.Cells;
            cells[rowIndex, columnIndex].SetStyle(styleTitle);
            return true;
        }
        /// <summary>
        /// 设置行的字体颜色
        /// </summary>
        ///<param name="color">
        ///<param name="rowIndex">
        /// <returns></returns>
        public bool SetRowFontColor(System.Drawing.Color color, int rowIndex)
        {
            Cells cells = workSheet.Cells;
            Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
            styleTitle.Font.Color = color;
            StyleFlag styleFlag = new StyleFlag();
            styleFlag.All = true;
            cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag);
            return true;
        }
        /// <summary>
        /// 设置列的字体颜色
        /// </summary>
        ///<param name="color">
        ///<param name="columnIndex">
        /// <returns></returns>
        public bool SetColumnFontColor(System.Drawing.Color color, int columnIndex)
        {
            Cells cells = workSheet.Cells;
            Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
            styleTitle.Font.Color = color;
            StyleFlag styleFlag = new StyleFlag();
            styleFlag.All = true;
            cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag);
            return true;
        }
        /// <summary>
        /// 设置字体名称
        /// </summary>
        ///<param name="name">
        ///<param name="rowIndex">
        ///<param name="columnIndex">
        /// <returns></returns>
        public bool SetCellFontName(string name, int rowIndex, int columnIndex)
        {
            Style styleTitle = GetStyle;
            styleTitle.Font.Name = name;//.VerticalAlignment = (TextAlignmentType)(int)sy;
            Cells cells = workSheet.Cells;
            cells[rowIndex, columnIndex].SetStyle(styleTitle);
            return true;
        }
        /// <summary>
        /// 设置行字体名称
        /// </summary>
        ///<param name="name">
        ///<param name="rowIndex">
        /// <returns></returns>
        public bool SetRowFontName(string name, int rowIndex)
        {
            Cells cells = workSheet.Cells;
            Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
            styleTitle.Font.Name = name;
            StyleFlag styleFlag = new StyleFlag();
            styleFlag.All = true;
            cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag);
            return true;
        }
        /// <summary>
        /// 设置列字体名称
        /// </summary>
        ///<param name="name">
        ///<param name="columnIndex">
        /// <returns></returns>
        public bool SetColumnFontName(string name, int columnIndex)
        {
            Cells cells = workSheet.Cells;
            Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
            styleTitle.Font.Name = name;
            StyleFlag styleFlag = new StyleFlag();
            styleFlag.All = true;
            cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag);
            return true;
        }
 
        /// <summary>
        /// 设置字体大小
        /// </summary>
        ///<param name="size">
        ///<param name="rowIndex">
        ///<param name="columnIndex">
        /// <returns></returns>
        public bool SetCellFontSize(int size, int rowIndex, int columnIndex)
        {
            Style styleTitle = GetStyle;
            styleTitle.Font.Size = size;//.VerticalAlignment = (TextAlignmentType)(int)sy;
            Cells cells = workSheet.Cells;
            cells[rowIndex, columnIndex].SetStyle(styleTitle);
            return true;
        }
        /// <summary>
        /// 设置行字体大小
        /// </summary>
        ///<param name="size">
        ///<param name="rowIndex">
        /// <returns></returns>
        public bool SetRowFontSize(int size, int rowIndex)
        {
            Cells cells = workSheet.Cells;
            Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
            styleTitle.Font.Size = size;
            StyleFlag styleFlag = new StyleFlag();
            styleFlag.All = true;
            cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag);
            return true;
        }
        /// <summary>
        /// 设置列的字体大小
        /// </summary>
        ///<param name="size">
        ///<param name="columnIndex">
        /// <returns></returns>
        public bool SetColumnFontSize(int size, int columnIndex)
        {
            Cells cells = workSheet.Cells;
            Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
            styleTitle.Font.Size = size;
            StyleFlag styleFlag = new StyleFlag();
            styleFlag.All = true;
            cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag);
            return true;
        }
 
        /// <summary>
        /// 字体是否加粗
        /// </summary>
        ///<param name="iSBold">
        ///<param name="rowIndex">
        ///<param name="columnIndex">
        /// <returns></returns>
        public bool SetCellFontIsBold(bool iSBold, int rowIndex, int columnIndex)
        {
            Style styleTitle = GetStyle;
            styleTitle.Font.IsBold = iSBold;//.VerticalAlignment = (TextAlignmentType)(int)sy;
            Cells cells = workSheet.Cells;
            cells[rowIndex, columnIndex].SetStyle(styleTitle);
            return true;
        }
        /// <summary>
        /// 设置字体行是否加租
        /// </summary>
        ///<param name="iSBold">
        ///<param name="rowIndex">
        /// <returns></returns>
        public bool SetRowFontIsBold(bool iSBold, int rowIndex)
        {
            Cells cells = workSheet.Cells;
            Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
            styleTitle.Font.IsBold = iSBold;
            StyleFlag styleFlag = new StyleFlag();
            styleFlag.All = true;
            cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag);
            return true;
        }
        /// <summary>
        /// 设置字体列是否加粗
        /// </summary>
        ///<param name="iSBold">
        ///<param name="columnIndex">
        /// <returns></returns>
        public bool SetColumnFontIsBold(bool iSBold, int columnIndex)
        {
            Cells cells = workSheet.Cells;
            Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
            styleTitle.Font.IsBold = iSBold;
            StyleFlag styleFlag = flag;
            cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag);
            return true;
        }
        public StyleFlag flag
        {
            get
            {
                StyleFlag styleFlag = new StyleFlag();
                styleFlag.All = true;
                return styleFlag;
            }
        }
        /// <summary>
        /// 字体斜体
        /// </summary>
        ///<param name="iSItalic">
        ///<param name="rowIndex">
        ///<param name="columnIndex">
        /// <returns></returns>
        public bool SetCellIsItalic(bool iSItalic, int rowIndex, int columnIndex)
        {
            Style styleTitle = GetStyle;
            styleTitle.Font.IsItalic = iSItalic;//.VerticalAlignment = (TextAlignmentType)(int)sy;
            Cells cells = workSheet.Cells;
            cells[rowIndex, columnIndex].SetStyle(styleTitle);
            return true;
        }
        /// <summary>
        /// 设置行 字体斜体
        /// </summary>
        ///<param name="iSItalic">
        ///<param name="rowIndex">
        /// <returns></returns>
        public bool SetRowIsItalic(bool iSItalic, int rowIndex)
        {
            Cells cells = workSheet.Cells;
            Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
            styleTitle.Font.IsItalic = iSItalic;
            StyleFlag styleFlag = flag;
            cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag);
            return true;
        }
        /// <summary>
        /// 设置列 字体是否为斜体
        /// </summary>
        ///<param name="iSItalic">
        ///<param name="columnIndex">
        /// <returns></returns>
        public bool SetColumnIsItali(bool iSItalic, int columnIndex)
        {
            Cells cells = workSheet.Cells;
            Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
            styleTitle.Font.IsItalic = iSItalic;
            StyleFlag styleFlag = flag;
            cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag);
            return true;
        }
 
        /// <summary>
        /// 设置删除线
        /// </summary>
        ///<param name="iStrike">
        ///<param name="rowIndex">
        ///<param name="columnIndex">
        /// <returns></returns>
        public bool SetCellIsStrikeOut(bool iStrike, int rowIndex, int columnIndex)
        {
            Style styleTitle = GetStyle;
            styleTitle.Font.IsStrikeout = iStrike;//.VerticalAlignment = (TextAlignmentType)(int)sy;
            Cells cells = workSheet.Cells;
            cells[rowIndex, columnIndex].SetStyle(styleTitle);
            return true;
        }
        /// <summary>
        /// 设置行是否删除线
        /// </summary>
        ///<param name="iStrike">
        ///<param name="rowIndex">
        /// <returns></returns>
        public bool SetRowIsStrikeOut(bool iStrike, int rowIndex)
        {
            Cells cells = workSheet.Cells;
            Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
            styleTitle.Font.IsStrikeout = iStrike;
            StyleFlag styleFlag = flag;
            cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag);
            return true;
        }
        /// <summary>
        /// 设置列是否删除线
        /// </summary>
        ///<param name="iStrike">
        ///<param name="columnIndex">
        /// <returns></returns>
        public bool SetColumnIsStrikeOut(bool iStrike, int columnIndex)
        {
            Cells cells = workSheet.Cells;
            Style styleTitle = GetStyle;//workBook.Styles[workBook.Styles.Add()];
            styleTitle.Font.IsStrikeout = iStrike;
            StyleFlag styleFlag = flag;
            cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag);
            return true;
        }
        /// <summary>
        /// 设置边框是否实线
        /// </summary>
        ///<param name="rowIndex">行索引
        ///<param name="columnIndex">列索引
        ///<param name="left">左侧是否为实线
        ///<param name="right">右侧是否为实线
        ///<param name="top">上部是否为实线
        ///<param name="bottom">下部是否为实线
        /// <returns></returns>
        public bool SetCellBorder(int rowIndex, int columnIndex, bool left = false, bool right = false, bool top = false, bool bottom = false)
        {
            Cells cells = workSheet.Cells;
            Style styleTitle = GetStyle;
            StyleFlag styleFlag = flag;
            styleTitle.Borders[BorderType.LeftBorder].LineStyle = left == false ? CellBorderType.None : CellBorderType.Thin;
            styleTitle.Borders[BorderType.RightBorder].LineStyle = right == false ? CellBorderType.None : CellBorderType.Thin;
            styleTitle.Borders[BorderType.TopBorder].LineStyle = top == false ? CellBorderType.None : CellBorderType.Thin;
            styleTitle.Borders[BorderType.BottomBorder].LineStyle = bottom == false ? CellBorderType.None : CellBorderType.Thin;
            cells[rowIndex, columnIndex].SetStyle(styleTitle);
            return true;
        }
        /// <summary>
        /// 设置行边框是否为实线
        /// </summary>
        ///<param name="rowIndex">
        ///<param name="left">
        ///<param name="right">
        ///<param name="top">
        ///<param name="bottom">
        /// <returns></returns>
        public bool SetRowBorder(int rowIndex, bool left = false, bool right = false, bool top = false, bool bottom = false)
        {
            Cells cells = workSheet.Cells;
            Style styleTitle = GetStyle;
            StyleFlag styleFlag = flag;
            styleTitle.Borders[BorderType.LeftBorder].LineStyle = left == false ? CellBorderType.None : CellBorderType.Thin;
            styleTitle.Borders[BorderType.RightBorder].LineStyle = right == false ? CellBorderType.None : CellBorderType.Thin;
            styleTitle.Borders[BorderType.TopBorder].LineStyle = top == false ? CellBorderType.None : CellBorderType.Thin;
            styleTitle.Borders[BorderType.BottomBorder].LineStyle = bottom == false ? CellBorderType.None : CellBorderType.Thin;
            cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag);
            return true;
        }
        /// <summary>
        /// 设置列的边框是否为实线
        /// </summary>
        ///<param name="columnIndex">
        ///<param name="left">
        ///<param name="right">
        ///<param name="top">
        ///<param name="bottom">
        /// <returns></returns>
        public bool SetColumnBorder(int columnIndex, bool left = false, bool right = false, bool top = false, bool bottom = false)
        {
            Cells cells = workSheet.Cells;
            Style styleTitle = GetStyle;
            StyleFlag styleFlag = flag;
            styleTitle.Borders[BorderType.LeftBorder].LineStyle = left == false ? CellBorderType.None : CellBorderType.Thin;
            styleTitle.Borders[BorderType.RightBorder].LineStyle = right == false ? CellBorderType.None : CellBorderType.Thin;
            styleTitle.Borders[BorderType.TopBorder].LineStyle = top == false ? CellBorderType.None : CellBorderType.Thin;
            styleTitle.Borders[BorderType.BottomBorder].LineStyle = bottom == false ? CellBorderType.None : CellBorderType.Thin;
            cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag);
            return true;
        }
        /// <summary>
        /// 设置单元格背景色 为纯色
        /// </summary>
        ///<param name="rowIndex">
        ///<param name="columnIndex">
        ///<param name="color">
        /// <returns></returns>
        public bool SetCellBackColor(int rowIndex, int columnIndex, System.Drawing.Color color)
        {
            var s = workSheet.Cells[rowIndex, columnIndex].GetStyle();
            s.ForegroundColor = color;
            s.Pattern = BackgroundType.Solid;
            workSheet.Cells[rowIndex, columnIndex].SetStyle(s);
            return true;
        }
        /// <summary>
        /// 设置行的背景色 为纯色
        /// </summary>
        ///<param name="rowIndex">
        ///<param name="color">
        /// <returns></returns>
        public bool SetRowBackColor(int rowIndex, System.Drawing.Color color)
        {
            Cells cells = workSheet.Cells;
            Style styleTitle = GetStyle;
            styleTitle.ForegroundColor = color;
            styleTitle.Pattern = BackgroundType.Solid;
 
            StyleFlag styleFlag = flag;
            cells.Rows[rowIndex].ApplyStyle(styleTitle, styleFlag);
            return true;
        }
        /// <summary>
        /// 设置列的背景色 为纯色
        /// </summary>
        ///<param name="columnIndex">
        ///<param name="color">
        /// <returns></returns>
        public bool SetColumnBackColor(int columnIndex, System.Drawing.Color color)
        {
            Cells cells = workSheet.Cells;
            Style styleTitle = GetStyle;
            styleTitle.ForegroundColor = color;
            styleTitle.Pattern = BackgroundType.Solid;
 
            StyleFlag styleFlag = flag;
            cells.Columns[columnIndex].ApplyStyle(styleTitle, styleFlag);
            return true;
        }
 
        public Style GetStyle
        {
            get
            {
                return workBook.Styles[workBook.Styles.Add()];
            }
        }
 
        /// <summary>
        /// DataTable 导出至模板excel中
        /// </summary>
        ///<param name="table">数据源
        ///<param name="dataRowNum">每页数据的行数(无表头表尾,只是数据)
        ///<param name="pageRowNum">每页总行数(加上表头与表尾)
        ///<param name="firstTitleRowNum">首页标题的行数(表头)
        ///<param name="pageTiletRowNum">表头+表尾行数(分页时中间的行数就是第一页表尾+第二页表头)
        ///<param name="columnNum">要输出的列数(前几列)
        ///<param name="MergeColumns">合并列例如(2,3合并,输入3即可,如果多行:2,3,4合并,则输入3,4)'
        ///<param name="pageColumnIndex">分页列,例如以班组分页就写班组所在的列id[必须以该列进行排序]
        /// <returns></returns>
        public List<excelinfo> TableToExcel(DataTable table, int dataRowNum, int pageRowNum, int firstTitleRowNum, int pageTiletRowNum, int columnNum, int[] MergeColumns, int pageColumnIndex)
        {
            try
            {
                List<excelinfo> listBanzu = null;
                //分页标识(例如 班组不同分页)
                string pageflag = string.Empty;
                if (pageColumnIndex > 0)
                {
                    pageflag = table.Rows[0][pageColumnIndex].ToString();
                }
 
                //必须先分页.
                //总页数
                int pagecount = 1;
                //分页行数
                int fenge = firstTitleRowNum;
 
                int fenye = -1;
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    if (fenye == -1)
                        fenye = i;
                    //正常分页
                    if (fenye > 0 && fenye % dataRowNum == 0)
                    {
                        CopyRows(0, 0, pageRowNum, pageRowNum * pagecount);
                        pagecount++;
                        fenye = 0;
                    }
                    //遇到分页组分页
                    if (table.Rows[i][pageColumnIndex].ToString() != pageflag)
                    {
                        pageflag = table.Rows[i][pageColumnIndex].ToString();
                        CopyRows(0, 0, pageRowNum, pageRowNum * pagecount);
                        pagecount++;
                        fenye = 0;
                    }
                    fenye++;
                }
 
                if (pageColumnIndex > 0)
                {
                    listBanzu = new List<excelinfo>();
                    pageflag = table.Rows[0][pageColumnIndex].ToString();
                }
                fenye = -1;
 
 
                int id = 1;
                //3.插入数据 每一页
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    if (fenye == -1)
                        fenye = i;
                    //正常分页
                    if (fenye > 0 && fenye % dataRowNum == 0)
                    {
                        if (listBanzu != null)
                            listBanzu.Add(new ExcelInfo() { pageID = id, banzu = pageflag });
                        fenge += pageTiletRowNum;
                        fenye = 0;
                        id++;
                    }
                    //遇到分页组分页
                    if (table.Rows[i][pageColumnIndex].ToString() != pageflag)
                    {
                        if (listBanzu != null)
                            listBanzu.Add(new ExcelInfo() { pageID = id, banzu = pageflag });
                        pageflag = table.Rows[i][pageColumnIndex].ToString();
                        fenge += dataRowNum - fenye + pageTiletRowNum; //每页行数-已有行数+表头表尾
                        fenye = 0;
                        id++;
                    }
 
                    //合并行
                    int col = -1;
                    for (int t = 0; t < columnNum; t++)
                    {
                        if (col == -1)
                        {
                            col = t;
                        }
                        if (MergeColumns != null)
                        {
                            for (int m = 0; m < MergeColumns.Count(); m++)
                            {
                                if (t == MergeColumns[m])
                                {
                                    col++;
                                }
                            }
                        }
                        SetCellValue(i + fenge, col, table.Rows[i][t].ToString());
                        col++;
                    }
                    fenye++;
                }
                if (listBanzu.Count < pagecount) {
                    //增加最后一页
                    listBanzu.Add(new ExcelInfo() { pageID = pagecount, banzu = pageflag });
                }
                #region
                //int total = table.Rows.Count;
                //int count = total / dataRowNum;
                //if (total % dataRowNum > 0)
                //{
                //    count += 1;
                //}
 
                ////2.分页复制模板
                //for (int i = 0; i < count; i++)
                //{
                //    CopyRows(0, 0, pageRowNum, i * pageRowNum);
                //}
 
                //int fenge = firstTitleRowNum;
 
                //if (table.Columns.Count < columnNum)
                //{
                //    columnNum = table.Columns.Count;
                //}
 
                ////3.插入数据 每一页
                //for (int i = 0; i < table.Rows.Count; i++)
                //{
                //    if (i > 0 && i % dataRowNum == 0)
                //    {
                //        fenge += pageTiletRowNum;
                //    }
 
                //    int col = -1;
                //    for (int t = 0; t < columnNum; t++)
                //    {
                //        if (col == -1)
                //        {
                //            col = t;
                //        }
                //        if (MergeColumns != null)
                //        {
                //            for (int m = 0; m < MergeColumns.Count(); m++)
                //            {
                //                if (t == MergeColumns[m])
                //                {
                //                    col++;
                //                }
                //            }
                //        }
                //        SetCellValue(i + fenge, col, table.Rows[i][t].ToString());
                //        col++;
                //    }
                //}
                #endregion
                return listBanzu;
            }
            catch (Exception ex)
            {
                lastErrorMSG = ex.Message;
                return null;
            }
        }
 
    }
    public class ExcelInfo
    {
        public int pageID;
        public string banzu;
        public string date;
    }
    /// <summary>
    /// 文字水平显示
    /// </summary>
    public enum HorizontalAlignment
    {
        Bottom = 0,
        Center = 1,
        CenterAcross = 2,
        Distributed = 3,
        Fill = 4,
        General = 5,
        Justify = 6,
        Left = 7,
        Right = 8,
        Top = 9,
    }
    /// <summary>
    /// 垂直显示
    /// </summary>
    public enum VerticalAlignment
    {
        Bottom = 0,
        Center = 1,
        CenterAcross = 2,
        Distributed = 3,
        Fill = 4,
        General = 5,
        Justify = 6,
        Left = 7,
        Right = 8,
        Top = 9,
    }
}
</excelinfo></excelinfo></excelinfo>

   

调用


var v = dt.DefaultView;
               v.Sort = "班组";
               ExcelHelper exhelp = new ExcelHelper("templet\\kq.xlsx", "1");
               List<excelinfo> banzu = exhelp.TableToExcel(v.ToTable(), 20, 28, 5, 8, 35, new int[] { 3 }, 35);
               if (banzu != null)
               {
                   foreach (var item in banzu)
                   {
                       exhelp.SetCellValue((item.pageID - 1) * 28, 0, "考勤表");
                       exhelp.SetCellValue((item.pageID - 1) * 28 + 2, 0, "部门名称(全称): xx名称");
                       exhelp.SetCellValue((item.pageID - 1) * 28 + 2, 10, "班组:" + item.banzu);
                       exhelp.SetCellValue((item.pageID - 1) * 28 + 2, 24, dt.Rows[0]["月份"].ToString());
                       exhelp.SetCellValue((item.pageID - 1) * 28 + 27, 0, "第" + item.pageID + "页,共" + banzu.Count + "页                     o=缺勤 √ =出勤");
                   }
 
                   SaveFileDialog sfd = new SaveFileDialog();
                   sfd.AddExtension = true;
                   sfd.DefaultExt = "xlsx";
                   if (sfd.ShowDialog() == DialogResult.OK)
                   {
                       exhelp.SaveAsFile(sfd.FileName);
                   }
                   return true;
               }
               return false;</excelinfo>

 本文由职坐标整理并发布,了解更多内容,请关注职坐标编程语言C#.NET频道!

本文由 @Vivian 发布于职坐标。未经许可,禁止转载。
喜欢 | 1 不喜欢 | 0
看完这篇文章有何感觉?已经有1人表态,100%的人喜欢 快给朋友分享吧~
评论(0)
后参与评论

您输入的评论内容中包含违禁敏感词

我知道了

助您圆梦职场 匹配合适岗位
验证码手机号,获得海同独家IT培训资料
选择就业方向:
人工智能物联网
大数据开发/分析
人工智能Python
Java全栈开发
WEB前端+H5

请输入正确的手机号码

请输入正确的验证码

获取验证码

您今天的短信下发次数太多了,明天再试试吧!

提交

我们会在第一时间安排职业规划师联系您!

您也可以联系我们的职业规划师咨询:

小职老师的微信号:z_zhizuobiao
小职老师的微信号:z_zhizuobiao

版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
 沪公网安备 31011502005948号    

©2015 www.zhizuobiao.com All Rights Reserved

208小时内训课程