5

I have C# app for deleting first few rows from excel and then format file to .csv, but now i got not .xlsx but .xlsm and i cant find how to work with, i cant even load data from columns. Its some report file from SAP and i dont find any macro inside. I tried something like this

        /* Load Excel File */
        Excel.Application excelApp = new Excel.Application();
        string workbookPath = @"file.xlsm";
        Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 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(@"out_file.xlsm");
        excelWorkbook.Close(false);
        excelApp.Application.Quit();

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

This work with .xlsx extension (it will delete rows and save it under another name) but not with .xlsm (program run successfully but it dont delete data). Even if i manually excel file save as .xlsx and run program on that file it dont work, but if i manually copy paste data to another .xlsx and run program on that file it work, i dont get it. How can i rewrite this program to delete rows from .xlsm files ? Please help, thank you.

Muflix
  • 6,192
  • 17
  • 77
  • 153
  • Did you try supplying the file format to `SaveAs`? This code should work as such on XLSM also. – shree.pat18 Aug 05 '14 at 06:13
  • 1
    I know that the library EPPLUS can and will work with both xlsx files and xlsm files. Plus, it's magnitudes faster. – Christian Sauer Aug 05 '14 at 06:40
  • try adding `excelApp.Visible = true;`. This will show the excel UI and you can see what's happening inside – Alireza Aug 05 '14 at 10:39
  • shree.pat18: I tried excelWorkbook.SaveAs(@"file.xlsm", Excel.XlFileFormat.xlWorkbookNormal); and it does not work too. Alireza: It open and close excel, nothing more. Christian Sauer: thank you, i will look at it. If you have experience with it can you post few code here ? – Muflix Aug 05 '14 at 13:44

1 Answers1

2

Thanks to Christian Sauer, the EPPLUS.dll worked.

Step 1

Solution Explorer > Project Name > Add > Reference > Browse to EPPLUS.dll

Step 2

using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.IO;

Step 3 (delete rows range)

 using (var p = new ExcelPackage(new FileInfo(@"file.xlsm")))
            {
                var sheet = p.Workbook.Worksheets["Sheet1"];
                sheet.DeleteRow(1, 87);
                p.SaveAs(new FileInfo(@"output.xlsm"));
            }
)

Step 4 (export .xlsm to .csv)

Insert Code between these lines

                sheet.DeleteRow(1, 87);
           ====>[HERE]
                p.SaveAs(new FileInfo(@"output.xlsm"));

/* Code placed to [HERE] placeholder */
using (var writer = File.CreateText(@"output.csv"))
            {
                var rowCount = sheet.Dimension.End.Row;
                var columnCount = sheet.Dimension.End.Column;
                for (var r = 1; r <= rowCount; r++)
                {
                    for (var c = 1; c <= columnCount; c++)
                    {
                        writer.Write(sheet.Cells[r, c].Value);
                        writer.Write(";");
                    }
                    writer.WriteLine();
                }
            }
Muflix
  • 6,192
  • 17
  • 77
  • 153