0

i want to do a "Save as" in my program in visual Studio but i have a problem...

This is the code where export datagridview to excel :

    If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then
        Exit Sub
    End If
    Dim dset As New DataSet
    dset.Tables.Add()
    For i As Integer = 0 To DataGridView1.ColumnCount - 1
        dset.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText)
    Next
    Dim dr1 As DataRow
    For i As Integer = 0 To DataGridView1.RowCount - 1
        dr1 = dset.Tables(0).NewRow
        For j As Integer = 0 To DataGridView1.Columns.Count - 1
            dr1(j) = DataGridView1.Rows(i).Cells(j).Value
        Next
        dset.Tables(0).Rows.Add(dr1)
    Next

    Dim excel As New Microsoft.Office.Interop.Excel.Application
    Dim wBook As Microsoft.Office.Interop.Excel.Workbook
    Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

    wBook = excel.Workbooks.Add()
    wSheet = wBook.ActiveSheet()

    Dim dt As System.Data.DataTable = dset.Tables(0)
    Dim dc As System.Data.DataColumn
    Dim dr As System.Data.DataRow
    Dim colIndex As Integer = 0
    Dim rowIndex As Integer = 0

    For Each dc In dt.Columns
        colIndex = colIndex + 1
        excel.Cells(1, colIndex) = dc.ColumnName
    Next

    For Each dr In dt.Rows
        rowIndex = rowIndex + 1
        colIndex = 0
        For Each dc In dt.Columns
            colIndex = colIndex + 1
            excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)

        Next
    Next

    wSheet.Columns.AutoFit()
    Dim strFileName As String = "D:\testehorario.xlsx"
    Dim blnFileOpen As Boolean = False
    Try
        Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
        fileTemp.Close()
    Catch ex As Exception
        blnFileOpen = False
    End Try

    If System.IO.File.Exists(strFileName) Then
        System.IO.File.Delete(strFileName)
    End If

    wBook.SaveAs(strFileName)
    excel.Workbooks.Open(strFileName)
    excel.Visible = True

and this is the code that does the "Save as":

Dim sfd As New SaveFileDialog() ' this creates an instance of the SaveFileDialog called "sfd"
sfd.Filter = "txt files (*.xlsx)|*.xlsx|All files (*.*)|*.*"
sfd.FilterIndex = 1
sfd.RestoreDirectory = True
If sfd.ShowDialog() = DialogResult.OK Then
    Dim FileName As String = sfd.FileName ' retrieve the full path to the file selected by the user
    Dim sw As New System.IO.StreamWriter(FileName, False) ' create a StreamWriter with the FileName selected by the User
    sw.WriteLine(TextBox1.Text) ' Write the contents of TextBox to the file
    sw.Close() ' close the file
End If

How can I export a datagridview to excel and make a Save As ? I have already tried using the "Save as" code in the top code but it gives error...

  • What error are you getting? – DaveP Jun 05 '17 at 09:35
  • When i run the program and make export a datagridview to excel, i save as in desktop. I open the excel file and gives error "Excel can't open the file ' URabc.xlsx ' because the format or file extension is not valid. Verify that the file is not damaged and the file extension corresponds to the format." –  Jun 05 '17 at 09:42
  • It is unclear exactly what the problem is, however I question the logic behind the first code snippet. As the code appears to work as expected, I question WHY you would make a `DataSet` then add a table to that data set then fill that table with the values from the `DataGridView`. THEN you use this table to make the `Excel` file???... It appears making this table is unnecessary, when you could simply loop through the `DataGridView` (which you are already doing to make the table) to export the data to an `Excel` file. You may want to explain why you are going through these unnecessary steps. – JohnG Jun 05 '17 at 23:15
  • In the second code snippet, it is clear you will get this `format or file extension is not valid` error because when you save the file using the `StreamWriter`, it will save a simple text file. You can put any extension you like for the file, however, simply making the extension an `xlsx` extension IS NOT going to make it a true `Excel` file. When you try to open it, you will get the error you describe because it is not a true `Excel` file. You will have to create a new `Excel` file using interop then add the text from the text box to that new file… then save it. – JohnG Jun 05 '17 at 23:16

0 Answers0