Запись данных в Excel файл

В примере рассмотрим запись данных в файл формата *.xls из блоков процесса с помощью сценария и с помощью шаблона.

Запись в файл сценарием

В сценарии используется создание файла из потока (методы класса FileStream) библиотеки Aspose.Cells для заполнения файла данными.

В качестве примера рассмотрим экспорт в Excel данных о рейтингах сотрудников.

Экспортируемые в Excel данные представлены в блоках Калибровка рейтингов (переменная контекста context. KalibrovkaReytingov) и Рейтинг результативности (переменная контекста context. ReytingRezuljtativnosti).

Для корректной работы сценария необходимо подключить сборку System.Drawing и пространства имен:

using System;
using Aspose.Cells;
using EleWise.ELMA.Runtime; 
using EleWise.ELMA.Runtime.Managers;
using System.Drawing;
using System.IO;

Основной метод для экспорта данных в Excel – ExportToExcel. В нем создается файл методом CreateFile и каждая строка первого и второго блока записываются методом FilingXLS. После записи строк в таблицу файл сохраняется с использованием менеджера доступа к файлам DataAccessManager.FileManager.

Текст сценария:

  public void ExportToExcel(Context context)
  {// создать файл
   CreateFile(context);
   
   // записать первую таблицу
   int indRow = 1;
   foreach (var row in context.KalibrovkaReytingov)
   {
    string kateg = "";
    if (row.Kategoriya!=null)
     kateg = row.Kategoriya.KodKategorii;
    string neobOc = "";
    if (row.NeobhodimostjProvestiGodovuyuOcenku)
     neobOc = "Да";
    else
     neobOc = "Нет";
    string korrSales = "";
    if (row.KorrektirovkaOcenkiZaSales!=null)
     korrSales = row.KorrektirovkaOcenkiZaSales.Value;
    string korrNS = "";
    if (row.KorrektirovkOcenkiZaNS!=null)
     korrNS = row.KorrektirovkOcenkiZaNS.Value;
    string reyt = "";
    if (row.ItogovyyReytingSotrudnikaPoRezuljtatamKalibrovki!=null)
     reyt = row.ItogovyyReytingSotrudnikaPoRezuljtatamKalibrovki.Value;

    string[] parList = new string[10];
    parList[0] = row.Sotrudnik.FIO;
    parList[1] = row.PRID;
    parList[2] = kateg;
    parList[3] = neobOc;
    parList[4] = row.OcenkiSalesIzFormySotrudnika;
    parList[5] = korrSales;
    parList[6] = row.OcenkiZaNSIzFormySotrudnika;
    parList[7] = korrNS;
    parList[8] = row.ItogovyyReytingRezuljtativnostiIzFormySotrudnika;
    parList[9] = reyt;
    FilingXLS(context, indRow, 1, parList);
    indRow++;
   }
   // вставить строки из второй таблице
   indRow++;
   string[] parNameList = new string[4];
   parNameList[0] = "Рейтинг результативности";
   parNameList[1] = "Количество сотрудников в вашей команде с рейтингом";
   parNameList[2] = "Утвержденная шкала распределения рейтингов";
   parNameList[3] = "Распределение рейтингов в вашей команде";
   FilingXLS(context, indRow, 2, parNameList);
   indRow++;
   foreach(var row in context.ReytingRezuljtativnosti)
   {
    string kol = "";
    if (row.KolichestvoSotrudnikovVKomandeSReytingom!=null)
     kol = row.KolichestvoSotrudnikovVKomandeSReytingom.ToString();
    string raspr = "";
    if (row.RaspredelenieReytingovVVasheyKomande!=null)
     raspr = row.RaspredelenieReytingovVVasheyKomande.ToString();
    
    string[] parList = new string[4];
    parList[0] = row.ReytingRezuljtativnosti;
    parList[1] = kol;
    parList[2] = row.UtverzhdennayaShkalaRaspredeleniyaReytingov;
    parList[3] = raspr;
    FilingXLS(context, indRow, 2, parList);
    indRow++;
   }    
  
   DataAccessManager.FileManager.SaveFile(context.Fayl);   
  }

Файл создается в специально созданной папке на сервере FormShablonPM. Формат наименования файла «KalibReyt<ггггммдд>_<чч.мм>.xls». Созданный файл записывается в контекстную переменную context.Fayl.

