Office Interop: CSV als Excel speichern --> Formatierung falsch

  • VB.NET
  • .NET (FX) 4.0

Es gibt 17 Antworten in diesem Thema. Der letzte Beitrag () ist von KlyX.

    Office Interop: CSV als Excel speichern --> Formatierung falsch

    Hallo zusammen,

    ich exportiere den Inhalt eines DGV nach Excel. Nachdem das mit Interop selbst bei sehr wenigen Daten viel zu lange dauert, mache ich den Umweg über CSV (was zigfach schneller ist).
    Der Export des CSV klappt soweit ganz gut. Wenn ich es danach mit Excel öffne, sieht es perfekt aus.
    Code:

    VB.NET-Quellcode

    1. Dim StrExport As String = ""
    2. For Each C As DataGridViewColumn In db_data.db_data_report.Columns
    3. StrExport &= """" & C.HeaderText & """;"
    4. Next
    5. StrExport = StrExport.Substring(0, StrExport.Length - 1)
    6. StrExport &= Environment.NewLine
    7. For Each R As DataGridViewRow In db_data.db_data_report.Rows
    8. For Each C As DataGridViewCell In R.Cells
    9. If Not C.Value Is Nothing Then
    10. StrExport &= """" & C.Value.ToString & """;"
    11. Else
    12. StrExport &= """" & "" & """;"
    13. End If
    14. Next
    15. StrExport = StrExport.Substring(0, StrExport.Length - 1)
    16. StrExport &= Environment.NewLine
    17. Next
    18. Dim tw As System.IO.TextWriter = New System.IO.StreamWriter("test.csv", False, System.Text.Encoding.Default)
    19. tw.Write(StrExport)
    20. tw.Close()


    Nun öffne ich die CSV über Excel-Interop und speichere das File ab:

    VB.NET-Quellcode

    1. 'CSV in Excel schreiben
    2. Dim app As Microsoft.Office.Interop.Excel.Application
    3. Dim wb As Microsoft.Office.Interop.Excel.Workbook
    4. Dim ws As Microsoft.Office.Interop.Excel.Worksheet
    5. Dim misValue As Object = System.Reflection.Missing.Value
    6. app = New Microsoft.Office.Interop.Excel.Application
    7. wb = app.Workbooks.Open("test.csv")
    8. ws = wb.Sheets(1)
    9. ws.SaveAs("test.xlsx", XlFileFormat.xlOpenXMLWorkbook)
    10. wb.Close()
    11. app.Quit()
    12. releaseObject(app)
    13. releaseObject(wb)
    14. releaseObject(ws)
    15. Exit Sub


    Wenn ich das Ergebnis nun in Excel speichere, sieht es komplett daneben aus...
    Woran kann das liegen? Was mache ich falsch?

    Das CSV wird mit ; getrennt und hat die einzelnen Werte noch mit " " ummantelt. Müsste ja soweit eigentlich passen oder?

    Wie die Excel, das CSV im Excel und das CSV im Texteditor aussieht hab ich euch angehängt.

    Danke für Tipps :)
    LG,
    KlyX
    Bilder
    • csv text.JPG

      21,75 kB, 458×65, 109 mal angesehen
    • csv.JPG

      33,47 kB, 915×77, 124 mal angesehen
    • excel.JPG

      39,17 kB, 767×122, 122 mal angesehen
    Chris' Weblog - Mein Blog rund um Vieles :D
    jo komisch. also es ist Excel, was sein eigenes Format - xlsx - nicht korrekt schreibt?

    ansonsten fällt mir bischen auf:
    1. du öffnest die csv mit Workbooks.Open(), und erhälst ein Workbook.
    2. Dann aber speicherst du ein worksheet - also garnet das Workbook.
    3. Und du speicherst es mit XlFileFormat.xlOpenXMLWorkbook - also ein sheet - was garkein Workbook ist, speicherstu du mit ...Workbook, und zwar mit OpenXmlWorkbook - also das scheint mir vom Namen her überhaupt nicht angemessen zu sein.
    Hi Erfindein,

    dein erster Satz triffts ganz gut ;)
    Zu den Auffälligkeiten: ich geb zu, so genau hab ich das nicht beachtet.
    Meines Wissens muss ich doch zwingend ein Workbook aufmachen und da dann das Sheet.
    Also mein:

    VB.NET-Quellcode

    1. wb = app.Workbooks.Open("C:\Users\Christoph\ownCloud\test.csv")
    2. ws = wb.Sheets(1)


    mit sheets kann ich zumindest nix öffnen ;)

    Beim Speichern kann man meines Wissens sowohl workbook oder worksheet speichern. Kommt das nicht aufs Gleiche raus?
    Hab zumindest jetzt mal die Speichern-Zeile angepasst und speichere jetzt das Workbook.

    VB.NET-Quellcode

    1. wb.SaveAs("C:\Users\Christoph\ownCloud\test.xlsx", XlFileFormat.xlOpenXMLWorkbook, Local:=True)


    Als FileFormat gibt es übrigens als XSLX nur das xlOpenXMLWorkbook (mal abgesehen von der Variante mit Makros, die mich nicht interessiert). Also ist das soweit schon richtig.
    Habe noch geschaut, ob ich mit "Local:=True" die Standardeinstellung fürs Semikolon als Trenner definieren kann.
    Leider keine Änderung...

    Nachtrag: wenn ich statt Semikolon mit Kommata arbeite, dann frisst Excel es soweit :)
    ABER: dafür werden andere Textblöcke unterbrochen. Gibt es hier einen Parameter, damit das nicht passiert? Ich habe einen Textblock in der Auswertung drin, der fast alle Zeichen enthalten kann inkl. Umbrüche. Dagegen ist leider wohl auch kein " " gefeit. Was kann ich da tun? :)

    Zweiter Nachtrag: oder gibt es eine <<schnelle>> Lösung, für Daten nach Excel zu exportieren, ohne dass man über CSV geht? Interop ist ja selbst bei 10-20 Zeilen saulahm :(.
    Dann hätte ich den ganzen CSV-Quark weg ;).

    Chris' Weblog - Mein Blog rund um Vieles :D

    Dieser Beitrag wurde bereits 2 mal editiert, zuletzt von „KlyX“ ()

    Hallo @KlyX,

    wenn Du mit CSV arbeiten willst, warum machst Du es Dir so schwer und packst deine Werte in double quotes?
    Das zweite beim Laden der Textdatei in Excel. Mein Vorschlag wäre die Methode Worksheet.QueryTables.Add zu verwenden.
    Dabei hast Du u.a. die Möglichkeit Trennzeichen und auch DatenTypen der Spalten festzulegen.

    Solltest Du ohne CSV auskommen wollen, solltest Du mit Arrays arbeiten und die als Range an Excel übergeben. Auch umgekehrt (Bereiche aus Execl auslesen) ist das die einzig schnelle Methode.
    Warum Du dazu in dem DGV rumpuhlen willst ist mir schleierhaft. Das DGV sollte an einer Tabelle hängen (direkt oder über Bindigssource), warum also nicht direkt eine Abfrage in ein Array leiten?
    HI Dkskm,

    danke für dein Feedback.
    Die Werte sind in Double Quotes, damit ich keinerlei Probleme mit dem einen Textfeld bekomme, indem Quasi alles drin steht. Dann sieht nämlich das generierte CSV gut aus. Lasse ich die Double Quotes weg, verschluckt sich das CSV bei den Zeilenumbrüchen im Textfeld.
    Und da kommt jetzt der Haken: egal ob mit oder ohne Double Quotes: im endgültigen Excel-File stolpert das Ding wieder bei den Zeilenumbrüchen und verreisst mir die Excel.

    Ich habe wie du vorgeschlagen hast, etwas umgebaut. Sieht nun so aus:

    VB.NET-Quellcode

    1. With ws.QueryTables.Add(Connection:="TEXT;test.csv", Destination:=ws.Range("A1"))
    2. .TextFileParseType = XlTextParsingType.xlDelimited
    3. .TextFileTextQualifier = XlTextQualifier.xlTextQualifierDoubleQuote
    4. .TextFileCommaDelimiter = False
    5. .TextFileConsecutiveDelimiter = False
    6. .TextFileTabDelimiter = False
    7. .TextFileSemicolonDelimiter = True
    8. .TextFileCommaDelimiter = False
    9. .TextFileSpaceDelimiter = False
    10. .Refresh()
    11. End With


    So habe ich ja Semikolon als Trennzeichen definierte (ja, ginge auch über "OtherDelimiter") und die anderen Möglichkeiten ausgeschlossen.
    Eventuell müsste ich noch das entsprechende Textfeld als solches kennzeichnen?
    Soweit ich weiß ginge das innerhalb des With-End With mit:

    VB.NET-Quellcode

    1. ​ .TextFileColumnDataTypes = Array(2, 2, 2)


    Nur dann meckert er mir aber, dass Array ein Typ und kein Ausdruck ist.
    Hä? :D

    Und bezüglich DGV... ja, ich weiss... total schlecht. Ich habe mich vor Jahren mal in das Thema Tabellen und Bindingusources eingearbeitet. Das doofe ist, das hat bei mir in Zusammenarbeit mit meinen MySQL-Datenbanken (von daher kommen die Daten) irgendwie nie so geklappt wie ich wollte. Deshalb arbeite ich (man möge mich dafür bitte nicht auf dem scheiterhaufen verbrennen) vor allem direkt im DGV. Total bescheuert und eigentlich doof, aber für die Mini-Projekte die ich ab und zu bastel reicht es... meistens...

    LG,
    KlyX
    Chris' Weblog - Mein Blog rund um Vieles :D

    KlyX schrieb:


    VB.NET-Quellcode

    1. ​ .TextFileColumnDataTypes = Array(2, 2, 2)


    Nur dann meckert er mir aber, dass Array ein Typ und kein Ausdruck ist.
    Hä? :D


    Okay, ich habe nur eine Antwort für Dich, sorry.

    VB.NET-Quellcode

    1. Dim locColumnTypes As New System.Collections.Generic.List(Of Integer)

    mach Dir für jede Spalte über eine Schleife einen

    VB.NET-Quellcode

    1. locColumnTypes.Add( x )


    und dann

    VB.NET-Quellcode

    1. .TextFileColumnDataTypes = locColumnTypes.ToArray()
    @Dkskm: danke, das ist ja eigentlich einfach... ;)
    Aber es scheint mein Problem nicht zu lösen.
    Ich bilder mir also mein Array mit 11 Stellen, schreibe darin immer eine "2" rein (dein x wird mit einer 2 ersetzt) , um dann unten in der Definition des DataTypes entsprechend zu setzen.
    Leider führt das zum Gleichen Ergebnis: die Excel-Datei wird "verrissen". Übrigens in jeder Variante - sowohl mit den doppelten " als auch ohne - ist der Output der Gleiche.
    Das mit der Zuweisung des DataTypes scheint aber im Grundsatz korrekt zu funktionieren, denn wenn ich den Typ auf "9" setze - also sprich "IgnoreColumn", dann wird die entsprechende Spalte nicht ausgegeben im Excel.

    Bin langsam echt am Ende mit dem Latein ;)
    Chris' Weblog - Mein Blog rund um Vieles :D
    @petaod: nein und zwar, weil ich das File nun nicht mehr öffne wie bisher, sondern wie von Dkskm vorgeschlagen über QueryTables.Add.
    Soweit ich weiss, kann ich dort gar kein Local:=True setzen.
    Oder liege ich da falsch? Wenn ja, dann bin ich froh um Info, wo das hingehört.
    Chris' Weblog - Mein Blog rund um Vieles :D
    Nein bei QueryTable gibt's keinen Local.
    Aber bevor ich die Spalten einzeln klassifizieren müsste, würde ich den Versuch schon wagen, ob das auch als Einzeiler per Open automatisch geht, wenn ich den Parameter spezifiziere.

    Wenn du nicht geschrieben hättest, dass du an dem QueryTable-Ansatz verzweifelst, hätte ich mich gar nicht mehr eingemischt.

    Falls du bei der QueryTable bleibst, vergiss nicht, diese nach dem Refresh zu löschen, sonst hast du da einen ewigen Link drin.

    Im Übrigen kannst du auch noch den Ansatz über TextToColumns versuchen.
    Das ist ein ähnlich umständliches Verfahren.
    Oder gleich mit Workbooks.OpenText arbeiten.

    Es gibt zahllose Varianten, CSV in Excel zu bringen.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --
    kein Thema, bin ja froh über alle Ideen ;)
    Ich habe allerdings - nach einem Gespräch mit meinem Arbeitskollegen - die Vermutung dass das einfach an den Zeilenumbrüchen liegt.
    Sprich: ich müsste wohl schauen, dass ich die Zeilenumbrüche aus dem einen Feld rausbekomme.
    Ich versuch da mal was :D
    Chris' Weblog - Mein Blog rund um Vieles :D
    Warum quälst Du dich überhaupt mit dem Umweg CSV ab? Ein Array ist doch auch schnell übergeben. Siehe mein Post #4.

    Ansonsten, um den Link der QueryTables zu löschen, musst Du der QueryTables.Name einen Namen festlegen und kannst dann nach dem Import mit

    VB.NET-Quellcode

    1. Sheet.Activate() 'Tabellenblatt als Aktiv markieren
    2. Sheet.QueryTables["TabellenblattName"].Delete() 'Abfrage (externe Datenverbindung) entfernen

    den Link entfernen.

    Dieser Beitrag wurde bereits 4 mal editiert, zuletzt von „Dksksm“ ()

    Du hast recht, das mit dem Array hatte ich wieder vergessen ;).
    Hab das jetzt erstmal soweit hinbekommen, dass es klappt :) Juhu :)

    Aber ein Problem habe ich noch: wie kann ich den Excel-Range auf die Größe meines Arrays festlegen?
    Angenommen mein Array ist 8,25 groß (ermittle ich aus RowCount und ColumnCount des DGV), wie kann ich nun den Excel-Range definieren?
    Ich kenne hier nur die Methode:

    VB.NET-Quellcode

    1. xlWorkSheet.Range("A1:Z50").Value = dataarray


    Sprich: ich lege den Range mit eindeutigen Zellennamen fest.
    Kann ich nicht irgendwie den Range mit Zahlen festlegen? Also z.B. (Range(0,0;5,50))?
    Klar kann ich einfach den maximal möglichen Range definieren, aber dann habe ich lauter N/A drin (korrekterweise ;))
    Und da ich im Voraus nie weiß wie viele Zeilen an Daten ich habe, kann ich das nicht eindeutig festlegen... hmpf :D
    Chris' Weblog - Mein Blog rund um Vieles :D