Export to Excel Formatierung

  • VB.NET
  • .NET (FX) 4.5–4.8

Es gibt 9 Antworten in diesem Thema. Der letzte Beitrag () ist von icewather.

    Export to Excel Formatierung

    Servus,

    Mit meinem Programm exportiere ich eine DGV in eine Exceldatei. -> Funktioniert einwandfrei.

    Aber die Formatierung der fertigen Excelsheet ist nicht schön.

    Ist es möglich das DGV in eine bestehende Excel zu exportieren wo ich vorab bereits die Formatierung angepasst habe ?

    Gruß Danke vorab
    @StefanS0 Wie funktioniert Dein Export?
    Wie kommt die Formatierung an?
    Wie soll sie aussehen?
    Falls Du diesen Code kopierst, achte auf die C&P-Bremse.
    Jede einzelne Zeile Deines Programms, die Du nicht explizit getestet hast, ist falsch :!:
    Ein guter .NET-Snippetkonverter (der ist verfügbar).
    Programmierfragen über PN / Konversation werden ignoriert!
    Variante mit Export:
    DGV als CSV exportieren.
    Excel-Instanz starten.
    Öffnen einer vorformatierten .XLSX
    CSV als neues Workbook öffnen mit OpenTextFile
    Values vom CSV-Workbook in das XLSX-Workbook kopieren.
    CSV schliessen
    XLSX speichern und schliessen

    Direkte Variante:
    Excel-Application instantiieren.
    Vorformatiertes XLSX öffnen.
    DGV-Werte in die Zell-Values des XLSX kopieren
    XLSX speichern und schliessen
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    VB.NET-Quellcode

    1. Dim Pfadvonsave As String = SaveFileDialog2.FileName
    2. Dim oExcel As Object
    3. Dim oBook As Object
    4. Dim oSheet As Object
    5. ' Dim ssampleFolder As String = ""
    6. 'Start a new workbook in Excel.
    7. oExcel = CreateObject("Excel.Application")
    8. oBook = oExcel.Workbooks.Add
    9. 'Add data to cells of the first worksheet in the new workbook.
    10. oSheet = oBook.Worksheets(1)
    11. For excelschleife As Integer = 0 To SKlistDGV.Rows.Count - 1
    12. oSheet.Range("A" & (excelschleife + 1)).Value = SKlistDGV.Rows(excelschleife).Cells(0).Value
    13. Next
    14. 'Save the Workbook and quit Excel.
    15. oBook.SaveAs(Pfadvonsave) '& "new.xlsx")
    16. oSheet = Nothing
    17. oBook = Nothing
    18. oExcel.Quit()
    19. oExcel = Nothing
    20. GC.Collect()


    Derzeit funktioniert mein Export so, aber dieser Export braucht wegen der Schleife sehr lang.

    Die Variante direkt Export würde mir besser gefallen. Hättest du hierzu einen besseren Denkanstoß als mein beispiel ?
    Lg Danke
    Hey,

    du könntest es mal mit ClosedXMl versuchen, eventuell könnte das schneller sein.

    Nuget-Manager/Paketmanager in Visual Studio : Install-Package ClosedXML

    Hier kannst du z.b. auch eine Datatable direkt exportieren. Wenn du so eine Datasource hast brauchst du diese dann nichtmal erstellen.
    Beim Export wird auch die Spaltenbreite in Excel an den Inhalt angepasst.

    Hier ein kleines Beispiel:

    VB.NET-Quellcode

    1. Me.Cursor = Cursors.WaitCursor
    2. 'erstellt ein neues Workbook, wenn du ein bestehendes öffnen möchtest gibt es hier auch direkt einen Constructor mit Datei-Pfad
    3. Dim xWB As New XLWorkbook()
    4. Try
    5. 'Die StandardSheets brauchen wir nicht also weg damit
    6. For Each worksheet As IXLWorksheet In xWB.Worksheets
    7. worksheet.Delete()
    8. Next
    9. 'Hier wird nun ein neues Tablenblatt hinzugefügt mit dem Namen ExportFromDGV
    10. 'So geht das nur wenn die Datasource eine Datatable ist sonst muss diese vorher noch aufgebaut werden
    11. xWB.AddWorksheet(CType(Me.DataGridViewMain.DataSource, DataTable), "ExportFromDGV")
    12. 'So kannst du dann Ranges formatieren wie du möchtest unter anderem auch eigene Zahlenformatierungen
    13. xWB.Worksheets(0).Range("B2:C4").DataType = XLCellValues.Number
    14. xWB.SaveAs("C:\Users\tt\Desktop\test.xlsx")
    15. Catch ex As Exception
    16. MsgBox("Es ist ein Fehler beim Erstellen der Excel-Datei aufgetretten(""" & ex.Message & """)")
    17. Finally
    18. If xWB IsNot Nothing Then xWB.Dispose()
    19. Me.Cursor = Cursors.Default
    20. End Try

    Das sieht spannend aus, weil die Microsoft-Interop-Variante extrem langsam ist. Doch ich erhalte zwei Fehler:

    1. Bei der Definition Dim xWB As New XLWorkbook()
    kommt die Fehlermeldung "XLWorkbook.cs" wurde nicht gefunden
    ClosedXMl wurde korrekt installiert - mit Erfolgsmeldung und ohne Fehler. Doch auf meiner Festplatte ist die Datei nicht zu finden.

    Hier die detaillierte Meldung:
    Quelle für "C:\Git\ClosedXML\ClosedXML\Excel\XLWorkbook.cs" wird gesucht. Checksum: SHA1 {9b 17 1a 9 dd 2e f1 83 cb ee 9f c6 f4 67 5b 0 5a e0 a8 5a}
    Die Datei "C:\Git\ClosedXML\ClosedXML\Excel\XLWorkbook.cs" ist nicht vorhanden.
    Das Verzeichnis c:\git\closeXML... gibt es nicht

    2. Fehler hier: xWB.AddWorksheet(CType(foStart.grRes.DataSource, DataTable), "impressExport")
    "Das Objekt des Typs "System.Windows.Forms.BindingSource" kann nicht in Typ "System.Data.DataTable" umgewandelt werden."

    Das DGV habe ich meines Erachtens korrekt initialisiert:

    Public daRes As New OleDbDataAdapter
    Public dvRes As New DataView
    Public dtRes As New DataTable
    Public dsRes As New DataSet
    Public bsRes As New BindingSource
    daRes = New OleDbDataAdapter("SELECT " & sqlgrResSelect & " FROM " & sqlgrResFrom & " WHERE " & sqlgrResWhere & " ORDER BY " & sqlgrResOrder, dbConn)
    dtRes = New DataTable("Ressourcen")
    dbConn.Open()
    daRes.Fill(dtRes)
    dbConn.Close()
    Dim bsRes As New BindingSource
    dvRes = New DataView(dtRes)
    bsRes.DataSource = dvRes
    DGV.DataSource = bsRes

    Das ist alles reichlich kompliziert. Vielleicht könnte ich es auch anders machen. Liegt der Fehler hier irgendwo?
    Danke für eine Antwort.
    Zu 1.
    Schwer zu sagen wo der Fehler liegt. An sich braucht man auch nur die ClosedXML.dll.
    Ich würde diese an deiner Stelle einfach runterladen und dann darauf verweisen.

    Zu 2.
    Deine Datasource ist eine Bindingsource und keine Datatable.
    Entweder du speicherst dir die Datatable in eine extra Variable oder du machst eine dementsprechende Umwandlung.
    So in etwa: ctype(ctype(foStart.grRes.DataSource, BindingSource).datasource, dataview).table

    Eventuell kannst du ja die BindingSource weglassen wenn du diese nicht benötigst (Kannst die DataView direkt an die Datasource der DGV übergeben).

    Grüße
    Danke Korni für deine Antwort

    Ich bleibe bei Fehler 1 hängen und komme nicht weiter. Wenn ich im Debug-Modus mehrfach F11 drücke, bleibt der Cursor auf der Zeile <Dim xWB As New XLWorkbook()> stehen und VS2015 bringt eine Fehlermeldung nach der anderen:
    - Die Quelldatei "C:\Git\ClosedXML\ClosedXML\Excel\Style\XLWorkbook.cs wurde nicht gefunden.
    - Die Quelldatei "C:\Git\ClosedXML\ClosedXML\Excel\Style\XLWorkbook_Load.cs" wurde nicht gefunden
    - Die Quelldatei "C:\Git\ClosedXML\ClosedXML\Excel\Style\XLStyle.cs" wurde nicht gefunden.
    - Die Quelldatei "C:\Git\ClosedXML\ClosedXML\Excel\Style\XLFont.cs" wurde nicht gefunden.
    - Die Quelldatei "C:\Git\ClosedXML\ClosedXML\Excel\Style\XLAlignment.cs" wurde nicht gefunden.
    usw.

    Ich habe folgende imports:
    'Imports Microsoft.Office.Interop ausgeschaltet
    'Imports Microsoft.Office.Interop.Excel ausgeschaltet
    Imports System.Globalization
    Imports System.Data.OleDb
    Imports ClosedXML.Excel

    Die Verweise auf ClosedXML.dll und DocumentFormat.OpenXml.dll sind korrekt gesetzt.

    Hmm, weiss nicht weiter und habe auf dem Web nichts gefunden. Es sieht so aus, als würde die dll nur teilweise funktionieren. Dieser ominöse Pfad c:\git... gibt es auf meiner Festplatte nicht. Auch kein einziges File, nachdem gesucht wird. Weiss jemand weiter?

    Danke, Beat
    Nun habe ich das Problem anders gelöst. Es gibt eine coole Funktion, mit der man ein RecordSet direkt in Excel einfügen kann. Blitzschnell!! Schade ist nur, dass die HeaderZeile nicht mitkommt. Diese muss man aus dem DGV mit einer Schlaufe reinkopieren. Nur diese eine einzige Zeile mit den Headern zu füllen dauert länger, als 300 Datensätze nach Excel zu kopieren:

    Public Sub ExportExcel()
    Dim xlApp As New Microsoft.Office.Interop.Excel.Application
    Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
    Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value
    Dim vRow As Object = 0
    Dim vCol As Object = 0
    Dim rs As ADODB.Recordset
    dbAdoConn.ConnectionString = "PROVIDER=Microsoft.ACE.OLEDB.12.0; Data Source=" + vdbPath
    dbAdoConn.Open()
    rs = dbAdoConn.Execute("SELECT " & sqlSelect & " FROM " & sqlFrom & " WHERE " & sqlWhere & " ORDER BY '" & sqlOrder & "'")
    rs.MoveFirst()
    xlWorkBook = xlApp.Workbooks.Add(misValue)
    xlWorkSheet = xlWorkBook.Sheets("Tabelle1")
    With grRes 'DGV
    vCol = .ColumnCount
    vRow = .RowCount
    For j = 0 To .ColumnCount - 1
    For k As Integer = 1 To .Columns.Count
    xlWorkSheet.Cells(1, k) = .Columns(k - 1).HeaderText
    Next
    Next
    End With
    xlWorkSheet.Range("A2").CopyFromRecordset(rs)

    ' verschiedene Möglichkeiten, in Excel automatisch zu formatieren - funktioniert bei mir noch nicht so richtig
    'xlWorkSheet.Columns.AutoFit()
    xlWorkSheet.Cells(vRow, vCol).AutoFormat(ExcelAutoFormat.xlRangeAutoFormatColor1)

    xlApp.Visible = True
    End Sub