Ich wollte nun mal probieren, wie man das mittels einem DOM-Objekt mit Libreoffice-Basic umsetzen kann.
Beim Erzeugen der XML-Files habe ich das Problem, dass zwischen den einzelnen Tags keine Zeilenumbrüche eingefügt werden. Auch die Einrückung passt nicht. Allerdings, wenn man ein XML-Dokument als DOM-Objekt einliest und direkt wieder in eine Datei schreibt, sind Zeilenumbrüche und Einrückungen vorhanden.
Weiß jemand wie man das steuern kann? Geht aber ausschließlich um DOM-Objekte. Dass man das mit Outputstream realisieren kann, ist mir bekannt.
Aber das Importieren der XML-Files klappt schon mal ganz gut.
Hier etwas Code, mit dem man E-Rechnungen in UBL und CII einlesen kann.
Die Daten werden in Variablen und Arrays gespeichert.
Auch ist Beispiel-Code dabei, wie die Daten weiterverarbeitet werden können.
In dem Fall wird ein Calc-Dokument mit den Daten gefüllt.
Ich hoffe, ich habe alle relvanten Daten erwischt.
Falls es jemand gebrauchen kann, hier der Code:
Code: Alles auswählen
REM ***** BASIC *****
Option Explicit
Global Const BILLING_REFERENCE_ID=0, BILLING_REFERENCE_ISSUE_DATE=1
Global Const CHARGE_INDICATOR=0, ALLOWANCE_CHARGE_REASON_CODE=1, ALLOWANCE_CHARGE_REASON=2
Global Const MULTIPLIER_FACTOR_NUMERIC=5, AMOUNT=6, BASE_AMOUNT=7
Global Const TAXABLE_AMOUNT=0, TAX_AMOUNT=1, TAX_EXEMPTION_REASON=2, TAX_CATEGORY_ID=3, TAX_PERCENT=4
Global Const INVOICE_LINE_LEVEL=0, INVOICE_LINE_ID=1, PARENT_INVOICE_LINE_ID=2
Global Const INVOICE_LINE_NOTE=5, INVOICED_QUANTITY=6, QUANTITY_UNIT_CODE=7
Global Const INVOICE_LINE_ITEM_NAME=8, INVOICE_LINE_ITEM_DESCRIPTION=9
Global Const INVOICE_LINE_ITEM_BUYERS_IDENTIFICATION_ID=10, INVOICE_LINE_ITEM_SELLER_IDENTIFICATION_ID=11
Global Const INVOICE_LINE_EXTENSION_AMOUNT=12, INVOICE_LINE_PRICE_AMOUNT=13
Type structInvoice
sInvoiceID As String
sIssueDate As String
sDueDate As String
sInvoiceTypCode As String
sDocumentCurrencyCode As String
sBuyerReference As String
sSellerEMail As String
sSellerName As String
sSellerDescription As String
sSellerStreetName As String
sSellerCityName As String
sSellerPLZ As String
sSellerCountryCode As String
sSellerTaxCompanyID As String
sSellerContactName As String
sSellerContactTelephone As String
sSellerContactElectronicMail As String
sActualDeliveryDate As String
sPaymentMeansCode As String
sPayeeFinancialAccountID As String
sPayeeFinancialAccountName As String
sPayeeFinancialInstitutionBranchID As String
sPaymentTerms As String
sTaxTotalAmount As String
sLineExtensionAmount As String
sTaxExclusiveAmount As String
sTaxInclusiveAmount As String
sAllowanceTotalAmount As String
sChargeTotalAmount As String
sPrepaidAmount As String
sPayableRoundingAmount As String
sPayableAmount As String
End Type
Dim Invoice As New structInvoice
Dim arrInvoiceNote As Variant, arrBillingReference As Variant, arrAllowanceCharge As Variant, arrTaxSubtotal As Variant, arrInvoiceLines As Variant
Sub readInvoice
Dim oDocBuilder, oDOM, sURL As String, a%, bInvoiceFound As Boolean
Dim oFilePicker As Object, oSettings As Object, oFileAccess As Object
oSettings = CreateUnoService("com.sun.star.util.PathSettings")
sURL = oSettings.Work
oFilePicker = CreateUnoService("com.sun.star.ui.dialogs.FilePicker")
oFilePicker.AppendFilter("XML-Dateien (*.xml)", "*.xml")
oFilePicker.SetCurrentFilter("XML-Dateien (*.xml)")
oFilePicker.MultiSelectionMode=False
oFileAccess = CreateUnoService("com.sun.star.ucb.SimpleFileAccess")
If oFileAccess.exists(sURL) Then
oFilePicker.setDisplayDirectory(sURL)
End If
If oFilePicker.execute() Then
sURL=oFilePicker.Files(0)
End If
oDocBuilder = CreateUnoService("com.sun.star.xml.dom.DocumentBuilder")
oDOM = oDocBuilder.parseURI(ConvertFromURL(sURL))
oDOM.normalize()
For a = 0 To oDOM.ChildNodes.getLength - 1
Select Case oDOM.ChildNodes.item(a).NodeName
Case "Invoice"
bInvoiceFound=True
readXRechnung(oDOM)
Case "CrossIndustryInvoice"
bInvoiceFound=True
readZUGFeRD(oDOM)
End Select
Next
If Not bInvoiceFound Then
Print "Keine Rechnungsstruktur erkannt"
End if
End Sub
Sub readXRechnung(oDOM As Object)
Dim oElem As Object, oElemList As Object, oSubElemList As Object
Dim a% , b% , c% , d% , e% , f% , g% , h% , i% , j% , k% , l%, x%
oElemList=oDOM.getElementsByTagName("Invoice").item(0).getChildNodes()
With Invoice
For a = 0 To oElemList.getLength() - 1
Select Case oElemList.item(a).NodeName
Case "ID"
.sInvoiceID=oElemList.item(a).FirstChild.NodeValue '[BT-1]
Case "IssueDate"
.sIssueDate=oElemList.item(a).FirstChild.NodeValue '[BT-2]
Case "DueDate"
.sDueDate=oElemList.item(a).FirstChild.NodeValue '[BT-9]
Case "InvoiceTypeCode"
.sInvoiceTypCode=oElemList.item(a).FirstChild.NodeValue '[BT-3]
Case "Note"
ReDim Preserve arrInvoiceNote(b)
arrInvoiceNote(b)=oElemList.item(a).FirstChild.NodeValue '[BT-22]
b=b+1
Case "DocumentCurrencyCode"
.sDocumentCurrencyCode=oElemList.item(a).FirstChild.NodeValue '[BT-5]
Case "BuyerReference"
.sBuyerReference=oElemList.item(a).FirstChild.NodeValue '[BT-10]
Case "BillingReference" '[BG-3]
ReDim Preserve arrBillingReference(c,2)
arrBillingReference(c,BILLING_REFERENCE_ID)=getValue(oElemList.item(a),Array("ID")) '[BT-25]
arrBillingReference(c,BILLING_REFERENCE_ISSUE_DATE)=getValue(oElemList.item(a),Array("IssueDate")) '[BT-26]
c=c+1
Case "AccountingSupplierParty"
.sSellerEMail=getValue(oElemList.item(a),Array("Party","EndpointID"),"schemeID","EM") '[BT-34]
.sSellerName=getValue(oElemList.item(a),Array("Party","PartyName","Name")) '[BT-28]
.sSellerStreetName=getValue(oElemList.item(a),Array("Party","PostalAddress","StreetName")) '[BT-35]
.sSellerCityName=getValue(oElemList.item(a),Array("Party","PostalAddress","CityName")) '[BT-37]
.sSellerPLZ=getValue(oElemList.item(a),Array("Party","PostalAddress","PostalZone")) '[BT-38]
.sSellerCountryCode=getValue(oElemList.item(a),Array("Party","PostalAddress","Country","IdentificationCode")) '[BT-40]
.sSellerTaxCompanyID=getValue(oElemList.item(a),Array("Party","PartyTaxScheme","CompanyID")) '[BT-31] [BT-32]
.sSellerContactName=getValue(oElemList.item(a),Array("Party","Contact","Name")) '[BT-41]
.sSellerContactTelephone=getValue(oElemList.item(a),Array("Party","Contact","Telephone")) '[BT-42]
.sSellerContactElectronicMail=getValue(oElemList.item(a),Array("Party","Contact","ElectronicMail")) '[BT-43]
Case "Delivery"
.sActualDeliveryDate=getValue(oElemList.item(a),Array("ActualDeliveryDate")) '[BT-72]
Case "PaymentMeans"
.sPaymentMeansCode=getValue(oElemList.item(a),Array("PaymentMeansCode")) '[BT-81]
.sPayeeFinancialAccountID=getValue(oElemList.item(a),Array("PayeeFinancialAccount","ID")) '[BT-84]
.sPayeeFinancialAccountName=getValue(oElemList.item(a),Array("PayeeFinancialAccount","Name")) '[BT-85]
.sPayeeFinancialInstitutionBranchID=getValue(oElemList.item(a),Array("PayeeFinancialAccount","FinancialInstitutionBranch","ID")) '[BT-86]
Case "PaymentTerms"
.sPaymentTerms=getValue(oElemList.item(a),Array("Note")) '[BT-20]
Case "AllowanceCharge"
ReDim Preserve arrAllowanceCharge(d,8)
arrAllowanceCharge(d,CHARGE_INDICATOR)=getValue(oElemList.item(a),Array("ChargeIndicator")) '[Use “true” when informing about Charges and “false” when informing about Allowances.]
arrAllowanceCharge(d,ALLOWANCE_CHARGE_REASON_CODE)=getValue(oElemList.item(a),Array("AllowanceChargeReasonCode")) '[BT-98] [BT-105]
arrAllowanceCharge(d,ALLOWANCE_CHARGE_REASON)=getValue(oElemList.item(a),Array("AllowanceChargeReason")) '[BT-97] [BT-104]
arrAllowanceCharge(d,MULTIPLIER_FACTOR_NUMERIC)=getValue(oElemList.item(a),Array("MultiplierFactorNumeric")) '[BT-94] [BT-101]
arrAllowanceCharge(d,AMOUNT)=getValue(oElemList.item(a),Array("Amount")) '[BT-92] [BT-99]
arrAllowanceCharge(d,BASE_AMOUNT)=getValue(oElemList.item(a),Array("BaseAmount")) '[BT-93] [BT-100]
arrAllowanceCharge(d,TAX_CATEGORY_ID)=getValue(oElemList.item(a),Array("TaxCategory","ID")) '[BT-95] [BT-102]
arrAllowanceCharge(d,TAX_PERCENT)=getValue(oElemList.item(a),Array("TaxCategory","Percent")) '[BT-96] [BT-103]
d=d+1
Case "TaxTotal"
.sTaxTotalAmount=getValue(oElemList.item(a),Array("TaxAmount")) '[BT-110] [BT-111]
oSubElemList=oElemList.item(a).getElementsByTagName("TaxSubtotal")
For e = 0 To oSubElemList.getLength() - 1
ReDim Preserve arrTaxSubtotal(e,5)
arrTaxSubtotal(e,TAXABLE_AMOUNT)=getValue(oSubElemList.item(e),Array("TaxableAmount")) '[BT-116]
arrTaxSubtotal(e,TAX_AMOUNT)=getValue(oSubElemList.item(e),Array("TaxAmount")) '[BT-117]
arrTaxSubtotal(e,TAX_CATEGORY_ID)=getValue(oSubElemList.item(e),Array("TaxCategory","ID")) '[BT-118]
arrTaxSubtotal(e,TAX_PERCENT)=getValue(oSubElemList.item(e),Array("TaxCategory","Percent")) '[BT-119]
arrTaxSubtotal(e,TAX_EXEMPTION_REASON)=getValue(oSubElemList.item(e),Array("TaxCategory","TaxExemptionReason")) '[BT-120]
Next
Case "LegalMonetaryTotal"
.sLineExtensionAmount=getValue(oElemList.item(a),Array("LineExtensionAmount")) '[BT-106]
.sTaxExclusiveAmount=getValue(oElemList.item(a),Array("TaxExclusiveAmount")) '[BT-109]
.sTaxInclusiveAmount=getValue(oElemList.item(a),Array("TaxInclusiveAmount")) '[BT-112]
.sAllowanceTotalAmount=getValue(oElemList.item(a),Array("AllowanceTotalAmount")) '[BT-107]
.sChargeTotalAmount=getValue(oElemList.item(a),Array("ChargeTotalAmount")) '[BT-108]
.sPrepaidAmount=getValue(oElemList.item(a),Array("PrepaidAmount")) '[BT-113]
.sPayableRoundingAmount=getValue(oElemList.item(a),Array("PayableRoundingAmount")) '[BT-114]
.sPayableAmount=getValue(oElemList.item(a),Array("PayableAmount")) '[BT-115]
Case "InvoiceLine"
arrInvoiceLines=getInvoiceLines(oElemList.item(a),arrInvoiceLines)
End Select
Next
End With
CreateSheet
' PrintValues
End Sub
Sub readZUGFeRD(oDOM As Object)
Dim oElem As Object, oElemList As Object, oSubElemList As Object, oSubSubElemList As Object
Dim a% , b% , c% , d% , e% , f% , g% , h% , i% , j% , k% , l%, x%
oElemList=oDOM.getElementsByTagName("CrossIndustryInvoice").item(0).getChildNodes()
With Invoice
For a = 0 To oElemList.getLength() - 1
Select Case oElemList.item(a).NodeName
Case "ExchangedDocument"
.sInvoiceID=getValue(oElemList.item(a),Array("ID")) '[BT-1]
.sIssueDate=getValue(oElemList.item(a),Array("IssueDateTime","DateTimeString")) '[BT-2]
.sInvoiceTypCode=getValue(oElemList.item(a),Array("TypeCode")) '[BT-3]
oSubElemList=oElemList.item(a).getElementsByTagName("IncludedNote")
For i = 0 To oSubElemList.getLength() - 1
ReDim Preserve arrInvoiceNote(i)
arrInvoiceNote(i)=getValue(oSubElemList.item(i),Array("Content")) '[BT-22]
Next
Case "SupplyChainTradeTransaction"
oSubElemList=oElemList.item(a).getChildNodes()
For b = 0 To oSubElemList.getLength() - 1
Select Case oSubElemList.item(b).NodeName
Case "IncludedSupplyChainTradeLineItem"
ReDim Preserve arrInvoiceLines(b,13)
arrInvoiceLines(b,INVOICE_LINE_ID)=getValue(oSubElemList.item(b),Array("AssociatedDocumentLineDocument","LineID")) '[BT-126]
arrInvoiceLines(b,PARENT_INVOICE_LINE_ID)=getValue(oSubElemList.item(b),Array("AssociatedDocumentLineDocument","ParentLineID")) '[BT-X-304]
arrInvoiceLines(b,INVOICE_LINE_NOTE)=getValue(oSubElemList.item(b),Array("AssociatedDocumentLineDocument","IncludedNote")) '[BT-127]
arrInvoiceLines(b,INVOICE_LINE_ITEM_BUYERS_IDENTIFICATION_ID)=getValue(oSubElemList.item(b),Array("SpecifiedTradeProduct","BuyerAssignedID")) '[BT-156]
arrInvoiceLines(b,INVOICE_LINE_ITEM_SELLER_IDENTIFICATION_ID)=getValue(oSubElemList.item(b),Array("SpecifiedTradeProduct","SellerAssignedID")) '[BT-155]
arrInvoiceLines(b,INVOICE_LINE_ITEM_NAME)=getValue(oSubElemList.item(b),Array("SpecifiedTradeProduct","Name")) '[BT-153]
arrInvoiceLines(b,INVOICE_LINE_ITEM_DESCRIPTION)=getValue(oSubElemList.item(b),Array("SpecifiedTradeProduct","Description")) '[BT-154]
arrInvoiceLines(b,INVOICE_LINE_PRICE_AMOUNT)=getValue(oSubElemList.item(b),Array("SpecifiedLineTradeAgreement","NetPriceProductTradePrice","ChargeAmount")) '[BT-146]
arrInvoiceLines(b,INVOICED_QUANTITY)=getValue(oSubElemList.item(b),Array("SpecifiedLineTradeDelivery","BilledQuantity")) '[BT-129]
arrInvoiceLines(b,QUANTITY_UNIT_CODE)=getValue(oSubElemList.item(b),Array("SpecifiedLineTradeDelivery","BilledQuantity"),"unitCode","",True) '[BT-130]
arrInvoiceLines(b,TAX_CATEGORY_ID)=getValue(oSubElemList.item(b),Array("SpecifiedLineTradeSettlement","ApplicableTradeTax","CategoryCode")) '[BT-151]
arrInvoiceLines(b,TAX_PERCENT)=getValue(oSubElemList.item(b),Array("SpecifiedLineTradeSettlement","ApplicableTradeTax","RateApplicablePercent")) '[BT-152]
arrInvoiceLines(b,INVOICE_LINE_EXTENSION_AMOUNT)=getValue(oSubElemList.item(b),Array("SpecifiedLineTradeSettlement","SpecifiedTradeSettlementLineMonetarySummation","LineTotalAmount")) '[BT-131]
Case "ApplicableHeaderTradeAgreement"
.sBuyerReference=getValue(oSubElemList.item(b),Array("BuyerReference")) '[BT-10]
.sSellerName=getValue(oSubElemList.item(b),Array("SellerTradeParty","Name")) '[BT-27]
.sSellerDescription=getValue(oSubElemList.item(b),Array("SellerTradeParty","Description")) '[BT-33]
.sSellerPLZ=getValue(oSubElemList.item(b),Array("SellerTradeParty","PostalTradeAddress","PostcodeCode")) '[BT-38]
.sSellerStreetName=getValue(oSubElemList.item(b),Array("SellerTradeParty","PostalTradeAddress","LineOne")) '[BT-35]
.sSellerCityName=getValue(oSubElemList.item(b),Array("SellerTradeParty","PostalTradeAddress","CityName")) '[BT-37]
.sSellerCountryCode=getValue(oSubElemList.item(b),Array("SellerTradeParty","PostalTradeAddress","CountryID")) '[BT-40]
.sSellerEMail=getValue(oSubElemList.item(b),Array("SellerTradeParty","URIUniversalCommunication","URIID"),"schemeID","EM") '[BT-34]
.sSellerTaxCompanyID=getValue(oSubElemList.item(b),Array("SellerTradeParty","SpecifiedTaxRegistration","ID")) '[BT-31] [BT-32]
.sSellerContactName=getValue(oSubElemList.item(b),Array("SellerTradeParty","DefinedTradeContact","PersonName")) '[BT-41]
.sSellerContactTelephone=getValue(oSubElemList.item(b),Array("SellerTradeParty","DefinedTradeContact","TelephoneUniversalCommunication","CompleteNumber")) '[BT-42]
.sSellerContactElectronicMail=getValue(oSubElemList.item(b),Array("SellerTradeParty","DefinedTradeContact","EmailURIUniversalCommunication","URIID")) '[BT-43]
Case "ApplicableHeaderTradeDelivery"
.sActualDeliveryDate=getValue(oSubElemList.item(b),Array("ActualDeliverySupplyChainEvent","OccurrenceDateTime","DateTimeString")) '[BT-72]
Case "ApplicableHeaderTradeSettlement"
.sDocumentCurrencyCode=getValue(oSubElemList.item(b),Array("InvoiceCurrencyCode")) '[BT-5]
.sPaymentMeansCode=getValue(oSubElemList.item(b),Array("SpecifiedTradeSettlementPaymentMeans","TypeCode")) '[BT-81]
.sPayeeFinancialAccountID=getValue(oSubElemList.item(b),Array("SpecifiedTradeSettlementPaymentMeans","PayeePartyCreditorFinancialAccount","IBANID")) '[BT-84]
.sPayeeFinancialAccountName=getValue(oSubElemList.item(b),Array("SpecifiedTradeSettlementPaymentMeans","PayeePartyCreditorFinancialAccount","AccountName")) '[BT-85]
.sPayeeFinancialInstitutionBranchID=getValue(oSubElemList.item(b),Array("SpecifiedTradeSettlementPaymentMeans","PayeeSpecifiedCreditorFinancialInstitution","BICID")) '[BT-86]
oSubSubElemList=oSubElemList.item(b).getElementsByTagName("ApplicableTradeTax")
For c = 0 To oSubSubElemList.getLength() - 1
ReDim Preserve arrTaxSubtotal(c,5)
arrTaxSubtotal(c,TAX_AMOUNT)=getValue(oSubSubElemList.item(c),Array("CalculatedAmount")) '[BT-117]
arrTaxSubtotal(c,TAX_EXEMPTION_REASON)=getValue(oSubSubElemList.item(c),Array("ExemptionReason")) '[BT-120]
arrTaxSubtotal(c,TAXABLE_AMOUNT)=getValue(oSubSubElemList.item(c),Array("BasisAmount")) '[BT-116]
arrTaxSubtotal(c,TAX_CATEGORY_ID)=getValue(oSubSubElemList.item(c),Array("CategoryCode")) '[BT-118]
arrTaxSubtotal(c,TAX_PERCENT)=getValue(oSubSubElemList.item(c),Array("RateApplicablePercent")) '[BT-119]
Next
oSubSubElemList=oSubElemList.item(b).getElementsByTagName("SpecifiedTradeAllowanceCharge")
For d = 0 To oSubSubElemList.getLength() - 1
ReDim Preserve arrAllowanceCharge(d,8)
arrAllowanceCharge(d,CHARGE_INDICATOR)=getValue(oSubSubElemList.item(d),Array("ChargeIndicator","Indicator")) '[Use “true” when informing about Charges and “false” when informing about Allowances.]
arrAllowanceCharge(d,ALLOWANCE_CHARGE_REASON_CODE)=getValue(oSubSubElemList.item(d),Array("ReasonCode")) '[BT-98] [BT-105]
arrAllowanceCharge(d,ALLOWANCE_CHARGE_REASON)=getValue(oSubSubElemList.item(d),Array("Reason")) '[BT-97] [BT-104]
arrAllowanceCharge(d,MULTIPLIER_FACTOR_NUMERIC)=getValue(oSubSubElemList.item(d),Array("CalculationPercent")) '[BT-94] [BT-101]
arrAllowanceCharge(d,AMOUNT)=getValue(oSubSubElemList.item(d),Array("ActualAmount")) '[BT-92] [BT-99]
arrAllowanceCharge(d,BASE_AMOUNT)=getValue(oSubSubElemList.item(d),Array("BasisAmount")) '[BT-93] [BT-100]
arrAllowanceCharge(d,TAX_CATEGORY_ID)=getValue(oSubSubElemList.item(d),Array("CategoryTradeTax","CategoryCode")) '[BT-95] [BT-102]
arrAllowanceCharge(d,TAX_PERCENT)=getValue(oSubSubElemList.item(d),Array("CategoryTradeTax","RateApplicablePercent")) '[BT-96] [BT-103]
Next
.sPaymentTerms=getValue(oSubElemList.item(b),Array("SpecifiedTradePaymentTerms","Description")) '[BT-20]
.sDueDate=getValue(oSubElemList.item(b),Array("SpecifiedTradePaymentTerms","DueDateDateTime","DateTimeString")) '[BT-9]
.sLineExtensionAmount=getValue(oSubElemList.item(b),Array("SpecifiedTradeSettlementHeaderMonetarySummation","LineTotalAmount")) '[BT-106]
.sChargeTotalAmount=getValue(oSubElemList.item(b),Array("SpecifiedTradeSettlementHeaderMonetarySummation","ChargeTotalAmount")) '[BT-108]
.sAllowanceTotalAmount=getValue(oSubElemList.item(b),Array("SpecifiedTradeSettlementHeaderMonetarySummation","AllowanceTotalAmount")) '[BT-107]
.sTaxExclusiveAmount=getValue(oSubElemList.item(b),Array("SpecifiedTradeSettlementHeaderMonetarySummation","TaxBasisTotalAmount")) '[BT-109]
.sTaxTotalAmount=getValue(oSubElemList.item(b),Array("SpecifiedTradeSettlementHeaderMonetarySummation","TaxTotalAmount")) '[BT-110] [BT-111]
.sTaxInclusiveAmount=getValue(oSubElemList.item(b),Array("SpecifiedTradeSettlementHeaderMonetarySummation","GrandTotalAmount")) '[BT-112]
.sPrepaidAmount=getValue(oSubElemList.item(b),Array("SpecifiedTradeSettlementHeaderMonetarySummation","TotalPrepaidAmount")) '[BT-113]
.sPayableAmount=getValue(oSubElemList.item(b),Array("SpecifiedTradeSettlementHeaderMonetarySummation","DuePayableAmount")) '[BT-115]
.sPayableRoundingAmount=getValue(oSubElemList.item(b),Array("SpecifiedTradeSettlementHeaderMonetarySummation","RoundingAmount")) '[BT-114]
oSubSubElemList=oSubElemList.item(b).getElementsByTagName("InvoiceReferencedDocument")
For e = 0 To oSubSubElemList.getLength() - 1
ReDim Preserve arrBillingReference(e,2)
arrBillingReference(e,BILLING_REFERENCE_ID)=getValue(oSubSubElemList.item(e),Array("IssuerAssignedID")) '[BT-25]
arrBillingReference(e,BILLING_REFERENCE_ISSUE_DATE)=getValue(oSubSubElemList.item(e),Array("FormattedIssueDateTime","DateTimeString")) '[BT-26]
Next
End Select
Next
End Select
Next
End With
CreateSheet
End Sub
Function getInvoiceLines(oEntry As Object, arrInvoiceLines) As Variant
Dim a%, b%, c%, i%, oElement As Object
Dim iNumberSubInvoiceLines As Integer, iLevel As Integer, sParentID As String
Dim arrInvoiceLineIDs() As String
On Error Goto Catch
If Not IsArray(arrInvoiceLines) Then
ReDim arrInvoiceLines(0,13)
Else
ReDim Preserve arrInvoiceLines(UBound(arrInvoiceLines)+1,13)
End If
a=UBound(arrInvoiceLines)
arrInvoiceLines(a,INVOICE_LINE_LEVEL)=0
arrInvoiceLines(a,INVOICE_LINE_ID)=getValue(oEntry,Array("ID")) '[BT-126]
arrInvoiceLines(a,INVOICE_LINE_NOTE)=getValue(oEntry,Array("Note")) '[BT-127]
arrInvoiceLines(a,INVOICED_QUANTITY)=getValue(oEntry,Array("InvoicedQuantity")) '[BT-129]
arrInvoiceLines(a,QUANTITY_UNIT_CODE)=getValue(oEntry,Array("InvoicedQuantity"),"unitCode","",True) '[BT-130]
arrInvoiceLines(a,INVOICE_LINE_EXTENSION_AMOUNT)=getValue(oEntry,Array("LineExtensionAmount")) '[BT-131]
arrInvoiceLines(a,INVOICE_LINE_ITEM_DESCRIPTION)=getValue(oEntry,Array("Item","Description")) '[BT-154]
arrInvoiceLines(a,INVOICE_LINE_ITEM_NAME)=getValue(oEntry,Array("Item","Name")) '[BT-153]
arrInvoiceLines(a,INVOICE_LINE_ITEM_BUYERS_IDENTIFICATION_ID)=getValue(oEntry,Array("Item","BuyersItemIdentification","ID")) '[BT-156]
arrInvoiceLines(a,INVOICE_LINE_ITEM_SELLER_IDENTIFICATION_ID)=getValue(oEntry,Array("Item","SellersItemIdentification","ID")) '[BT-155]
arrInvoiceLines(a,TAX_CATEGORY_ID)=getValue(oEntry,Array("Item","ClassifiedTaxCategory","ID")) '[BT-151]
arrInvoiceLines(a,TAX_PERCENT)=getValue(oEntry,Array("Item","ClassifiedTaxCategory","Percent")) '[BT-152]
arrInvoiceLines(a,INVOICE_LINE_PRICE_AMOUNT)=getValue(oEntry,Array("Price","PriceAmount")) '[BT-146]
sParentID=arrInvoiceLines(a,INVOICE_LINE_ID)
iNumberSubInvoiceLines=oEntry.getElementsByTagName("SubInvoiceLine").getLength
If iNumberSubInvoiceLines > 0 Then
ReDim arrInvoiceLineIDs(iNumberSubInvoiceLines)
ReDim Preserve arrInvoiceLines(UBound(arrInvoiceLines)+iNumberSubInvoiceLines,13)
oElement=oEntry
For i = (a+1) To iNumberSubInvoiceLines + a - 1
Do While True
For b = 0 To oElement.getChildNodes.getLength - 1
If oElement.getChildNodes.item(b).NodeName = "SubInvoiceLine" Then
For c = 0 To oElement.getChildNodes.item(b).getChildNodes.getLength - 1
If oElement.getChildNodes.item(b).getChildNodes.item(c).NodeName = "ID" Then
If Not bFieldInArray(arrInvoiceLineIDs,oElement.getChildNodes.item(b).getChildNodes.item(c).FirstChild.NodeValue) Then
iLevel=iLevel+1
sParentID=getValue(oElement,Array("ID"))
oElement=oElement.getChildNodes.item(b)
Exit Do
End If
End If
Next
End If
Next
iLevel=iLevel-1
oElement=oElement.getParentNode
sParentID=getValue(oElement,Array("ID"))
Loop
arrInvoiceLines(i,INVOICE_LINE_LEVEL)=iLevel
arrInvoiceLines(i,PARENT_INVOICE_LINE_ID)=sParentID
arrInvoiceLines(i,INVOICE_LINE_ID)=getValue(oElement,Array("ID")) '[BT-126]
arrInvoiceLines(i,INVOICED_QUANTITY)=getValue(oElement,Array("InvoicedQuantity")) '[BT-129]
arrInvoiceLines(i,QUANTITY_UNIT_CODE)=getValue(oElement,Array("InvoicedQuantity"),"unitCode","",True) '[BT-130]
arrInvoiceLines(i,INVOICE_LINE_EXTENSION_AMOUNT)=getValue(oElement,Array("LineExtensionAmount")) '[BT-131]
arrInvoiceLines(i,INVOICE_LINE_ITEM_DESCRIPTION)=getValue(oElement,Array("Item","Description")) '[BT-154]
arrInvoiceLines(i,INVOICE_LINE_ITEM_NAME)=getValue(oElement,Array("Item","Name")) '[BT-153]
arrInvoiceLines(i,INVOICE_LINE_ITEM_BUYERS_IDENTIFICATION_ID)=getValue(oElement,Array("Item","BuyersItemIdentification","ID")) '[BT-156]
arrInvoiceLines(i,INVOICE_LINE_ITEM_SELLER_IDENTIFICATION_ID)=getValue(oElement,Array("Item","SellersItemIdentification","ID")) '[BT-155]
arrInvoiceLines(i,TAX_CATEGORY_ID)=getValue(oElement,Array("Item","ClassifiedTaxCategory","ID")) '[BT-151]
arrInvoiceLines(i,TAX_PERCENT)=getValue(oElement,Array("Item","ClassifiedTaxCategory","Percent")) '[BT-152]
arrInvoiceLines(i,INVOICE_LINE_PRICE_AMOUNT)=getValue(oElement,Array("Price","PriceAmount")) '[BT-146]
arrInvoiceLineIDs(i-a-1)=arrInvoiceLines(i,INVOICE_LINE_ID)
Next
End If
getInvoiceLines=arrInvoiceLines
Exit Function
Catch:
MsgBox "Fehler beim Import der XML-Daten " & Chr$(10) & Chr$(10) & Error$ & " " & Chr$(10) & " ", 16, "XML-Import"
End Function
Function bFieldInArray(arrArray(), sEntry as String) As Boolean
Dim i%
For i = LBound(arrArray()) to UBound(arrArray())
If arrArray(i) = sEntry Then
bFieldInArray = True
Exit Function
End if
Next
bFieldInArray = False
End Function
REM Function getValue #####################################################################################################################################
REM Parameter:
REM oEntry Element, welches untersucht werden soll
REM arrNodePath() ein Array mit Elementen die rekursiv durchlaufen werden sollen, aus dem letzten Eintrag im Array wird der Wert gelesen, es muss die Reihenfolge beachtet werden,
REM sAttributName Der Name das Atrributes, welches untersucht oder ausgegeben werden soll
REM sAttribut der Wert des letzten Elements wird nur ausgelesen, wenn dessen Attribut dem Parameter entspricht
REM bGetAttribut Wenn True wird Atrribut.NodeValue ausgegeben, Wenn False wird nur verglichen ob das Attribunt mit dem Parameter übereinstimmt und dann NodeValue vom Element ausgegeben
Function getValue(oEntry As Object, arrNodePath(), Optional sAttributName As String, Optional sAttribut As String, Optional bGetAttribut As Boolean) As String
Dim a%, oElement As Object
If IsMissing(bGetAttribut) Then bGetAttribut=False
oElement=oEntry
getValue=""
For a = 0 To UBound(arrNodePath())
If oElement.getElementsByTagName(arrNodePath(a)).getLength > 0 And a < UBound(arrNodePath()) Then
If oElement.getElementsByTagName(arrNodePath(a)).item(0).NodeName = arrNodePath(a) Then
oElement=oElement.getElementsByTagName(arrNodePath(a)).item(0)
End If
ElseIf oElement.getElementsByTagName(arrNodePath(a)).getLength > 0 And a = UBound(arrNodePath()) Then
If oElement.getElementsByTagName(arrNodePath(a)).item(0).NodeName = arrNodePath(a) Then
If Not IsMissing(sAttributName) Then
If oElement.getElementsByTagName(arrNodePath(a)).item(0).Attributes.getLength > 0 Then
If oElement.getElementsByTagName(arrNodePath(a)).item(0).Attributes.item(0).NodeName = sAttributName Then
If bGetAttribut Then
getValue=oElement.getElementsByTagName(arrNodePath(a)).item(0).Attributes.item(0).NodeValue
Else
If oElement.getElementsByTagName(arrNodePath(a)).item(0).Attributes.item(0).NodeValue = sAttribut Then
If oElement.getElementsByTagName(arrNodePath(a)).item(0).hasChildNodes Then _
getValue=oElement.getElementsByTagName(arrNodePath(a)).item(0).FirstChild.NodeValue
End If
End If
End If
End If
Exit Function
Else
If oElement.getElementsByTagName(arrNodePath(a)).item(0).hasChildNodes Then _
getValue=oElement.getElementsByTagName(arrNodePath(a)).item(0).FirstChild.NodeValue
Exit Function
End If
End If
Else
Exit Function
End If
Next
End Function
Sub PrintValues
Dim bDebug As Boolean
Dim a% , b% , c% , d% , e% , f% , g% , h% , i% , j% , k% , l%, x%
bDebug = True
' bDebug = False
With Invoice
If bDebug Then MsgBox "ID: " & .sInvoiceID & Chr$(10) &_
"IssueDate: " & .sIssueDate & Chr$(10) &_
"DueDate: " & .sDueDate & Chr$(10) &_
"InvoiceTypeCode: " & .sInvoiceTypCode
If IsArray(arrInvoiceNote) Then
For f = 0 To UBound(arrInvoiceNote())
If bDebug Then MsgBox f+1 & ". Note -->" & Chr$(10) & arrInvoiceNote(f)
Next
End If
If bDebug Then MsgBox "DocumentCurrencyCode: " & .sDocumentCurrencyCode
If bDebug Then MsgBox "BuyerReference: " & .sBuyerReference
If IsArray(arrBillingReference) Then
For g = 0 To UBound(arrBillingReference())
If bDebug Then MsgBox g+1 & ". ID: " & arrBillingReference(g,BILLING_REFERENCE_ID) & " IssueDate: " & arrBillingReference(g,BILLING_REFERENCE_ISSUE_DATE)
Next
End If
If bDebug Then MsgBox "Supplier Party -->" &Chr$(10) & .sSellerName & Chr$(10) & .sSellerStreetName & Chr$(10) &_
.sSellerCountryCode & " " & .sSellerPLZ & " " & .sSellerCityName & Chr$(10) & .sSellerEMail
If bDebug Then MsgBox "SupplierTaxCompanyID: " & .sSellerTaxCompanyID
If bDebug Then MsgBox "Supplier Contact -->" & Chr$(10) & .sSellerContactName & Chr$(10) &_
"Telephone: " & .sSellerContactTelephone & Chr$(10) &_
"ElectronicMail: " & .sSellerContactElectronicMail
If bDebug Then MsgBox "ActualDeliveryDate: " & .sActualDeliveryDate
If bDebug Then MsgBox "PaymentMeans -->" & Chr$(10) &_
"PaymentMeansCode: " & .sPaymentMeansCode & Chr$(10) &_
"IBAN: " & .sPayeeFinancialAccountID & Chr$(10) &_
"AccountName: " & .sPayeeFinancialAccountName & Chr$(10) &_
"BIC: " & .sPayeeFinancialInstitutionBranchID
If bDebug Then MsgBox "PaymentTerms:" & Chr$(10) & .sPaymentTerms
If IsArray(arrAllowanceCharge) Then
For h = 0 To UBound(arrAllowanceCharge())
If bDebug Then MsgBox h+1 & ". AllowanceCharge -->" & Chr$(10) &_
"ChargeIndicator: " & arrAllowanceCharge(h,CHARGE_INDICATOR) & Chr$(10) &_
"AllowanceChargeReasonCode: " & arrAllowanceCharge(h,ALLOWANCE_CHARGE_REASON_CODE) & Chr$(10) &_
"AllowanceChargeReason: " & arrAllowanceCharge(h,ALLOWANCE_CHARGE_REASON) & Chr$(10) &_
"MultiplierFactorNumeric: " & arrAllowanceCharge(h,MULTIPLIER_FACTOR_NUMERIC) & Chr$(10) &_
"Amount: " & arrAllowanceCharge(h,AMOUNT) & Chr$(10) &_
"BaseAmount: " & arrAllowanceCharge(h,BASE_AMOUNT) & Chr$(10) &_
"TaxCategoryID: " & arrAllowanceCharge(h,TAX_CATEGORY_ID) & Chr$(10) &_
"TaxCategoryPercent: " & arrAllowanceCharge(h,TAX_PERCENT)
Next
End If
If bDebug Then MsgBox "TaxTotalAmount: " & .sTaxTotalAmount
For i = 0 To UBound(arrTaxSubtotal())
If bDebug Then MsgBox i+1 & ". TaxSubtotal -->" & Chr$(10) &_
"TaxableAmount: " & arrTaxSubtotal(i,TAXABLE_AMOUNT) & Chr$(10) &_
"TaxAmount: " & arrTaxSubtotal(i,TAX_AMOUNT) & Chr$(10) &_
"TaxCategoryID: " & arrTaxSubtotal(i,TAX_CATEGORY_ID) & Chr$(10) &_
"TaxPercent: " & arrTaxSubtotal(i,TAX_PERCENT) & Chr$(10) &_
"TaxExemptionReason: " & arrTaxSubtotal(i,TAX_EXEMPTION_REASON)
Next
If bDebug Then MsgBox "LegalMonetaryTotal -->" & Chr$(10) &_
"LineExtensionAmount: " & .sLineExtensionAmount & Chr$(10) &_
"TaxExclusiveAmount: " & .sTaxExclusiveAmount & Chr$(10) &_
"TaxInclusiveAmount: " & .sTaxInclusiveAmount & Chr$(10) &_
"AllowanceTotalAmount: " & .sAllowanceTotalAmount & Chr$(10) &_
"PrepaidAmount: " & .sPrepaidAmount & Chr$(10) &_
"PayableRoundingAmount: " & .sPayableRoundingAmount & Chr$(10) &_
"PayableAmount: " & .sPayableAmount
For j = 0 To UBound(arrInvoiceLines())
If bDebug Then MsgBox(j+1 & ". InvoiceLine -->" & Chr$(10) &_
"Level: " & arrInvoiceLines(j,INVOICE_LINE_LEVEL) & Chr$(10) &_
"Parent ID: " & arrInvoiceLines(j,PARENT_INVOICE_LINE_ID) & Chr$(10) &_
"ID: " & arrInvoiceLines(j,INVOICE_LINE_ID) & Chr$(10) &_
"Note: " & arrInvoiceLines(j,INVOICE_LINE_NOTE) & Chr$(10) &_
"Quantity: " & arrInvoiceLines(j,INVOICED_QUANTITY) & Chr$(10) &_
"UnitCode: " & arrInvoiceLines(j,QUANTITY_UNIT_CODE) & Chr$(10) &_
"ExtensionAmount: " & arrInvoiceLines(j,INVOICE_LINE_EXTENSION_AMOUNT) & Chr$(10) &_
"Description: " & arrInvoiceLines(j,INVOICE_LINE_ITEM_DESCRIPTION) & Chr$(10) &_
"Name: " & arrInvoiceLines(j,INVOICE_LINE_ITEM_NAME) & Chr$(10) &_
"Buyer IdentID: " & arrInvoiceLines(j,INVOICE_LINE_ITEM_BUYERS_IDENTIFICATION_ID) & Chr$(10) &_
"Sellers IdentID: " & arrInvoiceLines(j,INVOICE_LINE_ITEM_SELLER_IDENTIFICATION_ID) & Chr$(10) &_
"TaxCategory: " & arrInvoiceLines(j,TAX_CATEGORY_ID) & Chr$(10) &_
"TaxPercent: " & arrInvoiceLines(j,TAX_PERCENT) & Chr$(10) &_
"PriceAmount: " & arrInvoiceLines(j,INVOICE_LINE_PRICE_AMOUNT))
Next
End With
End Sub
Sub CreateSheet
Dim oCalcDoc As Object, oSheet As Object, oPathSettings As Object
Dim iRow As Integer
oPathSettings = CreateUnoService("com.sun.star.util.PathSettings")
oCalcDoc = StarDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, Array())
oSheet = oCalcDoc.Sheets(0)
Dim a% , b% , c% , d% , e% , f% , g% , h% , i% , j% , k% , l%, x%
With Invoice
iRow=1
fillSheet(oSheet,"A" & iRow,"Invoice ID",True)
fillSheet(oSheet,"B" & iRow,"IssueDate",True)
fillSheet(oSheet,"C" & iRow,"DueDate",True)
fillSheet(oSheet,"D" & iRow,"ActualDeliveryDate",True)
fillSheet(oSheet,"E" & iRow,"InvoiceTypeCode",True)
fillSheet(oSheet,"F" & iRow,"DocumentCurrencyCode",True)
fillSheet(oSheet,"G" & iRow,"BuyerReference",True)
iRow=iRow+1
fillSheet(oSheet,"A" & iRow,.sInvoiceID,False)
fillSheet(oSheet,"B" & iRow,.sIssueDate,False)
fillSheet(oSheet,"C" & iRow,.sDueDate,False)
fillSheet(oSheet,"D" & iRow,.sActualDeliveryDate,False)
fillSheet(oSheet,"E" & iRow,.sInvoiceTypCode,False)
fillSheet(oSheet,"F" & iRow,.sDocumentCurrencyCode,False)
fillSheet(oSheet,"G" & iRow,.sBuyerReference,False)
iRow=iRow+1
If IsArray(arrInvoiceNote) Then
fillSheet(oSheet,"A" & iRow & ":D" & iRow,"Invoice Notes",True)
iRow=iRow+1
For a = 0 To UBound(arrInvoiceNote())
fillSheet(oSheet,"A" & iRow+a & ":D" & iRow+a,arrInvoiceNote(a),False)
Next
iRow=iRow+a+1
End If
If IsArray(arrBillingReference) Then
fillSheet(oSheet,"A" & iRow,"BillingReference ID",True)
fillSheet(oSheet,"B" & iRow,"IssueDate",True)
iRow=iRow+1
For b = 0 To UBound(arrBillingReference())
fillSheet(oSheet,"A" & iRow+b,arrBillingReference(b,BILLING_REFERENCE_ID),False)
fillSheet(oSheet,"B" & iRow+b,arrBillingReference(b,BILLING_REFERENCE_ISSUE_DATE),False)
Next
iRow=iRow+b+1
End If
fillSheet(oSheet,"A" & iRow,"Seller Party -->",False)
iRow=iRow+1
fillSheet(oSheet,"A" & iRow,"SellerName",True)
fillSheet(oSheet,"B" & iRow,"SellerStreetName",True)
fillSheet(oSheet,"C" & iRow,"SellerCountryCode",True)
fillSheet(oSheet,"D" & iRow,"SellerPLZ",True)
fillSheet(oSheet,"E" & iRow,"SellerCityName",True)
fillSheet(oSheet,"F" & iRow,"SellerEMail",True)
fillSheet(oSheet,"G" & iRow,"Seller TaxCompanyID",True)
iRow=iRow+1
fillSheet(oSheet,"A" & iRow,.sSellerName,False)
fillSheet(oSheet,"B" & iRow,.sSellerStreetName,False)
fillSheet(oSheet,"C" & iRow,.sSellerCountryCode,False)
fillSheet(oSheet,"D" & iRow,.sSellerPLZ,False)
fillSheet(oSheet,"E" & iRow,.sSellerCityName,False)
fillSheet(oSheet,"F" & iRow,.sSellerEMail,False)
fillSheet(oSheet,"G" & iRow,.sSellerTaxCompanyID,False)
iRow=iRow+2
fillSheet(oSheet,"A" & iRow,"Seller Contact -->",False)
iRow=iRow+1
fillSheet(oSheet,"A" & iRow,"ContactName",True)
fillSheet(oSheet,"B" & iRow,"Telephone",True)
fillSheet(oSheet,"C" & iRow,"ElectronicMail",True)
iRow=iRow+1
fillSheet(oSheet,"A" & iRow,.sSellerContactName,False)
fillSheet(oSheet,"B" & iRow,.sSellerContactTelephone,False)
fillSheet(oSheet,"C" & iRow,.sSellerContactElectronicMail,False)
iRow=iRow+2
fillSheet(oSheet,"A" & iRow,"PaymentMeans -->",False)
iRow=iRow+1
fillSheet(oSheet,"A" & iRow,"AccountName",True)
fillSheet(oSheet,"B" & iRow,"IBAN",True)
fillSheet(oSheet,"C" & iRow,"BIC",True)
fillSheet(oSheet,"D" & iRow,"PaymentMeansCode",True)
iRow=iRow+1
fillSheet(oSheet,"A" & iRow,.sPayeeFinancialAccountName,False)
fillSheet(oSheet,"B" & iRow,.sPayeeFinancialAccountID,False)
fillSheet(oSheet,"C" & iRow,.sPayeeFinancialInstitutionBranchID,False)
fillSheet(oSheet,"D" & iRow,.sPaymentMeansCode,False)
iRow=iRow+2
fillSheet(oSheet,"A" & iRow & ":D" & iRow,"PaymentTerms",True)
iRow=iRow+1
fillSheet(oSheet,"A" & iRow & ":D" & iRow,.sPaymentTerms,False)
iRow=iRow+2
If IsArray(arrAllowanceCharge) Then
fillSheet(oSheet,"A" & iRow,"AllowanceCharge -->",False)
iRow=iRow+1
fillSheet(oSheet,"A" & iRow,"ChargeIndicator",True)
fillSheet(oSheet,"B" & iRow,"MultiplierFactorNumeric",True)
fillSheet(oSheet,"C" & iRow,"Amount",True)
fillSheet(oSheet,"D" & iRow,"BaseAmount",True)
fillSheet(oSheet,"E" & iRow,"TaxCategoryID",True)
fillSheet(oSheet,"F" & iRow,"TaxCategoryPercent",True)
fillSheet(oSheet,"G" & iRow,"AllowanceChargeReasonCode",True)
fillSheet(oSheet,"H" & iRow,"AllowanceChargeReason",True)
iRow=iRow+1
For c = 0 To UBound(arrAllowanceCharge())
fillSheet(oSheet,"A" & iRow+c,arrAllowanceCharge(c,CHARGE_INDICATOR),False)
fillSheet(oSheet,"B" & iRow+c,arrAllowanceCharge(c,MULTIPLIER_FACTOR_NUMERIC),False)
fillSheet(oSheet,"C" & iRow+c,arrAllowanceCharge(c,AMOUNT),False)
fillSheet(oSheet,"D" & iRow+c,arrAllowanceCharge(c,BASE_AMOUNT),False)
fillSheet(oSheet,"E" & iRow+c,arrAllowanceCharge(c,TAX_CATEGORY_ID),False)
fillSheet(oSheet,"F" & iRow+c,arrAllowanceCharge(c,TAX_PERCENT),False)
fillSheet(oSheet,"G" & iRow+c,arrAllowanceCharge(c,ALLOWANCE_CHARGE_REASON_CODE),False)
fillSheet(oSheet,"H" & iRow+c,arrAllowanceCharge(c,ALLOWANCE_CHARGE_REASON),False)
Next
iRow=iRow+c+1
End If
fillSheet(oSheet,"A" & iRow,"TaxTotalAmount:",True)
fillSheet(oSheet,"B" & iRow,.sTaxTotalAmount,False)
iRow=iRow+2
If IsArray(arrTaxSubtotal) Then
fillSheet(oSheet,"A" & iRow,"TaxSubtotal -->",False)
iRow=iRow+1
fillSheet(oSheet,"A" & iRow,"TaxableAmount",True)
fillSheet(oSheet,"B" & iRow,"TaxAmount",True)
fillSheet(oSheet,"C" & iRow,"TaxCategoryID",True)
fillSheet(oSheet,"D" & iRow,"TaxPercent",True)
fillSheet(oSheet,"E" & iRow,"",True)
fillSheet(oSheet,"F" & iRow,"",True)
fillSheet(oSheet,"G" & iRow,"",True)
fillSheet(oSheet,"H" & iRow,"TaxExemptionReason",True)
iRow=iRow+1
For d = 0 To UBound(arrTaxSubtotal())
fillSheet(oSheet,"A" & iRow+d,arrTaxSubtotal(d,TAXABLE_AMOUNT),False)
fillSheet(oSheet,"B" & iRow+d,arrTaxSubtotal(d,TAX_AMOUNT),False)
fillSheet(oSheet,"C" & iRow+d,arrTaxSubtotal(d,TAX_CATEGORY_ID),False)
fillSheet(oSheet,"D" & iRow+d,arrTaxSubtotal(d,TAX_PERCENT),False)
fillSheet(oSheet,"H" & iRow+d,arrTaxSubtotal(d,TAX_EXEMPTION_REASON),False)
Next
iRow=iRow+d+1
End If
fillSheet(oSheet,"A" & iRow,"LegalMonetaryTotal -->",False)
iRow=iRow+1
fillSheet(oSheet,"A" & iRow,"LineExtensionAmount",True)
fillSheet(oSheet,"B" & iRow,"TaxExclusiveAmount",True)
fillSheet(oSheet,"C" & iRow,"TaxInclusiveAmount",True)
fillSheet(oSheet,"D" & iRow,"AllowanceTotalAmount",True)
fillSheet(oSheet,"E" & iRow,"PrepaidAmount",True)
fillSheet(oSheet,"F" & iRow,"PayableRoundingAmount",True)
fillSheet(oSheet,"G" & iRow,"PayableAmount",True)
iRow=iRow+1
fillSheet(oSheet,"A" & iRow,.sLineExtensionAmount,False)
fillSheet(oSheet,"B" & iRow,.sTaxExclusiveAmount,False)
fillSheet(oSheet,"C" & iRow,.sTaxInclusiveAmount,False)
fillSheet(oSheet,"D" & iRow,.sAllowanceTotalAmount,False)
fillSheet(oSheet,"E" & iRow,.sPrepaidAmount,False)
fillSheet(oSheet,"F" & iRow,.sPayableRoundingAmount,False)
fillSheet(oSheet,"G" & iRow,.sPayableAmount,False)
iRow=iRow+2
If IsArray(arrInvoiceLines) Then
fillSheet(oSheet,"A" & iRow,"InvoiceLine -->",False)
iRow=iRow+1
fillSheet(oSheet,"A" & iRow,"Level",True)
fillSheet(oSheet,"B" & iRow,"Parent ID",True)
fillSheet(oSheet,"C" & iRow,"InvoiceLine ID",True)
fillSheet(oSheet,"D" & iRow,"Quantity",True)
fillSheet(oSheet,"E" & iRow,"UnitCode",True)
fillSheet(oSheet,"F" & iRow,"Buyer IdentID",True)
fillSheet(oSheet,"G" & iRow,"Sellers IdentID",True)
fillSheet(oSheet,"H" & iRow,"Name",True)
fillSheet(oSheet,"I" & iRow,"TaxCategory",True)
fillSheet(oSheet,"J" & iRow,"TaxPercent",True)
fillSheet(oSheet,"K" & iRow,"ExtensionAmount" & Chr$(10) & "LineTotalAmount",True)
fillSheet(oSheet,"L" & iRow,"PriceAmount" & Chr$(10) & "ChargeAmount",True)
fillSheet(oSheet,"M" & iRow,"Description",True)
iRow=iRow+1
For e = 0 To UBound(arrInvoiceLines())
fillSheet(oSheet,"A" & iRow+e,arrInvoiceLines(e,INVOICE_LINE_LEVEL),False)
fillSheet(oSheet,"B" & iRow+e,arrInvoiceLines(e,PARENT_INVOICE_LINE_ID),False)
fillSheet(oSheet,"C" & iRow+e,arrInvoiceLines(e,INVOICE_LINE_ID),False)
fillSheet(oSheet,"D" & iRow+e,arrInvoiceLines(e,INVOICED_QUANTITY),False)
fillSheet(oSheet,"E" & iRow+e,arrInvoiceLines(e,QUANTITY_UNIT_CODE),False)
fillSheet(oSheet,"F" & iRow+e,arrInvoiceLines(e,INVOICE_LINE_ITEM_BUYERS_IDENTIFICATION_ID),False)
fillSheet(oSheet,"G" & iRow+e,arrInvoiceLines(e,INVOICE_LINE_ITEM_SELLER_IDENTIFICATION_ID),False)
fillSheet(oSheet,"H" & iRow+e,arrInvoiceLines(e,INVOICE_LINE_ITEM_NAME),False)
fillSheet(oSheet,"I" & iRow+e,arrInvoiceLines(e,TAX_CATEGORY_ID),False)
fillSheet(oSheet,"J" & iRow+e,arrInvoiceLines(e,TAX_PERCENT),False)
fillSheet(oSheet,"K" & iRow+e,arrInvoiceLines(e,INVOICE_LINE_EXTENSION_AMOUNT),False)
fillSheet(oSheet,"L" & iRow+e,arrInvoiceLines(e,INVOICE_LINE_PRICE_AMOUNT),False)
fillSheet(oSheet,"M" & iRow+e,arrInvoiceLines(e,INVOICE_LINE_ITEM_DESCRIPTION),False)
Next
iRow=iRow+e+1
End If
End With
oSheet.getColumns().OptimalWidth = True
oSheet.getRows().OptimalHeight = True
End Sub
Sub fillSheet(oSheet, sRange As String, sValue As String, bBottomLine As Boolean)
Dim oBorder As Object, oCell As Object, oRange As Object
Dim bLO As Boolean
oBorder = CreateUnoStruct("com.sun.star.table.BorderLine2")
bLO = True
If IsNull(oBorder) Then
bLO = False
oBorder = CreateUnoStruct("com.sun.star.table.BorderLine")
End If
oBorder.OuterLineWidth = 40
If InStr(sRange,":") > 0 Then
oRange=oSheet.getCellRangeByName(sRange)
oRange.merge(True)
oCell=oSheet.getCellRangeByName(Split(sRange,":")(0))
Else
oCell=oSheet.getCellRangeByName(sRange)
End If
If bBottomLine Then
If bLO Then
oBorder.LineStyle = com.sun.star.table.BorderLineStyle.SOLID
oBorder.LineWidth = 40
oCell.BottomBorder2 = oBorder
Else
oCell.BottomBorder = oBorder
End If
End If
oCell.VertJustify = com.sun.star.table.CellVertJustify.TOP
oCell.String = sValue
End Sub