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 handed over to another user who filled in the mandatory and optional fields. Marked some checkboxes 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 an excel checkbox (OLE object) to be 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 checkboxes in an 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 is installed.

The solution

Finally I checked XlsIO package from Syncfusion.

The way how a document is created in code looks pretty much similar to other libraries. Except checkboxes! To generate a checkbox in Excel (so an 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 that 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.