gifts2017

Хитрости сохранения табличного документа в Excel для дальнейшей работы с ним

Опубликовал Петр Вел (shmellevich) в раздел Программирование - Практика программирования

Все пользователи 1С всех версий (начиная с 7-й) сталкиваются с проблемой нормальной работы с данными, сохраненными в Excel файл.
Есть некоторые хитрости, которые позволяют облегчить работу с сохраненными данными из 1С в формат Excel.

Для дальнейшей работы с данными, полученными в табличном документе из системы 1С любой версии, их можно сохранить в формате xls(x).

Все бы хорошо, но при открытии в Excel-e пользователь сразу понимает, что с таблицей что-то не то, ну да, нет ярлыков листов книги. %)

Так было и в 7-й версии 1С и перекочевало в 8-ю, да, есть прогресс, в 8-ке можно сохранить в формате xlsx, родном формате последних версий Excel-я.

Для решения задачи есть несколько несложных манипуляций, первое, что приходит на ум любому пользователю, так это пересохранить таблицу в другой формат, но и тут нет нужного результата, потом начинаем делать копи-паст в новую книгу Excel. 
Есть еще вариант, как бы для продвинутых пользователей, - это зайти в настройки Excel-я и в разделе Дополнительно поставить галочку "Показывать ярлыки листов". Он является правильным, но тоже является рутинным, в свете частой обработки данных полученных из 1С в виде плоских таблиц и дальнейшего анализа данных в виде сводных таблиц и иных способов представления данных в Excel-е.

Сам точно так же замаялся бегать по галочкам и решил задачку, порывшись в просторах интернетов, например, тут и поглядев некоторые другие разработки, получилось реализовать несложный (для кого-то это баян, кому-то костыль, впрочем, о вкусах не спорят) механизм автоматизации решения проблемы с отображением ярлыков листов.

Все, что нужно, так это использовать два свойства окна Excel:

DisplayWorkbookTabs // признак отображения закладок листов книги
TabRatio  // соотношение ширины области закладок рабочей книги к ширине горизонтальной полосы прокрутки окна (как число между 0 (ноль) и 1, значение по умолчанию 0.6) 

Код:

Для v8:

ТабДокумент.Записать(ПолноеИмяФайла, ТипФайлаТабличногоДокумента.XLS);

Excel = Новый COMОбъект("Excel.Application");
Excel.WorkBooks.Open(ПолноеИмяФайла); 
Excel.Visible = 0;
Excel.ActiveWindow.DisplayWorkbookTabs = 1; 
Excel.ActiveWindow.TabRatio = 0.6;         
FullName = Excel.ActiveWorkbook.FullName;			
Excel.DisplayAlerts = false;
Excel.ActiveWorkbook.SaveAs(FullName, 18); // 18 - xls 97-2003; 51 - xlsx 2007-2013
//Excel.Visible = 1;       // если нужно поработать дальше с книгой
//Excel.Application.Quit() // если просто выходим 

Для v7:

ТабДокумент.Записать(ПолноеИмяФайла, "XLS");

Excel = СоздатьОбъект("Excel.Application");
Excel.WorkBooks.Open(ПолноеИмяФайла); 
Excel.Visible = 0; 
Excel.ActiveWindow.DisplayWorkbookTabs = 1; 
Excel.ActiveWindow.TabRatio = 0.6;
FullName = Excel.ActiveWorkbook.FullName;			
Excel.DisplayAlerts = false;
Excel.ActiveWorkbook.SaveAs(FullName, 18); // 18 - xls 97-2003; 51 - xlsx 2007-2013
//Excel.Visible = 1;       // если нужно поработать дальше с книгой
//Excel.Application.Quit() // если просто выходим

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Геннадий Зимин (kenza) 16.01.15 15:54
Спасибо! Сохранение файла без листов это меньшая часть проблем. Больше всего проблем доставляет, то что данные сохраняются в общем формате и работать с ними дальше невозможно. Нельзя поставить фильтры, группировки и прочее, даже вручную изменить тип данных не всегда удается. Решал проблему вандальным методом - сохраняем файл из 1С сразу же в HTML и далее открываем дальше его с помощью Ексель и с этого момента все данные имеют тот тип, который нужен. Далее можем сохранить файл уже в нормальный формат, ну или дальше с ним так и работать.
AndrewVVS; Hexed; ixilimuse; Abduvali; svetanik; +5 Ответить 1
2. Константин Юрин (kostyaomsk) 18.01.15 08:49
Для описание свойств екселя плюс. Хотя бы показали что эти два свойства, касающиеся закладок могут быть полезны.
3. anry mc (AnryMc) 18.01.15 10:22
Можно ещё так: Надстройка Excel у для "исправления" выгруженной из 1С "Карточки счета" http://infostart.ru/public/85416/
Преимущество: Использование выгрудкееыз из 1С "стандартными" методами...

