Results 1 to 4 of 4
  1. #1
    vsb
    vsb is offline Member
    Join Date
    Oct 2011
    Posts
    17
    Rep Power
    0

    Default Unable to write results into XLS using POI.

    I am struggling to write results into XLS, using Apache POI.
    I want to write test results into XLS file.
    First, I should verify whether or not the resultsall.xls file exists.
    If exists, I should first open the file and GET THE SHEET (resultsall).

    Then, I have to write the results from a specific row (eg: 3). Anyway this row may be anything.

    With the code below, I could not anything, except creating a file successfully.
    Please look through this code, and help me.



    public void writePOI(String pagename, String fieldname, String fvalue, int excelrow, String fname)
    throws Exception
    {

    try{
    boolean res= isFileExist();
    if (res==false)
    {
    createNewFile( "c:\\resultsall.xls");
    }
    HSSFWorkbook sampleWorkbook = null;
    HSSFSheet sampleDataSheet = null;
    FileInputStream fi = new FileInputStream ("c:\\resultsall.xls");
    sampleWorkbook = new HSSFWorkbook(fi);

    sampleWorkbook = new HSSFWorkbook();
    sampleDataSheet = sampleWorkbook.getSheet("resultsall");

    HSSFRow dataRow = sampleDataSheet.createRow(excelrow);
    HSSFCell firstHeaderCell = dataRow.createCell(2);
    firstHeaderCell.setCellValue("Pass");

    HSSFCell secondHeaderCell = dataRow.createCell(3);
    secondHeaderCell.setCellValue(fieldname);

    HSSFCell thirdHeaderCell = dataRow.createCell(4);
    thirdHeaderCell.setCellValue(fvalue);

    HSSFCell fourthHeaderCell = dataRow.createCell(4);
    fourthHeaderCell.setCellValue("The value was entered");


    FileOutputStream fo = new FileOutputStream("c:\\resultsall.xls");
    sampleWorkbook.write(fo);
    fo.flush();
    fo.close();
    }
    catch(Exception e)
    {
    System.out.println( e.toString());
    }
    excelrow ++;
    AllVariables.excelrow= excelrow;
    System.out.println("excelrow-> " + excelrow);
    }

  2. #2
    vsb
    vsb is offline Member
    Join Date
    Oct 2011
    Posts
    17
    Rep Power
    0

    Default

    Aplogies, please delete this post as I could figure the problem out.

  3. #3
    vsb
    vsb is offline Member
    Join Date
    Oct 2011
    Posts
    17
    Rep Power
    0

    Default Re: Unable to write results into XLS using POI.

    Please don't delete this thread, as this maybe useful to others.

    This is the correct code:
    If useful, anyone can take it.


    public void createNewFile(String fname)
    throws Exception
    {
    HSSFSheet sampleDataSheet = null;
    FileOutputStream fo = new FileOutputStream(fname);
    HSSFWorkbook wb = new HSSFWorkbook();
    sampleDataSheet = wb.createSheet("resultsall");




    wb.write(fo);
    fo.flush();
    fo.close();
    wb=null;
    fo=null;
    sampleDataSheet = null;
    }

    public boolean isFileExist()
    throws Exception
    {
    File file=new File("c:\\resultsall.xls");
    boolean exists = file.exists();
    return exists;
    }

    public void writePOI(String pagename, String fieldname, String fvalue, int excelrow, String fname)
    throws Exception
    {
    try{
    boolean res= isFileExist();
    if (res==false)
    {
    createNewFile( "c:\\resultsall.xls");
    }
    HSSFWorkbook sampleWorkbook = null;
    HSSFSheet sampleDataSheet = null;
    FileInputStream fi = new FileInputStream ("c:\\resultsall.xls");
    sampleWorkbook = new HSSFWorkbook(fi);

    sampleDataSheet = sampleWorkbook.getSheet("resultsall");

    HSSFRow dataRow = sampleDataSheet.createRow(excelrow);
    HSSFCell firstHeaderCell = dataRow.createCell(2);
    firstHeaderCell.setCellValue("Pass");

    HSSFCell secondHeaderCell = dataRow.createCell(3);
    secondHeaderCell.setCellValue(fieldname);

    HSSFCell thirdHeaderCell = dataRow.createCell(4);
    thirdHeaderCell.setCellValue(fvalue);

    HSSFCell fourthHeaderCell = dataRow.createCell(4);
    fourthHeaderCell.setCellValue("The value was entered");


    FileOutputStream fo = new FileOutputStream("c:\\resultsall.xls");
    sampleWorkbook.write(fo);
    fo.flush();
    fo.close();
    }
    catch(Exception e)
    {
    System.out.println( e.toString());
    }
    excelrow ++;
    AllVariables.excelrow= excelrow;
    }

  4. #4
    vsb
    vsb is offline Member
    Join Date
    Oct 2011
    Posts
    17
    Rep Power
    0

    Default Re: Unable to write results into XLS using POI.

    I see one issue here, I want to create a table for all the rows that have contents in them. My code is here:
    Please advise.


    public void createNewFile(String fname)
    throws Exception
    {
    HSSFSheet sampleDataSheet = null;
    FileOutputStream fo = new FileOutputStream(fname);
    HSSFWorkbook wb = new HSSFWorkbook();
    sampleDataSheet = wb.createSheet("resultsall");

    HSSFCellStyle style = wb.createCellStyle();

    HSSFFont font = wb.createFont();
    font.setFontName(HSSFFont.FONT_ARIAL);
    font.setFontHeightInPoints((short) 10);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.BROWN.index);
    style.setFont(font);


    HSSFRow row = sampleDataSheet.createRow(3);
    HSSFCell cell1 = row.createCell(2);
    cell1.setCellValue(new HSSFRichTextString("Results"));

    HSSFCell cell2 = row.createCell(3);
    cell2.setCellValue(new HSSFRichTextString("Page Name"));

    HSSFCell cell3 = row.createCell(4);
    cell3.setCellValue(new HSSFRichTextString("Field Name"));

    HSSFCell cell4 = row.createCell(5);
    cell4.setCellValue(new HSSFRichTextString("Field Value"));

    HSSFCell cell5 = row.createCell(6);
    cell5.setCellValue(new HSSFRichTextString("Description"));

    style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM) ;
    style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
    style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
    style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);

    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUN D);
    style.setFillForegroundColor(HSSFColor.YELLOW.inde x);


    cell1.setCellStyle(style);
    cell2.setCellStyle(style);
    cell3.setCellStyle(style);
    cell4.setCellStyle(style);
    cell5.setCellStyle(style);

    sampleDataSheet.autoSizeColumn((short)1);
    sampleDataSheet.autoSizeColumn((short) 2);
    sampleDataSheet.autoSizeColumn((short) 3);
    sampleDataSheet.autoSizeColumn((short) 4);
    sampleDataSheet.autoSizeColumn((short) 5);
    sampleDataSheet.autoSizeColumn((short) 6);


    wb.write(fo);
    fo.flush();
    fo.close();
    wb=null;
    fo=null;
    sampleDataSheet = null;
    }

    public boolean isFileExist()
    throws Exception
    {
    File file=new File("c:\\resultsall.xls");
    boolean exists = file.exists();
    return exists;
    }

    public void writePOI(String pagename, String fieldname, String fvalue, int excelrow, String fname)
    throws Exception
    {
    try{
    boolean res= isFileExist();
    if (res==false)
    {
    createNewFile( "c:\\resultsall.xls");
    }
    HSSFWorkbook sampleWorkbook = null;
    HSSFSheet sampleDataSheet = null;
    FileInputStream fi = new FileInputStream (fname);
    sampleWorkbook = new HSSFWorkbook(fi);

    sampleDataSheet = sampleWorkbook.getSheet("resultsall");

    HSSFRow dataRow = sampleDataSheet.createRow(excelrow);

    HSSFCell firstHeaderCell = dataRow.createCell(2);
    firstHeaderCell.setCellValue("Pass");

    HSSFCell secondHeaderCell = dataRow.createCell(3);
    secondHeaderCell.setCellValue(pagename);

    HSSFCell thirdHeaderCell = dataRow.createCell(4);
    thirdHeaderCell.setCellValue(fieldname);

    HSSFCell fourthHeaderCell = dataRow.createCell(5);
    fourthHeaderCell.setCellValue(fvalue);

    HSSFCell fifthHeaderCell = dataRow.createCell(6);
    fifthHeaderCell.setCellValue("The value was succesfully entered");
    HSSFCellStyle style = sampleWorkbook.createCellStyle();

    HSSFFont font = sampleWorkbook.createFont();
    font.setFontName(HSSFFont.FONT_ARIAL);
    font.setFontHeightInPoints((short) 10);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.BLACK.index);
    style.setFont(font);

    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

    firstHeaderCell.setCellStyle(style);
    secondHeaderCell.setCellStyle(style);
    thirdHeaderCell.setCellStyle(style);
    fourthHeaderCell.setCellStyle(style);
    fifthHeaderCell.setCellStyle(style);


    FileOutputStream fo = new FileOutputStream(fname);
    sampleWorkbook.write(fo);

    fo.flush();
    fo.close();
    }
    catch(Exception e)
    {
    System.out.println( e.toString());
    }
    excelrow ++;
    AllVariables.excelrow= excelrow;
    }

Similar Threads

  1. Make the TextField unable to write on
    By Josep_16 in forum Java Applets
    Replies: 4
    Last Post: 08-21-2011, 08:06 AM
  2. write to file and display results
    By sandbudd in forum New To Java
    Replies: 21
    Last Post: 04-24-2011, 04:00 PM
  3. Unable to write/post to a URL from Java
    By achab in forum Advanced Java
    Replies: 38
    Last Post: 11-06-2010, 01:21 AM
  4. Unable to create and write files
    By DrKilljoy in forum New To Java
    Replies: 4
    Last Post: 09-05-2010, 12:55 AM
  5. Sometimes get the right results sometimes dont
    By Battlefeldt in forum New To Java
    Replies: 0
    Last Post: 12-18-2009, 01:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •