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

Question

I believe that there is a lot of discussion on this issue. but I read all the messages and try, but it never works with C #. my goal is simple that I have an existing csv file. just want to convert exel file and do. many have said that using spire.xls is something, but I believe that MS.office.interop.excel can handle this.

Convert Excel file from .csv to .xlsx

I read above and this is the same as my problem. but the above code does not work on my pc .. i need to import another dll to use this. I am just copying the code from this site. copy again ...

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 a lot of mistakes. change the code below and now im only using MS.office.interop.excel and MS.office.interop.core in my link. it seems i need to use another dll file. anyway I made this code and am creating new code. this reduces the error, but I do not know what is the right 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 is the 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 to just grab the existing csv file and just modify the excel file. someone has a different solution because this answer does not work on my computer. (FROM#)

Answer 1

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

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

Another approach is to use components suitable for this purpose.
I used EEplus and it does the dirty work. It is licensed by LGPL, but the author doesn’t seem to worry about 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 customize the CVS structure using ExcelTextFormat .

I tested it with some data taken from here .

Here are some examples.

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 like NPOI or ClosedXML . NPOI and ClosedXML cannot read the CSV and perform the conversion, but using the ReadCsv function, you can do it yourself.

Both of these projects are licensed.

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 in a sample project on github there with some test for actions comparing the three products.

Answer 2

At the top of the file, insert the following line:

 using Microsoft.Office.Interop.Excel; 

This will include the namespace required to use the XlFileFormat and XlSaveAsAccessMode classes. If this does not work, you may need to add a link to this DLL in your project.

The Close method takes the following arguments:

• Savechanges
• File name
• RouteWorkbook

The documentation for this is here .

Hope this helps.