Выгрузка в Excel с использованием формата OpenXML

30.01.2019 9 Comments

Загрузка и выгрузка в Excel , Обмен данными 1С, Разработка





    1.png
    2.png
    4.png
    5.png
    6.png

По работе приходится часто выгружать данные в Excel формате. При большом количестве строк и формул этот процесс достаточно длительный.
Решил написать для своих целей небольшую библиотечку для выгрузки данных. Используется технология open-xml. Никаких библиотек не надо, пишу напрямую в xml файлики.

Платформа 8.3.11

Для теста обычных форм брал УТ 10.3

Обработка демонстрирует возможности библиотеки

Процедура Рамки(Книга)
рамки =   Книга.рамки;
рамки.Добавить( "1000", "<border><left style='thin'><color indexed='64'/></left><right/><top/><bottom/><diagonal/></border>");
рамки.Добавить( "1100", "<border><left style='thin'><color indexed='64'/></left><right style='thin'><color indexed='64'/></right><top/><bottom/><diagonal/></border>");
рамки.Добавить( "0010", "<border><left/><right/><top style='thin'><color indexed='64'/></top><bottom/><diagonal/></border>");
рамки.Добавить( "0011", "<border><left/><right/><top style='thin'><color indexed='64'/></top><bottom style='thin'><color indexed='64'/></bottom><diagonal/></border>");
рамки.Добавить( "1111", "<border><left style='thin'><color indexed='64'/></left><right style='thin'><color indexed='64'/></right><top style='thin'><color indexed='64'/></top><bottom style='thin'><color indexed='64'/></bottom><diagonal/></border>");
рамки.Добавить( "0111", "<border><left/><right style='thin'><color indexed='64'/></right><top style='thin'><color indexed='64'/></top><bottom style='thin'><color indexed='64'/></bottom><diagonal/></border>");
рамки.Добавить( "2122", "<border><left style='medium'><color indexed='64'/></left><right style='thin'><color indexed='64'/></right><top style='medium'><color indexed='64'/></top><bottom style='medium'><color indexed='64'/></bottom><diagonal/></border>");
рамки.Добавить( "1122", "<border><left style='thin'><color indexed='64'/></left><right style='thin'><color indexed='64'/></right><top style='medium'><color indexed='64'/></top><bottom style='medium'><color indexed='64'/></bottom><diagonal/></border>");
рамки.Добавить( "2211", "<border><left style='medium'><color indexed='64'/></left><right style='medium'><color indexed='64'/></right><top style='thin'><color indexed='64'/></top><bottom style='thin'><color indexed='64'/></bottom><diagonal/></border>");
рамки.Добавить( "2222", "<border><left style='medium'><color indexed='64'/></left><right style='medium'><color indexed='64'/></right><top style='medium'><color indexed='64'/></top><bottom style='medium'><color indexed='64'/></bottom><diagonal/></border>");
КонецПроцедуры

Процедура шрифты(Книга)
шрифты =   Книга.Шрифты;
шрифты.Добавить("Arial_6"  , "<font><sz val='6'/><name val='Arial'/><family val='2'/></font>"    );
шрифты.Добавить("Arial_8"  , "<font><sz val='8'/><name val='Arial'/><family val='2'/></font>"    );
шрифты.Добавить("Arial_8_b", "<font><b/><sz val='8'/><name val='Arial'/><family val='2'/></font>");
шрифты.Добавить("Arial_9"  , "<font><b/><sz val='9'/><name val='Arial'/><family val='2'/></font>");
шрифты.Добавить("Arial_10" , "<font><sz val='10'/><name val='Arial'/><family val='2'/></font>");
шрифты.Добавить("Arial_11" , "<font><sz val='11'/><name val='Arial'/><family val='2'/></font>");
шрифты.Добавить("Arial_11_b", "<font><b/><sz val='11'/><name val='Arial'/><family val='2'/></font>");
шрифты.Добавить("Arial_11_FF413003", "<font><sz val='11'/><color rgb='FF413003'/><name val='Arial'/><family val='2'/></font>");
шрифты.Добавить("Arial_11_b_i", "<font><b/><i/><sz val='11'/><name val='Arial'/><family val='2'/></font>");
шрифты.Добавить("Arial_11_b_FF413003", "<font><b/><sz val='11'/><color rgb='FF413003'/><name val='Arial'/><family val='2'/></font>");
шрифты.Добавить("Arial_12", "<font><sz val='12'/><name val='Arial'/><family val='2'/></font>");
шрифты.Добавить("Arial_12_b", "<font><b/><sz val='12'/><name val='Arial'/><family val='2'/></font>");
шрифты.Добавить("Arial_12_b_FF413003","<font><b/><sz val='12'/><color rgb='FF413003'/><name val='Arial'/><family val='2'/></font>");
КонецПроцедуры

