Cell Border mismatch of Manual sheet versus Generated sheet

mpujari · May 14, 2012, 9:56am

Hi,
In our application there is an requirement where in cells with no values in it, will be in red borders and cells with value will be in black. The table contains header with blue borders (first row) and rows from 2 to 5 are red border (as there are no values by default in cells).
Please find the attached excel file “redBorderManually.xls” with macro in it which handles borders, and please note that this file was prepared manually. And please find the attached file “redBorderGeneratedFile.xls” which was generated by Aspose (version 7.1.2) and even please find the attached Java file which was used for generation of the file “redBorderGeneratedFile.xls”
There is a mismatch between applying of border between manual created xls file and the generated one. To see the issue please follow below steps;

Case Manual created XLS file:
1. Enter any text value in 1st row, see that the top border changes to black from red.

Case Generated XLS file:
1. Enter any text value in 1st row, see that the top border still is in red.

Please let me know am I missing something here.

kashif.iqbal · May 14, 2012, 11:20am

Hi,

Please accept my apologies for delayed response.

You need to use conditional formatting to achieve what you want. The following code example shows how Conditional formatting is used to put a Black Thick border on Cell (1,1) if it is not empty.

///Instantiating a Workbook object

Workbook workbook = new Workbook();

//Get the first worksheet from the workbook

Worksheet sheet = workbook.getWorksheets().get(0);

ConditionalFormattingCollection cfs = sheet.getConditionalFormattings();

//The first method:adds an empty conditional formatting

int index = cfs.add();

FormatConditionCollection fcs = cfs.get(index);

//Sets the conditional format range - we use Cell(1,1) as an example here.

CellArea ca1 = new CellArea();

ca1.StartRow = 1;

ca1.StartColumn = 1;

ca1.EndRow = 1;

ca1.EndColumn = 1;

fcs.addArea(ca1);

//Set Condition for cells that are not empty

int conditionIndex = fcs.addCondition(FormatConditionType.NOT_CONTAINS_BLANKS);

//Get the recently added condition

FormatCondition fc = fcs.get(conditionIndex);

//Define the condition style here

fc.getStyle().setBorder(BorderType.TOP_BORDER, CellBorderType.THICK, Color.fromArgb(0, 0, 0, 0));

//Save the workbook now

