Excel erstellen und als Tabelle formatieren

Sub7evenHH · 31. August 2019, 20:35

Hi,
ich exportiere Daten aus einer DGV nach Excel. Das funktioniert auch wunderbar. Jetzt möchte ich die Excel aber noch als Tabelle formatieren. Hier scheitert leider jeglicher Versuch.

Hier mein Code.

VB.NET-Quellcode

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Try
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)
oSheet.Cells(1, 1) = "NAME"
oSheet.Cells(1, 2) = NUMMER
For i = 0 To DataGridView1.RowCount - 1
oSheet.Cells(i + 2, 1).NumberFormat = "@"
Try
oSheet.Cells(i + 2, 1) = DataGridView1(0, i).Value.ToString()
oSheet.Cells(i + 2, 2) = DataGridView1(1, i).Value.ToString()
Catch ex As Exception
End Try
Next
oSheet.Columns("A:Z").AutoFit
Dim password As String = "Test"
oBook.Password = password
oBook.SaveAs(ExcelExportPfad, password:=password)
oSheet = Nothing
oBook.Close()
oBook = Nothing
oExcel.Quit()
oExcel = Nothing

Wenn ich bei Excel den Macrorecorder laufen lasse, erhalte ich folgendes:

VB.NET-Quellcode

Range("A1:C1").Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$C$1"), , xlYes).Name = _
"Tabelle1"
Range("Tabelle1[#All]").Select
ActiveSheet.ListObjects("Tabelle1").TableStyle = "TableStyleMedium15"

Ich weiß nun aber nicht wie ich das umsetzen kann

Vielleicht könnt ihr mir ja weiterhelfen

*Topic verschoben*

MichaHo · 2. September 2019, 07:20

Hi,
Arbeite doch mit der Microsoft.Office.Interop.Excel DLL, damit hast Du mehr Möglichkeiten, z-Bsp. direkten Zugriff auf Range...
Ich ab eine Extension für den Import/Export einer DataTable, sieht in C# dann so aus:

C#-Quellcode

using Excel = Microsoft.Office.Interop.Excel;
public static void ExcelExport(this DataTable dt, string path = null)
{
Excel.Application xl = new Excel.Application();
if (xl == null)
{throw new ApplicationException("Excel ist nicht installiert!!");}
Excel.Workbook wkb = xl.Workbooks.Add();
Excel.Worksheet wks = wkb.Sheets[1];
int colCount = dt.Columns.Count;
if (dt == null || colCount == 0)
{ throw new ApplicationException("Excel Export gescheitert, da die Tabelle leer ist"); }
wks.Name = "Übersicht";
wks.Cells[1, 2] = "Zusammenfassung der Umsätze";
wks.Cells[3, 1] = "ID";
wks.Cells[3, 2] = "Verkäufernummer";
wks.Cells[3, 3] = "Name";
wks.Cells[3, 4] = "Umsatz";
int rowCount = dt.Rows.Count;
object[,] Cells = new object[rowCount, colCount];
for (int j = 0; j < rowCount; j++)
for (int i = 0; i < colCount; i++)
Cells[j, i] = dt.Rows[j][i];
wks.get_Range((Excel.Range)(wks.Cells[4, 1]), (Excel.Range)(wks.Cells[rowCount + 1, colCount])).Value = Cells;
wks.get_Range((Excel.Range)(wks.Cells[4, 4]), (Excel.Range)(wks.Cells[rowCount + 1, colCount])).NumberFormat = @"#.0,0 €";
xl.Visible = true;
}

petaod · 2. September 2019, 10:55

Sub7evenHH schrieb:

Range("A1:C1").Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$C$1"), , xlYes).Name = _
"Tabelle1"
Range("Tabelle1[#All]").Select
ActiveSheet.ListObjects("Tabelle1").TableStyle = "TableStyleMedium15"

VB.NET-Quellcode

With oSheet.ListObjects.Add(4, oSheet.Range("$A$1:$C$1"), , 1)
.Name = "Tabelle1"
.TableStyle = "TableStyleMedium15
End With

Sub7evenHH · 2. September 2019, 17:48

@petaod ich habe deine Lösung versucht umzusetzen. Leider erscheint mir hier ein Fehler:

XML-Quellcode

System.ArgumentException: Falscher Parameter. (Ausnahme von HRESULT: 0x80070057 (E_INVALIDARG))
bei Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack)
bei Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)
bei test.ExcelExport() in C:\Testprojekt\test\test.vb:Zeile 567.

Hast du da noch einen Rat?

@MichaHo Danke für den Tipp. Das werde ich mir natürlich auch mal anschauen. Ich würde aber doch schon gerne bei dem ersten Entwurf bleiben. Trotzdem herzlichen Dank!

petaod · 2. September 2019, 18:41

Bei welchem Code taucht der Fehler auf?
Hast du's mal mit Debuggen versucht und dir dort die relevanten Objekte angeschaut?

Sub7evenHH · 2. September 2019, 23:05

Wenn ich den Teil von dir mit bei meinem Code einfüge.
Der Code:

VB.NET-Quellcode

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Try
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)
With oSheet.ListObjects.Add(4, oSheet.Range("$A$1:$C$1"), , 1)
.Name = "Tabelle1"
.TableStyle = "TableStyleMedium15"
End With
oSheet.Cells(1, 1) = "NAME"
oSheet.Cells(1, 2) = NUMMER
For i = 0 To DataGridView1.RowCount - 1
oSheet.Cells(i + 2, 1).NumberFormat = "@"
Try
oSheet.Cells(i + 2, 1) = DataGridView1(0, i).Value.ToString()
oSheet.Cells(i + 2, 2) = DataGridView1(1, i).Value.ToString()
Catch ex As Exception
End Try
Next
oSheet.Columns("A:Z").AutoFit
Dim password As String = "Test"
oBook.Password = password
oBook.SaveAs(ExcelExportPfad, password:=password)
oSheet = Nothing
oBook.Close()
oBook = Nothing
oExcel.Quit()
oExcel = Nothing

Beim Debuggen kann ich leider nichts finden was mir weiterhilft bzw. was ich verstehe. Die einzige Meldung die nach Aufruf kommt ist:

Ausnahme ausgelöst: "System.ArgumentException" in Microsoft.VisualBasic.dll

Dksksm · 2. September 2019, 23:11

Über NuGet Microsoft.Office.Interop.Excel einbinden.
Im Form dann

VB.NET-Quellcode

Imports Excel = Microsoft.Office.Interop.Excel

Dann sollte folgender Code funktionieren.

VB.NET-Quellcode

Dim oExcel As Excel.Application = New Excel.Application()
If oExcel Is Nothing Then
Throw New ApplicationException("Excel ist nicht installiert!!")
End If
Dim oBook As Excel.Workbook
oBook = oExcel.Workbooks.Add
Dim oSheet As Excel.Worksheet = CType(oBook.Sheets(1), Excel.Worksheet)
oSheet.Cells(1, 1) = "NAME"
oSheet.Cells(1, 2) = "NUMMER"
For i = 0 To DataGridView1.RowCount - 1
If DataGridView1(0, i).Value IsNot Nothing Then
Dim rangeToFormat = oSheet.Range(oSheet.Cells(i + 2, 1), oSheet.Cells(i + 2, 1))
rangeToFormat.NumberFormat = "@"
oSheet.Cells(i + 2, 1) = DataGridView1(0, i).Value.ToString()
oSheet.Cells(i + 2, 2) = DataGridView1(1, i).Value.ToString()
End If
Next
Dim lastRow As Integer = oSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row
Dim lastColumn As Integer = oSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Column
Dim xlRangeLast As Excel.Range = oSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
Dim xlRangeToFormat = oSheet.Range(oSheet.Cells(1, 1), oSheet.Cells(lastRow, lastColumn))
oSheet.ListObjects.Add(, xlRangeToFormat, , Excel.XlYesNoGuess.xlYes).Name = "Tabelle1"
oSheet.ListObjects("Tabelle1").TableStyle = "TableStyleMedium15"
oSheet.Range("A1:B1").EntireColumn.AutoFit()
Dim password As String = "Test"
oBook.Password = password
oBook.SaveAs("D:\Test.xlsx", Password:=password)
oSheet = Nothing
oBook.Close()
oBook = Nothing
oExcel.Quit()
oExcel = Nothing

