here we discussed the different possibilities how one can send out a PDF-Form and get the results back.
viewtopic.php?f=7&t=91468
The xml-option gives back a nice XFDF-xml-document.
To extract form data of Folder of PDFs (using PDFtk) in to .txt-files see
viewtopic.php?f=21&t=91586#p434033
To convert all .txt files to .xdfd files see:
viewtopic.php?f=21&t=91587
Create CSV from Formdata of PDFs in a folder (using PDFtk)
viewtopic.php?f=21&t=91588
This XFDF to CSV-Converter will convert a chosen .xfdf-file into a .cvs-file:
(for a batch converter of folder of .xfdf-files scroll down)
Code: Select all
Sub Convert_XFDF_to_CSV
If (Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools")) Then GlobalScope.BasicLibraries.LoadLibrary("Tools")
rem---filepicker
Fileurl=""
rem to not use the filepicker set the file here and uncomment:
rem Fileurl=converttourl("C:\Users\kai\Desktop\PDFform_xml.xfdf")
if Fileurl="" then
oFileDlg = CreateUnoService("com.sun.star.ui.dialogs.FilePicker")
oFileDlg.AppendFilter("all files", "*.*")
oFileDlg.AppendFilter("XFDF files","*.xfdf")
oFileDlg.SetCurrentFilter("XFDF files")
rem oFileDlg.setDisplayDirectory(workdirfull)
If oFileDlg.execute() Then
oFiles = oFileDlg.getFiles()
If UBound(oFiles) >= 0 Then
Fileurl = oFiles(0)
End If
else
exit sub
End If
end if
if not Fileexists(Fileurl) then
msgbox "File not found!"
exit sub
end if
sFolderurl=DirectoryNameoutofPath(Fileurl,"/")
sFilename_noext=GetFileNameWithoutExtension(Fileurl,"/")
sFileext=GetFileNameExtension(Fileurl,"/")
sFilename=FileNameoutofPath(Fileurl,"/")
oSFA = createUnoService("com.sun.star.ucb.SimpleFileAccess")
oInStream = oSFA.openFileRead(Fileurl)
oDOM = createUnoService("com.sun.star.xml.dom.DocumentBuilder")
oXML = oDOM.parse(oInStream)
oInStream.closeInput
nodeListFields = oXML.getElementsByTagName("fields").item(0).getElementsByTagName("field")
rem msgbox nodeListFields.length
for i=0 to nodeListFields.length-1
curFieldNode = nodeListFields.item(i)
If curFieldNode.hasAttributes Then
nodeAttributes = curFieldNode.Attributes
For k=0 To nodeAttributes.Length - 1
'FieldNameResult = FieldNameResult & " " & nodeAttributes.item(k).NodeName & "=" & nodeAttributes.item(k).Value
FieldNameResult = FieldNameResult & ";" & convertXMLstring(nodeAttributes.item(k).Value)
Next k
end if
childcounter=1
sNodeValue=""
sNodeValuetemp=""
If curFieldNode.hasChildNodes Then
curChildNode = curFieldNode.firstChild
Do While NOT isNull(curChildNode)
If curChildNode.LocalName = "value" then
if curChildNode.hasChildNodes then
curChildChildNode=curChildNode.firstChild
sNodeValuetemp= curChildChildNode.NodeValue
sNodeValuetemp= replace(sNodeValuetemp,chr(10), "\n")
sNodeValuetemp= replace(sNodeValuetemp,";", ",")
else
sNodeValuetemp=""
end if
if childcounter=1 then
sNodeValue=sNodeValuetemp
else
sNodeValue = sNodeValue & "\n" & sNodeValuetemp
end if
childcounter=childcounter+1
end if
curChildNode = curChildNode.nextSibling
loop
FieldValueResult = FieldValueResult & ";" & sNodeValue
end if
next i
FieldNameResult=mid(FieldNameResult,2)
FieldValueResult=mid(replace(FieldValueResult,chr(10), " "),2)
'msgbox FieldNameResult & chr(13) & chr(10) & FieldValueResult
if fileexists(sFolderurl & "/" & sFilename_noext & ".csv") then kill(sFolderurl & "/" & sFilename_noext & ".csv")
outputfile = sFolderurl & "/" & sFilename_noext & ".csv"
oTextoutputStream = CreateUnoService("com.sun.star.io.TextOutputStream")
ooutputStream = oSFA.openFileWrite(outputfile)
oTextoutputStream.setOutputStream(ooutputStream)
oTextoutputStream.writestring(FieldNameResult & chr(10) & FieldValueResult)
oTextoutputStream.closeOutput()
msgbox "Saved " & convertfromurl(sFolderurl & "/" & sFilename_noext & ".csv")
End Sub
function convertXMLstring(instring)
instring=replace(instring,"#","%")
convertXMLstring = convertfromurl(instring)
end function
This XFDF to CSV-Converter will merge all .xfdf files in a chosen Folder, rebuild the HeaderNames and create a merged CSV:
(at the end CSV will be opened automatically in Calc, "\n"-strings converted to real linebreaks and optimal RowHeight and ColumnWidth set.)
Code: Select all
Sub Convert_multipleXFDFs_to_1CSV
If (Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools")) Then GlobalScope.BasicLibraries.LoadLibrary("Tools")
oSFA = createUnoService("com.sun.star.ucb.SimpleFileAccess")
sFolderpath=""
rem---folderpicker
rem to not use the folderpicker set the folder here and uncomment:
rem sFolderpath=converttourl("C:\Users\kai\Desktop")
if sFolderpath="" then
oDialog = CreateUnoService("com.sun.star.ui.dialogs.FolderPicker")
If oDialog.Execute() = 1 Then
sFolderpath = oDialog.getDirectory
else
exit sub
end if
end if
if not oSFA.Exists(sFolderpath) then
msgbox "Folder not found!"
exit sub
end if
dim allXFDFHeaders(0)
dim allXFDFValues()
sFileName=""
sFileName = Dir(sFolderpath & "/", 0)
Do While (sFileName <> "")
if GetFileNameExtension(sFileName)="xfdf" then
Fileurl=sFolderpath & "/" & sFileName
oSFA = createUnoService("com.sun.star.ucb.SimpleFileAccess")
oInStream = oSFA.openFileRead(Fileurl)
oDOM = createUnoService("com.sun.star.xml.dom.DocumentBuilder")
oXML = oDOM.parse(oInStream)
oInStream.closeInput
nodeListFields = oXML.getElementsByTagName("fields").item(0).getElementsByTagName("field")
for i=0 to nodeListFields.length-1
curFieldNode = nodeListFields.item(i)
If curFieldNode.hasAttributes Then
nodeAttributes = curFieldNode.Attributes
For k=0 To nodeAttributes.Length - 1
additem=1
for j=0 to ubound(allXFDFHeaders)
if allXFDFHeaders(j)=convertXMLstring(nodeAttributes.item(k).Value) then additem=0
next j
if additem=1 then
redim preserve allXFDFHeaders(ubound(allXFDFHeaders)+1)
allXFDFHeaders(ubound(allXFDFHeaders))=convertXMLstring(nodeAttributes.item(k).Value)
end if
Next k
end if
next i
end if
sFileName = Dir()
loop
k=0
i=0
j=0
sFileName = Dir(sFolderpath & "/", 0)
Do While (sFileName <> "")
if GetFileNameExtension(sFileName)="xfdf" then
Fileurl=sFolderpath & "/" & sFileName
oSFA = createUnoService("com.sun.star.ucb.SimpleFileAccess")
oInStream = oSFA.openFileRead(Fileurl)
oDOM = createUnoService("com.sun.star.xml.dom.DocumentBuilder")
oXML = oDOM.parse(oInStream)
oInStream.closeInput
nodeListFields = oXML.getElementsByTagName("fields").item(0).getElementsByTagName("field")
FieldNameResult=""
FieldValueResult=""
for i=0 to nodeListFields.length-1
curFieldNode = nodeListFields.item(i)
If curFieldNode.hasAttributes Then
nodeAttributes = curFieldNode.Attributes
For k=0 To nodeAttributes.Length - 1
FieldNameResult = FieldNameResult & chr(9) & convertXMLstring(nodeAttributes.item(k).Value)
Next k
end if
childcounter=1
sNodeValue=""
sNodeValuetemp=""
If curFieldNode.hasChildNodes Then
curChildNode = curFieldNode.firstChild
Do While NOT isNull(curChildNode)
If curChildNode.LocalName = "value" then
if curChildNode.hasChildNodes then
curChildChildNode=curChildNode.firstChild
sNodeValuetemp= curChildChildNode.NodeValue
sNodeValuetemp= replace(sNodeValuetemp,chr(10), "\n")
sNodeValuetemp= replace(sNodeValuetemp,";", ",")
else
sNodeValuetemp=""
end if
if childcounter=1 then
sNodeValue=sNodeValuetemp
else
sNodeValue = sNodeValue & "\n" & sNodeValuetemp
end if
childcounter=childcounter+1
end if
curChildNode = curChildNode.nextSibling
loop
FieldValueResult = FieldValueResult & chr(9) & sNodeValue
end if
next i
m=0
n=0
XFDFHeaders = split(FieldNameResult,chr(9))
XFDFValues = split(FieldValueResult,chr(9))
redim allXFDFValues(ubound(allXFDFHeaders))
for m=0 to ubound(XFDFHeaders)
for n=0 to ubound(allXFDFHeaders)
if XFDFHeaders(m)=allXFDFHeaders(n) then allXFDFValues(n)=XFDFValues(m)
next n
next m
FieldValueResult2 = FieldValueResult2 & sFileName & join(allXFDFValues(),";") & chr(13) & chr(10)
end if
sFileName = Dir()
loop
if ubound(allXFDFHeaders())=0 then
msgbox "No .xfdf files found!"
exit sub
end if
Outstring = "File" & join(allXFDFHeaders(),";") & chr(13) & chr(10) & FieldValueResult2
outputfile = sFolderpath & "/" & "merged.csv"
if fileexists(outputfile) then kill(outputfile)
oTextoutputStream = CreateUnoService("com.sun.star.io.TextOutputStream")
ooutputStream = oSFA.openFileWrite(outputfile)
oTextoutputStream.setOutputStream(ooutputStream)
oTextoutputStream.writestring(Outstring)
oTextoutputStream.closeOutput()
msgbox "Saved: " & convertfromurl(outputfile)
OpenCSVinCalc(outputfile)
calc_replaceLinebreak_UsedArea
End Sub
rem-----for opening in Calc
Sub OpenCSVinCalc(Url)
Dim oDocument as Object
fnURL=ConvertToURL(Url)
'Create new document and import data
oDocument = StarDesktop.LoadComponentFromURL( fnURL, "_blank", 0, _
Array(MakePropertyValue( "FilterName", "Text - txt - csv (StarCalc)" ), _
MakePropertyValue( "FilterOptions", "59,34,76,1,1" )
End Sub
Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue
oPropertyValue = createUnoStruct( "com.sun.star.beans.PropertyValue" )
If Not IsMissing( cName ) Then
oPropertyValue.Name = cName
EndIf
If Not IsMissing( uValue ) Then
oPropertyValue.Value = uValue
EndIf
MakePropertyValue() = oPropertyValue
End Function
function convertXMLstring(instring)
instring=replace(instring,"#","%")
convertXMLstring = convertfromurl(instring)
end function
sub calc_replaceLinebreak_UsedArea
Doc = ThisComponent
If Doc.SupportsService("com.sun.star.sheet.SpreadsheetDocument") Then
else
exit sub
end if
Sheet = Doc.Sheets.getByIndex(0)
oCursor = Sheet.createCursor()
oCursor.gotoEndofUsedArea(false)
for i=0 to oCursor.RangeAddress.EndColumn
j=0
for j=0 to oCursor.RangeAddress.EndRow
Sheet.getCellByPosition(i,j).string = replace(Sheet.getCellByPosition(i,j).string,"\n",chr(10))
'Sheet.getCellByPosition(i,j).string = replace(Sheet.getCellByPosition(i,j).string,chr(10),"\n")
next j
next i
rem---Set Optimal RowHeight
for k=0 to oCursor.RangeAddress.EndRow
oRow = Sheet.getRows().getByIndex(oCursor.RangeAddress.EndRow-k)
oRow.OptimalHeight()=True
next
rem---Set oOptimal ColumnWidth
for m=0 to oCursor.RangeAddress.EndColumn
oColumn = Sheet.getColumns().getByIndex(oCursor.RangeAddress.EndColumn-m)
oColumn.OptimalWidth()=True
next
end sub
http://de.openoffice.info/viewtopic.php?t=62289
Please test!