Hello everybody,

today I want to document a few lines of code which can help to convert excel file to CSV file with help of OLE:

static void ConvertExcelToCsv(string excelFileName, string csvOutputFileName, int worksheetNumber = 1)
{
    if (!File.Exists(excelFileName)) throw new FileNotFoundException(excelFileName);
    if (File.Exists(csvOutputFileName)) throw new ArgumentException("File already exists: " + csvOutputFileName);
 
    var connectionString =
        $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={excelFileName};Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"";
    var oleDbConnection = new OleDbConnection(connectionString);
 
    // get schema, then data
    var dataTable = new DataTable();
    try
    {
        oleDbConnection.Open();
        var schemaTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        if (schemaTable != null && schemaTable.Rows.Count < worksheetNumber) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");
        if (schemaTable != null)
        {
            var worksheet = schemaTable.Rows[worksheetNumber - 1]["table_name"].ToString().Replace("'", "");
            var sql = $"select * from [{worksheet}]";
            var oleDbDataAdapter = new OleDbDataAdapter(sql, oleDbConnection);
            oleDbDataAdapter.Fill(dataTable);
        }
    }
    catch (Exception e)
    {
        Console.WriteLine(e.ToString());
    }
    finally
    {
        // free resources
        oleDbConnection.Close();
    }
 
    // write out CSV data
    using (var streamWriter = new StreamWriter(csvOutputFileName))
    {
        foreach (DataRow row in dataTable.Rows)
        {
            bool firstLine = true;
            foreach (DataColumn col in dataTable.Columns)
            {
                if (!firstLine) { streamWriter.Write(","); } else { firstLine = false; }
                var data = row[col.ColumnName].ToString().Replace("\"", "\"\"");
                streamWriter.Write($"\"{data}\"");
            }
            streamWriter.WriteLine();
        }
    }
}