NPOI使用Excel批注导入数据

2023-12-05 15:38
文章标签 数据 excel 使用 导入 npoi 批注

本文主要是介绍NPOI使用Excel批注导入数据，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

在项目需求中，Excel导入非常普遍，于是专门编写一个工具类，使用Excel批注的强大功能实现格式灵活的Excel导入，可以应付绝大多数的Excel导入开发。分享代码如下：

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

namespace Utils
{
    public class ExcelHelper : IDisposable
    {
        private string fileName = null;
        private IWorkbook workBook = null;
        private FileStream fs = null;
        private bool disposed;

        public ExcelHelper(string fileName)
        {
            this.fileName = fileName;
            disposed = false;

        }

/// <summary>
        /// 数据导入DataTable,使用Excel批注与数据表字段关联
        /// </summary>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public DataTable Import(string sheetName)
        {
            DataTable data = new DataTable();
            ISheet sheet = null;
            int startRow = -1;
            bool flag = false;
            try
            {
                fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read);
                if (fileName.ToLower().EndsWith(".xlsx"))
                    workBook = new XSSFWorkbook(fs);
                else if (fileName.ToLower().EndsWith(".xls"))
                    workBook = new HSSFWorkbook(fs);
                if (sheetName != null)
                {
                    sheet = workBook.GetSheet(sheetName);
                    if (sheet == null)
                        sheet = workBook.GetSheetAt(0);
                }
                else
                {
                    sheet = workBook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    #region 读取Excel批注，创建DataTable列定义，并且得到记录标题行序号startRow
                    for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        flag = false;
                        for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                        {
                            ICell cell = row.GetCell(j);
                            if (cell == null || cell.CellComment == null)
                                continue;
                            IComment comment = cell.CellComment;
                            if (comment != null && !String.IsNullOrEmpty(comment.String.ToString()) && cell.CellType != CellType.Error)
                            {
                                switch (cell.CellType)
                                {
                                    case CellType.Numeric: { DataColumn col = new DataColumn(comment.String.ToString(), typeof(float)); col.Caption = j.ToString(); data.Columns.Add(col); break; }
                                    case CellType.Boolean: { DataColumn col = new DataColumn(comment.String.ToString(), typeof(bool)); col.Caption = j.ToString(); data.Columns.Add(col); break; }
                                    case CellType.String:
                                    case CellType.Formula: { DataColumn col = new DataColumn(comment.String.ToString(), typeof(string)); col.Caption = j.ToString(); data.Columns.Add(col); break; }
                                }
                                flag = true;
                            }
                        }
                        if (flag)
                        {
                            startRow = i;
                            break;
                        }
                    }
                    #endregion
                    if (startRow == -1) return null;  //Excel文件没有设置批注

                    for (int i = startRow + 1; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dataRow = data.NewRow();
                        flag = false;
                        foreach (DataColumn col in data.Columns)
                        {
                            ICell cell = row.GetCell(int.Parse(col.Caption));
                            string cellValue = string.Empty;
                            if (cell != null && cell.CellType != CellType.Blank && cell.CellType != CellType.Error)
                            {
                                switch (cell.CellType)
                                {
                                    case CellType.String: { cellValue = cell.StringCellValue; break; }
                                    case CellType.Boolean: { cellValue = cell.BooleanCellValue.ToString(); break; }
                                    case CellType.Numeric:
                                        {
                                            if (HSSFDateUtil.IsCellDateFormatted(cell))
                                                cellValue = cell.DateCellValue.ToString();
                                            else
                                                cellValue = cell.NumericCellValue.ToString();
                                            break;
                                        }
                                    case CellType.Formula:
                                        {
                                            HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workBook);
                                            cellValue = eva.Evaluate(cell).StringValue;
                                            break;
                                        }
                                    default: { cellValue = cell.ToString(); break; }
                                }
                                flag = true;
                                dataRow[col] = cellValue;
                            }
                        }
                        if (flag)
                            data.Rows.Add(dataRow);
                    }
                    return data;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                if (fs != null)
                    fs.Close();
                throw ex;
            }

        }

