Create Excel Supported XML in iPhone using Xcode.

Published:
This article guides you how to create Excel readable XML format using xcode. Here are some points you must take care -

First create a NSMutableString variable

NSMutableString *excel = [[NSMutableString alloc] init];
and  then build up your string with a header, then the spreadsheet header, then data and finally a closing section.

1. Header - Below lines should be added to the start of XML string -

      [excel appendString:@"<?xml version=\"1.0\"?>\n<?mso-application progid=\"Excel.Sheet\"?>\n<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" "];
[excel appendString:@"xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:html=\"http://www.w3.org/TR/REC-html40\">\n<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">"];
[excel appendString:@"<LastAuthor>Kuzora</LastAuthor>"];
[excel appendString:[NSString stringWithFormat:@"<Created>%@</Created>",[NSDate date]]];
[excel appendString:@"<Version>11.5606</Version>\n</DocumentProperties>\n<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">\n<WindowHeight>6690</WindowHeight>\n<WindowWidth>14355</WindowWidth>"];
[excel appendString:@"<WindowTopX>360</WindowTopX>\n<WindowTopY>75</WindowTopY>\n<ProtectStructure>False</ProtectStructure>\n<ProtectWindows>False</ProtectWindows>\n</ExcelWorkbook>\n<Styles>"];
[excel appendString:@"<Style ss:ID=\"Default\" ss:Name=\"Normal\">\n<Alignment ss:Vertical=\"Bottom\"/>\n<Borders/>\n<Font/>\n<Interior/>\n<NumberFormat/>\n<Protection/>\n</Style>"];
[excel appendString:@"<Style ss:ID=\"s21\">\n<NumberFormat ss:Format=\"Medium Date\"/>\n</Style><Style ss:ID=\"s22\">\n<NumberFormat ss:Format=\"Short Date\"/>\n</Style></Styles>"];
            
2. Add the following code to create Worksheets and Table headings -
[excel appendString:@"<Worksheet ss:Name=\"User\">"];
                  [excel appendString:@"<Table ss:ExpandedColumnCount=\"8\" ss:ExpandedRowCount=\"2\" x:FullColumns=\"1\" x:FullRows=\"1\">"];
                  [excel appendString:@"<Row>"];
                  [excel appendString:@"<Cell><Data ss:Type=\"String\">TraineeDetailID</Data></Cell>"];
                  [excel appendString:@"<Cell><Data ss:Type=\"String\">Name</Data></Cell>"];
                  [excel appendString:@"<Cell><Data ss:Type=\"String\">DOB</Data></Cell>"];
                  [excel appendString:@"<Cell><Data ss:Type=\"String\">HeightFeet</Data></Cell>"];    
                  [excel appendString:@"<Cell><Data ss:Type=\"String\">HeightInches</Data></Cell>"];
                  [excel appendString:@"<Cell><Data ss:Type=\"String\">Weight</Data></Cell>"];
                  [excel appendString:@"<Cell><Data ss:Type=\"String\">ImageFile</Data></Cell>"];
                  [excel appendString:@"<Cell><Data ss:Type=\"String\">BMI</Data></Cell></Row>"];
"ss:Name" will be the name of your worksheet
"ss:ExpandedColumnCount=\"8\" - number of columns of the table.
"ss:ExpandedRowCount=\"2\" - number of rows.

Above, I have then defined the column headers (just for an example). They are the ones beginning with <Cell>

After this add your dynamic data for each column using loop or whatever way you prefer.  Like

[excel appendString:@"<Row>"];
[excel appendString:[NSString stringWithFormat:@"<Cell><Data ss:Type=\"Number\">1</Data></Cell>"]];
[excel appendString:[NSString stringWithFormat:@"<Cell><Data ss:Type=\"String\">Aaa</Data></Cell>"]];
[excel appendString:[NSString stringWithFormat:@"<Cell><Data ss:Type=\"String\">1984-07-25</Data></Cell>"]];
[excel appendString:[NSString stringWithFormat:@"<Cell><Data ss:Type=\"Number\">5</Data></Cell>"]];    
[excel appendString:[NSString stringWithFormat:@"<Cell><Data ss:Type=\"Number\">6</Data></Cell>"]];
[excel appendString:[NSString stringWithFormat:@"<Cell><Data ss:Type=\"Number\">65</Data></Cell>"]];
[excel appendString:[NSString stringWithFormat:@"<Cell><Data ss:Type=\"String\">myimage.jpg</Data></Cell>"]];
[excel appendString:[NSString stringWithFormat:@"<Cell><Data ss:Type=\"Number\">12.5</Data></Cell></Row>"]];
                  
Then add the following code to close your worksheet.

[excel appendString:@"</Table><WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">"];
[excel appendString:@"<Selected/><LeftColumnVisible>4</LeftColumnVisible><Panes><Pane>"];
[excel appendString:@"<Number>3</Number><ActiveRow>2</ActiveRow><ActiveCol>2</ActiveCol>"];
[excel appendString:@"</Pane></Panes><ProtectObjects>False</ProtectObjects>"];
[excel appendString:@"<ProtectScenarios>False</ProtectScenarios></WorksheetOptions></Worksheet>"];

you can follow the above step for each spreadsheet (if you want to create more than one)

And in the end add the closing tag to close your workbook.

[excel appendString:@"</Workbook>"];

Save this "as file" and you can mail to any mail address. Sample "XML" File attached.

Enjoy coding.

Thanks
Nishant Trivedi

EE.xml
1
11,604 Views

Comments (3)

Hi,

I have tried above sample XML file and save as .xls file but its gave, unsupported formate message before open saved file in MS while in Open Office, whole XML was showing in column A.

Can you explain solution for it?


thanks

Author

Commented:
Try to save as .xml format. Its extension will be .xml but this file can be open in Excel. Also default application to open this file will be Excel. You can see the Excel icon.

Thanks
I have tried same as you mentioned but not getting success for it.
I have download EE.xml and try to open in Excel but give error as well as i wrote code as your article and save file as xml and xls but same result.

Can you provide more detail or your sample code so i will get the proper result.


Thanks

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community