ExcelUtil
package connect.util;
import org.apache.commons.collections.CollectionUtils;
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.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的功能
*
*/
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 final HashMap<String, CellStyle> cellStyleMap = new HashMap<>();
/**
* 读取excel文件里面的内容 支持日期,数字,字符,函数公式,布尔类型
*
* @param file
* @param rowCount
* @param columnCount
*/
public static List<ExcelSheetPO> readExcel(File file, Integer rowCount, Integer columnCount)
throws FileNotFoundException, IOException {
// 根据后缀名称判断excel的版本
String extName = file.getName().substring(file.getName().lastIndexOf(".") + 1);
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) {
DecimalFormat df = new DecimalFormat("0");// 格式化 number
// 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
*/
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 是否关闭输出流
*/
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 = createWorkbook(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);
//有需要可以自定义title和header
// createTitle(sheet, excelSheetPO, wb, version);
// createHeader(sheet, excelSheetPO, wb, version);
createBody(sheet, excelSheetPO, wb, version);
}
/**
* 内容
* @param sheet
* @param excelSheetPO
* @param wb
* @param 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);
Row row = sheet.createRow(i);
for (int j = 0; j < values.size() && j < version.getMaxColumn(); j++) {
Cell cell = row.createCell(j);
// cell.setCellStyle(getStyle(STYLE_DATA, wb));
cell.setCellValue(values.get(j).toString());
}
}
}
/**
* 生成header
* @param sheet
* @param excelSheetPO
* @param wb
* @param version
*/
private static void createHeader(Sheet sheet, ExcelSheetPO excelSheetPO, Workbook wb, ExcelVersion version) {
String[] headers = excelSheetPO.getDataList().get(0).toArray(new String[0]);
if (headers == null) {
Cell cellHeader = null;
} else {
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]);
}
}
}
/**
* 生成title
* @param sheet
* @param excelSheetPO
* @param wb
* @param version
*/
private static void createTitle(Sheet sheet, ExcelSheetPO excelSheetPO, Workbook wb, ExcelVersion version) {
Row titleRow = sheet.createRow(0);
Cell titleCel = titleRow.createCell(0);
excelSheetPO.setTitle("123");
titleCel.setCellValue(excelSheetPO.getTitle());
// titleCel.setCellStyle(getStyle(STYLE_TITLE, wb));
// 限制最大列数
int column = excelSheetPO.getDataList().size() > version.getMaxColumn() ? version.getMaxColumn()
: excelSheetPO.getDataList().size();
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, column - 1));
}
/**
* 获取样式style
* @param version
* @return
*/
// private static CellStyle getStyle(String type, Workbook wb) {
//
// 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 createWorkbook(ExcelVersion version) {
switch (version) {
case V2003:
return new HSSFWorkbook();
case V2007:
return new XSSFWorkbook();
}
return null;
}
}
ExcelSheetPO 表格对象
package connect.util;
import java.util.List;
/**
* 定义表格的数据对象
*/
public class ExcelSheetPO {
/**
* sheet的名称
*/
private String sheetName;
/**
* 表格标题
*/
private String title;
/**
* 头部标题集合
*/
private String[] headers;
/**
* 数据集合
*/
private List<List<Object>> dataList;
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String[] getHeaders() {
return headers;
}
public void setHeaders(String[] headers) {
this.headers = headers;
}
public List<List<Object>> getDataList() {
return dataList;
}
public void setDataList(List<List<Object>> dataList) {
this.dataList = dataList;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
}
excel版本枚举
package connect.util;
/**
* excel版本枚举
*
*/
public enum ExcelVersion {
/**
* 虽然V2007版本支持最大支持1048575 * 16383 ,
* V2003版支持65535*255
* 但是在实际应用中如果使用如此庞大的对象集合会导致内存溢出,
* 因此这里限制最大为10000*100,如果还要加大建议先通过单元测试进行性能测试。
* 1000*100 全部导出预计时间为27s左右
*/
V2003("xls", 10000, 100), V2007("xlsx", 100, 100);
private String suffix;
private int maxRow;
private int maxColumn;
ExcelVersion(String suffix, int maxRow, int maxColumn) {
this.suffix = suffix;
this.maxRow = maxRow;
this.maxColumn = maxColumn;
}
public String getSuffix() {
return this.suffix;
}
public int getMaxRow() {
return maxRow;
}
public void setMaxRow(int maxRow) {
this.maxRow = maxRow;
}
public int getMaxColumn() {
return maxColumn;
}
public void setMaxColumn(int maxColumn) {
this.maxColumn = maxColumn;
}
public void setSuffix(String suffix) {
this.suffix = suffix;
}
}
测试
package connect.util;
import connect.service.BaseTest;
import java.io.File;
import java.io.IOException;
import java.util.List;
public class Test extends BaseTest {
@org.junit.Test
public void set() {
String file = "/Users/mac/Desktop/拯救人们2019-01-21 重庆时时彩.xlsx";
File file1 = new File(file);
try {
//传入一个文件,调用readExcel()读取文件,返回List<ExcelSheetPO>
List<ExcelSheetPO> list = ExcelUtil.readExcel(file1, null, null);
for (ExcelSheetPO a : list) {
System.out.println(a.getHeaders() + ".." + a.getTitle() + ".." + a.getSheetName());
for (List<Object> b : a.getDataList()) {
System.out.println(b);
}
}
//调用createWorkbookAtDisk()生成excel
ExcelUtil.createWorkbookAtDisk(ExcelVersion.V2007, list, "/Users/mac/Desktop/resres.xlsx");
} catch (IOException e) {
e.printStackTrace();
}
}
}
转自:https://blog.csdn.net/weixin_41497481/article/details/87712446