cancel
Showing results for 
Search instead for 
Did you mean: 

Opening and updateing an excel sheet using jxl

SOLVED
Highlighted
Contributor

Opening and updateing an excel sheet using jxl

Hello,

 

I am working using the following code example but I am unable to get it to work.

 

import jxl.*;
import jxl.write.*;
import java.io.*;

public class CreateExcel_JxlApi {
    public static void main(String[] args) {

        //create WorkbookSettings object
        WorkbookSettings ws = new WorkbookSettings();

       try{
             //create work book

           WritableWorkbook workbook = Workbook.getWorkbook(new File("C:/FileLocation/ExcelFile.xls"));
           WritableWorkbook workbookCopy= Workbook.createWorkbook(new File("C:/FileLocation/NewExcelFile"), workbook);
           System.out.println("Did excel file create?");

             //create work sheet
             WritableSheet workSheet = null;
             workSheet = workbook.createSheet("Test Report" ,3);
             SheetSettings sh = workSheet.getSettings();

             //write to datasheet
             workSheet.addCell(new jxl.write.Label(10,0,"User Name"));
             workSheet.addCell(new jxl.write.Label(11,0,"Password2"));
             workSheet.addCell(new jxl.write.Label(13,0,"Another one for the Road"));
            
            
               
             //write to the excel sheet
             workbook.write();
           //close the workbook
             workbook.close();
   }catch(Exception e){
        e.printStackTrace();
  }
}
}

 

What I am trying to do is:

1. Open the excel worksheet with Groovy using jxl.

2. Write New Data onto the same Excel worksheet in a different Row.

3. Make Sure the previous data on the worksheet does not erase.

 

There have been some threads in the past but they either don't explain how it was accomplished or any links relating to the post are broken.

 

Thanks for looking.

Tags (1)
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Contributor

Re: Opening and updateing an excel sheet using jxl

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.

View solution in original post

4 REPLIES 4
Highlighted
Contributor

Re: Opening and updateing an excel sheet using jxl

I updated the code to the following.

 

 Workbook Main = Workbook.getWorkbook(new File("C:/FileLocation/ExcelFile.xls")); 
           WritableWorkbook workbook = Workbook.createWorkbook(new File("C:/FilLocation/ExcelFile2.xls"), ws);
           System.out.println("Did excel file create?");

             //create work sheet
             WritableSheet workSheet = null;
             workSheet = workbook.createSheet("Test Report" ,0);
             SheetSettings sh = workSheet.getSettings();

             //write to datasheet
             workSheet.addCell(new jxl.write.Label(0,0,"User Name"));
             workSheet.addCell(new jxl.write.Label(1,0,"Password"));  
             //write to the excel sheet
             workbook.write();
           //close the workbook
             workbook.close();
             Main.close();

 

It creates the file but still does not update the current file which is what I am looking for. Once again if anyone knows this, please let me know. I will update this as I go.

 

Thanks

Highlighted
Community Manager

Re: Opening and updateing an excel sheet using jxl

Hi Don,

 

 

I'm not familiar with Groovy very well. However, I would suggest that you check if a file exists first. After that, you can use createWorkbook or getWorkbook methods depending on the results of the previous step.

 

@Community, any other ideas?

---------
Tanya Gorbunova
SmartBear Community Manager

Learn SmartBear products in a fun and easy way and prove your knowledge!
>>Participate in the TechCorner Challenge today
Highlighted
Contributor

Re: Opening and updateing an excel sheet using jxl

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.

View solution in original post

Highlighted
Community Manager

Re: Opening and updateing an excel sheet using jxl

That's great that you figure out the issue. Thanks for sharing the solution with us!

---------
Tanya Gorbunova
SmartBear Community Manager

Learn SmartBear products in a fun and easy way and prove your knowledge!
>>Participate in the TechCorner Challenge today
New Here?
Join us and watch the welcome video:
Announcements
TechCorner Leaderboard
Compete with community members in the TechCorner Challenge and get into the Leaderboard!

Rank Participant Points Earned
1 msiadak 12
2 nmrao 8
3 HimanshuTayal 3
Top Kudoed Authors