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。

相关文章

使用Java解析JSON数据并提取特定字段的实现步骤(以提取mailNo为例)

使用Java解析JSON数据并提取特定字段的实现步骤(以提取mailNo为例)

《使用Java解析JSON数据并提取特定字段的实现步骤(以提取mailNo为例)》在现代软件开发中,处理JSON数据是一项非常常见的任务,无论是从API接口获取数据,还是将数据存储为JSON格式,解析... 目录1. 背景介绍1.1 jsON简介1.2 实际案例2. 准备工作2.1 环境搭建2.1.1 添加
阅读更多...
MySQL中删除重复数据SQL的三种写法

MySQL中删除重复数据SQL的三种写法

《MySQL中删除重复数据SQL的三种写法》：本文主要介绍MySQL中删除重复数据SQL的三种写法,文中通过代码示例讲解的非常详细,对大家的学习或工作有一定的帮助,需要的朋友可以参考下... 目录方法一：使用 left join + 子查询删除重复数据(推荐)方法二：创建临时表(需分多步执行，逻辑清晰，但会
阅读更多...
Java实现任务管理器性能网络监控数据的方法详解

Java实现任务管理器性能网络监控数据的方法详解

《Java实现任务管理器性能网络监控数据的方法详解》在现代操作系统中,任务管理器是一个非常重要的工具,用于监控和管理计算机的运行状态,包括CPU使用率、内存占用等,对于开发者和系统管理员来说,了解这些... 目录引言一、背景知识二、准备工作1. Maven依赖2. Gradle依赖三、代码实现四、代码详解五
阅读更多...
如何使用celery进行异步处理和定时任务(django)

如何使用celery进行异步处理和定时任务(django)

《如何使用celery进行异步处理和定时任务(django)》文章介绍了Celery的基本概念、安装方法、如何使用Celery进行异步任务处理以及如何设置定时任务,通过Celery,可以在Web应用中... 目录一、celery的作用二、安装celery三、使用celery 异步执行任务四、使用celery
阅读更多...
使用Python绘制蛇年春节祝福艺术图

使用Python绘制蛇年春节祝福艺术图

《使用Python绘制蛇年春节祝福艺术图》：本文主要介绍如何使用Python的Matplotlib库绘制一幅富有创意的“蛇年有福”艺术图,这幅图结合了数字,蛇形,花朵等装饰,需要的可以参考下... 目录1. 绘图的基本概念2. 准备工作3. 实现代码解析3.1 设置绘图画布3.2 绘制数字“2025”3.3
阅读更多...
详谈redis跟数据库的数据同步问题

详谈redis跟数据库的数据同步问题

《详谈redis跟数据库的数据同步问题》文章讨论了在Redis和数据库数据一致性问题上的解决方案,主要比较了先更新Redis缓存再更新数据库和先更新数据库再更新Redis缓存两种方案,文章指出,删除R... 目录一、Redis 数据库数据一致性的解决方案1.1、更新Redis缓存、删除Redis缓存的区别二
阅读更多...
Jsoncpp的安装与使用方式

Jsoncpp的安装与使用方式

《Jsoncpp的安装与使用方式》JsonCpp是一个用于解析和生成JSON数据的C++库,它支持解析JSON文件或字符串到C++对象,以及将C++对象序列化回JSON格式,安装JsonCpp可以通过... 目录安装jsoncppJsoncpp的使用Value类构造函数检测保存的数据类型提取数据对json数
阅读更多...
Redis事务与数据持久化方式

Redis事务与数据持久化方式

《Redis事务与数据持久化方式》该文档主要介绍了Redis事务和持久化机制,事务通过将多个命令打包执行,而持久化则通过快照（RDB）和追加式文件（AOF）两种方式将内存数据保存到磁盘,以防止数据丢失... 目录一、Redis 事务1.1 事务本质1.2 数据库事务与redis事务1.2.1 数据库事务1.
阅读更多...
python使用watchdog实现文件资源监控

python使用watchdog实现文件资源监控

《python使用watchdog实现文件资源监控》watchdog支持跨平台文件资源监控,可以检测指定文件夹下文件及文件夹变动,下面我们来看看Python如何使用watchdog实现文件资源监控吧... python文件监控库watchdogs简介随着Python在各种应用领域中的广泛使用，其生态环境也
阅读更多...
Python中构建终端应用界面利器Blessed模块的使用

Python中构建终端应用界面利器Blessed模块的使用

《Python中构建终端应用界面利器Blessed模块的使用》Blessed库作为一个轻量级且功能强大的解决方案,开始在开发者中赢得口碑,今天,我们就一起来探索一下它是如何让终端UI开发变得轻松而高... 目录一、安装与配置：简单、快速、无障碍二、基本功能：从彩色文本到动态交互1. 显示基本内容2. 创建链
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2