c# ExcelHelper

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
/*****************************
 * 概要：Excel
 * 设计者：DuanXuWen
 * 设计时间：20180309
 * 版本：0.1
 * 修改者：
 * 修改时间：
 * ***************************/

namespace Common
{
    public class ExcelHelper
    {
        /// <summary>
        /// 无参构造
        /// </summary>
        public ExcelHelper(){ }

        /// <summary>
        /// 打开Excel(【弹窗】选择文件)
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet GetExcelData()
        {
            try
            {
                OpenFileDialog file = new OpenFileDialog();
                file.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
                file.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
                file.Multiselect = false;
                if (file.ShowDialog() == DialogResult.Cancel)
                {
                    return null;
                }
                return GetExcelData(file.FileName, null);
            }
            catch (Exception ex)
            {
                string msg = "打开Excel文件相关错误！";
                string logAddress = System.Windows.Forms.Application.StartupPath + "\\Log\\ErrorMsg\\" + DateTime.Now.ToString("yyyyMMdd") + ".log";
                LogHelper.WriteLog(ex, logAddress, false, msg);
                return null;
            }
        }

        /// <summary>
        /// 导出Excel(【弹窗】选择保存路径)
        /// </summary>
        /// <param name="table">DataTable数据</param>
        /// <param name="title">标题</param>
        /// <returns></returns>
        public bool ExportExcel(DataTable table, string fillName, string title)
        {
            try
            {
                System.Windows.Forms.FolderBrowserDialog dialog = new System.Windows.Forms.FolderBrowserDialog();
                dialog.Description = "请选择保存路径";
                if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    string path = dialog.SelectedPath + "\\" + fillName;
                    return ExportExcels(table, path, title);
                }
                return false;
            }
            catch (Exception ex)
            {
                string msg = "导出Excel文件选择路径相关错误！";
                string logAddress = System.Windows.Forms.Application.StartupPath + "\\Log\\ErrorMsg\\" + DateTime.Now.ToString("yyyyMMdd") + ".log";
                LogHelper.WriteLog(ex, logAddress, false, msg);
                return false;
            }
        }

        /// <summary>
        /// 获取本机安装的office版本
        /// </summary>
        /// <returns></returns>
        public OfficeVersion GetOfficeVersion()
        {
            OfficeVersion version = OfficeVersion.Office2007;
            Microsoft.Win32.RegistryKey regKey = null;
            try
            {
                regKey = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\Excel.exe");
                if (regKey != null)
                {
                    string path1 = regKey.GetValue("Path").ToString();
                    string path = path1.Substring(0, path1.Length - 1);
                    string versionName = path.Substring(path.LastIndexOf("\\") + 1).ToUpper();
                    switch (versionName)
                    {
                        case "OFFICE11":
                            version = OfficeVersion.Office2003;
                            break;
                        case "OFFICE12":
                            version = OfficeVersion.Office2007;
                            break;
                        case "OFFICE16":
                            version = OfficeVersion.Office2016;
                            break;
                        default:
                            break;
                    }
                    regKey.Close();
                }
                return version;
            }
            catch (System.Security.SecurityException ex)
            {
                throw new System.Security.SecurityException("您没有读取注册表的权限", ex);
            }
            catch (Exception ex)
            {
                string msg = "获取本机安装的office版本相关错误！";
                string logAddress = System.Windows.Forms.Application.StartupPath + "\\Log\\ErrorMsg\\" + DateTime.Now.ToString("yyyyMMdd") + ".log";
                LogHelper.WriteLog(ex, logAddress, false, msg);
                throw ex;
            }
        }

        /// <summary>
        /// 获取文件内容
        /// </summary>
        /// <param name="path">文件地址</param>
        /// <param name="Sheet">页：Sheet1，Sheet2等 注：可为null,默认Sheet1</param>
        /// <returns>DataSet</returns>
        public DataSet GetExcelData(string path, string sheet)
        {
            try
            {
                OfficeVersion version = GetOfficeVersion();
                string connString = GetConnString(version, path);
                //默认页Sheet1
                string sheetValue = "Sheet1";
                if (sheet != null)
                {
                    sheetValue = sheet.ToString();
                }
                //读取文件
                string sql_select = string.Format("SELECT * FROM [{0}$]", sheetValue);
                return GetExcelDatas(connString, sql_select);
            }
            catch (Exception ex)
            {
                string msg = "获取Excel文件内容相关错误！详细：文件地址：" + path + "Sheet页：" + sheet;
                string logAddress = System.Windows.Forms.Application.StartupPath + "\\Log\\ErrorMsg\\" + DateTime.Now.ToString("yyyyMMdd") + ".log";
                LogHelper.WriteLog(ex, logAddress, false, msg);
                return null;
            }
        }

