CODEWITHSUNDEEP
c#excelopenxmlclosedxml
Toni
Toni

Reputation: 57

How to create an editable excel

I am creating an Excel file:

SpreadsheetDocument document = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook)
WorkbookPart workBookPart = document.AddWorkbookPart();
            workBookPart.Workbook = new Workbook();

            WorksheetPart workSheetPart = workBookPart.AddNewPart<WorksheetPart>();
            workSheetPart.Worksheet = new Worksheet();


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

            Sheet sheet = new Sheet()
            {
                Id = workBookPart.GetIdOfPart(workSheetPart),
                SheetId = 1,
                Name = "Test Sheet"
            };
            sheets.Append(sheet);

            workBookPart.Workbook.Save();

When I open the file it's only ReadOnly, how I can make it editable to the user?

ClosedXML is the priority, alternative way of the solution can be OpenXML

Upvotes: 0

Views: 429

Answers (1)

petelids
petelids

Reputation: 12815

The code you've provided there creates an invalid file rather that one that would be read-only. There are 2 minor issues. Firstly, there must be exactly one SheetData element under each Worksheet and secondly you need to call the Close method on the document (either directly or indirectly via a using statement).

The following code will create you a file that is editable with an explicit call to Close

SpreadsheetDocument document = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook);
WorkbookPart workBookPart = document.AddWorkbookPart();
workBookPart.Workbook = new Workbook();

WorksheetPart workSheetPart = workBookPart.AddNewPart<WorksheetPart>();
workSheetPart.Worksheet = new Worksheet();

//a worksheet *must* have exactly one child SheetData
workSheetPart.Worksheet.AppendChild(new SheetData());

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

Sheet sheet = new Sheet()
{
    Id = workBookPart.GetIdOfPart(workSheetPart),
    SheetId = 1,
    Name = "Test Sheet"
};
sheets.Append(sheet);

workBookPart.Workbook.Save();

//the document must be closed (and ideally disposed)
document.Close();
document.Dispose();

Here's the same but with the using statement instead:

using (SpreadsheetDocument document = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
{
    WorkbookPart workBookPart = document.AddWorkbookPart();
    workBookPart.Workbook = new Workbook();

    WorksheetPart workSheetPart = workBookPart.AddNewPart<WorksheetPart>();
    workSheetPart.Worksheet = new Worksheet();

    //a worksheet *must* have exactly one child SheetData
    workSheetPart.Worksheet.AppendChild(new SheetData());

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

    Sheet sheet = new Sheet()
    {
        Id = workBookPart.GetIdOfPart(workSheetPart),
        SheetId = 1,
        Name = "Test Sheet"
    };
    sheets.Append(sheet);

    workBookPart.Workbook.Save();
}

Edit

Doing this in ClosedXml is much simpler. The following will produce an equivalent workbook:

using ClosedXML.Excel;
....

var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Test Sheet");
workbook.SaveAs(filename);

Upvotes: 1

Related Questions