C#实现Excel合并单元格数据导入数据集

简介: C#实现Excel合并单元格数据导入数据集

功能需求

Excel里的worksheet表格导入到DataSet里,是项目应用里常用的一种操作。一般情况下,worksheet是一个标准的二维数组,如下图:

我们可以效仿 MS SQL SERVER 的一些基本导入选项,如首行是否包含数据,要导入哪个Sheet?还是遍历Sheets?

实际的情况,客户经常会提供一些合并单元格的Excel表格,如下图中的“所在部门名称”列:

再畅想一下,假设有跨列的情况如下:

解决导入,一种方法,是让客户进行单元格拆分或技术服务人员进行拆分后再导入。另一种就是我们要继续完善应用,处理实现合并单元格的自动化处理。

Excel与DataSet的映射关系

下图是 Excel 与 DataSet 的映射关系图:

1、Excel应用的Workbook对象与 DataSet 同为容器对象

2、Worksheets和Tables均代表各自的表集合

3、Worksheet与Table进行对应,产生和导入实际的数据

范例运行环境

操作系统: Windows Server 2019 DataCenter

操作系统上安装 Office Excel 2016

.net版本: .netFramework4.7.1 或以上

开发工具:VS2019  C#

Excel DCOM 配置

请参考我的文章《C# 读取Word表格到DataSet》有对Office DCOM详细配置介绍,这里不再赘述,Excel的对应配置名称如下图所示:

 

设计实现

组件库引入

方法设计

设计  object[] ExcelAsDataSet(string _filename,bool hastitle,string startaddress,string endaddress) 方法

返回值

方法返回object数组,共包括两个object对象,如果成功转化则 object[0] 存储 DataSet对象,否则为 null。如果不成功则 object[1] 存储string 错误信息对象,可根据object[1].ToString()!="" 来判断是否转化成功。

参数设计
  1. string _filename:Excel 数据源文件路径
  2. bool hastitle: 是否包含标题,如果设置为true,则表示首行数据为列名称定义
  3. string startaddress:可指定有效的起始单元格地址,不设置则默认为“A1”(即第一个单元格)
  4. string endaddress:可指定有效的截止单元格地址,不设置则默认为最后一个有值单元格(即XlCellType.xlCellTypeLastCell 枚举)

   通过3、4参数的定义,可以定义出有效的导入矩形区域。

打开数据源并计算Sheets

      object[] rv=new object[2];
      rv[0]=null;
      rv[1]="";
 
      //创建一个名为ExcelApp的组件对象
//      ExcelApplication excel = new ExcelApplication();
            Excel.Application excel = new Excel.Application();
      excel.DisplayAlerts=false;
      excel.AskToUpdateLinks=false;
      Excel.Workbook xb=excel.Workbooks.Add(_filename);
//获取活动的 worksheet和 excel sheet的个数,准备遍历sheets
      Worksheet worksheet = (Worksheet) excel.ActiveSheet;
      sheetCount=excel.Sheets.Count;  
      int startSheetIndex=1;
      int endSheetIndex=sheetCount;
      DataSet ds=new DataSet();
//遍历sheets
            for (int currentIndex = startSheetIndex; currentIndex <= endSheetIndex; currentIndex++)
            {
                worksheet = (Worksheet)excel.Worksheets[currentIndex];
                worksheet.Activate();
                
                //处理每一个sheet.....
 
            }

拆分合并的单元格

在获取有效的单元格区域后,就开始遍历单元格对象,判断单元格对象 MergeCells 属性即可,判断 Cell.MergeCells.ToString() == "True"  即表示该单元格为合并单元格对象。

示例代码如下:

//获取起始单元和截止单元格,以确定有效区域
 
                Excel.Range _startcell=worksheet.Range["A1","A1"]; //默认为第一个单元格
        if(startaddress!="")
        {
          try
          {
            _startcell=worksheet.Range[startaddress,startaddress];
          }
          catch(Exception ex)
          {
            rv[1]+=string.Format("{1}指定的起始单元格地址{0},不是合法的地址。\r\n",startaddress,worksheet.Name);
            //          KillProcessByStartTime("EXCEL",beforetime,aftertime);
            continue;
          }
        }
 
        Excel.Range _lastcell=worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell,Type.Missing);
