question

Hobbyistprogrammer-7674 avatar image
0 Votes"
Hobbyistprogrammer-7674 asked XingyuZhao-MSFT answered

Datagridview to Excel with formatting

Hallo,

I am looking for a code to export Datagridview contents with its formatting to Excel. I have found following as potential solution but i dont know how to use it. Could anyone please let me know how to use the imported dll. I dont find any documentation on how to use it.

https://archive.codeplex.com/?p=exporttoexcel

thanks

dotnet-visual-basic
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Can you be more specific with formatting as the formatting done in a DataGridView does not match that of a Excel WorkSheet.

0 Votes 0 ·

Lets say i want only colors of the header row and alternating row color and cell borders

0 Votes 0 ·
SimpleSamples avatar image
0 Votes"
SimpleSamples answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hobbyistprogrammer-7674 avatar image
0 Votes"
Hobbyistprogrammer-7674 answered

Hallo ,

i am close to get the desired result. i am struggling to set row font style to datagridview row font style (e.g Bold or regular) below is my code . i want to set my excel row font to bold if my datagridview row is bold. Any idea what is wrong here? thanks

                         With xlSH.Range(xlSH.Cell(i + 2, 1), xlSH.Cell(i + 2, jk))
                             .Style.Fill.BackgroundColor = XLColor.FromArgb(dgv.Rows(i).DefaultCellStyle.BackColor.ToArgb)
                             .Style = dgv.Rows(i).DefaultCellStyle.Font.Style
                             .Style.Font.FontColor = XLColor.FromArgb(dgv.Rows(i).DefaultCellStyle.ForeColor.ToArgb)
                         End With
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

XingyuZhao-MSFT avatar image
0 Votes"
XingyuZhao-MSFT answered

Hi @Hobbyistprogrammer-7674 ,
As suggested in the reference SimpleSamples provided:
https://stackoverflow.com/a/39314154/12666543
I make a test on my side, and the code works for me.
Data in my datagridview.
125367-1.png
Result in Excel.
125478-2.png
I use it this way.

 Imports Excel = Microsoft.Office.Interop.Excel
    
     Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
         DataGridView1.AllowUserToAddRows = False
         Dim dgvToHTMLTable As String = ConvertDataGridViewToHTMLWithFormatting(DataGridView1)
         Clipboard.SetText(dgvToHTMLTable)
    
         Dim xlWorkBook As Excel.Workbook
         Dim xlWorkSheet As Excel.Worksheet
         Dim xlApp As Excel.Application = New Excel.Application()
         xlApp.Visible = False
         xlApp.UserControl = True
    
         xlWorkBook = xlApp.Workbooks.Add(Type.Missing)
         xlApp.ActiveWorkbook.Sheets(1).Activate()
         xlWorkSheet = CType(xlWorkBook.Worksheets(1), Excel.Worksheet)
         xlWorkSheet.PasteSpecial(Missing.Value, False, False, Missing.Value, Missing.Value, Missing.Value, Missing.Value)
         xlWorkBook.SaveAs("your file path")
    
         xlWorkBook.Close()
         xlApp.Quit()
    
     End Sub

Hope it could be helpful.

Best Regards,
Xingyu Zhao


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.







1.png (5.5 KiB)
2.png (6.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.