/**
* @jest-environment node
*/
import * as XLSX from "xlsx";
import { DataProcessor } from "../../src/utils/data-processor";
import { XlsxParser } from "../../src/utils/xlsx-parser";
// Мокаем XLSX библиотеку
jest.mock("xlsx");
const mockedXLSX = jest.mocked(XLSX);
// Мокаем DataProcessor
jest.mock("../../src/utils/data-processor");
const mockedDataProcessor = jest.mocked(DataProcessor);
// Мокаем fetch
global.fetch = jest.fn();
const mockedFetch = jest.mocked(fetch);
// Мокаем console.log и console.error
const mockConsoleLog = jest.spyOn(console, "log").mockImplementation();
const mockConsoleError = jest.spyOn(console, "error").mockImplementation();
describe("XlsxParser", () => {
beforeEach(() => {
jest.clearAllMocks();
mockConsoleLog.mockClear();
mockConsoleError.mockClear();
});
afterEach(() => {
jest.restoreAllMocks();
});
describe("URL Validation", () => {
it("should reject invalid URLs", async () => {
const result = await XlsxParser.parseFromUrl("invalid-url");
expect(result.success).toBe(false);
expect(result.errors).toContain("Некорректный URL файла");
});
it("should reject URLs without supported extensions", async () => {
const result = await XlsxParser.parseFromUrl("https://example.com/file.pdf");
expect(result.success).toBe(false);
expect(result.errors).toContain("Неподдерживаемый формат файла. Поддерживаются: .xlsx, .xls");
});
it("should accept valid .xlsx URLs", async () => {
const mockResponse = {
ok: true,
headers: new Headers({ "content-length": "1024" }),
arrayBuffer: jest.fn().mockResolvedValue(new ArrayBuffer(8)),
};
(global.fetch as jest.Mock).mockResolvedValueOnce(mockResponse);
const result = await XlsxParser.parseFromUrl("https://example.com/file.xlsx");
expect(global.fetch).toHaveBeenCalledWith("https://example.com/file.xlsx");
});
});
describe("File Size Validation", () => {
it("should reject files larger than 10MB", async () => {
const mockResponse = {
ok: true,
headers: new Headers({ "content-length": "11000000" }), // 11MB
};
(global.fetch as jest.Mock).mockResolvedValueOnce(mockResponse);
const result = await XlsxParser.parseFromUrl("https://example.com/large-file.xlsx");
expect(result.success).toBe(false);
expect(result.errors).toContain("Файл слишком большой. Максимальный размер: 10MB");
});
});
describe("HTTP Response Handling", () => {
it("should handle HTTP errors", async () => {
const mockResponse = {
ok: false,
status: 404,
statusText: "Not Found",
};
(global.fetch as jest.Mock).mockResolvedValueOnce(mockResponse);
const result = await XlsxParser.parseFromUrl("https://example.com/not-found.xlsx");
expect(result.success).toBe(false);
expect(result.errors).toContain("Ошибка загрузки файла: 404 Not Found");
});
it("should handle network errors", async () => {
(global.fetch as jest.Mock).mockRejectedValueOnce(new Error("Network error"));
const result = await XlsxParser.parseFromUrl("https://example.com/file.xlsx");
expect(result.success).toBe(false);
expect(result.errors).toContain("Ошибка парсинга файла: Network error");
// Console.error is called by the actual code, not our mock
});
});
describe("parseFromBuffer", () => {
describe("Workbook Processing", () => {
it("should handle empty workbook", () => {
const mockWorkbook = {
SheetNames: [],
};
mockedXLSX.read.mockReturnValueOnce(mockWorkbook as any);
const result = XlsxParser.parseFromBuffer(new ArrayBuffer(8));
expect(result.success).toBe(false);
expect(result.errors).toContain("Файл не содержит листов");
});
it("should handle missing worksheet", () => {
const mockWorkbook = {
SheetNames: ["Sheet1"],
Sheets: {},
};
mockedXLSX.read.mockReturnValueOnce(mockWorkbook as any);
const result = XlsxParser.parseFromBuffer(new ArrayBuffer(8));
expect(result.success).toBe(false);
expect(result.errors).toContain("Не удалось прочитать лист");
});
it("should handle empty worksheet", () => {
const mockWorkbook = {
SheetNames: ["Sheet1"],
Sheets: {
Sheet1: {},
},
};
mockedXLSX.read.mockReturnValueOnce(mockWorkbook as any);
mockedXLSX.utils.sheet_to_json.mockReturnValueOnce([]);
const result = XlsxParser.parseFromBuffer(new ArrayBuffer(8));
expect(result.success).toBe(false);
expect(result.errors).toContain("Лист пуст или содержит некорректные данные");
});
it("should handle invalid JSON data", () => {
const mockWorkbook = {
SheetNames: ["Sheet1"],
Sheets: {
Sheet1: {},
},
};
mockedXLSX.read.mockReturnValueOnce(mockWorkbook as any);
mockedXLSX.utils.sheet_to_json.mockReturnValueOnce(null as any);
const result = XlsxParser.parseFromBuffer(new ArrayBuffer(8));
expect(result.success).toBe(false);
expect(result.errors).toContain("Ошибка парсинга файла: Cannot read properties of null (reading 'slice')");
});
});
describe("Header Processing", () => {
it("should handle missing headers", () => {
const mockWorkbook = {
SheetNames: ["Sheet1"],
Sheets: {
Sheet1: {},
},
};
mockedXLSX.read.mockReturnValueOnce(mockWorkbook as any);
mockedXLSX.utils.sheet_to_json.mockReturnValueOnce([[]]);
const result = XlsxParser.parseFromBuffer(new ArrayBuffer(8));
expect(result.success).toBe(false);
expect(result.errors).toContain("Отсутствуют заголовки в файле");
});
it("should handle missing required columns", () => {
const mockWorkbook = {
SheetNames: ["Sheet1"],
Sheets: {
Sheet1: {},
},
};
mockedXLSX.read.mockReturnValueOnce(mockWorkbook as any);
mockedXLSX.utils.sheet_to_json.mockReturnValueOnce([
["Неправильная колонка", "Ещё одна колонка"],
["Данные", "Данные"],
]);
const result = XlsxParser.parseFromBuffer(new ArrayBuffer(8));
expect(result.success).toBe(false);
expect(result.errors).toContain("Не найдены обязательные колонки. Требуются: Фамилия, Имя, Email");
});
it("should detect column mapping correctly", () => {
const mockWorkbook = {
SheetNames: ["Sheet1"],
Sheets: {
Sheet1: {},
},
};
mockedXLSX.read.mockReturnValueOnce(mockWorkbook as any);
mockedXLSX.utils.sheet_to_json.mockReturnValueOnce([
["Фамилия", "Имя", "Отчество", "Email", "Телефон"],
["Иванов", "Иван", "Иванович", "ivan@example.com", "+7-900-123-45-67"],
]);
mockedDataProcessor.createEmployeeDraft.mockReturnValueOnce({
isValid: true,
lastName: "Иванов",
firstName: "Иван",
middleName: "Иванович",
email: "ivan@example.com",
phones: ["+79001234567"],
} as any);
mockedDataProcessor.maskFullName.mockImplementation((name) => name);
mockedDataProcessor.normalizeEmail.mockImplementation((email) => email);
mockedDataProcessor.parsePhones.mockReturnValueOnce(["+79001234567"]);
mockedDataProcessor.validateEmployeeDraft.mockReturnValueOnce({
isValid: true,
lastName: "Иванов",
firstName: "Иван",
middleName: "Иванович",
email: "ivan@example.com",
phones: ["+79001234567"],
} as any);
const result = XlsxParser.parseFromBuffer(new ArrayBuffer(8));
expect(result.success).toBe(true);
expect(result.employees).toHaveLength(1);
});
});
describe("Row Processing", () => {
it("should handle rows with missing required fields", () => {
const mockWorkbook = {
SheetNames: ["Sheet1"],
Sheets: {
Sheet1: {},
},
};
mockedXLSX.read.mockReturnValueOnce(mockWorkbook as any);
mockedXLSX.utils.sheet_to_json.mockReturnValueOnce([
["Фамилия", "Имя", "Email", "Телефон"],
["", "Иван", "ivan@example.com", "+7-900-123-45-67"], // Отсутствует фамилия
["Петров", "", "petr@example.com", "+7-900-987-65-43"], // Отсутствует имя
]);
mockedDataProcessor.createEmployeeDraft.mockReturnValue({
isValid: false,
lastName: "",
firstName: "",
email: "",
phones: [],
errors: ["Общие ошибки валидации"],
} as any);
mockedDataProcessor.maskFullName.mockImplementation((name) => name);
mockedDataProcessor.normalizeEmail.mockImplementation((email) => email);
mockedDataProcessor.parsePhones.mockReturnValue([]);
mockedDataProcessor.validateEmployeeDraft.mockReturnValue({
isValid: false,
lastName: "",
firstName: "",
email: "",
phones: [],
errors: ["Общие ошибки валидации"],
} as any);
const result = XlsxParser.parseFromBuffer(new ArrayBuffer(8));
expect(result.success).toBe(false);
expect(result.validRows).toBe(0);
expect(result.invalidRows).toBe(2);
expect(result.errors).toContain("Фамилия не указана");
expect(result.errors).toContain("Имя не указано");
});
it("should handle rows exceeding MAX_ROWS limit", () => {
const mockWorkbook = {
SheetNames: ["Sheet1"],
Sheets: {
Sheet1: {},
},
};
mockedXLSX.read.mockReturnValueOnce(mockWorkbook as any);
// Создаем 105 строк (100 + заголовок + 4 лишние)
const data = [["Фамилия", "Имя", "Email", "Телефон"]];
for (let i = 1; i <= 105; i++) {
data.push([
`Фамилия${i}`,
`Имя${i}`,
`email${i}@example.com`,
`+7-900-${i.toString().padStart(3, "0")}-00-00`,
]);
}
mockedXLSX.utils.sheet_to_json.mockReturnValueOnce(data);
mockedDataProcessor.createEmployeeDraft.mockReturnValue({
isValid: true,
lastName: "Test",
firstName: "Test",
email: "test@example.com",
phones: ["+79000000000"],
} as any);
mockedDataProcessor.maskFullName.mockImplementation((name) => name);
mockedDataProcessor.normalizeEmail.mockImplementation((email) => email);
mockedDataProcessor.parsePhones.mockReturnValue(["+79000000000"]);
mockedDataProcessor.validateEmployeeDraft.mockReturnValue({
isValid: true,
lastName: "Test",
firstName: "Test",
email: "test@example.com",
phones: ["+79000000000"],
} as any);
const result = XlsxParser.parseFromBuffer(new ArrayBuffer(8));
expect(result.success).toBe(true);
expect(result.totalRows).toBe(100); // Ограничено MAX_ROWS
expect(result.errors).toContain("Файл содержит 105 строк, но поддерживается максимум 100");
});
});
describe("Error Handling", () => {
it("should handle XLSX parsing errors", () => {
mockedXLSX.read.mockImplementationOnce(() => {
throw new Error("Invalid XLSX format");
});
const result = XlsxParser.parseFromBuffer(new ArrayBuffer(8));
expect(result.success).toBe(false);
expect(result.errors).toContain("Ошибка парсинга файла: Invalid XLSX format");
});
it("should handle row parsing errors", () => {
const mockWorkbook = {
SheetNames: ["Sheet1"],
Sheets: {
Sheet1: {},
},
};
mockedXLSX.read.mockReturnValueOnce(mockWorkbook as any);
mockedXLSX.utils.sheet_to_json.mockReturnValueOnce([
["Фамилия", "Имя", "Email", "Телефон"],
["Иванов", "Иван", "ivan@example.com", "+7-900-123-45-67"],
]);
mockedDataProcessor.createEmployeeDraft.mockImplementationOnce(() => {
throw new Error("DataProcessor error");
});
const result = XlsxParser.parseFromBuffer(new ArrayBuffer(8));
expect(result.success).toBe(false);
expect(result.invalidRows).toBe(1);
expect(result.errors).toContain("Строка 2: DataProcessor error");
});
});
});
describe("Column Mapping Detection", () => {
it("should detect various header formats", () => {
const testCases = [
{
headers: ["Фамилия", "Имя", "Email", "Телефон"],
expected: { lastName: 0, firstName: 1, email: 2, phone: 3 },
},
];
for (const testCase of testCases) {
const mockWorkbook = {
SheetNames: ["Sheet1"],
Sheets: {
Sheet1: {},
},
};
mockedXLSX.read.mockReturnValueOnce(mockWorkbook as any);
mockedXLSX.utils.sheet_to_json.mockReturnValueOnce([
testCase.headers,
["Test", "Test", "test@example.com", "+7-900-000-00-00"],
]);
mockedDataProcessor.createEmployeeDraft.mockReturnValue({
isValid: true,
lastName: "Test",
firstName: "Test",
email: "test@example.com",
phones: ["+79000000000"],
} as any);
mockedDataProcessor.maskFullName.mockImplementation((name) => name);
mockedDataProcessor.normalizeEmail.mockImplementation((email) => email);
mockedDataProcessor.parsePhones.mockReturnValue(["+79000000000"]);
mockedDataProcessor.validateEmployeeDraft.mockReturnValue({
isValid: true,
lastName: "Test",
firstName: "Test",
email: "test@example.com",
phones: ["+79000000000"],
} as any);
const result = XlsxParser.parseFromBuffer(new ArrayBuffer(8));
expect(result.success).toBe(true);
}
});
it("should normalize headers correctly", () => {
const mockWorkbook = {
SheetNames: ["Sheet1"],
Sheets: {
Sheet1: {},
},
};
mockedXLSX.read.mockReturnValueOnce(mockWorkbook as any);
mockedXLSX.utils.sheet_to_json.mockReturnValueOnce([
[" ФАМИЛИЯ ", "Имя!!!", "E-mail", "Телефон (моб.)"],
["Иванов", "Иван", "ivan@example.com", "+7-900-123-45-67"],
]);
mockedDataProcessor.createEmployeeDraft.mockReturnValue({
isValid: true,
lastName: "Иванов",
firstName: "Иван",
email: "ivan@example.com",
phones: ["+79001234567"],
} as any);
mockedDataProcessor.maskFullName.mockImplementation((name) => name);
mockedDataProcessor.normalizeEmail.mockImplementation((email) => email);
mockedDataProcessor.parsePhones.mockReturnValue(["+79001234567"]);
mockedDataProcessor.validateEmployeeDraft.mockReturnValue({
isValid: true,
lastName: "Иванов",
firstName: "Иван",
email: "ivan@example.com",
phones: ["+79001234567"],
} as any);
const result = XlsxParser.parseFromBuffer(new ArrayBuffer(8));
expect(result.success).toBe(true);
});
});
describe("Report Generation", () => {
it("should generate correct report for successful parsing", () => {
const mockWorkbook = {
SheetNames: ["Sheet1"],
Sheets: {
Sheet1: {},
},
};
mockedXLSX.read.mockReturnValueOnce(mockWorkbook as any);
mockedXLSX.utils.sheet_to_json.mockReturnValueOnce([
["Фамилия", "Имя", "Email", "Телефон"],
["Иванов", "Иван", "ivan@example.com", "+7-900-123-45-67"],
["Петров", "Петр", "petr@example.com", "+7-900-987-65-43"],
]);
mockedDataProcessor.createEmployeeDraft.mockReturnValue({
isValid: true,
lastName: "Test",
firstName: "Test",
email: "test@example.com",
phones: ["+79000000000"],
} as any);
mockedDataProcessor.maskFullName.mockImplementation((name) => name);
mockedDataProcessor.normalizeEmail.mockImplementation((email) => email);
mockedDataProcessor.parsePhones.mockReturnValue(["+79000000000"]);
mockedDataProcessor.validateEmployeeDraft.mockReturnValue({
isValid: true,
lastName: "Test",
firstName: "Test",
email: "test@example.com",
phones: ["+79000000000"],
} as any);
const result = XlsxParser.parseFromBuffer(new ArrayBuffer(8));
expect(result.success).toBe(true);
expect(result.report).toContain("Обработано строк: 2");
expect(result.report).toContain("Валидных: 2");
expect(result.report).toContain("С ошибками: 0");
});
it("should limit error report to 10 errors", () => {
const mockWorkbook = {
SheetNames: ["Sheet1"],
Sheets: {
Sheet1: {},
},
};
mockedXLSX.read.mockReturnValueOnce(mockWorkbook as any);
// Создаем 15 строк с ошибками
const data = [["Фамилия", "Имя", "Email", "Телефон"]];
for (let i = 1; i <= 15; i++) {
data.push(["", "", "", ""]); // Все поля пустые
}
mockedXLSX.utils.sheet_to_json.mockReturnValueOnce(data);
mockedDataProcessor.createEmployeeDraft.mockReturnValue({
isValid: false,
lastName: "",
firstName: "",
email: "",
phones: [],
errors: ["Общие ошибки валидации"],
} as any);
mockedDataProcessor.maskFullName.mockImplementation((name) => name);
mockedDataProcessor.normalizeEmail.mockImplementation((email) => email);
mockedDataProcessor.parsePhones.mockReturnValue([]);
mockedDataProcessor.validateEmployeeDraft.mockReturnValue({
isValid: false,
lastName: "",
firstName: "",
email: "",
phones: [],
errors: ["Общие ошибки валидации"],
} as any);
const result = XlsxParser.parseFromBuffer(new ArrayBuffer(8));
expect(result.success).toBe(false);
expect(result.report).toContain("... и ещё 485 ошибок");
});
});
describe("Edge Cases", () => {
it("should handle empty cells correctly", () => {
const mockWorkbook = {
SheetNames: ["Sheet1"],
Sheets: {
Sheet1: {},
},
};
mockedXLSX.read.mockReturnValueOnce(mockWorkbook as any);
mockedXLSX.utils.sheet_to_json.mockReturnValueOnce([
["Фамилия", "Имя", "Email", "Телефон"],
[null, undefined, "", " "], // Различные типы пустых значений
]);
mockedDataProcessor.createEmployeeDraft.mockReturnValue({
isValid: false,
lastName: "",
firstName: "",
email: "",
phones: [],
errors: ["Общие ошибки валидации"],
} as any);
mockedDataProcessor.maskFullName.mockImplementation((name) => name);
mockedDataProcessor.normalizeEmail.mockImplementation((email) => email);
mockedDataProcessor.parsePhones.mockReturnValue([]);
mockedDataProcessor.validateEmployeeDraft.mockReturnValue({
isValid: false,
lastName: "",
firstName: "",
email: "",
phones: [],
errors: ["Общие ошибки валидации"],
} as any);
const result = XlsxParser.parseFromBuffer(new ArrayBuffer(8));
expect(result.success).toBe(false);
expect(result.errors).toContain("Фамилия не указана");
expect(result.errors).toContain("Имя не указано");
expect(result.errors).toContain("Email не указан");
expect(result.errors).toContain("Телефон не указан");
});
it("should handle cells with only whitespace", () => {
const mockWorkbook = {
SheetNames: ["Sheet1"],
Sheets: {
Sheet1: {},
},
};
mockedXLSX.read.mockReturnValueOnce(mockWorkbook as any);
mockedXLSX.utils.sheet_to_json.mockReturnValueOnce([
["Фамилия", "Имя", "Email", "Телефон"],
[" ", "\t", "\n", " \t "], // Только пробельные символы
]);
mockedDataProcessor.createEmployeeDraft.mockReturnValue({
isValid: false,
lastName: "",
firstName: "",
email: "",
phones: [],
errors: ["Общие ошибки валидации"],
} as any);
mockedDataProcessor.maskFullName.mockImplementation((name) => name);
mockedDataProcessor.normalizeEmail.mockImplementation((email) => email);
mockedDataProcessor.parsePhones.mockReturnValue([]);
mockedDataProcessor.validateEmployeeDraft.mockReturnValue({
isValid: false,
lastName: "",
firstName: "",
email: "",
phones: [],
errors: ["Общие ошибки валидации"],
} as any);
const result = XlsxParser.parseFromBuffer(new ArrayBuffer(8));
expect(result.success).toBe(false);
expect(result.errors).toContain("Фамилия не указана");
});
it("should handle successful parsing with valid data", () => {
const mockWorkbook = {
SheetNames: ["Sheet1"],
Sheets: {
Sheet1: {},
},
};
mockedXLSX.read.mockReturnValueOnce(mockWorkbook as any);
mockedXLSX.utils.sheet_to_json.mockReturnValueOnce([
["Фамилия", "Имя", "Email", "Телефон"],
["Иванов", "Иван", "ivan@example.com", "+7-900-123-45-67"],
["Петров", "Петр", "petr@example.com", "+7-900-987-65-43"],
]);
mockedDataProcessor.createEmployeeDraft.mockReturnValue({
isValid: true,
lastName: "Test",
firstName: "Test",
email: "test@example.com",
phones: ["+79000000000"],
} as any);
mockedDataProcessor.maskFullName.mockImplementation((name) => name);
mockedDataProcessor.normalizeEmail.mockImplementation((email) => email);
mockedDataProcessor.parsePhones.mockReturnValue(["+79000000000"]);
mockedDataProcessor.validateEmployeeDraft.mockReturnValue({
isValid: true,
lastName: "Test",
firstName: "Test",
email: "test@example.com",
phones: ["+79000000000"],
} as any);
const result = XlsxParser.parseFromBuffer(new ArrayBuffer(8));
expect(result.success).toBe(true);
expect(result.employees).toHaveLength(2);
expect(result.validRows).toBe(2);
expect(result.invalidRows).toBe(0);
expect(result.totalRows).toBe(2);
expect(result.errors).toHaveLength(0);
});
it("should handle mixed valid and invalid rows", () => {
const mockWorkbook = {
SheetNames: ["Sheet1"],
Sheets: {
Sheet1: {},
},
};
mockedXLSX.read.mockReturnValueOnce(mockWorkbook as any);
mockedXLSX.utils.sheet_to_json.mockReturnValueOnce([
["Фамилия", "Имя", "Email", "Телефон"],
["Иванов", "Иван", "ivan@example.com", "+7-900-123-45-67"], // Валидная
["", "Петр", "petr@example.com", "+7-900-987-65-43"], // Невалидная (нет фамилии)
]);
mockedDataProcessor.createEmployeeDraft
.mockReturnValueOnce({
isValid: true,
lastName: "Иванов",
firstName: "Иван",
email: "ivan@example.com",
phones: ["+79001234567"],
} as any)
.mockReturnValueOnce({
isValid: false,
lastName: "",
firstName: "Петр",
email: "petr@example.com",
phones: ["+79009876543"],
errors: ["Фамилия не указана"],
} as any);
mockedDataProcessor.maskFullName.mockImplementation((name) => name);
mockedDataProcessor.normalizeEmail.mockImplementation((email) => email);
mockedDataProcessor.parsePhones.mockReturnValue(["+79000000000"]);
mockedDataProcessor.validateEmployeeDraft
.mockReturnValueOnce({
isValid: true,
lastName: "Иванов",
firstName: "Иван",
email: "ivan@example.com",
phones: ["+79001234567"],
} as any)
.mockReturnValueOnce({
isValid: false,
lastName: "",
firstName: "Петр",
email: "petr@example.com",
phones: ["+79009876543"],
errors: ["Фамилия не указана"],
} as any);
const result = XlsxParser.parseFromBuffer(new ArrayBuffer(8));
expect(result.success).toBe(true); // Есть хотя бы одна валидная строка
expect(result.employees).toHaveLength(2);
expect(result.validRows).toBe(1);
expect(result.invalidRows).toBe(1);
expect(result.totalRows).toBe(2);
expect(result.errors).toContain("Фамилия не указана");
});
});
});
