private static void PrintListValidationDetails(ExcelWorksheet sheet, IExcelDataValidationList listValidation, int row)
{
string value = string.Empty;
// if formula is used - show it...
if (!string.IsNullOrEmpty(listValidation.Formula.ExcelFormula))
{
value = listValidation.Formula.ExcelFormula;
}
else
{
// otherwise - show the values from the list collection
var sb = new StringBuilder();
foreach (var listValue in listValidation.Formula.Values)
{
if (sb.Length > 0)
{
sb.Append(",");
}
sb.Append(listValue);
}
value = sb.ToString();
}
sheet.Cells["D" + row.ToString()].Value = value;
}
/// <summary>
/// Hàm tạo combobox trong excel
/// </summary>
/// <param name="sheet">sheet cần xét</param>
/// <param name="columnName">Tên cột cần fill</param>
/// <param name="startRow">Hàng bắt đầu</param>
/// <param name="totalRow">Tổng số hàng muốn fill</param>
/// <param name="listValue">Danh sách dữ liệu cần fill</param>
public ExcelWorksheet FillComboboxToExcel(ExcelWorksheet sheet, string columnName, int startRow, int totalRow, List <string> listValue)
{
IExcelDataValidationList val = null;
if (startRow > 0 && listValue.Count > 0)
{
for (int i = 0; i <= totalRow; i++)
{
val = sheet.DataValidations.AddListValidation((columnName + (startRow + i)));
for (int j = 0; j < listValue.Count(); j++)
{
val.Formula.Values.Add(listValue[j]);
}
}
}
return(sheet);
}
/// <summary>
/// 添加列表数据验证
/// </summary>
/// <param name="collection">数据验证集合</param>
/// <param name="address">有效作用域，如：A:A，A2:A65535</param>
/// <param name="listData">列表数据集合</param>
/// <param name="errorStyle">验证失败提示样式</param>
/// <param name="allowBlank">数据是否允许为空</param>
/// <param name="showErrorMessage">是否显示验证失败的提示消息</param>
/// <param name="error">验证失败提示消息</param>
/// <returns>返回列表数据验证对象</returns>
public static IExcelDataValidationList AddListValidation(this ExcelDataValidationCollection collection,
String address, String [] listData, ExcelDataValidationWarningStyle errorStyle = ExcelDataValidationWarningStyle.stop,
Boolean allowBlank = true, Boolean showErrorMessage = true,
String error = null)
{
IExcelDataValidationList validationList = collection.AddListValidation(address);
foreach (var data in listData)
{
validationList.Formula.Values.Add(data);
}
validationList.AllowBlank = allowBlank;
validationList.ErrorStyle = errorStyle;
validationList.ShowErrorMessage = showErrorMessage;
validationList.Error = error ?? String.Format("只允许数据内容[{0}]", listData.ToSqlDataString());
return(validationList);
}
/// <summary>
/// Hàm tạo combobox trong excel
/// </summary>
/// <param name="sheet">sheet cần xét</param>
/// <param name="columnName">Tên cột cần fill</param>
/// <param name="startRow">Hàng bắt đầu</param>
/// <param name="totalRow">Tổng số hàng muốn fill</param>
/// <param name="listValue">Danh sách dữ liệu cần fill</param>
public static void ListValidationExcel(this ExcelWorksheet sheet,
string columnName, int startRow, int totalRow, List <string> listValue)
{
IExcelDataValidationList val = null;
if (startRow > 0 && listValue.Count > 0)
{
for (int i = 0; i <= totalRow; i++)
{
val = sheet.DataValidations.AddListValidation((columnName + (startRow + i)));
for (int j = 0; j < listValue.Count(); j++)
{
val.Formula.Values.Add(listValue[j]);
}
// val.ShowErrorMessage = true;
// val.Error = "Chỉ được phép chọn dữ liệu trong danh sách";
}
}
}
public void Setup()
{
SetupTestData();
_validation = _sheet.Workbook.Worksheets[1].DataValidations.AddListValidation("A1");
}
public void Setup()
{
SetupTestData();
_validation = _sheet.Workbook.Worksheets[1].DataValidations.AddListValidation("A1");
}
private static void PrintListValidationDetails(ExcelWorksheet sheet, IExcelDataValidationList listValidation, int row)
{
string value = string.Empty;
// if formula is used - show it...
if(!string.IsNullOrEmpty(listValidation.Formula.ExcelFormula))
{
value = listValidation.Formula.ExcelFormula;
}
else
{
// otherwise - show the values from the list collection
var sb = new StringBuilder();
foreach(var listValue in listValidation.Formula.Values)
{
if(sb.Length > 0)
{
sb.Append(",");
}
sb.Append(listValue);
}
value = sb.ToString();
}
sheet.Cells["D" + row.ToString()].Value = value;
}
