Hello guys, i am trying to copy the data along with the format of the report from one excel file to another and also i am trying to remove the border of the graph chart if graph is present on the sheet for that purpose i am using below code. so this code is executing and copying file data and format as i needed but it is not removing border of the chart graph. Also i am using condition along with loop so if more than one graph is present then it should remove border of all graph.
So do you guys have any solutions please suggest
Thank you in advance
IP= “input file location”
TP= “output file location”
wb_in = Workbook(“IP input file location”)
wb_out = Workbook(“TP output file location”)
wbs_in = wb_in.getWorksheets()
wbs_out = wb_out.getWorksheets()
in_wbsc = wbs_in.get(“sheet name”).getCells()
out_wbsc = wbs_out.get(“sheet name”).getCells()
maxr_in = in_wbsc.getMaxDisplayRange().getRefersTo()
range_in = in_wbsc.createRange(maxr_in)
range_out = out_wbsc.createRange(maxr_in)
range_out.copyValue(range_in)
wb_out.save(“TP” same as outputfile location")
wb = Workbook(“updated file location which is ‘TP’ where the above file is saved”)
wbs = wb.getWorksheets().get(“sheetname”)
chartcount = wbs.getCharts().getCount()
if chartcount < 1:
wb.save(“TP output file location”)
else:
for i in range(0,chartcount):
chart = wbs.getCharts().get(i)
chart.getPlotArea().getBorder().setVisible(False)
chart.getChartArea().getBorder().setVisible(False)
wb.save(“TP output file location”)
wb.save(TP)
jpype.shutdownJVM()
and i am also trying to set default calculation option of excel file to automatic for this purpose, i am using below code but it is not doing any changes.
wb.getSettings().getFormulaSettings().setCalculationMode(CalcModeType.AUTOMATIC)
and this
wb.getSettings().setCalcMode(CalcModeType.AUTOMATIC)
so if you guys have any idea or guidance please suggest.
Thanks once again
@asposeLegit,
We have tested your code of removing borders for chart area and plot area with some template files but we cannot find the issue. Those borders are invisible in the re-saved file. Please provide us your template file so we can check it further. It will be better for us to trace your issue if you can provide the resultant file generated at your end and the file you need to get.
sample_file_test.zip (30.4 KB)
In this zip file i have input and output file in input file i have graph but in output i am not getting graph for your reference.
@asposeLegit,
As the method name denotes, Range.copyValue() will only copy cell values and all other objects including chart will not be copied to the resultant workbook. With your code of copyValue(), it is expected result that there is no any graph in your wb_out. If so, how can you get the issue of removing chart borders? By our test with your template file(we changed Range.copyValue() to Range.copy()), we find the charts can be copied correctly, and the borders also can be removed correctly.
Thank you so much @johnson.shi your suggestion does work perfectly as i want.
But one more thing i wanna ask that if i wanna copy whole format of table like background color of cell in excel border of table data and suppose some row or column is merge or hidden etc then same format should be copy in other file also is their any method using which i can copy the whole format. If it is their then can you please suggest something or suggest some any documentation link.
also i wanna learn some more about this library is their any tutorial website is their using which i can learn bit more about this library, if it is their then pls help.
Thanks once again for the help.
@asposeLegit,
Range.copy() would copy everything including data and formatting. If you want to copy formatting only, you may use Range.copyStyle() method.
Please see the Aspose.Cells for Python via Java Docs to know more about the library and what features (with example codes) the API supports for your reference.