Created attachment 39695 [details] Generic excel file that shows the cell type issue To give a quick synopsis, I'm using Apache POI API to generate an excel file. I first collect and print all the data needed into a csv file. I then import the csv file into an excel file using an excel template. When the excel file is finished generating, everything looks as expected. However, when I attempt to change the cell type for any cell, the change never takes effect. I think it might the result of importing all data as String values onto the excel file, but I'm not really sure. I need the ability to update the cell type for specific cells so that our clients can add their custom formulas to the excel after it's been generated. For example, if A1 is a numeric cell with a value = 123.45 and I add the formula to an empty cell '=SUM(A1)', the result the formula return '0'. I've even tried changing the cell type of A1 to Numeric, but the formula still returns 0. Can someone please confirm if these is a bug, or if there is anything I can do to allow the manually cell type edits to take effect after the excel file has been generated? I've attached a generic xlsx file that shows the issue that I am referring to. I'm trying to run SUMIF formula to subtotal amounts that have a certain value in a description column. Thank you.
I am current using the 4.1.2 version of Apache POI
The file is useless without the code that demos what you are doing.
If you want Excel to calculate the formulas when you open the workbook, you need to call. workbook.setForceFormulaRecalculation(true); POI has some basic support for calculating formula results but a lot of Excel functions are not supported or only partially supported. I would strongly recommend just using setForceFormulaRecalculation(true). https://poi.apache.org/components/spreadsheet/eval.html
Created attachment 39697 [details] zip file that contains class, test class, csv file, excel template, and output files Thank you for your help. I tried adding the recommended line of code (below) to my class, but it unfortunately it did not resolve my issue. workbook.setForceFormulaRecalculation(true); I've attached a zip file that contains a version of my class that can be used to reproduce the issue that I am seeing. I've also included a csv file, an excel template, output files, and a test class to run a quick test of my code. Please let me know if you need anything else. Thank you.
Sorry, please use this version of the test class. This version has the correct file names for the csv file, excel template file, and output file. Thank you import java.io.File; public class TestExcelWriter { public static void main(String[] args) throws Exception { File outPutFile = new File("outputFile.xlsx"); String templateFilePath = "template.xlsx"; File csvFile = new File("inputFile.csv"); ExcelWriter excelWriter = new ExcelWriter(); excelWriter.generateExcelFile(outPutFile, templateFilePath, csvFile); } }
I am not going to look any further at that sample. It is far too complicated.
OK got it. No worries. I'll try uploading much simpler version of the code. I'll keep you posted.
Created attachment 39698 [details] zip file contains a simple test class and an excel template Hi PJ, Hopefully this is a lot simpler than the previous version. Simply run the test class. The excel template path should be relative. The excel template contains 1 row and 1 cell as the source row/cell that will be used for all rows. After running the test class, if you manually add '=SUM(A1:A6)' to any cell to get the sum of all modified cells, you will notice that the formula is not calculating correctly. Please let me know if you need anything else. Thank you
*** This bug has been marked as a duplicate of bug 68985 ***