Convert XLSM to XLSX

Question

I'm using the EPPLUS library to read data from Excel to create another file. Unfortunately it does not support the .XLSM extension file. Is there a nice way to convert .XLSM files to .XLSX file for the purpose of reading the file with EPPLUS?

(using EPPLUS for reading would be nice because all my code is already written using it :) )

Impuissant

looks like it's not possible (yet): epplus.codeplex.com/discussions/282220 – Dogleg

To clarify, not asking if I can use EPPlus to read the file. Asking if there are any methods / api's / other things I can use to convert an xlsm file to xlsx so that I can read it using EPPlus – Impuissant

Answer 1

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\\test.xlsm");
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\\test.xlsx", stream.ToArray()); 
}

What this code does is it takes your macro enabled workbook file and opens it into a SpreadsheetDocument object. The type of this object is MacroEnabledWorkbook, but since you want it as a Workbook you call the ChangeDocumentType method to change it from a MacroEnabledWorkbook to a Workbook. This will work since the underlying XML is the same between a .xlsm and a .xlsx file.

Brecher

sorry, I know this is a old post, but I can't seem to get this to work. I was able to get the output file (xlsx), but was not able to open the file in excel. "The file is a macro-free file, but contains macro-enable content." – Loosing

how we can convert xls to xlsm using same stuff, I got an error "File with corruption". – Nonmetallic

I don't think you can since xls is the old format office used to use – Brecher

Just to detail a bit for people who were lost like I was, we need to add both those: using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml; – Costanza

Doesn't seem to work for me. I'm getting an error that says the file "...is a macro-free file, but contains macro-enabled content." No data displays after the conversion, just a gray background. – Albinaalbinism

please go through the link,(#54124871) as I received the similar error message when used the above one – Daft

Answer 2

Using the Open XML SDK, like in amurra's answer, but in addition to changing doc type, VbaDataPart and VbaProjectPart should be removed, otherwise Excel will show error a file is corrupted.

using (var inputStream = File.OpenRead("C:\\temp\\test.xlsm"))
using (var outStream = new MemoryStream()) {
    inputStream.CopyTo(outStream);
    using (var doc = SpreadsheetDocument.Open(outStream, true)) {
        doc.DeletePartsRecursivelyOfType<VbaDataPart>();
        doc.DeletePartsRecursivelyOfType<VbaProjectPart>();
        doc.ChangeDocumentType(DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
    }
    File.WriteAllBytes("C:\\temp\\test.xlsx", outStream.ToArray());
}

Isolda

Answer 3

package xlsbtoxlsx;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.regex.Pattern;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackagePart;
import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.openxml4j.opc.PackageRelationshipCollection;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbookType;

public class XlsbToXlsxConvertor {

    public static void main(String[] args) throws Exception {
        
        String inputpath="C:\\Excel Data Files\\XLSB\\CSD_TDR_20200823";
        String outputpath="C:\\Excel Data Files\\XLSB\\output";
        
       new XlsbToXlsxConvertor().xlsmToxlsxFileConvertor(inputpath, outputpath);
    }

    public void xlsmToxlsxFileConvertor(String inputpath, String outputpath) throws Exception {
        XSSFWorkbook workbook;
        FileOutputStream out;
        System.out.println("inputpath  " + inputpath);
        File directoryPath = new File(inputpath);
        // List of all files and directories
        String contents[] = directoryPath.list();
        System.out.println("List of files and directories in the specified directory:");
        for (int i = 0; i < contents.length; i++) {
            System.out.println(contents[i]);
            // create workbook from XLSM template
            workbook = (XSSFWorkbook) WorkbookFactory
                    .create(new FileInputStream(inputpath + File.separator + contents[i]));
            // save copy as XLSX ----------------START
            OPCPackage opcpackage = workbook.getPackage();
            // get and remove the vbaProject.bin part from the package
            PackagePart vbapart = opcpackage.getPartsByName(Pattern.compile("/xl/vbaProject.bin")).get(0);
            opcpackage.removePart(vbapart);
            // get and remove the relationship to the removed vbaProject.bin part from the
            // package
            PackagePart wbpart = workbook.getPackagePart();
            PackageRelationshipCollection wbrelcollection = wbpart
                    .getRelationshipsByType("http://schemas.microsoft.com/office/2006/relationships/vbaProject");
            for (PackageRelationship relship : wbrelcollection) {
                wbpart.removeRelationship(relship.getId());
            }
            // set content type to XLSX
            workbook.setWorkbookType(XSSFWorkbookType.XLSX);

            // write out the XLSX

            out = new FileOutputStream(outputpath + File.separator + contents[i].replace(".xlsm", "") + ".xlsx");
            workbook.write(out);
            out.close();
            System.out.println("done");
            workbook.close();
        }
    }

}

Cauda