none
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
    Monday, January 22, 2007 11:03 PM

Answers

  • 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 = drDevil.GetType().ToString();
              string type7 = dr[7].GetType().ToString();
                    }
                }
            }

    Tuesday, January 23, 2007 4:25 AM

All replies

  • 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 = drDevil.GetType().ToString();
              string type7 = dr[7].GetType().ToString();
                    }
                }
            }

    Tuesday, January 23, 2007 4:25 AM
  • 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
    Tuesday, January 23, 2007 7:50 PM
  • ComponentOne XLS for .NET is the solution.....
    Friday, November 23, 2007 12:25 PM
  • 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/
    Friday, June 03, 2011 4:24 PM
  • What if you don't have excel, is there a way to do this without excel on the PC?
    Jamie
    Saturday, July 02, 2011 5:18 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.

     


    Jamie
    Monday, July 04, 2011 5:03 PM
  • 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
    Monday, July 04, 2011 5:04 PM
  • Hi, you can also try this C# Excel component, it has a straightforward API for converting between Excel file and CSV file in C#:

    // Convert XLS to CSV.
    ExcelFile.Load("Workbook.xls", LoadOptions.XlsDefault).Save("Workbook.csv", SaveOptions.CsvDefault);
    
    // Convert CSV to XLS.
    ExcelFile.Load("Workbook.csv", LoadOptions.CsvDefault).Save("Workbook.xls", SaveOptions.XlsDefault);

    You can also remove the middleman and just read Excel file in C#, make some changes on your XLS and save it back again, here is a sample:

    // Load Excel file.
    var workbook = ExcelFile.Load("Workbook.xls", LoadOptions.XlsDefault);
    // Select active worksheet.
    var worksheet = workbook.Worksheets.ActiveWorksheet;
    
    // Interact through all allocated cells
    foreach (var row in worksheet.Rows)
        foreach (var cell in row.AllocatedCells)
            if(Regex.IsMatch(cell.Value.ToString(), yourRegexPattern))
                // Make some changes.
                cell.Value = "Apply changes!";
    
    // Save Excel file.
    workbook.Save("Workbook.xls", SaveOptions.XlsDefault);

    • Edited by Mario Anicc Friday, October 18, 2013 10:23 AM Correcting formating
    Friday, October 18, 2013 10:22 AM