Текст сценария по созданию файла:

  public void CreateFile(Context context)
  { 
  // Создать файл на сервере формата *.xls
   var PathSystem   = Locator.GetServiceNotNull<IRuntimeApplication>().Configuration.Config.FilePath.Replace("configuration.config","");
   string fullpath  = PathSystem + "FormShablonPM\\";
   DateTime now = DateTime.Now; 
   string nowForFileName = now.Year.ToString();
   if (now.Month< 10)
    nowForFileName += "0" + now.Month.ToString();
   else
    nowForFileName += now.Month.ToString();
   
   if (now.Day< 10)
    nowForFileName += "0" + now.Day.ToString();
   else
    nowForFileName += now.Day.ToString();
   nowForFileName += "_" ;   
   
   Console.WriteLine(now.ToString().Substring(12,1));
   if (now.ToString().Substring(12,1) == ":")
    nowForFileName+= now.ToString().Substring(11,1) + "." + now.ToString().Substring(13,2);
   else
    nowForFileName+= now.ToString().Substring(11,2) + "." + now.ToString().Substring(14,2);
   
   string name = "KalibReyt";
   
   string fileName = fullpath + name + nowForFileName + ".xls";
   Stream stream = new FileStream(fileName, FileMode.Create);
   var temp = BinaryFile.CreateContentFilePath(fileName);
   using (var fs = new FileStream(temp, FileMode.CreateNew, FileAccess.Write))
   {
    stream.Seek(0, SeekOrigin.Begin);
    stream.CopyTo(fs);
   }
   var mimeMappingService = Locator.GetServiceNotNull<IMimeMappingService>();
   var fileFL = new BinaryFile
   {
    ContentType = mimeMappingService.GetTypeByExtension(Path.GetExtension(fileName)),
    Name = Path.GetFileName(fileName),
    ContentFilePath = temp,
    CreateDate = DateTime.Now,
   };
   
   DataAccessManager.FileManager.SaveFile(fileFL);
   
   // записать файл в контекстную переменную
   context.Fayl = fileFL;
   
   // записать в файл первую строку с заголовками
   string[] parList = new string[10];
   parList[0] = "Сотрудник";
   parList[1] = "PRID";
   parList[2] = "Категория";
   parList[3] = "Необходимость провести годовую оценку";
   parList[4] = "Оценки Sales из формы сотрудника";
   parList[5] = "Корректировка оценки за Sales";
   parList[6] = "Оценки за NS из формы сотрудника";
   parList[7] = "Корректировка оценки за NS";
   parList[8] = "Итоговый рейтинг результативности из формы сотрудника";
   parList[9] = "Итоговый рейтинг сотрудника по результатам калибровки";
   FilingXLS(context, 0, 1, parList);

  }

При записи строк в файл для обращения к созданному файлу формата .xls, его строкам и ячейкам используются классы и методы библиотеки Aspose.Cells.

В метод FilingXLS для записи строки таблицы передаются параметры:

  • сontext – переменная контекста процесса;
  • row – номер создаваемой строки;
  • tablNumber – номер таблицы;
  • list – список значений ячеек.

Форматирование таблицы производится внутри метода FilingXLS с помощью метода SetCellBord и SetColumnWidth.

  public void FilingXLS(Context context, int row, int tablNumber, params string[] list)
  {// Записать строку в файл
   string filepath_excel = context.Fayl.ContentFilePath;
   Workbook workbook = new Workbook(filepath_excel);
   workbook.Settings.Encoding = Encoding.GetEncoding(1251);
   Worksheet newWorksheet;
   newWorksheet = workbook.Worksheets[0];
   Cells cells = newWorksheet.Cells;   
   
   int beginInd = 0;
   int countRow = list.Count();
   if (tablNumber == 2){
    beginInd = 6;
    countRow = list.Count()+6;}
   for (int i = beginInd; i < countRow; i++)
   {
    int j = i;
    if (tablNumber == 2)
     j = i - 6;
    cells[row, i].PutValue(list[j]);
    
    SetCellBord(cells[row, i], row, i, context, newWorksheet); // установить границы ячейки
    // при вызове для первой строки установить ширину столбцов
    if (row ==0){
     cells.SetColumnWidth(j, 18);
     if (i == 0)
      cells.SetColumnWidth(j, 30);
     if (i==1)
      cells.SetColumnWidth(j, 12);
     if (i == 2)
      cells.SetColumnWidth(j, 15);
     if (i == 5||i == 7)
      cells.SetColumnWidth(j, 16);}
    
   }
   workbook.Save(filepath_excel); 
  
  }

