from
Creating Excel File Through XML We all know that the only place to keep data is a database. Surprisingly, 150% of our customers firmly believe that data has to be stored in Excel sheets. As expected your customer rejected your first and quick attempt Ever since Excel XP (Excel 2000 with downloadable plugins) Excel supports XML files in XMLSS (XML Spreadsheet) format. That's almost a decade now! Even your most cost sensitive customer might have now replaced their Office Creating Excel filesIf you need to store data, of course, you store them in a table. At least that’s what every Visual FoxPro developer will tell you. Ask a .NET developer the same question and they’ll wonder why you would even consider something Our customers are business persons. The only place for data that one can take seriously is – one shouldn’t need to point out the obvious – naturally Excel. Excel drives businesses to a degree that most of us won’t even Under some conditions Barclay’s issues a voucher to its customer that they can redeem with British Airways. Employees track these conditions manually, and then send an email to British Airways where the recipient is entered Excel is everywhere. Excel makes data accessible. When you provider data to users in Excel format, they are usually happy, because now they can work with data. Data in a SQL server or in the application is locked away. Visual FoxPro offers multiple ways to export data to Excel. The simplest ones of them are the COPY TO and EXPORT commands. They directly support several older versions of Excel. Alternatively, you can create a tab delimited Office 2007 introduced a little change that makes this one tiny bit more difficult. Presumably for security reasons, Excel 2007 and Excel 2010 prompt for confirmation when you open a file where the file extension differs The quick and simple approaches are exactly this: quick and simple. There’s not a lot of choice you have regarding formatting or data types. Excel deducts the type from the content. If something looks like a number, it With ODBC and OLEDB you can directly write into Excel sheets. This gives you quite a bit of control over how data is interpreted and where data is placed in the document. There’s zilch control over any visual aspect, though. For many, many years automation was the only way to transfer data to Excel and control formatting at the same time. There are two kinds of automation. The older one of the two is Dynamic Data Exchange, DDE for short. The They aren’t known for being blazingly fast. None of them will work when Excel is not installed on the machine. In fact, even the version of Excel is often relevant. With automation you can’t ensure that no dialog comes Excel XP introduced a new storage format that solves all of these problems: XML. It wouldn’t be a true Microsoft format, if the format hadn’t changed over time. Excel XP and Excel 2003 use a syntax that is called XML Spreadsheet http://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx Excel 2007 and Excel 2010 open XMLSS files without any problem. The native format of these two versions, though, is Office Open XML. Do not confuse this with the similarly named Open Office XML. The latter is the native XMLSS is much more limited. Not even every feature of Excel supported. Missing are, for instance, pictures, embedded ActiveX controls or OLE objects, and VBA projects which means macro support. Lacking support for pictures For us XMLSS has one big advantage over OOXML. It’s dramatically easier to handle. You don’t need to generate dozens of different files and store them in a ZIP archive. XLSX files are in fact ZIP archives. XMLSS exists As mentioned previously, you don’t have to use XML as the file extension when you create XMLSS files. In the default configuration, opening a file with the XML extension would still open Microsoft Excel when the content Therefore it’s less confusing and more compatible when you use the familiar XLS extension for you XML files. Excel opens these as XML spreadsheets automatically. The simplest form of such an XML spreadsheet looks like <?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<Worksheet ss:Name="Sheet1">
</Worksheet>
</Workbook>
This defines a workbook with a single, empty worksheet. If you would save this document from Excel, you’d notice that Excel adds a real bunch of further information. All of those are optional and would only clutter the By starting with an empty work book I already broke with the habit of beginning with a „Hello World“ example. Let me provide you with one now: <?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<Worksheet ss:Name="Sheet1">
<Table>
<Row>
<Cell>
<Data ss:Type="String">Hello world</Data>
</Cell>
</Row>
</Table>
</Worksheet>
</Workbook>
Some attributes are required. For instance, on the <Data> tag you have to specify which type of data you want to store in the cell. This is contrary to Excel’s normal behavior, which is to figure out things based on the If you don’t specify the ss:Type attribute, Excel won’t open the document at all. Instead you get an error dialog. If you used automation to open the XLS file, Excel forwards the error to Visual FoxPro. It’s therefore If you write code that creates Excel files you’ll likely see this error message quite frequently. Excel is very picky about the data format in the XML file. Forget just a single attribute or tag, or misspell one, store This pickiness of Excel isn’t the result of a frustrated program manager at Microsoft who decided that developers need to share his pain. If you have worked with XML documents, you most likely used the DOM parser. DOM Excel uses a different mode called the SAX parser. .NET makes this parser available as the System.Xml.XmlReader class. This parser reads the entire XML document in a single pass top to bottom. Instead of asking for whatever What you gain is performance. Using this parser and insisting on tags being in a strict order makes it possible for Excel to open a 50 MB XML document within two seconds. With the DOM parser you would likely spend minutes Debugging Excel file is an art of its own. Fortunately, Excel provides some guidance when the file format is wrong. Unfortunately, this might be less than you expect. Excel creates a log file every time you open an invalid C:\Documents and Settings\<user>\Local Settings\Temporary Internet Files\Content.MSO
On Windows Vista and Windows 7 you’ll find this file in the following directory: C:\Users\<user>\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO
As I’ve mentioned above the log file does contain any details about the error, the actual position, etc. The error log only specifies the tag and, if applicable, the attribute or the value that caused the problem. However, Right now our sample worksheet doesn’t look particular exciting. Let’s therefore start exploiting some more Excel features step by step. The content consists of rows and cells. You definitely noticed that this doesn’t leave any room for columns. There’s a separate tag for everything you can change on the column level. The most frequent setting needed for <Table>
<Column ss:Width="200"/>
<Row>…</Row>
</Table>
Excel expects that you pass all sizes and positions in points, not in pixels. A point is a typographical unit that is 1/72th of an inch. The actual pixel value depends on the resolution of your monitor. A standard resolution pixels = points * 96/72
points = pixels * 72/96
The column we defined in the previous sample is therefore 267 pixels wide. Other options available on the column level include visibility, automatic resizing, and so on. To define multiple columns you simply add multiple The physical order of the <Column> tags defines the order of the columns in the Excel sheet. Many tags accept the ss:Index attribute. With this tag you can skip one or multiple columns. For instance, if you want to define <Column ss:Width="200"/>
<Column ss:Index="10" ss:Width="200"/>
XMLSS identifies columns by numbers. Numbering starts with 1, not like in .NET with 0. Column number 10 is the same as the J column in Excel. You will likely use the ss:Index attribute quite a bit with the <Cell> tag. Just link FoxPro distinguishes between EMPTY() and ISBLANK(), Excel keeps track of whether a cell is used or not, even if the content is empty in both cases. To leave a cell totally empty and undefined, you have to skip Within a <Cell> tag you use the <Data> tag to define the content of a cell, as well as the type. The three most common data types are strings, numbers and date values. <Row>
<Cell><Data ss:Type="String">Hello world</Data></Cell>
<Cell>
<Data
ss:Type="DateTime">2008-05-01T00:00:00.000</Data>
</Cell>
<Cell><Data ss:Type="Number">123.456</Data></Cell>
</Row>
There a number of rules regarding the content of a cell that you have to follow. Everything between <Data> and </Data> is the content. First of all, the content must not have any additional blanks or line breaks. For this <Data ss:Type="DateTime">
2008-05-01T00:00:00.000
</Data>
The line above will cause an error when you attempt to open the document. Excel won’t tell you which line caused the error. At least when the error is in the <Data> tag, you get an error message that actually contains XML ERROR in Table
REASON: Bad Value
FILE: C:\Users\Christof\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO\47D0ED28.xls
GROUP: Cell
TAG: Data
VALUE: 2008-05-01T00:00:00.000
The content between <Data> and </Data> must be convertible according to the data type. There’s no cleanup performed by Excel before the conversion takes place. All string values have to be provided in UTF-8 format. For English text UTF-8 and ANSI are identical. However, umlauts used in German texts, accented characters used in every country around the US, require a conversion. [<Data ss:Type="String">]+STRCONV(cData,9)+[</Data>]
If you pass a field to STRCONV(), make sure you trim the value with the ALLTRIM() function. Otherwise the cell content might look strange, especially when the cell is not left aligned. Numerical values must use a decimal If your application uses SET SYSFORMATS ON to respect the users setting, or defines SET POINT, you need to reset this value to “.” for the export process. Even if your application only runs in the US and you don’t have Local lcPoint
lcPoint = Set("Point")
Set Point To "."
With this setting in place you can convert numerical values with a simple call to the TRANSFORM() function. [<Data ss:Type="Number">]+TRANSFORM(nData)+[</Data>]
Numbers aren’t optional! If you turn a cell into a numeric value, you must provide a number. If you don’t have a value, the zero is a good default value. You can’t leave the tag empty or specify something else. This is TRANSFORM( NVL(nData,0) )
Excel saves date values as integer numbers. This number is the number of days since a particular date. When you store a date value in an XML spreadsheet, though, you have to use the standard XML date format. Fortunately, [<Data ss:Type="DateTime">]+TTOC(tData,3)+[</Data>]
Here comes the confusing part: Even though you specified the data type as date and you passed a date value in XML format, what you seen when you open the spreadsheet in Excel is a mere number. Data values are not automatically Excel uses styles similar to Word or CSS. However, Excel offers significantly less flexibility. Styles are defined in a <Styles> tag on the work book level. All sheets within a work book are using the same style. Every <Workbook
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet
<Styles>
<Style ss:ID="styleDemo">
<NumberFormat ss:Format="Standard"/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">…</Worksheet>
</Workbook>
Styles in Excel do not support inheritance, at all. Moreover, you can only assign one style to each cell. Imagine, you have a cell with a complex format involving conditions, a background pattern, a border, and so on. Excel names all styles as “S” followed by an incrementing number. You don’t have to follow this example. Just like you don’t name variable lc1 to lc200 in your application, naming styles this way makes them a maintenance Every time you save a file, Excel completely regenerates the list of styles. Styles that are not used anywhere in the spread sheet are removed from the file. All others styles are named as Excel would name them, that is, When developers settle on names, those aren’t necessarily the best choices. <NumberFormat> is such a tag where name must have been chosen by a developer without reflecting the choice with other team members. You can think If you want to display the date value from one of our previous samples as “01-Mai-2008” (that’s German, not a spelling error), you have to enter the following code into an English version of Excel when the regional settings [$-409]T-MMM-JJJJ;@
Excel is extremely language dependent. Some of this will bite you when you deal with different versions of Excel or different regional settings. But it’s not that time yet. For now you can be lucky, as formats are stored <Style ss:ID="s22">
<NumberFormat ss:Format="[ENG][$-409]d\-mmm\-yyyy;@"/>
</Style>
You can find the correct format string using trial and error. I personally find it more efficient to format the cell in Excel in the way I want it. Then save the file as XML Spreadsheet and take a look at the style definition This becomes a bit easier when you format the cell with a font that you don’t use anywhere else in the Excel sheet. With this you can open the generated XML file and merely search for the font name to quickly identify To format a cell with a certain style you pass the ID of the style to the <Cell> tag’s styleID attribute: <Cell ss:StyleID="s22"><Data ss:Type="Da…</Data></Cell>
You cannot format a cell without using a style. In HTML and CSS you have the choice of creating a CSS selector with the style information, writing the format into the style attribute or use one of the older HTML tags like Aside from the format string you can also change the font, style and size in an XMLSS file. Within the <Style> tag you use the <Font> tag to alter the font settings: <Style ss:ID="s22">
<Font ss:FontName="Tahoma" ss:Size="13"
ss:Bold="1" ss:Color="silver"/>
</Style>
Switches such as ss:Bold, ss:Italic, ss:Outline, ss:Shadow, ss:StrikeThrough, have a value of one, when the style is active, or zero when the style is deactivated. Zero is the default value for all of them. An exception Excel shines when it comes to false friends. There was the <NumberFormat> tag that doesn’t deal with numbers, the date format that doesn’t show up as a date, empty cells that cannot be empty, and so forth. Colors are similar. Within the XML files, Excel uses a hexadecimal notation that is similar to HTML. For instance, the value #FF0000 denotes red. Behind the #-sign, you specify the values for red, blue and green. Each is a two-digit hexadecimal http://msdn.microsoft.com/en-us/library/aa358802(VS.85).aspx Unfortunately, the tool tip names in the color selection dialog of Excel do not match these names, so you can deduct from the user interface what values you have to use in the XML file to obtain the same effect. The possibility to specify hex values makes Excel look much more flexible than it actually is. FoxPro 2.x used color sets to specify the color for various elements. Excel calls them palettes, but the concept is the same. Whenever you specify a color in hexadecimal notation in one of the styles, Excel searches the list of all 56 available colors. When there is no exact match, Excel will automatically use the closest color. Before you can <Workbook>
<Colors>
<Color>
<Index>37</Index>
<RGB>#D4CAFA</RGB>
</Color>
</Colors>
<Styles>…</Styles>
<Worksheet ss:Name="Sheet1">…</Worksheet>
</Workbook>
Index can be a number between 1 and 56. RGB is the desired color. You cannot use more than 56 different colors in one Excel workbook. There are many more options to format a spread sheet. You can change the background of a cell, define the margin for every side of the cell, define a print area, select the active cell when you the spread sheet, and much XML files are pure text files. Visual FoxPro provides extremely flexible ways to generate these text files. You can create them entirely programmatically from the ground up, or you use TEXTMERGE together with templates. The following sample generates a formatted Excel sheet from the Northwind Customer table where every second line is differently colored: Use Northwind\Customers
Set Point To "."
Local lcFile lcFile = GetEnv("USERPROFILE")+"\Desktop\Customers.xls"
If File(m.lcFile) Erase (m.lcFile)
EndIf
Local lcRows, lnField, luValue, lcStyle, lcData lcRows = ""
Scan
lcRows = m.lcRows + "<Row>" For lnField = 1 to Fcount()
luValue = Evaluate(Field(m.lnField))
lcStyle = Iif(Recno()%2==0,"even","odd")
Do case
Case InList(Vartype(m.luValue),"C")
lcData = ;
[<Data ss:Type="String">]+Strconv(Alltrim(m.luValue),9)+[</Data>]
Case InList(Vartype(m.luValue),"N")
lcData = ;
[<Data ss:Type="Number">]+Transform(Nvl(m.luValue,0))+[</Data]
Otherwise
Loop
EndCase
lcRows = m.lcRows + ; [<Cell ss:StyleID="]+m.lcStyle+[">]+m.lcData+[</Cell>]
EndFor
lcRows = m.lcRows + "</Row>" endscan
Local lcXML
Text to m.lcXML Noshow Textmerge
<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<Styles>
<Style ss:ID="even">
<Font ss:FontName="Tahoma" ss:Size="13" ss:Bold="1" />
</Style>
<Style ss:ID="odd">
<Font ss:FontName="Tahoma" ss:Size="13" ss:Color="red" />
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table><<m.lcRows>></Table>
</Worksheet>
</Workbook>
EndText
StrToFile(m.lcXml,m.lcFile) |