ClosedXML.Report
ClosedXML.Report copied to clipboard
ClosedXML
Pivot table: Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.
Hi,
i was trying to create an example with pivot tables and i've used your template tPivot1.xlsx When trying to open the generated excel file with Office 2016 and 2019, i'm getting the following error:
"Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded. Removed Part: /xl/pivotTables/pivotTable.xml part with XML error. (PivotTable view) HRESULT 0x8000ffff Line 1, column 0."
I thought i was doing something wrong until i opened the same file with LibreOffice and i saw that the pivot table was correctly generated.
Am i missing something or is there really an issue with Office?
Thank you
Hi. Have you changed something in the template or in the report generation code?
Hi, i've changed the name of the named range to Data and that was the only change i've made in the template. Here's the class and export methods:
public class Order
{
public string Company { get; set; }
public int PaymentMethod { get; set; }
public string OrderNo { get; set; }
public DateTime ShipDate { get; set; }
public int ItemsTotal { get; set; }
public int TaxRate { get; set; }
public int AmountPaid { get; set; }
}
public virtual byte[] Export<TEntity>(IList<TEntity> dataToExport, Guid templateId) where TEntity : class
{
byte[] template = GetTemplateFile(templateId);
if (template == null) return null;
using (MemoryStream stream = new MemoryStream())
{
stream.Write(template, 0, template.Length);
stream.Seek(0, SeekOrigin.Begin);
return CreateExcelFromTemplate.CreateExcel(
stream,
new
{
Data = dataToExport,
DynamicHeaders = DynamicColumnsInExcel,
Username = _userService.Fullname,
CreationDate = DateTime.Now.ToString("dd/MM/yyyy"),
CreationDateTime = DateTime.Now.ToString("dd/MM/yyyy HH:mm"),
Reference = Reference,
Title = Title
});
}
}
public static class CreateExcelFromTemplate
{
/// <summary>
/// Generate excel file from template by file stream
/// </summary>
/// <param name="fileStream"></param>
/// <param name="data"></param>
/// <returns>File byte array</returns>
public static byte[] CreateExcel(Stream fileStream, object data)
{
using (XLTemplate template = new XLTemplate(fileStream))
{
return Generate(template, data);
}
}
private static byte[] Generate(XLTemplate template, object data)
{
template.AddVariable(data);
template.Generate();
using (MemoryStream outputStream = new MemoryStream())
{
template.SaveAs(outputStream);
return outputStream.ToArray();
}
}
} Thank you for the quick response.
I didn't install ClosedXML, just ClosedXML.Report. Do i have to install both of them?
ClosedXML should be installed automatically as a dependency on ClosedXML.Report. Most likely this is the latest compatible version, it should be 0.95.1. Tomorrow I'll check how Pivot works with ClosedXML 0.95.1.
Hi. Have you solved this issue? You're right, ClosedXML generates a file that Excel cannot read. The fix should be in ClosedXML.
Hi. I didn't fix the issue yet. Do you know what's causing the problem? I could try to help if you give some hints on where the issue might be. Thank you.
I think, I found the problem... I described it here: https://github.com/ClosedXML/ClosedXML/discussions/2135
