.net - C# convert csv to xls (using existing csv file)

Question

i believe here are lot a discussion with this matter. but i read all post and try but it never work with c#. my goal is simple that i have existing csv file. just want convert exel file and done. many people said that using spire.xls something but i believe MS .office.interop.excel could handle it.

Converting Excel File From .csv To .xlsx

i read above issue and this is same as my problem. but above code not work in my PC.. do i need to import other dll to use this. i just copy code from that site. copy again below...

currently im using Lib as MS.office.interop.excel and MS.office.interop.core

Application app = new Application();
Workbook wb = app.Workbooks.Open(@"C:estcsv.csv", Type.Missing, Type.Missing,               Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.SaveAs(@"C:estcsv.xlsx", XlFileFormat.xlOpenXMLWorkbook, Type.Missing,    Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.Close();
app.Quit();

here are lot a error. modify code is below and now im only using MS.office.interop.excel and MS.office.interop.core in my reference. it looks like i need to use another dll file. anyway i did follow that code and make new code. it reduce error but i don't know this is correct approach. below is what i tried now.

Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;

System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);

xlWorkBook = xlApp.Workbooks.Open(@"C:estcsv.csv", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlWorkBook.SaveAs(@"C:estcsv.xlsx",    XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlWorkBook.Close(); 

and here are error message

Error 3 The name 'XlFileFormat' does not exist in the current context C:UsersjochoiDesktopjoseph_BT_??_codeDC_Test - ver01DC_TestDC.cs 528 54 DC_Test
Error 4 The name 'XlSaveAsAccessMode' does not exist in the current context C:UsersjochoiDesktopjoseph_BT_??_codeDC_Test - ver01DC_TestDC.cs 528 142 DC_Test
Error 4 No overload for method 'Close' takes '0' arguments C:UsersjochoiDesktopjoseph_BT_??_codeDC_Test - ver01DC_TestDC.cs 525 13 DC_Test

my goal is just grab exist csv file and just change to excel file. does anyone has other solution because that answer is not work in my pc. (c#)

Answer 1

COM Interop is not the best solution, especially if you're planning to run your code in a server environment.

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

Another approach is to use components fit for that purpose.
I've used EEplus and it does its dirty job. It has a LGPL licence but the author does not seem to be to worried about you using it in your commercial product.

Just install the nuget package:

Install-Package EPPlus

and use this code:

using System.IO;
using OfficeOpenXml;

class Program
{
    static void Main(string[] args)
    {
        string csvFileName = @"FL_insurance_sample.csv";
        string excelFileName = @"FL_insurance_sample.xls";

        string worksheetsName = "TEST";

        bool firstRowIsHeader = false;

        var format = new ExcelTextFormat();
        format.Delimiter = ',';
        format.EOL = "
";              // DEFAULT IS "
";
        // format.TextQualifier = '"';

        using (ExcelPackage package = new ExcelPackage(new FileInfo(excelFileName)))
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(worksheetsName);
            worksheet.Cells["A1"].LoadFromText(new FileInfo(csvFileName), format, OfficeOpenXml.Table.TableStyles.Medium27, firstRowIsHeader);
            package.Save();
        }

        Console.WriteLine("Finished!");
        Console.ReadLine();
    }
}

You can configure the structure of you CVS using ExcelTextFormat.

I've tested it with some data taken from here.

Some more samples can be found here.

UPDATE:

Another option is to read the CSV file yourself as a text file:

private IEnumerable<string[]> ReadCsv(string fileName, char delimiter = ';')
{
    var lines = System.IO.File.ReadAllLines(fileName, Encoding.UTF8).Select(a => a.Split(delimiter));
    return (lines);
}

and use other open-source projects such as NPOI or ClosedXML. NPOI and ClosedXML cannot read CSV and do the conversion but using the function ReadCsv you can do it yourself.

Both these projects have permissive licences.

NPOI Conversion:

private static bool ConvertWithNPOI(string excelFileName, string worksheetName, IEnumerable<string[]> csvLines)
{
    if (csvLines == null || csvLines.Count() == 0)
    {
    return (false);
    }

    int rowCount = 0;
    int colCount = 0;

    IWorkbook workbook = new XSSFWorkbook();
    ISheet worksheet = workbook.CreateSheet(worksheetName);

    foreach (var line in csvLines)
    {
    IRow row = worksheet.CreateRow(rowCount);

    colCount = 0;
    foreach (var col in line)
    {
        row.CreateCell(colCount).SetCellValue(TypeConverter.TryConvert(col));
        colCount++;
    }
    rowCount++;
    }

    using (FileStream fileWriter = File.Create(excelFileName))
    {
       workbook.Write(fileWriter);
       fileWriter.Close();
    }

    worksheet = null;
    workbook = null;

    return (true);
}

ClosedXML Conversion:

private static bool ConvertWithClosedXml(string excelFileName, string worksheetName, IEnumerable<string[]> csvLines)
{
    if (csvLines == null || csvLines.Count() == 0)
    {
    return (false);
    }

    int rowCount = 0;
    int colCount = 0;

    using (var workbook = new XLWorkbook())
    {
    using (var worksheet = workbook.Worksheets.Add(worksheetName))
    {
        rowCount = 1;
        foreach (var line in csvLines)
        {
        colCount = 1;
        foreach (var col in line)
        {
            worksheet.Cell(rowCount, colCount).Value = TypeConverter.TryConvert(col);
            colCount++;
        }
        rowCount++;
        }

    }
    workbook.SaveAs(excelFileName);
    }

    return (true);
}

If someone is interested there's a sample project on github with some test for performances comparing the three products.