locked
Cell Styles not getting applied in my excel sheet RRS feed

  • Question

  • Hi All,

    I am new to DocumentFormat.OpenXML SDK 2.0 version, since past two weeks i was trying to get proper data format into the excel sheet using code behind language as C#.Net. I was able to pass the data format by specifying the cell format type as follows:

     

    private
    
     void
    
     GenerateWorkbookStylesPart1Content(WorkbookStylesPart workbookStylesPart1)
     {
      Stylesheet stylesheet1 = new
    
     Stylesheet();
    
      Fonts fonts1 = new
    
     Fonts() { Count = (UInt32Value)0U };
    
      Font font1 = new
    
     Font();
      FontSize fontSize1 = new
    
     FontSize() { Val = 11D };
      Color color1 = new
    
     Color() { Theme = (UInt32Value)1U };
      FontName fontName1 = new
    
     FontName() { Val = "Calibri"
    
     };
      FontFamilyNumbering fontFamilyNumbering1 = new
    
     FontFamilyNumbering() { Val = 2 };
      FontScheme fontScheme1 = new
    
     FontScheme() { Val = FontSchemeValues.Minor };
    
      font1.Append(fontSize1);
      font1.Append(color1);
      font1.Append(fontName1);
      font1.Append(fontFamilyNumbering1);
      font1.Append(fontScheme1);
    
      fonts1.Append(font1);
    
      Fills fills1 = new
    
     Fills() { Count = (UInt32Value)0U };
    
      Fill fill1 = new
    
     Fill();
      PatternFill patternFill1 = new
    
     PatternFill() { PatternType = PatternValues.None };
    
      fill1.Append(patternFill1);
    
      Fill fill2 = new
    
     Fill();
      PatternFill patternFill2 = new
    
     PatternFill() { PatternType = PatternValues.Gray125 };
    
      fill2.Append(patternFill2);
    
      fills1.Append(fill1);
      fills1.Append(fill2);
    
      Borders borders1 = new
    
     Borders() { Count = (UInt32Value)0U };
    
      Border border1 = new
    
     Border();
      LeftBorder leftBorder1 = new
    
     LeftBorder();
      RightBorder rightBorder1 = new
    
     RightBorder();
      TopBorder topBorder1 = new
    
     TopBorder();
      BottomBorder bottomBorder1 = new
    
     BottomBorder();
      DiagonalBorder diagonalBorder1 = new
    
     DiagonalBorder();
    
      border1.Append(leftBorder1);
      border1.Append(rightBorder1);
      border1.Append(topBorder1);
      border1.Append(bottomBorder1);
      border1.Append(diagonalBorder1);
    
      borders1.Append(border1);
    
      CellStyleFormats cellStyleFormats1 = new
    
     CellStyleFormats() { Count = (UInt32Value)0U };
      CellFormat cellFormat1 = new
    
     CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
    
      cellStyleFormats1.Append(cellFormat1);
    
      CellFormats cellFormats1 = new
    
     CellFormats() { Count = (UInt32Value)6U };
      CellFormat cellFormat2 = new
    
     CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
    
      cellFormats1.Append(cellFormat2);
    
    
      //extra code for cell formatting by Ravindranath
    
    
      CellFormat cellFormat3 = new
    
     CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFont = true
    
    , ApplyFill = true
    
    , ApplyBorder = true
    
    , ApplyAlignment = true
    
     };
      Alignment alignment1 = new
    
     Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Center };
    
      cellFormat3.Append(alignment1);
      CellFormat cellFormat4 = new
    
     CellFormat() { NumberFormatId = (UInt32Value)3U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true
    
     };
      CellFormat cellFormat5 = new
    
     CellFormat() { NumberFormatId = (UInt32Value)10U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true
    
     };
      CellFormat cellFormat6 = new
    
     CellFormat() { NumberFormatId = (UInt32Value)14U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true
    
     };
    
      cellFormats1.Append(cellFormat3);
      cellFormats1.Append(cellFormat4);
      cellFormats1.Append(cellFormat5);
      cellFormats1.Append(cellFormat6);
    
    
    
      CellStyles cellStyles1 = new
    
     CellStyles() { Count = (UInt32Value)0U };
      CellStyle cellStyle1 = new
    
     CellStyle() { Name = "Normal"
    
    , FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
    
      cellStyles1.Append(cellStyle1);
      DifferentialFormats differentialFormats1 = new
    
     DifferentialFormats() { Count = (UInt32Value)0U };
      TableStyles tableStyles1 = new
    
     TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium9"
    
    , DefaultPivotStyle = "PivotStyleLight16"
    
     };
    
      stylesheet1.Append(fonts1);
      stylesheet1.Append(fills1);
      stylesheet1.Append(borders1);
      stylesheet1.Append(cellStyleFormats1);
      stylesheet1.Append(cellFormats1);
      stylesheet1.Append(cellStyles1);
      stylesheet1.Append(differentialFormats1);
      stylesheet1.Append(tableStyles1);
    
      workbookStylesPart1.Stylesheet = stylesheet1;
     }
    

     

    Also i did some minor change in other file i.e I have a method called WriteDataByCell which is as follows

     

    public
    
     void
    
     WriteDataByCell(IList<ExcelCellData> excelData, string
    
     sheetName)
     {
      // Open the document for editing.
    
    
      using
    
     (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true
    
    ))
      {
      IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
      if
    
     (sheets.Count() == 0)
      {
       // The specified worksheet does not exist.
    
    
       return
    
    ;
      }
      WorksheetPart worksheetPart = GetWorksheetPartByName(document, sheetName);
      Worksheet worksheet = worksheetPart.Worksheet;
      SheetData sheetData = worksheet.GetFirstChild<SheetData>();
    
      SharedStringTablePart shareStringPart;
      if
    
     (document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
      {
       shareStringPart = document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
      }
      else
    
    
      {
       shareStringPart = document.WorkbookPart.AddNewPart<SharedStringTablePart>();
      }
    
      if
    
     (excelData != null
    
     && excelData.Count > 0)
      {
       UInt32Value cellStyleIndex = 0;
       foreach
    
     (ExcelCellData cellData in
    
     excelData)
       {
       // int index = InsertSharedStringItem(cellData.Text, shareStringPart);
    
    
       Cell cellRange = InsertCellInWorksheet(GetColumnName(cellData.FirstCell), GetRowIndex(cellData.FirstCell), worksheetPart);
    
       if
    
     (cellData.FirstCell.ToString() != cellData.LastCell.ToString())
       {
        if
    
     (cellData.Style.MergerCells)
        {
        //Create merge cells
    
    
        CreateMergeCells(worksheet, cellData.FirstCell, cellData.LastCell);
        }
       }
       if
    
     (cellData.Style != null
    
    )
       {
        //cell formatting indexes
    
    
        UInt32Value cellFillIndex = 0;
        UInt32Value cellBorderIndex = 0;
        UInt32Value cellFontIndex = 0;
        
    
        styleSheet = document.WorkbookPart.WorkbookStylesPart.Stylesheet;
        if
    
     (cellData.Style.BackgroundColor != null
    
     && cellData.Style.BackgroundColor != System.Drawing.Color.White)
        {
        //set the background color style
    
    
        cellFillIndex = CreateFill(styleSheet, cellData.Style.BackgroundColor);
        }
        if
    
     (cellData.Style.Borders != null
    
    )
        {
        //set border color
    
    
        cellBorderIndex = CreateBorders(styleSheet, cellData.Style.Borders.Weight, cellData.Style.Borders.Color);
        }
        //set font properties
    
    
        bool
    
     isBold = cellData.Style.Font.IsBold;
        bool
    
     isItalic = cellData.Style.Font.IsItalic;
        bool
    
     isStrikeThrough = cellData.Style.Font.Strikethrough;
        System.Drawing.Color fontColor;
        HorizontalAlignmentValues horizontalAlignment = cellData.Style.HorizontalAlignment;
        VerticalAlignmentValues verticalAlignment = cellData.Style.VerticalAlignment;
    
        if
    
     (cellData.Style.Font.Color != null
    
    )
        {
        fontColor = cellData.Style.Font.Color;
        }
        else
    
    
        {
        fontColor = System.Drawing.Color.Black;
        }
        string
    
     fontName = string
    
    .Empty;
        if
    
     (!string
    
    .IsNullOrEmpty(cellData.Style.Font.Name))
        {
        fontName = cellData.Style.Font.Name;
        }
        int
    
     fontSize = cellData.Style.Font.Size;
    
        // Create a custom stylesheet
    
    
    
        //build the formatted header style
    
    
        cellFontIndex = CreateFont(styleSheet, fontName, fontSize, isBold, fontColor, isItalic, isStrikeThrough);
        //Create the cell styles
    
    
        cellStyleIndex = CreateCellFormat(styleSheet, cellFontIndex, cellFillIndex, cellBorderIndex, horizontalAlignment, verticalAlignment, cellData.Style.WrapText);
        
        //cellRange.StyleIndex = cellStyleIndex;
    
    
        //Currently these properties are not implemented
    
    
        //cellRange.Font.Subscript = cellData.Style.Font.Subscript;
    
    
        //cellRange.Font.Superscript = cellData.Style.Font.Superscript;
    
    
        //cellRange.WrapText = cellData.Style.WrapText;
    
    
       }
    
       //int index = InsertSharedStringItem(cellData.Text, shareStringPart);
    
    
       //cellRange.CellValue = new CellValue(index.ToString());
    
    
       //cellRange.DataType = new EnumValue<CellValues>(CellValues.SharedString);
    
    
    
       //string cellCValue = GetCellValue(worksheetPart, shareStringPart, GetColumnName(cellData.FirstCell), GetRowIndex(cellData.FirstCell).ToString());
    
    
    
    // Code which i modified to accept data formats in the excel sheet...
    
    
    <br/>
       int
    
     intValue;
       //string cellValue = "";
    
    
       double
    
     doubleValue;
       DateTime dateValue;
       int
    
     index;
       if
    
     (double
    
    .TryParse(cellData.Text.ToString(), out
    
     doubleValue))
       {
        cellRange.StyleIndex = 2U;
        //cellRange.StyleIndex = NumberFormatId = (UInt32Value)0U;
    
    
        cellRange.CellValue = new
    
     CellValue(doubleValue.ToString());
       }
       else
    
     if
    
     (int
    
    .TryParse(cellData.Text.ToString(), out
    
     intValue))
       {
        cellRange.CellValue = new
    
     CellValue(intValue.ToString());
       }
       else
    
     if
    
     (DateTime.TryParse(cellData.Text.ToString(), out
    
     dateValue))
       {
        //cellRange.StyleIndex = 4U;
    
    
        //DateTime d = new DateTime(1900, 1, 1);
    
    
        //d = d.AddDays(Int32.Parse(dateValue.ToString()) - 1);
    
    
        //cellValue = d.ToShortDateString();
    
    
        cellRange.CellValue = new
    
     CellValue(dateValue.ToString());
       }
       else
    
     if
    
     (cellData.Text.EndsWith("%"
    
    ))
       {
        string
    
     data = cellData.Text.Substring(0, cellData.Text.Length-1);
        double
    
     d;
        Double.TryParse(data, out
    
     d);
        if
    
     (cellData.Text == "0.00%"
    
     || cellData.Text == "0.00 %"
    
     || cellData.Text == "0%"
    
     || cellData.Text == "0 %"
    
     || d != 0)
        {
        d = d / 100;
        cellRange.StyleIndex = 3U;
        cellRange.CellValue = new
    
     CellValue(d.ToString());
        }
        else
    
    
        {
        index = InsertSharedStringItem(cellData.Text, shareStringPart);
        cellRange.StyleIndex = 1U;
        cellRange.CellValue = new
    
     CellValue(index.ToString());
        cellRange.DataType = new
    
     EnumValue<CellValues>(CellValues.SharedString);
        }
       }
       else
    
    
       {
        index = InsertSharedStringItem(cellData.Text, shareStringPart);
        cellRange.StyleIndex = 1U;
        cellRange.CellValue = new
    
     CellValue(index.ToString());
        cellRange.DataType = new
    
     EnumValue<CellValues>(CellValues.SharedString);
       }
    
    
    
       //cellRange.DataType = new EnumValue<CellValues>(CellValues.Number);
    
    
    
    
       }
      }
      worksheet.Save();
      }
     }
    

    The above block of code is written to accept the data type and write it to excel sheet.

    My problem is data gets written correctly in the excel sheet but all the styles and fonts used in the sheet are messed up totally. i am not able to get to the solution why they are getting messed up. The code blocks above are according to the flow. In the second code block u can see that i am calling a foreach loop inside which i am specifying the styles for each cell data, i have two functions namely, CreateFont and CreateCellFormat over here all the things get messed up. i am pasting the two functions below.

    First CreateFont

     

    private
    
     UInt32Value CreateFont(Stylesheet styleSheet, string
    
     fontName, Nullable<double
    
    > fontSize, bool
    
     isBold, System.Drawing.Color foreColor, bool
    
     isItalic, bool
    
     isStrikeThrough)
     {
      Font font = new
    
     Font();
      if
    
     (isItalic)
      {
      Italic italicFont = new
    
     Italic();
      font.Italic = italicFont;
      }
      if
    
     (!string
    
    .IsNullOrEmpty(fontName))
      {
      FontName name = new
    
     FontName() { Val = fontName };
      font.Append(name);
      }
    
      if
    
     (fontSize.HasValue)
      {
      DocumentFormat.OpenXml.Spreadsheet.FontSize size = new
    
     DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = fontSize.Value };
      font.Append(size);
      }
    
      if
    
     (isBold)
      {
      Bold bold = new
    
     Bold();
      font.Append(bold);
      }
      if
    
     (isStrikeThrough)
      {
      Strike strikeThrough = new
    
     Strike();
      font.Append(strikeThrough);
      }
      if
    
     (foreColor != null
    
    )
      {
      Color color = new
    
     Color()
      {
       Rgb = new
    
     HexBinaryValue()
       {
       Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(foreColor.A, foreColor.R, foreColor.G, foreColor.B)).Replace("#"
    
    , ""
    
    )
       }
      };
      font.Append(color);
      }
      styleSheet.Fonts.Append(font);
      UInt32Value result = styleSheet.Fonts.Count;
      styleSheet.Fonts.Count++;
      return
    
     result;
     }
    

     

    The second function CreateCellFormat

     

     

    private UInt32Value CreateCellFormat(Stylesheet styleSheet, UInt32Value fontIndex, UInt32Value fillIndex, UInt32Value borders, HorizontalAlignmentValues horizontalAlignment, VerticalAlignmentValues verticalAlignment, bool wrapText)
     {
      CellFormat cellFormat = new CellFormat(new Alignment() { Horizontal = horizontalAlignment, Vertical = verticalAlignment, WrapText = wrapText });//, WrapText=true 
      if (fontIndex != null)
      cellFormat.FontId = fontIndex;
      if (fillIndex != null)
      cellFormat.FillId = fillIndex;
      if (borders != null)
      cellFormat.BorderId = borders;
    
      styleSheet.CellFormats.Append(cellFormat);
      UInt32Value result = styleSheet.CellFormats.Count;
      styleSheet.CellFormats.Count++;
      return result;
     }

     

    this two functions are messing up my styles in the excel sheet.

    The conclusion i came to is that the values of unsigned int are getting incremented continuously in the foreach loop of 2nd code block which i pasted.  I also created a replica of the output file in which i inserted the values manually and compared both the files with the OpneXML production tool, there i see major differences in the formats and fonts.

    For example my original file shows

    Fonts fonts1 = new Fonts(){ Count = (UInt32Value)7U };

    My created file shows

    Fonts fonts1 = new Fonts(){ Count = (UInt32Value)155U }; this is due to the loop of foreach in the second code block not able to figure out how to keep it to minimum.

    Then my data formats are not consistent since i have hard coded the styleindex in the second code block, i dont know how to specify proper styleIndex to each and every cell.

    I am also doubt full of Datatypes being written in the cell.

    Please help me asap, any help would be appreciated.

    Thanks

    Ravindranath

     

     

     

     

     

     

     

     

     

     

     

     

     

    • Moved by edhickey Thursday, September 16, 2010 3:07 PM (From:.NET 3.0/3.5 Windows Workflow Foundation)
    • Edited by ravindranath Friday, September 17, 2010 6:36 AM
    Thursday, September 16, 2010 2:34 PM

