[SOLVED] Excel XLS/XLSM Read/Write without Office installed

[hunte922]

I am using an Excel file as a database for a point of sales system.

I only need to view the database using Office on one computer. All others only need to read/write to the file.

Is there a way to read and write to XLS or XLSM files without installing Office? Changing the file type is okay as long as it can be opened in Excel and has all the same features as an XLS or XLSM file.

Thanks in advance.

Hunter

 

Edit: Solution found

Edited March 26, 2016 by hunte922

[water]

You could use ADO to access Excel like a database.
Either check my ADO wiki tutorial or check the ADO UDF written by mLipok.

There are other UDFs available which are limited just to XLSX data type.

[hunte922]

  On 3/25/2016 at 8:22 PM, water said:

You could use ADO to access Excel like a database.
Either check my ADO wiki tutorial or check the ADO UDF written by mLipok.

There are other UDFs available which are limited just to XLSX data type.

Expand  

In your "ADO Example Excel", how can I read cells past the 2nd column? I can only get A:B.

[mLipok]

@hunte922

read here: https://www.connectionstrings.com/excel-2007-odbc/

To learn how to use it you should use this example:

Func _Example_MSExcel()

    Local $sFileFullPath = Default ; Here put FileFullPath to your Access File or use Default to open FileOpenDialog
    Local $sProvider = Default
    Local $sExtProperties = Default
    Local $HDR = Default
    Local $IMEX = Default

    Local $sConnectionString = _ADO_ConnectionString_Excel($sFileFullPath = Default, $sProvider, $sExtProperties, $HDR, $IMEX)

    _Example_1_RecordsetToConsole($sConnectionString, "select * from [Sheet1$]")
    _Example_2_RecordsetDisplay($sConnectionString, "select * from [Sheet1$]")
    _Example_3_ConnectionProperties($sConnectionString)

EndFunc   ;==>_Example_MSExcel

 

[hunte922]

  On 3/25/2016 at 9:25 PM, mLipok said:

@hunte922

read here: https://www.connectionstrings.com/excel-2007-odbc/

To learn how to use it you should use this example:

Func _Example_MSExcel()

    Local $sFileFullPath = Default ; Here put FileFullPath to your Access File or use Default to open FileOpenDialog
    Local $sProvider = Default
    Local $sExtProperties = Default
    Local $HDR = Default
    Local $IMEX = Default

    Local $sConnectionString = _ADO_ConnectionString_Excel($sFileFullPath = Default, $sProvider, $sExtProperties, $HDR, $IMEX)

    _Example_1_RecordsetToConsole($sConnectionString, "select * from [Sheet1$]")
    _Example_2_RecordsetDisplay($sConnectionString, "select * from [Sheet1$]")
    _Example_3_ConnectionProperties($sConnectionString)

EndFunc   ;==>_Example_MSExcel

 

Expand  

I'm getting this error when I run the example, with or without $sFileFullPath defined:

###############################
ADO.au3 v.2.1.13 BETA (1155) : ==> COM Error intercepted !
$oADO_Error.description is:     The Microsoft Jet database engine could not find the object 'Sheet1$'.  Make sure the object exists and that you spell its name and the path name correctly.
$oADO_Error.windescription:     Exception occurred.

$oADO_Error.number is:  80020009
$oADO_Error.lastdllerror is:    0
$oADO_Error.scriptline is:  1155
$oADO_Error.source is:  Microsoft JET Database Engine
$oADO_Error.helpfile is:    
$oADO_Error.helpcontext is:     5003011
###############################

 

Edited March 25, 2016 by hunte922

[mLipok]

Do you have sheet named "Sheet1" in your XLS file ?

[hunte922]

  On 3/25/2016 at 9:41 PM, mLipok said:

Do you have sheet named "Sheet1" in your XLS file ?

Expand  

Yes.

[willichan]

Although you can use ADO to access the data from an Excel file without opening the Excel application, to my understanding, you still need to have Excel (or rather Office, since other Office applications also use Excel objects) installed on the machine running the code in order for the Excel object to be available for use.  Else-wise, how would the system know how to access newer excel file versions/formats, if the files needed for it do not come from the installation of the version of Excel that defines that new file version/format.

If Windows came with the objects readily available, nothing would stop developers from creating fully MS Office file compatible applications without anyone having to pay Microsoft for their commercial (not-free) Office at all.  Office is Microsoft's 2nd largest revenue source right now (according to my reading).  They are not going to give it away for free.

Other applications that are able to read/write Office file formats have either had to reverse engineer the file formats, or pay licensing fees to Microsoft to do so.

Since the OP is stipulating "without installing Office", the ADO solution is not going to work.

Recommendations:

• If you do not want to have to pay for Office licenses on all of the machines, you might want to look into using SQLite, or some other data storage method.  You can always use ODBC on the one machine that needs to look at the data in Excel to pull the data into an Excel file.
• The other concern you will need to address is that if multiple PCs are attempting to access/update the Excel file concurrently, you will need to implement some form of file locking. (This is also true if using other non-client-server based solutions, including SQLite)  You can look at my File Locking with Cooperative Semaphores for one possible solution to that problem.

[mLipok]

2007 Office System Driver: Data Connectivity Components
https://www.microsoft.com/en-us/download/details.aspx?id=23734

 

  Quote

1.    INSTALLATION AND USE RIGHTS.  You may install and use any number of copies of the software on your devices to design, develop and test your programs that read or write files in a Microsoft Office file format.  You may not use the software to develop a general purpose word processing, spreadsheet or database management system, and you may not use the software in your programs as a data store.