/// <summary>
        /// 数据导入DataTable,使用Excel批注与数据表字段关联。适用于Excel文件就是列转横后的格式。
        /// </summary>
        /// <param name="sheetName">Excel工作薄</param>
        /// <param name="splitStr">分隔字符串，标明列转横的特定字符</param>
        /// <param name="cType">标明列转横的字段类型，C=字符，D=日期，N=数字，O=其他</param>
        /// <returns></returns>
        public DataTable Import(string sheetName, string splitStr, string cType)
        {
            DataTable data = new DataTable();
            ISheet sheet = null;
            int startRow = -1;
            bool flag = false;
            string fieldName = "";
            try
            {
                fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read);
                if (fileName.ToLower().EndsWith(".xlsx"))
                    workBook = new XSSFWorkbook(fs);
                else if (fileName.ToLower().EndsWith(".xls"))
                    workBook = new HSSFWorkbook(fs);
                if (sheetName != null)
                {
                    sheet = workBook.GetSheet(sheetName);
                    if (sheet == null)
                        sheet = workBook.GetSheetAt(0);
                }
                else
                {
                    sheet = workBook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    #region 读取Excel批注，创建DataTable列定义，并且得到记录标题行序号startRow
                    for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        flag = false;
                        for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                        {
                            ICell cell = row.GetCell(j);
                            if (cell == null || cell.CellComment == null)
                                continue;
                            IComment comment = cell.CellComment;
                            if (comment != null && !String.IsNullOrEmpty(comment.String.ToString()) && cell.CellType != CellType.Error)
                            {
                                fieldName = comment.String.ToString();
                                if (fieldName.StartsWith(splitStr))
                                {
                                    if (cType == "D")
                                        fieldName = "D@" + cell.DateCellValue.ToString("yyyyMMdd");
                                    else if (cType == "N")
                                        fieldName = "N@" + cell.NumericCellValue.ToString();
                                    else
                                        fieldName = cType + "@" + cell.NumericCellValue.ToString();
                                }
                                switch (cell.CellType)
                                {
                                    case CellType.Numeric: { DataColumn col = new DataColumn(fieldName, typeof(float)); col.Caption = j.ToString(); data.Columns.Add(col); break; }
                                    case CellType.Boolean: { DataColumn col = new DataColumn(fieldName, typeof(bool)); col.Caption = j.ToString(); data.Columns.Add(col); break; }
                                    case CellType.String:
                                    case CellType.Formula: { DataColumn col = new DataColumn(fieldName, typeof(string)); col.Caption = j.ToString(); data.Columns.Add(col); break; }
                                }
                                flag = true;
                            }
                        }
                        if (flag)
                        {
                            startRow = i;
                            break;
                        }
                    }
                    #endregion
                    if (startRow == -1) return null;  //Excel文件没有设置批注

                    for (int i = startRow + 1; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dataRow = data.NewRow();
                        flag = false;
                        foreach (DataColumn col in data.Columns)
                        {
                            ICell cell = row.GetCell(int.Parse(col.Caption));
                            string cellValue = string.Empty;
                            if (cell != null && cell.CellType != CellType.Blank && cell.CellType != CellType.Error)
                            {
                                switch (cell.CellType)
                                {
                                    case CellType.String: { cellValue = cell.StringCellValue; break; }
                                    case CellType.Boolean: { cellValue = cell.BooleanCellValue.ToString(); break; }
                                    case CellType.Numeric:
                                        {
                                            if (HSSFDateUtil.IsCellDateFormatted(cell))
                                                cellValue = cell.DateCellValue.ToString();
                                            else
                                                cellValue = cell.NumericCellValue.ToString();
                                            break;
                                        }
                                    case CellType.Formula:
                                        {
                                            HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workBook);
                                            cellValue = eva.Evaluate(cell).StringValue;
                                            break;
                                        }
                                    default: { cellValue = cell.ToString(); break; }
                                }
                                flag = true;
                                dataRow[col] = cellValue;
                            }
                        }
                        if (flag)
                            data.Rows.Add(dataRow);
                    }
                    return data;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                if (fs != null)
                    fs.Close();
                throw ex;
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (fs != null)
                    fs.Close();
            }
            fs = null;
            disposed = true;
        }
    }

}

//调用方法

