private static void SetColumnDefinitionAdditional(XlsIo definitionFile)
{
var tableCopy = definitionFile.XlsSheet.ToList <XlsRow>();
var sheet = definitionFile.XlsSheet;
definitionFile.XlsSheet.Select(o => new
{
ModelName = o["ModelName"].ToStr(),
TableName = o["TableName"].ToStr(),
Label = o["Label"].ToStr(),
Base = o["Base"].ToBool()
})
.Where(o => !o.Base && o.TableName != "string")
.Distinct()
.ForEach(column =>
sheet.Where(o => o["Base"].ToBool()).ForEach(commonColumnDefinition =>
{
if (IsTargetColumn(sheet, commonColumnDefinition, column.TableName) &&
IsNotExists(tableCopy, commonColumnDefinition, column.TableName))
{
var copyColumnDefinition = new XlsRow();
definitionFile.XlsSheet.Columns.ForEach(xcolumn =>
copyColumnDefinition[xcolumn] = commonColumnDefinition[xcolumn]);
copyColumnDefinition["Id"] =
column.TableName + "_" + copyColumnDefinition["ColumnName"];
copyColumnDefinition["ModelName"] = column.ModelName;
copyColumnDefinition["TableName"] = column.TableName;
copyColumnDefinition["Label"] = column.Label;
copyColumnDefinition["Base"] = "0";
copyColumnDefinition["ItemId"] = "0";
tableCopy.Add(copyColumnDefinition);
}
}));
definitionFile.XlsSheet = new XlsSheet(tableCopy, definitionFile.XlsSheet.Columns);
}
private static bool IsTargetColumn(
XlsSheet sheet, XlsRow commonColumnDefinition, string tableName)
{
return(commonColumnDefinition["ItemId"].ToInt() == 0 ||
sheet.Any(o => o["TableName"].ToString() == tableName &&
o["ItemId"].ToInt() > 0));
}
/// <summary>
/// компановка данных Ответственные специалисты по проекту
/// </summary>
/// <returns></returns>
private List <CellWorkOrder> GetTab2Data(XlsRow row)
{
var result = new List <CellWorkOrder>(3);
var value1 = row.Cells[10]?.Value.ToString();
var value2 = row.Cells[15]?.Value.ToString();
var value3 = row.Cells[16]?.Value.ToString();
_sideTab1Mapp.TryGetValue((int)row.Cells[1].Index, out var sideId);
if (sideId == null)
{
return(result);
}
if (!string.IsNullOrEmpty(value1))
{
result.Add(new CellWorkOrder {
Value = value1, TitleId = 1, SideId = sideId
});
}
if (!string.IsNullOrEmpty(value2))
{
result.Add(new CellWorkOrder {
Value = value2, TitleId = 2, SideId = sideId
});
}
if (!string.IsNullOrEmpty(value3))
{
result.Add(new CellWorkOrder {
Value = value3, TitleId = 3, SideId = sideId
});
}
return(result);
}
/// <summary>
/// компановка данных Шапка листа
/// </summary>
/// <returns></returns>
private List <CellWorkOrder> GetTab1Data(XlsRow row)
{
var result = new List <CellWorkOrder>();
const int sideColIdx = 1;
const int valueColIdx = 10;
_sideTab1Mapp.TryGetValue((int)row.Cells[sideColIdx].Index, out var sideId);
if (sideId == null)
{
return(result);
}
string value;
if (row.Cells[valueColIdx].AddressName == "K9" || row.Cells[valueColIdx].AddressName == "K10")
{
value = ConvertToOrclDate(row.Cells[valueColIdx]?.Value.ToString());
}
else
{
value = row.Cells[valueColIdx]?.Value.ToString();
}
if (string.IsNullOrEmpty(value))
{
return(result);
}
result.Add(new CellWorkOrder
{
SideId = sideId,
TitleId = 1,
Value = value
});
return(result);
}
public XlsRow AddRow()
{
var row = new XlsRow();
Items.Add(row);
return(row);
}
public XlsRow AddEmptyRow(int colSpan = 0, int rowSpan = 0)
{
var row = new XlsRow();
Items.Add(row);
row.AddEmptyCell(colSpan, rowSpan);
return(row);
}
private static SqlUpdateOrInsert SqlStatement(
string tableName, XlsIo xlsIo, XlsRow xlsRow)
{
var sqlStatement = new SqlUpdateOrInsert();
sqlStatement.TableBracket = "[" + tableName + "]";
xlsIo.XlsSheet.Columns.ForEach(xlsColumn =>
AddParam(tableName, xlsRow, sqlStatement, xlsColumn));
return(sqlStatement);
}
/// <summary>
/// компановка данных Тэхнико-экономические показатели строительства по проекту
/// </summary>
/// <returns></returns>
private List <CellWorkOrder> GetTable3Data(XlsRow row)
{
var result = new List <CellWorkOrder>();
decimal?sideId = null;
var parts = row.Cells[1].Value?.ToString().Split('(');
var input = parts != null && parts.Length > 1 ? parts[0]?.Trim() : row.Cells[1].Value?.ToString().Trim();
if (decimal.TryParse(input, out var res))
{
sideId = res;
}
else
{
return(result);
}
for (var i = 2; i < 15; i++)
{
var titleId = _titleTab3Mapp.FirstOrDefault(f => f.MappVal == row.Cells[i].ColumnName)?.Key;
if (titleId == null)
{
continue;
}
string value;
if (row.Cells[i].ColumnName == "H" || row.Cells[i].ColumnName == "I")
{
value = row.Cells[i].Value?.ToString().Replace("ст", string.Empty).Replace("к", string.Empty);
}
else
{
value = ChangeDecSep(row.Cells[i]);
}
if (string.IsNullOrEmpty(value))
{
continue;
}
result.Add(new CellWorkOrder
{
SideId = sideId,
TitleId = titleId,
Value = value
});
}
return(result);
}
private static void AddParam(
string tableName,
XlsRow xlsRow,
SqlUpdateOrInsert sqlStatement,
string columnName)
{
var value = xlsRow[columnName].ToString();
if (!value.IsNullOrEmpty())
{
if (value == "''")
{
value = string.Empty;
}
if (IsUniqueColumn(tableName, columnName))
{
AddParam_NoUpdate(tableName, sqlStatement, columnName, value);
AddWhere(tableName, sqlStatement, columnName, value);
}
else if (IsPkColumn(tableName, columnName))
{
AddParam_NoUpdate(tableName, sqlStatement, columnName, value);
if (!HasUniqueColumn(tableName))
{
AddWhere(tableName, sqlStatement, columnName, value);
}
}
else if (IsIdentityColumn(tableName, columnName))
{
AddParam_NoUpdate(tableName, sqlStatement, columnName, value);
}
else if (IsHashColumn(tableName, columnName))
{
AddParam_Hash(tableName, sqlStatement, columnName, value);
}
else if (IsDateTimeColumn(tableName, columnName))
{
AddParam_DateTime(tableName, sqlStatement, columnName, value);
}
else
{
AddParam_General(tableName, sqlStatement, columnName, value);
}
}
}
private void BuildTable(XlsRow row, WordTableDef table, int rowNo, int colNo)
{
var tableRow = table.InsertRow(rowNo);
tableRow.Style = row.Style;
foreach (var item in row.Items)
{
/*if (item is XlsDataField)
* {
* var cell = tableRow.AddCell(colNo);
* cell.Style = item.Style;
* cell.ColSpan = 1;
* var val = ((XlsCell)item).GetValue();
* var type = ((XlsDataField)item).Field.GetDataType();
* var s = String.Empty;
* if (val != null)
* switch (type)
* {
* case CissaDataType.Text:
* s = (string)val;
* break;
* case CissaDataType.Int:
* s = ((int)val).ToString();
* break;
* case CissaDataType.Float:
* s = ((double)val).ToString("F");
* break;
* case CissaDataType.Currency:
* s = ((decimal)val).ToString("N");
* break;
* case CissaDataType.DateTime:
* s = ((DateTime)val).ToShortDateString();
* break;
* case CissaDataType.Bool:
* s = ((bool)val) ? "Да" : "Нет";
* break;
* default:
* s = val.ToString();
* break;
* }
* cell.AddText(s, item.Style);
* colNo++;
* }
* else if (item is XlsCell)
* {
* var cell = tableRow.AddCell(colNo);
* cell.Style = item.Style;
* cell.ColSpan = 1;
* var val = ((XlsCell)item).GetValue();
* cell.AddText(val != null ? val.ToString() : String.Empty, item.Style);
* colNo++;
* }
* else if (item is XlsNode)
* {
* var cell = tableRow.AddCell(colNo);
* cell.Style = item.Style;
* cell.ColSpan = item.GetCols();
* if (item is XlsTextNode)
* cell.AddText(((XlsTextNode)item).Text, item.Style);
* else if (item is XlsCellNode)
* {
* var val = ((XlsCellNode)item).Cell.GetValue();
* cell.AddText(val != null ? val.ToString() : String.Empty, item.Style);
* }
*
* var node = (XlsNode)item;
* if (node.Items.Count > 0)
* AddNodeCell(node, table, rowNo + 1, colNo);
* colNo += node.GetCols();
* }*/
AddTableRowCell(table, tableRow, item, ref rowNo, ref colNo);
}
}
private static bool IsNotExists(
List <XlsRow> tableCopy, XlsRow commonColumnDefinition, string tableName)
{
return(!tableCopy.Any(o => o["TableName"].ToString() == tableName &&
o["ColumnName"].ToString() == commonColumnDefinition["ColumnName"].ToString()));
}