using System;
using System.IO;
using System.Windows.Forms;
using System.Data;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;

namespace BaseLibRWFile
{

    public class Excel
    {
        ICell cell = null;
        /// <summary>
        /// 把DataGridView控件里面的数据读出，放在DataTable里面
        /// </summary>
        /// <param name="dgv">DataGridView控件</param>
        /// <returns></returns>
        public DataTable GetDgvToTable(DataGridView dgv)
        {
            DataTable dt = new DataTable();
            for (int i = 0; i < dgv.Columns.Count; i++)
            {
                DataColumn dc = new DataColumn(dgv.Columns[i].Name.ToString());
                dt.Columns.Add(dc);
            }
            for (int i = 0; i < dgv.Rows.Count - 1; i++)
            {
                DataRow dr = dt.NewRow();
                for (int countsub = 0; countsub < dgv.Columns.Count; countsub++)
                {
                    dr[countsub] = Convert.ToString(dgv.Rows[i].Cells[countsub].Value);
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }
        /// <summary>
        /// 把Excel的数据读出，放在DataTable里面
        /// </summary>
        /// <param name="filePath">Excel文件完整路径</param>
        /// <param name="sheetname">sheet名称，可不填，默认第一个sheet</param>
        /// <returns></returns>
        public DataTable ExcelToDataTable(string filePath, string sheetname = "")
        {
            if (!File.Exists(filePath))
            {
                return null;
            }
            DataTable dt = new DataTable();
            using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook wk;
                if (filePath.Contains(".xlsx"))
                {
                    wk = new XSSFWorkbook(fs);
                }
                else
                {
                    wk = new HSSFWorkbook(fs);
                }
                if (wk != null)
                {
                    ISheet sheet;
                    if (string.IsNullOrEmpty(sheetname))
                    {
                        sheet = wk.GetSheetAt(0);
                    }
                    else
                    {
                        sheet = wk.GetSheet(sheetname);
                    }
                    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                    IRow headerRow = null;
                    int cellCount = 0;
                    for (int i = 0; i <= sheet.LastRowNum; i++)
                    {
                        headerRow = sheet.GetRow(i);
                        if (headerRow!=null)
                        {
                            if (headerRow.LastCellNum > cellCount)
                            {
                                cellCount = headerRow.LastCellNum;
                            }
                        }
                    }
                    //得到Excel工作表的总列数                     
                    for (int j = 0; j < cellCount; j++)
                    {
                        ICell cell = headerRow.GetCell(j);
                        if (cell == null)
                        {
                            dt.Columns.Add("");
                        }
                        else
                        {
                            dt.Columns.Add();
                        }
                    }
                    for (int i = 0; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dataRow = dt.NewRow();
                        if (row != null)
                        {
                            for (int j = row.FirstCellNum; j < cellCount; j++)
                            {
                                ICell cell = row.GetCell(j);
                                if (cell == null)
                                {
                                    dataRow[j] = "";
                                }
                                else
                                {
                                    switch (cell.CellType)
                                    {
                                        case CellType.Blank:
                                            dataRow[j] = "";
                                            break;
                                        case CellType.Boolean:
                                            dataRow[j] = cell.BooleanCellValue;
                                            break;
                                        case CellType.Numeric:
                                            dataRow[j] = cell.ToString();
                                            break;
                                        case CellType.String:
                                            dataRow[j] = cell.StringCellValue;
                                            break;
                                        case CellType.Error:
                                            dataRow[j] = cell.ErrorCellValue;
                                            break;
                                        case CellType.Formula:
                                            try
                                            {
                                                dataRow[j] = cell.NumericCellValue;
                                            }
                                            catch
                                            {
                                                dataRow[j] = cell.StringCellValue;
                                            }
                                            break;
                                        default:
                                            dataRow[j] = cell.CellFormula;
                                            break;
                                    }
                                }
                            }
                            dt.Rows.Add(dataRow);
                        }
                    }
                }
            }
            return dt;
        }
        /// <summary>
        /// 把Excel的数据读出，放在DataSet里面
        /// </summary>
        /// <param name="filePath">Excel文件完整路径</param>
        /// <returns></returns>
        public DataSet ExcelToDataSet(string filePath)
        {
            DataSet ds = new DataSet();
            try
            {
                using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    IWorkbook wk = null;
                    if (filePath.Contains(".xlsx"))
                    {
                        wk = new XSSFWorkbook(fs);
                    }
                    else
                    {
                        wk = new HSSFWorkbook(fs);
                    }
                    if (wk != null)
                    {
                        ISheet sheet = null;
                        for (int i = 0; i < wk.NumberOfSheets; i++)
                        {
                            string sheetname = wk.GetSheetName(i);
                            sheet = wk.GetSheet(sheetname);
                            if (sheet != null)
                            {
                                DataTable dt = ExcelToDataTable(filePath, sheetname);
                                if (dt != null)
                                {
                                    dt.TableName = sheetname.Trim();
                                    ds.Tables.Add(dt);
                                }
                            }
                        }
                    }
                }
                return ds;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"读取数据到DataSet出错\r\n{ex.Message}", "Error");
                return null;
            }
        }

