Как добавить лист в 1с

Обновлено: 05.01.2025

Видеоролик выполнен в программе «1С:Бухгалтерия 8» релиз 3.0.41.39.

Способ внесения исправлений в книгу продаж зависит от того, в какой момент они вносятся.

Если исправления в книгу продаж вносятся в текущем налоговом периоде (до его окончания), то в ней следует отразить аннулируемый счет-фактуру с отрицательными значениями и зарегистрировать исправленный счет-фактуру с положительными значениями (п. 11 Правил ведения книги продаж, утв. Постановлением Правительства РФ от 26.12.2011 № 1137, далее – Постановление № 1137).

Исправление ошибки, связанной с занижением суммы реализации, в учетной системе регистрируется документом Корректировка реализации с видом операции Исправление в первичных документах. По кнопке Выписать исправленный счет-фактуру регистрируется исправленный счет-фактура.

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

Дополнительные листы можно сформировать отдельно от книги продаж, для этого нужно установить флаг Выводить только дополнительные листы.

Можно использовать два режима формирования дополнительных листов:

за текущий период - дополнительные листы формируются за период формирования книги продаж с корректировками, внесенными в последующих налоговых периодах;
за корректируемый период - дополнительные листы формируются за все предыдущие налоговые периоды, в которые внесены изменения в текущем налоговом периоде (периоде формирования книги продаж).

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

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

Клиент-Серверный вариант в управляемых формах.

1. Считываем данные из файла в массив из структур и затем этот массив передаем на сервер для дальнейших манипуляций с данными.

2-ой способ - это передать файл Excel на сервер через хранилище данных и далее работать с ним уже на сервере.

Очень часто приходится работать с очень большими файлами Excel, и его обработка путем перебора строк занимает огромное количество времени.

В таких случаех удобно в одно действие загрузить всю таблицу в массив и в потом работать уже непосредственно с массивом.

На выходе получаем двумерный массив, который содержит все данные указанного листа Excel

Полезные функции при работе с Excel:

Устанавливает видимость Excel при работе

Добавление новой книги в файл Excel

Сохранение книги Excel

Добавление нового листа к книге

Переименование листа

Изменение маштаба листа

Изменение ориентации листа

Отступы листа

Обращение к ячейки
чтение/запись

Обращение к области ячеек

Очень часто при чтении или записи значений в Excel ставятся лишние пробелы в числе, например, вместо 1502 он читает как 1 502 и в дальнейшем это значение не приводится к числу. Эту проблему можно решить заменой.

Объединение ячеек

Работа с именованными ячейками в Excel

Удаление ячейки

Удаление области ячеек

Обращение к строке

Изменение ширины колонки

Обращение к колонке

Удаление Строки

Фон ячейки / области / Строки /

Функция переводит цвет из формата RGB в формат Excel

Управление шрифтом в ячейки/строке/области

Р азрешает переносить по словам в ячейке

Управление рамкой ячейки

Устанавливаем формат ячейки

Формула в ячейки

Функция для получения ширины колонки Excel
Спасибо пользователю goodwill

Разрешить перенос слов в ячейке
Спасибо пользователю roofless

Группировки данных на листе
Спасибо пользователю dr-wit, ignor

При работе с Excel мы оперируем столбцами как числом (Например, 1 столбец), а у Excel адресация столбцов производится с помощью символов. И когда нам нужно отредактировать формулу, то нам нужно номер столбца преобразовать в символ. В таких случаях вам пригодится эта функция.

2. Работа с Excel через ТабличныйДокумент 1С

С помощью данного метода можно и загружать из Excel и выгружать в Excel. Но на мой взгляд этот метод идепально подходит когда вам необходимо посто сохранить информацию в Excel без дальнейшей манипуляции.

Итак, приступим: загрузка из Excel:

1. Загружаем файл Excel в табличный документ

2. Производим манипуляции уже с ТабличнымДокументом

