Reading multiple cells after merged cell from excel sheet

Question

Ghenji 2018-3-14

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/388260-reading-multiple-cells-after-merged-cell-from-excel-sheet

评论： Guillaume 2018-3-20

Cells A1 and B1 are merged. Now when I read Cells A1 and B1 with Excel COM Application, they read 'Waste Accumulation'and 'NaN' and The immediate cell going down after merged cell is just A2 . Is there a method with which I can read both the cells A2 and B2 after read merged cell? I would like to read column after cells A2 and B2 separately.

3 个评论
显示 1更早的评论隐藏 1更早的评论

Stephen23 2018-3-14

编辑：Stephen23 2018-3-14

Ugh, merged cells!

http://datascopic.net/xlcaliber-7deadlysins/

https://www.techrepublic.com/blog/10-things/10-ways-to-keep-excel-from-biting-you-in-the-butt/

Ghenji 2018-3-14

在 MATLAB Online 中打开

Problem is not with the code. In fact am trying to figure out if its possible to do it. Once I get the address for the string "Waste acc..." I would like to read data in next row from this Active cell. Something am working with ----

[filename,pathname] = uigetfile('*.xls;*.xlsx;*.xlsm;*.xlsb', 'Multiselect', 'on');
fullname = cellstr(fullfile(pathname, filename));
sheetname = {'Sheet3'};
string_item = {'Waste Accumulation'};
excel = actxserver('Excel.Application');
excel.Visible = 0;
workbook = excel.Workbooks.Open(fullname{1});
sheet = workbook.Worksheets.Item(sheetname{1});
start_add = sheet.Cells.Find(string_item{1}).Address;
end_add = sheet.Range(start_add).End('xlDown').Address;

code is just an example and incomplete. in short once I get to the cell address of string _item, I would like to read next immediate row and I should get data of all the cells that merged cell has covered. Like 'Qua' and 'Rev' in this case. Once I get the address of 'Qua' or 'Rev' I can further read from that column only. Have tried my best to explain if not I can come with another example.

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Guillaume 2018-3-14

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/388260-reading-multiple-cells-after-merged-cell-from-excel-sheet#answer_310069

在 MATLAB Online 中打开

I'm still not exactly clear what your question is. If you're trying to find the cells that are part of the merge I've already shown how to do that in my comment:

%...
sheet = workbook.Worksheets.Item(sheetname{1});
start_range = sheet.Cells.Find(string_item{1});  %no need to get the address
end_range = start_range.End('xlDown');
numcolumns = start_range.MergeArea.Columns.Count;  %if you want to know how many columns are merged

After that I'm not sure what you want to do.

5 个评论
显示 3更早的评论隐藏 3更早的评论

Guillaume 2018-3-20

编辑：Guillaume 2018-3-20

在 MATLAB Online 中打开

Not having your spreadsheet, I obviously cannot try your code.

I forgot to subtract one from numcolumns. Hence why you get an extra column. Fixed now.

If you just want the one row after the merged cells:

sheet = workbook.Worksheets.Item(sheetname{1});
start_range = sheet.Cells.Find(string_item{1});  %no need to get the address
numcolumns = start_range.MergeArea.Columns.Count;
full_range = get(sheet, 'Range', get(start_range, 'Offset', 1), get(start_range, 'Offset', 1, numcolumns-1));
header = full_range.value;

Guillaume 2018-3-20

在 MATLAB Online 中打开

Ok. Something I hadn't noticed before: when you offset a merged range by 0 columns, the returned range is offset relative to the start column, when you offset the same range by 1 or more columns, the returned range is offset relative to the end column. That greatly messes things up. Wouldn't be the first thing that makes no sense with Excel!

Never mind, we can obtain the range of the next row without using Offset:

start_range = sheet.Cells.Find(data_items{1});  %no need to get the address
numcolumns = start_range.MergeArea.Columns.Count;
full_range = get(sheet, 'Range', get(sheet, 'Cells', start_range.Row + 1, start_range.Column), get(sheet, 'Cells', start_range.Row + 1, start_range.Column + numcolumns - 1));
header = full_range.Value