visual bais output written to excel
-
I have the following excel 2003 formatting to ask: I am writing a Visual Basic.Net 2005 desktop application write out data from a sql server 2005 database to a excel 2003 spreadsheet. After you look at my questions you can see a portion of the code that I am currently working with. if you can answer any of the questions below, I would appreciate it. I would like to know how to change the following code to do the following: 1. I would like to have the center data in most of the columns including the column headers. 2. I would like to see all the data in all the cels including the column headers. 3. I would like the last column of the table to 'wraparound' to the next line and display no more that 75 to 100 characters in a line. (The last column is varchar(500) due to the messages that could be displayed.) 4. I would like the column headers to be BOLD and the font be Times new Roman and be able to control the font size. 5. I would like the detail lines to be Times New Roman in a regular font size. 6. Some of the column headers are larger than the detail column. Thus I need the column headers to wrap. Some of the column headers are larger four words long. Thus, I do not want the column headers to 'wrap' in the middle of a word. I would like the column headers only to wrap between words. Private Sub ShowReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim xlApp As Excel.Application Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet Dim misValue As Object = System.Reflection.Missing.Value Dim xlClApp As Excel.ApplicationClass Dim excelFile As String = " " Dim dt As DataTable Dim da4 As SqlDataAdapter cnn4 = New SqlConnection(connectionString) cmd4 = New SqlCommand("stored procedure name", cnn4) cmd4.CommandType = CommandType.StoredProcedure With cmd4 .Parameters.Add("@parm1", SqlDbType.VarChar).Value = strParm1 .Parameters.Add("@parm2", SqlDbType.VarChar).Value = strParm2 End With da4 = New SqlDataAdapter(cmd4) da4.Fill(ds4) xlApp = New Excel.Application xlClApp = New Excel.ApplicationClass xlApp.Workbooks.Add() xlWorkBook = xlApp.Workbooks.Add(misValue) xlWorkSheet = CType(xlWorkBook.Sheets("sheet1"), Microsoft.Office.Interop.Excel.Worksheet) xlApp.Visible = False xlApp.ScreenUpdating = True dt = ds4.Tables(0) 'Add the column headings for the from the dataset Dim dc As DataColumn Dim
-
I have the following excel 2003 formatting to ask: I am writing a Visual Basic.Net 2005 desktop application write out data from a sql server 2005 database to a excel 2003 spreadsheet. After you look at my questions you can see a portion of the code that I am currently working with. if you can answer any of the questions below, I would appreciate it. I would like to know how to change the following code to do the following: 1. I would like to have the center data in most of the columns including the column headers. 2. I would like to see all the data in all the cels including the column headers. 3. I would like the last column of the table to 'wraparound' to the next line and display no more that 75 to 100 characters in a line. (The last column is varchar(500) due to the messages that could be displayed.) 4. I would like the column headers to be BOLD and the font be Times new Roman and be able to control the font size. 5. I would like the detail lines to be Times New Roman in a regular font size. 6. Some of the column headers are larger than the detail column. Thus I need the column headers to wrap. Some of the column headers are larger four words long. Thus, I do not want the column headers to 'wrap' in the middle of a word. I would like the column headers only to wrap between words. Private Sub ShowReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim xlApp As Excel.Application Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet Dim misValue As Object = System.Reflection.Missing.Value Dim xlClApp As Excel.ApplicationClass Dim excelFile As String = " " Dim dt As DataTable Dim da4 As SqlDataAdapter cnn4 = New SqlConnection(connectionString) cmd4 = New SqlCommand("stored procedure name", cnn4) cmd4.CommandType = CommandType.StoredProcedure With cmd4 .Parameters.Add("@parm1", SqlDbType.VarChar).Value = strParm1 .Parameters.Add("@parm2", SqlDbType.VarChar).Value = strParm2 End With da4 = New SqlDataAdapter(cmd4) da4.Fill(ds4) xlApp = New Excel.Application xlClApp = New Excel.ApplicationClass xlApp.Workbooks.Add() xlWorkBook = xlApp.Workbooks.Add(misValue) xlWorkSheet = CType(xlWorkBook.Sheets("sheet1"), Microsoft.Office.Interop.Excel.Worksheet) xlApp.Visible = False xlApp.ScreenUpdating = True dt = ds4.Tables(0) 'Add the column headings for the from the dataset Dim dc As DataColumn Dim
Most of your questions can be answered by recording macros from with Excel itself, and examining the resultant VBA code. Since you have already created the requisite Excel objects within your application, the VBA code can more or less be directly used in your code, with the correct references. HTH
-
Most of your questions can be answered by recording macros from with Excel itself, and examining the resultant VBA code. Since you have already created the requisite Excel objects within your application, the VBA code can more or less be directly used in your code, with the correct references. HTH
ChandraRam: I have the following questions to ask you which include: 1. I am not certain of when I can use a recording macro in excel? I basically need to have the visual basic.net 2005 desktop application control the processing since it is the one the drives the generation of separate excel worksheets in distinct workbooks. (There is one worksheet per workbook since each spreadsheet is email and to differet customers.) 2. When I use the recroding macro, when it be before I create the excel object, while I am putting data into the excel object, or after the excel worksheet has been generated? 3. I am not certain where you are refering to using the VBA code. Am I including the VBA code in the following .NET code: xlApp = New Excel.Application xlClApp = New Excel.ApplicationClass xlApp.Workbooks.Add() xlWorkBook = xlApp.Workbooks.Add(misValue) xlWorkSheet = CType(xlWorkBook.Sheets("sheet1"), Microsoft.Office.Interop.Excel.Worksheet) xlApp.Visible = False xlApp.ScreenUpdating = True dt = ds4.Tables(0) 'Add the column headings for the from the dataset Dim dc As DataColumn Dim iCols As Int32 = 0 For Each dc In dt.Columns xlWorkSheet.Range("A1").Offset(0, iCols).Value = dc.ColumnName xlWorkSheet.Range("A1").Offset(0, iCols).Font.Bold = True xlWorkSheet.Range("A1").Offset(0, iCols).BorderAround() iCols += 1 Next Dim iRows As Int32 For iRows = 0 To dt.Rows.Count - 1 xlWorkSheet.Range("A2").Offset(0, iCols).Select() xlWorkSheet.Range("A2").Offset(0, iCols).Justify() xlWorkSheet.Range("A2").Offset(0, iCols).WrapText = True xlWorkSheet.Range("A2").Offset(iRows).Resize(1, iCols).Value = _ dt.Rows(iRows).ItemArray() xlWorkSheet.Range("A2").Offset(0, iCols).BorderAround() Next excelFile = "c:\exceltst.xls" xlWorkBook.Sheets("sheet1").SaveAs(excelFile) 4. How can I use VBA code here? 5. Here is my code so far: Private Sub ShowReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim xlApp As Excel.Application Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet Dim misValue As Object = System.Reflection.Missing.Value Dim xlClApp As Excel.ApplicationClass Dim excelFile As String = " " Dim dt As DataTable Dim da4 As SqlDataAdapter cnn4 = New SqlConnection(connectionString) cmd4 = New SqlCommand("stored procedure name", cnn4) cmd4.CommandType = CommandType.StoredProcedure With cmd4 .Parameters.Add("@parm1", SqlDbType.VarChar).Value = strParm1 .Parameters.Add("@pa