В предыдущих постах мы рассмотрели основные программные блоки необходимые для большинства обработок отчетов в Calc. Эти блоки можно копировать в свои макросы и изменяя параметры создавать новые уникальные обработки. В этом посте мы сделаем первую полноценную обработку на основе простого макроса Учет производства "Отчет средняя себестоимость комплексов" на основе файла "Продажи блюд производства (с отгрузками)".
1. Необходимо отфильтровать на отдельный лист только списания комплексов с МХ Столовая
2. Вставить столбец СтолСебсНДС и провести в нем расчеты НДС
3. Подсчитать итоги по столбцам - суммы средние значения.
Выгрузим и откроем файл "Продажи блюд производства (с отгурзками)". Откроем его и нажмем сочетание клавиш ALT+F11. Раскроем библиотеку Standart, встаем курсом на Mоdule 1 и жмем "Редактировать". Откроется окно редактора StarBasic. Внизу на закладках модулей жмем ПКМ - Вставить - Модуль Basic.Переименуем в его Stolov. В этом модуле есть заготовка Sub Main. Поменяем Main на что-нибудь осмысленное. Т.к. он будет главным модулем - дадим имя Stolov - одноименные имена модуля бейсик и главного макроса очень удобно использовать.
Начнем заполнять код нашими заготовками, если будут попадаться не изученные куски кода - буду комментировать подробнее.
Dim oDescriptor,oNewDescriptor,bCopyOutputData as Boolean, bUseCriteria as Boolean
oDoc = ThisComponent
oSheet = ThisComponent.getSheets().getByIndex(0)
oAddr = oSheet.getCellRangeByName("A1:AF65535").getRangeAddress()
oDoc.DatabaseRanges.addNewByName("MyName", oAddr)
oRange = oDoc.DatabaseRanges.getByName("MyName")
set oDescriptor = oRange.getFilterDescriptor
oSheet = ThisComponent.createInstance ("com.sun.star.sheet.Spreadsheet")
ThisComponent.Sheets.insertByName ("Столовая для персонала", oSheet)
oCellAddress = ThisComponent.Sheets (1).getCellByPosition(0, 0).getCellAddress()
oFD = oRange.getFilterDescriptor()
With oFD
.CopyOutputData = True
.OutputPosition = (oCellAddress)
End With
oField = createUnoStruct("com.sun.star.sheet.TableFilterField")
With oField
.Field = 3
.Operator = com.sun.star.sheet.FilterOperator.EQUAL
.StringValue = "Столовая для персонала"
End With
oFD.setFilterFields(Array(oField))
oRange.refresh()
'Удаляем ненужный нам лист Sheet1
oSheet = ThisComponent.createInstance ("com.sun.star.sheet.Spreadsheet")
ThisComponent.Sheets.removeByName ("Sheet1", oSheet)
'Удаляем ненужные в отчете столбцы
oSheet = ThisComponent.Sheets (0)
oRangeAddress = oSheet.getCellRangeByName("A1:A65536").getRangeAddress()
oSheet.removeRange(oRangeAddress, com.sun.star.sheet.CellDeleteMode.LEFT)
oSheet = ThisComponent.Sheets (0)
oRangeAddress = oSheet.getCellRangeByName("C1:Z65536").getRangeAddress()
oSheet.removeRange(oRangeAddress, com.sun.star.sheet.CellDeleteMode.LEFT)
oSheet = ThisComponent.Sheets (0)
oRangeAddress = oSheet.getCellRangeByName("H1:K65536").getRangeAddress()
oSheet.removeRange(oRangeAddress, com.sun.star.sheet.CellDeleteMode.LEFT)
'Вызываем макрос Raschet_NDS (см. ниже)
Raschet_NDS
' Вызываем макрос Podstan1 (см. ниже)
Podstan1
' Устанавливаем на полученный диапазон формат два знака после запятой
ThisComponent.Sheets (0).getCellRangeByName("C2:H65535").NumberFormat=2
'Вставляем две пустые строки сверху (отступ)
ThisComponent.Sheets (0).ROWs.insertByIndex(0,1)
ThisComponent.Sheets (0).ROWs.insertByIndex(0,1)
'Выделяем первую верхнюю ячейку полученного нами диапазона
oCell = ThisComponent.Sheets (0).getCellByPosition(0, 2)
ThisComponent.CurrentController.Select(oCell)
' вызываем макрос Oformlenie (см. ниже)
Oformlenie
'Сбрасываем выделение диапазона, выделением первой ячейки на листе
oCell = ThisComponent.Sheets (0).getCellByPosition(0, 0)
ThisComponent.CurrentController.Select(oCell)
'Устанавливаем оптимальную ширину столбцов
For I = 0 To 9
Columns = ThisComponent.Sheets (0).Columns(I)
Columns.OptimalWidth = True
Next I
' Выводим в ячейку А1 ГМ и текущую дату
'Текущая дата всегда вызывается функцией DateValue(Now)
ThisComponent.Sheets (0).getCellByPosition(0, 0).String = "ГМ " & " по " & DateValue(Now)
' вызываем макрос Cveta3 (см. ниже)
Cveta3
End Sub
Sub Raschet_NDS
odoc=thiscomponent
oSheet = ThisComponent.Sheets(0)
oCellCursor = oSheet.createCursor()
oCellCursor.GotoStartOfUsedArea(True)
oCellCursor.GotoEndOfUsedArea(True)
Start_Row = oCellCursor.getRangeAddress.StartRow
End_Row = oCellCursor.getRangeAddress.EndRow
'Определяем необходимые ячейки, участвующие в расчете
'Сумма комплекса с НДС будет равна кратному от деления столбцов НДС18/СписСтол
' Плюс СтолСеб
currcell = osheet.getcellbyposition(7, End_Row)
currcell2 = osheet.getcellbyposition(2, End_Row)
currcell5 = osheet.getcellbyposition(5, End_Row)
currcell3 = osheet.getcellbyposition(3, End_Row)
oConv = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv.Address = currcell.getCellAddress
oConv2 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv2.Address = currcell2.getCellAddress
oConv5 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv5.Address = currcell5.getCellAddress
oConv3 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv3.Address = currcell3.getCellAddress
'Сама формула расчета
Podstanovka = "=(" & oConv5.UserInterfaceRepresentation & "/" & oConv2.UserInterfaceRepresentation &")+" & oConv3.UserInterfaceRepresentation
currcell.setFormula (Podstanovka)
odoc.getCurrentController().Select(currcell)
dim document as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "By"
args1(0).Value = 1
args1(1).Name = "Sel"
args1(1).Value = true
dispatcher.executeDispatch(document, ".uno:GoUpToStartOfData", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:FillUp", "", 0, Array())
oSheet = ThisComponent.createInstance ("com.sun.star.sheet.Spreadsheet")
'Делаем заголовок для 8-го столбца с нужной нам строчкой
ThisComponent.Sheets (0).getCellByPosition(7, 0).String = "СтолСебсНДС"
End Sub
Sub Podstan1
'Макрос Podstan1 целиком отвечает за подбитие итогов столбцов
'Просмотрите внимательно этот код, чтобы понять к каких столбцах идет простое суммирование, в каких другая операция. Почему там применяется другая операция)
oCell = ThisComponent.Sheets (0).getCellByPosition(0, 0)
ThisComponent.CurrentController.Select(oCell)
odoc=thiscomponent
oSheet = ThisComponent.Sheets(0)
oCellCursor = oSheet.createCursor()
oCellCursor.GotoStartOfUsedArea(True)
oCellCursor.GotoEndOfUsedArea(True)
Start_Row = oCellCursor.getRangeAddress.StartRow
End_Row = oCellCursor.getRangeAddress.EndRow
currcell = osheet.getcellbyposition(2, End_Row+1)
currcell2 = osheet.getcellbyposition(2, Start_Row+1)
currcell3 = osheet.getcellbyposition(2, End_Row)
oConv = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv.Address = currcell.getCellAddress
oConv2 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv2.Address = currcell2.getCellAddress
oConv3 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv3.Address = currcell3.getCellAddress
Podstanovka = "=SUM(" & oConv2.UserInterfaceRepresentation & ":" & oConv3.UserInterfaceRepresentation & ")"
currcell.setFormula (Podstanovka)
currcell = osheet.getcellbyposition(4, End_Row+1)
currcell2 = osheet.getcellbyposition(4, Start_Row+1)
currcell3 = osheet.getcellbyposition(4, End_Row)
oConv = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv.Address = currcell.getCellAddress
oConv2 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv2.Address = currcell2.getCellAddress
oConv3 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv3.Address = currcell3.getCellAddress
Podstanovka = "=SUM(" & oConv2.UserInterfaceRepresentation & ":" & oConv3.UserInterfaceRepresentation & ")"
currcell.setFormula (Podstanovka)
'Расчет средней себеcтоимости
currcell = osheet.getcellbyposition(3, End_Row+1)
currcell2 = osheet.getcellbyposition(4, End_Row+1)
currcell3 = osheet.getcellbyposition(2, End_Row+1)
oConv = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv.Address = currcell.getCellAddress
oConv2 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv2.Address = currcell2.getCellAddress
oConv3 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv3.Address = currcell3.getCellAddress
Podstanovka = "=(" & oConv2.UserInterfaceRepresentation & "/" & oConv3.UserInterfaceRepresentation & ")"
currcell.setFormula (Podstanovka)
currcell = osheet.getcellbyposition(6, End_Row+1)
currcell2 = osheet.getcellbyposition(6, Start_Row+1)
currcell3 = osheet.getcellbyposition(6, End_Row)
oConv = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv.Address = currcell.getCellAddress
oConv2 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv2.Address = currcell2.getCellAddress
oConv3 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv3.Address = currcell3.getCellAddress
Podstanovka = "=SUM(" & oConv2.UserInterfaceRepresentation & ":" & oConv3.UserInterfaceRepresentation & ")"
currcell.setFormula (Podstanovka)
currcell = osheet.getcellbyposition(7, End_Row+1)
currcell2 = osheet.getcellbyposition(6, End_Row+1)
currcell3 = osheet.getcellbyposition(2, End_Row+1)
oConv = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv.Address = currcell.getCellAddress
oConv2 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv2.Address = currcell2.getCellAddress
oConv3 = thiscomponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv3.Address = currcell3.getCellAddress
Podstanovka = "=(" & oConv2.UserInterfaceRepresentation & "/" & oConv3.UserInterfaceRepresentation & ")"
currcell.setFormula (Podstanovka)
'Данные строчки делают шрифт жирным в первом и последнем столбцах таблицы
' Это стандартная функция, когда мы хотим выделить итоговую строку
ThisComponent.Sheets(0).getRows().getByIndex( 0 ).CharWeight = com.sun.star.awt.FontWeight.BOLD
ThisComponent.Sheets(0).getRows().getByIndex(End_Row+1).CharWeight = com.sun.star.awt.FontWeight.BOLD
End Sub
sub Oformlenie
'Данный макрос убирает формулы из таблицы, а также добавляет границ ячеек, чтобы
'таблица смотрелась удобно. В основном коде мы уже встали на 1-ю левую верхнюю ячейку
'макрос выделит диапазон до конца и уберет не нужные флаги
dim document as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Sel"
args1(0).Value = true
dispatcher.executeDispatch(document, ".uno:GoToEndOfData", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dim args3(5) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Flags"
args3(0).Value = "SV"
args3(1).Name = "FormulaCommand"
args3(1).Value = 0
args3(2).Name = "SkipEmptyCells"
args3(2).Value = false
args3(3).Name = "Transpose"
args3(3).Value = false
args3(4).Name = "AsLink"
args3(4).Value = false
args3(5).Name = "MoveMode"
args3(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args3())
'Данная часть кода отвечает за заливку границ ячеек (стандартный код)
dim args4(12) as new com.sun.star.beans.PropertyValue
args4(0).Name = "OuterBorder.LeftBorder"
args4(0).Value = Array(0,0,2,0)
args4(1).Name = "OuterBorder.LeftDistance"
args4(1).Value = 0
args4(2).Name = "OuterBorder.RightBorder"
args4(2).Value = Array(0,0,2,0)
args4(3).Name = "OuterBorder.RightDistance"
args4(3).Value = 0
args4(4).Name = "OuterBorder.TopBorder"
args4(4).Value = Array(0,0,2,0)
args4(5).Name = "OuterBorder.TopDistance"
args4(5).Value = 0
args4(6).Name = "OuterBorder.BottomBorder"
args4(6).Value = Array(0,0,2,0)
args4(7).Name = "OuterBorder.BottomDistance"
args4(7).Value = 0
args4(8).Name = "InnerBorder.Horizontal"
args4(8).Value = Array(0,0,2,0)
args4(9).Name = "InnerBorder.Vertical"
args4(9).Value = Array(0,0,2,0)
args4(10).Name = "InnerBorder.Flags"
args4(10).Value = 0
args4(11).Name = "InnerBorder.ValidFlags"
args4(11).Value = 127
args4(12).Name = "InnerBorder.DefaultDistance"
args4(12).Value = 0
dispatcher.executeDispatch(document, ".uno:SetBorderStyle", "", 0, args4())
end sub
Sub Cveta3
'Макрос Cveta3 c помошью цикла for to next проверяет числовые значения в столбце 4
'Если себестоимость комплекса выше 38.14 - ячейка подсвечивается ярко красным цветом
oCell = ThisComponent.Sheets (0).getCellByPosition(0, 0)
ThisComponent.CurrentController.Select(oCell)
oSheet = ThisComponent.CurrentController.getActiveSheet()
myrows=oSheet.getrows
oCellCursor = oSheet.createCursor()
oCellCursor.GotoStartOfUsedArea(True)
oCellCursor.GotoEndOfUsedArea(True)
'Поскольку в главном макросе мы уже сделали отступ в две строки отсчет первой ячейки
' ведем с StarRow+3, т.е. наш диапазон без заголовка
Start_Row = oCellCursor.getRangeAddress.StartRow+3
End_Row = oCellCursor.getRangeAddress.EndRow + 1
For i=End_Row To Start_Row step -1
'Указываем столбец в котором ищем значение
text1 = osheet.getcellbyposition(3,i).Value
'Указывает что text1 должен быть больше 38.14
If text1 > 38.14 Then
'Применяем к таким найденным ячейкам светло-красный цвет.
'За это отвечает код 16764057 функции CellBackColor (стандартно)
oSheet.getCellByPosition (3,i).CellBackColor = 16764057
End if
Next i
End Sub
После записи макроса закрываем окно разработки. В файле вновь жмем ALT+F11, находим модуль Stolov. В окне существующие макросы выделяем Stolov и жмем выполнить.
Как создавать меню, установочные файлы и прикреплять меню к макросам рассмотрим позже. Сейчас все создаваемые макросы вызываем напрямую через это окно.
также рекомендуется хранить коды создаваемых макросов в отдельных текстовых файлах, чтобы в случае сбоя ООО быстро восстановить потери.
а) проследите многократным нажатием клавиш отмены CTRL+Z все этапы работы макроса.
б) Закройте и откройте файл без сохранения - внесите намеренную ошибку - себестоимость комплекса свыше указанного числа, проверьте как обработчик справился с ошибкой.
Функция Exit Sub часто применяется для отладки макроса поэтапно - она вставляется в любую строчку кода главного макроса и прекращает выполнение последующего кода.
Поэкспериментируйте с функцией Exit Sub в главном созданном макросе, напишите эту строчку ее после следующих строк и выполните макрос:
