My issue lies with Microsoft.Office.Interop.Excel. I would like to get rid of references to it, because it requires an install of Excel on the machine that you're running a particular piece of code that references the .dll.

What this means in practice is that I have started using EPPlus to modify Excel files, workbooks, worksheets, etc...

EPPlus has functionality to save to .csv, but only starting from version 5, which requires a paid license. For budgetary reasons, I will not be purchasing that license, and therefore be staying at a previous version of EPPlus.

Though, this means that I have no convenient way of saving an Excel file (xslx) as csv.

Can anybody tell me a way of saving an excel sheet as CSV without using Interop.Excel

CodePudding user response：

I maintain a couple free and open source libraries that can help with this: Sylvan.Data.Csv and Sylvan.Data.Excel.

Converting from Excel to CSV is trivial using these libraries.

using Sylvan.Data.Csv;
using Sylvan.Data.Excel;

using var excelReader = ExcelDataReader.Create(@"data.xlsx");
using var csvWriter = CsvDataWriter.Create("data.csv");
csvWriter.Write(excelReader);

If performance is a concern, these libraries are also the fastest libraries in the .NET ecosystem for their respective tasks..

CodePudding user response：

I believe another alternative is to use the NPOI library to read Excel files without using Interop.

More details about this library you can find at: https://github.com/dotnetcore/NPOI

Here is a very simple example of using NPOI, I used StreamWriter to save the CSV file and FileStream to get the XLSX file in this example:

using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;

string csvSeparator = ";";
string filePath = @"{XLXS file path}";
StreamWriter sw = new StreamWriter(@"{CSV file path}", true);

using (var file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
    string strExt = System.IO.Path.GetExtension(filePath);

    IWorkbook wb;

    #region Check extension to define the Workbook
    if (strExt.Equals(".xls"))
    {
        wb = new HSSFWorkbook(file);
    }
    else
    {
        wb = new XSSFWorkbook(file);
    }
    #endregion

    ISheet sheet = wb.GetSheetAt(0);//Start reading at index 0

    for (int i = 0; i <= sheet.LastRowNum; i  )//Row
    {
        IRow row = sheet.GetRow(i);

        for (int j = 0; j < row.LastCellNum; j  )//Column
        {
            ICell cell = row.GetCell(j);

            object cellValue = null;

            #region Check cell type in order to define its value type
            switch (cell.CellType)
            {
                case CellType.Blank:
                case CellType.Error:
                    cellValue = null;
                    break;
                case CellType.Boolean:
                    cellValue = cell.BooleanCellValue;
                    break;
                case CellType.Numeric:
                    cellValue = cell.NumericCellValue;
                    break;
                case CellType.String:
                    cellValue = cell.StringCellValue;
                    break;
                default:
                    cellValue = cell.StringCellValue;
                    break;
            }
            #endregion

            sw.Write(cellValue.ToString());//Write the cell value
            sw.Write(csvSeparator);//Add the CSV separator
        }
        sw.Write(Environment.NewLine);//Add new line
    }
    sw.Flush();
    sw.Close();
}