Generating xls documents with checkboxes in C#

A short while ago I had a task that required generating excel documents (xls) with special OLE objects. Checkboxes, to be precise.

Problem overview

The functionality was fairly simple. User generated a password protected excel document with some empty fields to be filled by somebody else. The document was handled over to another user who filled in the mandatory and optional fields. Marked some check boxes and send back. As the final step the document was processed by the program and all fields there were mapped to corresponding properties in the model.

Technically I wanted a excel checkbox (OLE object) to me mapped as boolean property in my C# class. In my opinion this would be natural.

Possibilities

I’ve checked several libraries including SpreadsheetLight, EPPlus or Telerik Reporting but none of the allowed me to add check boxes in a easy way (if it was possible at all). I didn’t want to use Microsoft.Office.Interop.Excel.dll, because (as far as I know) this would require installing Office on each machine, where my program in installed.

The solution

Finally I checked XlsIO package from Syncfusion.

The way how document is created in code looks pretty much similar to other libraries. Except checkboxes! To generate a checkbox in Excel (so a OLE object) using XlsIO you need to do only the following:

ICheckBoxShape checkBox = cell.Worksheet.CheckBoxes.AddCheckBox(cell.Row, cell.Column, x, y);
checkBox.LinkedCell = checkBoxCellLogicalData;

All what we do here is declaring a checkbox, putting it in a selected cell and linking it with a cell holding logical value. So now, when in Excel, we will change the checkbox state then the LinkedCell value will be updated. We need to remember to hide the LinekdCell 🙂 This can be done via FormulaHidden property on CellStyle

To read the value back from Excel document we need to access the value of LinkedCell

Summary

I was really surprised that only one library allowed making such a simple solution. Anyway it is faster to use an existing component instead of spending time on own solution.

Follow me:

One thought on “Generating xls documents with checkboxes in C#

  1. Pingback: dotnetomaniak.pl

Leave a Reply

Your email address will not be published. Required fields are marked *