Expand  

 

ps.

Sorry for long waiting ( I was watching "Doctor Who" season 8 "part" 8 , with the "moon story" ).

 

EDIT:

Microsoft Access Database Engine 2010 Redistributable (EDIT: If I understand well, this have more Dev Friendly license)
https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255
 

Managing Data Sources
https://msdn.microsoft.com/en-us/library/ms712362(v=vs.85).aspx

About Drivers and Data Sources
https://msdn.microsoft.com/en-us/library/ms710285(v=vs.85).aspx

 

Edited March 25, 2016 by mLipok

[mLipok]

@hunte922

use _ADO_GetProvidersList() to display list of provider instaled on your machine.
Post here content from _ArrayDisplay()

Also post entire SciTE console output from your testing case from post #5

 

Edited March 25, 2016 by mLipok

[hunte922]

  On 3/25/2016 at 11:20 PM, mLipok said:

@hunte922

use _ADO_GetProvidersList() to display list of provider instaled on your machine.
Post here content from _ArrayDisplay()

Also post entire SciTE console output from your testing case from post #5

Expand  

No need - I've already figured out how to get it to work. The only problem now is getting Microsoft Access Database Engine 2010 to process Excel files without having it installed. Do you think you know a quick fix?

Func _ADOExcel_Open($sFileName, $readonly = "True")
   Global $ADO_Connection = ObjCreate("ADODB.Connection")
   Local $ADO_ConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' & $sFilename & ';Extended Properties="Excel 8.0;HDR=No;ReadOnly=' & $readonly & '"'
   $ADO_Connection.Open($ADO_ConnectionString)
EndFunc

 

[InunoTaishou]

I'm a little late but just to let you know that those xls (well all office documents) are just zip files with files that control how the data is formatted in the corresponding office program.

If you rename the .xls to .zip and open it up, there is a file called sharedStrings.xml that has all the data for the xls file and the sheet1.xml file has the row and column for the corresponding data. I've never tried to put it together but if you get the ADO working you could probably figure it out, or someone from google already has and posted some tutorial for deciphering it.

[mLipok]

@hunte922 If you need to install  Microsoft Access Database Engine 2010 then there is no other way but you must read carefull the license from this MS software.

@InunoTaishou so try it with: https://www.autoitscript.com/w/images/6/6e/ADO_Example_Excel.xls
As I think you mean XLSX , but this is not the sam thing as XLS .

[hunte922]

I got it to work. Finally.

It's 3 AM.

I made the change from XLS to XLSX which brought some extra, frustrating problems related to cell writing.

I also had to install the 32-bit version of Microsoft Access Database Engine 2010 (I originally had the 64-bit version).

Here's what I've done:

expandcollapse popup

#include <ADO_CONSTANTS.au3>

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")
Func MyErrFunc()
  Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"    & @CRLF  & @CRLF & _
             "err.description is: " & @TAB & $oMyError.description  & @CRLF & _
             "err.windescription:"   & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "       & @TAB & hex($oMyError.number,8)  & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "   & @TAB & $oMyError.scriptline   & @CRLF & _
             "err.source is: "       & @TAB & $oMyError.source       & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile     & @CRLF & _
             "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
            )
Endfunc

Func _ADOExcel_Open($sFileName, $readonly = "True")
   Global $ADO_Connection = ObjCreate("ADODB.Connection")
   Local $ADO_ConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' & $sFilename & ';Extended Properties="Excel 12.0;HDR=NO;IMEX=0;ReadOnly=' & $readonly & '"'
   $ADO_Connection.Open($ADO_ConnectionString)
EndFunc

Func _ADOExcel_SheetRead()
   Local $ADO_Recordset = ObjCreate("ADODB.Recordset")
   Local $ADO_SQL_Query = "Select * FROM [Sheet1$]"
   $ADO_Recordset.Open($ADO_SQL_Query, $ADO_Connection, $ADO_adOpenForwardOnly, $ADO_adLockReadOnly, $ADO_adCmdTableDirect)
   Local $aArray = $ADO_Recordset.GetRows
   $ADO_Recordset.Close
   Return $aArray
EndFunc

Func _ADOExcel_CellRead($col, $row)
   Local $ADO_Recordset = ObjCreate("ADODB.Recordset")
   Local $ADO_SQL_Query = "Select * FROM [Sheet1$]"
   $ADO_Recordset.Open($ADO_SQL_Query, $ADO_Connection, $ADO_adOpenForwardOnly, $ADO_adLockReadOnly, $ADO_adCmdTableDirect)
   Local $aArray = $ADO_Recordset.GetRows
   $ADO_Recordset.Close
   Return $aArray[$row - 1][$col]
EndFunc

Func _ADOExcel_CellWrite($col, $row, $value = "")
   Local $ADO_Recordset = ObjCreate("ADODB.Recordset")
   Local $ADO_SQL_Query = "Select * FROM [Sheet1$]"
   $ADO_Recordset.Open($ADO_SQL_Query, $ADO_Connection, $ADO_adOpenKeyset, $ADO_adLockOptimistic, $ADO_adCmdText)
   $ADO_Recordset.Move($row - 1)
   $ADO_Recordset.Update($col, $value)
   $ADO_Recordset.Close
EndFunc

Func _ADOExcel_Close()
   $ADO_Connection.Close
   $ADO_Connection = Null
EndFunc

 

Edited March 26, 2016 by hunte922

[willichan]

Glad you were able to make it work.  Don't forget to look into the concurrency issue if multiple machines have a potential to update the files.