|
Проблема создания отчетов до сих пор остается
актуальной. Генератор отчетов, поставляемый вместе с VBStudio
6.0 мало функционален и излишне заморочен, а Crystal
Reports все еще стоит немалых денег :). К тому же, хотя
Crystal Reports позволяет делать отчеты практически любой
сложности, такая функциональность не часто бывает востребована,
а время, необходимое на его изучение, исчисляется неделями.
Пора сформулировать вопрос: что делать, если нужно быстро
навоять простенький отчет с более-менее приемлемым оформлением.
Я приведу вам один из вариантов ответа — используйте Excel.Application
или Word.Application. Лично я сам юзаю эти объекты
в качестве генератора отчетов довольно давно, потому успел
по достоинству оценить всю прелесть подобного решения. Судите
сами: трудно представить себе машину (компьютер) на которой
не был бы установлен офисный пакет. Если вы пишите базу
данных (БД) для организации, то наличие Excel’а и Word’а
можете проконтролировать самолично. К тому же пользователей
очень радует :) привычный интерфейс, поэтому вам не придется
учить их работать с готовым отчетом. Если я убедил вас в логичности
подобного решения, давайте посмотрим, как же сие осуществить.
Прежде всего идем в Project/References… и ставим галочку на
Microsoft Excel 9.0 Object Library (см. рис. 1).
Рис. 1
Теперь определимся с информацией, какую необходимо
вывести в отчет. Я приведу пример кода из собственной проги,
которую писал для родной конторы. В ней операторы (пользователи
проги) продавали клиентам разовые услуги (отправка факса,
ксерокопирование, пользование интернетом и пр.), и товары
(телефоны, пейджеры и пр.). То есть на конец смены
каждому оператору (они менялись из смены в смену) был необходим
отчет сколько и чего он продал, плюс общая сумма всех операций.
В базе данных информация о продажах у меня разнесена в две
разные таблицы («товары» и «услуги»), следовательно мне понадобится
два рекордсета. У вас он может быть один, или три, или еще
с десяток — это зависит от того, как вы организовали свою
БД, но суть создания отчета при этом не изменится.
Итак, создаем новый модуль, называем его Report, открываем
его окно и пишем новую процедуру под названием ReportSmena.
Я делаю эту процедуру в отдельном модуле, так как мне ее приходится
вызывать из разных мест программы. Если вам сие не нужно,
вписывайте код в обработку кнопки «Смена» (или как вы там
ее назвали…)
Листинг 1
Option Explicit
Public objExcel As Excel.Application
‘ это в General Declaration
'*****************************************************СОЗДАНИЕ
ОТЧЕТА
Public Sub ReportSmena(strNameNow
As String, strDataNow As String)
Dim strRange As
String ‘переменная диапазона ячеек
Dim rstSmenaServis As
New ADODB.Recordset ‘рекордсет
продажи услуг
Dim rstSmenaTrade As
New ADODB.Recordset ‘рекордсет
продажи товаров
Dim i As Integer
Dim intStr As Integer
‘переменная для отслеживания текущей строки в листе Excel
Dim intStrFirst As
Integer ‘переменная начала диапазона
строк таблицы
Dim intStrTeg As
Integer ‘сюда мы запомним позицию
вывода фразы «Не продавались» для Товаров
Dim curSumAll As
Double ‘переменная накопления
общей суммы
'**********************************************************************УСТАНОВКИ
ЛИСТА Excel
Set objExcel = New
Excel.Application ‘создаем экземпляр
объекта Excel.Application
objExcel.Visible = True
‘делаем его видимым (открываем Excel)
objExcel.SheetsInNewWorkbook = 1 ‘устанавливаем
количество листов в открываемой книге
objExcel.Workbooks.Add
objExcel.ActiveSheet.Range("a1:m100").Font.Name
= "Times New Roman" ‘шрифт
в диапазоне
objExcel.ActiveSheet.Columns(1).ColumnWidth = 3
‘устанавливаем ширину столбцов
objExcel.ActiveSheet.Columns(2).ColumnWidth = 5
objExcel.ActiveSheet.Columns(3).ColumnWidth = 35
objExcel.ActiveSheet.Columns(4).ColumnWidth = 8
objExcel.ActiveSheet.Columns(5).ColumnWidth = 8
objExcel.ActiveSheet.Columns(6).ColumnWidth = 8
objExcel.ActiveSheet.Columns(7).ColumnWidth = 11
''''''''''*****************************************************************ЗАГОЛОВОК
objExcel.ActiveSheet.Range("a1:m6").Font.Bold =
True
objExcel.ActiveSheet.Rows(1).Font.Size = 14
‘сверху посредине пишем «ОТЧЕТ» жирным шрифтом
objExcel.ActiveSheet.Cells(1, 3) = "ОТЧЕТ"
objExcel.ActiveSheet.Range("a1:c1").HorizontalAlignment
= xlRight ‘выравнивание в ячейке
objExcel.ActiveSheet.Range("a2:m100").Font.Size
= 10 ‘размер шрифта в диапазоне
objExcel.ActiveSheet.Cells(2, 1) = "Оператор: "
& strNameNow
objExcel.ActiveSheet.Cells(3, 1) = "Смена: " &
Replace(strDataNow, ".", "/")
objExcel.ActiveSheet.Range("a3:g3").Borders.Item(xlEdgeBottom).LineStyle
= xlDouble ‘подчеркиваем двойной линией
objExcel.ActiveSheet.Range("a5:b5").Font.Bold
= True
objExcel.ActiveSheet.Cells(5, 2) = "УСЛУГИ"
'''*****************************************************ВЫБИРАЕМ
РЕКОРДСЕТ rstSmena***
‘я не буду описывать выборку рекордсета (это выходит за рамки
темы), надеюсь, вы знаете, как это делать ?,
‘скажу только, что здесь мы выбираем данные по услугам
If rstSmenaServis.BOF <> True
And rstSmenaServis.EOF <> True
Then ‘если рекордсет не пуст
objExcel.ActiveSheet.Range("a5:g5").Borders.Item(xlEdgeBottom).LineStyle
= xlDouble
‘Заголовок таблицы УСЛУГИ
objExcel.ActiveSheet.Range("a6:m100").Font.Bold
= False
objExcel.ActiveSheet.Cells(6, 1) = "№"
objExcel.ActiveSheet.Cells(6, 2) = "Талон"
objExcel.ActiveSheet.Cells(6, 3) = "Наименование"
objExcel.ActiveSheet.Cells(6, 4) = "Код"
objExcel.ActiveSheet.Cells(6, 5) = "Кол-во"
objExcel.ActiveSheet.Cells(6, 6) = "Цена"
objExcel.ActiveSheet.Cells(6, 7) = "Сумма"
objExcel.ActiveSheet.Range("a6:g6").HorizontalAlignment
= xlCenter ‘выравнивание по центру
objExcel.ActiveSheet.Range("a6:g6").Interior.ColorIndex
= 15 ‘делаем заливку шапки таблицы серым
цветом
objExcel.ActiveSheet.Range("a6:g6").Borders.Item(xlEdgeBottom).LineStyle
= xlDouble
i = 0
Do ‘заполняем
таблицу данными
objExcel.ActiveSheet.Cells(i + 7, 4).HorizontalAlignment =
xlRight
objExcel.ActiveSheet.Cells(i + 7, 4) = rstSmenaServis.Fields("Код")
objExcel.ActiveSheet.Cells(i + 7, 2) = rstSmenaServis.Fields("№")
objExcel.ActiveSheet.Cells(i + 7, 3) = rstSmenaServis.Fields("Наименование")
objExcel.ActiveSheet.Cells(i + 7, 5) = rstSmenaServis.Fields("Количство")
objExcel.ActiveSheet.Cells(i + 7, 6).NumberFormat = "#,##0.00$"
‘денежный формат
objExcel.ActiveSheet.Cells(i + 7, 6) = rstSmenaServis.Fields("Цена")
objExcel.ActiveSheet.Cells(i + 7, 7).NumberFormat = "#,##0.00$"
‘денежный формат
objExcel.ActiveSheet.Cells(i + 7, 7) = rstSmenaServis.Fields("Сумма")
rstSmenaServis.MoveNext
objExcel.ActiveSheet.Cells(i + 7, 1) = i + 1 ‘порядковый
номер
i = i + 1
Loop Until rstSmenaServis.EOF
intStr = i + 6
strRange = "a" & intStr & ":"
& "g" & intStr ‘ «собираем»
строкову с диапазоном (a<intStr>:g<intStr>)
objExcel.ActiveSheet.Range(strRange).Borders.Item(xlEdgeBottom).LineStyle
= xlSolid
strRange = "=SUM(G7:G" & intStr & ")"
‘ «собираем» формулу суммы
intStr = intStr + 1
objExcel.ActiveSheet.Cells(intStr, 7).NumberFormat = "#,##0.00$"
objExcel.ActiveSheet.Cells(intStr, 7).Value = strange ‘вставляем
формулу в ячейку
curSumAll = curSumAll + objExcel.ActiveSheet.Cells(intStr,
7) ‘здесь накапливаем общую сумму
objExcel.ActiveSheet.Cells(intStr, 6) = "Итого:"
objExcel.ActiveSheet.Cells(intStr, 6).Font.Bold = True
objExcel.ActiveSheet.Cells(intStr, 7).Font.Bold = True
strRange = "e" & intStr
objExcel.ActiveSheet.Range(strRange).HorizontalAlignment =
xlRight
Else ‘а если
рекордсет пуст, пишем, что не продавались
objExcel.ActiveSheet.Cells(5, 5).Font.Bold = False
objExcel.ActiveSheet.Cells(5, 5) = "Не продавались"
intStr = intStr + 7
End If
rstSmenaServis.Close ‘закрываем рекордсет
Set rstSmenaServis = Nothing
'''''****************************************************************ТОВАРЫ*************
‘выбираем данные по товарам. Объявляем
рекордсет, и выбираем уже товары. Тут все тоже самое, что
Как вы уже заметили, я передаю в процедуру две
переменные. Одна из них несет информацию о дате, потому как
вполне возможно, что вам понадобится сформировать отчет по
произвольной дате, а вторая содержит имя оператора (пользователя),
чей отчет необходимо сформировать. Если выборку рекордсетов
вы будете делать именно в этом коде, возможно, имеет смысл
передавать переменную strDataNow в формате даты, а не а формате
строки, потому как в SQL-запрос все равно придется
преобразовывать значение в формат даты, но можно ее преобразовать
тут же, перед созданием запроса. Тут выбор всецело за вами.
Если этот код кажется вам слишком сложным, могу вас заверить,
что это впечатление обманчиво. Длинный листинг получился у
нас потому, что нам необходимо отследить, отформатировать
и заполнить значениями практически каждую ячейку в выбранном
диапазоне. Для этого придется считать строки и запоминать
некоторые из них, как мы это делали, например, для начальной
позиции вывода данных таблицы товаров. Выводить данные в таблицу
услуг мы начали с фиксированного значения, поскольку могли
точно определиться с какой строки рисовать таблицу. Но поскольку
мы не знали заранее, сколько будет в этой таблице строк, нам
необходимо было этот диапазон отследить, что мы и сделали
с помощью переменной intStrFirst, а сам диапазон восстановили
строкой кода:
strRange = "a" & intStr & ":"
& "g" & intStr
Вообще, «собирать» строковую переменную с помощью конкатенации
довольно удобно, и мы не раз этим пользовались, например,
когда собирали формулу суммы:
strRange = "=SUM(G" & intStrFirst & ":G"
& intStr & ")"
При этом в ячейке Excel’а появится формула. Удобно, не правда
ли?
Надо сказать пару слов о том, как будет работать этот код.
Вернее, как Excel будет с ним работать. По сути дела, мы просто
передаем и заставляем Excel выполнить код VBA. Все,
что написано в листинге 1 будет выполняться последовательно,
и так же будет отображаться в листе Excel’а. Сначала, вы увидите,
как откроется Excel, а потом на ваших глазах, строка за строкой
будут форматироваться ячейки и заполняться данными. Если в
коде присутствуют ошибки, Excel выполнит (нарисует), все,
что было до нее (до ошибки) и остановится. Это удобно для
отладки кода.
Если вас не устраивает то форматирование, которое у нас получилось,
или вы хотите больше узнать о константах Excel VBA,
обратитесь к справке Excel’а. У меня еще не было случая,
чтобы я не нашел там описание чего-то необходимого. Ну и на
рисунке 2 показан сам отчет, сформированный наши кодом.
Рис.2
Вот, в общем то и все. Удачи в прогерском деле
.
Вернуться к разделу
статей
|