        /// <summary>
        /// 获取版本连接
        /// </summary>
        /// <param name="version">Excel版本</param>
        /// <param name="path">Excel文件地址</param>
        /// <returns>string</returns>
        public string GetConnString(OfficeVersion version, string path)
        {
            //获取文件后缀
            string fileSuffix = System.IO.Path.GetExtension(path);
            //判断文件是否为Excel【后缀是否包含(.xls,.xlsx)】
            if (!string.IsNullOrEmpty(fileSuffix) && fileSuffix.Contains(".xls"))
            {
                string connString = string.Empty;
                //连接串赋值
                if (fileSuffix == ".xls" && version == OfficeVersion.Office2003)
                {
                    connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
                }
                if (fileSuffix == ".xlsx" && version == OfficeVersion.Office2016)
                {
                    connString = "Provider=Microsoft.Ace.OleDb.16.0;" + "data source=" + path + ";Extended Properties='Excel 16.0; HDR=YES; IMEX=1'";
                }
                if (fileSuffix == ".xlsx" && version == OfficeVersion.Office2007)
                {
                    connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + path + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
                }
                return connString;
            }
            else
            {
                return "" + System.IO.Path.GetFileName(path) + "此文件不符合要求(.xls或.xlsx)";
            }
        }

        /// <summary>
        /// 获取文件内容
        /// </summary>
        /// <param name="connString">版本链接</param>
        /// <param name="sqlSelect">查询语句</param>
        /// <returns>DataSet</returns>
        public DataSet GetExcelDatas(string connString, string sqlSelect)
        {
            try
            {
                using (DataSet set = new DataSet())
                {
                    using (OleDbConnection conn = new OleDbConnection(connString))
                    using (OleDbDataAdapter cmd = new OleDbDataAdapter(sqlSelect, conn))
                    {
                        conn.Open();
                        cmd.Fill(set);
                        conn.Close();
                    }
                    return set;
                }
            }
            catch (Exception ex)
            {
                string msg = "获取Excel文件内容相关错误！详细：文件版本链接：" + connString + "查询语句：" + sqlSelect;
                string logAddress = System.Windows.Forms.Application.StartupPath + "\\Log\\ErrorMsg\\" + DateTime.Now.ToString("yyyyMMdd") + ".log";
                LogHelper.WriteLog(ex, logAddress, false, msg);
                return null;
            }
        }

        /// <summary>
        /// 获取Excel文件Sheet页集[TABLE_NAME]
        /// </summary>
        /// <param name="connString">文件版本链接</param>
        /// <returns>DataTable</returns>
        public DataTable GetSheetTable(string connString)
        {
            try
            {
                DataTable table = new DataTable();
                using (OleDbConnection cn = new OleDbConnection(connString))
                {
                    cn.Open();
                    table = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    cn.Close();
                }
                return table;
            }
            catch (Exception ex)
            {
                string msg = "获取Excel文件Sheet页集相关错误！详细：文件版本链接：" + connString;
                string logAddress = System.Windows.Forms.Application.StartupPath + "\\Log\\ErrorMsg\\" + DateTime.Now.ToString("yyyyMMdd") + ".log";
                LogHelper.WriteLog(ex, logAddress, false, msg);
                return null;
            }
        }

