DEMOMySql/MicroServices/Business/Business.Application/Sqe/SqeExcelService.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Business;
using Business.Core.Utilities;
using Business.Dto;
using Business.ResourceExamineManagement;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Volo.Abp.Application.Services;
using Volo.Abp.MultiTenancy;

namespace Business.VSM
{
    /// <summary>
    /// Sqe数据导入接口实现
    /// </summary>
    public class SqeExcelService : ApplicationService, ISqeExcelService
    {
        private readonly ICurrentTenant _currentTenant;
        //LoggerHelper WLLog;
        public SqeExcelService()
        {
           // WLLog = new LoggerHelper();
        }

        /// <summary>
        /// 导入Excel文件的sheetName的sheet页面到数据表
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public DataTable ImportExcelSheetToDataTable_test(string filePath, string sheetName)
        {
            FileStream file = null;
            DataTable dt = new DataTable();
            int index = 0;
            try
            {
                file = new FileStream(filePath, FileMode.Open, FileAccess.Read);

                //打开Excel对象
                IWorkbook workbook;
                IRow row;
                string fileExt = Path.GetExtension(filePath).ToLower();
                workbook = null;
                if (fileExt == ".xlsx")
                {
                    workbook = new XSSFWorkbook(file);
                }
                else
                {
                    if (fileExt == ".xls")
                    {
                        workbook = new HSSFWorkbook(file);
                    }
                    else
                    {
                        workbook = null;
                    }
                }
                if (workbook == null) { return null; }

                //Excel的Sheet对象
                NPOI.SS.UserModel.ISheet sheet = workbook.GetSheet(sheetName);

                //获取Sheet的所有的行
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                //int colCountMax = 0;

                dt.TableName = sheetName;

                //判断是否拥有首行
                bool firstLine = true;
                //if (!haveColumnName)
                //{
                //    firstLine = false;
                //}

                bool bDate = false;
                while (rows.MoveNext())
                {
                    index++;

                    bDate = false;
                    if (fileExt == ".xlsx")
                    {
                        row = (XSSFRow)rows.Current;
                    }
                    else
                    {
                        row = (HSSFRow)rows.Current;
                    }
                    if (!firstLine)
                    {
                        //生成表结构
                        //仅当首次调用时会进行循环生成
                        for (int i = dt.Columns.Count; i < row.Cells.Count; i++)
                        {
                            dt.Columns.Add(Convert.ToChar(((int)'A') + i).ToString());
                        }
                    }
                    else
                    {
                        //依据首行进行字段生成
                        for (int i = dt.Columns.Count; i < row.Cells.Count; i++)
                        {
                            dt.Columns.Add(row.Cells[i].ToString());
                        }

                    }

                    //没有首行，生成数据
                    if (!firstLine)
                    {
                        DataRow dr = dt.NewRow();

                        for (int i = 0; i < row.LastCellNum - 1; i++)
                        {
                            NPOI.SS.UserModel.ICell cell = row.GetCell(i);
                            String s = "";
                            if (cell != null)
                            {
                                switch (cell.CellType)
                                {
                                    case NPOI.SS.UserModel.CellType.Unknown:
                                        break;

                                    case NPOI.SS.UserModel.CellType.Numeric:
                                        if (HSSFDateUtil.IsCellDateFormatted(cell))
                                        {
                                            if (index < 6)
                                            {
                                                s = cell.DateCellValue.ToString();
                                            }
                                            else
                                            {
                                                s = cell.ToString();
                                            }
                                        }
                                        else
                                        {
                                            s = cell.NumericCellValue.ToString();
                                        }
                                        break;

                                    case NPOI.SS.UserModel.CellType.String:
                                        s = cell.StringCellValue;
                                        break;

                                    case NPOI.SS.UserModel.CellType.Formula:
                                        if (i == 0 || i == 1)
                                        {
                                            s = cell.StringCellValue;
                                        }
                                        else
                                        {
                                            try
                                            {
                                                if (cell.CellFormula == "序号")
                                                {
                                                    s = cell.ToString();
                                                }
                                                else
                                                {
                                                    s = cell.NumericCellValue != null ? cell.NumericCellValue.ToString() : null;
                                                }
                                            }
                                            catch (Exception ex)
                                            {
                                                //WLLog.Error("Excel导入问题：", ex);
                                                new NLogHelper("SqeExcelService").WriteLog("ImportExcelSheetToDataTable", "Excel导入问题" + ex.Message, _currentTenant.Id.ToString());
                                            }
                                        }
                                        break;

                                    case NPOI.SS.UserModel.CellType.Blank:
                                        if (bDate)
                                        {
                                            //对于日期的特殊处理
                                            s = dr[i - 1].ToString();
                                        }
                                        else
                                        {
                                            s = cell.StringCellValue;
                                        }
                                        break;

                                    case NPOI.SS.UserModel.CellType.Boolean:
                                        break;

                                    case NPOI.SS.UserModel.CellType.Error:
                                        break;

                                    default:
                                        break;
                                }
                            }
                            dr[i] = s;
                        }
                        dt.Rows.Add(dr);
                    }
                    firstLine = false;

                }
                return dt;
            }
            catch (Exception ex)
            {
                //WLLog.Error("Excel导入问题：", ex);
                new NLogHelper("SqeExcelService").WriteLog("ImportExcelSheetToDataTable", "Excel导入问题" + ex.Message, _currentTenant.Id.ToString());
            }
            finally
            {
                if (file != null)
                {
                    file.Close();
                }
            }
            return dt;
        }

