Thursday, June 9, 2016

Read and write to an EXCEL document

Need a NuGet package for Bytescount

Spreadsheet inputDoc = new Spreadsheet();
      Spreadsheet outputDoc = new Spreadsheet();
      inputDoc.LoadFromFile("D:Landing_Pages.xlsx");

      Worksheet newSheet = outputDoc.Workbook.Worksheets.Add("HighPriorityPages");
      newSheet.Columns[1].Width = 400;
      newSheet.Columns[3].Width = 400;
      int newRowCount = 1;

      for (int x = 0; x < inputDoc.Workbook.Worksheets.Count; x++)
      {
       
        // Get worksheet by name
        Worksheet worksheet = inputDoc.Workbook.Worksheets[x];
       

        int lastRow = worksheet.NotEmptyRowMax;
        int priorityColumn = worksheet.NotEmptyColumnMax;

       for (int i = 0; i < lastRow; i++)
        {
          // Set current cell
          Cell currentinputCell = worksheet.Cell(i, priorityColumn);
          if (currentinputCell.Value != null && currentinputCell.Value.ToString().ToLower().Contains("priority") && (currentinputCell.Value.ToString().Contains("3") || currentinputCell.Value.ToString().Contains("2")))
          {
            string path = worksheet.Cell(i, 4).Value.ToString();

            Item scItem = Sitecore.Context.Database.GetItem(path);

            if (scItem != null)
            {
              newSheet.Cell(newRowCount, 0).Value = worksheet.Name;
              newSheet.Cell(newRowCount, 1).Value = path;
              newSheet.Cell(newRowCount, 2).Value = currentinputCell.Value;
              newSheet.Cell(newRowCount, 3).Value = scItem.TemplateName;
              newRowCount++;
            }
          }
        }

        inputDoc.Close();
      }
     
      // Save document
      outputDoc.SaveAs("D:Landing_Pages_Filtered.xlsx");
      // Close Spreadsheet
      outputDoc.Close();

No comments:

Post a Comment