/// <summary>
/// Создание страницы с информацией об объектах (слепок редактора).
/// </summary>
/// <param name="workSheet"></param>
/// <param name="app"></param>
private static void CreateObjectsPageWithoutActions(
ref Workbook workbook)
{
string sheetName = "Технологические объекты";
Worksheet workSheet = workbook.Worksheets.Add(sheetName);
const int widthColumnA = 40;
const int widthColumnC = 55;
const int widthColumnE = 45;
TreeView tree = ExcelDataCollector
.SaveObjectsWithoutActionsAsTree();
int row = 1;
WriteTreeNode(ref workSheet, tree.Nodes, ref row, true);
workSheet.Range.Style.Font.FontName = "Calibri";
workSheet.Range.Style.Font.Size = 11;
workSheet.Range.EntireColumn.AutoFitColumns();
workSheet.PageSetup.IsSummaryRowBelow = false;
workSheet.PageSetup.IsSummaryColumnRight = true;
workSheet.Range[$"A1:A{row}"].ColumnWidth = widthColumnA;
workSheet.Range[$"C1:C{row}"].ColumnWidth = widthColumnC;
workSheet.Range[$"E1:E{row}"].ColumnWidth = widthColumnE;
workSheet.Range.EntireRow.AutoFitRows();
}
/// <summary>
/// Создание страницы с описанием устройств
/// </summary>
private static void CreateInformDevicePage(ref Workbook workBook)
{
string sheetName = "Техустройства";
Worksheet workSheet = workBook.Worksheets.Add(sheetName);
var deviceHeader = new string[]
{
"Название",
"Описание",
"Тип",
"Подтип"
};
workSheet.InsertArray(deviceHeader, 1, 1, false);
object[,] res = ExcelDataCollector.SaveDevicesInformationAsArray();
string endPos = "Q" + (res.GetLength(0) + 1);
workSheet.InsertArray(res, 2, 1);
// Форматирование по ширине содержимого.
foreach (var row in workSheet.Range.Rows)
{
var notNullCells = row.CellList.Where(x => x.Text != null);
var multilineCells = notNullCells.Where(x => x.Text.Contains("\n"));
if (multilineCells.Count() > 0)
{
row.IsWrapText = true;
}
}
workSheet.Range.Style.Font.FontName = "Calibri";
workSheet.Range.Style.Font.Size = 11;
workSheet.Range.AutoFitColumns();
workSheet.Range.EntireRow.AutoFitRows();
}
/// <summary>
/// Создание страницы с изделиями устройств
/// </summary>
/// <param name="workBook"></param>
private static void CreateDeviceArticlesPage(ref Workbook workBook)
{
string sheetName = "Изделия устройств";
Worksheet workSheet = workBook.Worksheets.Add(sheetName);
object[,] devicesWithArticles = ExcelDataCollector
.SaveDevicesArticlesInfoAsArray();
workSheet.InsertArray(devicesWithArticles, 1, 1);
workSheet.Range.Style.Font.FontName = "Calibri";
workSheet.Range.Style.Font.Size = 11;
workSheet.Range.EntireColumn.AutoFitColumns();
workSheet.Range.EntireRow.AutoFitRows();
}
/// <summary>
/// Создание страницы с итоговыми данными по устройствам
/// </summary>
private static void CreateTotalDevicePage(ref Workbook workBook)
{
string sheetName = "Сводная таблица устройств";
Worksheet workSheet = workBook.Worksheets.Add(sheetName);
object[,] res = ExcelDataCollector.SaveDevicesSummaryAsArray();
string endPos = "Q" + res.GetLength(0);
workSheet.InsertArray(res, 1, 1);
workSheet.Range.Style.Font.FontName = "Calibri";
workSheet.Range.Style.Font.Size = 11;
workSheet.Range.EntireColumn.AutoFitColumns();
workSheet.Range.EntireRow.AutoFitRows();
}
/// <summary>
/// Создание страницы с устройствами для операций и шагов техобъектов
/// </summary>
private static void CreateObjectDevicesPage(ref Workbook workBook)
{
string sheetName = "Операции и устройства";
Worksheet workSheet = workBook.Worksheets.Add(sheetName);
CellRange excelCells = workSheet.Range["A1:C1"];
// Производим объединение
excelCells.Merge();
excelCells.Value = "Технологические объекты";
var header = new string[]
{
"Вкл.устройства",
"Выкл. устройства",
"Верхние седла",
"Нижние седла",
"Сигналы для включения",
"Устройства (DI)",
"Устройства (DO)",
"Устройства",
"Группы DI-->DO"
};
workSheet.InsertArray(header, 1, 4, false);
workSheet.Range["A1:L1"].EntireColumn.AutoFitColumns();
//Заполнение страницы данными
TreeView tree = ExcelDataCollector
.SaveTechObjectOperationsAndActionsAsTree();
int row = 2;
WriteTreeNode(ref workSheet, tree.Nodes, ref row);
//Форматирование страницы
workSheet.Range.Style.Font.FontName = "Calibri";
workSheet.Range.Style.Font.Size = 11;
workSheet.FreezePanes(2, 1);
row = workSheet.Range.Rows.Length;
workSheet.Range[$"A1:C{row}"].EntireColumn.AutoFitColumns();
// установка переноса текста в ячейке.
workSheet.Range.IsWrapText = true;
workSheet.PageSetup.IsSummaryColumnRight = true;
workSheet.PageSetup.IsSummaryRowBelow = false;
workSheet.Range.EntireRow.AutoFitRows();
}
/// <summary>
/// Создание страницы с итоговыми данными по устройствам
/// </summary>
private static void CreateDeviceConnectionPage(ref Workbook workBook)
{
string sheetName = "Подключение устройств";
Worksheet workSheet = workBook.Worksheets.Add(sheetName);
TreeView tree = ExcelDataCollector.SaveDeviceConnectionAsTree();
int row = 1;
WriteTreeNode(ref workSheet, tree.Nodes, ref row);
workSheet.Range.Style.Font.FontName = "Calibri";
workSheet.Range.Style.Font.Size = 11;
workSheet.Range.EntireColumn.AutoFitColumns();
workSheet.Range.EntireColumn.IsWrapText = true;
workSheet.PageSetup.IsSummaryRowBelow = false;
workSheet.PageSetup.IsSummaryColumnRight = true;
workSheet.Range.EntireRow.AutoFitRows();
}
/// <summary>
/// Создание страницы с параметрами техобъектов проекта
/// </summary>
private static void CreateObjectParamsPage(ref Workbook workBook)
{
string sheetName = "Параметры объектов";
Worksheet workSheet = workBook.Worksheets.Add(sheetName);
// Настройка имен столбцов.
workSheet.Range["A1:A1"].Text = "Технологический объект";
CellRange excelCells = workSheet.Range["B1:C1"];
excelCells.Merge();
excelCells.Value = "Параметры";
var paramsHeader = new string[]
{
"Значение",
"Размерность",
"Операция",
"Lua имя"
};
workSheet.InsertArray(paramsHeader, 1, 4, false);
// Получить и записать данные
TreeView tree = ExcelDataCollector.SaveParamsAsTree();
int row = 2;
WriteTreeNode(ref workSheet, tree.Nodes, ref row);
// Форматирование страницы.
workSheet.FreezePanes(2, 1);
workSheet.Range.Style.Font.FontName = "Calibri";
workSheet.Range.Style.Font.Size = 11;
row = workSheet.Range.Rows.Length;
workSheet.Range[$"A1:G{row}"].EntireColumn.AutoFitColumns();
workSheet.PageSetup.IsSummaryColumnRight = true;
workSheet.PageSetup.IsSummaryRowBelow = false;
workSheet.Range.EntireRow.AutoFitRows();
}
/// <summary>
/// Создание страницы с модулями IO
/// </summary>
private static void CreateModulesPage(string prjName,
ref Workbook workBook)
{
string sheetName = "Модули ввода-вывода";
Worksheet workSheet = workBook.Worksheets.Add(sheetName);
var modulesCount = new Dictionary <string, int>();
var modulesColor = new Dictionary <string, System.Drawing.Color>();
var asInterfaceConnection = new Dictionary <string, object[, ]>();
object[,] res = ExcelDataCollector.SaveIOAsConnectionArray(prjName,
modulesCount, modulesColor, asInterfaceConnection);
workSheet.InsertArray(res, 1, 1);
int finalRows = res.GetLength(0) + 2;
//Форматирование страницы
workSheet.Range.BorderInside(LineStyleType.Thin);
workSheet.Range.BorderAround(LineStyleType.Medium);
workSheet.Range.Style.VerticalAlignment = VerticalAlignType
.Center;
workSheet.Range.Style.Font.FontName = "Calibri";
workSheet.Range.Style.Font.Size = 11;
workSheet.Range.IsWrapText = false;
CellRange rangeCurrent = workSheet.Range["A1:A1"];
CellRange rangeStart = rangeCurrent;
int totalCountRows = workSheet.Range.Rows.Length;
int i = 1;
string arr2 = rangeCurrent.Text as string;
do
{
int startColumn = rangeCurrent.Column;
int startRow = rangeCurrent.Row;
rangeCurrent = workSheet.Range[startRow + 1, startColumn,
startRow + 1, startColumn];
string arr1 = rangeStart.Value as string;
arr2 = rangeCurrent.Value as string;
if (arr1 != arr2)
{
workSheet.Range[rangeStart.Row, rangeStart.Column,
rangeCurrent.Row - 1, rangeCurrent.Column].Merge();
CellRange moduleNameRange = workSheet.Range[rangeStart.Row,
rangeStart.Column + 1, rangeStart.Row,
rangeStart.Column + 1];
string moduleName = moduleNameRange.Value as string;
if (modulesColor.ContainsKey(moduleName))
{
moduleNameRange.Style.Color = modulesColor[moduleName];
}
if (Int32.TryParse(arr1, out _))
{
workSheet.Range[rangeStart.Row, rangeStart.Column + 1,
rangeCurrent.Row - 1, rangeCurrent.Column + 1]
.Merge();
workSheet.Range[rangeStart.Row, rangeStart.Column,
rangeCurrent.Row - 1, rangeCurrent.Column + 5]
.BorderAround(LineStyleType.Thick);
}
else
{
workSheet.Range[rangeStart.Row, rangeStart.Column,
rangeCurrent.Row - 1, rangeCurrent.Column + 5]
.Borders.LineStyle = LineStyleType.None;
}
rangeStart = rangeCurrent;
}
i++;
}while (i <= totalCountRows);
// Форматирование по ширине содержимого.
workSheet.Range.EntireColumn.AutoFitColumns();
workSheet.Range.EntireColumn.IsWrapText = true;
CellRange column = workSheet.Range[$"B2:B{finalRows}"];
column.Style.Rotation = 90;
// 6.43 - 50 пикселей
column.ColumnWidth = 6.43;
column.HorizontalAlignment = HorizontalAlignType.Center;
column = workSheet.Range[$"A2:A{finalRows}"];
//26.43 - 190 пикселей
column.ColumnWidth = 26.43;
column.HorizontalAlignment = HorizontalAlignType.Center;
column = workSheet.Range[$"C2:C{finalRows}"];
// 2.14 - 20 пикселей
column.ColumnWidth = 6.43;
column.HorizontalAlignment = HorizontalAlignType.Center;
int totalStart = totalCountRows + 3;
int totalEnd = totalStart;
int idx = 0;
int total = 0;
//Выделение отдельным блоком модулей AS-interface
if (asInterfaceConnection.Count != 0)
{
var ASInterface = new object[
asInterfaceConnection.Count * 130, 4];
idx = 0;
ASInterface[idx, 0] = "AS-interface/IO-Link";
idx++;
foreach (string key in asInterfaceConnection.Keys)
{
ASInterface[idx, 0] = key;
idx++;
int startColumn = 2;
object[,] connections = asInterfaceConnection[key];
int rows = connections.GetLength(0);
int cols = connections.GetLength(1);
for (int ii = 0; ii < rows; ii++)
{
bool notNull = true;
for (int jj = 0; jj < cols; jj++)
{
if (connections[ii, jj] != null)
{
ASInterface[idx, startColumn + jj] =
connections[ii, jj];
}
else
{
notNull = false;
}
}
if (notNull)
{
idx++;
}
}
}
totalEnd = totalStart + idx;
workSheet.InsertArray(ASInterface, totalStart, 1);
column = workSheet.Range[$"A{totalStart}:A{totalEnd}"];
column.HorizontalAlignment = HorizontalAlignType.Center;
column = workSheet.Range[$"C{totalStart}:C{totalEnd}"];
column.HorizontalAlignment = HorizontalAlignType.Center;
totalStart = totalEnd + 2;
}
//Создание сводной таблицы используемых модулей на основании словаря
object[,] modulesTotal = new object[modulesCount.Count + 1, 2];
idx = 0;
total = 0;
// Заполнение таблицы
foreach (string key in modulesCount.Keys)
{
modulesTotal[idx, 0] = key;
modulesTotal[idx, 1] = modulesCount[key];
total += modulesCount[key];
idx++;
}
modulesTotal[idx, 0] = "Всего:";
modulesTotal[idx, 1] = total;
//Форматирование таблицы
totalEnd = totalStart + modulesCount.Count;
rangeCurrent = workSheet.Range[$"A{totalStart}:B{totalEnd}"];
workSheet.InsertArray(modulesTotal, totalStart, 1);
rangeCurrent.Style.Rotation = 0;
rangeCurrent.VerticalAlignment = VerticalAlignType.Center;
rangeCurrent.HorizontalAlignment = HorizontalAlignType.Right;
rangeCurrent.BorderInside(LineStyleType.Thin);
rangeCurrent.BorderAround(LineStyleType.Medium);
rangeCurrent = workSheet.Range[$"A{totalStart}:A{totalStart}"];
rangeStart = rangeCurrent;
workSheet.Range.EntireRow.AutoFitRows();
// Окрас ячеек
i = totalStart;
arr2 = rangeCurrent.Text as string;
do
{
int startColumn = rangeCurrent.Column;
int startRow = rangeCurrent.Row;
rangeCurrent = workSheet.Range[startRow + 1, startColumn,
startRow + 1, startColumn];
string arr1 = rangeStart.Value as string;
arr2 = rangeCurrent.Value as string;
if (arr1 != arr2)
{
CellRange moduleNameRange = rangeStart;
string moduleName = moduleNameRange.Value as string;
if (modulesColor.ContainsKey(moduleName))
{
moduleNameRange.Style.Color = modulesColor[moduleName];
}
rangeStart = rangeCurrent;
}
i++;
}while (i <= totalEnd);
rangeCurrent = null;
rangeStart = null;
}
