Excel file saving issue

Aspose.Cells Product Family
21

hi

any reply for this post?

Thanks & regards

Ranjith.

22

Hi Ranjith,


Attached is a snapshot of your Excel file [Summary Balances Report_1008404_535_1.xlsx] opened in Excel 2010. In “Pivot” sheet, the negative values are rendered in RED as per your requirement.
Please comment.
23

hi

please check all my attached files in this post .

no one displays -ve values in red colour in pivot table.

Thanks & regards.

Ranjith

24

Hi Ranjith,


Attached is a snapshot of your other Excel file [Summary Balances Report_1008404_535_1.xlsx] opened in Excel 2010. In this file the Negative values are rendered in RED as per your requirement.

25

Hi again,


Further investigation revealed that your file [Summary Balances Report_1008404_535_1.xlsx] behaves differently in different versions of MS Excel. As I have mentioned earlier that when opened through Excel 2010, we found no issue regarding the negative values in RED. They are rendered fine. But when the same file was opened in MS Excel 2007, the negative values are rendered in BLACK.

We have re-opened the Ticket associated with thread to resolve this issue. Also, the snapshot of Excel 2007 is attached for your reference.

26

hi

As I have mentioned earlier that when opened through Excel 2010, we found no issue regarding the negative values in RED. They are rendered fine. But when the same file was opened in MS Excel 2007, the negative values are rendered in BLACK.

any update on this?

Thanks & Regards
Ranjith.

27

Hi Ranjith,


I am afraid, we do not have any update on the said issue. But we have logged your comments to get ETA of Fix. As soon as we get any news, we will post it here.

Thank you
28

Hi,


We have your output file, but we also need your sample project as well as your input file. Please provide at earliest. Thank you
29

hi


it is problem with DXFStyle.

without using font setting block. -ve numbers are display in red color.

if i use this DXFStyle it is diplays in black.

code:

package test.oracle.apps.ak.server;



import java.io.IOException;

