Как мы знаем, семерка может стандартно сохранять только в XLS 97. Огромные таблицы оооочень долго сохраняются. А больше 32 тыс строк, этот формат вообще не поддерживает. Да и не дождешься…. Можно использовать йоксель, но он тоже не очень быстро сохраняет, плюс в XLS 2003 формат, который опять же имеет ограничения в количестве строк на листе, которые обходятся созданием нового листа. И во всех этих вариантах имеется проблема с датой из-за двух цифр в годе. Пришло время все это переделать…
Пришел я к тому, что
1) Открываем Excel и заполняем лист с параметрами отчета (для информации) на втором листе, а на первом листе мы создаем колонки из таблицы значения, а также заполняем первую строку (после шапки) где указываем значение число или дату, чтобы колонка «имела тип» и корректно в дальнейшем отображалась пользователю. Также наводим марафет в внешнем виде (авто ширина колонок, жирным шапку). Закрываем эксельку.
2) Подключаемся по ADO (это как запись в таблицу базы данных)
3) Заполняем ТЗ которую нужно вывести в эту эксельку.
4) чтобы память 1ской не засиралась, в цикл заполнения вставляем процедуру записи ADO и опустошения накопленной ТЗ. Так сказать пакетная запись.
5) после выходая из цикла дозаписываем остатки ТЗ в эксель ADO.
6) отключаемся от эксельки в режиме ADO
7) удаляем форматную строку созданную в первом пункте.
По окончанию у нас получается ГОТОВАЯ экселька 2007 формата, где уже все отформатировано для пользователя и даты выведены как даты!
ФАЙЛЫ ДЛЯ СКАЧИВАНИЯ
Перем ТЗ;
Перем Connection, Command;
//-->> Функции для быстрого сохранения в Эксель
Функция ПодготовитьСтрокуКПечати(лСтрока)
лРез = СокрЛП(лСтрока);
лРез = СтрЗаменить(лРез,"'","");
Возврат лРез;
КонецФункции
Функция ЭксельСоздать(лПутьКФайлу, лТЗ, лСписокПараметров)
Перем НазваниеКолонки, ШиринаКолонки, ТипПеременной, ДлинаПеременной,ФорматСтроки;
Попытка
Если ФС.СуществуетФайл(лПутьКФайлу)=1 Тогда
ФС.УдалитьФайл(лПутьКФайлу);
КонецЕсли;
Исключение
Сообщить("Ошибка удаления Excel-файла: "+ОписаниеОшибки());
Возврат 0;
КонецПопытки;
Попытка
Эксель = СоздатьОбъект("Excel.Application");
Эксель.Visible=0;
Эксель.DisplayAlerts = 0;
Исключение
Сообщить("Ошибка создания компоненты Excel: "+ОписаниеОшибки());
Возврат 0;
КонецПопытки;
флКнигаУспешноСоздана=1;
Попытка
Книга = Эксель.WorkBooks.Add();
//удаляем лишние страницы, в разных версиях Excel по разному (например в 2008 создается 3 листа, в 2016 - 1 лист)
лТребуемоеКоличествоЛистов = 2;
Если Книга.WorkSheets.Count>лТребуемоеКоличествоЛистов Тогда
Если Книга.WorkSheets.Count<>лТребуемоеКоличествоЛистов Тогда
Лист = Книга.WorkSheets(1);
Лист.Delete();
КонецЕсли;
ИначеЕсли Книга.WorkSheets.Count<лТребуемоеКоличествоЛистов Тогда
Если Книга.WorkSheets.Count<>лТребуемоеКоличествоЛистов Тогда
Книга.WorkSheets.Add();
КонецЕсли;
КонецЕсли;
ЛистДанные = Книга.WorkSheets(1);
ЛистДанные.Name = "Данные";
ЛистПараметры = Книга.WorkSheets(2);
ЛистПараметры.Name = "Параметры";
Для НомерПараметра=1 По лСписокПараметров.РазмерСписка() Цикл
лПредставление="";
лЗначение = лСписокПараметров.ПолучитьЗначение(НомерПараметра,лПредставление);
ЛистПараметры.Cells(НомерПараметра, 1).Value = лПредставление;
ЛистПараметры.Cells(НомерПараметра, 1).Font.Bold = 1;
ЛистПараметры.Cells(НомерПараметра, 2).Value = лЗначение;
КонецЦикла;
лНомерКолонки=1;
Для НомерКолонки=1 По лТЗ.КоличествоКолонок() Цикл
ТипКолонки = "";
лВнутреннееНазвание = лТЗ.ПолучитьПараметрыКолонки(НомерКолонки,ТипКолонки,,,НазваниеКолонки,ШиринаКолонки);
Если ШиринаКолонки<>-1 Тогда
ЛистДанные.Cells(1, лНомерКолонки).Value = НазваниеКолонки;
ЛистДанные.Cells(1, лНомерКолонки).Font.Bold = 1;
ЛистДанные.Cells(1, лНомерКолонки).HorizontalAlignment = -4108; //XlHAlign.xlHAlignLeft
Если ТипКолонки="Число" Тогда
ЛистДанные.Cells(2, лНомерКолонки).Value = 1;
ИначеЕсли ТипКолонки="Дата" Тогда
ЛистДанные.Cells(2, лНомерКолонки).Value = ТекущаяДата();
КонецЕсли;
лНомерКолонки=лНомерКолонки+1;
КонецЕсли;
КонецЦикла;
ЛистДанные.Columns.AutoFit();
ЛистПараметры.Columns.AutoFit();
Исключение
Сообщить("Ошибка создания Excel-книги: "+ОписаниеОшибки());
флКнигаУспешноСоздана=0;
КонецПопытки;
Если флКнигаУспешноСоздана=1 Тогда
Попытка
Если ФС.СуществуетФайл(лПутьКФайлу)=1 Тогда
ФС.УдалитьФайл(лПутьКФайлу);
КонецЕсли;
Исключение
Сообщить("Ошибка создания Excel-файла: "+ОписаниеОшибки());
флКнигаУспешноСоздана=0;
КонецПопытки;
КонецЕсли;
Если флКнигаУспешноСоздана=1 Тогда
Попытка
xlWorkbookDefault =51; //xlsx
Книга.SaveAs(лПутьКФайлу,xlWorkbookDefault);
Исключение
Сообщить("Ошибка сохранения Excel-книги: "+ОписаниеОшибки());
флКнигаУспешноСоздана=0;
КонецПопытки;
КонецЕсли;
Попытка
Эксель.Application.Quit();
Исключение
флКнигаУспешноСоздана=0;
КонецПопытки;
ЛистДанные=0;
ЛистПараметры=0;
Книга=0;
Эксель = 0;
Возврат флКнигаУспешноСоздана;
КонецФункции
Функция ЭксельADO_Подключиться(лПутьКФайлу)
СтрокаПодключения = "
|Provider=Microsoft.ACE.OLEDB.12.0;
|Data Source='"+лПутьКФайлу+"';
|Extended Properties=""Excel 12.0 xml; HDR=YES"";";
Попытка
// Создаем соединение
Connection = СоздатьОбъект("ADODB.Connection");
Connection.Open(СтрокаПодключения);
Command = СоздатьОбъект("ADODB.Command");
Command.ActiveConnection = Connection;
Command.CommandType = 1;
Исключение
Сообщить("Ошибка подключения к файлу Excel: "+ОписаниеОшибки());
Возврат 0;
КонецПопытки;
Возврат 1;
КонецФункции
Процедура ЭксельADO_ПечатьСтрокПакетно(ТЗ, ТекстСостояние="", флПечатать=0)
Перем НазваниеКолонки, ШиринаКолонки, ТипПеременной, ДлинаПеременной,ФорматСтроки;
Если ТЗ.КоличествоСтрок()=0 Тогда
Возврат;
КонецЕсли;
Если (ТЗ.КоличествоСтрок()>9999) или (флПечатать=1) Тогда
Для НомерСтроки=1 По ТЗ.КоличествоСтрок() Цикл
ПроцентВыполнения = Окр(НомерСтроки/ТЗ.КоличествоСтрок()*100);
Состояние(ТекстСостояние+ Шаблон(". Пакетная запись в файл. Выполнено [ПроцентВыполнения]%"));
лСписокЗначенийКолонок = СоздатьОбъект("СписокЗначений");
лСтрокаПараметров="";
Для НомерКолонки=1 По ТЗ.КоличествоКолонок() Цикл
ИдентификаторКолонки = ТЗ.ПолучитьПараметрыКолонки(НомерКолонки,ТипПеременной,ДлинаПеременной,,НазваниеКолонки,ШиринаКолонки,ФорматСтроки);
Если ШиринаКолонки<>-1 Тогда
ЗначениеЯчейки = ТЗ.ПолучитьЗначение(НомерСтроки,НомерКолонки);
Если ТипПеременной="Число" Тогда
Если ДлинаПеременной=1 Тогда
лСтрокаПараметров = лСтрокаПараметров + "'"+?(ЗначениеЯчейки=0,"ОК","Ошибка")+"', ";
Иначе
ПечЗначение = Формат(ЗначениеЯчейки,ФорматСтроки);
лСтрокаПараметров = лСтрокаПараметров + ПечЗначение+", ";
КонецЕсли;
ИначеЕсли ТипПеременной="Документ" Тогда
Попытка
ПечЗначение = ЗначениеЯчейки.НомерДок;
Исключение
ПечЗначение="";
КонецПопытки;
лСписокЗначенийКолонок.ДобавитьЗначение();
лСтрокаПараметров = лСтрокаПараметров + "'"+ПодготовитьСтрокуКПечати(ПечЗначение)+"', ";
ИначеЕсли ТипПеременной="Дата" Тогда
ПечЗначение = ?(ПустоеЗначение(ЗначениеЯчейки)=1,"",Формат(ЗначениеЯчейки,"Д ДДММГГГГ"));
//лСтрокаПараметров = лСтрокаПараметров + "'"+ПодготовитьСтрокуКПечати(ПечЗначение)+"', ";
лСтрокаПараметров = лСтрокаПараметров+"'"+ПечЗначение+"', "
ИначеЕсли ТипПеременной="Строка" Тогда
ПечЗначение = СокрЛП(ЗначениеЯчейки);
лСтрокаПараметров = лСтрокаПараметров + "'"+ПодготовитьСтрокуКПечати(ПечЗначение)+"', ";
Иначе
ПечЗначение = ЗначениеЯчейки;
лСтрокаПараметров = лСтрокаПараметров + "'"+ПодготовитьСтрокуКПечати(ПечЗначение)+"', ";
КонецЕсли;
КонецЕсли;
КонецЦикла;
лСтрокаПараметров = Лев(лСтрокаПараметров,СтрДлина(лСтрокаПараметров)-2); //убираем последнюю запятую
Command.CommandText = "INSERT INTO [Данные$] VALUES ("+лСтрокаПараметров+")";
Command.Execute();
КонецЦикла;
ТЗ.УдалитьСтроки();
КонецЕсли;
КонецПроцедуры
Процедура ЭксельADO_Отключиться()
Состояние("Запись файла");
Command = 0;
Попытка
Connection.Close();
Исключение
КонецПопытки;
Connection = 0;
КонецПроцедуры
Функция ЭксельУдалитьФорматнуюСтроку(лПутьКФайлу)
Состояние("Удаление форматной строки");
Попытка
Эксель = СоздатьОбъект("Excel.Application");
Эксель.Visible=0;
Эксель.DisplayAlerts = 0;
Исключение
Сообщить("Ошибка создания компоненты Excel: "+ОписаниеОшибки());
Возврат 0;
КонецПопытки;
флУспешноУдаленаФорматнаяСтрока=1;
Попытка
Книга = Эксель.WorkBooks.Open(лПутьКФайлу);
ЛистДанные = Книга.WorkSheets("Данные");
ЛистПараметры = Книга.WorkSheets("Параметры");
ЛистДанные.Rows(2).Delete();
ЛистДанные.Columns.AutoFit();
ЛистПараметры.Columns.AutoFit();
Книга.Save();
Исключение
Сообщить("Ошибка удаления форматной строки: "+ОписаниеОшибки());
флУспешноУдаленаФорматнаяСтрока=0;
КонецПопытки;
Попытка
Эксель.Application.Quit();
Исключение
флУспешноУдаленаФорматнаяСтрока=0;
КонецПопытки;
Книга=0;
Эксель = 0;
Состояние("");
Возврат флУспешноУдаленаФорматнаяСтрока;
КонецФункции
//<<--
Процедура Сформировать()
КаталогФайла="";
ИмяФайла="";
Если ФС.ВыбратьФайл(1,ИмяФайла,КаталогФайла,"Укажите файл для сохранения","Excel 2007 (*.xlsx)|*.xlsx")=0 Тогда
Возврат;
КонецЕсли;
ИмяФайлаЭксель=КаталогФайла + ИмяФайла;
ВремяНачала = _GetPerformanceCounter ();
//-->> ТЗ
ТЗ=СоздатьОбъект("ТаблицаЗначений");
ТЗ.НоваяКолонка("Дата1", "Дата",,,"Дата 1",9);
ТЗ.НоваяКолонка("Дата2", "Дата",,,"Дата 2",9);
ТЗ.НоваяКолонка("НомерДок", "Строка",,,"Номер",15);
ТЗ.НоваяКолонка("Точка", "Справочник.ТорговыеТочки",,,"Торговая точка",10);
ТЗ.НоваяКолонка("Товар", "Справочник.Товары",,,"Товар",22);
ТЗ.НоваяКолонка("Количество", "Число",,3,"Количество",9,"Ч15.3");
//<<--
СписокПараметров=СоздатьОбъект("СписокЗначений");
//СписокПараметров.Установить("Период начало",НачДата);
//СписокПараметров.Установить("Период конец",КонДата);
//СписокПараметров.Установить("Торговые точки",?(ВыбТорговыеТочки.РазмерСписка()=0,"Все",ВыбТорговыеТочки.ВСтрокуСРазделителями()));
//СписокПараметров.Установить("Клиенты",?(ВыбКлиенты.РазмерСписка()=0,"Все",ВыбКлиенты.ВСтрокуСРазделителями()));
//СписокПараметров.Установить("Товары",?(ВыбТовары.РазмерСписка()=0,"Все",ВыбТовары.ВСтрокуСРазделителями()));
Если ЭксельСоздать(ИмяФайлаЭксель, ТЗ, СписокПараметров)=0 Тогда
Предупреждение("Ошибка создания эксель файла!",30); Возврат;
КонецЕсли;
Если ЭксельADO_Подключиться(ИмяФайлаЭксель)=0 Тогда
Предупреждение("Ошибка подключения к Excel-файлу!",30); Возврат;
КонецЕсли;
//Делаем запрос к данными
ТЗ_Запрос = СоздатьОбъект("ТаблицаЗначений");
Состояние("Этап 1/2. Выполнение запроса ...");
//ТЗ_Запрос = запрос.Выполнить();
Счетчик = 0;
ВсегоДокументов = ТЗ_Запрос.КоличествоСтрок();
Состояние("");
//Делаем заполнение ТЗ на основе полученных данных
ТЗ_Запрос.ВыбратьСтроки();
Пока ТЗ_Запрос.ПолучитьСтроку() = 1 Цикл
Счетчик=Счетчик+1;
ПроцентВыполнения = Окр(Счетчик/ВсегоДокументов*100);
ТекстСостояния = Шаблон("Этап 2/2. Формирование [ПроцентВыполнения]% ([Счетчик]/[ВсегоДокументов])");
Состояние(ТекстСостояния);
ТЗ.НоваяСтрока();
ТЗ.Дата1 = ТекущаяДата();
ТЗ.Дата2 = ТекущаяДата();
ТЗ.НомерДок = "";
ТЗ.Точка = "";
ТЗ.Товар = "";
ТЗ.Количество = "";
//Добавляем в Excel, если накопилось 10000 строк и чистим таблицу, чтобы опустошить память.
ЭксельADO_ПечатьСтрокПакетно(ТЗ, ТекстСостояния);
КонецЦикла;
//Довыводим остатки что остались в таблице
ЭксельADO_ПечатьСтрокПакетно(ТЗ, ТекстСостояния, 1);
ТЗ = 0;
ЭксельADO_Отключиться();
Если ЭксельУдалитьФорматнуюСтроку(ИмяФайлаЭксель)=0 Тогда
Предупреждение("Ошибка удаления форматной строки(второй) в Excel-файле!",30); Возврат;
КонецЕсли;
ВремяОкончания = _GetPerformanceCounter ();
Предупреждение ("Отчет сформирован и сохранен. Затрачено " +
Окр ((ВремяОкончания - ВремяНачала) / 1000, 2) + " сек");
КонецПроцедуры