workbook.save(“C://JavaOutput.xls”);

In the attached snapshot you can see that when the cell is empty, there is no dark Top border. When some content is added to cell B2, the top border is converted to dark thick. I hope this helps you in getting what you want.

If there is still any confusion, please feel free to contact us.

Thank you for using Aspose.Cells for Java

shakeel.faiz · May 14, 2012, 11:25am

Hi,

Thanks for your posting and using Aspose.Cells for Java.

I cannot notice any difference in both files, could you please help me with screenshots explaining your problem.

I am using Ms-Excel 2010.

Please download and use the latest version:
Aspose.Cells
for Java v7.2.0.7
and see if it resolves your problem. Let us know your feedback.

Please see the screenshot for a reference.

Screenshot:

mpujari · May 14, 2012, 11:28pm

Hi,
Sorry for the typo in the previous post. it should have been 2nd row rather then 1st row. You can notice the issue in the 2nd row.

Case Manual created XLS file:
1. Enter any text value in 2nd row, see that the top border changes to black from red.

Case Generated XLS file:
1. Enter any text value in 2nd row, see that the top border still is in red.

Please find the screen shot as attached.

thanks and regards,
Mahesh Pujari

shakeel.faiz · May 14, 2012, 11:36pm

Hi,

Thanks for your clarification.

Could you please let us know how did you create redBorderManually.xls manually using Ms-Excel.

Please help us with screenshots showing the steps to create this file manually inside Ms-Excel.

It will help us in sorting out this issue.

mpujari · May 15, 2012, 2:52am

Hi,
Please find the screen-shot which explains how I created the borders in the file “redBorderManually.xls”.
Note: Please make sure to copy the macro which takes care of applying borders when we enter any value in the cells.

Please let me know if I am not clear.

thanks and regards,
Mahesh Pujari

amjad.sahi · May 15, 2012, 3:12am

Hi,

I am afraid, we do not support creating/adding or manipulating macros, you have to try conditional formatting to accomplish your requirement a bit as there is no other way around, see the post Kashif suggested:

https://forum.aspose.com/t/112834

Also, I recommend you to kindly see the document on conditional formattings for your complete reference, you have to write your code to apply the relative conditional formattings to the cells accordingly:

http://www.aspose.com/docs/display/cellsnet/Conditional+Formatting

Thanks for your understanding!

mpujari · May 15, 2012, 3:48am

hi,
We know that macro creating/adding or manipulating macros are not supported in Aspose nor in jexcel (which we used previously).
By saying “Please make sure to copy the macro” what I meant was to copy the macro code which you will find in file “redBorderManually.xls” or in “redBorderGeneratedFile.xls” as attached in this pose in the excel which you are creating manually. I hope I am clear on this now.

thanks and regards,
Mahesh Pujari

amjad.sahi · May 15, 2012, 4:04am

Hi,

Well, Aspose.Cells for .NET only preserves macros/vba codes in the template file and save the file with it only. You cannot copy or extract macros from template files to paste to other files. Suppose, you have macros/vba codes in a template file, now when you open this template file and resave the file using Aspose.Cells APIs, the macros would be retained as they are.

Thank you.

mpujari · May 15, 2012, 4:24am

Hi Amjad,
I made a note just to specify that, we do have a macro background which does border for empty and non-empty cells. We are using the template approach (as we have been using it in JExcel as well).
Anyways when mshakeel.faiz asked how I created redBorderManually.xls, I just wanted to make sure that the macro to be copied in the file which does check for cell values and add appropriate border to the target cell.
I hope I am clear on the macro stuff. and if you wanted more information pls let me know.

thanks and regards,
Mahesh Pujari

shakeel.faiz · May 15, 2012, 4:44am

Hi,

Since Aspose.Cells for Java can preserve macros, so what you can do is that you write a macro beforehand in a template file and then generate borders with Aspose.Cells code as you have done it before.

This way, your output file will have both macros and generated border.

Hopefully, you got my point.

kashif.iqbal · May 15, 2012, 4:54am

Hi,

Are you trying to say that when you open the redBorderManually.xls and save it using Aspose.Cells for Java, the macro no more remains effective?

You can see in the attached screenshot that Aspose.Cells has already copied the macro code while saving the Generated file.

Thanks

mpujari · May 15, 2012, 5:07am

Hi,
No I am not trying to say that, macro code is there and very well done for it. What I am rather trying to say is that, there is an issue with 2nd row of generated excel file. When you edit and add any content to the 2nd row you will find that in the generated excel file the top border remains red, where are in manually created excel file the top border changes to black.
I have already posted the screen-shot in this post (“Cell Border mismatch of Manual sheet versus Generated sheet”)

Am I still clear on this…

thanks and regards,
Mahesh Pujari

shakeel.faiz · May 15, 2012, 5:12am

Hi,

Please try following code which will give your desired result:

Java

Worksheet worksheet1 = workbook.getWorksheets().get(0);

Cells cells = worksheet1.getCells();

for (int i = 0; i < 1; i++) {

for (int j = 0; j < 10; j++) {

Cell cell = cells.get(i, j);

Style style = wb.createStyle();

//Setting the line of the top border

style.setBorder(BorderType.TOP_BORDER, CellBorderType.MEDIUM, Color.getBlue());

//Setting the line of the bottom border

///style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.MEDIUM, Color.getRed());

//Setting the line of the left border

style.setBorder(BorderType.LEFT_BORDER, CellBorderType.MEDIUM, Color.getBlue());

//Setting the line of the right border

style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.MEDIUM, Color.getBlue());

//Saving the modified style to the “A1” cell.

cell.setStyle(style);

}

}

// write out red borders from A2:E5

for (int i = 1; i < 5; i++) {

for (int j = 0; j < 10; j++) {

Cell cell = cells.get(i, j);

Style style = wb.createStyle();

//Setting the line of the top border

style.setBorder(BorderType.TOP_BORDER, CellBorderType.MEDIUM, Color.getRed());

style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.MEDIUM, Color.getRed());

//Setting the line of the left border

style.setBorder(BorderType.LEFT_BORDER, CellBorderType.MEDIUM, Color.getRed());

//Setting the line of the right border

style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.MEDIUM, Color.getRed());

//Saving the modified style to the “A1” cell.

wb.getSettings().setUpdateAdjacentCellsBorder(i!=1 && i!=4);

cell.setStyle(style);

}

}

mpujari · May 15, 2012, 5:17am

Hi,
Thanks for the quick reply and understanding my issue. The fixed worked in the sample application, I haven’t tried on our application though, but I think your fix should work. Will revert back.

regards,
Mahesh Pujari

mpujari · May 15, 2012, 6:54am

Hi,
The fix worked like a charm, thanks for the fix.

regards,
Mahesh Pujari

shakeel.faiz · September 26, 2012, 11:30am

Hi,

Thanks for your comments and feedback.

It’s good to know that your issue is resolved with this fix. Let us know if you face any other issue relating to Aspose.Cells, we will be glad to help you.