- This Post, I will export excel file by XML Spreadsheet.
- Reference link: http://msdn.microsoft.com/en-us/library/office/aa140066%28v=office.10%29#odc_xmlss_howto
- Step 1:
- I will create a Function. Parameters is fullName, address, email:
- (NSString *)strExcelFile: (NSString *)fullName address:(NSString *)address email:(NSString *)email
{
}
- In function, I declare a String (NSMutableString data type), this string will contain all format excel file:
NSMutableString *excel = [[NSMutableString alloc] init];
- Step 2: You must append a default xml string into strExcel:
[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>"];
- Step 3: In This Step, I will create styles for row or colume.
+ The first: I will make style for border table, text font, Aligne, color border,….. Style name is “Default”
+ I will begin with <Style> tag & end with </Style> tag
+ Root’s <Style> is <Styles> </Styles>
//Begin Default Style
[excel appendString:@"<Style ss:ID=\"Default\" ss:Name=\"Normal\">\n"];
[excel appendString:@"<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>\n"];
[excel appendString:@"<Font ss:Bold=\"1\" ss:Size=\"15\" ss:Color=\"Green\" ss:FontName=\"Arial\" />\n"];
[excel appendString:@"<Borders>\n"];
[excel appendString:@"<Border ss:Position=\"Left\" ss:Color=\"Gray\" ss:Line\"Continuous\" ss:Weight=\"1\"/>\n"];
[excel appendString:@"<Border ss:Position=\"Top\" ss:Color=\"Gray\" ss:Line\"Continuous\" ss:Weight=\"1\"/>\n"];
[excel appendString:@"<Border ss:Position=\"Right\" ss:Color=\"Gray\" ss:Line\"Continuous\" ss:Weight=\"1\"/>\n"];
[excel appendString:@"<Border ss:Position=\"Bottom\" ss:Color=\"Gray\" ss:Line\"Continuous\" ss:Weight=\"1\"/>\n"];
[excel appendString:@"</Borders>"];
[excel appendString:@"</Style>"];
//End Defaul Style
+ The next, I create style for Header Title, header title’s name is “Header”
//BEGIN HEADER STYLE
[excel appendString:@"<Style ss:ID=\"Header\">"];
[excel appendString:@"<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>\n"];
[excel appendString:@"<Font ss:Bold=\"1\" ss:Color=\"Green\" ss:Size=\"17\" ss:FontName=\"Arial\" />\n"];
[excel appendString:@"<Interior ss:Color=\"Yellow\" ss:Pattern=\"Solid\"/>"];
[excel appendString:@"</Style>"];
//END HEADER STYLE
+ After, I create style for colume name: colume name style is “ColumeName”
//Begin Format ColumeName Stye
[excel appendString:@"<Style ss:ID=\"ColumeName\">"];
[excel appendString:@"<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>\n"];
[excel appendString:@"<Font ss:Bold=\"1\" ss:Size=\"17\" ss:Color=\"White\" ss:FontName=\"Arial\" />\n"];
[excel appendString:@"<Interior ss:Color=\"Green\" ss:Pattern=\"ThinReverseDiagStripe\"/>"];
[excel appendString:@"</Style>"];
//End Format ColumeName Stye
+ And close root’s <style>
[excel appendString:@"</Styles>\n"];
- Step 4: Setting worksheet and setting colume: In this sample, I create 3 colume: Full Name, Address, Email.
//Setting Worksheet
[excel appendString:@"<Worksheet ss:Name=\"Presntation\">\n"];
[excel appendString:@"<Table ss:ExpandedColumnCount=\"8\" ss:ExpandedRowCount=\"12\" x:FullColumns=\"1\" x:FullRows=\"1\">\n"];
//Setting The number colume
[excel appendFormat:@"<ss:Column ss:Width=\"200\"/>\n"];
[excel appendFormat:@"<ss:Column ss:Width=\"300\"/>\n"];
[excel appendFormat:@"<ss:Column ss:Width=\"300\"/>\n"];
- Step 5: Create rows for table:
+ The first: I create header tile. I will set “Header style ID” (step 3) for header row
//HEADER ROW
[excel appendFormat:@"<Row ss:StyleID=\"Header\" ss:AutoFitHeight=\"0\" ss:Height=\"40\" >\n"];
[excel appendFormat:@"<Cell ss:MergeAcross=\"4\"><Data ss:\"String\">DEMO EXPORT EXCEL FILE</Data></Cell>"];
[excel appendFormat:@"</Row>"];
+ The Second: Create colume name, set “ColumeName style ID” (step 3) for colume name row
//COLUME NAME ROW
[excel appendFormat:@"<Row ss:StyleID=\"ColumeName\" ss:AutoFitHeight=\"0\" ss:Height=\"50\" >\n"];
[excel appendFormat:@"<Cell><Data ss:\"String\">FULL NAME</Data></Cell>"];
[excel appendFormat:@"<Cell><Data ss:\"String\">ADDRESS</Data></Cell>"];
[excel appendFormat:@"<Cell><Data ss:\"String\">EMAIL</Data></Cell>"];
[excel appendFormat:@"</Row>"];
+ The Thirh: crete value colume. I will get value from textFeilds of MainViewController.
//VALUE ROW
[excel appendFormat:@"<Row ss:AutoFitHeight=\"0\" ss:Height=\"30\" >\n"];
[excel appendString:[NSString stringWithFormat:@"<Cell><Data ss:\"String\">%@</Data></Cell>",fullName]];
[excel appendFormat:@"<Cell><Data ss:\"String\">%@</Data></Cell>",address];
[excel appendFormat:@"<Cell><Data ss:\"String\">%@</Data></Cell>",email];
[excel appendFormat:@"</Row>"];
- THE END
[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>"];
[excel appendString:@"</Workbook>"];
//Return excel string for function
return [excel autorelease];
- After creating the excel string, I continue write excel string into file *.xls
=====================>>>>>>>>>>>>>>> I have 2 function
- (NSString *)strExcelFile: (NSString *)fullName address:(NSString *)address email:(NSString *)email
{
NSMutableString *excel = [[NSMutableString alloc] init];
//Default Setting XML
[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>"];
//Begin Default Style
[excel appendString:@"<Style ss:ID=\"Default\" ss:Name=\"Normal\">\n"];
[excel appendString:@"<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>\n"];
[excel appendString:@"<Font ss:Bold=\"1\" ss:Size=\"15\" ss:Color=\"Green\" ss:FontName=\"Arial\" />\n"];
[excel appendString:@"<Borders>\n"];
[excel appendString:@"<Border ss:Position=\"Left\" ss:Color=\"Gray\" ss:Line\"Continuous\" ss:Weight=\"1\"/>\n"];
[excel appendString:@"<Border ss:Position=\"Top\" ss:Color=\"Gray\" ss:Line\"Continuous\" ss:Weight=\"1\"/>\n"];
[excel appendString:@"<Border ss:Position=\"Right\" ss:Color=\"Gray\" ss:Line\"Continuous\" ss:Weight=\"1\"/>\n"];
[excel appendString:@"<Border ss:Position=\"Bottom\" ss:Color=\"Gray\" ss:Line\"Continuous\" ss:Weight=\"1\"/>\n"];
[excel appendString:@"</Borders>"];
[excel appendString:@"</Style>"];
//End Defaul Style
//BEGIN HEADER STYLE
[excel appendString:@"<Style ss:ID=\"Header\">"];
[excel appendString:@"<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>\n"];
[excel appendString:@"<Font ss:Bold=\"1\" ss:Color=\"Green\" ss:Size=\"17\" ss:FontName=\"Arial\" />\n"];
[excel appendString:@"<Interior ss:Color=\"Yellow\" ss:Pattern=\"Solid\"/>"];
[excel appendString:@"</Style>"];
//END HEADER STYLE
//Begin Format ColumeName Stye
[excel appendString:@"<Style ss:ID=\"ColumeName\">"];
[excel appendString:@"<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>\n"];
[excel appendString:@"<Font ss:Bold=\"1\" ss:Size=\"17\" ss:Color=\"White\" ss:FontName=\"Arial\" />\n"];
[excel appendString:@"<Interior ss:Color=\"Green\" ss:Pattern=\"ThinReverseDiagStripe\"/>"];
[excel appendString:@"</Style>"];
//End Format ColumeName Stye
[excel appendString:@"</Styles>\n"];
//Setting Worksheet
[excel appendString:@"<Worksheet ss:Name=\"Presntation\">\n"];
[excel appendString:@"<Table ss:ExpandedColumnCount=\"8\" ss:ExpandedRowCount=\"12\" x:FullColumns=\"1\" x:FullRows=\"1\">\n"];
//Setting The number colume
[excel appendFormat:@"<ss:Column ss:Width=\"200\"/>\n"];
[excel appendFormat:@"<ss:Column ss:Width=\"300\"/>\n"];
[excel appendFormat:@"<ss:Column ss:Width=\"300\"/>\n"];
//HEADER ROW
[excel appendFormat:@"<Row ss:StyleID=\"Header\" ss:AutoFitHeight=\"0\" ss:Height=\"40\" >\n"];
[excel appendFormat:@"<Cell ss:MergeAcross=\"4\"><Data ss:\"String\">DEMO EXPORT EXCEL FILE</Data></Cell>"];
[excel appendFormat:@"</Row>"];
//COLUME NAME ROW
[excel appendFormat:@"<Row ss:StyleID=\"ColumeName\" ss:AutoFitHeight=\"0\" ss:Height=\"50\" >\n"];
[excel appendFormat:@"<Cell><Data ss:\"String\">FULL NAME</Data></Cell>"];
[excel appendFormat:@"<Cell><Data ss:\"String\">ADDRESS</Data></Cell>"];
[excel appendFormat:@"<Cell><Data ss:\"String\">EMAIL</Data></Cell>"];
[excel appendFormat:@"</Row>"];
//VALUE ROW
[excel appendFormat:@"<Row ss:AutoFitHeight=\"0\" ss:Height=\"30\" >\n"];
[excel appendString:[NSString stringWithFormat:@"<Cell><Data ss:\"String\">%@</Data></Cell>",fullName]];
[excel appendFormat:@"<Cell><Data ss:\"String\">%@</Data></Cell>",address];
[excel appendFormat:@"<Cell><Data ss:\"String\">%@</Data></Cell>",email];
[excel appendFormat:@"</Row>"];
//=====================================
[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>"];
[excel appendString:@"</Workbook>"];
return [excel autorelease];
}
- (IBAction)export:(id)sender
{
NSString *kPresentationRateFilePath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)objectAtIndex:0];
NSString *excelFile = [[self strExcelFile:self.txtFullName.text address:self.txtAddress.text email:self.txtEmail.text] retain];
[excelFile writeToFile:[kPresentationRateFilePath stringByAppendingPathComponent:@"Presentation_Rate.xls"] atomically:YES encoding:NSUTF8StringEncoding error:&error];
}