Для проверки ваших знаний в Excel VBA Вы можете попробовать решить следующую тестовую задачу (кейс). Она может потребоваться при приеме на работу как разработчика VBA и SQL Server. Вот её содержание.
- Необходимо создать таблицу на сервере MSSQL с 3 столбцами
- Реализовать загрузку данных на сервер из Excel (200 000 записей) через VBA или Python через UI. При реализации на Python можно использовать любой UI framework
- Сделать хранимую процедуру на MS SQL сервере для выгрузки данных за определенный период.
- Реализовать возможность указывать период от до (на форме VBA или значения в Excel, на выбранной UI framework форме) и результат хранимой процедуры из 3 пункта выгружать в новую книгу Excel. Так же реализовать форматирование отчета (закрепление шапки и формат столбцов)
- Отчет должен содержать столбцы: Год, Месяц, Артикул, средние продажи за год и месяц, доля продаж артикула за выбранный период
- Логика отчета с расчетом средних продаж и доли продаж должна быть реализована в хранимой процедуре
- Приложение. Файл data.xlsb с 200 тысяч записей.
Оценка работы
На первый взгляд задание простое, исходных данных не много, но есть нюансы. Их придется учитывать, а также то, что конкурсное задание могут решить правильно ваши конкуренты. Работодателю придется выбирать между вами и ими. Выход из этого такой. Надо уделить внимание качеству интерфейса, попытаться сделать его профессионально, например, с инсталляцией. Для решения задачи, я выбрал 3 этапа работы, которые и отобразил на форме Excel.
- Этап. Настройка интерфейса .
- Этап. Загрузка в базу данных из Excel.
- Этап. Создание отчёта за период
Интерфейс формы в Excel
Добавим на форму 3 группы объектов по числу этапов. 1 этап чисто вспомогательный, но нужен для того, чтобы облегчить этап тестирования программы. Это может создать благоприятное впечатление о вас ещё до проверки знаний. Обратите внимание, что форма имеет специальный фон и иконки для каждого этапа. Красивый дизайн это тоже дополнительный плюс для разработчика.
Рассмотрим четыре кнопки которые находятся в первой группе объектов 1 этапа.
- Кнопка "1. Выбрать sql сервер и базу данных" означает, что при её нажатии вы перейдёте в интерфейс для редактирование строки соединения.
- Кнопка "2. Создать объекты базы из script.sql" означает, что будет выполнен скрипт для создания в пустой базе данных на сервере таблицы и sp процедуры.
- Кнопка "script.sql" означает, что при её нажатии будет открыт файл script.sql для редактирования.
- Кнопка Шаблон.xlt позволят открыть файл excel для редактирования.
Другие кнопки из других этапов не рассматриваем, так как понятно для чего они нужны. Единственное нужно отметить тот момент, что используются списки для выбора дат фильтрации загрузки и выгрузки данных в Excel. Используем их исключительно потому, что объект позволяющий водить даты, DTPicker, присутствует в 32 разрядной версии Windows, a в других может быть и не быть.
Файловая система
Создаём систему хранения файлов на диске. Удобно будет, когда после разархивации файла с решением, проверяющий увидет всего один главный файл для запуска, и ему не надо будет напрягаться, чтобы начать тестировать приложения. Это плюс для разработчика.
Далее необходимо разработать вспомогательные файлы и поместить их в папку Data.
Назначение файлов.
- Data.xlsb - это файл с исходными данными для загрузки в базу. Он есть в задании, его не надо разрабатывать.
- Script.sql - это файл надо разработать для создания базы данных на сервере включая таблицу и процедуру. Нужен для этапа установки.
- Соединение.udl - это файл нужен для хранения строки соединениях к базе данных sql сервера. Очень нужен, т.к. позволяет редактировать соединение в графическом интерфейсе.
- Шаблон.xlt это файл который позволяет вывести данные из базы в excel. Нужен для сохранения форматирования полей. Это требование есть в задании.
Создание базы данных SQL
На первом этапе разработки необходимо разработать таблицу и сохранённую процедуру для SQL server. Скрипты sql данного задания можно разработать в программе Microsoft SQL Server Management Studio. Когда вы разработаете базу данных окончательно, то сохраните скрипт всех объектов в формате *.sql. Для того, чтобы его можно было запустить из Excel, а не SSMS, т. к. это удобно для проверяющего. Обязательно в скрипте напишите комментарии, т.к. это упростит понимание кода. Это плюс для вас.
-- =============================================
-- Author: Виктор Конюков
-- Create date: 02.07.2024
-- Description: Вывод данных в отчет. Он должен содержать столбцы: Год, Месяц, Артикул, средние продажи за год и месяц,
-- доля продаж артикула за выбранный период
-- Testing
/*
select * from dbo.data
exec ReportExcel_SP '02.03.2021', '02.04.2021'
*/
-- =============================================
CREATE PROCEDURE [dbo].[ReportExcel_SP] (
@Date1 as Datetime,
@Date2 as Datetime )
AS
BEGIN
SET NOCOUNT ON;
--DECLARE @TEST int
--SET @TEST = 0
-- Средние продажи за год
SELECT year(t.dt) as y, t.article, avg(t.kg) as avg_year into #year_avg from dbo.data t with(nolock)
group by t.article, year(t.dt)
-- Средние продажи за месяц
SELECT year(t.dt) as y, month(t.dt) as m, t.article, avg(t.kg) as avg_month into #month_avg from dbo.data t with(nolock)
group by t.article, year(t.dt), month(t.dt)
-- Продажи всего
select t.article, sum(t.kg) as sumAll into #sumSalesAll from dbo.data t with(nolock)
group by t.article
-- Продажи за период
select t.article, sum(t.kg) as sumPeriod into #sumSalesPeriod from dbo.data t with(nolock)
where dt between @date1 and @date2
group by t.article
Подключение к базе данных
При подключения к базе данных SQL сервера, чтобы не изменять строку соединения текстовым редактором, мы можем использовать специальный интерфейс. Для этого достаточно создать udl файл. При открытии его в Windows будет отображена специальная форма для настройки соединения. Проверяющему достаточно будет настроить строку и создать пустую базу данных. Все остальное сделает ваша программа. По умолчанию укажите в файле сервер .sqlexpress и базу данных TestExcel.
Импорт из Excel
Для импорта из Excel потребуется написать программный код на VBA. Здесь существует несколько особенностей, которые позволят повысить скорость загрузки и сделать интерфейс более дружественным
Данные из Excel предварительно нужно загрузить в массив.
' Обрабатываем файл
Me.LabelInfo.Caption = "Открываем файл Excel ..."
Set xlApp = CreateObject("Excel.Application") ' Открываем Excel
Set xlBook = xlApp.Workbooks.Open(Filename:=xlFileName) ' Открываем файл
Set xlSheet = xlBook.Sheets("Sales") ' Выбираем лист книги
'xlApp.Visible = True ' Отображаем Excel
' Загружаем Excel данные в массив
Me.LabelInfo.Caption = "Загружаем Excel данные в массив ..."
Dim arr() As Variant
arr = xlSheet.Range("A2").CurrentRegion
Необходимо использовать библиотеку ADO, а в качестве строки соединения использовать udl файл.
' Определяем новое соединение
Set cnn = New ADODB.Connection
' Открываем соединение, используя файл udl
cnn.Open "File Name=" & Application.ActiveWorkbook.Path & "\Data\Соединение.udl"
Нужно загружать записи не по одной, а пакетом, чтобы повысить скорость импорта данных. Обязательно нужно отобразить процесс загрузки на форме. Записей очень много, более 200 000.
' Выполняем построчную загрузку со второй строки, т.к. первая строка - это заголовки
Dim dt As Date, article As String, kg As Double
Dim row As Long, cnt As Long, sqlStart As String, ch As String
cnt = 0
ch = ""
sqlStart = "insert into dbo.data (dt, article,kg) values "
For row = 2 To UBound(arr, 1) - 1
dt = arr(row, 1)
If (dt >= Me.DateImport1 And dt <= Me.DateImport2) Or (Me.DateImport1 = "" And Me.DateImport2 = "") Then
cnt = cnt + 1
article = arr(row, 2)
kg = arr(row, 3)
' Делаем загрузку ввиде пула записей (20 штук)
If (cnt Mod 20 = 0 And sql <> "") Then
cnn.Execute sqlStart + sql + ";"
ch = ""
sql = ""
End If
sql = sql + ch + "('" & dt & "', '" & article & "', " & kg & ")"
ch = ","
' Информируем пользователя через каждые 100 записей
If (row Mod 100 = 0) Then
Me.LabelInfo.Caption = "Загружена строка " & row & " из " & MaxRows
DoEvents ' Обновляем форму
End If
End If
Next
Me.LabelInfo.Caption = "Загрузка завершена. Всего загружено строк: " & cnt & " из " & MaxRows
Шаблон в Excel
Шаблон excel достаточно простой и его можно нарисовать быстро. Он нужен для сохранения форматированного текста, как указано в задании. Главное обратите внимание, что в ячейках D4, E4, F4 используется ДВЕ строки и одна из них выделена другим цветом. Попробуйте это сделать самостоятельно. Это еще плюс вам как разработчику.
Для выгрузки данных в Excel пригодится следующий код.
' Устанавливаем ссылку на страницу
Set xlApp = CreateObject("Excel.Application") ' Открываем Excel
Set xlBook = xlApp.Workbooks.Open(Filename:=xlFileName) ' Открываем файл
Set xlSheet = xlBook.Sheets("Анализ") ' Выбираем лист книги
xlApp.Visible = True ' Отображаем Excel
Me.LabelInfo2.Caption = "Выгружаются данные ..."
DoEvents
' Записываем данные в ячейки
xlSheet.Range("B2").Value = Me.DateReport1.Text 'Me.DTPicker1.Value
xlSheet.Range("C2").Value = Me.DateReport2.Text 'Me.DTPicker1.Value
'Используем запрос из базы данных для заполнения Excel
Dim cn As New ADODB.Connection, rs As New ADODB.Recordset, sql As String
cn.Open "File Name=" & Application.ActiveWorkbook.Path & "\Data\Соединение.udl"
' Выполняем процедуру
'SQL = "exec ReportExcel_SP '" & Me.DTPicker1.Value & "', '" & Me.DTPicker2.Value & "'"
sql = "exec ReportExcel_SP '" & Me.DateReport1.Value & "', '" & Me.DateReport2.Value & "'"
rs.Open sql, cn
xlSheet.Range("A5").CopyFromRecordset rs
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
Me.LabelInfo2.Caption = "Отчет создан успешно"
DoEvents
Инструкция по интерфейсу
На заключительном этапе разработаем инструкцию по интерфейсу. Разместите ее прямо в файле Excel. Еще добавим и массив данных для фильтрации. Это будет удобно для проверяющего.
Заключение
Приведенный здесь алгоритм позволит вам решать задачи по импорту и экспорту данных в Excel с использованием VBA и SQL Server