POI操作excel文件

1、maven依赖

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>

poi操作xls文件，poi-ooxml操作xlsx文件

2、读xlsx文件

public void readXlsx(String path) {
XSSFWorkbook book = null;
try {
book = new XSSFWorkbook(new FileInputStream(path));
XSSFSheet sheet = book.getSheetAt(0);
int rows = sheet.getLastRowNum();
if (rows < 1) {
book.close();
} else {
//row=1是从第二行开始，第一行第一列默认的位置默认都是0
for (int row = 1; row <= rows; row++) {
XSSFRow frow = sheet.getRow(row);
System.out.println(this.getXlsxValue(frow.getCell(row-1)));//读取每列数据
}
}
book.close();
} catch (Exception e) {
e.printStackTrace();
}
}

public String getXlsxValue(XSSFCell cell) {
if (cell == null) {
return null;
}
String cellValue = "";
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_NUMERIC:// 数字
DecimalFormat df = new DecimalFormat("0");
cellValue = df.format(cell.getNumericCellValue());
break;
case XSSFCell.CELL_TYPE_STRING: // 字符串
cellValue = cell.getStringCellValue().trim();
break;
case XSSFCell.CELL_TYPE_BOOLEAN: // 布尔
cellValue = cell.getBooleanCellValue() + "";
break;
case XSSFCell.CELL_TYPE_FORMULA: // 公式
cellValue = cell.getCellFormula();
break;
case XSSFCell.CELL_TYPE_BLANK: // 空值
cellValue = null;
break;
case XSSFCell.CELL_TYPE_ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}

写入xls文件示例

public void writeXls(String path,String sheetName,String[] headCell,List<User> list) throws IOException{
HSSFWorkbook book = new HSSFWorkbook();
HSSFSheet sheet = book.createSheet(sheetName);
// 插入第一行，列头
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < headCell.length; i++) {
row.createCell(i).setCellValue(headCell[i]);
}
//插入内容
for (int i = 0; i < list.size(); i++) {
User user=list.get(i);
row = sheet.createRow(i + 1);
row.createCell(0).setCellValue(user.getName());
row.createCell(1).setCellValue(user.getPassword());
}


File file = new File(path);
if (!file.exists()) {
file.createNewFile();
}
FileOutputStream os = new FileOutputStream(file);
book.write(os);
os.close();
book.close();
}

3、读xls文件

public void readXls(String path) {
HSSFWorkbook book = null;
try {
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(path));
book = new HSSFWorkbook(fs);
HSSFSheet sheet = book.getSheetAt(0);
int rows = sheet.getLastRowNum();
if (rows < 1) {
book.close();
} else {
//row=1是从第二行开始，第一行第一列默认的位置默认都是0
for (int row = 1; row <= rows; row++) {
HSSFRow frow = sheet.getRow(row);
System.out.println(this.getXlsValue(frow.getCell(row-1)));//读取每列数据
}
}
book.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

public static String getXlsValue(HSSFCell cell) {
if (cell == null) {
return null;
}
String cellValue = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:// 数字
DecimalFormat df = new DecimalFormat("0");
cellValue = df.format(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
cellValue = cell.getStringCellValue().trim();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // 布尔
cellValue = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
cellValue = cell.getCellFormula();
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
cellValue = null;
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}

写入xlsx文件示例

public void writeXlsx(String path,String sheetName,String[] headCell,List<User> list) throws IOException{
XSSFWorkbook book = new XSSFWorkbook();
XSSFSheet sheet = book.createSheet(sheetName);
// 插入第一行，列头
XSSFRow row = sheet.createRow(0);
for (int i = 0; i < headCell.length; i++) {
row.createCell(i).setCellValue(headCell[i]);
}
//插入内容
for (int i = 0; i < list.size(); i++) {
User user=list.get(i);
row = sheet.createRow(i + 1);
row.createCell(0).setCellValue(user.getName());
row.createCell(1).setCellValue(user.getPassword());
}


File file = new File(path);
if (!file.exists()) {
file.createNewFile();
}
FileOutputStream os = new FileOutputStream(file);
book.write(os);
os.close();
book.close();
}