produce csv containing all sheets.

[dwmaillist]

Hello,

I am interested in extracting the information from an excel workbook
with many sheets in it and outputting a csv like text file. Since I
want a single file with all information for all sheets I don't think
that I want to use the SaveAs() method.

Right now the only way I have found to extract the contents of an
individual sheet is to cycle through the sheet one cell at a time
using a range returned by UsedRange. This is inefficient for large
sheets.

What I had "hoped" to do but can't make it work is this

range = (Excel.Range)ws.Rows;
object[] values = (object[])range.Rows.Value2;

for (int rowIndex = 1; rowIndex <= values.GetLength(); rowIndex++) {
sCurrentRow = String.Join(sColumnSeparator,
(string[])values.GetValue(rowIndex));
}

If I could just retrieve the row as a string array then I could use
String.Join and hopefully save some time.

The ideal would be a method like this: ws.getCSV(), which would return
a string containing the CSV for the file.

I would appreciate any help. Thanks!

 

[Robson Siqueira]

hey,

What you could do is to use OLEDB for each sheet you have. Open the
spreadsheet using OLEDB commands and then perform a select * on each sheet.
It will be very good performance-wise, I think.

Source code wise, something like this would work:

using System.Data;
using System.Data.OleDb;
....
String sConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + [Your Excel File Name Here] + ";" +
"Extended Properties=Excel 8.0;";

OleDbConnection objConn = new OleDbConnection(sConnectionString);
objConn.Open();
OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM [Sheet1$]",
objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
DataSet objDataset1 = new DataSet();
objAdapter1.Fill(objDataset1);
objConn.Close();