work with .xlsm c#

Question

I tried to use Spire.Xls library, but it does not support .xlsm and when i convert it to .xlsx hasn`t saved it, same with Microsoft.Office.Excel.Interop.

    private void button1_Click(object sender, EventArgs e)
    {
        //string xlsm = @"D:\foot_Regular B07BNJ56GV B07BNK8S3Q B07BMX2NN4 with3.xlsm";
        string xlsx = @"D:\foot_Regular B07BNJ56GV B07BNK8S3Q B07BMX2NN4 with3.xlsx";
        //ConverXlsmToXlsx(xlsm, xlsx);

        //string xlsx = @"D:\1.xlsx";

        /* Load Excel File */
        Excel.Application excelApp = new Excel.Application();

        Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(xlsx, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

        /* Load worksheets collection */
        Excel.Sheets excelSheets = excelWorkbook.Worksheets;

        /* Select first worksheet */
        Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets[1];


        /* Deleting first 87 Rows */
        Excel.Range range = excelWorksheet.get_Range("1:87").EntireRow;
        range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);

        /* Save File */
        excelWorkbook.SaveAs(@"D:\out_file.xlsx");
        excelWorkbook.Close(false);
        excelApp.Application.Quit();

        /* Release COM objects otherwise Excel remain running */
        releaseObject(range);
        releaseObject(excelWorkbook);
        releaseObject(excelWorksheet);
        releaseObject(excelApp);

        MessageBox.Show("Finished");
    }

Conver function:

   public static void ConverXlsmToXlsx(string path, string outputPath)
    {
        byte[] byteArray = File.ReadAllBytes(path);
        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(outputPath, stream.ToArray());
        }
    }

How i can easy work with .xlsm files via C#? Help please with it, I would really appreciate it.

score 0 · Answer 1 · answered Jul 09 '18 at 11:41

EPPlus seems to be the library to use.

You don't need Interop or an actual installation of MS Excel.

They have a sample how to work with VBA macros, but it seems your problem was just reading and saving files without touching the macros, so you should be good with their basic samples:

using (ExcelPackage package = new ExcelPackage(newFile))
{
    // make your modifications

    package.Save();
}

score 0 · Answer 2 · answered Jul 09 '18 at 12:23

0

I personally use ClosedXML.

ClosedXML makes it easier for developers to create Excel 2007+ (.xlsx, .xlsm, etc) files. It provides a nice object oriented way to manipulate the files (similar to VBA) without dealing with the hassles of XML Documents. It can be used by any .NET language like C# and VisualBasic.NET.

You can find more details here.

answered Jul 09 '18 at 12:23

mb14

460
4
15

string path = @"D:\foot_Regular3.xlsm"; var workbook = new XLWorkbook(path); throws "System.TypeLoadException" in mscorlib.dll – Dima Morgunov Jul 10 '18 at 09:48
It could be several things. You can check this answer [here](https://stackoverflow.com/a/16086422/7147323) – mb14 Jul 10 '18 at 11:26

score 0 · Answer 3 · answered Jul 10 '18 at 10:00

Spire.XLS supports .xlsm files, here is the code to directly delete rows from a xlsm file with it:

Workbook workbook = new Workbook();
workbook.LoadFromFile("Input.xlsm");

Worksheet sheet = workbook.Worksheets[0];

sheet.DeleteRow(1,87);

workbook.SaveToFile("Output.xlsm", ExcelVersion.Version2007);

I use the Spire.XLS Pack(Hotfix) Version:8.6.6

work with .xlsm c#

3 Answers3