А для "непродвинутого пользователя" можно в Экселе вывести кнопку...
Светлый ум; +1 Ответить
4. kiruha Дронов (kiruha) 19.01.15 14:27
Да, это меньшая часть проблем.
У нас, например, искажаются/пропадают цвета фона ячеек при сохранении/копировании в xls/xlsx
Нормального решения не нашел (Не нормальное - увеличить градиент цвета вручную)
5. Dim Dragonim (Dragonim) 19.01.15 17:46


Я не знаю как вы добились результата на картинке, но вот мой результат сохранения в формате xlsx, открытый в Excel 2013, Бухгалтерия предприятия 3.0 (3.0.36.19), платформа 8.3.5.1231, оборотно-сальдовая ведомость по счёту На рисунке в левом нижнем углу отчётливо видно вертикальное троеточие, если за него потянуть вправо, то откроются страницы из которых состоит данная Excel книга.

Исходя из выше написанного считаю статью лишней, а проблему надуманной. При сохранении из 1С в Excel имеется столько проблем, что можно составить отдельную статью только из их перечисления, но даже в той статье вряд ли стоит уделять место и время данной проблеме.

Ну и конечно же стандартный коммент о том что решение проблемы через внешний объект это плохая практика.
DronRF; echo77; Asples; madonov; Libera007; sivalor; +6 Ответить 1
6. Петр Вел (shmellevich) 19.01.15 20:58
(5) Dragonim, свойство TabRatio в диапазоне от 0 до 1, значение чем ближе к 1, тем три точки ближе к правому краю экрана, и это работает в строгой последовательности DisplayWorkbookTabs = 1, а потом TabRatio = 0.6 - это 60% слева. 1 - три точки будут в правом углу экрана. Работает на 7.7.027 и на 8.2.19.106

Ответ на стандартный коммент - когда приходится сохранять книгу Excel с несколькими листами из 1С, пример титулка - общие итоги, а последующие разные расшифровки, то без внешнего объекта как бы никак.
Hexed; svetanik; +2 Ответить 1
7. Dim Dragonim (Dragonim) 19.01.15 21:18
(6)
Ответ на стандартный коммент - когда приходится сохранять книгу Excel с несколькими листами из 1С, пример титулка - общие итоги, а последующие разные расшифровки, то без внешнего объекта как бы никак.


Видимо мне в жизни очень сильно повезло, что я ни когда не сталкивался в 1С с такими отчётами. Надеюсь моё везение на этом не закончиться.
8. Петр Вел (shmellevich) 19.01.15 21:51
(7) Dragonim, Мне тоже везло с отчетами, до текущей работы ))
9. Sky XN (sky.xn) 20.01.15 07:09
(4) kiruha, это ошибка платформы, в новом тестовом релизе уже исправлена, подробнее смотри на сайте users.v8.1c.ru
10. Сергей Харламов (zatoichi) 20.01.15 09:11
немного помогло. Спасибо!
11. Мищенко Вадим (vslimv) 20.01.15 09:56
Еще бы делать это без Excel, на сервере, но это уже мечты) Спасибо.
12. Александр Новак (xor4eg) 20.01.15 10:12
Ещё забабахайте статью, как сделать, чтоб 1С при сохранении в excel прекратила объединять сотни ячеек в одну.
olexich; ixilimuse; +2 Ответить
13. GSoft. (GSoft) 20.01.15 13:26
Если уж править код, то вот как я реализовал подобную задачу в 7.7.

