Monday, January 22, 2007 11:03 PMHi all
I'm trying to convert an Excel file (xls) to csv and back again, using C#.
I'd like to convert the xls file into csv, then use regex to parse and change some things in the csv file, and convert the changed csv file to xsl.
Could you help? Code snippets will be greatly appreciated.
Many thanks in advance.
Tuesday, January 23, 2007 4:25 AM
I can help with the read part, the best way I found (for free :)) was to use the OledbConnecton to open the excel file. I'm sure there's an equivalent that allows you to write back.
I initially found help from: http://davidhayden.com/blog/dave/archive/2006/05/31/2975.aspx and http://www.codeproject.com/office/excel_using_oledb.asp
Looking on google the following may be useful:
string ExcelFilename = "c:\\ExcelFile.xls";
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=" + ExcelFilename + ";" + @"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""";
using (OleDbConnection connection = new OleDbConnection(connectionString))
worksheets = connection.GetSchema("Tables");
foreach (DataRow row in worksheets.Rows)
// For Sheets: 0=Table_Catalog,1=Table_Schema,2=Table_Name,3=Table_Type
// For Columns: 0=Table_Name, 1=Column_Name, 2=Ordinal_Position
string SheetName = (string)row;
OleDbCommand command = new OleDbCommand(@"SELECT * FROM [" + SheetName + "]", connection);
OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
oleAdapter.SelectCommand = command;
DataTable dt = new DataTable();
for (int r = 0; r < dt.Rows.Count; r++)
string type1 = dr.GetType().ToString();
string type2 = dr.GetType().ToString();
string type3 = dr.GetType().ToString();
string type4 = dr.GetType().ToString();
string type5 = dr.GetType().ToString();
string type6 = dr.GetType().ToString();
string type7 = dr.GetType().ToString();
Tuesday, January 23, 2007 7:50 PMMany thanks for this.I'll give it a try. One of the solutions (Gemplus) uses a commercial application.
I'm still on the lookout for solutions, and I'll try yours.
Friday, November 23, 2007 12:25 PMComponentOne XLS for .NET is the solution.....
Friday, June 03, 2011 4:24 PMI have an example of converting from Excel to a CSV completely in c# code on my website at http://jarloo.com/code/api-code/excel-to-csv/
Saturday, July 02, 2011 5:18 PMWhat if you don't have excel, is there a way to do this without excel on the PC?
Monday, July 04, 2011 5:03 PM
I found a C# zip with code on C# corner on the net that does this perfectly. can't seem to convert it to a vb net console app though, having issues with the conversion.
Monday, July 04, 2011 5:04 PM
worked perfectly for me.. with or without headers too. Only problem is getting it converted over to vb net console app.