**********************************************************************************************
It came to me a couple of days ago though.
It is quite old specifications (Let's say millions of years or so..)
Just have a (archeological) fun.
That is my real intention here.
**********************************************************************************************
I wrote a brief code for fun.
It reads xlsx file's content.
(strings and numbers-simply data.)
First of all.
I made a testing.xlsx file.
It has 100,000 cells within a sheet.
Each of it has 10 alpha-numeric random characters.
For instance, "d4j9415852".
And all of the cells are unique (no duplications).
Now.
I tried to extract its contents and made a txt file.
When I run the COM PIA codes.
It took me 24.9 secs.
And, this "Fast" codes took only 0.8 sec.
OMG, it is 30 times faster.
That is good, real good !!!
Though, it is just a level of pseudo code, it works.
Regards
COM PIA codes (it is slow, too slow)
Code: Select all
myExcel := "Testing.xlsx"
startTime := A_TickCount
oExcel := ComObjCreate( "Excel.Application" )
oExcel.Workbooks.Open( myExcel )
For eachCell In oExcel.ActiveSheet.UsedRange.Columns(1).Cells
myContents .= eachCell.Text "`n"
oExcel.Quit
oExcel =
resultFile := RegExReplace( myExcel, "\.xlsx") "_resultFile.txt"
FileAppend,% myContents, % resultFile
;Run, % resultFile,,, Max
MsgBox % A_TickCount - startTime
Code: Select all
; [Updated 2019 January 18] This is OLD Code
myExcel := "Testing.xlsx"
startTime := A_TickCount
tempFolder := RegExReplace( myExcel, ".*\K\\.*") "\_Excel_UnZip\"
tempName := RegExReplace( myExcel, "\.xlsx") ".zip"
FileCopy, % myExcel , % tempName
FileCreateDir, % tempFolder
tempObject := ComObjCreate("Shell.Application")
tempObject.Namespace(tempFolder).CopyHere( tempObject.Namespace(tempName).items, 4|16)
FileDelete, % tempName
FileEncoding, UTF-8
FileRead, contentNumber, % tempFolder "\" "xl\worksheets\sheet1.xml"
FileRead, contentString, % tempFolder "\" "xl\sharedStrings.xml"
stringArray := []
While @ := RegExMatch( contentString, "<t>(.+?)</t>", _, @ ? StrLen(_) + @ : 1 )
stringArray.Insert( _1)
myPattern := "<c r=""[A-Z]+[0-9]+""( t=""s"")?><v>(.+?)</v></c>"
While @ := RegExMatch( contentNumber, myPattern, _, @ ? StrLen(_) + @ : 1 )
myContents .= ( StrLen( _1) ? stringArray[1+_2] : _2 ) "`n"
FileRemoveDir, % tempFolder, 1
resultFile := RegExReplace( myExcel, "\.xlsx") "_Extracted.txt"
FileAppend,% myContents, % resultFile
;Run, % resultFile,,, Max
MsgBox % A_TickCount - startTime
[Updated 2019 January 18]
This time, just collects contents.
-fixed "...cells is formatted (Bold/Italic/Underlined/formatted as text/foreground color/background color etc.) it will be skipped.."
Code: Select all
myExcel := "Testing.xlsx"
tempFolder := RegExReplace(myExcel, ".*\K\\.*") "\_Excel_UnZip\"
tempName := RegExReplace(myExcel, "\.xlsx") ".zip"
FileCopy, % myExcel , % tempName
FileCreateDir, % tempFolder
tempObject := ComObjCreate("Shell.Application")
tempObject.Namespace(tempFolder).CopyHere(tempObject.Namespace(tempName).items, 4|16)
FileDelete, % tempName
FileEncoding, UTF-8
FileRead, myCNT, % tempFolder "\" "xl\worksheets\sheet1.xml"
FileRead, mySST, % tempFolder "\" "xl\sharedStrings.xml"
FileRemoveDir, % tempFolder, 1
sharedStringArray := []
While @ := RegExMatch(mySST, "<t[^>]*>(.+?)</t>", _, @ ? StrLen(_) + @ : 1)
sharedStringArray.Push(_1)
While @ := RegExMatch(myCNT, "<c r=""([A-Z]+)(\d+)""[^>]*?(t=""s"")?><v>(.+?)</v>", _, @ ? StrLen(_) + @ : 1)
{
rowIndex := _2 ; 1, 2, 3... 1048576
columnName := _1 ; A, B...AA, AB,... XFD
If(columnName = "A") ; at the moment column "A" Only
myResult .= (_3 ? sharedStringArray[1 + _4] : _4) "`n"
}
MsgBox % myResult
-I am not good at numbers in Excel. For instance;
"Excel cell values" --> "Values in Here"
"0.01" --> "0.01" It is OK. No problem at all
"0.002" --> "2.0000000000000001E-3" It starts problem here. Suddenly changed into Scientific. What the heck.. ?
"0.0034" --> "3.3999999999999998E-3" Woooo... It is the worst I have ever seen. I just gave it up.
When you are going to use someone else's script, Please just leave a brief comment saying thank you.
타인의 스크립트를 이용할 때는 최소한의 감사 표시를 남기시기 바랍니다. 개싸가지 도적질은 그만 하시고..