Opening and updateing an excel sheet using jxl
- 9 years ago
I found a solution to the problem.
First I switched to Apache POI. It's similar to jxl but has more support and is constantly updated.
With POI, in order to update get your desired results (at least from what I'm seeing) you would have to upload the file you want to update and create a copy of the file. The reason this happens is because the system locks the first while. When I was doing this with jxl, the whole file would overwrite any data that was in the file and wouldn't output my desired results.
Here is some sample code that I found that works for me.
FileInputStream fsIP= new FileInputStream(new File("C:/FileLocation/Excel.xls")); //Read the spreadsheet that needs to be updated HSSFWorkbook wb = new HSSFWorkbook(fsIP); //Access the workbook HSSFSheet worksheet = wb.getSheetAt(6); //Access the worksheet, so that we can update / modify it. def response=context.expand('${RESTAPI#Response}') def i = (context.expand('${RESTAPI#Order}')).toInteger() def json = new JsonSlurper().parseText(response) a=json.AMT.toString() b=(json.Date.toString()).substring(0,10) c=json.Num.toString() //Cell cell = null; // declare a Cell object cell = worksheet.getRow(i).getCell(14); // Access the second cell in second row to update the value cell.setCellValue(a); // Get current cell value value and overwrite the value cell = worksheet.getRow(i).getCell(15); cell.setCellValue(b); cell = worksheet.getRow(i).getCell(16); cell.setCellValue(c); fsIP.close(); //Close the InputStream FileOutputStream output_file =new FileOutputStream(new File("C:/FileLocation/File2.xls")); //Open FileOutputStream to write updates wb.write(output_file); //write changes output_file.close(); //close the stream
I ran into another issue though. During a DataSource Loop, the file only writes it to one line when I have multiple. I will look for a solution and post it here once I find it for those who are looking for the same.
I'm new to Groovy and Ready API so I'm learning this as I go.