Архив рубрики: Library

Печать твердых копий из OOBase

ООфис  имеет встроенные средства для создания отчетов и они наверное подойдут многим. Но откровенно говоря я склоняюсь к печати документов через программно генерируемые таблицы ООКалк.

Кто еще хочет попробовать вот много кода из которого можно и для себя взять какие-то моменты. Главное собственно вызвать ООКалк с пустым документом, а по прочим вопросам документации достаточно.

Sub PrintNZP()

MsgBox "Закройте текущее сообщение и дождитесь сообщение <<Обработка документа окончена>>"

Dim oNDoc, oNSheets, oNSheet

oNDoc = StarDesktop.loadComponentFromURL( _
"private:factory/scalc", "_blank", 0, Array() )
oNSheets = oNDoc.getSheets()
oNSheet = oNSheets.getByIndex(0)


Dim sCurrentKod As String
Dim Connection As Variant
Dim Statement As Variant
Dim ResultSet As Variant
Dim UpdateStatement As Variant
Dim sKod As String
Dim dPlatmin As Double
Dim dCts As Double
Dim dPlatkop As Double
Dim iCountErr As Integer

If FormDoc_det.isModified Then
FormDoc_det().UpdateRow()
End If

GlobalScope.BasicLibraries.loadLibrary("aovc")
sCurrentKod = FormDoc_h().Columns.getByName("Kod").String
Connection = aovc.GetConnection()
Statement = Connection.CreateStatement()
ResultSet = Statement.ExecuteQuery("SELECT doc_det.*, doc_det.kol * doc_det.nv AS min, doc_det.kol * doc_det.rc / 100 AS grn, (SELECT DISTINCT name FROM cennic AS c WHERE c.kod = doc_det.koddet) AS namedet, (SELECT DISTINCT i.name FROM izdel AS i WHERE i.kod = doc_det.kodiz) AS nameiz FROM dbo.doc_det AS doc_det where doc_det.kod=" & sCurrentKod & " order by kod")

Dim iCounter as Integer
Dim I As Integer
oNSheet.GetCellByPosition(7,0).string = "А К Т"
oNSheet.GetCellByPosition(3,1).string = "инвентаризации незавершенного производства по цеху 16 на 1-е"
oNSheet.GetCellByPosition(7,0).CharHeight = 14
oNSheet.GetCellByPosition(3,1).CharHeight = 14
oNSheet.GetCellByPosition(3,1).string = FormDoc_h.Columns.GetByName("prim").String
oNSheet.GetCellByPosition(0,2).string = "№ пп"
oNSheet.GetCellRangeByPosition(0,2,0,3).Merge(True)
oNSheet.GetCellByPosition(0,2).Columns.GetByIndex(0).Width = "700"
oNSheet.GetCellByPosition(0,2).isTextWrapped = True
oNSheet.GetCellByPosition(0,2).HoriJustify = 2
oNSheet.GetCellByPosition(0,2).VertJustify = 2

oNSheet.GetCellByPosition(1,2).string = "Наименование изделий, деталей и узлов"
oNSheet.GetCellByPosition(1,2).Columns.GetByIndex(0).Width = "4200"
oNSheet.GetCellRangeByPosition(1,2,1,3).Merge(True)
oNSheet.GetCellByPosition(1,2).isTextWrapped = True
oNSheet.GetCellByPosition(1,2).HoriJustify = 2
oNSheet.GetCellByPosition(1,2).VertJustify = 2

oNSheet.GetCellByPosition(2, 2).string = "№№ деталей или узлов"
oNSheet.GetCellByPosition(2,2).isTextWrapped = True
oNSheet.GetCellRangeByPosition(2,2,2,3).Merge(True)
oNSheet.GetCellByPosition(2,2).HoriJustify = 2
oNSheet.GetCellByPosition(2,2).VertJustify = 2
oNSheet.GetCellByPosition(2,2).Columns.GetByIndex(0).Width = "1800"

oNSheet.GetCellByPosition(3, 2).string = "Ед. изм."
oNSheet.GetCellByPosition(3,2).isTextWrapped = True
oNSheet.GetCellRangeByPosition(3,2,3,3).Merge(True)
oNSheet.GetCellByPosition(3,2).HoriJustify = 2
oNSheet.GetCellByPosition(3,2).VertJustify = 2
oNSheet.GetCellByPosition(3,2).Columns.GetByIndex(0).Width = "1000"

oNSheet.GetCellByPosition(4, 2).string = "№ послед-ней закончен-ной операции"
'oNSheet.GetCellByPosition(4,2).isTextWrapped = True
oNSheet.GetCellRangeByPosition(4,2,4,3).isTextWrapped = True
oNSheet.GetCellRangeByPosition(4,2,4,3).Merge(True)
oNSheet.GetCellByPosition(4,2).HoriJustify = 2
oNSheet.GetCellByPosition(4,2).VertJustify = 2
oNSheet.GetCellByPosition(4,2).Columns.GetByIndex(0).Width = "2000"

