Formatting Excel in VB.NET

Question

I am exporting DataGridViews to Excel using ClipboardCopyMode. However, I want to format the header and the cell width/height, because the cells aren't adjusting normally.

Also, I want to add some data in it apart of the data from the DataGridView.

This is the code of the sub I have:

     Dim appExcel As Excel.Application
     Dim wbExcel As Excel.Workbook

            System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("es-US")

            If Combo.SelectedValue.ToString().Trim() = "Something" Then

                dgv.SelectAll()
                dgv.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
                Clipboard.SetDataObject(dgv.GetClipboardContent())

            End If

      appExcel = New Excel.Application
      appExcel.SheetsInNewWorkbook = 1
      wbExcel = appExcel.Workbooks.Add
      appExcel.Visible = True

      wbExcel.Worksheets(1).Range("A3").Select()
      wbExcel.Worksheets(1).Paste()

How can I do these things and beautify my exported Excel?

Thanks in advance

soohoonigan · Accepted Answer · 2017-06-09 12:51:51Z

1

To style the cells, you can create an Excel.Style and apply it to a range. First, you need to get a reference to the worksheet like:

Dim xlWorksheet As Excel.Worksheet = wbExcel.Worksheets(1)

Then Create the style:

Dim RptHeader As Excel.Style = xlWorksheet.Application.ActiveWorkbook.Styles.Add("RptHeader")
RptHeader.Font.Bold = True
RptHeader.Font.Size = 14
RptHeader.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSteelBlue)
RptHeader.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

Then set a range/cell's style (For a range, just use something like xlWorksheet.Range("A1:A2").Style):

xlWorksheet.Cells(1, 1).Style = "RptHeader"

...As for cell width/height, the basic syntax looks like:

xlWorksheet.Columns(1).EntireColumn.ColumnWidth = 25
xlWorksheet.Rows(1).RowHeight = 25

When you say the columns aren't "adjusting normally" though, I get the sense you're talking about autofit, which can also be accomplished like so:

xlWorksheet.Range("A:XFD").EntireColumn.AutoFit()

To add data that is not part of your copy from the datagridview, simply set a cell's value to whatever you want like:

xlWorkSheet.Cells(1, 2) = "1234" '1 being the row-index, 2 the column-index

edited Jun 9, 2017 at 12:51

answered Jun 8, 2017 at 22:02

soohoonigan

2,3602 gold badges12 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

KPavezC Over a year ago

Thanks for your answer, and well, I encountered useful the columnwidth property, but strangely, when I put the AutoFit(), it does not correct the cells. Also, when I apply the Style, it places just after my table instead of in the header of my table. And well, I added a bit of code that I forgot to put in my question

KPavezC Over a year ago

Now I did some editing and the style property works almost correctly

Collectives™ on Stack Overflow

Formatting Excel in VB.NET

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related