Hello,
I've created this new class to read Excel .xlsx files without having Excel installed.
In my initial tests, it's working well. I'm leaving it here for you to try (for those interested in reading files without having Office or Excel installed on their computer).
At the moment, it can only read spreadsheets, but I'd like to be able to make changes to the spreadsheet as well; that would be a pending task.
I hope you can test it and, if there are any improvements, please let me know what they are so I can improve this class.
Best regards.
Carlos Sincuir
I've created this new class to read Excel .xlsx files without having Excel installed.
In my initial tests, it's working well. I'm leaving it here for you to try (for those interested in reading files without having Office or Excel installed on their computer).
/*
* Clase TExcelXlsxReader v1.0 22/09/2025
*
* Esta Clase lee archivos Excel .xlsx sin tener el Excel instalado en la computadora
*
* Autor: Carlos Sincuir
*
*/
#include "FiveWin.ch"
//-------------------------------------------------------------------------------------------------------------
FUNCTION Main()
LOCAL oXls, nI, nJ, nRow, nCol, xValue, cLine
LOCAL aSheetNames, cMask, aDocs, cFile
cMask := "EXCEL 2007- (*.xlsx)|*.xlsx|"
aDocs := aGetFiles(cMask, "Elija la hoja electronica que contiene desea leer", 1)
If Empty(Len(aDocs))
Return .F.
EndIf
cFile := aDocs[1]
If !File(cFile)
MsgAlert("Archivo no encontrado: " + cFile, "Error")
Return .F.
EndIf
oXls := TExcelXlsxReader():New(cFile)
IF !oXls:lSuccess
? "Error:", oXls:cLog
RETURN .F.
ENDIF
//? "Éxito:", oXls:cLog
// Mostrar nombres de hojas
aSheetNames := oXls:GetSheetNames()
//? "Hojas encontradas:", Len(aSheetNames)
/*
FOR nI := 1 TO Len(aSheetNames)
? " Hoja", nI, ":", aSheetNames[nI]
? " Filas:", oXls:GetMaxRow(nI)
? " Columnas:", oXls:GetMaxCol(nI)
NEXT nI
*/
? oXls:Get(1,12,1,"C")
? oXls:Get(1,12,7,"C")
? oXls:Get(1,18,15,"N")
? oXls:GetCellValue( 1, "O20" )
oXls:Close()
RETURN .T.
//------------------------------------------------------------------------------
CLASS TExcelXlsxReader
EXPORTED:
DATA cFile AS CHARACTER INIT ""
DATA cTempDir AS CHARACTER INIT ""
DATA aSharedStrings AS ARRAY INIT {}
DATA aSheetNames AS ARRAY INIT {}
DATA aSheets AS ARRAY INIT {}
DATA lSuccess AS LOGICAL INIT .T.
DATA cLog AS CHARACTER INIT ""
DATA hWorkbook AS HASH INIT NIL
DATA hSharedStrings AS HASH INIT NIL
METHOD New(cFile) CONSTRUCTOR
METHOD Get(nSheet, nRow, nCol, cType)
METHOD GetCellValue(nSheet, cCellRef)
METHOD GetSheetNames()
METHOD GetMaxRow(nSheet)
METHOD GetMaxCol(nSheet)
METHOD Close()
PROTECTED:
METHOD LoadSharedStrings()
METHOD LoadWorkbook()
METHOD LoadSheetData(nSheet)
METHOD ColFromRef(cRef)
METHOD RowFromRef(cRef)
METHOD ProcessSharedStrings(hXml)
METHOD ProcessWorkbook(hXml)
METHOD ProcessSheetData(hXml, nSheet)
METHOD GetSharedString(nIndex)
METHOD ValidateParams(cParam, nParam)
METHOD CheckFileExists(cFilePath)
METHOD CleanupTemp()
METHOD CreateTempDirs()
METHOD ProcessCellValue(cValue, cType)
ENDCLASS
//------------------------------------------------------------------------------
METHOD New(cFile) CLASS TExcelXlsxReader
LOCAL oError, nI
::cLog := ""
IF !::ValidateParams(cFile, 1)
::lSuccess := .F.
::cLog := 'Parámetros inválidos: Archivo requerido'
RETURN Self
ENDIF
::CreateTempDirs()
::cFile := cFile
::cTempDir := cFilePath(GetModuleFileName(GetInstance())) + "tmpxls\"
TRY
// Crear directorios temporales
// Verificar que el archivo existe
IF !File(::cFile)
Throw("Archivo no encontrado: " + ::cFile)
ENDIF
// Descomprimir archivo .xlsx
IF !HB_UNZIPFILE(::cFile, {|| .T.}, .T., NIL, ::cTempDir, NIL)
Throw("Error descomprimiendo archivo .xlsx")
ENDIF
// Verificar archivos clave
IF !::CheckFileExists(::cTempDir + "xl\workbook.xml")
Throw("Archivo workbook.xml no encontrado en .xlsx")
ENDIF
// Cargar datos
::LoadSharedStrings()
::LoadWorkbook()
// Inicializar array de hojas
::aSheets := Array(Len(::aSheetNames))
FOR nI := 1 TO Len(::aSheetNames)
::aSheets[nI] := {}
::LoadSheetData(nI)
NEXT nI
::cLog := 'Archivo .xlsx cargado correctamente: ' + AllTrim(Str(Len(::aSheetNames))) + ' hojas'
CATCH oError
::lSuccess := .F.
::cLog := 'Error cargando .xlsx: ' //+ oError:Description
::CleanupTemp()
END
RETURN Self
//------------------------------------------------------------------------------
METHOD CreateTempDirs() CLASS TExcelXlsxReader
LOCAL aDirs := {;
"tmpxls",;
"tmpxls\_rels",;
"tmpxls\docProps",;
"tmpxls\xl",;
"tmpxls\xl\_rels",;
"tmpxls\xl\theme",;
"tmpxls\xl\worksheets";
}
LOCAL cDir, nI
FOR nI := 1 TO Len(aDirs)
cDir := cFilePath(GetModuleFileName(GetInstance())) + aDirs[nI]
IF !lIsDir(cDir)
lMkDir(cDir)
ENDIF
NEXT nI
RETURN NIL
//------------------------------------------------------------------------------
METHOD LoadSharedStrings() CLASS TExcelXlsxReader
LOCAL cFile := ::cTempDir + "xl\sharedStrings.xml"
LOCAL hXml, oError
// SharedStrings es opcional en algunos archivos Excel
IF !File(cFile)
::aSharedStrings := {}
RETURN Self
ENDIF
TRY
hXml := XMLToHash(cFile)
IF hXml != NIL
::hSharedStrings := hXml
::ProcessSharedStrings(hXml)
ENDIF
CATCH oError
::lSuccess := .F.
::cLog := 'Error cargando SharedStrings: ' + oError:Description
END
RETURN Self
//------------------------------------------------------------------------------
METHOD ProcessSharedStrings(hXml) CLASS TExcelXlsxReader
LOCAL aSi, oSi, oT, nI
::aSharedStrings := {}
// Buscar estructura: sst -> si -> t
IF hb_HHasKey(hXml, "sst") .AND. hb_HHasKey(hXml["sst"], "si")
aSi := hXml["sst"]["si"]
// Si solo hay un elemento, convertir a array
IF !HB_ISARRAY(aSi)
aSi := {aSi}
ENDIF
FOR nI := 1 TO Len(aSi)
oSi := aSi[nI]
IF HB_ISHASH(oSi) .AND. hb_HHasKey(oSi, "t")
oT := oSi["t"]
IF HB_ISHASH(oT) .AND. hb_HHasKey(oT, "value")
AAdd(::aSharedStrings, oT["value"])
ELSEIF HB_ISSTRING(oT)
AAdd(::aSharedStrings, oT)
ELSE
AAdd(::aSharedStrings, "")
ENDIF
ELSE
AAdd(::aSharedStrings, "")
ENDIF
NEXT nI
ENDIF
RETURN NIL
//------------------------------------------------------------------------------
METHOD LoadWorkbook() CLASS TExcelXlsxReader
LOCAL cFile := ::cTempDir + "xl\workbook.xml"
LOCAL hXml, oError
TRY
hXml := XMLToHash(cFile)
IF hXml != NIL
::hWorkbook := hXml
::ProcessWorkbook(hXml)
ENDIF
CATCH oError
::lSuccess := .F.
::cLog := 'Error cargando Workbook: ' + oError:Description
END
RETURN Self
//------------------------------------------------------------------------------
METHOD ProcessWorkbook(hXml) CLASS TExcelXlsxReader
LOCAL oSheets, aSheet, oSheet, nI, cName, cSheetId
::aSheetNames := {}
// Buscar estructura: workbook -> sheets -> sheet
IF hb_HHasKey(hXml, "workbook") .AND. hb_HHasKey(hXml["workbook"], "sheets")
oSheets := hXml["workbook"]["sheets"]
IF hb_HHasKey(oSheets, "sheet")
aSheet := oSheets["sheet"]
// Si solo hay una hoja, convertir a array
IF !HB_ISARRAY(aSheet)
aSheet := {aSheet}
ENDIF
FOR nI := 1 TO Len(aSheet)
oSheet := aSheet[nI]
IF HB_ISHASH(oSheet) .AND. hb_HHasKey(oSheet, "attributes")
cName := ""
cSheetId := ""
IF hb_HHasKey(oSheet["attributes"], "name")
cName := oSheet["attributes"]["name"]
ENDIF
IF hb_HHasKey(oSheet["attributes"], "sheetId")
cSheetId := oSheet["attributes"]["sheetId"]
ENDIF
// Verificar que existe el archivo de la hoja
IF !Empty(cSheetId) .AND. ;
::CheckFileExists(::cTempDir + "xl\worksheets\sheet" + cSheetId + ".xml")
AAdd(::aSheetNames, IF(Empty(cName), "Sheet" + cSheetId, cName))
ENDIF
ENDIF
NEXT nI
ENDIF
ENDIF
RETURN NIL
//------------------------------------------------------------------------------
METHOD LoadSheetData(nSheet) CLASS TExcelXlsxReader
LOCAL cFile := ::cTempDir + "xl\worksheets\sheet" + AllTrim(Str(nSheet)) + ".xml"
LOCAL hXml, oError
IF !::CheckFileExists(cFile)
::lSuccess := .F.
::cLog := 'Archivo de hoja no encontrado: sheet' + AllTrim(Str(nSheet)) + '.xml'
RETURN Self
ENDIF
TRY
hXml := XMLToHash(cFile)
IF hXml != NIL
::ProcessSheetData(hXml, nSheet)
ENDIF
CATCH oError
::lSuccess := .F.
::cLog := 'Error cargando datos de hoja ' + AllTrim(Str(nSheet)) + ': ' + oError:Description
END
RETURN Self
//------------------------------------------------------------------------------
METHOD ProcessSheetData(hXml, nSheet) CLASS TExcelXlsxReader
LOCAL oSheetData, aRows, oRow, aCells, oCell, nI, nJ
LOCAL nRow, nCol, cRef, cType, cValue, xValue
LOCAL aRowData
::aSheets[nSheet] := {}
// Buscar estructura: worksheet -> sheetData -> row -> c
IF hb_HHasKey(hXml, "worksheet") .AND. hb_HHasKey(hXml["worksheet"], "sheetData")
oSheetData := hXml["worksheet"]["sheetData"]
IF hb_HHasKey(oSheetData, "row")
aRows := oSheetData["row"]
// Si solo hay una fila, convertir a array
IF !HB_ISARRAY(aRows)
aRows := {aRows}
ENDIF
FOR nI := 1 TO Len(aRows)
oRow := aRows[nI]
// Obtener número de fila
nRow := 1
IF HB_ISHASH(oRow) .AND. hb_HHasKey(oRow, "attributes") .AND. ;
hb_HHasKey(oRow["attributes"], "r")
nRow := Val(oRow["attributes"]["r"])
ENDIF
// Inicializar fila si no existe
DO WHILE Len(::aSheets[nSheet]) < nRow
AAdd(::aSheets[nSheet], {})
ENDDO
// Procesar celdas de la fila
IF hb_HHasKey(oRow, "c")
aCells := oRow["c"]
// Si solo hay una celda, convertir a array
IF !HB_ISARRAY(aCells)
aCells := {aCells}
ENDIF
FOR nJ := 1 TO Len(aCells)
oCell := aCells[nJ]
IF HB_ISHASH(oCell)
// Obtener referencia de celda (ej: "A1", "B2")
cRef := ""
cType := ""
cValue := ""
IF hb_HHasKey(oCell, "attributes")
IF hb_HHasKey(oCell["attributes"], "r")
cRef := oCell["attributes"]["r"]
ENDIF
IF hb_HHasKey(oCell["attributes"], "t")
cType := oCell["attributes"]["t"]
ENDIF
ENDIF
// Obtener valor de la celda
IF hb_HHasKey(oCell, "v")
IF HB_ISHASH(oCell["v"]) .AND. hb_HHasKey(oCell["v"], "value")
cValue := oCell["v"]["value"]
ELSEIF HB_ISSTRING(oCell["v"])
cValue := oCell["v"]
ENDIF
ENDIF
// Procesar valor según tipo
xValue := ::ProcessCellValue(cValue, cType)
// Obtener columna desde referencia
nCol := ::ColFromRef(cRef)
// Expandir array de columnas si es necesario
DO WHILE Len(::aSheets[nSheet][nRow]) < nCol
AAdd(::aSheets[nSheet][nRow], "")
ENDDO
// Asignar valor
::aSheets[nSheet][nRow][nCol] := xValue
ENDIF
NEXT nJ
ENDIF
NEXT nI
ENDIF
ENDIF
RETURN NIL
//------------------------------------------------------------------------------
METHOD ProcessCellValue(cValue, cType) CLASS TExcelXlsxReader
LOCAL xResult := ""
DO CASE
CASE cType == "s" // String compartido
xResult := ::GetSharedString(Val(cValue))
CASE cType == "n" .OR. Empty(cType) // Número
xResult := Val(cValue)
CASE cType == "b" // Boolean
xResult := (cValue == "1")
OTHERWISE // Texto inline
xResult := cValue
ENDCASE
RETURN xResult
//------------------------------------------------------------------------------
METHOD GetSharedString(nIndex) CLASS TExcelXlsxReader
// Los Ãndices en XML empiezan en 0, en Harbour en 1
nIndex := nIndex + 1
IF nIndex >= 1 .AND. nIndex <= Len(::aSharedStrings)
RETURN ::aSharedStrings[nIndex]
ENDIF
RETURN ""
//------------------------------------------------------------------------------
METHOD ColFromRef(cRef) CLASS TExcelXlsxReader
LOCAL cCol := "", nI, nCol := 0
// Extraer parte alfabética (columna) de la referencia
FOR nI := 1 TO Len(cRef)
IF IsAlpha(SubStr(cRef, nI, 1))
cCol += SubStr(cRef, nI, 1)
ELSE
EXIT
ENDIF
NEXT nI
// Convertir letras a número (A=1, B=2, ... Z=26, AA=27, etc.)
FOR nI := 1 TO Len(cCol)
nCol := nCol * 26 + (Asc(Upper(SubStr(cCol, nI, 1))) - Asc("A") + 1)
NEXT nI
RETURN nCol
//------------------------------------------------------------------------------
METHOD RowFromRef(cRef) CLASS TExcelXlsxReader
LOCAL cRow := "", nI
// Extraer parte numérica (fila) de la referencia
FOR nI := 1 TO Len(cRef)
IF IsDigit(SubStr(cRef, nI, 1))
cRow += SubStr(cRef, nI, 1)
ENDIF
NEXT nI
RETURN Val(cRow)
//------------------------------------------------------------------------------
METHOD Get(nSheet, nRow, nCol, cType) CLASS TExcelXlsxReader
LOCAL xValue := ""
IF nSheet < 1 .OR. nSheet > Len(::aSheets) .OR. ;
nRow < 1 .OR. nCol < 1
::lSuccess := .F.
::cLog := 'Parámetros inválidos: Hoja/Fila/Columna fuera de rango'
RETURN xValue
ENDIF
// Verificar que existe la fila
IF nRow <= Len(::aSheets[nSheet]) .AND. ;
nCol <= Len(::aSheets[nSheet][nRow])
xValue := ::aSheets[nSheet][nRow][nCol]
ENDIF
// Convertir tipo si se solicita
IF cType != NIL
DO CASE
CASE cType == "C" // Character
If ValType(xValue) == "N"
xValue := AlLTrim(Str(xValue))
ElseIf ValType(xValue) == "L"
xValue := IIF(xValue, "T", "F")
ElseIf ValType(xValue) == "D"
xValue := Dtoc( xValue )
End
CASE cType == "N" // Numeric
If ValType(xValue) == "C"
xValue := Val(xValue)
ElseIf ValType(xValue) == "L"
xValue := IIF(xValue, 1, 0)
EndIf
CASE cType == "L" // Logical
xValue := IF(ValType(xValue) == "C", (Upper(xValue) $ "T,TRUE,S,SI,Y,YES,1"), ;
IF(ValType(xValue) == "N", (xValue != 0), xValue))
ENDCASE
ENDIF
RETURN xValue
//------------------------------------------------------------------------------
METHOD GetCellValue(nSheet, cCellRef) CLASS TExcelXlsxReader
LOCAL nRow := ::RowFromRef(cCellRef)
LOCAL nCol := ::ColFromRef(cCellRef)
RETURN ::Get(nSheet, nRow, nCol)
//------------------------------------------------------------------------------
METHOD GetSheetNames() CLASS TExcelXlsxReader
RETURN AClone(::aSheetNames)
//------------------------------------------------------------------------------
METHOD GetMaxRow(nSheet) CLASS TExcelXlsxReader
IF nSheet >= 1 .AND. nSheet <= Len(::aSheets)
RETURN Len(::aSheets[nSheet])
ENDIF
RETURN 0
//------------------------------------------------------------------------------
METHOD GetMaxCol(nSheet) CLASS TExcelXlsxReader
LOCAL nMaxCol := 0, nI
IF nSheet >= 1 .AND. nSheet <= Len(::aSheets)
FOR nI := 1 TO Len(::aSheets[nSheet])
nMaxCol := Max(nMaxCol, Len(::aSheets[nSheet][nI]))
NEXT nI
ENDIF
RETURN nMaxCol
//------------------------------------------------------------------------------
METHOD ValidateParams(cParam, nParam) CLASS TExcelXlsxReader
RETURN !Empty(cParam) .AND. nParam > 0
//------------------------------------------------------------------------------
METHOD CheckFileExists(cFilePath) CLASS TExcelXlsxReader
IF !File(cFilePath)
RETURN .F.
ENDIF
RETURN .T.
//------------------------------------------------------------------------------
METHOD CleanupTemp() CLASS TExcelXlsxReader
IF !Empty(::cTempDir) .AND. lIsDir(::cTempDir)
hb_DirRemoveAll(::cTempDir)
ENDIF
RETURN NIL
//------------------------------------------------------------------------------
METHOD Close() CLASS TExcelXlsxReader
::CleanupTemp()
::aSharedStrings := {}
::aSheetNames := {}
::aSheets := {}
::hWorkbook := hash()
::hSharedStrings := hash()
RETURN NIL
//------------------------------------------------------------------------------
// Función helper para convertir número de columna a letra
FUNCTION Col2Letter(nCol)
LOCAL cResult := "", nQuotient
DO WHILE nCol > 0
nQuotient := Int((nCol - 1) / 26)
cResult := Chr(65 + ((nCol - 1) % 26)) + cResult
nCol := nQuotient
ENDDO
RETURN cResult
//------------------------------------------------------------------------------
FUNCTION XMLToHash(cXMLFile)
LOCAL oXmlDoc, oRoot, hResult
TRY
oXmlDoc := TXmlDocument():New(cXMLFile)
CATCH
? "Error: No se puede leer el archivo XML: " + cXMLFile
RETURN NIL
END
oRoot := oXmlDoc:oRoot
hResult := hash()
IF oRoot:oChild == NIL
RETURN hResult
ENDIF
ProcessNode(oRoot:oChild, @hResult)
RETURN hResult
//------------------------------------------------------------------------------
STATIC FUNCTION ProcessNode(oNode, hContainer)
LOCAL hNodeData, xNodeValue
DO WHILE oNode != NIL
// Inicializar el valor del nodo
xNodeValue := IF(!Empty(oNode:cData), oNode:cData, "")
// Decidir si necesitamos una estructura hash o solo el valor
IF !Empty(oNode:aAttributes) .OR. oNode:oChild != NIL
// Si hay atributos o hijos, crear estructura hash
hNodeData := hash()
hb_HSet(hNodeData, "value", xNodeValue)
// Procesar atributos si existen
IF !Empty(oNode:aAttributes)
hb_HSet(hNodeData, "attributes", hash())
HEval(oNode:aAttributes, {|k,v| hb_HSet(hNodeData["attributes"], k, v)})
ENDIF
// Procesar nodos hijos recursivamente
IF oNode:oChild != NIL
ProcessNode(oNode:oChild, @hNodeData)
ENDIF
xNodeValue := hNodeData
ENDIF
// Agregar el nodo al contenedor
IF hb_HHasKey(hContainer, oNode:cName)
// Si ya existe una entrada para este nombre de nodo, convertir a array
IF !HB_ISARRAY(hContainer[oNode:cName])
hContainer[oNode:cName] := {hContainer[oNode:cName]}
ENDIF
AAdd(hContainer[oNode:cName], xNodeValue)
ELSE
hb_HSet(hContainer, oNode:cName, xNodeValue)
ENDIF
oNode := oNode:oNext
ENDDO
RETURN NILI hope you can test it and, if there are any improvements, please let me know what they are so I can improve this class.
Best regards.
Carlos Sincuir