protected override void Import(object sender, EventArgs e)
        {
            int i = 0, sucCount = 0, errCount = 0;
            StringBuilder msg = new StringBuilder();
            DataTable tb;
            if (!srcFile.HasFile)
            {
                Alert.ShowInTop("请选择需导入的文件。", MessageBoxIcon.Warning);
                return;
            }
            string fileName = srcFile.ShortFileName;
            if (!Common.IsExcelFile(fileName))
            {
                Alert.ShowInTop("源文件格式不正确，请选取Excel文件。", MessageBoxIcon.Warning);
                return;
            }
            string path = Server.MapPath("/upload/Plan/");
            fileName = "Sch" + Common.FormatDatetime(DateTime.Now, Common.DATE_FORMAT_LONG_C) + new Random().Next(1000).ToString() + Path.GetExtension(fileName);
            if (!Common.UploadFile(srcFile, path, fileName))
            {
                Alert.ShowInTop("文件上传失败！", MessageBoxIcon.Warning);
                return;
            }
            using (ExcelHelper excel = new ExcelHelper(path + fileName))
            {
                try
                {
                    tb = excel.Import("Sheet1", "DAY@", "D");  //导入列转横后的Excel数据，需转换为主表加明细表
                }
                catch (Exception ex)
                {
                    Alert.ShowInTop(ex.Message, MessageBoxIcon.Error);
                    return;
                }
                if (tb.Columns.IndexOf("CORP_ID") < 0 || tb.Columns.IndexOf("MO") < 0)
                {
                    Alert.ShowInTop("上传文件格式不正确，操作中断！", MessageBoxIcon.Warning);
                    return;
                }
                FC.Details[0].Data = ((PlnScheduleDtlDal)FC.Details[0].Manager).GetDetailTableByNo("-1"); //获取表结构
                foreach (DataRow row in tb.Rows)
                {
                    PlnSchedule obj = (PlnSchedule)FC.Model;
                    FC.Details[0].Data.Clear(); //重置明细表数据
                    i++;
                    try
                    {
                        FC.Manager.SetDefaultValue(FC);
                        obj.TRAN_NO = DateTime.Now.ToString("yyyyMMddHHmmssffff");
                        obj.CORP_ID = row["CORP_ID"].ToString();
                        obj.MO = row["MO"].ToString();
                        obj.PROD_CODE = row["PROD_CODE"].ToString();
                        obj.PROD_NAME = row["PROD_NAME"].ToString();
                        obj.SONO = row["SONO"].ToString();
                        obj.SO_LINE = Common.ToInt(row["SO_LINE"].ToString(), 0);
                        obj.PROC_CODE = row["PROC_CODE"].ToString();
                        obj.WKC_CODE = row["WKC_CODE"].ToString();
                        obj.CAPACITY = Common.ToInt(row["CAPACITY"].ToString(), 0);
                        obj.PLAN_QTY = Common.ToInt(row["PLAN_QTY"].ToString(), 0);
                        obj.COMP_QTY = Common.ToInt(row["COMP_QTY"].ToString(), 0);
                        obj.UNIT_CODE = row["UNIT_CODE"].ToString();
                        obj.BEGIN_DATE = Common.ToDatetime(row["BEGIN_DATE"].ToString(), "2010/01/01");
                        obj.END_DATE = Common.ToDatetime(row["BEGIN_DATE"].ToString(), "2010/01/01");

                        foreach (DataColumn col in tb.Columns)
                        {
                            if (col.ColumnName.StartsWith("D@"))  //每日排程明细
                            {
                                if (Common.ToFloat(row[col].ToString(), 0) > 0)
                                {
                                    DataRow dr = FC.Details[0].Data.NewRow();
                                    FC.Details[0].Manager.SetDefaultValue(FC, dr);
                                    dr["TRAN_NO"] = obj.TRAN_NO;
                                    dr["MO"] = obj.MO;
                                    dr["PLAN_DATE"] = DateTime.ParseExact(col.ColumnName.Substring(2), "yyyyMMdd", CultureInfo.CurrentCulture);
                                    dr["QTY"] = Common.ToFloat(row[col].ToString(), 0);
                                    dr["UNIT_CODE"] = obj.UNIT_CODE;
                                    FC.Details[0].Data.Rows.Add(dr);
                                    if (FC.Details[0].EditList.IndexOf(Convert.ToInt32(dr["ID"])) < 0)
                                        FC.Details[0].EditList.Add(Convert.ToInt32(dr["ID"].ToString()));
                                }
                            }
                        }

                        if (String.IsNullOrEmpty(obj.CORP_ID) || String.IsNullOrEmpty(obj.WKC_CODE) || String.IsNullOrEmpty(obj.PROD_CODE) || (obj.PLAN_QTY == 0) ||
                            String.IsNullOrEmpty(obj.PROD_NAME))
                        {
                            errCount++;
                            msg.Append("第" + i.ToString() + "行数据不完整，不能导入。<br/>");
                            continue;
                        }
                        if (FormControl.Update(FC, TypeInfo.Actions.INSERT))
                            sucCount++;
                        else
                            errCount++;
                    }
                    catch (Exception ex)
                    {
                        errCount++;
                        msg.Append("第" + i.ToString() + "行导入异常：" + ex.Message + "<br/>");
                    }
                }
            }
            Alert.ShowInTop("本次成功导入[" + sucCount.ToString() + "]条记录，失败[" + errCount.ToString() + "]。" + (msg.ToString().Trim().Length > 0 ? "<br/>异常详情：" + msg.ToString() : ""), MessageBoxIcon.Information);
            Search(null, null);
        }




这篇关于NPOI使用Excel批注导入数据的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/458149。

相关文章

Spring StateMachine实现状态机使用示例详解

