Работа с excel на сервере

Introduction

Very often, Excel spreadsheets are used to transfer data between applications. CSV files are also used for the same purpose.

There are a number of methods available to work with Excel spreadsheets and CSV files.

Read and Write Excel Documents using OLEDB Automation

You will need to add references to Microsoft.Office.Interop.Excel library located in C:WindowsassemblyGAC_MSILMicrosoft.Office.Interop.Excel or similar folder.

Statement using Excel = Microsoft.Office.Interop.Excel; is also required.

The followed steps could be used to create Excel spreadsheet from dataset:

  1. Create Excel objects: 
    Application excel = new Excel.Application();
Workbook workBook = excel.Workbooks.Add();
Worksheet sheet = workBook.ActiveSheet;
  2. Create header row: 
    foreach (DataColumn dc in ds.Tables[0].Columns)
{
    sheet.Cells[1, c + 1] = dc.Caption;
    c++;
}
  3. Load the data from dataset: 
    foreach (DataRow dr in ds.Tables[0].Rows)
{
    foreach (DataColumn dc in ds.Tables[0].Columns)
    {
        sheet.Cells[r + 1, c + 1] = dr[dc].ToString();
        c++;
    }
    r++;
    c=0;
}
  4. Save results in desired format: 
    workBook.SaveAs(OutputFileName, XlFileFormat.xlExcel8); 
workbook.Close();

Application can read and write Excel documents in the latest format, or use 2003 or earlier formats of spreadsheet, can work with CSV or tab-separated documents.

However, this method is not recommended. It is slow, heavy and unreliable. Microsoft Excel must be installed on the server. Developer should expect many bad surprises.

Jet OLEDB Provider

Another way to manipulate with spreadsheets and text files is a JET OLEDB provider.

Steps to create Excel file are:

  1. Create connection string: 
    excelConStr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source={0};Extended Properties=""Excel 8.0;""", OutputFileName);
  2. Create and populate spreadsheet: 
    using (OleDbConnection conn = new OleDbConnection(excelConStr))
{
     conn.Open();
     oledbcmd = new OleDbCommand();
     oledbcmd.Connection = conn;

     string ssql = "CREATE TABLE [table1] (";

     foreach (DataColumn dc in ds.Tables[0].Columns)
        ssql += "" + dc.Caption + " VARCHAR, ";
    
     ssql += ")";
     ssql = ssql.Replace(", )", ")");

     oledbcmd.CommandText = ssql;
     oledbcmd.ExecuteNonQuery();

     foreach (DataRow dr in ds.Tables[0].Rows)
     {
         ssql = "INSERT INTO [table1] VALUES(";
         foreach(DataColumn dc in ds.Tables[0].Columns)
             ssql += "'" + dr[dc].ToString() + "',";

         ssql += ")";
         ssql = ssql.Replace(",)", ")");

         oledbcmd.CommandText = ssql;
         oledbcmd.ExecuteNonQuery()
     }
     conn.Close()
}

This code illustrates how to create Excel spreadsheet from dataset. You will get Excel 2003 format. Different formats could be created by updating extended properties of connection string.

Microsoft Access Database Engine Redistributable

This is not a replacement for the Jet OLEDB Provider in server-side applications, but it has many nice features. For instance, you can use the steps below to read CSV file by using SQL statement:

  1. Create connection string: 
    string CSVConStr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;
Data Source={0};Extended Properties=""Text;HDR=YES;IMEX=2;FMT=Delimited""", 
System.IO.Path.GetDirectoryName(FileName));

    The first line of CSV file must have a header.

  2. Create dataset from CSV file: 
    using (excelCon = new OleDbConnection(CSVConStr))
{
    excelCon.Open();   
    SQL = "SELECT * FROM [" + System.IO.Path.GetFileName(FileName) + "]";
    cmd = new OleDbCommand(SQL, excelCon);
    ds = new DataSet();
    da = new OleDbDataAdapter(cmd);
    da.Fill(ds, "MyData");
}

