avatar

poi读写Excel简单介绍

Apache POI 可以对Microsoft Office 进行操作, 下面是工作中使用的对Excel进行读写操作的常用方式.

引入依赖

<!-- excel poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>

简单使用

主要介绍读写时, 分别常用到的一些对象及其含义, 方便自己编写util.

package com.liuzhihang.tool.excel.poi;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

/**
* @author liuzhihang
* @date 2018/4/20 16:12
*/
public class ExcelTest {

public static void main(String[] args) throws Exception {

// readerTest();
writerTest();
}

private static void writerTest() throws IOException {

File file = new File("c:Users/liuzhihang/Desktop/test.xlsx");
if (file.exists()) {
System.out.println("读取的文件存在!");
file.delete();
}
file.createNewFile();
// 操作 .xls 的 workbook
Workbook hssfWorkbook = new HSSFWorkbook();
// 操作 .xlsx 的 workbook
XSSFWorkbook xssfWorkbook = new XSSFWorkbook();

// 创建 sheet 页
XSSFSheet sheet = xssfWorkbook.createSheet();
// 创建 0 行 操作对象
Row row0 = sheet.createRow(0);
// 创建单元格并赋值
row0.createCell(0).setCellValue("序号");

OutputStream outputStream = new FileOutputStream(file);
// 写入文件
xssfWorkbook.write(outputStream);
}

private static void readerTest() throws Exception {

File file = new File("c:Users/liuzhihang/Desktop/parkingLotTempLate.xlsx");

Workbook workBook = ExcelUtil.getWorkBook(file);

// 获取 excel 页
// Sheet sheetByIndex = workBook.getSheetAt(0);
// Sheet sheetByName = workBook.getSheet("Sheet0");

// 操作 sheet
Sheet sheet = workBook.getSheetAt(0);
// 获取最后一行行数 从 0 开始
int lastRowNum = sheet.getLastRowNum();
// 获取总行数
int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();

// 操作行 获取第0行
Row row = sheet.getRow(0);
String value = row.getCell(0).getStringCellValue();

}


}

ExcelUtil 简单工具

poi读写 excel 的简单工具 ExcelUtil, 实际工作中可结合javaBean使用并重新编写util.

package com.liuzhihang.tool.excel.poi;

import lombok.extern.log4j.Log4j2;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.List;


/**
* @author liuzhihang
* @date 2018/4/20 12:02
*/
@Log4j2
public class ExcelUtil {

/**
* 读取两列excel 返回第二列的集合
*
* @param workbook
* @return
*/
public static List<String> readExcelForTwoColumns(Workbook workbook) {

if (workbook == null) {
log.info("获取 workbook 为null");
return null;
}

List<String> list = new ArrayList<>();

try {
Sheet sheet = workbook.getSheetAt(0);
//获取总行数
int rowNum = sheet.getLastRowNum();
//正文内容应该从第二行开始,第一行为文件的标头的标题
for (int i = 0; i < rowNum; i++) {
Row row = sheet.getRow(i + 1);
String value = getCellValue(row.getCell(1)).toString();
if (StringUtils.isNotBlank(value)) {
list.add(value);
}
}
} catch (Exception e) {
log.error(e.getMessage());
}
return list;
}

/**
* 写 excel
*
* @param excelFile
* @param list
*/
public static void writerExcelForTwoColumns(File excelFile, List<String> list) {

OutputStream outputStream = null;
try {
outputStream = new FileOutputStream(excelFile);

Workbook workBook = null;
String fileName = excelFile.getName();
if (fileName.endsWith(".xls")) {
workBook = new HSSFWorkbook();
} else if (fileName.endsWith(".xlsx")) {
workBook = new XSSFWorkbook();
} else {
log.info("文件格式不正确!, 当前文件名:{}", fileName);
throw new Exception("文件格式不正确");
}
// 创建第 0 页
Sheet sheet = workBook.createSheet();
Row row1 = sheet.createRow(0);
row1.createCell(0).setCellValue("序号");
row1.createCell(1).setCellValue("编号");

for (int i = 0; i < list.size(); i++) {
Row row = sheet.createRow(i + 1);
row.createCell(0).setCellValue(i + 1);
row.createCell(1).setCellValue(list.get(i));
}

workBook.write(outputStream);
} catch (Exception e) {
log.error("写excel失败", e);
} finally {
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}

/**
* 获取工作表
*
* @param file
* @return
*/
public static Workbook getWorkBook(File file) throws Exception {

String fileName = file.getName();
Workbook workbook = null;

try {
InputStream inputStream = new FileInputStream(file);
if (fileName.endsWith(".xls")) {
workbook = new HSSFWorkbook(inputStream);
} else if (fileName.endsWith(".xlsx")) {
workbook = new XSSFWorkbook(inputStream);
} else {
log.info("文件格式不正确!, 当前文件名:{}", fileName);
throw new Exception("文件格式不正确");
}
} catch (Exception e) {
throw e;
}
return workbook;
}

/**
* 获取单元格的数据
*
* @param cell
* @return
*/
public static Object getCellValue(Cell cell) {

if (cell != null) {
switch (cell.getCellTypeEnum()) {
// 数字
case NUMERIC:
return cell.getNumericCellValue();
// 字符串
case STRING:
return cell.getStringCellValue();
// 公式
case FORMULA:
return cell.getCellFormula();
// 布尔
case BOOLEAN:
return cell.getBooleanCellValue();
case ERROR:
return cell.getErrorCellValue();
// 空
default:
return "";
}
}
return "";
}

}
文章作者: liuzhihang
文章链接: https://liuzhihang.com/2018/02/15/poi-read-and-write-excel-brief-introduction.html
版权声明: 本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 Notes

评论