Ideas to implement offline smoke test for downloaded excel files

Question

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!

Answer 1

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();

Answer 2

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

Answer 3

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