how to write data of datagrid in file excel

[QuangNgo]

Hi guys,

I'm working on livecode project , I'm now having a problem of writing data of datagrid in file excel
Hope you guys give me some advice to fix that problem
I'm so appreciated !

Quang

[bangkok]

First method :

put the dgProp[ "column labels" ] of group "myDataGrid" into theColumnNames

replace cr with tab in theColumnNames

put the dgText of group "myDataGrid" into theText

ask file "Save file :"
put "file:"&it&".txt" into theFileName
put theColumnNames&CR&CR&theText into url theFileName

Method number 2 :

put empty into theText
put the dgData of group "myDataGrid" into theData
put the dgIndexes of group "myDataGrid" into theIndexes

repeat for each item theIndex in theIndexes
put theData[theIndex]["myCol1"] &tab&theData[theIndex]["myCol2"]& cr after theText
end repeat

ask file "Save file :"
put "file:"&it&".txt" into theFileName
put theText into url theFileName

[QuangNgo]

Thanks a lot bangkok
That's very useful !

Thanks again
Quang

[QuangNgo]

Hi bangkok

There is a small problem .I do follow your solution and It work well except one thing is file format.
In your code, I changed like this
ask file "Save file :"
put "file:"&it&".xlsx" into theFileName -----turn txt into xlsx (the error here file format or extension is not valid) but It's fine if the file extension is ".xls"
put theText into url theFileName

Is there any differences between them ? Could you explain it to me why?
Thanks again
Quang

[SparkOut]

MS Excel has a much more sophisticated file format than just setting the text. Simply setting the filename extension doesn't change the file format. To write a data file in Excel would involve reverse engineering the proprietary file format and working out how to convert the text data into the binary storage format, with all the delimiters and all the other file information required.
That is waaaay out of this scope, and not necessary at all. Excel will quite happily open a tab delimited text file and show on screen. You can then save the file in native Excel format from within the Excel interface.
If you are concerned that a double-click on your file with a .txt extension opens that file in Notepad or other text editor then you could perhaps try changing the filename extension to .tab which is also recognised by Excel. Otherwise (if you really have to) you could adapt bangkok's code to include a double quote before and after each data element, and change the delimeter to comma instead of tab. Then use .csv as the filename extension and you will probably find that Excel is the more usual default application that opens such files (.tab or .csv). Tab delimited is a much more robust solution than Comma Separated Values though.

[QuangNgo]

Hi SparkOut !

Thank you so much your advice That's very useful explaination

Thanks again both of you

Quang

[BarrySumpter]

First method :

Code: Select all

   put the dgProp[ "column labels" ] of group "myDataGrid" into theColumnNames

   replace cr with tab in theColumnNames

   put the   dgText of group "myDataGrid" into theText

   ask file "Save file :"
   put "file:" & it & ".txt" into theFileName
   put theColumnNames & CR & CR & theText into url theFileName

Method number 2 :

Code: Select all

   put empty into theText
   put the dgData of group "myDataGrid" into theData
   put the dgIndexes of group "myDataGrid" into theIndexes
   
   repeat for each item theIndex in theIndexes
      put theData[theIndex]["myCol1"] & tab & theData[theIndex]["myCol2"] & cr after theText
   end repeat

   ask file "Save file :"
   put "file:" & it &".txt" into theFileName
   put theText into url theFileName

[QuangNgo]

Hi Barry Sumpter

I had the solution to do it before but I'm so happy with your help

Thanks a lot ^^

Regards,
Quang

[BarrySumpter]

Should have added the note that I reposted that same code to make it easier for me to read.

Nice simple straightforward script.
Very nice.