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. A 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 sent it 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 them 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 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 we change the checkbox state in Excel, the LinkedCell value will be updated. We need to remember to hide the LinkedCell :) 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 us to make such a simple solution. Anyway, it is faster to use an existing component instead of spending time on your own solution.