package cc.mrbird.febs.common.utils.excl;
|
|
import cn.hutool.core.util.ArrayUtil;
|
import org.apache.commons.collections.CollectionUtils;
|
import org.apache.commons.lang.ArrayUtils;
|
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
|
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
|
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.ss.util.CellRangeAddress;
|
import org.apache.poi.ss.util.WorkbookUtil;
|
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
|
import java.io.*;
|
import java.text.DecimalFormat;
|
import java.text.SimpleDateFormat;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.LinkedList;
|
import java.util.List;
|
|
/**
|
* excel工具类 提供读取和写入excel的功能
|
*
|
* @author JIANGYOUYAO
|
* @email 935090232@qq.com
|
* @date 2017年12月20日
|
*/
|
public class ExcelUtil {
|
|
/**
|
* 标题样式
|
*/
|
private final static String STYLE_HEADER = "header";
|
/**
|
* 表头样式
|
*/
|
private final static String STYLE_TITLE = "title";
|
/**
|
* 数据样式
|
*/
|
private final static String STYLE_DATA = "data";
|
|
private static String getFileExtName(File file) {
|
String fileName = file.getName();
|
String prefix = fileName.substring(fileName.lastIndexOf(".") );
|
return prefix;
|
}
|
|
/**
|
* 读取excel文件里面的内容 支持日期，数字，字符，函数公式，布尔类型
|
*
|
* @param file
|
* @param rowCount
|
* @param columnCount
|
* @return
|
* @throws FileNotFoundException
|
* @throws IOException
|
* @author JIANGYOUYAO
|
* @email 935090232@qq.com
|
* @date 2017年12月20日
|
*/
|
public static List<ExcelSheetPO> readExcel(File file, Integer rowCount, Integer columnCount)
|
throws FileNotFoundException, IOException {
|
|
// 根据后缀名称判断excel的版本
|
String extName = getFileExtName(file);
|
Workbook wb = null;
|
if (ExcelVersion.V2003.getSuffix().equals(extName)) {
|
wb = new HSSFWorkbook(new FileInputStream(file));
|
|
} else if (ExcelVersion.V2007.getSuffix().equals(extName)) {
|
wb = new XSSFWorkbook(new FileInputStream(file));
|
|
} else {
|
// 无效后缀名称，这里之能保证excel的后缀名称，不能保证文件类型正确，不过没关系，在创建Workbook的时候会校验文件格式
|
throw new IllegalArgumentException("Invalid excel version");
|
}
|
// 开始读取数据
|
List<ExcelSheetPO> sheetPOs = new ArrayList<>();
|
// 解析sheet
|
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
|
Sheet sheet = wb.getSheetAt(i);
|
List<List<Object>> dataList = new ArrayList<>();
|
ExcelSheetPO sheetPO = new ExcelSheetPO();
|
sheetPO.setSheetName(sheet.getSheetName());
|
sheetPO.setDataList(dataList);
|
int readRowCount = 0;
|
if (rowCount == null || rowCount > sheet.getPhysicalNumberOfRows()) {
|
readRowCount = sheet.getPhysicalNumberOfRows();
|
} else {
|
readRowCount = rowCount;
|
}
|
// 解析sheet 的行
|
for (int j = sheet.getFirstRowNum(); j < readRowCount; j++) {
|
Row row = sheet.getRow(j);
|
if (row == null) {
|
continue;
|
}
|
if (row.getFirstCellNum() < 0) {
|
continue;
|
}
|
int readColumnCount = 0;
|
if (columnCount == null || columnCount > row.getLastCellNum()) {
|
readColumnCount = (int) row.getLastCellNum();
|
} else {
|
readColumnCount = columnCount;
|
}
|
List<Object> rowValue = new LinkedList<Object>();
|
// 解析sheet 的列
|
for (int k = 0; k < readColumnCount; k++) {
|
Cell cell = row.getCell(k);
|
rowValue.add(getCellValue(wb, cell));
|
}
|
dataList.add(rowValue);
|
}
|
sheetPOs.add(sheetPO);
|
}
|
return sheetPOs;
|
}
|
|
|
|
|
|
private static Object getCellValue(Workbook wb, Cell cell) {
|
Object columnValue = null;
|
if (cell != null) {
|
// 格式化 number
|
DecimalFormat df = new DecimalFormat("0");
|
// String
|
// 字符
|
// 格式化日期字符串
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
// 格式化数字
|
DecimalFormat nf = new DecimalFormat("0.00");
|
switch (cell.getCellType()) {
|
case Cell.CELL_TYPE_STRING:
|
columnValue = cell.getStringCellValue();
|
break;
|
case Cell.CELL_TYPE_NUMERIC:
|
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
|
columnValue = df.format(cell.getNumericCellValue());
|
} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
|
columnValue = nf.format(cell.getNumericCellValue());
|
} else {
|
columnValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
|
}
|
break;
|
case Cell.CELL_TYPE_BOOLEAN:
|
columnValue = cell.getBooleanCellValue();
|
break;
|
case Cell.CELL_TYPE_BLANK:
|
columnValue = "";
|
break;
|
case Cell.CELL_TYPE_FORMULA:
|
// 格式单元格
|
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
|
evaluator.evaluateFormulaCell(cell);
|
CellValue cellValue = evaluator.evaluate(cell);
|
columnValue = cellValue.getNumberValue();
|
break;
|
default:
|
columnValue = cell.toString();
|
}
|
}
|
return columnValue;
|
}
|
|
/**
|
* 在硬盘上写入excel文件
|
*
|
* @param version
|
* @param excelSheets
|
* @param filePath
|
* @throws IOException
|
* @author JIANGYOUYAO
|
* @email 935090232@qq.com
|
* @date 2017年12月20日
|
*/
|
public static void createWorkbookAtDisk(ExcelVersion version, List<ExcelSheetPO> excelSheets, String filePath)
|
throws IOException {
|
FileOutputStream fileOut = new FileOutputStream(filePath);
|
createWorkbookAtOutStream(version, excelSheets, fileOut, true);
|
}
|
|
/**
|
* 把excel表格写入输出流中
|
*
|
* @param version
|
* @param excelSheets
|
* @param outStream
|
* @param closeStream 是否关闭输出流
|
* @throws IOException
|
* @author JIANGYOUYAO
|
* @email 935090232@qq.com
|
* @date 2017年12月20日
|
*/
|
public static void createWorkbookAtOutStream(ExcelVersion version, List<ExcelSheetPO> excelSheets,
|
OutputStream outStream, boolean closeStream) throws IOException {
|
if (CollectionUtils.isNotEmpty(excelSheets)) {
|
Workbook wb = createWorkBook(version, excelSheets);
|
wb.write(outStream);
|
if (closeStream) {
|
outStream.close();
|
}
|
}
|
}
|
|
private static Workbook createWorkBook(ExcelVersion version, List<ExcelSheetPO> excelSheets) {
|
Workbook wb = getWorkbookInstance(version);
|
for (int i = 0; i < excelSheets.size(); i++) {
|
ExcelSheetPO excelSheetPO = excelSheets.get(i);
|
if (excelSheetPO.getSheetName() == null) {
|
excelSheetPO.setSheetName("sheet" + i);
|
}
|
// 过滤特殊字符
|
Sheet tempSheet = wb.createSheet(WorkbookUtil.createSafeSheetName(excelSheetPO.getSheetName()));
|
buildSheetData(wb, tempSheet, excelSheetPO, version);
|
}
|
return wb;
|
}
|
|
private static void buildSheetData(Workbook wb, Sheet sheet, ExcelSheetPO excelSheetPO, ExcelVersion version) {
|
sheet.setDefaultRowHeight((short) 400);
|
// sheet.setDefaultColumnWidth((short) 10);
|
sheet.autoSizeColumn(1);
|
createTitle(sheet, excelSheetPO, wb, version);
|
createHeader(sheet, excelSheetPO, wb, version);
|
createBody(sheet, excelSheetPO, wb, version);
|
}
|
|
private static void createBody(Sheet sheet, ExcelSheetPO excelSheetPO, Workbook wb, ExcelVersion version) {
|
List<List<Object>> dataList = excelSheetPO.getDataList();
|
for (int i = 0; i < dataList.size() && i < version.getMaxRow(); i++) {
|
List<Object> values = dataList.get(i);
|
Row row = sheet.createRow(2 + i);
|
for (int j = 0; j < values.size() && j < version.getMaxColumn(); j++) {
|
Cell cell = row.createCell(j);
|
Object value = values.get(j);
|
if (value == null) {
|
value = "";
|
}
|
cell.setCellValue(String.valueOf(value));
|
}
|
}
|
}
|
|
private static void createHeader(Sheet sheet, ExcelSheetPO excelSheetPO, Workbook wb, ExcelVersion version) {
|
String[] headers = excelSheetPO.getHeaders();
|
Row row = sheet.createRow(1);
|
for (int i = 0; i < headers.length && i < version.getMaxColumn(); i++) {
|
Cell cellHeader = row.createCell(i);
|
cellHeader.setCellStyle(getStyle(STYLE_HEADER, wb));
|
cellHeader.setCellValue(headers[i]);
|
// 调整每一列宽度
|
sheet.autoSizeColumn((short) i);
|
// 解决自动设置列宽中文失效的问题
|
sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 16 / 10);
|
}
|
|
}
|
|
private static void createTitle(Sheet sheet, ExcelSheetPO excelSheetPO, Workbook wb, ExcelVersion version) {
|
Row titleRow = sheet.createRow(0);
|
Cell titleCel = titleRow.createCell(0);
|
titleCel.setCellValue(excelSheetPO.getTitle());
|
titleCel.setCellStyle(getStyle(STYLE_TITLE, wb));
|
// 限制最大列数
|
if (ArrayUtil.isNotEmpty(excelSheetPO.getHeaders())) {
|
int column = excelSheetPO.getHeaders().length > version.getMaxColumn() ? version.getMaxColumn()
|
: excelSheetPO.getHeaders().length;
|
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, column - 1));
|
}
|
|
}
|
|
private static CellStyle getStyle(String type, Workbook wb) {
|
|
HashMap<String, CellStyle> cellStyleMap = new HashMap<>();
|
if (cellStyleMap.containsKey(type)) {
|
return cellStyleMap.get(type);
|
}
|
// 生成一个样式
|
CellStyle style = wb.createCellStyle();
|
// style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
|
// style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
|
// style.setBorderRight(HSSFCellStyle.BORDER_THIN);
|
// style.setBorderTop(HSSFCellStyle.BORDER_THIN);
|
style.setWrapText(true);
|
|
if (STYLE_HEADER == type) {
|
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
|
Font font = wb.createFont();
|
font.setFontHeightInPoints((short) 16);
|
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
|
style.setFont(font);
|
} else if (STYLE_TITLE == type) {
|
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
|
Font font = wb.createFont();
|
font.setFontHeightInPoints((short) 18);
|
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
|
style.setFont(font);
|
} else if (STYLE_DATA == type) {
|
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
|
Font font = wb.createFont();
|
font.setFontHeightInPoints((short) 12);
|
style.setFont(font);
|
}
|
cellStyleMap.put(type, style);
|
return style;
|
}
|
|
private static Workbook getWorkbookInstance(ExcelVersion version) {
|
switch (version) {
|
case V2003:
|
return new HSSFWorkbook();
|
case V2007:
|
return new XSSFWorkbook();
|
case V_BIGDATA:
|
return new SXSSFWorkbook(2000);
|
default:
|
throw new IllegalArgumentException("param is not defind");
|
}
|
}
|
}
|
