Merging cells problem with actxserver

8 次查看（过去 30 天）
Blue
Blue 2023-5-5
评论： 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;

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

采纳的回答

Fangjun Jiang
Fangjun Jiang 2023-5-5
编辑：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更早的评论
Fangjun Jiang
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.

请先登录，再进行评论。

更多回答（1 个）

Cris LaPierre
Cris LaPierre 2023-5-5
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;
  2 个评论
Fangjun Jiang
Fangjun Jiang 2023-5-5
The OP's problem was caused by the overwritten variable 'eSheet1'.
I like the way you do Merge.
Cris LaPierre
Cris LaPierre 2023-5-5
Didn't refresh the page before posting.
Thanks

请先登录，再进行评论。

类别

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

标签

产品


版本

R2021b

Community Treasure Hunt

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

Start Hunting!

Translated by