none
Office.Interop.Excel Worksheet.PageSetup.set_PrintQuality bug? RRS feed

  • Question

  • I've discovered a nasty little bug in some code we have written which prints an Excel file using the Excel Interops in C#. It turns out that all of the pages being printed ended up with the page number "2" on them. 1 page print ... page number 2. 12 page print ... all pages numbered 2. So I started looking into it and it appears as though the set_PrintQuality method, which we are calling to set the DPI to 600 both horizontally and vertically, is actually setting the Worksheet.PageSetup.CenterFooter value. Note my 3 screen captures below:

    For Reference:  Windows 7 Professional; Visual Studio 2010 Professional SP1; Microsoft.Office.Interop.Excel (v14.0); .NET Framework 4

    Screen capture 1. The worksheet object is populated and we are about to call set_PrintQuality(1, dpi) to set the horizontal dpi to 600. Note in the watch window that the PageSetup.CenterFooter value is "".

    Screen capture 2. PageSetup.set_PrintQuality(1,dpi) has been called and we are about to call set_PrintQuality(2,dpi) to set the vertical dpi to 600. Note the value of CenterFooter in the watch window has been set to "1" now.

    (omitted this image because I can only embed 2 images per post ... here is a link to the image ... Screen Capture 2 )

    Screen Capture 3. The second call to set_PrintQuality has been called. Note the watch value of CenterFooter is now "2". This is the value that appears on all pages for our page number (basically has set static text in the footer).

    Any ideas on what could be happening here?

    Brandon P



    • Edited by Brandon72 Monday, May 13, 2013 5:44 PM Change Screen Cap 2 link
    Friday, May 10, 2013 1:26 PM

All replies

  • UPDATE:

    I have tried severaly ways of setting the PrintQuality.

    // using the array indexer ...
    object dpi = 600;
    worksheet.PageSetup.PrintQuality[1] = dpi;
    
    // using the set method with an index
    object dpi = 600;
    worksheet.PageSetup.set_PrintQuality(1, dpi);

    In both instances the index overwrites the value of the CenterFooter. I finally (exasperated) figured why not just try it without the index?

    // Ignore the index and just set the object array to a value
    // winner
    object dpi = 600;
    worksheet.PageSetup.PrintQuality = dpi;

    This actually works. If this is a "feature" of object array I've never been aware of it before ... that you can set every element in the array by simply setting the object[] to a specific value. It does just that however. And, the CenterFooter doesn't get overwritten. This would present a problem if you needed to set different dpi's for the horizontal and vertical values, but since we are setting them both to 600 this works for this particular situation. Kluge committed with a //HACK comment.


    • Edited by Brandon72 Friday, May 10, 2013 5:53 PM
    Friday, May 10, 2013 5:49 PM
  • ANOTHER NOTE:

    It looks like this has been around for a while?

    http://www.mofeel.net/61-microsoft-public-dotnet-framework-interop/3716.aspx

    Friday, May 10, 2013 5:55 PM
  • I cannot see the 2nd pic.

    Besides, can you repro this if you print it to a file (pdf, xps)? It will be easier for communities to repro if you can upload a sample proj to skydrive and share it.


    with regards, Yuri

    Monday, May 13, 2013 2:19 PM
  • The link for the second pic was apparently only a temporary ... since it wasn't used in a post it was removed ... here is pic 2. I'll see what I can do on a sample project.

    Monday, May 13, 2013 3:36 PM
  • EDIT: here is a link to the full project on skydrive ... test file included ... just add the watches and step through to see the issue.

    Project Link (Skydrive)

    END EDIT ...

    Yuri ... I don't think an actual print is required to see this issue ... it is evident in the debugger. While stepping through you can see that a call to set the PrintQuality changes the CenterFooter.

    I am pasting some code here from a small console app I just created that will reproduce this issue. You should be able to create a new console app in VS 2010, add reference to the MS.Office.Interop.Excel.dll (v14.0), and paste in this code, and add a Test.xlsx file with data in it to the bin\debug directory to see the issue.

    I set a break point just before the loop and added watch on the following: worksheet.PageSetup.CenterFooter; worksheet.PageSetup.PrintQuality[1], worksheet.PageSetup.PrintQuality[2]. That should be all that is necessary to see the problem ... just step through and you will see CenterFooter change when PrintQuality is set

    using System;
    using Excel = Microsoft.Office.Interop.Excel;
    
    namespace ConsoleApplication1
    {
        class Program
        {
    
            // PRIOR TO RUNNING:
            // Add reference to Microsoft.Office.Interop.Excel.dll
            // Add watch to watchList for:
            //    worksheet.PageSetup.PrintQuality[1];
            //    worksheet.PageSetup.PrintQuality[2];
            //    worksheet.PageSetup.CenterFooter;
            // Put a Test.xlsx with several worksheets with data on them in bin\debug directory
    
            static void Main(string[] args)
            {
                Excel.Application excel = new Excel.Application();
                excel.Visible = false;
                excel.DisplayAlerts = false;
    
                string fileName = AppDomain.CurrentDomain.BaseDirectory + "Test.xlsx";
    
                Excel.Workbook workbook = excel.Workbooks.Open(Filename: fileName, UpdateLinks: 0, ReadOnly: true, Format: 5, IgnoreReadOnlyRecommended: true);
                
                object dpi = 600;
                object[] dpis = { 600, 600 };
    
                for (int i = 1; i <= workbook.Worksheets.Count; i++)
                {
                    Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[i];
    
                    // access center footer to get it into the app and viewable in the watch window
                    // if you take this line out you will have to cast PageSetup as dynamic in watch window
                    // watch: ((dynamic)worksheet.PageSetup).CenterFooter
                    // and refresh it's value after ever step forward in the debugger.
                    string centerFooter = worksheet.PageSetup.CenterFooter;
    
                    // Access the PrintQuality property as an indexed array ... 
                    worksheet.PageSetup.PrintQuality[1] = dpi;
                    worksheet.PageSetup.PrintQuality[2] = dpi;
    
                    worksheet.PageSetup.CenterFooter = "";
    
                    // Access the PrintQuality property through setter ...
                    worksheet.PageSetup.set_PrintQuality(1, dpi);
                    worksheet.PageSetup.set_PrintQuality(2, dpi);
    
                    worksheet.PageSetup.CenterFooter = "";
    
                    // Set the PrintQality array to an object ...
                    worksheet.PageSetup.PrintQuality = dpi;
    
                }
            }
        }
    }




    • Edited by Brandon72 Monday, May 13, 2013 5:47 PM Make Project Link an actual link
    Monday, May 13, 2013 4:16 PM
  • Hi Bspweb,

    Thank you for posting in the MSDN Forum.

    I can reproduce your scenario. I'll report your issue internal and you'll be informed if there's any update.

    Thank you for your time and effort over this issue.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, May 14, 2013 1:33 PM
    Moderator
  • Thanks Quist.

    Brandon P

    Tuesday, May 14, 2013 1:38 PM
  • Quist,

    Has there been any further information on this issue. We are still experiencing sporadic issues with setting the PrintQuality on the PageSetup object in the Worksheet. The issue was reproduced and I have no word on the fix or proper workaround in 6 months.

    Thanks,

    Brandon P

    Friday, November 22, 2013 5:09 PM