oNSheet.GetCellByPosition(5, 2).string = "Книж-ный оста-ток"
oNSheet.GetCellByPosition(5,2).isTextWrapped = True
oNSheet.GetCellRangeByPosition(5,2,5,3).Merge(True)
oNSheet.GetCellByPosition(5,2).HoriJustify = 2
oNSheet.GetCellByPosition(5,2).VertJustify = 2
oNSheet.GetCellByPosition(5,2).Columns.GetByIndex(0).Width = "1800"


oNSheet.GetCellByPosition(6, 2).string = "Факти-ческое нали-чие"
oNSheet.GetCellByPosition(6,2).isTextWrapped = True
oNSheet.GetCellRangeByPosition(6,2,6,3).Merge(True)
oNSheet.GetCellByPosition(6,2).HoriJustify = 2
oNSheet.GetCellByPosition(6,2).VertJustify = 2
oNSheet.GetCellByPosition(6,2).Columns.GetByIndex(0).Width = "1400"

oNSheet.GetCellByPosition(7, 2).string = "Результат"
oNSheet.GetCellByPosition(7,2).isTextWrapped = True
oNSheet.GetCellRangeByPosition(7,2,8,2).Merge(True)
oNSheet.GetCellByPosition(7,2).HoriJustify = 2
oNSheet.GetCellByPosition(7,2).VertJustify = 2
'oNSheet.GetCellByPosition(7,2).Columns.GetByIndex(0).Width = "1800"


oNSheet.GetCellByPosition(7, 3).string = "Из-лиш-ки"
oNSheet.GetCellByPosition(7,3).isTextWrapped = True
oNSheet.GetCellByPosition(7,3).HoriJustify = 2
oNSheet.GetCellByPosition(7,3).VertJustify = 2
oNSheet.GetCellByPosition(7,3).Columns.GetByIndex(0).Width = "1100"

oNSheet.GetCellByPosition(8, 3).string = "Недо-стача"
oNSheet.GetCellByPosition(8,3).isTextWrapped = True
oNSheet.GetCellByPosition(8,3).HoriJustify = 2
oNSheet.GetCellByPosition(8,3).VertJustify = 2
oNSheet.GetCellByPosition(8,3).Columns.GetByIndex(0).Width = "1100"


oNSheet.GetCellByPosition(9, 2).string = "Стоимость материала"
oNSheet.GetCellByPosition(9,2).isTextWrapped = True
oNSheet.GetCellRangeByPosition(9,2,10,2).Merge(True)
oNSheet.GetCellByPosition(9,2).HoriJustify = 2
oNSheet.GetCellByPosition(9,2).VertJustify = 2
'oNSheet.GetCellByPosition(9,2).Columns.GetByIndex(0).Width = "1800"


oNSheet.GetCellByPosition(9, 3).string = "На единицу"
oNSheet.GetCellByPosition(9,3).isTextWrapped = True
oNSheet.GetCellByPosition(9,3).HoriJustify = 2
oNSheet.GetCellByPosition(9,3).VertJustify = 2
oNSheet.GetCellByPosition(9,3).Columns.GetByIndex(0).Width = "2400"

oNSheet.GetCellByPosition(10, 3).string = "На все количе-ство"
oNSheet.GetCellByPosition(10,3).isTextWrapped = True
oNSheet.GetCellByPosition(10,3).HoriJustify = 2
oNSheet.GetCellByPosition(10,3).VertJustify = 2
oNSheet.GetCellByPosition(10,3).Columns.GetByIndex(0).Width = "1900"


oNSheet.GetCellByPosition(11, 2).string = "Стоимость зарплаты"
oNSheet.GetCellByPosition(11,2).isTextWrapped = True
oNSheet.GetCellRangeByPosition(11,2,12,2).Merge(True)
oNSheet.GetCellByPosition(11,2).HoriJustify = 2
oNSheet.GetCellByPosition(11,2).VertJustify = 2

oNSheet.GetCellByPosition(11, 3).string = "На единицу"
oNSheet.GetCellByPosition(11,3).isTextWrapped = True
oNSheet.GetCellByPosition(11,3).HoriJustify = 2
oNSheet.GetCellByPosition(11,3).VertJustify = 2
oNSheet.GetCellByPosition(11,3).Columns.GetByIndex(0).Width = "2400"

oNSheet.GetCellByPosition(12, 3).string = "На все количе-ство"
oNSheet.GetCellByPosition(12,3).isTextWrapped = True
oNSheet.GetCellByPosition(12,3).HoriJustify = 2
oNSheet.GetCellByPosition(12,3).VertJustify = 2
oNSheet.GetCellByPosition(12,3).Columns.GetByIndex(0).Width = "1600"

