void Button2_Click(object sender, EventArgs e)
{
NpoiLib npl = new NpoiLib();
List<Row> list = npl.ReadExcel(Server.MapPath("text.xls"), 0);
Response.Write("<table><tr><td>111</td><td>222</td><td>333</td></tr>");
int i = 0;
string[] explicitListValues = new string[] { "AS", "CE CP", "SC IC", "Others" };
foreach (Row r in list)
{
if (r != null)
{
Cell c = r.GetCell(0);
NPOI.SS.Util.CellRangeAddressList ranglist = new NPOI.SS.Util.CellRangeAddressList();
ranglist.AddCellRangeAddress(new CellRangeAddress(0, 10, 0, 3));
DVConstraint dvconstraint = DVConstraint.CreateExplicitListConstraint(explicitListValues);
HSSFDataValidation dataValidation = new HSSFDataValidation(ranglist, dvconstraint);
((HSSFSheet)c.Sheet).AddValidationData(dataValidation);
string v1 = npl.ReadRowData(r, 0);
string v2 = npl.ReadRowData(r, 1);
string v3 = npl.ReadRowData(r, 2);
Response.Write("<tr><td>" + v1 + "</td><td>" + v2 + "</td><td>" + v3 + "</td></tr>");
}
}
Response.Write("</table>");
}
static void Main(string[] args)
{
InitializeWorkbook();
ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
ISheet sheet2 = hssfworkbook.CreateSheet("Sheet2");
//create three items in Sheet2
IRow row0 = sheet2.CreateRow(0);
ICell cell0 = row0.CreateCell(4);
cell0.SetCellValue("Product1");
row0 = sheet2.CreateRow(1);
cell0 = row0.CreateCell(4);
cell0.SetCellValue("Product2");
row0 = sheet2.CreateRow(2);
cell0 = row0.CreateCell(4);
cell0.SetCellValue("Product3");
CellRangeAddressList rangeList = new CellRangeAddressList();
//add the data validation to the first column (1-100 rows)
rangeList.AddCellRangeAddress(new CellRangeAddress(1, 100, 0, 0));
DVConstraint dvconstraint = DVConstraint.CreateFormulaListConstraint("Sheet2!$E1:$E3");
HSSFDataValidation dataValidation = new
HSSFDataValidation(rangeList, dvconstraint);
//add the data validation to sheet1
((HSSFSheet)sheet1).AddValidationData(dataValidation);
WriteToFile();
}
/**
* Constructor which Initializes the cell range on which this object will be
* applied
* @param constraint
*/
public HSSFDataValidation(CellRangeAddressList regions, IDataValidationConstraint constraint)
{
_regions = regions;
//FIXME: This cast can be avoided.
_constraint = (DVConstraint)constraint;
}
public XSSFDataValidation(XSSFDataValidationConstraint constraint, CellRangeAddressList regions, CT_DataValidation ctDataValidation)
: base()
{
this.validationConstraint = constraint;
this.ctDdataValidation = ctDataValidation;
this.regions = regions;
}
public CFHeaderRecord(RecordInputStream in1)
{
field_1_numcf = in1.ReadShort();
field_2_need_recalculation = in1.ReadShort();
field_3_enclosing_cell_range = new CellRangeAddress(in1);
field_4_cell_ranges = new CellRangeAddressList(in1);
}
static void Main(string[] args)
{
string workbookName = "test.xlsx";
IWorkbook workbook = null;
ISheet sheet = null;
IDataValidationHelper dvHelper = null;
IDataValidationConstraint dvConstraint = null;
IDataValidation validation = null;
CellRangeAddressList addressList = null;
// Using the ss.usermodel allows this class to support both binary
// and xml based workbooks. The choice of which one to create is
// made by checking the file extension.
if (workbookName.EndsWith(".xlsx"))
{
workbook = new XSSFWorkbook();
}
else
{
workbook = new HSSFWorkbook();
}
// Build the sheet that will hold the data for the validations. This
// must be done first as it will create names that are referenced
// later.
sheet = workbook.CreateSheet("Linked Validations");
BuildDataSheet(sheet);
// Build the first data validation to occupy cell A1. Note
// that it retrieves it's data from the named area or region called
// CHOICES. Further information about this can be found in the
// static buildDataSheet() method below.
addressList = new CellRangeAddressList(0, 0, 0, 0);
dvHelper = sheet.GetDataValidationHelper();
dvConstraint = dvHelper.CreateFormulaListConstraint("CHOICES");
validation = dvHelper.CreateValidation(dvConstraint, addressList);
sheet.AddValidationData(validation);
// Now, build the linked or dependent drop down list that will
// occupy cell B1. The key to the whole process is the use of the
// INDIRECT() function. In the buildDataSheet(0 method, a series of
// named regions are created and the names of three of them mirror
// the options available to the user in the first drop down list
// (in cell A1). Using the INDIRECT() function makes it possible
// to convert the selection the user makes in that first drop down
// into the addresses of a named region of cells and then to use
// those cells to populate the second drop down list.
addressList = new CellRangeAddressList(0, 0, 1, 1);
dvConstraint = dvHelper.CreateFormulaListConstraint(
"INDIRECT(UPPER($A$1))");
validation = dvHelper.CreateValidation(dvConstraint, addressList);
sheet.AddValidationData(validation);
FileStream sw = File.OpenWrite(workbookName);
workbook.Write(sw);
sw.Close();
}
public XSSFDataValidation(CellRangeAddressList regions, CT_DataValidation ctDataValidation)
: base()
{
this.validationConstraint = GetConstraint(ctDataValidation);
this.ctDdataValidation = ctDataValidation;
this.regions = regions;
this.ctDdataValidation.errorStyle = (ST_DataValidationErrorStyle.stop);
this.ctDdataValidation.allowBlank = (true);
}
public CellRangeAddressList Copy()
{
CellRangeAddressList result = new CellRangeAddressList();
int nItems = _list.Count;
for (int k = 0; k < nItems; k++)
{
CellRangeAddress region = (CellRangeAddress)_list[k];
result.AddCellRangeAddress(region.Copy());
}
return(result);
}
public void TestPLVRecord1()
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(XLS_FILENAME);
HSSFWorkbook workbook = new HSSFWorkbook(is1);
CellRangeAddressList cellRange = new CellRangeAddressList(0, 0, 1, 1);
IDataValidationConstraint constraint = DVConstraint.CreateFormulaListConstraint(DV_DEFINITION);
HSSFDataValidation dataValidation = new HSSFDataValidation(cellRange, constraint);
// This used to throw an error before
try
{
workbook.GetSheet(SHEET_NAME).AddValidationData(dataValidation);
}
catch (InvalidOperationException)
{
Assert.Fail("Identified bug 53972, PLV record breaks addDataValidation()");
}
}
public void Test53965()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
List<XSSFDataValidation> lst = sheet.GetDataValidations(); //<-- works
Assert.AreEqual(0, lst.Count);
//create the cell that will have the validation applied
sheet.CreateRow(0).CreateCell(0);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateCustomConstraint("SUM($A$1:$A$1) <= 3500");
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, addressList);
sheet.AddValidationData(validation);
// this line caused XmlValueOutOfRangeException , see Bugzilla 3965
lst = sheet.GetDataValidations();
Assert.AreEqual(1, lst.Count);
}
//private BaseForm getCurrentForm()
//{
// BaseForm baseForm = null;
// using (Repositories.BaseFormRepository repository = new Repositories.BaseFormRepository(null))
// {
// baseForm = repository.getInstanceByPeriodDate(DateTime.Now);
// }
// return baseForm;
//}
/// <summary>
/// Traduz de Questionário da Amarribo para arquivo excel (Workbook)
/// </summary>
/// <param name="baseForm">Questionário de transparência</param>
/// <param name="protectSheets">Flag de proteção do questionário</param>
/// <returns></returns>
private XSSFWorkbook translateFormToWorkBook(BaseForm baseForm, bool protectSheets, long rfId = 0)
{
try
{
XSSFWorkbook wb = null;
if (baseForm != null)
{
wb = createWorkBook();
IFont titleFont = wb.CreateFont();
IFont subTitleFont = wb.CreateFont();
IFont titleQuestion = wb.CreateFont();
titleFont.FontHeight = 20;
subTitleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
subTitleFont.FontHeight = 15;
titleQuestion.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
ICellStyle cellTitleStyle = wb.CreateCellStyle();
//cellTitleStyle.Alignment = HorizontalAlignment.Center;
cellTitleStyle.SetFont(titleFont);
ICellStyle cellSubTitleStyle = wb.CreateCellStyle();
cellSubTitleStyle.SetFont(subTitleFont);
//cellSubTitleStyle.Alignment = HorizontalAlignment.Center;
ICellStyle cellTitleQuestionStyle = wb.CreateCellStyle();
cellTitleQuestionStyle.SetFont(titleQuestion);
ICellStyle cellQuestion = wb.CreateCellStyle();
cellQuestion.WrapText = true;
ICellStyle cellAnswer = wb.CreateCellStyle();
cellAnswer.IsLocked = false;
ICellStyle cellHidden = wb.CreateCellStyle();
cellHidden.IsHidden = true;
if (baseForm.BaseBlocks != null)
{
//Será criada uma sheet única para todas as perguntas
XSSFSheet sheet = null;
//Total rows no atual bloco
int row = 0;
//Titulo da aba tem limite de 30 caracteres.
var sheetName = baseForm.Name.Length > 30 ? baseForm.Name.Substring(0, 30) : baseForm.Name;
sheet = (XSSFSheet)wb.CreateSheet(Commons.StringUtils.removeSpecialCaracters(sheetName));
if (protectSheets)
sheet.ProtectSheet(PROTECT_SHEET_PASSWORD);
if (baseForm.BaseBlocks != null)
{
if (baseForm.BaseBlocks.Count > 0)
{
int count = 1;
foreach (var bb in baseForm.BaseBlocks)
{
if (bb.BaseSubBlocks != null && bb.BaseSubBlocks.Count > 0)
{
sheet.CreateRow(row).CreateCell(0).SetCellValue(bb.Name);
sheet.GetRow(row).GetCell(0).CellStyle = cellTitleStyle;
sheet.GetRow(row).CreateCell(4).SetCellValue("B");
//Merge celula do titulo
sheet.AddMergedRegion(new CellRangeAddress(row, row, 0, 3));
row++;
//Para cada subbloco
foreach (var bsb in bb.BaseSubBlocks)
{
sheet.CreateRow(row).CreateCell(0).SetCellValue(bsb.Name);
sheet.GetRow(row).GetCell(0).CellStyle = cellSubTitleStyle;
sheet.GetRow(row).CreateCell(4).SetCellValue("S");
//Merge celula do titulo
sheet.AddMergedRegion(new CellRangeAddress(row, row, 0, 3));
row++;
//Adiciona os subtitulos
sheet.CreateRow(row).CreateCell(0).SetCellValue("Índice");
sheet.GetRow(row).GetCell(0).CellStyle = cellTitleQuestionStyle;
sheet.GetRow(row).CreateCell(1).SetCellValue("Pergunta");
sheet.GetRow(row).GetCell(1).CellStyle = cellTitleQuestionStyle;
sheet.GetRow(row).CreateCell(2).SetCellValue("Resposta");
sheet.GetRow(row).GetCell(2).CellStyle = cellTitleQuestionStyle;
sheet.GetRow(row).CreateCell(3).SetCellValue("Observações");
sheet.GetRow(row).GetCell(3).CellStyle = cellTitleQuestionStyle;
sheet.GetRow(row).CreateCell(4).SetCellValue("TP");
row++;
if (bsb.BaseQuestions != null && bsb.BaseQuestions.Count > 0)
{
//Utilizado para gerar com respostas
//var rand = new Random(DateTime.Now.Millisecond);
int initRow = row;
foreach (var bq in bsb.BaseQuestions)
{
Entities.Answer answer = null;
if (rfId > 0)
{
answer = bq.Answers.Where(f => f.ResponseFormId == rfId).FirstOrDefault();
}
//Indice da pergunta
sheet.CreateRow(row).CreateCell(0).SetCellValue(count);
//Pergunta
//sheet.GetRow(row).CreateCell(1).SetCellValue(bq.Question);
ICell cellQ = sheet.GetRow(row).CreateCell(1);
cellQ.CellStyle = cellQuestion;
formatStyleQuestion(bq.Question, ref cellQ, ref wb);
//Adiciona o campo com dropdown e libera para edição
//int r = rand.Next(1, 4);
if (answer != null && rfId > 0)
{
if (answer.Score.HasValue)
{
sheet.GetRow(row).CreateCell(2).SetCellValue(answer.Score.Value.ToString());//("");//(actions[r])
}
else
{
sheet.GetRow(row).CreateCell(2).SetCellValue("N/A");//("");//(actions[r])
}
}
else
{
sheet.GetRow(row).CreateCell(2).SetCellValue("");//("");//(actions[r])
}
sheet.GetRow(row).GetCell(2).CellStyle = cellAnswer;
//observações.
sheet.GetRow(row).CreateCell(3).SetCellValue("");
sheet.GetRow(row).GetCell(3).CellStyle = cellAnswer;
//Tipo da linha
sheet.GetRow(row).CreateCell(4).SetCellValue("P");
sheet.GetRow(row).GetCell(4).CellStyle = cellHidden;
//Adiciona o id da pergunta
sheet.GetRow(row).CreateCell(5).SetCellValue(bq.Id);
sheet.GetRow(row).GetCell(5).CellStyle = cellHidden;
//Id do formulario
sheet.GetRow(row).CreateCell(6).SetCellValue(bq.BaseSubBlock.BaseBlock.BaseFormId);
sheet.GetRow(row).GetCell(6).CellStyle = cellHidden;
//Id do responseForm
sheet.GetRow(row).CreateCell(7).SetCellValue(rfId);
sheet.GetRow(row).GetCell(7).CellStyle = cellHidden;
row++;
count++;
}
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint constraint = (XSSFDataValidationConstraint)dvHelper.CreateExplicitListConstraint(actions);
CellRangeAddressList addressList = new CellRangeAddressList(initRow, row - 1, 2, 2);
XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(constraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Valor inválido", "Valor inserido não é permitido");
sheet.AddValidationData(validation);
}
}
}
}
}
}
sheet.SetColumnWidth(1, 25500);
sheet.SetColumnWidth(3, 25500);
sheet.SetColumnWidth(4, 0); //0 de width para esconder a coluna do id, NPOI não tem solução para hide de coluna
sheet.SetColumnWidth(5, 0); //0 de width para esconder a coluna do id, NPOI não tem solução para hide de coluna
sheet.SetColumnWidth(6, 0); //0 de width para esconder a coluna do id, NPOI não tem solução para hide de coluna
sheet.SetColumnWidth(7, 0); //0 de width para esconder a coluna do id, NPOI não tem solução para hide de coluna
}
}
return wb;
}
catch (Exception ex)
{
Lib.Log.ErrorLog.saveError("Lib.Utils.ExcelUtils.translateFormToWorkBook", ex);
throw ex;
}
}
//YK: GetXYZArray() array accessors are deprecated in xmlbeans with JDK 1.5 support
public List<XSSFDataValidation> GetDataValidations()
{
List<XSSFDataValidation> xssfValidations = new List<XSSFDataValidation>();
CT_DataValidations dataValidations = this.worksheet.dataValidations;
if (dataValidations != null && dataValidations.count > 0)
{
foreach (CT_DataValidation ctDataValidation in dataValidations.dataValidation)
{
CellRangeAddressList addressList = new CellRangeAddressList();
List<String> sqref = ctDataValidation.sqref;
foreach (String stRef in sqref)
{
String[] regions = stRef.Split(new char[] { ' ' });
for (int i = 0; i < regions.Length; i++)
{
String[] parts = regions[i].Split(new char[] { ':' });
CellReference begin = new CellReference(parts[0]);
CellReference end = parts.Length > 1 ? new CellReference(parts[1]) : begin;
CellRangeAddress cellRangeAddress = new CellRangeAddress(begin.Row, end.Row, begin.Col, end.Col);
addressList.AddCellRangeAddress(cellRangeAddress);
}
}
XSSFDataValidation xssfDataValidation = new XSSFDataValidation(addressList, ctDataValidation);
xssfValidations.Add(xssfDataValidation);
}
}
return xssfValidations;
}
public void TestGetDataValidationsFormula()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateCustomConstraint("A2:A3");
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.FORMULA, c.GetValidationType());
Assert.AreEqual("A2:A3", c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
}
/*
* (non-Javadoc)
*
* @see
* NPOI.SS.UserModel.DataValidationHelper#CreateValidation(org
* .apache.poi.SS.UserModel.DataValidationConstraint,
* NPOI.SS.Util.CellRangeAddressList)
*/
public IDataValidation CreateValidation(IDataValidationConstraint constraint, CellRangeAddressList cellRangeAddressList)
{
return new HSSFDataValidation(cellRangeAddressList, constraint);
}
public void TestGetDataValidationsListExplicit()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateExplicitListConstraint(new String[] { "aaa",
"bbb", "ccc" });
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
validation.SuppressDropDownArrow = (/*setter*/true);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
Assert.AreEqual(true, dv.SuppressDropDownArrow);
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.LIST, c.GetValidationType());
Assert.AreEqual(null, c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
String[] values = c.ExplicitListValues;
Assert.AreEqual(3, values.Length);
Assert.AreEqual("aaa", values[0]);
Assert.AreEqual("bbb", values[1]);
Assert.AreEqual("ccc", values[2]);
}
public void TestGetDataValidationsListFormula()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateFormulaListConstraint("A2");
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
validation.SuppressDropDownArrow = (/*setter*/true);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
Assert.AreEqual(true, dv.SuppressDropDownArrow);
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.LIST, c.GetValidationType());
Assert.AreEqual("A2", c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
}
public void TestGetDataValidationsDecimal(){
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateDecimalConstraint(OperatorType.BETWEEN, "=A2",
"200");
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.DECIMAL, c.GetValidationType());
Assert.AreEqual(OperatorType.BETWEEN, c.Operator);
Assert.AreEqual("A2", c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(200, c.Value2);
}
/**
* Constructs a DV record and Sets its fields appropriately.
*
* @param in the RecordInputstream to Read the record from
*/
public DVRecord(RecordInputStream in1)
{
_option_flags = in1.ReadInt();
_promptTitle = ReadUnicodeString(in1);
_errorTitle = ReadUnicodeString(in1);
_promptText = ReadUnicodeString(in1);
_errorText = ReadUnicodeString(in1);
int field_size_first_formula = in1.ReadUShort();
_not_used_1 = in1.ReadShort();
//read first formula data condition
_formula1 = NPOI.SS.Formula.Formula.Read(field_size_first_formula, in1);
int field_size_sec_formula = in1.ReadUShort();
_not_used_2 = in1.ReadShort();
//read sec formula data condition
_formula2 = NPOI.SS.Formula.Formula.Read(field_size_sec_formula, in1);
//read cell range address list with all affected ranges
_regions = new CellRangeAddressList(in1);
}
/**
* Constructor which initializes the cell range on which this object will be
* applied
* @param constraint
*/
public HSSFDataValidation(CellRangeAddressList regions, DVConstraint constraint)
{
_regions = regions;
_constraint = constraint;
}
public void TestDvProtectionOrder_bug47363b()
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
sheet.ProtectSheet("secret");
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint dvc = dataValidationHelper.CreateintConstraint(OperatorType.BETWEEN, "10", "100");
CellRangeAddressList numericCellAddressList = new CellRangeAddressList(0, 0, 1, 1);
IDataValidation dv = dataValidationHelper.CreateValidation(dvc, numericCellAddressList);
try
{
sheet.AddValidationData(dv);
}
catch (InvalidOperationException e)
{
String expMsg = "Unexpected (NPOI.HSSF.Record.PasswordRecord) while looking for DV Table insert pos";
if (expMsg.Equals(e.Message))
{
throw new AssertionException("Identified bug 47363b");
}
throw e;
}
TestCases.HSSF.UserModel.RecordInspector.RecordCollector rc;
rc = new RecordInspector.RecordCollector();
((HSSFSheet)sheet).Sheet.VisitContainedRecords(rc, 0);
int nRecsWithProtection = rc.Records.Length;
sheet.ProtectSheet(null);
rc = new RecordInspector.RecordCollector();
((HSSFSheet)sheet).Sheet.VisitContainedRecords(rc, 0);
int nRecsWithoutProtection = rc.Records.Length;
Assert.AreEqual(4, nRecsWithProtection - nRecsWithoutProtection);
}
private void CopyRowAreaApply(HSSFWorkbook workbook, ISheet sourceWorksheet, ISheet destinationWorksheet, int sourceRowNum, int destinationRowNum, int rowNum)
{
var getLastRowNum = destinationWorksheet.LastRowNum;
for (int i = 0; i < rowNum; i++)
{
CopyRow(workbook, sourceWorksheet, destinationWorksheet, sourceRowNum + i, destinationRowNum + i);
}
var markListConstraint = DVConstraint.CreateExplicitListConstraint(new string[] { "氣體", "液體", "固體" });
var markNumConstraint = DVConstraint.CreateNumericConstraint(0x02, 0x06, "0", null);
var markUnitLongConstraint = DVConstraint.CreateExplicitListConstraint(new string[] { "kcal/m³", "kcal/L", "kcal/kg" });
var markUnitShortConstraint = DVConstraint.CreateExplicitListConstraint(new string[] { "m³", "kL", "kg" });
var markColumn = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 3, 3);
var markdv = new HSSFDataValidation(markColumn, markListConstraint);
var markULColumn = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 6, 6);
var markULdv = new HSSFDataValidation(markULColumn, markUnitLongConstraint);
var markSLColumn = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 8, 8);
var markSLdv = new HSSFDataValidation(markSLColumn, markUnitShortConstraint);
var markNumColumn_5 = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 5, 5);
var markdvNum_5 = new HSSFDataValidation(markNumColumn_5, markNumConstraint);
var markNumColumn_7 = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 7, 7);
var markdvNum_7 = new HSSFDataValidation(markNumColumn_7, markNumConstraint);
var markNumColumn_9 = new CellRangeAddressList(getLastRowNum, getLastRowNum, 9, 9);
var markdvNum_9 = new HSSFDataValidation(markNumColumn_9, markNumConstraint);
var markNumColumn_10 = new CellRangeAddressList(getLastRowNum, getLastRowNum, 10, 10);
var markdvNum_10 = new HSSFDataValidation(markNumColumn_10, markNumConstraint);
destinationWorksheet.AddValidationData(markdv);
destinationWorksheet.AddValidationData(markULdv);
destinationWorksheet.AddValidationData(markSLdv);
destinationWorksheet.AddValidationData(markdvNum_5);
destinationWorksheet.AddValidationData(markdvNum_7);
destinationWorksheet.AddValidationData(markdvNum_9);
destinationWorksheet.AddValidationData(markdvNum_10);
}
//数据有效性以及下拉框的设置
private static HSSFDataValidation CreateDataValidation(int index, Tk5FieldInfoEx fieldInfo)
{
CellRangeAddressList region = new CellRangeAddressList(1, 65535, index, index);
DVConstraint constraint = null;
HSSFDataValidation dataValidate = null;
if (fieldInfo.Decoder != null && fieldInfo.Decoder.Type == DecoderType.CodeTable)
{
IEnumerable<IDecoderItem> data = GetDecoderItem(fieldInfo);
if (data != null)
{
List<string> optionList = new List<string>();
foreach (IDecoderItem item in data)
{
if (item != null)
{
TkDebug.AssertArgumentNullOrEmpty(item.Name, "item.Name", null);
optionList.Add(item.Name);
}
}
constraint = DVConstraint.CreateExplicitListConstraint(optionList.ToArray());
}
}
else
{
if (fieldInfo.InternalControl != null && fieldInfo.InternalControl.SrcControl == ControlType.CheckBox)
{
constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "√", "" });
}
else
{
switch (fieldInfo.DataType)
{
case TkDataType.DateTime:
case TkDataType.Date:
constraint = DVConstraint.CreateDateConstraint(6, "1900-01-01", null, "yyyy-MM-dd");
break;
case TkDataType.Double:
case TkDataType.Decimal:
case TkDataType.Money:
constraint = DVConstraint.CreateNumericConstraint(2, 1, "1", "0");
break;
case TkDataType.Long:
case TkDataType.Int:
case TkDataType.Short:
case TkDataType.Byte:
case TkDataType.Bit:
constraint = DVConstraint.CreateNumericConstraint(1, 1, "1", "0");
break;
default:
break;
}
}
}
if (constraint != null)
{
dataValidate = new HSSFDataValidation(region, constraint);
dataValidate.CreateErrorBox("error", "You must input a valid value!");
}
return dataValidate;
}
/** Creates new CFHeaderRecord */
public CFHeaderRecord()
{
field_4_cell_ranges = new CellRangeAddressList();
}
public ActionResult Template(int? tagId)
{
var basicInfoSheetName = ProUploadService.BASIC_SHEET;
var moreInfoSheetName = ProUploadService.MORE_SHEET;
var supportSheetName = "不要修改";
var headerLabels = new Dictionary<string, dynamic>() {
{"商品代码",new {dataformat=0,width=10}},
{"商品名称",new {dataformat=0,width=20}},
{"描述",new {dataformat=0,width=50}},
{"吊牌价",new {dataformat=2,width=8}},
{"现价",new {dataformat=2,width=8}},
{"品牌名",new {dataformat=0,width=20}},
{ "分类名",new {dataformat=0,width=20}},
{"门店名",new {dataformat=0,width=20}},
{ "促销活动编码",new {dataformat=0,width=20}},
{ "专题编码（多个以,分割)",new {dataformat=0,width=20}},
{"可销售",new {dataformat=2,width=5}},
{"商品货号",new {dataformat=0,width=10}}
};
var workbook = new HSSFWorkbook();
var headerLabelCellStyle = workbook.CreateCellStyle();
headerLabelCellStyle.BorderBottom = BorderStyle.THIN;
headerLabelCellStyle.BorderLeft = BorderStyle.THIN;
headerLabelCellStyle.BorderRight = BorderStyle.THIN;
headerLabelCellStyle.BorderTop = BorderStyle.THIN;
headerLabelCellStyle.WrapText = true;
var headerLabelFont = workbook.CreateFont();
headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;
headerLabelCellStyle.SetFont(headerLabelFont);
//set support sheet
var supportSheet = workbook.CreateSheet(supportSheetName);
workbook.SetSheetHidden(workbook.GetSheetIndex(supportSheet), true);
Func<int, dynamic, int> supportFill = (rowIndex, data) =>
{
var brandRow = supportSheet.CreateRow(rowIndex++);
var brandCodeCol = brandRow.CreateCell(1);
brandCodeCol.SetCellType(CellType.STRING);
brandCodeCol.SetCellValue(data.Id);
var brandNameCol = brandRow.CreateCell(2);
brandNameCol.SetCellType(CellType.STRING);
brandCodeCol.SetCellValue(data.Name);
return rowIndex;
};
int brandRowIndex = 0;
foreach (var brand in _brandRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name }))
{
brandRowIndex = supportFill(brandRowIndex, brand);
}
int tagRowIndex = brandRowIndex;
var tagLinq = _tagRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name });
//if (tagId.HasValue)
// tagLinq = tagLinq.Where(t => t.Id == tagId.Value);
foreach (var tag in tagLinq.OrderBy(t => t.Name))
{
tagRowIndex = supportFill(tagRowIndex, tag);
}
int storeRowIndex = tagRowIndex;
foreach (var store in _storeRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name }))
{
storeRowIndex = supportFill(storeRowIndex, store);
}
//set basic sheet
var sheet1 = workbook.CreateSheet(basicInfoSheetName);
//workbook.SetSheetOrder(basicInfoSheetName, 0);
var rowFirst = sheet1.CreateRow(0);
Action<int, string, dynamic> cellSetting = (cellindex, desc, option) =>
{
var cell = rowFirst.CreateCell(cellindex);
cell.SetCellType(CellType.STRING);
cell.SetCellValue(desc);
cell.CellStyle = headerLabelCellStyle;
sheet1.SetColumnWidth(cellindex, option.width * 255);
var currentCellStyle = workbook.CreateCellStyle();
currentCellStyle.DataFormat = (short)option.dataformat;
sheet1.SetDefaultColumnStyle(cellindex, currentCellStyle);
};
int index = 0;
foreach (var key in headerLabels.Keys)
{
cellSetting(index++, key, headerLabels[key]);
}
//set constraint
DVConstraint brandConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B$1:$B${1}", supportSheetName, brandRowIndex));
CellRangeAddressList brandaddressList = new CellRangeAddressList(1, 1000, 5, 5);
HSSFDataValidation branddataValidation = new HSSFDataValidation(brandaddressList, brandConstaint);
branddataValidation.SuppressDropDownArrow = false;
sheet1.AddValidationData(branddataValidation);
DVConstraint tagConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, brandRowIndex + 1, tagRowIndex));
CellRangeAddressList tagaddressList = new CellRangeAddressList(1, 1000, 6, 6);
HSSFDataValidation tagdataValidation = new HSSFDataValidation(tagaddressList, tagConstaint);
tagdataValidation.SuppressDropDownArrow = false;
sheet1.AddValidationData(tagdataValidation);
DVConstraint storeConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, tagRowIndex + 1, storeRowIndex));
CellRangeAddressList storeaddressList = new CellRangeAddressList(1, 1000, 7, 7);
HSSFDataValidation storedataValidation = new HSSFDataValidation(storeaddressList, storeConstaint);
storedataValidation.SuppressDropDownArrow = false;
sheet1.AddValidationData(storedataValidation);
DVConstraint is4saleConstaint = DVConstraint.CreateExplicitListConstraint(new string[] { ProUploadService.IS_4SALE_YES, ProUploadService.IS_4SALE_NO });
CellRangeAddressList is4saleaddressList = new CellRangeAddressList(1, 1000, 10, 10);
HSSFDataValidation is4saledataValidation = new HSSFDataValidation(is4saleaddressList, is4saleConstaint);
is4saledataValidation.SuppressDropDownArrow = false;
sheet1.AddValidationData(is4saledataValidation);
//set sheet2
//create property value sheet
var propertyLinq = _propertyRepo.Get(b => b.Status != (int)DataStatus.Deleted)
.Join(Context.Set<TagEntity>(), o => o.CategoryId, i => i.Id, (o, i) => new { P = o, C = i })
.OrderBy(b => b.P.CategoryId)
.ThenBy(b => b.P.PropertyDesc);
int propertyRowIndex = storeRowIndex;
foreach (var tag in tagLinq)
{
var propertyFromIndex = propertyRowIndex;
foreach (var property in propertyLinq.Where(p => p.C.Id == tag.Id).Select(p=>new {Id = p.P.Id,Name=p.P.PropertyDesc}))
{
propertyRowIndex = supportFill(propertyRowIndex, property);
}
if (propertyRowIndex > propertyFromIndex)
{
var rName = workbook.CreateName();
rName.RefersToFormula = string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, propertyFromIndex + 1, propertyRowIndex);
rName.NameName = tag.Name;
}
foreach (var property in propertyLinq.Where(p => p.C.Id == tag.Id))
{
int valueFromIndex = propertyRowIndex;
foreach (var pvalue in _valueRepo.Get(b => b.Status != (int)DataStatus.Deleted && b.PropertyId == property.P.Id).Select(p => new { Id = p.Id, Name = p.ValueDesc }))
{
propertyRowIndex = supportFill(propertyRowIndex, pvalue);
}
if (propertyRowIndex > valueFromIndex)
{
var rName = workbook.CreateName();
rName.RefersToFormula = string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, valueFromIndex + 1, propertyRowIndex);
rName.NameName = string.Format("{0}_{1}", property.C.Name, property.P.PropertyDesc);
}
}
}
var moreheaderLabels = new Dictionary<string, dynamic>() {
{"商品代码",new {dataformat=0,width=10}},
{"属性名",new {dataformat=0,width=20}},
{"属性值",new {dataformat=0,width=10}}
};
var sheet2 = workbook.CreateSheet(moreInfoSheetName);
var morerowFirst = sheet2.CreateRow(0);
int moreCellIndex = 0;
foreach (var key in moreheaderLabels.Keys)
{
var cell = morerowFirst.CreateCell(moreCellIndex);
cell.SetCellType(CellType.STRING);
cell.SetCellValue(key);
cell.CellStyle = headerLabelCellStyle;
sheet2.SetColumnWidth(moreCellIndex, moreheaderLabels[key].width * 255);
var currentCellStyle = workbook.CreateCellStyle();
currentCellStyle.DataFormat = (short)moreheaderLabels[key].dataformat;
sheet2.SetDefaultColumnStyle(moreCellIndex, currentCellStyle);
moreCellIndex++;
}
for (int i = 3; i <= 11; i++)
{
var cell = morerowFirst.CreateCell(i);
cell.SetCellType(CellType.STRING);
cell.SetCellValue(string.Empty);
cell.CellStyle = headerLabelCellStyle;
sheet2.SetColumnWidth(i, 10 * 255);
}
//set merge cell for last cell
sheet2.AddMergedRegion(new CellRangeAddress(0, 0, 2, 11));
//set constraint
DVConstraint codeConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$A${1}:$A${2}", basicInfoSheetName, 2, 1000));
CellRangeAddressList codeaddressList = new CellRangeAddressList(1, 1000, 0, 0);
HSSFDataValidation codedataValidation = new HSSFDataValidation(codeaddressList, codeConstaint);
codedataValidation.SuppressDropDownArrow = false;
sheet2.AddValidationData(codedataValidation);
for (int i = 1; i < 1000; i++)
{
//create hide helper cell
sheet2.SetArrayFormula(string.Format("VLOOKUP($A${0},'{1}'!$A$1:$L$1000,7,FALSE)&\"_\"&$B${0}",i+1,basicInfoSheetName),
new CellRangeAddress(i,i,12,12));
//create hide helper cell
sheet2.SetArrayFormula(string.Format("VLOOKUP($A${0},'{1}'!$A$1:$L$1000,7,FALSE)", i + 1, basicInfoSheetName),
new CellRangeAddress(i, i, 13, 13));
sheet2.SetColumnHidden(12, true);
sheet2.SetColumnHidden(13, true);
DVConstraint pConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("INDIRECT($N${0})", i + 1)); // DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, storeRowIndex + 1, propertyRowIndex));
CellRangeAddressList paddressList = new CellRangeAddressList(i,i, 1, 1);
HSSFDataValidation pdataValidation = new HSSFDataValidation(paddressList, pConstaint);
pdataValidation.SuppressDropDownArrow = false;
sheet2.AddValidationData(pdataValidation);
//set constraint
DVConstraint pvConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("INDIRECT($M${0})", i + 1));
CellRangeAddressList pvaddressList = new CellRangeAddressList(i, i, 2, 11);
HSSFDataValidation pvdataValidation = new HSSFDataValidation(pvaddressList, pvConstaint);
pvdataValidation.SuppressDropDownArrow = false;
sheet2.AddValidationData(pvdataValidation);
}
workbook.SetActiveSheet(workbook.GetSheetIndex(sheet1));
var ms = new MemoryStream();
workbook.Write(ms);
ms.Position = 0;
var downloadName = tagId.HasValue ? string.Format("商品上传模版-{0}.xls", tagId.Value) : "商品上传模块.xls";
return File(ms, "application/vnd.ms-excel", downloadName);
}
public void TestGetDataValidationsDate()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateDateConstraint(OperatorType.EQUAL,
"2014/10/25", null, null);
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.DATE, c.GetValidationType());
Assert.AreEqual(OperatorType.EQUAL, c.Operator);
Assert.AreEqual(null, c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(DateUtil.GetExcelDate(DateUtil.ParseYYYYMMDDDate("2014/10/25")), c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
}
public CellRangeAddressList Copy()
{
CellRangeAddressList result = new CellRangeAddressList();
int nItems = _list.Count;
for (int k = 0; k < nItems; k++)
{
CellRangeAddress region = (CellRangeAddress)_list[k];
result.AddCellRangeAddress(region.Copy());
}
return result;
}
public DVRecord(int validationType, int operator1, int errorStyle, bool emptyCellAllowed,
bool suppressDropDownArrow, bool isExplicitList,
bool showPromptBox, String promptTitle, String promptText,
bool showErrorBox, String errorTitle, String errorText,
Ptg[] formula1, Ptg[] formula2,
CellRangeAddressList regions)
{
int flags = 0;
flags = opt_data_type.SetValue(flags, validationType);
flags = opt_condition_operator.SetValue(flags, operator1);
flags = opt_error_style.SetValue(flags, errorStyle);
flags = opt_empty_cell_allowed.SetBoolean(flags, emptyCellAllowed);
flags = opt_suppress_dropdown_arrow.SetBoolean(flags, suppressDropDownArrow);
flags = opt_string_list_formula.SetBoolean(flags, isExplicitList);
flags = opt_show_prompt_on_cell_selected.SetBoolean(flags, showPromptBox);
flags = opt_show_error_on_invalid_value.SetBoolean(flags, showErrorBox);
_option_flags = flags;
_promptTitle = ResolveTitleText(promptTitle);
_promptText = ResolveTitleText(promptText);
_errorTitle = ResolveTitleText(errorTitle);
_errorText = ResolveTitleText(errorText);
_formula1 = NPOI.SS.Formula.Formula.Create(formula1);
_formula2 = NPOI.SS.Formula.Formula.Create(formula2);
_regions = regions;
}
/// <summary>
/// 生成下拉列表
/// </summary>
/// <param name="sheet"></param>
/// <param name="firstRow"></param>
/// <param name="lastRow"></param>
/// <param name="FirstCol"></param>
/// <param name="LastRow"></param>
/// <param name="arr"></param>
public void WriteDropDownList(Sheet sheet, int firstRow, int lastRow, int FirstCol, int LastCol, string[] arr)
{
NPOI.SS.Util.CellRangeAddressList ranglist = new NPOI.SS.Util.CellRangeAddressList();
ranglist.AddCellRangeAddress(new CellRangeAddress(firstRow, lastRow, FirstCol, LastCol));
DVConstraint dvconstraint = DVConstraint.CreateExplicitListConstraint(arr);
HSSFDataValidation dataValidation = new HSSFDataValidation(ranglist, dvconstraint);
((HSSFSheet)sheet).AddValidationData(dataValidation);
}
public void TestGetDataValidationsAny()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(ValidationType.ANY,
OperatorType.IGNORED, null, null);
CellRangeAddressList AddressList = new CellRangeAddressList(1, 2, 3, 4);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
validation.EmptyCellAllowed = (/*setter*/true);
validation.CreateErrorBox("error-title", "error-text");
validation.CreatePromptBox("prompt-title", "prompt-text");
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
{
CellRangeAddressList regions = dv.Regions;
Assert.AreEqual(1, regions.CountRanges());
CellRangeAddress Address = regions.GetCellRangeAddress(0);
Assert.AreEqual(1, Address.FirstRow);
Assert.AreEqual(2, Address.LastRow);
Assert.AreEqual(3, Address.FirstColumn);
Assert.AreEqual(4, Address.LastColumn);
}
Assert.AreEqual(true, dv.EmptyCellAllowed);
Assert.AreEqual(false, dv.SuppressDropDownArrow);
Assert.AreEqual(true, dv.ShowErrorBox);
Assert.AreEqual("error-title", dv.ErrorBoxTitle);
Assert.AreEqual("error-text", dv.ErrorBoxText);
Assert.AreEqual(true, dv.ShowPromptBox);
Assert.AreEqual("prompt-title", dv.PromptBoxTitle);
Assert.AreEqual("prompt-text", dv.PromptBoxText);
IDataValidationConstraint c = dv.ValidationConstraint;
Assert.AreEqual(ValidationType.ANY, c.GetValidationType());
Assert.AreEqual(OperatorType.IGNORED, c.Operator);
}
private void CopyRowAreaApplyDetail(HSSFWorkbook workbook, ISheet sourceWorksheet, ISheet destinationWorksheet, int sourceRowNum, int destinationRowNum, int rowNum)
{
var getLastRowNum = destinationWorksheet.LastRowNum;
for (int i = 0; i < rowNum; i++)
{
CopyRow(workbook, sourceWorksheet, destinationWorksheet, sourceRowNum + i, destinationRowNum + i);
}
var markNumConstraint = DVConstraint.CreateNumericConstraint(0x02, 0x06, "0", null);
var markNumColumn_4 = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 4, 4);
var markdvNum_4 = new HSSFDataValidation(markNumColumn_4, markNumConstraint);
destinationWorksheet.AddValidationData(markdvNum_4);
}
/// <summary>
/// 生成下拉列表
/// 此方法暂时不能使用
/// </summary>
/// <param name="sheet"></param>
/// <param name="firstRow"></param>
/// <param name="lastRow"></param>
/// <param name="FirstCol"></param>
/// <param name="LastCol"></param>
/// <param name="arr"></param>
public void WriteDropDownList2(Sheet sheet, int firstRow, int lastRow, int FirstCol, int LastCol, string[] data)
{
string tmpShtDictionary = "tmpShtDictionary";
Sheet tmpSheet = _workbook.CreateSheet(tmpShtDictionary);
for (int i = 0; i < data.Length; i++)
{
tmpSheet.CreateRow(i).CreateCell(0).SetCellValue(data[i]);
}
Name range = _workbook.CreateName();
range.RefersToFormula = tmpShtDictionary + "!$A1:$A" + data.Length.ToString();
range.NameName = "dicRange";
NPOI.SS.Util.CellRangeAddressList ranglist = new NPOI.SS.Util.CellRangeAddressList();
ranglist.AddCellRangeAddress(new CellRangeAddress(firstRow, lastRow, FirstCol, LastCol));
DVConstraint dvconstraint = DVConstraint.CreateFormulaListConstraint(range.NameName);
HSSFDataValidation dataValidation = new HSSFDataValidation(ranglist, dvconstraint);
((HSSFSheet)sheet).AddValidationData(dataValidation);
_workbook.RemoveSheetAt(_workbook.GetSheetIndex(tmpSheet));
_workbook.RemoveName(range.NameName);
}
