This project has moved. For the latest updates, please go here.

Pivot Table creation results in Corrupt Spreadsheet

Oct 28, 2015 at 3:14 PM
Edited Oct 28, 2015 at 3:15 PM
Hello,

I am using ClosedXML 0.76 in VB with VS2013 and I am trying to generate a pivot table from data that was added to a sheet via a Datatable. The Raw data itself gets inserted fine but if I generate the pivot table and then try to open the file The spreadsheet gets corrupted and in order to fix it Excel has to remove the Pivot Table entirely.

Here is the code I am using
Module Module1
    Sub Main()
        Dim CMD As New SqlCommand("SalesSum")
        Dim StartDate As Date = FormatDateTime("01-01-" & Now.Year, DateFormat.ShortDate)
        Dim EndDate As Date = FormatDateTime(Now(), DateFormat.ShortDate)

        CMD.Parameters.Add("@StartDate", SqlDbType.Date).Value = StartDate
        CMD.Parameters.Add("@EndDate", SqlDbType.Date).Value = EndDate
        Console.WriteLine("Getting Sales Data From " & StartDate & " To " & EndDate)
        Dim DT As DataTable = ExecuteCMD(CMD)

        Dim fileTest As String = "C:\Temp\TestPivot.xlsx"
        If File.Exists(fileTest) Then
            File.Delete(fileTest) ' oh, file is still open
        End If

        Dim oXLwb As New XLWorkbook()

        Console.WriteLine("Writing Data to Spreadsheet")


        Dim xlPtSheet = oXLwb.Worksheets.Add("AllStoreSales")
        Dim xlSheet = oXLwb.Worksheets.Add(DT, "RawData")

        Dim xlRange = xlSheet.RangeUsed()
        Dim xlHeader = xlSheet.Range(1, 1, 1, DT.Columns.Count)
        Dim xlDatarange = xlSheet.Range(xlHeader.FirstCell(), xlRange.LastCell())


        Console.WriteLine("Generating AllStoreSales Pivot Table")

        Dim pt = xlPtSheet.PivotTables.AddNew("AllStoreSales", xlPtSheet.Cell(3, 1), xlDatarange)

        pt.RowLabels.Add("SalesMonth")
        pt.RowLabels.Add("Department")
        pt.RowLabels.Add("Stores")

        pt.Values.Add("TYNetSales")
        pt.Values.Add("LYNetSales")

        Console.WriteLine("Saving Spreadsheet to " & fileTest)
        oXLwb.SaveAs(fileTest)
End Sub
Trying to see if another pair of eyes can spot what I am doing wrong or if this is a bug with the ClosedXML version I am using
Oct 29, 2015 at 5:50 AM
[url=http://www.drjjwendel.com/liposuction]Dr. J.J Wendel[/url], Experts in Liposuction