oNSheet.GetCellByPosition(13, 2).string = "Цехо-вые рас-ходы _____%"
oNSheet.GetCellByPosition(13,2).isTextWrapped = True
oNSheet.GetCellRangeByPosition(13,2,13,3).Merge(True)
oNSheet.GetCellByPosition(13,2).HoriJustify = 2
oNSheet.GetCellByPosition(13,2).VertJustify = 2
oNSheet.GetCellByPosition(13,2).Columns.GetByIndex(0).Width = "1600"

oNSheet.GetCellByPosition(14, 2).string = "Обще-завод-ские рас-ходы _____%"
oNSheet.GetCellByPosition(14,2).isTextWrapped = True
oNSheet.GetCellRangeByPosition(14,2,14,3).Merge(True)
oNSheet.GetCellByPosition(14,2).HoriJustify = 2
oNSheet.GetCellByPosition(14,2).VertJustify = 2
oNSheet.GetCellByPosition(14,2).Columns.GetByIndex(0).Width = "1600"

oNSheet.GetCellByPosition(15, 2).string = "Всего завод-ская себе-стои-мость"
oNSheet.GetCellByPosition(15,2).isTextWrapped = True
oNSheet.GetCellRangeByPosition(15,2,15,3).Merge(True)
oNSheet.GetCellByPosition(15,2).HoriJustify = 2
oNSheet.GetCellByPosition(15,2).VertJustify = 2
oNSheet.GetCellByPosition(15,2).Columns.GetByIndex(0).Width = "1800"



Dim BasicBorder as New com.sun.star.table.BorderLine
Dim BasicBorder0 as New com.sun.star.table.BorderLine
Dim oBorder As Object
oBorder = oNSheet.getCellRangeByPosition(0,2,15,4).TableBorder
rem Werte für einen einfachen Rand
BasicBorder.Color = RGB(0, 0, 0)
BasicBorder.InnerLineWidth = 20
BasicBorder.OuterLineWidth = 20
BasicBorder.LineDistance =0

BasicBorder0.Color = RGB(0, 0, 0)
BasicBorder0.InnerLineWidth = 100
BasicBorder0.OuterLineWidth = 0
BasicBorder0.LineDistance = 0

oBorder.LeftLine = BasicBorder
oBorder.TopLine = BasicBorder
oBorder.RightLine = BasicBorder
oBorder.BottomLine = BasicBorder
oBorder.HorizontalLine = BasicBorder
oBorder.VerticalLine = BasicBorder
oNSheet.getCellRangeByPosition(0,2,15,4).TableBorder = oBorder

Dim currentKodDet As Long
Dim currentKodIz As Long
Dim Npp As Long
Dim Total As Long
Dim Count As Long

currentKodDet = -9999
currentKodIz = -9999
Npp=0
Total = 0
Count = 0

iCounter = 3

While ResultSet.Next

If ResultSet.Columns.getByName("kol").Long = 0 Then
GOTO lNext
End If

oBorder = oNSheet.getCellRangeByPosition(0,iCounter,15,iCounter).TableBorder
oBorder.LeftLine = BasicBorder
oBorder.TopLine = BasicBorder
oBorder.RightLine = BasicBorder
oBorder.BottomLine = BasicBorder
oBorder.HorizontalLine = BasicBorder
oBorder.VerticalLine = BasicBorder
oNSheet.getCellRangeByPosition(0,iCounter,15,iCounter).TableBorder = oBorder

If currentKodDet <> ResultSet.Columns.getByName("koddet").Long OR currentKodIz <> ResultSet.Columns.getByName("kodiz").Long Then

If Count > 1 Then
oNSheet.GetCellByPosition(5, iCounter).value = Total
oNSheet.GetCellByPosition(6, iCounter).value = Total
oBorder = oNSheet.getCellRangeByPosition(4, iCounter,6,iCounter).TableBorder
oBorder.TopLine = BasicBorder0
oNSheet.getCellRangeByPosition(4, iCounter,6,iCounter).TableBorder = oBorder
iCounter = iCounter + 1
End If

oBorder = oNSheet.getCellRangeByPosition(0,iCounter,15,iCounter).TableBorder
oBorder.LeftLine = BasicBorder
oBorder.TopLine = BasicBorder
oBorder.RightLine = BasicBorder
oBorder.BottomLine = BasicBorder
oBorder.HorizontalLine = BasicBorder
oBorder.VerticalLine = BasicBorder
oNSheet.getCellRangeByPosition(0,iCounter,15,iCounter).TableBorder = oBorder

currentKodDet = ResultSet.Columns.getByName("koddet").Long
Npp = Npp + 1
Total = 0
Count = 0
iCounter = iCounter + 1
End If