petaod · 3. September 2019, 07:58

Sub7evenHH schrieb:

Die einzige Meldung die nach Aufruf kommt ist:

Ausnahme ausgelöst: "System.ArgumentException" in Microsoft.VisualBasic.dll

Mach die Try..Catch-Aufrufe raus und teile uns mit, in welcher Zeile die Fehlermeldung ausgelöst wird.

Sub7evenHH · 3. September 2019, 08:16

@Dksksm
Da erhalte ich folgenden Fehler:

XML-Quellcode

************** Ausnahmetext **************
System.InvalidCastException: Das COM-Objekt des Typs "Microsoft.Office.Interop.Excel.ApplicationClass" kann nicht in den Schnittstellentyp "Microsoft.Office.Interop.Excel._Application" umgewandelt werden. Dieser Vorgang konnte nicht durchgeführt werden, da der QueryInterface-Aufruf an die COM-Komponente für die Schnittstelle mit der IID "{000208D5-0000-0000-C000-000000000046}" aufgrund des folgenden Fehlers nicht durchgeführt werden konnte: Fehler beim Laden der Typbibliothek/DLL. (Ausnahme von HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)).
bei System.StubHelpers.StubHelpers.GetCOMIPFromRCW(Object objSrc, IntPtr pCPCMD, IntPtr& ppTarget, Boolean& pfNeedsRelease)
bei Microsoft.Office.Interop.Excel.ApplicationClass.get_Workbooks()

@petaod Mit deinem Code in der Zeile:

VB.NET-Quellcode

With oSheet.ListObjects.Add(4, oSheet.Range("$A$1:$C$1"), , 1)

petaod · 3. September 2019, 08:38

Ersetz mal die 4 durch eine 1.

Ansonsten: Versuche einfach selbst zu verstehen, was der Aufruf macht:
docs.microsoft.com/en-us/offic…el.xllistobjectsourcetype
docs.microsoft.com/en-us/offic…api/excel.listobjects.add
und probier die Parameter aus, bis er macht, was du willst.

Sub7evenHH · 3. September 2019, 08:52

@petaod

VIELEN VIELEN HERZLICHEN DANK! Es klappt nun wenn ich die 4 durch eine 1 ersetze

Super für die Hilfe die einem hier gegeben wird

Das Problem ist also gelöst.

Dksksm · 3. September 2019, 10:05

Das sieht mir nicht nach "Option Strict On" aus....

Wenn man bei mir die Zeile

VB.NET-Quellcode

oSheet.ListObjects.Add(, xlRangeToFormat, , Excel.XlYesNoGuess.xlYes).Name = "Tabelle1"

mit

VB.NET-Quellcode

oSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, xlRangeToFormat, , Excel.XlYesNoGuess.xlYes).Name = "Tabelle1"

ersetzt, müsste es laufen, warum sollte es sonst bei mir funktionieren?

Wenn der Thread erledigt ist, dann setzt ihn doch bitte auch auf ereldigt.

petaod · 3. September 2019, 16:36

Dksksm schrieb:

Das sieht mir nicht nach "Option Strict On" aus....

Nein, ist weit weg von Strict On
Es beginnt damit, dass die Excel-Instanz per Late Binding mit CreateObject erzeugt wird, weshalb auch die ganzen Interop-Konstanten nicht verfügbar sind und numerisch angegeben werden müssen.

Excel erstellen und als Tabelle formatieren

Excel erstellen und als Tabelle formatieren

VB.NET-Quellcode

VB.NET-Quellcode

C#-Quellcode

Sub7evenHH schrieb:

VB.NET-Quellcode

XML-Quellcode

VB.NET-Quellcode

VB.NET-Quellcode

VB.NET-Quellcode

Sub7evenHH schrieb:

XML-Quellcode

VB.NET-Quellcode

VB.NET-Quellcode

VB.NET-Quellcode

Dksksm schrieb:

Ähnliche Themen

4 Benutzer haben hier geschrieben