SharePoint 2013 : OpenXML au service de SharePoint 4/5 et 5/5

Posted on

Quatrième de la série, après avoir vu la génération de document Word, puis PowerPoint, attaquons l’utilisation d’OpenXML avec Excel.

L’exemple présenté va nous permettre de générer des lignes de façon dynamique et de les remplir depuis nos listes SharePoint.

 

ExcelProcessing

Nous créons un simple document Excel de ce type :

image

 

Côté SharePoint, une liste simple avec 2 Champs ( Title & Description), une fois créée regardons le code.

Helper Class

Dans cette classe, nous retrouvons quelques méthode très utile qui vont nous permettre d’effectuer des manipulations dans notre tableau.

  • CreateCell : Création de cellule
  • InsertCellInWorksheet :  Insertion d’une cellule ( dépend de CreateCell)
  • InsertRow: Insertion d’une ligne
  • GetCellInWorksheet : Récupération d’une cellule
  • UpdateCell : Mise à jour d’une cellule
public static class Helper
    {
        public static Cell CreateCell(string text, string column, uint lineNumber,
        Worksheet wsheet)
        {
            Cell newCell = InsertCellInWorksheet(column, lineNumber, wsheet);
            newCell.CellValue = new CellValue(text);
            newCell.DataType = new EnumValue<CellValues>(CellValues.String);
            return newCell;
        }

        public static Cell InsertCellInWorksheet(string columnName, uint rowIndex,
       Worksheet worksheet)
        {
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();
            string cellReference = columnName + rowIndex;
            Row row;
            Cell refCell = null;
            if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
            {
                row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            }
            else
            {
                row = new Row() { RowIndex = rowIndex };
                sheetData.Append(row);
            }
            if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
            {
                refCell = row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
            }
            else
            {
                foreach (Cell cell in row.Elements<Cell>())
                {
                    if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                    {
                        refCell = cell;
                        break;
                    }
                }

                Cell newCell = new Cell() { CellReference = cellReference };
                row.InsertBefore(newCell, refCell);


                refCell = newCell;
                worksheet.Save();
            }

            return refCell;
        }
        public static void InsertRow(Worksheet worksheet)
        {
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();
            Row lastRow = sheetData.Elements<Row>().LastOrDefault();

            if (lastRow != null)
            {
                sheetData.InsertAfter(new Row() {
                RowIndex = (lastRow.RowIndex + 1) }, lastRow);
            }

        }

        public static void UpdateCell(Worksheet worksheet, string text, uint rowIndex,
        string columnName)
        {
            if (worksheet != null)
            {
                Cell cell = GetCellInWorksheet(columnName, rowIndex, worksheet);

                cell.CellValue = new CellValue(text);
                cell.DataType = new EnumValue<CellValues>(CellValues.String);


            }

        }
        public static Cell GetCellInWorksheet(string columnName, uint rowIndex,
        Worksheet worksheet)
        {
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();
            string cellReference = columnName + rowIndex;
            Row row;
            Cell refCell = null;
            if (sheetData.Elements<Row>().
                          Where(r => r.RowIndex == rowIndex).Count() != 0)
                row = sheetData.Elements<Row>().
                            Where(r => r.RowIndex == rowIndex).First();
            else
                return null;

            if (row.Elements<Cell>().
              Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
                refCell = row.Elements<Cell>().
                Where(c => c.CellReference.Value == cellReference).First();

            return refCell;
        }
    }

 

Code de génération

Maintenant il ne nous reste à appliquer ces méthodes dans notre code :

 

using (SpreadsheetDocument outDoc = SpreadsheetDocument.Open(docstream, true))
     {
          WorkbookPart wbPart = outDoc.WorkbookPart;
          Sheet tabSheet = wbPart.Workbook.Descendants<Sheet>().
          Where(s => s.Name != null && s.Name.Value.ToString().Trim() == "Demo").FirstOrDefault();

          if (tabSheet != null)
            {
                Worksheet wsheet = ((WorksheetPart)(wbPart.GetPartById(tabSheet.Id))).Worksheet;
                 SheetData sheetData = wsheet.GetFirstChild<SheetData>();
                 SPList spList = web.Lists.TryGetList("demo");
                  if (spList != null)
                  {
                     SPQuery qry = new SPQuery();
                     qry.ViewFields = @"<FieldRef Name='Description' /><FieldRef Name='Title' />";
                     SPListItemCollection listitems = spList.GetItems(qry);

                     uint startline = 6;

                     foreach (SPListItem item in listitems)
                    {
                       Helper.InsertRow(wsheet);//Insert after the last row
                       Cell cellA = Helper.CreateCell(item["Title"].ToString(), "A", startline , wsheet);
                       Cell cellB = Helper.CreateCell(item["Description"].ToString(), "B", startline, wsheet);
                        startline++;
                    }
                }
              //Update a Simple Cell
             Helper.UpdateCell(wsheet, "New Value in Cell", 3, "A");
             wsheet.Save();
}}

Résultat :

image

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s