Our class is going to be fairly simple. This class could easily be extended to include formatting and insertion of formulas in our cells. For our purposes we'll need to refer to the following namespaces:
Imports System.IO
Imports System.Web
Imports System.Text
We'll be using System.IO for reading and writing to file, System.web for building html elements, and System.Text for working with strings.
Next we'll declare a namespace called Reports as well as a class declaration we'll name WorkBook.
Namespace Reports
PublicClass WorkBook
EndClass
EndNamespace
Now we'll need some properties. This one will be used to reference our data source for the workbook object.
'Property
for holding dataset
Private workbook_ds As DataSet
Property datasource() As DataSet
Get
datasource
= workbook_ds
EndGet
Set(ByVal Value As DataSet)
workbook_ds
= Value
EndSet
EndProperty
Let's tell our object where to put
our data.
'Set the
workpath
Private strPath AsString
Property workpath() AsString
Get
workpath
= strPath
EndGet
Set(ByVal Value AsString)
strPath =
Value
EndSet
EndProperty
SubNew()
'Entry
point
EndSub
Now we'll develop our main method. This is the one we'll call when we need to create a workbook. This method will accept a dataset and convert each table in the dataset to a corresponding worksheet.
PublicSub renderWorkbook(ByVal ds As DataSet)
'Send
Workbook to browser
createworksheets(workbook_ds)
EndSub
PrivateSub createworksheets(ByVal ds As DataSet)
Dim tbl As DataTable
Dim colSheets AsNew Collection()
'Create
directory that holds worksheets and support files
'First
we'll create a directory based on the path variable set
'through
the workpath property
Directory.CreateDirectory(strPath)
'Now
we'll create the directory to hold supporting files.
'We'll
append “_files” to our dataset name.
Directory.CreateDirectory(strPath
& "\" & ds.DataSetName & "_files")
'Walk
through each table in dataset and create worksheet
ForEach tbl In ds.Tables
'We'll
need this collection later to create our multiple worksheets
colSheets.Add(tbl.TableName.ToString)
'Pass
data table to be exported to a sub that will create the worksheet file.
createWorksheetFile(tbl,
ds.DataSetName.ToString)
Next
'Call our
sub that creates the support files
createSupportFiles(colSheets,
ds.DataSetName.ToString)
'This sub
will create the main workbook.xls
createXls(colSheets,
ds.DataSetName.ToString)
EndSub
In this method we make use of three sub routines:
- createWorksheetFile
- createsupportFiles
- createxls
PrivateSub createWorksheetFile(ByVal tbl As DataTable, ByVal strName AsString)
'Files to
create
'bookname_files/sheet001.htm
'bookname_files/sheet002.htm
'bookname_files/sheet(etc.).htm
Dim myFileName, myFileNamePath AsString
'We'll
use stringBuilder to create our documents.
'StringBuilder
is faster than using append to create string-based data
Dim strHTML AsNew StringBuilder()
Dim rw As DataRow
'Take the
name of the table to create our variables for the file name and path.
myFileName
= tbl.TableName.ToString
myFileNamePath
= strPath & "\" & strName & "_files\" &
myFileName & ".htm"
'Create
header – This will go at the top of each of our worksheet files, so we build
'it up
here for later use in our For Next statement
strHTML.Append(createSheetHeader(strName
& ".xls"))
strHTML.Append("<body>"
& vbCrLf)
strHTML.Append("<table
x:str border=1 cellpadding=0 cellspacing=0 width=192
style='border-collapse:"
& vbCrLf)
strHTML.Append("
collapse;table-layout:fixed;width:144pt'" & vbCrLf)
Dim intCount AsInteger = tbl.Columns.Count
Dim I AsInteger
'Create
sheet headings
'This is
where could add additional formating for the headings.
strHTML.Append("<tr>"
& vbCrLf)
For I = 0 To intCount - 1
strHTML.Append("<td><b>"
& tbl.Columns(I).ColumnName.ToString &
"</b></td>"
& vbCrLf)
Next
strHTML.Append("</tr>"
& vbCrLf)
'Turn
each data row into <tr>
Dim strItem AsString
'Walk
through each row of our table and convert it to <tr>
ForEach rw In tbl.Rows
strHTML.Append("<tr>"
& vbCrLf)
'Walk
through each item in our datarow and
'convert
each item into <td>
For I = 0 To intCount - 1
strItem =
rw.Table.Columns(I).ColumnName.ToString
strHTML.Append("<td>"
& fixNull((rw.Item(I))) & "</td>" & vbCrLf)
Next
strHTML.Append("</tr>"
& vbCrLf)
Next
strHTML.Append("</table>"
& vbCrLf)
strHTML.Append("</html>"
& vbCrLf)
writeToFile(strHTML.ToString,
myFileNamePath)
EndSub
We'll use the same kinds of methods to create the remaining files. See the source code for all the gory details.
