We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Adding data from datatable with parallel formatting in .NET

Hi,

I've a datatable in following format with values,

First Name Last Name Age Format Condition
Jon thomas 25 1
Andrew R 21 2
Namit Bhasin 29 3

There are 3 legends on the excel sheet (A1 - Green background, A2 Blue Back ground, A3- Grey Background)

I want to achieve following,

1.Import above table on the excel sheet for the data columns FirstName, Last Name and age. I don't want to loop withing the excel sheet cells. Instead I want to do the standard import function available with aspose e.g. data import.

2. When the format condition value = 1; row colour (green) should be picked up from A1 (legend);

3. When the format condition value = 2; row colour (Blue) should be picked up from A2 (legend);

4. When the format condition value = 3; row colour (Grey) should be picked up from A3 (legend);

How do I achieve this?

Hi,

Well, I think you have to loop through the conditional format column and manually set the style of the rows. You may check the following documentation link for details regarding setting row style.

https://docs.aspose.com/display/cellsnet/Data+Formatting#DataFormatting-FormattingRowsandColumns

If you want to implement conditional formatting to automatically set the row style based on your conditional format table, I don’t think this kind of conditional formatting is supported in MS Excel either. If you know any way to implement this kind of Conditional Formatting using MS Excel then please create a file manually in MS Excel and post it here. We will check it soon.

Thank You & Best Regards,

For GetMergedRange().ApplyStyle; there is an option to set style flag. I am copying styles from another cell. For style flag setting - I am keeping "HorizontalAlignment", "VerticalAlignment" and "TextDirection" options to false.

Using this technique, I am able to override some properties of an existing cell. However such facility is only available for CellRange only.

Now, when I need to override style of a cell object the only method available is SetStyle without any style flag options.

With cells, I don't want to touch the existing text direction property which is already applied. How can I achieve this?

Hi,

To modify the existing style of the cell you may use Cell.GetStyle Api to get the current style of the cell and then modify the style and use Cell.SetStyle Api to set the cell’s updated style. Please see the following sample code in this regard.

//Instantiating a Workbook object

Workbook workbook = new Workbook();

workbook.Open("D:\\book1.xls");

Worksheet worksheet = workbook.Worksheets[0];

Cells cells = worksheet.Cells;

Style style = cells["A1"].GetStyle();

style.Font.IsItalic = true;

style.Font.IsBold = true;

cells["A1"].SetStyle(style);

//Saving the Excel file

workbook.Save("D:\\book1.xls");

Thank You & Best Regards,

Hey, This is not what I asked!

As per my understanding, with Cell.GetMergedRange().ApplyStyle; there is a overload which accepts StyleFlag.

With the style flag, I can specify which formatting to change which not to change. Eg,

Workbook workbook = new Workbook();<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

workbook.Open("D:\\book1.xls");

Worksheet worksheet = workbook.Worksheets[0];

Cells cells = worksheet.Cells;

Style style = cells["A1"].GetStyle();

StyleFlag flag = new StyleFlag();

Flag.HorizontalAlignment = false;

localSheet.Cells["A2"].GetMergedRange().ApplyStyle(style , flag);

Setting Style Flag is ensuring that HorizontalAlignment which is already applied on the currentCell i.e A2 in above code is not overridden by what is copied from A1.

I am not able to achieve similar thing with Cell.SetStyle. The problem scenario is; I don't want to specify any settings for the style in my code; just want to prevent some settings from copied on. This is possible with CellRange but not with cell.

Please review and suggest a solution.

Hi,

Please check the below code, it works the same as your code. “A1” style will be applied on “A2” without the Horizontal Alignment.

//Instantiating a Workbook object

Workbook workbook = new Workbook();

workbook.Open("D:\\book1.xls");

Worksheet worksheet = workbook.Worksheets[0];

Cells cells = worksheet.Cells;

Style style = cells["A1"].GetStyle();

style.HorizontalAlignment = false;

cells["A2"].SetStyle(style);

//Saving the Excel file

workbook.Save("D:\\book1.xls");

Thank You & Best Regards,

Ah... ok.

I thought,

style.Font.IsItalic = true;
style.Font.IsBold = true;

will make Font Italic or Bold. It would be nice, if description of properties/methods (visual studio intellisence) are provided with the api.

Thanks for your reply. I will try this and will revert.

~/Vinay

Hi,

Please ignore my previous post with the sample code (as there is a mistake in that) and try the following sample code as a work around.

//Instantiating a Workbook object

Workbook workbook = new Workbook();

workbook.Open("D:\\Test_Temp\\book1.xls");

Worksheet worksheet = workbook.Worksheets[0];

Cells cells = worksheet.Cells;

Style style = cells["A1"].GetStyle();

Style style2 = cells["A2"].GetStyle();

style.HorizontalAlignment = style2.HorizontalAlignment;

cells["A2"].SetStyle(style);

//Saving the Excel file

workbook.Save("D:\\book1.xls");

This code will keep intact the Horizontal Alignment of cell A2 and override all the other styles as A1.

Thank You & Best Regards,