0

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

user11251276
  • 53
  • 1
  • 8
  • You can use NPOI Excel library to archieve this – MestreDosMagros Oct 06 '21 at 17:28
  • Does this answer your question? [Writing data into CSV file in C#](https://stackoverflow.com/questions/18757097/writing-data-into-csv-file-in-c-sharp) – Pac0 Oct 06 '21 at 17:34
  • There are tons of libraries to read excel and tons to write csv – Magnetron Oct 06 '21 at 19:42
  • Reading Excel: [ExcelMapper](https://github.com/mganss/ExcelMapper), [ExcelDataReader](https://github.com/ExcelDataReader/ExcelDataReader), [ClosedXML](https://github.com/ClosedXML/ClosedXML), [NPOI](https://github.com/nissl-lab/npoi) – Magnetron Oct 06 '21 at 19:46

2 Answers2

3

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..

MarkPflug
  • 28,292
  • 8
  • 46
  • 54
1

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();
}
ctkano
  • 11
  • 3