Давайте теперь разберем сохранение в Excel с помощью данного метода:

Тут все очень просто сначала мы формируем обычный Табличный документ и затем записываем его в Excel

2. Работа с Excel ADODB

Выражаю особую благодарность коллеги Fragster за хороший комментарий

Данный метод позволяет работать с Excel через ODBC и имеет ряд преимуществ:

Не требует установки самой Excel, необходима лишь установить ODBC. Но как правило он уже установлен. Это особенность позволяет работать на стороне сервера без дополнительных установок Excel.
Позволяет работать с таблицой Excel как с БД и строить к ней запросы на T-SQL. Таким образом мы можем делать отборы еще на этапе чтения данных и другие преимущества что дает Т-SQL. Что на мой взгляд огромный плюс.

Подключение к ADO

Отключение от ADO

Выполнение запроса

Пример запроса:

Запись в Excel тоже производится в виде запроса:

Хочу отметить что наименование полей производится по первой строке в таблице

ADODB предоставляет ряд объектов, с которыми мы работаем

ADODB.Recordset

В файле продемонстрированны оба варианта работы с запросами.

На этом пока все. По возможности буду дополнять статью :)

В архиве находится обработка, которая демонстрирует все описанные и другие возможности при работе с Excel.

Дано: 22 таблицы унифицированного формата с перечнем помещений от 22 дочерних предприятий.

Задача: сделать сводную таблицу с данными всех 22 таблиц

Итак, решение. Есть три варианта решения данной задачи. Первый - использовать встроенную (начиная с excel 2013) надстройку PowerPivot (об этом методе мы поговорим позднее). Второй - посредством сводной таблицы через несколько диапазонов консолидации. Не люблю этот метод, т.к. такая сводная таблица имеет существенно более ограниченный функционал по сравнению с простой сводной таблицей. Наконец, третий вариант - это создать общую таблицу с использованием функции INDIRECT (ДВССЫЛ). Этому методу и посвятим сегодня наш пост.

Что мы имеем? 22 одинаковых по формату таблицы на отдельных листах и с разным количеством строк в каждой. Соответственно, для построения общей таблицы нам надо решить следующие "проблемы":

1) как заставить общую таблицу "переключаться" с одного листа данных на другой?;

2) как заставить ее делать это в нужный момент (когда закончились строки на одном листе)?

Начнем со второго вопроса, т.к. ответ на него мы уже знаем. Это циклы, о которых мы подробно говорили в выпуске 2 "Циклы в Excel без VBA". Для решения проблемы достаточно в наш файл добавить лист с перечнем всех обществ, соответствующими названиями листов и количеством подсчетом количества строк на каждом таком листе.

Обратите внимание на формулу в столбце Кол-во записей. Мы могли бы использовать просто COUNTA (СЧЁТА) со ссылкой на каждый лист. Но это потребует времени для "линковки" каждого листа. Плюс мы с вами готовим универсальные решения, которые будут работать в независимости от количества листов.

Именно для этого в функцию COUNTA (СЧЁТА) и внедряется эта чуднАя функция INDIRECT (ДВССЫЛ). Остановимся на ней подробнее.

Функция имеет, по сути, единственный параметр - ref_text (ссылка_на_текст). Что делает эта функция? Всего-навсего преобразует текст в ссылку на ячейку. То есть она преобразует текст, например, "А1" в ссылку и возвращает значение ячейки А1.

Соответственно, зная несложные правила работы с текстом в Excel, мы можем легко сделать текст внутри изменяемым, а значит можем сделать и изменяемыми ссылки, которые будет возвращать функция INDIRECT (ДВССЫЛ).

Разберем пример выше. Функция INDIRECT (ДВССЫЛ) имеет следующий вид:

Чтобы понять, что означает текст внутри нее, давайте вспомним, как выглядит в Excel ссылка на ячейку на другом листе. Вот так:

'Название листа'!A1