        /// <summary>
        ///  获取Excel文件Sheet页集合
        /// </summary>
        /// <param name="connString">文件版本链接</param>
        /// <returns>List<string>集合</returns>
        public static List<string> GetExcelSheetList(string connString)
        {
            try
            {
                DataTable table = new DataTable();
                List<string> list = new List<string>();
                using (OleDbConnection cn = new OleDbConnection(connString))
                {
                    cn.Open();
                    table = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    cn.Close();
                }
                if (table != null && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        list.Add(row["TABLE_NAME"].ToString());
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                string msg = "获取Excel文件Sheet页集相关错误！详细：文件版本链接：" + connString;
                string logAddress = System.Windows.Forms.Application.StartupPath + "\\Log\\ErrorMsg\\" + DateTime.Now.ToString("yyyyMMdd") + ".log";
                LogHelper.WriteLog(ex, logAddress, false, msg);
                return null;
            }
        }

        /// <summary>
        /// 导出Excel文件
        /// </summary>
        /// <param name="table">DataTable数据</param>
        /// <param name="excelFilePathName">文件路径+名称+后缀</param>
        /// <param name="title">文件标题</param>
        /// <returns></returns>
        public bool ExportExcels(DataTable table, string excelFilePathName, string title)
        {
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("Sheet1");
                ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                //背景色
                HeadercellStyle.FillPattern = FillPattern.SolidForeground;
                HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
                //字体
                NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
                headerfont.Boldweight = (short)FontBoldWeight.Bold;
                HeadercellStyle.SetFont(headerfont);
                //列名
                int icolIndex = 0;
                int firstIndex = 0;
                if (title != "")
                {
                    //插入标题
                    firstIndex = 1;
                    IRow firstRow = sheet.CreateRow(0);
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        ICell firstCell = firstRow.CreateCell(i);
                        if (i == 0)
                        {
                            firstCell.SetCellValue(title);
                            ICellStyle FirstCellStyle = workbook.CreateCellStyle();
                            FirstCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                            FirstCellStyle.SetFont(headerfont);
                            firstCell.CellStyle = FirstCellStyle;
                        }
                    }
                    //合并标题行
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, table.Columns.Count - 1);
                    sheet.AddMergedRegion(cellRangeAddress);
                }
                //列表头
                IRow headerRow = sheet.CreateRow(firstIndex);
                foreach (DataColumn item in table.Columns)
                {
                    ICell cell = headerRow.CreateCell(icolIndex);
                    cell.SetCellValue(item.ColumnName);
                    cell.CellStyle = HeadercellStyle;
                    icolIndex++;
                }
                ICellStyle cellStyle = workbook.CreateCellStyle();
                //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
                cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                cellfont.Boldweight = (short)FontBoldWeight.Normal;
                cellStyle.SetFont(cellfont);
                //写入内容
                int iRowIndex = 1 + firstIndex;
                int iCellIndex = 0;
                foreach (DataRow Rowitem in table.Rows)
                {
                    IRow DataRow = sheet.CreateRow(iRowIndex);
                    foreach (DataColumn Colitem in table.Columns)
                    {
                        ICell cell = DataRow.CreateCell(iCellIndex);
                        cell.SetCellValue(Rowitem[Colitem].ToString());
                        cell.CellStyle = cellStyle;
                        iCellIndex++;
                    }
                    iCellIndex = 0;
                    iRowIndex++;
                }
                //自适应列宽度
                for (int i = 0; i < icolIndex; i++)
                {
                    sheet.AutoSizeColumn(i);
                }
                //写Excel
                using (FileStream file = new FileStream(excelFilePathName, FileMode.OpenOrCreate))
                {
                    workbook.Write(file);
                    file.Flush();
                    file.Close();
                    file.Dispose();
                }
            }
            catch (Exception ex)
            {
                string msg = "导出Excel文件相关错误！详细：DataTable数据：" + table + "文件路径：" + excelFilePathName + "标题：" + title;
                string logAddress = System.Windows.Forms.Application.StartupPath + "\\Log\\ErrorMsg\\" + DateTime.Now.ToString("yyyyMMdd") + ".log";
                LogHelper.WriteLog(ex, logAddress, false, msg);
                return false;
            }
            return true;
        }
    }

    /// <summary>
    /// office版本
    /// </summary>
    public enum OfficeVersion
    {
        /// <summary>
        /// office03
        /// </summary>
        Office2003 = 0,

        /// <summary>
        /// Office07
        /// </summary>
        Office2007 = 1,

        /// <summary>
        /// Office16
        /// </summary>
        Office2016 = 2
    }
}