openxml spreadsheat save-as

Question

I have an Excel 2007 spreadsheet that I edit with the OpenXML SDK 2. I remove some rows etc. I would like to know how to save that Spreadsheetdocument to another filename.

Pointsman

Answer 1

To my knowledge there is no built in way to change the filename, but since one way to edit the file is to use streams you can easily give the name of the file you want when writing out the contents of a stream:

byte[] byteArray = File.ReadAllBytes("C:\\temp\\oldName.xltx");
using (MemoryStream stream = new MemoryStream())
{
    stream.Write(byteArray, 0, (int)byteArray.Length);
    using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(stream, true))
    {
       // Do work here
    }
    File.WriteAllBytes("C:\\temp\\newName.xlsx", stream.ToArray()); 
}

There are other ways to change the name as well, like when returning the file to a user in an ASP.NET MVC web application, but it depends on what you are trying to do.

Boot

This code does not seem to be right. Do you need stream.Seek(0, SeekOrigin.Begin) before passing it to SpreadsheetDocument.Open call? Otherwise the open call will receive an empty stream since it is at the end of the stream. – Horoscope

Presumably there is an internal call to seek 0, as the code works for me as-is. – Rasbora

Answer 2

Have a look at this library ClosedXML which is based on OpenXML; it simplifies a lot of document operations and gives you the SaveAs method. Here is an example of what you can do.

        var workbook = new XLWorkbook();
        var worksheet = workbook.Worksheets.Add("Sample Sheet");
        worksheet.Cell("A1").Value = "Hello World!";
        workbook.SaveAs("HelloWorld.xlsx");

Hatchet

I wanted to keep using OpenXML but I gave ClosedXML a try after 2 hours of trying to figure out how to open an existing excel template, filling some information, and push it to the browser. I have made it just in 3 minutes with ClosedXML.It was extremely easy I have to say. – Antiperiodic

Answer 3

I strongly recommend ClosedXML - just used it for the first time and initially I've done an "Import from excel" functionality in few hours using standard Open XML 2.0 SDK - I've rewrite it in less then 15 minutes and also I've done the export to excel in 10 minutes using this tool.

Institutive

Answer 4

You could save that Spreadsheetdocument to another filename then open it to edit.

string sourceFile = "C:\Users\***.xlsx";
string destFile = "C:\Users\*****.xlsx";
System.IO.File.Copy(sourceFile, destFile, true);
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(destFile, true))
{
  //remove some rows etc
  //save workbookpart
}

References:How to: Copy, Delete, and Move Files and Folders (C# Programming Guide)

Berwickupontweed

Answer 5

Here's my solution for a save as, tested with complicated workbook that has macros and conditional formatting, etc. (this method is in a wrapper that has a SpreadsheetDocument property "Document"):

    /// <summary>
    /// Saves as.
    /// </summary>
    /// <param name="fileName">Name of the file.</param>
    public void SaveAs(String filename)
    {
        String dir = filename.Replace(System.IO.Path.GetFileName(filename), String.Empty);
        if (!System.IO.Directory.Exists(dir))
            System.IO.Directory.CreateDirectory(dir);

        SpreadsheetDocument newDoc = SpreadsheetDocument.Create(filename, Document.DocumentType);

        //Make sure it's clear
        newDoc.DeleteParts<OpenXmlPart>(newDoc.GetPartsOfType<OpenXmlPart>());

        //Copy all parts into the new book
        foreach (OpenXmlPart part in Document.GetPartsOfType<OpenXmlPart>())
        {
            OpenXmlPart newPart = newDoc.AddPart<OpenXmlPart>(part);
        }

        //Perform 'save as'
        newDoc.WorkbookPart.Workbook.Save();
        newDoc.Close();
        this.Document.Close();

        //Open new doc
        this.Document = SpreadsheetDocument.Open(filename, true);
    }

Chrisom

Answer 6

I don't know what version this functionality was introduced, but I use OpenXmlPackage.SaveAs, where
SpreadsheetDocument, is a subclass of OpenXmlPackage.

This function returns the new document, so you can switch to the copied one and apply your changes to it:

void FromTemplate()
{
  using var template = SpreadsheetDocument.Open("Template.xlsx", isEditable: true);
  using var output = (SpreadsheetDocument)template.SaveAs(path);

  //no longer need to keep handle open
  template.Dispose();

  //apply your changes to package
}

Rotarian