none
OpenXML Excel file, works in Excel 2007, OpenOffice, etc but not in Excel 2010 RRS feed

  • Question

  • Hi everyone,

    I have a problem.  I host a free website, sharing source code and ideas with other developers.  One of my projects is a very simple C# class, which generates an Excel 2007 .xlsx file.  All source code is provided, along with a demo showing how to use it, free of charge.  No registration required.

    http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm

    The .xlsx files that this class generates are fine, they open in Excel 2007 & OpenOffice, I've run the OpenXmlValidator on the files, and there are no reported problems.

    However, If I open the file in Excel 2010 (with SP1), it opens, but if I go to the Print or Print Preview screen, Excel 2010 crashes.  

    Googling around, I've found that many many users have experienced problems with HP print drivers, I followed some suggestions, such as turning off multi-threading in Excel, but it makes no difference.  I tried setting the default printer as the "Microsoft XPS Document Writer", but it made no difference.

    Here is a copy of the Excel file that my demo produces (and which crashes Excel 2010).   Can someone tell me why it opens okay, but then crashes Excel 2010 ?  

    http://www.MikesKnowledgeBase.com/SampleCode/Sample.xlsx

    I can get around the problem by loading this file in Excel 2010, saving it as an ".xls" file (!!) and re-loading that... but it kinda defeats the purpose.

    And, more generally, why does Excel 2007, OpenOffice and the OpenXmlValidator consider it to be in the correct format, but it crashes Excel 2010 ?  I'm surprised that Excel 2010 doesn't at least attempt to report some kind of problem with the Excel file, rather than bombing out.

    Mike


    Thursday, August 23, 2012 8:03 AM

