phpoffice/phpspreadsheet is a successor of phpExcel. We recently developed an automated solution to pull all the backup success rate reports from Dell EMC Data Protection Advisor (DPA). The portal which displays the report was good but not enough to use that information further like sharing the reports, pivoting etc..

After trying several methods, found this library to be very useful. There aren’t many examples available on the internet to retrieve data from MySQL and write to XLSX file. Hope this blog might be helpful who would like to use this library for creating XLSX files. Fear not, it doesn’t throw errors while opening this file in MS Office Excel


Convert XLSX to HTML / MHT

During certain scenarios we want to automate file conversion for sending reports via email. If we need to send emails to external recipients; Macro enabled excel sheets might get blocked on the go.

To overcome such situations, I found a site to convert excel “XLSX” to “XLS”. I would like to thank to owner of the blog and courtesy to use the script.

But this may not help us since even XLS file extensions with Macros will be blocked by exchange. So I modified script to get HTM/MHT format which can easily be sent via emails without any hiccups.

VB Script is as below

Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(Wscript.Arguments(0))
objExcel.Application.Visible = False
objExcel.Application.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs Wscript.Arguments(1), 45
objExcel.Application.DisplayAlerts = True
‘ XLSX->XLS conversion script by Michael Kuzmin
‘ This is how you can you it:
‘ c:\xlsx2htm.vbs “C:\inputdocument.xlsx” “C:\outputdocument”
‘ file format numnber 57 is for pdf format
’44 is for html
’45 is for mht

‘end of script

Copy above script to notepad and save it as “cc:\xlsx2htm.vbs”

Open command prompt and type

c:\xlsx2htm.vbs “C:\inputdocument.xlsx” “C:\outputdocument

output file will be in “MHT” format.