р7макрос.рф

Выкладываю реализованный макрос с всероссийского чемпионата по макросам Р7 Офис. Общие функции

/**
* @fileoverview Локальная библиотека для работы с данными из внешнего источника.
* Содержит функции для загрузки, фильтрации, преобразования данных и взаимодействия с Google Таблицами или подобными редакторами.
*/

/**
* Получает доступ к листу таблицы с именем "Данные".
* @type {Sheet}
*/
let ws = Api.GetSheet("Данные");

/**
* Объект, содержащий локальные утилиты для работы с данными.
* @namespace Common.LocalLib
*/
Common.LocalLib = {};

/**
* Переменная для хранения массива данных, полученных из внешнего источника.
* @type {Array<Object>}
*/
let arrayData = [];

let resultArray = [];

let kpiArray = [];

Common.LocalLib.getResultArray = function () {
return resultArray;
};

Common.LocalLib.setResultArray = function (array) {
resultArray = [];
resultArray = [...array]; // поверхностная копия
};

Common.LocalLib.clearResultArray = function () {
resultArray = [];
};

Common.LocalLib.getKpiArray = function () {
return kpiArray;
};

Common.LocalLib.setKpiArray = function (array) {
kpiArray = [];
kpiArray = [...array]; // поверхностная копия
};

/**
* Возвращает текущий массив данных.
*
* @returns {Array<Object>} Массив объектов с данными о событиях.
*/
Common.LocalLib.getData = function () {
return arrayData;
};

/**
* Асинхронно загружает данные по указанному URL и сохраняет их во внутренний массив `arrayData`.
*
* @param {string} url - Ссылка на CSV-файл (через HTTP/HTTPS или file://).
* @returns {Promise<void>}
*/
Common.LocalLib.getAllDataFromUrl = async function (url) {
try {
const response = await fetch(url);
const csvText = await response.text();
const lines = csvText.trim().split("\n");

/**
* Конструктор данных события.
* @constructor
* @param {string} eventTime - Дата и время события.
* @param {string} eventType - Тип события: view, cart, purchase.
* @param {string} productId - Идентификатор продукта.
* @param {string} categoryCode - Код категории (разделён точками).
* @param {string} brand - Бренд продукта.
* @param {string} price - Цена товара.
* @param {string} userId - Идентификатор пользователя.
* @param {string} userSession - Идентификатор пользовательской сессии.
*/
function Data(eventTime, eventType, productId, categoryCode, brand, price, userId, userSession) {
this.eventTime = eventTime;
this.eventType = eventType;
this.productId = productId;
this.categoryCode = categoryCode;
this.brand = brand;
this.price = price;
this.userId = userId;
this.userSession = userSession;
}

for (let row = 1; row < lines.length; row++) {
let values = lines[row].split(";");
let data = new Data(
values[0], values[1], values[2], values[3],
values[4], values[5], values[6], values[7]
);
arrayData.push(data);
}

// Записываем количество строк в ячейку C3
ws.GetRange("C3").SetValue(arrayData.length);
Asc.editor.controller.view.resize();
Common.FilterLib.createFilter(arrayData);

// Уведомление об успешной загрузке
Common.UI.alert({
title: "Обновление данных",
msg: "Успешно загружено " + arrayData.length + " записей. Нажмите \"ОК\" чтобы продолжить",
buttons: [
{ caption: "OK", value: "yes" }
]
});

} catch (error) {
console.error('Ошибка загрузки данных:', error);

// Очищаем счётчик при ошибке
ws.GetRange("C3").SetValue(0);
Asc.editor.controller.view.resize();

// Уведомление об ошибке
Common.UI.alert({
title: "Ошибка обновления данных",
msg: "Данные не загружены! Проверьте правильность написания ссылки на внешний файл. Нажмите \"ОК\" чтобы продолжить",
buttons: [
{ caption: "OK", value: "yes" }
]
});
}
};

/**
* Преобразует массив объектов в двумерный массив значений.
*
* @param {Array<Object>} array - Массив объектов.
* @returns {Array<Array<string>>} Двумерный массив строковых значений.
*/
Common.LocalLib.convertToOneDimensionalArray = function (array) {
return array.map(item => [
formatStrDate(item.eventTime.trim()),
item.eventType.trim(),
item.productId.trim(),
item.categoryCode.trim(),
item.brand.trim(),
item.price.trim(),
item.userId.trim(),
item.userSession.trim()
]);
};

