4

Need to convert a .xls or .xlsx to a .csv without the use of Excel in an C#/ASP.net web app. The application is currently using the NPOI.dll for some functionality but I do not see any info on the codeplex wiki for NPOI for that particular functionality. Does anyone have any suggestions?

Thanks

Tim
  • 1,249
  • 5
  • 28
  • 54
  • Thanks chezy. I did see something similar on C# corner but I missed that post on stackoverflow. Thanks for posting that one. I think I will go with this solution. – Tim Sep 27 '11 at 18:23

4 Answers4

5

There are libraries ( Excel Data Reader, for eg) that let you read excel. Once you are able to read data, writing to csv should be simple.

Bala R
  • 107,317
  • 23
  • 199
  • 210
5

ADODB.NET can be used to treat the Excel files as datasource.

//string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;";
string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;";

ConnectionString = string.Format(ConnectionString, @"FullPathToExcelFile");

OleDbConnection conn = new OleDbConnection(ConnectionString);
conn.Open();

OleDbCommand cmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
OleDbDataAdapter oleDBAdapter = new OleDbDataAdapter();
oleDBAdapter.SelectCommand = cmdSelect;

DataSet myDataset = new DataSet();
oleDBAdapter.Fill(myDataset);
conn.Close(); 

// Do whatever with data in myDataset including export to csv...
amit_g
  • 30,880
  • 8
  • 61
  • 118
3

Have a look at the FileHelpers library. It will do exactly what you want.

With FileHelpers, you can read from Excel files and write to csv or flat text files. And it's object oriented! All you have to do is to annotate classes with some attributes so that they match the source excel file.

Consider this example: 

[DelimitedRecord("|")]
public class CustomersVerticalBar {
   public string CustomerID;
   public string CompanyName;
   ...
}

Read using this:

ExcelStorage provider = new ExcelStorage(typeof(CustomersVerticalBar));

provider.StartRow = 3;
provider.StartColumn = 2;

provider.FileName = "Customers.xls";

CustomerVerticalBar[] res = (CustomerVerticalBar[]) provider.ExtractRecords();

Example taken from here: http://filehelpers.sourceforge.net/example_exceldatalink.html

Mikael Östberg
  • 16,982
  • 6
  • 61
  • 79
  • Thanks Mikael, That is a nice simple solution however, I hate the idea that I have to define the output. It makes this not so flexible for the business if they decide to change something in the future. – Tim Sep 27 '11 at 18:19
  • @MikaelÖstberg - All you have to do is define an ouput. You could then read that input and change it for additional functionality. – Security Hound Sep 27 '11 at 18:29
  • @Tim Were you thinking a generic Excel -> CSV thing? Take anything that is in the Excel file and write it so a CSV? – Mikael Östberg Sep 27 '11 at 18:38
  • 1
    I am not a fan of Filehelpers, because I have had it corrupt data. Doesn't happen very often, but it does happen. The problem lies in FileHelpers.RecordInfo.RecursiveGetFields(...) which in turn calls FileHelpers.FieldInfoCacheManipulator.ResetFieldInfoCache(...) which uses reflection to modify private members of the actual .NET Reflection library in an attempt to force .NET reflection to give fields back in the order they were declared. However Microsoft explicitly states "Your code must not depend on the order in which fields/properties are returned" msdn.microsoft.com/.../kyaxdd3x.aspx – Nathan Sep 27 '11 at 18:48
-4

.xls is a proprietary binary format which cannot be read in plain text format, so you will need office or Libre Office or something to read that... .xlsx is an xml based format, and should be possible just by parsing through the DOM... but you would still be manually iterating through each value and manually delimiting, etc. Have you considered using an xslt?

therealmitchconnors
  • 2,732
  • 1
  • 18
  • 36
  • Unfortunately I am stuck using the format in use by the business end. I was hoping there might be a 3rd party library out there somewhere with that functionality that I didn't know about – Tim Sep 27 '11 at 18:01
  • You might look into the Libre Office SDK, but I haven't used it. It theoretically could help you read xls files. – therealmitchconnors Sep 27 '11 at 18:06
  • I don't think that's his problem - he's already using NPOI to read the Excel files. – Rup Sep 27 '11 at 18:31