.net 开发常用帮助类——ExcelHelp

using System.Data;
using Aspose.Cells;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Drawing;

namespace CbdFinance.Common
{
    /// <summary>
    /// Excel帮助类[读取，导出]
    /// </summary>
    public class ExcelHelp
    {
        /// <summary>
        /// 生成Excel文件(导出)
        /// </summary>
        /// <returns></returns>
        //public static ExcelPackage ExportExcel(,string workbook = "Sheet1")
        //{
        //    ExcelPackage package = new ExcelPackage();
        //    //创建工作表 【匹配成功明细】
        //    ExcelWorksheet worksheetSucceed = package.Workbook.Worksheets.Add("匹配成功明细");

        //    //设置表头
        //    SetTableHead(worksheetSucceed, "商户订单号", "账务日期", "订单金额");

        //    //设置表头样式
        //    SetTableHeadStyle(worksheetSucceed, 3, 33, 20, 13);

        //    var rowIndex = 2;
        //    foreach (var item in succeedList)
        //    {
        //        SetTableBoby(rowIndex, worksheetSucceed, item.OrderNo, item.AccountingDate.ToString("yyyy-MM-dd HH:mm:ss"), item.OrderAmount);
        //        rowIndex++;
        //    }
        //    succeedList.Clear();
        //    return package;
        //}

        /// <summary>
        /// 设置标题
        /// </summary>
        /// <param name="worksheet">工作表对象</param>
        /// <param name="title">标题</param>
        /// <param name="mergeCellNum">合并数量</param>
        public static void SetTableOneTitle(ExcelWorksheet worksheet, string title, int mergeCellNum = 1)
        {
            worksheet.Cells[1, mergeCellNum].Value = title;
            headStyle(worksheet, 1);
        }

        /// <summary>
        /// 设置标题
        /// </summary>
        /// <param name="worksheet">工作表对象</param>
        /// <param name="title">标题</param>
        /// <param name="mergeCellNum">合并数量</param>
        public static void SetTableOneTitle(ExcelWorksheet worksheet, string title, string titleVal, int mergeCellNum = 2)
        {
            worksheet.Cells[1, 1].Value = title;
            mergeCellNum = mergeCellNum <= 2 ? 2 : mergeCellNum;
            worksheet.Cells[1, mergeCellNum].Value = titleVal;
            headStyle(worksheet, 1);
        }

        /// <summary>
        /// 设置表头(列名)
        /// </summary>
        /// <param name="worksheet">工作表对象</param>
        /// <param name="cellNameList">列名集合</param>
        public static void SetTableHead(ExcelWorksheet worksheet, List<string> cellNameList)
        {
            for (int i = 1; i <= cellNameList.Count; i++)
            {
                worksheet.Cells[1, i].Value = cellNameList[i - 1];
            }
        }

        /// <summary>
        /// 设置表头(列名)
        /// </summary>
        /// <param name="worksheet">工作表对象</param>
        /// <param name="cellNameList">列名集合</param>
        public static void SetTableHead(ExcelWorksheet worksheet, params string[] cellNameList)
        {
            for (int i = 1; i <= cellNameList.Length; i++)
            {
                worksheet.Cells[1, i].Value = cellNameList[i - 1];
            }
        }

        /// <summary>
        /// 设置表头样式
        /// </summary>
        /// <param name="worksheetSucceed">工作表对象</param>
        /// <param name="cellCount">列数量</param>
        /// <param name="widths">列宽</param>
        public static void SetTableHeadStyle(ExcelWorksheet worksheet, int cellCount, params int[] widths)
        {
            //默认宽度
            const int width = 15;
            for (int i = 1; i <= cellCount; i++)
            {
                worksheet.Cells[1, i].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                worksheet.Cells[1, i].Style.Font.Bold = true;//字体为粗体
                worksheet.Cells[1, i].Style.Font.Color.SetColor(Color.Wheat);//字体颜色
                worksheet.Cells[1, i].Style.Font.Name = "微软雅黑";//字体
                worksheet.Cells[1, i].Style.Font.Size = 12;//字体大小

                //设置单元格背景样式
                worksheet.Cells[1, i].Style.Fill.PatternType = ExcelFillStyle.Solid;
                worksheet.Cells[1, i].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(55, 145, 231));//设置单元格背景色 Color.FromArgb(128, 128, 128)灰色
                //设置单元格边框，两种方法
                //方法一
                worksheet.Cells[1, i].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(55, 145, 231));//设置单元格所有边框 (191, 191, 191)灰色
                //方法二
                //worksheet.Cells[1, 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;//单独设置单元格底部边框样式和颜色（上下左右均可分开设置）
                //worksheet.Cells[1, 1].Style.Border.Bottom.Color.SetColor(Color.FromArgb(191, 191, 191));　

