<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Create Excel Supported XML in iPhone using Xcode.

Published on
16,612 Points
10,512 Views
1 Endorsement
Last Modified:
Approved
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
Comment
Author:nish_tr
  • 2
3 Comments

Expert Comment

by:kamalElan
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
0
LVL 2

Author Comment

by:nish_tr
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
0

Expert Comment

by:kamalElan
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
0

Featured Post

Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Join & Write a Comment

This video demonstrates how to sync Microsoft Exchange Public Folders with smartphones using CodeTwo Exchange Sync and Exchange ActiveSync. To learn more about CodeTwo Exchange Sync and download the free trial, go to: http://www.codetwo.com/excha…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month