Spring StateMachine实现状态机使用示例详解

《SpringStateMachine实现状态机使用示例详解》本文介绍SpringStateMachine实现状态机的步骤,包括依赖导入、枚举定义、状态转移规则配置、上下文管理及服务调用示例,重点解... 目录什么是状态机使用示例什么是状态机状态机是计算机科学中的​​核心建模工具​​，用于描述对象在其生命
阅读更多...
使用Python删除Excel中的行列和单元格示例详解

使用Python删除Excel中的行列和单元格示例详解

《使用Python删除Excel中的行列和单元格示例详解》在处理Excel数据时,删除不需要的行、列或单元格是一项常见且必要的操作,本文将使用Python脚本实现对Excel表格的高效自动化处理,感兴... 目录开发环境准备使用 python 删除 Excphpel 表格中的行删除特定行删除空白行删除含指定
阅读更多...
深入理解Go语言中二维切片的使用

深入理解Go语言中二维切片的使用

《深入理解Go语言中二维切片的使用》本文深入讲解了Go语言中二维切片的概念与应用,用于表示矩阵、表格等二维数据结构,文中通过示例代码介绍的非常详细,需要的朋友们下面随着小编来一起学习学习吧... 目录引言二维切片的基本概念定义创建二维切片二维切片的操作访问元素修改元素遍历二维切片二维切片的动态调整追加行动态
阅读更多...
prometheus如何使用pushgateway监控网路丢包

prometheus如何使用pushgateway监控网路丢包

《prometheus如何使用pushgateway监控网路丢包》：本文主要介绍prometheus如何使用pushgateway监控网路丢包问题,具有很好的参考价值,希望对大家有所帮助,如有错误... 目录监控网路丢包脚本数据图表总结监控网路丢包脚本[root@gtcq-gt-monitor-prome
阅读更多...
SpringBoot+EasyExcel实现自定义复杂样式导入导出

SpringBoot+EasyExcel实现自定义复杂样式导入导出

《SpringBoot+EasyExcel实现自定义复杂样式导入导出》这篇文章主要为大家详细介绍了SpringBoot如何结果EasyExcel实现自定义复杂样式导入导出功能,文中的示例代码讲解详细,... 目录安装处理自定义导出复杂场景1、列不固定，动态列2、动态下拉3、自定义锁定行/列，添加密码4、合并
阅读更多...
Python通用唯一标识符模块uuid使用案例详解

Python通用唯一标识符模块uuid使用案例详解

《Python通用唯一标识符模块uuid使用案例详解》Pythonuuid模块用于生成128位全局唯一标识符,支持UUID1-5版本,适用于分布式系统、数据库主键等场景,需注意隐私、碰撞概率及存储优... 目录简介核心功能1. UUID版本2. UUID属性3. 命名空间使用场景1. 生成唯一标识符2. 数
阅读更多...
SpringBoot中如何使用Assert进行断言校验

SpringBoot中如何使用Assert进行断言校验

《SpringBoot中如何使用Assert进行断言校验》Java提供了内置的assert机制,而Spring框架也提供了更强大的Assert工具类来帮助开发者进行参数校验和状态检查,下... 目录前言一、Java 原生assert简介1.1 使用方式1.2 示例代码1.3 优缺点分析二、Spring Fr
阅读更多...
Android kotlin中 Channel 和 Flow 的区别和选择使用场景分析

Android kotlin中 Channel 和 Flow 的区别和选择使用场景分析

《Androidkotlin中Channel和Flow的区别和选择使用场景分析》Kotlin协程中,Flow是冷数据流,按需触发,适合响应式数据处理；Channel是热数据流,持续发送,支持... 目录一、基本概念界定FlowChannel二、核心特性对比数据生产触发条件生产与消费的关系背压处理机制生命周期
阅读更多...
java使用protobuf-maven-plugin的插件编译proto文件详解

java使用protobuf-maven-plugin的插件编译proto文件详解

《java使用protobuf-maven-plugin的插件编译proto文件详解》：本文主要介绍java使用protobuf-maven-plugin的插件编译proto文件,具有很好的参考价... 目录protobuf文件作为数据传输和存储的协议主要介绍在Java使用maven编译proto文件的插件
阅读更多...
SpringBoot线程池配置使用示例详解

SpringBoot线程池配置使用示例详解

《SpringBoot线程池配置使用示例详解》SpringBoot集成@Async注解,支持线程池参数配置（核心数、队列容量、拒绝策略等）及生命周期管理,结合监控与任务装饰器,提升异步处理效率与系统... 目录一、核心特性二、添加依赖三、参数详解四、配置线程池五、应用实践代码说明拒绝策略（Rejected
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2