I have a situation where I need to download and save excel file(.xlsx) to .CSV format in .net core console application.

Since, Microsoft.Interop packages are not compatible with .Net core 3.1, what other approach I can use to save Excel file as .CSV?

Appreciate suggestions.

CodePudding user response：

This is a combination of multiple existing answers on SO.

First is from here Convert the xlsx to a DataTable using ClosedXML

using ClosedXML.Excel;
...
    public static DataTable GetDataFromExcel(string path, dynamic worksheet)
    {
        //Save the uploaded Excel file.


        DataTable dt = new DataTable();
        //Open the Excel file using ClosedXML.
        using (XLWorkbook workBook = new XLWorkbook(path))
        {
            //Read the first Sheet from Excel file.
            IXLWorksheet workSheet = workBook.Worksheet(worksheet);

            //Create a new DataTable.

            //Loop through the Worksheet rows.
            bool firstRow = true;
            foreach (IXLRow row in workSheet.Rows())
            {
                //Use the first row to add columns to DataTable.
                if (firstRow)
                {
                    foreach (IXLCell cell in row.Cells())
                    {
                        if (!string.IsNullOrEmpty(cell.Value.ToString()))
                        {
                            dt.Columns.Add(cell.Value.ToString());
                        }
                        else
                        {
                            break;
                        }
                    }
                    firstRow = false;
                }
                else
                {
                    int i = 0;
                    DataRow toInsert = dt.NewRow();
                    foreach (IXLCell cell in row.Cells(1, dt.Columns.Count))
                    {
                        try
                        {
                            toInsert[i] = cell.Value.ToString();
                        }
                        catch (Exception ex)
                        {
                           //Handle this, or don't.
                        }
                        i  ;
                    }
                    dt.Rows.Add(toInsert);
                }
            }
            return dt;
        }

If you need to do any data transformations, do it while the data is in a DataTable.

Then use CSVHelper to export as a CSV (SO answer I found had a solution that didn't use the Culture Info which was added as a requirement to the Library a few updates ago):

using CSVHelper;
using System.Globilization;
....
    public static void SaveCSV(DataTable records)
    {
        string newFile = @"C:\somePath.csv";
        using (StreamWriter writer = new StreamWriter(newFile))
        {
            using (CsvWriter csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
            {
                //add headers 
                foreach (DataColumn dc in records.Columns)
                {
                    csv.WriteField(dc.ColumnName);
                }
                csv.NextRecord();
                foreach(DataRow dr in records.Rows)
                {
                    for (int i = 0; i< records.Columns.Count; i  )
                    {
                        csv.WriteField(dr[i]);
                    }
                    csv.NextRecord();
                }
            }
        }
    }

CodePudding user response：

if it's just a words data no images then you can copy data from excel and save it in another file in