Merging cells problem with actxserver

Question

Blue 2023-5-5

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1958679-merging-cells-problem-with-actxserver

评论： Cris LaPierre 2023-5-5

I have an issue with using actxserver to merge cells in Excel. In the script below I expect to merge the cells B1:C1, D1:E1, F1:G1 and H1:I1 but Excel has instead merged the cells B1:C1, E1:F1, J1:K1 and Q1:R1. I dont get it. How can I get the expected result ?

% Create an Excel object.
e = actxserver('Excel.Application');
% Add a new workbook.
eWorkbook = e.Workbooks.Add;
e.Visible = 1;
% Make the first sheet active.
eSheets = e.ActiveWorkbook.Sheets;
eSheet1 = eSheets.get('Item', 1);
eSheet1.Activate
% Merge some cells
eSheet1 = eSheet1.get('Range', 'B1:C1');
eSheet1.MergeCells = 1;
eSheet1 = eSheet1.get('Range', 'D1:E1');
eSheet1.MergeCells = 1;
eSheet1 = eSheet1.get('Range', 'F1:G1');
eSheet1.MergeCells = 1;
eSheet1 = eSheet1.get('Range', 'H1:I1');
eSheet1.MergeCells = 1;
% Save and close file
SaveAs(eWorkbook, [pwd, '\test_merge.xlsx']);
eWorkbook.Saved = 1;
Close(eWorkbook);
% Quit the Excel program and delete the server object.
e.Quit;
e.delete;
clear e;

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

Fangjun Jiang 2023-5-5

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1958679-merging-cells-problem-with-actxserver#answer_1229964

编辑：Fangjun Jiang 2023-5-5

The merge of D1:E1 was impacted by the merge of B1:C1?

Try the reverse order, F1:G1, then D1:E1, then B1:C1.

Also, variable "eSheet1" is over-written. It was Sheet but then was Range. Definitely problem there.

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

Fangjun Jiang 2023-5-5

编辑：Fangjun Jiang 2023-5-5

It was the over-written of "eSheet1" issue. I've tried. Once you rename the

eSheet1 = eSheet1.get('Range', 'B1:C1'); to

eRange = eSheet1.get('Range', 'B1:C1');

eRange.MergeCells = 1

then, problem solved.

Blue 2023-5-5

Thats right! Thank you

请先登录，再进行评论。

Answer 2

Cris LaPierre 2023-5-5

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1958679-merging-cells-problem-with-actxserver#answer_1230029

在 MATLAB Online 中打开

To me, it appears that get('Range') is using relative rather than absolute reference. When you merge B1 and C1, B1 is treated as A1, and ('Range','D1:E1') is calculated as if B1 is the top left corner. This is more obvious if you reverse the order of merging, which is what I first tried as a fix.

I'm pretty sure this is because you overwrite eSheet1 every time to select a new range.

I would write your code like this, which I believe produces the desired result.

% Create an Excel object.
e = actxserver('Excel.Application');
% Add a new workbook.
eWorkbook = e.Workbooks.Add;
e.Visible = 1;
% Make the first sheet active.
eSheets = e.ActiveWorkbook.Sheets;
eSheet1 = eSheets.get('Item', 1);
eSheet1.Activate
% Merge some cells
eSheet1.Range('B1:C1').Merge;
eSheet1.Range('D1:E1').Merge;
eSheet1.Range('F1:G1').Merge;
eSheet1.Range('H1:I1').Merge;
% Save and close file
SaveAs(eWorkbook, [pwd, '\test_merge.xlsx']);
eWorkbook.Saved = 1;
Close(eWorkbook);
% Quit the Excel program and delete the server object.
e.Quit;
e.delete;
clear e;