При разработке решений на платформе 1С:Предприятие, особенно в окружении AltLinux, часто возникает задача программного заполнения файлов Excel. Изначальный вопрос на форуме "Чем ковырять братцы?" отражает общую потребность в надежных и гибких инструментах для работы с электронными таблицами. Мы рассмотрим несколько подходов, начиная от встроенных средств 1С и заканчивая мощными внешними библиотеками, которые позволяют достичь высокой точности и детализации при работе с файлами Excel.
Автор темы упоминает использование "обычного mxl макета" как быстрое, но "неспортивное" решение. Сообщение 5 предлагает "сохранение макета" как "самое простое". Действительно, платформа 1С предоставляет объект ТабличныйДокумент, который является кроссплатформенным и может быть использован для формирования данных и их последующего сохранения в различные форматы, включая .xlsx, .mxl и .csv.
Этот подход удобен для базовых задач, где не требуется сложное форматирование (например, для быстрого экспорта справочников в Excel). Мы можем создать ТабличныйДокумент, заполнить его данными и сохранить:
// Пример создания и сохранения ТабличногоДокумента
ТабличныйДокумент = Новый ТабличныйДокумент;
// Установим некоторые параметры страницы, если необходимо
// ТабличныйДокумент.ОриентацияСтраницы = ОриентацияСтраницы.Ландшафт;
// ТабличныйДокумент.МасштабПечати = 75;
// Добавим область (макет) или напрямую запишем текст в ячейки
// Например, создадим простую таблицу программно
ТабличныйДокумент.ВывестиТекст("Отчет");
ТабличныйДокумент.НачатьНовуюСтраницу();
ТабличныйДокумент.Область(2, 1, 2, 2).Текст = "Заголовок 1";
ТабличныйДокумент.Область(2, 3, 2, 4).Текст = "Заголовок 2";
Для ИндексСтроки = 3 По 10 Цикл
ТабличныйДокумент.Область(ИндексСтроки, 1, ИндексСтроки, 2).Текст = "Данные " + ИндексСтрока;
ТабличныйДокумент.Область(ИндексСтроки, 3, ИндексСтроки, 4).Число = ИндексСтрока * 100;
КонецЦикла;
ИмяФайла = "/tmp/Отчет1С.xlsx"; // Указываем путь в AltLinux
ТабличныйДокумент.Записать(ИмяФайла, ТипФайлаТабличногоДокумента.XLSX);
Сообщить("Файл сохранен: " + ИмяФайла);
Однако, как справедливо замечено в сообщении 7, этот "простой" метод имеет существенные ограничения. Если нам нужны:
ТабличныйДокумент может не всегда корректно передавать эти параметры при сохранении в Excel, особенно если они были заданы неявно или зависят от содержимого.ТабличногоДокумента, формулы часто преобразуются в их вычисленные значения или сохраняются как строки, а не как активные формулы Excel.ТабличныйДокумент предоставляет ограниченные возможности.
Сообщение 7 ярко описывает проблемы, связанные с форматом OpenXML (.xlsx), называя его "высранным" и указывая на "косяки", такие как чувствительность к порядку заполнения ячеек (например, A2, затем A1 может привести к не открывающемуся файлу) и сложности с описанием количества строк в таблицах с итогами. Эти сложности возникают из-за внутренней структуры XML-файлов, которая требует строгого соответствия спецификации.
Изначальный вопрос темы затрагивает формат .xls. Важно понимать, что .xls — это устаревший бинарный формат, используемый Microsoft Excel до версии 2007 года. Он является закрытым и сложным для программного создания и редактирования, особенно с сохранением всех нюансов форматирования.
Напротив, .xlsx — это современный формат, основанный на Office Open XML. Несмотря на критику в сообщении 7 относительно его сложности, он является открытым стандартом и гораздо лучше поддерживается программными библиотеками для работы с Excel. Он представляет собой ZIP-архив, содержащий набор XML-файлов, описывающих структуру книги, листы, данные, стили и другие элементы.
Мы настоятельно рекомендуем отдавать предпочтение формату .xlsx для программного заполнения. Большинство современных библиотек ориентированы именно на него, предоставляя полный контроль над структурой и содержимым файла, что позволяет обойти "косяки" OpenXML, упомянутые в обсуждении, предоставляя прямой программный доступ к каждому элементу.
В контексте AltLinux и 1С, особенно если нам требуется точное форматирование, формулы, даты и стили, как описано в сообщении 7, использование COMОбъект("Excel.Application") не подходит. Этот подход привязывает решение к операционной системе Windows и установленному MS Office, что неприемлемо для Linux-сервера 1С (в таких случаях пользователями также высоко ценится универсальная загрузка из Excel без установленного офиса).
Здесь на помощь приходят мощные библиотеки Python. Мы можем использовать внешний скрипт на Python, который будет получать данные от 1С (например, через временные файлы CSV, JSON или XML, или через внешние соединения, если это возможно) и генерировать Excel-файл с необходимой детализацией.
Библиотека OpenPyxl (openpyxl) является одной из самых популярных для чтения и записи файлов Excel в формате .xlsx. Она позволяет нам создавать новые книги, работать с листами, получать доступ к ячейкам и их значениям, вставлять различные типы данных (текст, числа, формулы), а также применять разнообразные стили.
С помощью OpenPyxl мы можем решить проблемы, поднятые в сообщении 7, предоставляя полный программный контроль:
Рассмотрим пример использования OpenPyxl для создания файла с форматированием и формулами:
import openpyxl
from openpyxl.styles import Font, Border, Side, Alignment, PatternFill
from openpyxl.utils import get_column_letter
# Создаем новую книгу и активный лист
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "Отчет Из 1С"
# Заголовок отчета
ws['A1'] = 'Подробный Отчет по Данным 1С'
ws['A1'].font = Font(name='Arial', size=16, bold=True)
ws.merge_cells('A1:D1')
ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
# Заголовки таблицы
headers = ['Наименование', 'Количество', 'Цена', 'Сумма']
for col_num, header in enumerate(headers, 1):
col_letter = get_column_letter(col_num)
ws[f'{col_letter}3'] = header
ws[f'{col_letter}3'].font = Font(name='Arial', size=11, bold=True, color="FFFFFF")
ws[f'{col_letter}3'].fill = PatternFill(start_color="4CAF50", end_color="4CAF50", fill_type="solid")
ws[f'{col_letter}3'].alignment = Alignment(horizontal='center', vertical='center')
# Установим ширину колонок
ws.column_dimensions[col_letter].width = 15 if col_num < 4 else 20
# Границы для заголовков
thin_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
for col_num in range(1, len(headers) + 1):
col_letter = get_column_letter(col_num)
ws[f'{col_letter}3'].border = thin_border
# Данные из 1С (предположим, что они пришли в виде списка словарей)
data = [
{'Наименование': 'Товар А', 'Количество': 10, 'Цена': 100.50},
{'Наименование': 'Товар Б', 'Количество': 5, 'Цена': 250.75},
{'Наименование': 'Товар В', 'Количество': 20, 'Цена': 50.25},
{'Наименование': 'Товар Г', 'Количество': 7, 'Цена': 120.00}
]
# Заполняем данные и формулы
row_start = 4
for idx, item in enumerate(data):
row = row_start + idx
ws[f'A{row}'] = item['Наименование']
ws[f'B{row}'] = item['Количество']
ws[f'C{row}'] = item['Цена']
# Формула для суммы: Количество * Цена
ws[f'D{row}'] = f'=B{row}*C{row}'
# Форматирование ячеек данных
for col_letter in ['A', 'B', 'C', 'D']:
cell = ws[f'{col_letter}{row}']
cell.border = thin_border
cell.font = Font(name='Arial', size=10)
if col_letter in ['B', 'C', 'D']:
cell.alignment = Alignment(horizontal='right')
if col_letter in ['C', 'D']: # Формат для денежных значений
cell.number_format = '#,##0.00 ₽'
# Итоги
total_row = row_start + len(data) + 1
ws[f'C{total_row}'] = 'Итого:'
ws[f'C{total_row}'].font = Font(name='Arial', size=11, bold=True)
ws[f'C{total_row}'].alignment = Alignment(horizontal='right')
# Формула для общей суммы
ws[f'D{total_row}'] = f'=SUM(D{row_start}:D{total_row-2})' # Суммируем столбец 'Сумма'
ws[f'D{total_row}'].font = Font(name='Arial', size=11, bold=True)
ws[f'D{total_row}'].number_format = '#,##0.00 ₽'
ws[f'D{total_row}'].border = thin_border
ws[f'D{total_row}'].fill = PatternFill(start_color="FFE0B2", end_color="FFE0B2", fill_type="solid")
# Сохраняем файл
file_path = '/tmp/report_openpyxl.xlsx'
wb.save(file_path)
print(f"Файл успешно создан: {file_path}")
Этот пример демонстрирует, как мы можем получить полный контроль над созданием файла Excel, решая проблемы с форматированием, формулами и типами данных, о которых говорилось в сообщении 7.
XlsxWriter — это еще один мощный модуль Python, специально предназначенный для создания файлов .xlsx. Он не может читать существующие файлы, но предлагает обширные возможности для точного форматирования, вставки изображений, диаграмм, настройки страницы, автофильтров, условного форматирования и многого другого.
Его ключевые преимущества:
Пример использования XlsxWriter:
import xlsxwriter
# Создаем новую книгу и добавляем лист
workbook = xlsxwriter.Workbook('/tmp/report_xlsxwriter.xlsx')
worksheet = workbook.add_worksheet("Отчет XlsxWriter")
# Добавляем форматы
bold = workbook.add_format({'bold': True})
currency_format = workbook.add_format({'num_format': '#,##0.00 ₽'})
header_format = workbook.add_format({
'bold': True,
'font_color': 'white',
'bg_color': '#4CAF50',
'align': 'center',
'valign': 'vcenter',
'border': 1
})
total_format = workbook.add_format({
'bold': True,
'num_format': '#,##0.00 ₽',
'bg_color': '#FFE0B2',
'border': 1
})
# Ширина колонок
worksheet.set_column('A:A', 20)
worksheet.set_column('B:D', 15)
# Заголовок
worksheet.merge_range('A1:D1', 'Подробный Отчет по Данным 1С (XlsxWriter)',
workbook.add_format({'bold': True, 'font_size': 16, 'align': 'center', 'valign': 'vcenter'}))
# Заголовки таблицы
headers = ['Наименование', 'Количество', 'Цена', 'Сумма']
for col_num, header in enumerate(headers):
worksheet.write(2, col_num, header, header_format)
# Данные
data = [
{'Наименование': 'Товар А', 'Количество': 10, 'Цена': 100.50},
{'Наименование': 'Товар Б', 'Количество': 5, 'Цена': 250.75},
{'Наименование': 'Товар В', 'Количество': 20, 'Цена': 50.25},
{'Наименование': 'Товар Г', 'Количество': 7, 'Цена': 120.00}
]
row_num = 3
for item in data:
worksheet.write(row_num, 0, item['Наименование'])
worksheet.write(row_num, 1, item['Количество'])
worksheet.write(row_num, 2, item['Цена'], currency_format)
# Формула для суммы: Количество * Цена
worksheet.write_formula(row_num, 3, f'=B{row_num+1}*C{row_num+1}', currency_format)
row_num += 1
# Итоги
worksheet.write(row_num, 2, 'Итого:', bold)
# Формула для общей суммы
worksheet.write_formula(row_num, 3, f'=SUM(D4:D{row_num})', total_format)
# Закрываем книгу
workbook.close()
print(f"Файл успешно создан: /tmp/report_xlsxwriter.xlsx")
Хотя Pandas в первую очередь является библиотекой для анализа данных, он прекрасно интегрируется с openpyxl или xlsxwriter для экспорта DataFrame в Excel. Если основные данные уже структурированы в DataFrame, процесс записи в Excel значительно упрощается.
После экспорта базовых данных с помощью Pandas, мы можем использовать OpenPyxl или XlsxWriter для добавления сложного форматирования, формул или других элементов, которые Pandas напрямую не поддерживает в полной мере.
import pandas as pd
# Предположим, у нас есть данные из 1С в DataFrame
data = {
'Наименование': ['Товар А', 'Товар Б', 'Товар В', 'Товар Г'],
'Количество': [10, 5, 20, 7],
'Цена': [100.50, 250.75, 50.25, 120.00]
}
df = pd.DataFrame(data)
# Создаем объект ExcelWriter, используя openpyxl как движок
writer = pd.ExcelWriter('/tmp/report_pandas.xlsx', engine='openpyxl')
# Записываем DataFrame в лист Excel
df.to_excel(writer, sheet_name='Данные 1С', index=False)
# Сохраняем книгу
writer.close() # Для Pandas 1.x и выше
# writer.save() # Для более старых версий Pandas
print(f"Файл Excel создан с помощью Pandas: /tmp/report_pandas.xlsx")
# Если нужно добавить форматирование, можно загрузить файл openpyxl и доработать:
# import openpyxl
# wb = openpyxl.load_workbook('/tmp/report_pandas.xlsx')
# ws = wb['Данные 1С']
# ws['A1'].font = openpyxl.styles.Font(bold=True)
# wb.save('/tmp/report_pandas_formatted.xlsx')
Библиотека pyexcel предоставляет унифицированный API для работы с множеством табличных форматов, включая csv, ods, xls (через дополнительные плагины, такие как pyexcel-xls), xlsx (через pyexcel-xlsx). Это может быть полезно, если нам нужно работать с разными форматами в одном приложении или упростить переключение между ними.
import pyexcel
# Данные для записи
data = [
["Имя", "Возраст"],
["Алексей", 30],
["Мария", 25]
]
# Записываем в xlsx
pyexcel.save_as(array=data, dest_file_name="/tmp/report_pyexcel.xlsx")
print(f"Файл Excel создан с помощью pyexcel: /tmp/report_pyexcel.xlsx")
# Если требуется поддержка старого .xls (требуется установка pyexcel-xls)
# pyexcel.save_as(array=data, dest_file_name="/tmp/report_pyexcel.xls")
Пакет formulas (необходимо установить через pip) может быть полезен, если нам нужно не только записывать формулы, но и интерпретировать их или выполнять вычисления вне Excel, например, на сервере AltLinux, где нет установленного Excel. Эта библиотека может считывать книги Excel, анализировать формулы и компилировать их в Python-код для быстрых вычислений. Это расширяет возможности, позволяя нам проверять логику формул или использовать их результаты без фактического открытия Excel.
Чтобы использовать эти Python-библиотеки из 1С в AltLinux, мы можем реализовать следующую схему:
ЗапуститьПриложение() или КомандаСистемы(), передавая путь к временному файлу данных и путь, куда должен быть сохранен итоговый Excel-файл, в качестве аргументов командной строки.Пример вызова Python-скрипта из 1С:
// В модуле 1С:Предприятия (на сервере)
ПутьКФайлуДанных = "/tmp/данные_для_excel.json";
ПутьКРезультирующемуExcel = "/tmp/итог_отчет.xlsx";
ПутьКПитонСкрипту = "/opt/1c_scripts/excel_generator.py"; // Убедитесь, что Python и скрипт доступны
// Подготовим данные в JSON (пример)
СтруктураДанных = Новый Структура;
СтруктураДанных.Вставить("Заголовок", "Отчет по продажам");
СтруктураДанных.Вставить("Строки", Новый Массив);
Для Каждого СтрокаТЧ Из МояТабличнаяЧасть Цикл // Пример данных из табличной части
СтрокаДанных = Новый Структура;
СтрокаДанных.Вставить("Наименование", СтрокаТЧ.Наименование);
СтрокаДанных.Вставить("Количество", СтрокаТЧ.Количество);
СтрокаДанных.Вставить("Цена", СтрокаТЧ.Цена);
СтруктураДанных.Строки.Добавить(СтрокаДанных);
КонецЦикла;
ЗаписьJSON = Новый ЗаписьJSON;
ЗаписьJSON.УстановитьСтроку();
ЗаписатьJSON(ЗаписьJSON, СтруктураДанных);
СтрокаJSON = ЗаписьJSON.Закрыть();
// Сохраняем JSON во временный файл
ТекстФайла = Новый ТекстовыйДокумент;
ТекстФайла.УстановитьТекст(СтрокаJSON);
ТекстФайла.Записать(ПутьКФайлуДанных, КодировкаТекста.UTF8);
// Формируем команду для запуска Python-скрипта
КомандаСистемы = "python3 " + ПутьКПитонСкрипту + " " + ПутьКФайлуДанных + " " + ПутьКРезультирующемуExcel;
РезультатВыполнения = ЗапуститьПриложение(КомандаСистемы, , Истина); // Истина - ждать завершения
Если РезультатВыполнения = 0 Тогда
Сообщить("Excel-файл успешно создан: " + ПутьКРезультирующемуExcel);
Иначе
Сообщить("Произошла ошибка при создании Excel-файла. Код возврата: " + РезультатВыполнения);
КонецЕсли;
А соответствующий Python-скрипт (excel_generator.py) будет выглядеть примерно так:
import sys
import json
import openpyxl
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
from openpyxl.utils import get_column_letter
def generate_excel_report(input_json_path, output_excel_path):
try:
with open(input_json_path, 'r', encoding='utf-8') as f:
data_from_1c = json.load(f)
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "Отчет из 1С"
# Заголовок отчета
report_title = data_from_1c.get('Заголовок', 'Отчет')
ws['A1'] = report_title
ws['A1'].font = Font(name='Arial', size=16, bold=True)
ws.merge_cells('A1:D1')
ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
# Заголовки таблицы
headers = ['Наименование', 'Количество', 'Цена', 'Сумма']
header_row = 3
for col_num, header in enumerate(headers, 1):
col_letter = get_column_letter(col_num)
ws[f'{col_letter}{header_row}'] = header
ws[f'{col_letter}{header_row}'].font = Font(name='Arial', size=11, bold=True, color="FFFFFF")
ws[f'{col_letter}{header_row}'].fill = PatternFill(start_color="1E90FF", end_color="1E90FF", fill_type="solid") # Синий цвет
ws[f'{col_letter}{header_row}'].alignment = Alignment(horizontal='center', vertical='center')
ws.column_dimensions[col_letter].width = 18
thin_border = Border(left=Side(style='thin'), right=Side(style='thin'),
top=Side(style='thin'), bottom=Side(style='thin'))
for col_num in range(1, len(headers) + 1):
col_letter = get_column_letter(col_num)
ws[f'{col_letter}{header_row}'].border = thin_border
# Данные
row_start = header_row + 1
for idx, item in enumerate(data_from_1c.get('Строки', [])):
row = row_start + idx
ws[f'A{row}'] = item['Наименование']
ws[f'B{row}'] = item['Количество']
ws[f'C{row}'] = item['Цена']
ws[f'D{row}'] = f'=B{row}*C{row}' # Формула!
for col_letter in ['A', 'B', 'C', 'D']:
cell = ws[f'{col_letter}{row}']
cell.border = thin_border
cell.font = Font(name='Arial', size=10)
if col_letter in ['B', 'C', 'D']:
cell.alignment = Alignment(horizontal='right')
if col_letter in ['C', 'D']:
cell.number_format = '#,##0.00' # Упрощенный формат для примера
# Итоги
if data_from_1c.get('Строки'):
total_row = row_start + len(data_from_1c['Строки'])
ws[f'C{total_row}'] = 'Итого:'
ws[f'C{total_row}'].font = Font(name='Arial', size=11, bold=True)
ws[f'C{total_row}'].alignment = Alignment(horizontal='right')
ws[f'D{total_row}'] = f'=SUM(D{row_start}:D{total_row-1})'
ws[f'D{total_row}'].font = Font(name='Arial', size=11, bold=True)
ws[f'D{total_row}'].number_format = '#,##0.00'
ws[f'D{total_row}'].border = thin_border
ws[f'D{total_row}'].fill = PatternFill(start_color="D3D3D3", end_color="D3D3D3", fill_type="solid")
wb.save(output_excel_path)
print(f"Excel report generated at: {output_excel_path}")
return 0 # Успех
except Exception as e:
print(f"Error generating Excel report: {e}", file=sys.stderr)
return 1 # Ошибка
if __name__ == "__main__":
if len(sys.argv) != 3:
print("Usage: python excel_generator.py ", file=sys.stderr)
sys.exit(1)
input_json = sys.argv[1]
output_excel = sys.argv[2]
sys.exit(generate_excel_report(input_json, output_excel))
Этот подход с использованием Python-скриптов позволяет нам гибко решать сложные задачи, которые невозможно или крайне трудно реализовать только средствами 1С, особенно когда речь идет о специфике форматов Excel и необходимости его точной программной генерации в Linux-окружении. Важно также помнить, что формулы в таких случаях обычно должны быть прописаны в английском формате, с английскими разделителями, так как это стандарт для OpenXML.
В AltLinux доступны полноценные офисные пакеты, такие как LibreOffice Calc или OnlyOffice. Они способны открывать, редактировать и сохранять файлы Excel (включая .xlsx и .xls). Для интерактивной работы пользователя они являются отличной заменой Microsoft Office. Однако для программного заполнения с высокой степенью детализации форматирования, библиотеки Python предоставляют гораздо более гибкий и мощный инструментарий, чем, например, макросы LibreOffice Basic, которые могут иметь проблемы с совместимостью VBA и не всегда удобны для серверной автоматизации.
Для очень сложных и высокопроизводительных задач, особенно когда требуется широкий спектр функций Excel и кросс-платформенность (включая Linux), существуют коммерческие библиотеки. Например, Aspose.Cells for Python via Java предоставляет мощный API для создания и редактирования Excel-файлов, предлагая функциональность, близкую к возможностям самого Microsoft Excel. Такие решения обычно используются в корпоративной среде для критически важных процессов.
Выбор метода для заполнения Excel-файлов в AltLinux из 1С зависит от требований к сложности форматирования и содержания.
ТабличныйДокумент 1С с сохранением в .xlsx.openpyxl, xlsxwriter, pandas), вызывая их из 1С. Этот подход позволяет преодолеть ограничения, описанные в сообщении 7..xlsx над устаревшим .xls для программной работы.Таким образом, мы видим, что, хотя начальный вопрос кажется простым (будь то экспорт данных или разработка простой обработки загрузки из excel), решение может быть многогранным и требовать комбинации различных технологий для достижения оптимального результата в специфической среде AltLinux с интеграцией 1С.