С помощью страницы Подключение к книге Microsoft Excel найдите и выберите книгу Excel, содержащую данные, которые нужно импортировать, а затем выберите лист или диапазон, который вы хотите использовать.
Выберите книгу, которую вы хотите импортировать.
В этом поле перечислены последние использовавшиеся файлы Excel. Если флажок пуст, вы можете ввести имя книги Excel в поле или нажать кнопку Обзор , чтобы найти его.
- Введите текст, выберите его из списка или перейдите к книге Excel, содержащей данные, которые вы хотите импортировать.
- Оставшаяся часть мастера может быть необязательной. Выполните одно из указанных ниже действий.
- Нажмите кнопку Готово , чтобы импортировать все данные на первом листе книги, и позвольте мастеру выбрать уникальный идентификатор (ключевой столбец).
- Нажмите кнопку Далее , чтобы продолжить работу мастера, и выполните одно из указанных ниже действий.
- Импорт данных, которые не находятся на первом листе книги. По умолчанию импортируются только данные первого листа.
- Импорт только части большого набора данных.
- Выберите уникальный идентификатор (ключевой столбец), а не разрешите мастеру выбирать один из них. Если нажать кнопку Далее , вы можете указать, какой лист или диапазон вы хотите использовать на следующей странице мастера.
Выберите лист или диапазон, который вы хотите использовать.
В этом поле перечислены все доступные листы и именованные диапазоны в выбранной книге. (Диапазон — это две или более ячеек книги, которые были выбраны в Excel, а затем задано имя, по которому можно идентифицировать диапазон.)
Мгновенное заполнение в Excel
Примечание: объемные ссылки (именованные диапазоны ячеек на нескольких листах) недоступны.
Выбор настраиваемого диапазона Нажмите эту кнопку, чтобы выбрать определенный набор ячеек. Книга, выбранная на предыдущей странице мастера, откроется в Excel. Откроется диалоговое окно Импорт в Visio . Вы можете либо ввести диапазон в поле выберите диапазон диалогового окна Импорт в Visio , либо выделить ячейки непосредственно в книге. Если выделить ячейки в книге, диалоговое окно останется на открытом листе, пока вы заберете нужный вариант, и в поле Выбор диапазона появится диапазон.
Если вы выберете настраиваемый диапазон, не забудьте включить все заголовки столбцов в выделенный фрагмент. На рисунке ниже показан пример выделения ячеек на листе.
Первая строка данных, содержащая заголовки столбцов Этот флажок установлен по умолчанию. Рекомендуется убедиться, что верхняя ячейка в каждом столбце на листе Excel (или в настраиваемом диапазоне) содержит заголовки столбцов, а не строки данных. Заголовки не являются обязательными, но они делают импортированные данные значительно более удобными для работы.
Остальные страницы мастера могут быть необязательными. Выполните одно из указанных ниже действий.
- Нажмите кнопку Готово , если вы хотите, чтобы мастер выберет уникальный идентификатор (ключевой столбец).
- Нажмите кнопку Далее , чтобы выбрать собственный уникальный идентификатор (Key колумм).
Источник: support.microsoft.com
Как установить надстройку для Excel !SEMTools
Подключение к источнику данных средствами MS Excel
Бывают ситуации, когда на рабочей станции отсутствуют такие средства взаимодействия с БД как: MS SQL Server Management Studio, Aquafold Aqua Data Studio, DBeaver и т.п., а вероятность их установки в краткосрочной перспективе близка к нолю. В то же время, присутствует острая необходимость подключения к этой самой БД и работы с данными. Как оказалось, на помощь может прийти старый добрый MS Excel.
13 682 просмотров
В моем случае требовалось подключиться к MS SQL Server, однако, MS Excel умеет устанавливать соединение не только с ним, но и с большинством современных БД: MySQL, PostgreeSQL, IBM DB2 и даже Oracle и Teradata, а также с файлами данных CSV, XML, JSON, XLS(X), MDB и другими.
Теперь немного о действиях, совершенных мной с целью подключения к базе:
В новой книге на ленте выбираем «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Из других источников» -> «(4) С сервера SQL Server».
Далее, в окне Мастера подключения к данным, заполняем «(1) Имя сервера» -> «(2) Учетные сведения»[ -> «(3) Имя пользователя» и «Пароль»]. Таким образом, мы сообщаем MS Excel, с каким сервером мы хотим установить соединение и какой метод аутентификации хотим использовать. Я использовал «проверку подлинности Windows», но возможно также указать учетные данные отличные от установленных в Windows.
Выбираем целевую «(1) Базу данных» -> «(2)(3) Определенную таблицу» или «Несколько таблиц» или же базу в целом (тогда оба «чекбокса» оставляем пустыми).
После всех проделанных манипуляций, Мастер подключения предложит сохранить файл подключения. Потребуется задать «(1) Имя файла». Желательно также указать «(2) Описание» и «(3) Понятное имя файла», чтобы спустя время было понятно какой файл подключения к какой базе или таблице обращается.
Теперь выбрать созданное подключение можно будет следующим образом: «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Существующие подключения».
Открыв только что созданное подключение, в случае если вы соединялись с базой в целом, MS Excel опять предложит выбрать одну или несколько конкретных таблиц:
Определив таблицы, MS Excel предложит выбрать «(1) Способ представления данных» и «(2) Куда следует поместить данные». Для простоты я выбрал табличное представление и размещение на уже имеющемся листе, чтобы не плодить новые. Далее следует нажать на «(3) Свойства».
В свойствах подключения, нужно перейти на вкладку «(1) Определение». Здесь можно выбрать «(2) Тип команды». Даже если требуется выгружать лишь одну таблицу без каких-либо связей, настоятельно рекомендую выбрать SQL команду, чтобы иметь возможность ограничить размер выгружаемой таблицы (например, с помощью TOP(n)). Так, если вы попытаетесь выгрузить целиком таблицу базы, это может привести в лучшем случае к замедлению работы MS Excel, а в худшем к падению программы, к тому же – это необоснованная нагрузка на сам сервер базы данных и на сеть. После того как «(3) Текст команды» будет введен и нажата кнопка «ОК», MS Excel предложит сохранить изменения запроса – отвечаем положительно.
В итоге получаем данные прямо из базы, что и требовалось.
Источник: vc.ru
Старт работы с Excel на C#
В современном мире разработки приложений нередко встает необходимость работы с Excel документами. Чаще всего это разного рода отчеты, но иногда xls/x файлы используются в качестве хранилища данных. Например, если пользователь должен иметь возможность загрузить данные в приложение или выгрузить, в человеко-читаемом виде, Excel де-факто является стандартом. Относительно дружелюбный интерфейс, прозрачная структура, в купе с его распространенностью. трудно навскидку назвать решение лучше.
Однако, у многих Excel до сих пор ассоциируется с чем-то тяжелым, неповоротливым и сложным. Давайте посмотрим, как мы — обычные C# разработчики, можем легко сформировать простой Excel документ, на примере табличного отчета.
Историческая справка
На 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 документ не может существовать без страниц, должна быть хотя бы одна. Добавляем ее, все интуитивно понятно:
var sheet = package.Workbook.Worksheets .Add(«Market Report»);
Запускаем снова и. вот оно! Теперь наше приложение генерирует документ и, хотя там еще ничего нет, он уже весит 2,5KB — значит мы работаем с Excel правильно и все идет как надо.
Вывод данных
Давайте выведем основную информацию по компании в шапку. Для доступа к конкретной ячейки объект Cells на странице пакета снабжен удобным индексатором. При этом, до конкретной ячейки можно достучаться как через номер строки и столбца, так и по привычному всем буквенно-числовому коду:
sheet.Cells[«B2»].Value = «Company:»; sheet.Cells[2, 3].Value = report.Company.Name;
Полный код вывода шапки.
sheet.Cells[«B2»].Value = «Company:»; sheet.Cells[2, 3].Value = report.Company.Name; sheet.Cells[«B3»].Value = «Location:»; sheet.Cells[«C3″].Value = $», » + $», » + $»»; sheet.Cells[«B4»].Value = «Sector:»; sheet.Cells[«C4»].Value = report.Company.Sector; sheet.Cells[«B5»].Value = report.Company.Description;
Для вывода исторических данных понадобится как минимум шапка таблицы и цикл по массиву History:
sheet.Cells[8, 2, 8, 4].LoadFromArrays(new object[][] < new []>); var row = 9; var column = 2; foreach (var item in report.History)
Предлагаю обратить внимание на метод LoadFromArrays, который заполняет диапазон ячеек рваным(зубчатым) массивом. Здесь мы можем видеть, что типизация теряется и передавая массив object мы ожидаем что EPPlus в конечном итоге использует ToString, чтобы записать переданное в ячейки.
Стилизация
Если вы прямо сейчас откроете документ, то вы возможно увидите не то, что хотелось бы отдать в продакшн в пятницу вечером.
Как это выглядит
Во-первых, шапка никак не выделяется, во-вторых таблица не имеет границ. выравнивание пляшет, даты отображаются магическими числами, а капитализация «уходит в какую-то математику» — как это прокомментировал аналитик.
Да, на все эти красивости у нас уйдет больше года кода, чем на сам вывод данных, и, в конечном тоге, получившаяся каша из логики вывода данных и разметки заставит некоторых усомниться в их компетентности. но, мы же backend разработчики, так давайте сверстаем Excel Sheet!
Размер ячеек
Из коробки у нас есть возможность сделать автофит а так же вручную выставить ширину в соответствии с нашей ситуацией. А ситуация у нас не самая хорошая — по задумке аналитика в шапке у ячеек должен быть автофит, а у ячеек таблицы — тоже автофит. Так в чем же подвох?
Если вы когда-нибудь до этого открывали Excel, то возможно знаете, что ширина ячеек не может отличаться в рамках столбца и автофит будет по самому широкому контенту ячейки. Однако, простые вещи бывает нетак то просто объяснить. Но если вы справитесь, то вот как это будет выглядеть в коде:
sheet.Cells[1, 1, row, column + 2].AutoFitColumns(); sheet.Column(2).Width = 14; sheet.Column(3).Width = 12;
Формат данных
Как и большая часть стиля ячейки, он задается через одноименное свойство Style. Обратите внимание на вычисление 3-го аргумента индексатора. Это звоночек некачественного кода, но к этому мы вернемся в позже.
sheet.Cells[9, 4, 9 + report.History.Length, 4].Style.Numberformat.Format = «yyyy»; sheet.Cells[9, 2, 9 + report.History.Length, 2].Style.Numberformat.Format = «### ### ### ##0»;
Выравнивание
Его можно задать как на ячейке, так и на диапазоне. На самом деле, для EPPlus, это одна и та же сущность — некий ExcelRange, описывающий диапазон ячеек, в том числе и со всего 1 ячейкой.
sheet.Column(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; sheet.Cells[8, 3, 8 + report.History.Length, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
Стиль текста
Также легко задается, используя Style.Font, кстати, здесь, на 2-й строчке, мы впервые указываем диапазон так, как привыкли его видеть пользователи Excel:
sheet.Cells[8, 2, 8, 4].Style.Font.Bold = true; sheet.Cells[«B2:C4»].Style.Font.Bold = true;
Границы
Задаем стиль линии, а также ее толщину. К этому моменту от кол-ва магических чисел-параметров индексатора уже рябит в глазах, но мы уже на финишной прямой. не так ли?
sheet.Cells[8, 2, 8 + report.History.Length, 4].Style.Border.BorderAround(ExcelBorderStyle.Double); sheet.Cells[8, 2, 8, 4].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
График
«Ну что за отчет без графиков, верно, Карл?» — ловко подметит специалист по тестированию, и не важно, что этого не было в ТЗ а на часах уже половина 9-го.
Хотя график как сущность сам по себе сложнее таблиц и с графиками мы не работаем каждый день, EPPlus предоставляет довольно понятный API. Давайте добавим простейший график, отражающий рост капитализации:
var capitalizationChart = sheet.Drawings.AddChart(«FindingsChart», OfficeOpenXml.Drawing.Chart.eChartType.Line); capitalizationChart.Title.Text = «Capitalization»; capitalizationChart.SetPosition(7, 0, 5, 0); capitalizationChart.SetSize(800, 400); var capitalizationData = (ExcelChartSerie)(capitalizationChart.Series.Add(sheet.Cells[«B9:B28»], sheet.Cells[«D9:D28»])); capitalizationData.Header = report.Company.Currency;
Еще, может понадобиться защитить страницу от редактирования:
sheet.Protection.IsProtected = true;
На этом все, репозиторий с рабочим приложением находится здесь.
Заключение
О чем говорит финальная версия метода Generate?
public byte[] Generate(MarketReport report) < var package = new ExcelPackage(); var sheet = package.Workbook.Worksheets .Add(«Market Report»); sheet.Cells[«B2»].Value = «Company:»; sheet.Cells[2, 3].Value = report.Company.Name; sheet.Cells[«B3»].Value = «Location:»; sheet.Cells[«C3″].Value = $», » + $», » + $»»; sheet.Cells[«B4»].Value = «Sector:»; sheet.Cells[«C4»].Value = report.Company.Sector; sheet.Cells[«B5»].Value = report.Company.Description; sheet.Cells[8, 2, 8, 4].LoadFromArrays(new object[][] < new []>); var row = 9; var column = 2; foreach (var item in report.History) < sheet.Cells[row, column].Value = item.Capitalization; sheet.Cells[row, column + 1].Value = item.SharePrice; sheet.Cells[row, column + 2].Value = item.Date; row++; >sheet.Cells[1, 1, row, column + 2].AutoFitColumns(); sheet.Column(2).Width = 14; sheet.Column(3).Width = 12; sheet.Cells[9, 4, 9+ report.History.Length, 4].Style.Numberformat.Format = «yyyy»; sheet.Cells[9, 2, 9+ report.History.Length, 2].Style.Numberformat.Format = «### ### ### ##0»; sheet.Column(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; sheet.Cells[8, 3, 8 + report.History.Length, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; sheet.Column(4).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; sheet.Cells[8, 2, 8, 4].Style.Font.Bold = true; sheet.Cells[«B2:C4»].Style.Font.Bold = true; sheet.Cells[8, 2, 8 + report.History.Length, 4].Style.Border.BorderAround(ExcelBorderStyle.Double); sheet.Cells[8, 2, 8, 4].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; var capitalizationChart = sheet.Drawings.AddChart(«FindingsChart», OfficeOpenXml.Drawing.Chart.eChartType.Line); capitalizationChart.Title.Text = «Capitalization»; capitalizationChart.SetPosition(7, 0, 5, 0); capitalizationChart.SetSize(800, 400); var capitalizationData = (ExcelChartSerie)(capitalizationChart.Series.Add(sheet.Cells[«B9:B28»], sheet.Cells[«D9:D28»])); capitalizationData.Header = report.Company.Currency; sheet.Protection.IsProtected = true; return package.GetAsByteArray(); >
Во-первых, прежде всего, о том, что мы успешно справились с задачей, а именно, сгенерировали свой первый Excel отчет, поработали со стилями и даже решили пару попутных проблем.
Во-вторых, возможно имеет смысл искать новою работу, но, забегая вперед, я бы с этим не спешил. Если данная публикация наберет 1+ просмотров, то во второй части мы поговорим о том, как можно отделить стилизацию от логики заполнения данными, упростить манипуляции над ячейками и в целом сделаем код боле поддерживаемым.
Источник: habr.com