java 处理表格工具POI（包括读写Excel、建立多个sheet）

74 阅读 0 评论 49 点赞

我是靠谱客的博主羞涩唇膏，最近开发中收集的这篇文章主要介绍java 处理表格工具POI（包括读写Excel、建立多个sheet），觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

没有废话，直接上代码。

1.相关jar包。

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

2.实例程序整体结构

ExcelDataVO：实体类；

PoiExcelRead：读取本地Excel数据；

PoiExcelWrite：数据写入新建Excel；

PoiExcelExport：导出新建Excel；

PoiExcel：测试类。

package com.springboot.util;
import java.math.BigDecimal;
/**
* @Date : 2020/7/8 16:00
* @Description :
*/
public class ExcelDataVO {
private String name;
private String remake;
private long amount;
private long lastAmount;
private float percent;
private float propertyRate;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getRemake() {
return remake;
}
public void setRemake(String remake) {
this.remake = remake;
}
public long getAmount() {
return amount;
}
public void setAmount(long amount) {
this.amount = amount;
}
public long getLastAmount() {
return lastAmount;
}
public void setLastAmount(long lastAmount) {
this.lastAmount = lastAmount;
}
public float getPercent() {
return percent;
}
public void setPercent(float percent) {
this.percent = percent;
}
public float getPropertyRate() {
return propertyRate;
}
public void setPropertyRate(float propertyRate) {
this.propertyRate = propertyRate;
}
}

package com.springboot.util;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;
/**
* @Date : 2020/7/8 14:27
* @Description :
*/
public class PoiExcelRead {
private static Logger logger = Logger.getLogger(PoiExcelRead.class.getName()); // 日志打印类
private static final String XLS = "xls";
private static final String XLSX = "xlsx";
/**
* 根据文件后缀名类型获取对应的工作簿对象
*
* @param inputStream 读取文件的输入流
* @param fileType
文件后缀名类型（xls或xlsx）
* @return 包含文件数据的工作簿对象
* @throws IOException
*/
public static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
Workbook workbook = null;
if (fileType.equalsIgnoreCase(XLS)) {
workbook = new HSSFWorkbook(inputStream);
} else if (fileType.equalsIgnoreCase(XLSX)) {
workbook = new XSSFWorkbook(inputStream);
}
return workbook;
}
/**
* 读取Excel文件内容
*
* @param fileName 要读取的Excel文件所在路径
* @return 读取结果列表，读取失败时返回null
*/
public static List<ExcelDataVO> readExcel(String fileName) {
Workbook workbook = null;
FileInputStream inputStream = null;
try {
// 获取Excel后缀名
String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
// 获取Excel文件
File excelFile = new File(fileName);
if (!excelFile.exists()) {
logger.warning("指定的Excel文件不存在！");
return null;
}
// 获取Excel工作簿
inputStream = new FileInputStream(excelFile);
workbook = getWorkbook(inputStream, fileType);
// 读取excel中的数据
List<ExcelDataVO> resultDataList = parseExcel(workbook);
return resultDataList;
} catch (Exception e) {
logger.warning("解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage());
return null;
} finally {
try {
if (null != workbook) {
workbook.close();
}
if (null != inputStream) {
inputStream.close();
}
} catch (Exception e) {
logger.warning("关闭数据流出错！错误信息：" + e.getMessage());
return null;
}
}
}
/**
* 解析Excel数据
*
* @param workbook Excel工作簿对象
* @return 解析结果
*/
private static List<ExcelDataVO> parseExcel(Workbook workbook) {
List<ExcelDataVO> resultDataList = new ArrayList<>();
// 解析sheet
for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
Sheet sheet = workbook.getSheetAt(sheetNum);
// 校验sheet是否合法
if (sheet == null) {
continue;
}
// 获取第一行数据
int firstRowNum = sheet.getFirstRowNum();
Row firstRow = sheet.getRow(firstRowNum);
if (null == firstRow) {
logger.warning("解析Excel失败，在第一行没有读取到任何数据！");
}
// 解析每一行的数据，构造数据对象
int rowStart = firstRowNum + 1;
int rowEnd = sheet.getPhysicalNumberOfRows();
for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
Row row = sheet.getRow(rowNum);
if (null == row) {
continue;
}
ExcelDataVO resultData = convertRowToData(row);
if (null == resultData) {
logger.warning("第 " + row.getRowNum() + "行数据不合法，已忽略！");
continue;
}
resultDataList.add(resultData);
}
ExcelDataVO flagData = new ExcelDataVO();
flagData.setRemake("这是分隔符");
flagData.setAmount(0);
flagData.setLastAmount(0);
flagData.setPercent(0);
switch (sheetNum) {
case 0:
flagData.setName("sheet1");
break;
case 1:
flagData.setName("sheet2");
break;
case 2:
flagData.setName("sheet3");
break;
default:
flagData.setName("--------");
break;
}
resultDataList.add(flagData);
}
return resultDataList;
}
/**
* 将单元格内容转换为字符串
*
* @param cell
* @return
*/
private static String convertCellValueToString(Cell cell) {
if (cell == null) {
return null;
}
String returnValue = null;
switch (cell.getCellTypeEnum()) {
case NUMERIC:
//数字
Double doubleValue = cell.getNumericCellValue();
// 格式化科学计数法，取一位整数
DecimalFormat df = new DecimalFormat("0");
returnValue = df.format(doubleValue);
break;
case STRING:
//字符串
returnValue = cell.getStringCellValue();
break;
case BOOLEAN:
//布尔
Boolean booleanValue = cell.getBooleanCellValue();
returnValue = booleanValue.toString();
break;
case BLANK:
// 空值
break;
case FORMULA:
// 公式
returnValue = cell.getCellFormula();
break;
case ERROR:
// 故障
break;
default:
break;
}
return returnValue;
}
/**
* 提取每一行中需要的数据，构造成为一个结果数据对象
* <p>
* 当该行中有单元格的数据为空或不合法时，忽略该行的数据
*
* @param row 行数据
* @return 解析后的行数据对象，行数据错误时返回null
*/
private static ExcelDataVO convertRowToData(Row row) {
ExcelDataVO resultData = new ExcelDataVO();
Cell cell;
int cellNum = 0;
cell = row.getCell(cellNum++);
String name = convertCellValueToString(cell);
resultData.setName(name);
cell = row.getCell(cellNum++);
String remakeStr = convertCellValueToString(cell);
resultData.setRemake(remakeStr);
cell = row.getCell(cellNum++);
String amount = convertCellValueToString(cell);
if (amount == null || "".equals(amount)) {
resultData.setAmount(0);
} else {
resultData.setAmount(Long.valueOf(amount));
}
cell = row.getCell(cellNum++);
String lastAmount = convertCellValueToString(cell);
if (lastAmount == null || "".equals(lastAmount)) {
resultData.setLastAmount(0);
} else {
long newAmount = 0;
if (amount != null) {
newAmount = Long.valueOf(amount);
}
long newLastAmount = Long.valueOf(lastAmount);
Double percent = (newAmount - newLastAmount) / (double) (newLastAmount);
resultData.setLastAmount(newLastAmount);
resultData.setPercent(Float.valueOf(String.valueOf(percent)));
}
return resultData;
}
}

