Is there any simple way to convert .xls file to .csv file? (Excel)

Is there any simple way to convert .xls file to开发者_高级运维 .csv file ? (Excel)

in C# code ?

I mean to take an existing .xls file and convert them to .csv file

Here's a C# method to do this. Remember to add your own error handling - this mostly assumes that things work for the sake of brevity. It's 4.0+ framework only, but that's mostly because of the optional worksheetNumber parameter. You can overload the method if you need to support earlier versions.

static void ConvertExcelToCsv(string excelFilePath, string csvOutputFile, int worksheetNumber = 1) {
   if (!File.Exists(excelFilePath)) throw new FileNotFoundException(excelFilePath);
   if (File.Exists(csvOutputFile)) throw new ArgumentException("File exists: " + csvOutputFile);

   // connection string
   var cnnStr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"", excelFilePath);
   var cnn = new OleDbConnection(cnnStr);

   // get schema, then data
   var dt = new DataTable();
   try {
      cnn.Open();
      var schemaTable = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
      if (schemaTable.Rows.Count < worksheetNumber) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");
      string worksheet = schemaTable.Rows[worksheetNumber - 1]["table_name"].ToString().Replace("'", "");
      string sql = String.Format("select * from [{0}]", worksheet);
      var da = new OleDbDataAdapter(sql, cnn);
      da.Fill(dt);
   }
   catch (Exception e) {
      // ???
      throw e;
   }
   finally {
      // free resources
      cnn.Close();
   }

   // write out CSV data
   using (var wtr = new StreamWriter(csvOutputFile)) {
      foreach (DataRow row in dt.Rows) {
         bool firstLine = true;
         foreach (DataColumn col in dt.Columns) {
            if (!firstLine) { wtr.Write(","); } else { firstLine = false; }
            var data = row[col.ColumnName].ToString().Replace("\"", "\"\"");
            wtr.Write(String.Format("\"{0}\"", data));
         }
         wtr.WriteLine();
      }
   }
}

Checkout the .SaveAs() method in Excel object.

wbWorkbook.SaveAs("c:\yourdesiredFilename.csv", Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV)

Or following:

public static void SaveAs()
{
    Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
    Microsoft.Office.Interop.Excel.Workbook wbWorkbook = app.Workbooks.Add(Type.Missing);
    Microsoft.Office.Interop.Excel.Sheets wsSheet = wbWorkbook.Worksheets;
    Microsoft.Office.Interop.Excel.Worksheet CurSheet = (Microsoft.Office.Interop.Excel.Worksheet)wsSheet[1];

    Microsoft.Office.Interop.Excel.Range thisCell = (Microsoft.Office.Interop.Excel.Range)CurSheet.Cells[1, 1];

    thisCell.Value2 = "This is a test.";

    wbWorkbook.SaveAs(@"c:\one.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    wbWorkbook.SaveAs(@"c:\two.csv", Microsoft.Office.Interop.Excel.XlFileFormat.xlCSVWindows, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

    wbWorkbook.Close(false, "", true);
}

Install these 2 packages

<packages>
  <package id="ExcelDataReader" version="3.3.0" targetFramework="net451" />
  <package id="ExcelDataReader.DataSet" version="3.3.0" targetFramework="net451" />
</packages>

Helper function

using ExcelDataReader;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ExcelToCsv
{
    public class ExcelFileHelper
    {
        public static bool SaveAsCsv(string excelFilePath, string destinationCsvFilePath)
        {

            using (var stream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                IExcelDataReader reader = null;
                if (excelFilePath.EndsWith(".xls"))
                {
                    reader = ExcelReaderFactory.CreateBinaryReader(stream);
                }
                else if (excelFilePath.EndsWith(".xlsx"))
                {
                    reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                }

                if (reader == null)
                    return false;

                var ds = reader.AsDataSet(new ExcelDataSetConfiguration()
                {
                    ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
                    {
                        UseHeaderRow = false
                    }
                });

                var csvContent = string.Empty;
                int row_no = 0;
                while (row_no < ds.Tables[0].Rows.Count)
                {
                    var arr = new List<string>();
                    for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                    {
                        arr.Add(ds.Tables[0].Rows[row_no][i].ToString());
                    }
                    row_no++;
                    csvContent += string.Join(",", arr) + "\n";
                }
                StreamWriter csv = new StreamWriter(destinationCsvFilePath, false);
                csv.Write(csvContent);
                csv.Close();
                return true;
            }
        }
    }
}

Usage :

var excelFilePath = Console.ReadLine();
string output = Path.ChangeExtension(excelFilePath, ".csv");
ExcelFileHelper.SaveAsCsv(excelFilePath, output);

I had to come up with a hybrid solution after updating to Visual Studio 2022 and testing the most relevant answers for this to work.

First, we need to install the following Nuget packages: ExcelDataReader, ExcelDataReader.DataSet and System.Text.Encoding.CodePages

Then, for the sake of clean architecture, proceed to create a separate class within your corresponding namespace:

using ExcelDataReader;
using System.Collections.Generic;
using System.IO;
using System.Text;

namespace YourProjectNameSpace
{
    public class ExcelFileHelper
    {

        /// <summary>
        /// Converts a given XLS into CSV file format.
        /// </summary>
        public static bool SaveAsCsv(string excelFilePath, string destinationCsvFilePath)
        {

            Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);

            using (var stream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                IExcelDataReader reader = null;
                if (excelFilePath.EndsWith(".xls"))
                {
                    reader = ExcelReaderFactory.CreateBinaryReader(stream);
                }
                else if (excelFilePath.EndsWith(".xlsx"))
                {
                    reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                }

                if (reader == null)
                    return false;

                var ds = reader.AsDataSet(new ExcelDataSetConfiguration()
                {
                    ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
                    {
                        UseHeaderRow = false
                    }
                });

                var csvContent = string.Empty;
                int row_no = 0;
                while (row_no < ds.Tables[0].Rows.Count)
                {
                    var arr = new List<string>();
                    for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                    {
                        arr.Add(ds.Tables[0].Rows[row_no][i].ToString());
                    }
                    row_no++;
                    csvContent += string.Join(",", arr) + "\n";
                }
                StreamWriter csv = new StreamWriter(destinationCsvFilePath, false);
                csv.Write(csvContent);
                csv.Close();
                return true;
            }
        }
    }
}

