Sunday, December 19, 2010
Link for Google Map Integration dynamically.
http://webcodeblog.com/2010/04/22/setting-up-google-maps-for-asp-net-and-sql-server/
Friday, December 10, 2010
Code for Export to Excel sheet in vb.net
Public Shared Function CreateExcelFile(ByVal fileName As String, ByVal dt As DataTable) As Boolean
Dim excelExport As New Microsoft.Office.Interop.Excel.Application()
Dim excelBook As Microsoft.Office.Interop.Excel.Workbook
Dim excelSheets As Microsoft.Office.Interop.Excel.Sheets
Dim excelSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim excelCells As Microsoft.Office.Interop.Excel.Range
Dim location As Integer = System.Windows.Forms.Application.ExecutablePath.LastIndexOf("\")
'Dim exPath As String = System.Windows.Forms.Application.ExecutablePath
Dim filePath As String
Try
'If Not isInstalled(ApiEnums.OfficeApplications.Excel) Then
' MsgBox("Microsoft Excel is required for this functionality." & vbCrLf & _
' "Contact your Help Desk about getting " & vbCrLf & "this installed. Thank You.")
'Else
filePath = "C:\" & fileName
'If Not Directory.Exists(exPath.Substring(0, (location + 1)) + "tmpFiles\") Then
' Directory.CreateDirectory(exPath.Substring(0, (location + 1)) + "tmpFiles\")
'End If
excelExport.Visible = False : excelExport.DisplayAlerts = False
excelBook = excelExport.Workbooks.Add
excelSheets = excelBook.Worksheets
excelSheet = CType(excelSheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet)
excelSheet.Name = "YourSheetName - " & Date.Now.Day & Date.Now.ToString("MMM") & Date.Now.ToString("yy")
excelCells = excelSheet.Cells
' excelCells.Font.Bold = True
PopulateSheet(dt, excelCells)
excelBook.SaveAs(filePath)
excelBook.Close()
excelExport.Quit()
ReleaseComObject(excelCells) : ReleaseComObject(excelSheet)
ReleaseComObject(excelSheets)
ReleaseComObject(excelBook) : ReleaseComObject(excelExport)
excelExport = Nothing : excelBook = Nothing : excelSheets = Nothing
excelSheet = Nothing : excelCells = Nothing
System.GC.Collect()
Return True
'End If
Catch ex As Exception
MsgBox(ex.Message, "Error exporting data")
Return False
End Try
End Function
Private Shared Sub PopulateSheet(ByVal dt As System.Data.DataTable, ByVal oCells As Microsoft.Office.Interop.Excel.Range)
Dim dRow As DataRow
Dim dataArray() As Object
Dim count As Integer
Dim column_count As Integer
'Output Column Headers
Dim Id As String = String.Empty
Dim mId As String = String.Empty
Id = dt.Columns(0).ColumnName & dt.Rows(0).Item("Id")
mId = dt.Columns(1).ColumnName & dt.Rows(0).Item("MatterIdString")
dt.Columns.RemoveAt(0)
dt.Columns.RemoveAt(0)
For column_count = 0 To dt.Columns.Count - 1
'oCells(4, 4) = " Kimmel and Silverman, P.C." & Environment.NewLine & "All Time for Selected File:" & Environment.NewLine & "Doll, Patricia v. Collection Specialists, Inc." & Environment.NewLine & clientId & Environment.NewLine & mId
oCells(1, 3) = "Kimmel and Silverman, P.C"
oCells(2, 3) = "All Time for Selected File:"
oCells(3, 3) = "Doll, Patricia v. Collection Specialists, Inc."
oCells(4, 3) = Id
oCells(5, 3) = mId
oCells(7, column_count + 1) = dt.Columns(column_count).ToString
'With oCells.Range(5, column_count + 1)
' .Font.Bold = True
'End With
With oCells(2, 3)
.Font.Bold = True
End With
With oCells(3, 3)
.Font.Bold = True
End With
With oCells(7, 1)
.Font.Bold = True
End With
With oCells(7, 2)
.Font.Bold = True
End With
With oCells(7, 3)
.Font.Bold = True
.WrapText = True
.EntireColumn.AutoFit()
End With
With oCells(7, 4)
.Font.Bold = True
End With
With oCells(7, 5)
.Font.Bold = True
End With
With oCells(7, 6)
.Font.Bold = True
End With
Next
'Output Data
For count = 0 To dt.Rows.Count - 1
dRow = dt.Rows.Item(count)
dataArray = dRow.ItemArray
For column_count = 0 To UBound(dataArray)
oCells(count + 8, column_count + 1) = dataArray(column_count).ToString
If (column_count + 1 = 3) Then
With oCells(count + 8, column_count + 1)
.WrapText = True
End With
End If
If (column_count + 1 = 4) Then
Dim Duration As Integer
Duration = dataArray(column_count).ToString()
Dim ts As New TimeSpan(0, 0, Duration)
Dim hrs As Double = 0.0
hrs = ts.Hours + (ts.Minutes / 60) + (ts.Seconds / 360)
oCells(count + 8, column_count + 1) = hrs
End If
Next
Next
End Sub
Dim excelExport As New Microsoft.Office.Interop.Excel.Application()
Dim excelBook As Microsoft.Office.Interop.Excel.Workbook
Dim excelSheets As Microsoft.Office.Interop.Excel.Sheets
Dim excelSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim excelCells As Microsoft.Office.Interop.Excel.Range
Dim location As Integer = System.Windows.Forms.Application.ExecutablePath.LastIndexOf("\")
'Dim exPath As String = System.Windows.Forms.Application.ExecutablePath
Dim filePath As String
Try
'If Not isInstalled(ApiEnums.OfficeApplications.Excel) Then
' MsgBox("Microsoft Excel is required for this functionality." & vbCrLf & _
' "Contact your Help Desk about getting " & vbCrLf & "this installed. Thank You.")
'Else
filePath = "C:\" & fileName
'If Not Directory.Exists(exPath.Substring(0, (location + 1)) + "tmpFiles\") Then
' Directory.CreateDirectory(exPath.Substring(0, (location + 1)) + "tmpFiles\")
'End If
excelExport.Visible = False : excelExport.DisplayAlerts = False
excelBook = excelExport.Workbooks.Add
excelSheets = excelBook.Worksheets
excelSheet = CType(excelSheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet)
excelSheet.Name = "YourSheetName - " & Date.Now.Day & Date.Now.ToString("MMM") & Date.Now.ToString("yy")
excelCells = excelSheet.Cells
' excelCells.Font.Bold = True
PopulateSheet(dt, excelCells)
excelBook.SaveAs(filePath)
excelBook.Close()
excelExport.Quit()
ReleaseComObject(excelCells) : ReleaseComObject(excelSheet)
ReleaseComObject(excelSheets)
ReleaseComObject(excelBook) : ReleaseComObject(excelExport)
excelExport = Nothing : excelBook = Nothing : excelSheets = Nothing
excelSheet = Nothing : excelCells = Nothing
System.GC.Collect()
Return True
'End If
Catch ex As Exception
MsgBox(ex.Message, "Error exporting data")
Return False
End Try
End Function
Private Shared Sub PopulateSheet(ByVal dt As System.Data.DataTable, ByVal oCells As Microsoft.Office.Interop.Excel.Range)
Dim dRow As DataRow
Dim dataArray() As Object
Dim count As Integer
Dim column_count As Integer
'Output Column Headers
Dim Id As String = String.Empty
Dim mId As String = String.Empty
Id = dt.Columns(0).ColumnName & dt.Rows(0).Item("Id")
mId = dt.Columns(1).ColumnName & dt.Rows(0).Item("MatterIdString")
dt.Columns.RemoveAt(0)
dt.Columns.RemoveAt(0)
For column_count = 0 To dt.Columns.Count - 1
'oCells(4, 4) = " Kimmel and Silverman, P.C." & Environment.NewLine & "All Time for Selected File:" & Environment.NewLine & "Doll, Patricia v. Collection Specialists, Inc." & Environment.NewLine & clientId & Environment.NewLine & mId
oCells(1, 3) = "Kimmel and Silverman, P.C"
oCells(2, 3) = "All Time for Selected File:"
oCells(3, 3) = "Doll, Patricia v. Collection Specialists, Inc."
oCells(4, 3) = Id
oCells(5, 3) = mId
oCells(7, column_count + 1) = dt.Columns(column_count).ToString
'With oCells.Range(5, column_count + 1)
' .Font.Bold = True
'End With
With oCells(2, 3)
.Font.Bold = True
End With
With oCells(3, 3)
.Font.Bold = True
End With
With oCells(7, 1)
.Font.Bold = True
End With
With oCells(7, 2)
.Font.Bold = True
End With
With oCells(7, 3)
.Font.Bold = True
.WrapText = True
.EntireColumn.AutoFit()
End With
With oCells(7, 4)
.Font.Bold = True
End With
With oCells(7, 5)
.Font.Bold = True
End With
With oCells(7, 6)
.Font.Bold = True
End With
Next
'Output Data
For count = 0 To dt.Rows.Count - 1
dRow = dt.Rows.Item(count)
dataArray = dRow.ItemArray
For column_count = 0 To UBound(dataArray)
oCells(count + 8, column_count + 1) = dataArray(column_count).ToString
If (column_count + 1 = 3) Then
With oCells(count + 8, column_count + 1)
.WrapText = True
End With
End If
If (column_count + 1 = 4) Then
Dim Duration As Integer
Duration = dataArray(column_count).ToString()
Dim ts As New TimeSpan(0, 0, Duration)
Dim hrs As Double = 0.0
hrs = ts.Hours + (ts.Minutes / 60) + (ts.Seconds / 360)
oCells(count + 8, column_count + 1) = hrs
End If
Next
Next
End Sub
Subscribe to:
Posts (Atom)