Writing data to an Excel file

This example describes how to write data to an .xls file from process activities using a script and a template.

Writing to a file with a script

In the script a file from the stream (FileStream class methods) of the Aspose.Cells library is created to be filled in with data.

As an example, let's import data on employee ratings to Excel.

The data exported to Excel are in the Rating calibration (context.RatingCalibration context variable) and the Productivity rating (context.ProductivityRating context variable) blocks.

For the script to work correctly, add the System.Drawing assembly and the namespaces:

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

The main method for exporting data to Excel is ExportToExcel. A file is created in it with the CreateFile method and each row of the first and the second blocks are written with the FilingXLS method. After writing rows to the table, the file is saved using the file access manager DataAccessManager.FileManager.

Script:

public void ExportToExcel(Context context)
{// create a file
 CreateFile(context);
  
 // write the first table
 int indRow = 1;
 foreach (var row in context.RatingCalibration)
 {
  string kateg = "";
  if (row.Category!=null)
   kateg = row.Category.CategoryCode;
  string neobOc = "";
  if (row.AnnualAssessmentRequired)
   neobOc = "Yes";
  else
   neobOc = "No";
  string korrSales = "";
  if (row.SalesAssessmentAdjustment!=null)
   korrSales = row.SalesAssessmentAdjustment.Value;
  string korrNS = "";
  if (row.NSAssessmentAdjustment!=null)
   korrNS = row.NSAssessmentAdjustment.Value;
  string reyt = "";
  if (row.TotalEmployeeRatingAfterCalibration!=null)
   reyt = row.TotalEmployeeRatingAfterCalibration.Value;
 
  string[] parList = new string[10];
  parList[0] = row.Employee.FIO;
  parList[1] = row.PRID;
  parList[2] = kateg;
  parList[3] = neobOc;
  parList[4] = row.SalesAssessmentsFromEmployeeForm;
  parList[5] = korrSales;
  parList[6] = row.NSAssessmentsFromEmployeeForm;
  parList[7] = korrNS;
  parList[8] = row.TotalProductivityRatingFromEmployeeForm;
  parList[9] = reyt;
  FilingXLS(context, indRow, 1, parList);
  indRow++;
 }
 // insert rows from the second table
 indRow++;
 string[] parNameList = new string[4];
 parNameList[0] = "Productivity rating";
 parNameList[1] = "Number of employees in your team with rating";
 parNameList[2] = "Approved ratings scale";
 parNameList[3] = "Ratings in your team";
 FilingXLS(context, indRow, 2, parNameList);
 indRow++;
 foreach(var row in context.ProductivityRating)
 {
  string kol = "";
  if (row.EmployeeNumberInTeamWithRating!=null)
   kol = row.EmployeeNumberInTeamWithRating.ToString();
  string raspr = "";
  if (row.RatingInTeam!=null)
   raspr = row.RatingInTeam.ToString();
   
  string[] parList = new string[4];
  parList[0] = row.ProductivityRating;
  parList[1] = kol;
  parList[2] = row.ApprovedRatingScale;
  parList[3] = raspr;
  FilingXLS(context, indRow, 2, parList);
  indRow++;
 }    
 
 DataAccessManager.FileManager.SaveFile(context.File);   
}

The file is created in a special folder on the server FormTemplatePM. File name format "CalibRate<yyyymmdd>_<hh.mm>.xls". The created file is written to the context variable context.File.

Text for creating a file:

public void CreateFile(Context context)
{ 
// Create an .xls file on the server
 var PathSystem   = Locator.GetServiceNotNull<IRuntimeApplication>().Configuration.Config.FilePath.Replace("configuration.config","");
 string fullpath  = PathSystem + "FormTemplatePM\\";
 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 = "CalibRate";
  
 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);
  
 // write the file to the context variable
 context.File = fileFL;
  
 // write the first row with headers to the file
 string[] parList = new string[10];
 parList[0] = "Employee";
 parList[1] = "PRID";
 parList[2] = "Category";
 parList[3] = "Annual assessment required";
 parList[4] = "Sales assessments from the employee form";
 parList[5] = "Sales assessment adjustment";
 parList[6] = "NS assessments from the employee form";
 parList[7] = "NS assessment adjustment";
 parList[8] = "Total productivity rating from the employee form";
 parList[9] = "Total employee rating after calibration";
 FilingXLS(context, 0, 1, parList);
 
}

When writing rows to the file, classes, and methods of the Aspose.Cells library are used to refer the created .xls file, its rows, and cells.

The following parameters are passed to the FilingXLS method for writing a table row:

• сontext – process context variable;
• row – number of the created row;
• tablNumber – table number;
• list – list of cell values.

The table is formatted inside the FilingXLS method using the SetCellBord and SetColumnWidth methods.

public void FilingXLS(Context context, int row, int tablNumber, params string[] list)
{// Write a row to the file
 string filepath_excel = context.File.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); // set the cell borders
  // when calling set the column width for the first row
  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); 
 
}

In the SetCellBord method, cell borders, font, and background color are set depending on the row, column and table number.  

public void SetCellBord(Cell cell, int indRow, int indCol, Context context, Worksheet newWorksheet)
   {// set cell borders
     Style style = cell.GetStyle();
     if (indRow == (context.RatingCalibration.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.RatingCalibration.Count)||indRow == (context.RatingCalibration.Count)+context.ProductivityRating.Count + 2)
         style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium;
       if ((indCol == 0&& indRow <= context.RatingCalibration.Count)||(indCol == 6&&indRow > (context.RatingCalibration.Count)+1))
         style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Medium;
       if (indCol == 9&& (indRow <= (context.RatingCalibration.Count)||indRow > (context.RatingCalibration.Count)+1))
         style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium;
        
     }
     if (indCol > 3&&indRow <= context.RatingCalibration.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);
   }

After the export, you get a filled out file, linked to the context variable context.File.

 

Writing to a file by a template

To write to a file by a template, you need to create a template document and upload it to ELMA:

• as a document when using generation by a template document or by a script;
• in the Generate Document Version activity settings.

To write blocks to an .xls document, use this structure:

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

The values of each {$Item.Text1} field can be in separate cells and in one cell. The {for Item in {$Block}} operator must be in the first cell of a row (the first column of a sheet).

It is possible to display several blocks in one document.

 

You can use the Generate Document Version activity or a script to generate by a template.

For the script to work correctly, add the following namespaces:

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

A script for generating a document version by a template:

public void ExportToExcel(Context context)
{// create file
 int id_doc=348; //id of the template document 
 var versionFile = Locator.GetServiceNotNull<IDocumentFileManager>().GetFileByDocumentId(id_doc);
 var file = Locator.GetServiceNotNull<ITemplateFileManager>().CreateFromTemplate(versionFile.Id);
 // Create a generator
 var generator = Locator.GetServiceNotNull<DocumentGeneratorManager>().Create(file.ContentFilePath);
 // Generate the document
 generator.Generate(context);
 // Save the generated file
 Locator.GetServiceNotNull<IFileManager>().SaveFile(file);
 // Write the result to the variable
 context.File = file;
 }

Disadvantages of using generation by a template compared to creating a document in a script using the Aspose.Cells library:

• you cannot place two tables in two columns (i.e. located in the same rows) since the for and end block cycle structure commands must be in the first column;
• limited formatting of table rows (e.g. it is impossible to render the bottom table border differently from the cell borders of the rest of the table or set different background for rows.

When creating a document in a script using the Aspose.Cells library, you can structure and format tables almost in any way.