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.
Thanks to Christian Sauer, the EPPLUS.dll worked.
Solution Explorer > Project Name > Add > Reference > Browse to EPPLUS.dll
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.IO;
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"));
}
)
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();
}
}