                //设置单元格的行高和列宽
                //worksheet.Cells.Style.ShrinkToFit = true;//单元格自动适应大小
                //worksheet.Row(1).Height = 15;//设置行高
                //worksheet.Row(1).CustomHeight = true;//自动调整行高
                if (widths != null && widths.Count() > 0 && i < widths.Count())
                {
                    worksheet.Column(i).Width = widths[i - 1];//设置列宽
                }
                else
                {
                    worksheet.Column(i).Width = width;
                }
            }
        }

        private static void headStyle(ExcelWorksheet worksheet, int i)
        {
            worksheet.Cells[1, i].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
            worksheet.Cells[1, i].Style.Font.Bold = true;//字体为粗体
            worksheet.Cells[1, i].Style.Font.Color.SetColor(Color.Wheat);//字体颜色
            worksheet.Cells[1, i].Style.Font.Name = "微软雅黑";//字体
            worksheet.Cells[1, i].Style.Font.Size = 12;//字体大小

            //设置单元格背景样式
            worksheet.Cells[1, i].Style.Fill.PatternType = ExcelFillStyle.Solid;
            worksheet.Cells[1, i].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(55, 145, 231));//设置单元格背景色 Color.FromArgb(128, 128, 128)灰色
            //设置单元格边框，两种方法
            //方法一
            worksheet.Cells[1, i].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(55, 145, 231));//设置单元格所有边框 (191, 191, 191)灰色
        }

        /// <summary>
        /// 设置表体数据
        /// </summary>
        /// <param name="rowIndex"></param>
        /// <param name="worksheet"></param>
        /// <param name="values"></param>
        public static void SetTableBody(int rowIndex, ExcelWorksheet worksheet, params object[] values)
        {
            int cellIndex = 1;
            foreach (var item in values)
            {
                worksheet.Cells[rowIndex, cellIndex].Value = item;
                //设置单元格边框，两种方法
                //方法一 多线程调用时设置单元格所有边框样式会报错？？？
                //worksheet.Cells[rowIndex, cellIndex].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));//设置单元格所有边框
                cellIndex++;
            }
            if (values != null && values.Length > 0)
            {
                values = null;
            }
        }

        /// <summary>
        /// 读取Excel文件
        /// </summary>
        /// <param name="filePath">文件完整路径</param>
        /// <param name="worksheetIndex">读取工作表索引</param>
        /// <param name="firstRowIndex">首行数据索引</param>
        /// <param name="totalColumns">总列数</param>
        /// <returns>DataTable</returns>
        public static DataTable ReadExcelFile(string filePath, int worksheetIndex = 0, int firstRowIndex = 1, int totalColumns = 33)
        {
            DataTable dt = new DataTable();
            var workbook = new Workbook(filePath);
            var cells = workbook.Worksheets[worksheetIndex].Cells;
            //var rows = cells.Rows.Count - 1;//为什么要-1 2016-03-04
            var rows = cells.Rows.Count;

            dt = cells.ExportDataTable(firstRowIndex, 0, rows, totalColumns);

            return dt;
        }

        /// <summary>
        /// 读取EXcel的第一行作为列明
        /// </summary>
        /// <param name="filePath">Excel路径</param>
        /// <param name="worksheetIndex">工作薄索引</param>
        /// <param name="firstRowIndex">首行数据索引</param>
        /// <returns>列集合</returns>
        public static DataTable ReadExcelFileFirstRowIsColumnNames(string filePath, int worksheetIndex = 0, int firstRowIndex = 1)
        {
            DataTable dataTable = null;
            var workbook = new Workbook(filePath);
            var cells = workbook.Worksheets[worksheetIndex].Cells;
            //读取第一行作为列名
            var firstRowTable = cells.ExportDataTable(0, 0, 1, 100);
            if (firstRowTable != null && firstRowTable.Rows.Count > 0)
            {
                var firstRowColumns = new List<DataColumn>();
                for (int i = 0; i < 100; i++)
                {
                    var columnName = firstRowTable.Rows[0][i].ToString();
                    if (!string.IsNullOrEmpty(columnName))
                    {
                        firstRowColumns.Add(new DataColumn(columnName, typeof(string)));
                    }
                    else
                    {
                        break;
                    }
                }
                //填充列名和数据
                dataTable = new DataTable();
                dataTable.Columns.AddRange(firstRowColumns.ToArray());
                var rowCount = cells.Rows.Count-1;
                cells.ExportDataTable(dataTable,firstRowIndex, 0, rowCount, false);
            }
            return dataTable;
        }

        /// <summary>
        /// 获取Excel文件的总行数
        /// </summary>
        /// <param name="filePath">文件完整路径</param>
        /// <param name="worksheetIndex">读取工作表索引</param>
        /// <param name="firstRowIndex">首行数据索引</param>
        /// <returns>获取总行数</returns>
        public static int GetExcelFileRowCount(string filePath, int worksheetIndex = 0, int firstRowIndex = 1)
        {
            var rows = 0;
            var workbook = new Workbook(filePath);
            var cells = workbook.Worksheets[worksheetIndex].Cells;
            //var rows = cells.Rows.Count - 1;//为什么要-1 2016-03-04
            rows = cells.Rows.Count;
            return rows;
        }
    }
}