/// <summary>

    /// Excel操作类（引用程序集Aspose.Cells.dll）

    /// </summary>

    public class ExcelOperation

    {

        /// <summary>

        /// 读取指定路径的Excel文件为DataTable

        /// </summary>

        /// <param name="fileName">文件全路径</param>

        /// <param name="firstRow">要开始读取的第一行在Excel中的行索引（默认会以此行各列数据作为DataTable的列名）</param>

        /// <param name="firstColumn">要开始读取的第一列在Excel中的列索引</param>

        /// <param name="invalidEndRowNumber">文件尾部无效行数目（该部分所有行数据将不会被读取）</param>

        /// <returns>返回DataTable</returns>

        public static DataTable ReadExcel(string fileName, int firstRow, int firstColumn, int invalidEndRowNumber)

        {

            Cells cells;

            Workbook workbook = new Workbook(fileName);

            cells = workbook.Worksheets[].Cells;

            if (cells == null)

            {

                return null;

            }

            DataTable dt = cells.ExportDataTable(firstRow, firstColumn, cells.MaxDataRow - invalidEndRowNumber, cells.MaxDataColumn);

            string realColumnName = string.Empty;

            for (int i = ; i < dt.Columns.Count; i++)

            {

                if (dt.Rows[][i] == null || dt.Rows[][i].ToString().Trim() == string.Empty)

                {

                    realColumnName += dt.Rows[][i];

                    dt.Columns[i].ColumnName = realColumnName.Replace(" ", string.Empty);

                    realColumnName = realColumnName.TrimEnd(dt.Rows[][i].ToString().ToCharArray()).Replace(" ", string.Empty);

                }

                else

                {

                    realColumnName = dt.Rows[][i].ToString();

                    if ((i + ) <= cells.MaxColumn -  && (dt.Rows[][i + ] == null || dt.Rows[][i + ].ToString().Trim() == string.Empty))

                    {

                        realColumnName += dt.Rows[][i].ToString();

                        dt.Columns[i].ColumnName = realColumnName.Replace(" ", string.Empty);

                        realColumnName = realColumnName.Remove(realColumnName.Length - dt.Rows[][i].ToString().Length - , dt.Rows[][i].ToString().Length).Replace(" ", string.Empty);

                    }

                    else

                    {

                        dt.Columns[i].ColumnName = realColumnName.Replace(" ", string.Empty);

                    }

                }

            }

            return dt;

        }

        /// <summary>

        /// 将DataTable导出指定路径的Excel文件

        /// </summary>

        /// <param name="dt">DataTable</param>

        /// <param name="tableName">Excel文件的表头内容</param>

        /// <param name="path">文件保存的全路径</param>

        public static void ExportExcel(DataTable dt, string headerText, string path)

        {

            Workbook workbook = new Workbook(); //工作簿

            Worksheet sheet = workbook.Worksheets[]; //工作表

            Cells cells = sheet.Cells;//单元格 

            //为标题设置样式

            Style styleTitle = workbook.Styles[workbook.Styles.Add()];//新增样式

            styleTitle.HorizontalAlignment = TextAlignmentType.Center;//文字居中

            styleTitle.Font.Name = "宋体";//文字字体

            styleTitle.Font.Size = ;//文字大小

            styleTitle.Font.IsBold = true;//粗体 

            //样式2

            Style style2 = workbook.Styles[workbook.Styles.Add()];//新增样式

            style2.HorizontalAlignment = TextAlignmentType.Center;//文字居中

            style2.Font.Name = "宋体";//文字字体

            style2.Font.Size = ;//文字大小

            style2.Font.IsBold = true;//粗体

            style2.IsTextWrapped = true;//单元格内容自动换行

            style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;

            style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;

            style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;

            style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

            //样式3

            Style style3 = workbook.Styles[workbook.Styles.Add()];//新增样式

            style3.HorizontalAlignment = TextAlignmentType.Center;//文字居中

            style3.Font.Name = "宋体";//文字字体

            style3.Font.Size = ;//文字大小

            style3.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;

            style3.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;

            style3.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;

            style3.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

            int Colnum = dt.Columns.Count;//表格列数

            int Rownum = dt.Rows.Count;//表格行数 

            //生成行1 标题行

            cells.Merge(, , , Colnum);//合并单元格

            cells[, ].PutValue(headerText);//填写内容

            cells[, ].SetStyle(styleTitle);

            cells.SetRowHeight(, );

            //生成行2 列名行

            for (int i = ; i < Colnum; i++)

            {

                cells[, i].PutValue(dt.Columns[i].ColumnName);

                cells[, i].SetStyle(style2);

                cells.SetRowHeight(, );

            }

            //生成数据行

            for (int i = ; i < Rownum; i++)

            {

                for (int k = ; k < Colnum; k++)

                {

                    cells[ + i, k].PutValue(dt.Rows[i][k].ToString());

                    cells[ + i, k].SetStyle(style3);

                }

                cells.SetRowHeight( + i, );

            }

            workbook.Save(path);

        }

    }