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