写出xlsx
//创建一个工作簿对象
XSSFWorkbook wb = new XSSFWorkbook();
//插入指定名称的工作表
ISheet sheet = wb.CreateSheet("mysheet");
//插入指定行,在此行对象上创建单元格,并写入内容
IRow row = sheet.CreateRow(2); //参数为工作表的行号
for (int i = 0; i < 10; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(i);
}
//定义一个文件流对象,将工作簿对象保存为Excel文件
FileStream fs = new FileStream("test.xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite);
wb.Write(fs);
fs.Close(); fs.Dispose();
读取xlsx
FileStream fs = new FileStream("test.xlsx", FileMode.Open, FileAccess.Read);
//创建工作簿对象
XSSFWorkbook wb = new XSSFWorkbook(fs);
ISheet sheet = wb.GetSheet("mysheet");
IRow row = sheet.GetRow(2);
Console.WriteLine(row.GetCell(1));
//Cell representing that column or null if undefined. ICell GetCell(int cellnum);
使用DataTable保存读取的表
DataTable dt = new DataTable();
//指定excel所在路径
string excelPath = path;
//以文件流读取excel文件
FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read);
//Offic2007以上
IWorkbook workbook = new XSSFWorkbook(fs);
//Offic2007以下
if (excelPath.ToLower().EndsWith(".xls"))
{
workbook = new XSSFWorkbook(fs);
}
//取第一个sheet页的数据
ISheet sheet = workbook.GetSheetAt(0);
//获取第一行数据
IRow firstRow = sheet.GetRow(0);
//获取第一行中数据列的数量
int columnCount = firstRow.LastCellNum;
for (int i = firstRow.FirstCellNum; i <= columnCount; i++)
{
//获取单元格
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
//将第一行数据作为表头
string cellValue = cell.StringCellValue;
if (!string.IsNullOrEmpty(cellValue))
{
dt.Columns.Add(cellValue);
}
}
}
//获取数据行的数量
int rowCount = sheet.LastRowNum;
for (int i = 1; i <= rowCount; i++)
{
//获取行
IRow row = sheet.GetRow(i);
if (row == null) continue;
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j <= dt.Columns.Count; j++)
{
if (row.GetCell(j) != null)
{
//这里减FirstCellNum可以去除空白列数据
dataRow[j - row.FirstCellNum] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
//资源释放
workbook.Close();
全部代码
using System;
using System.Data;
using System.IO; //提供文件流文件和文件流操作所需类
using NPOI.SS.UserModel; //提供ISheet、IRow、ICell等接口
using NPOI.XSSF.UserModel; //提供相关类操作扩展名为xlsx的2007之后版本Excel文件
namespace TestMpoi
{
class MmyExcel
{
//创建一个新Excel文件并导出
public void CreateExcel()
{
//创建一个工作簿对象
XSSFWorkbook wb = new XSSFWorkbook();
//插入指定名称的工作表
ISheet sheet = wb.CreateSheet("mysheet");
//插入指定行,在此行对象上创建单元格,并写入内容
IRow row = sheet.CreateRow(2); //参数为工作表的行号
for (int i = 0; i < 10; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(i);
}
//定义一个文件流对象,将工作簿对象保存为Excel文件
FileStream fs = new FileStream("test.xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite);
wb.Write(fs);
fs.Close(); fs.Dispose();
}
//打开已有Excel文件,修改内容后再导出
public void OpenExcel()
{
FileStream fs = new FileStream("test.xlsx", FileMode.Open, FileAccess.Read);
//创建工作簿对象
XSSFWorkbook wb = new XSSFWorkbook(fs);
ISheet sheet = wb.GetSheet("mysheet");
IRow row = sheet.GetRow(2);
Console.WriteLine(row.GetCell(1));
fs.Close(); fs.Dispose();
//根据工作表名获取工作簿对象,也可通过索引获取工作表wb.GetSheetAt(int index)
// ISheet sheet = wb.GetSheet("mysheet");
// //更改指定单元格内容,如果该单元格为null则会引发异常,需要对指定行和单元格进行判断
// //sheet.GetRow(3).Cells[2].SetCellValue("内容"); //假设修改内容的单元格不为null可以这样修改内容
// IRow row = sheet.GetRow(3);
// if(row==null) row=sheet.CreateRow(3);
// ICell cell = row.GetCell(2); //ICell cell=row.Cells[2];
// if(cell==null) cell=row.CreateCell(2);
// cell.SetCellValue("内容");
// //定义一个文件流对象,将工作簿对象保存为Excel文件
// //读取指定单元格内容:string str=cell.StringCellValue;
// FileStream fs1 = new FileStream("test2.xlsx",FileMode.OpenOrCreate,FileAccess.ReadWrite);
// wb.Write(fs1);
// fs1.Close(); fs1.Dispose();
}
public DataTable readExcel(String path)
{
//实例化一个datatable用来存储数据
DataTable dt = new DataTable();
//指定excel所在路径
string excelPath = path;
//以文件流读取excel文件
FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read);
//Offic2007以上
IWorkbook workbook = new XSSFWorkbook(fs);
//Offic2007以下
if (excelPath.ToLower().EndsWith(".xls"))
{
workbook = new XSSFWorkbook(fs);
}
//取第一个sheet页的数据
ISheet sheet = workbook.GetSheetAt(0);
//获取第一行数据
IRow firstRow = sheet.GetRow(0);
//获取第一行中数据列的数量
int columnCount = firstRow.LastCellNum;
for (int i = firstRow.FirstCellNum; i <= columnCount; i++)
{
//获取单元格
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
//将第一行数据作为表头
string cellValue = cell.StringCellValue;
if (!string.IsNullOrEmpty(cellValue))
{
dt.Columns.Add(cellValue);
}
}
}
//获取数据行的数量
int rowCount = sheet.LastRowNum;
for (int i = 1; i <= rowCount; i++)
{
//获取行
IRow row = sheet.GetRow(i);
if (row == null) continue;
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j <= dt.Columns.Count; j++)
{
if (row.GetCell(j) != null)
{
//这里减FirstCellNum可以去除空白列数据
dataRow[j - row.FirstCellNum] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
//资源释放
workbook.Close();
//显示到页面
return dt;
}
public void PrintTable(DataTable table)
{
// print head
PrintLine(12 * (table.Columns.Count+1));
Console.Write(string.Format("{0,12}"," "));
foreach (DataColumn col in table.Columns)
{
Console.Write(string.Format("{0,12}", col.Caption));
}
Console.Write("\n");
PrintLine(12 * (table.Columns.Count+1), "=");
// print rows
for (int i = 0; i < table.Rows.Count; i++)
{
Console.Write(string.Format("{0,12}",i+1));
for (int j = 0; j < table.Columns.Count; j++)
{
Console.Write(string.Format("{0,12}", table.Rows[i][j].ToString()));
}
Console.Write("\n");
}
PrintLine(12 * (table.Columns.Count+1));
}
/// <summary>
/// Print a line with specific char on to the console
/// </summary>
/// <param name="length">count of the char to be printed</param>
/// <param name="lineChar">the char to be printed, default is "="</param>
private void PrintLine(int length, string lineChar = "-")
{
string line = string.Empty;
for (int i = 0; i < length; i++)
{
line += lineChar;
}
Console.WriteLine(line);
}
}
}
program.cs
using System;
using System.Data;
using TestMpoi;
namespace helloworld
{
class Program
{
static void Main(string[] args)
{
MmyExcel excel_hhh = new MmyExcel();
excel_hhh.CreateExcel();
excel_hhh.OpenExcel();
DataTable table = new DataTable();
table = excel_hhh.readExcel("./test1.xlsx");
excel_hhh.PrintTable(table);
}
}
}