        /// <summary>
        /// 向Excel文件插入一行以逗号分隔的字符串
        /// </summary>
        /// <param name="filePath">Excel文件完整路径</param>
        /// <param name="sheetname">工作表名称</param>
        /// <param name="RowNo">插入位置（第几行）</param>
        /// <param name="strValue">插入内容</param>
        /// <returns></returns>
        public bool InsertRow(string filePath,  int RowNo, string strValue, string sheetname=null)
        {
            try
            {
                IWorkbook wk = null;
                using (FileStream fs = File.Open(filePath, FileMode.Open,
                 FileAccess.Read, FileShare.ReadWrite))
                {
                    if (filePath.Contains(".xlsx"))
                    {
                        wk = new XSSFWorkbook(fs);
                    }
                    else
                    {
                        wk = new HSSFWorkbook(fs);
                    }
                    fs.Close();
                }
                ISheet sheet =sheetname==null?wk.GetSheetAt(0):wk.GetSheet(sheetname);
                sheet.ShiftRows(RowNo, sheet.LastRowNum + 1, 1);
                string[] s = strValue.Split(',');
                IRow row1 = sheet.CreateRow(RowNo);
                ICell cell = null;
                for (int i = 0; i < s.Length; i++)
                {
                    cell = row1.CreateCell(i);
                    cell.SetCellValue(s[i]);
                }
                using (FileStream fileStream = File.Create(filePath))
                {
                    wk.Write(fileStream);
                    fileStream.Close();
                }
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Excel插入行出错\r\nExcel insert row error\r\n{ex.Message}", "Warning");
                return false;
            }
        }