В методе SetCellBord динамически в зависимости от номера строки, столбца и таблицы устанавливаются границы ячеек, шрифт и цвет фона.  

 public void SetCellBord(Cell cell, int indRow, int indCol, Context context, Worksheet newWorksheet)
    {// установить границы ячейки
      Style style = cell.GetStyle();
      if (indRow == (context.KalibrovkaReytingov.Count + 2)||indRow == 0){
        style.IsTextWrapped = true;
        style.Font.IsBold = true; 
        style.Pattern = BackgroundType.Solid;
        if (indRow == 0)
          style.ForegroundColor = Color.FromArgb(197, 217, 241); 
        else
          style.ForegroundColor = Color.FromArgb(255, 204, 153); 
        style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Medium;
        style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium;
        style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Medium;
        style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium;
      }
      else
      {        
        style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
        style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
        style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
        style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;      
        if (indRow == (context.KalibrovkaReytingov.Count)||indRow == (context.KalibrovkaReytingov.Count)+context.ReytingRezuljtativnosti.Count + 2)
          style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium;
        if ((indCol == 0&& indRow <= context.KalibrovkaReytingov.Count)||(indCol == 6&&indRow > (context.KalibrovkaReytingov.Count)+1))
          style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Medium;
        if (indCol == 9&& (indRow <= (context.KalibrovkaReytingov.Count)||indRow > (context.KalibrovkaReytingov.Count)+1))
          style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium;
        
      }
      if (indCol > 3&&indRow <= context.KalibrovkaReytingov.Count ){
        style.Font.IsBold = true; }
      
      style.Borders[BorderType.TopBorder].Color = Color.FromArgb(128, 128, 128); 
      style.Borders[BorderType.BottomBorder].Color = Color.FromArgb(128, 128, 128);
      style.Borders[BorderType.LeftBorder].Color = Color.FromArgb(128, 128, 128);
      style.Borders[BorderType.RightBorder].Color = Color.FromArgb(128, 128, 128);
      style.Font.Size = 10;
      style.Font.Name = "Calibri";

      cell.SetStyle(style);
    }

После выполнения экспорта получаем заполненный файл, связанный с контекстной переменной context.Fayl.

 

Запись в файл по шаблону

Для записи в файл по шаблону необходимо сформировать документ-шаблон и загрузить его в систему:

  • как документ при использовании генерации по шаблону-документу или при генерации сценарием;
  • в настройках блока Генерация документа.

Для вывода блоков в документ формата *.xls используем конструкцию:

{for Item in {$Blok}}
{$Item.Tekst1} {$Item.Tekst2} {$Item.Tekst3}
{end}

Значения каждого из полей {$Item.Tekst1} может располагаться как в отдельной ячейке, так и в одной ячейке. Оператор {for Item in {$Blok}} должен располагаться в первой ячейке строки (в первом столбце листа).

Есть возможность выводить несколько блоков в одном документе.

 

Генерацию по шаблону можно выполнить с помощью блока Генерация документа или в сценарии.

Для корректной работы сценария по генерации документа из шаблона необходимо подключить пространства имен:

using EleWise.ELMA.Documents.Managers;
using EleWise.ELMA.Runtime.Managers;
using EleWise.ELMA.Services; 
using EleWise.ELMA.Templates;

Текст сценария генерации документа по шаблону:

  public void ExportToExcel(Context context)
  {// создать файл
   int id_doc=348; //идентификатор документа, в котором содержится шаблон 
   var versionFile = Locator.GetServiceNotNull<IDocumentFileManager>().GetFileByDocumentId(id_doc);
   var file = Locator.GetServiceNotNull<ITemplateFileManager>().CreateFromTemplate(versionFile.Id);
   // Создаем генератор
   var generator = Locator.GetServiceNotNull<DocumentGeneratorManager>().Create(file.ContentFilePath);
   // Генерируем документ
   generator.Generate(context);
   // Сохраняем файл сгенерированного документа
   Locator.GetServiceNotNull<IFileManager>().SaveFile(file);
   // Записываем результат в переменную
   context.Fayl = file;
   }

Минусы использования генерации по шаблону по сравнению с созданием документа в сценарии с использованием библиотеки Aspose.Cells:

  • в документе нельзя расположить 2 таблицы в 2 столбца (то есть располагающихся в одних строках), так как команды конструкции цикла по блоку for и end должны располагаться в первом столбце;
  • ограниченные возможности форматирования строк таблиц (например, нельзя отрисовать нижнюю границу  таблицы, отличную от границ ячеек всей остальной таблицы или задать разный фон для строк).

При создании документа в сценарии с использованием библиотеки Aspose.Cells возможности конструирования и форматирования таблиц практически не ограничены.