        public DataTable ImportExcelSheetToDataTable_process(string filePath, string sheetName)
        {
            FileStream file = null;
            DataTable dt = new DataTable();
            int index = 0;
            try
            {
                file = new FileStream(filePath, FileMode.Open, FileAccess.Read);

                //打开Excel对象
                IWorkbook workbook;
                IRow row;
                string fileExt = Path.GetExtension(filePath).ToLower();
                workbook = null;
                if (fileExt == ".xlsx")
                {
                    workbook = new XSSFWorkbook(file);
                }
                else
                {
                    if (fileExt == ".xls")
                    {
                        workbook = new HSSFWorkbook(file);
                    }
                    else
                    {
                        workbook = null;
                    }
                }
                if (workbook == null) { return null; }

                //Excel的Sheet对象
                NPOI.SS.UserModel.ISheet sheet = workbook.GetSheet(sheetName);

                //获取Sheet的所有的行
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                //int colCountMax = 0;

                dt.TableName = sheetName;

                //判断是否拥有首行
                bool firstLine = true;
                //if (!haveColumnName)
                //{
                //    firstLine = false;
                //}

                bool bDate = false;
                while (rows.MoveNext())
                {
                    index++;

                    bDate = false;
                    if (fileExt == ".xlsx")
                    {
                        row = (XSSFRow)rows.Current;
                    }
                    else
                    {
                        row = (HSSFRow)rows.Current;
                    }
                    if (!firstLine)
                    {
                        //生成表结构
                        //仅当首次调用时会进行循环生成
                        for (int i = dt.Columns.Count; i < row.Cells.Count; i++)
                        {
                            dt.Columns.Add(Convert.ToChar(((int)'A') + i).ToString());
                        }
                    }
                    else
                    {
                        //依据首行进行字段生成
                        for (int i = dt.Columns.Count; i < row.Cells.Count; i++)
                        {
                            dt.Columns.Add(row.Cells[i].ToString());
                        }

                    }

                    //没有首行，生成数据
                    if (!firstLine)
                    {
                        DataRow dr = dt.NewRow();

                        for (int i = 0; i < row.LastCellNum - 1; i++)
                        {
                            NPOI.SS.UserModel.ICell cell = row.GetCell(i);
                            String s = "";
                            if (cell != null)
                            {
                                switch (cell.CellType)
                                {
                                    case NPOI.SS.UserModel.CellType.Unknown:
                                        break;

                                    case NPOI.SS.UserModel.CellType.Numeric:
                                        if (HSSFDateUtil.IsCellDateFormatted(cell))
                                        {
                                            if (index < 6)
                                            {
                                                s = cell.DateCellValue.ToString();
                                            }
                                            else
                                            {
                                                s = cell.ToString();
                                            }
                                        }
                                        else
                                        {
                                            s = cell.NumericCellValue.ToString();
                                        }
                                        break;

                                    case NPOI.SS.UserModel.CellType.String:
                                        s = cell.StringCellValue;
                                        break;

                                    case NPOI.SS.UserModel.CellType.Formula:
                                        try
                                        {
                                            if (cell.CellFormula == "序号")
                                            {
                                                s = cell.ToString();
                                            }
                                            else
                                            {
                                                s = cell.NumericCellValue != null ? cell.NumericCellValue.ToString() : null;
                                            }
                                        }
                                        catch (Exception ex)
                                        {
                                            //WLLog.Error("Excel导入问题：", ex);
                                            new NLogHelper("SqeExcelService").WriteLog("ImportExcelSheetToDataTable", "Excel导入问题" + ex.Message, _currentTenant.Id.ToString());
                                        }

                                        break;

                                    case NPOI.SS.UserModel.CellType.Blank:
                                        if (bDate)
                                        {
                                            //对于日期的特殊处理
                                            s = dr[i - 1].ToString();
                                        }
                                        else
                                        {
                                            s = cell.StringCellValue;
                                        }
                                        break;

                                    case NPOI.SS.UserModel.CellType.Boolean:
                                        break;

                                    case NPOI.SS.UserModel.CellType.Error:
                                        break;

                                    default:
                                        break;
                                }
                            }
                            dr[i] = s;
                        }
                        dt.Rows.Add(dr);
                    }
                    firstLine = false;

                }
                return dt;
            }
            catch (Exception ex)
            {
                //WLLog.Error("Excel导入问题：", ex);
                new NLogHelper("SqeExcelService").WriteLog("ImportExcelSheetToDataTable", "Excel导入问题" + ex.Message, _currentTenant.Id.ToString());
            }
            finally
            {
                if (file != null)
                {
                    file.Close();
                }
            }
            return dt;
        }



        /// <summary>
        /// 获取Excel文件Sheet名称列表，与Jet引擎不一致，获取的时候没有$多余字符。
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public List<String> GetSheetNames(string filePath)
        {
            List<String> tables = null;
            FileStream file = null;
            try
            {
                tables = new List<string>();

                file = new FileStream(filePath, FileMode.Open, FileAccess.Read);

                HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);

                int i = 0;
                while (true)
                {
                    try
                    {
                        String s = hssfworkbook.GetSheetName(i);
                        if (s != null)
                        {
                            tables.Add(s);
                            i++;
                        }
                        else
                        {
                            break;
                        }
                    }
                    catch (Exception ex)
                    {
                        break;
                    }
                }
            }
            catch (Exception ex)
            {
                //WLLog.Error("Excel导入问题：", ex);
                new NLogHelper("SqeExcelService").WriteLog("ImportExcelSheetToDataTable", "Excel导入问题"+ex.Message, _currentTenant.Id.ToString());
            }
            finally
            {
                if (file != null)
                {
                    file.Close();
                }
            }

            return tables;
        }

    }
}