Apply formatting to merged cells in workbook with Aspose.Cells for Java

Aspose.Cells Product Family
1

Hi there,

Version: Java AsposeCells-2.5.4.19

I am having issue on cell format in merge cell. here are the snipped code in ruby:

def format_decimal_places(decimal_place, source_cell)
decimal_formatting_style = source_cell.style.clone
decimal_format_str = “”

if decimal_place > 0
decimal_format = “0” * decimal_place
decimal_format_str = “#,##0.” + decimal_format
elsif decimal_place == nil || decimal_place == 0
decimal_format_str = “#,##0”
end
decimal_formatting_style.setCustom(decimal_format_str);
source_cell.setStyle(decimal_formatting_style);
end

workbook = Workbook.new
workbook.open("/tmp/stupid.xls", FileFormatType::EXCEL97TO2003)
worksheet = workbook.getWorksheets().getSheet(0)

num = BigDecimal.new “2.5678"

cell = worksheet.cells.getCell(“A1”)
cell.setValue(num.doubleValue)

format_decimal_places(2, cell)

workbook.save(”/tmp/s.xls")
---------------------------------------

The above code is trying to format the cell (Number type) with specified decimal places. If you open the excel ‘s.xls’ and right click on cell ‘A1’ and select ‘Format Cells’. In Number tab, there is no category is being selected where it should be Number Category.

However, this issue does not happen on non-merge cell.



2

Hi,


Well, I have tested your scenario using the following sample code, it works fine with it. The output file is fine with formatted A1 cell as per the source code. The output file is also attached.

Sample code:
Workbook workbook = new Workbook();
workbook.open(“stupid.xls”, FileFormatType.EXCEL97TO2003);
Worksheet worksheet = workbook.getWorksheets().getSheet(0);

double num = 2.5678;

Cell cell = worksheet.getCells().getCell(“A1”);
cell.setValue(num);

String decimal_format_str = “#,##0.0”;
Style style = cell.getStyle();
style.setCustom(decimal_format_str);
cell.setStyle(style);

workbook.save(“s.xls”);


If you still find the issue, kindly give us complete runnable code, so that we could check your issue correctly.

Thank you.
3

Hi Sahi,

Actually, it was pretty suprise for me that 1 decimal places is working as expected however, if you try more than 2 decimal places, the problem starts showing.

Cheers,

4

Hi,


I can now notice the issue as you have described in your first post.

Sample code:

Workbook workbook = new Workbook();
workbook.open(“stupid.xls”, FileFormatType.EXCEL97TO2003);
Worksheet worksheet = workbook.getWorksheets().getSheet(0);

double num = 2.5678;

Cell cell = worksheet.getCells().getCell(“A1”);
cell.setValue(num);

String decimal_format_str = “#,##0.00”;
Style style = cell.getStyle();
style.setCustom(decimal_format_str);
cell.setStyle(style);

workbook.save(“s.xls”);


Although the cell is formatted fine and the value is displayed according to the custom formatting set as per the source code (above). But no category is selected. We will look into it if this is an issue or something else.

I have logged a ticket for it for our investigation with an id: CELLSJAVA-29982. We will get back to you soon.

Thank you.
5

Hi Sahi,


Just wondering when would I able to get the fix?

Cheers,

6
Hi,

For performance consideration, when you set a style to a cell, we do not check the merged cells settings on this cell and so we do not update style of other cells in the same merged areas. So, following code for aspose.cells:
cells.merge(0, 0, 0, 1);
cell.setStyle(style);
will gives the same result as following code sequence:
cell.setStyle(style);
cells.merge(0, 0, 0, 1);
and they are both same with following steps in ms excel mannually:
set A1’s style and then merge A1 and B1.
If you unmerged A1 and B1 in your template file, change A1’s style to “#,##0.00” and then merge A1 and B1 again, you will get the same result as what aspose.cells generated.
To make the category of number formattings be selected, please update all cells in the merged area mannually by yourself, code like following:
cells.getCell(“A1”).setStyle(style);
cells.getCell(“B1”).setStyle(style);
7

Another issue of similar kind, with Aspose.Net


Attached sheet and following code;

String filePath = @“E:\Code_Bak\Personal\ExcelAsposeTester\ColumnTest.xls”;
String savFilePath = @“E:\Code_Bak\Personal\ExcelAsposeTester\ColumnTest_OUT.xls”;


Workbook book = new Workbook(filePath, new LoadOptions(LoadFormat.Excel97To2003));
Worksheet localSheet = book.Worksheets[“Sheet2”];
Style st = localSheet.Cells[“G7”].GetStyle();

StyleFlag flag = new StyleFlag();
flag.All = true;

Range r = localSheet.Cells[“C7”].GetMergedRange();
Int32 firstRow = r.FirstRow;
Int32 firstCol = r.FirstColumn;
Int32 rowCount = r.RowCount;
Int32 colCount = r.ColumnCount;
//r.UnMerge();
for (Int32 i = firstRow; i < (rowCount + firstRow); i++)
{
for (Int32 j = firstCol; j < (colCount + firstCol); j++)
{
localSheet.Cells[i, j].SetStyle(st, true);
}
}
//r.Merge();
//localSheet.Cells["C7"].GetMergedRange().ApplyStyle(st, flag);
book.Save(savFilePath, SaveFormat.Excel97To2003);

When I hide one of the columns in Merged range for Cell C7 or delete one of the columns, one of the cell borders go off. Can you please suggest a way to retain the formatting on the cells please?

8
Hi,

No, it is not needed for you to unmerge the cells at first. We were just showing how to reproduce such kind of issue when editing your template file in ms excel. For your application with aspose.cells and for this specific template file, you just need to use following code:

style....
cells.getCell(“A1”).setStyle(style);
cells.getCell(“B1”).setStyle(style);
workbook.save(...);
For common template file, the generic code should be like following:
Cell cell = ...;
CellArea ca = cell.getMergedArea();
if(ca == null)
{
cell.setStyle(style);
}
else
{
int startColumn = ca.getStartColumn();
int endColumn = ca.getEndColumn();
int endRow = ca.getEndRow();
for(int row = ca.getStartRow(); row<=endRow; row++)
{
for(int col=startColumn; col<=endColumn; col++)
{
cells.getCell(row, col).setStyle(style);
}
}
9

Last one was for .Net api. There is no getMergedArea() method available in cell Class. Did you mean GetMergedRange().


By the way, thanks so much.
10

Hi,

Yes, you are right. You should use GetMergedRange() method instead in .NET API.