import com.aspose.cells.*;
public class piovttest {
public static void main(String[] args) throws Exception {
Workbook workbook = new Workbook();
workbook.open(“D:\pivot\1008404_1813_1.csv”,FileFormatType.CSV);
piovttest p=new piovttest();

p.CreateStaticReport(workbook);

//Saving the Excel file
workbook.save(“D:\pivot\1813.xlsx”,FileFormatType.XLSX);
}

private void CreateStaticReport(Workbook workbook)
{
try{
Worksheets worksheets = workbook.getWorksheets();
Worksheet worksheet = worksheets.addSheet(“Pivot sheet”);

PivotTables pivotTables = worksheet.getPivotTables();

//Adding a PivotTable to the worksheet
int index = pivotTables.add("=sheet1!$A$1:Q$78",“A1”,“PivotTable1”);

//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = (PivotTable)pivotTables.get(index);

//Unshowing grand totals for rows.
pivotTable.setRowGrand(false);

//Draging the first field to the row area.
pivotTable.addFieldToArea(PivotFieldType.ROW,“GL Account String”);

//Draging the second field to the column area.
pivotTable.addFieldToArea(PivotFieldType.PAGE,“GLCC#Company,”);
pivotTable.addFieldToArea(PivotFieldType.PAGE,“GLCC#Department”);
pivotTable.addFieldToArea(PivotFieldType.PAGE,“GLCC#Account”);
pivotTable.addFieldToArea(PivotFieldType.PAGE,“GLCC#Sub-Account”);
pivotTable.addFieldToArea(PivotFieldType.PAGE,“GLCC#Product”);
pivotTable.addFieldToArea(PivotFieldType.PAGE,“GLCC#Sub-Product”);
pivotTable.addFieldToArea(PivotFieldType.PAGE,“GLCC#Company#Descr”);
pivotTable.addFieldToArea(PivotFieldType.PAGE,“GLCC#Department#Descr”);
pivotTable.addFieldToArea(PivotFieldType.PAGE,“GLCC#Account#Descr”);
pivotTable.addFieldToArea(PivotFieldType.PAGE,“GLCC#Sub-Account#Descr”);
pivotTable.addFieldToArea(PivotFieldType.PAGE,“GLCC#Product#Descr”);




//font setting
//start
PivotTable pt = (PivotTable)worksheet.getPivotTables().get(0);
DXFStyle dxfStyle = workbook.createDXFStyle();
Font font = dxfStyle.getFont();
dxfStyle.setCustom(“0.00_);Red”);
font.setSize(8);
dxfStyle.setFont(font);
pt.formatAll(dxfStyle);

//end

//Draging the third field to the data area.
pivotTable.addFieldToArea(PivotFieldType.DATA,“Available Balance”);
pivotTable.addFieldToArea(PivotFieldType.DATA,“Actual Balance”);
pivotTable.addFieldToArea(PivotFieldType.DATA,“Encumbrance Balance”);
pivotTable.addFieldToArea(PivotFieldType.DATA,“Budget Balance”);
PivotFields pivotFields = pivotTable.getDataFields();
pivotFields.get(0).setFunction(ConsolidationFunction.COUNT);
pivotFields.get(0).setNumberFormat(“0.00_);Red”);
pivotFields.get(1).setFunction(ConsolidationFunction.SUM);
pivotFields.get(1).setNumberFormat(“0.00_);Red”);
pivotFields.get(2).setFunction(ConsolidationFunction.COUNT);
pivotFields.get(2).setNumberFormat(“0.00_);Red”);
pivotFields.get(3).setFunction(ConsolidationFunction.SUM);
pivotFields.get(3).setNumberFormat(“0.00_);Red”);
pivotTable.setAutoFormatType(PivotTableAutoFormatType.REPORT1);

// pivotTable.calculateData();
}
catch(Exception e){e.printStackTrace();}


}

}



please find the input file.

Thanks & Regards

Ranjith.

30

Hi,


Thanks for the template file and code sample.

After an initial test, I can find the issue using your code and template file. I have logged a ticket for the issue with an id: CELLSJAVA-29364. We will figure your issue out soon.

Thank you.

31

Hi,


After further analysis of your issue we found, this isue is because the data in your attached CSV file does not match in the sample program provided by you. In the program, this file is named as 1008404_1813_1.csv, but your attached csv file is 1008404_5456_1.csv. Also, most of the Field names in the program could not be found in your attached csv file.
Please provide as the actual file regarding your sample code for your issue. Also, give us your output file too.

Thank you.

32

hi

please find the attached file.

Thanks & Regards

Ranjith.

33

Hi,


Thanks for providing us the template file.

After an initial test, I can see the issue in the output file. But again, I can find that if I simply open the generated file into MS Excel and apply formatting e.g using “Number Format” and “Format Cells” by right-clicking on the pivot field item(s) in MS Excel manually, it does not implement/work either. We need further investigation whether it is an issue with our product or something else. We will look into it soon and get back to you.

Thank you.
34

Hi,

I think this is a bug of Ms-Excel 2007 and it seems to
resolved in Ms-Excel 2010. In order to bypass this issue, we need to add additional code when
DxfStyle is created.

Java


PivotTable pt = (PivotTable)worksheet.getPivotTables().get(0);

DXFStyle dxfStyle = workbook.createDXFStyle();

Font font = dxfStyle.getFont();

dxfStyle.setCustom(“0.00_);Red”);

font.setSize(8);

font.setColor(Color.BLACK);

dxfStyle.setFont(font);

pt.formatAll(dxfStyle);

35

hi

thanks for giving solution.

but this is not what we expected.


it will shows the format and also change the remaining fields color(row fields and data fields colors is black).

we need format for pivot table fields.

find the attachment.

if possible please run my posted code and check the generated file.

there is one method format(int row,
int column,
DXFStyle style)



how to get the cell address of row fields and data fields.

Thanks & Regards.

Ranjith.



36

Hi,

Thanks for your feedback and comment. I have logged your provided information inside the ticket with id: CELLSJAVA-29364. We will get back to you asap.

37

hi

any update on this.

Thanks & Regards

Ranjith.

38

Hi,

I am afraid, there is no update for you.

Could you please create another thread and illustrating your problem there? I will create a new issue for your problem. Please be elaborative as much as you can.