I am trying to write to an excel file and everything works fine, but it writes the data twice. I thought maybe it was my tables in my database, but I wrote code that I tested to delete my temp tables before I run this code. Here is my code:
Private Sub writeToExcelPatientData(By
Val fileName2 As String, ByVal dsFinal As System.Data.DataTable)
Dim exlApp As Microsoft.Office.Interop.E
xcel.Appli
cation
Dim iCol, iRow, iColVal As Integer
Dim missing As Object = System.Reflection.Missing.
Value
Dim bNew As Boolean
Dim i As Integer
' Open the document that was chosen by the dialog
Dim aBook As Microsoft.Office.Interop.E
xcel.Workb
ook
Try
''re-initialize excel app
exlApp = New Microsoft.Office.Interop.E
xcel.Appli
cation
If exlApp Is Nothing Then
''throw an exception
Throw (New Exception("Unable to Start Microsoft Excel"))
Else
''supresses overwrite warnings
exlApp.DisplayAlerts = False
'aBook = New Excel.Workbook
''check if file exists
If File.Exists(fileName2) Then
aBook = exlApp.Workbooks.Open(file
Name2)
Else
aBook = exlApp.Workbooks.Add(Micro
soft.Offic
e.Interop.
Excel.XlWB
ATemplate.
xlWBATWork
sheet)
End If
With exlApp
.SheetsInNewWorkbook = 1
'.Workbooks.Add()
'trying to adjust where data is put
.Worksheets(1).Select(2)
'For displaying the column name in the the excel file.
Dim iColumn As Integer = 16
For iCol = 0 To dsFinal.Columns.Count - 1
''clear column name before setting a new value
'iColumn = iColumn + iCol
.Cells(1, iCol + 1).Value = ""
.Cells(1, iCol + 1).Value = dsFinal.Columns(iCol).Colu
mnName.ToS
tring
Next
'For displaying the column value row-by-row in the the excel file.
For iRow = 0 To dsFinal.Rows.Count - 1
'iColumn = 15
For iColVal = 0 To dsFinal.Columns.Count - 1
iColumn = iColumn + 1
'iColumn = iColumn + iColVal
'.Cells(iRow + 2, iColumn + 1).Value = ""
'.Cells(iRow + 2, iColumn + 1).Value = Trim(Dt.Rows(iRow).ItemArr
ay(iColVal
).ToString
)
.Cells(iRow + 2, iColVal + 1).Value = Trim(dsFinal.Rows(iRow).It
emArray(iC
olVal).ToS
tring)
Next
Next
If File.Exists(fileName2) Then
.ActiveWorkbook().Save() 'filename)
Else
.ActiveWorkbook().SaveAs(f
ileName2, missing, missing, missing, missing, missing, Microsoft.Office.Interop.E
xcel.XlSav
eAsAccessM
ode.xlNoCh
ange, missing, missing, missing, missing, missing)
End If
.ActiveWorkbook.Close()
End With
Console.Write("File exported sucessfully")
End If
Catch ex As Runtime.InteropServices.CO
MException
Console.Write("ERROR: " & ex.Message)
Catch ex As Exception
Console.Write("ERROR: " & ex.Message)
Finally
exlApp.Quit()
System.Runtime.InteropServ
ices.Marsh
al.Release
ComObject(
exlApp)
aBook = Nothing
exlApp = Nothing
End Try
End Sub
OTHER PART OF CODE**********************
**********
*******'
filename2 = "c:\Census.xls"
Dim dataRow As DataRow
Dim columnName As String
Dim column As DataColumn
Dim schemaTable1 As System.Data.DataTable
Dim dataTable2 As System.Data.DataTable
Do
' Create new data table
schemaTable1 = myreaderCY.GetSchemaTable
dataTable2 = DSfinal.Tables(0)
If Not IsDBNull(schemaTable1) Then
' A query returning records was executed
Dim j As Integer
For j = 0 To schemaTable1.Rows.Count - 1
dataRow = schemaTable1.Rows(j)
' Create a column name that is unique in the data table
columnName = dataRow("ColumnName")
'Add the column definition to the data table
column = New DataColumn(columnName, CType(dataRow("DataType"),
Type))
'dataTable1.Columns.Add(co
lumn)
Next
'Dt.Tables.Add(dataTable1)
'Fill the data table we just created
While myreaderCY.Read()
dataRow = dataTable2.NewRow()
For j = 0 To myreaderCY.FieldCount - 1
dataRow(j) = myreaderCY(j)
Next
dataTable2.Rows.Add(dataRo
w)
End While
Else
'No records were returned
column = New DataColumn("RowsAffected")
dataTable2.Columns.Add(col
umn)
Dt.Tables.Add(dataTable2)
dataRow = dataTable2.NewRow()
dataRow(0) = myreaderCY.RecordsAffected
dataTable2.Rows.Add(dataRo
w)
End If
Loop While myreaderCY.NextResult()
End Sub