public static ColumnMappingViewModel CreateBlankSourceViewModel(ColumnMappingImportType importType, TableMappingViewModel tableMappingViewModel,
DBColumn destinationColumn, ColumnUse columnUse)
{
switch (importType)
{
case (SQLImporter.ViewModel.ColumnMappingImportType.Excel):
ExcelColumnMapping excelColumnMapping = new ExcelColumnMapping("", destinationColumn, columnUse);
return(new ExcelColumnMappingViewModel(excelColumnMapping, tableMappingViewModel));
case (SQLImporter.ViewModel.ColumnMappingImportType.Table):
TableColumnMapping tableColumnMapping = new TableColumnMapping(null, null, destinationColumn, columnUse);
return(new TableColumnMappingViewModel(tableColumnMapping, tableMappingViewModel));
case (SQLImporter.ViewModel.ColumnMappingImportType.Literal):
LiteralColumnMapping literalColumnMapping = new LiteralColumnMapping("", LiteralType.String, destinationColumn, columnUse);
return(new LiteralColumnMappingViewModel(literalColumnMapping, tableMappingViewModel));
default:
NullColumnMapping nullColumnMapping = new NullColumnMapping(destinationColumn, columnUse);
return(new NullColumnMappingViewModel(nullColumnMapping, tableMappingViewModel));
}
}
public void StatementColumnMappingPartTest()
{
DBColumn col = new DBColumn(null, "TestCol", true, DBDatatype.integer);
SourceDataEntry entry = SourceDataEntry.CreateDataEntry("Test", DataType.String, "Test");
SourceDataRow row = new SourceDataRow(new SourceDataEntry[] { entry }, "");
DBTable table = new DBTable("dbo", "TestTable");
DBColumn col1 = new DBColumn(table, "TestCol1", true, DBDatatype.integer);
table.Columns = new List <DBColumn>()
{
col1
};
TableMapping tableMapping = new TableMapping(table, TableMappingImportType.Insert, null);
NullColumnMapping nullColumnMapping = new NullColumnMapping(col, ColumnUse.Insert);
LiteralColumnMapping literalColumnMapping1 = new LiteralColumnMapping("Test", LiteralType.String, col, ColumnUse.Insert);
LiteralColumnMapping literalColumnMapping2 = new LiteralColumnMapping("Test's", LiteralType.String, col, ColumnUse.Insert);
ExcelColumnMapping excelColumnMapping = new ExcelColumnMapping("Test", col, ColumnUse.Insert);
TableColumnMapping tableColMapping = new TableColumnMapping(tableMapping, col1, col, ColumnUse.Insert);
StatementColumnMappingPart nullColumnPart = new StatementColumnMappingPart(nullColumnMapping, row);
StatementColumnMappingPart literalColumnPart1 = new StatementColumnMappingPart(literalColumnMapping1, row);
StatementColumnMappingPart literalColumnPart2 = new StatementColumnMappingPart(literalColumnMapping2, row);
StatementColumnMappingPart excelColumnPart = new StatementColumnMappingPart(excelColumnMapping, row);
StatementColumnMappingPart tableColumnPart = new StatementColumnMappingPart(tableColMapping, row);
Assert.AreEqual("NULL", nullColumnPart.GetColumnMappingValue());
Assert.AreEqual("'Test'", literalColumnPart1.GetColumnMappingValue());
Assert.AreEqual("'Test''s'", literalColumnPart2.GetColumnMappingValue());
Assert.AreEqual("'Test'", excelColumnPart.GetColumnMappingValue());
StatementTableVariablePart tableVariablePart = new StatementTableVariablePart(tableMapping);
Assert.AreEqual(String.Format("(SELECT TOP 1 t.TestCol1 FROM {0} t)", tableVariablePart.GetTableVariable()),
tableColumnPart.GetColumnMappingValue());
}
/// <summary>
/// 打开CSV 文件
/// </summary>
/// <param name="fileName">文件全名</param>
/// <param name="firstRow">开始行</param>
/// <param name="firstColumn">开始列</param>
/// <param name="getRows">获取多少行</param>
/// <param name="getColumns">获取多少列</param>
/// <param name="haveTitleRow">是有标题行</param>
/// <returns>DataTable</returns>
public static List <T> ReadCsv <T>(string filePath, Int16 firstRow = 0, Int16 firstColumn = 0, Int16 getRows = 0, Int16 getColumns = 0, bool haveTitleRow = true)
where T : new()
{
List <T> result = new List <T>();
if (!File.Exists(filePath))
{
throw new FileNotFoundException(filePath + " not exists.");
}
FileStream fs = new FileStream(filePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
StreamReader sr = new StreamReader(fs, System.Text.Encoding.Default);
try
{
List <ExcelColumnMapping> mappings = new List <ExcelColumnMapping>();
var properties = typeof(T).GetProperties();
foreach (var property in properties)
{
var attr = property.GetCustomAttribute <ExcelColumnNameAttribute>();
var indexAttr = property.GetCustomAttribute <ExcelColumnIndexAttribute>();
if (attr != null)
{
mappings.Add(new ExcelColumnMapping()
{
Property = property, ColumnIndex = (indexAttr as ExcelColumnIndexAttribute).ColumnIndex, ColumnName = (attr as ExcelColumnNameAttribute).ColumnName
});
}
}
//记录每次读取的一行记录
string strLine = "";
//记录每行记录中的各字段内容
string[] aryLine;
//标示列数
int columnCount = 0;
//是否已建立了表的字段
bool bCreateTableColumns = false;
//第几行
int iRow = 1;
//去除无用行
if (firstRow > 0)
{
for (int i = 1; i < firstRow; i++)
{
sr.ReadLine();
}
}
// { ",", ".", "!", "?", ";", ":", " " };
string[] separators = { "," };
//逐行读取CSV中的数据
while ((strLine = sr.ReadLine()) != null)
{
strLine = strLine.Trim();
aryLine = strLine.Split(separators, System.StringSplitOptions.RemoveEmptyEntries);
columnCount = aryLine.Length;
if (columnCount == 0)
{
continue;
}
if (bCreateTableColumns == false)
{
bCreateTableColumns = true;
var columnList = aryLine.ToList();
foreach (var item in columnList)
{
var mapping = mappings.FirstOrDefault(m => m.ColumnName == item.Replace("\"", "").Trim());
if (mapping != null)
{
mapping.ColumnIndex = columnList.IndexOf(item);
}
}
continue;
}
T data = default(T);
data = new T();
try
{
for (int j = 0; j < mappings.Count; j++)
{
ExcelColumnMapping map = mappings[j];
var value = aryLine[map.ColumnIndex].Replace("\"", "").Replace("=", "").Replace("'", "");
if (value.Length > 49)
{
value = value.Substring(0, 49);
}
map.Property.SetValue(data, value);
}
result.Add(data);
}
catch (Exception ex)
{
throw ex;
}
iRow = iRow + 1;
if (getRows > 0)
{
if (iRow > getRows)
{
break;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
sr.Close();
fs.Close();
}
return(result);
}
internal void tableMappingSwitchDatabase(TableMapping[] tableMappings, Database db)
{
Dictionary <int, TableMapping> newTableMappings = new Dictionary <int, TableMapping>();
foreach (TableMapping tableMapping in tableMappings)
{
DBTable table = db.Tables.Where(t => t.Reference == tableMapping.DestinationTable.Reference).FirstOrDefault();
if (table != null)
{
TableMapping newTableMapping = new TableMapping(table, tableMapping.ImportType, new ColumnMapping[0]);
newTableMappings.Add(tableMapping.Index, newTableMapping);
}
}
foreach (int index in newTableMappings.Keys)
{
TableMapping newTableMapping = newTableMappings[index];
List <ColumnMapping> newColumnMappings = new List <ColumnMapping>();
TableMapping oldTableMapping = tableMappings
.Where(t => t.Index == index).First();
ColumnMapping[] oldColumnMappings = oldTableMapping.ColumnMappings;
foreach (ColumnMapping oldColumnMapping in oldColumnMappings)
{
DBColumn column = newTableMapping.DestinationTable.Columns
.Where(c => c.Name == oldColumnMapping.DestinationColumn.Name)
.FirstOrDefault();
if (column != null)
{
string type = oldColumnMapping.GetType().ToString();
ColumnMapping newColumnMapping = null;
if (type == typeof(ExcelColumnMapping).ToString())
{
var excelColumnMapping = (ExcelColumnMapping)oldColumnMapping;
newColumnMapping = new ExcelColumnMapping(excelColumnMapping.SourceHeader, column, oldColumnMapping.ColumnUse);
}
else if (type == typeof(TableColumnMapping).ToString())
{
var tableColumnMapping = (TableColumnMapping)oldColumnMapping;
if (newTableMappings.ContainsKey(tableColumnMapping.SourceTableMapping.Index))
{
TableMapping newSourceTableMapping = newTableMappings[tableColumnMapping.SourceTableMapping.Index];
DBColumn newSourceColumn = newSourceTableMapping.DestinationTable.Columns
.Where(c => c.Name == tableColumnMapping.SourceColumn.Name)
.FirstOrDefault();
if (newSourceColumn != null)
{
newColumnMapping = new TableColumnMapping(newSourceTableMapping, newSourceColumn, column, oldColumnMapping.ColumnUse);
}
}
}
else if (type == typeof(LiteralColumnMapping).ToString())
{
var literalColumnMapping = (LiteralColumnMapping)oldColumnMapping;
newColumnMapping = new LiteralColumnMapping(literalColumnMapping.Literal, literalColumnMapping.LiteralType, column, literalColumnMapping.ColumnUse);
}
if (newColumnMapping == null)
{
newColumnMapping = new NullColumnMapping(column, oldColumnMapping.ColumnUse);
}
newColumnMappings.Add(newColumnMapping);
}
}
newTableMapping.ColumnMappings = newColumnMappings.ToArray();
}
this.TableMappings = newTableMappings.Values.ToList();
}
public static List <T> ReadExcelNoIndex <T>(string filePath, string sheetName)
where T : new()
{
List <T> result = null;
if (!File.Exists(filePath))
{
throw new FileNotFoundException(filePath + " not exists.");
}
List <ExcelColumnMapping> mappings = new List <ExcelColumnMapping>();
var properties = typeof(T).GetProperties();
foreach (var property in properties)
{
var attr = property.GetCustomAttribute <ExcelColumnNameAttribute>();
if (attr != null)
{
mappings.Add(new ExcelColumnMapping()
{
Property = property, ColumnIndex = mappings.Count, ColumnName = (attr as ExcelColumnNameAttribute).ColumnName
});
}
}
using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
var workbook = WorkbookFactory.Create(stream);
var sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
if (sheet == null)
{
throw new FormatException("sheet not exists");
}
result = new List <T>();
IRow row = null;
ICell cell = null;
T data = default(T);
for (int i = 1; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
if (row == null)
{
continue;
}
data = new T();
try
{
for (int j = 0; j < mappings.Count; j++)
{
ExcelColumnMapping map = mappings[j];
cell = row.GetCell(map.ColumnIndex);
if (cell != null)
{
if (cell.CellType == CellType.Numeric)
{
if (map.Property.PropertyType == typeof(double))
{
map.Property.SetValue(data, cell.NumericCellValue);
}
else if (cell.DateCellValue != null)
{
map.Property.SetValue(data, cell.DateCellValue);
}
}
else
{
if (!string.IsNullOrEmpty(cell.StringCellValue))
{
cell.SetCellType(CellType.String);
if (map.ColumnName == "联系电话" || map.ColumnName == "订单编号" ||
map.ColumnName == "联系手机" || map.ColumnName == "手机")
{
map.Property.SetValue(data, cell.StringCellValue.Replace("\"", "").Replace("=", "").Replace("'", ""));
}
else
{
map.Property.SetValue(data, cell.StringCellValue);
}
}
}
}
}
result.Add(data);
}
catch (Exception ex)
{
throw new Exception($"第{i}行,{ex.Message}");
}
}
}
return(result);
}
public static List <T> ReadExcel <T>(string filePath, string sheetName)
where T : new()
{
List <T> result = null;
if (!File.Exists(filePath))
{
throw new FileNotFoundException(filePath + " not exists.");
}
List <ExcelColumnMapping> mappings = new List <ExcelColumnMapping>();
var properties = typeof(T).GetProperties();
foreach (var property in properties)
{
var attr = property.GetCustomAttribute <ExcelColumnNameAttribute>();
var indexAttr = property.GetCustomAttribute <ExcelColumnIndexAttribute>();
if (attr != null)
{
mappings.Add(new ExcelColumnMapping()
{
Property = property, ColumnIndex = (indexAttr as ExcelColumnIndexAttribute).ColumnIndex, ColumnName = (attr as ExcelColumnNameAttribute).ColumnName
});
}
}
using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
var workbook = WorkbookFactory.Create(stream);
var sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
if (sheet == null)
{
throw new FormatException("sheet not exists");
}
result = new List <T>();
IRow row = null;
ICell cell = null;
T data = default(T);
//读取列头
var excelColumnHeads = sheet.GetRow(0);
foreach (var item in excelColumnHeads.Cells)
{
cell = item;
if (cell != null)
{
cell.SetCellType(CellType.String);
var value = cell.StringCellValue;
var mapping = mappings.FirstOrDefault(m => m.ColumnName == value.Trim());
if (mapping != null)
{
mapping.ColumnIndex = excelColumnHeads.Cells.IndexOf(item);
}
}
}
for (int i = 1; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
if (row == null)
{
continue;
}
data = new T();
try
{
for (int j = 0; j < mappings.Count; j++)
{
ExcelColumnMapping map = mappings[j];
cell = row.GetCell(map.ColumnIndex);
if (cell != null)
{
cell.SetCellType(CellType.String);
var value = cell.StringCellValue;
if (map.ColumnName == "联系电话" || map.ColumnName == "订单编号" ||
map.ColumnName == "联系手机" || map.ColumnName == "手机")
{
value = value.Replace("\"", "").Replace("=", "").Replace("'", "");
}
map.Property.SetValue(data, value);
}
}
result.Add(data);
}
catch (Exception ex)
{
throw ex;
}
}
}
return(result);
}
public static TableMapping[] TableMappingTestData()
{
DBTable addressTable = new DBTable("dbo", "Address");
DBColumn a_idCol = new DBColumn(addressTable, "a_id", true, DBDatatype.integer);
DBColumn streetNameCol = new DBColumn(addressTable, "StreetName", false, DBDatatype.nvarchar);
DBColumn streetNumberCol = new DBColumn(addressTable, "StreetNumber", false, DBDatatype.integer);
DBColumn zipCodeCol = new DBColumn(addressTable, "ZipCode", false, DBDatatype.integer);
addressTable.Columns = new List <DBColumn>()
{
a_idCol, streetNameCol, streetNumberCol, zipCodeCol
};
NullColumnMapping a_idMapping = new NullColumnMapping(a_idCol, ColumnUse.Insert);
ExcelColumnMapping streetNameMapping = new ExcelColumnMapping("Street name", streetNameCol, ColumnUse.Insert);
ExcelColumnMapping streetNumberMapping = new ExcelColumnMapping("Street number", streetNumberCol, ColumnUse.Insert);
ExcelColumnMapping zipCodeMapping = new ExcelColumnMapping("Zip code", zipCodeCol, ColumnUse.Insert);
TableMapping addressTableMapping = new TableMapping(addressTable, TableMappingImportType.Insert, new ColumnMapping[] { a_idMapping, streetNameMapping, streetNumberMapping, zipCodeMapping });
DBTable personTable = new DBTable("dbo", "Person");
DBColumn p_idCol = new DBColumn(personTable, "p_id", true, DBDatatype.integer);
DBColumn firstNameCol = new DBColumn(personTable, "FirstName", false, DBDatatype.nvarchar);
DBColumn lastNameCol = new DBColumn(personTable, "LastName", false, DBDatatype.nvarchar);
DBColumn a_idPersonCol = new DBColumn(personTable, "a_id", false, DBDatatype.integer);
personTable.Columns = new List <DBColumn>()
{
p_idCol, firstNameCol, lastNameCol, a_idPersonCol
};
TableMapping personTableMapping = new TableMapping(personTable, TableMappingImportType.Insert, new ColumnMapping[0]);
NullColumnMapping p_idMapping = new NullColumnMapping(p_idCol, ColumnUse.Insert);
ExcelColumnMapping firstNameMapping = new ExcelColumnMapping("FirstName", firstNameCol, ColumnUse.Insert);
ExcelColumnMapping lastNameMapping = new ExcelColumnMapping("Surname", lastNameCol, ColumnUse.Insert);
TableColumnMapping aIdMapping = new TableColumnMapping(addressTableMapping, a_idMapping.DestinationColumn, a_idPersonCol, ColumnUse.Insert);
personTableMapping.ColumnMappings = new ColumnMapping[] { p_idMapping, firstNameMapping, lastNameMapping, aIdMapping };
DBTable contactInfoTable = new DBTable("dbo", "ContactInfo");
DBColumn pn_idCol = new DBColumn(contactInfoTable, "pn_id", true, DBDatatype.integer);
DBColumn textCol = new DBColumn(contactInfoTable, "text", false, DBDatatype.nvarchar);
DBColumn p_idCICol = new DBColumn(contactInfoTable, "p_id", false, DBDatatype.integer);
DBColumn ci_idCICol = new DBColumn(contactInfoTable, "ci_id", false, DBDatatype.integer);
contactInfoTable.Columns = new List <DBColumn>()
{
pn_idCol, textCol, p_idCICol, ci_idCICol
};
ExcelColumnMapping phoneNumberMapping = new ExcelColumnMapping("Phone", textCol, ColumnUse.Insert);
TableColumnMapping pIDMapping = new TableColumnMapping(personTableMapping, p_idMapping.DestinationColumn, p_idCICol, ColumnUse.Insert);
LiteralColumnMapping citIdMapping = new LiteralColumnMapping("1", LiteralType.Integer, ci_idCICol, ColumnUse.Insert);
TableMapping phoneTableMapping = new TableMapping(contactInfoTable, TableMappingImportType.Insert, new ColumnMapping[] { phoneNumberMapping, pIDMapping, citIdMapping });
ExcelColumnMapping mobileNumberMapping = new ExcelColumnMapping("Mobile", textCol, ColumnUse.Insert);
TableColumnMapping pIDMobileMapping = new TableColumnMapping(personTableMapping, p_idMapping.DestinationColumn, p_idCICol, ColumnUse.Insert);
LiteralColumnMapping citIdMobileMapping = new LiteralColumnMapping("2", LiteralType.Integer, ci_idCICol, ColumnUse.Insert);
TableMapping mobileTableMapping = new TableMapping(contactInfoTable, TableMappingImportType.Insert, new ColumnMapping[] { mobileNumberMapping, pIDMobileMapping, citIdMobileMapping });
return(new TableMapping[] { personTableMapping, phoneTableMapping, addressTableMapping, mobileTableMapping });
}
private string excelColumnMappingPart(ExcelColumnMapping mapping, SourceDataEntry dataEntry)
{
return(SQLServerDataEntryTranslator.Translate(dataEntry));
}
public ExcelColumnMappingViewModel(ExcelColumnMapping excelColumnMapping, TableMappingViewModel tableMappingViewModel) :
base(excelColumnMapping, tableMappingViewModel)
{
this.importType = ColumnMappingImportType.Excel;
}