/**
* Фильтрует массив данных по заданным критериям.
*
* @param {string|null|undefined} eventType - Тип события для фильтрации.
* @param {string|null|undefined} productId - ID продукта для фильтрации.
* @param {string|null|undefined} categoryCode - Код категории (по префиксу).
* @param {string|null|undefined} brand - Бренд для фильтрации.
* @param {string|Date|null|undefined} minEventTime - Минимальная дата события.
* @param {string|Date|null|undefined} maxEventTime - Максимальная дата события.
* @param {number|string|null|undefined} minPrice - Минимальная цена.
* @param {number|string|null|undefined} maxPrice - Максимальная цена.
* @returns {Array<Object>} Отфильтрованный массив объектов.
*/
Common.LocalLib.filterArrayData = function (eventType, productId, categoryCode, brand, minEventTime, maxEventTime, minPrice, maxPrice) {
return arrayData.filter(item => {
const matchEventType = eventType === null || item.eventType === eventType;
const matchProductId = productId === null || item.productId === productId;
const matchBrand = brand === null || item.brand === brand;

let matchCategory = true;
if (categoryCode !== null && categoryCode !== undefined) {
if (categoryCode === '') {
matchCategory = item.categoryCode === '';
} else {
matchCategory = typeof item.categoryCode === 'string' && item.categoryCode.startsWith(categoryCode);
}
}

let matchEventTime = true;

const itemEventTime = item.eventTime ? new Date(item.eventTime) : null;
const parsedMin = minEventTime ? new Date(minEventTime) : null;
const parsedMax = maxEventTime ? new Date(maxEventTime) : null;

// Проверяем, что дата itemEventTime корректна
if (itemEventTime && !isNaN(itemEventTime.getTime())) {
// Если заданы обе границы — объединяем условия
if (parsedMin && parsedMax) {
matchEventTime = itemEventTime >= parsedMin && itemEventTime <= parsedMax;
} else if (parsedMin) {
matchEventTime = itemEventTime >= parsedMin;
} else if (parsedMax) {
matchEventTime = itemEventTime <= parsedMax;
}
} else {
// Если у элемента нет даты, и фильтр требует диапазон — он не подходит
matchEventTime = !(parsedMin || parsedMax);
}

let matchPrice = true;

const itemPrice = parseFloat(item.price.replace(',', '.') || NaN);

// Проверяем, что itemPrice — корректное число
if (!isNaN(itemPrice)) {
// Парсим min и max, если они заданы
const parsedMin = minPrice !== null ? parseFloat(minPrice.replace(',', '.') || NaN) : NaN;
const parsedMax = maxPrice !== null ? parseFloat(maxPrice.replace(',', '.') || NaN) : NaN;

// Проверяем, что min и max — валидные числа
const isValidMin = !isNaN(parsedMin);
const isValidMax = !isNaN(parsedMax);

// Основное условие: проверка диапазона
if (isValidMin && isValidMax) {
matchPrice = itemPrice >= parsedMin && itemPrice <= parsedMax;
} else if (isValidMin) {
matchPrice = itemPrice >= parsedMin;
} else if (isValidMax) {
matchPrice = itemPrice <= parsedMax;
} else {
// Если min и max не заданы или невалидны → всё подходит
matchPrice = true;
}
} else {
// Если у элемента некорректная цена → не проходит, если есть фильтры
matchPrice = !(minPrice !== null || maxPrice !== null);
}

return matchEventType && matchProductId && matchCategory && matchBrand && matchEventTime && matchPrice;
});
};

/**
* Возвращает номер последней заполненной строки в используемом диапазоне листа.
*
* @param {Sheet} oSheet - Лист.
* @returns {number} Номер последней заполненной строки.
*/
Common.LocalLib.getLastFilledRowFromRange = function (oSheet) {
let range = oSheet.UsedRange.Address;
const match = range.match(/\$(\d+)$/);
return match ? parseInt(match[1], 10) : 0;
};

/**
* Возвращает номер последней заполненной строки в указанном столбце.
*
* @param {Sheet} oSheet - Лист.
* @param {number} oCol - Номер столбца (начиная с 0 или 1 — зависит от API).
* @param {number} oMaxRow - Максимальный номер строки для проверки.
* @returns {number} Номер последней заполненной строки.
*/
Common.LocalLib.getLastFilledRow = function (oSheet, oCol, oMaxRow) {
for (let row = oMaxRow; row >= 1; row--) {
let cellValue = oSheet.GetCells(row, oCol).GetValue();
if (cellValue !== "") {
return row;
}
}
return 0;
};

/**
* Устанавливает границы для указанного диапазона ячеек.
*
* @param {Range} oRange - Диапазон ячеек.
* @param {boolean} isInside - Флаг, указывающий, нужно ли добавлять внутренние границы.
* @param {Color} [oColor=black] - Цвет границы.
*/
Common.LocalLib.setBorders = function (oRange, isInside, oColor = Api.CreateColorFromRGB(0, 0, 0)) {
oRange.SetBorders("Bottom", "Thin", oColor);
oRange.SetBorders("Top", "Thin", oColor);
oRange.SetBorders("Left", "Thin", oColor);
oRange.SetBorders("Right", "Thin", oColor);
if (isInside) {
oRange.SetBorders("InsideHorizontal", "Thin", oColor);
oRange.SetBorders("InsideVertical", "Thin", oColor);
}
};

/**
* Форматирует дату в зависимости от типа входного значения.
*
* @param {string|Date} input - Входное значение: строка с датой или объект Date.
* @returns {string} Дата в формате DD.MM.YYYY.
*/
Common.LocalLib.formatDate = function(input) {
if (typeof input === 'string') {
return formatStrDate(input);
} else if (input instanceof Date) {
return formatDateToDDMMYYYY(input);
} else {
console.warn("Некорректный тип входных данных для форматирования даты:", input);
return "";
}
};

/**
* Локальная функция форматирования даты.
* @private
*/
function formatStrDate(str) {
const match = str.match(/(\d{4})-(\d{2})-(\d{2})/);
if (match) {
const year = match[1];
const month = match[2];
const day = match[3];
return `${day}.${month}.${year}`;
}
return "";
}

/**
* Преобразует объект Date в строку формата DD.MM.YYYY.
*
* @param {Date} date - Объект даты.
* @returns {string} Дата в формате DD.MM.YYYY.
*/
function formatDateToDDMMYYYY(date) {
const day = String(date.getDate()).padStart(2, '0'); // День месяца (2 цифры)
const month = String(date.getMonth() + 1).padStart(2, '0'); // Месяц (2 цифры, начиная с 0)
const year = date.getFullYear(); // Год

return `${day}.${month}.${year}`;
}