Rotate the Text in a Excel sheet

Category: open xml format sdk

Question

Nejox on Tue, 12 Jul 2016 14:34:56


Hi,

i have following code to format my Cells. On which point i have to add the Alignment to rotate my Text?

I tried it to add in Font index 3 "new Alignment() { TextRotation = (UInt32Value)90U }" but this does not work :(

Can anyone help me?

Greetings Nejox

 private Stylesheet AddStyleSheet()
        {
            Stylesheet stylesheet = null;

            Fonts fonts = new Fonts(new Font(               // Index 0 - default
                new FontSize() { Val = 10 }),
                new Font(                                   // Index 1 - header
                   new FontSize() { Val = 12 },
                   new FontName() { Val = "Arial" },
                   new Bold()),
                    new Font(                               // Index 2
                        new FontSize() { Val = 9 },
                        new FontName() { Val = "Arial" }),
                         new Font(                          // Index 3
                             new FontSize() { Val = 12 },
                             new FontName() { Val = "Arial" },                          
                             new Bold()
               ));

            Fills fills = new Fills(
                    new Fill(new PatternFill() { PatternType = PatternValues.None }),                                       // Index 0 - default
                    new Fill(new PatternFill() { PatternType = PatternValues.Gray125 }),                                    // Index 1 - default
                    new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = "66666666" } })
                    { PatternType = PatternValues.Solid })                                                                  // Index 2 - header
                );

            Borders borders = new Borders(
                    new Border(),                                                                                           // index 0 default
                    new Border(                                                                                             // index 1 bottom border
                        new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin }),
                            new Border(                                                                                     //index 2 border
                            new LeftBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                            new RightBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                            new TopBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                            new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                            new DiagonalBorder())
                );


            CellFormats cellFormats = new CellFormats(new CellFormat(),         // default
                    new CellFormat                                              // Format 1
                    {
                        FontId = 1,
                        BorderId = 1,
                        ApplyBorder = true
                    },                                                          
                    new CellFormat                                              // Format 2
                    {
                        FontId = 2,
                        BorderId = 0,
                        ApplyFill = true
                    },
                    new CellFormat                                              // Format 3
                    {
                        FontId = 2,
                        BorderId = 2,
                        ApplyFill = true
                    },
                    new CellFormat                                              // Format 4
                    {
                        FontId = 3,
			//Add alignment! How?
                    }
                    
                );

            stylesheet = new Stylesheet(fonts, fills, borders, cellFormats);

            return stylesheet;          
        }


Replies

Edward8520 on Wed, 13 Jul 2016 05:14:53


Hi Nejox,

How did you append CellFormats after you add new Alignment()? Here is a simple demo which is generated from Open XML SDK 2.5 for Microsoft Office

        ChangeWorkbookStylesPart1(((WorkbookStylesPart)UriPartDictionary["/xl/styles.xml"]));

        private  void ChangeWorkbookStylesPart1(WorkbookStylesPart workbookStylesPart1)
        {
            Stylesheet stylesheet1 = workbookStylesPart1.Stylesheet;

            CellFormats cellFormats1=stylesheet1.GetFirstChild<CellFormats>();
            cellFormats1.Count = (UInt32Value)2U;

            CellFormat cellFormat1 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyAlignment = true };
            Alignment alignment1 = new Alignment(){ TextRotation = (UInt32Value)180U };

            cellFormat1.Append(alignment1);
            cellFormats1.Append(cellFormat1);
        }

Best Regards,

Edward

Nejox on Wed, 13 Jul 2016 07:34:37


Hello Edward,

i use this code to format my Cells. When i create a new Cell i give it my StyleIndex. Borders, fonts, and fills works very good. But i dont know how i can add this rotation to my stylsheet that i can simply call my StyleIndex 4 to rotate the Cell.

using (SpreadsheetDocument excel = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)) { WorkbookPart wbp = excel.AddWorkbookPart(); wbp.Workbook = new Workbook(); WorksheetPart wsp = wbp.AddNewPart<worksheetpart>(); wsp.Worksheet = new Worksheet(); //Adding style WorkbookStylesPart stylesheet = wbp.AddNewPart<workbookstylespart>(); stylesheet.Stylesheet = AddStyleSheet(); stylesheet.Stylesheet.Save(); Sheets sheets = wbp.Workbook.AppendChild(new Sheets());

SheetData _sd = new SheetData(); //add a sheet to the excel document Sheet sheet = new Sheet() { Id = wbp.GetIdOfPart(wsp), SheetId = 1, Name = "Test" }; //add Heading Row row1 = new Row() { RowIndex = (UInt32)1u }; _sd.AppendChild(row1); //This cell should be rotate, but how? Cell cell1= new Cell() { CellReference = "A1", DataType = CellValues.String, CellValue = new CellValue("long test string"), //I add the Style here StyleIndex = Convert.ToUInt32(4) }; row1.Append(cell1); sheets.Append(sheet); wbp.Workbook.Save(); wsp.Worksheet.Append(_sd); wsp.Worksheet.Save(); excel.WorkbookPart.Workbook.Save(); excel.Close();





Nejox on Wed, 13 Jul 2016 08:15:26


Nevermind. I did to assemble your Demo with my code and it works now :-)

Ty Edward

 private Stylesheet AddStyleSheet()
        {
            Stylesheet stylesheet = null;

            Fonts fonts = new Fonts(new Font(               // Index 0 - default
                new FontSize() { Val = 10 }),
                new Font(                                   // Index 1 - header
                   new FontSize() { Val = 12 },
                   new FontName() { Val = "Arial" },
                   new Bold()),
                    new Font(                               // Index 2
                        new FontSize() { Val = 9 },
                        new FontName() { Val = "Arial" }),
                         new Font(                          // Index 3
                             new FontSize() { Val = 12 },
                             new FontName() { Val = "Arial" },
                             new Bold()
               ));

            Fills fills = new Fills(
                    new Fill(new PatternFill() { PatternType = PatternValues.None }),                                       // Index 0 - default
                    new Fill(new PatternFill() { PatternType = PatternValues.Gray125 }),                                    // Index 1 - default
                    new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = "66666666" } })
                    { PatternType = PatternValues.Solid })                                                                  // Index 2 - header
                );

            Borders borders = new Borders(
                    new Border(),                                                                                           // index 0 default
                    new Border(                                                                                             // index 1 bottom border
                        new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin }),
                            new Border(                                                                                     //index 2 border
                            new LeftBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                            new RightBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                            new TopBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                            new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                            new DiagonalBorder())
                );

            Alignment alignment1 = new Alignment() { TextRotation = (UInt32Value)180U };

            CellFormats cellFormats = new CellFormats(new CellFormat(),         // default
                    new CellFormat                                              // Format 1
                    {
                        FontId = 1,
                        BorderId = 1,
                        ApplyBorder = true
                    },
                    new CellFormat                                              // Format 2
                    {
                        FontId = 2,
                        BorderId = 0,
                        ApplyFill = true
                    },
                    new CellFormat                                              // Format 3
                    {
                        FontId = 2,
                        BorderId = 2,
                        ApplyFill = true
                    },
                    new CellFormat                                              // Format 4
                    {
                        FontId = 3,
                        Alignment = alignment1
                    }
                );

            stylesheet = new Stylesheet(fonts, fills, borders, cellFormats);

            return stylesheet;
        }