Процедура Заливки(Книга)
Заливки =   Книга.Заливки;
Заливки.Добавить("fill_FFFFFF00","<fill><patternFill patternType='solid'><fgColor rgb='FFFFFF00'/><bgColor indexed='64'/></patternFill></fill>");
КонецПроцедуры

//  Создание книг
// создание 1 книги с заданным числом листов
Процедура СоздатьПустуюКнигу(КоличествоЛистов)
книга = СоздатьКнигу(КоличествоЛистов);
Записать_книгу(книга, папка + "ПустаяКнига.xlsx")
КонецПроцедуры

// создание 2 книг и работа с ней
Процедура СоздатьПустуюКнигу_2(КоличествоЛистов)
книга = СоздатьКнигу(КоличествоЛистов);
ДобавитьСтроку(книга, 1, "B" ,"1", "ооо веснушка_1111"            , 0);

книга1 = СоздатьКнигу(1);
ДобавитьСтроку(книга1, 0, "B" ,"1", "ооо веснушка_2222"            , 0);

Записать_книгу(книга, папка + "ПустаяКнига_1.xlsx");
Записать_книгу(книга1, папка + "ПустаяКнига_2.xlsx");
КонецПроцедуры

// создание книги с  о стилями
Процедура СоздатьКнигуПроба()
книга = СоздатьКнигу();

Рамки(Книга);
Заливки(Книга);
шрифты(Книга);

ЛистНомер =0 ;

Заголовок11             = ДобавитьСтиль(книга, 0, книга.шрифты.Индекс(книга.шрифты.НайтиПоЗначению("Arial_11"))  , 0, 0, "vertical='center'");
Заголовок11_рамка_центр = ДобавитьСтиль(книга, 0, книга.шрифты.Индекс(книга.шрифты.НайтиПоЗначению("Arial_12_b")), 0, книга.рамки.Индекс(книга.рамки.НайтиПоЗначению("1111")), "horizontal='center'");
// https://msdn.microsoft.com/ru-ru/library/office/documentformat.openxml.spreadsheet.numberingformat.aspx
Заголовок11_рамка_дата  = ДобавитьСтиль(книга,14, книга.шрифты.Индекс(книга.шрифты.НайтиПоЗначению("Arial_11"))  , 0, книга.рамки.Индекс(книга.рамки.НайтиПоЗначению("1111")), "horizontal='center'");
ПодСтрокой              = ДобавитьСтиль(книга, 0, книга.шрифты.Индекс(книга.шрифты.НайтиПоЗначению("Arial_6"))   , 0, 0, "horizontal='center' vertical='top'");
Заголовок12             = ДобавитьСтиль(книга, 0, книга.шрифты.Индекс(книга.шрифты.НайтиПоЗначению("Arial_12"))  , 0, 0, "");

Рамка_верх_низ          = ДобавитьСтиль(книга, 0, 0, 0, книга.рамки.Индекс(книга.рамки.НайтиПоЗначению("0011")), "");
Рамка_верх_низ_право    = ДобавитьСтиль(книга, 0, 0, 0, книга.рамки.Индекс(книга.рамки.НайтиПоЗначению("0111")), "");
Рамка_верх              = ДобавитьСтиль(книга, 0, 0, 0, книга.рамки.Индекс(книга.рамки.НайтиПоЗначению("0010")), "");
Рамка_л                 = ДобавитьСтиль(книга, 0, 0, 0, книга.рамки.Индекс(книга.рамки.НайтиПоЗначению("1000")), "");
хмл = "";
ДобавитьСтроку(книга, ЛистНомер, "B" ,"1", "ооо веснушка"            , Заголовок12);
ДобавитьСтроку(книга, ЛистНомер, "B" ,"2", "наименование организации", ПодСтрокой);
ДобавитьСтроку(книга, ЛистНомер, "B" ,"3", "ооо радость"             , Заголовок12);
ДобавитьСтроку(книга, ЛистНомер, "B" ,"4", "наименование конрагента" , ПодСтрокой);
ДобавитьСтроку(книга, ЛистНомер, "D" ,"5", "Номер документа"       , Заголовок11_рамка_центр);
ДобавитьСтроку(книга, ЛистНомер, "F" ,"5", ""                        , Рамка_верх_низ);
ДобавитьСтроку(книга, ЛистНомер, "G","5", Формат(НачалоМесяца(Текущаядата()),"ДФ=dd.MM.yyyy"), Заголовок11_рамка_дата);
Книга.Листы[ЛистНомер].ОбъединенныеЯчейки.Добавить("<mergeCell ref=""D5:F5""/>");

Записать_книгу(книга, папка + "Книга.xlsx")
КонецПроцедуры

// создание отчета с формулами на основании документа
Процедура СоздатьКнигуДокумент()
книга = СоздатьКнигу();

Рамки(Книга);
Заливки(Книга);
шрифты(Книга);

ЛистНомер =0 ;