Microsoft Access Database Engine 2010 Redistributable (https://www.microsoft.com/en-us/download/details.aspx?id=13255) should be installed on the host box. Excel installation on the server is not required.

ClosedXML Library

Example below shows how to create Excel 2007/2010 spreadsheet from dataset:

  1. Add references to ClosedXML library
  2. Add using ClosedXML.Excel
  3. Add actual code: 
    var wb = new XLWorkbook();
wb.Worksheets.Add(ds);
wb.SaveAs(OutputFileName);

SDK and documentation are available here.

This SDK works very fast, and provides a nice object oriented way to manipulate the files, and Excel installation on the server is not required.

Another powerful tool you can consider is EPPlus (https://epplus.codeplex.com/).

This member has not yet provided a Biography. Assume it’s interesting and varied, and probably something to do with programming.

Источник

Всем привет! Я сюда зашел за помощью.
Начал для семьи формировать красивый файлик со сложным расчетом доходности по проектам разным.

Так хорошо вышло, что я решил пойти дальше и опубликовать сей труд для всех, но либо в качестве приложения, либо в качестве веб-страницы.

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

Сейчас получается так:
Я открываю на ПК Excel, ввожу там данные, сохраняю, открываю Word-отчет, и сохраняю как PDF. вуаля, подробный отчет с описанием всех ньюансов проекта готов.

А как бы мне хотелось?
Вот так:

Пользователь заходит на страницу, вводит данные в форму,
Форма отправляет эти данные в нужные ячейки в экселе, который будет на сервере,
Эксель автоматом сразу же считает и выдает там результат в других ячейках,
Страница берет получившийся результат из нужных ячеек экселя и показывает пользователю
Рядом появляется кнопка, позволяющая скачать уже готовый pdf файл с отчетом по проекту.

Либо это можно сделать в виде приложения для смартфона.

Подскажите, как можно это сделать?

Источник

Содержание

  1. 1С 8.3 Чтение Excel через Табличный документ — Программист 1С Минск. Автоматизация бизнеса.
  2. 1С 8.3 Чтение Excel через Табличный документ
  3. Загрузка из EXCEL на сервере
  4. Работа с файлами Excel
  5. Обмен через табличный документ
  6. Обмен через OLE
  7. Использование COMSafeArray
  8. Обмен через ADO
  9. Выгрузка без программирования

1С 8.3 Чтение Excel через Табличный документ — Программист 1С Минск. Автоматизация бизнеса.

1С 8.3 Чтение Excel через Табличный документ

&НаКлиенте
Процедура ФайлНачалоВыбора ( Элемент , ДанныеВыбора , СтандартнаяОбработка ) //необходимо в событии «НачалоВыбора» поля ввода вызвать ДиалогВыбораФайла

Диалог = Новый ДиалогВыбораФайла ( РежимДиалогаВыбораФайла . Открытие );
Диалог . Заголовок = «Выберите файл Excel» ;
Диалог . ПредварительныйПросмотр = Ложь;
Диалог . Фильтр = «(*.xls,*.xlsx)|*.xls;*.xlsx;|Microsoft Excel 97/2000/XP/2003 (*.xls)|*.xls|Microsoft Excel 2007/2010 (*.xlsx)|*.xlsx» ;

Если ЗначениеЗаполнено ( Объект . Файл ) Тогда
Диалог . ПолноеИмяФайла = Объект . Файл ;
КонецЕсли;

Если Диалог . Выбрать () Тогда
ВыбранныйФайл = Диалог . ПолноеИмяФайла ;
Объект . Файл = ВыбранныйФайл ;
ЧтениеExcel_через_ТД ( ВыбранныйФайл );
КонецЕсли;

&НаСервере
Процедура ЧтениеExcel_через_ТД ( ВыбранныйФайл )

//1) Выводим Excel в табличн.документ
ТаблДок = Новый ТабличныйДокумент ;
ТаблДок . Прочитать ( ВыбранныйФайл , СпособЧтенияЗначенийТабличногоДокумента . Значение );

//2) Выгружаем в таблицу значений
ПЗ = Новый ПостроительЗапроса ;
ПЗ . ИсточникДанных = Новый ОписаниеИсточникаДанных ( ТаблДок . Область ());
ПЗ . ДобавлениеПредставлений = ТипДобавленияПредставлений . НеДобавлять ;
ПЗ . ЗаполнитьНастройки ();
ПЗ . Выполнить ();

ТаблицаЗначений = ПЗ . Результат . Выгрузить ();

Источник

Загрузка из EXCEL на сервере

Здравствуйте! Подскажите что может быть, ситуация следующая.
Есть сервер, на нем крутится 1С.
Есть база в которую идет загрузка из 1С(КС вариант).
Так вот при передачи фала EXCEL с клиента на сервер происходит сохранение данного файла в определенной папке(в контексте сервера), потом так же в контексте сервера происходит попытка открытия этого файла — но вылетает ошибка :
————————————————-
<Документ.КонкурсПоУслугам.Форма.ФормаЗагрузкиИзEXCEL.Форма(36)>: Ошибка при вызове метода контекста (Open)
Книга = Эксель.WorkBooks.Open(ИмяФайла);
по причине:
Произошла исключительная ситуация (Microsoft Excel): Приложению Microsoft Excel не удается получить доступ к файлу «G:1C_TempШахматка_08_11_2012_02_06_32.xlsx». Это может быть вызвано одной из следующих причин.

• Указан несуществующий файл или путь.
• Файл используется другой программой.
• Имя книги, которую вы пытаетесь сохранить, совпадает с именем другой книги, открытой в данный момент.
————————————————-
Код который все это делает ниже:
————————————————-
//2. Получим файлик для загрузки из временного хранилища
ИмяФайла = «G:1C_TempШахматка_»+Формат(ТекущаяДата(),»ДФ=dd_MM_yyyy_hh_mm_ss»)+».xlsx»;//Сисадмины сказали что в темпе хранит не феншуйно и выделили отдельную папку
Файлик = ПолучитьИзВременногоХранилища(АдресВовременномХранилище);
Файлик.Записать(ИмяФайла);

//3. Попробуем открыть EXCEL
Попытка Эксель = Новый COMОбъект(«Excel.Application»);
Исключение Сообщить(ОписаниеОшибки()); УдалитьФайлы(ИмяФайла); Возврат Неопределено;
КонецПопытки;
————————————————-
Перед открытием файлика, в режиме отладки, проверял — он существует(ФС.Существует()). Другой программой он не используется так как создается. у пользователя под которым выполняется код на стороне сервера разрешение на чтение/запись файла есть.
Что может быть, куда копать?

выложил не весь код.Вот весь:
//2. Получим файлик для загрузки из временного хранилища
ИмяФайла = «G:1C_TempШахматка_»+Формат(ТекущаяДата(),»ДФ=dd_MM_yyyy_hh_mm_ss»)+».xlsx»;//Сисадмины сказали что в темпе хранит не феншуйно и выделили отдельную папку
Файлик = ПолучитьИзВременногоХранилища(АдресВовременномХранилище);
Файлик.Записать(ИмяФайла);

//3. Попробуем открыть EXCEL
Попытка Эксель = Новый COMОбъект(«Excel.Application»);
Исключение Сообщить(ОписаниеОшибки()); УдалитьФайлы(ИмяФайла); Возврат Неопределено;
КонецПопытки;

//4. Откроем файлик и книгу
Книга = Эксель.WorkBooks.Open(ИмяФайла);
Лист = Книга.WorkSheets(НомерЛиста);

(1) если бы.Ставил точку останова на
Книга = Эксель.WorkBooks.Open(ИмяФайла);
находил этот файлик по ИмяФайла, открывал его, закрывал и продолжал откладку.Все равно куй.

Вызов на стороне клиента:
&НаКлиенте
Процедура Загрузить(Команда)

//1. Поместим наш файлик во временное хранилище
Перем АдресВовременномХранилище;
Если Не ЗначениеЗаполнено(Файл) Тогда Возврат; КонецЕсли;
ПоместитьФайл(АдресВовременномХранилище, Файл, , Ложь);

//2. Произведем чтение и разбор этого файла
Адрес = ПроизвестиЧтениеГоризонтальнойШахматки(АдресВовременномХранилище);

//3. Оповестим о том что все сделано
Если ЭтоАдресВременногоХранилища(Адрес) Тогда
ОповеститьОВыборе(Новый Структура(«ДанныеЗагрузки», Адрес));
Иначе
ЭтаФорма.Закрыть();
КонецЕсли;
КонецПроцедуры

Вызов на стороне сервера:
&НаСервере
Функция ПроизвестиЧтениеГоризонтальнойШахматки(АдресВовременномХранилище)

//1. Сформируем табличку в которую будем загружать
тзУслуги = Новый ТаблицаЗначений;
тзУслуги.Колонки.Добавить(«Лот» , Новый ОписаниеТипов(«СправочникСсылка.Объекты»));
тзУслуги.Колонки.Добавить(«Услуга» , Новый ОписаниеТипов(«СправочникСсылка.Объекты»));
тзУслуги.Колонки.Добавить(«Марка» , Новый ОписаниеТипов(«Строка»));
тзУслуги.Колонки.Добавить(«ЕдиницаИзмерения», Новый ОписаниеТипов(«СправочникСсылка.ЕдиницыИзмерения»));
тзУслуги.Колонки.Добавить(«Количество» , Новый ОписаниеТипов(«Число»));
тзУслуги.Колонки.Добавить(«ПДЦБезНДС» , Новый ОписаниеТипов(«Число»));
тзУслуги.Колонки.Добавить(«ПДЦ» , Новый ОписаниеТипов(«Число»));
тзУслуги.Колонки.Добавить(«Дорога» , Новый ОписаниеТипов(«СправочникСсылка.Дороги»));
тзУслуги.Колонки.Добавить(«УчастникКонкурса», Новый ОписаниеТипов(«СправочникСсылка.Организации»));
тзУслуги.Колонки.Добавить(«ФКП» , Новый ОписаниеТипов(«Число»));
тзУслуги.Колонки.Добавить(«ФКП_БезНДС» , Новый ОписаниеТипов(«Число»));

//2. Получим файлик для загрузки из временного хранилища
//ИмяФайла = «G:1C_TempШахматка_»+Формат(ТекущаяДата(),»ДФ=dd_MM_yyyy_hh_mm_ss»)+».xlsx»;//Сисадмины сказали что в темпе хранит не феншуйно и выделили отдельную папку
ИмяФайла = «G:1С ОбработкиШахматка.xlsx»;
Файлик = ПолучитьИзВременногоХранилища(АдресВовременномХранилище);
Файлик.Записать(ИмяФайла);
Файлик = «»;

//3. Попробуем открыть EXCEL
Попытка Эксель = Новый COMОбъект(«Excel.Application»);
Исключение Сообщить(ОписаниеОшибки()); УдалитьФайлы(ИмяФайла); Возврат Неопределено;
КонецПопытки;

//4. Откроем файлик и книгу
Книга = Эксель.WorkBooks.Open(ИмяФайла);
Лист = Книга.WorkSheets(НомерЛиста);

//5. Получим количество колонок и строк
ВсегоКолонок = Лист.Cells(1,1).SpecialCells(11).Column;
ВсегоСтрок = Лист.Cells(1,1).SpecialCells(11).Row;

//Далше всякая ерунда, она не нужна что бы глаз не мозолить
КонецФункции

Источник

Работа с файлами Excel

MS Excel давно стал стандартом для работы с электронными таблицами. В статье рассматриваются способы программной выгрузки и загрузки из 1С в файлы Excel.

Существует несколько способов программной работы с файлами Excel из 1С. Каждый из них имеет свои преимущества и недостатки.

Обмен через табличный документ

Данный способ простой. Его суть заключается в том, что объект ТабличныйДокумент имеет методы:

  • Записать ( ИмяФайла >, ТипФайлаТаблицы >) для выгрузки данных в файл;
  • Прочитать ( ИмяФайла >, СпособЧтенияЗначений >) для загрузки данных из файла.

Внимание!

Метод Записать () доступен как на клиенте, так и на сервере. Метод Прочитать () доступен только на стороне сервера. Необходимо помнить об этом
при планировании клиент-серверного взаимодействия.

Рассмотрим пример сохранения табличного документа в файл. Необходимо любым способом создать и заполнить объект ТабличныйДокумент, а выгрузка в файл осуществляется всего лишь одной строкой:

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

Здесь ТабДок — сформированный табличный документ, ПутьКФайлу — имя файла для выгрузки, ТипФайлаТабличногоДокумента.XLSX — формат создаваемого файла. Поддерживаются следующие форматы Excel:

  • XLS95 — формат Excel 95;
  • XLS97 — формат Excel 97;
  • XLSX — формат Excel 2007.

Загрузка из файла осуществляется также достаточно просто:

ТабДок = Новый ТабличныйДокумент ;
ТабДок . Прочитать ( ПутьКФайлу , СпособЧтенияЗначенийТабличногоДокумента.Значение );

Здесь ПутьКФайлу — путь к загружаемому файлу Excel. СпособЧтенияЗначенийТабличногоДокумента.Значение определяет, каким образом нужно интерпретировать данные, считываемые из исходного документа. Доступны варианты:

Обмен через OLE

Обмен через технологию OLE automation, пожалуй, самый распространенный вариант программной работы с файлами Excel. Он позволяет использовать весь функционал, предоставляемый Excel, но отличается медленной скоростью работы по сравнению с другими способами. Для обмена через OLE требуется установка MS Excel:

  • На компьютере конечного пользователя, если обмен происходит на стороне клиента;
  • На компьютере сервера 1С:Предприятие, если обмен происходит на стороне сервера.

Пример выгрузки:

// Создание COM-объекта
Эксель = Новый COMОбъект ( «Excel.Application» );
// Отключение вывода предупреждений и вопросов
Эксель . DisplayAlerts = Ложь;
// Создание новой книги
Книга = Эксель . WorkBooks . Add ();
// Позиционирование на первом листе
Лист = Книга . Worksheets ( 1 );

// Запись значения в ячейку
Лист . Cells ( НомерСтроки , НомерКолонки ). Value = ЗначениеЯчейки ;

// Сохранение файла
Книга . SaveAs ( ИмяФайла );

// Закрытие Эксель и освобождение памяти
Эксель . Quit ();
Эксель = 0 ;

Примеры чтения:

// Создание COM-объекта
Эксель = Новый COMОбъект ( «Excel.Application» );
// Открытие книги
Книга = Эксель . Workbooks . Open ( ПутьКФайлу );
// Позиционирование на нужном листе
Лист = Книга . Worksheets ( 1 );

// Чтение значения ячейки, обычно здесь располагается цикл обхода ячеек
ЗначениеЯчейки = Лист . Cells ( НомерСтроки , НомерКолонки ). Value ;

// Закрытие книги
Книга . Close ( 0 );

// Закрытие Эксель и освобождение памяти
Эксель . Quit ();
Эксель = 0 ;

// Открытие книги
Книга = ПолучитьCOMОбъект ( ПутьКФайлу );
// Позиционирование на нужном листе
Лист = Книга . Worksheets ( 1 );

// Чтение значения ячейки, обычно здесь располагается цикл обхода ячеек
ЗначениеЯчейки = Лист . Cells ( НомерСтроки , НомерКолонки ). Value ;

// Закрытие книги
Книга . Application . Qui t ();

Для обхода всех заполненных строк листа Excel можно использовать следующие приемы:

// —— ВАРИАНТ 1 ——
КоличествоСтрок = Лист . Cells ( 1 , 1 ). SpecialCells ( 11 ). Row ;
Для НомерСтроки = 1 По КоличествоСтрок Цикл
ЗначениеЯчейки = Лист . Cells ( НомерСтроки , НомерКолонки ). Value ;
КонецЦикла;

// —— ВАРИАНТ 2 ——
НомерСтроки = 0 ;
Пока Истина Цикл
НомерСтроки = НомерСтроки + 1 ;
ЗначениеЯчейки = Лист . Cells ( НомерСтроки , НомерКолонки ). Value ;
Если НЕ ЗначениеЗаполнено ( ЗначениеЯчейки ) Тогда
Прервать;
КонецЕсли;
КонецЦикла;

Вместо последовательного обхода всех строк листа можно выгрузить все данные в массив и работать с ним. Такой подход будет быстрее при чтении большого объема данных:

ВсегоКолонок = Лист . Cells ( 1 , 1 ). SpecialCells ( 11 ). Column ;
ВсегоСтрок = Лист . Cells ( 1 , 1 ). SpecialCells ( 11 ). Row ;

Область = Лист . Range ( Лист . Cells ( 1 , 1 ), Лист . Cells ( ВсегоСтрок , ВсегоКолонок ));
Данные = Область . Value . Выгрузить ();

В таблице ниже приведены наиболее востребованные свойства и методы для работы с Excel через OLE:

Действие Код Комментарий
Работа с приложением
Установка видимости окна приложения Эксель . Visible = Ложь;
Установка режима вывода предупреждений (выводить/не выводить) Эксель . DisplayAlerts = Ложь;
Закрытие приложения Эксель . Quit ();
Работа с книгой
Создание новой книги Книга = Эксель . WorkBooks . Add ();
Открытие существующей книги Книга = Эксель . WorkBooks . Open ( ИмяФайла );
Сохранение книги Книга . SaveAs ( ИмяФайла );
Закрытие книги Книга . Close ( 0 );
Работа с листом
Установка текущего листа Лист = Книга . WorkSheets ( НомерЛиста );
Установка имени Лист . Name = Имя ;
Установка защиты Лист . Protect ();
Снятие защиты Лист . UnProtect ();
Установка ориентации страницы Лист . PageSetup . Orientation = 2 ; 1 — книжная, 2 — альбомная
Установка левой границы Лист . PageSetup . LeftMargin = Эксель . CentimetersToPoints ( Сантиметры );
Установка верхней границы Лист . PageSetup . TopMargin = Эксель . CentimetersToPoints ( Сантиметры );
Установка правой границы Лист . PageSetup . RightMargin = Эксель . CentimetersToPoints ( Сантиметры );
Установка нижней границы Лист . PageSetup . BottomMargin = Эксель . CentimetersToPoints ( Сантиметры );
Работа со строками, колонками, ячейками
Установка ширины колонки Лист . Columns ( НомерКолонки ). ColumnWidth = Ширина ;
Удаление строки Лист . Rows ( НомерСтроки ). Delete ();
Удаление колонки Лист . Columns ( НомерКолонки ). Delete ();
Удаление ячейки Лист . Cells ( НомерСтроки , НомерКолонки ). Delete ();
Установка значения Лист . Cells ( НомерСтроки , НомерКолонки ). Value = Значение ;
Объединение ячеек Лист . Range ( Лист . Cells ( НомерСтроки , НомерКолонки ), Лист . Cells ( НомерСтроки1 , НомерКолонки1 )). Merge ();
Установка шрифта Лист . Cells ( НомерСтроки , НомерКолонки ). Font . Name = ИмяШрифта ;
Установка размера шрифта Лист . Cells ( НомерСтроки , НомерКолонки ). Font . Size = РазмерШрифта ;
Установка жирного шрифта Лист . Cells ( НомерСтроки , НомерКолонки ). Font . Bold = 1 ; 1 — жирный шрифт, 0 — нормальный
Установка курсива Лист . Cells ( НомерСтроки , НомерКолонки ). Font . Italic = 1 ; 1 — курсив, 0 — нормальный
Установка подчеркнутого шрифта Лист . Cells ( НомерСтроки , НомерКолонки ). Font . Underline = 2 ; 2 — подчеркнутый, 1 — нет

Для того, чтобы узнать какое свойство нужно менять или какой метод вызвать можно воспользоваться макросами Excel. Если записать макрос с требуемыми действиями, то после можно посмотреть программный код на VBA записанного макроса.

Использование COMSafeArray

При выгрузке больших объемов данных из 1С в Excel для ускорения можно использовать объект COMSafeArray. Согласно определению из синтакс-помощника, COMSafeArray — объектная оболочка над многомерным массивом SafeArray из COM. Позволяет создавать и использовать SafeArray для обмена данными между COM-объектами. Проще говоря, это массив значений, который можно использовать для обмена между приложениями по технологии OLE.

// Создание COMSafeArray
МассивКом = Новый COMSafeArray ( «VT_Variant» , ВсегоКолонок , ВсегоСтрок );
// Заполнение COMSafeArray
Для Стр = 0 По ВсегоСтрок — 1 Цикл
Для Кол = 0 По ВсегоКолонок — 1 Цикл
МассивКом . SetValue ( Кол , Стр , Значение );
КонецЦикла;
КонецЦикла;
// Присвоение области листа Excel значений из COMSafeArray
Лист . Range ( Лист . Cells ( 1 , 1 ), Лист . Cells ( ВсегоСтрок , ВсегоКолонок )). Value = МассивКом ;

Обмен через ADO

Файл Excel при обмене через ADO представляет собой базу данных, к которой можно обращаться при помощи SQL-запросов. Установка MS Excel не требуется, но обязательно наличие драйвера ODBC, при помощи которого будет осуществляться доступ. Используемый драйвер ODBC определяется при указании строки соединения к файлу. Обычно требуемый драйвер уже установлен на компьютере.

Обмен через ADO заметно быстрее обмена через OLE, но при выгрузке нет возможности использовать функционал Excel для оформления ячеек, разметки страниц, задания формул и т.д.

Пример выгрузки:

// Создание COM-объекта для соединения
Соединение = Новый COMОбъект ( «ADODB.Connection» );

// Установка строки соединения
Соединение . ConnectionString = «
|Provider=Microsoft.ACE.OLEDB.12.0;
|Data Source=» + ИмяФайла + «;
|Extended Properties=»»Excel 12.0 XML;HDR=YES»»;» ;
Соединение . Open (); // Открытие соединения

// Создание COM-объекта для команды
Команда = Новый COMОбъект ( «ADODB.Command» );
Команда . ActiveConnection = Соединение ;

// Присвоение текста команды для создания таблицы
Команда . CommandText = «CREATE TABLE [Лист1] (Колонка1 char(255), Колонка2 date, Колонка3 int, Колонка4 float)» ;
Команда . Execute (); // Выполнение команды

// Присвоение текста команды для добавления строки таблицы
Команда . CommandText = «INSERT INTO [Лист1] (Колонка1, Колонка2, Колонка3, Колонка4) values (‘абвгдеё’, ‘8/11/2017’, ‘12345’, ‘12345,6789’)» ;
Команда.Execute(); // Выполнение команды

// Удаление команды и закрытие соединения
Команда = Неопределено;
Соединение . Close ();
Соединение = Неопределено ;

Для создания нового листа и формирования его структуры можно воспользоваться объектами ADOX.Catalog и ADOX.Table. В этом случае код примет вид:

// Создание COM-объекта для работы с книгой
Книга = Новый COMОбъект ( «ADOX.Catalog» );
Книга . ActiveConnection = Соединение ;

// Создание COM-объекта для работы со структурой данных на листе
Таблица = Новый COMОбъект ( «ADOX.Table» );
Таблица . Name = «Лист1» ;
Таблица . Columns . Append ( «Колонка1» , 202 );
Таблица . Columns . Append ( «Колонка2» , 7 );
Таблица . Columns . Append ( «Колонка3» , 5 );
Таблица . Columns . Append ( «Колонка4» , 5 );

// Создание в книге листа с описанной структурой
Книга . Tables . Append ( Таблица );
Таблица = Неопределено;
Книга = Неопределено;

В приведенном примере в методе

Таблица . Columns . Append ( «Колонка1» , 202 );

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

  • 5 — adDouble;
  • 6 — adCurrency;
  • 7 — adDate;
  • 11 — adBoolean;
  • 202 — adVarWChar;
  • 203 — adLongVarWChar.

Пример чтения:

// Создание COM-объекта для соединения
Соединение = Новый COMОбъект ( «ADODB.Connection» );

// Установка строки соединения
Соединение . ConnectionString = «
|Provider=Microsoft.ACE.OLEDB.12.0;
|Data Source=» + ИмяФайла + «;
|Extended Properties=»»Excel 12.0 XML;HDR=YES»»;» ;
Соединение . Open (); // Открытие соединения

// Создание COM-объекта для получения выборки
Выборка = Новый COMОбъект ( «ADODB.Recordset» );
ТекстЗапроса = «SELECT * FROM [Лист1$]» ;

// Выполнение запроса
Выборка . Open ( ТекстЗапроса , Соединение );

// Обход результата выборки
Пока НЕ Выборка . EOF () Цикл
ЗначениеКолонки1 = Выборка . Fields . Item ( «Колонка1» ). Value ; // Обращение по имени колонки
ЗначениеКолонки2 = Выборка . Fields . Item ( 0 ). Value ; // Обращение по индексу колонки
Выборка . MoveNext ();
КонецЦикла;

Выборка . Close ();
Выборка = Неопределено;
Соединение . Close ();
Соединение = Неопределено;

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

  • YES — первая строка воспринимается как названия колонок. К значениям можно обращаться по имени и по индексу колонки.
  • NO — первая строка воспринимается как данные. К значениям можно обращаться только по индексу колонки.

В приведенных примерах рассмотрено лишь несколько объектов ADO. Объектная модель ADO состоит из следующих объектов:

Выгрузка без программирования

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

Для сохранения табличного документа (например, результата отчета) можно вызвать команду Сохранить или Сохранить как… главного меню.

В открывшемся окне требуется выбрать каталог, имя и формат сохраняемого файла.

Для сохранения данных динамических списков (например, списка номенклатуры) необходимо:

  1. Вывести данные в табличный документ при помощи команды Еще ⇒ Вывести список…;
  2. Сохранить табличный документ в требуемый формат.

Источник

Источник

Настройка DCOM для Excel.Application в 1С, за минуту

Настройка DCOM для Excel.Application в 1С, за минуту

Добрый день! Уважаемые читатели и гости, популярного IT блога pyatilistnik.org. Сегодня я продолжу пополнять свою копилку знаний в разделе 1С. Вчера меня один из разработчиков, попросил ему помочь настроить компоненты DCOM для Excel.Application в связке с приложением 1С. Я ранее этого не делал, так что пришлось в этом разбираться. По итогам найденного решения, появилась и эта заметка. Уверен, что на этот материал найдутся желающие.

Что такое DCOM

Distributed Component Object Model (DCOM) — программная архитектура, разработанная компанией Microsoft для распределения приложений между несколькими компьютерами в сети. Программный компонент на одной из машин может использовать DCOM для передачи сообщения (его называют удаленным вызовом процедуры) к компоненту на другой машине. DCOM автоматически устанавливает соединение, передает сообщение и возвращает ответ удаленного компонента. С данным компонентов мы уже знакомились, когда решали ошибку 10016.

Настройка DCOM для Excel.Application

И так у меня есть виртуальная машина под управлением Windows Server 2012 R2, на ней установлены серверные компоненты 1С, есть задача для Excel.Application модуля, настроить DCOM компоненты.

Нам необходимо открыть в панели управления Windows, компонент «Службы компонентов».

Либо можете, через окно выполнить ввести DCOMCNFG, откроется, тоже самое.

В службах компонентов (Component Services) откройте ветку:

Если по каким-то причинам у вас нет пункта Microsoft Excel Application, откройте реестр Windows, найдите ветку: HKEY_CLASSES_ROOTAppIDEXCEL.EXE, если ее нет, то создаем ее. В ней должен быть ключ REG_SZ со значением .

КОМУ ЛЕНЬ самому создавать, то можете скачать данный ключ реестра у меня. Просто запустите потом и подтвердите, что согласны добавить. Затем откройте командную строку от имени администратора и выполните команду:

У вас откроется mmc оснастка «Службы компонентов», которая делает то же что и DCOMCONFIG, но позволяет видеть 32 битные компоненты. В результате этих манипуляций компонент Microsoft Excel Application у вас должен появиться. Открываем свойства Microsoft Excel Application.

Переходим на вкладку «Безопасность (Secutity)». В пункте «Разрешения на запуск и активацию (Launch and Activation Permissions)», выберите «Настроить (Customize )» и изменить. Вам нужно добавить сюда учетную запись, от имени которой запускается служба 1С и предоставить ей права:

  • Локальный запуск — Local Launch
  • Локальная активация — Local Activation

Далее переходим к пункту «Разрешения на доступ (Access Permissions)» и так же меняем их. Добавьте там ту же учетную запись, от имени которой запускается сервис 1С, и дайте ей права «Локальный доступ (Local Access)». Сохраните настройки.

Закрываем окно служб компонентов, теперь для того, чтобы закончит ьнастройку DCOM для 1С и Microsoft Excel Application, вам необходимо дать права на две папки.

Вам нужно убедиться, что у вас есть две папки:

Работа с файлами Excel

MS Excel давно стал стандартом для работы с электронными таблицами. В статье рассматриваются способы программной выгрузки и загрузки из 1С в файлы Excel.

Существует несколько способов программной работы с файлами Excel из 1С. Каждый из них имеет свои преимущества и недостатки.

Обмен через табличный документ

Данный способ простой. Его суть заключается в том, что объект ТабличныйДокумент имеет методы:

  • Записать ( , ) для выгрузки данных в файл;
  • Прочитать ( , ) для загрузки данных из файла.

Внимание!

Метод Записать () доступен как на клиенте, так и на сервере. Метод Прочитать () доступен только на стороне сервера. Необходимо помнить об этом
при планировании клиент-серверного взаимодействия.

Рассмотрим пример сохранения табличного документа в файл. Необходимо любым способом создать и заполнить объект ТабличныйДокумент, а выгрузка в файл осуществляется всего лишь одной строкой:

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

Здесь ТабДок — сформированный табличный документ, ПутьКФайлу — имя файла для выгрузки, ТипФайлаТабличногоДокумента.XLSX — формат создаваемого файла. Поддерживаются следующие форматы Excel:

  • XLS95 — формат Excel 95;
  • XLS97 — формат Excel 97;
  • XLSX — формат Excel 2007.

Загрузка из файла осуществляется также достаточно просто:

ТабДок = Новый ТабличныйДокумент ;
ТабДок . Прочитать ( ПутьКФайлу , СпособЧтенияЗначенийТабличногоДокумента.Значение );

Здесь ПутьКФайлу — путь к загружаемому файлу Excel. СпособЧтенияЗначенийТабличногоДокумента.Значение определяет, каким образом нужно интерпретировать данные, считываемые из исходного документа. Доступны варианты:

Обмен через OLE

Обмен через технологию OLE automation, пожалуй, самый распространенный вариант программной работы с файлами Excel. Он позволяет использовать весь функционал, предоставляемый Excel, но отличается медленной скоростью работы по сравнению с другими способами. Для обмена через OLE требуется установка MS Excel:

  • На компьютере конечного пользователя, если обмен происходит на стороне клиента;
  • На компьютере сервера 1С:Предприятие, если обмен происходит на стороне сервера.

Пример выгрузки:

// Создание COM-объекта
Эксель = Новый COMОбъект ( «Excel.Application» );
// Отключение вывода предупреждений и вопросов
Эксель . DisplayAlerts = Ложь;
// Создание новой книги
Книга = Эксель . WorkBooks . Add ();
// Позиционирование на первом листе
Лист = Книга . Worksheets ( 1 );

// Запись значения в ячейку
Лист . Cells ( НомерСтроки , НомерКолонки ). Value = ЗначениеЯчейки ;

// Сохранение файла
Книга . SaveAs ( ИмяФайла );

// Закрытие Эксель и освобождение памяти
Эксель . Quit ();
Эксель = 0 ;

Примеры чтения:

// Создание COM-объекта
Эксель = Новый COMОбъект ( «Excel.Application» );
// Открытие книги
Книга = Эксель . Workbooks . Open ( ПутьКФайлу );
// Позиционирование на нужном листе
Лист = Книга . Worksheets ( 1 );

// Чтение значения ячейки, обычно здесь располагается цикл обхода ячеек
ЗначениеЯчейки = Лист . Cells ( НомерСтроки , НомерКолонки ). Value ;

// Закрытие книги
Книга . Close ( 0 );

// Закрытие Эксель и освобождение памяти
Эксель . Quit ();
Эксель = 0 ;

// Открытие книги
Книга = ПолучитьCOMОбъект ( ПутьКФайлу );
// Позиционирование на нужном листе
Лист = Книга . Worksheets ( 1 );

// Чтение значения ячейки, обычно здесь располагается цикл обхода ячеек
ЗначениеЯчейки = Лист . Cells ( НомерСтроки , НомерКолонки ). Value ;

// Закрытие книги
Книга . Application . Qui t ();

Для обхода всех заполненных строк листа Excel можно использовать следующие приемы:

// —— ВАРИАНТ 1 ——
КоличествоСтрок = Лист . Cells ( 1 , 1 ). SpecialCells ( 11 ). Row ;
Для НомерСтроки = 1 По КоличествоСтрок Цикл
ЗначениеЯчейки = Лист . Cells ( НомерСтроки , НомерКолонки ). Value ;
КонецЦикла;

// —— ВАРИАНТ 2 ——
НомерСтроки = 0 ;
Пока Истина Цикл
НомерСтроки = НомерСтроки + 1 ;
ЗначениеЯчейки = Лист . Cells ( НомерСтроки , НомерКолонки ). Value ;
Если НЕ ЗначениеЗаполнено ( ЗначениеЯчейки ) Тогда
Прервать;
КонецЕсли;
КонецЦикла;

Вместо последовательного обхода всех строк листа можно выгрузить все данные в массив и работать с ним. Такой подход будет быстрее при чтении большого объема данных:

ВсегоКолонок = Лист . Cells ( 1 , 1 ). SpecialCells ( 11 ). Column ;
ВсегоСтрок = Лист . Cells ( 1 , 1 ). SpecialCells ( 11 ). Row ;

Область = Лист . Range ( Лист . Cells ( 1 , 1 ), Лист . Cells ( ВсегоСтрок , ВсегоКолонок ));
Данные = Область . Value . Выгрузить ();

В таблице ниже приведены наиболее востребованные свойства и методы для работы с Excel через OLE:

Для того, чтобы узнать какое свойство нужно менять или какой метод вызвать можно воспользоваться макросами Excel. Если записать макрос с требуемыми действиями, то после можно посмотреть программный код на VBA записанного макроса.

Использование COMSafeArray

При выгрузке больших объемов данных из 1С в Excel для ускорения можно использовать объект COMSafeArray. Согласно определению из синтакс-помощника, COMSafeArray — объектная оболочка над многомерным массивом SafeArray из COM. Позволяет создавать и использовать SafeArray для обмена данными между COM-объектами. Проще говоря, это массив значений, который можно использовать для обмена между приложениями по технологии OLE.

// Создание COMSafeArray
МассивКом = Новый COMSafeArray ( «VT_Variant» , ВсегоКолонок , ВсегоСтрок );
// Заполнение COMSafeArray
Для Стр = 0 По ВсегоСтрок — 1 Цикл
Для Кол = 0 По ВсегоКолонок — 1 Цикл
МассивКом . SetValue ( Кол , Стр , Значение );
КонецЦикла;
КонецЦикла;
// Присвоение области листа Excel значений из COMSafeArray
Лист . Range ( Лист . Cells ( 1 , 1 ), Лист . Cells ( ВсегоСтрок , ВсегоКолонок )). Value = МассивКом ;

Обмен через ADO

Файл Excel при обмене через ADO представляет собой базу данных, к которой можно обращаться при помощи SQL-запросов. Установка MS Excel не требуется, но обязательно наличие драйвера ODBC, при помощи которого будет осуществляться доступ. Используемый драйвер ODBC определяется при указании строки соединения к файлу. Обычно требуемый драйвер уже установлен на компьютере.

Обмен через ADO заметно быстрее обмена через OLE, но при выгрузке нет возможности использовать функционал Excel для оформления ячеек, разметки страниц, задания формул и т.д.

Пример выгрузки:

// Создание COM-объекта для соединения
Соединение = Новый COMОбъект ( «ADODB.Connection» );

// Установка строки соединения
Соединение . ConnectionString = «
|Provider=Microsoft.ACE.OLEDB.12.0;
|Data Source=» + ИмяФайла + «;
|Extended Properties=»»Excel 12.0 XML;HDR=YES»»;» ;
Соединение . Open (); // Открытие соединения

// Создание COM-объекта для команды
Команда = Новый COMОбъект ( «ADODB.Command» );
Команда . ActiveConnection = Соединение ;

// Присвоение текста команды для создания таблицы
Команда . CommandText = «CREATE TABLE [Лист1] (Колонка1 char(255), Колонка2 date, Колонка3 int, Колонка4 float)» ;
Команда . Execute (); // Выполнение команды

// Присвоение текста команды для добавления строки таблицы
Команда . CommandText = «INSERT INTO [Лист1] (Колонка1, Колонка2, Колонка3, Колонка4) values (‘абвгдеё’, ‘8/11/2017’, ‘12345’, ‘12345,6789’)» ;
Команда.Execute(); // Выполнение команды

// Удаление команды и закрытие соединения
Команда = Неопределено;
Соединение . Close ();
Соединение = Неопределено ;

Для создания нового листа и формирования его структуры можно воспользоваться объектами ADOX.Catalog и ADOX.Table. В этом случае код примет вид:

// Создание COM-объекта для работы с книгой
Книга = Новый COMОбъект ( «ADOX.Catalog» );
Книга . ActiveConnection = Соединение ;

// Создание COM-объекта для работы со структурой данных на листе
Таблица = Новый COMОбъект ( «ADOX.Table» );
Таблица . Name = «Лист1» ;
Таблица . Columns . Append ( «Колонка1» , 202 );
Таблица . Columns . Append ( «Колонка2» , 7 );
Таблица . Columns . Append ( «Колонка3» , 5 );
Таблица . Columns . Append ( «Колонка4» , 5 );

// Создание в книге листа с описанной структурой
Книга . Tables . Append ( Таблица );
Таблица = Неопределено;
Книга = Неопределено;

В приведенном примере в методе

Таблица . Columns . Append ( «Колонка1» , 202 );

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

  • 5 — adDouble;
  • 6 — adCurrency;
  • 7 — adDate;
  • 11 — adBoolean;
  • 202 — adVarWChar;
  • 203 — adLongVarWChar.

Пример чтения:

// Создание COM-объекта для соединения
Соединение = Новый COMОбъект ( «ADODB.Connection» );

// Установка строки соединения
Соединение . ConnectionString = «
|Provider=Microsoft.ACE.OLEDB.12.0;
|Data Source=» + ИмяФайла + «;
|Extended Properties=»»Excel 12.0 XML;HDR=YES»»;» ;
Соединение . Open (); // Открытие соединения

// Создание COM-объекта для получения выборки
Выборка = Новый COMОбъект ( «ADODB.Recordset» );
ТекстЗапроса = «SELECT * FROM [Лист1$]» ;

// Выполнение запроса
Выборка . Open ( ТекстЗапроса , Соединение );

// Обход результата выборки
Пока НЕ Выборка . EOF () Цикл
ЗначениеКолонки1 = Выборка . Fields . Item ( «Колонка1» ). Value ; // Обращение по имени колонки
ЗначениеКолонки2 = Выборка . Fields . Item ( 0 ). Value ; // Обращение по индексу колонки
Выборка . MoveNext ();
КонецЦикла;

Выборка . Close ();
Выборка = Неопределено;
Соединение . Close ();
Соединение = Неопределено;

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

  • YES — первая строка воспринимается как названия колонок. К значениям можно обращаться по имени и по индексу колонки.
  • NO — первая строка воспринимается как данные. К значениям можно обращаться только по индексу колонки.

В приведенных примерах рассмотрено лишь несколько объектов ADO. Объектная модель ADO состоит из следующих объектов:

Выгрузка без программирования

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

Для сохранения табличного документа (например, результата отчета) можно вызвать команду Сохранить или Сохранить как… главного меню.

В открывшемся окне требуется выбрать каталог, имя и формат сохраняемого файла.

Для сохранения данных динамических списков (например, списка номенклатуры) необходимо:

  1. Вывести данные в табличный документ при помощи команды Еще ⇒ Вывести список…;
  2. Сохранить табличный документ в требуемый формат.

Остались вопросы?
Спросите в комментариях к статье.

Инструкция по загрузке из Excel в 1C для программиста

Нужно загрузить большой объем данных из Экселя в 1С? Настроить постоянную загрузку накладных и товаров? Обратитесь к нам! Оставьте заявку в форме выше или позвоните нам по телефону +7 499 350 29 00.

Несомненно, одним из самых распространенных способов массового занесения информации в информационную систему является загрузка информации в 1С 8.3 из Excel. Ниже я рассмотрю пошаговое создание обработки загрузки данных и дам ссылку на скачивание её.

Данная инструкция написана для программистов, для пользователей 1С скорее подойдут следующие статьи:

  • Загрузка данных штатными методами в 1С — Загрузка из Excel на примере прайс-листа.
  • Если вам необходимо загрузить номенклатуру в 1С — пример загрузки товаров в 1С 8.3.

Самые популярные данные, которые требуются для загрузки в 1С из Excel:

  • номенклатура и её штрих-коды, в том числе с картинками;
  • накладные от поставщика;
  • прайс-листы от контрагентов;
  • начальные остатки по складам или бухгалтерскому учету.

MS Excel — программа для работы с электронными таблицами, созданная компанией Microsoft. Она предоставляет возможности экономико-статистических расчетов, графические инструменты и язык макропрограммирования VBA. Excel является одним из наиболее популярных приложений в мире.

Обработку мы будем делать универсальную, подобно той, что мы делали для загрузки файлов csv в 1С.

1. Создание обработки загрузки и её формы

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

2 Обработчик выбора файла

Тут всё сделаем как в загрузке из CSV:

При нажатии на выбор файла необходимо отображать диалог выбора файла в ОС. Сделать это очень просто. Необходимо на обработчик “НачалоВыбора” в поле ввода файла указать следующий код:

3 Обработка данных из Excel и загрузка в 1С 8.3

Для начала мы очищаем информацию в таблице, находим файл на диске, подключаемся к Excel через COM-объект и проводим все технические настройки:

Получите 267 видеоуроков по 1С бесплатно:

После того как мы получили все данные из файла, мы считываем 1 строку файла и генерируем колонки на форме с наименованиями из файла:

После формирования колонок считываем тело файла эксель и построчно заносим данные в табличное поле:

В результате мы получаем загруженные данные из excel c такими же колонками:

Не забудьте закрыть Эксель в 1С после выполнения процедур с помощью кода:

Универсальную загрузку из excel в 1C можно бесплатно скачать тут.

Обратите внимание, эта обработка написана в режиме «обычного приложения 1С».

Если у Вас всё же возникли проблемы с загрузкой, Вы можете обратиться за помощью к опытному программисту 1C.

Видеоурок по загрузке товаров в 1С из файлов Excel:

Если Вы начинаете изучать 1С программирование, рекомендуем наш бесплатный курс (не забудьте подписаться на YouTube — регулярно выходят новые видео):

К сожалению, мы физически не можем проконсультировать бесплатно всех желающих, но наша команда будет рада оказать услуги по внедрению и обслуживанию 1С. Более подробно о наших услугах можно узнать на странице Услуги 1С или просто позвоните по телефону +7 (499) 350 29 00. Мы работаем в Москве и области.

Обучение программированию на 1С

Использование COM-соединения, выгрузка в Excel через COM-объект Excel.Application

Вообще COM-объекты используют для соединения информационной базы 1С с файлом Word, Excel, Outlook или любой другой программой, поддерживающей данный интерфейс обмена данными. В этой статье рассмотрим задачу выгрузки/загрузки данных из/в MS Excel. Чтобы это осуществить воспользуемся COM-соединением и объектом Excel.Application. Для примера возьмём задачу выгрузки/загрузки данных о номенклатуре. Пример рассмотрим ниже.

COM-соединение

Что же такое COM-соединение? Component Object Model (или COM) – это технология (фирмы Microsoft) взаимодействующих компонентов, которые одновременно могут быть использованы в разных приложениях. При этом весь функционал соответствующего компонента наследуется внутрь разрабатываемого приложения. В нашем случае COM-объект Excel.Application используется внутри кода 1С для операций с файлом книги MS Excel.

Объект Excel.Application

У объекта Excel.Application существует ряд методов, которые нам могут пригодиться для реализации нижепоставленной задачи:

  • ОбъектExcel.WorkBooks.Open(ИмяФайла) – Открытие книги MS Excel
  • ОбъектExcel.ActiveWorkbook.Close() – Закрытие текущей книги
  • ОбъектExcel.Quit() – Закрытие COM-объекта
  • ОбъектExcel.Sheets(ИмяЛиста) – Получает лист книги
  • ЛистExcel.Cells(НачалоСтрока, НачалоСтолбец) – Ячейка таблицы на данном листе
  • ЛистExcel.Range(Ячейка1, Ячейка2) – Выделенная область
  • ЯчейкаExcel.Value – Значение ячейки таблицы
  • ЯчейкаExcel.Text – Текст ячейки таблицы

Постановка задачи

Итак, предположим, что в обработке 1С у нас имеется табличная часть, состоящая из следующих колонок:

Необходимо реализовать 2 функционала (сделать на форме 2 основные кнопки):

  1. Выгрузка табличной части в подготовленный файл MS Excel
  2. Загрузка табличной части из файла.

Алгоритм выгрузки/загрузки в MS Excel

Алгоритм выгрузки следующий:

  1. Выгружаем табличную часть в таблицу значений
  2. Создаём новый COM-объект Excel.Application
  3. Выбираем файл, открываем файл книги MS Excel
  4. Переходим на заданный лист книги
  5. Выгружаем данные в файл
  6. Закрываем книгу, выходим из COM-объекта.

Алгоритм загрузки следующий:

  1. Создаём новый COM-объект Excel.Application
  2. Выбираем файл, открываем файл книги MS Excel
  3. Переходим на заданный лист книги
  4. Загружаем данные из файла в таблицу значений
  5. Закрываем книгу, выходим из COM-объекта
  6. Таблицу значений выгружаем в табличную часть.

Операция выгрузки и загрузки данных о номенклатуре происходит в заранее подготовленный шаблон MS Excel.

Пример кода 1С

Код 1С я постарался разделить на отдельные функции, чтобы, скопировав, с ними можно было работать где угодно. На форме обработки 1С были созданы 3 кнопки:

В итоге в реализации алгоритма получились следующие основные процедуры и функции 1С:

  • ПолучитьExcel – Получает COM-объект MS Excel;
  • ЗакрытьExcel – Закрывает использование COM-объекта MS Excel;
  • ПолучитьЛистExcel – Получает лист книги Excel;
  • ДобавитьТабличныйДокументВExcel – Добавляет табличный документ на лист Excel (нужно для выгрузки данных);
  • ПрочитатьОбластьИзExcel – Читает область ячеек с листа Excel (нужно для загрузки данных);
  • ШиринаЛистаExcel – Ширина листа Excel;
  • ВысотаЛистаExcel – Высота листа Excel;
  • ПреобразоватьТДвТЗ – Преобразует табличный документ в таблицу значений;
  • ПреобразоватьТЗвТД – Преобразует таблицу значений в табличный документ;

Для начала приведу вспомогательную функцию для открытия/сохранения файла на диске:

Грамотная загрузка из Excel в 1С

Многим 1сникам не понятен единственно верный путь чтения Excel-файлов.

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

Файлы Excel, пользователь, кстати, может открыть и посмотреть в 1С.

Но у метода Прочитать есть недостаток — он криво воспринимает некоторые даты, как ни читай — текстом или значениями. Будем надеяться, со временем эти банальные глюки 1с все же починят. А пока надо применять свои руки, т.е. читать через старый добрый Excel.Application, методика позволяет также читать книги с несколькими листами.

Важно, что читать нужно на клиенте. На сервере Excel запускается от имени пользователя, под которым работает служба 1С-сервера, на практике в 50% случаев админы не могут корректно настроить работу COM-приложения. Поэтому лучше прочитать весь файл на клиенте и передать его в виде массива структур.

Привожу пример чтения обычного файла, содержащего 12 листов с накладными среднего размера. 6 409 ячеек прочитались за 25 секунд. 256 ячеек в секунду — не быстро, но все же быстрее чем ручками?

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

Ну и еще один штрих — многие пользователи работают в терминале и не ставят на сервер Excel по лицензионным соображениям. С файлами Excel может работать и Open Office, в том числе и по COM-объекту. Так что в планах написать функцию загрузки для Open Office.

Текст функции под катом:

Функция ЗагрузитьExcelФайлВМассивЛистов ( ИмяФайла , ЗНАЧ СоответствиеФильтраЛистов = Неопределено)
//СоответствиеФильтраЛистов — соответствие содержащее числа с номерами листа (начиная с единицы)
//Или названия листов в виде строки.
//Возвращается массив структур с полями:
// Номер — номер листа с единицы
// Имя — имя листа строкой
// Строк — количество строк
// Колонок — количество колонок
// Ячейки — двумерный массив колонок, первое измерение — строка, второе — колонка

МассивЛистов = Новый Массив (); //Это масс

Эксель = New COMObject ( «Excel.Application» );
Эксель . visible = false;
Книга = Эксель . Workbooks . Open ( ИмяФайла );

Для НомерЛиста = 1 To Книга . Sheets . Count Цикл
Лист = Книга . Sheets ( НомерЛиста );
ИмяЛиста = Лист . Name ;
Если СоответствиеФильтраЛистов <> Неопределено Тогда
Если СоответствиеФильтраЛистов [ НомерЛиста ] = Неопределено
И СоответствиеФильтраЛистов [ ИмяЛиста ] = Неопределено
Тогда
Продолжить; //Если лист грузить не надо
КонецЕсли;
КонецЕсли;

КоличествоСтрок = Лист . UsedRange . Rows . Count ;
КоличествоКолонок = Лист . UsedRange . Columns . Count ;

Ячейки = Новый Массив ( КоличествоСтрок , КоличествоКолонок );
Для Строка = 1 По КоличествоСтрок Цикл
Для Колонка = 1 По КоличествоКолонок Цикл
Ячейки [ Строка — 1 ][ Колонка — 1 ] = Лист . Cells ( Строка , Колонка ). Value ;
КонецЦикла;
КонецЦикла;

СтруктураЛиста = Новый Структура ();
СтруктураЛиста . Вставить ( «Строк» , КоличествоСтрок );
СтруктураЛиста . Вставить ( «Колонок» , КоличествоКолонок );
СтруктураЛиста . Вставить ( «Ячейки» , Ячейки );
СтруктураЛиста . Вставить ( «Номер» , НомерЛиста );
СтруктураЛиста . Вставить ( «Имя» , ИмяЛиста );

МассивЛистов . Добавить ( СтруктураЛиста );

Источник

Понравилась статья? Поделить с друзьями:

А вот еще интересные статьи:

  • Работа с excel на делфи
  • Работа с excel на windows phone
  • Работа с excel макетами
  • Работа с excel как с базой данных практические задания
  • Работа с excel как с базой данных практическая работа

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии