/**
* Opens the sheet we wrote out by BinomialOperator and makes sure the formulas
* all Match what we expect (x operator y)
*/
private static void BinomialVerify(String operator1, HSSFWorkbook wb)
{
NPOI.SS.UserModel.ISheet s = wb.GetSheetAt(0);
IRow r = null;
ICell c = null;
//get our minimum values
r = s.GetRow(0);
c = r.GetCell(1);
Assert.IsTrue(("1" + operator1 + "1").Equals(c.CellFormula),
"minval Formula is as expected 1" + operator1 + "1 != " + c.CellFormula);
for (int x = 1; x < short.MaxValue && x > 0; x = (short)(x * 2))
{
r = s.GetRow(x);
for (int y = 1; y < 256 && y > 0; y++)
{
c = r.GetCell(y);
Assert.IsTrue(("" + x + operator1 + y).Equals(c.CellFormula),
"loop Formula is as expected " + x + operator1 + y + "!=" + c.CellFormula
);
}
}
//Test our maximum values
r = s.GetRow(0);
c = r.GetCell(0);
Assert.IsTrue(
("" + short.MaxValue + operator1 + short.MaxValue).Equals(c.CellFormula), "maxval Formula is as expected"
);
}
public void TestWithTwoHyperlinks()
{
HSSFWorkbook wb = OpenSample("WithTwoHyperLinks.xls");
NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(0);
ICell cell1 = sheet.GetRow(4).GetCell(0);
IHyperlink link1 = cell1.Hyperlink;
Assert.IsNotNull(link1);
Assert.AreEqual("Foo", link1.Label);
Assert.AreEqual("http://poi.apache.org/", link1.Address);
Assert.AreEqual(4, link1.FirstRow);
Assert.AreEqual(0, link1.FirstColumn);
ICell cell2 = sheet.GetRow(8).GetCell(1);
IHyperlink link2 = cell2.Hyperlink;
Assert.IsNotNull(link2);
Assert.AreEqual("Bar", link2.Label);
Assert.AreEqual("http://poi.apache.org/hssf/", link2.Address);
Assert.AreEqual(8, link2.FirstRow);
Assert.AreEqual(1, link2.FirstColumn);
wb.Close();
}
public void TestOnARealFile()
{
HSSFWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("DateFormats.xls");
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
InternalWorkbook wb = workbook.Workbook;
Assert.IsNotNull(wb);
IRow row;
ICell cell;
NPOI.SS.UserModel.ICellStyle style;
double aug_10_2007 = 39304.0;
// Should have dates in 2nd column
// All of them are the 10th of August
// 2 US dates, 3 UK dates
row = sheet.GetRow(0);
cell = row.GetCell(1);
style = cell.CellStyle;
Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001);
Assert.AreEqual("d-mmm-yy", style.GetDataFormatString());
Assert.IsTrue(DateUtil.IsInternalDateFormat(style.DataFormat));
Assert.IsTrue(DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString()));
Assert.IsTrue(DateUtil.IsCellDateFormatted(cell));
row = sheet.GetRow(1);
cell = row.GetCell(1);
style = cell.CellStyle;
Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001);
Assert.IsFalse(DateUtil.IsInternalDateFormat(cell.CellStyle.DataFormat));
Assert.IsTrue(DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString()));
Assert.IsTrue(DateUtil.IsCellDateFormatted(cell));
row = sheet.GetRow(2);
cell = row.GetCell(1);
style = cell.CellStyle;
Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001);
Assert.IsTrue(DateUtil.IsInternalDateFormat(cell.CellStyle.DataFormat));
Assert.IsTrue(DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString()));
Assert.IsTrue(DateUtil.IsCellDateFormatted(cell));
row = sheet.GetRow(3);
cell = row.GetCell(1);
style = cell.CellStyle;
Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001);
Assert.IsFalse(DateUtil.IsInternalDateFormat(cell.CellStyle.DataFormat));
Assert.IsTrue(DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString()));
Assert.IsTrue(DateUtil.IsCellDateFormatted(cell));
row = sheet.GetRow(4);
cell = row.GetCell(1);
style = cell.CellStyle;
Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001);
Assert.IsFalse(DateUtil.IsInternalDateFormat(cell.CellStyle.DataFormat));
Assert.IsTrue(DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString()));
Assert.IsTrue(DateUtil.IsCellDateFormatted(cell));
}
public void Test44410()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("SingleLetterRanges.xls");
NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(0);
HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(wb);
// =index(C:C,2,1) -> 2
IRow rowIDX = sheet.GetRow(3);
// =sum(C:C) -> 6
IRow rowSUM = sheet.GetRow(4);
// =sum(C:D) -> 66
IRow rowSUM2D = sheet.GetRow(5);
// Test the sum
ICell cellSUM = rowSUM.GetCell(0);
FormulaRecordAggregate frec = (FormulaRecordAggregate)((HSSFCell)cellSUM).CellValueRecord;
Ptg[] ops = frec.FormulaRecord.ParsedExpression;
Assert.AreEqual(2, ops.Length);
Assert.AreEqual(typeof(AreaPtg), ops[0].GetType());
Assert.AreEqual(typeof(FuncVarPtg), ops[1].GetType());
// Actually stored as C1 to C65536
// (last row is -1 === 65535)
AreaPtg ptg = (AreaPtg)ops[0];
Assert.AreEqual(2, ptg.FirstColumn);
Assert.AreEqual(2, ptg.LastColumn);
Assert.AreEqual(0, ptg.FirstRow);
Assert.AreEqual(65535, ptg.LastRow);
Assert.AreEqual("C:C", ptg.ToFormulaString());
// Will show as C:C, but won't know how many
// rows it covers as we don't have the sheet
// to hand when turning the Ptgs into a string
Assert.AreEqual("SUM(C:C)", cellSUM.CellFormula);
// But the evaluator knows the sheet, so it
// can do it properly
Assert.AreEqual(6, eva.Evaluate(cellSUM).NumberValue, 0);
// Test the index
// Again, the formula string will be right but
// lacking row count, Evaluated will be right
ICell cellIDX = rowIDX.GetCell(0);
Assert.AreEqual("INDEX(C:C,2,1)", cellIDX.CellFormula);
Assert.AreEqual(2, eva.Evaluate(cellIDX).NumberValue, 0);
// Across two colums
ICell cellSUM2D = rowSUM2D.GetCell(0);
Assert.AreEqual("SUM(C:D)", cellSUM2D.CellFormula);
Assert.AreEqual(66, eva.Evaluate(cellSUM2D).NumberValue, 0);
wb.Close();
}
public DataTable RenderFromExcel(NPOI.SS.UserModel.ISheet sheet)
{
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
int rowCount = sheet.LastRowNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j);
}
}
}
table.Rows.Add(dataRow);
}
return(table);
}
//导入
public DataTable ImportExcel(string filePath)
{
HSSFWorkbook hssfworkbook;
//初始化信息
#region
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
#endregion
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
{
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(0); //第一行为标题行
int cellCount = headerRow.LastCellNum; //LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum; //LastRowNum = PhysicalNumberOfRows - 1
//handling header.
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
//if (headerRow.getCell(0) != null)
//{
// row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
// stuUser.setPhone(row.getCell(0).getStringCellValue());
//}
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = GetCellValue(row.GetCell(j));
}
}
}
table.Rows.Add(dataRow);
}
return(table);
}
}
/// <summary>
/// 导入Excel
/// </summary>
/// <param name="importPath">导入文件路径</param>
/// <param name="headerIndex">表头所在行索引 （兼容第一行为大标题的情况）</param>
public static DataSet ImportExcel(string importPath, int headerIndex = 0)
{
DataSet ds = new DataSet();
DataTable dt = null;
FileStream fs = new FileStream(importPath, FileMode.Open, FileAccess.Read);
IWorkbook book;
if (Path.GetExtension(importPath) == "xls")
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
}
else
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);
}
int sheetCount = book.NumberOfSheets;
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex);
if (sheet == null)
{
continue;
}
NPOI.SS.UserModel.IRow row = sheet.GetRow(headerIndex); //从第0行开始取 列头
if (row == null)
{
continue;
}
int firstCellNum = row.FirstCellNum;
int lastCellNum = row.LastCellNum;
if (firstCellNum == lastCellNum)
{
continue;
}
dt = new DataTable(sheet.SheetName);
for (int i = firstCellNum; i < lastCellNum; i++)
{
dt.Columns.Add(row.GetCell(i).StringCellValue, typeof(string));
}
for (int i = headerIndex + 1; i <= sheet.LastRowNum; i++) //从第1行开始取数据
{
DataRow newRow = dt.Rows.Add();
for (int j = firstCellNum; j < lastCellNum; j++)
{
var cell = sheet.GetRow(i).GetCell(j);
cell.SetCellType(CellType.String);
newRow[j] = sheet.GetRow(i).GetCell(j).StringCellValue;
}
}
ds.Tables.Add(dt);
}
return(ds);
}
private void button1_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
DataTable dt = null;
OpenFileDialog sflg = new OpenFileDialog();
sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
FileStream fs = new FileStream(sflg.FileName, FileMode.Open, FileAccess.Read);
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
int sheetCount = book.NumberOfSheets;
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex);
if (sheet == null)
{
continue;
}
NPOI.SS.UserModel.IRow row = sheet.GetRow(0);
if (row == null)
{
continue;
}
int firstCellNum = row.FirstCellNum;
int lastCellNum = row.LastCellNum;
if (firstCellNum == lastCellNum)
{
continue;
}
dt = new DataTable(sheet.SheetName);
for (int i = firstCellNum; i < lastCellNum; i++)
{
dt.Columns.Add(row.GetCell(i).StringCellValue, typeof(string));
}
for (int i = 1; i <= sheet.LastRowNum; i++)
{
DataRow newRow = dt.Rows.Add();
for (int j = firstCellNum; j < lastCellNum; j++)
{
newRow[j] = sheet.GetRow(i).GetCell(j).StringCellValue;
}
}
ds.Tables.Add(dt);
}
}
static void CreateSheet(List <Record> rs, NPOI.SS.UserModel.ISheet sheet)
{
var OutRs = new List <OutPutRecord>();
var 仓库统计H型钢 = string.Empty;
var 仓库统计低合金H型钢 = string.Empty;
foreach (var 仓库Group in rs.GroupBy(x => x.仓库))
{
var 仓库记录列表 = 仓库Group.ToList();
foreach (var 规格Group in 仓库记录列表.GroupBy(x => x.规格 + "-" + x.产地))
{
var r = new OutPutRecord();
var SegInfo = 规格Group.Key.Split("-".ToCharArray());
r.规格 = SegInfo[0];
r.产地 = 仓库Group.Key + "-" + SegInfo[1] + (SegInfo.Length == 3 ? ("-" + SegInfo[2]) : string.Empty);
r.数量 = 规格Group.Sum(x => x.可供数);
Console.WriteLine(r.产地 + ":" + r.规格);
OutRs.Add(r);
}
仓库统计H型钢 += 仓库Group.Key + ":" + 仓库Group.Sum(x => x.品名 == "H型钢" ? x.可供数 : 0) + "支，";
仓库统计低合金H型钢 += 仓库Group.Key + ":" + 仓库Group.Sum(x => x.品名 == "低合金H型钢" ? x.可供数 : 0) + "支，";
}
Console.WriteLine("Start Fill");
//填写数量
SeekAndFill(sheet, OutRs, 0);
SeekAndFill(sheet, OutRs, 10);
if (sheet.SheetName == "H型钢")
{
var rfirst = sheet.FirstRowNum;
var rlast = sheet.LastRowNum;
for (int i = rfirst; i < rlast; i++)
{
var row = sheet.GetRow(i);
if (row.GetCell(1) != null && row.GetCell(1).CellType == CellType.String && row.GetCell(1).StringCellValue == "仓库")
{
row.GetCell(2).SetCellValue(仓库统计H型钢.TrimEnd("，".ToCharArray()));
break;
}
}
}
else
{
SeekAndFill(sheet, OutRs, 20);
var rfirst = sheet.FirstRowNum;
var rlast = sheet.LastRowNum;
for (int i = rfirst; i < rlast; i++)
{
var row = sheet.GetRow(i);
if (row.GetCell(21) != null && row.GetCell(21).CellType == CellType.String && row.GetCell(21).StringCellValue == "仓库")
{
row.GetCell(22).SetCellValue(仓库统计低合金H型钢.TrimEnd("，".ToCharArray()));
break;
}
}
}
}
public void TestStyleNames()
{
HSSFWorkbook wb = OpenSample("WithExtendedStyles.xls");
NPOI.SS.UserModel.ISheet s = wb.GetSheetAt(0);
ICell c1 = s.GetRow(0).GetCell(0);
ICell c2 = s.GetRow(1).GetCell(0);
ICell c3 = s.GetRow(2).GetCell(0);
HSSFCellStyle cs1 = (HSSFCellStyle)c1.CellStyle;
HSSFCellStyle cs2 = (HSSFCellStyle)c2.CellStyle;
HSSFCellStyle cs3 = (HSSFCellStyle)c3.CellStyle;
Assert.IsNotNull(cs1);
Assert.IsNotNull(cs2);
Assert.IsNotNull(cs3);
// Check we got the styles we'd expect
Assert.AreEqual(10, cs1.GetFont(wb).FontHeightInPoints);
Assert.AreEqual(9, cs2.GetFont(wb).FontHeightInPoints);
Assert.AreEqual(12, cs3.GetFont(wb).FontHeightInPoints);
Assert.AreEqual(15, cs1.Index);
Assert.AreEqual(23, cs2.Index);
Assert.AreEqual(24, cs3.Index);
Assert.IsNull(cs1.ParentStyle);
Assert.IsNotNull(cs2.ParentStyle);
Assert.IsNotNull(cs3.ParentStyle);
Assert.AreEqual(21, cs2.ParentStyle.Index);
Assert.AreEqual(22, cs3.ParentStyle.Index);
// Now Check we can get style records for
// the parent ones
Assert.IsNull(wb.Workbook.GetStyleRecord(15));
Assert.IsNull(wb.Workbook.GetStyleRecord(23));
Assert.IsNull(wb.Workbook.GetStyleRecord(24));
Assert.IsNotNull(wb.Workbook.GetStyleRecord(21));
Assert.IsNotNull(wb.Workbook.GetStyleRecord(22));
// Now Check the style names
Assert.AreEqual(null, cs1.UserStyleName);
Assert.AreEqual(null, cs2.UserStyleName);
Assert.AreEqual(null, cs3.UserStyleName);
Assert.AreEqual("style1", cs2.ParentStyle.UserStyleName);
Assert.AreEqual("style2", cs3.ParentStyle.UserStyleName);
// now apply a named style to a new cell
ICell c4 = s.GetRow(0).CreateCell(1);
c4.CellStyle = (cs2);
Assert.AreEqual("style1", ((HSSFCellStyle)c4.CellStyle).ParentStyle.UserStyleName);
}
/// <summary>读取excel
/// 默认第一行为标头
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <returns></returns>
public static DataTable Import(string strFileName)
{
DataTable dt = new DataTable();
// HSSFWorkbook hssfworkbook;
IWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
string fileExt = Path.GetExtension(strFileName);
if (fileExt == ".xls")
{
hssfworkbook = new HSSFWorkbook(file);
}
else if (fileExt == ".xlsx")
{
hssfworkbook = new XSSFWorkbook(file);
}
else
{
return(new DataTable());
}
}
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
return(dt);
}
public void TestReadEmployeeSimple()
{
HSSFWorkbook workbook = OpenSample("Employee.xls");
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
Assert.AreEqual(EMPLOYEE_INFORMATION, sheet.GetRow(1).GetCell(1).RichStringCellValue.String);
Assert.AreEqual(LAST_NAME_KEY, sheet.GetRow(3).GetCell(2).RichStringCellValue.String);
Assert.AreEqual(FIRST_NAME_KEY, sheet.GetRow(4).GetCell(2).RichStringCellValue.String);
Assert.AreEqual(SSN_KEY, sheet.GetRow(5).GetCell(2).RichStringCellValue.String);
}
public void Test44297()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("44297.xls");
IRow row;
ICell cell;
NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(0);
HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(wb);
row = sheet.GetRow(0);
cell = row.GetCell(0);
Assert.AreEqual("31+46", cell.CellFormula);
Assert.AreEqual(77, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(1);
cell = row.GetCell(0);
Assert.AreEqual("30+53", cell.CellFormula);
Assert.AreEqual(83, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(2);
cell = row.GetCell(0);
Assert.AreEqual("SUM(A1:A2)", cell.CellFormula);
Assert.AreEqual(160, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(4);
cell = row.GetCell(0);
Assert.AreEqual("32767+32768", cell.CellFormula);
Assert.AreEqual(65535, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(7);
cell = row.GetCell(0);
Assert.AreEqual("32744+42333", cell.CellFormula);
Assert.AreEqual(75077, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(8);
cell = row.GetCell(0);
Assert.AreEqual("327680/32768", cell.CellFormula);
Assert.AreEqual(10, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(9);
cell = row.GetCell(0);
Assert.AreEqual("32767+32769", cell.CellFormula);
Assert.AreEqual(65536, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(10);
cell = row.GetCell(0);
Assert.AreEqual("35000+36000", cell.CellFormula);
Assert.AreEqual(71000, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(11);
cell = row.GetCell(0);
Assert.AreEqual("-1000000-3000000", cell.CellFormula);
Assert.AreEqual(-4000000, eva.Evaluate(cell).NumberValue, 0);
wb.Close();
}
/// <summary>
/// 删除指定工作表名的所有行
/// </summary>
/// <param name="sheetName">工作表名称</param>
private void sheetClear(string sheetName)
{
xlSheet = xlBook.GetSheet(sheetName);
NPOI.SS.UserModel.IRow row = null;
for (int i = 0; i < xlSheet.LastRowNum + 1; i++)
{
if (xlSheet.GetRow(i) != null)
{
row = xlSheet.GetRow(i);
xlSheet.RemoveRow(row);
}
}
}
/// <summary>
/// 合并单元格
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="sheet">工作表</param>
/// <param name="columnIndex">列索引</param>
/// <param name="options">导出选项配置</param>
private void MergeCells <T>(NPOI.SS.UserModel.ISheet sheet, int columnIndex, IExportOptions <T> options)
where T : class, new()
{
string currentCellValue;
var startRowIndex = options.DataRowStartIndex;
NPOI.SS.Util.CellRangeAddress mergeRangeAddress;
var startRow = sheet.GetRow(startRowIndex);
if (startRow == null)
{
return;
}
var startCell = startRow.GetCell(columnIndex);
if (startCell == null)
{
return;
}
string startCellValue = startCell.StringCellValue;
if (string.IsNullOrWhiteSpace(startCellValue))
{
return;
}
for (var rowIndex = options.DataRowStartIndex; rowIndex < sheet.PhysicalNumberOfRows; rowIndex++)
{
var cell = sheet.GetRow(rowIndex)?.GetCell(columnIndex);
currentCellValue = cell == null ? string.Empty : cell.StringCellValue;
if (currentCellValue.Trim() != startCellValue.Trim())
{
mergeRangeAddress = new CellRangeAddress(startRowIndex, rowIndex - 1, columnIndex, columnIndex);
if (mergeRangeAddress.NumberOfCells > 1)
{
sheet.AddMergedRegion(mergeRangeAddress);
startRow.GetCell(columnIndex).CellStyle.VerticalAlignment = VerticalAlignment.Center;
}
startRowIndex = rowIndex;
startCellValue = currentCellValue;
}
if (rowIndex == sheet.PhysicalNumberOfRows - 1 && startRowIndex != rowIndex)
{
mergeRangeAddress = new CellRangeAddress(startRowIndex, rowIndex, columnIndex, columnIndex);
sheet.AddMergedRegion(mergeRangeAddress);
startRow.GetCell(columnIndex).CellStyle.VerticalAlignment = VerticalAlignment.Center;
}
}
}
public void TestModifySimple()
{
HSSFWorkbook workbook = OpenSample("Simple.xls");
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
ICell cell = sheet.GetRow(0).GetCell(0);
cell.SetCellValue(new HSSFRichTextString(REPLACED));
workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook);
sheet = workbook.GetSheetAt(0);
cell = sheet.GetRow(0).GetCell(0);
Assert.AreEqual(REPLACED, cell.RichStringCellValue.String);
}
/// <summary>
/// 设置单元格内容
/// </summary>
/// <param name="row">单元格行标(从0开始，下同)</param>
/// <param name="col">单元格列标</param>
/// <param name="o">写入内容</param>
public void SetValue(int r, int c, object o)
{
if (o != null)
{
if (r <= osheet.LastRowNum)
{
IRow row = osheet.GetRow(r);
if (row == null)
{
row = osheet.CreateRow(r);
row.HeightInPoints = 14;
}
if (c <= row.LastCellNum)
{
ICell cell = row.GetCell(c);
cell.SetCellValue(o.ToString());
}
else
{
for (int j = row.LastCellNum; j < c; j++)
{
row.CreateCell(j + 1);
ICell cell22 = row.GetCell(j + 1);
ICellStyle style = obook.CreateCellStyle();
cell22.CellStyle = style;
}
ICell cell = row.GetCell(c);
cell.SetCellValue(o.ToString());
}
}
else
{
for (int i = osheet.LastRowNum; i < r; i++)
{
IRow row22 = osheet.CreateRow(i + 1);
row22.HeightInPoints = 14;
}
IRow row = osheet.GetRow(r);
for (int j = row.LastCellNum; j < c; j++)
{
row.CreateCell(j + 1);;
ICell cell22 = row.GetCell(j + 1);
ICellStyle style = obook.CreateCellStyle();
cell22.CellStyle = style;
}
ICell cell = row.GetCell(c);
cell.SetCellValue(o.ToString());
}
}
}
public void TestBOFandEOFRecords()
{
HSSFWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("SimpleChart.xls");
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
IRow firstRow = sheet.GetRow(0);
ICell firstCell = firstRow.GetCell(0);
//System.out.println("first assertion for date");
Assert.AreEqual(new DateTime(2000, 1, 1, 10, 51, 2),
DateUtil.GetJavaDate(firstCell.NumericCellValue, false));
IRow row = sheet.CreateRow(15);
ICell cell = row.CreateCell(1);
cell.SetCellValue(22);
InternalSheet newSheet = ((HSSFSheet)workbook.GetSheetAt(0)).Sheet;
IList <RecordBase> records = newSheet.Records;
//System.out.println("BOF Assertion");
Assert.IsTrue(records[0] is BOFRecord);
//System.out.println("EOF Assertion");
Assert.IsTrue(records[records.Count - 1] is EOFRecord);
workbook.Close();
}
public void TestAbsRefs()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r;
ICell c;
r = s.CreateRow(0);
c = r.CreateCell(0);
c.CellFormula = ("A3+A2");
c = r.CreateCell(1);
c.CellFormula = ("$A3+$A2");
c = r.CreateCell(2);
c.CellFormula = ("A$3+A$2");
c = r.CreateCell(3);
c.CellFormula = ("$A$3+$A$2");
c = r.CreateCell(4);
c.CellFormula = ("SUM($A$3,$A$2)");
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
r = s.GetRow(0);
c = r.GetCell(0);
Assert.IsTrue(("A3+A2").Equals(c.CellFormula), "A3+A2");
c = r.GetCell(1);
Assert.IsTrue(("$A3+$A2").Equals(c.CellFormula), "$A3+$A2");
c = r.GetCell(2);
Assert.IsTrue(("A$3+A$2").Equals(c.CellFormula), "A$3+A$2");
c = r.GetCell(3);
Assert.IsTrue(("$A$3+$A$2").Equals(c.CellFormula), "$A$3+$A$2");
c = r.GetCell(4);
Assert.IsTrue(("SUM($A$3,$A$2)").Equals(c.CellFormula), "SUM($A$3,$A$2)");
}
/**
* Writes a function then Tests to see if its correct
*
*/
public void RefAreaArrayFunctionTest(String function)
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
r = s.CreateRow(0);
c = r.CreateCell(0);
c.CellFormula = (function + "(A2:A4,B2:B4)");
c = r.CreateCell(1);
c.CellFormula = (function + "($A$2:$A4,B$2:B4)");
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
r = s.GetRow(0);
c = r.GetCell(0);
Assert.IsTrue(
(function + "(A2:A4,B2:B4)").Equals(c.CellFormula), "function =" + function + "(A2:A4,B2:B4)"
);
c = r.GetCell(1);
Assert.IsTrue((function + "($A$2:$A4,B$2:B4)").Equals(c.CellFormula),
"function =" + function + "($A$2:$A4,B$2:B4)"
);
}
/**
* All multi-binomial operator Tests use this to Create a worksheet with a
* huge set of x operator y formulas. Next we call BinomialVerify and verify
* that they are all how we expect.
*/
private static void BinomialOperator(String operator1)
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
//get our minimum values
r = s.CreateRow(0);
c = r.CreateCell(1);
c.CellFormula = (1 + operator1 + 1);
for (int x = 1; x < short.MaxValue && x > 0; x = (short)(x * 2))
{
r = s.CreateRow(x);
for (int y = 1; y < 256 && y > 0; y++)
{
c = r.CreateCell(y);
c.CellFormula = ("" + x + operator1 + y);
}
}
//make sure we do the maximum value of the Int operator
if (s.LastRowNum < short.MaxValue)
{
r = s.GetRow(0);
c = r.CreateCell(0);
c.CellFormula = ("" + short.MaxValue + operator1 + short.MaxValue);
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
BinomialVerify(operator1, wb);
}
public void TestSheetFunctions()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet("A");
IRow r = null;
ICell c = null;
r = s.CreateRow(0);
c = r.CreateCell(0); c.SetCellValue(1);
c = r.CreateCell(1); c.SetCellValue(2);
s = wb.CreateSheet("B");
r = s.CreateRow(0);
c = r.CreateCell(0); c.CellFormula = ("AVERAGE(A!A1:B1)");
c = r.CreateCell(1); c.CellFormula = ("A!A1+A!B1");
c = r.CreateCell(2); c.CellFormula = ("A!$A$1+A!$B1");
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheet("B");
r = s.GetRow(0);
c = r.GetCell(0);
Assert.IsTrue(("AVERAGE(A!A1:B1)").Equals(c.CellFormula), "expected: AVERAGE(A!A1:B1) got: " + c.CellFormula);
c = r.GetCell(1);
Assert.IsTrue(("A!A1+A!B1").Equals(c.CellFormula), "expected: A!A1+A!B1 got: " + c.CellFormula);
}
public void TestManyRows()
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
IRow row;
ICell cell;
int i, j;
for (i = 0, j = 32771; j > 0; i++, j--)
{
row = sheet.CreateRow(i);
cell = row.CreateCell(0);
cell.SetCellValue(i);
}
sanityChecker.CheckHSSFWorkbook(workbook);
Assert.AreEqual(32770, sheet.LastRowNum, "LAST ROW == 32770");
cell = sheet.GetRow(32770).GetCell(0);
double lastVal = cell.NumericCellValue;
HSSFWorkbook wb = HSSFTestDataSamples.WriteOutAndReadBack(workbook);
NPOI.SS.UserModel.ISheet s = wb.GetSheetAt(0);
row = s.GetRow(32770);
cell = row.GetCell(0);
Assert.AreEqual(lastVal, cell.NumericCellValue, 0, "Value from last row == 32770");
Assert.AreEqual(32770, s.LastRowNum, "LAST ROW == 32770");
}
public void TestSquareMacro()
{
HSSFWorkbook w = OpenSample("SquareMacro.xls");
NPOI.SS.UserModel.ISheet s0 = w.GetSheetAt(0);
IRow[] r = { s0.GetRow(0), s0.GetRow(1) };
ICell a1 = r[0].GetCell(0);
Assert.AreEqual("square(1)", a1.CellFormula);
Assert.AreEqual(1d, a1.NumericCellValue, 1e-9);
ICell a2 = r[1].GetCell(0);
Assert.AreEqual("square(2)", a2.CellFormula);
Assert.AreEqual(4d, a2.NumericCellValue, 1e-9);
ICell b1 = r[0].GetCell(1);
Assert.AreEqual("IF(TRUE,square(1))", b1.CellFormula);
Assert.AreEqual(1d, b1.NumericCellValue, 1e-9);
ICell b2 = r[1].GetCell(1);
Assert.AreEqual("IF(TRUE,square(2))", b2.CellFormula);
Assert.AreEqual(4d, b2.NumericCellValue, 1e-9);
ICell c1 = r[0].GetCell(2);
Assert.AreEqual("square(square(1))", c1.CellFormula);
Assert.AreEqual(1d, c1.NumericCellValue, 1e-9);
ICell c2 = r[1].GetCell(2);
Assert.AreEqual("square(square(2))", c2.CellFormula);
Assert.AreEqual(16d, c2.NumericCellValue, 1e-9);
ICell d1 = r[0].GetCell(3);
Assert.AreEqual("square(one())", d1.CellFormula);
Assert.AreEqual(1d, d1.NumericCellValue, 1e-9);
ICell d2 = r[1].GetCell(3);
Assert.AreEqual("square(two())", d2.CellFormula);
Assert.AreEqual(4d, d2.NumericCellValue, 1e-9);
}
private static DateTime ReadCell(HSSFWorkbook workbook, int rowIdx, int colIdx)
{
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
IRow row = sheet.GetRow(rowIdx);
ICell cell = row.GetCell(colIdx);
return(cell.DateCellValue);
}
private static void ConfirmResolveCellRef(HSSFWorkbook wb, CellReference cref)
{
NPOI.SS.UserModel.ISheet s = wb.GetSheet(cref.SheetName);
IRow r = s.GetRow(cref.Row);
ICell c = r.GetCell((int)cref.Col);
Assert.IsNotNull(c);
}
private static void ConfirmCell(NPOI.SS.UserModel.ISheet sheet, int rowIx, int colIx,
double expectedValue, String expectedFormula)
{
ICell cell = sheet.GetRow(rowIx).GetCell(colIx);
Assert.AreEqual(expectedValue, cell.NumericCellValue, 0.0);
Assert.AreEqual(expectedFormula, cell.CellFormula);
}
public void TestShiftRows()
{
// Read initial file in
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("SimpleMultiCell.xls");
NPOI.SS.UserModel.ISheet s = wb.GetSheetAt(0);
// Shift the second row down 1 and Write to temp file
s.ShiftRows(1, 1, 1);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
// Read from temp file and Check the number of cells in each
// row (in original file each row was unique)
s = wb.GetSheetAt(0);
Assert.AreEqual(1, s.GetRow(0).PhysicalNumberOfCells);
ConfirmEmptyRow(s, 1);
Assert.AreEqual(2, s.GetRow(2).PhysicalNumberOfCells);
Assert.AreEqual(4, s.GetRow(3).PhysicalNumberOfCells);
Assert.AreEqual(5, s.GetRow(4).PhysicalNumberOfCells);
// Shift rows 1-3 down 3 in the current one. This Tests when
// 1 row is blank. Write to a another temp file
s.ShiftRows(0, 2, 3);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
// Read and ensure things are where they should be
s = wb.GetSheetAt(0);
ConfirmEmptyRow(s, 0);
ConfirmEmptyRow(s, 1);
ConfirmEmptyRow(s, 2);
Assert.AreEqual(1, s.GetRow(3).PhysicalNumberOfCells);
ConfirmEmptyRow(s, 4);
Assert.AreEqual(2, s.GetRow(5).PhysicalNumberOfCells);
// Read the first file again
wb = HSSFTestDataSamples.OpenSampleWorkbook("SimpleMultiCell.xls");
s = wb.GetSheetAt(0);
// Shift rows 3 and 4 up and Write to temp file
s.ShiftRows(2, 3, -2);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
Assert.AreEqual(s.GetRow(0).PhysicalNumberOfCells, 3);
Assert.AreEqual(s.GetRow(1).PhysicalNumberOfCells, 4);
ConfirmEmptyRow(s, 2);
ConfirmEmptyRow(s, 3);
Assert.AreEqual(s.GetRow(4).PhysicalNumberOfCells, 5);
}
private static DataTable ReadFromFile(string filePath)
{
IWorkbook hssfworkbook;
#region 初始化信息
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = WorkbookFactory.Create(file) as IWorkbook;
}
#endregion
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
rows.MoveNext();
IRow row = rows.Current as IRow;
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
//将第一列作为列表头
DataColumn dtc = new DataColumn(row.GetCell(j).ToString(), typeof(string));
dt.Columns.Add(dtc);
}
while (rows.MoveNext())
{
try
{
row = (IRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
Console.WriteLine(cell == null ? "" : cell + "\t\t");
Console.WriteLine();
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
catch (Exception ex)
{
continue;
// throw;
}
}
return(dt);
}
public void TestShiftWithFormulas()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("ForShifting.xls");
NPOI.SS.UserModel.ISheet sheet = wb.GetSheet("Sheet1");
Assert.AreEqual(20, sheet.LastRowNum);
ConfirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)");
ConfirmRow(sheet, 1, 2, 172, 1, "ROW(D2)", "100+B2", "COUNT(D2:E2)");
ConfirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)");
ConfirmCell(sheet, 6, 1, 271, "200+B1");
ConfirmCell(sheet, 7, 1, 272, "200+B2");
ConfirmCell(sheet, 8, 1, 273, "200+B3");
ConfirmCell(sheet, 14, 0, 0.0, "A12"); // the cell referred to by this formula will be replaced
// -----------
// Row index 1 -> 11 (row "2" -> row "12")
sheet.ShiftRows(1, 1, 10);
// Now Check what sheet looks like after move
// no changes on row "1"
ConfirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)");
// row "2" is now empty
Assert.AreEqual(0, sheet.GetRow(1).PhysicalNumberOfCells);
// Row "2" moved to row "12", and the formula has been updated.
// note however that the cached formula result (2) has not been updated. (POI differs from Excel here)
ConfirmRow(sheet, 11, 2, 172, 1, "ROW(D12)", "100+B12", "COUNT(D12:E12)");
// no changes on row "3"
ConfirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)");
ConfirmCell(sheet, 14, 0, 0.0, "#REF!");
// Formulas on rows that weren't shifted:
ConfirmCell(sheet, 6, 1, 271, "200+B1");
ConfirmCell(sheet, 7, 1, 272, "200+B12"); // this one moved
ConfirmCell(sheet, 8, 1, 273, "200+B3");
// Check formulas on other sheets
NPOI.SS.UserModel.ISheet sheet2 = wb.GetSheet("Sheet2");
ConfirmCell(sheet2, 0, 0, 371, "300+Sheet1!B1");
ConfirmCell(sheet2, 1, 0, 372, "300+Sheet1!B12");
ConfirmCell(sheet2, 2, 0, 373, "300+Sheet1!B3");
ConfirmCell(sheet2, 11, 0, 300, "300+Sheet1!#REF!");
// Note - named ranges formulas have not been updated
}