Telerik blogs
XamarinT2 Dark_1200x303

The Telerik SpreadProcessing library for Xamarin enables you to easily create from scratch, modify existing documents or convert between the most common spreadsheet formats. You can save the generated workbook to a stream, or to a local file. 

Need to generate XLSX, CSV or PDF documents in your Xamarin App? You've come to the right place! Our team shipped support for the SpreadProcessing library as part of our Telerik UI for Xamarin R1 2020 release.

The RadSpreadProcessing library that ships with Telerik UI for Xamarin, is a .NET Standard library that enables you to easily create, modify or convert documents between the most common spreadsheet formats. You can then save the generated workbook to a stream.

In this blog post we are going to pass through the creation of a payroll report from scratch, but first let’s check what the document model of the RadSpreadProcessing library consists of.

The basic elements in the document model of RadSpreadProcessing library are the following:

  • Workbook is the core of the document model. It has several important characteristics such as Active Worksheet, History, Collection of Cell Styles, Theme and others. It can also be viewed as a collection of worksheets.
  • Worksheet is a collection of cells organized in rows and columns.
  • Cell is the basic data unit in a worksheet, the intersection point of a column and a row.
  • Rows and Columns are groups of cells which are respectively on the same horizontal or vertical line.

Supported Formats

The Xamarin SpreadProcessing library allow you to easily open and save files of different formats.

Currently, with RadSpreadProcessing you can:

  • Import and export Office Open XML Workbook (.XLSX)
  • Import and export Comma Separated Values (.CSV)
  • Import and export Tab Separated Values (.TXT)
  • Export Portable Document Format (.PDF)
  • Export the spreadsheet as document to a Stream or byte array

The processing library doesn’t need any external dependencies in order to convert documents from/to the supported formats. The document model is UI-independent.

Payroll Report Creation Demo

Before we get started, make sure to add references to the following assemblies in your project:

  • Telerik.Documents.Core.dll
  • Telerik.Documents.Spreadsheet.dll
  • Telerik.Documents.Spreadsheet.FormatProviders.OpenXml.dll
  • Telerik.Zip.dll

Let’s see what the document is going to look like at the end:

Complete Document

Loading the Data

Let’s create a Workbook with a single worksheet named “Payroll Report” in it:

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Name = "Payroll Report";

We are making some preparations like setting the main column headers:

worksheet.Cells[1, 0].SetValue("Employee");
worksheet.Cells[1, 1].SetValue("2015");
worksheet.Cells[1, 2].SetValue("2016");
worksheet.Cells[1, 3].SetValue("2017");
worksheet.Cells[1, 4].SetValue("2018");
worksheet.Cells[1, 5].SetValue("2019");

Now, it is time to enter the payroll data. The following snippets illustrate how to input the information from the PayrollModel array to the worksheet:

