I found some code.
Workbook objWorkbook = new Workbook();
objWorkbook.Open("XLSTest3.xml", FileFormatType.SpreadsheetML);
Worksheet objWorksheet = objWorkbook.Worksheets[0];
DataTable dt = new DataTable();
dt = objWorksheet.Cells.ExportDataTableAsString(0, 0, objWorksheet.Cells.MaxRow + 1, objWorksheet.Cells.MaxColumn + 1, true);
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "XLSTest3";
//* Set up the column mappings *
bulkCopy.ColumnMappings.Clear();
bulkCopy.ColumnMappings.Add("First Name", "First Name");
bulkCopy.ColumnMappings.Add("Last Name", "Last Name");
bulkCopy.ColumnMappings.Add("Address", "Address");
bulkCopy.ColumnMappings.Add("Phone", "Phone");
bulkCopy.ColumnMappings.Add("DOB", "DOB");
//* Write from the source to the destination *
bulkCopy.WriteToServer(dt);
}
This worked fine with my test SpreadsheetML that I created myself in Excel using Save As âXML Spreadsheetâ.
However, the files I will be using are SpreadsheetML files that I have no control over what is in them. They are for sure SpreadsheetML and open fine in Excel, but one test file using the code above, gives me:
âError in opening SpreadsheetML file.â
âInvalid formula in cell @0: =âtest exportâ!R4C1:R4C435â
The offending XML looks like this:
âŠ
<NamedRange ss:Name=â_FilterDatabaseâ
ss:RefersTo=â=âtest exportâ!R4C1:R4C435â ss:Hidden=â1â/>
...
Canât we just ignore âformula errorsâ?
When I manually fix that cell, it loads the Worksheet, but then this line:
dt = objWorksheet.Cells.ExportDataTableAsString(0, 0, objWorksheet.Cells.MaxRow + 1, objWorksheet.Cells.MaxColumn + 1, true);
give me the error âColumnName is required when it is part of a DataTable.". I assume this is a DataTable error and not really your product.
So, what is really the best way to load SpreadsheetML into SQL Server?
Thanks,
Mike =:-)