C# application to import excel file content into a text file

Question

I have excel file with following columns

Field1 Field2 Field3, Field4
===============================
123 4   abcd    efghi
456 5   aabb    ffjjk
789 7   ccdd    ababa

I would like to write a c# console application that would read the excel file and create text file with the following output

Field1 Field2 Field3, Field4
===============================
123 4   ab  hi
456 5   aa  jk
789 7   cc  ba

in my console application, I should be able to specify per column in excel file, the start and, end columns.

How would I write the application from start to finish?

"from start to finish" sounds like you want others to do your work. I would strongly suggest pointing to a specific problem. — Simon D., Dec 08 '10 at 22:25
This "question" should be broken down into all the sub-parts of the problem that are giving you trouble. Do you know how to access data from an excel file? Do you know how to get user input in a console application? Do you know how to iterate, transform, and output data? — jball, Dec 08 '10 at 22:25

score 4 · Accepted Answer · edited May 23 '17 at 11:47

One way of doing this is to query to spread sheet

string strConn = "Provider=Microsoft.Jet.OleDb.4.0;data source=C:\\Inetpub\\wwwroot\\CS\\HostData.xls;Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(strConn);

string strSQL = "SELECT * FROM [A1:B439]";
OleDbCommand objCmd = new OleDbCommand(strSQL, objConn);

objConn.Open();
dgReadHosts.DataSource = objCmd.ExecuteReader();
dgReadHosts.DataBind();
objConn.Close();

i grabbed this example from here: http://forums.asp.net/t/1097496.aspx another good source is to search SO :P Reading Excel files from C#

Lastly i think you'll like this one: http://msdn.microsoft.com/en-us/library/dd920313.aspx
it includes an example using linq AND and example of outputting the spreadsheet to the consol

score 2 · Answer 2 · answered Dec 08 '10 at 22:27

You can just use Excel Automation to get out the data and then just write it to a file using the standard System.IO classes.

See here for details: How to automate Microsoft Excel from Microsoft Visual C#.NET

If you manage to retrieve all the values for one row into some kind of array or list, you could then use String.Join to convert that into a tab separated string and then just write one line at a time to the file.

To only get certain parts of the values for some columns, you might be able to use String.Substring.

You might also be able to use OleDB to read out all the data, since it seems to be in a tabular format, which could be faster and avoid the dependency on having Excel installed.

C# application to import excel file content into a text file

2 Answers2