网站备案信息保护,免费网站怎么注册,如东网站建设,开源购物商城目录
功能完善
Excel与DataSet的映射关系
运行环境
Excel DCOM 配置
设计实现
组件库引入
方法更新
返回值
参数设计
打开数据源并计算Sheets
拆分合并的单元格
创建DataTable
将单元格数据写入DataTable
删除虚拟列
总结 功能完善
在我的文章 《C#实现Excel…
目录
功能完善
Excel与DataSet的映射关系
运行环境
Excel DCOM 配置
设计实现
组件库引入
方法更新
返回值
参数设计
打开数据源并计算Sheets
拆分合并的单元格
创建DataTable
将单元格数据写入DataTable
删除虚拟列
总结 功能完善
在我的文章 《C#实现Excel合并单元格数据导入数据集》里讲述了可以将具有合并单元格的Excel文件数据导入到DataSet里在实际使用情况中遇到如下情况如下图 如图中的 H 列它是一个合并单元格但它也属于一个特殊的单元格即 worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell,Type.Missing) 最后一个内容单元格由于其合并单元格的地址问题导入程序可能会计算成为一行数据 如图 H1 列假设为标题列字段名那么 H2 列为数据行的第一行作为最后一个单元格因此可能会被识别为只有一行数据而忽略后面所有的行。 因此比较快速的一种解决方案是在右侧增加一个虚拟列 如图通过增加列标题“虚拟列”达到重新计算出 最后单元格 根据最后单元格的地址可以计算出新的行数以达到计算出正确行数据的目的。
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()! 来判断是否转化成功。
参数设计
string _filenameExcel 数据源文件路径bool hastitle: 是否包含标题如果设置为true则表示首行数据为列名称定义string startaddress可指定有效的起始单元格地址不设置则默认为“A1”即第一个单元格string endaddress:可指定有效的截止单元格地址不设置则默认为最后一个有值单元格即XlCellType.xlCellTypeLastCell 枚举 通过3、4参数的定义可以定义出有效的导入矩形区域。
打开数据源并计算Sheets object[] rvnew object[2];rv[0]null;rv[1];//创建一个名为ExcelApp的组件对象
// ExcelApplication excel new ExcelApplication();Excel.Application excel new Excel.Application();excel.DisplayAlertsfalse;excel.AskToUpdateLinksfalse;Excel.Workbook xbexcel.Workbooks.Add(_filename);
//获取活动的 worksheet和 excel sheet的个数,准备遍历sheetsWorksheet worksheet (Worksheet) excel.ActiveSheet;sheetCountexcel.Sheets.Count; int startSheetIndex1;int endSheetIndexsheetCount;DataSet dsnew DataSet();
//遍历sheetsfor (int currentIndex startSheetIndex; currentIndex endSheetIndex; currentIndex){worksheet (Worksheet)excel.Worksheets[currentIndex];worksheet.Activate();//处理每一个sheet.....} 拆分合并的单元格
在获取有效的单元格区域后就开始遍历单元格对象判断单元格对象 MergeCells 属性即可判断 Cell.MergeCells.ToString() True 即表示该单元格为合并单元格对象。
示例代码如下
//获取起始单元和截止单元格以确定有效区域Excel.Range _startcellworksheet.Range[A1,A1]; //默认为第一个单元格if(startaddress!){try{_startcellworksheet.Range[startaddress,startaddress];}catch(Exception ex){rv[1]string.Format({1}指定的起始单元格地址{0}不是合法的地址。\r\n,startaddress,worksheet.Name);// KillProcessByStartTime(EXCEL,beforetime,aftertime);continue;}}Excel.Range _lastcellworksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell,Type.Missing);
//默认获取有值的最后一个有效的单元格excel.Cells[1, _lastcell.Column 1] vcol (_lastcell.Column 1).ToString();_lastcell worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing);if(endaddress!){try{_lastcellworksheet.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; //将拆分的单元格重新赋值(备份值)}}
程序中通过 excel.Cells[1, _lastcell.Column 1] vcol (_lastcell.Column 1).ToString(); 设置增加虚拟列列名以达到重新计算最后单元格的目的。
创建DataTable
如果首行是列数据则以该行的值创建表结构否则自动创建以“C”为前缀的列名如C1、C2...Cn以此类推。 System.Data.DataTable dtds.Tables.Add();dt.TableNameworksheet.Name; //表名为worksheet的名称for(int i_startcell.Column;i_lastcell.Column;i){Excel.Range _cellworksheet.Range[worksheet.Cells[_startcell.Row,i],worksheet.Cells[_startcell.Row,i]];string _colnamehastitletrue?_cell.Value2.ToString():C(i-_startcell.Column1).ToString(); //如果第一行是标题则赋单元格的值否则以C开头加序号DataColumn dcdt.Columns.Add();dc.ColumnName_colname;dc.DataTypeSystem.Type.GetType(System.String);dc.AllowDBNulltrue;} 将单元格数据写入DataTable object[,] cellsnull; 定义二维对象数组if(hastitle) //如果首行包含列则加行索引加1取数据行{startrow_startcell.Row1; }
//将有效区域单元格转化赋值为 object[,]
cells(object[,])worksheet.Range[worksheet.Cells[startrow,_startcell.Column],worksheet.Cells[_lastcell.Row,_lastcell.Column]].Value2;//遍历数组添加行数据到 DataTable里
int _rowcountcells.GetLength(0);
int _colcountcells.GetLength(1);
for(int i0;i_rowcount;i)
{object[] newrowdatanew object[_colcount];for(int j0;j_colcount;j){newrowdata[j]cells[i,j];}DataRow drdt.Rows.Add(newrowdata);
}删除虚拟列
重新计算最后单元格删除 DataSet 最后列虚拟列这样就达到正确输出数据集数据的目的。 代码如下
_lastcell excel.Cells[_lastcell.Row, _lastcell.Column - 1];
dt.Columns.Remove(dt.Columns[dt.Columns.Count - 1]); 总结
在实际的应用中还可能遇到更多的合并情况我们要进行进一步的情况判断和功能完善让导入功能变得更强大本文示例提供了一些操作Excel相关的关键方法和属性这里仅作参考欢迎大家评论指教
