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。

相关文章

python使用库爬取m3u8文件的示例

python使用库爬取m3u8文件的示例

《python使用库爬取m3u8文件的示例》本文主要介绍了python使用库爬取m3u8文件的示例,可以使用requests、m3u8、ffmpeg等库,实现获取、解析、下载视频片段并合并等步骤,具有... 目录一、准备工作二、获取m3u8文件内容三、解析m3u8文件四、下载视频片段五、合并视频片段六、错误
阅读更多...
gitlab安装及邮箱配置和常用使用方式

gitlab安装及邮箱配置和常用使用方式

《gitlab安装及邮箱配置和常用使用方式》：本文主要介绍gitlab安装及邮箱配置和常用使用方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1.安装GitLab2.配置GitLab邮件服务3.GitLab的账号注册邮箱验证及其分组4.gitlab分支和标签的
阅读更多...
SpringBoot3应用中集成和使用Spring Retry的实践记录

SpringBoot3应用中集成和使用Spring Retry的实践记录

《SpringBoot3应用中集成和使用SpringRetry的实践记录》SpringRetry为SpringBoot3提供重试机制,支持注解和编程式两种方式,可配置重试策略与监听器,适用于临时性故... 目录1. 简介2. 环境准备3. 使用方式3.1 注解方式 基础使用自定义重试策略失败恢复机制注意事项
阅读更多...
nginx启动命令和默认配置文件的使用

nginx启动命令和默认配置文件的使用

《nginx启动命令和默认配置文件的使用》：本文主要介绍nginx启动命令和默认配置文件的使用,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录常见命令nginx.conf配置文件location匹配规则图片服务器总结常见命令# 默认配置文件启动./nginx
阅读更多...
在Windows上使用qemu安装ubuntu24.04服务器的详细指南

在Windows上使用qemu安装ubuntu24.04服务器的详细指南

《在Windows上使用qemu安装ubuntu24.04服务器的详细指南》本文介绍了在Windows上使用QEMU安装Ubuntu24.04的全流程：安装QEMU、准备ISO镜像、创建虚拟磁盘、配置... 目录1. 安装QEMU环境2. 准备Ubuntu 24.04镜像3. 启动QEMU安装Ubuntu4
阅读更多...
使用Python和OpenCV库实现实时颜色识别系统

使用Python和OpenCV库实现实时颜色识别系统

《使用Python和OpenCV库实现实时颜色识别系统》：本文主要介绍使用Python和OpenCV库实现的实时颜色识别系统,这个系统能够通过摄像头捕捉视频流,并在视频中指定区域内识别主要颜色（红... 目录一、引言二、系统概述三、代码解析1. 导入库2. 颜色识别函数3. 主程序循环四、HSV色彩空间详解
阅读更多...
Windows下C++使用SQLitede的操作过程

Windows下C++使用SQLitede的操作过程

《Windows下C++使用SQLitede的操作过程》本文介绍了Windows下C++使用SQLite的安装配置、CppSQLite库封装优势、核心功能（如数据库连接、事务管理）、跨平台支持及性能优... 目录Windows下C++使用SQLite1、安装2、代码示例CppSQLite：C++轻松操作SQ
阅读更多...
SQL Server修改数据库名及物理数据文件名操作步骤

SQL Server修改数据库名及物理数据文件名操作步骤

《SQLServer修改数据库名及物理数据文件名操作步骤》在SQLServer中重命名数据库是一个常见的操作,但需要确保用户具有足够的权限来执行此操作,：本文主要介绍SQLServer修改数据... 目录一、背景介绍二、操作步骤2.1 设置为单用户模式（断开连接）2.2 修改数据库名称2.3 查找逻辑文件名
阅读更多...
Python常用命令提示符使用方法详解

Python常用命令提示符使用方法详解

《Python常用命令提示符使用方法详解》在学习python的过程中,我们需要用到命令提示符(CMD)进行环境的配置,：本文主要介绍Python常用命令提示符使用方法的相关资料,文中通过代码介绍的... 目录一、python环境基础命令【Windows】1、检查Python是否安装2、 查看Python的安
阅读更多...
Python并行处理实战之如何使用ProcessPoolExecutor加速计算

Python并行处理实战之如何使用ProcessPoolExecutor加速计算

《Python并行处理实战之如何使用ProcessPoolExecutor加速计算》Python提供了多种并行处理的方式,其中concurrent.futures模块的ProcessPoolExecu... 目录简介完整代码示例代码解释1. 导入必要的模块2. 定义处理函数3. 主函数4. 生成数字列表5.
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2