package com.springboot.util;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
/**
* @Date : 2020/7/8 17:01
* @Description :
*/
public class PoiExcelWrite {
private static List<String> CELL_HEADS; //列头
static {
// 类装载时就载入指定好的列头信息，如有需要，可以考虑做成动态生成的列头
CELL_HEADS = new ArrayList<>();
}
/**
* 生成Excel并写入数据信息
*
* @param dataList 数据列表
* @return 写入数据后的工作簿对象
*/
public static Workbook exportData(String date, List<ExcelDataVO> dataList) {
int sheetNum = 0;
List<String> sheetName = new ArrayList<>();
CELL_HEADS.add("name");
CELL_HEADS.add("remake");
CELL_HEADS.add(date);
CELL_HEADS.add(String.valueOf(Integer.valueOf(date) - 1));
CELL_HEADS.add("floatRate");
CELL_HEADS.add("rate");
// 生成xlsx的Excel
//Workbook workbook = new SXSSFWorkbook();
// 如需生成xls的Excel，请使用下面的工作簿对象，注意后续输出时文件后缀名也需更改为xls
Workbook workbook = new HSSFWorkbook();
for (ExcelDataVO excelDataVO : dataList) {
if (excelDataVO.getRemake() != null && excelDataVO.getRemake().equals("这是分隔符")) {
sheetName.add(excelDataVO.getName());
sheetNum++;
}
}
for (int i = 0; i < sheetNum; i++) {
int m = 0;
int n = 0;
int p = 0;
List<ExcelDataVO> sheetData = new ArrayList<>();
long totalProperty = 0;
// 生成Sheet表，写入第一行的列头
Sheet sheet = buildDataSheet(workbook);
//设置sheetName
workbook.setSheetName(i, sheetName.get(i));
//获取每个sheet数据
for (int j = 0; j < dataList.size(); j++) {
if (dataList.get(j).getName().equals("sheet1")) {
m = j;
}
if (dataList.get(j).getName().equals("sheet2")) {
n = j;
}
if (dataList.get(j).getName().equals("sheet3")) {
p = j;
}
if (dataList.get(j).getName().equals("金额")) {
totalProperty = dataList.get(j).getAmount();
}
}
switch (i) {
case 0:
sheetData = dataList.subList(0, m);
break;
case 1:
sheetData = dataList.subList(m + 1, n);
break;
case 2:
sheetData = dataList.subList(n + 1, p);
break;
default:
sheetData = dataList;
break;
}
//构建每行的数据内容
int rowNum = 1;
for (Iterator<ExcelDataVO> it = sheetData.iterator(); it.hasNext(); ) {
ExcelDataVO data = it.next();
if (data == null) {
continue;
}
//输出行数据
Row row = sheet.createRow(rowNum++);
convertDataToRow(data, row, workbook, totalProperty);
}
}
return workbook;
}
/**
* 生成sheet表，并写入第一行数据（列头）
*
* @param workbook 工作簿对象
* @return 已经写入列头的Sheet
*/
private static Sheet buildDataSheet(Workbook workbook) {
Sheet sheet = workbook.createSheet();
// 设置列头宽度
for (int i = 0; i < CELL_HEADS.size(); i++) {
sheet.setColumnWidth(i, 4000);
}
// 设置默认行高
sheet.setDefaultRowHeight((short) 400);
// 构建头单元格样式
CellStyle cellStyle = buildHeadCellStyle(sheet.getWorkbook());
// 写入第一行各列的数据
Row head = sheet.createRow(0);
for (int i = 0; i < CELL_HEADS.size(); i++) {
Cell cell = head.createCell(i);
cell.setCellValue(CELL_HEADS.get(i));
cell.setCellStyle(cellStyle);
}
return sheet;
}
/**
* 设置第一行列头的样式
*
* @param workbook 工作簿对象
* @return 单元格样式对象
*/
private static CellStyle buildHeadCellStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
//对齐方式设置
style.setAlignment(HorizontalAlignment.CENTER);
//边框颜色和宽度设置
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框
//设置背景颜色
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//粗体字设置
Font font = workbook.createFont();
font.setBold(true);
style.setFont(font);
return style;
}
/**
* 将数据转换成行
*
* @param data 源数据
* @param row
行对象
* @return
*/
private static void convertDataToRow(ExcelDataVO data, Row row, Workbook workbook,long totalProperty) {
int cellNum = 0;
Cell cell;
cell = row.createCell(cellNum++);
cell.setCellValue(null == data.getName() ? "" : data.getName());
cell = row.createCell(cellNum++);
cell.setCellValue(null == data.getRemake() ? "" : data.getRemake());
cell = row.createCell(cellNum++);
if (!"".equals(data.getAmount())) {
cell.setCellValue(data.getAmount());
} else {
cell.setCellValue("");
}
cell = row.createCell(cellNum++);
if (!"".equals(data.getLastAmount())) {
cell.setCellValue(data.getLastAmount());
} else {
cell.setCellValue("");
}
cell = row.createCell(cellNum++);
if (!"".equals(data.getPercent())) {
if (data.getPercent() > 0.3 || data.getPercent() < -0.3) {
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.RED.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(style);
}
cell.setCellValue(data.getPercent());
} else {
cell.setCellValue("");
}
cell = row.createCell(cellNum++);
float rate = 0;
if (!"".equals(data.getAmount())) {
rate = Float.valueOf(data.getAmount())/totalProperty;
if (rate > 0.03){
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(style);
}
cell.setCellValue(rate);
}else{
cell.setCellValue("");
}
}
}

