Suite à une demande de "moulinette" pour faire des recherches croisés entre plusieurs fichier Excel qui m'a été fait, j'en ai profité pour faire les choses à peut près proprement.
Si ça peut servire à quelqu'un...
Code : Tout sélectionner
EnableExplicit
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,Row,Col,Sheet=0) ; Returns cell value, " " if empty, "" if it failed.
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,Row,Col,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
EndModule
;=================================================Example of use=================================================
; Define xml, row, loop
; xml = Excel::Open(OpenFileRequester("",GetCurrentDirectory(),"Excel | *.xlsx",0))
; Debug Excel::GetSheetName(xml)
; row = Excel::CountRow(xml)
; Debug Excel::CountCol(xml)
; For loop = 1 To row
; Debug Excel::ReadCell(xml,loop,1)
; Debug Excel::ReadCell(xml,loop,2)
; Debug Excel::ReadCell(xml,loop,7)
; Next
; Excel::Close(xml)
;================================================================================================================
Edit : certaines valeurs de cellules sont par fois dans le "sheet.xml" et rajout de la lecture du résultat des formules.
Edit : MAJ, il y avais un petit but sur la fonction CountRow