How to read and write values in excel using java?

Question

Sample Excel --> Sorry I'm not allowed to attached a image..

TC No. | Title | Result

1 | State and Vin | Failed

2 | State and Reg Code | Passed

3 | Booking a Test Drive | Passed

public class sampleTest{
public static void main(String[] args) throws Exception {
         int iTest = 2, iTest2 = 3;
          if (iTest == iTest2){
              //It will pass a value into the excel (e.g. "Passed")
          }
          else{
             //It will pass a value into the excel (e.g. "Failed")
          }
    }

My program's goal is to generate a report by getting the Passed and Failed results from my tests. My main problem here is on how to read the results from the excel and place the value "Passed" or "Failed" under Result column.

You can alternatively create a CSV file with the fields that you mentioned and open the csv file with excel. — Shankhoneer Chakrovarty
– Shankhoneer Chakrovarty, Commented Jul 31, 2012 at 7:18
I already have an existing excel file. What I need to do is to write "Passed" or "Failed" under the Result column. By the way, I can't use csv file because I need to create a pie chart within the excel file. — mary hazelyn valencia
– mary hazelyn valencia, Commented Jul 31, 2012 at 7:37
I have edited my answer to demonstrate how to open a xls file and read the content — Shankhoneer Chakrovarty
– Shankhoneer Chakrovarty, Commented Jul 31, 2012 at 7:44

Shankhoneer Chakrovarty · Accepted Answer · 2012-07-31 07:41:57Z

1

Download the apache poi jar from here Go through these examples which demonstrates how to read/write xls data from a java program Sample help code:

public static void main(String[] args) throws IOException {
        Workbook wb = new HSSFWorkbook(); 
        Sheet sheet = wb.createSheet("sheet");
        Row row = sheet.createRow((short) 0);

        row.createCell(0).setCellValue(1.2);
        row.createCell(1).setCellValue(wb.getCreationHelper().createRichTextString("This is a string"));
        row.createCell(2).setCellValue(true);

        FileOutputStream fileOut = new FileOutputStream("workbook.xls");
        wb.write(fileOut);
        fileOut.close();
    }

This might help you to get started. The whole flow is:

Create a workbook => The main xls file
Then create a sheet
Then create a row.
For each row create as many cells as you want and fill the cells with different values
Write the workbook like a file.

There can be multiple type of cells see this for more info. To know how to read an excel file:

InputStream myxls = new FileInputStream("workbook.xls");
        wb     = new HSSFWorkbook(myxls);


         sheet = wb.getSheetAt(0);       // first sheet
         row     = sheet.getRow(0);        // third row
        HSSFCell cell   = (HSSFCell) row.getCell((short)1);  // fourth cell
        if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
            System.out.println("The Cell was a String with value \" " + cell.getStringCellValue()+" \" ");
        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            System.out.println("The cell was a number " + cell.getNumericCellValue());
        } else {
            System.out.println("The cell was nothing we're interested in");
        }

For more info see this

edited Jul 31, 2012 at 7:41

answered Jul 31, 2012 at 7:17

Shankhoneer Chakrovarty

6851 gold badge6 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mary hazelyn valencia Over a year ago

Thanks for that idea. however I already have an existing excel file created - so what I need to do is to write "Passed" or "Failed" under the Result column. i don't know how can i get the exact column so that i can insert the value "Passed" or "Failed" status. I will just read all the answers that was given by -didxga and -Mikko Maunu.

Mikko Maunu · Accepted Answer · 2012-07-31 07:15:38Z

0

Via library that will be your interface to Excel document. One option is Apache POI. Excel example code can be found from here.

Other option is Java Excel API.

answered Jul 31, 2012 at 7:15

Mikko Maunu

42.3k11 gold badges143 silver badges142 bronze badges

Collectives™ on Stack Overflow

How to read and write values in excel using java?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related