We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Workbook(inputstream) not working in Aspose 7.1.0

Hi,

I tried introducing green strip using text box in x axis and yaxis . The 'X-Axis' green text box height should be (0,0) to ( 0, -5) and 'X-Axis' green text box width (0,0) to (0,-5).But i get some misalignment.I have attached the code snippet and Required foamat in excel along with this and also find the current excel file we obtain.Please help me out to resolve this issue.

int chartIndex = ws.getCharts().add(ChartType.SCATTER, 3, 9, 27, 19);

int y = ws.getCharts().get.getPlotArea().getHeight();

int x = ws.getCharts().get.getPlotArea().getWidth();

int width = (new Double(((5*x)/(info.getMaxSERangeRow()*2)))).intValue();

int height = (new Double(((5*y)/(info.getMaxMSRangeRow()*2)))).intValue();

TextBox yGreenBox = ws.getCharts()..get(0.getShapes().addTextBoxInChart(450, (x / 2) - width + 250 ,(y / 2) , width);

TextBox xGreenBox = ws.getCharts().get(0).getShapes().addTextBoxInChart((y / 2) + 450, (x / 2) + 250, height, (x / 2));

Thanks & Regards,

Geetha

Hi,

Kindly provide us some workaround ASAP as it is very critical for us.

Thanks & Regards,

Geetha

Hi,

Thanks for providing us your issue details.

We will look into it, how to achieve your desired results and then get back to you asap.

Hi,

I have forwarded your issue to development team and has logged your issue in our database. Development team will look into it and help you asap.

This issue has been logged as CELLSJAVA-40140.

Hi,

We are not sure how do you determine the values for the added textbox such as the width, height, 450, 250.

To set the textbox to the position you desired, you should adjust those values by yourself, such as by your “Current_xlsx_file.xlsx”, you should decrease the values for the “top” parameter and increase the values for the “height” parameter.

And please note those parameters should be in unit of 1/4000 of the chart area. Also, calling Chart.calculate() method before getting x,y,width,height values may improve the result.

To put the textbox according to plotarea’s size and position, we think following code may be better:

Java


Chart chart = sheet.getCharts().get(0);

chart.calculate();

ChartFrame pa = chart.getPlotAreaWithoutTickLabels();

int plotareaW = pa.getWidth();

int plotareaH = pa.getHeight();

int width=…; //1/4000 of the chartarea

int height=…; //1/4000 of the chartarea

shapes = chart.getShapes();

shapes.addTextBoxInChart(pa.getY(), pa.getX()+plotareaW/2-width, plotareaH/2, width);

shapes.addTextBoxInChart(pa.getY()+plotareaH/2, pa.getX()+plotareaW/2, height, plotareaW/2);

By our test, to make the generated textbox be positioned exactly same with “Required_format.xlsx”, it seems a bit tuning is required for those parameters, such as adjust 30, 60(in unit of 1/4000 of the chartarea’s size) separately for y/x:
int width=100;
shapes.addTextBoxInChart(pa.getY(), pa.getX()+plotareaW/2-width-60, plotareaH/2+30, width);
shapes.addTextBoxInChart(pa.getY()+plotareaH/2+30, pa.getX()+plotareaW/2-60, plotareaH/2, plotareaW/2);

Hi Shakeel,

Thanks for your effort on this. We will check and let you know.

Regards,

Geetha

Hi,

Even after keeping Series name as true in Datalabels.Serirs names are not getting displayed.

Following is the Code snippet we have used,

DataLabels datalabels = series.getDataLabels();

datalabels.setShowCategoryName(false);

datalabels.setShowValue(false);

datalabels.setShowPercentage(false);

datalabels.setShowBubbleSize(false);

datalabels.setShowSeriesName(true);

datalabels.getFont().setColor(Color.getBlue());

datalabels.getFont().setSize(13);

Could u plz provide us the solution for this.

Thanks & regards,

Geethanjali.K

Hi,

We have also used the following Code snippet for series name.Kindly ,help us in this issue.

for (int row = 4; row

< (4 + info.getIncludedDealersOnly().size()); row++)

{

String nRef = "=" + dataWs.getName() + "!$"

+ CellsHelper.columnIndexToName(1) + "$" + row;

int nseries = chart.getNSeries().add(nRef, true);

Series series = chart.getNSeries().get(nseries);

String nameRef = "=" + dataWs.getName() + "!$"

+CellsHelper.columnIndexToName(1) + "$" + row;

series.setName(nameRef);

series.getDataLabels().setText(dataWs.getCells().get(row,1).getStringValue());

System.out.println("Row::"+row+"-->"+dataWs.getCells().get(row,1).getStringValue());

String xRef = "=" + dataWs.getName() + "!$"

+ CellsHelper.columnIndexToName(3) + "$" + row;

series.setXValues(xRef);

String yRef = "=" + dataWs.getName() + "!$"

+ CellsHelper.columnIndexToName(4) + "$" + row;

series.setValues(yRef);

formatSeriesAppearance(series);

}

Thanks & Regards,

Geethanjali.K

Hi,

Please provide me your source input file and the expected output file.

You can created expected output file using Ms-Excel.

Source Input File:
Attachment: QPAReport_from_test.xls (If it is different than please provide)

Expected Output File:
? (Please provide)

Hi,

Please find the attached excel.The reference number in data sheet(3rd sheet) should be displayed in the excel for all points.In the previous post ,i have attached the code snippet.Kindly provide us the solution ASAP. also i could not set display unit labels of x axis and y axis to bold.

Thanks & Regards,

Geethanjali.K

Hi,

Sorry, it is difficult for me to sort out this issue. I need your more help.

Please provide me these things.

1 - Your complete code.
2 - Your actual output file
3 - Your expected output file
4 - Screenshot with red circles highlighting the differences in the actual and expected output.

Hi Shakeel,

Following is the code we have used to display the reference number.Could u plz say wat correction is to be made to display it.

for (int row = 4; row

< (4 + info.getIncludedDealersOnly().size()); row++)

{

String nRef = "=" + dataWs.getName() + "!$"

+ CellsHelper.columnIndexToName(1) + "$" + row;

int nseries = chart.getNSeries().add(nRef, true);

//Series series = chart.getNSeries().get(nseries);

Series series = chart.getNSeries().get(nseries);

//SeriesCollection NSeries = chart.getNSeries();

String nameRef = "=" + dataWs.getName() + "!$"

+CellsHelper.columnIndexToName(1) + "$" + row;

series.setName(nameRef);

//series.getDataLabels().setText(dataWs.getCells().get(row,1).getStringValue());

System.out.println("Row::"+row+"-->"+dataWs.getCells().get(row,1).getStringValue());

String xRef = "=" + dataWs.getName() + "!$"

+ CellsHelper.columnIndexToName(3) + "$" + row;

series.setXValues(xRef);

String yRef = "=" + dataWs.getName() + "!$"

+ CellsHelper.columnIndexToName(4) + "$" + row;

series.setValues(yRef);

I have attached the Expected output we require,Current output we acquire and screen shot of required things missing in current screen shot.

Kindly provide us the solution ASAP.

Thanks & Regards,

Geethanjali.K

Hi,

Thanks for your providing files.

It would be better, if you provide us a simpler file which you could create manually using Ms-Excel instead of complex file and simpler runnable code that could be loaded in Eclipse.

Anyway, now I have forwarded your issue to development team. They will look into it and provide you a solution asap.

This issue has been logged as CELLSJAVA-40145.

Hi,

We don’t know how to run your code segment directly you provided. We provide two ways to make data labels be displayed.

Please check the attachment.

Hi Shakeel,

We tried ur Sample program,The issue is that the program works fine when we save in .xls file but it does not work(Datalabels - ShowSeriesName is not working) when we save it in .xlsx file. We are trying to save in .xlsx file only and not in .xls file. Please look into it and let me know the updates.

Regards,

Geetha

Hi,

It looks ok to me, please check the output xlsx files attached by me generated with the previous code.

modified code


workbook.save(path + “\SavaAs1.xlsx”, SaveFormat.XLSX);

workbook.save(path + “\SavaAs2.xlsx”, SaveFormat.XLSX);

Please download and use the latest version:
Aspose.Cells
for Java v7.1.1.3

Hi,

Even in the file u have attached( i.e savaAs1.xlsx).The Series name is not displayed while saving it as xlsx file.Kindly find the attached screen shot.

Thanks & Regards,

Geetha

Thank u shakeel,I have resolved in another way using Chart points.

Can u plz clarify me whether Axis title can be set in the format i have attached.

Thanks & Regards,

Geetha

Hi,

You can control chart’s title text positioning using X and Y properties.
e.g

chart.getCategoryAxis().getTitle().get|setX()/get|setY()
chart.getValueAxis().getTitle().get|setX()/get|setY()


You can set chart title text using chart.getTitle().setText() method.

Other than that, I think, you want to set the chart’s text rotation angle too.

Because your Y-Axis title is vertical and X-Axis title is horizontal.

You can control rotation using the chart.getTitle().setRotationAngle() method

getRotationAngle/setRotationAngle


public int getRotationAngle() / public void setRotationAngle(int value)
Represents text rotation angle.
0: Not rotated.

255: Top to Bottom.

-90: Downward.


If you are looking something else, then please provide me your sample xlsx files and the screenshot highlighting the desired areas with red circles.

Hi,



Please download and try this fix: Aspose-cells-7.1.1.4-java

We fixed the Series.getDataLabels().setShowSeriesName(true) for 2007 format file issues.

About the title position issues, you may add the following code segment before saving and adjust the values to drag the tile to better location.


c.getCategoryAxis().getTitle().setX(3400);


c.getCategoryAxis().getTitle().setY(1600);


c.getValueAxis().getTitle().setX(2050);


c.getValueAxis().getTitle().setY(200)