NPOI操作Excel的基本用法
文件的读入
public static void ReadExcel(string excelPath)
{
FileStream fs = new FileStream(excelPath,FileMode.Open, FileAccess.Read);
if(excelPath.EndsWith(".xlsx"))
{
workbook = new XSSFWorkbook(fs);
}
else if(excelPath.EndsWith(".xls"))
{
workbook = new HSSFWorkbook(fs);
}
else
{
workbook = null;
}
}
获取sheet个数
public static int GetSheetCount()
{
if (workbook != null)
{
return workbook.NumberOfSheets;
}
return 0;
}
获取sheet名
public static string GetSheetName(int nIndex)
{
if(workbook!=null)
{
return workbook.GetSheetName(nIndex);
}
return "";
}
读取excel所有行数据
public static List<string> GetSheetData(int nIndex)
{
List<string> result = new List<string>();
sheet = workbook.GetSheetAt(nIndex);
int rowNum = sheet.LastRowNum;
int cellNumm = 0 ;
cellNumm = sheet.GetRow(0).LastCellNum;
string ss = "";
for (int k=0;k< rowNum;k++)
{
ss = "";
if (k==0)
{
}
else
{
for(int j=0;j< cellNumm;j++)
{
rowHeader = sheet.GetRow(k);
if (rowHeader.GetCell(0).StringCellValue == "")
break;
ss+= rowHeader.GetCell(j).StringCellValue + " ";
}
}
result.Add(ss);
}
return result;
}
创建表格
public static void CreatExcel()
{
workbook = new HSSFWorkbook();
if (workbook.NumberOfSheets > 0)
sheet = workbook.GetSheetAt(0);
else
sheet = workbook.CreateSheet("data");
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
sheet.SetColumnWidth(0, 30 * 256);
sheet.SetColumnWidth(1, 30 * 256);
sheet.SetColumnWidth(2, 30 * 256);
sheet.SetColumnWidth(3, 30 * 256);
sheet.SetColumnWidth(4, 30 * 256);
sheet.SetColumnWidth(5, 30 * 256);
sheet.SetColumnWidth(6, 30 * 256);
sheet.SetColumnWidth(7, 30 * 256);
rowHeader = sheet.CreateRow(0);
rowHeader.Height = 20 * 25;
rowHeader.CreateCell(0, CellType.String).SetCellValue("项目号");
rowHeader.CreateCell(1, CellType.String).SetCellValue("数量");
rowHeader.CreateCell(2, CellType.String).SetCellValue("XXX");
rowHeader.CreateCell(3, CellType.String).SetCellValue("XXX");
rowHeader.CreateCell(4, CellType.String).SetCellValue("XXX");
rowHeader.CreateCell(5, CellType.String).SetCellValue("XXX");
rowHeader.CreateCell(6, CellType.String).SetCellValue("XXX");
rowHeader.CreateCell(7, CellType.String).SetCellValue("XXX");
for (int k = 0; k < 8; k++)
{
rowHeader.GetCell(k).CellStyle = style;
}
}
写入数据
public static void WriteExcel(int rowNum, string sn, string ngItem)
{
IRow row = sheet.CreateRow(rowNum);
row.Height = 40 * 20;
row.CreateCell(0, CellType.String).SetCellValue(sn);
}
数据保存
public static void ExportExcel(string excelPath)
{
fs = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.Write);
workbook.Write(fs);
workbook.Close();
fs.Close();
}