locked
How to change page setup property of excel programmatically without reading excel - for printing purpose RRS feed

  • Question

  • Hi,

    I have a c#.net code to open excel file and change the page setup property to fit the page in a single paper during printing.

    The sheets are read inside a loop and i have below code to change the settings.

    sheet.PageSetup.FitToPagesTall = 1;
    sheet.PageSetup.FitToPagesWide = 1;

    For big excel files it is taking time to do page setup. Is there any other way to change the settings without reading the excel. For small files it is not taking much time. Only for huge file size it is taking time.

    Thanks.

    • Moved by CoolDadTx Friday, February 12, 2016 2:09 PM Office related
    Friday, February 12, 2016 9:11 AM

Answers

  • >>>For big excel files it is taking time to do page setup. Is there any other way to change the settings without reading the excel. For small files it is not taking much time. Only for huge file size it is taking time.

    According to your description, I suggest that you could use Open Xml to change page setup property of excel, refer to codes below:

    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    
    
    string excelFileName = "D:\\blank.xlsx";
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(excelFileName, true))
    {
        WorkbookPart workbookPart = document.WorkbookPart;
        IEnumerable<string> worksheetIds = workbookPart.Workbook.Descendants<Sheet>().Select(w => w.Id.Value);
        WorksheetPart worksheetPart;
        foreach (string worksheetId in worksheetIds)
        {
            worksheetPart = ((WorksheetPart)workbookPart.GetPartById(worksheetId));
            PageSetup pageSetup = worksheetPart.Worksheet.Descendants<PageSetup>().FirstOrDefault();
            if (pageSetup != null)
            {
                pageSetup.Orientation = OrientationValues.Landscape;
            }
            else
            {
                pageSetup = new PageSetup();
                pageSetup.Orientation = OrientationValues.Landscape;
                pageSetup.FitToWidth = 1; 
                worksheetPart.Worksheet.AppendChild(pageSetup);
            }
            worksheetPart.Worksheet.Save();
        }
        workbookPart.Workbook.Save();
    }
    

    For more information, click here to refer about PageSetup Class

    • Proposed as answer by David_JunFeng Thursday, February 18, 2016 9:09 AM
    • Marked as answer by David_JunFeng Tuesday, February 23, 2016 7:16 AM
    Tuesday, February 16, 2016 4:48 AM

All replies

  • Hi,

    It would help if you mentioned what version of excel you are using. 

    Nevertheless, I remember I did a project few years ago and needed something similar to your requirement and I used a COM dll from Microsoft called DSOFile. 

    https://support.microsoft.com/en-us/kb/224351

    This is really cool and lets you edit properties without opening file. Like the article says, you may have to install some compatibility packages in order to read latest excel files. 

    Please let me know if you need any further help or an example.

    Please select as an answer if this helped.

    Thanks,


    Hari Mothukuri

    Friday, February 12, 2016 6:53 PM
  • >>>For big excel files it is taking time to do page setup. Is there any other way to change the settings without reading the excel. For small files it is not taking much time. Only for huge file size it is taking time.

    According to your description, I suggest that you could use Open Xml to change page setup property of excel, refer to codes below:

    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    
    
    string excelFileName = "D:\\blank.xlsx";
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(excelFileName, true))
    {
        WorkbookPart workbookPart = document.WorkbookPart;
        IEnumerable<string> worksheetIds = workbookPart.Workbook.Descendants<Sheet>().Select(w => w.Id.Value);
        WorksheetPart worksheetPart;
        foreach (string worksheetId in worksheetIds)
        {
            worksheetPart = ((WorksheetPart)workbookPart.GetPartById(worksheetId));
            PageSetup pageSetup = worksheetPart.Worksheet.Descendants<PageSetup>().FirstOrDefault();
            if (pageSetup != null)
            {
                pageSetup.Orientation = OrientationValues.Landscape;
            }
            else
            {
                pageSetup = new PageSetup();
                pageSetup.Orientation = OrientationValues.Landscape;
                pageSetup.FitToWidth = 1; 
                worksheetPart.Worksheet.AppendChild(pageSetup);
            }
            worksheetPart.Worksheet.Save();
        }
        workbookPart.Workbook.Save();
    }
    

    For more information, click here to refer about PageSetup Class

    • Proposed as answer by David_JunFeng Thursday, February 18, 2016 9:09 AM
    • Marked as answer by David_JunFeng Tuesday, February 23, 2016 7:16 AM
    Tuesday, February 16, 2016 4:48 AM