public void TestGetSheetByIndex()
{
string dataFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "file", "data.xlsx");
NPOIOperator oper = new NPOIOperator(dataFilePath, true);
var sheet1 = oper.GetSheet(0);
Assert.IsNotNull(sheet1);
var sheet100 = oper.GetSheet(100);
Assert.IsNull(sheet100);
}
public void TestFlushWithTemplate()
{
var dt = GetTestData();
string templateFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "file", "template.xlsx");
var fileName = "template" + Guid.NewGuid().ToString() + ".xlsx";
string dataFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "file", fileName);
NPOIOperator oper = new NPOIOperator(templateFilePath, dataFilePath, true);
var sheet = oper.GetSheet("Sheet1");
oper.WriteContent(sheet, dt, 3, 1);
oper.Flush();
try
{
NPOIOperator oper2 = new NPOIOperator(dataFilePath, true);
var sheet2 = oper2.GetSheet("Sheet1");
var dt2 = oper2.ReadSheet(sheet, true, true, 2, 1);
Assert.AreEqual(3, dt2.Rows.Count);
Assert.AreEqual("李四", dt2.Rows[1]["姓名"]);
Assert.AreEqual(new DateTime(2000, 8, 8), dt2.Rows[1]["出生日期"]);
}
finally
{
File.Delete(dataFilePath);
}
}
public void TestReadCell()
{
string dataFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "file", "forread.xlsx");
NPOIOperator oper = new NPOIOperator(dataFilePath, true);
var sheet = oper.GetSheet(0);
var row = sheet.GetRow(2);
var cell = row.GetCell(1);
var cellValue = oper.ReadCell(cell, CellDataType.Text);
Assert.AreEqual("李四", cellValue);
}
public void TestReadRowWithUseCellType()
{
var dataTable = new DataTable();
dataTable.Columns.Add("列1");
dataTable.Columns.Add("列2");
dataTable.Columns.Add("列3");
dataTable.Columns.Add("列4");
dataTable.Columns.Add("列5");
string dataFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "file", "forread.xlsx");
NPOIOperator oper = new NPOIOperator(dataFilePath, true);
var sheet = oper.GetSheet(0);
var row = sheet.GetRow(2);
var rowData = oper.ReadRow(row, dataTable.Columns, true);
Assert.AreEqual(5, rowData.Length);
Assert.AreEqual("李四", rowData[1]);
Assert.AreEqual(new DateTime(1984, 5, 26), rowData[4]);
}
public void TestReadRow()
{
var dt = GetTestData();
string dataFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "file", "forread.xlsx");
NPOIOperator oper = new NPOIOperator(dataFilePath, true);
var sheet = oper.GetSheet(0);
var row = sheet.GetRow(2);
var rowData = oper.ReadRow(row, dt.Columns);
Assert.AreEqual(5, rowData.Length);
Assert.AreEqual("李四", rowData[1]);
Assert.AreEqual(new DateTime(1984, 5, 26), rowData[4]);
}
public void TestReadContentWithEndRowNumberAndUseCellType()
{
string dataFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "file", "forread.xlsx");
NPOIOperator oper = new NPOIOperator(dataFilePath, true);
var sheet = oper.GetSheet("Sheet1");
var dataTable = new DataTable();
dataTable.Columns.Add("列1");
dataTable.Columns.Add("列2");
dataTable.Columns.Add("列3");
dataTable.Columns.Add("列4");
dataTable.Columns.Add("列5");
var readCount = oper.ReadContent(sheet, 1, 6, dataTable, true);
Assert.AreEqual(6, readCount);
Assert.AreEqual("猴六", dataTable.Rows[3]["列2"].ToString());
Assert.AreEqual(new DateTime(1988, 3, 14), dataTable.Rows[3]["列5"]);
}
public void TestReadContentWithEndRowNumber()
{
string dataFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "file", "forread.xlsx");
NPOIOperator oper = new NPOIOperator(dataFilePath, true);
var sheet = oper.GetSheet("Sheet1");
var dataTable = GetTestDataSchema();
var readCount = oper.ReadContent(sheet, 1, 6, dataTable);
Assert.AreEqual(6, readCount);
Assert.AreEqual("猴六", dataTable.Rows[3]["姓名"].ToString());
Assert.AreEqual(new DateTime(1988, 3, 14), dataTable.Rows[3]["出生日期"]);
}
public void TestReadTitleWithRow()
{
string dataFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "file", "forread.xlsx");
NPOIOperator oper = new NPOIOperator(dataFilePath, true);
var sheet = oper.GetSheet("Sheet1");
var firstRow = sheet.GetRow(0);
var cols = oper.ReadTitle(firstRow);
Assert.AreEqual(5, cols.Length);
Assert.AreEqual("出生日期", cols[4].ColumnName);
}
public void TestReadSheetWithCellType()
{
string dataFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "file", "forread.xlsx");
NPOIOperator oper = new NPOIOperator(dataFilePath, true);
var sheet = oper.GetSheet("Sheet1");
var readDT = oper.ReadSheet(sheet, true, true, 0);
Assert.AreEqual(6, readDT.Rows.Count);
Assert.AreEqual("猴六", readDT.Rows[3]["姓名"].ToString());
Assert.AreEqual(new DateTime(1988, 3, 14), readDT.Rows[3]["出生日期"]);
}
public void TestReadSheetWithSencondLine()
{
string dataFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "file", "forread.xlsx");
NPOIOperator oper = new NPOIOperator(dataFilePath, true);
var sheet = oper.GetSheet("Sheet1");
var readDT = oper.ReadSheet(sheet, false, 1);
Assert.AreEqual(6, readDT.Rows.Count);
Assert.AreEqual("猴六", readDT.Rows[3]["列2"].ToString());
Assert.AreEqual("14-3月-1988", readDT.Rows[3]["列5"]);
}
public void TestWriteCell()
{
var dt = GetTestData();
string dataFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "file", "onesheetfile.xlsx");
NPOIOperator oper = new NPOIOperator(dataFilePath, true);
var sheet = oper.GetSheet("Sheet3");
var row = sheet.CreateRow(0);
var cell1 = row.CreateCell(0);
oper.WriteCell(cell1, dt.Rows[1]["姓名"], CellDataType.Text);
var cell2 = row.CreateCell(1);
oper.WriteCell(cell2, dt.Rows[1]["性别"], CellDataType.Int);
var readDT = oper.ReadSheet(sheet, false, true, 0);
Assert.AreEqual(1, readDT.Rows.Count);
Assert.AreEqual("李四", readDT.Rows[0]["列1"].ToString());
Assert.AreEqual(0, readDT.Rows[0]["列2"]);
}
public void TestWriteRow()
{
var dt = GetTestData();
string dataFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "file", "onesheetfile.xlsx");
NPOIOperator oper = new NPOIOperator(dataFilePath, true);
var sheet = oper.GetSheet("Sheet3");
var row = sheet.CreateRow(1);
oper.WriteRow(row, dt.Columns, dt.Rows[1]);
var readDT = oper.ReadSheet(sheet, false, 1);
Assert.AreEqual(1, readDT.Rows.Count);
Assert.AreEqual("李四", readDT.Rows[0]["列2"].ToString());
}
public void TestWriteContent()
{
var dt = GetTestData();
string dataFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "file", "onesheetfile.xlsx");
NPOIOperator oper = new NPOIOperator(dataFilePath, true);
var sheet = oper.GetSheet("Sheet3");
var writeLineNumber = oper.WriteContent(sheet, dt, 2);
Assert.AreEqual(5, writeLineNumber);
var readDT = oper.ReadSheet(sheet, false, 2);
Assert.AreEqual(3, readDT.Rows.Count);
Assert.AreEqual("李四", readDT.Rows[1]["列2"].ToString());
}
public void TestWriteTitleWithRow()
{
var dt = GetTestData();
string dataFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "file", "onesheetfile.xlsx");
NPOIOperator oper = new NPOIOperator(dataFilePath, true);
var sheet = oper.GetSheet("Sheet3");
var row = sheet.CreateRow(0);
oper.WriteTitle(row, dt.Columns);
var cols = oper.ReadTitle(sheet);
Assert.AreEqual(5, cols.Length);
Assert.AreEqual("姓名", cols[1].ColumnName);
}
public void TestWriteSheetWithSheetInstanceAndNoTitle()
{
var dt = GetTestData();
string dataFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "file", "onesheetfile.xlsx");
NPOIOperator oper = new NPOIOperator(dataFilePath, true);
var sheet = oper.GetSheet("Sheet3");
var writeLineNumber = oper.WriteSheet(sheet, dt, false);
Assert.AreEqual(3, writeLineNumber);
var readDT = oper.ReadSheet("Sheet3", false);
Assert.AreEqual(3, readDT.Rows.Count);
}