Introduction

This article explains how to use the ExcelDataReader library for simple file conversion, particularly Excel to csv. Easily convert your Excel 97-2003(.xls) or 2007(.xlsx) files to simple comma separated value file (.csv).

The CSV file is the easier file to operate with in the program.

Need

As I am a beginner, I experienced some difficulty in operating Excel file directly in my program. So I thought of doing it.

Some of the problems I faced are like I couldn't fetch the appropriate cell, difficulty in accessing row wise, etc.

Since CSV is a simple stream file, it is easier to deal with the file data. CSV data can also be viewed as an excel sheet.

Working

The project works on ExcelDataReader library.

The main aim of this project is to read the Excel data into the program & convert it to an easy to operate format that is CSV.

Coming to the working of project:

IExcelDataReader excelReader

This object allows us to operate with the Excel file.

Then we read whole Excel data into a Dataset & that Dataset will be used to produce CSV file.

DataSet result = excelReader.AsDataSet();

I am using Dataset here because it is easy to get the values and write the CSV file from contents of dataset as the matrix separated by commas.

User friendly Interface

Browse for input Excel file to convert.
Browse for output folder & file name.
Click the CONVERT button.

The converted CSV file will be in the output folder specified.

Using the Code

The ExcelDataReader library is used. Only prototypes are shown in the preview. Get full code in the attached source file. Add the ExcelDataReader library references to your project if you use this code in other projects (References are attached).

FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

// Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);

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

Sheets in Excel file become tables in Dataset:

result.Tables[0].TableName.ToString(); // to get first sheet name (table name)

Using the above codes, the CSV file can be generated.

string csvData = "";
int row_no = 0;

while (row_no < result.Tables[ind].Rows.Count) // ind is the index of table
				// (sheet name) which you want to convert to csv
   {
      for (int i = 0; i < result.Tables[ind].Columns.Count; i++)
          {
             csvData += result.Tables[ind].Rows[row_no][i].ToString() + ",";
          }
      row_no++;
      csvData  += "\n";
   }

After generating CSV data, write it to file.

string output = filepath + filename + ".csv"; // define your own filepath & filename
StreamWriter csv = new StreamWriter(@output, false);
csv.Write(csvData);
csv.Close();

I thank the author of ExcelDataReader library which helped a lot in getting my project done.

Here is the reference for ExcelDataReader:

http://exceldatareader.codeplex.com/

Convert .xlsx & .xls to .csv