private static HSSFWorkbook CreateWBA()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet1 = wb.CreateSheet("Sheet1");
ISheet sheet2 = wb.CreateSheet("Sheet2");
ISheet sheet3 = wb.CreateSheet("John's sales");
CreateDataRow(sheet1, 0, 11, 12, 13, 14);
CreateDataRow(sheet1, 1, 21, 22, 23, 24);
CreateDataRow(sheet1, 2, 31, 32, 33, 34);
CreateDataRow(sheet2, 0, 50, 55, 60, 65);
CreateDataRow(sheet2, 1, 51, 56, 61, 66);
CreateDataRow(sheet2, 2, 52, 57, 62, 67);
CreateDataRow(sheet3, 0, 30, 31, 32);
CreateDataRow(sheet3, 1, 33, 34, 35);
IName name1 = wb.CreateName();
name1.NameName = ("sales1");
name1.RefersToFormula = ("Sheet1!A1:D1");
IName name2 = wb.CreateName();
name2.NameName = ("sales2");
name2.RefersToFormula = ("Sheet2!B1:C3");
IRow row = sheet1.CreateRow(3);
row.CreateCell(0).SetCellValue("sales1"); //A4
row.CreateCell(1).SetCellValue("sales2"); //B4
return(wb);
}
public void TestMultipleNamedWrite()
{
HSSFWorkbook wb = new HSSFWorkbook();
wb.CreateSheet("TestSheet1");
String sheetName = wb.GetSheetName(0);
Assert.AreEqual("TestSheet1", sheetName);
//Creating new Named Range
NPOI.SS.UserModel.Name newNamedRange = wb.CreateName();
newNamedRange.NameName = ("RangeTest");
newNamedRange.RefersToFormula = (sheetName + "!$D$4:$E$8");
//Creating another new Named Range
NPOI.SS.UserModel.Name newNamedRange2 = wb.CreateName();
newNamedRange2.NameName = ("AnotherTest");
newNamedRange2.RefersToFormula = (sheetName + "!$F$1:$G$6");
NPOI.SS.UserModel.Name namedRange1 = wb.GetNameAt(0);
String referece = namedRange1.RefersToFormula;
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
NPOI.SS.UserModel.Name nm = wb.GetNameAt(wb.GetNameIndex("RangeTest"));
Assert.IsTrue("RangeTest".Equals(nm.NameName), "Name is " + nm.NameName);
Assert.IsTrue((wb.GetSheetName(0) + "!$D$4:$E$8").Equals(nm.RefersToFormula), "Reference is " + nm.RefersToFormula);
nm = wb.GetNameAt(wb.GetNameIndex("AnotherTest"));
Assert.IsTrue("AnotherTest".Equals(nm.NameName), "Name is " + nm.NameName);
Assert.IsTrue(newNamedRange2.RefersToFormula.Equals(nm.RefersToFormula), "Reference is " + nm.RefersToFormula);
}
public void TestBug58085RemoveSheetWithNames()
{
HSSFWorkbook wb1 = new HSSFWorkbook();
ISheet sheet1 = wb1.CreateSheet("sheet1");
ISheet sheet2 = wb1.CreateSheet("sheet2");
ISheet sheet3 = wb1.CreateSheet("sheet3");
sheet1.CreateRow(0).CreateCell((short)0).SetCellValue("val1");
sheet2.CreateRow(0).CreateCell((short)0).SetCellValue("val2");
sheet3.CreateRow(0).CreateCell((short)0).SetCellValue("val3");
IName namedCell1 = wb1.CreateName();
namedCell1.NameName = (/*setter*/ "name1");
String reference1 = "sheet1!$A$1";
namedCell1.RefersToFormula = (/*setter*/ reference1);
IName namedCell2 = wb1.CreateName();
namedCell2.NameName = (/*setter*/ "name2");
String reference2 = "sheet2!$A$1";
namedCell2.RefersToFormula = (/*setter*/ reference2);
IName namedCell3 = wb1.CreateName();
namedCell3.NameName = (/*setter*/ "name3");
String reference3 = "sheet3!$A$1";
namedCell3.RefersToFormula = (/*setter*/ reference3);
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb1);
wb1.Close();
IName nameCell = wb2.GetName("name1");
Assert.AreEqual("sheet1!$A$1", nameCell.RefersToFormula);
nameCell = wb2.GetName("name2");
Assert.AreEqual("sheet2!$A$1", nameCell.RefersToFormula);
nameCell = wb2.GetName("name3");
Assert.AreEqual("sheet3!$A$1", nameCell.RefersToFormula);
wb2.RemoveSheetAt(wb2.GetSheetIndex("sheet1"));
nameCell = wb2.GetName("name1");
Assert.AreEqual("#REF!$A$1", nameCell.RefersToFormula);
nameCell = wb2.GetName("name2");
Assert.AreEqual("sheet2!$A$1", nameCell.RefersToFormula);
nameCell = wb2.GetName("name3");
Assert.AreEqual("sheet3!$A$1", nameCell.RefersToFormula);
wb2.Close();
}
public void TestSheetLevelFormulas()
{
HSSFWorkbook wb = new HSSFWorkbook();
IRow row;
ISheet sh1 = wb.CreateSheet("Sheet1");
IName nm1 = wb.CreateName();
nm1.NameName = ("sales_1");
nm1.SheetIndex = (0);
nm1.RefersToFormula = ("Sheet1!$A$1");
row = sh1.CreateRow(0);
row.CreateCell(0).SetCellValue(3);
row.CreateCell(1).SetCellFormula("sales_1");
row.CreateCell(2).SetCellFormula("sales_1*2");
ISheet sh2 = wb.CreateSheet("Sheet2");
IName nm2 = wb.CreateName();
nm2.NameName = ("sales_1");
nm2.SheetIndex = (1);
nm2.RefersToFormula = ("Sheet2!$A$1");
row = sh2.CreateRow(0);
row.CreateCell(0).SetCellValue(5);
row.CreateCell(1).SetCellFormula("sales_1");
row.CreateCell(2).SetCellFormula("sales_1*3");
//check that NamePtg refers to the correct NameRecord
Ptg[] ptgs1 = HSSFFormulaParser.Parse("sales_1", wb, FormulaType.Cell, 0);
NamePtg nPtg1 = (NamePtg)ptgs1[0];
Assert.AreSame(nm1, wb.GetNameAt(nPtg1.Index));
Ptg[] ptgs2 = HSSFFormulaParser.Parse("sales_1", wb, FormulaType.Cell, 1);
NamePtg nPtg2 = (NamePtg)ptgs2[0];
Assert.AreSame(nm2, wb.GetNameAt(nPtg2.Index));
//check that the formula evaluator returns the correct result
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
Assert.AreEqual(3.0, evaluator.Evaluate(sh1.GetRow(0).GetCell(1)).NumberValue, 0.0);
Assert.AreEqual(6.0, evaluator.Evaluate(sh1.GetRow(0).GetCell(2)).NumberValue, 0.0);
Assert.AreEqual(5.0, evaluator.Evaluate(sh2.GetRow(0).GetCell(1)).NumberValue, 0.0);
Assert.AreEqual(15.0, evaluator.Evaluate(sh2.GetRow(0).GetCell(2)).NumberValue, 0.0);
}
public void TestWithNamedRange()
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet s = workbook.CreateSheet("Foo");
s.CreateRow(0).CreateCell((short)0).SetCellValue(1.1);
s.CreateRow(1).CreateCell((short)0).SetCellValue(2.3);
s.CreateRow(2).CreateCell((short)2).SetCellValue(3.1);
IName name = workbook.CreateName();
name.NameName = ("testName");
name.RefersToFormula = ("A1:A2");
ConfirmParseFormula(workbook);
// Now make it a single cell
name.RefersToFormula = ("C3");
ConfirmParseFormula(workbook);
// And make it non-contiguous
name.RefersToFormula = ("A1:A2,C3");
ConfirmParseFormula(workbook);
}
public void TestNamedRange()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("Simple.xls");
//Creating new Named Range
IName newNamedRange = wb.CreateName();
//Getting Sheet Name for the reference
String sheetName = wb.GetSheetName(0);
//Setting its name
newNamedRange.NameName = "RangeTest";
//Setting its reference
newNamedRange.RefersToFormula = sheetName + "!$D$4:$E$8";
//Getting NAmed Range
IName namedRange1 = wb.GetNameAt(0);
//Getting it sheet name
sheetName = namedRange1.SheetName;
// sanity check
SanityChecker c = new SanityChecker();
c.CheckHSSFWorkbook(wb);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
IName nm = wb.GetNameAt(wb.GetNameIndex("RangeTest"));
Assert.IsTrue("RangeTest".Equals(nm.NameName), "Name is " + nm.NameName);
Assert.AreEqual(wb.GetSheetName(0) + "!$D$4:$E$8", nm.RefersToFormula);
}
public static void SetDropDownList(this HSSFSheet sheet, string[] datas, HSSFWorkbook workbook,
CellRangeAddressList addressList, string formulaName)
{
var hiddenSheetName = "HiddenDataSource" + DateTime.Now.ToString("yyyyMMddHHmmss");
ISheet CourseSheet = workbook.CreateSheet(hiddenSheetName);
workbook.SetSheetHidden(workbook.GetSheetIndex(hiddenSheetName), true);
//CourseSheet.CreateRow(0).CreateCell(0).SetCellValue("");
IRow row = null;
ICell cell = null;
for (int i = 0; i < datas.Length; i++)
{
row = CourseSheet.CreateRow(i);
cell = row.CreateCell(0);
cell.SetCellValue(datas[i]);
}
IName range = workbook.CreateName();
range.RefersToFormula = string.Format("{0}!$A$1:$A${1}", hiddenSheetName, datas.Length);
range.NameName = formulaName;
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(formulaName);
HSSFDataValidation dataValidate = new HSSFDataValidation(addressList, constraint);
sheet.AddValidationData(dataValidate);
}
private void setSheet2(HSSFWorkbook workBook, ISheet sheet)
{
//创建表
ISheet sheet2 = workBook.CreateSheet("岗位数据");
//隐藏
workBook.SetSheetHidden(1, true);
//取数据
using (var db = DbFactory.Open())
{
var builder = db.From <Model.Post.Post>().Where(w => w.PostType == ZZTXEnums.行政村防汛防台工作组.ToString());
var rlist = db.Select <PostViewModel>(builder);
for (int iRowIndex = 0; iRowIndex < rlist.Count; iRowIndex++)
{
sheet2.CreateRow(iRowIndex).CreateCell(0).SetCellValue(rlist[iRowIndex].PostName);
}
}
//设计表名称
IName range = workBook.CreateName();
range.RefersToFormula = "岗位数据!$A:$A";
range.NameName = "PostDataName";
//定义下拉框范围
CellRangeAddressList regions = new CellRangeAddressList(3, 65535, 1, 1);
//设置数据引用
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("PostDataName");
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidate);
}
/// <summary>
/// 设置下拉选项
/// </summary>
/// <param name="workBook"></param>
/// <param name="sheet"></param>
/// <param name="cellName"></param>
/// <param name="cellNo"></param>
/// <param name="list"></param>
public void setSheet2(HSSFWorkbook workBook, ISheet sheet, string cellName, int cellNo, List <string> list)
{
//创建表
ISheet sheet2 = workBook.CreateSheet(cellName);
//隐藏
workBook.SetSheetHidden(1, true);
//取数据
for (int i = 0; i < list.Count; i++)
{
sheet2.CreateRow(i).CreateCell(0).SetCellValue(list[i]);
}
//设计表名称
IName range = workBook.CreateName();
range.RefersToFormula = cellName + "!$A:$A";
range.NameName = cellName;
//定义下拉框范围
CellRangeAddressList regions = new CellRangeAddressList(2, 65535, cellNo, cellNo);
//设置数据引用
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(cellName);
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidate);
}
public void TestNamedCell_2()
{
// setup for this Testcase
String sname = "TestSheet", cname = "TestName", cvalue = "TestVal";
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet(sname);
sheet.CreateRow(0).CreateCell(0).SetCellValue(new HSSFRichTextString(cvalue));
// Create named range for a single cell using cellreference
NPOI.SS.UserModel.Name namedCell = wb.CreateName();
namedCell.NameName = (cname);
String reference = sname + "!A1";
namedCell.RefersToFormula = (reference);
// retrieve the newly Created named range
int namedCellIdx = wb.GetNameIndex(cname);
NPOI.SS.UserModel.Name aNamedCell = wb.GetNameAt(namedCellIdx);
Assert.IsNotNull(aNamedCell);
// retrieve the cell at the named range and Test its contents
CellReference cref = new CellReference(aNamedCell.RefersToFormula);
Assert.IsNotNull(cref);
NPOI.SS.UserModel.Sheet s = wb.GetSheet(cref.SheetName);
Row r = sheet.GetRow(cref.Row);
Cell c = r.GetCell(cref.Col);
String contents = c.RichStringCellValue.String;
Assert.AreEqual(contents, cvalue, "Contents of cell retrieved by its named reference");
}
//public static void test1()
//{
// HSSFWorkbook hssfworkbook = new HSSFWorkbook();
// HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1") as HSSFSheet;
// CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0);
// DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "itemA", "itemB", "itemC" });
// HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
// sheet1.AddValidationData(dataValidate);
// MemoryStream ms = new MemoryStream();
// hssfworkbook.Write(ms);
// string workbookFile = @"D:\\wulei22.xls";
// hssfworkbook = null;
// FileStream fs = new FileStream(workbookFile, FileMode.Create, FileAccess.Write);
// byte[] data = ms.ToArray();
// fs.Write(data, 0, data.Length);
// fs.Flush();
// fs.Close();
//}
public static void setdownlist()
{
//创建工作簿
HSSFWorkbook ssfworkbook = new HSSFWorkbook();
//创建工作表(页)
HSSFSheet sheet1 = ssfworkbook.CreateSheet("Sheet1") as HSSFSheet;
//创建一行
HSSFRow headerRow = (HSSFRow)sheet1.CreateRow(0);
//设置表头
headerRow.CreateCell(0).SetCellValue("ID");
//设置表头的宽度
sheet1.SetColumnWidth(0, 15 * 256);
#region 添加显示下拉列表
HSSFSheet sheet2 = ssfworkbook.CreateSheet("ShtDictionary") as HSSFSheet;
ssfworkbook.SetSheetHidden(1, true); //隐藏
sheet2.CreateRow(0).CreateCell(0).SetCellValue("itemA"); //列数据
sheet2.CreateRow(1).CreateCell(0).SetCellValue("itemB");
sheet2.CreateRow(2).CreateCell(0).SetCellValue("itemC");
HSSFName range = ssfworkbook.CreateName() as HSSFName;//创建名称
// range.Reference = "ShtDictionary!$A$1:$A$3";//格式
range.NameName = "dicRange";
#endregion
headerRow.CreateCell(1).SetCellValue("Selected");
sheet1.SetColumnWidth(1, 15 * 256);
//将下拉列表添加
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 1, 1);
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("dicRange");
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet1.AddValidationData(dataValidate);
headerRow.CreateCell(2).SetCellValue("VALUE");
sheet1.SetColumnWidth(2, 15 * 256);
//写入数据
//创建数据行
HSSFRow dataRow = (HSSFRow)sheet1.CreateRow(1);
//填充数据
dataRow.CreateCell(0).SetCellValue("1"); //id
dataRow.CreateCell(1).SetCellValue(""); //选择框
dataRow.CreateCell(2).SetCellValue("值"); //选择框
System.IO.MemoryStream ms = new System.IO.MemoryStream();
ssfworkbook.Write(ms);
string filename = "Sheet1" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + ".xls";
object Response = null;
string workbookFile = @"D:\\wulei.xls";
FileStream fs = new FileStream(workbookFile, FileMode.Create, FileAccess.Write);
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
fs.Close();
//Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + filename + ""));
//Response.BinaryWrite(ms.ToArray());
ms.Close();
ms.Dispose();
}
//Crea una tabla a partir de los parametros indicados
public void CrearTabla(List <string> Columns, int Rows, int X, int Y, string headerText, string tableName, string hoja)
{
ISheet Sheet1 = hssfworkbook.GetSheet(hoja) != null?hssfworkbook.GetSheet(hoja) : hssfworkbook.CreateSheet(hoja);
ICell Title = Sheet1.GetRow(Y) != null ? (Sheet1.GetRow(Y).GetCell(X) != null ? Sheet1.GetRow(Y).GetCell(X) : Sheet1.GetRow(Y).CreateCell(X)) : Sheet1.CreateRow(Y).CreateCell(X);
Title.CellStyle = EstTitulos;
Title.SetCellValue(headerText);
Sheet1.AddMergedRegion(new CellRangeAddress(Y, Y + 1, X, X + Columns.Count - 1));
/* CAMPOS */
IRow RowColumns = Sheet1.GetRow(Y + 2) != null?Sheet1.GetRow(Y + 2) : Sheet1.CreateRow(Y + 2);
for (int c = 0; c < Columns.Count; c++)
{
ICell CellColumn = RowColumns.GetCell(X + c) != null?RowColumns.GetCell(X + c) : RowColumns.CreateCell(X + c);
CellColumn.CellStyle = EstSubtitulos;
CellColumn.SetCellValue(Columns[c]);
Sheet1.AutoSizeColumn(CellColumn.ColumnIndex);
}
for (int i = 0; i < Rows; i++)
{
IRow CurrentRow = Sheet1.GetRow(Y + i + 3) != null?Sheet1.GetRow(Y + i + 3) : Sheet1.CreateRow(Y + i + 3);
for (int j = 0; j < Columns.Count; j++)
{
ICell currentCell = CurrentRow.GetCell(X + j) != null?CurrentRow.GetCell(X + j) : CurrentRow.CreateCell(X + j);
currentCell.CellStyle = EstCeldas;
}
}
IName name = hssfworkbook.CreateName();
if (hssfworkbook.GetName(tableName) == null)
{
name.NameName = tableName;
}
else
{
throw new Exception("No se pueden crear dos espacios de nombre iguales.");
}
name.RefersToFormula = "'" + hoja + "'" + "!$" + getChar(X + 1) + "$" + (X + 3) + ":$" + getChar(Y + Columns.Count) + "$" + (X + 3 + Rows);
}
public void TestNamesInFormulas()
{
TestCases.CultureShim.SetCurrentCulture("en-US");
IWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
IName name1 = wb.CreateName();
name1.NameName = "aConstant";
name1.RefersToFormula = "3.14";
IName name2 = wb.CreateName();
name2.NameName = "aFormula";
name2.RefersToFormula = "SUM(Sheet1!$A$1:$A$3)";
IName name3 = wb.CreateName();
name3.NameName = "aSet";
name3.RefersToFormula = "Sheet1!$A$2:$A$4";
IRow row0 = sheet.CreateRow(0);
IRow row1 = sheet.CreateRow(1);
IRow row2 = sheet.CreateRow(2);
IRow row3 = sheet.CreateRow(3);
row0.CreateCell(0).SetCellValue(2);
row1.CreateCell(0).SetCellValue(5);
row2.CreateCell(0).SetCellValue(3);
row3.CreateCell(0).SetCellValue(7);
row0.CreateCell(2).SetCellFormula("aConstant");
row1.CreateCell(2).SetCellFormula("aFormula");
row2.CreateCell(2).SetCellFormula("SUM(aSet)");
row3.CreateCell(2).SetCellFormula("aConstant+aFormula+SUM(aSet)");
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
Assert.AreEqual(3.14, fe.Evaluate(row0.GetCell(2)).NumberValue);
Assert.AreEqual(10.0, fe.Evaluate(row1.GetCell(2)).NumberValue);
Assert.AreEqual(15.0, fe.Evaluate(row2.GetCell(2)).NumberValue);
Assert.AreEqual(28.14, fe.Evaluate(row3.GetCell(2)).NumberValue);
}
public void TestMultiNamedRange()
{
// Create a new workbook
HSSFWorkbook wb = new HSSFWorkbook();
// Create a worksheet 'sheet1' in the new workbook
wb.CreateSheet();
wb.SetSheetName(0, "sheet1");
// Create another worksheet 'sheet2' in the new workbook
wb.CreateSheet();
wb.SetSheetName(1, "sheet2");
// Create a new named range for worksheet 'sheet1'
NPOI.SS.UserModel.Name namedRange1 = wb.CreateName();
// Set the name for the named range for worksheet 'sheet1'
namedRange1.NameName = ("RangeTest1");
// Set the reference for the named range for worksheet 'sheet1'
namedRange1.RefersToFormula = ("sheet1" + "!$A$1:$L$41");
// Create a new named range for worksheet 'sheet2'
NPOI.SS.UserModel.Name namedRange2 = wb.CreateName();
// Set the name for the named range for worksheet 'sheet2'
namedRange2.NameName = ("RangeTest2");
// Set the reference for the named range for worksheet 'sheet2'
namedRange2.RefersToFormula = ("sheet2" + "!$A$1:$O$21");
// Write the workbook to a file
// Read the Excel file and verify its content
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
NPOI.SS.UserModel.Name nm1 = wb.GetNameAt(wb.GetNameIndex("RangeTest1"));
Assert.IsTrue("RangeTest1".Equals(nm1.NameName), "Name is " + nm1.NameName);
Assert.IsTrue((wb.GetSheetName(0) + "!$A$1:$L$41").Equals(nm1.RefersToFormula), "Reference is " + nm1.RefersToFormula);
NPOI.SS.UserModel.Name nm2 = wb.GetNameAt(wb.GetNameIndex("RangeTest2"));
Assert.IsTrue("RangeTest2".Equals(nm2.NameName), "Name is " + nm2.NameName);
Assert.IsTrue((wb.GetSheetName(1) + "!$A$1:$O$21").Equals(nm2.RefersToFormula), "Reference is " + nm2.RefersToFormula);
}
private void CreateEmptyExcelFile(string filepath)
{
HSSFWorkbook wkbook = new HSSFWorkbook();
wkbook.CreateName();
using (FileStream filesave = new FileStream(filepath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
wkbook.Write(filesave);
}
}
public void TestFormulasWithUnderscore()
{
HSSFWorkbook wb = new HSSFWorkbook();
IName nm1 = wb.CreateName();
nm1.NameName = ("_score1");
nm1.RefersToFormula = ("A1");
IName nm2 = wb.CreateName();
nm2.NameName = ("_score2");
nm2.RefersToFormula = ("A2");
ISheet sheet = wb.CreateSheet();
ICell cell = sheet.CreateRow(0).CreateCell(2);
cell.CellFormula = ("_score1*SUM(_score1+_score2)");
Assert.AreEqual("_score1*SUM(_score1+_score2)", cell.CellFormula);
}
/// <summary>
/// 引用另一个工作表的形式 得到下拉
/// </summary>
/// <param name="book"></param>
/// <param name="columnIndex"></param>
/// <param name="values"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public static void CreateListConstaint(this HSSFWorkbook book, Int32 columnIndex, IEnumerable <String> values, string sheetName = "")
{
if (values == null)
{
return;
}
if (string.IsNullOrEmpty(sheetName))
{
sheetName = "_constraintSheet_";
}
//创建下拉数据到新表中
ISheet sheet = book.GetSheet(sheetName) ?? book.CreateSheet(sheetName);;
var firstRow = sheet.GetRow(0);
var conColumnIndex = firstRow == null ? 0 : firstRow.PhysicalNumberOfCells;
var rowIndex = 0;
var lastValue = string.Empty;
foreach (var value in values)
{
var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
row.CreateCell(conColumnIndex).SetCellValue(value);
rowIndex++;
lastValue = value;
}
//如果无可选值的话，则增加一个空选项，防止用户填写内容
if (values.Count() == 0)
{
var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
row.CreateCell(conColumnIndex).SetCellValue(" ");
rowIndex++;
}
//给该列所有单元格加上下拉选择
IName range = book.CreateName();
range.RefersToFormula = String.Format("{2}!${0}$1:${0}${1}",
(Char)('A' + conColumnIndex),
rowIndex.ToString(), sheetName);
string rangeName = "dicRange" + columnIndex;
range.NameName = rangeName;
var cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex);
var constraint = DVConstraint.CreateFormulaListConstraint(rangeName);
book.SetSheetHidden(book.GetSheetIndex(sheet), SheetState.HIDDEN);
//创建验证
HSSFDataValidation valid = new HSSFDataValidation(cellRegions, constraint);
//关联验证
HSSFSheet v = book.GetSheetAt(0) as HSSFSheet;
v.AddValidationData(valid);
}
private void WriteReportWithData()
{
var reportdata = _data.ReportData.ToList().AsParallel();
InitializeWorkbook();
var sheet = _hssfworkbook.CreateSheet("request");
_hssfworkbook.CreateSheet("hidden");
var style = _hssfworkbook.CreateCellStyle();
style.FillForegroundColor = HSSFColor.DarkBlue.Index;
style.FillPattern = FillPattern.SolidForeground;
var font = _hssfworkbook.CreateFont();
font.Color = HSSFColor.White.Index;
style.SetFont(font);
var rows = reportdata.Count() + 1;
for (var j = 0; j < rows; j++)
{
var row = sheet.CreateRow(j);
for (var i = 0; i < _data.Columns.Count; i++)
{
var header = _data.Columns.ElementAt(i);
if (j == 0)
{
var cell = row.CreateCell(i);
cell.SetCellValue(header);
cell.CellStyle = style;
}
else
{
var dictionary = reportdata.ElementAt(j - 1);
row.CreateCell(i).SetCellValue(dictionary[header]);
}
}
sheet.AutoSizeColumn(j);
}
sheet.SetColumnHidden(0, true);
var namedcell = _hssfworkbook.CreateName();
namedcell.NameName = "hidden";
var constraint = DVConstraint.CreateFormulaListConstraint("hidden");
var addressList = new CellRangeAddressList(1, reportdata.Count(), _data.Columns.Count,
_data.Columns.Count);
var validation = new HSSFDataValidation(addressList, constraint);
_hssfworkbook.SetSheetHidden(1, true);
}
public void TestFormulaGeneral()
{
// perhaps this Testcase belongs on TestHSSFName
IWorkbook wb = new HSSFWorkbook();
IName name = wb.CreateName();
wb.CreateSheet("Sheet1");
name.NameName = (/*setter*/ "test");
name.RefersToFormula = (/*setter*/ "Sheet1!A1+Sheet1!A2");
Assert.AreEqual("Sheet1!A1+Sheet1!A2", name.RefersToFormula);
name.RefersToFormula = (/*setter*/ "5*6");
Assert.AreEqual("5*6", name.RefersToFormula);
}
public void TestDefinedNameWithComplexFlag_bug47048()
{
// Mock up a spreadsheet to match the critical details of the sample
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Input");
IName definedName = wb.CreateName();
definedName.NameName = ("Is_Multicar_Vehicle");
definedName.RefersToFormula = ("Input!$B$17:$G$17");
// Set up some data and the formula
IRow row17 = sheet.CreateRow(16);
row17.CreateCell(0).SetCellValue(25.0);
row17.CreateCell(1).SetCellValue(1.33);
row17.CreateCell(2).SetCellValue(4.0);
IRow row = sheet.CreateRow(0);
ICell cellA1 = row.CreateCell(0);
cellA1.CellFormula = ("SUM(Is_Multicar_Vehicle)");
// Set the complex flag - POI doesn't usually manipulate this flag
NameRecord nameRec = TestHSSFName.GetNameRecord(definedName);
nameRec.OptionFlag = (short)0x10; // 0x10 -> complex
HSSFFormulaEvaluator hsf = new HSSFFormulaEvaluator(wb);
CellValue value;
try
{
value = hsf.Evaluate(cellA1);
Assert.AreEqual(CellType.Numeric, value.CellType);
Assert.AreEqual(5.33, value.NumberValue, 0.0);
}
catch (Exception e)
{
if (e.Message.Equals("Don't now how to evalate name 'Is_Multicar_Vehicle'"))
{
Assert.Fail("Identified bug 47048a");
}
throw e;
}
finally
{
wb.Close();
}
}
public void TestFormulaRelAbs_bug46174()
{
// perhaps this Testcase belongs on TestHSSFName
IWorkbook wb = new HSSFWorkbook();
IName name = wb.CreateName();
wb.CreateSheet("Sheet1");
name.NameName = (/*setter*/ "test");
name.RefersToFormula = (/*setter*/ "Sheet1!$B$3");
if (name.RefersToFormula.Equals("Sheet1!B3"))
{
throw new AssertionException("Identified bug 46174");
}
Assert.AreEqual("Sheet1!$B$3", name.RefersToFormula);
}
public void TestNames()
{
HSSFWorkbook wb = new HSSFWorkbook();
try
{
wb.GetNameAt(0);
Assert.Fail("Fails without any defined names");
}
catch (ArgumentException e)
{
//Assert.IsTrue(e.Message.Contains("no defined names"), e.Message);
}
HSSFName name = (HSSFName)wb.CreateName();
Assert.IsNotNull(name);
Assert.IsNull(wb.GetName("somename"));
name.NameName = ("myname");
Assert.IsNotNull(wb.GetName("myname"));
Assert.AreEqual(0, wb.GetNameIndex(name));
Assert.AreEqual(0, wb.GetNameIndex("myname"));
try
{
wb.GetNameAt(5);
Assert.Fail("Fails without any defined names");
}
catch (ArgumentException e)
{
//Assert.IsTrue(e.Message.Contains("outside the allowable range"), e.Message);
}
try
{
wb.GetNameAt(-3);
Assert.Fail("Fails without any defined names");
}
catch (ArgumentException e)
{
//Assert.IsTrue(e.Message.Contains("outside the allowable range"), e.Message);
}
}
public void TestUnicodeNamedRange()
{
HSSFWorkbook workBook = new HSSFWorkbook();
workBook.CreateSheet("Test");
NPOI.SS.UserModel.Name name = workBook.CreateName();
name.NameName = ("\u03B1");
name.RefersToFormula = ("Test!$D$3:$E$8");
HSSFWorkbook workBook2 = HSSFTestDataSamples.WriteOutAndReadBack(workBook);
NPOI.SS.UserModel.Name name2 = workBook2.GetNameAt(0);
Assert.AreEqual("\u03B1", name2.NameName);
Assert.AreEqual("Test!$D$3:$E$8", name2.RefersToFormula);
}
/// <summary>
/// 设置有效性
/// </summary>
/// <param name="colName">列名</param>
/// <param name="startRowIndex">开始行索引 0起</param>
/// <param name="validSheetName">有效性 目标sheet</param>
/// <param name="validateColName">有效性列名</param>
/// <param name="validateRowStart">有效性开始行 0起</param>
/// <param name="validateRowEnd">有效性结束行 0起</param>
public void SetValidation(string colName, int startRowIndex,
string validSheetName, string validateColName, int validateRowStart, int validateRowEnd)
{
var columnIndex = ColumnNameToIndex(colName);
//设置数据有效性作用域
var regions = new CellRangeAddressList(startRowIndex, 65535, columnIndex, columnIndex);
//设置名称管理器管理数据源范围
var range = workbook.CreateName();
// 验证页, 验证列名 验证开始行
range.RefersToFormula = validSheetName + "!$" + validateColName + "$" + (validateRowStart + 1) +
//验证结束列 //验证结束行
":$" + validateColName + "$" + (validateRowStart + validateRowEnd);
range.NameName = "dicRange" + columnIndex;
//根据名称生成下拉框内容
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("dicRange" + columnIndex);
//绑定下拉框和作用区域
var dataValidate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidate);
}
public void TestNamedCell_1()
{
// setup for this Testcase
String sheetName = "Test Named Cell";
String cellName = "A name for a named cell";
String cellValue = "TEST Value";
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet(sheetName);
sheet.CreateRow(0).CreateCell(0).SetCellValue(new HSSFRichTextString(cellValue));
// Create named range for a single cell using areareference
NPOI.SS.UserModel.Name namedCell = wb.CreateName();
namedCell.NameName = (cellName);
String reference = "'" + sheetName + "'" + "!A1:A1";
namedCell.RefersToFormula = (reference);
// retrieve the newly Created named range
int namedCellIdx = wb.GetNameIndex(cellName);
NPOI.SS.UserModel.Name aNamedCell = wb.GetNameAt(namedCellIdx);
Assert.IsNotNull(aNamedCell);
// retrieve the cell at the named range and Test its contents
AreaReference aref = new AreaReference(aNamedCell.RefersToFormula);
Assert.IsTrue(aref.IsSingleCell, "Should be exactly 1 cell in the named cell :'" + cellName + "'");
CellReference cref = aref.FirstCell;
Assert.IsNotNull(cref);
NPOI.SS.UserModel.Sheet s = wb.GetSheet(cref.SheetName);
Assert.IsNotNull(s);
Row r = sheet.GetRow(cref.Row);
Cell c = r.GetCell(cref.Col);
String contents = c.RichStringCellValue.String;
Assert.AreEqual(contents, cellValue, "Contents of cell retrieved by its named reference");
}
public static HSSFDataValidation CreateListConstraint(this HSSFWorkbook book, int columnIndex, IEnumerable <string> values)
{
var sheetName = "_constraintSheet_";
ISheet sheet = book.GetSheet(sheetName) ?? book.CreateSheet(sheetName);
var firstRow = sheet.GetRow(0);
var conColumnIndex = firstRow == null ? 0 : firstRow.PhysicalNumberOfCells;
var rowIndex = 0;
var lastValue = "";
foreach (var value in values)
{
var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
row.CreateCell(conColumnIndex).SetCellValue(value);
rowIndex++;
lastValue = value;
}
//如果只有一个可选值，则增加一个相同的选项
if (values.Count() == 1)
{
var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
row.CreateCell(conColumnIndex).SetCellValue(lastValue);
rowIndex++;
}
IName range = book.CreateName();
range.RefersToFormula = String.Format("{2}!${0}$1:${0}${1}",
(Char)('A' + conColumnIndex),
rowIndex.ToString(), sheetName);
string rangeName = "dicRange" + columnIndex;
range.NameName = rangeName;
var cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex);
var constraint = DVConstraint.CreateFormulaListConstraint(rangeName);
book.SetSheetHidden(book.GetSheetIndex(sheet), true);
return(new HSSFDataValidation(cellRegions, constraint));
}
public void TestWithNamedRange()
{
HSSFWorkbook workbook = new HSSFWorkbook();
FormulaParser fp;
Ptg[] ptgs;
NPOI.SS.UserModel.Sheet s = workbook.CreateSheet("Foo");
s.CreateRow(0).CreateCell((short)0).SetCellValue(1.1);
s.CreateRow(1).CreateCell((short)0).SetCellValue(2.3);
s.CreateRow(2).CreateCell((short)2).SetCellValue(3.1);
NPOI.SS.UserModel.Name name = workbook.CreateName();
name.NameName = ("testName");
name.RefersToFormula = ("A1:A2");
ptgs = HSSFFormulaParser.Parse("SUM(testName)", workbook);
Assert.IsTrue(ptgs.Length == 2, "two tokens expected, got " + ptgs.Length);
Assert.AreEqual(typeof(NamePtg), ptgs[0].GetType());
Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType());
// Now make it a single cell
name.RefersToFormula = ("C3");
ptgs = HSSFFormulaParser.Parse("SUM(testName)", workbook);
Assert.IsTrue(ptgs.Length == 2, "two tokens expected, got " + ptgs.Length);
Assert.AreEqual(typeof(NamePtg), ptgs[0].GetType());
Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType());
// And make it non-contiguous
name.RefersToFormula = ("A1:A2,C3");
ptgs = HSSFFormulaParser.Parse("SUM(testName)", workbook);
Assert.IsTrue(ptgs.Length == 2, "two tokens expected, got " + ptgs.Length);
Assert.AreEqual(typeof(NamePtg), ptgs[0].GetType());
Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType());
}
public static void test1()
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
HSSFSheet sheet2 = hssfworkbook.CreateSheet("ShtDictionary") as HSSFSheet;
sheet2.CreateRow(0).CreateCell(0).SetCellValue("itemA");
sheet2.CreateRow(1).CreateCell(0).SetCellValue("itemB");
sheet2.CreateRow(2).CreateCell(0).SetCellValue("itemC");
HSSFName range = hssfworkbook.CreateName() as HSSFName;
range.RefersToFormula = "ShtDictionary!$A1:$A3";
range.NameName = "dicRange";
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1") as HSSFSheet;
CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0);
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("dicRange");
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet1.AddValidationData(dataValidate);
MemoryStream ms = new MemoryStream();
hssfworkbook.Write(ms);
string workbookFile = @"D:\\wulei1.xls";
hssfworkbook = null;
FileStream fs = new FileStream(workbookFile, FileMode.Create, FileAccess.Write);
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
fs.Close();
}
public void TestInvoke()
{
HSSFWorkbook wb;
NPOI.SS.UserModel.Sheet sheet ;
Cell cell;
if (false)
{
// TODO - this code won't work until we can create user-defined functions directly with POI
wb = new HSSFWorkbook();
sheet = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
NPOI.SS.UserModel.Name hssfName = wb.CreateName();
hssfName.NameName = ("myFunc");
}
else
{
// This sample spreadsheet already has a VB function called 'myFunc'
wb = HSSFTestDataSamples.OpenSampleWorkbook("testNames.xls");
sheet = wb.GetSheetAt(0);
Row row = sheet.CreateRow(0);
cell = row.CreateCell(1);
}
cell.CellFormula = ("myFunc()");
String actualFormula = cell.CellFormula;
Assert.AreEqual("myFunc()", actualFormula);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
NPOI.SS.UserModel.CellValue evalResult = fe.Evaluate(cell);
// Check the return value from ExternalFunction.evaluate()
// TODO - make this test assert something more interesting as soon as ExternalFunction works a bit better
Assert.AreEqual(NPOI.SS.UserModel.CellType.ERROR, evalResult.CellType);
Assert.AreEqual(ErrorEval.FUNCTION_NOT_IMPLEMENTED.ErrorCode, evalResult.ErrorValue);
}
public void TestSetSheetOrderHSSF()
{
IWorkbook wb = new HSSFWorkbook();
ISheet s1 = wb.CreateSheet("first sheet");
ISheet s2 = wb.CreateSheet("other sheet");
IName name1 = wb.CreateName();
name1.NameName = (/*setter*/ "name1");
name1.RefersToFormula = (/*setter*/ "'first sheet'!D1");
IName name2 = wb.CreateName();
name2.NameName = (/*setter*/ "name2");
name2.RefersToFormula = (/*setter*/ "'other sheet'!C1");
IRow s1r1 = s1.CreateRow(2);
ICell c1 = s1r1.CreateCell(3);
c1.SetCellValue(30);
ICell c2 = s1r1.CreateCell(2);
c2.CellFormula = (/*setter*/ "SUM('other sheet'!C1,'first sheet'!C1)");
IRow s2r1 = s2.CreateRow(0);
ICell c3 = s2r1.CreateCell(1);
c3.CellFormula = (/*setter*/ "'first sheet'!D3");
ICell c4 = s2r1.CreateCell(2);
c4.CellFormula = (/*setter*/ "'other sheet'!D3");
// conditional formatting
ISheetConditionalFormatting sheetCF = s1.SheetConditionalFormatting;
IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule(
ComparisonOperator.BETWEEN, "'first sheet'!D1", "'other sheet'!D1");
IConditionalFormattingRule[] cfRules = { rule1 };
CellRangeAddress[] regions = { new CellRangeAddress(2, 4, 0, 0), // A3:A5
};
sheetCF.AddConditionalFormatting(regions, cfRules);
wb.SetSheetOrder("other sheet", 0);
// names
Assert.AreEqual("'first sheet'!D1", wb.GetName("name1").RefersToFormula);
Assert.AreEqual("'other sheet'!C1", wb.GetName("name2").RefersToFormula);
// cells
Assert.AreEqual("SUM('other sheet'!C1,'first sheet'!C1)", c2.CellFormula);
Assert.AreEqual("'first sheet'!D3", c3.CellFormula);
Assert.AreEqual("'other sheet'!D3", c4.CellFormula);
// conditional formatting
IConditionalFormatting cf = sheetCF.GetConditionalFormattingAt(0);
Assert.AreEqual("'first sheet'!D1", cf.GetRule(0).Formula1);
Assert.AreEqual("'other sheet'!D1", cf.GetRule(0).Formula2);
}
