I'm guessing Office 2007 connection strings will result in XML format because Excel 2007 prefers XML. This is what I have in my notes.
The default Excel 2007 file extension is .XLSx (the x means XML)
There is also:
.XLSb files (XML in binary format)
.XlsM files (XML with macros)
With the older format, the engine limited you to 65,000 records. The new format is unlimited,as I recall. So you can:
(1) Use an Excel 2003 connection string if you have less than 65,000 records
(2) Use a loop to output to a CSV file, if perhaps that would serve your purpose better.
Vulpes recently provided some CSV code:
public static void TransferTableToCSV(DataTable dt, string filePath)
{
List<string> csvRows = new List<string>();
string csvRow;
string[] temp;
int count = dt.Columns.Count;
// add column names as first line
temp = new string[count];
for(int i = 0; i < count; i++)
{
temp[i] = dt.Columns[i].ColumnName;
}
csvRow = String.Join(",", temp);
csvRows.Add(csvRow);
// now add data
foreach(DataRow dr in dt.Rows)
{
temp = new string[count];
for(int i = 0; i < count; i++)
{
temp[i] = dr.ItemArray[i].ToString();
}
csvRow = String.Join(",", temp);
csvRows.Add(csvRow);
}
System.IO.File.WriteAllLines(filePath, csvRows.ToArray());
}
The following code exports a datatable to excel using Excel 2003 connection string (if the filename ends in XLS). If the filename ends in XLSx or XLSb or XLSm or if the number of records > 65,000, it switches to an Excel 2007 connection string.
public void subExportToExcel(string sheetToCreate, DataTable dtToExport, string tableName)
{
List<DataRow> rows = new List<DataRow>();
foreach (DataRow row in dtToExport.Rows) rows.Add(row);
subExportToExcel(sheetToCreate, rows, dtToExport, tableName);
}
public void subExportToExcel(string sheetToCreate, List<DataRow> selectedRows, DataTable origDataTable, string tableName)
{
char Space = ' ';
string dest = sheetToCreate;
int i = 0;
while (File.Exists(dest))
{
dest = Path.GetDirectoryName(sheetToCreate) + "\\" + Path.GetFileName(sheetToCreate) + i +Path.GetExtension(sheetToCreate);
i += 1;
}
sheetToCreate = dest;
if (tableName == null) tableName = string.Empty;
tableName = tableName.Trim().Replace(Space, '_');
if (tableName.Length == 0) tableName = origDataTable.TableName.Replace(Space, '_');
if (tableName.Length == 0) tableName = "NoTableName";
if (tableName.Length > 30) tableName = tableName.Substring(0, 30);
//Excel names are less than 31 chars
string queryCreateExcelTable = "CREATE TABLE [" + tableName + "] (";
Dictionary<string, string> colNames = new Dictionary<string, string>();
foreach (DataColumn dc in origDataTable.Columns)
{
//Cause the query to name each of the columns to be created.
string modifiedcolName = dc.ColumnName.Replace(Space, '_').Replace('.', '#');
string origColName = dc.ColumnName;
colNames.Add(modifiedcolName, origColName);
queryCreateExcelTable += "[" + modifiedcolName + "]" + " text,";
}
queryCreateExcelTable = queryCreateExcelTable.TrimEnd(new char[] { Convert.ToChar(",") }) + ")";
//adds the closing parentheses to the query string
if (selectedRows.Count > 65000 && sheetToCreate.ToLower().EndsWith(".xls"))
{
//use Excel 2007 for large sheets.
sheetToCreate = sheetToCreate.ToLower().Replace(".xls", string.Empty) + ".xlsx";
}
string strCn = string.Empty;
string ext = System.IO.Path.GetExtension(sheetToCreate).ToLower();
if (ext == ".xls") strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sheetToCreate + "; Extended Properties='Excel 8.0;HDR=YES'";
if (ext == ".xlsx") strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0 Xml;HDR=YES' ";
if (ext == ".xlsb") strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0;HDR=YES' ";
if (ext == ".xlsm") strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0 Macro;HDR=YES' ";
System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(strCn);
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(queryCreateExcelTable, cn);
cn.Open();
cmd.ExecuteNonQuery();
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + tableName + "]", cn);
System.Data.OleDb.OleDbCommandBuilder cb = new System.Data.OleDb.OleDbCommandBuilder(da);
//creates the INSERT INTO command
cb.QuotePrefix = "[";
cb.QuoteSuffix = "]";
cmd = cb.GetInsertCommand();
//gets a hold of the INSERT INTO command.
foreach (DataRow row in selectedRows)
{
foreach (System.Data.OleDb.OleDbParameter param in cmd.Parameters)
param.Value = row[colNames[param.SourceColumn]];
cmd.ExecuteNonQuery(); //INSERT INTO command.
}
cn.Close();
cn.Dispose();
da.Dispose();
GC.Collect();
GC.WaitForPendingFinalizers();
}

You can try using GemBox <a target="_new" href="http://www.gemboxsoftware.com/GBSpreadsheet.htm">.NET component for reading/writing Excel files.</a> It is compatible with any spreadsheet format (xls,xlsx,csv...)