C#调用NOPI对Excel进行读写操作
添加引用NOPI
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
//===========创建 写入 样式 其他============
XSSFWorkbook workbork = new XSSFWorkbook();//创建workbork
//创建Sheet
workbork.CreateSheet("Sheet1");
workbork.CreateSheet("Sheet2");
//往 Sheet创建行列,并写入值
XSSFSheet sheet = (XSSFSheet)workbork.GetSheet("Sheet1");
sheet.CreateRow(3).CreateCell(2).SetCellValue("This is a Test");
XSSFCell cell =(XSSFCell) sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue(new DateTime(2020, 03, 11));
//格式设置
XSSFCellStyle cellStyle = (XSSFCellStyle)workbork.CreateCellStyle();
XSSFDataFormat format =(XSSFDataFormat) workbork.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyy/mm/dd");
cell.CellStyle = cellStyle;
XSSFCell cell1 = (XSSFCell)sheet.CreateRow(1).CreateCell(1);
cell1.SetCellValue(1.2);
XSSFCellStyle cellStyle1 = (XSSFCellStyle)workbork.CreateCellStyle();
XSSFDataFormat format1 = (XSSFDataFormat)workbork.CreateDataFormat();
cellStyle1.DataFormat = format1.GetFormat("0.00");
cell1.CellStyle = cellStyle1;
XSSFCell cell2 = (XSSFCell)sheet.CreateRow(1).CreateCell(1);
cell2.SetCellValue("Region");
XSSFCellStyle cellStyle2 = (XSSFCellStyle)workbork.CreateCellStyle();
//字体设置
XSSFFont font = (XSSFFont)workbork.CreateFont();
font.FontHeight = 20 * 20;
cellStyle2.SetFont(font);
cell2.CellStyle = cellStyle2;
//===================NOOI Get Cell Value
// 获取某行某列的值=========================
public static string GetCellValue(string filePath,string sheetName,int Row1,int Colunm1)
{
IWorkbook workbook = null;
ISheet sheet = null;
IRow row = null;
ICell cell = null;
using (var fs = File.OpenRead(filePath))
{
if (filePath.IndexOf(".xlsx") > 0)
{
//2007版本
workbook = new XSSFWorkbook(fs);
}
else if (filePath.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(fs);
}
if (workbook != null)
{
sheet = workbook.GetSheet(sheetName);
row = sheet.GetRow(Row1);
cell = row.GetCell(Colunm1);
if (cell != null)
{
cell.SetCellType(CellType.String);
return cell.StringCellValue;
}
}
}
return "";
}
//===================NPOI Get SheetName
// 获取所有Sheet名称 ==========================
public static string[] GetSheetName(string filePath)
{
int sheetNumber = 0;
FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read);
if (filePath.IndexOf(".xlsx") > 0)
{
//2007版本
XSSFWorkbook xssfworkbook = new XSSFWorkbook(file);
sheetNumber = xssfworkbook.NumberOfSheets;
string[] sheetNames = new string[sheetNumber];
for (int i = 0; i < sheetNumber; i++)
{
sheetNames[i] = xssfworkbook.GetSheetName(i);
}
return sheetNames;
}
else if (filePath.IndexOf(".xls") > 0)
{
//2003版本
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
sheetNumber = hssfworkbook.NumberOfSheets;
string[] sheetNames = new string[sheetNumber];
for (int i = 0; i < sheetNumber; i++)
{
sheetNames[i] = hssfworkbook.GetSheetName(i);
}
return sheetNames;
}
return null;
}