//默认获取有值的最后一个有效的单元格
        if(endaddress!="")
        {
          try
          {
            _lastcell=worksheet.Range[endaddress,endaddress];
          }
          catch(Exception ex)
          {
            rv[1]+=string.Format("{1}指定的结束单元格地址{0},不是合法的地址。\r\n",endaddress,worksheet.Name);
            //          KillProcessByStartTime("EXCEL",beforetime,aftertime);
            //            return rv;
            continue;
          }
        }
 
 
//遍历有效区域单元格
 
                    foreach (Excel.Range aicell in worksheet.Range[_startcell,_lastcell])
                    {
                        if (aicell.MergeCells.ToString() == "True")
                        {
                            //处理合并单元格
                            object temp_merge_value = aicell.Value2; //备份单元格的值
                            int u_row = aicell.Row;  //记录单元格的首行索引
                            int u_rows = aicell.MergeArea.Rows.Count; //记录单元格的合并区域包含的行数
                            int u_col = aicell.Column; //记录单元格的首列索引
                            int u_cols = aicell.MergeArea.Columns.Count; //记录单元格的合并区域包含的列数
                            aicell.MergeArea.UnMerge();  //取消合并,拆分单元格
                            Excel.Range new_aicell = worksheet.Range[worksheet.Cells[u_row, u_col], worksheet.Cells[u_row + u_rows - 1, u_col + u_cols - 1]];  //获取拆分后单元格后的有效区域
                            new_aicell.Value2 = temp_merge_value; //将拆分的单元格重新赋值(备份值)
                        }
                    }

创建DataTable

如果首行是列数据,则以该行的值创建表结构,否则自动创建以“C”为前缀的列名,如C1、C2...Cn以此类推。

        System.Data.DataTable dt=ds.Tables.Add();
        dt.TableName=worksheet.Name;  //表名为worksheet的名称
        for(int i=_startcell.Column;i<=_lastcell.Column;i++)
        {
          Excel.Range _cell=worksheet.Range[worksheet.Cells[_startcell.Row,i],worksheet.Cells[_startcell.Row,i]];
                string _colname=hastitle==true?_cell.Value2.ToString():"C"+(i-_startcell.Column+1).ToString(); //如果第一行是标题,则赋单元格的值,否则以C开头加序号
            DataColumn dc=dt.Columns.Add();
            dc.ColumnName=_colname;
            dc.DataType=System.Type.GetType("System.String");
            dc.AllowDBNull=true;
        }
        

将单元格数据写入DataTable

object[,] cells=null;  定义二维对象数组
    if(hastitle) //如果首行包含列,则加行索引加1取数据行
  {
    startrow=_startcell.Row+1;  
    }
//将有效区域单元格转化赋值为 object[,] 
cells=(object[,])worksheet.Range[worksheet.Cells[startrow,_startcell.Column],worksheet.Cells[_lastcell.Row,_lastcell.Column]].Value2;
 
//遍历数组,添加行数据到 DataTable里
int _rowcount=cells.GetLength(0);
int _colcount=cells.GetLength(1);
for(int i=0;i<_rowcount;i++)
{
  object[] newrowdata=new object[_colcount];
  for(int j=0;j<_colcount;j++)
  {
    newrowdata[j]=cells[i,j];
  }
  DataRow dr=dt.Rows.Add(newrowdata);
}

总结

在实际的应用中,还可以设定多种参数选项:

1、如导入单元格的数据,是格式化后的数据(ExcelReport.ImportDataType.FormattingValue),还是原始数据(ExcelReport.ImportDataType.OriginalValue),这也是Cell.Value和Cell.Value2的区别

2、创建表列名字段过度依赖于单元格的值,可能会创建失败,建议定义参数指定是否重写列名

3、是否只导入指定的sheet或活动的sheet。

这些选项都可以根据实际的业务进行扩展,我们在此仅讲述了一些操作Excel相关的关键方法和属性,这里仅作参考,欢迎大家评论指教!