        /// <summary>
        /// 向Excel文件写入一行以逗号分隔的字符串
        /// </summary>
        /// <param name="filePath">Excel文件完整路径</param>
        /// <param name="sheetname">工作表名称</param>
        /// <param name="RowNo">写入位置（第几行）</param>
        /// <param name="strValue">插入内容</param>
        /// <returns></returns>
        public bool WriteRow(string filePath, int RowNo, string strValue, string sheetname = null)
        {
            try
            {
                IWorkbook wk = null;
                using (FileStream fs = File.Open(filePath, FileMode.Open,
                 FileAccess.Read, FileShare.ReadWrite))
                {
                    if (filePath.Contains(".xlsx"))
                    {
                        wk = new XSSFWorkbook(fs);
                    }
                    else
                    {
                        wk = new HSSFWorkbook(fs);
                    }
                    fs.Close();
                }
                ISheet sheet = sheetname == null ? wk.GetSheetAt(0) : wk.GetSheet(sheetname);
                IRow row1 = sheet.GetRow(RowNo);
                if (row1==null)
                {
                    row1 = sheet.CreateRow(RowNo);
                }
                string[] s = strValue.Split(',');
                ICell cell = null;
                for (int i = 0; i < s.Length; i++)
                {
                    cell = row1.CreateCell(i);
                    cell.SetCellValue(s[i]);
                }
                using (FileStream fileStream = File.Create(filePath))
                {
                    wk.Write(fileStream);
                    fileStream.Close();
                }
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Excel写入行出错\r\nExcel insert row error\r\n{ex.Message}", "Warning");
                return false;
            }
        }
        /// <summary>
        /// 向Excel文件写入一列以逗号分隔的字符串
        /// </summary>
        /// <param name="filePath">Excel文件完整路径</param>
        /// <param name="sheetname">工作表名称</param>
        /// <param name="column">写入位置（列）</param>
        /// <param name="startRow">写入位置（起始行）</param>
        /// <param name="strValue">写入内容</param>
        /// <returns></returns>
        public bool WriteColumn(string filePath, int column,int startRow,string strValue, string sheetname = null)
        {
            try
            {
                IWorkbook wk = null;
                using (FileStream fs = File.Open(filePath, FileMode.Open,
                 FileAccess.Read, FileShare.ReadWrite))
                {
                    if (filePath.Contains(".xlsx"))
                    {
                        wk = new XSSFWorkbook(fs);
                    }
                    else
                    {
                        wk = new HSSFWorkbook(fs);
                    }
                    fs.Close();
                }
                ISheet sheet = sheetname == null ? wk.GetSheetAt(0) : wk.GetSheet(sheetname);
                IRow row = null;
                ICell cell = null;
                string[] str = strValue.Split(',');
                for (int i = 0; i < str.Length; i++)
                {
                    row = sheet.GetRow(startRow+i);
                    if (row == null)
                    {
                        row = sheet.CreateRow(startRow+i);
                    }
                    cell = row.CreateCell(column);
                    cell.SetCellValue(str[i]);
                }               
                using (FileStream fileStream = File.Create(filePath))
                {
                    wk.Write(fileStream);
                    fileStream.Close();
                }
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Excel写入列出错\r\nExcel write column error\r\n{ex.Message}", "Warning");
                return false;
            }
        }
        /// <summary>
        /// 向Excel文件写入一列double数组
        /// </summary>
        /// <param name="filePath">Excel文件完整路径</param>
        /// <param name="sheetname">工作表名称</param>
        /// <param name="column">写入位置（列）</param>
        /// <param name="startRow">写入位置（起始行）</param>
        /// <param name="dValue">写入内容</param>
        /// <returns></returns>
        public bool WriteColumn(string filePath, int column, int startRow, double[] dValue, string sheetname = null)
        {
            try
            {
                IWorkbook wk = null;
                using (FileStream fs = File.Open(filePath, FileMode.Open,
                 FileAccess.Read, FileShare.ReadWrite))
                {
                    if (filePath.Contains(".xlsx"))
                    {
                        wk = new XSSFWorkbook(fs);
                    }
                    else
                    {
                        wk = new HSSFWorkbook(fs);
                    }
                    fs.Close();
                }
                ISheet sheet = sheetname == null ? wk.GetSheetAt(0) : wk.GetSheet(sheetname);
                IRow row = null;
                ICell cell = null;
                for (int i = 0; i < dValue.Length; i++)
                {
                    row = sheet.GetRow(startRow + i);
                    if (row == null)
                    {
                        row = sheet.CreateRow(startRow + i);
                    }
                    cell = row.CreateCell(column);
                    cell.SetCellValue(dValue[i]);
                }
                using (FileStream fileStream = File.Create(filePath))
                {
                    wk.Write(fileStream);
                    fileStream.Close();
                }
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Excel写入列出错\r\nExcel write column error\r\n{ex.Message}", "Warning");
                return false;
            }
        }

