Работа с excel из с

Блог Виктора Деревянко

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

среда, 20 мая 2009 г.

Работа с Excel из C#.

Столкнулся с необходимостью поработать с файлами Excel из C#. Задачи простые — считать данные из книги Excel, создать новую книгу Excel, внести в нее данные. Предполагается, что Excel на компьютере имеется, но какой версии, не известно. Файлы по размерам маленькие, скорость работы не критична.

Существует несколько вариантов работы с Excel из C#: автоматизация Excel, подключение через OleDB/ODBC, дополнительные библиотеки (Aspose Excel), работа через XML, через Open XML и т.п.

Наиболее простой вариант — воспользоваться автоматизацией Excel. Да, скорость работы — не блеск. Зато удобно использовать, код пишется быстро, объемы кода не велики. Из .NET автоматизация подключается парой кликов мыши — добавил в References сборку Microsoft.Office.Interop.Excel — и работай с привычными com-объектами Application,Workbook, Worksheet и т.п.

Проблема одна — сборки «Microsoft.Office.Interop.Excel» для каждой версии Excel разные. У меня установлен office 2003 и, соответственно, interop-сборка версии 11. А что делать, если мне нужно разработать приложение, которое может работать с Excel 97? Или с любыми версиями Excel? Для того, чтобы считать или записать пару значений в ячейки листа Excel сложного api не требуется — любой Excel сгодится. А мне приходится привязываться к конкретной версии.

Можно воспользоваться поздним связыванием. Но если его использовать «в лоб», то ни о никаком удобстве работы речи уже не идет — код станет сложным, а вызовы методов — косвенными, нетипизированными. Ошибок будет — вагон.

Выход нашелся. На СodeProject обнаружил замечательную статью «SafeCOMWrapper — Managed Disposable Strongly Typed safe wrapper to late bound COM». В ней изложена элегантная методика использования позднего связывания, устраняющая все проблемы: связывание становится поздним (привязки к конкретной версии Excel нет), для объектов автоматизации автоматически реализуется шаблон IDisposable (отработанные объекты уничтожаются автоматически), все вызовы методов явные.

Идея реализации, вкратце, следующая. Для каждого COM-объекта автоматизации вы прописываете отдельный интерфейс. В интерфейс включаете все методы и свойства, которые вам необходимо использовать у этого COM-объекта. Обратите внимание — только те, которые необходимо использовать, а вовсе не все, реализуемые COM-объектом.

Каждый COM-объект автоматизации «заворачивается» в класс COMWrapper, унаследованный от RealProxy. RealProxy — это стандартный класс, позволяющий организовать перехват вызовов методов. При создании COM-объекта вы создаете экземпляр COMWrapper и указываете требуемый вам интерфейс. Среда динамически генерирует прокси-объект, реализующий этот интерфейс. С этим прокси-объектом вы в дальнейшем и работаете как с объектом автоматизации. Вызов любого метода прокси-объетка перехватывается и транслируется в вызов метода Invoke класса RealProxy, перекрытый в классе COMWrapper. Здесь его можно обработать как душе угодно. В реализации по умолчанию, вызовы свойств транслируются в вызовы соответствующих методов get_ и set_, создаваемых .NET, возвращаемые объекты автоматизации автоматически заворачиваются в COMWrapper и т.п.

В оригинально статье приведен пример использования данной методики для Microsoft.Outlook. Я, на базе приведенных исходных кодов, адаптировал методику для работы с Microsoft.Excel. Естественно, реализовав интерфейсы только для тех объектов автоматизации, которые потребовались мне для работы. Дополнить интерфейсы недостающими методами или добавить интерфейсы для других объектов автоматизации не составляет труда. Достаточно посмотреть через Object Browser метаданные сборки «Microsoft.Office.Interop.Excel» и скопировать оттуда необходимые объявления методов и интерфейсов (с минимальной адаптацией).

Пример кода для работы с Excel: Оригинальные исходные коды лежат на codeproject вместе со статьей. Исходные коды, адаптированные для Microsoft Excel, можно взять здесь.

  • Download source codes of Managed Excel project
  • View source codes of Managed Excel project
  • Short description in English

Update. Практика использования выявила небольшую проблему — забыть вызвать Dispose у оберток объектов Excel очень легко. После чего Excel висит в памяти до тех пор, пока не закроешь приложение (или до следующей сборки мусора). Пример: вместо
Просто надо быть внимательнее.

Записная книжка программиста-новичка, C#, SQL, PHP и все-все-все

Я ведь это уже делал, но хрен теперь найдешь тот кусок кода, гуглим снова… Где бы найти простое и понятное руководство для начинающего, а не тонкости для мега-гуру?

Навигация по записям

Работа с Excel из C# через COM Interop, часть 1, открываем документ и выводим данные

Задача: вывести данные в таблицу Excel с красивым оформлением и открыть ее, чтобы пользователь мог напечатать или сохранить средствами самого Excel. Необходимо поддерживать все версии Office, начиная с 97, то есть вывод в новые xml-форматы Office 2007 и выше невозможен

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

Читать еще:  В эксель анализ данных

Таким образом задача сводится к следующему:

1) Подготовить файлы шаблонов для разных типов выборок (шапка, размер шрифта, перенос по словам, ширина столбцов)
2) Взять данные из программы и вывести их в нужное место шаблона (как правило это прямоугольный массив ячеек соответствующий DataTable)
3) Подключить к проекту dll-библиотеку Excel Microsoft.Office.Interop.Excel в раздел references и проставить параметр копирования библиотеки к скомпилированным файлами CopyLocal в True.
4) Написать класс для работы с Excel и вывести через него данные из таблицы

При такой постановки задачи «Но это же неэстетично! — Зато дешево, надежно и практично» сам класс работы с Excel получается достаточно простым, но превосходно выполняет поставленную задачу

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

В простейшем случае получим код вида

Само собой ничто не мешает в дальнейшем расширить этот класс методами для работы с оформлением, вроде

Такой метод вряд ли подойдет для больших и сложных корпоративных систем, разрабатываемых высокооплачиваемыми профессионалами, но может спасти программиста-новичка в случае требования начальства «сделать ВЧЕРА вывод отчета в excel в соответствии с требования клиента по оформлению».

О программировании и всём, всём, всём

воскресенье, 26 сентября 2010 г.

Работа с Excel из C# (забираем из Excel данные)

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

Сегодня я хотел бы поговорить о работе с Excel из C#.
Пара коментариев:
1. Я использую VS 2010 и Excel 2007.
2. Как я уже сказал, мне работа с Excel понадобилась в одном из проектов, поэтому примеры будут из этого проекта.

1. Для начала работы с Excel-ем, необходимо добавить ссылку на сборку Microsoft Excel 12.0 Object Library.
2. Подключаем с псевдонимом пространство имен:
using Excel = Microsoft.Office.Interop.Excel;
3. Загружаем в память новый экземпляр приложения:
4. Открываем ранее существующий документ:
excelApp.Workbooks.Open(_sourceFile.FullName);
А вот здесь, как уже увидили те кто раньше работали с Excel, радостная новость! Теперь c# поддерживает параметры по умолчанию! И никаких больше «миссинг валуе».
5. Свойство Workbooks, как это не странно, содержит открытые книги. Почитаем с первой страницы, первой открытой книги, строчки в список (да, я имею в виду именно первой, а не нулевой! Пользуясь случаем, передаю пламенный привет раработчикам Visual Basic, начинающих нуманацию элементов массива с 1).
Вот таким нехитрым способом, мы отмапили из экселевского файла ячеки в двухмерный список, с которым дальше можно и работать, не прибегая, к всяким жутким ухищрениям.
Да, чуть не забыл, проверка в while, проверяет есть ли хоть что-то в первом столбце в текущей строке, если есть, то добро пожаловать в мапинг. если нет, то файл кончился (ну у меня просто файл такой, у вас может быть другое условие разбора. Например, нужно перетащить наперед заданное число строк, или как нибудь еще).
6. Закрываем Excel

На этом вроде бы и все, но как говорится: Следите за рекламой! В следующей части покажу как генерировать сложную таблицу (объединение ячеек и все такое) с нуля.

З.ы. Спасибо, одному моему коллеге, который напомнил, что надо писать в блог, а то что-то за лето, я действительно, расслабился.

Работа с файлами 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. В этом случае код примет вид:

Читать еще:  Excel снять защиту с ячейки

// Создание 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 с помощью C# (Microsoft.Office.Interop.Excel)

Оставляю заметку по работе с Excel с помощью C#.

Привожу фрагменты кода, которые искал когда-то сам для работы с Excel документами.

Наработки очень пригодились в работе для формирования отчетности.

Прежде всего нужно подключить библиотеку Microsoft.Office.Interop.Excel.

Visual Studio здесь довольно старой версии. Если у вас версия новая, отличаться будет только вид окна.

Далее создаем псевдоним для работы с Excel:

using Excel = Microsoft.Office.Interop.Excel;

Расстановка рамок.

Расставляем рамки со всех сторон:

Цвет рамки можно установить так:

Выравнивания в диапазоне задаются так:

Определим задачу: получить сумму диапазона ячеек A4:A10.

Для начала снова получим диапазон ячеек:

Excel.Range formulaRange = sheet.get_Range(sheet.Cells[4, 1], sheet.Cells[9, 1]);

Далее получим диапазон вида A4:A10 по адресу ячейки ( [4,1]; [9;1] ) описанному выше:

string adder = formulaRange.get_Address(1, 1, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);

Теперь в переменной adder у нас хранится строковое значение диапазона ( [4,1]; [9;1] ), то есть A4:A10.

Выделение ячейки или диапазона ячеек

Так же можно выделить ячейку или диапазон, как если бы мы выделили их мышкой:

Авто ширина и авто высота

Чтобы настроить авто ширину и высоту для диапазона, используем такие команды:

Получаем значения из ячеек

Чтобы получить значение из ячейки, используем такой код:

Добавляем лист в рабочую книгу

Чтобы добавить лист и дать ему заголовок, используем следующее:

Добавление разрыва страницы

Сохраняем документ

Как открыть существующий документ Excel

Комментарии

При работе с Excel с помощью C# большую помощь может оказать редактор Visual Basic, встроенный в Excel.

Для этого в настройках ленты надо добавить пункт «Разработчик». Далее начинаем запись макроса, производим действия и останавливаем запись.

Далее заходим в редактор Visual Basic и смотрим код, который туда записался:

В данном макросе записаны все действия, которые мы выполнили во время его записи. Эти методы и свойства можно использовать в C# коде.

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

Так же во время работы может возникнуть ошибка: метод завершен неверно. Это может означать, что не выбран лист, с которым идет работа.

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
Adblock
detector