Clash Royale CLAN TAG

Simple excel creation does not open file is corrupt

I am trying to create a simple excel file with multiple sheets using open xml, unfortunately the file does not open after it's being created.

After the file is generated, when I open it with Microsoft Excel it says

We found a problem, do you want to recover as much as we can?

using (SpreadsheetDocument spreedDoc = SpreadsheetDocument.Create(filePath,
DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
WorkbookPart wbPart = spreedDoc.WorkbookPart;

wbPart = spreedDoc.AddWorkbookPart();
wbPart.Workbook = new Workbook();

Sheets sheets = wbPart.Workbook.AppendChild(new Sheets());

foreach (var sheetData in excelSheetData)
{
// Add a blank WorksheetPart.
WorksheetPart worksheetPart = wbPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());

string relationshipId = wbPart.GetIdOfPart(worksheetPart);

// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements<Sheet>().Count() > 0)
{
sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}

// Give the new worksheet a name.
string sheetNameToWrite = sheetName;
if (string.IsNullOrWhiteSpace(sheetNameToWrite))
{
sheetNameToWrite = "Sheet"+sheetId;
}
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
sheets.AppendChild(sheet);
}

//wbPart.Workbook.Sheets.AppendChild(sheet);
wbPart.Workbook.Save();
}


On trying to Repair in excel gives below message

-<repairedRecords summary="Following is a list of repairs:">

<repairedRecord>Repaired Records: Worksheet properties from /xl/workbook.xml part (Workbook)</repairedRecord>

</repairedRecords>

</recoveryLog>


1 Answer
1

Have you seen this?
http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm

The necessary steps to create a functional excel file with multiple worksheets in OpenXML (that work for me) are as follows:

using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
{
spreadsheet.AddWorkbookPart();
spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));

WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
Stylesheet stylesheet = new Stylesheet();
workbookStylesPart.Stylesheet = stylesheet;
workbookStylesPart.Stylesheet.Save();

for (int worksheetNo = 1; worksheetNo < worksheetCountYouWantToCreate; worksheetNo++)
{
string workSheetID = "rId" + worksheetNo;
string worksheetName = "worksheet" + worksheetNo;

WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();

newWorksheetPart.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData());

// write data here
// ...
// ...

newWorksheetPart.Worksheet.Save();

if (worksheetNo == 1)
spreadsheet.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());

spreadsheet.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>().AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart),
SheetId = (uint)worksheetNo,
Name = worksheetName
});
}
spreadsheet.WorkbookPart.Workbook.Save();
}


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.