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。

相关文章

JavaScript中的reduce方法执行过程、使用场景及进阶用法

JavaScript中的reduce方法执行过程、使用场景及进阶用法

《JavaScript中的reduce方法执行过程、使用场景及进阶用法》：本文主要介绍JavaScript中的reduce方法执行过程、使用场景及进阶用法的相关资料,reduce是JavaScri... 目录1. 什么是reduce2. reduce语法2.1 语法2.2 参数说明3. reduce执行过程
阅读更多...
如何使用Java实现请求deepseek

如何使用Java实现请求deepseek

《如何使用Java实现请求deepseek》这篇文章主要为大家详细介绍了如何使用Java实现请求deepseek功能,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录1.deepseek的api创建2.Java实现请求deepseek2.1 pom文件2.2 json转化文件2.2
阅读更多...
python使用fastapi实现多语言国际化的操作指南

python使用fastapi实现多语言国际化的操作指南

《python使用fastapi实现多语言国际化的操作指南》本文介绍了使用Python和FastAPI实现多语言国际化的操作指南,包括多语言架构技术栈、翻译管理、前端本地化、语言切换机制以及常见陷阱和... 目录多语言国际化实现指南项目多语言架构技术栈目录结构翻译工作流1. 翻译数据存储2. 翻译生成脚本
阅读更多...
C++ Primer 多维数组的使用

C++ Primer 多维数组的使用

《C++Primer多维数组的使用》本文主要介绍了多维数组在C++语言中的定义、初始化、下标引用以及使用范围for语句处理多维数组的方法,具有一定的参考价值,感兴趣的可以了解一下... 目录多维数组多维数组的初始化多维数组的下标引用使用范围for语句处理多维数组指针和多维数组多维数组严格来说,C++语言没
阅读更多...
在 Spring Boot 中使用 @Autowired和 @Bean注解的示例详解

在 Spring Boot 中使用 @Autowired和 @Bean注解的示例详解

《在SpringBoot中使用@Autowired和@Bean注解的示例详解》本文通过一个示例演示了如何在SpringBoot中使用@Autowired和@Bean注解进行依赖注入和Bean... 目录在 Spring Boot 中使用 @Autowired 和 @Bean 注解示例背景1. 定义 Stud
阅读更多...
使用 sql-research-assistant进行 SQL 数据库研究的实战指南(代码实现演示)

使用 sql-research-assistant进行 SQL 数据库研究的实战指南(代码实现演示)

《使用sql-research-assistant进行SQL数据库研究的实战指南(代码实现演示)》本文介绍了sql-research-assistant工具,该工具基于LangChain框架,集... 目录技术背景介绍核心原理解析代码实现演示安装和配置项目集成LangSmith 配置（可选）启动服务应用场景
阅读更多...
使用Python快速实现链接转word文档

使用Python快速实现链接转word文档

《使用Python快速实现链接转word文档》这篇文章主要为大家详细介绍了如何使用Python快速实现链接转word文档功能,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 演示代码展示from newspaper import Articlefrom docx import
阅读更多...
oracle DBMS_SQL.PARSE的使用方法和示例

oracle DBMS_SQL.PARSE的使用方法和示例

《oracleDBMS_SQL.PARSE的使用方法和示例》DBMS_SQL是Oracle数据库中的一个强大包,用于动态构建和执行SQL语句,DBMS_SQL.PARSE过程解析SQL语句或PL/S... 目录语法示例注意事项DBMS_SQL 是 oracle 数据库中的一个强大包，它允许动态地构建和执行
阅读更多...
SpringBoot中使用 ThreadLocal 进行多线程上下文管理及注意事项小结

SpringBoot中使用 ThreadLocal 进行多线程上下文管理及注意事项小结

《SpringBoot中使用ThreadLocal进行多线程上下文管理及注意事项小结》本文详细介绍了ThreadLocal的原理、使用场景和示例代码,并在SpringBoot中使用ThreadLo... 目录前言技术积累1.什么是 ThreadLocal2. ThreadLocal 的原理2.1 线程隔离2
阅读更多...
Python itertools中accumulate函数用法及使用运用详细讲解

Python itertools中accumulate函数用法及使用运用详细讲解

《Pythonitertools中accumulate函数用法及使用运用详细讲解》：本文主要介绍Python的itertools库中的accumulate函数,该函数可以计算累积和或通过指定函数... 目录1.1前言：1.2定义：1.3衍生用法：1.3Leetcode的实际运用:总结 1.1前言：本文将详
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2