Text to XLSX
Posted: Sun Mar 26, 2017 9:37 am
Hi,
Recently i needed to convert a text to xlsx .... so here is my work, this is a module that converts text files to xlsx files
Only text and numbers (no date, no time ...) and creates only sheet1
It is quite basic and it seems to serve my needs very well feel free to use and please report any bug you might face ( i wont be surprised 
)
Said
Edit: replaced the My_IsNumber() procedure with a new one ... the new one matches Excel way
Edit2: replaced again that same routine with a new and faster My_IsNumer() again .... hopefully the last one
Recently i needed to convert a text to xlsx .... so here is my work, this is a module that converts text files to xlsx files
Only text and numbers (no date, no time ...) and creates only sheet1
It is quite basic and it seems to serve my needs very well
feel free to use and please report any bug you might face ( i wont be surprised )Said
Code: Select all
; XLSX module
; mainly to write Text files as XLSX files, no charts, no pivot-tables, ....
; Only one sheet of data, only text and numbers!
; Mar-2017
DeclareModule XLSX
Declare.i TextToXLSX(FileNameTxt.s, FileNameXLSX.s, ColSep.s = #TAB$)
EndDeclareModule
;-\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
;-......... CORE MODULE
;-\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
Module XLSX
EnableExplicit
UseZipPacker()
#Base26$ = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Global NewMap SharedStrings.s()
Global SharedStringsCount.i, LastCol.i, LastRow.i
Structure TBigStr
*Pointer
CurOffset.i ; <= next offset (we write at that position at next append)
; when < 0 then allocation (re-allocation) is no more possible!
EndStructure
Procedure My_IsNumber(txt.s)
; sign +/- : is accepted at the begining or after e (begining <=> prv = 0)
; dot . : is accepted anywhere but once only
; exp e : is validated if preceeded by a digit and ( followed by a digit or followed by a sign digit)
; A valid number will have its ValD() correct
;
Protected cur.c, prv.c, *p.Character, i, n, nbr_dot, nbr_exp, vld_exp, nbr_dgt
n = Len(txt)
If n = 0 : ProcedureReturn #False : EndIf
For i=1 To n
prv = cur
*p = @txt + (i-1) * SizeOf(Character)
cur = *p\c
Select cur
Case '+', '-'
If (prv <> 'e') And (prv <> 0) : ProcedureReturn #False : EndIf
Case '.'
nbr_dot + 1
If nbr_exp > 0 : ProcedureReturn #False : EndIf
Case 'e'
If (prv = '.') Or ((prv >= '0') And (prv <= '9'))
nbr_exp + 1
vld_exp = -1 ; an 'e' has started
Else
ProcedureReturn #False
EndIf
Case '0' To '9'
If vld_exp = -1 : vld_exp + 1 : EndIf ; closing started 'e' if any
nbr_dgt + 1
Default
ProcedureReturn #False
EndSelect
If (nbr_dot > 1) Or (nbr_exp > 1) : ProcedureReturn #False : EndIf
Next
If vld_exp <> 0 : ProcedureReturn #False : EndIf
If nbr_dgt <= 0 : ProcedureReturn #False : EndIf
ProcedureReturn #True
EndProcedure
Procedure.s TrimZeros(txt.s)
Protected *p.Character, i,j,n
n = Len(txt)
If n = 0 : ProcedureReturn "" : EndIf
For i=1 To n
*p = @txt + ((i-1)* SizeOf(Character))
If *p\c = '.'
For j=n To i Step -1
*p = @txt + ((j-1)* SizeOf(Character))
If (*p\c <> '0') And (*p\c <> '.') : Break : EndIf
*p\c = 0
Next
EndIf
Next
ProcedureReturn txt
EndProcedure
Procedure.i BigStrAppend(*me.TBigStr, nwStr.s)
; the fastest! saves a lot of time by avoiding repetitive memory allocation/move!
; the price is the reservation of a decent block of memory ~ 10 KB !
Protected chunk,addReq,totSize, *p
chunk = 10240
If *me\CurOffset < 0 : ProcedureReturn #False : EndIf
If *me\Pointer = 0 : *me\Pointer = ReAllocateMemory(*me\Pointer, chunk) : EndIf
;addReq = Len(nwStr) * SizeOf(Character) ; bit faster than:
addReq = StringByteLength(nwStr)
If addReq = 0 : ProcedureReturn #True : EndIf
totSize = MemorySize(*me\Pointer)
If totSize < *me\curOffset + addReq + 2
;*me\Pointer = ReAllocateMemory(*me\Pointer,*me\CurOffset + chunk + addReq + 2)
*p = ReAllocateMemory(*me\Pointer,*me\CurOffset + chunk + addReq + 2)
If *p
*me\Pointer = *p
Else
*me\curOffset = -1
ProcedureReturn #False
EndIf
totSize = MemorySize(*me\Pointer)
EndIf
If *me\curOffset + addReq > totSize
MessageRequester("XXXXXX"," >>>>>>>>>>>>>>>>>>> BigStrAppend")
EndIf
PokeS(*me\Pointer + *me\curOffset, nwStr) ; -1,#PB_Ascii)
*me\curOffset = *me\curOffset + addReq
ProcedureReturn #True
EndProcedure
Procedure.s BigStrGetString(*me.TBigStr)
Protected strRet.s
If *me\Pointer
strRet = PeekS(*me\Pointer)
*me\CurOffset = 0
FreeMemory(*me\Pointer)
ClearStructure(*me,TBigStr)
EndIf
ProcedureReturn strRet
EndProcedure
Procedure.i BigStrClear(*me.TBigStr)
If *me\Pointer
*me\CurOffset = 0
FreeMemory(*me\Pointer)
ClearStructure(*me,TBigStr)
EndIf
EndProcedure
Procedure.i SplitString(s.s, multiCharSep.s, Array a.s(1))
; last substring is not necesseraly followed by a char-sep
; retrun 0 on empty string and return 1 on string with no sep at all
Protected count, i, soc, lnStr,lnBStr, lnSep,lnBSep, ss, ee
soc = SizeOf(Character)
lnSep = Len(multiCharSep) : lnBSep = lnSep * soc
lnStr = Len(s) : lnBStr = lnStr * soc
If lnStr <= 0 : ProcedureReturn 0 : EndIf
count = CountString(s,multiCharSep)
If count <= 0
Dim a(1) : a(1) = s : ProcedureReturn 1
EndIf
If Right(s,lnSep) <> multiCharSep : count + 1 : EndIf
Dim a(count) ; a(0) is ignored
i = 1: ss = 0: ee = 0
While ee < lnBStr And i <= count
If CompareMemory(@s + ee, @multiCharSep, lnBSep)
a(i) = PeekS(@s + ss, (ee-ss)/soc)
ss = ee + lnBSep: ee = ss: i+1
Else
ee + soc
EndIf
Wend
If i < count+1: a(count) = PeekS(@s + ss, (ee-ss)/soc) : EndIf
ProcedureReturn count ;return count of substrings
EndProcedure
;----------------------------------------------------------------------------
Procedure FirstPass(FileNameTxt.s, ColSep.s = #TAB$)
Protected fNbr, fmt, iWrd, nWrd, Dim tWrd.s(0), n, txt.s
; global variables
ClearMap(SharedStrings())
LastCol = 0
LastRow = 0
SharedStringsCount = 0
fNbr = ReadFile(#PB_Any, FileNameTxt)
If fNbr
fmt = ReadStringFormat(fNbr)
While Not Eof(fNbr)
txt = ReadString(fNbr, fmt) : LastRow + 1
nWrd = SplitString(txt, ColSep, tWrd())
If LastCol < nWrd : LastCol = nWrd : EndIf ; needed in BuildSheet1Data
For iWrd=1 To nWrd
If tWrd(iWrd) = "" : Continue : EndIf
If My_IsNumber(tWrd(iWrd)) : Continue : EndIf
SharedStringsCount + 1
If Not FindMapElement(SharedStrings(), tWrd(iWrd))
n = MapSize(SharedStrings())
SharedStrings(tWrd(iWrd)) = Str(n) ; 0-based index
EndIf
Next
Wend
CloseFile(fNbr)
EndIf
ProcedureReturn SharedStringsCount
EndProcedure
Procedure.s ExcelColRef(x.i)
; adapted from skywalk code: http://www.purebasic.fr/english/viewtopic.php?f=12&t=55799
; SF_IntToBase()
Protected.i p, BaseLen = 26
Protected.s r$
If x > 0
While x <> 0
p = (x % BaseLen)
If p = 0 : p = 26 : EndIf ; 1 .. 26
r$ = Mid(#Base26$, p, 1) + r$
x = (x-1) / BaseLen
Wend
Else
r$ = ""
EndIf
ProcedureReturn r$
EndProcedure
Procedure.s RefOfCell(Row.i, Col.i)
; returns A1, ....
ProcedureReturn ExcelColRef(Col) + Str(Row)
EndProcedure
; _R_ : refers to row #
; _C_ : refers to col #
; _V_ : is a 'stringified' value
; in __CellDataText : the V parameter refers to the Value in the map SharedStrings()
Macro __CellDataText(_R_,_C_,_V_)
"<c r=" +#DQUOTE$+ RefOfCell(_R_,_C_) +#DQUOTE$+ " t=" +#DQUOTE$+"s"+#DQUOTE$+ "><v>" + _V_ + "</v></c>"
EndMacro
Macro __CellDataNumber(_R_,_C_,_V_)
"<c r=" +#DQUOTE$+ RefOfCell(_R_,_C_) +#DQUOTE$+ "><v>" + TrimZeros(_V_) + "</v></c>"
EndMacro
Procedure.s CleanTextXML(txt.s)
; txt should not contain >, <, &
; each special character should be replaced with: > < &
Protected *mb, i, n, found_1, found_2, found_3, ret.s
Protected.c curChr, asc_1, asc_2, asc_3
n = Len(txt)
If n <=0 : ProcedureReturn "" : EndIf
asc_1 = Asc(">")
asc_2 = Asc("<")
asc_3 = Asc("&")
*mb = @txt
For i=0 To n-1
curChr = PeekC(*mb + (i * SizeOf(Character)) )
If curChr = asc_1 : found_1 = #True : EndIf
If curChr = asc_2 : found_2 = #True : EndIf
If curChr = asc_3 : found_3 = #True : EndIf
Next
ret = txt
If found_1 : ret = ReplaceString(txt, ">", ">") : EndIf
If found_2 : ret = ReplaceString(txt, "<", "<") : EndIf
If found_3 : ret = ReplaceString(txt, "&", "&") : EndIf
ProcedureReturn ret
EndProcedure
Procedure.s BuildSheet1Data(FileNameTxt.s, ColSep.s = #TAB$)
Protected fNbr, fmt
Protected iWrd, nWrd, Dim tWrd.s(0)
Protected.s txt, txt_hdr, txt_row, txt_cel, ret
Protected Row, Col
Protected bs.TBigStr, *bs.TBigStr = @bs
fNbr = ReadFile(#PB_Any, FileNameTxt)
If fNbr
fmt = ReadStringFormat(fNbr)
;<?xml version='1.0' encoding='UTF-8' standalone='yes'?><worksheet xmlns='http://schemas.openxmlformats.org/spreadsheetml/2006/main' xmlns:r='http://schemas.openxmlformats.org/officeDocument/2006/relationships' xmlns:mc='http://schemas.openxmlformats.org/markup-compatibility/2006' mc:Ignorable='x14ac' xmlns:x14ac='http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac'>
txt_hdr = "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" +#CRLF$+ "<worksheet xmlns='http://schemas.openxmlformats.org/spreadsheetml/2006/main' xmlns:r='http://schemas.openxmlformats.org/officeDocument/2006/relationships' xmlns:mc='http://schemas.openxmlformats.org/markup-compatibility/2006' mc:Ignorable='x14ac' xmlns:x14ac='http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac'>"
ReplaceString(txt_hdr, "'", #DQUOTE$, #PB_String_InPlace)
BigStrAppend(*bs, txt_hdr)
txt_hdr = "<dimension ref='A1:" + RefOfCell(LastRow, LastCol) + "'/>"
ReplaceString(txt_hdr, "'", #DQUOTE$, #PB_String_InPlace)
BigStrAppend(*bs, txt_hdr)
; <sheetViews><sheetView tabSelected='1' workbookViewId='0'/></sheetViews><sheetFormatPr defaultRowHeight='15' x14ac:dyDescent='0.25'/>
txt_hdr = "<sheetViews><sheetView tabSelected='1' workbookViewId='0'/></sheetViews><sheetFormatPr defaultRowHeight='15' x14ac:dyDescent='0.25'/>"
ReplaceString(txt_hdr, "'", #DQUOTE$, #PB_String_InPlace)
BigStrAppend(*bs, txt_hdr)
BigStrAppend(*bs, "<sheetData>")
While Not Eof(fNbr)
txt = ReadString(fNbr, fmt) : Row + 1
nWrd = SplitString(txt, ColSep, tWrd())
If nWrd = 0 : Continue : EndIf
;<row r='4' spans='1:6' x14ac:dyDescent='0.25'>
txt_row = "<row r='" + Str(Row) + "' spans='1:" + Str(LastCol) +"' x14ac:dyDescent='0.25'>"
ReplaceString(txt_row, "'", #DQUOTE$, #PB_String_InPlace)
BigStrAppend(*bs, txt_row)
For iWrd=1 To nWrd
If tWrd(iWrd) = "" : Continue : EndIf
Col = iWrd
If My_IsNumber(tWrd(iWrd))
txt_cel = __CellDataNumber(Row, Col, tWrd(iWrd))
Else
; anything else is a text (for now at least)
txt_cel = __CellDataText(Row, Col, SharedStrings(tWrd(iWrd)) )
EndIf
BigStrAppend(*bs, txt_cel)
Next
BigStrAppend(*bs, "</row>") ; ending the row-text
Wend
; ending the file header text
;</sheetData><pageMargins left='0.7' right='0.7' top='0.75' bottom='0.75' header='0.3' footer='0.3'/></worksheet>
txt_hdr = "</sheetData><pageMargins left='0.7' right='0.7' top='0.75' bottom='0.75' header='0.3' footer='0.3'/></worksheet>"
ReplaceString(txt_hdr, "'", #DQUOTE$, #PB_String_InPlace)
BigStrAppend(*bs, txt_hdr)
CloseFile(fNbr)
EndIf
ret = BigStrGetString(*bs)
ProcedureReturn ret
EndProcedure
Procedure.s BuildSharedStrings()
; uses the global map SharedStrings() to build the string section: sharedStrings.xml
Protected.s txt_hdr, txt_row, ret
Protected i,n, Dim t.s(0)
Protected bs.TBigStr, *bs.TBigStr = @bs
n = MapSize(SharedStrings())
; <?xml version="1.0" encoding="UTF-8" standalone="yes"?><sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="15" uniqueCount="9">
txt_hdr = "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" +#CRLF$+ "<sst xmlns='http://schemas.openxmlformats.org/spreadsheetml/2006/main'"
ReplaceString(txt_hdr, "'", #DQUOTE$, #PB_String_InPlace)
BigStrAppend(*bs, txt_hdr)
txt_hdr = " count='" +Str(SharedStringsCount) + "' uniqueCount='" +Str(n) +"'>"
ReplaceString(txt_hdr, "'", #DQUOTE$, #PB_String_InPlace)
BigStrAppend(*bs, txt_hdr)
; moving the map into an array
If n > 0
Dim t(n-1)
ForEach SharedStrings()
i = Val(SharedStrings()) ; 0 .. n-1
t(i) = CleanTextXML( MapKey(SharedStrings()) )
Next
For i=0 To ArraySize(t())
; <si><t>1a</t></si>
txt_row = "<si><t>"+ t(i)+ "</t></si>"
BigStrAppend(*bs, txt_row)
Next
EndIf
BigStrAppend(*bs, "</sst>")
ret = BigStrGetString(*bs)
ProcedureReturn ret
EndProcedure
Procedure.i FreshXLSX(Pkr.i)
; builds from scratch a new empty xlsx
Protected org_txt.s, section.s, i,n, Dim buffer.a(0)
; "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" +#CRLF$+ "<Types xmlns='http://schemas.openxmlformats.org/package/2006/content-types'><Default Extension='rels' ContentType='application/vnd.openxmlformats-package.relationships+xml'/><Default Extension='xml' ContentType='application/xml'/><Override PartName='/xl/workbook.xml' ContentType='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml'/><Override PartName='/xl/worksheets/sheet1.xml' ContentType='application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml'/><Override PartName='/xl/theme/theme1.xml' ContentType='application/vnd.openxmlformats-officedocument.theme+xml'/><Override PartName='/xl/styles.xml' ContentType='application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml'/><Override PartName='/xl/sharedStrings.xml' ContentType='application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml'/><Override PartName='/docProps/core.xml' ContentType='application/vnd.openxmlformats-package.core-properties+xml'/><Override PartName='/docProps/app.xml' ContentType='application/vnd.openxmlformats-officedocument.extended-properties+xml'/></Types>
org_txt = "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" +#CRLF$+ "<Types xmlns='http://schemas.openxmlformats.org/package/2006/content-types'><Default Extension='rels' ContentType='application/vnd.openxmlformats-package.relationships+xml'/><Default Extension='xml' ContentType='application/xml'/><Override PartName='/xl/workbook.xml' ContentType='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml'/><Override PartName='/xl/worksheets/sheet1.xml' ContentType='application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml'/><Override PartName='/xl/theme/theme1.xml' ContentType='application/vnd.openxmlformats-officedocument.theme+xml'/><Override PartName='/xl/styles.xml' ContentType='application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml'/><Override PartName='/xl/sharedStrings.xml' ContentType='application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml'/><Override PartName='/docProps/core.xml' ContentType='application/vnd.openxmlformats-package.core-properties+xml'/><Override PartName='/docProps/app.xml' ContentType='application/vnd.openxmlformats-officedocument.extended-properties+xml'/></Types>"
ReplaceString(org_txt, "'", #DQUOTE$, #PB_String_InPlace)
n = StringByteLength(org_txt, #PB_Ascii)
Dim buffer(n-1)
PokeS(@buffer(0), org_txt, -1, #PB_Ascii|#PB_String_NoZero)
i = AddPackMemory(Pkr, @buffer(0), n, "[Content_Types].xml" )
;<?xml version='1.0' encoding='UTF-8' standalone='yes'?><Relationships xmlns='http://schemas.openxmlformats.org/package/2006/relationships'><Relationship Id='rId3' Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties' Target='docProps/app.xml'/><Relationship Id='rId2' Type='http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties' Target='docProps/core.xml'/><Relationship Id='rId1' Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument' Target='xl/workbook.xml'/></Relationships>
org_txt = "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" +#CRLF$+ "<Relationships xmlns='http://schemas.openxmlformats.org/package/2006/relationships'><Relationship Id='rId3' Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties' Target='docProps/app.xml'/><Relationship Id='rId2' Type='http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties' Target='docProps/core.xml'/><Relationship Id='rId1' Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument' Target='xl/workbook.xml'/></Relationships>"
ReplaceString(org_txt, "'", #DQUOTE$, #PB_String_InPlace)
n = StringByteLength(org_txt, #PB_Ascii)
Dim buffer(n-1)
PokeS(@buffer(0), org_txt, -1, #PB_Ascii|#PB_String_NoZero)
i = AddPackMemory(Pkr, @buffer(0), n, "_rels/.rels" )
org_txt = "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" +#CRLF$+ "<Properties xmlns='http://schemas.openxmlformats.org/officeDocument/2006/extended-properties' xmlns:vt='http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes'><Application>Microsoft Excel</Application><DocSecurity>0</DocSecurity><ScaleCrop>false</ScaleCrop><HeadingPairs><vt:vector size='2' baseType='variant'><vt:variant><vt:lpstr>Worksheets</vt:lpstr></vt:variant><vt:variant><vt:i4>1</vt:i4></vt:variant></vt:vector></HeadingPairs><TitlesOfParts><vt:vector size='1' baseType='lpstr'><vt:lpstr>Sheet1</vt:lpstr></vt:vector></TitlesOfParts><Company></Company><LinksUpToDate>false</LinksUpToDate><SharedDoc>false</SharedDoc><HyperlinksChanged>false</HyperlinksChanged><AppVersion>15.0300</AppVersion></Properties>"
ReplaceString(org_txt, "'", #DQUOTE$, #PB_String_InPlace)
n = StringByteLength(org_txt, #PB_Ascii)
Dim buffer(n-1)
PokeS(@buffer(0), org_txt, -1, #PB_Ascii|#PB_String_NoZero)
i = AddPackMemory(Pkr, @buffer(0), n, "docProps/app.xml" )
org_txt = "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" +#CRLF$+ "<cp:coreProperties xmlns:cp='http://schemas.openxmlformats.org/package/2006/metadata/core-properties' xmlns:dc='http://purl.org/dc/elements/1.1/' xmlns:dcterms='http://purl.org/dc/terms/' xmlns:dcmitype='http://purl.org/dc/dcmitype/' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>"
org_txt = org_txt + "<dc:creator>PB_XLSX_Module</dc:creator></cp:coreProperties>"
ReplaceString(org_txt, "'", #DQUOTE$, #PB_String_InPlace)
n = StringByteLength(org_txt, #PB_Ascii)
Dim buffer(n-1)
PokeS(@buffer(0), org_txt, -1, #PB_Ascii|#PB_String_NoZero)
i = AddPackMemory(Pkr, @buffer(0), n, "docProps/core.xml" )
; xl
org_txt = "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" +#CRLF$+ "<styleSheet xmlns='http://schemas.openxmlformats.org/spreadsheetml/2006/main' xmlns:mc='http://schemas.openxmlformats.org/markup-compatibility/2006' mc:Ignorable='x14ac' xmlns:x14ac='http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac'><fonts count='1' x14ac:knownFonts='1'><font><sz val='11'/><color theme='1'/><name val='Calibri'/><family val='2'/><scheme val='minor'/></font></fonts><fills count='2'><fill><patternFill patternType='none'/></fill><fill><patternFill patternType='gray125'/></fill></fills><borders count='1'><border><left/><right/><top/><bottom/><diagonal/></border></borders><cellStyleXfs count='1'><xf numFmtId='0' fontId='0' fillId='0' borderId='0'/></cellStyleXfs><cellXfs count='1'><xf numFmtId='0' fontId='0' fillId='0' borderId='0' xfId='0'/></cellXfs><cellStyles count='1'><cellStyle name='Normal' xfId='0' builtinId='0'/></cellStyles><dxfs count='0'/><tableStyles count='0' defaultTableStyle='TableStyleMedium2' defaultPivotStyle='PivotStyleLight16'/><extLst><ext uri='{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}' xmlns:x14='http://schemas.microsoft.com/office/spreadsheetml/2009/9/main'><x14:slicerStyles defaultSlicerStyle='SlicerStyleLight1'/></ext><ext uri='{9260A510-F301-46a8-8635-F512D64BE5F5}' xmlns:x15='http://schemas.microsoft.com/office/spreadsheetml/2010/11/main'><x15:timelineStyles defaultTimelineStyle='TimeSlicerStyleLight1'/></ext></extLst></styleSheet>"
ReplaceString(org_txt, "'", #DQUOTE$, #PB_String_InPlace)
n = StringByteLength(org_txt, #PB_Ascii)
Dim buffer(n-1)
PokeS(@buffer(0), org_txt, -1, #PB_Ascii|#PB_String_NoZero)
i = AddPackMemory(Pkr, @buffer(0), n, "xl/styles.xml" )
org_txt = "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" +#CRLF$+ "<workbook xmlns='http://schemas.openxmlformats.org/spreadsheetml/2006/main' xmlns:r='http://schemas.openxmlformats.org/officeDocument/2006/relationships' xmlns:mc='http://schemas.openxmlformats.org/markup-compatibility/2006' mc:Ignorable='x15' xmlns:x15='http://schemas.microsoft.com/office/spreadsheetml/2010/11/main'><fileVersion appName='xl' lastEdited='6' lowestEdited='6' rupBuild='14420'/><workbookPr defaultThemeVersion='153222'/><mc:AlternateContent xmlns:mc='http://schemas.openxmlformats.org/markup-compatibility/2006'><mc:Choice Requires='x15'><x15ac:absPath url='C:\Temp\xlsx\' xmlns:x15ac='http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac'/></mc:Choice></mc:AlternateContent><bookViews><workbookView xWindow='0' yWindow='0' windowWidth='20490' windowHeight='7755'/></bookViews><sheets><sheet name='Sheet1' sheetId='1' r:id='rId1'/></sheets><calcPr calcId='152511'/><extLst><ext uri='{140A7094-0E35-4892-8432-C4D2E57EDEB5}' xmlns:x15='http://schemas.microsoft.com/office/spreadsheetml/2010/11/main'><x15:workbookPr chartTrackingRefBase='1'/></ext></extLst></workbook>"
ReplaceString(org_txt, "'", #DQUOTE$, #PB_String_InPlace)
n = StringByteLength(org_txt, #PB_Ascii)
Dim buffer(n-1)
PokeS(@buffer(0), org_txt, -1, #PB_Ascii|#PB_String_NoZero)
i = AddPackMemory(Pkr, @buffer(0), n, "xl/workbook.xml" )
; xl/_rels
; "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" +#CRLF$+ "<Relationships xmlns='http://schemas.openxmlformats.org/package/2006/relationships'><Relationship Id='rId3' Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles' Target='styles.xml'/><Relationship Id='rId2' Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme' Target='theme/theme1.xml'/><Relationship Id='rId1' Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet' Target='worksheets/sheet1.xml'/><Relationship Id='rId4' Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings' Target='sharedStrings.xml'/></Relationships>"
org_txt = "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" +#CRLF$+ "<Relationships xmlns='http://schemas.openxmlformats.org/package/2006/relationships'><Relationship Id='rId3' Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles' Target='styles.xml'/><Relationship Id='rId2' Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme' Target='theme/theme1.xml'/><Relationship Id='rId1' Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet' Target='worksheets/sheet1.xml'/><Relationship Id='rId4' Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings' Target='sharedStrings.xml'/></Relationships>"
ReplaceString(org_txt, "'", #DQUOTE$, #PB_String_InPlace)
n = StringByteLength(org_txt, #PB_Ascii)
Dim buffer(n-1)
PokeS(@buffer(0), org_txt, -1, #PB_Ascii|#PB_String_NoZero)
i = AddPackMemory(Pkr, @buffer(0), n, "xl/_rels/workbook.xml.rels" )
; xl/theme
org_txt = "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" +#CRLF$+ "<a:theme xmlns:a='http://schemas.openxmlformats.org/drawingml/2006/main' name='Office Theme'><a:themeElements><a:clrScheme name='Office'><a:dk1><a:sysClr val='windowText' lastClr='000000'/></a:dk1><a:lt1><a:sysClr val='window' lastClr='FFFFFF'/></a:lt1><a:dk2><a:srgbClr val='44546A'/></a:dk2><a:lt2><a:srgbClr val='E7E6E6'/></a:lt2><a:accent1><a:srgbClr val='5B9BD5'/></a:accent1><a:accent2><a:srgbClr val='ED7D31'/></a:accent2><a:accent3><a:srgbClr val='A5A5A5'/></a:accent3><a:accent4><a:srgbClr val='FFC000'/></a:accent4><a:accent5><a:srgbClr val='4472C4'/></a:accent5><a:accent6><a:srgbClr val='70AD47'/></a:accent6><a:hlink><a:srgbClr val='0563C1'/></a:hlink><a:folHlink><a:srgbClr val='954F72'/></a:folHlink></a:clrScheme><a:fontScheme name='Office'><a:majorFont><a:latin typeface='Calibri Light' panose='020F0302020204030204'/><a:ea typeface=''/><a:cs typeface=''/><a:font script='Jpan' typeface='ＭＳ Ｐゴシック'/><a:font script='Hang' typeface='맑은 고딕'/><a:font script='Hans' typeface='宋体'/><a:font script='Hant' typeface='新細明體'/><a:font script='Arab' typeface='Times New Roman'/><a:font script='Hebr' typeface='Times New Roman'/><a:font script='Thai' typeface='Tahoma'/><a:font script='Ethi' typeface='Nyala'/><a:font script='Beng' typeface='Vrinda'/><a:font script='Gujr' typeface='Shruti'/><a:font script='Khmr' typeface='MoolBoran'/><a:font script='Knda' typeface='Tunga'/><a:font script='Guru' typeface='Raavi'/><a:font script='Cans' typeface='Euphemia'/><a:font script='Cher' typeface='Plantagenet Cherokee'/><a:font script='Yiii' typeface='Microsoft Yi Baiti'/><a:font script='Tibt' typeface='Microsoft Himalaya'/><a:font script='Thaa' typeface='MV Boli'/><a:font script='Deva' typeface='Mangal'/><a:font script='Telu' typeface='Gautami'/><a:font script='Taml' typeface='Latha'/><a:font script='Syrc' typeface='Estrangelo Edessa'/><a:font script='Orya' typeface='Kalinga'/><a:font script='Mlym' typeface='Kartika'/><a:font script='Laoo' typeface='DokChampa'/><a:font script='Sinh' typeface='Iskoola Pota'/><a:font script='Mong' typeface='Mongolian Baiti'/><a:font script='Viet' typeface='Times New Roman'/><a:font script='Uigh' typeface='Microsoft Uighur'/><a:font script='Geor' typeface='Sylfaen'/></a:majorFont><a:minorFont><a:latin typeface='Calibri' panose='020F0502020204030204'/><a:ea typeface=''/><a:cs typeface=''/><a:font script='Jpan' typeface='ＭＳ Ｐゴシック'/><a:font script='Hang' typeface='맑은 고딕'/><a:font script='Hans' typeface='宋体'/><a:font script='Hant' typeface='新細明體'/><a:font script='Arab' typeface='Arial'/><a:font script='Hebr' typeface='Arial'/><a:font script='Thai' typeface='Tahoma'/><a:font script='Ethi' typeface='Nyala'/><a:font script='Beng' typeface='Vrinda'/><a:font script='Gujr' typeface='Shruti'/><a:font script='Khmr' typeface='DaunPenh'/><a:font script='Knda' typeface='Tunga'/><a:font script='Guru' typeface='Raavi'/><a:font script='Cans' typeface='Euphemia'/><a:font script='Cher' typeface='Plantagenet Cherokee'/><a:font script='Yiii' typeface='Microsoft Yi Baiti'/><a:font script='Tibt' typeface='Microsoft Himalaya'/><a:font script='Thaa' typeface='MV Boli'/><a:font script='Deva' typeface='Mangal'/><a:font script='Telu' typeface='Gautami'/><a:font script='Taml' typeface='Latha'/><a:font script='Syrc' typeface='Estrangelo Edessa'/><a:font script='Orya' typeface='Kalinga'/><a:font script='Mlym' typeface='Kartika'/><a:font script='Laoo' typeface='DokChampa'/><a:font script='Sinh' typeface='Iskoola Pota'/><a:font script='Mong' typeface='Mongolian Baiti'/><a:font script='Viet' typeface='Arial'/><a:font script='Uigh' typeface='Microsoft Uighur'/><a:font script='Geor' typeface='Sylfaen'/></a:minorFont></a:fontScheme><a:fmtScheme name='Office'><a:fillStyleLst><a:solidFill><a:schemeClr val='phClr'/></a:solidFill><a:gradFill rotWithShape='1'><a:gsLst><a:gs pos='0'><a:schemeClr val='phClr'><a:lumMod val='110000'/><a:satMod val='105000'/><a:tint val='67000'/></a:schemeClr></a:gs><a:gs pos='50000'><a:schemeClr val='phClr'><a:lumMod val='105000'/><a:satMod val='103000'/><a:tint val='73000'/></a:schemeClr></a:gs><a:gs pos='100000'><a:schemeClr val='phClr'><a:lumMod val='105000'/><a:satMod val='109000'/><a:tint val='81000'/></a:schemeClr></a:gs></a:gsLst><a:lin ang='5400000' scaled='0'/></a:gradFill><a:gradFill rotWithShape='1'><a:gsLst><a:gs pos='0'><a:schemeClr val='phClr'><a:satMod val='103000'/><a:lumMod val='102000'/><a:tint val='94000'/></a:schemeClr></a:gs><a:gs pos='50000'><a:schemeClr val='phClr'><a:satMod val='110000'/><a:lumMod val='100000'/><a:shade val='100000'/></a:schemeClr></a:gs><a:gs pos='100000'><a:schemeClr val='phClr'><a:lumMod val='99000'/><a:satMod val='120000'/><a:shade val='78000'/></a:schemeClr></a:gs></a:gsLst><a:lin ang='5400000' scaled='0'/></a:gradFill></a:fillStyleLst><a:lnStyleLst><a:ln w='6350' cap='flat' cmpd='sng' algn='ctr'><a:solidFill><a:schemeClr val='phClr'/></a:solidFill><a:prstDash val='solid'/><a:miter lim='800000'/></a:ln><a:ln w='12700' cap='flat' cmpd='sng' algn='ctr'><a:solidFill><a:schemeClr val='phClr'/></a:solidFill><a:prstDash val='solid'/><a:miter lim='800000'/></a:ln><a:ln w='19050' cap='flat' cmpd='sng' algn='ctr'><a:solidFill><a:schemeClr val='phClr'/></a:solidFill><a:prstDash val='solid'/><a:miter lim='800000'/></a:ln></a:lnStyleLst><a:effectStyleLst><a:effectStyle><a:effectLst/></a:effectStyle><a:effectStyle><a:effectLst/></a:effectStyle><a:effectStyle><a:effectLst><a:outerShdw blurRad='57150' dist='19050' dir='5400000' algn='ctr' rotWithShape='0'><a:srgbClr val='000000'><a:alpha val='63000'/></a:srgbClr></a:outerShdw></a:effectLst></a:effectStyle></a:effectStyleLst><a:bgFillStyleLst><a:solidFill><a:schemeClr val='phClr'/></a:solidFill><a:solidFill><a:schemeClr val='phClr'><a:tint val='95000'/><a:satMod val='170000'/></a:schemeClr></a:solidFill><a:gradFill rotWithShape='1'><a:gsLst><a:gs pos='0'><a:schemeClr val='phClr'><a:tint val='93000'/><a:satMod val='150000'/><a:shade val='98000'/><a:lumMod val='102000'/></a:schemeClr></a:gs><a:gs pos='50000'><a:schemeClr val='phClr'><a:tint val='98000'/><a:satMod val='130000'/><a:shade val='90000'/><a:lumMod val='103000'/></a:schemeClr></a:gs><a:gs pos='100000'><a:schemeClr val='phClr'><a:shade val='63000'/><a:satMod val='120000'/></a:schemeClr></a:gs></a:gsLst><a:lin ang='5400000' scaled='0'/></a:gradFill></a:bgFillStyleLst></a:fmtScheme></a:themeElements><a:objectDefaults/><a:extraClrSchemeLst/><a:extLst><a:ext uri='{05A4C25C-085E-4340-85A3-A5531E510DB2}'><thm15:themeFamily xmlns:thm15='http://schemas.microsoft.com/office/thememl/2012/main' name='Office Theme' id='{62F939B6-93AF-4DB8-9C6B-D6C7DFDC589F}' vid='{4A3C46E8-61CC-4603-A589-7422A47A8E4A}'/></a:ext></a:extLst></a:theme>"
ReplaceString(org_txt, "'", #DQUOTE$, #PB_String_InPlace)
n = StringByteLength(org_txt, #PB_UTF8)
Dim buffer(n-1)
PokeS(@buffer(0), org_txt, -1, #PB_UTF8|#PB_String_NoZero)
i = AddPackMemory(Pkr, @buffer(0), n, "xl/theme/theme1.xml" )
; xl/worksheets
;org_txt = "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" +#CRLF$+ "<worksheet xmlns='http://schemas.openxmlformats.org/spreadsheetml/2006/main' xmlns:r='http://schemas.openxmlformats.org/officeDocument/2006/relationships' xmlns:mc='http://schemas.openxmlformats.org/markup-compatibility/2006' mc:Ignorable='x14ac' xmlns:x14ac='http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac'><dimension ref='A1'/><sheetViews><sheetView tabSelected='1' workbookViewId='0'/></sheetViews><sheetFormatPr defaultRowHeight='15' x14ac:dyDescent='0.25'/><sheetData/><pageMargins left='0.7' right='0.7' top='0.75' bottom='0.75' header='0.3' footer='0.3'/></worksheet>"
;ReplaceString(org_txt, "'", #DQUOTE$, #PB_String_InPlace)
;n = StringByteLength(org_txt, #PB_Ascii)
;Dim buffer(n-1)
;PokeS(@buffer(0), org_txt, -1, #PB_Ascii|#PB_String_NoZero)
;i = AddPackMemory(Pkr, @buffer(0), n, "xl/worksheets/sheet1.xml" )
EndProcedure
;----------------------------------------------------------------------------
Procedure.i TextToXLSX(FileNameTxt.s, FileNameXLSX.s, ColSep.s = #TAB$)
Protected pth.s, fName.s, section.s, f1.s, f2.s
Protected pkr_nbr, n, j, Dim buffer.a(0)
fName = FileNameXLSX
pkr_nbr = CreatePack(#PB_Any, fName, #PB_PackerPlugin_Zip)
If pkr_nbr
FreshXLSX(pkr_nbr)
FirstPass(FileNameTxt, ColSep)
section = BuildSharedStrings()
n = StringByteLength(section, #PB_UTF8)
Dim buffer(n)
PokeS(@buffer(0), section, -1, #PB_UTF8)
j = AddPackMemory(pkr_nbr, @buffer(0), n, "xl/sharedStrings.xml" )
section = BuildSheet1Data(FileNameTxt, ColSep)
n = StringByteLength(section, #PB_UTF8)
Dim buffer(n)
PokeS(@buffer(0), section, -1, #PB_UTF8)
j = AddPackMemory(pkr_nbr, @buffer(0), n, "xl/worksheets/sheet1.xml" )
ClosePack(pkr_nbr)
EndIf
EndProcedure
EndModule
CompilerIf #PB_Compiler_IsMainFile
Global xlsx_file.s, text_file.s = GetTemporaryDirectory() + "test.txt"
Global fNbr, i,j,n,m, txt.s
n = 10 : m = 7
fNbr = CreateFile(#PB_Any, text_file)
If fNbr
For i=1 To n
txt = ""
For j=1 To m
txt = txt + "cell (" + Str(i) + ", " + Str(j) + ")"
If j < m : txt + #TAB$ : EndIf
Next
WriteStringN(fNbr, txt)
Next
;txt = "888 274 91626 274/23262 1.00 1.00 27/02/2017 64I/KUMER 1.56 AR-BLUE 28 OBSEQUIO CLIENTE 9E"
;WriteStringN(fNbr, txt)
CloseFile(fNbr)
EndIf
xlsx_file = GetTemporaryDirectory() + "test.xlsx"
XLSX::TextToXLSX(text_file, xlsx_file)
RunProgram( xlsx_file )
CompilerEndIf
Edit2: replaced again that same routine with a new and faster My_IsNumer() again .... hopefully the last one
... so i sincerely thank you for your that