Процедура в глобальном модуле
//__________________________________________________________­___________________
//Функция открывает таблицу в MS Excel
//Таблица			- печатная форма 1С
//ИмяЛиста			- имя, которое будет присвоено листу книги MS Excel (если не задано, то остается "Sheet1")
//ОтображатьЯрлычки	- показывать/скрыть ярлычки листов в MS Excel
//ОтображатьСетку	- показывать/скрыть линии сетки в MS Excel
//УдалитьСтроки		- позволяет удалить указанное количество верхних строк таблицы
//УдалитьСтроки		- позволяет удалить указанное количество левых столбцов таблицы
Функция глОткрытьТаблицуВExcel(Таблица, ИмяЛиста = "", ОтображатьЯрлычки = 1, ОтображатьСетку = 1, УдалитьСтроки = 0, УдалитьСтолбцы = 0) Экспорт
	Перем Excel, ExcelWB, ExcelSh, ExcelPS, ИмяФайла, Рез, Зн, Зн1;
	
	Если ТипЗначенияСтр(Таблица) <> "Таблица" Тогда 
		Возврат 0; 
	КонецЕсли;    
	
	Попытка 
		Excel = СоздатьОбъект("Excel.Application") 
	Исключение 
		Сообщить("Не удалось открыть MS Excel","!"); 
		Возврат 0; 
	КонецПопытки;
	
	Пока 1=1 Цикл
		//Подберем уникальное имя для временного файла
		ИмяФайла = КаталогВременныхФайлов() + "1c" + СтрЗаменить(ТекущееВремя(), ":", "");
		Если (ФС.СуществуетФайл(ИмяФайла+".tmp") = 1) или (ФС.СуществуетФайл(ИмяФайла+".xls") = 1) Тогда 
			ФС.УдалитьФайл(ИмяФайла+".tmp"); 
			ФС.УдалитьФайл(ИмяФайла+".xls") 
		Иначе 
			Прервать; 
		КонецЕсли
	КонецЦикла;
	
	Таблица.Записать(ИмяФайла+".tmp", "XLS");
	
	Если ФС.СуществуетФайл(ИмяФайла+".tmp") = 0 Тогда 
		Сообщить("Ошибка записи файла """ + ИмяФайла + "tmp""","!"); 
		Excel = ""; 
		Возврат 0; 
	КонецЕсли;    
	
	Рез = 1;
	
	Попытка
		ExcelWB = Excel.Workbooks.Add(ИмяФайла+".tmp");	//Откроем временный файл в Excel в режиме шаблона (чтобы не блокировать файл)
		Попытка											//Если на клиентской машине не установлен принтер, обращение к настройкам страницы вызывает ошибку. Поэтому работаем в "Попытка ... КонецПопытки"
			ExcelPS = ExcelWB.Sheets(1).PageSetup;		//Исправим спецсимволы в колонтитулах
			Зн = ExcelPS.LeftHeader;	Зн1 = СтрЗаменить(СтрЗаменить(СтрЗаменить(СтрЗаменить(Зн, "#P", "&P"), "#Q", "&N"), "#T", "&T"), "#D", "&D"); Если Зн <> Зн1 Тогда ExcelPS.LeftHeader	= Зн1 КонецЕсли;
			Зн = ExcelPS.CenterHeader;	Зн1 = СтрЗаменить(СтрЗаменить(СтрЗаменить(СтрЗаменить(Зн, "#P", "&P"), "#Q", "&N"), "#T", "&T"), "#D", "&D"); Если Зн <> Зн1 Тогда ExcelPS.CenterHeader	= Зн1 КонецЕсли;
			Зн = ExcelPS.RightHeader;	Зн1 = СтрЗаменить(СтрЗаменить(СтрЗаменить(СтрЗаменить(Зн, "#P", "&P"), "#Q", "&N"), "#T", "&T"), "#D", "&D"); Если Зн <> Зн1 Тогда ExcelPS.RightHeader	= Зн1 КонецЕсли;
			Зн = ExcelPS.LeftFooter;	Зн1 = СтрЗаменить(СтрЗаменить(СтрЗаменить(СтрЗаменить(Зн, "#P", "&P"), "#Q", "&N"), "#T", "&T"), "#D", "&D"); Если Зн <> Зн1 Тогда ExcelPS.LeftFooter	= Зн1 КонецЕсли;
			Зн = ExcelPS.CenterFooter;	Зн1 = СтрЗаменить(СтрЗаменить(СтрЗаменить(СтрЗаменить(Зн, "#P", "&P"), "#Q", "&N"), "#T", "&T"), "#D", "&D"); Если Зн <> Зн1 Тогда ExcelPS.CenterFooter	= Зн1 КонецЕсли;
			Зн = ExcelPS.RightFooter;	Зн1 = СтрЗаменить(СтрЗаменить(СтрЗаменить(СтрЗаменить(Зн, "#P", "&P"), "#Q", "&N"), "#T", "&T"), "#D", "&D"); Если Зн <> Зн1 Тогда ExcelPS.RightFooter	= Зн1 КонецЕсли;
		Исключение 
		КонецПопытки;
		
		ExcelWB.SaveAs(ИмяФайла+".xls", -4143);			//Сохраним, чтобы обновить формат файла до текущего (xlNormal = -4143)
		ExcelWB.Close(0);								//Закроем книгу ...
		ExcelWB = Excel.Workbooks.Add(ИмяФайла+".xls");	//... и вновь откроем в режиме шаблона
		
		Попытка
			ExcelSh = ExcelWB.Sheets(1);
			
			Если ПустаяСтрока(ИмяЛиста) = 0 Тогда			//Зададим имя ярлычка
				ExcelSh.Name = ИмяЛиста
			КонецЕсли;
			
			Если ОтображатьЯрлычки = 1 Тогда				//Сделаем видимыми ярлычки листов
			    ExcelWB.Windows(1).DisplayWorkbookTabs = 1;
				ExcelWB.Windows(1).TabRatio = 0.6			//... и отодвинем горизонтальную полосу прокрутки от левого края
			КонецЕсли;
			
			Если ОтображатьСетку = 0 Тогда					//Скроем линии сетки
				ExcelWB.Windows(1).DisplayGridlines = 0
			КонецЕсли;
		
			Если УдалитьСтроки > 0 Тогда					//Удалим верхние строки
				ExcelSh.Range(ExcelSh.Rows(1), ExcelSh.Rows(УдалитьСтроки)).Delete()
			КонецЕсли;
			
			Если УдалитьСтолбцы > 0 Тогда					//Удалим левые столбцы
				ExcelSh.Range(ExcelSh.Columns(1), ExcelSh.Columns(УдалитьСтолбцы)).Delete()
			КонецЕсли;
		Исключение 
		КонецПопытки;
		
		ExcelWB.Saved = 0;
	Исключение
		Сообщить("Ошибка открытия файла в MS Excel","!"); Рез = 0
	КонецПопытки;
	
	Попытка 
		Excel.Visible = 1 
	Исключение 
	КонецПопытки;  
	
	ExcelWB = ""; ExcelSh = ""; ExcelPS = ""; Excel = "";
	ФС.УдалитьФайл(ИмяФайла+".tmp");
	ФС.УдалитьФайл(ИмяФайла+".xls");
	Возврат Рез
