Convert xls to csv programmatically (and back again)

Question

Hi 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.

Laurence

Answer 1

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:
http://www.carlosag.net/Tools/ExcelXmlWriter/
http://www.gemboxsoftware.com/GBSpreadsheetFree.htm

 string ExcelFilename = "c:\\ExcelFile.xls";
        DataTable worksheets;
        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))
        {
            connection.Open();
            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[2];
                OleDbCommand command = new OleDbCommand(@"SELECT * FROM [" + SheetName + "]", connection);
                OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
                oleAdapter.SelectCommand = command;
                DataTable dt = new DataTable();
                oleAdapter.FillSchema(dt, SchemaType.Source);
                oleAdapter.Fill(dt);
                for (int r = 0; r < dt.Rows.Count; r++)
                {
          string type1 = dr[1].GetType().ToString();
          string type2 = dr[2].GetType().ToString();
          string type3 = dr[3].GetType().ToString();
          string type4 = dr[4].GetType().ToString();
          string type5 = dr[5].GetType().ToString();
          string type6 = dr.GetType().ToString();
          string type7 = dr[7].GetType().ToString();
                }
            }
        }

Answer 2

Many 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.

Thanks

Answer 3

ComponentOne XLS for .NET is the solution.....

Answer 4

I 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/

Answer 5

What if you don't have excel, is there a way to do this without excel on the PC?

---

Jamie

Answer 6

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.

 

---

Jamie

Answer 7

Try this link: http://www.c-sharpcorner.com/UploadFile/yuanwang200409/102242008174401PM/1.aspx

 

worked perfectly for  me.. with or without headers too. Only problem is getting it converted over to vb net console app.

---

Jamie