J'ai récupéré le code de Boby pour lire une cellule excel d'un fichier .xlxs existant. Ca fonctionne bien.
https://www.purebasic.fr/french/viewtop ... =6&t=16688
J'ai besoin d'une procedure pour écrire une cellule. J'ai naïvement copier/coller la procedure d'écriture et remplacé les GetXMLNodeText(node) par SetXMLNodeText(node,text$).
Ca ne fonctionne pas.
Quelqu'un aurait il dans ses tiroirs une procédure pour écrire une cellule excel, par hasard ?
Merci de votre aide.
Code : Tout sélectionner
DeclareModule Excel
Declare Open(File$) ; Returns the new generated number if the file was opened successfully and zero if there was an error
Declare Close(File) ; Returns nonzero if the operation was successful or zero if it failed.
Declare CountSheets(File) ; Returns amount of sheets in the workbook, zero if can't count.
Declare.s GetSheetName(File,Sheet=1) ; Returns sheet name of the #sheet, "" if it failed.
Declare CountRow(File,Sheet=0) ; Returns amount of row in the #sheet, zero if can't count.
Declare CountCol(File,Sheet=0) ; Returns amount of col in the #sheet, zero if can't count.
Declare LoadSheet(File,Sheet) ; Returns nonzero if the operation was successful or zero if it failed.
Declare.s ReadCell(File,Col,Row,Sheet=0) ; Returns cell value, " " if empty, "" if it failed.
Declare.s WriteCell(File,Col,Row,text$,Sheet=0)
EndDeclareModule
Module Excel
UseZipPacker()
Structure file
File.i
SharedString.i
Sheet.i
WorkBook.i
EndStructure
Procedure Open(File$)
*pack.file = AllocateMemory(SizeOf(file))
If Not GetExtensionPart(File$) = "xlsx" : ProcedureReturn #False : EndIf
*Pack\File = OpenPack(#PB_Any,File$)
If *pack\File
CreateDirectory(GetTemporaryDirectory()+Str(*Pack\File))
CreateDirectory(GetTemporaryDirectory()+Str(*Pack\File)+"\_rels")
CreateDirectory(GetTemporaryDirectory()+Str(*Pack\File)+"\docProps")
CreateDirectory(GetTemporaryDirectory()+Str(*Pack\File)+"\xl")
CreateDirectory(GetTemporaryDirectory()+Str(*Pack\File)+"\xl\_rels")
CreateDirectory(GetTemporaryDirectory()+Str(*Pack\File)+"\xl\printerSettings")
CreateDirectory(GetTemporaryDirectory()+Str(*Pack\File)+"\xl\theme")
CreateDirectory(GetTemporaryDirectory()+Str(*Pack\File)+"\xl\worksheets")
ExaminePack(*Pack\File)
While NextPackEntry(*Pack\File)
UncompressPackFile(*Pack\File,GetTemporaryDirectory()+Str(*Pack\File)+"\"+PackEntryName(*pack\File))
Wend
ClosePack(*Pack\File)
*pack\WorkBook = LoadXML(#PB_Any,GetTemporaryDirectory()+Str(*Pack\File)+"\xl\workbook.xml")
If Not XMLStatus(*pack\WorkBook) = #PB_XML_Success : ProcedureReturn #False : EndIf
*pack\SharedString = LoadXML(#PB_Any,GetTemporaryDirectory()+Str(*Pack\File)+"\xl\sharedStrings.xml")
If Not XMLStatus(*pack\SharedString) = #PB_XML_Success : ProcedureReturn #False : EndIf
ProcedureReturn *Pack
Else
ProcedureReturn #False
EndIf
EndProcedure
Procedure Close(*File.file)
If Not *File : ProcedureReturn #False : EndIf
If IsXML(*File\SharedString) : FreeXML(*File\SharedString) : EndIf
If IsXML(*File\Sheet) : FreeXML(*File\Sheet) : EndIf
If IsXML(*File\WorkBook) : FreeXML(*File\WorkBook) : EndIf
If DeleteDirectory(GetTemporaryDirectory()+Str(*File\File),"*.*",#PB_FileSystem_Recursive)
ProcedureReturn #True
Else
ProcedureReturn #False
EndIf
EndProcedure
Procedure CountSheets(*File.file)
If Not *File : ProcedureReturn #False : EndIf
Protected node
node = MainXMLNode(*file\WorkBook)
node = ChildXMLNode(node,4)
ProcedureReturn XMLChildCount(node)
EndProcedure
Procedure.s GetSheetName(*File.file,Sheet=1)
If Not *File : ProcedureReturn "" : EndIf
Protected node
node = MainXMLNode(*File\WorkBook)
node = ChildXMLNode(node,4)
While Not GetXMLNodeName(node) = "sheets"
node = NextXMLNode(node)
If node = 0 : ProcedureReturn "" : EndIf
Wend
node = ChildXMLNode(node,Sheet)
If node
ProcedureReturn GetXMLAttribute(node,"name")
Else
ProcedureReturn ""
EndIf
EndProcedure
Procedure CountRow(*File.file,Sheet=0) ; If sheet is set to 0, the current (or 1st if not current) Sheet will be read.
If Not *File : ProcedureReturn #False : EndIf
Protected node, Row$
If Sheet = 0
If IsXML(*File\Sheet) = 0
If Not LoadSheet(*File,1) : ProcedureReturn #False : EndIf
EndIf
Else
If Not LoadSheet(*File,Sheet) : ProcedureReturn #False : EndIf
EndIf
node = MainXMLNode(*File\Sheet)
node = ChildXMLNode(node)
While GetXMLNodeName(node) <> "dimension"
node = NextXMLNode(node)
Wend
row$ = ReverseString(((ReverseString(StringField(GetXMLAttribute(node,"ref"),2,":")))))
Repeat
If Asc(row$) < 48 Or Asc(row$) > 57
row$ = RemoveString(row$,Chr(Asc(row$)))
Else
Break
EndIf
ForEver
ProcedureReturn Val(Row$)
EndProcedure
Procedure CountCol(*File.File,Sheet=0) ; If sheet is set to 0, the current (or 1st if not current) Sheet will be read.
If Not *File : ProcedureReturn #False : EndIf
Protected node, col, size
If Sheet = 0
If IsXML(*File\Sheet) = 0
If Not LoadSheet(*File,1) : ProcedureReturn #False : EndIf
EndIf
Else
If Not LoadSheet(*File,Sheet) : ProcedureReturn #False : EndIf
EndIf
node = MainXMLNode(*File\Sheet)
node = ChildXMLNode(node)
While GetXMLNodeName(node) <> "dimension"
node = NextXMLNode(node)
Wend
size = Len(StringField(GetXMLAttribute(node,"ref"),2,":")) - Len(Str(Val((ReverseString(StringField(GetXMLAttribute(node,"ref"),2,":"))))))
If size = 1
ProcedureReturn Asc(Left(StringField(GetXMLAttribute(node,"ref"),2,":"),1))-64
ElseIf size = 2
ProcedureReturn (Asc(Left(StringField(GetXMLAttribute(node,"ref"),2,":"),1))-64)*26 + Asc(Left(Right(StringField(GetXMLAttribute(node,"ref"),2,":"),Len(StringField(GetXMLAttribute(node,"ref"),2,":"))-1),1))-64
EndIf
EndProcedure
Procedure LoadSheet(*File.file,Sheet)
If Not *File : ProcedureReturn #False : EndIf
If IsXML(*File\Sheet) : FreeXML(*File\Sheet) : EndIf
*File\Sheet = LoadXML(#PB_Any,GetTemporaryDirectory()+Str(*File\File)+"\xl\worksheets\sheet"+Sheet+".xml")
If XMLStatus(*File\Sheet) = #PB_XML_Success
ProcedureReturn #True
Else
ProcedureReturn #False
EndIf
EndProcedure
Procedure.s ReadCell(*File.file,Col,Row,Sheet=0) ; If sheet is set to 0, the current (or 1st if not current) Sheet will be read.
If Not *File : ProcedureReturn "" : EndIf
Protected node, node2
If Sheet = 0
If IsXML(*File\Sheet) = 0
If Not LoadSheet(*File,1) : ProcedureReturn "" : EndIf
EndIf
Else
If Not LoadSheet(*File,Sheet) : ProcedureReturn "" : EndIf
EndIf
node = MainXMLNode(*File\Sheet)
node2 = MainXMLNode(*File\SharedString)
node = ChildXMLNode(node)
While Not GetXMLNodeName(node) = "sheetData"
node = NextXMLNode(node)
Wend
node = ChildXMLNode(node)
While Not Val(GetXMLAttribute(node,"r")) = row
node = NextXMLNode(node)
If node = 0 : ProcedureReturn " " : EndIf
Wend
If ChildXMLNode(node,col)
node = ChildXMLNode(node,col)
Else
ProcedureReturn " "
EndIf
If GetXMLAttribute(node,"t") = "s"
If ChildXMLNode(node)
node = ChildXMLNode(node)
Else
ProcedureReturn " "
EndIf
node2 = ChildXMLNode(node2,Val(GetXMLNodeText(node))+1)
If node2
node2 = ChildXMLNode(node2)
ProcedureReturn GetXMLNodeText(node2)
Else
ProcedureReturn ""
EndIf
ElseIf GetXMLAttribute(node,"t") = ""
If ChildXMLNode(node)
node = ChildXMLNode(node)
ProcedureReturn GetXMLNodeText(node)
Else
ProcedureReturn " "
EndIf
ElseIf GetXMLAttribute(node,"t") = "e"
If ChildXMLNode(node)
node = ChildXMLNode(node)
While Not GetXMLNodeName(node) = "v"
node = NextXMLNode(node)
If node = 0 : ProcedureReturn "" : EndIf
Wend
ProcedureReturn GetXMLNodeText(node)
Else
ProcedureReturn " "
EndIf
EndIf
EndProcedure
Procedure.s WriteCell(*File.file,Col,Row,text$,Sheet=0) ; If sheet is set to 0, the current (or 1st if not current) Sheet will be read.
If Not *File : ProcedureReturn "" : EndIf
Protected node, node2
If Sheet = 0
If IsXML(*File\Sheet) = 0
If Not LoadSheet(*File,1) : ProcedureReturn "" : EndIf
EndIf
Else
If Not LoadSheet(*File,Sheet) : ProcedureReturn "" : EndIf
EndIf
node = MainXMLNode(*File\Sheet)
node2 = MainXMLNode(*File\SharedString)
node = ChildXMLNode(node)
While Not GetXMLNodeName(node) = "sheetData"
node = NextXMLNode(node)
Wend
node = ChildXMLNode(node)
While Not Val(GetXMLAttribute(node,"r")) = row
node = NextXMLNode(node)
If node = 0 : ProcedureReturn " " : EndIf
Wend
If ChildXMLNode(node,col)
node = ChildXMLNode(node,col)
Else
ProcedureReturn " "
EndIf
If GetXMLAttribute(node,"t") = "s"
If ChildXMLNode(node)
node = ChildXMLNode(node)
Else
ProcedureReturn " "
EndIf
node2 = ChildXMLNode(node2,Val(GetXMLNodeText(node))+1)
If node2
node2 = ChildXMLNode(node2)
SetXMLNodeText(node2,text$)
ProcedureReturn
Else
ProcedureReturn ""
EndIf
ElseIf GetXMLAttribute(node,"t") = ""
If ChildXMLNode(node)
node = ChildXMLNode(node)
SetXMLNodeText(node,text$)
ProcedureReturn
Else
ProcedureReturn " "
EndIf
ElseIf GetXMLAttribute(node,"t") = "e"
If ChildXMLNode(node)
node = ChildXMLNode(node)
While Not GetXMLNodeName(node) = "v"
node = NextXMLNode(node)
If node = 0 : ProcedureReturn "" : EndIf
Wend
SetXMLNodeText(node,text$)
ProcedureReturn
Else
ProcedureReturn " "
EndIf
EndIf
EndProcedure
EndModule
;=================================================Example of use=================================================
xml = Excel::Open("Classeur1.xlsx")
ligne=1
While Excel::ReadCell(xml,1,ligne)<>" "
ligne+1
Wend
;ligne= prochaine ligne vierge
Debug Excel::WriteCell(xml,1,ligne,"test")
Excel::Close(xml)