Xltx files

Jul 23, 2012 at 12:37 PM

not really a discussion, but something i did want to share.

for a project i was working on i had to use xltx files as input and export xlsx files. the EPPlus library worked great for everthing else i needed to do, but i found that feature missing, so i figured out the difference between xltx and xlsx myself.

and in case other people might be having the same issues or in case you want to add this option to the library, here is the code i wrote to make the conversion as a thanks for the work you did.

 

this might need adaptation to support multiple workbooks though.

//using ExcelPagage MyPackage
Package mypack = MyPackage.Package;
Uri workbook = new Uri("/xl/workbook.xml", UriKind.Relative);
PackageRelationshipCollection relcoll = mypack.GetPart(workbook).GetRelationships();
mypack.DeletePart(workbook);
mypack.CreatePart(workbook, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml", CompressionOption.SuperFast);
foreach (PackageRelationship Relation in relcoll)
{
    mypack.GetPart(workbook).CreateRelationship(Relation.TargetUri, Relation.TargetMode, Relation.RelationshipType, Relation.Id);
}
Feb 1, 2013 at 6:10 AM
can you provide any more info on how to get this to work.

I have tried to create an extension method, but it doesnt seem to have any impact to solve the problem of opening a XLTM and saving as XLSM.
namespace EPPlusExtension
{
    using System;
    using System.IO.Packaging;  //needs reference to WindowsBase
    using OfficeOpenXml;
    public static class EP_RG
    {
        public static void ConvertXLT2XLM(this ExcelPackage myXLP)
        {//
            //using ExcelPagage MyPackage
            Package mypack = myXLP.Package;
            Uri workbook = new Uri("/xl/workbook.xml", UriKind.Relative);
            PackageRelationshipCollection relcoll = mypack.GetPart(workbook).GetRelationships();
            mypack.DeletePart(workbook);
            mypack.CreatePart(workbook, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml", CompressionOption.SuperFast);
            foreach (PackageRelationship Relation in relcoll)
            {
                mypack.GetPart(workbook).CreateRelationship(Relation.TargetUri, Relation.TargetMode, Relation.RelationshipType, Relation.Id);
            }
        }
    }
}

                    using (ExcelPackage xl = new ExcelPackage(InvoiceSave, InvoiceTempate))
                    {
                        ExcelWorksheet ws = xl.Workbook.Worksheets["Project Information"];
                        //Project_No is a workbook scope name
                        if (xl.Workbook.Names.ContainsKey("Project_No"))
                        {
                            ExcelNamedRange prid = xl.Workbook.Names["Project_No"];
                            prid.Value = projectName + projectSuffix;

                            xl.ConvertXLT2XLM();
                            xl.Save();
                            return;
Feb 1, 2013 at 7:53 AM
Edited Feb 1, 2013 at 8:11 AM
This code will convert a .xltm file to .xlsm file:
using System;
using System.IO;
using DocumentFormat.OpenXml; // References C:\Program Files (x86)\Open XML SDK\V2.5\lib\DocumentFormat.OpenXml.dll
using DocumentFormat.OpenXml.Packaging;

namespace Change_xltm_file_to_xlsm
{
  class Program
  {
    static void Main(string[] args)
    {
      byte[] byteArray = File.ReadAllBytes(@"d:\sample1.xltm");
      using (MemoryStream stream = new MemoryStream())
      {
        stream.Write(byteArray, 0, (int)byteArray.Length);
        using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(stream, true))
        {
          // Change from template .xlsm type to workbook .xlsm, type
          spreadsheetDoc.ChangeDocumentType(SpreadsheetDocumentType.MacroEnabledWorkbook);
        }
        File.WriteAllBytes(@"d:\sample1.xlsm", stream.ToArray());
      }
      Console.Write("Program finished.  Press a key\n");
    }
  }
}
The spreadsheetDoc.ChangeDocumentType(SpreadsheetDocumentType.MacroEnabledWorkbook); line does the conversion This code does not use epplus. So you can do stuff epplus and then save and close the file and then reopen it and convert it with the code above.

You need the Microsoft OpenXML SDK 2.5 installed: http://www.microsoft.com/en-gb/download/details.aspx?id=30425
May 14, 2013 at 1:30 PM
Hi xav2075

Your sample is working unless you don't attempt to work with the worksheets using EPPlus afterwards.
I also found this sample on OpenXML forums and have used it for creating Excel spreadsheets from Excel XLTX templates.

But when I try to use the created spreadsheet using EPPlus after the change of documenttype, EPPlus cant find the sheets collection.

Studying the XML code after changing the documenttype using Open XML Producticity code, one can se that the the rootname of the workbook is changed from "workbook" to "workbook2".
This seems to be the cause of EPPlus not finding any sheets after the "conversion".

Do you (or anybody) have some mor input regarding this?

Thanks in advance

Peter karlström
Midrange AB
Sweden