Imports <xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
Imports <xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
Imports System.Text
Imports System.Collections.Generic
Public Module ProcessFiles
Public Sub ProcessStrings(ByVal dataSet As DataSet)
Dim stringDefinitions = <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<si>
<t>Address</t>
</si>
<si>
<t>State</t>
</si>
<si>
<t>Zip</t>
</si>
<si>
<t>Date</t>
</si>
<si>
<t>Invoice No.</t>
</si>
<si>
<t>Name</t>
</si>
<si>
<t>City</t>
</si>
<si>
<t>Phone</t>
</si>
<si>
<t>Hours</t>
</si>
<si>
<t>Description</t>
</si>
<si>
<t>Rate</t>
</si>
<si>
<t>Total $</t>
</si>
<si>
<t>Sub Total</t>
</si>
<si>
<t>Shipping & Handling</t>
</si>
<si>
<t>Tax</t>
</si>
<si>
<t>My Personal Company</t>
</si>
<si>
<t>123 Main Street</t>
</si>
<si>
<t>Some Town, FL 99999</t>
</si>
<si>
<t>999-555-1212</t>
</si>
<%= From r In dataSet.Tables("Client").Rows(0).ItemArray() _
Select <si>
<t><%= r %></t>
</si> %>
<%= From r In dataSet.Tables("WorkHours").Rows _
Select <si>
<t><%= r(2) %></t>
</si> %>
</sst>
Dim element As XElement
element = stringDefinitions.FirstNode()
element.SetAttributeValue("count", (19 + dataSet.Tables("WorkHours").Rows.Count + 3))
element.SetAttributeValue("uniqueCount", (19 + dataSet.Tables("WorkHours").Rows.Count))
stringDefinitions.Save(".\Invoice\xl\sharedStrings.xml", SaveOptions.DisableFormatting)
End Sub
Dim subTotal As Int16
Dim totalNumOfHours As Int16
Dim functionIndex As Int16
Dim stringCount As Int16
Dim currentRow As Int16
Dim printNumberFirst As Boolean
Public Function GetRowCount(ByVal column As String, ByVal increment As Boolean) As String
Dim s As String
s = String.Format("{0}{1}{2}{0}", "", column, currentRow)
If (increment) Then
currentRow = currentRow + 1
End If
Return (s)
End Function
Public Function GetStringCount() As Int16
stringCount = stringCount + 1
Return (stringCount)
End Function
Public Function Multiply(ByVal a As Int16, ByVal b As Int16)
Return a * b
End Function
Public Function PrintNumber(ByVal index As Int16, ByVal list() As Int16)
If (index = -1) Then
printNumberFirst = False
Return String.Empty
End If
If (printNumberFirst) Then
Return list(index)
End If
Return list(index + 3)
End Function
Public Function ConvertDate(ByVal strDate As String) As Int32
Dim newDate As DateTime = DateTime.Parse(strDate)
Return newDate.ToOADate()
End Function
Public Function PrintFunction1a() As String
Dim sb As StringBuilder = New StringBuilder(50)
Select Case functionIndex
Case 0
Return (Nothing)
Case Else
Return ("shared")
End Select
End Function
Public Function PrintFunction1b() As String
Dim sb As StringBuilder = New StringBuilder(50)
Select Case functionIndex
Case 0
Return (Nothing)
Case 1
Return ("M14:M35")
Case Else
Return (Nothing)
End Select
End Function
Public Function PrintFunction1c() As String
Dim sb As StringBuilder = New StringBuilder(50)
Select Case functionIndex
Case 0
Return (Nothing)
Case Else
Return ("0")
End Select
End Function
Public Function PrintFunction2(ByVal column1 As String, ByVal column2 As String) As String
Select Case functionIndex
Case 0, 1
functionIndex += 1
Return ("IF(" + column1 + "<>""""," + column1 + "*" + column2 + ","""")")
Case Else
functionIndex += 1
Return String.Empty
End Select
End Function
Public Function TotalHours(ByVal hours As Int16)
totalNumOfHours += hours
Return (hours)
End Function
Public Function TotalCost(ByVal amount As Int16)
subTotal += amount
Return (amount)
End Function
Public Sub ProcessWorkSheet(ByVal dataSet As DataSet, ByVal invoiceNumber As Int16)
subTotal = 0
totalNumOfHours = 0
functionIndex = 0
printNumberFirst = True
currentRow = 13
stringCount = 24
Dim printNumberList() As Int16 = {36, 37, 38, 30, 31, 32}
Dim emptyRows As New List(Of Int16)
For index As Int16 = 1 To (22 - dataSet.Tables("WorkHours").Rows.Count)
emptyRows.Add(index)
Next index
Dim worksheet = <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<dimension ref="A1:O46"/>
<sheetViews>
<sheetView showGridLines="0" tabSelected="1" workbookViewId="0">
<selection activeCell="L14" sqref="L14"/>
</sheetView>
</sheetViews>
<sheetFormatPr defaultRowHeight="12.75"/>
<cols>
<col min="1" max="1" width="4.42578125" style="1" customWidth="1"/>
<col min="2" max="2" width="9.140625" style="1"/>
<col min="3" max="3" width="11.85546875" style="1" customWidth="1"/>
<col min="4" max="4" width="13.42578125" style="1" customWidth="1"/>
<col min="5" max="5" width="2.28515625" style="1" customWidth="1"/>
<col min="6" max="6" width="6" style="1" customWidth="1"/>
<col min="7" max="7" width="5.140625" style="1" customWidth="1"/>
<col min="8" max="8" width="4" style="1" customWidth="1"/>
<col min="9" max="9" width="7.42578125" style="1" customWidth="1"/>
<col min="10" max="10" width="4.140625" style="1" customWidth="1"/>
<col min="11" max="11" width="4.85546875" style="1" customWidth="1"/>
<col min="12" max="12" width="11.28515625" style="1" customWidth="1"/>
<col min="13" max="13" width="11.7109375" style="1" customWidth="1"/>
<col min="14" max="14" width="6" style="1" customWidth="1"/>
<col min="15" max="16384" width="9.140625" style="1"/>
</cols>
<sheetData>
<row r="1" spans="1:15" ht="23.25">
<c r="C1" s="7" t="s">
<v>15</v>
</c>
<c r="O1" s="9"/>
</row>
<row r="2" spans="1:15" ht="15.75">
<c r="C2" s="8" t="s">
<v>16</v>
</c>
<c r="O2" s="9"/>
</row>
<row r="3" spans="1:15" ht="15.75">
<c r="C3" s="8" t="s">
<v>17</v>
</c>
<c r="O3" s="9"/>
</row>
<row r="4" spans="1:15" ht="15.75">
<c r="C4" s="8" t="s">
<v>18</v>
</c>
<c r="O4" s="9"/>
</row>
<row r="5" spans="1:15">
<c r="O5" s="9"/>
</row>
<row r="6" spans="1:15">
<c r="O6" s="9"/>
</row>
<row r="7" spans="1:15" ht="15" customHeight="1">
<c r="B7" s="6" t="s">
<v>5</v>
</c>
<c r="C7" s="42" t="s">
<v>19</v>
</c>
<c r="D7" s="42"/>
<c r="E7" s="42"/>
<c r="F7" s="42"/>
<c r="G7" s="42"/>
<c r="H7" s="42"/>
<c r="I7" s="42"/>
<c r="L7" s="6" t="s">
<v>3</v>
</c>
<c r="M7" s="45">
<v><%= ConvertDate(DateTime.Today().ToString()) %></v>
</c>
<c r="O7" s="9"/>
</row>
<row r="8" spans="1:15" ht="15" customHeight="1">
<c r="B8" s="6" t="s">
<v>0</v>
</c>
<c r="C8" s="43" t="s">
<v>20</v>
</c>
<c r="D8" s="43"/>
<c r="E8" s="4"/>
<c r="F8" s="4"/>
<c r="G8" s="4"/>
<c r="H8" s="4"/>
<c r="I8" s="4"/>
<c r="L8" s="6" t="s">
<v>4</v>
</c>
<c r="M8" s="2">
<v><%= invoiceNumber %></v>
</c>
<c r="O8" s="9"/>
</row>
<row r="9" spans="1:15" ht="15" customHeight="1">
<c r="B9" s="6" t="s">
<v>6</v>
</c>
<c r="C9" s="43" t="s">
<v>21</v>
</c>
<c r="D9" s="43"/>
<c r="F9" s="25" t="s">
<v>1</v>
</c>
<c r="G9" s="4" t="s">
<v>22</v>
</c>
<c r="H9" s="24" t="s">
<v>2</v>
</c>
<c r="I9" s="4" t="s">
<v>23</v>
</c>
<c r="O9" s="9"/>
</row>
<row r="10" spans="1:15" ht="15" customHeight="1">
<c r="B10" s="6" t="s">
<v>7</v>
</c>
<c r="C10" s="43" t="s">
<v>24</v>
</c>
<c r="D10" s="43"/>
<c r="F10" s="26"/>
<c r="O10" s="9"/>
</row>
<row r="11" spans="1:15" ht="15" customHeight="1">
<c r="B11" s="3"/>
<c r="C11" s="3"/>
<c r="D11" s="3"/>
<c r="E11" s="3"/>
<c r="F11" s="3"/>
<c r="G11" s="3"/>
<c r="H11" s="3"/>
<c r="I11" s="3"/>
<c r="J11" s="3"/>
<c r="K11" s="3"/>
<c r="L11" s="3"/>
<c r="M11" s="3"/>
<c r="O11" s="9"/>
</row>
<row r="12" spans="1:15" ht="15" customHeight="1">
<c r="A12" s="5"/>
<c r="B12" s="23" t="s">
<v>8</v>
</c>
<c r="C12" s="23" t="s">
<v>3</v>
</c>
<c r="D12" s="33" t="s">
<v>9</v>
</c>
<c r="E12" s="34"/>
<c r="F12" s="34"/>
<c r="G12" s="34"/>
<c r="H12" s="34"/>
<c r="I12" s="34"/>
<c r="J12" s="34"/>
<c r="K12" s="35"/>
<c r="L12" s="23" t="s">
<v>10</v>
</c>
<c r="M12" s="27" t="s">
<v>11</v>
</c>
<c r="O12" s="9"/>
</row>
<%= From r In dataSet.Tables("WorkHours").Rows _
Select <row r=<%= GetRowCount("", False) %> spans="1:15" ht="15" customHeight="1">
<c r=<%= GetRowCount("A", False) %> s="5"/>
<c r=<%= GetRowCount("B", False) %> s="13">
<v><%= TotalHours(r(0)) %></v>
</c>
<c r=<%= GetRowCount("C", False) %> s="28">
<v><%= ConvertDate(r(1)) %></v>
</c>
<c r=<%= GetRowCount("D", False) %> s=<%= PrintNumber(0, printNumberList) %> t="s">
<v><%= GetStringCount() %></v>
</c>
<c r=<%= GetRowCount("E", False) %> s=<%= PrintNumber(1, printNumberList) %>/>
<c r=<%= GetRowCount("F", False) %> s=<%= PrintNumber(1, printNumberList) %>/>
<c r=<%= GetRowCount("G", False) %> s=<%= PrintNumber(1, printNumberList) %>/>
<c r=<%= GetRowCount("H", False) %> s=<%= PrintNumber(1, printNumberList) %>/>
<c r=<%= GetRowCount("I", False) %> s=<%= PrintNumber(1, printNumberList) %>/>
<c r=<%= GetRowCount("J", False) %> s=<%= PrintNumber(1, printNumberList) %>/>
<c r=<%= GetRowCount("K", False) %> s=<%= PrintNumber(2, printNumberList) %>/>
<c r=<%= GetRowCount("L", False) %> s="10">
<v>70</v>
</c>
<c r=<%= GetRowCount("M", False) %> s="11">
<f t=<%= PrintFunction1a() %> ref=<%= PrintFunction1b() %> si=<%= PrintFunction1c() %>><%= PrintFunction2(GetRowCount("B", False), GetRowCount("L", False)) %></f>
<v><%= TotalCost(Multiply(r(0), 70)) %></v>
</c>
<c r=<%= GetRowCount("O", True) %> s="9"/>
<%= PrintNumber(-1, printNumberList) %>
</row> %>
<%= From r In emptyRows _
Select <row r=<%= GetRowCount("", False) %> spans="1:15" ht="15" customHeight="1">
<c r=<%= GetRowCount("A", False) %> s="5"/>
<c r=<%= GetRowCount("B", False) %> s="13"/>
<c r=<%= GetRowCount("C", False) %> s="28"/>
<c r=<%= GetRowCount("D", False) %> s="30"/>
<c r=<%= GetRowCount("E", False) %> s="31"/>
<c r=<%= GetRowCount("F", False) %> s="31"/>
<c r=<%= GetRowCount("G", False) %> s="31"/>
<c r=<%= GetRowCount("H", False) %> s="31"/>
<c r=<%= GetRowCount("I", False) %> s="31"/>
<c r=<%= GetRowCount("J", False) %> s="31"/>
<c r=<%= GetRowCount("K", False) %> s="32"/>
<c r=<%= GetRowCount("L", False) %> s="10"/>
<c r=<%= GetRowCount("M", False) %> s="11" t="str">
<f t="shared" si="0"/>
<v/>
</c>
<c r=<%= GetRowCount("O", True) %> s="9"/>
</row> %>
<row r="35" spans="1:15" ht="15" customHeight="1">
<c r="A35" s="5"/>
<c r="B35" s="14"/>
<c r="C35" s="29"/>
<c r="D35" s="39"/>
<c r="E35" s="40"/>
<c r="F35" s="40"/>
<c r="G35" s="40"/>
<c r="H35" s="40"/>
<c r="I35" s="40"/>
<c r="J35" s="40"/>
<c r="K35" s="41"/>
<c r="L35" s="10"/>
<c r="M35" s="11" t="str">
<f t="shared" si="0"/>
<v/>
</c>
<c r="O35" s="9"/>
</row>
<row r="36" spans="1:15" ht="15" customHeight="1">
<c r="B36" s="21">
<f>SUM(B13:B35)</f>
<v><%= totalNumOfHours %></v>
</c>
<c r="C36" s="22" t="s">
<v>8</v>
</c>
<c r="L36" s="15" t="s">
<v>12</v>
</c>
<c r="M36" s="19">
<f>SUM(M13:M35)</f>
<v><%= subTotal %></v>
</c>
<c r="O36" s="9"/>
</row>
<row r="37" spans="1:15" ht="15" customHeight="1">
<c r="L37" s="16" t="s">
<v>13</v>
</c>
<c r="M37" s="19">
<v>0</v>
</c>
<c r="O37" s="9"/>
</row>
<row r="38" spans="1:15" ht="15" customHeight="1">
<c r="K38" s="17" t="s">
<v>14</v>
</c>
<c r="L38" s="18">
<v>0</v>
</c>
<c r="M38" s="12">
<f>IF(M36>0,IF(L38<>"",L38*M36,""),"")</f>
<v>0</v>
</c>
<c r="O38" s="9"/>
</row>
<row r="39" spans="1:15" ht="15" customHeight="1">
<c r="O39" s="9"/>
</row>
<row r="40" spans="1:15" ht="15" customHeight="1">
<c r="L40" s="20" t="s">
<v>11</v>
</c>
<c r="M40" s="44">
<f>SUM(M36:M38)</f>
<v><%= subTotal %></v>
</c>
<c r="O40" s="9"/>
</row>
<row r="41" spans="1:15" ht="15" customHeight="1">
<c r="O41" s="9"/>
</row>
<row r="42" spans="1:15" ht="15" customHeight="1">
<c r="O42" s="9"/>
</row>
<row r="43" spans="1:15" ht="15" customHeight="1">
<c r="O43" s="9"/>
</row>
<row r="44" spans="1:15" ht="15" customHeight="1">
<c r="O44" s="9"/>
</row>
<row r="45" spans="1:15" ht="15" customHeight="1">
<c r="A45" s="9"/>
<c r="B45" s="9"/>
<c r="C45" s="9"/>
<c r="D45" s="9"/>
<c r="E45" s="9"/>
<c r="F45" s="9"/>
<c r="G45" s="9"/>
<c r="H45" s="9"/>
<c r="I45" s="9"/>
<c r="J45" s="9"/>
<c r="K45" s="9"/>
<c r="L45" s="9"/>
<c r="M45" s="9"/>
<c r="N45" s="9"/>
<c r="O45" s="9"/>
</row>
<row r="46" spans="1:15">
<c r="A46" s="9"/>
<c r="B46" s="9"/>
<c r="C46" s="9"/>
<c r="D46" s="9"/>
<c r="E46" s="9"/>
<c r="F46" s="9"/>
<c r="G46" s="9"/>
<c r="H46" s="9"/>
<c r="I46" s="9"/>
<c r="J46" s="9"/>
<c r="K46" s="9"/>
<c r="L46" s="9"/>
<c r="M46" s="9"/>
<c r="N46" s="9"/>
<c r="O46" s="9"/>
</row>
</sheetData>
<mergeCells count="28">
<mergeCell ref="D23:K23"/>
<mergeCell ref="D12:K12"/>
<mergeCell ref="D13:K13"/>
<mergeCell ref="D14:K14"/>
<mergeCell ref="D15:K15"/>
<mergeCell ref="D16:K16"/>
<mergeCell ref="D17:K17"/>
<mergeCell ref="D31:K31"/>
<mergeCell ref="D32:K32"/>
<mergeCell ref="D33:K33"/>
<mergeCell ref="D34:K34"/>
<mergeCell ref="D35:K35"/>
<mergeCell ref="C7:I7"/>
<mergeCell ref="C8:D8"/>
<mergeCell ref="C9:D9"/>
<mergeCell ref="C10:D10"/>
<mergeCell ref="D30:K30"/>
<mergeCell ref="D24:K24"/>
<mergeCell ref="D25:K25"/>
<mergeCell ref="D26:K26"/>
<mergeCell ref="D27:K27"/>
<mergeCell ref="D28:K28"/>
<mergeCell ref="D29:K29"/>
<mergeCell ref="D18:K18"/>
<mergeCell ref="D19:K19"/>
<mergeCell ref="D20:K20"/>
<mergeCell ref="D21:K21"/>
<mergeCell ref="D22:K22"/>
</mergeCells>
<pageMargins left="0.25" right="0.25" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
<pageSetup orientation="portrait" r:id="rId1"/>
<drawing r:id="rId2"/>
</worksheet>
worksheet.Save(".\Invoice\xl\worksheets\sheet1.xml", SaveOptions.DisableFormatting)
End Sub
End Module
Submit an article or tip