Notice I had to include this line at the beginning of the function:

Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);

If the line from above is omitted, you could end up getting the following error:

NotSupportedException: No data is available for encoding 1252

So, make sure of using it for better compatibility.

Finally, use example:

var execPath = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)?.Replace("file:\\", "");
    
string FileNameXLS = "\\file.xls";
string FileNameCSV = "\\file.csv";
        
Console.WriteLine("Exporting file to CSV...." + "\n");
ExcelFileHelper.SaveAsCsv(execPath + FileNameXLS, execPath + FileNameCSV);
Console.WriteLine("File exported to CSV!" + "\n");

I need to do the same thing. I ended up with something similar to Kman

       static void ExcelToCSVCoversion(string sourceFile,  string targetFile)
    {
        Application rawData = new Application();

        try
        {
            Workbook workbook = rawData.Workbooks.Open(sourceFile);
            Worksheet ws = (Worksheet) workbook.Sheets[1];
            ws.SaveAs(targetFile, XlFileFormat.xlCSV);
            Marshal.ReleaseComObject(ws);
        }

        finally
        {
            rawData.DisplayAlerts = false;
            rawData.Quit();
            Marshal.ReleaseComObject(rawData);
        }


        Console.WriteLine();
        Console.WriteLine($"The excel file {sourceFile} has been converted into {targetFile} (CSV format).");
        Console.WriteLine();
    }

If there are multiple sheets this is lost in the conversion but you could loop over the number of sheets and save each one as csv.

This is a modification of nate_weldon's answer with a few improvements:

• More robust releasing of Excel objects
• Set application.DisplayAlerts = false; before attempting to save to hide prompts

Also note that the application.Workbooks.Open and ws.SaveAs methods expect sourceFilePath and targetFilePath to be full paths (ie. directory path + filename)

private static void SaveAs(string sourceFilePath, string targetFilePath)
{
    Application application = null;
    Workbook wb = null;
    Worksheet ws = null;

    try
    {
        application = new Application();
        application.DisplayAlerts = false;
        wb = application.Workbooks.Open(sourceFilePath);
        ws = (Worksheet)wb.Sheets[1];
        ws.SaveAs(targetFilePath, XlFileFormat.xlCSV);
    }
    catch (Exception e)
    {
        // Handle exception
    }
    finally
    {
        if (application != null) application.Quit();
        if (ws != null) Marshal.ReleaseComObject(ws);
        if (wb != null) Marshal.ReleaseComObject(wb);
        if (application != null) Marshal.ReleaseComObject(application);
    }
}

I integrate the @mattmc3 aswer. If you want to convert a xlsx file you should use this connection string (the string provided by matt works for xls formats, not xlsx):

var cnnStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO\"", excelFilePath);

I had a similar task issue. Convert an inbound transactions like xlsx to tab delimited for automated processing by an existing system. Needs to run unattended. After reviewing many solutions on several different sites, and trying two of them, using MS Office Excel for C#, like above, and running into issues with different versions of MS Office, and older versions possibly on the PC, and having no control over that. I ended up going with... Aspose.Cells through NuGet. Solution is four lines of code.

string soureFilePath = "my/source/path/file.xlsx";
string targetFilePath = "my/output/path/file.txt"; 
var book = new Workbook(soureFilePath);
book.Save(targetFilePath, SaveFormat.Tsv);

It only converts sheet 1, and ignored sheets 2 and 3, but that is Ok for my use. I'm guessing it does have functionality to convert all of them if needed, I just did not need that, so did not look into it.

Their web site, incase folks want to view their information or license agreement (no cost use).

Manipulate Spreadsheets Easily | Aspose.Cells Universal Library https://products.aspose.com/cells

Note: I do not work for Aspose, I am not affiliated with Aspose, and I am not profiting in any way from this posting.