Visual studio работа с excel

Работа с 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 столбца, и т.п. Пример:

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

Visual studio работа с excel

Описывается C#-программа вывода отчета в Excel.
Данные берутся из xml-файла, часть которого показана ниже:

AAM Updates Notifier

1
23.11.2016 3:15:24.824
23.11.2016 3:15:24.825
157798,765625 B

0
23.11.2016 3:15:27.90
23.11.2016 3:15:27.90
323631,9375 B

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

Рис. 1. Сводный интернет-трафик

Программа реализована как Windows Forms Application.
Файл с данными явно указывается в программе, поэтому форма содержит только дну кнопку (рис. 2).

Рис. 2. Форма приложения

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

Создав проект, в окне Solution Explorer выполнить:
правая кнопка мышки на References — Add Reference — .в окне Reference Manager выбрать Assemblies — Extensions — отметить флажком Microsoft.Office.Interop.Excel — OK (рис. 3. и рис. 4).

Рис. 3. Solution Explorer — References — Add Reference

Рис. 4. Reference Manager — Assemblies — Extensions — Microsoft.Office.Interop.Excel

В use-раздел формы приложения добавить:

using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

Порядок формирования отчета

1. Загрузить xml-файл в DataSet portionsDS.
2. Сформировать по данным portionsDS список prtnsList. Элементом списка является структура prtnCrrnt (тип prtn).
3. Сформировать по списку prtnsList три выборки из списка:
   • группировка prtnsListGrouped по именам процессов с сортировкой по имени — позволяет вывести список всех процессов;
   • группировка prtnsListGrouped_0 по именам процессов, принимающих данные, с сортировкой по имени и с итогами по объему данных и количеству порций данных;
   • группировка prtnsListGrouped_1 по именам процессов, передающих данные, с сортировкой по имени и с итогами по объему данных и количеству порций данных;
4. Сформировать, используя полученные выборки (группировки), массивы имен
   • всех процессов (arrPrcAll);
   • процессов, принимающих данные (arrPrcIn);
   • процессов, передающих данные (arrPrcOut).
5. Сформировать, используя полученные выборки (группировки), массивы с итоговыми объемом и числом порций для входящих и исходящих данных — соответственном массивы prtnInTotal, mntInTotal и prtnOutTotal, mntOutTotal.
6. Вывести, обращаясь к сформированным массивам, отчет с итогами — сводный интернет-трафик (см. рис. 1)

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

Используемые объекты, методы и свойства

Загрузка xml-файла выполняется методом ReadXml объекта типа DataSet (portionsDS.ReadXml(fileName);).
Результат portionsDS содержит коллекцию таблиц Tables (DataTableCollection tbls = portionsDS.Tables;), обнаруженных в xml-файле.
Нас интересует первая таблица коллекции (DataTable tbl = tbls[0];), из которой извлекаем коллекцию ее строк:

DataRowCollection portions = tbl.Rows;

После завершения обработки коллекции строк таблицы ресурсы, выделенные под portionsDS, освобождаются (portionsDS.Dispose();).
Выборки из списка prtnsList, полученного по данным, загруженным в portionsDS, осуществляются с использованием методов Where, GroupBy, Select и OrderBy.
Данные массивов с именами процессов и итогами выбираются из списка prtnsList методами Select и ToArray.
Очистка списка prtnsList выполняется методом Clear (prtnsList.Clear();).
Созданный объект xlApp типа Excel.Application позволяет плучить получить книгу Excel (Excel.Workbook xlWorkBook;), которая используется для получения первого листа этой книги:

Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

Для работы с ячейками листа создаются объекты типа Excel.Range:

Excel.Range Cells = xlWorkSheet.Cells;
Excel.Range range = xlWorkSheet.Range[«B1:B1», System.Type.Missing];
и другие.

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

• высота текста Font.Size — Cells[1, 2].Font.Size = 14;
• горизонтальное выравнивание HorizontalAlignment — Cells[i, j].HorizontalAlignment = …;
• ширина столбцов ColumnWidth — range.EntireColumn.ColumnWidth = 7;
• границы ячейки Borders — Cells[2, 2].Borders(1).ColorIndex = 1; // Граница слева
• объединение ячеек Merge — range = xlWorkSheet.get_Range(«D2:E2»); range.Merge(Type.Missing);
• перенос текста WrapText — Cells[i + 4, 3].WrapText = true;

Заметим, что изменение Style.Font.Size, например Cells[1, 2].Style.Font.Size = 14;, отразится на всех ячейках с текстом стиля Style.
Готовый отчет наблюдаем, обращаясь к свойству Visible объекта xlApp:

