public CommonComments(ProjectWorkbook projectWorkbook)
{
Excel.Workbook wb = _app.ActiveWorkbook;
_projectWorkbook = projectWorkbook;
_SheetUrv12 = ExcelHelper.GetSheet(wb, "Урв12");
_projectManager = new ProjectManager.ProjectManager();
_project = _projectManager.ActiveProject;
_AnalisysSheet = ExcelHelper.GetSheet(wb, _project.AnalysisSheetName);
}
/// Добавить список
/// Добавить столбцы КП
/// Проставить формулы
public void LoadUrv12()
{
pb.SetMainBarVolum(7);
pb.MainBarTick("Очистка");
ClearDataRng12();
string letterName = _project.Columns.Find(x => x.Name == Project.ColumnsNames[StaticColumns.Name]).ColumnSymbol;
string letterNumber = _project.Columns.Find(x => x.Name == Project.ColumnsNames[StaticColumns.Number]).ColumnSymbol;
string letterLevel = _project.Columns.Find(x => x.Name == Project.ColumnsNames[StaticColumns.Level]).ColumnSymbol;
string letterCost = _project.Columns.Find(x => x.Name == Project.ColumnsNames[StaticColumns.CostTotal]).ColumnSymbol;
int lastRow = _AnalisysSheet.UsedRange.Row + _AnalisysSheet.UsedRange.Rows.Count - 1;
pb.MainBarTick("Определение столбцов КП");
List <OfferColumns> addresses = new ProjectWorkbook().OfferColumns;
pb.Writeline("Копирование заголовков");
PrintTitlesOffers12(addresses);
int rowPaste = 14;
int colPaste = 6;
int lastCol = colPaste + 5 * addresses.Count - 1;
int count = lastRow - _project.RowStart + 1;
if (count < 1)
{
throw new AddInException($"Строки отсутствуют лист: {_project.AnalysisSheetName}");
}
pb.SetSubBarVolume(count);
pb.MainBarTick("Заполнение строк");
ProjectWorkbook projectWorkbook = new ProjectWorkbook();
Excel.Range dataRange = projectWorkbook.GetAnalysisRange();
for (int row = _project.RowStart; row <= lastRow; row++)
{
if (pb.IsAborted)
{
throw new AddInException("Процесс остановлен");
}
pb.SubBarTick();
Excel.Range cellNumber = _AnalisysSheet.Range[$"${letterNumber}{row}"];
int columnCellNumber = cellNumber.Column;
string number = cellNumber.Value?.ToString() ?? "";
if (string.IsNullOrEmpty(number))
{
continue;
}
string name = _AnalisysSheet.Range[$"${letterName}{row}"].Value?.ToString() ?? "";
string level = _AnalisysSheet.Range[$"${letterLevel}{row}"].Value?.ToString() ?? "";
string cost = _AnalisysSheet.Range[$"${letterCost}{row}"].Value?.ToString() ?? "";
int levelNum = int.TryParse(level, out int ln) ? ln : 0;
if (levelNum > 0 && levelNum < 6)
{
SheetUrv12.Rows[rowPaste].Insert(Excel.XlInsertShiftDirection.xlShiftDown);
Excel.Range numberCell = SheetUrv12.Cells[rowPaste, 2];
SheetUrv12.Cells[rowPaste, 1].Value = level; // Уровень
numberCell.NumberFormat = "@";
numberCell.Value = number;
SheetUrv12.Cells[rowPaste, 3].Value = name;
string letterOutName = ExcelHelper.GetColumnLetter(numberCell);
int colTotalCost = ExcelHelper.GetColumn(letterCost, _AnalisysSheet);
int column = colTotalCost - columnCellNumber + 1;
SheetUrv12.Cells[rowPaste, 4].Formula =
$"= VLOOKUP(${letterOutName}{rowPaste}, '{_project.AnalysisSheetName}'! {dataRange.Address}, {column}, FALSE)";
// Формат строки по уровню
Dictionary <string, Excel.Range> pallets = ExcelReader.ReadPallet(_SheetPalette);
if (pallets.TryGetValue(level, out Excel.Range pallet))
{
ExcelHelper.SetCellFormat(SheetUrv12.Range[SheetUrv12.Cells[rowPaste, 2], SheetUrv12.Cells[rowPaste, lastCol]], pallet);
}
// Вывод и форматирование значений
foreach (OfferColumns address in addresses)
{
int col = address.ColCostTotalOffer - columnCellNumber + 1;
//РУБ. РФ
SheetUrv12.Cells[rowPaste, colPaste].Formula =
$"= VLOOKUP(${letterOutName}{rowPaste}, '{_project.AnalysisSheetName}'! {dataRange.Address}, {col}, FALSE)";
//% отклонения материалы
col = address.ColDeviationMaterials - columnCellNumber + 1;
SheetUrv12.Cells[rowPaste, colPaste + 1].Formula =
$"= VLOOKUP(${letterOutName}{rowPaste}, '{_project.AnalysisSheetName}'! {dataRange.Address}, {col}, FALSE)";
//% отклонения работы
col = address.ColDeviationWorks - columnCellNumber + 1;
SheetUrv12.Cells[rowPaste, colPaste + 2].Formula =
$"= VLOOKUP(${letterOutName}{rowPaste}, '{_project.AnalysisSheetName}'! {dataRange.Address}, {col}, FALSE)";
// % отклонения всего
string letterOutTotalDiff = ExcelHelper.GetColumnLetter(SheetUrv12.Cells[rowPaste, colPaste]);
SheetUrv12.Cells[rowPaste, colPaste + 3].Formula = $"=IF(D{rowPaste}>0, ${letterOutTotalDiff}{rowPaste}/$D{rowPaste}-1, \"\")";
//КОММЕНТАРИИ К СТОИМОСТИ
col = address.ColComments - columnCellNumber + 1;
SheetUrv12.Cells[rowPaste, colPaste + 4].Formula =
$"= VLOOKUP(${letterOutName}{rowPaste}, '{_project.AnalysisSheetName}'! {dataRange.Address}, {col}, FALSE)";
colPaste += 5;
}
colPaste = 6;
rowPaste++;
}
}
SetConditionFormat12();
pb.MainBarTick("Формулы итогов");
TotalFormuls12();
pb.MainBarTick("Формат ячеек");
SetNumberFormat12(addresses.Count);
pb.MainBarTick("Общие комментарии");
new CommonComments(projectWorkbook).SetInfo();
pb.MainBarTick($"Удаление строки №{_rowStart}");
Excel.Range rng = SheetUrv12.Cells[_rowStart, 1];
rng.EntireRow.Delete();
}
/// <summary>
///
/// </summary>
private void TotalFormuls12()
{
List <OfferColumns> addresses = new ProjectWorkbook().OfferColumns;
// int rowBottomTotal = ExcelHelper.FindCell(SheetUrv12, "ОБЩАЯ СУММА РАСХОДОВ (без НДС)").Row;
int rowBottomTotal = ExcelHelper.FindCell(SheetUrv12, "НДС, 20%").Row - 1;
int lastRow = rowBottomTotal - 2;
string formulaSumm = "";
for (int row = _rowStart; row <= lastRow; row++)
{
string number = SheetUrv12.Cells[row, 2].Value?.ToString() ?? "";
if (string.IsNullOrEmpty(number))
{
continue;
}
number = number.Trim(new char[] { ' ', '.' });
int levelNum = number.Split('.').Length;
if (levelNum == 1)
{
formulaSumm += (string.IsNullOrEmpty(formulaSumm)) ? $"={SheetUrv12.Cells[row, 4].Address}" :
$"+{SheetUrv12.Cells[row, 4].Address}";
}
}
if (!string.IsNullOrEmpty(formulaSumm))
{
SheetUrv12.Cells[rowBottomTotal, 4].Formula = formulaSumm;
SheetUrv12.Cells[rowBottomTotal + 1, 4].Formula =
$"={SheetUrv12.Cells[rowBottomTotal, 4].Address}*0.2";
SheetUrv12.Cells[rowBottomTotal + 2, 4].Formula =
$"={SheetUrv12.Cells[rowBottomTotal, 4].Address}+" +
$"{SheetUrv12.Cells[rowBottomTotal + 1, 4].Address}";
}
int colPaste = 6;
foreach (OfferColumns address in addresses)
{
formulaSumm = "";
//string formulaSumm
for (int row = _rowStart; row <= lastRow; row++)
{
string number = SheetUrv12.Cells[row, 2].Value?.ToString() ?? "";
if (string.IsNullOrEmpty(number))
{
continue;
}
number = number.Trim(new char[] { ' ', '.' });
int levelNum = number.Split('.').Length;
if (levelNum == 1)
{
formulaSumm += (string.IsNullOrEmpty(formulaSumm)) ? $"={SheetUrv12.Cells[row, colPaste].Address}" :
$"+{SheetUrv12.Cells[row, colPaste].Address}";
}
}
if (!string.IsNullOrEmpty(formulaSumm))
{
SheetUrv12.Cells[rowBottomTotal, colPaste].Formula = formulaSumm;
SheetUrv12.Cells[rowBottomTotal + 1, colPaste].Formula =
$"={SheetUrv12.Cells[rowBottomTotal, colPaste].Address}*0.2";
SheetUrv12.Cells[rowBottomTotal + 2, colPaste].Formula =
$"={SheetUrv12.Cells[rowBottomTotal, colPaste].Address}+" +
$"{SheetUrv12.Cells[rowBottomTotal + 1, colPaste].Address}";
colPaste += 5;
}
}
}
/// <summary>
/// Обновление значений урв 12
/// </summary>
/// <param name="pb"></param>
internal void UpdateUrv12()
{
pb.SetMainBarVolum(4);
pb.MainBarTick("Обновление формул \"Урв 12\"");
int colPaste = 1;
ProjectWorkbook projectWorkbook = new ProjectWorkbook();
Excel.Range dataRange = projectWorkbook.GetAnalysisRange();
List <OfferColumns> addresses = new ProjectWorkbook().OfferColumns;
pb.Writeline("Копирование заголовков");
PrintTitlesOffers12(addresses);
string letterNumber = projectWorkbook.GetLetter(StaticColumns.Number);
Excel.Range cellNumber = _AnalisysSheet.Range[$"${letterNumber}{_project.RowStart}"];
int columnCellNumber = cellNumber.Column;
// int rowBottomTotal = ExcelHelper.FindCell(SheetUrv12, "ОБЩАЯ СУММА РАСХОДОВ (без НДС)").Row;
int rowBottomTotal = ExcelHelper.FindCell(SheetUrv12, "НДС, 20%").Row - 1;
int lastRow = rowBottomTotal - 2;
pb.SetSubBarVolume(addresses.Count);
//// Вывод и форматирование значений
foreach (OfferColumns address in addresses)
{
if (pb.IsAborted)
{
throw new AddInException("Процесс остановлен");
}
pb.SubBarTick();
colPaste += 5;
pb.Writeline($"Формулы: {address.ParticipantName}");
string textCost = SheetUrv12.Cells[_rowStart, colPaste].Value?.ToString() ?? "";
if (!string.IsNullOrEmpty(textCost))
{
continue; // Пропустить заполненные КП
}
string formulaSumm = "";
for (int row = _rowStart; row <= lastRow; row++)
{
string number = SheetUrv12.Cells[row, 2].Value?.ToString() ?? "";
if (string.IsNullOrEmpty(number))
{
continue;
}
number = number.Trim(new char[] { ' ', '.' });
int levelNum = number.Split('.').Length;
if (levelNum == 1)
{
formulaSumm += (string.IsNullOrEmpty(formulaSumm)) ? $"={SheetUrv12.Cells[row, colPaste].Address}" :
$"+{SheetUrv12.Cells[row, colPaste].Address}";
}
// Формат строки по уровню
Dictionary <string, Excel.Range> pallets = ExcelReader.ReadPallet(_SheetPalette);
string keyLvl = levelNum.ToString();
if (pallets.TryGetValue(keyLvl, out Excel.Range pallet))
{
ExcelHelper.SetCellFormat(SheetUrv12.Range[SheetUrv12.Cells[row, colPaste], SheetUrv12.Cells[row, colPaste + 4]], pallet);
}
}
int col = address.ColCostTotalOffer - columnCellNumber + 1;
//РУБ. РФ
SheetUrv12.Cells[_rowStart, colPaste].Formula =
$"= VLOOKUP($B{_rowStart}, '{_project.AnalysisSheetName}'! {dataRange.Address}, {col}, FALSE)";
//% отклонения материалы
col = address.ColDeviationMaterials - columnCellNumber + 1;
SheetUrv12.Cells[_rowStart, colPaste + 1].Formula =
$"= VLOOKUP($B{_rowStart}, '{_project.AnalysisSheetName}'! {dataRange.Address}, {col}, FALSE)";
//% отклонения работы
col = address.ColDeviationWorks - columnCellNumber + 1;
SheetUrv12.Cells[_rowStart, colPaste + 2].Formula =
$"= VLOOKUP($B{_rowStart}, '{_project.AnalysisSheetName}'! {dataRange.Address}, {col}, FALSE)";
// % отклонения всего
string letterOutTotalDiff = ExcelHelper.GetColumnLetter(SheetUrv12.Cells[_rowStart, colPaste]);
SheetUrv12.Cells[_rowStart, colPaste + 3].Formula = $"=${letterOutTotalDiff}{_rowStart}/$D{_rowStart}-1";
//КОММЕНТАРИИ К СТОИМОСТИ
col = address.ColComments - columnCellNumber + 1;
SheetUrv12.Cells[_rowStart, colPaste + 4].Formula =
$"= VLOOKUP($B{_rowStart}, '{_project.AnalysisSheetName}'! {dataRange.Address}, {col}, FALSE)";
Excel.Range rng = SheetUrv12.Range[SheetUrv12.Cells[_rowStart, colPaste], SheetUrv12.Cells[_rowStart, colPaste + 4]];
Excel.Range destination = SheetUrv12.Range[SheetUrv12.Cells[_rowStart, colPaste], SheetUrv12.Cells[lastRow, colPaste + 4]];
rng.AutoFill(destination, Excel.XlAutoFillType.xlFillValues);
destination.Columns[2].NumberFormat = "0%";
destination.Columns[3].NumberFormat = "0%";
destination.Columns[4].NumberFormat = "0%";
if (!string.IsNullOrEmpty(formulaSumm))
{
SheetUrv12.Cells[rowBottomTotal, colPaste].Formula = formulaSumm;
SheetUrv12.Cells[rowBottomTotal + 1, colPaste].Formula =
$"={SheetUrv12.Cells[rowBottomTotal, colPaste].Address}*0.2";
SheetUrv12.Cells[rowBottomTotal + 2, colPaste].Formula =
$"={SheetUrv12.Cells[rowBottomTotal, colPaste].Address}+" +
$"{SheetUrv12.Cells[rowBottomTotal + 1, colPaste].Address}";
}
}
pb.Writeline("Условное форматирование");
SetConditionFormat12();
pb.MainBarTick("Формулы итогов");
TotalFormuls12();
pb.MainBarTick("Формат ячеек");
SetNumberFormat12(addresses.Count);
pb.MainBarTick("Общие комментарии");
new CommonComments(projectWorkbook).SetInfo();
//TODO загрузить наиболее дорогии позиции
}