Reading multiple cells after merged cell from excel sheet

21 次查看（过去 30 天）
Ghenji
Ghenji 2018-3-14
评论： 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更早的评论
Ghenji
Ghenji 2018-3-14
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.

请先登录，再进行评论。

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

采纳的回答

Guillaume
Guillaume 2018-3-14
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更早的评论
Guillaume
Guillaume 2018-3-20
编辑：Guillaume 2018-3-20
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
Guillaume 2018-3-20
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

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Use COM Objects in MATLAB 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by