none
Print GridLines RRS feed

  • Question

  • How can I set and apply the PrintOptions for Spreadsheet to print the Gridlines?

    The msn documentation doesn't really tell you how, and I couldn't find a sample either.

    This is what I got, but this creates an error: (the Excel file can't be opened)

    If I take out the SetPrintOptions, everything else works as should.

     using (var spreadSheet = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
                {
                    WorkbookPart workbookPart = spreadSheet.AddWorkbookPart();
                    WorksheetPart workSheetPart = workbookPart.AddNewPart<WorksheetPart>();
                    Workbook workbook = new Workbook();
                    FileVersion fv = new FileVersion();
                    fv.ApplicationName = "Microsoft Office Excel";
                    Worksheet workSheet = new Worksheet();               

                    SheetData sheetData = new SheetData();
                    //workSheetPart.Worksheet = new Worksheet(sheetData);

                    WorkbookStylesPart stylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
                    stylesPart.Stylesheet = StylesheetUtility.GenerateStyleSheet();
                    stylesPart.Stylesheet.Save();
                    

                    List<int[]> subTables = new List<int[]>();
                    int rowNum = ProcessDataGrid(workSheet, sheetData, dg, ExcludeColumnHeaders, 1, true, ExcludeSubColumnHeaders, onlyInner, true, subTables, workSheetPart, imageColumns, isImageSameColumn);


                    workSheet.Append(sheetData);
                    SetSheetPrintProperties(workSheet, 1, 1, OrientationValues.Landscape);
                    

    //set Printer Options Gridlines
                    workSheet.Append(SetPrintOptions(true));

    workSheetPart.Worksheet = workSheet;
                    workSheetPart.Worksheet.Save();


                    Sheets sheets = workbook.GetFirstChild<Sheets>();
                    if (sheets == null) sheets = new Sheets();
                    string relationshipId = workbookPart.GetIdOfPart(workSheetPart);

                    uint sheetId = (sheets.Elements<Sheet>().Count() > 0) ? sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1 : 1;

                    Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = name };
                    sheets.Append(sheet);

                    workbook.Append(fv);
                    workbook.Append(sheets);
                    spreadSheet.WorkbookPart.Workbook = workbook;
                    spreadSheet.WorkbookPart.Workbook.Save();                
                }

     public void SetSheetPrintProperties(Worksheet ws, UInt32 fitToHeight, UInt32 fitToWidth, OrientationValues orient = OrientationValues.Portrait)
            {
                SheetProperties sp = new SheetProperties(new PageSetupProperties());            
                ws.SheetProperties = sp;

                // Set the FitToPage property to true
                ws.SheetProperties.PageSetupProperties.FitToPage = BooleanValue.FromBoolean(true);

                DocumentFormat.OpenXml.Spreadsheet.PageSetup pgOr = new DocumentFormat.OpenXml.Spreadsheet.PageSetup();
                pgOr.Orientation = DocumentFormat.OpenXml.Spreadsheet.OrientationValues.Landscape;
                pgOr.FitToHeight = fitToHeight;
                pgOr.FitToWidth = fitToWidth;
                ws.AppendChild(pgOr);
            }

            private PrintOptions SetPrintOptions(bool printGridLines)
            {
                PrintOptions po = new PrintOptions();
                po.GridLines = true;
                po.GridLinesSet = true;
                return po;
            }




    • Edited by TAK78 Monday, September 14, 2015 3:07 PM
    Monday, September 14, 2015 1:42 PM

Answers

  • Hi TAK78,

    As far as I know, we can use Open XML to check the "Print GridLines" option however it is not possible to simulate check the "Print GridLines" feature in Excel because Excel application will add some binary data to describe the page layout. We can compare the difference before and after change this settings like figure below:

    Here is the code to check this setting for your reference:

      public void PrintGridLines(string filePath, string sheetName)
            {
                using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true))
                {
    
                    Sheet targetSheet= (Sheet)document.WorkbookPart.Workbook.Descendants<Sheet>().Where(a => a.Name == sheetName).First();
    
                    WorksheetPart wsp=(WorksheetPart)document.WorkbookPart.GetPartById(targetSheet.Id);
                    PhoneticProperties pp=wsp.Worksheet.Descendants<PhoneticProperties>().FirstOrDefault();
                    PrintOptions printOptions1 = new PrintOptions() { GridLines = true };
                    wsp.Worksheet.InsertAfter(printOptions1, pp);
                                    
                    PageSetup pageSetup1 = new PageSetup() { PaperSize = (UInt32Value)9U, Orientation = OrientationValues.Portrait, Id = "rId1" };
                    PageMargins pm=wsp.Worksheet.Descendants<PageMargins>().FirstOrDefault();
                   
                    wsp.Worksheet.InsertAfter(pageSetup1, pm);
                }
            }

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by TAK78 Tuesday, September 15, 2015 9:28 AM
    Tuesday, September 15, 2015 3:31 AM
    Moderator

All replies

  • Hi TAK78,

    As far as I know, we can use Open XML to check the "Print GridLines" option however it is not possible to simulate check the "Print GridLines" feature in Excel because Excel application will add some binary data to describe the page layout. We can compare the difference before and after change this settings like figure below:

    Here is the code to check this setting for your reference:

      public void PrintGridLines(string filePath, string sheetName)
            {
                using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true))
                {
    
                    Sheet targetSheet= (Sheet)document.WorkbookPart.Workbook.Descendants<Sheet>().Where(a => a.Name == sheetName).First();
    
                    WorksheetPart wsp=(WorksheetPart)document.WorkbookPart.GetPartById(targetSheet.Id);
                    PhoneticProperties pp=wsp.Worksheet.Descendants<PhoneticProperties>().FirstOrDefault();
                    PrintOptions printOptions1 = new PrintOptions() { GridLines = true };
                    wsp.Worksheet.InsertAfter(printOptions1, pp);
                                    
                    PageSetup pageSetup1 = new PageSetup() { PaperSize = (UInt32Value)9U, Orientation = OrientationValues.Portrait, Id = "rId1" };
                    PageMargins pm=wsp.Worksheet.Descendants<PageMargins>().FirstOrDefault();
                   
                    wsp.Worksheet.InsertAfter(pageSetup1, pm);
                }
            }

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by TAK78 Tuesday, September 15, 2015 9:28 AM
    Tuesday, September 15, 2015 3:31 AM
    Moderator
  • OK, so how I understand this, is that Excel will create for each File it creates a unqiue binary data string for the print settings. So if you create different sheets you would always have to read a new code string for that, which would makes that pretty useless.

    So I guess I will just set the PageSetup but the check for GridLines the user has to do themselfs.

    Well thanks for the help and explanation. I appreciate it.

    Tuesday, September 15, 2015 9:28 AM