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获取中国节假日数据记录入JSON文件

Python获取中国节假日数据记录入JSON文件

《Python获取中国节假日数据记录入JSON文件》项目系统内置的日历应用为了提升用户体验,特别设置了在调休日期显示“休”的UI图标功能,那么问题是这些调休数据从哪里来呢？我尝试一种更为智能的方法：P... 目录节假日数据获取存入jsON文件节假日数据读取封装完整代码项目系统内置的日历应用为了提升用户体验，
阅读更多...
vue使用docxtemplater导出word

vue使用docxtemplater导出word

《vue使用docxtemplater导出word》docxtemplater是一种邮件合并工具,以编程方式使用并处理条件、循环,并且可以扩展以插入任何内容,下面我们来看看如何使用docxtempl... 目录docxtemplatervue使用docxtemplater导出word安装常用语法 封装导出方
阅读更多...
Linux换行符的使用方法详解

Linux换行符的使用方法详解

《Linux换行符的使用方法详解》本文介绍了Linux中常用的换行符LF及其在文件中的表示,展示了如何使用sed命令替换换行符,并列举了与换行符处理相关的Linux命令,通过代码讲解的非常详细,需要的... 目录简介检测文件中的换行符使用 cat -A 查看换行符使用 od -c 检查字符换行符格式转换将
阅读更多...
使用Jackson进行JSON生成与解析的新手指南

使用Jackson进行JSON生成与解析的新手指南

《使用Jackson进行JSON生成与解析的新手指南》这篇文章主要为大家详细介绍了如何使用Jackson进行JSON生成与解析处理,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录1. 核心依赖2. 基础用法2.1 对象转 jsON（序列化）2.2 JSON 转对象（反序列化）3.
阅读更多...
使用Python实现快速搭建本地HTTP服务器

使用Python实现快速搭建本地HTTP服务器

《使用Python实现快速搭建本地HTTP服务器》：本文主要介绍如何使用Python快速搭建本地HTTP服务器,轻松实现一键HTTP文件共享,同时结合二维码技术,让访问更简单,感兴趣的小伙伴可以了... 目录1. 概述2. 快速搭建 HTTP 文件共享服务2.1 核心思路2.2 代码实现2.3 代码解读3.
阅读更多...
Elasticsearch 在 Java 中的使用教程

Elasticsearch 在 Java 中的使用教程

《Elasticsearch在Java中的使用教程》Elasticsearch是一个分布式搜索和分析引擎,基于ApacheLucene构建,能够实现实时数据的存储、搜索、和分析,它广泛应用于全文... 目录1. Elasticsearch 简介2. 环境准备2.1 安装 Elasticsearch2.2 J
阅读更多...
使用C#代码在PDF文档中添加、删除和替换图片

使用C#代码在PDF文档中添加、删除和替换图片

《使用C#代码在PDF文档中添加、删除和替换图片》在当今数字化文档处理场景中,动态操作PDF文档中的图像已成为企业级应用开发的核心需求之一,本文将介绍如何在.NET平台使用C#代码在PDF文档中添加、... 目录引言用C#添加图片到PDF文档用C#删除PDF文档中的图片用C#替换PDF文档中的图片引言在当
阅读更多...
Java中List的contains()方法的使用小结

Java中List的contains()方法的使用小结

《Java中List的contains()方法的使用小结》List的contains()方法用于检查列表中是否包含指定的元素,借助equals()方法进行判断,下面就来介绍Java中List的c... 目录详细展开1. 方法签名2. 工作原理3. 使用示例4. 注意事项总结结论：List 的 contain
阅读更多...
C#使用SQLite进行大数据量高效处理的代码示例

C#使用SQLite进行大数据量高效处理的代码示例

《C#使用SQLite进行大数据量高效处理的代码示例》在软件开发中,高效处理大数据量是一个常见且具有挑战性的任务,SQLite因其零配置、嵌入式、跨平台的特性,成为许多开发者的首选数据库,本文将深入探... 目录前言准备工作数据实体核心技术批量插入：从乌龟到猎豹的蜕变分页查询：加载百万数据异步处理：拒绝界面
阅读更多...
Android中Dialog的使用详解

Android中Dialog的使用详解

《Android中Dialog的使用详解》Dialog（对话框）是Android中常用的UI组件,用于临时显示重要信息或获取用户输入,本文给大家介绍Android中Dialog的使用,感兴趣的朋友一起... 目录android中Dialog的使用详解1. 基本Dialog类型1.1 AlertDialog（
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2