Реализация

Чтение xml-файла, формирование вспомогательных объектов и отчета обеспечивает следующий код:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication3
<
public partial class Form1 : Form
<
// Порция для отчетов История и Итоги
public struct prtn
<
public string pNm;
public int pTp;
public string sTime, cTime;
public Int64 size;
public int mnt;
public prtn(string pNm2, int pTp2, string sTime2, string cTime2, Int64 size2, int mnt2)
<
pNm = pNm2;
pTp = pTp2;
sTime = sTime2;
cTime = cTime2;
size = size2;
mnt = mnt2;
>
>
// Список порций для отчета Итоги. Формируется в LoadXML()
public List

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

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

суббота, 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);

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

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

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

среда, 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 висит в памяти до тех пор, пока не закроешь приложение (или до следующей сборки мусора). Пример: вместо
Просто надо быть внимательнее.

Как создать Excel (.XLS и .XLSX) файл в C# без установки Ms Office?

Как я могу создать таблицу Excel с C# без необходимости установки Excel на машине, на которой выполняется код?

25 Ответов

Вы можете использовать библиотеку под названием ExcelLibrary. Это бесплатная библиотека с открытым исходным кодом, размещенная на Google Code:

Это похоже на порт PHP ExcelWriter, который вы упомянули выше. Он еще не будет писать в новый формат .xlsx,но они работают над добавлением этой функциональности.

Он очень прост, мал и прост в использовании. Кроме того, он имеет DataSetHelper, что позволяет использовать DataSets и DataTables для легкой работы с данными Excel.

ExcelLibrary, похоже, все еще работает только для более старого формата Excel (.xls-файлы), но в будущем может быть добавлена поддержка более новых форматов 2007/2010.

Вы также можете использовать EPPlus, который работает только для файлов формата Excel 2007/2010 (.файлы xlsx). Есть также NPOI , который работает с обоими.

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

Кроме того, как отмечено @АртёмЦарионов ниже, EPPlus имеет поддержку для Pivot таблиц, а ExcelLibrary может иметь некоторую поддержку ( проблема Pivot таблицы в ExcelLibrary )

Вот несколько примеров кода для ExcelLibrary:

Вот пример взятия данных из базы данных и создания из нее рабочей книги. Обратите внимание, что код ExcelLibrary-это единственная строка внизу:

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

Если вы довольны форматом xlsx, попробуйте мой проект codeplex GitHub. EPPlus . Начал он с источника из ExcelPackage, но сегодня это полная переписка. Поддерживает диапазоны, стиль ячеек, диаграммы, фигуры, картинки, namesranges, автофильтр и многое другое.

Я с успехом использовал следующие проекты с открытым исходным кодом:

ExcelPackage для форматов OOXML (Office 2007)

Формат NPOI для .XLS (Office 2003). NPOI 2.0 (бета) также поддерживает XLSX.

Взгляните на мои записи в блоге:

А как насчет использования Open XML SDK 2.0 для Microsoft Office?

• Не требует установки Office
• Сделано Microsoft = достойная документация MSDN
• Только один .Net dll для использования в проекте
• SDK поставляется со многими инструментами, такими как diff, валидатор и т. д

Вы можете использовать OLEDB для создания и управления файлами Excel. Проверьте это: чтение и запись Excel с помощью OLEDB .

EDIT — еще несколько ссылок:

Коммерческое решение, SpreadsheetGear для .NET сделает это.

Вы можете посмотреть живые образцы ASP.NET (C# и VB) здесь и загрузить ознакомительную версию здесь .

Отказ от ответственности: я владею SpreadsheetGear LLC

Несколько вариантов я уже использовал:

Если XLSX-это обязательно: ExcelPackage -хороший старт, но он умер, когда разработчик перестал работать над ним. ExML взял оттуда и добавил несколько функций. ExML -неплохой вариант, я все еще использую его в нескольких производственных веб-сайтах.

Однако для всех моих новых проектов я использую NPOI, порт .NET Apache POI . NPOI 2.0 (Альфа) также поддерживает XLSX.

Чрезвычайно легким вариантом может быть использование таблиц HTML. Просто создайте теги head, body и table в файле и сохраните его как файл с расширением .xls. Существуют определенные атрибуты Microsoft, которые можно использовать для оформления выходных данных, включая формулы.

Я понимаю, что вы, возможно, не кодируете это в веб-приложении, но вот пример композиции файла Excel через таблицу HTML. Этот метод можно использовать, если вы кодируете консольное приложение, настольное приложение или службу.