npoi操作excel 读取 插入 写入等

    public class NPOIExcel
    {
        public const string pattern = @"^\d+(\.\d+)?$";//判断是否是数字
        public static void CreateFile(string filePath, string sheetName)
        {
            if (!File.Exists(filePath))
            {
                //文件不存在 创建新文件写入
                if (filePath.Contains(".xlsx") || filePath.Contains(".ett") || filePath.Contains(".xlsm"))
                {
                    XSSFWorkbook workbook = new XSSFWorkbook();
                    workbook.CreateSheet(sheetName);
                    FileStream fs = new FileStream(filePath, FileMode.Create);
                    workbook.Write(fs);
                    fs.Close();
                }
                else if (filePath.Contains(".xls") || filePath.Contains(".et"))
                {
                    HSSFWorkbook workbook = new HSSFWorkbook();
                    workbook.CreateSheet(sheetName);
                    FileStream fs = new FileStream(filePath, FileMode.Create);
                    workbook.Write(fs);
                    fs.Close();
                }
            }
        }

        /// <summary>
        /// 把DataTable的数据写入到指定的excel文件中
        /// </summary>
        /// <param name="TargetFileNamePath">目标文件excel的路径</param>
        /// <param name="sourceData">要写入的数据</param>
        /// <param name="sheetName">excel表中的sheet的名称,可以根据情况自己起</param>
        /// <param name="IsWriteColumnName">是否写入DataTable的列名称</param>
        /// <returns>返回写入的行数</returns>
        public static int DataTableToExcel(string TargetFileNamePath, System.Data.DataTable sourceData, string TemplateSheetName, string sheetName, bool IsWriteColumnName, bool Overwrite, string startCell, Dictionary<string, object> ConvertColumnTypes = null)
        {
            bool status = false;
            //数据验证
            if (!File.Exists(TargetFileNamePath))
            {
                //excel文件的路径不存在
                throw new ArgumentException(ConfigStringHelper.ExcelError_NotArgumentExceptionFilePath);
            }
            FileStream fs = new FileStream(TargetFileNamePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);//读取流

            ////根据Excel文件的后缀名创建对应的workbook
            IWorkbook workbook = null;
            if (TargetFileNamePath.IndexOf(".xlsx") > 0 || TargetFileNamePath.IndexOf(".ett") > 0 || TargetFileNamePath.IndexOf(".xlsm") > 0)
            {  //2007版本的excel
                workbook = new XSSFWorkbook(fs);
            }
            else if (TargetFileNamePath.IndexOf(".xls") > 0 || TargetFileNamePath.IndexOf(".et") > 0) //2003版本的excel
            {
                workbook = new HSSFWorkbook(fs);
            }
            else
            {
                return -1;    //都不匹配或者传入的文件根本就不是excel文件,直接返回
            }
            ISheet sheet = workbook.GetSheetAt(0);//获取工作表
            int SheetCount = workbook.NumberOfSheets;//获取表的数量
            string[] SheetName = new string[SheetCount];//保存表的名称
            //excel表的sheet名
            //ISheet sheet = null;
            if (!string.IsNullOrEmpty(TemplateSheetName))
            {
                int idex = workbook.GetSheetIndex(TemplateSheetName);
                if (string.IsNullOrEmpty(sheetName))
                {
                    throw new ArgumentException(ConfigStringHelper.ExcelError_NotSheetTemplate);
                }
                //修改sheet名字
                workbook.SetSheetName(idex, sheetName);
            }
            for (int i = 0; i < SheetCount; i++)
            {
                SheetName[i] = workbook.GetSheetName(i).ToLower();
                //获取sheet的索引

            }
            if (SheetName.Contains(sheetName.ToLower()) && Overwrite == false)
            {
                sheet = workbook.GetSheet(sheetName);
                status = false;
            }
            else if (SheetName.Contains(sheetName.ToLower()) && Overwrite == true)
            {
                workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName));
                sheet = workbook.CreateSheet(sheetName);
                status = true;
            }
            if (!SheetName.Contains(sheetName.ToLower()))
            {
                sheet = workbook.CreateSheet(sheetName);
            }
            if (sheet == null) return -1;   //无法创建sheet,则直接返回
            //写入Excel的行数
            int WriteRowCount = 0;

            int colunmIndex = 0;

            if (!string.IsNullOrEmpty(startCell))
            {
                CellRangeAddress cellRangeAddress = CellRangeAddress.ValueOf(startCell);
                WriteRowCount = cellRangeAddress.FirstRow;
                colunmIndex = cellRangeAddress.FirstColumn;

            }
            //这里获取源数据的总行和列
            var SourceRows = sourceData.Rows.Count;//行
            var SourceColumns = sourceData.Columns.Count;//列
            if (IsWriteColumnName == false)
            {
                IRow ColumnNameRow = null;
                //sheet表创建新的一行,即第一行
                if (status == true && string.IsNullOrEmpty(startCell))
                {
                    //表示覆盖
                    ColumnNameRow = sheet.CreateRow(0);
                }
                else if (status == false && string.IsNullOrEmpty(startCell))
                {
                    //表示不覆盖,此处需要判断范围内是否存在数据
                    for (var i = WriteRowCount; i < SourceRows + WriteRowCount; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null)
                        {
                            row = sheet.CreateRow(i);
                        }
                        for (var j = colunmIndex; j < SourceColumns + colunmIndex; j++)
                        {
                            ICell cell = row.GetCell(j);
                            if (cell == null)
                            {
                                cell = row.CreateCell(j);
                            }
                            else
                            {
                                throw new ArgumentException(ConfigStringHelper.ExcelError_WriteFailure);
                            }
                        }
                    }
                    ColumnNameRow = sheet.CreateRow(0);
                }
                if (!string.IsNullOrEmpty(startCell) && status == false)
                {
                    //表示不覆盖,从指定的位置处写入,需要判断指定位置是否有数据
                    for (var i = WriteRowCount; i < SourceRows + WriteRowCount; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null)
                        {
                            row = sheet.CreateRow(i);
                        }
                        for (var j = colunmIndex; j < SourceColumns + colunmIndex; j++)
                        {
                            ICell cell = row.GetCell(j);
                            if (cell == null)
                            {
                                cell = row.CreateCell(j);
                            }
                            else
                            {
                                throw new ArgumentException(ConfigStringHelper.ExcelError_WriteFailure);
                            }
                            var firsLine = row.GetCell(j).ToString();
                        }
                    }
                    ColumnNameRow = sheet.CreateRow(WriteRowCount);
                }
                else if (!string.IsNullOrEmpty(startCell) && status == true)
                {
                    //表示覆盖,从指定的位置处写入
                    ColumnNameRow = sheet.CreateRow(WriteRowCount);
                }
                //IRow ColumnNameRow = sheet.CreateRow(0); //0下标代表第一行
                int ColumnCount = colunmIndex;//开始列
                //进行写入DataTable的列名
                for (int colunmNameIndex = 0; colunmNameIndex < sourceData.Columns.Count; colunmNameIndex++)
                {

                    ColumnNameRow.CreateCell(ColumnCount).SetCellValue(sourceData.Columns[colunmNameIndex].ColumnName);
                    ColumnCount++;
                }
                WriteRowCount++;
            }
            bool endStatus = false;
            int count = 0;
            count = (WriteRowCount) + (sheet.LastRowNum);
            //写入数据
            for (int row = 0; row < sourceData.Rows.Count; row++)
            {
                IRow newRow = null;
                //sheet表创建新的一行
                if (status == false && string.IsNullOrEmpty(startCell))
                {
                    if (endStatus == false)
                    {
                        if (IsWriteColumnName == false)
                        {
                        }
                        else
                        {
                            for (var i = WriteRowCount + 1; i < SourceRows + WriteRowCount + 1; i++)
                            {
                                IRow row_a = sheet.GetRow(i);
                                if (row_a == null)
                                {
                                    row_a = sheet.CreateRow(i);
                                }
                                for (var j = colunmIndex; j < SourceColumns + colunmIndex; j++)
                                {
                                    ICell cell = row_a.GetCell(j);
                                    if (cell == null)
                                    {
                                        cell = row_a.CreateCell(j);
                                    }
                                    else
                                    {
                                        throw new ArgumentException(ConfigStringHelper.ExcelError_WriteFailure);
                                    }
                                    var firsLine = row_a.GetCell(j).ToString();
                                }
                            }
                        }
                    }
                    newRow = sheet.CreateRow(WriteRowCount);
                }
                else if (status == true && string.IsNullOrEmpty(startCell))
                {
                    //表示覆盖
                    newRow = sheet.CreateRow(WriteRowCount);
                }
                if (!string.IsNullOrEmpty(startCell) && status == true)
                {
                    //表示覆盖,从指定的位置处写入
                    newRow = sheet.CreateRow(WriteRowCount);
                }
                else if (!string.IsNullOrEmpty(startCell) && status == false)
                {

                    if (endStatus == false)
                    {
                        //表示不覆盖,从指定的位置处写入,需要判断指定位置是否有数据
                        if (IsWriteColumnName == false)
                        {
                        }
                        else
                        {
                            for (var i = WriteRowCount + 1; i < SourceRows + WriteRowCount + 1; i++)
                            {
                                IRow row_a = sheet.GetRow(i);
                                if (row_a == null)
                                {
                                    row_a = sheet.CreateRow(i);
                                }
                                for (var j = colunmIndex; j < SourceColumns + colunmIndex; j++)
                                {
                                    ICell cell = row_a.GetCell(j);
                                    if (cell == null)
                                    {
                                        cell = row_a.CreateCell(j);
                                    }
                                    else
                                    {
                                        throw new ArgumentException(ConfigStringHelper.ExcelError_WriteFailure);
                                    }
                                    var firsLine = row_a.GetCell(j).ToString();
                                }
                            }
                        }

                    }
                    newRow = sheet.CreateRow(WriteRowCount);
                }
                int Count = colunmIndex;//开始列
                for (int column = 0; column < sourceData.Columns.Count; column++)
                {
                    string columnName = sourceData.Columns[Convert.ToInt32(column)].ColumnName;
                    string text = sourceData.Rows[row][column].ToString();
                    Type type = sourceData.Columns[columnName].DataType;
                    if (!string.IsNullOrEmpty(text))
                    {
                        if (type.Name.Contains("Int32") || type.Name.Contains("Int16") || type.Name.Contains("Int64") || type.Name.Contains("SByte") || type.Name.Contains("Single") || type.Name.Contains("Double") || type.Name.Contains("Decimal"))
                        {
                            newRow.CreateCell(Count).SetCellValue(Convert.ToDouble(text));
                        }
                        else if (type.Name == "Boolean")
                        {
                            newRow.CreateCell(Count).SetCellValue(Convert.ToBoolean(text));
                        }
                        else if (type.Name == "DateTime")
                        {
                            newRow.CreateCell(Count).SetCellValue(Convert.ToDateTime(text));
                        }
                        else if (type.Name == "String")
                        {
                            if (ConvertColumnTypes != null)
                            {
                                if (ConvertColumnTypes.ContainsKey(columnName))
                                {
                                    double d;
                                    bool b = double.TryParse(text, out d);
                                    if (b)
                                    {
                                        newRow.CreateCell(Count).SetCellValue(d);
                                    }
                                    else
                                    {
                                        newRow.CreateCell(Count).SetCellValue(text);
                                    }
                                }
                                else
                                {
                                    newRow.CreateCell(Count).SetCellValue(text);
                                }
                            }
                            else
                            {
                                newRow.CreateCell(Count).SetCellValue(text);
                            }
                        }
                    }
                    else
                    {
                        if (ConvertColumnTypes != null)
                        {
                            if (ConvertColumnTypes.ContainsKey(columnName))
                            {
                                if (ConvertColumnTypes[columnName] != null)
                                {
                                    newRow.CreateCell(Count).SetCellValue(Convert.ToDouble(ConvertColumnTypes[columnName]));
                                }
                            }
                        }
                    }
                    Count++;
                }
                WriteRowCount++;
                endStatus = true;
            }
            //写入到excel中
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                using (FileStream fs1 = new FileStream(TargetFileNamePath, FileMode.Create, FileAccess.Write))
                {
                    byte[] _data = ms.ToArray();
                    fs1.Write(_data, 0, _data.Length);
                    fs1.Flush();
                    _data = null;
                }
            }
            return WriteRowCount;
        }

        /// <summary>
        /// 获取excel中的Sheet
        /// </summary>
        public static List<string> GetMultipleSheets(string fileName)
        {
            List<string> list = new List<string>();
            Dictionary<int, string> t = new Dictionary<int, string>();
            ISheet sheet = null;
            IWorkbook workbook = null;
            FileStream fs = null;
            try
            {
                fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);
                int count = workbook.NumberOfSheets; //获取所有SheetName
                for (int i = 0; i < count; i++)
                {
                    sheet = workbook.GetSheetAt(i);
                    if (sheet.LastRowNum > 0)
                    {
                        t.Add(i, workbook.GetSheetAt(i).SheetName);
                        list.Add(workbook.GetSheetAt(i).SheetName);
                    }

                }
                return list;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (fs != null)
                    fs.Dispose();
            }
        }

        /// <summary>
        /// Excel读取Sheets
        /// </summary>
        /// <param name="file">导入路径</param>
        /// <returns></returns>
        public static List<string> GetSheetNames(string file)
        {
            string fileExt = Path.GetExtension(file).ToLower();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook;
                if (fileExt == ".xlsx" || fileExt == ".ett" || fileExt == ".xlsm")
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (fileExt == ".xls" || fileExt == ".et")
                {
                    workbook = new HSSFWorkbook(fs);
                }
                else
                {
                    workbook = null;
                }
                if (workbook == null)
                {
                    return null;
                }
                int sheetCount = workbook.NumberOfSheets;//获取表的数量
                List<string> sheetNames = new List<string>();//保存表的名称
                for (int i = 0; i < sheetCount; i++)
                {
                    sheetNames.Add(workbook.GetSheetName(i));
                }
                return sheetNames;
            }
        }
        /*
        /// <summary>
        /// Excel读取Sheets
        /// </summary>
        /// <param name="file">导入路径</param>
        /// <returns></returns>
        public static List<string> GetSheetNames(IWorkbook workbook)
        {
            int sheetCount = workbook.NumberOfSheets;//获取表的数量
            List<string> sheetNames = new List<string>();//保存表的名称
            for (int i = 0; i < sheetCount; i++)
            {
                sheetNames.Add(workbook.GetSheetName(i));
            }
            return sheetNames;
        }
        */
        /// <summary>
        /// Excel读取Datable
        /// </summary>
        /// <param name="file">导入路径</param>
        /// <returns></returns>
        public static DataTable ExcelToTable(string file, bool isFirstRowColumn, string sheetName = "")
        {
            DataTable dt = new DataTable();
            IWorkbook workbook;
            ISheet sheet = null;
            string fileExt = Path.GetExtension(file).ToLower();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                if (fileExt == ".xlsx" || fileExt == ".ett" || fileExt == ".xlsm")
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (fileExt == ".xls" || fileExt == ".et")
                {
                    workbook = new HSSFWorkbook(fs);
                }
                else
                {
                    workbook = null;
                }
                if (workbook == null)
                {
                    return null;
                }
                sheet = workbook.GetSheetAt(0);
                if (!string.IsNullOrEmpty(sheetName))
                {
                    int SheetCount = workbook.NumberOfSheets;//获取表的数量
                    string[] SheetName = new string[SheetCount];//保存表的名称
                    for (int i = 0; i < SheetCount; i++)
                    {
                        SheetName[i] = workbook.GetSheetName(i).ToLower();

                    }
                    if (SheetName.Contains(sheetName.ToLower()))
                    {
                        sheet = workbook.GetSheet(sheetName);
                    }
                    else
                    {
                        throw new ArgumentException(ConfigStringHelper.ExcelError_NotSheet);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                var rowCount = sheet.LastRowNum + 1;//得到总行数
                //z这里遍历得到最大列数,因为存在第一行残缺为空的情况
                List<int> array = new List<int>();
                for (var i = 0; i < rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null)
                    {
                        row = sheet.CreateRow(i);
                    }
                    //array.Add(row.Cells.Count);//获取有效列
                    array.Add(row.LastCellNum);//获取所有列(包含空列以及残缺)
                }
                var maxValue = array.Max();//得到最大列数
                int Index = 0;//用来判断是否使用列头
                List<int> columns = new List<int>();
                //创建列头
                if (isFirstRowColumn == false)
                {
                    //第一行不作为DataTable的列名
                    Index = sheet.FirstRowNum;
                    for (int i = 0; i < maxValue; i++)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                        columns.Add(i);
                    }
                }
                else
                {
                    //判断第一行是否是合并单元格
                    IRow row = sheet.GetRow(0);
                    for (int i = 0; i < row.Cells.Count; i++)
                    {
                        ICell cell = row.GetCell(i);
                        if (cell == null)
                        {
                            cell = row.CreateCell(i);
                        }
                        var cellfirst = cell.IsMergedCell;
                        if (cellfirst == true)
                        {
                            //说明第一行有合并单元格无法作为列,此时需要提醒用户
                            throw new Exception(ConfigStringHelper.ExcelError_IncludeHeaders);
                        }
                    }
                    Index = sheet.FirstRowNum + 1;
                    //第一行作为DataTable列名
                    //表头  
                    IRow header = sheet.GetRow(sheet.FirstRowNum);
                    if (header == null)
                    {
                        for (int i = 0; i < maxValue; i++)
                        {
                            dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                            columns.Add(i);
                        }
                    }
                    else
                    {
                        //这里用来检测第一行数据是否合法,可以用来做列头
                        List<string> lst = new List<string>();
                        for (int i = 0; i < header.LastCellNum; i++)
                        {
                            object obj = GetValueType(header.GetCell(i));
                            if (obj == null)
                            {
                                lst.Add("Columns" + i.ToString());
                            }
                            else
                            {
                                lst.Add(obj.ToString());
                            }

                        }
                        bool status = lst.GroupBy(n => n).Any(c => c.Count() > 1);
                        if (status == true)
                        {
                            //说明第一行存在重复数据无法作为列头
                            throw new Exception(ConfigStringHelper.ExcelError_IncludeHeadersData);
                        }
                        for (int i = 0; i < maxValue; i++)
                        {
                            object obj = GetValueType(header.GetCell(i));
                            if (obj == null || obj.ToString() == string.Empty)
                            {
                                dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                            }
                            else
                                dt.Columns.Add(new DataColumn(obj.ToString()));
                            columns.Add(i);
                        }
                    }
                }
                //数据  
                for (int i = Index; i <= sheet.LastRowNum; i++)
                {
                    DataRow dr = dt.NewRow();
                    foreach (int j in columns)
                    {
                        dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
                    }
                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }
        /// <summary>
        /// 获取单元格类型
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueType(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank:
                    return null;
                case CellType.Boolean:
                    return cell.BooleanCellValue;
                case CellType.Numeric:
                    if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                    {
                        return Convert.ToDateTime(cell.DateCellValue);
                    }
                    else//其他数字类型
                    {
                        return Convert.ToDouble(cell.NumericCellValue);
                    }
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Error:
                  
                    XSSFCell xSSFCell = cell as XSSFCell;

                    if (null != xSSFCell)
                    {
                        return xSSFCell.ErrorCellString;
                    }
                    else
                    {
                        return cell.ToString();
                    }
                case CellType.Formula:
                    try
                    {
                        return cell.NumericCellValue.ToString();
                    }
                    catch
                    {
                        try
                        {
                            #region 这种方式处理可以保证100%无错误,但是效率太慢,一个文本公式读取需要3秒
                            //IFormulaEvaluator formulaEvaluator;
                            //if (_excelVersion == "2007")
                            //{
                            //    formulaEvaluator = new XSSFFormulaEvaluator(workbook);
                            //}
                            //else
                            //{
                            //    formulaEvaluator = new HSSFFormulaEvaluator(workbook);
                            //}

                            //formulaEvaluator.EvaluateInCell(cell);
                            //var FormulaValue = cell.ToString();
                            #endregion

                            var FormulaValue = cell.StringCellValue.ToString();
                            return FormulaValue;
                        }
                        catch
                        {
                            return "";
                        }
                    }
                default:
                    return "=" + cell.CellFormula;
            }
        }


        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public static DataTable ReadTableFromExcel(string fileName, string sheetName = "", bool isFirstRowColumn = false)
        {
            //FileStream fs = null;
            try
            {
                using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    ISheet sheet = null;
                    DataTable data = new DataTable();
                    int startRow = 0;
                    IWorkbook workbook = null;
                    if (fileName.Contains(".xlsx")) // 2007版本
                        workbook = new XSSFWorkbook(fs);
                    else if (fileName.Contains(".xls")) // 2003版本
                        workbook = new HSSFWorkbook(fs);

                    if (sheetName != null)
                    {
                        sheet = workbook.GetSheetAt(0);

                        int SheetCount = workbook.NumberOfSheets;//获取表的数量
                        string[] SheetName = new string[SheetCount];//保存表的名称
                        for (int i = 0; i < SheetCount; i++)
                        {
                            SheetName[i] = workbook.GetSheetName(i);

                        }
                        if (SheetName.Contains(sheetName))
                        {
                            sheet = workbook.GetSheet(sheetName);
                        }
                        else
                        {
                            sheet = workbook.GetSheetAt(0);
                        }
                        //if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet
                        //{
                        //sheet = workbook.GetSheet(sheetName);
                        //}
                    }
                    else
                    {
                        sheet = workbook.GetSheetAt(0);
                    }

                    if (sheet != null)
                    {
                        IRow firstRow = sheet.GetRow(0);
                        if (firstRow == null)
                        {
                            return data;
                        }
                        int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                        if (isFirstRowColumn)
                        {
                            for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                            {
                                ICell cell = null;
                                cell = firstRow.GetCell(i);
                                if (cell != null)
                                {
                                    string cellValue = cell.StringCellValue;
                                    if (cellValue != null)
                                    {
                                        DataColumn column = new DataColumn(cellValue);
                                        data.Columns.Add(column);
                                    }
                                }
                            }

                            startRow = sheet.FirstRowNum + 1;
                        }
                        else
                        {
                            for (int column = 0; column < cellCount; column++)
                            {
                                data.Columns.Add($"Column{column}");
                            }

                            startRow = sheet.FirstRowNum;
                        }

                        //最后一列的标号
                        int rowCount = sheet.LastRowNum;
                        for (int i = startRow; i <= rowCount; ++i)
                        {
                            IRow row = sheet.GetRow(i);
                            if (row == null) continue; //没有数据的行默认是null       

                            DataRow dataRow = data.NewRow();
                            for (int j = row.FirstCellNum; j < cellCount; ++j)
                            {
                                if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null
                                    dataRow[j] = row.GetCell(j).ToString();
                            }
                            data.Rows.Add(dataRow);
                        }
                    }
                    return data;
                }
            }
            finally
            {
                //if (fs != null)
                //{
                //    fs.Dispose();
                //}
            }
        }

        /// <summary>
        /// 读取单元格
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="cell">单元格坐标</param>
        /// <returns>返回的DataTable</returns>
        public static string ReadCell(string fileName, string sheetName = "", string cell = "")
        {
            FileStream fs = null;
            try
            {
                using (fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    ISheet sheet = null;
                    DataTable data = new DataTable();
                    int startRow = 0;
                    IWorkbook workbook = null;
                    if (fileName.Contains(".xlsx") || fileName.Contains(".ett") || fileName.Contains(".xlsm")) // 2007版本
                        workbook = new XSSFWorkbook(fs);
                    else if (fileName.Contains(".xls") || fileName.Contains(".et")) // 2003版本
                        workbook = new HSSFWorkbook(fs);

                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null)
                    {
                        throw new ArgumentException(ConfigStringHelper.ExcelError_NotSheet);
                    }
                    if (sheet != null)
                    {

                        CellReference cf = new CellReference(cell);

                        var irow = sheet.GetRow(cf.Row);
                        if (irow == null)
                        {
                            irow = sheet.CreateRow(cf.Row); ;
                        }
                        try
                        {
                            var cel = irow.GetCell(cf.Col);
                            if (cel == null)
                            {
                                cel = irow.CreateCell(cf.Col);
                            }
                            if (cel != null)
                            {
                                if (cel.CellType == CellType.Formula)
                                {
                                    cel.SetCellType(CellType.String);
                                }
                                return cel.ToString();
                            }
                        }
                        catch
                        {
                            throw new ArgumentException(ConfigStringHelper.ExcelError_CellExist);
                        }
                    }
                    return null;
                }
            }
            finally
            {
                if (fs != null)
                {
                    fs.Dispose();
                }
            }
        }

        /// <summary>
        /// 读取列
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="column">列</param>
        /// <returns>返回的DataTable</returns>
        public static string[] ReadColumn(string fileName, string sheetName, string column)
        {
            FileStream fs = null;
            try
            {
                using (fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    List<string> resultList = new List<string>();
                    ISheet sheet = null;

                    IWorkbook workbook = null;
                    if (fileName.Contains(".xlsx")) // 2007版本
                    {
                        workbook = new XSSFWorkbook(fs);
                    }
                    else
                    {
                        workbook = new HSSFWorkbook(fs);
                    }

                    if (sheetName != null)
                    {
                        sheet = workbook.GetSheet(sheetName);
                        if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet
                        {
                            sheet = workbook.GetSheetAt(0);
                        }
                    }
                    else
                    {
                        sheet = workbook.GetSheetAt(0);
                    }

                    if (sheet != null)
                    {
                        CellReference cf = new CellReference(column);

                        for (int rownum = sheet.FirstRowNum; rownum <= sheet.LastRowNum; rownum++)
                        {
                            var row = sheet.GetRow(rownum);

                            var cell = row?.GetCell(cf.Col);
                            if (cell != null)
                            {
                                if (cell.CellType == CellType.Formula)
                                {
                                    cell.SetCellType(CellType.String);
                                }
                                resultList.Add(cell.ToString());
                            }
                        }
                    }
                    return resultList.ToArray();
                }
            }
            finally
            {
                if (fs != null)
                {
                    fs.Dispose();
                }
            }
        }



        /// <summary>
        /// 读取范围
        /// </summary>
        [Obsolete]
        public static DataTable ReadRange(string fileName, string sheetName, string range, bool includeHeaders = false)
        {
            FileStream fs = null;
            try
            {
                using (fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    List<string> resultList = new List<string>();
                    ISheet sheet = null;

                    IWorkbook workbook = null;
                    if (fileName.Contains(".xlsx") || fileName.Contains(".ett") || fileName.Contains(".xlsm")) // 2007版本
                    {
                        workbook = new XSSFWorkbook(fs);
                    }
                    else
                    {
                        workbook = new HSSFWorkbook(fs);
                    }
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null)
                    {
                        throw new ArgumentException(ConfigStringHelper.ExcelError_NotSheet);
                    }
                    System.Data.DataTable dt = new System.Data.DataTable();
                    if (sheet != null)
                    {
                        try
                        {
                            CellRangeAddress cellRangeAddress = CellRangeAddress.ValueOf(range);
                            int firstcolumn = cellRangeAddress.FirstColumn;
                            int lastcolumn = cellRangeAddress.LastColumn;
                            int firstrow = cellRangeAddress.FirstRow;
                            int lastrow = cellRangeAddress.LastRow;


                            if (!range.Contains(":"))
                            {
                                if (lastcolumn == firstcolumn)
                                {
                                    IRow headrow = sheet.GetRow(firstrow);
                                    lastcolumn = headrow.Cells.Count;
                                }
                                if (lastrow == firstrow)
                                    lastrow = sheet.LastRowNum + 1;//得到总行数
                            }
                            int Column = lastcolumn - firstcolumn;
                            int startRow = firstrow;
                            List<int> columns = new List<int>();
                            //创建列头
                            if (includeHeaders)
                            {
                                IRow firstRow = sheet.GetRow(startRow);
                                int cellCount = firstRow.LastCellNum;
                                for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                {
                                    ICell cell = null;
                                    cell = firstRow.GetCell(i);
                                    if (cell != null)
                                    {
                                        string cellValue = cell.StringCellValue;
                                        if (cellValue != null)
                                        {
                                            DataColumn column = new DataColumn(cellValue);
                                            dt.Columns.Add(column);
                                            columns.Add(i);
                                        }
                                    }
                                }

                                startRow = firstrow + 1;
                            }
                            else
                            {

                                for (int i = 0; i < Column; i++)
                                {
                                    dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                                    columns.Add(i);
                                }
                            }
                            //数据  
                            for (var i = startRow; i < lastrow; i++)
                            {
                                DataRow dr = dt.NewRow();
                                IRow row = sheet.GetRow(i);
                                if (row == null)
                                {
                                    row = sheet.CreateRow(i);
                                }
                                bool merged = false;
                                for (var j = firstcolumn; j < Column + firstcolumn; j++)
                                {

                                    ICell cell = row.GetCell(j);


                                    if (cell == null)
                                    {
                                        cell = row.CreateCell(j);
                                    }
                                    if (cell.IsMergedCell)  //检测列的单元格是否合并
                                    {
                                        dr[j] = GetValueType(cell); //获取单元格的值
                                        if (string.IsNullOrWhiteSpace(dr[j].ToString()) && j > 0)
                                        {
                                            if (merged)
                                            {
                                                dr[j] = dr[j - 1];
                                            }
                                        }
                                        merged = true;
                                    }
                                    else
                                    {
                                        merged = false;
                                        var firsLine = row.GetCell(j).ToString();
                                        dr[j - cellRangeAddress.FirstColumn] = GetValueType(row.GetCell(j));
                                    }
                                }
                                dt.Rows.Add(dr);
                            }
                        }
                        catch
                        {
                            throw new ArgumentException(ConfigStringHelper.ExcelError_ErrorRange);
                        }
                    }
                    return dt;
                }
            }
            finally
            {
                if (fs != null)
                {
                    fs.Dispose();
                }
            }
        }

        /// <summary>
        /// 读取范围
        /// </summary>
        public static DataTable ReadRange(string fileName, string sheetName, string range, bool includeHeaders = false, bool isMergeColumn = false, bool isMergeRow = false)
        {
            FileStream fs = null;
            try
            {
                using (fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    List<string> resultList = new List<string>();
                    ISheet sheet = null;

                    IWorkbook workbook = null;
                    if (fileName.Contains(".xlsx") || fileName.Contains(".ett") || fileName.Contains(".xlsm")) // 2007版本
                    {
                        workbook = new XSSFWorkbook(fs);
                    }
                    else
                    {
                        workbook = new HSSFWorkbook(fs);
                    }
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null)
                    {
                        throw new ArgumentException("ExcelError_NotSheet");
                    }
                    System.Data.DataTable dt = new System.Data.DataTable();
                    if (sheet != null)
                    {
                        try
                        {
                            CellRangeAddress cellRangeAddress = CellRangeAddress.ValueOf(range);
                            int firstcolumn = cellRangeAddress.FirstColumn;
                            int lastcolumn = cellRangeAddress.LastColumn;
                            int firstrow = cellRangeAddress.FirstRow;
                            int lastrow = cellRangeAddress.LastRow;


                            if (!range.Contains(":"))
                            {
                                if (lastcolumn == firstcolumn)
                                {
                                    IRow headrow = sheet.GetRow(firstrow);
                                    lastcolumn = headrow.Cells.Count;
                                }
                                if (lastrow == firstrow)
                                    lastrow = sheet.LastRowNum + 1;//得到总行数
                            }
                            int Column = lastcolumn - firstcolumn;
                            int startRow = firstrow;
                            List<int> columns = new List<int>();
                            //创建列头
                            if (includeHeaders)
                            {
                                IRow firstRow = sheet.GetRow(startRow);
                                int cellCount = firstRow.LastCellNum;
                                for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                {
                                    ICell cell = null;
                                    cell = firstRow.GetCell(i);
                                    if (cell != null)
                                    {
                                        string cellValue = cell.StringCellValue;
                                        if (cellValue != null)
                                        {
                                            DataColumn column = new DataColumn(cellValue);
                                            dt.Columns.Add(column);
                                            columns.Add(i);
                                        }
                                    }
                                }

                                startRow = firstrow + 1;
                            }
                            else
                            {

                                for (int i = 0; i < Column; i++)
                                {
                                    dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                                    columns.Add(i);
                                }
                            }
                            //数据  

                            for (var i = startRow; i < lastrow; i++)
                            {
                                DataRow dr = dt.NewRow();
                                IRow row = sheet.GetRow(i);
                                if (row == null)
                                {
                                    row = sheet.CreateRow(i);
                                }
                                bool merged = false;

                                for (var j = firstcolumn; j < Column + firstcolumn; j++)
                                {

                                    ICell cell = row.GetCell(j);


                                    if (cell == null)
                                    {
                                        cell = row.CreateCell(j);
                                    }
                                    if (cell.IsMergedCell)  //检测列的单元格是否合并
                                    {

                                        dr[j - firstcolumn] = GetValueType(cell); //获取单元格的值
                                        if (isMergeColumn)
                                        {
                                            if (string.IsNullOrWhiteSpace(dr[j - firstcolumn].ToString()) && j - firstcolumn > 0)
                                            {
                                                if (merged)
                                                {
                                                    dr[j - firstcolumn] = dr[j - firstcolumn - 1];

                                                }

                                            }
                                        }
                                        if (isMergeRow)
                                        {
                                            if (string.IsNullOrWhiteSpace(dr[j - firstcolumn].ToString()) && i - startRow > 0)
                                            {
                                                DataRow drRow = dt.Rows[dt.Rows.Count - 1];
                                                dr[j - firstcolumn] = drRow[j - firstcolumn];
                                            }
                                        }
                                        merged = true;
                                    }
                                    else
                                    {
                                        merged = false;
                                        var firsLine = row.GetCell(j).ToString();
                                        dr[j - firstcolumn] = GetValueType(row.GetCell(j));
                                    }
                                }
                                dt.Rows.Add(dr);
                            }
                        }
                        catch (Exception ex)
                        {
                            throw new ArgumentException("ExcelError_ErrorRange");
                        }
                    }
                    return dt;
                }
            }
            finally
            {
                if (fs != null)
                {
                    fs.Dispose();
                }
            }
        }


        /// <summary>
        /// 读取行
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="rowIndex">行号</param>
        /// <param name="startColumnIndex">列号</param>
        /// <returns>返回的DataTable</returns>
        public static string[] ReadRow(string fileName, string sheetName, int rowIndex, int startColumnIndex)
        {
            FileStream fs = null;
            try
            {
                using (fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    List<string> resultList = new List<string>();
                    ISheet sheet = null;

                    IWorkbook workbook = null;
                    if (fileName.Contains(".xlsx") || fileName.Contains(".ett") || fileName.Contains(".xlsm")) // 2007版本
                    {
                        workbook = new XSSFWorkbook(fs);
                    }
                    else
                    {
                        workbook = new HSSFWorkbook(fs);
                    }


                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null)
                    {
                        throw new ArgumentException(ConfigStringHelper.ExcelError_NotSheet);
                    }

                    if (sheet != null)
                    {
                        var row = sheet.GetRow(rowIndex);
                        if (row == null)
                        {
                            row = sheet.CreateRow(rowIndex);
                        }
                        if (row != null)
                        {
                            for (int columnNumber = startColumnIndex - 1; columnNumber < row.LastCellNum; columnNumber++)
                            {
                                var cell = row.GetCell(columnNumber);
                                if (cell == null)
                                {
                                    cell = row.CreateCell(columnNumber);
                                }
                                if (cell != null)
                                {
                                    if (cell.CellType == CellType.Formula)
                                    {
                                        cell.SetCellType(CellType.String);
                                    }
                                    resultList.Add(cell.ToString());
                                }
                            }
                        }
                    }
                    return resultList.ToArray();
                }
            }
            finally
            {
                if (fs != null)
                {
                    fs.Dispose();
                }
            }
       
        }

        /// <summary>
        /// 写入单元格
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="cell">单元格坐标</param>
        /// <param name="value">写入的数据</param>
        /// <returns>返回的DataTable</returns>
        public static void WriteCell(string fileName, string sheetName, string cell, object value)
        {
            IWorkbook workbook = null;
            using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                if (fileName.Contains(".xlsx") || fileName.Contains(".ett") || fileName.Contains(".xlsm")) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.Contains(".xls") || fileName.Contains(".et")) // 2003版本
                    workbook = new HSSFWorkbook(fs);

                ISheet sheet = workbook.GetSheetAt(0); //获取第一个工作表
                int SheetCount = workbook.NumberOfSheets;//获取表的数量
                string[] SheetName = new string[SheetCount];//保存表的名称
                for (int i = 0; i < SheetCount; i++)
                {
                    SheetName[i] = workbook.GetSheetName(i).ToLower();
                    //获取sheet的索引
                }
                if (SheetName.Contains(sheetName.ToLower()))
                {
                    sheet = workbook.GetSheet(sheetName);
                }
                else
                {
                    throw new ArgumentException(ConfigStringHelper.ExcelError_NotSheet);
                }
                CellReference cf = new CellReference(cell);
                var row = sheet.GetRow(cf.Row);
                if (row == null)
                {
                    row = sheet.CreateRow(cf.Row);
                }
                try
                {
                    ICell icell = row.GetCell(cf.Col);
                    if (icell == null)
                    {
                        icell = row.CreateCell(cf.Col);
                    }

                    switch (icell.CellType)
                    {
                        case CellType.Formula:
                            icell.SetCellFormula(value.ToString());
                            break;
                        case CellType.Blank:
                        case CellType.Numeric:
                            if (value is DateTime)
                            {
                                icell.SetCellValue((DateTime)value);
                            }
                            else if (value is double)
                            {
                                icell.SetCellValue((double)value);
                            }
                            else
                            {
                                double doubleValue;

                                if (double.TryParse(value.ToString(), out doubleValue))
                                {
                                    icell.SetCellValue(doubleValue);
                                }
                                else
                                {
                                    icell.SetCellValue(value.ToString());
                                }
                            }
                            break;
                        case CellType.Boolean:
                            if (value is bool)
                            {
                                icell.SetCellValue((bool)value);
                            }
                            else
                            {
                                bool boolValue;
                                if (bool.TryParse(value.ToString(), out boolValue))
                                {
                                    icell.SetCellValue(boolValue);
                                }
                                else
                                {
                                    icell.SetCellValue(value.ToString());
                                }
                            }
                            break;
                        case CellType.Error:
                            if (value is byte)
                            {
                                icell.SetCellErrorValue((byte)value);
                            }
                            else
                            {
                                byte byteValue;
                                if (byte.TryParse(value.ToString(), out byteValue))
                                {
                                    icell.SetCellErrorValue(byteValue);
                                }
                                else
                                {
                                    icell.SetCellValue(value.ToString());
                                }
                            }
                            break;
                        case CellType.String:
                        default:
                            icell.SetCellValue(value.ToString());
                            break;
                    }
                    //Regex rx = new Regex(pattern);
                    //bool status = rx.IsMatch(value.ToString());
                    //if (status == true)
                    //{
                    //    //纯数字
                    //    icell.SetCellValue(Convert.ToDouble(value));
                    //}
                    //else
                    //{
                    //    icell.SetCellValue(value.ToString());
                    //}
                }
                catch
                {
                    throw new Exception(ConfigStringHelper.ExcelError_CellExist);
                }
                //这一句使用NPOI必须加,如不加在修改完单元格的值后 excel不会自动更新公式的值
                sheet.ForceFormulaRecalculation = true;
            }
            //写入到excel中
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                using (FileStream fs1 = new FileStream(fileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] _data = ms.ToArray();
                    fs1.Write(_data, 0, _data.Length);
                    fs1.Flush();
                    _data = null;
                }
            }

        }


        /// <summary>
        /// 写入单元格
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="cell">单元格坐标</param>
        /// <param name="value">写入的数据</param>
        /// <returns>返回的DataTable</returns>
        public static void WriteCell(ISheet sheet, string cell, object value)
        {
            CellReference cf = new CellReference(cell);
            var row = sheet.GetRow(cf.Row);
            if (row == null)
            {
                row = sheet.CreateRow(cf.Row);
            }
            try
            {
                if (value != null)
                {
                    ICell icell = row.GetCell(cf.Col);
                    if (icell == null)
                    {
                        icell = row.CreateCell(cf.Col);
                    }

                    switch (icell.CellType)
                    {
                        case CellType.Formula:
                            icell.SetCellFormula(value.ToString());
                            break;
                        case CellType.Numeric:
                            if (value is DateTime)
                            {
                                icell.SetCellValue((DateTime)value);
                            }
                            else if (value is double)
                            {
                                icell.SetCellValue((double)value);
                            }
                            else
                            {
                                double doubleValue;

                                if (double.TryParse(value.ToString(), out doubleValue))
                                {
                                    icell.SetCellValue(doubleValue);
                                }
                                else
                                {
                                    icell.SetCellValue(value.ToString());
                                }
                            }
                            break;
                        case CellType.Boolean:
                            if (value is bool)
                            {
                                icell.SetCellValue((bool)value);
                            }
                            else
                            {
                                bool boolValue;
                                if (bool.TryParse(value.ToString(), out boolValue))
                                {
                                    icell.SetCellValue(boolValue);
                                }
                                else
                                {
                                    icell.SetCellValue(value.ToString());
                                }
                            }
                            break;
                        case CellType.Error:
                            if (value is byte)
                            {
                                icell.SetCellErrorValue((byte)value);
                            }
                            else
                            {
                                byte byteValue;
                                if (byte.TryParse(value.ToString(), out byteValue))
                                {
                                    icell.SetCellErrorValue(byteValue);
                                }
                                else
                                {
                                    icell.SetCellValue(value.ToString());
                                }
                            }
                            break;
                        case CellType.String:
                        default:
                            icell.SetCellValue(value.ToString());
                            break;
                    }
                    //Regex rx = new Regex(pattern);
                    //bool status = rx.IsMatch(value.ToString());
                    //if (status == true)
                    //{
                    //    //纯数字
                    //    icell.SetCellValue(Convert.ToDouble(value));
                    //}
                    //else
                    //{
                    //    icell.SetCellValue(value.ToString());
                    //}
                }
            }
            catch
            {
                throw new Exception(ConfigStringHelper.ExcelError_CellExist);
            }
            //这一句使用NPOI必须加,如不加在修改完单元格的值后 excel不会自动更新公式的值
            sheet.ForceFormulaRecalculation = true;
        }

        /// <summary>
        /// 写入指定行
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="rowIndex">单元格坐标</param>
        /// <param name="data">写入的数据</param>
        /// <returns>返回的DataTable</returns>
        public static void WriteRow(string fileName, string sheetName, int rowIndex, IEnumerable<object> data, int startColumnIndex, bool isInsertRow = false)
        {
            FileStream fs = null;
            try
            {
                IWorkbook workbook = null;
                ISheet sheet = null;
                using (fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    if (fileName.Contains(".xlsx") || fileName.Contains(".ett") || fileName.Contains(".xlsm")) // 2007版本
                        workbook = new XSSFWorkbook(fs);
                    else if (fileName.Contains(".xls") || fileName.Contains(".et")) // 2003版本
                        workbook = new HSSFWorkbook(fs);
                }
                if (sheetName != null)
                {
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null)
                    {
                        throw new Exception(ConfigStringHelper.ExcelError_NotSheet);
                    }
                }
                else
                {
                    //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    var row = sheet.GetRow(rowIndex);

                    if (row == null)
                    {
                        row = sheet.CreateRow(rowIndex);
                    }

                    if (row != null)
                    {
                        int columnIndex = startColumnIndex;
                        foreach (var item in data)
                        {
                            if (item != null)
                            {
                                ICell icell = row.CreateCell(columnIndex);
                                if (icell == null)
                                {
                                    icell = row.CreateCell(columnIndex);
                                }
                                Regex rx = new Regex(pattern);
                                bool status = rx.IsMatch(item.ToString());
                                if (status == true)
                                {
                                    //纯数字
                                    icell.SetCellValue(Convert.ToDouble(item));
                                }
                                else
                                {
                                    icell.SetCellValue(item.ToString());
                                }
                            }
                            columnIndex++;
                        }
                        using (MemoryStream ms = new MemoryStream())
                        {
                            workbook.Write(ms);
                            using (FileStream fs1 = new FileStream(fileName, FileMode.Create, FileAccess.Write))
                            {
                                byte[] _data = ms.ToArray();
                                fs1.Write(_data, 0, _data.Length);
                                fs1.Flush();
                                _data = null;
                            }
                        }
                    }
                }
            }
            finally
            {
                if (fs != null)
                {
                    fs.Dispose();
                }
            }
        }

        /// <summary>
        /// 写入指定列
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="columnName">单元格坐标</param>
        /// <param name="ColumnIndex">列索引</param>
        /// <param name="data">写入的数据</param>
        /// <param name="StartRowIndex">起始行号 从零开始</param>
        /// <returns>返回的DataTable</returns>
        public static void WriteColumn(string fileName, string sheetName, string columnName, int ColumnIndex, IEnumerable<object> data, int StartRowIndex, bool insertColumn = false)
        {
            string cell_Value = "";
            List<object> inserValues = new List<object>();
            foreach (var item in data)
            {
                inserValues.Add(item);
            }
            IWorkbook workbook = null;
            using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                try
                {
                    ISheet sheet = null;
                    if (fileName.Contains(".xlsx") || fileName.Contains(".ett") || fileName.Contains(".xlsm")) // 2007版本
                        workbook = new XSSFWorkbook(fs);
                    else if (fileName.Contains(".xls") || fileName.Contains(".et")) // 2003版本
                        workbook = new HSSFWorkbook(fs);

                    if (sheetName != null)
                    {
                        sheet = workbook.GetSheet(sheetName);
                        if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet
                        {
                            throw new Exception(ConfigStringHelper.ExcelError_NotSheet);
                        }
                    }
                    else
                    {
                        sheet = workbook.GetSheetAt(0);
                    }

                    if (sheet != null)
                    {
                        bool status = false;
                        var rowCount = sheet.LastRowNum + 1;//得到总行数
                        if (!string.IsNullOrEmpty(columnName))
                        {
                            IRow row = sheet.GetRow(0);
                            if (row == null)
                            {
                                row = sheet.CreateRow(0);
                            }
                            for (var j = 0; j < row.Cells.Count; j++)
                            {

                                ICell cell = row.GetCell(j);
                                if (cell == null)
                                {
                                    cell = row.CreateCell(j);
                                }
                                var firsLine = row.GetCell(j).ToString();
                                if (firsLine == columnName)
                                {
                                    if (status == false)
                                    {
                                        cell_Value = columnName;
                                        status = true;
                                        ColumnIndex = j;
                                    }
                                    //记录所在的列,只记录一次如果出现多个同名列只计算第一列
                                }
                            }
                        }
                        if (string.IsNullOrEmpty(cell_Value) && !string.IsNullOrEmpty(columnName))
                        {
                            throw new ArgumentNullException(ConfigStringHelper.ExcelError_SheetNotcolumnName);
                        }
                        int targetColumn = StartRowIndex - 1;
                        int k = 0;
                        int Index = 0;
                        if (status == true)
                        {
                            Index = ColumnIndex;
                        }
                        else
                        {
                            Index = ColumnIndex - 1;
                        }
                        for (int rows = 0; rows < inserValues.Count(); rows++)
                        {
                            IRow row = sheet.GetRow(targetColumn);
                            if (row == null)
                            {
                                row = sheet.CreateRow(targetColumn);
                            }
                            ICell cell = row.GetCell(Index);
                            if (cell == null)
                            {
                                cell = row.CreateCell(Index);
                            }
                            Regex rx = new Regex(pattern);
                            bool statued = rx.IsMatch(inserValues[k].ToString());
                            if (statued == true)
                            {
                                //纯数字
                                cell.SetCellValue(Convert.ToDouble(inserValues[k]));
                            }
                            else
                            {
                                cell.SetCellValue(inserValues[k].ToString());
                            }
                            k++;
                            targetColumn++;
                        }

                    }
                    //这一句使用NPOI必须加,如不加在修改完单元格的值后 excel不会自动更新公式的值
                    sheet.ForceFormulaRecalculation = true;
                }
                finally
                {
                    if (fs != null)
                    {
                        fs.Dispose();
                    }
                }
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] _data = ms.ToArray();
                    fs.Write(_data, 0, _data.Length);
                    fs.Flush();
                    _data = null;
                }
            }


        }


        public static void InsertRow(string FilePath, string sheetName, IEnumerable<object> data, int insertrowIndex, int startColumnIndex)
        {
            List<object> inserValues = new List<object>();
            foreach (var item in data)
            {
                inserValues.Add(item);
            }
            IWorkbook workbook = null;
            //读取流
            using (FileStream fs = new FileStream(FilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                if (FilePath.IndexOf(".xlsx") > 0 || FilePath.IndexOf(".ett") > 0 || FilePath.IndexOf(".xlsm") > 0)
                {  //2007版本的excel
                    workbook = new XSSFWorkbook(fs);
                }
                else if (FilePath.IndexOf(".xls") > 0 || FilePath.IndexOf(".et") > 0) //2003版本的excel
                {
                    workbook = new HSSFWorkbook(fs);
                }
                ISheet sheet = workbook.GetSheetAt(0); //获取第一个工作表
                int SheetCount = workbook.NumberOfSheets;//获取表的数量
                string[] SheetName = new string[SheetCount];//保存表的名称
                for (int i = 0; i < SheetCount; i++)
                {
                    SheetName[i] = workbook.GetSheetName(i).ToLower();
                    //获取sheet的索引
                }
                if (string.IsNullOrEmpty(sheetName))
                {
                    //没有填写默认sheet,则获取第一个sheet
                    sheet = workbook.GetSheetAt(0);
                }
                else
                {
                    if (SheetName.Contains(sheetName.ToLower()))
                    {
                        sheet = workbook.GetSheet(sheetName);
                    }
                    else
                    {
                        throw new ArgumentException(ConfigStringHelper.ExcelError_NotSheet);
                    }
                }
                //插入行
                sheet.ShiftRows(insertrowIndex - 1, sheet.LastRowNum + insertrowIndex - 1, 1, true, true);
                IRow row = sheet.GetRow(insertrowIndex - 1);
                if (row == null)
                {
                    row = sheet.CreateRow(insertrowIndex - 1);
                }
                for (var j = 0; j < inserValues.Count; j++)
                {
                    ICell cell = row.GetCell(j);
                    if (cell == null)
                    {
                        cell = row.CreateCell(j);
                    }
                    if (inserValues[j] != null)
                    {
                        Regex rx = new Regex(pattern);
                        bool status = rx.IsMatch(inserValues[j].ToString());
                        if (status == true)
                        {
                            //纯数字
                            cell.SetCellValue(Convert.ToDouble(inserValues[j]));
                        }
                        else
                        {
                            cell.SetCellValue(inserValues[j].ToString());
                        }
                    }

                }
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                using (FileStream fs = new FileStream(FilePath, FileMode.Create, FileAccess.Write))
                {
                    byte[] _data = ms.ToArray();
                    fs.Write(_data, 0, _data.Length);
                    fs.Flush();
                    _data = null;
                }
            }
        }


        //插入空行
        private static void InsertRowCount(string FilePath, string sheetName, int rowCount, int insertrowIndex, int startColumnIndex)
        {

            IWorkbook workbook = null;
            //读取流
            using (FileStream fs = new FileStream(FilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                if (FilePath.IndexOf(".xlsx") > 0 || FilePath.IndexOf(".ett") > 0 || FilePath.IndexOf(".xlsm") > 0)
                {  //2007版本的excel
                    workbook = new XSSFWorkbook(fs);
                }
                else if (FilePath.IndexOf(".xls") > 0 || FilePath.IndexOf(".et") > 0) //2003版本的excel
                {
                    workbook = new HSSFWorkbook(fs);
                }
                ISheet sheet = workbook.GetSheetAt(0); //获取第一个工作表
                int SheetCount = workbook.NumberOfSheets;//获取表的数量
                string[] SheetName = new string[SheetCount];//保存表的名称
                for (int i = 0; i < SheetCount; i++)
                {
                    SheetName[i] = workbook.GetSheetName(i).ToLower();
                    //获取sheet的索引
                }
                if (string.IsNullOrEmpty(sheetName))
                {
                    //没有填写默认sheet,则获取第一个sheet
                    sheet = workbook.GetSheetAt(0);
                }
                else
                {
                    if (SheetName.Contains(sheetName.ToLower()))
                    {
                        sheet = workbook.GetSheet(sheetName);
                    }
                    else
                    {
                        throw new ArgumentException(ConfigStringHelper.ExcelError_NotSheet);
                    }
                }
                //插入行
                sheet.ShiftRows(insertrowIndex - 1, sheet.LastRowNum + insertrowIndex - 1, 1, true, true);
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                using (FileStream fs = new FileStream(FilePath, FileMode.Create, FileAccess.Write))
                {
                    byte[] _data = ms.ToArray();
                    fs.Write(_data, 0, _data.Length);
                    fs.Flush();
                    _data = null;
                }
            }
        }


        //实现在指定列前插入列
        /// <summary>
        /// 
        /// </summary>
        /// <param name="FilePath">excel路径</param>
        /// <param name="sheetName">sheet名称</param>
        /// <param name="data">数据源</param>
        /// <param name="columnName">指定的内容列名</param>
        /// <param name="insertColumnIndex">列索引</param>
        /// <param name="StartRowIndex">从第几行开始</param>
        public static void InsertColumn(string FilePath, string sheetName, IEnumerable<object> data, string columnName, int insertColumnIndex, int StartRowIndex)
        {
            string cell_Value = "";
            List<object> inserValues = new List<object>();
            foreach (var item in data)
            {
                inserValues.Add(item);
            }
            IWorkbook workbook = null;
            //读取流
            using (FileStream fs = new FileStream(FilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                if (FilePath.IndexOf(".xlsx") > 0 || FilePath.IndexOf(".ett") > 0 || FilePath.IndexOf(".xlsm") > 0)
                {  //2007版本的excel
                    workbook = new XSSFWorkbook(fs);
                }
                else if (FilePath.IndexOf(".xls") > 0 || FilePath.IndexOf(".et") > 0) //2003版本的excel
                {
                    workbook = new HSSFWorkbook(fs);
                }
                bool SheetStatus = false;
                ISheet sheet = workbook.GetSheetAt(0); //获取第一个工作表
                int SheetCount = workbook.NumberOfSheets;//获取表的数量
                string[] SheetName = new string[SheetCount];//保存表的名称

                for (int i = 0; i < SheetCount; i++)
                {
                    SheetName[i] = workbook.GetSheetName(i).ToLower();
                    //获取sheet的索引
                }
                if (string.IsNullOrEmpty(sheetName))
                {
                    //没有填写默认sheet,则获取第一个sheet
                    sheet = workbook.GetSheetAt(0);
                    SheetStatus = true;
                }
                else
                {

                    if (SheetName.Contains(sheetName.ToLower()))
                    {
                        sheet = workbook.GetSheet(sheetName);
                    }
                    else
                    {
                        throw new ArgumentException(ConfigStringHelper.ExcelError_NotSheet);
                    }
                }
                bool status = false;
                var rowCount = sheet.LastRowNum + 1;//得到总行数
                if (!string.IsNullOrEmpty(columnName))
                {
                    IRow row = sheet.GetRow(0);
                    if (row == null)
                    {
                        row = sheet.CreateRow(0);
                    }
                    for (var j = 0; j < row.Cells.Count; j++)
                    {

                        ICell cell = row.GetCell(j);
                        if (cell == null)
                        {
                            cell = row.CreateCell(j);
                        }
                        var firsLine = row.GetCell(j).ToString();
                        if (firsLine == columnName)
                        {
                            if (status == false)
                            {
                                cell_Value = columnName;
                                status = true;
                                insertColumnIndex = j;
                            }
                            //记录所在的列,只记录一次如果出现多个同名列只计算第一列
                        }
                    }
                }
                if (string.IsNullOrEmpty(cell_Value) && !string.IsNullOrEmpty(columnName))
                {
                    throw new ArgumentNullException(ConfigStringHelper.ExcelError_SheetNotcolumnName);
                }
                //z这里遍历得到最大列数,因为存在第一行残缺为空的情况
                List<int> array = new List<int>();
                for (var i = 0; i < rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null)
                    {
                        row = sheet.CreateRow(i);
                    }
                    //array.Add(row.Cells.Count);//获取有效列
                    array.Add(row.LastCellNum);//获取所有列
                }
                var maxValue = array.Max();//得到最大列数
                                           //创建列头
                System.Data.DataTable dt = new System.Data.DataTable();
                List<int> columns = new List<int>();
                for (int i = 0; i < maxValue; i++)
                {
                    dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                    columns.Add(i);
                }

                //数据  
                for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
                {
                    DataRow dr = dt.NewRow();
                    foreach (int j in columns)
                    {
                        dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
                    }
                    dt.Rows.Add(dr);
                }
                int Index = 0;
                if (status == true)
                {
                    Index = insertColumnIndex;
                }
                else
                {
                    Index = insertColumnIndex - 1;
                }
                //在指定的列前插入列
                dt.Columns.Add("custom999").SetOrdinal(Index);
                int J = 0;
                int StartIndex = StartRowIndex - 1;
                for (int k = StartIndex; k < inserValues.Count() + StartIndex; k++)
                {
                    if (dt.Rows.Count + StartIndex <= k)
                    {
                        DataRow dr = dt.NewRow();
                        dt.Rows.Add(dr);
                    }
                    dt.Rows[k]["custom999"] = inserValues[J];
                    J++;
                }
                //因为使用datatable处理所以要先删除原有sheet,创建新sheet导入数据
                if (SheetStatus == true)
                {
                    //说明是使用默认sheet
                    workbook.RemoveSheetAt(workbook.GetSheetIndex(SheetName[0]));
                    sheet = workbook.CreateSheet(SheetName[0]);
                }
                else
                {
                    workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName));
                    sheet = workbook.CreateSheet(sheetName);
                }
                int WriteRowCount = 0;
                //写入数据
                for (int row = 0; row < dt.Rows.Count; row++)
                {
                    IRow newRow;
                    newRow = sheet.CreateRow(WriteRowCount);
                    for (int column = 0; column < dt.Columns.Count; column++)
                    {

                        newRow.CreateCell(column).SetCellValue(dt.Rows[row][column].ToString());

                    }
                    WriteRowCount++;
                }
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                using (FileStream fs = new FileStream(FilePath, FileMode.Create, FileAccess.Write))
                {
                    byte[] _data = ms.ToArray();
                    fs.Write(_data, 0, _data.Length);
                    fs.Flush();
                    _data = null;
                }
            }
        }

        public static DataTable ReadTable(string FilePath, string sheetName, int startIndex, int endIndex)
        {
            DataTable dt = new DataTable();
            IWorkbook workbook = null;
            using (FileStream fs = new FileStream(FilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                if (startIndex > 0)
                {
                    if (startIndex > endIndex)
                    {
                        throw new ArgumentException(ConfigStringHelper.ExcelError_ArgumentExceptionRowJudge);
                    }
                }
                else
                {
                    if (endIndex > 1)
                    {
                        throw new ArgumentException(ConfigStringHelper.ExcelError_ArgumentExceptionRowNotStartIndex);
                    }
                }
                if (FilePath.IndexOf(".xlsx") > 0 || FilePath.IndexOf(".ett") > 0 || FilePath.IndexOf(".xlsm") > 0)
                {  //2007版本的excel
                    workbook = new XSSFWorkbook(fs);
                }
                else if (FilePath.IndexOf(".xls") > 0 || FilePath.IndexOf(".et") > 0) //2003版本的excel
                {
                    workbook = new HSSFWorkbook(fs);
                }
                ISheet sheet = workbook.GetSheetAt(0);//获取工作表
                int SheetCount = workbook.NumberOfSheets;//获取表的数量
                string[] SheetName = new string[SheetCount];//保存表的名称
                for (int i = 0; i < SheetCount; i++)
                {
                    SheetName[i] = workbook.GetSheetName(i).ToLower();
                    //获取sheet的索引
                }
                if (SheetName.Contains(sheetName.ToLower()))
                {
                    sheet = workbook.GetSheet(sheetName);
                }
                else
                {
                    throw new ArgumentException(ConfigStringHelper.ExcelError_NotSheet);
                }
                var rowCount = sheet.LastRowNum + 1;//得到总行数
                //z这里遍历得到最大列数,因为存在第一行残缺为空的情况
                List<int> array = new List<int>();
                for (var i = 0; i < rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null)
                    {
                        row = sheet.CreateRow(i);
                    }
                    array.Add(row.LastCellNum);//获取所有列(包含空列以及残缺)
                }
                var maxValue = array.Max();//得到最大列数
                int Index = 0;
                int Endex = 0;
                List<int> columns = new List<int>();
                for (int i = 0; i < maxValue; i++)
                {
                    dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                    columns.Add(i);
                }
                if (startIndex > 1)
                {
                    Index = startIndex;
                }
                if (endIndex > 1)
                {
                    Endex = endIndex;
                }
                else
                {
                    Endex = sheet.LastRowNum;
                }
                //数据  
                for (int i = Index; i <= Endex; i++)
                {
                    DataRow dr = dt.NewRow();
                    foreach (int j in columns)
                    {
                        dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
                    }
                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }

    }

 

上一篇:C++学习之数组


下一篇:同一文件夹下多工作簿内多sheet合并,且末列写入工作簿名称.py