Excel file saving issue

hi Team,

i generated excel file using java aspose cells.

file dialog box is displayed
when i click on open the excel file is opened and shows the data.
if the file contains pivot table at that time the pivot table is not displayed . remaining sheets data displayed well. i attached the source code of jsp
when i click save in file dialog box. The data for pivot and other sheets are displayed successfully in excel file.
please fix this issue.

Hi,

You have attached the JSP file only. That we are unable to execute without it's designer file. If you can, please attach complete solution. We already have your input file [1008404_1473_1.csv] from your another post. If this file is different then also attach that along with complete JSP project

hi babar.zara
please find the following attachment . which contains both code and input file.

and i am asking again how to set font settings for pivot table data and fields
i did not find any information in this link
http://www.aspose.com/demos/java-components/aspose.cells/java/quick-start/pivot-table/pivot-table.jsf
and
Aspose.Total for Java|Documentation


by default it will be 10 arial. it should be 8 in our case.

is there any way to dump whole data from csv to the excel and apply formatting to the excel
please find attached image. when i click yes
it will open excel but pivot table is not generated remaining sheets data will displayed

please correct my code.

any help on this will be appreciated.

Hi,


Yet there is no such feature in Aspose.Cells to set different font size than default. But we are looking forward to add this functionality in some next release. So on your reporting, we have added it in our Feature Request List under ID CELLSJAVA-25742.

We will get back to you on your other issue. Thank you for your patience.

Hi,


  • For XLS files, we do not support this feature because MS Excel90-2003 does not support to custom the style of pivottable(user can only use the auto format type).
  • For XLSX file format, please use following code:
TableStyle tableStyle = workbook.getTableStyles().addPivotTableStyle(“font_size_table_style”);
TableStyleElement tableStyleElement = tableStyle.getTableStyleElements().add(TableStyleElementType.WHOLE_TABLE);
DXFStyle dxfStyle = workbook.createDXFStyle();
Font font = dxfStyle.getFont();
font.setSize(8);
dxfStyle.setFont(font);
tableStyleElement.setElementStyle(dxfStyle);
pivotTable.setPivotTableStyleName(“font_size_table_style”);

hi

the mentioned code is working for xlsx.

when will provide the custom style for xls file.

Thanks & Regards
Ranjith.

<!–[if gte mso 10]> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman","serif";}

<![endif]–><span style=“font-size: 11pt; font-family: “Arial”,“sans-serif”;”>Hi,

As we have already told you that Excel97-2003 does not support to set custom styles for a pivot table. So, we cannot support to make the generated xls show same custom styles in Excel97-2003. However, there are some advanced properties introduced by Excel2007 for xls, supporting those advanced properties can make the xls file show custom styles in MS Excel2007 or heigher versions however. To support those advanced properties, we need more time and we cannot support it very soon. We will try to support it in later versions/fixes when we finish some other important tasks.

Once we support them, we will let you know.

Thank you.

hi

by using your previous response we can apply the format to xlsx file.

but the generated file shows font size as 10 but the actual font is 8.

if i change the font size to 10. it will not get affected.and some fields are still have font size as 11

please find the attached file.

Thanks & regards
Ranjith

Hi,

Thanks for the file.

We will look into it if we can find something. Once we have any update, we will let you know.

Hi,


After further investigation,
we found TableStyle’s font size is immutable in excel. To confirm that,
you can try to create a new pivottable style and click the “format”
menu, the font size field is gray and cannot be changed.

To use custom
font size, excel uses another style model different than TableStyle and
we do not support it currently. We are working on it.


Hi,

We have supported the functionality to change the font size of PivotTable’s style, please use the following code with the new fix release of Aspose.Cells JAVA v2.5.3.7 [attached]
PivotTable pt = ws.getPivotTables().get(0);
DXFStyle dxfStyle = workbook.createDXFStyle();
Font font = dxfStyle.getFont();
font.setSize(8);
dxfStyle.setFont(font);
pt.formatAll(dxfStyle);

Please share your feedback with us. Thank you

hi

this one is working
how can we set format to pivot table data columns.

for example

a column having numbers. if the number values <0 it should be displayed in red color
i am trying to set the format using DXFStyle
this will apply to the entire table.

is there any way to set custom format to the pivot table.

please find the attached file

Thanks & Regards
Ranjith

Hi,

Thanks for your question.

We have forwarded your query to our development team. Once, we will get any update, we will let you know. Thanks for your patience.

The issues you have found earlier (filed as 25742) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by aspose.notifier.

Hi,

For this requirement of setting custom number format for pivot table data columns, you can use following code:

pt.getDataFields().get(0).setNumberFormat(…);
or
pt.getDataFields().get(0).setNumber(…);


hi
thanks for your response.

i know this already

we are trying to set the format "0.00_);Red"

if value is -ve. the color will change to red

but it does not shows the color.

find attached file

Thanks & regards

Ranjith.




Hi Ranjith,


We are unable to identify any issue with the formatting in your last attached Excel file [Summary Balances Report_1008404_535_1.xlsx]. We have found the number formatting has been applied to cells with negative values, such as “B17”,“B18” etc. Can you please point us to the Cell(s) where formatting should be applied but has not.

Thank you for your cooperation.

hi

thanks for your response.


in this case the number format is applied but

if the value is -ve it should be displayed in “red” colour.

check data sheet once.

Thanks & Regards

Ranjith.


Hi,

Please see the code below how to set the red color for negative values.

The code below inserts two values in cell A1 and cell A2 and style both of the cells. Please see the output workbook created by the code and the screenshot.

Java


//Create a new workbook

Workbook workbook = new Workbook();


//Access its first sheet

Worksheet worksheet = workbook.getWorksheets().getSheet(0);


//Access cell A1 and A2

Cell cellA1 = worksheet.getCells().getCell(“A1”);

Cell cellA2 = worksheet.getCells().getCell(“A2”);


//Create a style for cell A1 and A2

DXFStyle style = workbook.createDXFStyle();

style.setCustom(“0_);[Red]\(0\)”);


//Set the style of both of the cells

cellA1.setStyle(style);

cellA2.setStyle(style);


//Put value +ve value inside A1 and -ve value inside A2

cellA1.setValue(222);

cellA2.setValue(-222);


//Write the workbook on disk

workbook.save(“f:\downloads\output.xlsx”, FileFormatType.XLSX);


Screenshot:

hi

thanks for your response.

problem is with the pivot table data.

in pivot table the -ve values should be displayed in red colour.

we are using the following code to apply format.

for(int j=0;j<pivotTableDataArrayList.size();j++)
pt.getDataFields().get(j).setNumberFormat("0.00_);[Red](0.00)");

this one applied to the pivot data. but colour is not displayed as RED.

please find the the attached document.

Thanks & regards

Ranjith.