Excel-使用C#读取.NET中的合并单元格(行) [英] Excel - Reading Merged Cells (rows) in .NET using C#

问题描述

问候StackOverflow'lings，

Greetings StackOverflow'lings,

让我解释一下我的困境:

Let me explain my predicament:

让我们说我在一排中有3列(总共3列，总共9列)，这3列中的每一列都有数据在正下方的行中，我有9列，每个单元格中都有数据.

Lets just say I have 3 columns (9 in total as they are merged in numbers of 3) in one row each of the 3 columns has data in it In the row directly beneath I have 9 columns with each cell having data in it.

这个想法是读取包含3列的第一行，然后找出这些列的跨度，以便在创建结果xml时可以为the分配正确的值.以下是我要实现的目标的粗略草图:

The idea is to read the first row with the 3 columns, and then find out how long those columns span for so I can assign the correct values to the when I am creating the resulting xml. Heres a rough sketch of what I am trying to achieve:

第1行第1列(范围合并为3列)=足球队第2行的第1、2、3列将分别保存有关足球队的数据第1列第2列(范围合并为3列)=足球运动员第2行的第4、5、6列将分别保存有关足球运动员的不同数据第1行第3列(范围合并为3列)=足球财务第2行的第7、8、9列将分别保存有关Football Finances的不同数据

Row 1 Column 1 (range is merged over 3 columns) = Football Teams Row 2 Columns 1, 2, 3 will each hold different data about Football Teams Row 1 Column 2 (range is merged over 3 columns) = Football Players Row 2 Columns 4, 5, 6 will each hold different data about Football Players Row 1 Column 3 (range is merged over 3 columns) = Football Finances Row 2 Columns 7, 8, 9 will each hold different data about Football Finances

由于某些原因，当我读取第一行时，我得到的值是:

For some reason when I read the first row I am getting the values:

[0] =足球队"[1] =空[2] =空[4] =足球运动员"

[0] = "Football Teams" [1] = null [2] = null [4] = "Football Players"

但是当我阅读第二行时，我得到了列的完整数组(9)！我正在使用的代码可以在下面看到:

But when I read row two I get the full array (9) of columns! The code I am using can be seen below:

Range dataRange = worksheet.get_Range("A1", Missing.Value);
dataRange = dataRange.get_End(XlDirection.xlRight);
string dataAddress = dataRange.get_Address(
        false, false, XlReferenceStyle.xlA1,
        Type.Missing, Type.Missing);

dataRange = worksheet.get_Range("A1", dataAddress);
// Cast the range into an object array
object[,] dataValues = (object[,])dataRange.Value2;

任何帮助/指导/正确方向的掌声将不胜感激！

Any help/guidance/slap in the right direction would be greatly appreciated!

推荐答案

合并单元格后，这些单元格仍然存在(就您的代码而言)，只是它们具有空值.因此，合并单元格组中的第一个单元格将具有该值.

When you have merged cells, the cells are still there (in terms of your code), it's just they have null values. So the first cell in the merged cells group will have the value.

阅读您的评论并重新阅读代码后，我相信问题在于您如何选择最后一列:

After reading your comments, and rereading your code, I believe the problem is with how you pick up the last column:

Range dataRange = worksheet.get_Range("A1", Missing.Value);
dataRange = dataRange.get_End(XlDirection.xlRight);

我经常发现在右边执行'End'并不一定会得到右边的最后一项，特别是如果单元格之间有空格(在这种情况下，因为单元格是合并的).因此最好这样做(进行一些清理以保存一些行)-'XFD'是Excel 2007及更高版本中的最后一列:

I often find that doing an 'End' to the right doesn't necessarily get the last item on the right, especially if there are spaces between cells (which is the case here, because the cells are merged). So it might be better to do this (with some cleanup to save some lines) - 'XFD' is the last column in Excel 2007 and above:

Range lastCell = worksheet.get_Range("XFD1").get_End(XlDirection.xlLeft);
Range dataRange = worksheet.get_Range("A1", lastCell);
object[,] dataValues = (object[,])dataRange.Value2;

请注意，此代码不会获取第三组中的最后2个null-唯一的方法是显式设置如下范围:

Note, that this code will not get the last 2 nulls in the third group - the only way to do that is to explicitly set the range like this:

Range dateRange = worksheet.get_Range("A1", "I1");

这篇关于Excel-使用C#读取.NET中的合并单元格(行)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！