        /// <summary>
        /// 向Excel文件指定单元格写入数据
        /// </summary>
        /// <param name="filePath">Excel文件完整路径</param>
        /// <param name="sheetname">工作表名称</param>
        /// <param name="row">行号</param>
        /// <param name="column">列号</param>
        /// <param name="strValue">插入内容</param>
        /// <returns></returns>
        public bool SetCellValue(string filePath, string sheetname, int row, int column, string value)
        {
            try
            {
                IWorkbook wk = null;
                using (FileStream fs = File.Open(filePath, FileMode.Open,
                FileAccess.Read, FileShare.ReadWrite))
                {
                    if (filePath.Contains(".xlsx"))
                    {
                        wk = new XSSFWorkbook(fs);
                    }
                    else
                    {
                        wk = new HSSFWorkbook(fs);
                    }
                    fs.Close();
                }
                ISheet sheet = wk.GetSheet(sheetname);

                IRow row1 = sheet.CreateRow(row);
                ICell cell = row1.CreateCell(column);

                //cell = sheet.GetRow(row).GetCell(column);
                //sheet.ForceFormulaRecalculation = true;  //强制计算
                //sheet.ProtectSheet("123");//密码保护
                if (value.Contains(","))
                {
                    string[] s = value.Split(',');
                    for (int i = 0; i < s.Length; i++)
                    {
                        cell = row1.CreateCell(i);
                        cell.SetCellValue(s[i]);
                    }
                }
                else
                {
                    cell.SetCellValue(value);
                }
                using (FileStream fileStream = File.Create(filePath))
                {
                    wk.Write(fileStream);
                    fileStream.Close();
                }
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Excel向指定单元格写入数据失败\r\nFails to write data to specified cell\r\n{ex.Message}", "Warning");
                return false;
            }
        }
        public bool SetExcelCellValue(string filePath, String sheetname, int row, int column, String value)
        {
            bool returnb;
            try
            {
                IWorkbook wk = null;
                using (FileStream fs = File.Open(filePath, FileMode.Open,
                FileAccess.Read, FileShare.ReadWrite))
                {
                    if (filePath.Contains(".xlsx"))
                    {

                        wk = new XSSFWorkbook(fs);
                    }
                    else
                    {
                        //把xls文件读入workbook变量里，之后就可以关闭了  
                        wk = new HSSFWorkbook(fs);
                    }
                    fs.Close();
                }
                //把xls文件读入workbook变量里，之后就可以关闭了  
                ISheet sheet = wk.GetSheet(sheetname);
                GetCellValue(cell);
                cell = sheet.GetRow(row).GetCell(column);
                cell.SetCellValue(value);
                using (FileStream fileStream = File.Create(filePath))
                {
                    wk.Write(fileStream);
                    fileStream.Close();
                }
                returnb = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                returnb = false;
                //  throw;
            }
            return returnb;
        }
        //获取cell的数据，并设置为对应的数据类型
        public object GetCellValue(ICell cell)
        {
            object value = null;
            try
            {
                if (cell.CellType != CellType.Blank)
                {
                    switch (cell.CellType)
                    {
                        case CellType.Numeric:
                            // Date comes here
                            if (DateUtil.IsCellDateFormatted(cell))
                            {
                                value = cell.DateCellValue;
                            }
                            else
                            {
                                // Numeric type
                                value = cell.NumericCellValue;
                            }
                            break;
                        case CellType.Boolean:
                            // Boolean type
                            value = cell.BooleanCellValue;
                            break;
                        case CellType.Formula:
                            value = cell.CellFormula;
                            break;
                        default:
                            // String type
                            value = cell.StringCellValue;
                            break;
                    }
                }
            }
            catch (Exception)
            {
                value = "";
            }
            return value;
        }
        /// <summary>
        /// 设置sheet密码
        /// </summary>
        /// <param name="filePath">Excel文件完整路径</param>
        /// <param name="password">要设置的密码</param>
        /// <param name="sheetname">工作表名称（可为空）</param>
        /// <returns></returns>
        public void SetSheetPassword(string filePath, string password, string sheetname=null)
        {
            IWorkbook wk = null;
            using (FileStream fs = File.Open(filePath, FileMode.Open,
            FileAccess.Read, FileShare.ReadWrite))
            {
                if (filePath.Contains(".xlsx"))
                {
                    wk = new XSSFWorkbook(fs);
                }
                else
                {
                    wk = new HSSFWorkbook(fs);
                }
                fs.Close();
            }
            ISheet sheet = null;
            if (string.IsNullOrEmpty(sheetname))
            {
                sheet = wk.GetSheetAt(0);
            }
            else
            {
                sheet = wk.GetSheet(sheetname);
            }
            //sheet.SetColumnWidth(0, 15 * 256);//设置列宽
            sheet.ProtectSheet(password);
            using (FileStream fileStream = File.Create(filePath))
            {
                wk.Write(fileStream);
                fileStream.Close();
            }
        }
        /// <summary>
        /// 读Excel文件指定单元格数据
        /// </summary>
        /// <param name="filePath">Excel文件完整路径</param>
        /// <param name="sheetname">工作表名称</param>
        /// <param name="row">行号</param>
        /// <param name="column">列号</param>
        /// <returns></returns>
        public string GetCellValue(string filePath, string sheetname, int row, int column)
        {
            try
            {
                IWorkbook wk = null;
                using (FileStream fs = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    if (filePath.Contains(".xlsx"))
                    {
                        wk = new XSSFWorkbook(fs);
                    }
                    else
                    {
                        wk = new HSSFWorkbook(fs);
                    }
                    fs.Close();
                }
                ISheet sheet = wk.GetSheet(sheetname);
                ICell cell = sheet.GetRow(row).GetCell(column);
                return cell.ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show($"读Excel文件指定单元格数据失败\r\nReading specified cell data failed\r\n{ex.Message}", "Warning");
                return null;
            }
        }
        /// <summary>
        /// 创建Excel文件
        /// </summary>
        /// <param name="filePath">Excel文件完整路径</param>
        /// <param name="SheetName">工作表名称</param>
        /// <returns></returns>
        public bool CreatExcel(string filePath, string[] SheetName = null)
        {
            try
            {
                IWorkbook wk = null;
                if (filePath.Contains(".xlsx"))
                {
                    wk = new XSSFWorkbook();
                }
                else
                {
                    wk = new HSSFWorkbook();
                }
                SheetName = SheetName ?? (new string[] { "sheet1" });
                for (int i = 0; i < SheetName.Length; i++)
                {
                    wk.CreateSheet(SheetName[i]);
                }
                FileStream fs = new FileStream(filePath, FileMode.Create);
                wk.Write(fs);
                fs.Close();
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"创建Excel失败\r\nExcel creation failed\r\n{ex.Message}", "Error");
                return false;
            }
        }
        /// <summary>
        /// 把DataTable数据写入Excel文件
        /// </summary>
        /// <param name="filePath">Excel文件完整路径</param>
        /// <param name="dt">DataTable数据</param>
        /// <returns></returns>
        public bool DataTableToExcel(string filePath, DataTable dt)
        {
            try
            {
                IWorkbook wk = null;
                if (File.Exists(filePath))
                {
                    File.Delete(filePath);
                }
                if (!File.Exists(filePath)) //判断文件是否存在,不存在时则创建并添加表头
                {
                    if (filePath.Contains(".xlsx"))
                    {
                        wk = new XSSFWorkbook();
                    }
                    else
                    {
                        wk = new HSSFWorkbook();
                    }
                    using (FileStream fs1 = new FileStream(filePath, FileMode.OpenOrCreate))//读取流
                    {
                        //创建sheet
                        ISheet sheet = wk.CreateSheet("Sheet1");
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            IRow row = sheet.CreateRow(i);
                            for (int j = 0; j < dt.Columns.Count; j++)
                            {
                                ICell cell = row.CreateCell(j);
                                cell.SetCellValue(dt.Rows[i][j].ToString());
                            }
                        }
                        wk.Write(fs1);
                        fs1.Close();
                    }
                }
                else
                {   //如果文件已存在则首先打开，并且获取到最大的行数
                    using (FileStream fs = new FileStream(filePath, FileMode.Open))//读取流
                    {
                        if (filePath.Contains(".xlsx"))
                        {
                            wk = new XSSFWorkbook(fs);
                        }
                        else
                        {
                            wk = new HSSFWorkbook(fs);
                        }
                        ISheet sheet1 = wk.GetSheetAt(0);
                        //int num = sheet1.LastRowNum + 1;//获取最大行数

                        FileStream fout = new FileStream(filePath, FileMode.Open, FileAccess.Write, FileShare.ReadWrite);//写入流
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            //创建行数时+num
                            IRow row = sheet1.CreateRow(i);
                            for (int j = 0; j < dt.Columns.Count; j++)
                            {
                                ICell cell = row.CreateCell(j);
                                cell.SetCellValue(dt.Rows[i][j].ToString());
                            }
                        }
                        wk.Write(fout);
                        fs.Close();
                        fout.Close();
                    }
                }
                return true;
            }
            catch
            {
                //MessageBox.Show($"创建Excel失败\r\nExcel creation failed\r\n{ex.Message}", "Error");
                return false;
            }
        }
        /// <summary>
        /// 读Excel文件指定Sheet数据，返回二维数组字符串
        /// </summary>
        /// <param name="filePath">Excel文件完整路径</param>
        /// <param name="SheetName">工作表名称</param>
        /// <param name="iRow">行号</param>
        /// <param name="iColumn">列号</param>
        /// <returns></returns>
        public string[,] ReadSheetData(string filePath, string SheetName = "", int iRow = -1, int iColumn = -1)
        {
            try
            {
                if (!File.Exists(filePath))
                {
                    MessageBox.Show($"{filePath} 文件不存在！\r\nThe specified excel file could not be found or is corrupted!");
                    return null;
                }
                IWorkbook wk = null;
                using (FileStream fs = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    if (filePath.Contains(".xlsx"))
                    {
                        wk = new XSSFWorkbook(fs);
                    }
                    else
                    {
                        wk = new HSSFWorkbook(fs);
                    }
                    fs.Close();
                }
                ISheet sheet = string.IsNullOrEmpty(SheetName) ? wk.GetSheetAt(0) : wk.GetSheet(SheetName);
                iRow = iRow > 0 ? iRow : sheet.LastRowNum -sheet.FirstRowNum+ 1;
                iColumn = iColumn > 0 ? iColumn : sheet.GetRow(sheet.FirstRowNum).LastCellNum + 1;
                string[,] ret = new string[iRow, iColumn];

                for (int i = sheet.FirstRowNum; i < iRow; i++)
                {
                    for (int j = 0; j < iColumn; j++)
                    {
                        try
                        {
                            ret[i- sheet.FirstRowNum, j] = sheet.GetRow(i).GetCell(j).ToString();
                        }
                        catch
                        {
                            ret[i- sheet.FirstRowNum, j] = "";
                        }
                    }
                }
                return ret;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"读取Sheet数据失败\r\nFailed to read sheet data\r\n{ex.Message}", "Error");
                return null;
            }
        }
        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="filePath">Excel文件完整路径</param>
        /// <param name="sheetname">工作表名称</param>
        /// <param name="startRow">开始行</param>
        /// <param name="endRow">结束行</param>
        /// <param name="startColumn">开始列</param>
        /// <param name="endColumn">结束列</param>
        /// <param name="value">合并后写入的数据</param>
        /// <param name="fontsize">字体大小</param>
        /// <returns></returns>
        public bool MergeCells(string filePath, int[] startRow, int[] endRow, int[] startColumn, int[] endColumn,
            string[] value=null,double[] fontsize=null, string sheetname = null)
        {
            try
            {
                IWorkbook wk = null;
                using (FileStream fs = File.Open(filePath, FileMode.Open,FileAccess.Read, FileShare.ReadWrite))
                {
                    if (filePath.Contains(".xlsx"))
                    {
                        wk = new XSSFWorkbook(fs);
                    }
                    else
                    {
                        wk = new HSSFWorkbook(fs);
                    }
                    fs.Close();
                }
                ISheet sheet = string.IsNullOrEmpty(sheetname) ? wk.GetSheetAt(0) : wk.GetSheet(sheetname);
                for (int i = 0; i < startRow.Length; i++)
                {
                                    
                    CellRangeAddress region = new CellRangeAddress(startRow[i], endRow[i], startColumn[i], endColumn[i]);
                    sheet.AddMergedRegion(region);
                    ICellStyle style = wk.CreateCellStyle();
                    style.VerticalAlignment = VerticalAlignment.Center;
                    style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    if (fontsize != null)
                    {
                        IFont font = wk.CreateFont();
                        font.FontHeight = fontsize[i];
                        style.SetFont(font);
                    }
                    //style.FillBackgroundColor = HSSFColor.Red.Index;
                    IRow row = sheet.GetRow(startRow[i]);                    
                    ICell cell = null;
                    if (row == null)
                    {
                        row = sheet.CreateRow(startRow[i]);                                              
                    }
                    if (row.GetCell(startColumn[i]) == null)
                    {
                        cell = row.CreateCell(startColumn[i]);
                    }                    
                    if (value != null)
                    {
                        cell.SetCellValue(value[i]);
                    }
                    cell.CellStyle = style;
                }
                
                using (FileStream fileStream = File.Create(filePath))
                {
                    wk.Write(fileStream);
                    fileStream.Close();
                }
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"合并单元格失败\r\nFailed to merge cells\r\n{ex.Message}", "Error");
                return false;
            }
        }

        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="filePath">Excel文件完整路径</param>
        /// <param name="sheetname">工作表名称</param>
        /// <param name="startRow">开始行</param>
        /// <param name="endRow">结束行</param>
        /// <param name="startColumn">开始列</param>
        /// <param name="endColumn">结束列</param>
        /// <param name="value">合并后写入的数据</param>
        /// <param name="fontsize">字体大小</param>
        /// <returns></returns>
        public bool MergeCells(string filePath, int startRow, int endRow, int startColumn, int endColumn,
            string value = null, double fontsize = -1, string sheetname = null)
        {
            try
            {
                IWorkbook wk = null;
                using (FileStream fs = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    if (filePath.Contains(".xlsx"))
                    {
                        wk = new XSSFWorkbook(fs);
                    }
                    else
                    {
                        wk = new HSSFWorkbook(fs);
                    }
                    fs.Close();
                }
                ISheet sheet = string.IsNullOrEmpty(sheetname) ? wk.GetSheetAt(0) : wk.GetSheet(sheetname);
                ICell cell = sheet.CreateRow(startRow).CreateCell(startColumn);
                if (value != null)
                {
                    cell.SetCellValue(value);
                }
                CellRangeAddress region = new CellRangeAddress(startRow, endRow, startColumn, endColumn);
                sheet.AddMergedRegion(region);
                ICellStyle style = wk.CreateCellStyle();
                style.VerticalAlignment = VerticalAlignment.Center;
                style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                if (fontsize >0)
                {
                    IFont font = wk.CreateFont();
                    font.FontHeight = fontsize;
                    style.SetFont(font);
                }
                //style.FillBackgroundColor = HSSFColor.Red.Index;
                cell.CellStyle = style;
                using (FileStream fileStream = File.Create(filePath))
                {
                    wk.Write(fileStream);
                    fileStream.Close();
                }
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"合并单元格失败\r\nFailed to merge cells\r\n{ex.Message}", "Error");
                return false;
            }
        }
    }
}