xlswrite to merged cells
in [Matlab]
|
Prev: Sendmail Error
Next: Fminsearch X0 interval
From: Freek on 14 Apr 2010 11:06 I have a cell array that I want to write to a cell in Excel, say 'E1'. However, for publishing purposes, this cell is merged with cells 'B1:H1'. When I write the array, it is written 7 times (to 'B1' ... 'H1'). This does not matter for the cell 'E1', but it does for other rows below this with cells that aren't merged, since there I see the output 7 times next to each other (for instance, in 'B2' ... 'H2' instead of only in 'E2'). I don't want to unmerge the Excel cells, again for publishing purposes. Is there any way to write the cell array correctly?
From: ade77 on 14 Apr 2010 12:18 "Freek " <f_van_der_steen(a)hotmail.com> wrote in message <hq4lkt$e6b$1(a)fred.mathworks.com>... > I have a cell array that I want to write to a cell in Excel, say 'E1'. However, for publishing purposes, this cell is merged with cells 'B1:H1'. > > When I write the array, it is written 7 times (to 'B1' ... 'H1'). This does not matter for the cell 'E1', but it does for other rows below this with cells that aren't merged, since there I see the output 7 times next to each other (for instance, in 'B2' ... 'H2' instead of only in 'E2'). > > I don't want to unmerge the Excel cells, again for publishing purposes. > > Is there any way to write the cell array correctly? Your question is very confusing, or you do not understand how excel works. When you merge cells(B1....H1), how can you just write to E1 alone. Since the cells are merged, the cells (B1..H1) will have to contain one value. In any case, if you want to write with merged cells option, you might be able to use activex. If you can clarify your problem, I will be able to give you a short code you need. Or maybe what you mean by writing to E1 is that you want to center the value in the merged cells?.
From: Freek on 14 Apr 2010 12:31 "ade77 " <ade100a(a)gmail.com> wrote in message <hq4psa$k2m$1(a)fred.mathworks.com>... > "Freek " <f_van_der_steen(a)hotmail.com> wrote in message <hq4lkt$e6b$1(a)fred.mathworks.com>... > > I have a cell array that I want to write to a cell in Excel, say 'E1'. However, for publishing purposes, this cell is merged with cells 'B1:H1'. > > > > When I write the array, it is written 7 times (to 'B1' ... 'H1'). This does not matter for the cell 'E1', but it does for other rows below this with cells that aren't merged, since there I see the output 7 times next to each other (for instance, in 'B2' ... 'H2' instead of only in 'E2'). > > > > I don't want to unmerge the Excel cells, again for publishing purposes. > > > > Is there any way to write the cell array correctly? > > Your question is very confusing, or you do not understand how excel works. > When you merge cells(B1....H1), how can you just write to E1 alone. Since the cells are merged, the cells (B1..H1) will have to contain one value. > > In any case, if you want to write with merged cells option, you might be able to use activex. > If you can clarify your problem, I will be able to give you a short code you need. > Or maybe what you mean by writing to E1 is that you want to center the value in the merged cells?. I do want to center the value in the merged cells, but that's not the problem since that goes well. I'll try to be more specific: Say I have a cell array with two entries. In the Excel-file I have cells 'B1:H1' merged, but cells 'B2: H2' are not merged. When I simply write the cell array to 'E1', the first value of the cell array is displayed correctly in the merged cells. The problem is the second value of the array, which is displayed in 'B2' to 'H2' (so in total 7 times) while I just want it to be displayed in 'E2'.
From: ade77 on 14 Apr 2010 14:47 For example:you want to write my_cell = {4;5}into E1, and E2, but B1 to H1 is merged. Option 1. The excel file given to you, does not have the cells B1:H1 merged, then do not merge the cells until you put the data, then merge the cells programatically using activex True = 1; False = 0; xlCenter = -4108; my_cell = {4;5}; xlswrite('yourfile.xls', my_cell, 'sheet1', 'E1'); Excel = actxserver('Excel.Application'); Workbooks = Excel.Workbooks; Excel.Visible = 0; % if you want to see the excel file real time enter = 1; Workbook = Excel.Workbooks.Open('full excel file path'); Range = Excel.Range('B1:H1'); Range.Select; Range.MergeCells = True; Range.HorizontalAlignment = xlCenter; Workbook.Save; Excel.Quit; Option 2. The cells B1:H1 is already merged when the file is given to you. then unmerge the cells, put in your cell_array, then merge the cells again. that will be your assignment. Just adjust the above code. If you have problems, later this evening, I will post the code.
From: Freek on 14 Apr 2010 15:10
"ade77 " <ade100a(a)gmail.com> wrote in message <hq52j8$h8e$1(a)fred.mathworks.com>... > For example:you want to write my_cell = {4;5}into E1, and E2, but B1 to > H1 is merged. > > Option 1. The excel file given to you, does not have the cells B1:H1 merged, > then do not merge the cells until you put the data, then merge the cells > programatically using activex > > True = 1; > False = 0; > xlCenter = -4108; > > my_cell = {4;5}; > xlswrite('yourfile.xls', my_cell, 'sheet1', 'E1'); > > Excel = actxserver('Excel.Application'); > Workbooks = Excel.Workbooks; > Excel.Visible = 0; % if you want to see the excel file real time enter = 1; > Workbook = Excel.Workbooks.Open('full excel file path'); > > Range = Excel.Range('B1:H1'); > Range.Select; > Range.MergeCells = True; > Range.HorizontalAlignment = xlCenter; > > Workbook.Save; > Excel.Quit; > > Option 2. The cells B1:H1 is already merged when the file is given to > you. > > then unmerge the cells, put in your cell_array, then merge the cells again. that > will be your assignment. Just adjust the above code. > If you have problems, later this evening, I will post the code. Thanks ade77. I was hoping for a simple solution in the xlswrite-line, although this is an interesting option. However, it might be easier to just split up the cell array into several parts (not just two, since there are more rows which are merged) that correspond with cells being merged or not and write all these parts separately to Excel. I'll think about what I'm going to do, if in the meantime you or anyone else thinks of a quicker solution please let me know. |