项目结构
引入依赖
NPOI.OpenXmlFormats.dll
NPOI.OpenXml4Net.dll
NPOI.OOXML.dll
NPOI.dll
ICSharpCode.SharpZipLib.dll
读取Excel
private void ReadExcel()
{
//1.将Excel读到文件流中
using (FileStream fsRead = File.OpenRead("Student.xls"))
{
//2.根据文件流,创建一个工作簿对象(workbook)
IWorkbook wk = new HSSFWorkbook(fsRead);
//3.读取工作簿中的工作表
for (int i = 0; i < wk.NumberOfSheets; i++)
{
//3.1根据索引,获取一个工作表
ISheet sheet = wk.GetSheetAt(i);
//3.2输出工作表的名称
Console.WriteLine("====={0}=====", sheet.SheetName);
//4.遍历工作表每一行的数据
for (int r = 0; r <= sheet.LastRowNum; r++)
{
//获取当前行
IRow row = sheet.GetRow(r);
//5.遍历一行中的每一个单元格,当这一行为空时,row会返回null
if (row != null)
{
//遍历一行的每一个单元格
for (int c = 0; c < row.LastCellNum; c++)
{
//创建单元格对象
ICell cell = row.GetCell(c);
//如果单元格未使用,则为null
if (cell != null)
{
Console.Write(cell.ToString() + "\t");
}
}
}
Console.WriteLine();
}
}
}
}
写Excel
private void WriteExcel( )
{
List<Person>listPersons=new List<Person>()
{
new Person(){Name="张三 1",Age=11,Gender='男'},
new Person(){Name="张三 2",Age=21,Gender='女'},
new Person(){Name="张三 3",Age=31,Gender='男'},
new Person(){Name="张三 4",Age=41,Gender='女'}
};
//1.创建Workbook对象
IWorkbook wk=new HSSFWorkbook();
//2.创建工作表对象
ISheet sheet = wk.CreateSheet("Persons");
//3.向工作表中添加行
for (int i = 0; i <= listPersons.Count-1; i++)
{
IRow row = sheet.CreateRow(i);
row.CreateCell(0).SetCellValue(listPersons[i].Name);
row.CreateCell(1).SetCellValue(listPersons[i].Age);
row.CreateCell(2).SetCellValue(listPersons[i].Gender.ToString());
}
using (FileStream writer=File.OpenWrite("Persons.xls"))
{
wk.Write(writer);
}
Console.WriteLine("ok");
}