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

Free Support Forum - aspose.com

Cell borders not being applied to all cells

Hi


I’ve encountered an Issue where I am converting an Excel CSV file to PDF. I am setting a full border for all of the cells, It works mostly as expected apart from some of the cells are not displaying their border, the cells which are not displaying their border contain a date and time and a scientific number.
See Example_WithoutManualEdit to see the conversion.

I managed to manually edit a single cell and set it to the correct format with style set number however retrieving its original style, it would return 0 and when attempting to get its parent, it would return null. this means i am unable to dynamically apply the styles and formatting to all the cells. When accessing a cell directly, it also resets the formatting.
See Example_WithManualEdit to see the conversion with the manual modification.

Here is the current code:

Worksheet worksheet = workbook.getWorksheets().get(0);
int maxRow = worksheet.getCells().getMaxDataRow();
int maxCol = worksheet.getCells().getMaxDataColumn();
worksheet.autoFitRows(0, maxRow);
worksheet.autoFitColumns(0, maxCol);

Style style = worksheet.getCells().getStyle();
style.setNumber(0);
style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, Color.getBlack());
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, Color.getBlack());
style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, Color.getBlack());
style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, Color.getBlack());

worksheet.getCells().setStyle(style);
worksheet.getCells().setStandardHeightPixels((worksheet.getCells().getStandardHeightPixels() + 10));
// modifying a single cell
Cell cell = worksheet.getCells().checkCell(1, 5);
//Cell cell = worksheet.getCells().checkCell(6, 6);
PdfSaveOptions options = new PdfSaveOptions();

options.setAllColumnsInOnePagePerSheet(true);
options.setPdfCompression(PdfCompressionCore.LZW);

workbook.save(bo, options);

Here is the code including the manual modification:

Worksheet worksheet = workbook.getWorksheets().get(0);
int maxRow = worksheet.getCells().getMaxDataRow();
int maxCol = worksheet.getCells().getMaxDataColumn();
worksheet.autoFitRows(0, maxRow);
worksheet.autoFitColumns(0, maxCol);

Style style = worksheet.getCells().getStyle();
style.setNumber(0);
style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, Color.getBlack());
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, Color.getBlack());
style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, Color.getBlack());
style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, Color.getBlack());

worksheet.getCells().setStyle(style);
worksheet.getCells().setStandardHeightPixels((worksheet.getCells().getStandardHeightPixels() + 10));
// modifying a single cell
Cell cell = worksheet.getCells().checkCell(1, 5);
//Cell cell = worksheet.getCells().checkCell(6, 6);
Style newStyle = style;
newStyle.setNumber(22);
newStyle.setBorder(BorderType.TOP_BORDER, CellBorderType.THICK, Color.getRed());
newStyle.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THICK, Color.getRed());
newStyle.setBorder(BorderType.LEFT_BORDER, CellBorderType.THICK, Color.getRed());
newStyle.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THICK, Color.getRed());
cell.setStyle(newStyle);
PdfSaveOptions options = new PdfSaveOptions();

options.setAllColumnsInOnePagePerSheet(true);
options.setPdfCompression(PdfCompressionCore.LZW);

workbook.save(bo, options);

Is this the expected behavior, is there a way to apply borders to all cells regardless of its type?

Hi,


Thanks for the sample code segments, template files and details.

Well, you should use Range.applyStyle() method to set your desired formatting to the cells, this is better approach and effective. I have updated your sample code accordingly and it works fine:
e.g
Sample code:

Workbook workbook = new Workbook(“example.csv”, new TxtLoadOptions(LoadFormat.CSV));

Worksheet worksheet = workbook.getWorksheets().get(0);
int maxRow = worksheet.getCells().getMaxDataRow();
int maxCol = worksheet.getCells().getMaxDataColumn();
Range range = worksheet.getCells().getMaxDisplayRange();
worksheet.autoFitRows(0, maxRow);
worksheet.autoFitColumns(0, maxCol);

Style style = workbook.createStyle();
style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, Color.getBlack());
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, Color.getBlack());
style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, Color.getBlack());
style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, Color.getBlack());

StyleFlag flag = new StyleFlag();
//Set borders on
flag.setBorders(true);

//apply the style to the range (which contains all the data cells in the worksheet)
range.applyStyle(style, flag);
worksheet.getCells().setStandardHeightPixels((worksheet.getCells().getStandardHeightPixels() + 10));
// modifying a single cell
Cell cell = worksheet.getCells().checkCell(1, 5);
//Cell cell = worksheet.getCells().checkCell(6, 6);
PdfSaveOptions options = new PdfSaveOptions();

options.setAllColumnsInOnePagePerSheet(true);
options.setPdfCompression(PdfCompressionCore.LZW);

workbook.save(“out1.pdf”, options);

Hope, this helps a bit.

Thank you.

Hi


This has resolved the issue

Thanks

Hi,


Good to know that your issue is sorted out by the updated code segment. Feel free to contact us any time if you have further questions or issue, we will be happy to assist you soon.

Thank you.