Copy style elements from one row to the next

Aspose.Cells Product Family
1

I have a row in the worksheet that has the correct style elements that I want to repeat in the next several rows as they are populated. For example I have just populated a row and declared the height of the row, the width of columns and the font type and size.

Is there a way I can copy all those style elements to each row following that contains similar data? I will be populating from a data table and do not know ahead of time how many rows I will be using.

2

Hi,

Well, there is no direct way to copy a whole row styles or obtaining the rowcolumn heights and widths. But you may do it in indirect way, copy styles cell by cell in some loop.

May the following code help you for your need, kindly consult it and you may change it accordingly. In the example, I copy styles and row/column formattings i.e. row heights etc. from row 5 to row 10:

Workbook workbook = new Workbook();
workbook.Open("d:\\test\\templaterows.xls");
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
//For copying row height from row number 5.
cells.SetRowHeight(9,cells.GetRowHeight(4));
//For copying background / foreground colors, fonts, alignment styles etc.
//Copying styles cell by cell of row 5 to row 10.
for(int i = 0;i< cells.MaxColumn;i++)
{
cells[9,i].Style = cells[4,i].Style;

}

workbook.Save("d:\\test\\out_templaterows.xls");

Thank you.

3

I noticed the CopyRow method in the documentation that came with the Aspose.Cells.

CopyRow -- Copies data and formattings of a whole row.

I am attaching a xls file that I will be using as a template.

Row number 4 (the one just after the headings), contains the formatting I need. Can I copy that row to rows 5, 6, 7 etc in the same table, for as many times as I have rows in the DataTable from which I will poplulate?

I see the syntax in the help files, but without an example it is hard to use.

I will send another file that has data in the formatted row, as an example of what I want to accomplish, in my next post. It appears that I can upload only 1 file at a time with each post.

Thanks for your help.

4

Here is a file that has data in the formatted row. The actual file I want to produce may have many rows. Also I need to determine the row number of the last row of data so I can place the footer information you see at the bottom. I am not experienced in Excel. Can I just declare the cells and formulas I want in the footer and it will just appear after the last row of data?

The formula cell in column F labeled Universe, is supposed to add up every number in the cells above it. What should that formula be? I can't use something like =SUM(F3:F20) because I don't know what the last row of data will be numbered.

Thank you

5

Hi,

Thanks for sharing your scenario and template files.

Yes, surely, you can utilize Cells.CopyRow()/CopyColumn() methods for your need.

To get help with sample example on how to use this method, please check Aspose.Cells Wiki documentation topic:

The formula cell in column F labeled Universe, is supposed to add up every number in the cells above it. What should that formula be? I can't use something like =SUM(F3:F20) because I don't know what the last row of data will be numbered.

Well to get the last row index you may use MaxDataRow property or MaxDataRowInColumn method of Cells class.

e.g.,

int mrow = cells.MaxDataRowInColumn(5);

It will give you the maximum row index of column F which contains data. And Alternatively you may utilize some shared methods of CellsHelper class for specifying formulas dynamically.

Thank you.