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:\testcsv.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:\testcsv.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:\testcsv.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:\testcsv.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:\Users\jochoi\Desktop\joseph_BT_전류_code\DC_Test - ver01\DC_Test\DC.cs 528 54 DC_Test
Error 4 The name 'XlSaveAsAccessMode' does not exist in the current context C:\Users\jochoi\Desktop\joseph_BT_전류_code\DC_Test - ver01\DC_Test\DC.cs 528 142 DC_Test
Error 4 No overload for method 'Close' takes '0' arguments C:\Users\jochoi\Desktop\joseph_BT_전류_code\DC_Test - ver01\DC_Test\DC.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#)

Yes, i add more detail in my question and also add error messages.... — Choi Joseph, Oct 30 '14 at 09:09
you could use the library from the question you've linked to. Or you could try the approach used in the answer for this question http://stackoverflow.com/questions/4781478/convert-csv-into-xls — 5uperdan, Oct 30 '14 at 09:20

LeftyX · Accepted Answer · 2020-05-22T16:39:16.060

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 = "\r";              // DEFAULT IS "\r\n";
        // 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.

Great, it works. but is it free ware? Because I'm going to use this in my company. i download EPPlus 3.1.3.zip. Is this freeware? can i use this in my company? — Choi Joseph, Oct 30 '14 at 10:28
It's [LGPL](http://choosealicense.com/licenses/). You can read this [thread](https://epplus.codeplex.com/discussions/208363). The author says you can use it in your commercial project. — LeftyX, Oct 30 '14 at 10:46
@ChoiJoseph: My pleasure. Don't forget to accept answers. That's the way SO works. Cheers. — LeftyX, Oct 30 '14 at 11:17
I have problem with TypeConverter.TryConvert. It said no such method in TypeConverter System.ComponentModel.dll. Is it self defined? — , Aug 03 '15 at 07:36
@user585440: `TypeConverter` has its own class. Did you restore all the packages ? — LeftyX, Aug 04 '15 at 08:34
What package? I use VS 2010 "(.net 4) System.ComponentModel. — , Aug 04 '15 at 22:57
@user585440: this is the [TypeConverter](https://github.com/Leftyx/ConvertCsvToExcel/blob/master/ConvertCsvToExcel/TypeConverter.cs). — LeftyX, Aug 05 '15 at 08:14
For what it's worth, I have had a lot of trouble with NPOI. It's ported from a Java library called POI. So, most of the documentation and SO questions are about POI. Sometimes the code is similar but often the Java solution is a completely different method which is frustrating. So far EPPLUS has been much better. — Jordan Ryder, Oct 16 '19 at 21:12
FYI for anyone using this answer, EPPlus does now have a cost for commercial use: https://www.epplussoftware.com/en/LicenseOverview/ — Josh, May 22 '20 at 09:22
@Josh: Thanks for the info. fixed and updated the link. Maybe the sample won't work :-s — LeftyX, May 22 '20 at 16:40
big thanks to OP for the NPOI code. Regarding the TypeConverter linked, in .net core I was getting an error that it couldn't convert object to double. I changed the TypeConverter signature to return dynamic instead of object and it works great. — truepudding, Sep 22 '20 at 14:04

score 3 · Answer 2 · answered Aug 03 '20 at 14:58

If you want to proceed with OpenXML then you can follow this.

install the nuget package

Install-Package DocumentFormat.OpenXml -Version 2.11.3

Read the CSV file yourself as a text file (Example from above answer):

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

OpenXML code:

private bool ConvertWithOpenXml(string excelFileName, string worksheetName, IEnumerable<string[]> csvLines)
        {
            if (csvLines == null || csvLines.Count() == 0)
            {
                return (false);
            }
            using (SpreadsheetDocument package = SpreadsheetDocument.Create(excelFileName, SpreadsheetDocumentType.Workbook, true))
            {
                package.AddWorkbookPart();
                package.WorkbookPart.Workbook = new Workbook();
                package.WorkbookPart.AddNewPart<WorksheetPart>();
                SheetData xlSheetData = new SheetData();
                foreach (var line in csvLines)
                {
                    Row xlRow = new Row();
                    foreach (var col in line)
                    {
                        Cell xlCell = new Cell(new InlineString(new Text(col.ToString()))) { DataType = CellValues.InlineString };
                        xlRow.Append(xlCell);
                    }
                    xlSheetData.Append(xlRow);
                }
                package.WorkbookPart.WorksheetParts.First().Worksheet = new Worksheet(xlSheetData);
                package.WorkbookPart.WorksheetParts.First().Worksheet.Save();

            
                // create the worksheet to workbook relation
                package.WorkbookPart.Workbook.AppendChild(new Sheets());
                package.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild(new Sheet()
                {
                    Id = package.WorkbookPart.GetIdOfPart(package.WorkbookPart.WorksheetParts.First()),

                    SheetId = 1,

                    Name = worksheetName

                });

                package.WorkbookPart.Workbook.Save();
            }
            return (true);
        }

score -1 · Answer 3 · answered Oct 30 '14 at 09:20

At the top of your file insert the following line:

using Microsoft.Office.Interop.Excel;

This will include the namespace needed to use the XlFileFormat and XlSaveAsAccessMode classes. If this does not work you may need to add a reference to this DLL to your project as well.

The Close method takes in the following arguments:

SaveChanges
Filename
RouteWorkbook

The documentation for that is here.

Hope that helps.

C# convert csv to xls (using existing csv file)

3 Answers3

Linked