2

I run automated smoke tests for my web application, but until now, these tests are not covering offline excel spreadsheets that are downloaded by the user.

I'm starting a study to find possible solutions to write in my script a way to let the user download and fill the spreadsheet through the smoke test. Has anyone already implemented this? Can you give me ideas to do it?

Thanks a lot in advance!

4
  • 1
    May you share some sample of spreadsheet?
    – amazpyel
    Mar 24, 2014 at 15:42
  • 1
    I can't, but I accomplished this 'mission' by using jexcel to open the spreadsheets and write data into it.
    – periback2
    Mar 24, 2014 at 20:49
  • In what programming language?
    – dzieciou
    Apr 23, 2014 at 18:26
  • java.. I already did it as I mentioned in the comment above yours @dzieciou. thanks! :)
    – periback2
    May 2, 2014 at 13:59

3 Answers 3

2

You can read/write Excel files with Apache POI or JExcelApi Java libraries.
Below are few examples.


Apache POI writing spreadsheet example:

import  java.io.*;
import  org.apache.poi.hssf.usermodel.HSSFSheet;
import  org.apache.poi.hssf.usermodel.HSSFWorkbook;
import  org.apache.poi.hssf.usermodel.HSSFRow;
import  org.apache.poi.hssf.usermodel.HSSFCell;

public class CreateExlFile{
     public static void main(String[]args){
    try{
    String filename="C:/spreadsheet.xls" ;
    HSSFWorkbook workbook=new HSSFWorkbook();
    HSSFSheet sheet =  workbook.createSheet("FirstSheet");  

    HSSFRow rowhead=   sheet.createRow((short)0);
    rowhead.createCell(0).setCellValue("No.");
    rowhead.createCell(1).setCellValue("First Name");
    rowhead.createCell(2).setCellValue("Last Name");

    HSSFRow row=   sheet.createRow((short)1);
    row.createCell(0).setCellValue("1");
    row.createCell(1).setCellValue("Edith");
    row.createCell(2).setCellValue("Coleman");

    FileOutputStream fileOut =  new FileOutputStream(filename);
    workbook.write(fileOut);
    fileOut.close();
    System.out.println("The file was successfully generated!");

    } catch ( Exception ex ) {
        System.out.println(ex);

    }
       }
   }

JExcelApi writing spreadsheet example:

import java.io.File; 
import java.util.Date; 
import jxl.*; 
import jxl.write.*; 

WritableWorkbook workbook = Workbook.createWorkbook(new File("output.xls"));
WritableSheet sheet = workbook.createSheet("FirstSheet", 0);

//The following code fragment puts a label in cell A3, and the number 3.14159 in cell D5. 
Label label = new Label(0, 2, "A label record"); 
sheet.addCell(label); 
Number number = new Number(3, 4, 3.1459); 
sheet.addCell(number);

workbook.write(); 
workbook.close();
1
  • this is the way I did :). I forgot to answer here. thanks anyway.
    – periback2
    Jun 23, 2014 at 11:28
1

If your program flow is something like:

  1. Download spreadsheet
  2. Make changes
  3. Upload changed spreadsheet

Then I suggest that you prepare a spreadsheet with the changes that you are testing for. Then your test can:

  1. Verify that the spreadsheet download is happening
  2. Upload the second spreadsheet
  3. Verify that the relevant changes happen in the application
0

Do you mean that you want the automated test to stop and wait for user interaction? That doesn't seem like the best idea to me, but maybe it makes sense for you. If that's what you're trying to do you could have the test wait for a file to exist in a known location.

IMO a better approach would be for the test to fill out the Excel sheet automatically. You can do that using Office Automation - here's some info http://msdn.microsoft.com/en-us/library/y1xatbkd(v=vs.80).aspx

1
  • I don't want the test to wait for human interaction.. I want it to fill the excel sheet. Actually I'm trying to implement a way to open the file and fill data in it and then re-import in the application to continue the tests... I'll take a look in this office automation (I had never heard about it) :)
    – periback2
    Dec 26, 2013 at 10:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.