在工作中经常会遇到操作excel的需求，对于格式简单、统一的excel可以选择EasyExcel来实现功能，很简单方便；而对于复杂的excel文件，如有各式各样的合并单元格、表头不固定、行列跟随业务数据动态变化……格式变幻多端的文件，easyExcel就显得无能为力了，选择poi就可以解决此问题了。
这里贴上在工作中解决问题时，自己封装的工具类，方便大家查阅：
目录
一、读取、写入Excel工具类
注：poi版本为org.apache.poi:4.1.0
/**
* 工具类-使用poi读取Excel
*/
@Slf4j
public class ExcelUtilByPOI {
private static final String EXCEL_XLS = "xls";
private static final String EXCEL_XLSX = "xlsx";
/**
* 读取excel文件中的全部表格
* 描述：适用于没有单元格合并的excel，并且 (titleOfRow,titleOfColumn)位置为读取内容的起始位置的情况
* 每一行构成一个map，key值是列标题，value是列值。没有值的单元格其value值为null
* 返回结果最外层list对应excel文件，第二层Iterable对应sheet页，第三层map对应sheet页中一行
*
* @param filePath 文件路径
* @param sheetCnt 读取的文件中前sheetCnt个sheet数据。如果值为-1，则读取所有的sheet，否则，读取前sheetCnt个sheet的数据。
* @return
* @throws Exception
*/
public static List<List<Map<String, Object>>> readExcelWithFiexPos(String filePath, int titleInRow, int titleInColumn, int sheetCnt) throws IOException {
Workbook wb = null;
try {
List<List<Map<String, Object>>> excelData = new ArrayList<>();
if (filePath.endsWith(EXCEL_XLS) || filePath.endsWith(EXCEL_XLSX)) {
File file = new File(filePath);
wb = getWorkbook(file);
int sheetSize = sheetCnt == -1 ? wb.getNumberOfSheets() : sheetCnt;
//遍历sheet
for (int i = 0; i < sheetSize; i++) {
Sheet sheet = wb.getSheetAt(i);
List<Map<String, Object>> theSheetData = readSheet(sheet, titleInRow, titleInColumn);
excelData.add(theSheetData);
}
} else {
log.error("读取的不是Excel文件");
}
return excelData;
} catch (FileNotFoundException e) {
throw e;
} finally {
if (wb != null) {
wb.close();
}
}
}
/**
* 读取excel文件中的全部表格
* 描述：适用于没有单元格合并的excel，并且 以fiexedValue在sheet中的位置为读取内容起始位置的情况
* 每一行构成一个map，key值是列标题，value是列值。没有值的单元格其value值为null
* 返回结果最外层list对应excel文件，第二层Iterable对应sheet页，第三层map对应sheet页中一行
*
* @param fixedValue 固定值（第一个列标题）
* @param filePath 文件路径
* @param sheetCnt 读取的文件中前sheetCnt个sheet数据。如果值为-1，则读取所有的sheet，否则，读取前sheetCnt个sheet的数据。
* @return
* @throws Exception
*/
public static List<List<Map<String, Object>>> readExcelWithFiexedTitle(String filePath, String fixedValue, int sheetCnt) throws IOException {
Workbook wb = null;
try {
List<List<Map<String, Object>>> excelData = new ArrayList<>();
if (filePath.endsWith(EXCEL_XLS) || filePath.endsWith(EXCEL_XLSX)) {
File file = new File(filePath);
wb = getWorkbook(file);
int sheetSize = sheetCnt == -1 ? wb.getNumberOfSheets() : sheetCnt;
//遍历sheet
for (int i = 0; i < sheetSize; i++) {
Sheet sheet = wb.getSheetAt(i);
List<Integer> posForSheet = readPosForValue(sheet, fixedValue);
List<Map<String, Object>> theSheetData = readSheet(sheet, posForSheet.get(0), posForSheet.get(1));
excelData.add(theSheetData);
}
} else {
log.error("读取的不是Excel文件");
}
return excelData;
} catch (FileNotFoundException e) {
throw e;
} finally {
if (wb != null) {
wb.close();
}
}
}
/**
* 读取excel文件个sheet第rowNum行的内容，从firstColNum列开始往后读取到第lastColNum列
*
* @param filePath
* @param rowNum
* @param firstColNum
* @param lastColNum
* @return
*/
public static List<List<Object>> readRowData(String filePath, int rowNum, int firstColNum, int lastColNum) throws IOException {
List<List<Object>> dataList = new ArrayList<>();
File file = new File(filePath);
Workbook wb = getWorkbook(file);
int sheetCnt = wb.getNumberOfSheets();
for (int cnt = 0; cnt < sheetCnt; cnt++) { //遍历每一个sheet
Sheet sheet = wb.getSheetAt(cnt);
Row row = sheet.getRow(rowNum);
lastColNum = lastColNum == -1 ? row.getLastCellNum() : lastColNum; //如果没有lastColNum则为其值为最后一列
List<Object> sheetContentList = new ArrayList<>();
Cell cell = null;
String value = null;
for (int i = firstColNum; i < lastColNum; i++) { //读取指定行的内容
cell = row.getCell(i);
value = readCellByType(cell);
sheetContentList.add(value);
}
dataList.add(sheetContentList);
}
return dataList;
}
/**
* 读取给定sheet的内容
* 描述：
* 读取excel文件中的指定名称的表格 用于没有单元格合并的表格，且 (titleOfRow,titleOfColumn)位置为读取内容的起始位置的情况
* 每一行构成一个map(key值是列标题，value是列值)。没有值的单元格其value值为null。
* 返回结果最外层的list对应一个sheet页，第二层的map对应sheet页中的一行。
*
* @param sheet
* @return
*/
private static List<Map<String, Object>> readSheet(Sheet sheet, int titleInRow, int titleInColumn) {
List<Map<String, Object>> sheetList = null;
sheetList = new ArrayList<Map<String, Object>>();
List<String> titles = new ArrayList<>();
int rowSize = sheet.getLastRowNum() + 1;
for (int i = titleInRow; i < rowSize; i++) {
Row row = sheet.getRow(i);
if (row == null)
continue;
;//略过空行
int cellSize = row.getLastCellNum();
if (i == titleInRow) //标题行
{
for (int j = titleInColumn; j < cellSize; j++) {
Cell cell = row.getCell(j);
if (cell != null) {
titles.add(cell.toString());
}
}
} else { //对应每一行的数据
Map<String, Object> rowDataMap = new LinkedHashMap<>();
for (int j = titleInColumn; j < titleInColumn + titles.size(); j++) {
Cell cell = row.getCell(j);
String value = null;
CellType cellType = null;
if (cell == null) {
continue;
}
cellType = cell.getCellTypeEnum();
switch (cellType) {
case STRING:
// value = cell.getRichStringCellValue().getString();
value = cell.getStringCellValue();
break;
case NUMERIC: //包含日期和普通数字
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
value = df.format(date);
} else {
double cellValue = cell.getNumericCellValue();
value = String.valueOf(cellValue);
// 下面的代码，会自动舍弃读取单元格中显示的值（可能是做了round（）之后的结果），不是单元格中最原本的值
/*NumberFormat nf = NumberFormat.getInstance();
String tmpValue = nf.format(cell.getNumericCellValue());
if (tmpValue.indexOf(",") >= 0) {
tmpValue = tmpValue.replaceAll(",", "");
}
value = tmpValue;*/
}
break;
case FORMULA:
// cell.getCellFormula();
cell.setCellType(CellType.STRING);
value = cell.getStringCellValue();
break;
case BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
default:
if (cell != null) {
value = cell.toString();
}
}
String key = titles.get(j - titleInColumn);
rowDataMap.put(key, value);
}
sheetList.add(rowDataMap);
}
}
return sheetList;
}
/**
* 读取sheet中指定值的位置
*
* @param sheet
* @return
*/
private static List<Integer> readPosForValue(Sheet sheet, String fixedValue) {
List<Integer> posList = new ArrayList();
Object value = null;
for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) { // 获取每行
XSSFRow row = (XSSFRow) sheet.getRow(j);
if (row != null) {
for (int k = 0; k < sheet.getRow(0).getPhysicalNumberOfCells(); k++) { // 获取每个单元格
Cell cell = row.getCell(k);
if (cell == null) {
continue;
}
CellType cellTypeEnum = cell.getCellTypeEnum();
switch (cellTypeEnum) {
case STRING:
value = cell.getRichStringCellValue().getString();
break;
// case Cell.CELL_TYPE_NUMERIC:
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
value = cell.getDateCellValue();
} else {
double cellValue = cell.getNumericCellValue();
value = String.valueOf(cellValue);
/* // 下面的代码，会自动舍弃读取单元格中显示的值（可能是做了round（）之后的结果），不是单元格中最原本的值
NumberFormat nf = NumberFormat.getInstance();
String tmpValue = nf.format(cell.getNumericCellValue());
if (tmpValue.indexOf(",") >= 0) {
tmpValue = tmpValue.replaceAll(",", "");
}
value = tmpValue;*/
}
break;
case BOOLEAN:
value = cell.getBooleanCellValue();
break;
case FORMULA:
value = cell.getCellFormula();
break;
default:
value = "";
break;
}
if (fixedValue.equals(value.toString())) {
posList.add(cell.getRowIndex());
posList.add(cell.getColumnIndex());
break;
}
}
}
if (!BeanUtil.isEmpty(posList)) {
break;
}
}
return posList;
}
/**
* 根据excel的版本，获取相应的Workbook
*
* @param file
* @return
*/
public static Workbook getWorkbook(File file) throws IOException {
Workbook wb = null;
InputStream fis = new FileInputStream(file);
if (file.getName().endsWith(EXCEL_XLS)) //2003
{
wb = new HSSFWorkbook(fis);
} else if (file.getName().endsWith(EXCEL_XLSX)) {
wb = new XSSFWorkbook(fis);//2007 2010
}
if (fis != null) {
fis.close();
}
return wb;
}
/**
* 判断指定的单元格是否是合并单元格
*
* @param sheet
* @param row 行下标
* @param column 列下标
* @return
*/
public static boolean isMergedRegion(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
return true;
}
}
}
return false;
}
/**
* 获取合并单元格的值
*
* @param sheet
* @param row
* @param column
* @return
*/
public static String getMergedCellValue(Sheet sheet, int row, int column) {
String value = null;
int mergedCellCnt = sheet.getNumMergedRegions();
for (int i = 0; i < mergedCellCnt; i++) {
CellRangeAddress mergedCell = sheet.getMergedRegion(i);
int firstColumn_pos = mergedCell.getFirstColumn();
int lastColumn_pos = mergedCell.getLastColumn();
int firstRow_pos = mergedCell.getFirstRow();
int lastRow_pos = mergedCell.getLastRow();
if (row >= firstRow_pos && row <= lastRow_pos) {
if (column >= firstColumn_pos && column <= lastColumn_pos) {
Row firstRow = sheet.getRow(firstRow_pos);
Cell cell = firstRow.getCell(firstColumn_pos);
if (cell == null) {
continue;
}
CellType cellType = cell.getCellTypeEnum();
switch (cellType) {
case STRING:
// value = cell.getRichStringCellValue().getString();
value = cell.getStringCellValue();
break;
case NUMERIC: //包含日期和普通数字
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
value = df.format(date);
} else {
double cellValue = cell.getNumericCellValue();
value = String.valueOf(cellValue);
/* 下面的代码，会自动舍弃读取单元格中显示的值（可能是做了round（）之后的结果），不是单元格中最原本的值
NumberFormat nf = NumberFormat.getInstance();
String tmpValue = nf.format(cell.getNumericCellValue());
if (tmpValue.indexOf(",") >= 0) {
tmpValue = tmpValue.replaceAll(",", "");
}
value = tmpValue;*/
}
break;
case FORMULA:
// cell.getCellFormula();
cell.setCellType(CellType.STRING);
value = cell.getStringCellValue();
break;
case BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
default:
if (cell != null) {
value = cell.toString();
}
}
return cell == null || value == null ? "" : value;
}
}
}
return null;
}
/**
* 读取单元格的类型
*
* @param cell
* @return
*/
public static String readCellByType(Cell cell) {
if (cell == null) {
return null;
}
CellType cellType = cell.getCellTypeEnum();
String value = null;
switch (cellType) {
case STRING:
// value = cell.getRichStringCellValue().getString();
value = cell.getStringCellValue();
break;
case NUMERIC: //包含日期和普通数字
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
value = df.format(date);
} else {
double cellValue = cell.getNumericCellValue();
value = String.valueOf(cellValue);
/* 下面的代码，会自动舍弃读取单元格中显示的值（可能是做了round（）之后的结果），不是单元格中最原本的值
NumberFormat nf = NumberFormat.getInstance();
String tmpValue = nf.format(cell.getNumericCellValue());
if (tmpValue.indexOf(",") >= 0) {
tmpValue = tmpValue.replaceAll(",", "");
}
value = tmpValue;*/
}
break;
case FORMULA:
// cell.getCellFormula();
cell.setCellType(CellType.STRING);
value = cell.getStringCellValue();
break;
case BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
default:
if (cell != null) {
value = cell.toString();
}
}
return cell == null || value == null ? "" : value;
}
/**
* 读取一个excel文件中所有的sheet名字列表
*
* @param filePath 文件路径
* @return
* @throws Exception
*/
public static List<String> readSheetNames(String filePath) throws Exception {
Workbook wb = null;
List<String> sheetNames = new ArrayList<String>();
try {
if (filePath.endsWith(EXCEL_XLS) || filePath.endsWith(EXCEL_XLSX)) {
// 读取Excel文档
File file = new File(filePath);
wb = getWorkbook(file);
int sheetSize = wb.getNumberOfSheets();
// 遍历sheet页
for (int i = 0; i < sheetSize; i++) {
Sheet sheet = wb.getSheetAt(i);
sheetNames.add(sheet.getSheetName());
}
} else {
throw new BaseException(EPowerResultCode.JUDGE_EXCEL_FILE);
}
return sheetNames;
} catch (FileNotFoundException e) {
log.error(e.getMessage());
throw e;
} finally {
if (wb != null) {
wb.close();
}
}
}
/**
* 读取excel中所有sheet的标题
*
* @return
*/
public static List<String> readSheetTitles(String filePath) throws Exception {
Workbook wb = null;
List<String> titleList = new ArrayList<String>();
try {
if (filePath.endsWith(EXCEL_XLS) || filePath.endsWith(EXCEL_XLSX)) {
// 读取Excel文档
File file = new File(filePath);
wb = getWorkbook(file);
int sheetSize = wb.getNumberOfSheets();
for (int i = 0; i < sheetSize; i++) {
Sheet sheet = wb.getSheetAt(i);
Row row = sheet.getRow(0);//默认第一行为表头
short lastCellNum = row.getLastCellNum();//共有多少列
for (int j = 0; j < lastCellNum; j++) {
Cell cell = row.getCell(j);
titleList.add(cell.getStringCellValue().trim());
}
}
}
return titleList;
} catch (Exception e) {
log.error(e.getMessage());
throw e;
} finally {
if (wb != null) {
wb.close();
}
}
}
/**
* 导出文件
* @param request
* @param response
* @param wb
* @param fileName
*/
public static void exportFile(HttpServletRequest request, HttpServletResponse response, Workbook wb, String fileName) throws Exception{
String userAgent = request.getHeader("USER-AGENT");
if (userAgent.contains("MSIE")) {// IE浏览器
fileName = URLEncoder.encode(fileName, "UTF8");
} else if (userAgent.contains("Mozilla")) {// google,火狐浏览器
fileName = new String(fileName.getBytes(), "ISO8859-1");
} else {
fileName = URLEncoder.encode(fileName, "UTF8");// 其他浏览器
}
response.setContentType("application/octet-stream;charset=UTF-8");
response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
OutputStream outputStream = response.getOutputStream();
wb.write(outputStream);
}
}二、Excel样式设置工具类
/**
* 导出的Excel单元格样式设置
*/
public class ExcelStyleUtil {
/**
* 表头字体
*
* @return
*/
public static Font headerFoot(Workbook wb) {
Font headerFont = wb.createFont();
headerFont.setFontName("微软雅黑");
headerFont.setFontHeightInPoints((short) 13);
headerFont.setBold(true);
// headerFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
headerFont.setColor(IndexedColors.BLACK.getIndex());
return headerFont;
}
/**
* 正文字体
*
* @param wb
* @return
*/
public static Font contextFont(Workbook wb) {
Font contextFont = wb.createFont();
contextFont.setFontName("微软雅黑");
contextFont.setFontHeightInPoints((short) 13);
contextFont.setBold(false);
contextFont.setColor(IndexedColors.BLACK.getIndex());
// contextFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
return contextFont;
}
/**
* 表头样式-左右上下居中
*
* @param wb
* @return
*/
public static CellStyle headerStyle(Workbook wb) {
CellStyle headerStyle = wb.createCellStyle();
Font font = headerFoot(wb);
headerStyle.setFont(font);
headerStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
headerStyle.setLocked(true);//锁定
headerStyle.setWrapText(false);// 自动换行
headerStyle.setBorderBottom(BorderStyle.THIN);//下边框
headerStyle.setBorderTop(BorderStyle.THIN);//上边框
headerStyle.setBorderLeft(BorderStyle.THIN);//左
headerStyle.setBorderRight(BorderStyle.THIN);//右
return headerStyle;
}
/**
* 单元格样式 - 水平、垂直居中
*
* @param wb
* @return
*/
public static CellStyle contextAlignCenterStyle(Workbook wb) {
CellStyle style = wb.createCellStyle();
Font font = contextFont(wb);
style.setFont(font);
style.setAlignment(HorizontalAlignment.CENTER);//水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
style.setLocked(true);
style.setWrapText(true);// 自动换行
style.setBorderBottom(BorderStyle.THIN);//下边框
style.setBorderTop(BorderStyle.THIN);//上边框
style.setBorderLeft(BorderStyle.THIN);//左
style.setBorderRight(BorderStyle.THIN);//右
return style;
}
/**
* 单元格样式-无左边框
* @param wb
* @return
*/
public static CellStyle contextNoLeftBorder(Workbook wb){
CellStyle style = wb.createCellStyle();
Font font = contextFont(wb);
style.setFont(font);
style.setAlignment(HorizontalAlignment.CENTER);//水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
style.setLocked(true);
style.setWrapText(true);// 自动换行
style.setBorderBottom(BorderStyle.THIN);//下边框
style.setBorderTop(BorderStyle.THIN);//上边框
style.setBorderRight(BorderStyle.THIN);//右
return style;
}
/**
* 单元格样式-无左右边框
* @param wb
* @return
*/
public static CellStyle contextNoLeftRightBorder(Workbook wb){
CellStyle style = wb.createCellStyle();
Font font = contextFont(wb);
style.setFont(font);
style.setAlignment(HorizontalAlignment.CENTER);//水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
style.setLocked(true);
style.setWrapText(true);// 自动换行
style.setBorderBottom(BorderStyle.THIN);//下边框
style.setBorderTop(BorderStyle.THIN);//上边框
return style;
}
}三、写入Excel简单示例
public void testWriteExcel(HttpServletRequest request, HttpServletResponse response){
//1.创建book
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("sheet名称，可为空");
//2.创建sheet
sheet.createFreezePane(1, 2, 0, 0);//冻结首列和前两行
CellStyle headerStyle = ExcelStyleUtil.headerStyle(wb);
//3.创建行
Row row0 = sheet.createRow(0);
//4.创建单元格
Cell cell00 = row0.createCell(0);
cell00.setCellStyle(headerStyle);//设置单元格样式
cell00.setCellValue("单元格值");
CellRangeAddress mergeReion = new CellRangeAddress(0, 0, 0, 2);
sheet.addMergedRegion(mergeReion);//合并首行的前两个单元格
//（1）第二行设置表头
Row row1 = sheet.createRow(1);
for (int i = 0; i < titles.size(); i++) {
Object title = titles.get(i);
Cell cell1i = row1.createCell(i);
cell1i.setCellValue(String.valueOf(title));
cell1i.setCellStyle(headerStyle);
}
//（2）下面设置表体数据：创建10行3列的表体
CellStyle contextStyle = ExcelStyleUtil.contextAlignCenterStyle(wb);//内容样式
for (int i = 0; i < 10; i++) {
Row rowi = sheet.createRow(i + 2);
for(int j=0;j<3;j++){
Cell cellij = rowi.createCell(j);
sheet.setColumnWidth(j,5000);//设置列宽
cellij.setCellStyle(contextStyle);
}
}
//（3）导出到浏览器
try{
ExcelUtilByPOI.exportFile(request,response,wb,"文件名");//调用工具方法
}catch (Exception e){
throw new BaseException(EPowerResultCode.DOWNLOAD_FILE_FAIL);
}
}