/// <summary>
/// Перекрашивает таблицу, используя палитру
/// </summary>
/// <remarks>В палитре Range, что бы копировать весь стиль оформления установленный пользователем на листе палитры</remarks>
/// <param name="ws">Лист в котором будет произведена закраска</param>
/// <param name="pallet">Палитра</param>
public static void Repaint(Excel.Worksheet ws, Dictionary <string, Excel.Range> pallets, IProgressBarWithLogUI pb)
{
Excel.Application application = ws.Application;
pb.SetSubBarVolume(ws.UsedRange.Rows.Count);
foreach (Excel.Range row in ws.UsedRange.Rows)
{
if (pb.IsAborted)
{
break;
}
pb.SubBarTick();
if (pallets.TryGetValue(row.Cells[1, 1].Text, out Excel.Range pallet))
{
pallet.Copy();
row.PasteSpecial(Excel.XlPasteType.xlPasteFormats, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
}
}
application.CutCopyMode = (Excel.XlCutCopyMode) 0;
}
/// Добавить список
/// Добавить столбцы КП
/// Проставить формулы
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>
/// <param name="offer"></param>
internal void Print(IProgressBarWithLogUI pb, string offerSettingsName)
{
// Ищем настройки столбцов
OfferSettings offerSettings = _offerManager.Mappings.Find(s => s.Name == offerSettingsName);
pb.Writeline($"Выбор листа {offerSettings.SheetName}");
// Лист данных КП
Excel.Worksheet offerSheet = ExcelHelper.GetSheet(_offerBook, offerSettings.SheetName);
pb.Writeline("Разгруппировка строк");
ShowSheetRows(offerSheet);
ListAnalysis SheetAnalysis = new ListAnalysis(_sheetProject, _CurrentProject);
pb.Writeline("Адресация полей");
/// Адресация полей КП
List <FieldAddress> addresslist = GetFields(offerSettings, SheetAnalysis.ColumnStartPrint);
Excel.Worksheet tamplateSheet = ExcelHelper.GetSheet(_wb, "Шаблоны");
pb.Writeline("Печать заголовков");
SheetAnalysis.PrintTitle(tamplateSheet, addresslist);
int lastRowOffer = offerSheet.UsedRange.Row + offerSheet.UsedRange.Rows.Count - 1;
pb.Writeline("Чтение массива данных");
// Массив загружаемых данных
object[,] arrData = GetArrData(offerSheet, offerSettings.RowStart, lastRowOffer);
int countRows = lastRowOffer - offerSettings.RowStart + 1;
pb.SetSubBarVolume(countRows);
pb.Writeline("Вывод строк");
for (int i = 1; i <= countRows; i++)
{
pb.SubBarTick();
if (pb.IsAborted)
{
throw new AddInException("Процесс остановлен.");
}
Record offerRecord = new Record
{
Addresslist = addresslist
};
// Сбор данных
foreach (FieldAddress field in addresslist)
{
object val = arrData[i, field.ColumnOffer];
string text = val?.ToString() ?? "";
offerRecord.Values.Add(field.ColumnPaste, val);
if (field.MappingAnalysis.Name == Project.ColumnsNames[StaticColumns.Level])
{
offerRecord.Level = int.TryParse(text, out int lvl) ? lvl : 0;
}
if (field.MappingAnalysis.Name == Project.ColumnsNames[StaticColumns.Number])
{
offerRecord.Number = text;
}
if (field.MappingAnalysis.Check)
{
offerRecord.KeyFilds.Add(text);
}
}
SheetAnalysis.PrintRecord(offerRecord);
}
pb.Writeline("Группировка столбцов");
SheetAnalysis.GroupColumn();
if (pb.IsAborted)
{
throw new AddInException("Процесс остановлен.");
}
pb.Writeline("Формулы \"Комментарии Спектрум к заявке участника\"");
}
/// <summary>
/// Печать КП
/// </summary>
/// <param name="offer"></param>
internal void PrintBaseEstimate(IProgressBarWithLogUI pb, string offerSettingsName)
{
OfferSettings offerSettings = _offerManager.Mappings.Find(s => s.Name == offerSettingsName);
Excel.Worksheet offerSheet = ExcelHelper.GetSheet(_offerBook, offerSettings.SheetName);
ShowSheetRows(offerSheet);
_sheetProject = ExcelHelper.GetSheet(_wb, _CurrentProject.AnalysisSheetName);
/// Столбец "номер п.п."
OfferColumnMapping colNumber = offerSettings.Columns.Find(x => x.Name == Project.ColumnsNames[StaticColumns.Number]);
int lastRow = GetLastRow(offerSheet, colNumber.ColumnSymbol);
int countRows = lastRow - offerSettings.RowStart + 1;
pb.SetSubBarVolume(countRows);///100-1
List <(int, int)> colPair = new List <(int projectCollumn, int offerColumn)>();
int rightColumn = 10;
foreach (OfferColumnMapping col in offerSettings.Columns)
{
if (string.IsNullOrEmpty(col.ColumnSymbol))
{
continue;
}
ColumnMapping projectColumn = _CurrentProject.Columns.Find(a => a.Name == col.Name);
if (!string.IsNullOrWhiteSpace(projectColumn?.ColumnSymbol ?? ""))
{
int cnP = ExcelHelper.GetColumn(projectColumn.ColumnSymbol, _sheetProject);
int cnO = ExcelHelper.GetColumn(col.ColumnSymbol, _sheetProject);
colPair.Add((cnP, cnO));
if (rightColumn < cnO)
{
rightColumn = cnO;
}
}
}
Excel.Range RngData = offerSheet.Range[offerSheet.Cells[offerSettings.RowStart, 1], offerSheet.Cells[lastRow, rightColumn]];
object[,] arrData = RngData.Value;
for (int i = 1; i <= countRows; i++)
{
int rowPaste = _CurrentProject.RowStart + i - 1;
pb.SubBarTick();
if (pb.IsAborted)
{
return;
}
foreach ((int projectCollumn, int offerColumn) in colPair)
{
object val = arrData[i, offerColumn];
string text = val?.ToString() ?? "";
Excel.Range cellPrint = _sheetProject.Cells[rowPaste, projectCollumn];
if (double.TryParse(text, out double number))
{
cellPrint.Value = Math.Round(number, 2);
}
else if (!string.IsNullOrEmpty(text))
{
cellPrint.Value = text;
}
}
}
pb.ClearSubBar();
}