Hi everyone,
I have 3 arrays that I need to turn into a 3 column excel spreadsheet. I’ve done some searching but haven’t found a concrete answer that doesn’t involve plugins that are pretty old. Is there a recommended way to do this?
Thanks,
Matt
- save it as a CSV (or other delimited file)… load it into Excel
- save it as a formatted HTML table… load it into Excel
Hi Matt,
Do you mean something like this code?
[code] //Create three arrays
Dim A() as String
Dim B() as String
Dim C() as String
//Populate the three arrays
Dim i as Integer
For i = 0 to 20
A.Append i.ToText
Next i
For i = 30 to 50
B.Append Chr(i)
Next i
For i = 50 to 70
C.Append i.ToText
Next i
//Open Excel and put array info in
Dim excel as new ExcelApplication
excel.Workbooks.Add
excel.Visible = true
For i = 0 to 20
excel.Range(“A”+CStr(i+1)).Value = A(i)
excel.Range(“B”+CStr(i+1)).Value = B(i)
excel.Range(“C”+CStr(i+1)).Value = C(i)
Next i[/code]
I believe most of the old code still works in Xojo.
The above code does require a plugin that is packaged with Xojo.
Just copy the MSOfficeAutomation.rbx plugin from the Windows directory: C:\Program Files (x86)\Xojo\Xojo 2016r2\Extras\Office Automation
and paste the file into the Windows directory: C:\Program Files (x86)\Xojo\Xojo 2016r2\Plugins
Restart Xojo and you are able to write and use this code, as well as much of the other code in this forum. 
Happy to help.
CSV works OK but if you ever try out LibXL you won’t want to use anything else. It can read and write ANYTHING you want in an Excel file. You read and write values, formulas, formats on multiple tabs.
It is not free and you will also need a Monkeybread license but the possibilities are limitless … all without having Excel installed.
You could also generate an Excel compatible XML file, which would allow for the inclusion of text formatting and formulas, and wouldn’t require a plugin.
I mentioned xml in my previous post, and I realize that an xml file may be of no interest. Fair enough. Or, it may have just caused people to flee in terror, xml being what it is. However, I’ve found that if you overlook the massive amount of textual detritus that accompanies the actual data, it’s surprisingly easy to generate an xml file. The trick is to store the boilerplate header and trailer code in a couple of strings. Hence:
Sub ArrayToExcel(output As TextOutputStream, myData(,) As String)
//
// converts a 2 dimensional array 'myData' to xml format Excel file
//
// Note, output file extension should be .xml
//
dim i,j As Integer
dim rowstart,rowend,cellstart,cellend,xmlHeader,xmlTrailer,quote As String
quote=chr(34)
xmlHeader="<?xml version='1.0' encoding='UTF-8'?><?mso-application progid='Excel.Sheet'?><Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet' xmlns:c='urn:schemas-microsoft-com:office:component:spreadsheet' xmlns:html='http://www.w3.org/TR/REC-html40' xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:x2='http://schemas.microsoft.com/office/excel/2003/xml' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'><OfficeDocumentSettings xmlns='urn:schemas-microsoft-com:office:office'><Colors><Color><Index>3</Index><RGB>#c0c0c0</RGB></Color><Color><Index>4</Index><RGB>#ff0000</RGB></Color></Colors></OfficeDocumentSettings><ExcelWorkbook xmlns='urn:schemas-microsoft-com:office:excel'><WindowHeight>9000</WindowHeight><WindowWidth>13860</WindowWidth><WindowTopX>240</WindowTopX><WindowTopY>75</WindowTopY><ProtectStructure>False</ProtectStructure><ProtectWindows>False</ProtectWindows></ExcelWorkbook><Styles><Style ss:ID='Default' ss:Name='Default'/><Style ss:ID='Result' ss:Name='Result'><Font ss:Bold='1' ss:Italic='1' ss:Underline='Single'/></Style><Style ss:ID='Result2' ss:Name='Result2'><Font ss:Bold='1' ss:Italic='1' ss:Underline='Single'/><NumberFormat ss:Format='Currency'/></Style><Style ss:ID='Heading' ss:Name='Heading'><Font ss:Bold='1' ss:Italic='1' ss:Size='16'/></Style><Style ss:ID='Heading1' ss:Name='Heading1'><Font ss:Bold='1' ss:Italic='1' ss:Size='16'/></Style><Style ss:ID='co1'/><Style ss:ID='ta1'/><Style ss:ID='ce1'><Font ss:Bold='1' ss:Italic='1'/></Style></Styles><ss:Worksheet ss:Name='Sheet1'><Table ss:StyleID='ta1'><Column ss:Span='2' ss:Width='64.00'/>"
xmlHeader=ReplaceAll(xmlHeader,"'",quote)
xmlTrailer="</Table><x:WorksheetOptions/></ss:Worksheet></Workbook>"
//Miscellaneous xml tags
rowstart=ReplaceAll("<Row ss:Height='13.0'>","'",quote)
rowend="</Row>"
cellstart=ReplaceAll("<Cell><Data ss:Type='String'>","'",quote)
cellend="</Data></Cell>"
//
// Code to output array data
//
output.Write(xmlHeader)
for i=0 to UBound(myData,1)
output.Write rowstart
for j=0 to UBound(myData,2)
output.Write (cellstart+myData(i,j)+cellend)
next
output.Write rowend
next
// Write closing xml
output.Write(xmlTrailer)
End Sub
In this example I assumed that all columns in the spreadsheet will be strings. To use numbers instead, change the text in the variable cellstart from ‘String’ to ‘Number’
Beware of a recent Microsoft security patch which prevents opening these and Excel HTML except from safe locations. The team who made the patch are working on another patch to fix the error.
Patchwork ?
