/// <summary>
///
/// </summary>
/// <param name="workbook"></param>
/// <param name="col"></param>
/// <param name="fromRow"></param>
/// <param name="toRow"></param>
private void CreateDropDownSerialNumberExcel(WorkBook workbook, DataColumn col, int fromRow, int toRow)
{
var list = TimeSheetMachineController.GetAll(null, null, null, null, null, null, null);
if (list == null)
{
return;
}
var validation = workbook.CreateDataValidation();
validation.Type = DataValidation.eUser;
var validateList = "\"{0}\"".FormatWith(string.Join(",", list.Select(l => l.SerialNumber + " ({0})".FormatWith(l.Id)).ToList()));
// formula string length cannot be greater than 256
if (validateList.Length < 256)
{
// set formula by string
validation.Formula1 = validateList;
}
else
{
var columnName = GetExcelColumnName(col.Ordinal + 1);
// select info sheet
workbook.Sheet = 1;
// write list into info sheet
foreach (var item in list.Select((value, index) => new { value, index }))
{
workbook.setText(item.index + 1, col.Ordinal, item.value.Name + " ({0})".FormatWith(item.value.Id));
}
// select
workbook.Sheet = 0;
// set formula by selected range
validation.Formula1 = "Info!${0}$2:${0}${1}".FormatWith(columnName, list.Count);
}
// selection range
workbook.setSelection(fromRow, col.Ordinal, toRow, col.Ordinal);
workbook.DataValidation = validation;
}
