Click here to Skip to main content
Click here to Skip to main content

Convert .xlsx & .xls to .csv

, 15 Sep 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
Simple file conversion using C#.NET

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

image.jpg

  • 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:

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Srikanth Anandateertha
Software Developer
India India
No Biography provided

Comments and Discussions

 
QuestionNOT WORKING Pinmembera2z4u076-Nov-12 12:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.1411022.1 | Last Updated 15 Sep 2011
Article Copyright 2011 by Srikanth Anandateertha
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid