Read and modify openoffice / libreoffice spreadsheets

[Kulrom]

Hi #PB_All
I made a module to work with .ods files.
At the moment you can:
- read data from cells
- write data to cells with style support
- add and delete rows
- save file

It is forbidden:
- Create .ods files
- Create styles

I very often have to process information in thousands of ".ods" files of the same type. So I had to make this module.
I'm posting it here. Maybe it will be useful for someone too.

Code: Select all

; ============================
; =        ezOds.pbi         =
; ============================
; Module For Read And modify spreadsheets open/libre office "*.ods"
; PB 6.01 LTS
; Author - Kulrom (03/2023)
; Last Update: 15/10/2024

; v 0.4.0 fixed ODS::SaveOdsBook()  - create .bak file if rewrite .ods file.  07/09/2023
; v 0.4.1 Added ODS::getSheetName() - return name of sheet by sheet id
;		  Added ODS::getSheetID() - return sheetId by name
;		  Added ODS::setSheetName() - set sheet name						 11/09/2023
; v 0.4.2 fixed ODS::getCellValue() - fixed calculation of cell address in a row with covered cells  29/09/2023
;									fixed calculation of cell adress in a row if repeated values
;		  fixed ODS::setCellValue() - ----//--------//-------
;		  fixed ODS::SaveOdsBook() - return 0 if file is openned in openOffice.
; v 0.4.3 fixed ODS::getCellValue() - fixed calculation of cell address in a row with merged and repeated cells 25/02/2024
; v 0.4.4 fixed ODS::SaveOdsBook()  - fixed for PB 6.11    06/08/2024
; v 0.4.5 fixed ODS::GetCellValue()  - fixed returned result if link in cell 11/10/2024
; v 0.4.6 fixed ODS::GetCellValue()  - fixed returned result if type is "percentage"
;		  added constant #percentage  for write values in percents  14/10/2024
; v 0.4.7 fixed ODS::GetCellValue() - fixed returned results if multirows and multiformated text in cell 15/10/2024
;         modify ODS::SetCellValue() - added the ability to write multi-line text
; v 0.5.0 The internal mechanism for allocating memory has been changed. It is now a list
;         Added ODS::freeAndNull() -  The macro clears the memory and resets the variable.
; v 0.5.1 Fixed ODS::SaveOdsBook() - added support PB version < 611

DeclareModule Ods
	#version$ = "0.5.1"
	; type constants for ODS cells
	#float = "float"
	#string = "string"
	#date = "date"
	#curency = "curency"
	#percentage = "percentage"
	
	Declare freeOdsBook(book)
	Declare openOdsBook(filename$)
	Declare saveOdsBook(book, filename$="")
	Declare.s getCellValue(book, sheet, row, col)
	Declare.s getCellType(book, sheet, row, col)
	Declare.s getCellStyle(book, sheet, row, col)
	Declare setCellValue(book, sheet, row, col, value$, type$, style$="")
	Declare DeleteRows(book, sheet, row_id, n_rows=1)
	Declare AddRows(book, sheet, row_id, n_rows=1)
	Declare getNRows(book, sheet)
	Declare.s getSheetName(book, sheet_id)
	Declare getSheetId(book, sheetName$)
	Declare setSheetName(book, sheet_id, new_sheet_name$)
	
	Macro freeAndNull(book)
		Ods::freeOdsBook(book)
		book = 0
	EndMacro
EndDeclareModule

Module Ods
EnableExplicit

;--------------Structures------------

Structure Sheet
	name.s						; название листа
	n_rows.i					; row number
	Array row_cash.i(65535)		; массив ссылок на узлы со строками array of pointers to nodes
	cash_is_ready.i				; Флаг актуальности кэша. При каждой операции записи кэш становится не актуальным
EndStructure

Structure Book
	filename$
	xml_id.i
	xml_node.i
	n_sheets.i
	Array sheet.Sheet(0)
EndStructure

;--------------Constants-------------------------------------------
#is_Save_xml =	#False  ; Константа указывает нужно ли сохранять xml на компьютер. Нужно для изучения структуры xml
#is_Format_xml = #False

;--------------Globals--------------------------------------------
Global NewList __BOOKS__.Book()

UseZipPacker()

;------------Private_methods--------------------------------------

CompilerIf #is_Save_xml
	Procedure _save_xml(xml.i, fname$)
		If #is_Format_xml : FormatXML(xml, #PB_XML_ReFormat|#PB_XML_ReIndent, 2) : EndIf
		SaveXML(xml, fname$)
	EndProcedure
CompilerEndIf

; Процедура для разбивки строки на список строк
Procedure _split_str_to_list(String.s, List StringList.s(), Separator.s = " ")
	If String.s = ""
		ProcedureReturn #False
	EndIf
	
	Protected S.String, *S.Integer = @S
	Protected.i p, slen
	slen = Len(Separator)
	ClearList(StringList())
	
	*S\i = @String
	Repeat
		AddElement(StringList())
		p = FindString(S\s, Separator)
		StringList() = PeekS(*S\i, p - 1)
		*S\i + (p + slen - 1) << #PB_Compiler_Unicode
	Until p = 0
	*S\i = 0
	ProcedureReturn #True
EndProcedure

Procedure _fill_row_cash(*sheet.Sheet, *sheet_node)
	Protected row = 0
	
	; если кэш не готов
	If Not *sheet\cash_is_ready
		; считаем кол-во строк
		Protected *row_node = XMLNodeFromPath(*sheet_node, "table:table-row")
		If *row_node
			While *row_node
				If ArraySize(*sheet\row_cash()) < row
					ReDim *sheet\row_cash(row)
				EndIf
				Protected repeated_rows = Val(GetXMLAttribute(*row_node, "table:number-rows-repeated"))
				Protected node_name$ = GetXMLNodeName(*row_node)
				If node_name$ = "table:table-row"
					*sheet\row_cash(row) = *row_node
					row + 1
				; Если строки сгруппированы
				ElseIf node_name$ = "table:table-row-group"
					Protected *row_node_1 = ChildXMLNode(*row_node)
					While *row_node_1
						Protected repeated_rows_1 = Val(GetXMLAttribute(*row_node_1, "table:number-rows-repeated"))
						node_name$ = GetXMLNodeName(*row_node_1)
						If node_name$ = "table:table-row"
							*sheet\row_cash(row) = *row_node_1
							row + 1
						EndIf
						If repeated_rows_1
							row + repeated_rows_1 - 1
						EndIf
						*row_node_1 = NextXMLNode(*row_node_1)
					Wend
				EndIf
				
				If repeated_rows
					row + repeated_rows - 1
				EndIf
				
				*row_node = NextXMLNode(*row_node)
			Wend
			*sheet\n_rows = row
			*sheet\cash_is_ready = #True
		EndIf
	EndIf
EndProcedure

; Функция возвращает id xml объекта content.xml из ods файла filename$
Procedure _get_content_xml(filename$)
	Protected *buffer
	Protected ods_file, buffer_size, xml_id
	
	If FileSize(filename$) >= 0
		ods_file = OpenPack(#PB_Any, filename$)
		If ods_file
			If ExaminePack(ods_file)
				
				While NextPackEntry(ods_file)
					If PackEntryName(ods_file) = "content.xml"
						buffer_size = PackEntrySize(ods_file)
						*buffer = AllocateMemory(buffer_size)
						UncompressPackMemory(ods_file, *buffer, buffer_size)
						xml_id = CatchXML(#PB_Any, *buffer, buffer_size)
						FreeMemory(*buffer)
						ClosePack(ods_file)
						ProcedureReturn xml_id
					EndIf
				Wend
				
			EndIf
			ClosePack(ods_file)
		EndIf
	EndIf
EndProcedure


Procedure _get_cell_node_in_row(*row_node, col_id)
	Protected col = 0, repeated_cols = 0
	Protected *cell_node = XMLNodeFromPath(*row_node, "*")
	Protected node_name$, node_value$
	
	While *cell_node
		repeated_cols = 0
		repeated_cols = Val(GetXMLAttribute(*cell_node, "table:number-columns-repeated"))
				
		If col = col_id
			ProcedureReturn *cell_node
		Else
			If repeated_cols
				col + repeated_cols
			Else
				col + 1
			EndIf
		EndIf
		
		If col_id < col
			If repeated_cols
				ProcedureReturn *cell_node
			Else
				ProcedureReturn 0
			EndIf
		EndIf
		
		*cell_node = NextXMLNode(*cell_node)
	Wend	
EndProcedure

; Функция возвращает указатель на узел строки
Procedure _get_row_node(*sheet.Sheet, *sheet_node, row_id)
	If Not *sheet\cash_is_ready
		_fill_row_cash(*sheet, *sheet_node)
	EndIf
	
	ProcedureReturn *sheet\row_cash(row_id)
EndProcedure

; Функция возвращает ссылку на узел листа
Procedure _get_sheet_node(*book_node, sheet_id)
	Protected sheet = 0
	Protected *sheet_node = XMLNodeFromPath(*book_node, "table:table")
	While *sheet_node
		If GetXMLNodeName(*sheet_node) = "table:table"
			If sheet_id = sheet
				ProcedureReturn *sheet_node
			EndIf
			sheet + 1
			*sheet_node = NextXMLNode(*sheet_node)
		EndIf
	Wend
EndProcedure

Procedure _get_cell_node(*book.Book, sheet, row, col)
	If *book
		Protected *sheet.Sheet = *book\sheet(sheet)
		Protected *sheet_node = _get_sheet_node(*book\xml_node, Sheet)
		If *sheet_node
			Protected *row_node = _get_row_node(*sheet, *sheet_node, row)
			If *row_node
				Protected *cell_node = _get_cell_node_in_row(*row_node, col)
				If *cell_node
					ProcedureReturn *cell_node
				EndIf
			EndIf
		EndIf
	EndIf
EndProcedure

; процедура возвращает текст из указанного узла
Procedure.s _get_string_value_from_cell(*cell_node)
	Protected res$ = ""
	
	Protected *node = XMLNodeFromPath(*cell_node, "text:p")
	While *node
		res$ + GetXMLNodeText(*node)
		
		Protected *child_node = ChildXMLNode(*node)
		If *child_node		
			While *child_node
				res$ + GetXMLNodeText(*child_node)
				*child_node = NextXMLNode(*child_node)
			Wend
		EndIf
		*node = NextXMLNode(*node)
		If *node : res$ + ~"\n" : EndIf
	Wend
	ProcedureReturn res$	
EndProcedure

Procedure _set_string_value_to_cell(*cell_node, value$)
	
	; стираем все данные	
	Protected *txt_node = XMLNodeFromPath(*cell_node, "text:p")
	While *txt_node		
		DeleteXMLNode(*txt_node)
		*txt_node = NextXMLNode(*txt_node)
	Wend
	
	; разбиваем строку на подстроки по \n
	Protected NewList lst$()
	_split_str_to_list(value$, lst$(), ~"\n")
	
	; записываем данные в ячейку
	ForEach lst$()
		*txt_node = CreateXMLNode(*cell_node, "text:p", -1)
		SetXMLNodeText(*txt_node, lst$())
	Next	
EndProcedure

; функия возвращает количество столбцов в строке и заполняет кэш
Procedure _fill_cell_cash(*row_node, Array cell_cash.i(1))
	Protected *cell_node = XMLNodeFromPath(*row_node, "*")
	Protected col
	Protected node_name$
	While *cell_node
		Protected repeated_cells = Val(GetXMLAttribute(*cell_node, "table:number-columns-repeated"))
		node_name$ = GetXMLNodeName(*cell_node)
		If node_name$ = "table:table-cell" Or node_name$ = "table:covered-table-cell"
			cell_cash(col) = *cell_node
			If repeated_cells
				col + repeated_cells
			Else
				col + 1
			EndIf
		EndIf
		*cell_node = NextXMLNode(*cell_node)
	Wend
	ProcedureReturn col
EndProcedure


; /////////////////////////////////////////////////////////////
; Процедура собирает новый файл ods
Procedure _repack_zip(file_in.s, file_out.s, *content_buffer, content_size)
	Protected *buffer, res = 1
	Protected buffer_size
	Protected entry_type
	Protected entry_name$
	Protected temp_file$, base_file$
	
	; Проверяем не совпадают ли исходный и результирующий файл по названию
	; Если совпадает, то создаём временный файл
	If file_out = file_in
		temp_file$ = file_in + ".bak"
		If Not CopyFile(file_in, temp_file$)
			ProcedureReturn #False
		EndIf
		file_in = temp_file$
	EndIf
	
	Protected zip_in = OpenPack(#PB_Any, file_in)
	Protected zip_out = CreatePack(#PB_Any, file_out)
	
	If zip_in
		If zip_out
			If ExaminePack(zip_in)
				While NextPackEntry(zip_in)
					entry_name$ = PackEntryName(zip_in)
					entry_type = PackEntryType(zip_in)
					buffer_size = PackEntrySize(zip_in)
					
					If entry_name$ = "content.xml"
						AddPackMemory(zip_out, *content_buffer, content_size, "content.xml")
						Continue
					EndIf
					If buffer_size																; Если это файл с размером
						*buffer = AllocateMemory(buffer_size)
						UncompressPackMemory(zip_in, *buffer, buffer_size)
						AddPackMemory(zip_out, *buffer, buffer_size, entry_name$)
						FreeMemory(*buffer)
					Else
						; Если это просто каталог
						CompilerIf #PB_Compiler_Version < 611
							AddPackMemory(zip_out, 0, 0, entry_name$)
						CompilerElse
							AddPackDirectory(zip_out, entry_name$)		; fixed for PB 6.11
						CompilerEndIf
					EndIf
				Wend
			EndIf
			ClosePack(zip_out)
		Else
			res = #False
		EndIf
		ClosePack(zip_in)
	Else
		res = #False
	EndIf
	ProcedureReturn res
EndProcedure


; процедура создаёт узел ячейки
Procedure _modify_cell_xml_node(*cell_node, value$, type$, style$="")
	If Not *cell_node
		ProcedureReturn #False
	EndIf
	
	SetXMLAttribute(*cell_node, "office:value-type", type$)
	
	If type$ = #float
		SetXMLAttribute(*cell_node, "office:value", value$)
	ElseIf type$ = #percentage
		SetXMLAttribute(*cell_node, "office:value", value$)
	ElseIf type$ = #date
		SetXMLAttribute(*cell_node, "office:date-value", value$)
	ElseIf type$ = #curency
		SetXMLAttribute(*cell_node, "office:value", value$)
	ElseIf type$ = #string
		_set_string_value_to_cell(*cell_node, value$)
	EndIf
	
	If style$ <> ""
		SetXMLAttribute(*cell_node, "table:style-name", style$)
	EndIf
	
	ProcedureReturn *cell_node
EndProcedure

; процедура удаляет записи о строках из кэша и изменяет размер массива кэша
Procedure _delete_rows_from_cash(Array row_cash.i(1), row_id, n_rows=1)
	Protected row
	
	; сдвигаем элементы массива на место удалённых строк
	For row = row_id + n_rows To ArraySize(row_cash())
		row_cash(row - n_rows) = row_cash(row)
	Next
	
	; изменяем размер массива
	Protected old_size = ArraySize(row_cash())
	Protected new_size = old_size - n_rows
	ReDim row_cash(new_size)
EndProcedure

Procedure _add_rows_to_cash(Array row_cash.i(1), row_id, n_rows=1)
	Protected row, prev_size
	
	prev_size = ArraySize(row_cash())
	ReDim row_cash(prev_size + n_rows)
	
	; сдвигаем элементы массива
	If row_id = prev_size
		row_cash(prev_size + n_rows) = row_cash(row_id)
		row_cash(row_id) = 0
	Else
		For row = prev_size To row_id+1 Step -1
			row_cash(row + n_rows) = row_cash(row)
			row_cash(row) = 0
		Next
	EndIf
EndProcedure

Procedure _create_row_node(*previous_row_node, n_rows, cols_in_row, is_first_row = #False)
	Protected *new_rownode, *cell_node
	Protected *parent_node
	
	*parent_node = ParentXMLNode(*previous_row_node)
	*new_rownode = CreateXMLNode(*parent_node, "table:table-row", *previous_row_node)
	
	If is_first_row
		SetXMLAttribute(*new_rownode, "table:style-name", GetXMLAttribute(NextXMLNode(*previous_row_node), "table:style-name"))
	Else
		SetXMLAttribute(*new_rownode, "table:style-name", GetXMLAttribute(*previous_row_node, "table:style-name"))
	EndIf
	
	SetXMLAttribute(*new_rownode, "table:number-rows-repeated", Str(n_rows))
	*cell_node = CreateXMLNode(*new_rownode, "table:table-cell")
	SetXMLAttribute(*cell_node, "table:number-columns-repeated", Str(cols_in_row))
	ProcedureReturn  *new_rownode
EndProcedure


Procedure _get_n_cols(*sheet_node)
	Protected *column_node
	Protected n_cols, repeated_cols
	
	*column_node = ChildXMLNode(*sheet_node)
	While GetXMLNodeName(*column_node) = "table:table-column"
		repeated_cols = Val(GetXMLAttribute(*column_node, "table:number-columns-repeated"))
		If repeated_cols
			n_cols + repeated_cols
		Else
			n_cols + 1
		EndIf
		*column_node = NextXMLNode(*column_node)
	Wend
	ProcedureReturn n_cols
EndProcedure


;--------------Public_methods-----------------------------

;//////////////////////////////////////////////////////////////
; Конструктор нового документа
Procedure openOdsBook(filename$)
	Protected *root_node, *book_node, *sheet_node
	Protected *book.Book
	Protected xml_id, res, sheets
	
	xml_id = _get_content_xml(filename$)
	
	CompilerIf #is_Save_xml
		_save_xml(xml_id, "content.xml")
	CompilerEndIf
	
	If xml_id
		*root_node = RootXMLNode(xml_id)
		*book_node = XMLNodeFromPath(*root_node, "/office:document-content/office:body/office:spreadsheet")
		*sheet_node = ChildXMLNode(*book_node)
		AddElement(__BOOKS__())
		*book = @__BOOKS__()
		*book\xml_id = xml_id
		*book\xml_node = *book_node
		*book\filename$ = filename$
		; Вычисляем количество вкладок
		While *sheet_node
			If GetXMLNodeName(*sheet_node) = "table:table"
				ReDim *book\sheet(sheets)
				*book\sheet(sheets)\name = GetXMLAttribute(*sheet_node, "table:name")
				sheets + 1
			EndIf
			*sheet_node = NextXMLNode(*sheet_node)
		Wend
		
		*book\n_sheets = sheets
		
		ProcedureReturn *book
	EndIf
EndProcedure


Procedure saveOdsBook(*book.Book, filename$="")
	Protected res
	Protected path$, lock_fname$
	
	If *book
		
		If filename$ = ""
			filename$ = *book\filename$
		EndIf
		
		path$ = GetPathPart(filename$)
		lock_fname$ = path$ + ".~lock." + GetFilePart(filename$) + "#"
		
		If FileSize(lock_fname$) >= 0
			ProcedureReturn #False
		EndIf
		
		CompilerIf #is_Save_xml
			_save_xml(*book\xml_id, "content_out.xml")
		CompilerEndIf

		Protected bufer_size = ExportXMLSize(*book\xml_id)
		Protected *buffer = AllocateMemory(bufer_size)
		ExportXML(*book\xml_id, *buffer, bufer_size)
		If _repack_zip(*book\filename$, filename$, *buffer, bufer_size)
			res = #True
		EndIf
		FreeMemory(*buffer)
	EndIf
	ProcedureReturn res
EndProcedure


Procedure freeOdsBook(*book.Book)
	If *book
		FreeXML(*book\xml_id)
		Protected sheet, row
		For sheet = 0 To ArraySize(*book\sheet())
			FreeArray(*book\sheet(sheet)\row_cash())
		Next
		ClearStructure(*book, Book)
		ChangeCurrentElement(__BOOKS__(), *book)
		DeleteElement(__BOOKS__())
	EndIf
EndProcedure


Procedure.s getCellValue(*book.Book, sheet, row, col)
	Protected *cell_node = _get_cell_node(*book, sheet, row, col)
	If *cell_node
		Select GetXMLAttribute(*cell_node, "office:value-type")
			Case "float"
				ProcedureReturn GetXMLAttribute(*cell_node, "office:value")
			Case "percentage"
				ProcedureReturn GetXMLAttribute(*cell_node, "office:value")
			Case "currency"
				ProcedureReturn GetXMLAttribute(*cell_node, "office:value")
			Case "date"
				ProcedureReturn GetXMLAttribute(*cell_node, "office:date-value")
			Case "string"
				ProcedureReturn _get_string_value_from_cell(*cell_node)
		EndSelect
	Else
		ProcedureReturn ""
	EndIf
EndProcedure


Procedure.s getCellType(*book.Book, sheet, row, col)
	Protected *cell_node = _get_cell_node(*book, sheet, row, col)
	If *cell_node
		ProcedureReturn GetXMLAttribute(*cell_node, "office:value-type")
	EndIf
EndProcedure


Procedure.s getCellStyle(*book.Book, sheet, row, col)
	Protected *cell_node = _get_cell_node(*book, sheet, row, col)
	If *cell_node
		ProcedureReturn GetXMLAttribute(*cell_node, "table:style-name")
	EndIf
EndProcedure


Procedure getNRows(*book.Book, sheet)
	Protected *sheet.Sheet = *book\sheet(sheet)
	Protected *sheet_node = _get_sheet_node(*book\xml_node, sheet)
	_fill_row_cash(*sheet, *sheet_node)
	ProcedureReturn *sheet\n_rows
EndProcedure


; Процедура возвращает название листа по sheet id. Если такого листа нет, то возвращает ""
Procedure.s getSheetName(*book.Book, sheet_id)
	If Not *book
		ProcedureReturn ""
	EndIf
	If sheet_id >= *book\n_sheets
		ProcedureReturn ""
	EndIf
	ProcedureReturn *book\sheet(sheet_id)\name
EndProcedure


; Процедура возвращает sheet Id по имени листа. Если такого листа не существует, то возвращает -1
Procedure getSheetId(*book.Book, sheetName$)
	Protected i, res
	
	If *book
		For i = 0 To *book\n_sheets - 1
			If *book\sheet(i)\name = sheetName$
				ProcedureReturn i
			EndIf
		Next
	EndIf
	
	ProcedureReturn -1
EndProcedure


; процедура записывает данные в ячейку
;@param ods_id: номер объекта ods. Получается в результате работы процедуры openOdsBook
;@param sheet: номер листа
;@param row: номер строки
;@param col: номер столбца
;@param value$: значение ячейки
;@param type$: тип данных. может быть "float", "string", "date", "currency"
;@param style$: Стиль ячейки. Стиль ячейки можно получить функцией getSellStyle()
Procedure setCellValue(*book.Book, sheet, row, col, value$, type$, style$="")
	
	If Not *book
		ProcedureReturn #False
	EndIf
	
	If sheet > ArraySize(*book\sheet())
		Debug #PB_Compiler_Procedure + ": Перебор по листам"
		ProcedureReturn #False
	EndIf
	Protected repeated_rows
	Protected *row_node, *new_row_node, *prev_row_node
	
	; Заполняем кэш, если он ещё не заполнен
	Protected *sheet.Sheet = *book\sheet(sheet)
	If Not *sheet\cash_is_ready
		_fill_row_cash(*sheet, _get_sheet_node(*book\xml_node, sheet))
	EndIf
	
	;{ блок подготовки строк
	If row > ArraySize(*sheet\row_cash())
		Debug #PB_Compiler_Procedure + ": Line " + #PB_Compiler_Line + ": Перебор по строкам"
		ProcedureReturn #False
	EndIf
	*row_node = *sheet\row_cash(row)
	If *row_node
		; обрабатываем случай когда есть такой узел в кэше
		repeated_rows = Val(GetXMLAttribute(*row_node, "table:number-rows-repeated"))
		If repeated_rows
			; обрабатываем случай когда выбранная строка пустая и имеет повторы
			If repeated_rows > 1
				*new_row_node = CopyXMLNode(*row_node, ParentXMLNode(*row_node), *row_node) 			; создаём копию строки и помещаем её следующей
				SetXMLAttribute(*row_node, "table:number-rows-repeated", "")							; Убираем повторы в текущей строке
				SetXMLAttribute(*new_row_node, "table:number-rows-repeated", Str(repeated_rows-1))		; В следующей строке делаем повторы
				*sheet\row_cash(row + 1) = *new_row_node												; добавляем новую строку в кэш
			EndIf
		Else 
			; обрабатываем случай когда выбранная строка не имеет повторов
		EndIf
	Else
		; Обрабатываем случай, когда нет такой строки в кэше
		; находим ближайшую предыдущую строку в кэше копируем её во все пустые ячейки
		Protected i = row
		While Not *sheet\row_cash(i)
			i - 1
		Wend
		*prev_row_node = *sheet\row_cash(i)
		repeated_rows = Val(GetXMLAttribute(*prev_row_node, "table:number-rows-repeated"))
		SetXMLAttribute(*prev_row_node, "table:number-rows-repeated", "")
		*new_row_node = *prev_row_node
		i + 1
		While Not *sheet\row_cash(i)
			*new_row_node = CopyXMLNode(*prev_row_node, ParentXMLNode(*prev_row_node), *new_row_node)  ; копируем строку, заполняем кэш на этом участке
			*sheet\row_cash(i) = *new_row_node
			i + 1
			If i > *sheet\n_rows-1
				Debug #PB_Compiler_Procedure + ": Перебор по строкам"
				ProcedureReturn #False
			EndIf
		Wend
	EndIf
	;} Конец блока подготовки строк
	
	;{ Блок подготовки ячеек
	*row_node = *sheet\row_cash(row)
	Dim cell_cash.i(65535)
	Protected n_cols = _fill_cell_cash(*row_node, cell_cash())		; заполняем кэш ячеек строки и получаем кол-во ячеек
	
	Protected *next_cell_node, *prev_cel_node, *new_cell_node
	Protected *cell_node = cell_cash(col)
	If *cell_node
		; Если ячейка есть в кэше
		Protected repeated_cells = Val(GetXMLAttribute(*cell_node, "table:number-columns-repeated"))
		If repeated_cells > 1
			; Обрабатываем случай когда ячейка пустая и есть повторы
			SetXMLAttribute(*cell_node, "table:number-columns-repeated", "")
			*next_cell_node = CopyXMLNode(*cell_node, ParentXMLNode(*cell_node), *cell_node)
			SetXMLAttribute(*next_cell_node, "table:number-columns-repeated", Str(repeated_cells-1))
			cell_cash(col+1) = *next_cell_node
		Else
			; Обрабатываем случай когда ячейка не имеет повторов
		EndIf
	Else
		; Обрабатываем случай, когда нет ячейки в кэше
		; Находим ближайшую ячейку в кэше
		i = col
		While Not cell_cash(i)
			i - 1
		Wend
		*prev_cel_node = cell_cash(i)
		repeated_cells = Val(GetXMLAttribute(*prev_cel_node, "table:number-columns-repeated"))
		SetXMLAttribute(*prev_cel_node, "table:number-columns-repeated", "")
		*new_cell_node = *prev_cel_node
		i + 1
		While Not cell_cash(i) And i < n_cols
			*new_cell_node = CopyXMLNode(*prev_cel_node, ParentXMLNode(*prev_cel_node), *new_cell_node)
			cell_cash(i) = *new_cell_node
			i + 1
		Wend
	EndIf
	;} конец блока подготовки ячеек
	
	*cell_node = cell_cash(col)
	FreeArray(cell_cash())
	If *cell_node
		_modify_cell_xml_node(*cell_node, value$, type$, style$)
	Else
		Debug #PB_Compiler_Procedure + ": Перебор по столбцам"
		ProcedureReturn #False
	EndIf
	
	ProcedureReturn #True
EndProcedure


Procedure setSheetName(*book.Book, sheet_id, new_sheet_name$)
	Protected *sheet_node
	
	If sheet_id <= *book\n_sheets -1
		If CheckFilename(new_sheet_name$)  ; Проверка на допустимость имени
			If *book
				*sheet_node = _get_sheet_node(*book\xml_node, sheet_id)
				If *sheet_node
					SetXMLAttribute(*sheet_node, "table:name", new_sheet_name$)
					*book\sheet(sheet_id)\name = new_sheet_name$
					ProcedureReturn #True
				EndIf
			EndIf
		EndIf
	EndIf
EndProcedure


Procedure DeleteRows(*book.Book, sheet, row_id, n_rows=1)
	If Not *book
		ProcedureReturn #False
	EndIf
	
	; делаем запись в 0-й столбец каждой строки. Это нужно для того чтобы точно создался узел в XML для каждой строки
	Protected row
	For row = row_id To row_id + n_rows - 1
		If Not setCellValue(*book, sheet, row, 0, "-1", "float")
			Debug "DeleteRow:  Перебор по строкам"
			ProcedureReturn #False
		EndIf
	Next
	
	For row = row_id To row_id +n_rows - 1
		DeleteXMLNode(*book\sheet(sheet)\row_cash(row))
	Next
	
	_delete_rows_from_cash(*book\sheet(sheet)\row_cash(), row_id, n_rows)
	
	ProcedureReturn #True
EndProcedure


Procedure AddRows(*book.Book, sheet, row_id, n_rows=1)
	Protected *sheet.Sheet = *book\sheet(sheet)
	Protected repeated_rows, row
	
	If Not *book
		ProcedureReturn #False
	EndIf
	
	If sheet > ArraySize(*book\sheet())
		Debug #PB_Compiler_Procedure + ": Перебор по листам"
		ProcedureReturn #False
	EndIf
	
	; Заполняем кэш строк, если он ещё не заполнен
	If Not *sheet\cash_is_ready
		_fill_row_cash(*sheet, _get_sheet_node(*book\xml_node, sheet))
	EndIf
	
	; добавляем строки в кэш
	_add_rows_to_cash(*sheet\row_cash(), row_id, n_rows)
	
	; TODO нужно разрабатывать
	Protected *row_node, *parrent_node, *new_rownode
	If row_id > 0
		*row_node = *sheet\row_cash(row_id + n_rows)
	Else
		; если вставляем самую первую строку
		; ищем первый узел со строкой
		*parrent_node = _get_sheet_node(*book\xml_node, sheet)
		*row_node = ChildXMLNode(*parrent_node)
		While GetXMLNodeName(*row_node) <> "table:table-row"
			*row_node = NextXMLNode(*row_node)
		Wend
		*new_rownode = _create_row_node(PreviousXMLNode(*row_node), n_rows, _get_n_cols(_get_sheet_node(*book\xml_node, sheet)), #True)
		*sheet\n_rows + n_rows
		*sheet\row_cash(0) = *new_rownode
		ProcedureReturn #True
	EndIf
	
	If *row_node
		; Если узел есть в кэше
		repeated_rows = Val(GetXMLAttribute(*row_node, "table:number-rows-repeated"))
		If repeated_rows > 1
			; Если есть повторы
			SetXMLAttribute(*row_node, "table:number-rows-repeated", Str(repeated_rows + n_rows))
			*sheet\n_rows + n_rows
		Else
			; Если нет повторов
			Protected *sheet_node = _get_sheet_node(*book\xml_node, sheet)
			*sheet\row_cash(row_id) = _create_row_node(PreviousXMLNode(*row_node), n_rows, _get_n_cols(*sheet_node))
			*sheet\n_rows + n_rows
		EndIf
	Else
		; Если нет узла в кэше
		; Ищем предыдущий узел и меняем в нём количество повторений
		row = row_id
		While Not *row_node
			*row_node = *sheet\row_cash(row)
			row - 1 
		Wend
		repeated_rows = Val(GetXMLAttribute(*row_node, "table:number-rows-repeated"))
		SetXMLAttribute(*row_node, "table:number-rows-repeated", Str(repeated_rows + n_rows))
		*sheet\n_rows + n_rows
	EndIf
	
	ProcedureReturn #True
EndProcedure
	
EndModule

;------------------TEST-----------------------
CompilerIf #PB_Compiler_IsMainFile
	EnableExplicit
	Define ods = Ods::openOdsBook("test.ods")
	Define row = 3 ; row = 2 | 10
	If ods
		Debug "* " + Ods::getCellValue(ods, 0, 0, 0)
		Debug "* " + Ods::getCellValue(ods, 0, 0, 1)
		Debug "* " + Ods::getCellValue(ods, 0, 0, 2)
		Ods::setCellValue(ods, 0, 0, 0, ~"string\nsecond\nthird", Ods::#string)
		Ods::saveOdsBook(ods, "test_out.ods")
	Else
		Debug "Файл .ods не был открыт"
	EndIf
	Ods::freeAndNull(ods)
CompilerEndIf

[blueb]

Thanks Kulrom . Great first post!

This will come in very handy for LibreOffice files.

I took the liberty of translating the Russian comments over to English (in order to understand it for my use)

Here it is:
Edited Feb 26, 2024 (including new changes)

Code: Select all

; ============================
; =        ezOds.pbi         =
; ============================
; Module For Read And modify spreadsheets open/libre office "*.ods"
; PB 6.01 LTS
; Author - Kulrom (03/2023)
; Last Update: 25/02/2024

; v 0.4.0 fixed ODS::SaveOdsBook()  - create .bak file if rewrite .ods file.  07/09/2023
; v 0.4.1 Added ODS::getSheetName() - return name of sheet by sheet id
;		  Added ODS::getSheetID() - return sheetId by name
;		  Added ODS::setSheetName() - set sheet name						 11/09/2023
; v 0.4.2 fixed ODS::getCellValue() - fixed calculation of cell address in a row with covered cells  29/09/2023
;									fixed calculation of cell adress in a row if repeated values
;		  fixed ODS::setCellValue() - ----//--------//-------
;		  fixed ODS::SaveOdsBook() - return 0 if file is openned in openOffice.
; v 0.4.3 fixed ODS::getCellValue() - fixed calculation of cell address in a row with merged and repeated cells 25/02/2024


DeclareModule Ods
	#version$ = "0.4.3"
	; type constants for ODS cells
	#float = "float"
	#string = "string"
	#date = "date"
	#curency = "curency"
	
	Declare openOdsBook(filename$)
	Declare saveOdsBook(ods_id, filename$="")
	Declare freeOdsBook(ods_id)
	Declare.s getCellValue(ods_id, sheet, row, col)
	Declare.s getCellType(ods_id, sheet, row, col)
	Declare.s getCellStyle(ods_id, sheet, row, col)
	Declare setCellValue(ods_id, sheet, row, col, value$, type$, style$="")
	Declare DeleteRows(ods_id, sheet, row_id, n_rows=1)
	Declare AddRows(ods_id, sheet, row_id, n_rows=1)
	Declare getNRows(ods_id, sheet)
	Declare.s getSheetName(ods_id, sheet_id)
	Declare getSheetId(ods_id, sheetName$)
	Declare setSheetName(ods_id, sheet_id, new_sheet_name$)
EndDeclareModule

Module Ods
EnableExplicit

;--------------Structures------------

Structure Sheet
	name.s						; sheet name
	n_rows.i					   ; row number
	Array row_cash.i(65535)		; array of pointers to nodes with strings array of pointers to nodes
	cash_is_ready.i				; Cache relevance flag. Each time a write operation is performed, the cache becomes irrelevant
EndStructure

Structure Book
	filename$
	xml_id.i
	xml_node.i
	n_sheets.i
	Array sheet.Sheet(0)
EndStructure

;--------------Constants-------------------------------------------
#is_Save_xml =	#False  ; Constant specifies whether the xml should be saved to the computer. Needed to learn the xml structure

;--------------Globals--------------------------------------------
Global NewMap __BOOKS_MAP.Book()
Global __BOOK_COUNTER = 1


UseZipPacker()


;------------Private_methods--------------------------------------

CompilerIf #is_Save_xml
	Procedure _save_xml(xml.i, fname$)
		FormatXML(xml, #PB_XML_ReFormat|#PB_XML_ReIndent, 2)
		SaveXML(xml, fname$)
	EndProcedure
CompilerEndIf


Procedure _fill_row_cash(*sheet.Sheet, *sheet_node)
	Protected row = 0
	
	; if the cache is not ready
	If Not *sheet\cash_is_ready
		; count the number of rows
		Protected *row_node = XMLNodeFromPath(*sheet_node, "table:table-row")
		If *row_node
			While *row_node
				If ArraySize(*sheet\row_cash()) < row
					ReDim *sheet\row_cash(row)
				EndIf
				Protected repeated_rows = Val(GetXMLAttribute(*row_node, "table:number-rows-repeated"))
				Protected node_name$ = GetXMLNodeName(*row_node)
				If node_name$ = "table:table-row"
					*sheet\row_cash(row) = *row_node
					row + 1
				; If the rows are grouped
				ElseIf node_name$ = "table:table-row-group"
					Protected *row_node_1 = ChildXMLNode(*row_node)
					While *row_node_1
						Protected repeated_rows_1 = Val(GetXMLAttribute(*row_node_1, "table:number-rows-repeated"))
						node_name$ = GetXMLNodeName(*row_node_1)
						If node_name$ = "table:table-row"
							*sheet\row_cash(row) = *row_node_1
							row + 1
						EndIf
						If repeated_rows_1
							row + repeated_rows_1 - 1
						EndIf
						*row_node_1 = NextXMLNode(*row_node_1)
					Wend
				EndIf
				
				If repeated_rows
					row + repeated_rows - 1
				EndIf
				
				*row_node = NextXMLNode(*row_node)
			Wend
			*sheet\n_rows = row
			*sheet\cash_is_ready = #True
		EndIf
	EndIf
EndProcedure


;/////////////////////////////////////////////////////////////////////
; The function returns the id of xml object content.xml from ods file filename$
Procedure _get_content_xml(filename$)
	Protected *buffer
	Protected ods_file, buffer_size, xml_id
	
	If FileSize(filename$) >= 0
		ods_file = OpenPack(#PB_Any, filename$)
		If ods_file
			If ExaminePack(ods_file)
				
				While NextPackEntry(ods_file)
					If PackEntryName(ods_file) = "content.xml"
						buffer_size = PackEntrySize(ods_file)
						*buffer = AllocateMemory(buffer_size)
						UncompressPackMemory(ods_file, *buffer, buffer_size)
						xml_id = CatchXML(#PB_Any, *buffer, buffer_size)
						FreeMemory(*buffer)
						ClosePack(ods_file)
						ProcedureReturn xml_id
					EndIf
				Wend
				
			EndIf
			ClosePack(ods_file)
		EndIf
	EndIf
EndProcedure


Procedure _get_cell_node_in_row(*row_node, col_id)
	Protected col = 0, repeated_cols = 0
	Protected *cell_node = XMLNodeFromPath(*row_node, "*")
	Protected node_name$, node_value$
	
	While *cell_node
		repeated_cols = 0
		repeated_cols = Val(GetXMLAttribute(*cell_node, "table:number-columns-repeated"))
				
		If col = col_id
			ProcedureReturn *cell_node
		Else
			If repeated_cols
				col + repeated_cols
			Else
				col + 1
			EndIf
		EndIf
		
		If col_id < col
			If repeated_cols
				ProcedureReturn *cell_node
			Else
				ProcedureReturn 0
			EndIf
		EndIf
		
		*cell_node = NextXMLNode(*cell_node)
	Wend	
		
				
; 		If GetXMLNodeName(*cell_node) = "table:covered-table-cell"
; 			*cell_node = NextXMLNode(*cell_node)
; 			Continue
; 		Else
; 			repeated_cols = Val(GetXMLAttribute(*cell_node, "table:number-columns-repeated"))
; ; 			spanned_cols = Val(GetXMLAttribute(*cell_node, "table:number-columns-spanned"))
; 		EndIf
; 		
; 		node_name$ = GetXMLNodeName(*cell_node)
; 		node_value$ = GetXMLAttribute(*cell_node, "office:value")
; 		If node_name$ = "table:table-cell" Or node_name$ = "table:covered-table-cell"
; 			If col = col_id
; 				ProcedureReturn *cell_node
; 			EndIf
; 			
; 			col + 1
; 			
; 			If repeated_cols
; 				col + repeated_cols + spanned_cols- 1
; 			EndIf
; 			
; 
; 			If col_id < col
; 				If repeated_cols
; 					ProcedureReturn *cell_node
; 				Else
; 					ProcedureReturn 0
; 				EndIf
; 			EndIf
; 			
; 
; 		EndIf
; 		*cell_node = NextXMLNode(*cell_node)
; 	Wend
EndProcedure


; The function returns a pointer to the string node
Procedure _get_row_node(*sheet.Sheet, *sheet_node, row_id)
	If Not *sheet\cash_is_ready
		_fill_row_cash(*sheet, *sheet_node)
	EndIf
	
	ProcedureReturn *sheet\row_cash(row_id)
EndProcedure


;////////////////////////////////////////////////////////////////////
; The function returns a reference to a leaf node
Procedure _get_sheet_node(*book_node, sheet_id)
	Protected sheet = 0
	Protected *sheet_node = XMLNodeFromPath(*book_node, "table:table")
	While *sheet_node
		If GetXMLNodeName(*sheet_node) = "table:table"
			If sheet_id = sheet
				ProcedureReturn *sheet_node
			EndIf
			sheet + 1
			*sheet_node = NextXMLNode(*sheet_node)
		EndIf
	Wend
EndProcedure


; The function returns a pointer to an instance of the book
Procedure _get_book(book_id.i)
	Protected *book
	
	If FindMapElement(__BOOKS_MAP(), Str(book_id))
		ProcedureReturn @__BOOKS_MAP()
	Else
		ProcedureReturn #False
	EndIf
EndProcedure


Procedure _get_cell_node(ods_id, sheet, row, col)
	Protected *book.Book = _get_book(ods_id)
	If *book
		Protected *sheet.Sheet = *book\sheet(sheet)
		Protected *sheet_node = _get_sheet_node(*book\xml_node, Sheet)
		If *sheet_node
			Protected *row_node = _get_row_node(*sheet, *sheet_node, row)
			If *row_node
				Protected *cell_node = _get_cell_node_in_row(*row_node, col)
				If *cell_node
					ProcedureReturn *cell_node
				EndIf
			EndIf
		EndIf
	EndIf
EndProcedure

; function returns the number of columns in the row and fills the cache
Procedure _fill_cell_cash(*row_node, Array cell_cash.i(1))
	Protected *cell_node = XMLNodeFromPath(*row_node, "*")
	Protected col
	Protected node_name$
	While *cell_node
		Protected repeated_cells = Val(GetXMLAttribute(*cell_node, "table:number-columns-repeated"))
		node_name$ = GetXMLNodeName(*cell_node)
		If node_name$ = "table:table-cell" Or node_name$ = "table:covered-table-cell"
			cell_cash(col) = *cell_node
			If repeated_cells
				col + repeated_cells
			Else
				col + 1
			EndIf
		EndIf
		*cell_node = NextXMLNode(*cell_node)
	Wend
	ProcedureReturn col
EndProcedure


; /////////////////////////////////////////////////////////////
; The procedure builds a new ods file
Procedure _repack_zip(file_in.s, file_out.s, *content_buffer, content_size)
	Protected *buffer, res = 1
	Protected buffer_size
	Protected entry_type
	Protected entry_name$
	Protected temp_file$, base_file$
	
	; Check if the source file and the resulting file are the same in name
	; If they do, create a temporary file
	If file_out = file_in
		temp_file$ = file_in + ".bak"
		If Not CopyFile(file_in, temp_file$)
			ProcedureReturn #False
		EndIf
		file_in = temp_file$
	EndIf
	
	Protected zip_in = OpenPack(#PB_Any, file_in)
	Protected zip_out = CreatePack(#PB_Any, file_out)
	
	If zip_in
		If zip_out
			If ExaminePack(zip_in)
				While NextPackEntry(zip_in)
					entry_name$ = PackEntryName(zip_in)
					entry_type = PackEntryType(zip_in)
					buffer_size = PackEntrySize(zip_in)
					
					If entry_name$ = "content.xml"
						AddPackMemory(zip_out, *content_buffer, content_size, "content.xml")
						Continue
					EndIf
					If buffer_size																; If it is a file of size
						*buffer = AllocateMemory(buffer_size)
						UncompressPackMemory(zip_in, *buffer, buffer_size)
						AddPackMemory(zip_out, *buffer, buffer_size, entry_name$)
						FreeMemory(*buffer)
					Else																		; If it's just a catalog
						AddPackMemory(zip_out, 0, 0, entry_name$)
					EndIf
				Wend
			EndIf
			ClosePack(zip_out)
		Else
			res = #False
		EndIf
		ClosePack(zip_in)
	Else
		res = #False
	EndIf
	ProcedureReturn res
EndProcedure


; procedure creates a cell node
Procedure _modify_cell_xml_node(*cell_node, value$, type$, style$="")
	If Not *cell_node
		ProcedureReturn #False
	EndIf
	
	SetXMLAttribute(*cell_node, "office:value-type", type$)
	
	If type$ = "float"
		SetXMLAttribute(*cell_node, "office:value", value$)
	ElseIf type$ = "date"
		SetXMLAttribute(*cell_node, "office:date-value", value$)
	ElseIf type$ = "currency"
		SetXMLAttribute(*cell_node, "office:value", value$)
	ElseIf type$ = "string"
		Protected *txt_node = XMLNodeFromPath(*cell_node, "text:p")
		If *txt_node
			DeleteXMLNode(*txt_node)
		EndIf
		*txt_node = CreateXMLNode(*cell_node, "text:p", -1)
		SetXMLNodeText(*txt_node, value$)
	EndIf
	
	If style$ <> ""
		SetXMLAttribute(*cell_node, "table:style-name", style$)
	EndIf
	
	ProcedureReturn *cell_node
EndProcedure

; procedure removes row records from the cache and resizes the cache array
Procedure _delete_rows_from_cash(Array row_cash.i(1), row_id, n_rows=1)
	Protected row
	
	; shift array elements to the place of deleted rows
	For row = row_id + n_rows To ArraySize(row_cash())
		row_cash(row - n_rows) = row_cash(row)
	Next
	
	; resize the array
	Protected old_size = ArraySize(row_cash())
	Protected new_size = old_size - n_rows
	ReDim row_cash(new_size)
EndProcedure

Procedure _add_rows_to_cash(Array row_cash.i(1), row_id, n_rows=1)
	Protected row, prev_size
	
	prev_size = ArraySize(row_cash())
	ReDim row_cash(prev_size + n_rows)
	
	; shift the array elements
	If row_id = prev_size
		row_cash(prev_size + n_rows) = row_cash(row_id)
		row_cash(row_id) = 0
	Else
		For row = prev_size To row_id+1 Step -1
			row_cash(row + n_rows) = row_cash(row)
			row_cash(row) = 0
		Next
	EndIf
EndProcedure

Procedure _create_row_node(*previous_row_node, n_rows, cols_in_row, is_first_row = #False)
	Protected *new_rownode, *cell_node
	Protected *parent_node
	
	*parent_node = ParentXMLNode(*previous_row_node)
	*new_rownode = CreateXMLNode(*parent_node, "table:table-row", *previous_row_node)
	
	If is_first_row
		SetXMLAttribute(*new_rownode, "table:style-name", GetXMLAttribute(NextXMLNode(*previous_row_node), "table:style-name"))
	Else
		SetXMLAttribute(*new_rownode, "table:style-name", GetXMLAttribute(*previous_row_node, "table:style-name"))
	EndIf
	
	SetXMLAttribute(*new_rownode, "table:number-rows-repeated", Str(n_rows))
	*cell_node = CreateXMLNode(*new_rownode, "table:table-cell")
	SetXMLAttribute(*cell_node, "table:number-columns-repeated", Str(cols_in_row))
	ProcedureReturn  *new_rownode
EndProcedure


Procedure _get_n_cols(*sheet_node)
	Protected *column_node
	Protected n_cols, repeated_cols
	
	*column_node = ChildXMLNode(*sheet_node)
	While GetXMLNodeName(*column_node) = "table:table-column"
		repeated_cols = Val(GetXMLAttribute(*column_node, "table:number-columns-repeated"))
		If repeated_cols
			n_cols + repeated_cols
		Else
			n_cols + 1
		EndIf
		*column_node = NextXMLNode(*column_node)
	Wend
	ProcedureReturn n_cols
EndProcedure


;--------------Public_methods-----------------------------

;//////////////////////////////////////////////////////////////
; New Document Builder
Procedure openOdsBook(filename$)
	Protected *root_node, *book_node, *sheet_node
	Protected *book.Book
	Protected xml_id, res, sheets
	
	xml_id = _get_content_xml(filename$)
	
	CompilerIf #is_Save_xml
		_save_xml(xml_id, "content.xml")
	CompilerEndIf
	
	If xml_id
		*root_node = RootXMLNode(xml_id)
		*book_node = XMLNodeFromPath(*root_node, "/office:document-content/office:body/office:spreadsheet")
		*sheet_node = ChildXMLNode(*book_node)
		__BOOKS_MAP(Str(__BOOK_COUNTER))\xml_id = xml_id
		__BOOKS_MAP(Str(__BOOK_COUNTER))\xml_node = *book_node
		__BOOKS_MAP(Str(__BOOK_COUNTER))\filename$ = filename$
		*book = @__BOOKS_MAP()
		res = __BOOK_COUNTER
		; Calculate the number of tabs
		While *sheet_node
			If GetXMLNodeName(*sheet_node) = "table:table"
				ReDim *book\sheet(sheets)
				*book\sheet(sheets)\name = GetXMLAttribute(*sheet_node, "table:name")
				sheets + 1
			EndIf
			*sheet_node = NextXMLNode(*sheet_node)
		Wend
		
		*book\n_sheets = sheets
	EndIf
	
	
	If res
		__BOOK_COUNTER + 1
		ProcedureReturn res
	Else
		ProcedureReturn #False
	EndIf
EndProcedure


Procedure saveOdsBook(ods_id, filename$="")
	Protected res
	Protected *book.Book = _get_book(ods_id)
	Protected path$, lock_fname$
	
	If *book
		
		If filename$ = ""
			filename$ = *book\filename$
		EndIf
		
		path$ = GetPathPart(filename$)
		lock_fname$ = path$ + ".~lock." + GetFilePart(filename$) + "#"
		
		If FileSize(lock_fname$) >= 0
			ProcedureReturn #False
		EndIf
		
		CompilerIf #is_Save_xml
			_save_xml(*book\xml_id, "content_out.xml")
		CompilerEndIf

		Protected bufer_size = ExportXMLSize(*book\xml_id)
		Protected *buffer = AllocateMemory(bufer_size)
		ExportXML(*book\xml_id, *buffer, bufer_size)
		If _repack_zip(*book\filename$, filename$, *buffer, bufer_size)
			res = #True
		EndIf
		FreeMemory(*buffer)
	EndIf
	ProcedureReturn res
EndProcedure


Procedure freeOdsBook(ods_id)
	Protected *book.Book = _get_book(ods_id)
	If *book
		FreeXML(*book\xml_id)
		Protected sheet, row
		For sheet = 0 To ArraySize(*book\sheet())
			FreeArray(*book\sheet(sheet)\row_cash())
		Next
		ClearStructure(*book, Book)
		DeleteMapElement(__BOOKS_MAP(), Str(ods_id))
	EndIf
EndProcedure


Procedure.s getCellValue(ods_id, sheet, row, col)
	Protected *cell_node = _get_cell_node(ods_id, sheet, row, col)
	If *cell_node
		Select GetXMLAttribute(*cell_node, "office:value-type")
			Case "float"
				ProcedureReturn GetXMLAttribute(*cell_node, "office:value")
			Case "currency"
				ProcedureReturn GetXMLAttribute(*cell_node, "office:value")
			Case "date"
				ProcedureReturn GetXMLAttribute(*cell_node, "office:date-value")
			Case "string"
				Protected *text_node = XMLNodeFromPath(*cell_node, "text:p")
				If *text_node
					ProcedureReturn GetXMLNodeText(*text_node)
				EndIf
		EndSelect
	Else
		ProcedureReturn ""
	EndIf
EndProcedure


Procedure.s getCellType(ods_id, sheet, row, col)
	Protected *cell_node = _get_cell_node(ods_id, sheet, row, col)
	If *cell_node
		ProcedureReturn GetXMLAttribute(*cell_node, "office:value-type")
	EndIf
EndProcedure


Procedure.s getCellStyle(ods_id, sheet, row, col)
	Protected *cell_node = _get_cell_node(ods_id, sheet, row, col)
	If *cell_node
		ProcedureReturn GetXMLAttribute(*cell_node, "table:style-name")
	EndIf
EndProcedure


Procedure getNRows(ods_id, sheet)
	Protected *book.Book = _get_book(ods_id)
	Protected *sheet.Sheet = *book\sheet(sheet)
	Protected *sheet_node = _get_sheet_node(*book\xml_node, sheet)
	_fill_row_cash(*sheet, *sheet_node)
	ProcedureReturn *sheet\n_rows
EndProcedure


; The procedure returns the sheet name by sheet id. If there is no such sheet, it returns ""
Procedure.s getSheetName(ods_id, sheet_id)
	Protected *book.Book = _get_book(ods_id)
	If Not *book
		ProcedureReturn ""
	EndIf
	If sheet_id >= *book\n_sheets
		ProcedureReturn ""
	EndIf
	ProcedureReturn *book\sheet(sheet_id)\name
EndProcedure


; The procedure returns sheet Id by sheet name. If no such sheet exists, it returns -1
Procedure getSheetId(ods_id, sheetName$)
	Protected *book.Book = _get_book(ods_id)
	Protected i, res
	
	If *book
		For i = 0 To *book\n_sheets - 1
			If *book\sheet(i)\name = sheetName$
				ProcedureReturn i
			EndIf
		Next
	EndIf
	
	ProcedureReturn -1
EndProcedure


; procedure writes data to the cell
;@param ods_id: number of the ods object. This is obtained as a result of the openOdsBook procedure
;@param sheet: sheet number
;@param row: row number
;@param col: column number
;@param value$: cell value
;@param type$: data type. can be "float", "string", "date", "currency".
;@param style$: Cell style. The cell style can be obtained with getSellStyle() function
Procedure setCellValue(ods_id, sheet, row, col, value$, type$, style$="")
	Protected *book.Book = _get_book(ods_id)
	
	If Not *book
		ProcedureReturn #False
	EndIf
	
	If sheet > ArraySize(*book\sheet())
		Debug #PB_Compiler_Procedure + ": Sheet by sheet"
		ProcedureReturn #False
	EndIf
	Protected repeated_rows
	Protected *row_node, *new_row_node, *prev_row_node
	
	; Fill the cache, if it is not already full
	Protected *sheet.Sheet = *book\sheet(sheet)
	If Not *sheet\cash_is_ready
		_fill_row_cash(*sheet, _get_sheet_node(*book\xml_node, sheet))
	EndIf
	
	;{ string preparation block
	If row > ArraySize(*sheet\row_cash())
		Debug #PB_Compiler_Procedure + ": Line " + #PB_Compiler_Line + ": String search"
		ProcedureReturn #False
	EndIf
	*row_node = *sheet\row_cash(row)
	If *row_node
		; handle the case when there is such a node in the cache
		repeated_rows = Val(GetXMLAttribute(*row_node, "table:number-rows-repeated"))
		If repeated_rows
			; handle the case when the selected string is empty and has repeats
			If repeated_rows > 1
				*new_row_node = CopyXMLNode(*row_node, ParentXMLNode(*row_node), *row_node) 			; create a copy of the string and place it next
				SetXMLAttribute(*row_node, "table:number-rows-repeated", "")							; Remove repetitions in the current line
				SetXMLAttribute(*new_row_node, "table:number-rows-repeated", Str(repeated_rows-1))		; On the next line, repeat
				*sheet\row_cash(row + 1) = *new_row_node												; add a new line to the cache
			EndIf
		Else 
			; handle the case when the selected string has no repeats
		EndIf
	Else
		; Process the case when there is no such row in the cache
		; find the nearest previous row in the cache copy it to all empty cells
		Protected i = row
		While Not *sheet\row_cash(i)
			i - 1
		Wend
		*prev_row_node = *sheet\row_cash(i)
		repeated_rows = Val(GetXMLAttribute(*prev_row_node, "table:number-rows-repeated"))
		SetXMLAttribute(*prev_row_node, "table:number-rows-repeated", "")
		*new_row_node = *prev_row_node
		i + 1
		While Not *sheet\row_cash(i)
			*new_row_node = CopyXMLNode(*prev_row_node, ParentXMLNode(*prev_row_node), *new_row_node)  ; копируем строку, заполняем кэш на этом участке
			*sheet\row_cash(i) = *new_row_node
			i + 1
			If i > *sheet\n_rows-1
				Debug #PB_Compiler_Procedure + ": String search"
				ProcedureReturn #False
			EndIf
		Wend
	EndIf
	;} End of row preparation block
	
	;{ Cell preparation block
	*row_node = *sheet\row_cash(row)
	Dim cell_cash.i(65535)
	Protected n_cols = _fill_cell_cash(*row_node, cell_cash())		; fill the cell cache of the row and get the number of cells
	
	Protected *next_cell_node, *prev_cel_node, *new_cell_node
	Protected *cell_node = cell_cash(col)
	If *cell_node
		; If the cell is in the cache
		Protected repeated_cells = Val(GetXMLAttribute(*cell_node, "table:number-columns-repeated"))
		If repeated_cells > 1
			; Process the case when the cell is empty and there are repeats
			SetXMLAttribute(*cell_node, "table:number-columns-repeated", "")
			*next_cell_node = CopyXMLNode(*cell_node, ParentXMLNode(*cell_node), *cell_node)
			SetXMLAttribute(*next_cell_node, "table:number-columns-repeated", Str(repeated_cells-1))
			cell_cash(col+1) = *next_cell_node
		Else
			; Process the case when the cell has no repeats
		EndIf
	Else
		; Process the case when there is no cell in the cache
		; Find the nearest cell in the cache
		i = col
		While Not cell_cash(i)
			i - 1
		Wend
		*prev_cel_node = cell_cash(i)
		repeated_cells = Val(GetXMLAttribute(*prev_cel_node, "table:number-columns-repeated"))
		SetXMLAttribute(*prev_cel_node, "table:number-columns-repeated", "")
		*new_cell_node = *prev_cel_node
		i + 1
		While Not cell_cash(i) And i < n_cols
			*new_cell_node = CopyXMLNode(*prev_cel_node, ParentXMLNode(*prev_cel_node), *new_cell_node)
			cell_cash(i) = *new_cell_node
			i + 1
			; If i > n_cols - 1
			; 	Debug "Column override."
			; 	ProcedureReturn #False
			; EndIf
		Wend
	EndIf
	;} end of cell preparation block
	
	*cell_node = cell_cash(col)
	FreeArray(cell_cash())
	If *cell_node
		_modify_cell_xml_node(*cell_node, value$, type$, style$)
	Else
		Debug #PB_Compiler_Procedure + ": Column-by-column search"
		ProcedureReturn #False
	EndIf
	
	ProcedureReturn #True
EndProcedure


Procedure setSheetName(ods_id, sheet_id, new_sheet_name$)
	Protected *book.Book = _get_book(ods_id)
	Protected *sheet_node
	
	If sheet_id <= *book\n_sheets -1
		If CheckFilename(new_sheet_name$)  ; Name validity check
			If *book
				*sheet_node = _get_sheet_node(*book\xml_node, sheet_id)
				If *sheet_node
					SetXMLAttribute(*sheet_node, "table:name", new_sheet_name$)
					*book\sheet(sheet_id)\name = new_sheet_name$
					ProcedureReturn #True
				EndIf
			EndIf
		EndIf
	EndIf
EndProcedure


Procedure DeleteRows(ods_id, sheet, row_id, n_rows=1)
	Protected *book.Book = _get_book(ods_id)
	
	If Not *book
		ProcedureReturn #False
	EndIf
	
	; write to the 0th column of each row. This is necessary to accurately create a node in XML for each row
	Protected row
	For row = row_id To row_id + n_rows - 1
		If Not setCellValue(ods_id, sheet, row, 0, "-1", "float")
			Debug "DeleteRow:  Перебор по строкам"
			ProcedureReturn #False
		EndIf
	Next
	
	For row = row_id To row_id +n_rows - 1
		DeleteXMLNode(*book\sheet(sheet)\row_cash(row))
	Next
	
	_delete_rows_from_cash(*book\sheet(sheet)\row_cash(), row_id, n_rows)
	
	ProcedureReturn #True
EndProcedure


Procedure AddRows(ods_id, sheet, row_id, n_rows=1)
	Protected *book.Book = _get_book(ods_id)
	Protected *sheet.Sheet = *book\sheet(sheet)
	Protected repeated_rows, row
	
	If Not *book
		ProcedureReturn #False
	EndIf
	
	If sheet > ArraySize(*book\sheet())
		Debug #PB_Compiler_Procedure + ": Sheet-by-sheet"
		ProcedureReturn #False
	EndIf
	
	; Fill the row cache, if it is not already filled
	If Not *sheet\cash_is_ready
		_fill_row_cash(*sheet, _get_sheet_node(*book\xml_node, sheet))
	EndIf
	
	; add the lines to the cache
	_add_rows_to_cash(*sheet\row_cash(), row_id, n_rows)
	
	;TODO needs to be developed
	Protected *row_node, *parrent_node, *new_rownode
	If row_id > 0
		*row_node = *sheet\row_cash(row_id + n_rows)
	Else
		; if we insert the very first line
		; look for the first node with the string
		*parrent_node = _get_sheet_node(*book\xml_node, sheet)
		*row_node = ChildXMLNode(*parrent_node)
		While GetXMLNodeName(*row_node) <> "table:table-row"
			*row_node = NextXMLNode(*row_node)
		Wend
		*new_rownode = _create_row_node(PreviousXMLNode(*row_node), n_rows, _get_n_cols(_get_sheet_node(*book\xml_node, sheet)), #True)
		*sheet\n_rows + n_rows
		*sheet\row_cash(0) = *new_rownode
		ProcedureReturn #True
	EndIf
	
	If *row_node
		; If the node is in the cache
		repeated_rows = Val(GetXMLAttribute(*row_node, "table:number-rows-repeated"))
		If repeated_rows > 1
			; If there are repeats
			SetXMLAttribute(*row_node, "table:number-rows-repeated", Str(repeated_rows + n_rows))
			*sheet\n_rows + n_rows
		Else
			; If there are no repeats
			Protected *sheet_node = _get_sheet_node(*book\xml_node, sheet)
			*sheet\row_cash(row_id) = _create_row_node(PreviousXMLNode(*row_node), n_rows, _get_n_cols(*sheet_node))
			*sheet\n_rows + n_rows
		EndIf
	Else
		; If there is no node in the cache
		; Search for the previous node and change the number of repetitions in it
		row = row_id
		While Not *row_node
			*row_node = *sheet\row_cash(row)
			row - 1 
		Wend
		repeated_rows = Val(GetXMLAttribute(*row_node, "table:number-rows-repeated"))
		SetXMLAttribute(*row_node, "table:number-rows-repeated", Str(repeated_rows + n_rows))
		*sheet\n_rows + n_rows
	EndIf
	
	ProcedureReturn #True
EndProcedure
	
EndModule
;-----------------------TEST-----------------------
CompilerIf #PB_Compiler_IsMainFile
	
	Define ods = Ods::openOdsBook("test.ods")
	Define row = 10 ; row = 2 | 10
	If ods
		Debug "* " + Ods::getCellValue(ods, 0, row, 0)
		Debug "* " + Ods::getCellValue(ods, 0, row, 1)
		Debug "* " + Ods::getCellValue(ods, 0, row, 2)
		Debug "* " + Ods::getCellValue(ods, 0, row, 3)
		Debug "* " + Ods::getCellValue(ods, 0, row, 4)
		Debug "* " + Ods::getCellValue(ods, 0, row, 5)
		Debug "* " + Ods::getCellValue(ods, 0, row, 6)
		Debug "* " + Ods::getCellValue(ods, 0, row, 7)
		Debug "* " + Ods::getCellValue(ods, 0, row, 8)
		Debug "* " + Ods::getCellValue(ods, 0, row, 9)

		Ods::setCellValue(ods, 0, 9, 1, "777", Ods::#string)
		ods::saveOdsBook(ods, "test_out.ods")
; 		Ods::freeOdsBook(ods)
		Debug "Done!"
	Else
		Debug "The .ods file was not opened"
	EndIf
	
	
CompilerEndIf

[Quin]

Wow, this is fantastic. Thanks so much!

[Kulrom]

I took the liberty of translating the Russian comments over to English (in order to understand it for my use)

Thanks for the translation, blueb. To be honest, I was too lazy to translate . Only here the word "Строка" in this case should be translated as "row" and not "string"

[Kulrom]

Updated to version 0.4.1.
Added ODS::getSheetName() - return name of sheet by sheet id
Added ODS::getSheetID() - return sheetId by name
Added ODS::setSheetName() - set sheet name

The code in the start message has been updated

[idle]

This is a great contribution. Thanks for sharing.

[jacdelad]

Yeah, this is great. Also it's always cool when someone who isn't one of the usual suspects submits something so widely usable.

[Fred]

Nice work

[GoodNPlenty]

Thank You for posting as this will be very helpful.

[loulou2522]

With PB 6.03 beta 6 when execution
The .ods file was not opened
What I do wrong ?

[StarBootics]

With PB 6.03 beta 6 when execution
The .ods file was not opened
What I do wrong ?

At the current state the code from Kulrom work only on existing files. So make sure the test_1.ods file exist in the same folder you have saved the source code file.

Best regards.
StarBootics

[Kulrom]

I posted a new version of the module in the first message

Version 0.4.2
fixed ODS::getCellValue() - fixed calculation of cell address in a row with covered cells
fixed calculation of cell adress in a row if repeated values
fixed ODS::setCellValue() - fixed calculation of cell address in a row with covered cells
fixed ODS::SaveOdsBook() - return 0 if file is openned in openOffice.

[Kulrom]

v 0.4.3 fixed ODS::getCellValue() - fixed calculation of cell address in a row with merged and repeated cells 25/02/2024

[idle]

Thanks, it's very much appreciated

[blueb]

v 0.4.3 fixed ODS::getCellValue() - fixed calculation of cell address in a row with merged and repeated cells 25/02/2024

Hi Kulrom

I'll having issues with 6.11 LTS (x64) on line 347
Apparently some changes in: AddPackMemory(zip_out, 0, 0, entry_name$)
Doesn't seem to like zeros