All replies

  • Hi Mike,

    Thanks for posting in the MSDN Forum.

    I can open the link of http://www.MikeKnowledgeBase.com/SampleCode/Sample.xlsx due to proxy issue. Would you please share you spreadsheetdocument on skydrive for further research?

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Friday, August 24, 2012 1:57 AM
    Moderator
  • Sorry, I missed out an S.

    The link is actually:

    http://www.mikesknowledgebase.com/SampleCode/Sample.xlsx



    Friday, August 24, 2012 6:52 AM
  • Hi Mike,

    I noticed that you first WorksheetPart is named "sheet.xml". As far as I know, if your create a spreadsheetdocuemnt via Open Xml SDK 2.0 it will be "sheet1.xml". Woud you please try to generate it va Open Xml SDK 2.0?

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us


    Saturday, August 25, 2012 2:52 AM
    Moderator
  • Hi Tom,

    Thanks the reply.

    The class I use does use v2 of the Open XML SDK.  If you examine the "DocumentFormat.OpenXml.dll" file which is part of my downloadable example, you'll see the version number is 2.0.5022.0.

    As for the name of the WorksheetPart, nope, I let Open XML choose how to name the worksheet parts, based on the worksheet number (0 for the first worksheet, 1 for the second, etc)

    string workSheetID = "rId" + worksheetNumber.ToString();
    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>(workSheetID);

    Remember, Office 2010 does open - and display - the values in this workbook correctly.  But then crashes when trying to Print Preview it.   So the actual loading of the data, making sure it is in the right format, does seem to work successfully....

    Could it be that my code doesn't use a SharedString table ?   Has this suddenly become (quietly) compulsory in Excel 2010 ?

    Mike

    Wednesday, August 29, 2012 12:01 PM
  • Hi Mike,

    OK, I will involve some experts into this issue to see whether they can help you out. There might be some time delay, thanks for your patience.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, August 30, 2012 2:53 AM
    Moderator
  • Could you please go to Event Viewer and check the Error there?  The message about Excel crash is helpful to investigating the issue.

    thanks.


    Forrest Guo | MSDN Community Support | Feedback to manager

    Thursday, August 30, 2012 9:29 AM
    Moderator
  • Thanks Tom.

    Just to confirm:  I have Google'd this issue, and I have tried HotFixes such as this one:
    http://support.microsoft.com/kb/2521018

    You can view all of the source code for the class which I use to create the .xlsx file on the website:
    http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm

    This webpage includes links to the entire C# source code, or you can view just the Class file (.cs) file using this link:
    http://www.mikesknowledgebase.com/SampleCode/CreateExcelFile.zip

    Thanks... and good luck !

    Mike

    Thursday, August 30, 2012 12:09 PM
  • It seems Workbook.XML doesn't have all the information needed by Excel for pagination while prepare for print.

    Using your sample code, here is the XML generated for Workbook.XML part within Open XML package :
    <?xml version="1.0" encoding="utf-8"?>
    <x:workbook xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
      <x:sheets>
        <x:sheet name="Drivers" sheetId="1" r:id="rId1" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" />
        <x:sheet name="Vehicles" sheetId="2" r:id="rId2" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" />
        <x:sheet name="Vehicle owners" sheetId="3" r:id="rId3" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" />
      </x:sheets>
    </x:workbook>

    Replace above content with below XML and print should work fine:
    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
      <fileVersion appName="xl" lastEdited="5" lowestEdited="5" rupBuild="9303"/>
      <workbookPr defaultThemeVersion="124226"/>
      <bookViews>
        <workbookView xWindow="510" yWindow="510" windowWidth="4455" windowHeight="2520"/>
      </bookViews>
      <sheets>
        <sheet name="Drivers" sheetId="1" r:id="rId1"/>
        <sheet name="Vehicles" sheetId="2" r:id="rId2"/>
        <sheet name="Vehicle owners" sheetId="3" r:id="rId3"/>
      </sheets>
      <calcPr calcId="0"/>
    </workbook>

    The change is not with data but few elements which define the view for Excel. The required code change can be found using Open XML SDK tool code-reflect.


    Shiv Khare

    Wednesday, September 5, 2012 9:56 PM
    Moderator
  • Hi Shiv,

    (Several weeks later..)

    You were half right.  
    My code was crashing in Excel 2010, as my C# class was missing one line of code:

    workbook.Append(new BookViews(new WorkbookView()));

    Which poses the question...  have a look at Microsoft's own documentation for the minimum requirements for an Excel file.

    http://msdn.microsoft.com/en-us/library/office/gg278316.aspx

    It doesn't mention BookViews as being required at all...  and the files which Microsoft's own sample code produces work fine in Excel 2007, Open Office and via ODBC.  It's just Excel 2010 which has an issue with .xlsx files created without a BookView element.

    So, does Excel 2010 break Microsoft's own guidelines in what is required to create an Excel file...  or is this a bug in Excel 2010...?

    Edit:  Just to mention, some users of my "Export to Excel" C# class said that (without this extra line of code), their files would open correctly in Excel 2010, but that Excel would throw a “that command cannot be used on multiple selections” error when they attempted to add an extra Worksheet to it.

    • Edited by Mike Gledhill Tuesday, October 16, 2012 11:28 AM
    • Proposed as answer by GSeidler Thursday, October 18, 2012 12:42 PM
    Tuesday, October 16, 2012 11:23 AM
  • Hi Mike,

    thanks a lot for your posting!!!

    I spent a lot of time in analyzing my XSLX-structures and XML-files but I couldn't find the right answer (I also thought, that the MS documentation for minium requirements would be complete).

    So three simple lines in workbook.xml make it work: 

      <bookViews>
        <workbookView />
      </bookViews>

    - Gerald -

    • Proposed as answer by Spyros P Friday, April 3, 2015 11:26 AM
    Thursday, October 18, 2012 12:45 PM
  • Many-many thanks. You're life savior. 

    My own implementation of xlsx export was working OK with Office 2010. But all of sudden it had stopped working. It started to crash each time when trying to print. Don't know why. 

    So I googled this page.

    One thing you forget to mention is that BookViews should be appended BEFORE Sheets. Otherwise file would be invalid.



    • Edited by quadfactor Thursday, January 3, 2013 2:46 PM
    • Proposed as answer by Spyros P Friday, April 3, 2015 11:26 AM
    Thursday, January 3, 2013 2:45 PM
  • Thanks for the solution to the print preview issue. One question I have is that when I use the code, it names my sheet sheet.xml instead of sheet1.xml like Excel does. Is there a way to control the name or is this not an issue to worry about?

    Ray Pietrzak

    Friday, March 28, 2014 6:19 PM
  • This point was a life-saver:

    "One thing you forget to mention is that BookViews should be appended BEFORE Sheets. Otherwise file would be invalid."

    Many thanks!

    Friday, April 3, 2015 11:25 AM