public ExcelSheetMetadata GetMetaData(string file)
{
using (var stream = File.Open(file, FileMode.Open, FileAccess.Read))
using (var excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream))
{
var excelMetaData = new ExcelSheetMetadata();
excelReader.IsFirstRowAsColumnNames = true;
if (excelReader.Read())
{
for (int i = 0; i < excelReader.FieldCount; i++)
{
var excelFieldName = excelReader.GetString(i);
if (string.IsNullOrEmpty(excelFieldName))
{
// Ignore blank column names
continue;
}
var columnName = excelFieldName.Trim();
excelMetaData.AddColumn(columnName);
}
}
return(excelMetaData);
}
}
public void ImportToTable(string path, string tableName, ExcelSheetMetadata meta)
{
using (var stream = File.Open(path, FileMode.Open, FileAccess.Read))
using (var excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream))
{
excelReader.IsFirstRowAsColumnNames = true;
excelReader.Read();
var bulk = new BulkDataAccess();
bulk.BulkInsertTo(excelReader, tableName, meta);
}
}
public void CreateTable(string tableName, ExcelSheetMetadata meta)
{
var sb = new StringBuilder();
sb.AppendLine("create table [" + tableName + "] (");
sb.AppendLine(string.Join(", ", meta.Columns.Select(x => string.Format("[{0}] varchar(max)", x.ColumnName))));
sb.AppendLine(");");
using (var con = new SqlConnection(ConnectionManager.GetConnectionStringForCurrentWindow()))
using (var cmd = new SqlCommand(sb.ToString(), con))
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
}
public void BulkInsertTo(IExcelDataReader excelReader, string tableName, ExcelSheetMetadata metadata)
{
using (var bulkCopy = new SqlBulkCopy(ConnectionManager.GetConnectionStringForCurrentWindow()))
{
bulkCopy.DestinationTableName = tableName;
int ordinal = 0;
foreach (var column in metadata.Columns)
{
var mapping = new SqlBulkCopyColumnMapping
{
DestinationOrdinal = ordinal,
SourceOrdinal = ordinal++
};
bulkCopy.ColumnMappings.Add(mapping);
}
bulkCopy.WriteToServer(excelReader);
}
}