0

I am trying format a column data for number. Below are code working for Single cell.

//Instantiating a Workbook object
Workbook workbook = new Workbook();

//Accessing the added worksheet in the Excel file
int sheetIndex = workbook.getWorksheets().add();
Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);
Cells cells = worksheet.getCells();

//Adding the current system date to "A1" cell
Cell cell = cells.get("A1");
cell.setValue(Calendar.getInstance());

//Setting the display format of the date to number 15 to show date as "d-mmm-yy"
Style style = cell.getStyle();
style.setCustom("d-mmm-yy");
cell.setStyle(style);

//Adding a numeric value to "A2" cell
cell = cells.get("A2");
cell.setValue(20);

//Setting the display format of the value to number 9 to show value as percentage
style = cell.getStyle();
style.setCustom("0.0%");
cell.setStyle(style);

//Adding a numeric value to "A3" cell
cell = cells.get("A3");
cell.setValue(1546);

//Setting the display format of the value to number 6 to show value as currency
style = cell.getStyle();
style.setCustom("$#,##0;[Red]$-#,##0");
cell.setStyle(style);

//Saving the modified Excel file in default format
workbook.save("C:\\output.xls");

Here is the code for column.The below code is not working :( 

//Instantiating a Workbook object
Workbook workbook = new Workbook();

//Accessing the added worksheet in the Excel file
int sheetIndex = workbook.getWorksheets().add();
Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);

Cells cells = worksheet.getCells();
Column column = cells.getColumns().get(30);

Style style =column.getStyle();
style.setHorizontalAlignment(TextAlignmentType.CENTER);
style.setCustom("$#,##0;[Red]$-#,##0");

StyleFlag styleFlag = new StyleFlag();

//Applying the style to the column
column.applyStyle(style, styleFlag);

//Saving the modified Excel file in default format
workbook.save("C:\\output.xls");

Can anybody help on this.

Janny
  • 681
  • 1
  • 8
  • 33

1 Answers1

0

You need to set the relevant formatting options on for StyleFlag object, see the updated sample code that works fine: e.g Sample code:

 //Instantiating a Workbook object
        Workbook workbook = new Workbook();

        //Accessing the added worksheet in the Excel file
        int sheetIndex = workbook.getWorksheets().add();
        Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);

        Cells cells = worksheet.getCells();
        Column column = cells.getColumns().get(30);

        Style style1 = column.getStyle();
        style1.setHorizontalAlignment(TextAlignmentType.CENTER);
        style1.setCustom("$#,##0;[Red]$-#,##0");

        StyleFlag styleFlag1 = new StyleFlag();
        styleFlag1.setNumberFormat(true);   
        styleFlag1.setHorizontalAlignment(true);    

        //Applying the style to the column
        column.applyStyle(style1, styleFlag1);

        //Saving the modified Excel file in default format
        workbook.save("C:\\output.xls");

I am Developer evangelist at Aspose.

Amjad Sahi
  • 1,813
  • 1
  • 10
  • 15
  • If i add this "styleFlag1.setNumberFormat(true); styleFlag1.setHorizontalAlignment(true); " two like then. should my code work fine? – Janny Jul 16 '15 at 11:30
  • Well, there is another issue in your code, so kindly change the following lines of code: i.e., Style style =column.getStyle(); style.setHorizontalAlignment(TextAlignmentType.CENTER); style.setCustom("$#,##0;[Red]$-#,##0"); to: Style style1 = column.getStyle(); style1.setHorizontalAlignment(TextAlignmentType.CENTER); style1.setCustom("$#,##0;[Red]$-#,##0"); as you are applying style1 for Column.ApplyStyle() method. You should run my updated code segment which is fine. – Amjad Sahi Jul 17 '15 at 06:50
  • This is just a type mistake, I tried with that also. – Janny Jul 17 '15 at 07:54
  • I am not sure why you still find the issue as it works fine on our end. Could you post your query/ issue on Aspose.Cells forums, we will help you soon. – Amjad Sahi Aug 10 '15 at 08:36