Соответственно, в нашем случае у нас неизменен столбец B:B, а вот название листа меняется. Для "автоматизации" формулы заменяем Название листа на ссылку на название листа с данными по соответствующему обществу (столбец Название листа). Получаем следующее:

" ' " & A1 & " '!B:B "

где все, что подчеркнуто - текст, а выделено жирным - ссылки.

ВАЖНО! Обратите внимание на кавычки и конкатенацию ("склеивание") при помощи амперсента. Не забудьте, что текст вносится в кавычках и соединяется со ссылками при помощи символа &.

Получается, что INDIRECT (ДВССЫЛ) получает название листа из ячеек в столбце А:А (Название листа), а диапазон у нас прописан фиксированно текстом (В:В). COUNTA (СЧЁТА) же просто считает количество строк в столбце В:В на соответствующем листа за вычетом заголовка.

Перейдем к формированию общей таблицы. Как вы уже наверное прикинули, у нас будет два вложенных цикла: один - количество обществ, второй - количество записей на листе данных по соответствующему обществу. Соответственно, нам потребуются два вспомогательных столбца (определим их в столбцы А и В). Ровно так же, как мы делали на примере в выпуске 2 "Циклы в Excel без VBA", пишем формулу первого цикла:

=IF(A2="";"";IF(COUNTIF($A$2:A2;A2)=OFFSET('Список обществ'!$E$2;A2-1;0);IF(A2+1>COUNTA('Список обществ'!A:A)-1;"";A2+1);A2))

Затем пишем формулу второго цикла:

Далее при помощи уже знакомой нам функции OFFSET (СМЕЩ) заполняем столбцы из таблицы на листе Список обществ.

Осталось подтянуть данные с 22 листов по обществам. Тут нам опять поможет OFFSET (СМЕЩ), но уже в паре с INDIRECT (ДВССЫЛ).

OFFSET (СМЕЩ) передает в формулу название листа, а конструкция B2+1 - номер строки в столбце Е на том листе.

ВАЖНО! Обратите внимание, что OFFSET (СМЕЩ) вкладывается внутрь INDIRECT (ДВССЫЛ), а не наоборот.

Публикаций на тему загрузки из EXCEL - множество, но
"
— Вам билетёр нужен?
— Был нужен, да уже взяли.
— Может и я на что сгожусь?
— Может и сгодишься, если скалиться не будешь .
"
"НЕУЛОВИМЫЕ МСТИТЕЛИ" (1966).

// Конвертировать имя файла для метода "LibreOffice CALC".
//
Функция ConvertToURL ( ФайлEXCEL )

