0

I'm trying to create an excel spreadsheet with the ClosedXML assembly for some master/detail stuff. I can create the flat master output fine. However, when I went back and started adding on the detail portions, the code runs fine but Excel reports the file as being corrupted.

Note, the output I'm trying to create is a simple pattern of

Master Record
[empty cell]Details
[empty cell]Details
Master Record
    etc...

Here's my code:

// get "master level" order table
DataTable masterTable = ParseMaster(view, columnPlacement, hiddenItems);

masterTable.Columns.OfType<DataColumn>().ToList().ForEach(column => column.Caption = column.ColumnName);

//create workbook and add "master level" table, capturing the new worksheet in an instance variable
XLWorkbook workbook = new XLWorkbook();
IXLWorksheet sheet = workbook.Worksheets.Add(masterTable, name);

//new code
//lookup the order id column for use later on
IXLAddress orderIdAddress =
    sheet.RowsUsed().First().CellsUsed().First(cell => cell.Value.ToString() == "OrderID").Address;

//loop through the inserted records in a reverse fashion
//thinking was that this would be a bit more efficient and perhaps be a bit gentler on ClosedXML's event propogation
for(int rowIdx = sheet.Rows().Count(); rowIdx > 1; rowIdx--)
{
    //find the row for the current iteration
    IXLRangeRow row = sheet.Range(rowIdx, 1, rowIdx, sheet.ColumnsUsed().Count()).Rows().First();

    //grab the order id using the location per the above lookup
    string orderId = row.Cells().First(cell => cell.Address.ColumnNumber == orderIdAddress.ColumnNumber).Value.ToString();

    //get the relevant detail from an object out of scope from this example
    Detail det = view.Orders.First(order => order.OrderID == orderId);

    //perform a similar parsing of its contents returning a datatable extremely similar to the "master level" table above
    DataTable detailTable = ParseDetail(view, det);

    //add new rows to the sheet to prevent overruns and bleed into other rows
    IXLRangeRows newRows = row.InsertRowsBelow(detailTable.Rows.Count + 1);

    //add the detail table table to the first cell of the newly added rows
    newRows.First().Cell(1).InsertTable(detailTable);
}
//end new code

using (MemoryStream stream = new MemoryStream())
{
    using (ZipArchive archive = new ZipArchive(stream, ZipArchiveMode.Create, true))
    {
        ZipArchiveEntry entry = archive.CreateEntry(name + ".xlsx", CompressionLevel.Fastest);
        using (Stream entryStream = entry.Open())
        {
            workbook.SaveAs(entryStream);
        }
    }

    return stream.ToArray();
}

I'm sure something I'm doing isn't jiving well with the OpenXML format but I'm a bit of a noob at this and the ClosedXML documentation doesn't have anything that's jumping out at me.

Bigsby
  • 952
  • 6
  • 20
  • 2
    You don't need to compress xlsx files, they are already compressed. – Raidri Apr 11 '16 at 14:13
  • Since Excel is a *flat* format, master or detail are just a matter of formatting. You aren't trying to insert one table inside another? It's *very hard* to read the code as it is, without any comments or meaningful variable names. Can you create an Excel sheet using *Excel* to produce what you want? If not, you can't produce the same thing with a library – Panagiotis Kanavos Apr 11 '16 at 14:15
  • @Raidri well, I'm getting ~15% improvement with compression. It might not be huge, but it's what ye olde boss wants. – Bigsby Apr 11 '16 at 14:37
  • @PanagiotisKanavos I've added comments. Yes, Excel the application can do this fine. There's no reason you can't do my example output in Excel. There's nothing weird or extraordinary about it. – Bigsby Apr 11 '16 at 14:45

0 Answers0