相关文章
|
2月前
|
Python
如何根据Excel某列数据为依据分成一个新的工作表
在处理Excel数据时,我们常需要根据列值将数据分到不同的工作表或文件中。本文通过Python和VBA两种方法实现该操作:使用Python的`pandas`库按年级拆分为多个文件,再通过VBA宏按班级生成新的工作表,帮助高效整理复杂数据。
|
2月前
|
数据采集 数据可视化 数据挖掘
用 Excel+Power Query 做电商数据分析:从 “每天加班整理数据” 到 “一键生成报表” 的配置教程
在电商运营中,数据是增长的关键驱动力。然而,传统的手工数据处理方式效率低下,耗费大量时间且易出错。本文介绍如何利用 Excel 中的 Power Query 工具,自动化完成电商数据的采集、清洗与分析,大幅提升数据处理效率。通过某美妆电商的实战案例,详细拆解从多平台数据整合到可视化报表生成的全流程,帮助电商从业者摆脱繁琐操作,聚焦业务增长,实现数据驱动的高效运营。
|
12天前
|
SQL 关系型数据库 MySQL
如何将Excel表的数据导入RDS MySQL数据库?
本文介绍如何通过数据管理服务DMS将Excel文件(转为CSV格式)导入RDS MySQL数据库,涵盖建表、编码设置、导入模式选择及审批执行流程,并提供操作示例与注意事项。
|
4月前
|
存储 安全 大数据
网安工程师必看!AiPy解决fscan扫描数据整理难题—多种信息快速分拣+Excel结构化存储方案
作为一名安全测试工程师,分析fscan扫描结果曾是繁琐的手动活:从海量日志中提取开放端口、漏洞信息和主机数据,耗时又易错。但现在,借助AiPy开发的GUI解析工具,只需喝杯奶茶的时间,即可将[PORT]、[SERVICE]、[VULN]、[HOST]等关键信息智能分类,并生成三份清晰的Excel报表。告别手动整理,大幅提升效率!在安全行业,工具党正碾压手动党。掌握AiPy,把时间留给真正的攻防实战!官网链接:https://www.aipyaipy.com,解锁更多用法!
|
2月前
|
Python
将Excel特定某列数据删除
将Excel特定某列数据删除
|
9月前
|
数据采集 数据可视化 数据挖掘
利用Python自动化处理Excel数据:从基础到进阶####
本文旨在为读者提供一个全面的指南,通过Python编程语言实现Excel数据的自动化处理。无论你是初学者还是有经验的开发者,本文都将帮助你掌握Pandas和openpyxl这两个强大的库,从而提升数据处理的效率和准确性。我们将从环境设置开始,逐步深入到数据读取、清洗、分析和可视化等各个环节,最终实现一个实际的自动化项目案例。 ####
1674 10
|
7月前
|
物联网 数据处理 C#
C#实现上位机开发,串口通信,读写串口数据并处理16进制数据
C#实现上位机开发,串口通信,读写串口数据并处理16进制数据。在自动化、物联网以及工业控制行业中,上位机开发是一项重要的技能。本教程主要介绍使用C#进行上位机开发,重点在于串口通信和数据处理。
1441 82
|
9月前
|
Python
使用OpenPyXL库实现Excel单元格其他对齐方式设置
本文介绍了如何使用Python的`openpyxl`库设置Excel单元格中的文本对齐方式,包括文本旋转、换行、自动调整大小和缩进等,通过具体示例代码展示了每种对齐方式的应用方法,适合需要频繁操作Excel文件的用户学习参考。
450 85
使用OpenPyXL库实现Excel单元格其他对齐方式设置
|
11月前
|
数据采集 存储 JavaScript
自动化数据处理:使用Selenium与Excel打造的数据爬取管道
本文介绍了一种使用Selenium和Excel结合代理IP技术从WIPO品牌数据库(branddb.wipo.int)自动化爬取专利信息的方法。通过Selenium模拟用户操作,处理JavaScript动态加载页面,利用代理IP避免IP封禁,确保数据爬取稳定性和隐私性。爬取的数据将存储在Excel中,便于后续分析。此外,文章还详细介绍了Selenium的基本设置、代理IP配置及使用技巧,并探讨了未来可能采用的更多防反爬策略,以提升爬虫效率和稳定性。
623 4

热门文章

最新文章