EPPLUS：DataValidation列表的长度不能超过255个字符 [英] EPPLUS: Length of a DataValidation list cannot exceed 255 characters
问题描述
这个问题在另一篇文章的基础上被回答：这里然而，对于我的情况，我无法将验证值硬编码到表格中，我根据单元格的内容从数据库中提取它们，并且需要对单元格进行单独的验证每列4列。有没有办法可以实现？谢谢你提前。
This question is answered on a basic level on another post: here However for my case I am not able to hard code the validation values into the sheet I am pulling them from a database based on the content of the cell and will need to do a separate validation for 4 columns on every row. Is there a way this can be achieved? Thank you in advance.
// Data Validations //
// Product Validation //
for (int i = 2; i < rowCount; i++)
{
var val = ws.DataValidations.AddListValidation(ws.Cells[i, 5].Address);
val.ShowErrorMessage = true;
val.ErrorTitle = "Entry was invalid.";
val.Error = "Please choose options from the drop down only.";
var ticketEntity = ticketQueryable.Where(o => o.TTSTicketNumber == ws.Cells[i, 3].Value.ToString()).Single<CustCurrentTicketEntity>();
var prodIds = prodExtQueryable.Where(p => p.ZoneId == ticketEntity.ZoneId && p.TicketTypeId == ticketEntity.TicketTypeId);
if (ticketEntity != null)
{
var prodIdsList = new List<int>();
foreach (var prodId in prodIds)
{
prodIdsList.Add(prodId.ProductId);
}
var ProductList = ProductCache.Instance.AllProducts.Where(p => prodIdsList.Contains(p.ProductId)).Select(p => new SelectListItem() { Value = p.ProductId.ToString(), Text = p.Name });
foreach (var Result in ProductList)
{
var product = Result.Text;
val.Formula.Values.Add(product);
}
}
}
推荐答案
所以是的，因为Ernie说我做的是添加第二张ProductValidations并将其设置为Hidden（取消隐藏以检查它是否正常工作）。然后我从DataTable加载我的数据，然后添加一些基本的EPPLUS格式。然后，我将遍历Rows并将值插入到每个单元格的ProductValidations表中。接下来，我将列号转换为正确的Excel列字母名称（A，AC，BCE等），然后创建一个字符串作为以ProductValidations表格中的正确范围的Excel公式为目标的Excel公式传回。另外对于从服务器下载Excel文件问题的任何人来说，这个guid方法对我来说很正常。
So yes as Ernie said What I did was add a second sheet "ProductValidations" and set it to Hidden (unhide it to check that it is working). I then Load my data from the DataTable and then add some basic EPPLUS formatting. I then iterate over the Rows and Insert values into the "ProductValidations" sheet for each cell. Next I convert my column number to the correct Excel Column letter name (A, AC, BCE etc) I then create a string to pass back as an Excel formula targeting the correct range of cells in the "ProductValidations" sheet. Also to anyone having an issue downloading the Excel file from the server this guid method works just fine for me.
public ActionResult DownloadExcel(EntityReportModel erModel, string filename)
{
var dataResponse = iEntityViewService.LoadEntityView(new EntityViewInput
{
SecurityContext = SessionCache.Instance.SecurityContext,
EntityViewName = "Ticket",
Parameters = new Dictionary<string, object> {
{"MinTicketDateTime", "04/26/16"}
}
});
var table = dataResponse.DataSet.Tables[0];
filename = "TICKETS-" + DateTime.Now.ToString("yyyy-MM-dd--hh-mm-ss") + ".xlsx";
using (ExcelPackage pack = new ExcelPackage())
{
ExcelWorksheet ws = pack.Workbook.Worksheets.Add(filename);
//Add second sheet to put Validations into
ExcelWorksheet productVal = pack.Workbook.Worksheets.Add("ProductValidations");
// Hide Validation Sheet
productVal.Hidden = OfficeOpenXml.eWorkSheetHidden.Hidden;
// Load the data from the datatable
ws.Cells["A1"].LoadFromDataTable(table, true);
ws.Cells[ws.Dimension.Address].AutoFitColumns();
int columnCount = table.Columns.Count;
int rowCount = table.Rows.Count;
// Format Worksheet//
ws.Row(1).Style.Font.Bold = true;
List<string> deleteColumns = new List<string>() {
"CurrentTicketId",
};
List<string> dateColumns = new List<string>() {
"TicketDateTime",
"Updated",
"InvoiceDate"
};
ExcelRange r;
// Format Dates
for (int i = 1; i <= columnCount; i++)
{
// if cell header value matches a date column
if (dateColumns.Contains(ws.Cells[1, i].Value.ToString()))
{
r = ws.Cells[2, i, rowCount + 1, i];
r.AutoFitColumns();
r.Style.Numberformat.Format = @"mm/dd/yyyy hh:mm";
}
}
// Delete Columns
for (int i = 1; i <= columnCount; i++)
{
// if cell header value matches a delete column
if ((ws.Cells[1, i].Value != null) && deleteColumns.Contains(ws.Cells[1, i].Value.ToString()))
{
ws.DeleteColumn(i);
}
}
int col = 0;
int Prow = 0;
int valRow = 1;
// Data Validations //
// Iterate over the Rows and insert Validations
for (int i = 2; i-2 < rowCount; i++)
{
Prow = 0;
col++;
valRow++;
// Add Validations At this row in column 7 //
var ProdVal = ws.DataValidations.AddListValidation(ws.Cells[valRow, 7].Address);
ProdVal.ShowErrorMessage = true;
ProdVal.ErrorTitle = "Entry was invalid.";
ProdVal.Error = "Please choose options from the drop down only.";
var ticketEntity = ticketQueryable.Where(o => o.TTSTicketNumber == ws.Cells[i, 3].Value.ToString()).Single<CustCurrentTicketEntity>();
// Product Validation //
var prodIds = prodExtQueryable.Where(p => p.ZoneId == ticketEntity.ZoneId && p.TicketTypeId == ticketEntity.TicketTypeId);
if (ticketEntity != null)
{
var prodIdsList = new List<int>();
foreach (var prodId in prodIds)
{
prodIdsList.Add(prodId.ProductId);
}
var ProductList = ProductCache.Instance.AllProducts.Where(p => prodIdsList.Contains(p.ProductId)).Select(p => new SelectListItem() { Value = p.ProductId.ToString(), Text = p.Name });
//For Each Item in the list move the row forward and add that value to the Validation Sheet
foreach (var Result in ProductList)
{
Prow++;
var product = Result.Text;
productVal.Cells[Prow, col].Value = product;
}
// convert column name from a number to the Excel Letters i.e A, AC, BCE//
int dividend = col;
string columnName = String.Empty;
int modulo;
while (dividend > 0)
{
modulo = (dividend - 1) % 26;
columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
dividend = (int)((dividend - modulo) / 26);
}
// Pass back to sheeet as an Excel Formula to get around the 255 Char limit for Validations//
string productValidationExcelFormula = "ProductValidations!" + columnName + "1:" + columnName + Prow;
ProdVal.Formula.ExcelFormula = productValidationExcelFormula;
}
}
// Save File //
var fileStream = new MemoryStream(pack.GetAsByteArray());
string handle = Guid.NewGuid().ToString();
fileStream.Position = 0;
TempData[handle] = fileStream.ToArray();
// Note we are returning a filename as well as the handle
return new JsonResult()
{
Data = new { FileGuid = handle, FileName = filename }
};
}
}
[HttpGet]
public virtual ActionResult Download(string fileGuid, string fileName)
{
if (TempData[fileGuid] != null)
{
byte[] data = TempData[fileGuid] as byte[];
return File(data, "application/vnd.ms-excel", fileName);
}
else
{
//Log err
return new EmptyResult();
}
}
这篇关于EPPLUS：DataValidation列表的长度不能超过255个字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
