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();
}
}
}