javaDEMO
Java 基础 Demo 站: https://www.javastudy.cloud
Java 中高级开发博客: https://www.lixiang.red
Java 学习公众号: java 技术大本营
POI 简介
在 java 中,对 word/Excel 的处理,基本上都是通过 poi 来完成的
poi 地址:
https://poi.apache.org/components/spreadsheet/index.html
Word/Excel 分两大版本,对应着我们 doc/xls 和 docx/xlsx 这些后缀名,其中的 x 是代表 xml 的意思. 就是说, 新版的 Word/Excel 都可以用 XML 来表式.
使用场景:在用 word+freemarker 做模板时, 先在 word 中,用{model}占位符. 然后把 word 另存为 xml 文档. 然后在 freemarker 中,读取这个 xml 文档,然后传入 map,最后输出流再转成 word 就可以了.
当然在实际开发中,还是 Excel 用的多. 用来导入和导出数据.要使用 poi 处理 EXCEL 需要以下步骤:
1.引入相关的依赖
2. 定义实体类和 Excel 表头的关联
3. 编写 Excel 操作的工具类
3. 测试
POI 导入/导出 EXCEL
引入相关的依赖
compile group: 'org.apache.poi', name: 'poi', version: '4.1.1'
compile group: 'org.apache.poi', name: 'poi-ooxml', version: '4.1.1'
定义实体类和 Excel 表头的关联
这里我定义一个 CellField 来描述这种关系,其结构如下所示
public class CellField {
/** cell 值,字符串 */
public static final int FIELD_STRING = 1;
/** cell类型, 普通的 */
public static final int CELL_NORMAL = 1;
/** cell类型, 下拉框
* 下拉框的中的java字段一定要和实体类的值对应,不然不好set进去,而且最好是和String类型的对应
*/
public static final int CELL_DROP = 2;
/** java实体类名字 */
private String javaName;
/** excel表头的东西 */
private String cellName;
/** cell值的类型,有字符串,日期,等等,默认是字符串的 */
private Integer fieldType = 1;
/** cell 的类型, 有普通的,下拉框,等等,默认是普通的 */
private Integer cellType = 1;
/** 排序值,默认是0 */
private Integer order = 0;
/** 默认值 */
private Object defaultValue;
public CellField() {
}
public CellField(String javaName, String cellName) {
this.javaName = javaName;
this.cellName = cellName;
}
public CellField(String javaName, String cellName, Integer cellType) {
this.javaName = javaName;
this.cellName = cellName;
this.cellType = cellType;
}
public String getJavaName() {
return javaName;
}
public CellField setJavaName(String javaName) {
this.javaName = javaName;
return this;
}
public String getCellName() {
return cellName;
}
public CellField setCellName(String cellName) {
this.cellName = cellName;
return this;
}
public Integer getFieldType() {
return fieldType;
}
public CellField setFieldType(Integer fieldType) {
this.fieldType = fieldType;
return this;
}
public Integer getCellType() {
return cellType;
}
public CellField setCellType(Integer cellType) {
this.cellType = cellType;
return this;
}
public Integer getOrder() {
return order;
}
public CellField setOrder(Integer order) {
this.order = order;
return this;
}
public Object getDefaultValue() {
return defaultValue;
}
public CellField setDefaultValue(Object defaultValue) {
this.defaultValue = defaultValue;
return this;
}
}
POI 操作 Excel 的几个基础方法
/**
* 创建Excel的表头
*/
public static Row createExcelHeader(List<CellField> cellFields){
Workbook wb = new XSSFWorkbook();
return createExcelHeader( wb.createSheet(),cellFields);
}
public static Row createExcelHeader(Sheet sheet,List<CellField> cellFields){
cellFields.sort(Comparator.comparingInt(CellField::getOrder));
Map<String, CellField> paramMap = cellFields.stream().collect(Collectors.toMap(CellField::getJavaName, x -> x,(x,y)-> x,LinkedHashMap::new));
int colPointer = 0;
// 渲染第一行的表头
Row firstRow = accessRow(sheet, 0);
for (CellField field : cellFields) {
String fieldName = field.getJavaName();
if (paramMap.get(fieldName) != null) {
Cell cell = accessCell(firstRow, colPointer++);
// System.out.println(fieldName + "!!!!" + paramMap.get(fieldName));
cell.setCellValue(paramMap.get(fieldName).getCellName());
if(CELL_DROP==field.getCellType()){
createDropList((List<String>) field.getDefaultValue(),cell,sheet);
}
}
}
return firstRow;
}
/**
* 对excel创建下拉框
* @param list
* @param cell
* @param sheet
*/
public static void createDropList(List<String> list, Cell cell, Sheet sheet) {
CellRangeAddressList addressList = new CellRangeAddressList(1, 2000, cell.getColumnIndex(), cell.getColumnIndex());
DataValidationHelper dvHelper = sheet.getDataValidationHelper();
DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(list.toArray(new String[0]));
DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
sheet.addValidationData(validation);
}
/**
* 导出Excel文件
* @param wb
* @return
*/
public static void exportExcelFile(Workbook wb, String partFileName) {
if (wb != null) {
String fileName = "Excel-" + partFileName + "-"
+ new SimpleDateFormat("yyyy_MM_dd_HH_mm_ss").format(new Date()) + ".xlsx";
OutputStream out = null;
try {
out = new FileOutputStream(new File(fileName));
wb.write(out);
} catch (IOException e) {
e.printStackTrace();
}
finally {
try {
if(out!=null){
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/***
* 创建一行
* @param sheet
* @param rowNum
* @return
*/
private static Row accessRow(Sheet sheet, int rowNum) {
Row row = sheet.getRow(rowNum);
if (row == null) {
row = sheet.createRow(rowNum);
}
return row;
}
/**
* 创建一个单元格
*
* @param row
* @param column
* @return
*/
private static Cell accessCell(Row row, int column) {
Cell cell = row.getCell(column);
if (cell == null) {
cell = row.createCell(column);
}
return cell;
}
POI 把 List 导出成 Excel
/**
* 将List对象转换为excel文档.
*
* @param list POJO对象列表
* @param cellFields 表头的描述项
* @throws Exception
*/
public static <T> Workbook beanToExcel(List<T> list, List<CellField> cellFields, Class<T> clazz) {
cellFields.sort(Comparator.comparingInt(CellField::getOrder));
Map<String, CellField> paramMap = cellFields.stream().collect(Collectors.toMap(CellField::getJavaName, x -> x,(x,y)-> x,LinkedHashMap::new));
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet();
// 处理要反射的字段
Field[] fields = clazz.getDeclaredFields();
Map<String, Field> fieldMap = new HashMap<>(fields.length);
for (Field field : fields) {
field.setAccessible(true);
String fieldName = field.getName();
fieldMap.put(fieldName, field);
}
// 渲染第一行的表头
Row firstRow = createExcelHeader(sheet,cellFields);
int beginNum = 1;
for (T d : list) {
Row row = accessRow(sheet, beginNum++);
for (int i = 0; i < paramMap.size(); i++) {
//开始为每行写入值
try {
for (int i1 = 0; i1 < cellFields.size(); i1++) {
//获取表头对应的java名
CellField cellField = cellFields.get(i1);
String javaName = cellField.getJavaName();
String cellHeaderName = cellField.getCellName();
if (javaName != null && cellHeaderName.equals(firstRow.getCell(i).getStringCellValue())) {
Field field = fieldMap.get(javaName);
Cell cell = accessCell(row, i);
if(field ==null){
//对于CellFieldList里面有的字段,实体类中没有的,跳过这一列
cell.setBlank();
continue;
}
Object obj = field.get(d);
if (obj == null) {
continue;
} else if (String.class.isAssignableFrom(obj.getClass())) {
cell.setCellValue((String) obj);
} else if (Date.class.isAssignableFrom(obj.getClass())) {
// 日期存储为Number，显示需求依赖CellStyle
cell.setCellValue((Date) obj);
} else if (Number.class.isAssignableFrom(obj.getClass())) {
// 数字类型的也都按字符串方式来存
cell.setCellValue(String.valueOf(obj));
} else if (Boolean.class.isAssignableFrom(obj.getClass())) {
cell.setCellValue(((Boolean) obj));
} else {
cell.setCellValue(obj.toString());
}
}
}
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
return workbook;
}
POI 读取 Excel 为 List
public static <T> List<T> excelToList(InputStream inputStream,List<CellField> cellFieldList, Class<T> clazz){
List<T> list = new ArrayList<>();
//hashMap是以键值对(key and value)来存储的
//<列名，单元格值>
HashMap<String, String> indexMap = new HashMap<>();
try {
//fileInputStream就是一个book
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
//从book中获取第一个sheet
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
//获取sheet的总行数
int rows = xssfSheet.getPhysicalNumberOfRows();
//规定第1(引索是0)行是字段名行
XSSFRow firstRow = xssfSheet.getRow(0);
//遍历是从第2(引索是1)行开始
for (int i = 1; i < rows; i++) {
//从整个sheet里面获取第i行的数据
XSSFRow xssfRow = xssfSheet.getRow(i);
if (xssfRow == null) {
//如果某一行为空 就跳过读取下一行
continue;
}
//读取一行中的每一个单元格
for (int j = 0; j < xssfRow.getLastCellNum(); j++) {
//获取该单元格的列名
//getStringCellValue从单元格取出类型为String的值
String columnCellName = firstRow.getCell(j).getStringCellValue();
//开始从第一列开始遍历单元格
XSSFCell cell = xssfRow.getCell(j);
String value = "";
if (cell != null) {
switch (cell.getCellType()) {
case FORMULA:
value = cell.getCellFormula();
break;
case NUMERIC:
value = "" + cell.getNumericCellValue();
break;
case STRING:
value = cell.getStringCellValue();
break;
case BLANK:
value = "<BLANK>";
break;
case BOOLEAN:
value = "" + cell.getBooleanCellValue();
break;
case ERROR:
value = "" + cell.getErrorCellValue();
break;
default:
value = "" + cell.getStringCellValue();
}
}
indexMap.put(columnCellName,value);
}
//一行循环之后，将值注入到新生成的实例中，然后放到list中
try {
T t = clazz.newInstance();
//获取到传入Class的全部变量名
// 对CellList进行循环, 然后赋值
for (CellField cellField : cellFieldList) {
String javaName = cellField.getJavaName();
String columnName = cellField.getCellName();
try{
Field field = clazz.getDeclaredField(javaName);
field.setAccessible(true);
if(field.getType()==Integer.class){
if(indexMap.get(columnName)!=null){
double d = Double.valueOf(indexMap.get(columnName));
field.set(t, (int)d);
}
}else if(field.getType()==Double.class) {
if(indexMap.get(columnName)!=null){
double d = Double.valueOf(indexMap.get(columnName));
field.set(t, d);
}
}else if(field.getType()== BigDecimal.class) {
if(indexMap.get(columnName)!=null){
BigDecimal b = new BigDecimal(indexMap.get(columnName));
field.set(t, b);
}
}else {
field.set(t, indexMap.get(columnName));
}
}catch (NoSuchFieldException e){
// 如果发生了这个异常,说明没有对应的java字段,可能是下拉框
// 对异常不做处理,也就是对这个不做处理
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
list.add(t);
} catch (InstantiationException | IllegalAccessException e) {
e.printStackTrace();
}
}
} catch (IOException e) {
e.printStackTrace();
}
return list;
}
DEMO 总评
Java 操作 Excel 基本上都是通过 POI,有很多人有不同的封装, 大体上思路都是差不多的.本 DEMO 的思路也仅供参考,两三天简单封装了一下, 考虑的也不是太全面.欢迎小伙伴们一起完善,加油!
欢迎来到这里！
我们正在构建一个小众社区，大家在这里相互信任，以平等 • 自由 • 奔放的价值观进行分享交流。最终，希望大家能够找到与自己志同道合的伙伴，共同成长。
注册 关于