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); } }