Answers

  • Hi Ravindrath

    Sorry to "give you the run around", and unfortunately I don't have Moderator permissions in the Excel Developer forum so I can't move this again for you... The correct place to ask about constructing a file in the OpenXML file format is the OpenXML SDK forum:

    http://social.msdn.microsoft.com/Forums/en-US/oxmlsdk/threads


    Cindy Meister, VSTO/Word MVP
    • Proposed as answer by Cindy Meister MVP Thursday, September 16, 2010 4:17 PM
    • Marked as answer by ravindranath Friday, September 17, 2010 6:18 AM
    Thursday, September 16, 2010 4:17 PM

All replies

  • Hi Ravindrath

    Sorry to "give you the run around", and unfortunately I don't have Moderator permissions in the Excel Developer forum so I can't move this again for you... The correct place to ask about constructing a file in the OpenXML file format is the OpenXML SDK forum:

    http://social.msdn.microsoft.com/Forums/en-US/oxmlsdk/threads


    Cindy Meister, VSTO/Word MVP
    • Proposed as answer by Cindy Meister MVP Thursday, September 16, 2010 4:17 PM
    • Marked as answer by ravindranath Friday, September 17, 2010 6:18 AM
    Thursday, September 16, 2010 4:17 PM
  • Thanks Cindy for your valuable suggestion, are you a developer in .Net? if yes i would like to add you in my messenger list so that i can ask queries to you if i come across, if you dont mind.
    Friday, September 17, 2010 6:18 AM
  • Hi ravindranath2

    I only reply to questions posted in these forums. Email support is reserved for paying customers...


    Cindy Meister, VSTO/Word MVP
    Friday, September 17, 2010 6:52 AM