I found some code.
Workbook objWorkbook = new Workbook();<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
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:
...
<Worksheet ss:Name="test export">
<Names>
<NamedRange ss:Name="_FilterDatabase"
ss:RefersTo="='test export'!R4C1:R4C435" ss:Hidden="1"/>
</Names>
...
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 =:-)