If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
excel formatting
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 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) xlWorkBook.Close() xlApp.Quit() xlWorkSheet = Nothing xlWorkBook = Nothing xlClApp = Nothing da4.Dispose() cmd4.Dispose() cnn4.Close() |
Thread Tools | |
Display Modes | |
|
|