C создание таблицы excel
I have some tabular data that I'd like to turn into an Excel table.
Information about the data:
- A couple million rows
- 5 columns, all strings (very simple and regular table structure)
- In my script I'm currently using a nested List data structure but I can change that
- Performance of the script is not critical
Searching online gives many results, and I'm confused whether I should use OleDb, ADO RecordSets, or something else. Some of these technologies seem like overkill for my scenario, and some seem like they might be obsolete.
What is the very simplest way to do this?
Edit: this is a one-time script I intend to run from my attended desktop.
The easiest way involves a decent 3rd-part library :-) Look for "excel automation". There should be some KB articles on MSDN and such. Be aware that using the Excel COM interops are significantly slower than most/all 3rd-party tools and expect to run in a non-service environment, etc. There may also be problems with dealing with "a couple million rows" (never tried anywhere near these limits!), even excluding the additional resource/time overhead -- not criticial doesn't exclude "sometime today" :)
Also, for such a simple dump, consider CSV -> Excel (manually or via automation) instead of adding all the rows one-at-a-time (a couple of million is a couple of million!). Another option is dumping straight to XSLX (XML, just grab "a template") via a streaming writer, etc. Of course these step is just not present in a 3rd-party library.
6 Answers 6
Avoid using COM interop at all costs. Use a third-party API. Really. In fact, if you're doing this server-side, you virtually have to. There are plenty of free options. I highly recommend using EPPlus, but there are also enterprise-level solutions available. I've used EPPlus a fair amount, and it works great. Unlike interop, it allows you to generate Excel files without requiring Excel to be installed on the machine, which means you also don't have to worry about COM objects sticking around as background processes. Even with proper object disposal, the Excel processes don't always end.
I know you said you want to avoid third-party libraries, but they really are the way to go. Microsoft does not recommend automating Office. It's really not meant to be automated anyway.
However, you may want to reconsider inserting "a couple million rows" into a single spreadsheet.
В современном мире разработки приложений нередко встает необходимость работы с Excel документами. Чаще всего это разного рода отчеты, но иногда xls/x файлы используются в качестве хранилища данных. Например, если пользователь должен иметь возможность загрузить данные в приложение или выгрузить, в человеко-читаемом виде, Excel де-факто является стандартом. Относительно дружелюбный интерфейс, прозрачная структура, в купе с его распространенностью. трудно навскидку назвать решение лучше.
Историческая справка
Времена, когда доминировал проприетарный формат .xls(Excel Binary File Format) давно прошли и сейчас мы имеем только .xlsx(Excel Workbook), в рамках Office Open XML. Последний представляет собой обычный .zip архив с XML файлами. Не будем углубляться в его структуру, я искренне надеюсь что вам это никогда не понадобится.
На github, и не только, можно найти ряд библиотек, бесплатных и не только. Пожалуй самой популярной является EPPlus. До определенной степени, она довольно хорошо отражает концепцию Excel, именно по этому я всегда использую EPPlus. Версия 4 полностью бесплатна, начиная с 5‐й версии вам потребуется приобрести лицензию для коммерческого использования.
Задача
Итак, предположим, продукт-мэнеджеру ударила в голову идея того, что возможность выгружать некий отчет в формате Excel увеличит кол-во пользователей на 100500%. Проджет-менеджер решает выкатить эту киллер-фичу как хотфикс прямо сегодня — ведь работы всего на пару часов.
Сам по себе, отчет содержит краткое описание компании и историю изменения некоторых экономических показателей. Для простоты все свойства компании — строки. Экономические показатели — большие целые числа и числа с плавающей точкой, а также даты. Предположим, что где-то в недрах микросервисного backend-да есть сервис-генератор подобных отчетов, например по id компании. Однако, поскольку id нет смысла выводить пользователю, идентификатор отсутствует в самой модели отчета.
Аналитик, в свою очередь, выдает задачу с феноменально точным описанием - "Сгенерировать excel отчет на базе данных MarketReport". Что ж, для нашего примера, создадим заглушку — генератор фейковых данных:
Первый запуск
Подключим EPPlus версии 4.5.3.3 и создадим базовую обвязку для будущего генератора.
Сердцем генератора будет метод Generate. ExcelPackage это модель документа, через которую мы и будем осуществлять все взаимодействия с ним. Также имеется конструктор для передачи пути к файлу или потока.
В методе main создается генератор отчетов, а также генератор Excel файлов. Далее полученный файл просто записывается на диск.
При попытке запустить приложение, получаем exception: InvalidOperationException: The workbook must contain at least one worksheet
Все правильно, Excel документ не может существовать без страниц, должна быть хотя бы одна. Добавляем ее, все интуитивно понятно:
Запускаем снова и. вот оно! Теперь наше приложение генерирует документ и, хотя там еще ничего нет, он уже весит 2,5KB - значит мы работаем с Excel правильно и все идет как надо.
Вывод данных
Давайте выведем основную информацию по компании в шапку. Для доступа к конкретной ячейки объект Cells на странице пакета снабжен удобным индексатором. При этом, до конкретной ячейки можно достучаться как через номер строки и столбца, так и по привычному всем буквенно-числовому коду:
Полный код вывода шапки.
Для вывода исторических данных понадобится как минимум шапка таблицы и цикл по массиву History:
Предлагаю обратить внимание на метод LoadFromArrays, который заполняет диапазон ячеек рваным(зубчатым) массивом. Здесь мы можем видеть, что типизация теряется и передавая массив object мы ожидаем что EPPlus в конечном итоге использует ToString, чтобы записать переданное в ячейки.
Стилизация
Если вы прямо сейчас откроете документ, то вы возможно увидите не то, что хотелось бы отдать в продакшн в пятницу вечером.
Как это выглядит
Во-первых, шапка никак не выделяется, во-вторых таблица не имеет границ. выравнивание пляшет, даты отображаются магическими числами, а капитализация "уходит в какую-то математику" - как это прокомментировал аналитик.
Да, на все эти красивости у нас уйдет больше года кода, чем на сам вывод данных, и, в конечном тоге, получившаяся каша из логики вывода данных и разметки заставит некоторых усомниться в их компетентности. но, мы же backend разработчики, так давайте сверстаем Excel Sheet!
Размер ячеек
Из коробки у нас есть возможность сделать автофит а так же вручную выставить ширину в соответствии с нашей ситуацией. А ситуация у нас не самая хорошая — по задумке аналитика в шапке у ячеек должен быть автофит, а у ячеек таблицы — тоже автофит. Так в чем же подвох?
Если вы когда-нибудь до этого открывали Excel, то возможно знаете, что ширина ячеек не может отличаться в рамках столбца и автофит будет по самому широкому контенту ячейки. Однако, простые вещи бывает нетак то просто объяснить. Но если вы справитесь, то вот как это будет выглядеть в коде:
Формат данных
Как и большая часть стиля ячейки, он задается через одноименное свойство Style. Обратите внимание на вычисление 3-го аргумента индексатора. Это звоночек некачественного кода, но к этому мы вернемся в позже.
Выравнивание
Его можно задать как на ячейке, так и на диапазоне. На самом деле, для EPPlus, это одна и та же сущность — некий ExcelRange, описывающий диапазон ячеек, в том числе и со всего 1 ячейкой.
Стиль текста
Также легко задается, используя Style.Font, кстати, здесь, на 2-й строчке, мы впервые указываем диапазон так, как привыкли его видеть пользователи Excel:
Границы
Задаем стиль линии, а также ее толщину. К этому моменту от кол-ва магических чисел-параметров индексатора уже рябит в глазах, но мы уже на финишной прямой. не так ли?
График
"Ну что за отчет без графиков, верно, Карл?" - ловко подметит специалист по тестированию, и не важно, что этого не было в ТЗ а на часах уже половина 9-го.
Хотя график как сущность сам по себе сложнее таблиц и с графиками мы не работаем каждый день, EPPlus предоставляет довольно понятный API. Давайте добавим простейший график, отражающий рост капитализации:
Еще, может понадобиться защитить страницу от редактирования:
На этом все, репозиторий с рабочим приложением находится здесь.
Заключение
Во-первых, прежде всего, о том, что мы успешно справились с задачей, а именно, сгенерировали свой первый Excel отчет, поработали со стилями и даже решили пару попутных проблем.
Программно вытащить таблицу из Excel и сохранить в Oracle
задача: с помощью си шарпа вытащить таблицу из экселя и сохранить в оракл, данные из эксель.
Как создать таблицу в бд access программно?
Здравствуйте! Подскажите пожалуйста, как создать таблицу в бд access программно?
Программно создать таблицу в Excel
создать через форму delphi в ms excel таблицу квадратов чисел от 1 до 99 . Подскажите ,умоляю .
Решение
В общем, решил задачу следующим образом:
1) заранее создал шаблон Excel файла со своей таблицей
2) при создании Excel программно добавил путь к шаблону
3) файл открывается с шаблоном и туда можно вписывать свои данные.
Решение
denistrannik, для понимания. То, что вы просите создать на листе Excel называется "Умная таблица". Не все знают об этом, поэтому не очень корректно поняли ваш вопрос.
Вручную через меню Excel она создаётся так:
1. Ставим курсор внутри нашей таблицы (любого набора данных на листе)
2. меню "Вставка" - Таблица - в всплывающем окошке проверяем диапазон и отмечаем галочку "Таблица с заголовками"
Средствами макроса (VBA) она создаётся так:
Теперь у вас на листе создалась "Умная таблица"
Программно импортировать таблицу Excel с указанием типов полей
Добрый день. Допустим, есть таблица Excel, в которой несколько полей имеют довольно таки.
Программно создать таблицу
Помогите пожалуйста:'( Кто-нибудь знает как программно создать в Delphi таблицу формата .dbf.
Программно создать таблицу в БД
Мне нужно . Написал я в общем вот такую херь. begin Form1.qry2.SQL.Clear; .
Как программно создать таблицу?
подскажите как программно создать что-нибудь на подобие такой таблички? я нигде не могу найти как.
Создать программно таблицу в .doc
Всем доброго времени суток, пишу программу которая в конце работы должна проверенный и.
Программно создать таблицу в Fragment
Добрый день форумчане, возник у меня такой вопрос. Пытаюсь программно создать таблицу в Fragment.
Привожу фрагменты кода, которые искал когда-то сам для работы с 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
Комментарии
Для этого в настройках ленты надо добавить пункт «Разработчик». Далее начинаем запись макроса, производим действия и останавливаем запись.
Далее заходим в редактор Visual Basic и смотрим код, который туда записался:
Данный метод так же может оказать помощь в формировании относительных формул, например, выполнить сложение чисел, находящиеся слева от текущей ячейки на 4 столбца, и т.п. Пример:
Так же во время работы может возникнуть ошибка: метод завершен неверно. Это может означать, что не выбран лист, с которым идет работа.
Чтобы выбрать лист, выполните sheetData.Select(Type.Missing); где sheetData это нужный лист.
Как прочитать данные из ячейки excel,и записать эти данные в sql server?
Добрый день.
Отправил на почту.
Добрый день.
А мне можно тоже самое?)
Вы можете записать макрос на изменение цвета в Visual Basic и списать получившийся код. Затем использовать его в своей программе.
Здравствуйте.
Можете подсказать, как «заставить» приложение работать с разными версиями MS Office? На машине разработчика стоит Office 2010, при запуске на машине с 2003-м — увы — ошибка.
Microsoft.Office.Interop.Excel это довольно старый способ работать с Excel документами.
Что касается версии Office 2003, то он использует совсем другой драйвер.
Соответственно версия Microsoft.Office.Interop.Excel.dll нужна старая, плюс драйвер microsoft jet 4.0, который на новых системах (Win 8, 10) работает неправильно.
Единственное, что могу посоветовать, так это скачать Microsoft Office Compatibility Pack для Office 2003, чтобы научить его открывать xslx документы.
А в своей программе использовать не Interop.Excel, а библиотеку EPPlus. Она работает с excel документами, используя технологию OpenXml и не надо париться по поводу драйверов.
Код будет очень похож на Interop.Excel-ный.
Очень полезная штука, спасибо за удобное представление информации на Вашем сайте!
Скажите пожалуйста, как прочитать данные из ячейки Excel и записать их в SQL Server?
Введение
Добавление необходимых ссылок в решение
Для подключения в решение библиотек для взаимодействия с Microsoft Excel необходимо добавить добавьте следующие ссылки:
- Microsoft Excel 16.0 Object Library
- Microsoft Office 16.0 Object Library
В Visual Studio это делается следующим образом:
1. В «Обозревателе решений» кликаем правой кнопкой мыши по строке «Зависимости» и выбираем пункт «Добавить ссылку на модель COM»
2. В открывшемся окне в строке поиска набираем «Microsoft», выбираем необходимые ссылки, то есть Microsoft Excel 16.0 Object Library и Microsoft Office 16.0 Object Library и жмем «Ok»:
После этого, в обозревателе решений для нашего проекта в зависимостях появятся следующие ссылки:
Работа с объектом Application (Excel)
Теперь напишем следующий код для метода Main() :
Разберемся с тем, что происходит в нашем приложении при запуске. Вначале мы создаем объект application типа Excel.Application ( Excel — псевдоним пространства имен Microsoft.Office.Interop.Excel ). После выполнения этой строки кода происходит запуск процесса Microsoft Excel. Убедиться в этом можно, открыв диспетчер задач Windows и заглянув в раздел «Фоновые процессы»:
Чтобы показать главное окно Excel пользователю, мы устанавливаем значение true свойству Visible :
И, наконец, для того, чтобы закрыть Excel и освободить память, занятую процессом Excel мы вызываем метод Quit() .
и запустить приложение несколько раз. Например, на рисунке ниже показан диспетчер задач после трех запусков нашего приложения:
На первый взгляд, может показаться, что этот код вполне можно упростить, например, избавившись от «лишних» переменной workbooks , например так:
Такой код, безусловно, будет короче и рабочая книга также будет создана, НО при этом, память выделенная для процесса Excel не будет освобождена по причине того, что в приведенной выше строке кода мы на самом деле создаем не один, а два объекта: объект типа Excel.Workbooks и объект типа Excel.Workbook и, соответственно, оба эти объекта нам необходимо освободить после работы с ними.
Необходимо обеспечить гарантированное освобождение всех объектов COM перед завершением процесса, поэтому всю работу необходимо вести в блоке try…finally
В следующем примере демонстрируется то, как получить доступ к первому листу рабочей книги и записи в ячейку A1 строки « Hello Excel «:
После запуска этого приложения откроется Excel, создастся рабочая книга, на первом листе рабочей книги в ячейке А1 появится строка «Hello Excel» после чего Excel попросит сохранить книгу. После отказа сохранения Excel закроется и память процесса Microsoft Excel будет освобождена.
Итого
Читайте также: