Выгрузка в excel из c
В современном мире разработки приложений нередко встает необходимость работы с 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 отчет, поработали со стилями и даже решили пару попутных проблем.
Версия этой статьи для Microsoft Visual Basic 6,0 приведена в статье 247412.
Обзор
Метод, наиболее часто используемый для передачи данных в книгу Excel, является автоматизацией. С помощью автоматизации можно вызывать методы и свойства, относящиеся к задачам Excel. Автоматизация предоставляет максимальную гибкость для указания расположения данных в книге, форматирования книги и создания различных параметров во время выполнения.
С помощью автоматизации вы можете использовать различные методы для переноса данных:
- Перемещение ячейки данных по ячейке.
- Передача данных в массиве в диапазон ячеек.
- Перенесите данные из набора записей ADO в диапазон ячеек с помощью метода Копифромрекордсет.
- Создайте объект QueryTable на листе Excel, который содержит результат запроса в источнике данных ODBC или OLEDB.
- Перенесите данные в буфер обмена, а затем вставьте содержимое буфера обмена в лист Excel.
Вы также можете использовать несколько методов, которые не требуют автоматизации для передачи данных в Excel. Если вы используете серверную программу, это может быть хорошим подходом к отходящей обработке данных от клиентов.
Для переноса данных без автоматизации можно использовать следующие подходы:
Способ
Использование автоматизации для передачи ячейки данных по ячейкам
С помощью автоматизации можно переносить данные на лист по одной ячейке за раз:
Опять же, передача данных по ячейке допускается только для небольших объемов данных. Если необходимо перенести большие наборы данных в Excel, рекомендуется использовать один из других подходов, описанных в этой статье, для массовой передачи данных.
Использование автоматизации для переноса массива данных в диапазон листа
Вы можете перенести массив данных в диапазон из нескольких ячеек за один раз:
Если вы переносите данные с помощью массива, а не ячейки по ячейке, вы можете реализовать огромную производительность с большим количеством данных. Рассмотрите следующие строки из вышеупомянутого кода, которые передают данные в 300 ячейки листа:
Этот код представляет два запроса интерфейса: один для объекта Range, возвращаемого методом Range, и другой для объекта Range, который возвращает метод Resize. В отличие от переноса ячейки данных по ячейке, необходимо запросить интерфейсы 300 для объектов Range. Если это возможно, вы можете воспользоваться преимуществами для массового переноса данных и уменьшения количества запросов к интерфейсу.
Для получения дополнительных сведений о том, как использовать массивы для получения и задания значений в диапазонах с помощью автоматизации Excel, щелкните номер статьи ниже, чтобы просмотреть статью в базе знаний Майкрософт:
Использование автоматизации для переноса набора записей ADO в диапазон листа
Объектные модели для Excel 2000, Excel 2002 и Excel 2003 предоставляют метод Копифромрекордсет для переноса набора записей ADO в диапазон листа. В приведенном ниже коде показано, как автоматизировать Excel для переноса содержимого таблицы Orders в образце базы данных Northwind с помощью метода Копифромрекордсет:
Использование автоматизации для создания объекта QueryTable на листе
Объект QueryTable представляет таблицу, созданную на основе данных, возвращаемых из внешнего источника данных. При автоматизации Excel можно создать QueryTable, предоставив строку подключения к OLE DB или источнику данных ODBC, а также строку SQL. Excel создает набор записей и вставляет набор записей на лист в указанном расположении. Объекты QueryTable имеют следующие преимущества по сравнению с методом Копифромрекордсет:
- Excel обрабатывает создание набора записей и его расположение на листе.
- Вы можете сохранить запрос с помощью объекта QueryTable и обновить его позже, чтобы получить обновленный набор записей.
- Когда на лист добавляется новый QueryTable, вы можете указать, что данные, которые уже существуют в ячейках листа, будут сдвинуты для обработки новых данных (Дополнительные сведения см. в свойстве Рефрешстиле).
В приведенном ниже коде показано, как автоматизировать Excel 2000, Excel 2002 или Excel 2003 для создания нового QueryTable на листе Excel с помощью данных из учебной базы данных Northwind:
Использование буфера обмена Windows
Создание текстового файла с разделителями, который Excel может проанализировать по строкам и столбцам
Excel может открывать файлы с разделителями табуляцией и запятыми и правильно анализировать данные в ячейки. Эту функцию можно использовать, если требуется перенести большое количество данных на лист, используя небольшую, при автоматизации. Это может быть хорошим подходом к клиент-серверной программе, так как текстовый файл может быть создан на стороне сервера. Затем можно открыть текстовый файл на клиенте, используя автоматизацию там, где это необходимо.
Приведенный выше код не использует автоматизацию. Однако при желании можно использовать автоматизацию для открытия текстового файла и сохранения файла в формате книги Excel, как показано ниже:
С помощью поставщика OLE DB для Microsoft Jet можно добавлять записи в таблицу из существующей книги Excel. Таблица в Excel — это всего лишь диапазон ячеек; диапазон может иметь определенное имя. Как правило, первая строка диапазона содержит заголовки (или имена полей), а все последующие строки в диапазоне содержат записи.
Приведенный ниже код добавляет две новые записи в таблицу в Book7. xls. В этом случае таблицей является Лист1:
Для получения дополнительных сведений об использовании поставщика OLEDB для Jet с источниками данных Excel щелкните номера статей ниже, чтобы просмотреть статьи базы знаний Майкрософт:
278973 пример: в ексцеладо показано, как использовать ADO для чтения и записи данных в книгах Excel
257819 практическое руководство: использование ADO с данными Excel из Visual Basic или VBA
Передача XML-данных (Excel 2002 и Excel 2003)
Excel 2002 и 2003 могут открыть любой XML-файл с правильным форматом. XML-файлы можно открыть непосредственно с помощью команды открыть в меню файл или программным путем с помощью методов Open и OpenXML коллекции книги. Если вы создаете XML-файлы для использования в Excel, вы также можете создать таблицы стилей для форматирования данных.
Создание новой папки с именем К:\ексцелдата. В этом примере программа будет хранить книги Excel в этой папке.
Создайте новую книгу для примера, в который необходимо выполнить запись:
- Создайте новую книгу в Excel.
- На листе Sheet1 новой книги введите FirstName в ячейке a1 и LastName в ячейке B1.
- Выберите a1: B1.
- В меню Вставка выберите пункт имя, а затем — команду определить. Введите имя MyTable и нажмите кнопку ОК.
- Сохранение книги в виде C:\Exceldata\Book7.xls.
- Закройте Excel.
Добавьте ссылку на библиотеку объектов Excel и основную сборку взаимодействия ADODB. Для этого выполните следующие действия:
- On the Project menu, click Add Reference.
- На вкладке Сеть найдите ADODB и нажмите кнопку Выбрать.
Обратите внимание, что в Visual Studio 2005 нет необходимости щелкать кнопку выбрать.
3. На вкладке COM найдите объектная Библиотека Microsoft Excel 10,0 или библиотека объектов Microsoft Excel 11,0, а затем нажмите кнопку Выбрать.
Обратите внимание, что в Visual Studio 2005 нет необходимости щелкать кнопку выбрать.
Примечание Если вы используете Microsoft Excel 2002, а вы еще не сделали это, корпорация Майкрософт рекомендует скачать и установить основные сборки взаимодействия Microsoft Office XP (PIA).
В диалоговом окне Добавление ссылок нажмите кнопку ОК, чтобы принять выбранные параметры.
Добавление элемента управления "поле со списком" и элемента управления "Кнопка" в форму Form1.
Добавьте обработчики событий для события загрузки формы и событий Click элемента управления Button:
- В представлении конструктора для Form1.cs дважды щелкните элемент Form1.
Обработчик события Load для формы создан и отображается в Form1.cs.
2. В меню Вид выберите конструктор, чтобы переключиться в режим конструктора.
3. Дважды щелкните элемент Button1.
Обработчик события нажатия кнопки создается и отображается в Form1.cs.
В Form1.cs замените приведенный ниже код.
Добавьте следующие директивы using в директивы using в Form1.cs:
Нажмите клавишу F5 для сборки и запуска примера.
Ссылки
Для получения дополнительных сведений посетите следующий веб-сайт Майкрософт:
В этой статье приводится информация о подключении, которую вы должны предоставить, и необходимые настройки подключения для импорта данных из Excel и экспорта данных в Excel с помощью SQL Server Integration Services (SSIS).
В следующих разделах содержится вся информация, которая потребуется для успешной работы с приложением Excel из служб SSIS, а также для понимания и устранения распространенных проблем:
Средства, которые можно использовать.
Файлы, которые вам потребуются.
Сведения о подключении, которые необходимо предоставить, и параметры, которые необходимо настроить для загрузки данных в/из Excel с использованием SSIS.
-
в качестве источника данных.
- Укажите имя файла Excel и путь к нему.
- Выберите версию Excel.
- Укажите, содержит ли первая строка имена столбцов.
- Укажите лист или диапазон, который содержит данные.
Известные проблемы и ограничения.
- Проблемы с типами данных.
- Проблемы с импортом.
- Проблемы с экспортом.
Средства, которые можно использовать
Вы можете импортировать и экспортировать данные в Excel с использованием SSIS при помощи любого из следующих инструментов.
SQL Server Integration Services (SSIS) . Создайте пакет SSIS, настроив для него Excel в качестве источника или назначения с помощью диспетчера подключений Excel. (В этой статье не рассматривается создание пакетов SSIS.)
Получение файлов, необходимых для подключения к Excel
Прежде чем импортировать или экспортировать данные в Excel с использованием SSIS, вам может потребоваться скачать компоненты подключения для Excel, если они еще не установлены. По умолчанию компоненты подключений для Excel не устанавливаются.
Используйте таблицу из статьи Не удалось использовать интерфейсы Access ODBC, OLEDB или DAO за пределами Office Click-to-Run, чтобы понять, нужны ли дополнительные компоненты для вашей среды.
Примечание. Системные драйверы Office поддерживаются только в определенных случаях, конкретные руководства см. в статье Рекомендации по автоматизации Office на стороне сервера.
Назначение Excel в качестве источника данных
Сначала необходимо указать, что вам требуется установить подключение к Excel.
В службах SSIS
В службах SSIS создайте диспетчер соединений Excel для подключения к файлу источника или назначения Excel. Создать диспетчер соединений можно несколькими способами:
Щелкните правой кнопкой в области Диспетчеры соединений и выберите команду Создать соединение. В диалоговом окне Добавление диспетчера соединений со службами SSIS выберите EXCEL и затем Добавить.
В меню Службы SSIS выберите Создать соединение. В диалоговом окне Добавление диспетчера соединений со службами SSIS выберите EXCEL и затем Добавить.
Диспетчер соединений необходимо создавать в то время, как вы настраиваете источник Excel или назначение Excel на странице Диспетчер соединений в редакторе источника Excel или в редакторе назначения Excel.
В мастере импорта и экспорта SQL Server
В мастере импорта и экспорта на странице Выбор источника данных или Выбор назначения выберите Microsoft Excel в списке Источник данных.
Если вы не видите Excel в списке источников данных, убедитесь, что используется 32-разрядная версия мастера. Компоненты подключений для Excel обычно представляют собой 32-разрядные файлы и не отображаются в 64-разрядной версии мастера.
Файл Excel и путь к нему
В первую очередь необходимо предоставить имя файла Excel и путь к нему. Это можно сделать в редакторе диспетчера соединений Excel в пакете служб SSIS либо на странице Выбор источника данных или Выбор назначения в мастере импорта и экспорта.
Введите имя файла и путь к нему в следующем формате:
Для файла на локальном компьютере: C:\TestData.xlsx.
Для файла в общей сетевой папке: \\Sales\Data\TestData.xlsx.
Также можно нажать кнопку Обзор и выбрать электронную таблицу в диалоговом окне Открыть.
Подключить защищенный паролем файл Excel нельзя.
Версия Excel
Далее следует предоставить сведения о версии файла Excel. Это можно сделать в редакторе диспетчера соединений Excel в пакете служб SSIS либо на странице Выбор источника данных или Выбор назначения в мастере импорта и экспорта.
Выберите версию Microsoft Excel, в которой был создан файл, или другую совместимую версию. Например, если вам не удалось установить компоненты подключений для версии 2016, вы можете установить компоненты для версии 2010 и выбрать в этом списке вариант Microsoft Excel 2007-2010.
Если у вас установлены старые версии компонентов подключений, вы не сможете выбрать более новые версии Excel в этом списке. Список Версия Excel содержит все версии Excel, поддерживаемые службами SSIS. Наличие элементов в этом списке не означает, что необходимые компоненты подключений были установлены. Например, вариант Microsoft Excel 2016 будет присутствовать в этом списке даже в том случае, если у вас не установлены компоненты подключений для версии 2016.
Первая строка содержит имена столбцов
Если вы импортируете данные из Excel, далее необходимо указать, содержит ли первая строка данных имена столбцов. Это можно сделать в редакторе диспетчера соединений Excel в пакете служб SSIS или на странице Выбор источника данных в мастере импорта и экспорта.
- Если этот параметр отключен, поскольку в данных источника отсутствуют имена столбцов, мастер будет использовать в качестве заголовков столбцов значения F1, F2 и т. д.
- Если данные содержат имена столбцов, но этот параметр отключен, мастер импортирует строку имен столбцов как первую строку данных.
- Если данные не содержат имена столбцов, но этот параметр включен, мастер использует первую строку данных источника как имена столбцов. В этом случае первая строка в данных источника не включается в сами данные.
Если вы экспортируете данные из Excel и включили этот параметр, первая строка экспортированных данных будет включать имена столбцов.
Листы и диапазоны
В качестве источника или назначения для данных можно использовать три типа объектов Excel: лист, именованный диапазон или неименованный диапазон ячеек, который задается с помощью адреса.
Лист Чтобы указать лист, добавьте в конец имени листа символ $ и окружите строку разделителями, например [Листt1$] . Также можно выполнить поиск имени, заканчивающегося символом $ , в списке существующих таблиц и представлений.
Именованный диапазон Чтобы указать именованный диапазон, используйте имя диапазона, например Мой_диапазон. Также можно выполнить поиск имени, не заканчивающегося символом $ , в списке существующих таблиц и представлений.
Неименованный диапазон Чтобы указать диапазон ячеек, которым не были заданы имена, добавьте символ $ после имени листа, добавьте спецификацию диапазона и окружите строку разделителями, например [Лист1$A1:B4] .
Чтобы выбрать или указать тип объекта Excel, который вы хотите использовать в качестве источника или назначения для данных, выполните одно из следующих действий:
В службах SSIS
В службах SSIS на странице Диспетчер соединений в редакторе источника Excel или редакторе назначения Excel выполните одно из следующих действий:
Чтобы использовать лист или именованный диапазон, выберите Таблица или представление в разделе Режим доступа к данным. Затем выберите лист или именованный диапазон в списке Имя листа Excel.
Чтобы использовать неименованный диапазон, который задается по адресу, выберите Команда SQL в разделе Режим доступа к данным. Затем в поле Текст команды SQL введите запрос, аналогичный представленному ниже примеру:
В мастере импорта и экспорта SQL Server
В мастере импорта и экспорта выполните одно из следующих действий:
При импорте из Excel выполните одно из следующих действий:
Чтобы использовать лист или именованный диапазон, на странице Выбор копирования таблицы или запроса выберите Скопировать данные из одной или нескольких таблиц или представлений. Затем на странице Выбор исходных таблиц и представлений в столбце Источник укажите исходные листы и именованные диапазоны.
Чтобы использовать неименованный диапазон, который задается по адресу, на странице Выбор копирования таблицы или запроса выберите Написать запрос, указывающий данные для передачи. Затем на странице Определение исходного запроса введите запрос, аналогичный показанному ниже:
При экспорте в Excel выполните одно из следующих действий:
Чтобы использовать лист или именованный диапазон, на странице Выбор исходных таблиц и представлений в столбце Назначение выберите целевые листы и именованные диапазоны.
Чтобы использовать неименованный диапазон, который задается по адресу, на странице Выбор исходных таблиц и представлений в столбце Назначение укажите диапазон в следующем формате без использования разделителей: Sheet1$A1:B5 . Мастер автоматически добавит разделители.
После выбора или ввода объектов Excel для импорта или экспорта вы также можете выполнить следующие действия на странице Выбор исходных таблиц и представлений мастера:
Проверить сопоставления столбцов между источником и назначением, выбрав команду Изменить сопоставления.
Выполнить предварительный просмотр данных, чтобы убедиться в правильности выбора, с помощью команды Предварительный просмотр.
Проблемы с типами данных
Типы данных
Драйвер Excel распознает только ограниченный набор типов данных. Например, все числовые столбцы воспринимаются как тип double (DT_R8), а все строковые столбцы (кроме столбцов типа memo) воспринимаются как строки в Юникоде длиной 255 символов (DT_WSTR). Службы SSIS сопоставляют типы данных Excel следующим образом:
Числовой — с плавающей запятой двойной точности (DT_R8)
Денежный — денежный (DT_CY)
Логический — логический (DT_BOOL)
Дата и время — datetime (DT_DATE)
Строковый — строка в Юникоде длиной в 255 символов (DT_WSTR)
Memo — текстовый поток в Юникоде (DT_NTEXT)
Преобразования типов данных и длины по умолчанию
В службах SSIS неявное преобразование типов данных не выполняется. В результате, возможно, потребуется использовать преобразование "Производный столбец" или "Преобразование данных" для явного преобразования данных Excel до их загрузки в назначение, отличное от Excel, либо для преобразования данных из источника, отличного от Excel, до их загрузки в назначение Excel.
Ниже приведены некоторые примеры преобразований, которые могут потребоваться:
Преобразование между строковыми столбцами Excel в Юникоде и строковыми столбцами в формате с конкретными кодовыми страницами, отличными от Юникода.
Преобразование между строковыми столбцами Excel длиной в 255 символов и строковыми столбцами другой длины.
Преобразование между числовыми столбцами Excel с плавающей запятой двойной точности и числовыми столбцами других типов.
Если вы используете мастер импорта и экспорта и вам требуется выполнить некоторые из этих преобразований данных, мастер выполнит настройку необходимых преобразований автоматически. В результате, даже если вам требуется использовать пакет служб SSIS, часто бывает полезно создать первичный пакет с помощью мастера импорта и экспорта. В таком случае мастер создаст и настроит диспетчеры соединений, источники, преобразования и назначения автоматически.
Проблемы с импортом
Пустые строки
Если в качестве источника указан лист или диапазон, драйвер считывает непрерывный блок ячеек, начиная с первой непустой ячейки в верхнем левом углу листа или диапазона. В результате данные могут начинаться не со строки 1, однако в данных источника не должны присутствовать пустые строки. Например, пустые строки не допускаются между заголовками столбцов и строками данных, а также после названия в верхней части листа.
Если над данными располагаются пустые строки, вы не сможете выполнить их запрос в качестве листа. В Excel необходимо выбрать диапазон данных, присвоить ему имя и выполнить запрос к именованному диапазону, а не к листу.
Отсутствующие значения
Драйвер Excel считывает определенное количество строк (по умолчанию 8 строк) в указанном источнике для определения типа данных каждого столбца. Если столбец содержит смешанные типы данных, особенно если числовые данные смешаны с текстовыми данными, драйвер принимает решение в пользу того типа данных, которого больше, и возвращает значения NULL в ячейки, содержащие данные другого типа. (В случае равенства преимущество получает числовой тип.) Большинство параметров форматирования ячеек в листе Excel не затрагивает это определение типа данных.
Можно изменить поведение драйвера Excel, указав режим импорта для импорта всех значений в виде текста. Чтобы указать режим импорта, добавьте IMEX=1 к значению расширенных свойств в строке соединения диспетчера соединений с Excel в окне "Свойства".
Усеченный текст
Когда драйвер определяет, что столбец Excel содержит текстовые данные, он выбирает тип данных (строковый или memo) на основании самого длинного значения. Если драйвер не обнаруживает значений длиннее 255 символов в выбираемых строках, он считает, что столбец является строковым с длиной 255 символов, а не столбцом типа memo. Поэтому значения длиннее 255 символов могут быть усечены.
Чтобы импортировать данные из столбца типа memo без усечения, можно воспользоваться любым из двух способов:
Убедитесь, что столбец типа memo как минимум в одной из выбранных строк содержит значение длиной более 255 символов.
Увеличьте число строк в выборке драйвера, чтобы включить такую строку. Чтобы увеличить количество строк, включаемых в выборку, достаточно увеличить значение TypeGuessRows в следующем разделе реестра:
Проблемы с экспортом
Создание нового файла назначения
В службах SSIS
Создайте диспетчер соединений Excel с именем создаваемого файла Excel и путем к нему. Затем в редакторе назначения Excel в разделе Имя листа Excel выберите Создать, чтобы создать лист назначения. В этот момент службы SSIS создают новый файл Excel с указанным листом.
В мастере импорта и экспорта SQL Server
На странице Выбор назначения нажмите кнопку Обзор. В диалоговом окне Открыть перейдите в папку, в которой требуется создать новый файл Excel, укажите имя этого файла и выберите Открыть.
Экспорт в достаточно большой диапазон
Экспорт длинных текстовых значений
Чтобы успешно сохранять в столбцы Excel строки, имеющие длину более 255 символов, драйвер должен распознать тип данных целевого столбца как memo , а не как string.
- Если в существующей целевой таблице уже содержатся строки данных, то в столбце типа memo в первых нескольких строках, которые проверит драйвер, должен содержаться, по крайней мере, один экземпляр значения, имеющего длину более 255 символов.
См. также
Дополнительные сведения о компонентах и процедурах, описываемых в этой статье, см. в следующих статьях:
Решил написать статью, о том, как сделать выгрузку данных в Excel файл по шаблону и считывать данные из Excel.
Началось всё с того, что на работе, дали указание, уйти от MS Office, на бесплатные аналоги.
У нас уже была система выгрузки, основанная на библиотеке “Microsoft.Office. Interop.Excel” и много готовых шаблонов, для выгрузки тех или иных отчётов.
Поэтому надо было найти бесплатную библиотеку, работающую с офисом. И сделать так, чтоб выгрузка работала по той же системе, что и раньше. Ибо переделывать все шаблоны и структуру, не хотелось.
Вот я и наткнулся на OpenXML. И думал, сейчас по быстрому найду решение в интернете и всё готово (т.к. на это было выделено мало времени). Но подходящего решения так и не нашёл, поэтому и решил написать эту статью, для тех у кого будет, такая же проблема.
Саму библиотеку, можно скачать бесплатно с сайта Micrisoft (я использовал в проекте OpenXML sdk 2.5 “ OpenXMLSDKV25.msi ”)
здесь.
После скачивания “OpenXMLSDKV25.msi ”, устанавливаем и заходим в папку
“C:\Program Files\Open XML SDK\V2.5\lib” там лежит библиотека, которая нам понадобится, мы её подключим к проекту (ниже будет описано, как).
Проект был написан на Visual Studio 2010 (Framework 4.0).
Ниже пример шаблона (сделан для теста) “C:\Templates\template.xlsx”.
И пример выгруженных данных (как это будет выглядеть в итоге, после выгрузки).
Ключевые слова:
DataField: — Означает, что на этом месте будут выведены наши банные из DataTable.
DataField:[название выводимого поля]
Label: — Означает, что на этом месте будут выводиться данные, которые надо вставить однократно из словаря
Label:[название ключа в словаре]
А это файл из которого мы будем считывать данные “C:\Loading\ReadMePlease.xlsx”.
Теперь создадим в VS2010, Решение в котором будет 4 проекта:
1) OpenXmlPrj – это консольный проект, для запуска теста.
2) Interfaces – это проект типа “Библиотека классов”, будет хранить наши интерфейсы данных для выгрузки.
3) Framework — это проект типа “Библиотека классов”, тут и будет происходить вся работа с Excel-ем.
4) Converter — это проект типа “Библиотека классов”, для конвертирования наших данных в DataTable (т.к. работа происходит с DataTable).
Теперь в проекте “Framework” создаём две папки и подключим ссылку на библиотеку OpenXML и WindowsBase:
“Create” – для работы с выгрузкой данных.
“Load” – для работы с загрузкой данных.
“lib” – в папку, добавим библиотеку OpenXML.
В папке “Create” создаём 4 класса.
1) Worker – это будет наш главный обработчик.
4) Field – будет содержать индекс строки, где находится DataField, координаты ячеек с DataField и название поля, значение которого надо вывести.
В проекте “Converter” создадим класс
ConvertToDataTable – для конвертирования наших данных в DataTable.
И проекте “OpenXmlPrj” надо подключить ссылки на следующие проекты: Interfaces, Framework, Converter
Условия для создания шаблона:
1. Excel лист, обязательно должен называться “Лист1” (ну или если захотите переименовать, то в коде надо будет изменить название тоже).
2. Названия после DataField: должны строго совпадать с названиями колонок в DataTable.
3. Шаблон должен быть сохранён в формате “.xlsx”.
Условия для файла, с которого мы будем считывать данные:
1. Excel лист, обязательно должен называться “Лист1” (ну или если захотите переименовать, то в коде надо будет изменить название тоже).
2. Первая строка, должна содержать названия колонок, по которым мы потом будем парсить данные.
Все началось с того, что стал писать простенькую программу, которая должна была выполнять функции телефонного справочника. В качестве основного компонента, в котором будут размещаться все данные, выбрал объект класса ListView. Но любая хорошая программа должна уметь сохранять все свои данные в отдельный файл, с последующей его загрузкой, для продолжения работы с данными.
Что бы не изобретать велосипед, я принял решение о сохранение данных в XML-файл. Такой тип файла, очень удобен и популярен у многих разработчиков, с ним легко работать. Единственным его недостатком является слабая защищенность данных. Его можно открыть любым простым редактором текста, хотя и можно применить шифрование текста — это не гарантирует полной безопасности для информации в файле.
Как осуществить экспорт данных?
Как видно из скриншота, в элементе меню «Файл» уже все заготовлено для Экспорта и Импорта нашей телефонной книги, осталось написать сам код.
Адаптивный код
Такой способ очень хороший, при условии, когда мы лично пишем класс объекта, с которым будем работать. А как быть в тех случаях, когда мы использует уже готовый класс? К примеру, класс ListView или Datagridview, мы их не писали (лично), но многие из нас часто ими пользуются.
В этот момент, я понял, что нужно создавать свой класс который, я всегда смогу использовать. Так мне не нужно (в будущем) все время тратить на описывание различных параметров. Нужно лишь будет переопределить методы класса, или просто сделать правки.
Класс для работы с XML
Для работы данного класса потребуется подключить еще две сборки:
Класс я решил назвать "Stroka", это потому что мы в цикле будем перебирать все строки нашей таблице. Также обратите, что во всех частях кода описывается по 4 элемента. Это сделано намеренно. Количество элементов должно совпадать с нашим количество колонок, в таблице.
Так как наш класс готов, то мы можем его свернуть в нашем проекте, и больше в него не заглядывать.
Красивый экспорт (сохранение)
Давайте не будем спешить закончить нашу работу. А напишем еще несколько строчек кода, которые позволят нам вызывать диалоговое окно для сохранения.
Это окно мы сможем вызвать вот таким кодом:
Как вы могли заметить, это уже метод экспорта данных в XML-файл. Но здесь ничего интересного не происходит, только вызывается окно для экспорта, и его путь записывается в переменную "filename". Это обычная строковая переменная, которая создается еще в начале проекта.
Выглядит это так:
Сам экспорт выполнится в методе «Export();».
Время метода «Export();»
Важно заметить, что большая часть этого кода «шаблонна», для многих наших проектов с этим классом, единственной отличительной чертой всегда будет наш «цикл перебора всех элементов». Он будет отличаться в зависимости от того, с какой «таблицы» мы будем экспортировать данный. В данном случае, данные берутся с объекта класса ListView.
Обратите внимание на строчки:
Мы создаем объект класса (нашего) Stroka, и в его метод Main помещаем элементы строки (4-х наших колонок).
Экспорт завершен
Созданный XML-файл я открыл программой «Блокнот», и мы можем сравнить структуру файла с нашим кодом.
Наш класс позволяет также импортировать данные, в наше приложение. Мы также сможем редактировать, и выполнять любую другую работу с ними так, как будто данные изначально были в программе.
Читайте также: