Pivot table formatting

hi

we are using light cells data provider for generating large excel.

in pivot if the value of data cell is -ve it should display in red color.

for this one you suggested that use the following code.

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);

but this is not what we expected.

we are using the auto format type as

setAutoFormatType(PivotTableAutoFormatType.REPORT1);

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 code ,.csv and output file

Thanks & regards.

Ranjith


Hi,


I have tested your code and my output file is fine. The output file is exactly based on the source code you have written, see the attached output file after running your code/program.

I am using v2.5.4.20 (attached).

Thank you.

hi

in pivot if the value of data cell is -ve it should display in red color.

check the output file it is in black.



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);



to get in red color you said that use the font.setColor(Color.Black).
in this case it will apply the color for entire table.
then header fields are getting color as black.

we are applying the
setAutoFormatType(PivotTableAutoFormatType.REPORT1);

it will set the background color of header fields is black.

so the formatAll method is suitable for our requirement.

it should not affect the header fields.



Thanks & Regards

Ranjith.





Thanks & regards.

Ranjith

hi

any update on this one?

Thanks & regards

Ranjith.

Hi,

We have tested and confirm your issue has been solved by our new version V7.0.0.0. Code with the new version like following:

PivotTable pt = (PivotTable)worksheet.getPivotTables().get(0);
Style dxfStyle = workbook.createStyle();
Font font = dxfStyle.getFont();
dxfStyle.setCustom(“0.00_);Red”);
font.setSize(8);
pt.formatAll(dxfStyle);


So, please try the new version. Before update the new version, please pay more attention to the release note because there are many changes for APIs. Also, the new version has no LightCells APIs currently. We will try to support it in later fix/versions.

Please download: Aspose.Cells for Java 7.0.0

hi

thanks for giving new version.

this one is wokring fine for xls format.

for xlsx format i am getting the

error.

java.lang.IncompatibleClassChangeError

at org.dom4j.io.SAXReader.read(SAXReader.java:447)

at org.dom4j.io.SAXReader.read(SAXReader.java:365)

at com.aspose.cells.a.b.y.a(Unknown Source)

at com.aspose.cells.fx.a(Unknown Source)

at com.aspose.cells.fA.h(Unknown Source)

at com.aspose.cells.fA.a(Unknown Source)

at com.aspose.cells.fz.a(Unknown Source)

at com.aspose.cells.Workbook.a(Unknown Source)

at com.aspose.cells.Workbook.save(Unknown Source)

at com.aspose.cells.Workbook.save(Unknown Source)

at test.oracle.apps.ak.server.piovttest.main(piovttest.java:19)

public static void main(String[] args) throws Exception {
LoadOptions loadOptions = new LoadOptions(FileFormatType.CSV);

Workbook workbook = new Workbook("D:\\pivot\\1008404_1813_1.csv",loadOptions);
//Saving the Excel file
workbook.save("D:\\pivot\\1813.xlsx",FileFormatType.XLSX);
}

please find the attached csv fie.

Thanks & regards

Ranjith.

Hi Ranjith,


I have tested your code loading your CSV file to re-save to XLSX file, it works fine with v7.0.0. The output XLSX file is attached here.

Just for your knowledge, there are some other jars (with component’s main jar file) which are used in our component for XML related operations, the following jars are there in the release archive’s “\lib” folder, e.g:
dom4j-1.6.1.jar
stax2-api-3.0.2.jar
woodstox-core-asl-4.0.8.jar
jsr173_1.0_api.jar(only for Jdk1.4 and jdk1.5. It is builtin apis for jdk1.6)
bcprov-jdkXX-146.jar

Please make sure that you have also set these jars into your classpath.

Commonly, such kind of issues are because of the application has not been re-built completely when some dependent classes changed. Please make sure you have added all libraries/jars used by the new version and fully re-built your application based on the new libraries/jars.

Thank you.

hi

thanks for your suggestions.

it is working fine now.

we are using the lightcelldataprovider for generation of large excel and pivot excel file.

when will you support the using of lightcelldataprovider API with this new version.

Thanks

Ranjith.

Hi,


I am afraid we need one month or
more to support this feature for the new version/release.

Thank you.