Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Open XML SDK - Save a template file (.xltx to .xlsx)

I have the following code to open Excel template file and save it as .xlsx file and I get the error below when I try to open the new file. Please help to resolve this.

Excel cannot open the file ‘sa123.xlsx’ because the file format or the extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

        string templateName = "C:\\temp\\sa123.xltx";
        byte[] docAsArray = File.ReadAllBytes(templateName);
        using (MemoryStream stream = new MemoryStream())
        {
            stream.Write(docAsArray, 0, docAsArray.Length);    // THIS performs doc copy
            File.WriteAllBytes("C:\\temp\\sa123.xlsx", stream.ToArray());    
        }
like image 534
SA. Avatar asked Sep 30 '10 14:09

SA.


2 Answers

In order to do this you will need to use the Open XML SDK 2.0. Below is a snippet of code that worked for me when I tried it:

byte[] byteArray = File.ReadAllBytes("C:\\temp\\sa123.xltx");
using (MemoryStream stream = new MemoryStream())
{
    stream.Write(byteArray, 0, (int)byteArray.Length);
    using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(stream, true))
    {
       // Change from template type to workbook type
       spreadsheetDoc.ChangeDocumentType(SpreadsheetDocumentType.Workbook);
    }
    File.WriteAllBytes("C:\\temp\\sa123.xlsx", stream.ToArray()); 
}

What this code does is it takes your template file and opens it into a SpreadsheetDocument object. The type of this object is Template, but since you want it as a Workbook you call the ChangeDocumentType method to change it from a Template to a Workbook. This will work since the underlying XML is the same between a .xltx and a .xlsx file and it was just the type that was causing you an issue.

like image 197
amurra Avatar answered Sep 29 '22 00:09

amurra


Excel sees the .xlsx extension and tries to open it as a worksheet file. But it isn't. It's a template file. When you have a template open in Excel and save it as a .xlsx file, it converts it to the worksheet format. What you are doing is the same as changing the extension in the filename. Try it in Windows Explorer and you will get the same result.

I believe you should be able to accomplish what you want by using the Excel Object Model. I have not used this though.

like image 27
YWE Avatar answered Sep 28 '22 23:09

YWE