public static IEnumerable<PayrollModel> GetPayrollData()
{
PayrollModel[] payroll = new PayrollModel[]
{
new PayrollModel()
{
EmployeeName = "Jasper Garcia",
EmployeeSalaries = new List<int>()
{
10400,
12300,
12300,
13300,
14000,
}
},
new PayrollModel()
{
EmployeeName = "Junior Rivera",
...
int currentCol = 1;
foreach (PayrollModel model in PayrollModel.GetPayrollData())
{
int currentRow = 1;
worksheet.Cells[currentRow++, currentCol].SetValue(model.EmployeeName);
foreach (int salary in model.EmployeeSalaries)
{
worksheet.Cells[currentRow++, currentCol].SetValue(salary);
}
currentCol++;
}

After the implementation, this should be the worksheet as seen in the Excel app:

Data set

Setting the Title

We are going to set the title after we have populated the data because we want to take advantage of the Worksheet's GetUsedCellRange method. This will let us find out which is the last used column index and to use it in the cell selection to Merge all the needed cells:

CellRange usedCellRange = worksheet.GetUsedCellRange(new IPropertyDefinition[] { CellPropertyDefinitions.ValueProperty });
int lastColumnIndex = usedCellRange.ToIndex.ColumnIndex;
CellSelection titleCells = worksheet.Cells[0, 1, 0, lastColumnIndex];
titleCells.SetValue("Company Name");
titleCells.Merge();

Document Title Set

Styles

Let's create some styles:

CellStyle normalStyle = workbook.Styles["Normal"];
normalStyle.Fill = PatternFill.CreateSolidFill(new ThemableColor(ThemeColorType.Background2));
normalStyle.FontFamily = new ThemableFontFamily(ThemeFontType.Minor);
normalStyle.FontSize = UnitHelper.PointToDip(10);
normalStyle.VerticalAlignment = RadVerticalAlignment.Center;
CellStyle companyNameStyle = workbook.Styles.Add("CompanyNameStyle");
companyNameStyle.FontFamily = new ThemableFontFamily(ThemeFontType.Major);
companyNameStyle.FontSize = UnitHelper.PointToDip(32);
companyNameStyle.HorizontalAlignment = RadHorizontalAlignment.Center;
CellStyle columnHeadersStyle = workbook.Styles.Add("ColumnHeadersStyle");
columnHeadersStyle.FontFamily = new ThemableFontFamily(ThemeFontType.Major);
columnHeadersStyle.BottomBorder = new CellBorder(CellBorderStyle.Thick, new ThemableColor(ThemeColorType.Accent6));
columnHeadersStyle.FontSize = UnitHelper.PointToDip(12);
CellStyle editableAreaStyle = workbook.Styles.Add("EditableAreaStyle");
editableAreaStyle.Fill = PatternFill.CreateSolidFill(new ThemableColor(ThemeColorType.Background1));
editableAreaStyle.FontFamily = new ThemableFontFamily("Segoe UI Light");
editableAreaStyle.FontSize = UnitHelper.PointToDip(10);
editableAreaStyle.HorizontalAlignment = RadHorizontalAlignment.Right;
CellStyle totalStyle = workbook.Styles.Add("TotalStyle");
totalStyle.Fill = PatternFill.CreateSolidFill(new ThemableColor(ThemeColorType.Accent6));
totalStyle.FontSize = UnitHelper.PointToDip(12);
totalStyle.ForeColor = new ThemableColor(ThemeColorType.Background1);

Applying the already created styles:

CellRange usedCellRange = worksheet.GetUsedCellRange(new IPropertyDefinition[] { CellPropertyDefinitions.ValueProperty });
int lastColumnIndex = usedCellRange.ToIndex.ColumnIndex;
int lastRowIndex = usedCellRange.ToIndex.RowIndex;
worksheet.Cells[0, 1, 0, lastColumnIndex].SetStyleName("CompanyNameStyle");
worksheet.Cells[1, 0, 1, lastColumnIndex].SetStyleName("ColumnHeadersStyle");
worksheet.Cells[2, 1, lastRowIndex, lastColumnIndex].SetStyleName("EditableAreaStyle");
worksheet.Cells[lastRowIndex + 1, 0, lastRowIndex + 1, lastColumnIndex].SetStyleName("TotalStyle");

For a more complete look we are going to import a company logo:

FloatingImage companyLogo = new FloatingImage(worksheet, new CellIndex(0, 0), 1, 1)
{
Width = 65,
Height = 65,
};
using (Stream stream = ...)
{
companyLogo.ImageSource = new Telerik.Windows.Documents.Media.ImageSource(stream, "jpg");
}
worksheet.Shapes.Add(companyLogo);

After such improvements, let’s check out how the document looks:

Styles Applied

We are very close to the desired result.

When it comes to setting the width of the column (or the height of the rows) we have two options: the first one is to benefit from the Columns' AutoFitWidth method (or Rows' AutoFitHeight method) and the second one is to set their exact width and height using respectively SetWidth or SetHeight methods. In our example we are mixing both approaches:

CellRange usedCellRange = worksheet.GetUsedCellRange(new IPropertyDefinition[] { CellPropertyDefinitions.ValueProperty });
int lastColumnIndex = usedCellRange.ToIndex.ColumnIndex;
worksheet.Columns[0, lastColumnIndex].AutoFitWidth();
double rowHeight = companyLogo.Height;
worksheet.Rows[0].SetHeight(new RowHeight(rowHeight, true));

We already have our document and its formatting set up, now let's see how we can manipulate it using a bucket of valuable features.

Formulas

Let’s start with Formulas. The formula is an expression calculating the value of a cell in the spreadsheet. The library comes with more than 200 built-in functions. The API enables you to easily plug custom ones as well. Using the GetUsedCellRange method again is going to help us to iterate all the cells containing data and to set the value of a pre-prepared formula:

CellRange usedCellRange = worksheet.GetUsedCellRange(new IPropertyDefinition[] { CellPropertyDefinitions.ValueProperty });
int firstRowIndex = 3;
int lastUsedRowIndex = usedCellRange.ToIndex.RowIndex;
worksheet.Cells[lastUsedRowIndex + 1, 0].SetValue("TOTALS");
for (int columnIndex = 1; columnIndex <= usedCellRange.ToIndex.ColumnIndex; columnIndex++)
{
char currentColumnName = NameConverter.ConvertColumnIndexToName(columnIndex);
string formula = $"=Sum({currentColumnName}{firstRowIndex}:{currentColumnName}{lastUsedRowIndex + 1})";
worksheet.Cells[lastUsedRowIndex + 1, columnIndex].SetValue(formula);
}

Autofitted

Worksheet Protection

The second feature we are going to examine is Worksheet protection.You can protect every single worksheet from editing. The model offers protection options that let you choose a set of commands available to the user when protection is enabled. In our example we are going to protect all the cells excluding the ones containing the annual salaries.

CellRange usedCellRange = worksheet.GetUsedCellRange(new IPropertyDefinition[] { CellPropertyDefinitions.ValueProperty });
int lastRowIndex = usedCellRange.ToIndex.RowIndex;
int lastColumnIndex = usedCellRange.ToIndex.ColumnIndex;
CellRange cellRange = new CellRange(2, 1, lastRowIndex - 1, lastColumnIndex);
worksheet.Cells[cellRange].SetIsLocked(false);
WorksheetProtectionOptions options =
new WorksheetProtectionOptions(allowFiltering: false, allowSorting: false, allowFormatColumns: true, allowDeleteRows: false);
worksheet.Protect("password", options);

Protection Set

Find and Replace

You can easily Find and Replace a particular number or a text string in a workbook, worksheet or a specified range of cells. In the following code snippet, we are going to change the title “Company name” with a custom one: “My Company.”

ReplaceOptions options = new ReplaceOptions()
{
StartCell = new WorksheetCellIndex(worksheet, 0, 0),
FindWhat = "Company Name",
ReplaceWith = "My Company",
FindWithin = FindWithin.Sheet,
};
FindResult findResult = workbook.Find(options);
options.StartCell = findResult.FoundCell;
workbook.Replace(options);

Title Replaced

Freeze Panes

Freeze Panes allows you to always keep part of the worksheet visible when scrolling. You can easily implement this using the FreezePanes method accessible through the ViewState property of the Worksheet.

CellIndex fixedPaneTopLeftCellIndex = new CellIndex(0, 0);
worksheet.ViewState.FreezePanes(fixedPaneTopLeftCellIndex, 2, 1);

Panes Freezed

Data Validation

With the Data Validation functionality we are going to take control over what the user can enter into a cell. Different data validation rules are available, including list, number, date, text length or custom rules. For our example, we are going to use a data validation to make sure the salary input is a number between 0 and 80000.

CellRange usedCellRange = worksheet.GetUsedCellRange(new IPropertyDefinition[] { CellPropertyDefinitions.ValueProperty });
int lastRowIndex = usedCellRange.ToIndex.RowIndex;
int lastColumnIndex = usedCellRange.ToIndex.ColumnIndex;
CellRange cellRange = new CellRange(2, 1, lastRowIndex - 1, lastColumnIndex);
CellIndex dataValidationRuleCellIndex = new CellIndex(0, 0);
NumberDataValidationRuleContext context = new NumberDataValidationRuleContext(worksheet, dataValidationRuleCellIndex)
{
InputMessageTitle = "Restricted input",
InputMessageContent = "The input is restricted to whole number values between 0 and 80000",
ErrorStyle = ErrorStyle.Stop,
ErrorAlertTitle = "Wrong value",
ErrorAlertContent = "The entered value is not valid. Allowed values are in the range between 0 and 80000!",
IgnoreBlank = false,
ComparisonOperator = ComparisonOperator.Between,
Argument1 = "0",
Argument2 = "80000"

Data Validation Set

Grouping & Hiding

The RadSpreadProcessing library provides two different approaches for changing the visibility of the data so the user can define what they would like to see.

  1. The first approach we are going to examine is Grouping. You can select the cells that you want to collapse and assign the outline level property of the rows/columns or use the Group method exposed by the row/column selection classes. In our example we are going to make two groups:
    RowSelection groupOne = worksheet.Rows[2, 13];
    RowSelection groupTwo = worksheet.Rows[2, 4];
    worksheet.Rows[groupOne.CellRanges].Group();
    worksheet.Rows[groupTwo.CellRanges].Group();

    Rows Grouped
    Note: In order to use the group option, you need to disable the protection of the sheet.
  2. The second approach is to use the SetHidden method exposed by the RowSelection and ColumnSelection classes to change the hidden state of the rows/columns.
    RowSelection rowSelection = worksheet.Rows[5, 18];
    rowSelection.SetHidden(true);

    Rows Hidden

Export the Spreadsheet

And finally, we are going to export the Worksheet to XLSX using the XlsxFormatProvider. Check the following code snippet:

IWorkbookFormatProvider formatProvider = new XlsxFormatProvider();
using (Stream stream = new MemoryStream())
{
formatProvider.Export(workbook, stream);
// Use the stream to display or save the file
}

In addition, the Xamarin SpreadProcessing library offers tons of other features like automatic fill of data, Workbook Protection, Styling, Theming, Resizing, Clipboard Support, Shapes and Images, History, Format Codes, Hyperlinks, Number Formats, Filtering, Sorting, Page Setup, Headers and Footers, add or copy worksheets within or across workbooks and of course, copying and pasting of cells is supported as well.

If you can’t see a feature you have in mind - contact us, we probably do have it. We'd love to hear your thoughts, so feel free to head on over to our Xamarin Feedback Portal to let us know what you think and what you'd like to see next.

In this blog post we’ve learned how to create, modify and export the content of the worksheet to XLSX document. Download the latest version of Telerik UI for Xamarin from your account, or start a free trial to see it in action today.

Try Telerik UI for Xamarin


profile_pic_cropped
About the Author

Martin Velikov

Martin is a Software Engineer, part of the Document Processing team in Sofia, Bulgaria since July 2019. He is passionate about new technologies and is always on the crest of a wave with the novelties. In his spare time, Martin likes travelling to new destinations and exploring new cultures, hanging out with friends, reading books, practicing sports, and more.

Related Posts

Comments

Comments are disabled in preview mode.