0

I'm trying to export an excel sheet into xml spread sheet 2003 in order to apply some xsl on the result xml file ,

so , i have tried to save the sheet i'm working on using XlFormat enum as follows :

Excel.Application app = new Excel.Application();
Excel.Workbook workbook;
Excel.Worksheet NwSheet;
workbook = app.Workbooks.Open(@"C:\file.xlsx",
                          Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                          Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                          Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                          Type.Missing, Type.Missing);
NwSheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);

string outfile = @"c:\temp.xml";
NwSheet.SaveAs(outfile, Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

This code generated an xml file with the following problems :

1-cells of type date are generated as : 2011-10-10T00:00:00.000 , i want it to be 10/10/2011 exactly as it was in the excel sheet so how can i access to these cell's types in order to change them ??

2-numbers as 8.2 were generated as 8.1999999999999993 why did that happen and how can i get it just as is it in the excel sheet

i guess both are faces of the same problem , i just need the data as it appears in the excel sheet.

Sarah Mohammad
  • 57
  • 1
  • 9
  • 1
    First part - if you really don't want to do any date parsing, you'll have to format the dates as text and enter the exact format you want to see in the XML output. Second part - see here http://stackoverflow.com/questions/12862524/why-does-excel-xlsx-save-some-values-in-xml-with-additional-values – Tim Williams Oct 12 '12 at 16:09
  • For the second part , thank you..for the first one , what do you mean by "date parsing" ? – Sarah Mohammad Oct 12 '12 at 16:43
  • I mean that it would be quite straightforward to format a date as `10/10/2011` given an input of `2011-10-10T00:00:00.000` – Tim Williams Oct 12 '12 at 16:45
  • 10/10/2011 is how it looks as an input indeed , the format 2011-10-10T00:00:00.000 is generated in the xml. I'm sorry this is my mistake not being clear enough – Sarah Mohammad Oct 12 '12 at 16:55
  • Reformatting cells manually before processing is not acceptable_from business side_ – Sarah Mohammad Oct 12 '12 at 16:58
  • I meant as input *from the XML file* in whatever application you're intending to use to parse these XML files. Edit: sorry I missed you're planning to use xsl on the output. Not really up to speed on that so maybe someone else could suggest how to get at the cell's `ss:Type` attribute and use that to get the date format you want. – Tim Williams Oct 12 '12 at 17:26
  • Date formatting in xslt: http://stackoverflow.com/questions/9077233/xslt-format-date – Tim Williams Oct 12 '12 at 18:01

0 Answers0