If currentKodIz <> ResultSet.Columns.getByName("kodiz").Long Then
oBorder = oNSheet.getCellRangeByPosition(0,iCounter,15,iCounter).TableBorder
oBorder.LeftLine = BasicBorder
oBorder.TopLine = BasicBorder0
oBorder.RightLine = BasicBorder
oBorder.BottomLine = BasicBorder
oBorder.HorizontalLine = BasicBorder
oBorder.VerticalLine = BasicBorder
oNSheet.getCellRangeByPosition(0,iCounter,15,iCounter).TableBorder = oBorder
currentKodIz = ResultSet.Columns.getByName("kodiz").Long
Npp = 1
oNSheet.GetCellByPosition(1, iCounter).string = Trim(ResultSet.Columns.getByName("nameiz").String)
oNSheet.GetCellByPosition(1, iCounter).CharHeight = 20
iCounter = iCounter + 1
EndIf

oBorder = oNSheet.getCellRangeByPosition(0,iCounter,15,iCounter).TableBorder
oBorder.LeftLine = BasicBorder
oBorder.TopLine = BasicBorder
oBorder.RightLine = BasicBorder
oBorder.BottomLine = BasicBorder
oBorder.HorizontalLine = BasicBorder
oBorder.VerticalLine = BasicBorder
oNSheet.getCellRangeByPosition(0,iCounter,15,iCounter).TableBorder = oBorder


Count = Count + 1

If Count = 1 Then
oNSheet.GetCellByPosition(1, iCounter).string = Trim(ResultSet.Columns.getByName("namedet").String) & Rem97(ResultSet.Columns.getByName("naz").Long)
oNSheet.GetCellByPosition(0, iCounter).value = Npp
End If

oNSheet.GetCellByPosition(4, iCounter).String = sNop(ResultSet.Columns.getByName("nop").Long)
oNSheet.GetCellByPosition(5, iCounter).value = ResultSet.Columns.getByName("kol").Long
oNSheet.GetCellByPosition(6, iCounter).value = ResultSet.Columns.getByName("kol").Long

If ResultSet.Columns.getByName("nv").Double > 0 Then
oNSheet.GetCellByPosition(2, iCounter).value = ResultSet.Columns.getByName("nv").Double
oNSheet.GetCellByPosition(11, iCounter).value = ResultSet.Columns.getByName("rc").Double
Else
oNSheet.GetCellByPosition(11, iCounter).string = " - "
End If

Total = Total + ResultSet.Columns.getByName("kol").Long

iCounter = iCounter + 1
lNext: REM
Wend
If Count > 1 Then
oBorder = oNSheet.getCellRangeByPosition(0,iCounter,15,iCounter).TableBorder
oBorder.LeftLine = BasicBorder
oBorder.TopLine = BasicBorder
oBorder.RightLine = BasicBorder
oBorder.BottomLine = BasicBorder
oBorder.HorizontalLine = BasicBorder
oBorder.VerticalLine = BasicBorder
oNSheet.getCellRangeByPosition(0,iCounter,15,iCounter).TableBorder = oBorder
oNSheet.GetCellByPosition(5, iCounter).value = Total
oNSheet.GetCellByPosition(6, iCounter).value = Total
oBorder = oNSheet.getCellRangeByPosition(4, iCounter,6,iCounter).TableBorder
oBorder.TopLine = BasicBorder0
oNSheet.getCellRangeByPosition(4, iCounter,6,iCounter).TableBorder = oBorder
iCounter = iCounter + 1
End If
oBorder = oNSheet.getCellRangeByPosition(0,iCounter,15,iCounter).TableBorder
oBorder.LeftLine = BasicBorder
oBorder.TopLine = BasicBorder
oBorder.RightLine = BasicBorder
oBorder.BottomLine = BasicBorder0
oBorder.HorizontalLine = BasicBorder
oBorder.VerticalLine = BasicBorder
oNSheet.getCellRangeByPosition(0,iCounter,15,iCounter).TableBorder = oBorder


oNSheet.GetCellByPosition(1, iCounter + 1).string = "==========================================="
MsgBox "Обработка документа окончена"

End Sub


OOBase Basic

Спасибо за включение ссылки на мои заметки в Вики русскоязычного форума ООо http://wiki.forumooo.ru/wiki/%D0%9F%D0%BE%D0%BB%D0%B5%D0%B7%D0%BD%D1%8B%D0%B5_%D1%81%D1%81%D1%8B%D0%BB%D0%BA%D0%B8 (Сразу замечу, что не имел к этому никакого отношения - просто пытался старательно выполнять свою работу). Понял, что не хватает собственно файла с моими наработками. Вот он с Виртуального Диска Гуугла:
 
https://drive.google.com/file/d/0B1CrvL0JhNFeeFBJWWJ4TGZra1U/view?usp=sharing