none
Cell Formats not getting applied in my excel sheet RRS feed

  • Question

  • Hi i am new to documentFormat.OpenXML i am facing a problem in rendering the numbers, double data types and percentage data type in the excel sheet. My code is as follows:

    First Method for each cell data:

     

    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);
                  }
                }
    
               //Here i am applying the styles for each cell
    
                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);
                }
    
                int intValue;
                double doubleValue;
                DateTime dateValue;
                int index;
                if (double.TryParse(cellData.Text.ToString(), out doubleValue))
                {
                  cellRange.StyleIndex = new UInt32Value(cellStyleIndex);
                  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.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 = new UInt32Value(cellStyleIndex);
                    cellRange.CellValue = new CellValue(d.ToString());
                  }
                  else
                  {
                    index = InsertSharedStringItem(cellData.Text, shareStringPart);
                    cellRange.StyleIndex = new UInt32Value(cellStyleIndex);
                    cellRange.CellValue = new CellValue(index.ToString());
                    cellRange.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                  }
                }
                else
                {
                  index = InsertSharedStringItem(cellData.Text, shareStringPart);
                  cellRange.StyleIndex = new UInt32Value(cellStyleIndex);
                  cellRange.CellValue = new CellValue(index.ToString());
                  cellRange.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                }
                //cellRange.DataType = new EnumValue<CellValues>(CellValues.Number);
    
    
              }
            }
            worksheet.Save();
          }
        }

    in the above code i am calling a function called CreateCellFormat(); where for each cell it creates a cell format, i am pasting the function as below:

     

    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 });
          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;
        }

    Now the above function over here generates a cell format for each cell, in fact i have converted all the numbers to number cells but the format which i want i am not able to achieve it. Say for example i want numbers to be displayed as (12,345) and percentage to be displayed as (56.98%) in the excel sheet. At present the numbers are shown as (12345) and percentage is shown as (0.5698) in the excel sheet. i am unable to get a solution please give a solution asap, any help will be deeply appreciated.

    Thanks,

    Ravindranath

     

     

    Friday, September 17, 2010 6:34 AM

Answers

All replies