Showing posts with label OpenXml. Show all posts
Showing posts with label OpenXml. Show all posts

Thursday, October 18, 2012

OpenXml Reset Numbering Level


I want to create Word Document using OpenXml and create numbering level. I faced a problem when I want to start numbering Level.
To Solve this problem you need to create new instance from abstract number using function as below:

   
      static Numbering AddAbstractNumbering(Numbering numbering1, int numberId,
             int abstractNumber)
      {
          NumberingInstance numberingInstance6 = new NumberingInstance ()
          {NumberID = numberId};          
          AbstractNumId abstractNumId6 = new AbstractNumId() { Val = 1 };
          numberingInstance6.Append(abstractNumId6,
             new LevelOverride() {
                    StartOverrideNumberingValue = new StartOverrideNumberingValue() { Val = 1 }
             } );
          numbering1.Append(numberingInstance6);
          return numbering1;
      }


and use this instance to add numbering level to your paragraph:

   
      Paragraph paragraph2 = new Paragraph();
      ParagraphProperties paragraphProperties1 = new ParagraphProperties();
      ParagraphStyleId paragraphStyleId1 = new ParagraphStyleId() { Val = "ListParagraph" };
      NumberingProperties numberingProperties1 = new NumberingProperties();
      NumberingLevelReference numberingLevelReference1 =  
                                            new NumberingLevelReference() { Val = 0 };
      NumberingId numberingId1 = null;
      if (isNumberingStart)
      {
             numberId++;
             numbering1 = AddAbstractNumbering(numbering1, numberId);
             isNumberingStart = false;
       }
       numberingId1 = new NumberingId() { Val = numberId };
       numberingProperties1.Append(numberingLevelReference1);
       numberingProperties1.Append(numberingId1);

       paragraphProperties1.Append(paragraphStyleId1);
       paragraphProperties1.Append(numberingProperties1);

        paragraph2.Append(paragraphProperties1);

Wednesday, May 30, 2012

Getting Date from Excel using openXml

When iterated through excel and read cell contains date it returned int value. you can read cell contains date and convert to simple date format using code below:
  Cell cellDate;            
    string newDate;            
    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open("2.xlsx", false))            
   {
                var sheets = spreadsheetDocument.WorkbookPart.Workbook.Descendants();

                foreach (Sheet sheet in sheets)
                {
                    var sheets = spreadsheetDocument.WorkbookPart.Workbook.Descendants();
                    foreach (Sheet sheet in sheets)
                   {
                       List news = new List();
                       uint rowIndex = 1;
                       uint colIndex = 1;
                       WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheet.Id);

                       Worksheet worksheet = worksheetPart.Worksheet;
                       SheetData sheetData = worksheet.GetFirstChild();
                       IEnumerator rows = sheetData
                                            .Elements().GetEnumerator();
                      while (rows.MoveNext())
                      {
                        Row row = rows.Current;
                        cellDate = GetCell(sheetData, "C" + rowIndex.ToString());
                        if (cellDate.DataType != null && cellDate.DataType == CellValues.SharedString)
                        {
                             newDate = GetSharedStringItemById(spreadsheetDocument.WorkbookPart, int.Parse(cellDate.CellValue.InnerText));
                        }
                        else
                        {
                           newDate = DateTime.FromOADate(double.Parse(((CellValue)cellDate.FirstChild).Text)).ToString();
                        }
              }
           }
        }
     }

   public static Cell GetCell(SheetData sheetData, string fullAddress)
   {
            return sheetData.Descendants()
                .Where(c => c.CellReference == fullAddress)
                .FirstOrDefault();
   }

    private static Cell GetCell(Worksheet worksheet, string columnName, uint rowIndex)
    {
          Row row = GetRow(worksheet, rowIndex);

          if (row == null)
                return null;

          return row.Elements().Where(c => string.Compare
                   (c.CellReference.Value, columnName +
                   rowIndex, true) == 0).FirstOrDefault();
    }


    // Given a worksheet and a row index, return the row.
    private static Row GetRow(Worksheet worksheet, uint rowIndex)
    {
          return worksheet.GetFirstChild().
              Elements().Where(r => r.RowIndex == rowIndex).FirstOrDefault();
    }

    public static string GetSharedStringItemById(WorkbookPart workbookPart, int id)
    {
          return workbookPart.SharedStringTablePart.SharedStringTable.Elements()
                .ElementAt(id).InnerText;
    }