Create XL sheet or CSV from vb.net
-
I'm using vb.net with sql server. Wanted to know the least painful way of creating an Excel sheet or CSV populating it with data either from a datagrid or datatable or anyway, really. Thanks for any help!
"least painful" -LOL here's a few ways: http://support.microsoft.com/default.aspx?scid=kb;EN-US;306022
-
"least painful" -LOL here's a few ways: http://support.microsoft.com/default.aspx?scid=kb;EN-US;306022
I'm new at this, and I don't recognize a way that matches my setup. I already have a connection established and I like to use stored procedures. Below is how I retrieve data from the stored procedure. How do I go from here into an Excel sheet or CSV? *********************************************************** Dim ClientView As DataTable = DirectCast(Session("ViewClients"), DataTable) ClientView = New DataTable() Dim sc As New SSS.BusinessLogicLayer.Mgmt() ClientView = sc.GetClientView(Session("FAgentIDView"), Session("RB")) Session("ViewClients") = ClientView Dim dr As DataRow = DirectCast(Session("ViewClients"), DataTable).Rows(0) ********************************************************** Public Shared Function GetClientView(ByVal FAgentID As String, ByVal RB As String) As DataTable Dim ds As DataSet = SqlHelper.ExecuteDatase(ConfigurationSettings.AppSettings(LTCMgmt.SSS.Web.Global.CfgKeySSSString), "usp_tp_MailViewSetup", FAgentID, RB) Dim dt As New DataTable() dt.Columns.Add("ClientID") dt.Columns.Add("FName") dt.Columns.Add("LName") dt.Columns.Add("Address1") dt.Columns.Add("Address2") dt.Columns.Add("City") dt.Columns.Add("State") dt.Columns.Add("PostalCode") dt.Columns.Add("Voice") dt.Columns.Add("Fax") dt.Columns.Add("Mobile") dt.Columns.Add("EMail") dt.Columns.Add("BirthDate") dt.Columns.Add("MaritalStatus") Dim r As DataRow Dim workRow As DataRow For Each r In ds.Tables(0).Rows workRow = dt.NewRow Dim nOne As String nOne = r("BirthDate") nOne = Replace(nOne, " 12:00:00 AM", "") workRow("ClientID") = r("ClientID") workRow("FName") = r("FName") workRow("LName") = r("LName") workRow("Address1") = r("Address1") workRow("Address2") = r("Address2") workRow("City") = r("City") workRow("State") = r("State") workRow("PostalCode") = r("PostalCode") workRow("Voice") = r("Voice") workRow("Fax") = r("Fax") workRow("Mobile") = r("Mobile") workRow("EMail") = r("EMail") workRow("BirthDate") = nOne workRow("MaritalStatus") = r("MaritalStatus") dt.Rows.Add(workRow) Next Return dt End Function ***************************************************
-
I'm new at this, and I don't recognize a way that matches my setup. I already have a connection established and I like to use stored procedures. Below is how I retrieve data from the stored procedure. How do I go from here into an Excel sheet or CSV? *********************************************************** Dim ClientView As DataTable = DirectCast(Session("ViewClients"), DataTable) ClientView = New DataTable() Dim sc As New SSS.BusinessLogicLayer.Mgmt() ClientView = sc.GetClientView(Session("FAgentIDView"), Session("RB")) Session("ViewClients") = ClientView Dim dr As DataRow = DirectCast(Session("ViewClients"), DataTable).Rows(0) ********************************************************** Public Shared Function GetClientView(ByVal FAgentID As String, ByVal RB As String) As DataTable Dim ds As DataSet = SqlHelper.ExecuteDatase(ConfigurationSettings.AppSettings(LTCMgmt.SSS.Web.Global.CfgKeySSSString), "usp_tp_MailViewSetup", FAgentID, RB) Dim dt As New DataTable() dt.Columns.Add("ClientID") dt.Columns.Add("FName") dt.Columns.Add("LName") dt.Columns.Add("Address1") dt.Columns.Add("Address2") dt.Columns.Add("City") dt.Columns.Add("State") dt.Columns.Add("PostalCode") dt.Columns.Add("Voice") dt.Columns.Add("Fax") dt.Columns.Add("Mobile") dt.Columns.Add("EMail") dt.Columns.Add("BirthDate") dt.Columns.Add("MaritalStatus") Dim r As DataRow Dim workRow As DataRow For Each r In ds.Tables(0).Rows workRow = dt.NewRow Dim nOne As String nOne = r("BirthDate") nOne = Replace(nOne, " 12:00:00 AM", "") workRow("ClientID") = r("ClientID") workRow("FName") = r("FName") workRow("LName") = r("LName") workRow("Address1") = r("Address1") workRow("Address2") = r("Address2") workRow("City") = r("City") workRow("State") = r("State") workRow("PostalCode") = r("PostalCode") workRow("Voice") = r("Voice") workRow("Fax") = r("Fax") workRow("Mobile") = r("Mobile") workRow("EMail") = r("EMail") workRow("BirthDate") = nOne workRow("MaritalStatus") = r("MaritalStatus") dt.Rows.Add(workRow) Next Return dt End Function ***************************************************
I can't test this with your data source of course on my end, and you'll have to de-bug it over there too, but it should give you an idea of how I would go about getting it to go. You could call somthing like this on your return from GetClientView with your DataTable dt. Private Function OpenWorkbook(ByVal dt As DataTable) Dim oExcel As New Excel.Application Dim oBook As Excel.Workbook Dim oBooks As Excel.Workbooks Dim oSheet As Excel.Worksheet Dim oSheets As Excel.Sheets Dim oCells As Excel.Range Dim dr As DataRow Dim ary() As Object Dim iRow As Integer, iCol As Integer 'Start a new workbook in Excel. oExcel = CreateObject("Excel.Application") oBook = oExcel.Workbooks.Add oBook = oBooks.Item(1) oSheets = oBook.Worksheets oSheet = CType(oSheets.Item(1), Excel.Worksheet) oSheet.Name = "First Sheet" oCells = oSheet.Cells 'Output Column Headers For iCol = 0 To dt.Columns.Count - 1 oCells(2, iCol + 1) = dt.Columns(iCol).ToString Next 'Output Data For iRow = 0 To dt.Rows.Count - 1 dr = dt.Rows.Item(iRow) ary = dr.ItemArray For iCol = 0 To UBound(ary) oCells(iRow + 3, iCol + 1) = ary(iCol).ToString Next Next 'Save the workbook and quit Excel. oBook.SaveAs(Application.StartupPath & "Book1.xls") oSheet = Nothing oBook = Nothing oExcel.Quit() oExcel = Nothing GC.Collect() End Function Note: Don't forget to add the Com reference to Excel10 in your Solution Explorer.
-
I can't test this with your data source of course on my end, and you'll have to de-bug it over there too, but it should give you an idea of how I would go about getting it to go. You could call somthing like this on your return from GetClientView with your DataTable dt. Private Function OpenWorkbook(ByVal dt As DataTable) Dim oExcel As New Excel.Application Dim oBook As Excel.Workbook Dim oBooks As Excel.Workbooks Dim oSheet As Excel.Worksheet Dim oSheets As Excel.Sheets Dim oCells As Excel.Range Dim dr As DataRow Dim ary() As Object Dim iRow As Integer, iCol As Integer 'Start a new workbook in Excel. oExcel = CreateObject("Excel.Application") oBook = oExcel.Workbooks.Add oBook = oBooks.Item(1) oSheets = oBook.Worksheets oSheet = CType(oSheets.Item(1), Excel.Worksheet) oSheet.Name = "First Sheet" oCells = oSheet.Cells 'Output Column Headers For iCol = 0 To dt.Columns.Count - 1 oCells(2, iCol + 1) = dt.Columns(iCol).ToString Next 'Output Data For iRow = 0 To dt.Rows.Count - 1 dr = dt.Rows.Item(iRow) ary = dr.ItemArray For iCol = 0 To UBound(ary) oCells(iRow + 3, iCol + 1) = ary(iCol).ToString Next Next 'Save the workbook and quit Excel. oBook.SaveAs(Application.StartupPath & "Book1.xls") oSheet = Nothing oBook = Nothing oExcel.Quit() oExcel = Nothing GC.Collect() End Function Note: Don't forget to add the Com reference to Excel10 in your Solution Explorer.