Convert xls or xlsx file with multiple sheets into one csv file using interop

Question

I am trying to convert a xls or xlsx file with multiple sheets into one CSV file using c# and the interop library. I am only getting the one sheet in the CSV file. I know I can specify the sheet to save as or change the active sheet to save that one but I am looking for a solution to append all the sheets to the same CSV file that will work with both xls and xlsx files. I am automating this and don't care what is in the excel document just want to pull the string values out and append it to the csv file. Here is the code I am using:

Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = false;
app.DisplayAlerts = false;
Workbook wkb = app.Workbooks.Open(fullFilePath);
wkb.SaveAs(newFileName, XlFileFormat.xlCSVWindows);

Is this even possible?

Are all the sheets formatted exactly the same? Eg - Do they each have a header row? Is the combined content too large to fit on one sheet? — Tim Williams
CSV files are basically raw text files and do not support multiple tabs. If the sheets have the same format then you can add the content to the first from all of the others. — Sid Holland

Dan Csharpster Dan Csharpster · Accepted Answer · 2013-07-09T16:06:12

I'm just getting started tackling a similar situation, but I believe this may address your needs:

http://www.codeproject.com/Articles/246772/Convert-xlsx-xls-to-csv

This uses the ExcelDataReader api that you can get from NuGet

http://exceldatareader.codeplex.com/

Like Tim was saying, you're going to have to make sure and possibly validate that the columns and structure are the same between sheets. You may also have to eat the header rows on all the sheets after the first one. I'll post an update and some code samples once I've finished.

Update [7/15/2013]. Here's my finished code. Not very fancy, but it gets the job done. All of the sheets are tables in the DataSet, so you just loop through the tables adding onto your destination. I'm outputting to a MongoDB, but I'm guessing you can swap that out for a StreamWriter for your CSV file rather easily.

        private static void ImportValueSetAttributeFile(string filePath)
    {
        FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

        // Reading from a OpenXml Excel file (2007 format; *.xlsx)
        IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

        // DataSet - The result of each spreadsheet will be created in the result.Tables
        DataSet result = excelReader.AsDataSet();

        // Free resources (IExcelDataReader is IDisposable)
        excelReader.Close();

        var connectionString = ConfigurationManager.ConnectionStrings[0].ConnectionString;
        var database = ConfigurationManager.AppSettings["database"];
        var mongoAccess = new MongoDataAccess(connectionString, database);

        var cdm = new BaseDataManager();

        int ind = 0;

        for (int i = 0; i < result.Tables.Count; i++)
        {
            int row_no = 1;
            while (row_no < result.Tables[ind].Rows.Count) // ind is the index of table
                // (sheet name) which you want to convert to csv
            {
                var currRow = result.Tables[ind].Rows[row_no];
                var valueSetAttribute = new ValueSetAttribute()
                    {
                        CmsId = currRow[0].ToString(),
                        NqfNumber = currRow[1].ToString(),
                        ValueSetName = currRow[2].ToString(),
                        ValueSetOid = currRow[3].ToString(),
                        Definition = currRow[4].ToString(),
                        QdmCategory = currRow[5].ToString(),
                        Expansion = currRow[6].ToString(),
                        Code = currRow[7].ToString(),
                        Description = currRow[8].ToString(),
                        CodeSystem = currRow[9].ToString(),
                        CodeSystemOid = currRow[10].ToString(),
                        CodeSystemVersion = currRow[11].ToString()
                    };

                cdm.AddRecords<ValueSetAttribute>(valueSetAttribute, "ValueSetAttributes");

                row_no++;
            }
            ind++;
        }
    }

Convert xls or xlsx file with multiple sheets into one csv file using interop

1 Answers