СформироватьШапку(книга,ЛистНомер,документ);
СформироватьШапкуТаблицы(книга, ЛистНомер);
след = СформироватьСтроки(книга, ЛистНомер);
ДобавитьФормулу(книга,ЛистНомер,"E" , "E",строка( след),строка( след+11), "SUM(E10:E" + строка(след-1)+")",200,истина,13);

Колонки(Книга.Листы[ЛистНомер]) ;
Записать_книгу(книга, папка + "КнигаДокумент.xlsx")
КонецПроцедуры

// создание отчета с формулами на основании документа  и выводом тч в таблицу
Процедура СоздатьКнигуДокументТаблица()
книга = СоздатьКнигу();

Рамки(Книга);
Заливки(Книга);
шрифты(Книга);

ЛистНомер =0 ;

СформироватьШапку(книга,ЛистНомер,документ);
СформироватьШапкуТаблицы(книга, ЛистНомер);
след = СформироватьСтроки(книга, ЛистНомер);
tableStyleInfo = "<tableStyleInfo name=""TableStyleLight2"" showFirstColumn=""0"" showLastColumn=""0"" showRowStripes=""1"" showColumnStripes=""0""/>";
ст = массив_букв[Шапка.Количество()-1];
СоздатьТаблицу(книга,ЛистНомер, "rr","A10:" + ст+  стрзаменить(строка(след-1),Символы.НПП,""),Шапка,tableStyleInfo);

Колонки(Книга.Листы[ЛистНомер]) ;

Записать_книгу(книга, папка + "КнигаДокументТаблица.xlsx")
КонецПроцедуры

Функции для работы с библиотекой

СоздатьКнигу(КоличествоЛистов);

Записать_книгу(книга, путь)

Рамки(Книга); //заполнение списка значений
    Заливки(Книга); //заполнение списка значений
    шрифты(Книга); //заполнение списка значений

ДобавитьСтроку(книга,ЛистНомер,столбец,строка, Значение,стиль);

ДобавитьЧисло( книга,ЛистНомер,столбец,строка, Значение,стиль);

ДобавитьФормулу(книга, ЛистНомер, СтолбецНач, СтолбецКон,строкаНач,строкаКон, Значение,НомерФормулы,первая,номер_стиль)

 ДобавитьСтиль(книга,numFmtId, fontId, fillId, borderId, Alignment="")

СоздатьТаблицу(Книга,лист, имя, диапазон, колонки,tableStyleInfo)

19.09.2024

добавил функцию: ДобавитьЗаморозку(книга,лист, колонка, строка)

Related Posts

9 Comments

  1. sytkosa

    (0) Есть несколько вопросов.

    1. Под управляемые формы переделать возможно быстро ?

    2. Возможно ли сделать обработку в виде библиотеки например для вызова в oscript ?

    3. Вы проводили замеры по скорости записи файла. Можно ли ускорить запись текста ?

    4. Умеет ли обработка работать с графикой ?

    Reply ↓
  2. TMV 
    Записать_иксель

    жесть

    Reply ↓
  3. user1045404

    (1) сегодня выложу по УФ тестировал на Бухгалтерии.

    (2) завтра сделаю конфу с общим модулем.

    (3) в статье писал: Делал выгрузку шапка 10 строк тч 4000 строк, 67 столбцов 20 формул в строке. форматирование(шрифты, рамки, ширина столбцов, заливка) времени заняло 135 секунд.

    Реально табель с данными который выгружался порядка 30 мин. выгружается 15 мин. с выгрузкой эксперементировал разными способами(шаблона в Excel, вывод с форматировнием, последний вариант вывод в mxl. Сохранение в Excel. Установка формул.)

    (4) вставку изображений не делал. Дня два работы. Несколько файлов нужно создавать и установку связей сделать.

    Reply ↓
  4. user1045404

    (2) переименую в записать_книгу. (раз так слух режет). в самой обработке использую название функций таким образом

    Записать_Content_Types_xml

    Записать_styles_fonts

    Записать_styles_fills

    Записать_workbook_xml_rels

    Записать_sheet1_xml_rels

    Подчеркивание и два языка…..

    Reply ↓
  5. user1045404

    (2) кстати видел еще в названиях функций и переменных «экзель»

    Reply ↓
  6. Serj1C

    чем не устроило сохранение табличного документа в формат эксель типовыми методами платформы?

    Reply ↓
  7. user1045404

    (6) формулы долго устанавливаются. бывает и офис не установлен.

    Reply ↓
  8. user1045404

    (6)плюс выгружаю в таблицу, что пользователю удобно(полосатость, сортировка, фильтрация..)

    Reply ↓
  9. oleganatolievich

    Используйте СтрСоединить, СтрШаблон вместо конкатенаций в цикле.

    Reply ↓

Leave a Comment

Ваш адрес email не будет опубликован. Обязательные поля помечены *