КонецФункции //глОткрытьТаблицуВExcel

...Показать Скрыть




Вызов процедуры печати в отчете с выгрузкой в файл экселя и его отрытием
На форме две кнопки:
Печать (открытие печатной формы 1С): Формула вызова - Сформировать(0)
Открыть в Excel: Формула вызова - Сформировать(1)


Дополнения в процедуре "Сформировать" (или Печать - кому как больше нравится
Процедура  Сформировать(ЭкспортВЭксель = 0)
.................
.................
.................
	Если ЭкспортВЭксель = 0 Тогда
		Таб.ПараметрыСтраницы(2,,,,,,,,,1,,);
		Таб.Показать("Взаиморасчеты с контрагентами за период", "");   
	Иначе		
		глОткрытьТаблицуВExcel(Таб, "Взаиморасчеты с контрагентами за период", 1, 0, 0, 0);
	КонецЕсли;
КонецПроцедуры 
...Показать Скрыть
Прикрепленные файлы:
14. Петр Вел (shmellevich) 20.01.15 14:27
Это все хорошо вы описали, но суть статьи подсказать, как решить одну из многих проблем связки 1С + Excel,
а не куда его лучше положить, чтобы было лучше брать ;)

А править код это уже удел каждого разработчика на свое усмотрение.
Но все же спасибо за предложение.
15. Татьяна (svetanik) 20.01.15 14:52
Спасибо, что провели такую работу и не полнились выложить это общественности. Мне таки эта инфа очень к стати пришлась. Особенное спасибо за приведение кода для разных платформ. Можно использовать как справочник.
16. Dmitry (trdm) 21.01.15 22:32
Неплохо, вставлю ка я это в штатную конвертацию.
17. Eugen Krivylia (EugenLiquor) 27.01.15 09:51
Cпасибо за подробный гайд! Примерно так и делал, но вы это четко описали и сформулировали, многим людям эта статья поможет!
18. Петр Вел (shmellevich) 28.01.15 15:08
(17) Спасибо модератору, который меня заставил дополнить, а то было как-то сухо ))
Я разозлился и вот результат ))
19. Lady-mo (Lady-mo) 08.02.15 20:30
Спасибо за статью.
На самом деле пользователям важны ярлычки листов, и часто за этим обращаются, простое и удобное решение, очень помогло.
Кстати, а может вы действительно знаете, почему в сохраненном файле не работают фильтры и как с этим бороться?

20. Алексей Ива (lolik123) 18.11.15 22:28
21. Осипов Сергей (fixin) 06.05.16 13:38
Спасибо за подсказку с ярлыками.
А кроме ActiveWindow никак Window получить нельзя, например из текущего листа или книги?

Что касается пользователей, то можно просто понянуть полоску листов, она слева внизу, не надо никакие галочки выводить.
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа