ConnectToMicrosoftExcel: Difference between revisions
From Pickwiki
Jump to navigationJump to search
m link fix |
IanMcGowan (talk | contribs) Add options for creating XML spreadsheet as well as "true" XLSX, and links to code in [BasicSource] |
||
| Line 3: | Line 3: | ||
* Use the [[AccuTerm]] FTD utility to import and export with .xls files. [[AccuTerm]] also has [http://www.asent.com/code.html samples] to use Excel formulas to access MV. See | * Use the [[AccuTerm]] FTD utility to import and export with .xls files. [[AccuTerm]] also has [http://www.asent.com/code.html samples] to use Excel formulas to access MV. See | ||
* [http://nebula-rnd.com/products/xlite.htm <nowiki>NebulaXLite</nowiki>] allows creation of rich documents from BASIC, compared to plain data in cells. | * [http://nebula-rnd.com/products/xlite.htm <nowiki>NebulaXLite</nowiki>] allows creation of rich documents from BASIC, compared to plain data in cells. | ||
* Since Office 2003, Excel has supported so-called XML Spreadsheets, using a schema known as [https://msdn.microsoft.com/en-us/library/bb226687.aspx SpreadsheetML] - this is a simple approach that has the advantage of creating a single file, in a manageable format. There are a couple of downsides: 1) If you name the file .xml it opens in IE, but if you name it .xls it opens in Excel with a warning that may be alarming. 2) If you are exporting thousands of rows with dozens of columns, the resulting files can get quite large. Still, for modest needs this is an easy approach. [[SCI.XLS.RECALL]] has an example of a program that takes a delimited text file and converts it to an XML document suitable for loading into Excel. | |||
* Since Office 2010, the internal format used for Office documents has switched to a zipped collection of XML files. Since these are "just" text files, it's reasonable to attempt to produce them directly from a PICK Basic program, and this is certainly feasible. Trying to reverse-engineer the xlsx XML schema can be challenging - Excel throws everything and the kitchen sink into the zip file, and small errors lead to Excel rejecting the entire file. [http://polymathprogrammer.com/tag/openxml/ Polymath Programmer] has some excellent resources to explain what's going on at just the right level of detail. Well worth buying the [http://polymathprogrammer.com/about/ book], and the free [http://spreadsheetlight.com/ code] if you're using DotNet. [[SCI.XLSX.RECALL]] has an example of a more complicated program that takes a delimited file and turns it into a zipped collection of XML documents that can be open natively in Excel. | |||
== Importing data from Excel to MV == | == Importing data from Excel to MV == | ||
Latest revision as of 06:30, 3 March 2017
Exporting data from MV to Excel
- Create a CSV or other Tab-Delimited file. When a .csv file is opened with Excel you'll get a basic set of columns and rows.
- Use the AccuTerm FTD utility to import and export with .xls files. AccuTerm also has samples to use Excel formulas to access MV. See
- NebulaXLite allows creation of rich documents from BASIC, compared to plain data in cells.
- Since Office 2003, Excel has supported so-called XML Spreadsheets, using a schema known as SpreadsheetML - this is a simple approach that has the advantage of creating a single file, in a manageable format. There are a couple of downsides: 1) If you name the file .xml it opens in IE, but if you name it .xls it opens in Excel with a warning that may be alarming. 2) If you are exporting thousands of rows with dozens of columns, the resulting files can get quite large. Still, for modest needs this is an easy approach. SCI.XLS.RECALL has an example of a program that takes a delimited text file and converts it to an XML document suitable for loading into Excel.
- Since Office 2010, the internal format used for Office documents has switched to a zipped collection of XML files. Since these are "just" text files, it's reasonable to attempt to produce them directly from a PICK Basic program, and this is certainly feasible. Trying to reverse-engineer the xlsx XML schema can be challenging - Excel throws everything and the kitchen sink into the zip file, and small errors lead to Excel rejecting the entire file. Polymath Programmer has some excellent resources to explain what's going on at just the right level of detail. Well worth buying the book, and the free code if you're using DotNet. SCI.XLSX.RECALL has an example of a more complicated program that takes a delimited file and turns it into a zipped collection of XML documents that can be open natively in Excel.
Importing data from Excel to MV
- As above, see AccuTerm.
- See this blog entry for discussion of a possible future product as well as code to integrate Excel with MV via a web service: Search the blog for notes about integration with Microsoft Office applications like Outlook.
Direct Connectivity vs import/export
This can be accomplished with any connectivity code and Microsoft Office add-ins. Look into Microsoft VSTO (Visual Studio Tools for Office) to create the Excel integration piece. Other methods can be used such as XLAs or VBA macros. Excel development is a field unto itself. Use tools like UniObjects.NET, mv.NET, or ON.NET to do the actual connectivity directly into MV. For clients outside of the intranet, create the add-in with a web service interface and connect into any MV platform using standard web service methods. (TG)
See Also
There are various solutions on this site for converting Excel to CSV and parsing CSV to dynamic or dimensioned arrays.
Misc discussions
http://groups.google.com/groups?hl=en&q=group:comp.databases.pick+excel
Tags: excel csv spreadsheets