package com.springboot.util;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.logging.Logger;
/**
* @Date : 2020/7/8 18:17
* @Description :
*/
public class PoiExcelExport {
private static Logger logger = Logger.getLogger(PoiExcelExport.class.getName()); // 日志打印类
public static void exportExcel(String filePath, String date, List<ExcelDataVO> readResult) {
Workbook workbook = PoiExcelWrite.exportData(date,readResult);
// 以文件的形式输出工作簿对象
FileOutputStream fileOut = null;
try {
String exportFilePath = filePath+date+"新建表格.xls";
File exportFile = new File(exportFilePath);
if (exportFile.exists()) {
exportFile.delete();
}
exportFile.createNewFile();
fileOut = new FileOutputStream(exportFilePath);
workbook.write(fileOut);
fileOut.flush();
} catch (Exception e) {
logger.warning("输出Excel时发生错误，错误原因：" + e.getMessage());
} finally {
try {
if (null != fileOut) {
fileOut.close();
}
if (null != workbook) {
workbook.close();
}
} catch (IOException e) {
logger.warning("关闭输出流时发生错误，错误原因：" + e.getMessage());
}
}
}
}

package com.springboot.course01;
import com.springboot.util.ExcelDataVO;
import com.springboot.util.PoiExcelExport;
import com.springboot.util.PoiExcelRead;
import com.springboot.util.PoiExcelWrite;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.jupiter.api.Test;
import java.io.File;
import java.io.FileOutputStream;
import java.util.List;
/**
* @Date : 2020/7/8 16:16
* @Description :
*/
public class PoiExcel {
@Test
public void test() {
//filePath 本地文件路径，不带文件名
String filePath = "";
String fileName = "2019Excel源文件.xls";
String date = fileName.split("Excel")[0];
List<ExcelDataVO> readResult = PoiExcelRead.readExcel(filePath+fileName);
PoiExcelExport.exportExcel(filePath,date,readResult);
}
}

有疑问请留言，欢迎指正。