ФайлEXCEL = СтрЗаменить ( ФайлEXCEL , " " , "%20" );
ФайлEXCEL = СтрЗаменить ( ФайлEXCEL , "\" , "/" );

Возврат "file:/" + "/localhost/" + ФайлEXCEL ;

КонецФункции

// Получение списка листов файла типа XLSX средствами 1С (ЧтениеXML).
//
Функция ФайлExcelПолучитьСписокЛистов_1CXML_XLSX ( ФайлEXCEL )
Перем ZIPКаталог , WorkBook ;
Перем СписокЛистов ;
ZIPКаталог = КаталогВременныхФайлов () + "XLSX\" ;
СписокЛистов = Новый СписокЗначений ;
Файл = ПолучитьОбъектФайл ( ФайлEXCEL , Истина);
Если Файл = Неопределено Тогда
Сообщить ( "Невозможно получить список листов, т.к. невозможно открыть для чтения файл:
|" + ФайлEXCEL );
Возврат Новый СписокЗначений ;
КонецЕсли;
Если НЕ ВРег ( Файл . Расширение ) = ".XLSX" Тогда
Возврат Новый СписокЗначений ;
КонецЕсли;
WorkBook = Новый ЧтениеXML ;
WorkBook . ОткрытьФайл ( ZIPКаталог + "XL\WorkBook.xml" );
// Считать очередной узел XML.
Пока WorkBook . Прочитать () Цикл
Если НЕ ВРег ( WorkBook . Имя ) = ВРег ( "sheet" ) Тогда
Продолжить;
КонецЕсли;
// Тип текущего узла XML.
Если WorkBook . ТипУзла = ТипУзлаXML . НачалоЭлемента Тогда
// Считать очередной атрибут элемента XML.
Пока WorkBook . ПрочитатьАтрибут () Цикл
Если НЕ ВРег ( WorkBook . Имя ) = "NAME" Тогда
Продолжить;
КонецЕсли;
СписокЛистов . Добавить ( WorkBook . Значение );
КонецЦикла;
КонецЕсли;
КонецЦикла;
// Завершение работы.
// Закрытие Объектов.
WorkBook . Закрыть ();
Возврат СписокЛистов ;
КонецФункции

Функция ПолучитьОбъектФайл (Знач ФайлEXCEL , РаспаковатьXLSX = Ложь)
Перем Файл , objFSO , objFile , XLSXРаспакован ;
Если НЕ ЗначениеЗаполнено ( ФайлEXCEL ) Тогда
Возврат Неопределено;
КонецЕсли;
Файл = Новый Файл ( ФайлEXCEL );
Если НЕ Файл . Существует () Тогда
Сообщить ( "Файл не существует:
|" + ФайлEXCEL );
Возврат Неопределено;
КонецЕсли;
// Проверка: Занят ли файл другим процессом?
objFSO = Новый COMОбъект ( "Scripting.FileSystemObject" );
Попытка
objFSO . MoveFile ( ФайлEXCEL , ФайлEXCEL );
objFile = objFSO . GetFile ( ФайлEXCEL );
Исключение
objFile = Неопределено;
objFSO = Неопределено;
Сообщить ( "Ошибка открытия файла/Файл занят другой программой:
|" + ФайлEXCEL );
Возврат Неопределено;
КонецПопытки;
objFSO = Неопределено;
Если ВРег ( Файл . Расширение ) = ".XLSX" И РаспаковатьXLSX Тогда
// Распаковка файла XLSX во временный каталог.
XLSXРаспакован = РаспаковатьXLSXвКаталогВременныхФайлов ( ФайлEXCEL );
Если НЕ XLSXРаспакован Тогда
Возврат Неопределено;
КонецЕсли;
КонецЕсли;
Возврат Файл ;
КонецФункции

Функция РаспаковатьXLSXвКаталогВременныхФайлов ( ФайлEXCEL )
Перем ZIPФайл , ZIPКаталог ;
ZIPКаталог = КаталогВременныхФайлов () + "XLSX\" ;
Попытка
УдалитьФайлы ( ZIPКаталог );
ZIPФайл = Новый ЧтениеZipФайла ;
ZIPФайл . Открыть ( ФайлEXCEL );
ZIPФайл . ИзвлечьВсе ( ZIPКаталог , РежимВосстановленияПутейФайловZIP . Восстанавливать );
Возврат Истина;
Исключение
Возврат Ложь;
КонецПопытки;
Возврат Истина;
КонецФункции

Особенности и Ограничения:

1. Для функционирования метода «Microsoft ADODB» необходимо:

2. Для функционирования метода «LibreOffice CALC» необходим установленный LibreOffice .

Видеоролик выполнен в программе «1С:Бухгалтерия 8» релиз 3.0.41.39.

Способ внесения исправлений в книгу продаж зависит от того, в какой момент они вносятся.

Можно использовать два режима формирования дополнительных листов:

за текущий период - дополнительные листы формируются за период формирования книги продаж с корректировками, внесенными в последующих налоговых периодах;
за корректируемый период - дополнительные листы формируются за все предыдущие налоговые периоды, в которые внесены изменения в текущем налоговом периоде (периоде формирования книги продаж).

Читайте также: