Microsoft office interop excel описание

«My way» с MS Excel

В компании, где я работаю, пользователи работают с данными с помощью MS Excel. Основным хранилищем информации является БД Oracle. Отсюда требуется:

Представление отчетов из базы данных в формате Excel
Забирать информацию в виде Excel файлов и заливать её в Oracle

Проблематика:

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

Как-то был случай, когда начальник мышкой выделял часть таблицы в Excel и копировал его на другую страницу, задавая мне вопрос – “Неужели так сложно сделать это же, только с копированием в базу? Ты же Профессионал!”. После того разговора я, какое-то время, чувствовал себя хомячком, которому дали каплю никотина, убившую лошадь. Но время шло, и с помощью MSDN и интернета я стал ближе с MS Excel, а ежедневные танцы с бубном вокруг него дали свой результат.

Существует множество способов чтения (записи) данных из (в) Excel, каждый вправе выбирать тот, который ему наиболее удобен, я же в этой статье решил рассказать вам о своём пути работы с ним:

Начинал я с Microsoft Excel ODBC Driver, использовал Microsoft Integration Services, писал макросы на VB. Так же использовал шаблоны с уже готовыми макросами и заставлял пользователя работать только с ними. Так же принимались попытки не использовать MS Excel при работе с базой, но они не нашли понимания.

Попытки уйти от использования MS Excel

К чему я пришёл:

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

Microsoft Reporting Services (только для выгрузки) Ссылка
Microsoft Office Interop Excel Ссылка
Microsoft Open XML SDK Ссылка
EPPlus Ссылка

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

Microsoft Reporting Services

Средство удобное, позволяет строить отчёты, используя множество различных источников данных и выгружать их в файлы различных форматов. Поддерживается выгрузка в Excel, интегрировано в MS Sharepoint, обладает неплохим редактором отчетов – MS Report Builder.

Microsoft Office Interop Excel

Позволяет работать с файлами *.xls и *.xlsx. В компании, где я работаю, используется для загрузки данных из файлов MS Excel 2003 в базу. Так же данное средство может извлекать данные из файлов с расширением *.xlsx (формат Microsoft Office Open XML).

Читать еще: Закончилась подписка на office 365

Microsoft Open XML SDK

Применяется для выгрузки в Excel в формате *.xlsx (Microsoft Office Open XML). Для обеспечения быстродействия и поддержки возможности выгружать большие объёмы данных работа с Microsoft Office Open XML ведётся с помощью Simple API for XML (SAX) Ссылка.

EPPlus

EPPlus позволяет загружать и выгружать данные в формат *.xlsx. Его преимущество перед Open XML SDK – более дружественное API и меньшая трудоемкость. Он гораздо более удобен в работе нежели Open XML SDK. На данный момент он используется в компании в тех случаях, где не обязательно использовать Simple API for XML (SAX).

Заключение

Примеры кода на C# получились очень большими, поэтому я решил Заключение написать перед ними.
В данной статье я хотел поделиться опытом работы с excel файлами, т.к. на мой взгляд, это не совсем тривиальная задача и довольно распространенная. Изначально я хотел описать в деталях все тонкости работы перечисленных выше инструментов, но в итоге решил, что примеры кода будут более полезны.
Спасибо всем, кто смог прочитать всё, о чём я писал выше!

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

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

Архивы

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

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

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

Читать еще: Как обновить microsoft office 2003

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

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

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

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

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

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

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

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

Я, наверно, буду голосом большинства когда спрошу. ГДЕ ССЫЛКА НА ВТОРУЮ ЧАСТЬ ?

Необходимо поддерживать все версии Office, начиная с 97, то есть вывод в новые xml-форматы Office 2007 и выше невозможен
И где же решение данной части задачи?

Официальное трудоустройство, работа через интернет.

.NetBlog — блог о программировании на C# .Net, и других, не мене интересных вещах.

Заметки о разработке ПО, советы по .net, SharePoint, SQL, архитектура приложений, литература по программированию. А также просто просто мысли о жизни и рассказы о том, что интересно автору, например путешествиях и вкусной еде 😉

Читать еще: Microsoft office visual basic

суббота, 11 октября 2014 г.

COM Interop на примере работы с Excel

В предыдущем посте я писал о создании csv-файла и экспорта в него данных из ASP.NET и при этом, перечисляя разные варианты, не упомянул один совсем уже бредовый для web-приложения, но частенько очень подходящий для приложений десктопных. Это работа с установленным в системе COM-объектом c помощью технологии под названием COM Interop.

COM Interop используется в .Net для предоставления возможности взаимодействия управляемого .Net кода с COM-объектами. Тля того чтобы использовать какой-либо COM-объект из управляемого кода, необходимо создать сборку, содержащую информацию о типах содержащихся в COM-библиотеке, в совместимом с CRL формате.

В процессе работы приложения CLR создает для каждого COM-объекта внутренний объект, называемый Runtime Callable Wrapper (Вызываемая оболочка времени выполнения) или RCW, которая используется для создания COM-объекта и маршалинга данных между управляемой и неуправляемой средой. Также, RCW используется для мониторинга количества активных ссылок на COM-объект и его уничтожение, когда количество активных ссылок станет равным нулю. Выглядит это примерно так:

Обычно, если вы ходите создать CLR библиотеку для какого-либо компонента самостоятельно, то вам нужно использовать утилиту Tlbimp.exe, но для использования компонентов Office этого делать не нужно. Все необходимые библиотеки уже устанавливаются вместе с продуктом. Нам достаточно только добавить нужную сборку в проект.

И добавить соответствующую строку using в ваш код

using Excel = Microsoft.Office.Interop.Excel;

В качестве источника данных для примера будем использовать список массивов строк ( List ). В коде я написал комментарии, которые, думаю, будут вполне понятно объяснять что происходит.

Ну и пара нюансов с которыми вы можете столкнуться. В случае, если в COM-компоненте происходит исключение, которое вы не обработаете, то, как и положено, весть домен приложения будет аварийно закрыт. Вместе с RCW. В результате чего, COM-объект так и останется висеть в памяти. То есть, вполне реально получить вот такую картинку:

И нюанс второй. Код сверху не совсем правильный. Дело в том, что работая с COM Interop нужно быть предельно внимательным и осторожным, потому, что случайно можно создать COM-объект, совершенно об этом не подозревая. В данном случае, создается даже не один «случайный» объект, а несколько.
Во-первых, в строке xlsWB = App.Workbooks.Add(missingValue); создается объект App.Workbooks, который не присваевается переменной и, соответственно, не освобожается дл тех пор, пока не выгрузится домен приложения.
Во-вторых, такая же проблема в строке в строке: xlsSheet = (Excel.Worksheet)xlsWB.Worksheets.get_Item(1);

Для того, чтобы эту проблему решить необходимо добавить две новые переменные и немного изменить код инициализации: