Writing Excel documents : Automation Discussions

[atom]

Hiya

I thought I'd give a quick round-up of the possible ways to write an Excel document from a test case
Often manual testers like Excel reports from test cases, to show what was tested, and what was different from the expected. To my knowledge the following options exist:

Use Excel COM
- Easy to use object model
- Requires Excel to be installed on test machine
- Excel process may not close when test finishes

Use XML
For example DataTable.WriteXML
- Easy to create
- Doesn't provide any way to format cells (Colors, Borders, Font etc.)
- Not good for large xml files

Use Excel 2003 XML Format
See : http://msdn.microsoft.com/en-us/library ... 10%29.aspx
- Easy to create
- Cells can be formatted (Colors, Borders, Font etc.)
- Not easy to read back such files as input to a test case
- Not good for large xml files

Use Excel 2003 Binary XLS Format
See : http://npoi.codeplex.com/
- Similar object model to Excel COM
- Doesnt require Excel to be installed
- Cells can be formatted, and conditionally formatted
- Has Read/Write capabilities
- Good for large excel documents

Hope thats some good food for thought when thinking about creating Excel reports

Regards

[sdaly]

Nice comparison, didn't realise there were so many different ways!!

Here is a wee snippet I use to generate Excel, which may be of help to others.

Public Sub createExcel
Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
Dim wBook As Microsoft.Office.Interop.Excel.Workbook
Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim blnFileOpen As Boolean = False
Dim strFileName As String = "C:\testcase.xls"
wBook = excel.Workbooks.Add()
wSheet = wBook.ActiveSheet()

'#Headings
excel.cells(1,1)="Test Name"
excel.cells(1,2)="Step Description"
excel.cells(1,3)="Step Expected"
excel.cells(1,4)="Actual"
excel.cells(1,5)="Status"

Dim row as Integer = 2

'#Any data here
For Each test In testsList

excel.Cells(row,1) = test.name
excel.Cells(row,2) = test.description
excel.Cells(row,3) = test.expected
excel.Cells(row,4) = test.actual
excel.Cells(row,5) = test.status

row = row + 1
Next
wSheet.Columns.AutoFit()
wBook.SaveAs(strFileName)
excel.Workbooks.Open(strFileName)
excel.Visible = True
End Sub

[atom]

Yeah there's two other ways too:

Use CSV
- Fast to create
- Good for large files
- Care needed to ensure delimiter character is not corrupted
- No cell formatting possible

Use Open XML
- Not easy to create (AFAIK)