因为公司需要利用poi 进行自定义的导出，乘此了解一下poi的一些常用操作
client 端
import com.alibaba.excel.metadata.BaseRowModel;
import com.hiberate.huijpa.pojo.EmpExcelModel;
import com.hiberate.huijpa.util.ReflectUtil;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* @author liyhu
* @date 2019年09月28日
*/
public class PoiModelClient {
public static void main(String[] args) throws IllegalAccessException, NoSuchMethodException, InvocationTargetException, NoSuchFieldException {
String sheetName="one";
List<BaseRowModel> data=new ArrayList<>();
for (int i = 0; i < 9; i++) {
EmpExcelModel model=new EmpExcelModel();
model.setMobile("mobile"+i);
model.setCardPassword("pwd"+i);
model.setCardSn("sn"+i);
model.setCardNo("no"+i);
model.setFreezeStatus("正常");
data.add(model);
}
Workbook wb = ExcelUtil.createExcel(sheetName, data);
try (FileOutputStream os = new FileOutputStream("D:\\a.xlsx")){
wb.write(os);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
System.out.println("ok");
}
}
工具类
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.hiberate.huijpa.util.ReflectUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
/**
* @author liyhu
* @date 2019年09月28日
*/
public class ExcelUtil {
private static short text;
private static Font blodFont ;// 粗字
private static Font redFont; // 红字
private static Workbook globalWorkBook =new XSSFWorkbook();
// 匹配这种格式 *状态[正常/立即冻结/售完冻结]
private static Pattern pullDownPattern = Pattern.compile("[\\u4e00-\\u9fa5]+\\[([\\u4e00-\\u9fa5]+/[\\u4e00-\\u9fa5|/]+)]$");
static {
DataFormat dataFormat = globalWorkBook.createDataFormat();//创建格式化对象
text=dataFormat.getFormat("TEXT");
blodFont= globalWorkBook.createFont();
blodFont.setBold(true);// 加粗
blodFont.setFontName("宋体");
blodFont.setFontHeightInPoints((short) 14);// 14号字体
redFont = globalWorkBook.createFont();
redFont.setBold(true);
redFont.setFontName("宋体");
redFont.setColor(Font.COLOR_RED);
}
/**
* 这里的 workbook 不能用全局的 workbook
* @param workbook
* @return
*/
private static CellStyle crateTitleCellStyle(Workbook workbook){
CellStyle titleStyle = workbook.createCellStyle(); //标题样式
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
titleStyle.setBorderBottom(BorderStyle.THIN);
titleStyle.setBorderRight(BorderStyle.THIN);
titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return titleStyle;
}
/**
* 创建 1,2 行
* @param sheet
* @param colNum
*/
private static void setOneTwoTitle(Workbook workbook , Sheet sheet , int colNum,String firtRowVal){
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colNum - 1));// 合并单元格
Row firstTitleRow = sheet.createRow(0);
Cell firstTitleCell = firstTitleRow.createCell(0);
firstTitleCell.setCellValue(firtRowVal);
CellStyle firstCellStyle = createFirstRowStyle(workbook);
firstTitleCell.setCellStyle(firstCellStyle);
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, colNum - 1));
Row secondTitleRow = sheet.createRow(1);
Cell secondTitleCell = secondTitleRow.createCell(0);// 第二行样式
secondTitleCell.setCellValue("导入人：");
CellStyle secondCellStyle = createSecondRowStyle(workbook);
secondTitleCell.setCellStyle(secondCellStyle);
}
/**
* 创建第一行样式
* @param workbook
* @return
*/
private static CellStyle createFirstRowStyle(Workbook workbook){
CellStyle firstCellStyle = workbook.createCellStyle();// 第一行样式
firstCellStyle.setAlignment(HorizontalAlignment.CENTER);
firstCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
firstCellStyle.setFont(blodFont);
return firstCellStyle;
}
/**
* 创建第二行样式
* @param workbook
* @return
*/
private static CellStyle createSecondRowStyle(Workbook workbook){
CellStyle secondCellStyle = workbook.createCellStyle();
secondCellStyle.setAlignment(HorizontalAlignment.LEFT);
secondCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
secondCellStyle.setFont(blodFont);
return secondCellStyle;
}
/**
* 创建标题
* @param workbook
* @param sheet
* @param beanMap
* @param colWidthMap
*/
private static void setHeader(Workbook workbook, Sheet sheet, Map<String, Object> beanMap, BaseRowModel model, Map<Integer, Integer> colWidthMap, int startRow){
Row titleRow = sheet.createRow(startRow);
CellStyle textStyle = workbook.createCellStyle(); //标题样式
textStyle.setDataFormat(text);
//这里不能和类里的font公用
Font blodFont = workbook.createFont();
blodFont.setBold(true);
blodFont.setFontName("宋体");
blodFont.setFontHeightInPoints((short) 14);
CellStyle titleStyle= crateTitleCellStyle(workbook);
titleStyle.setFont(blodFont);
Field[] fields = model.getClass().getDeclaredFields();
Map<String, Field> fieldMap = Arrays.stream(fields).collect(Collectors.toMap(Field::getName, f -> f));
int index = 0;
for (Map.Entry<String, Object> header : beanMap.entrySet()) {
Cell cell = titleRow.createCell(index);
cell.setCellStyle(titleStyle);
String fieldName = header.getKey();
Field field = fieldMap.get(fieldName);
String excelPropertyVal = getExcelPropertyVal(field);
Matcher matcher = pullDownPattern.matcher(excelPropertyVal);
if(matcher.find()){// 创建该列的下拉
String[] subjects = matcher.group(1).split("/");
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(subjects);
CellRangeAddressList addressList = new CellRangeAddressList(startRow + 1, startRow+50, index, index);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
sheet.addValidationData(dataValidation);
}
setColWidth(colWidthMap,excelPropertyVal,index,true);
RichTextString richTextString = new XSSFRichTextString(excelPropertyVal);
richTextString.applyFont(blodFont);
if (richTextString.getString().startsWith("*")) {
richTextString.applyFont(0, 1, redFont);
}
cell.setCellValue(richTextString);
sheet.setDefaultColumnStyle(index, textStyle);
index++;
}
}
private static void setColWidth(Map<Integer, Integer> colWidthMap, String val, int index, boolean isHeader){
int length = val.length();
Integer defaultColWidth = colWidthMap.get(index);
if (length > defaultColWidth) {
if(isHeader){// 标题则是字数的两倍
length *= 2;
}
colWidthMap.put(index, length );
}
}
/**
* 得到 @ExcelProperty 注解的值
* @param field
* @return
*/
public static String getExcelPropertyVal(Field field){
ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
if(annotation == null){
return null;
}
return annotation.value()[0];
}
/**
* 这里的 workbook 不能用全局的 workbook <br/>
* 获取数据单元格样式
* @return
* @param workbook
*/
private static CellStyle createDataCellStyle(Workbook workbook){
CellStyle dataStyle = workbook.createCellStyle();
dataStyle.setAlignment(HorizontalAlignment.CENTER);
dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
dataStyle.setDataFormat(text);
return dataStyle;
}
/**
* 设置单元格数据
* @param sheet
* @param dataMapList
* @param colWidthMap
* @param startRow
*/
private static void setData(Workbook workbook, Sheet sheet, List<Map<String, Object>> dataMapList, Map<Integer, Integer> colWidthMap, int startRow){
CellStyle dataStyle= createDataCellStyle(workbook);
int rowIndex = 0;
for (Map<String, Object> beanMap : dataMapList) {
Row row = sheet.createRow(rowIndex + startRow);
int colIndex = 0;
for (Map.Entry<String, Object> entry : beanMap.entrySet()) {
Cell cell = row.createCell(colIndex);
String valString = entry.getValue().toString();
cell.setCellValue(valString);
cell.setCellStyle(dataStyle);
setColWidth(colWidthMap,valString,colIndex, true);
colIndex++;
}
rowIndex++;
}
}
public static Workbook createExcel(String sheetName, List<BaseRowModel> data) throws IllegalAccessException, NoSuchMethodException, InvocationTargetException, NoSuchFieldException {
List<Map<String, Object>> dataMapList = new ArrayList<>();
for (BaseRowModel model : data) {
Map<String, Object> map = ReflectUtil.beanToMap(model);
map.remove("cellStyleMap");//不需要这个属性
dataMapList.add(map);
}
Map<String, Object> firstBeanMap = dataMapList.get(0);
int colNum = firstBeanMap.size();// 列的数量
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet(sheetName);
sheet.setDefaultRowHeight((short) (2 * 256));//设置默认行高
setOneTwoTitle( workbook , sheet , colNum,"员工");
Map<Integer, Integer> colWidthMap = new HashMap<>();
for (int i = 0; i < colNum; i++) {// 设置默认列宽
colWidthMap.put(i, 14);
}
setHeader(workbook,sheet,firstBeanMap,data.get(0),colWidthMap,2);
setData(workbook,sheet,dataMapList,colWidthMap,3);
for (Map.Entry<Integer, Integer> entry : colWidthMap.entrySet()) {
sheet.setColumnWidth(entry.getKey(), entry.getValue() * 256);//设置每列宽度
}
return workbook;
}
public static TreeMap<Integer,String> getSortMap(Class<?> clazz){
TreeMap<Integer,String> treeMap=new TreeMap<>();
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
if(excelProperty == null){
continue;
}
treeMap.put(excelProperty.index(),field.getName());
}
return treeMap;
}
}
实体转换工具类
import com.alibaba.excel.annotation.ExcelProperty;
import org.apache.commons.beanutils.PropertyUtilsBean;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.TreeMap;
/**
* @author liyhu
* @date 2019年08月27日
*/
public class ReflectUtil {
public static <T>T mapToProperties(Map<String,Object> map,Class<T> tClass) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException, InstantiationException {
T t = tClass.newInstance();
PropertyUtilsBean propertyUtilsBean = new PropertyUtilsBean();
PropertyDescriptor[] descriptors = propertyUtilsBean.getPropertyDescriptors(t);
for (PropertyDescriptor descriptor : descriptors) {
String name = descriptor.getName();
if("class".equals(name)){
continue;
}
Object val = map.get(name);
if(val != null){
propertyUtilsBean.setProperty(t,name,val);
}
}
return t;
}
public static Map<String,Object> commonBeanToMap(Object obj) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException {
PropertyUtilsBean propertyUtilsBean = new PropertyUtilsBean();
PropertyDescriptor[] descriptors = propertyUtilsBean.getPropertyDescriptors(obj);
Map<String,Object> resultMap=new HashMap<>(descriptors.length);
for (PropertyDescriptor descriptor : descriptors) {
String name = descriptor.getName();
if("class".equals(name)){
continue;
}
Object val = propertyUtilsBean.getNestedProperty(obj, name);
if(val != null){
resultMap.put(name,val);
}
}
return resultMap;
}
public static Map<String,Object> beanToMap(Object obj) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException, NoSuchFieldException {
TreeMap<Integer,String> treeMap=new TreeMap<>();
Class<?> aClass = obj.getClass();
PropertyUtilsBean propertyUtilsBean = new PropertyUtilsBean();
PropertyDescriptor[] descriptors = propertyUtilsBean.getPropertyDescriptors(obj);
Map<String,Object> resultMap=new HashMap<>(descriptors.length);
for (PropertyDescriptor descriptor : descriptors) {
String name = descriptor.getName();
if("class".equals(name)){
continue;
}
Object val = propertyUtilsBean.getNestedProperty(obj, name);
if(val != null){
resultMap.put(name,val);
if("cellStyleMap".equals(name)){
continue;
}
Field field = aClass.getDeclaredField(name);
ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
if(excelProperty != null){
treeMap.put(excelProperty.index(),name);
}
}
}
Map<String,Object> result=new LinkedHashMap<>();
for (Map.Entry<Integer, String> entry : treeMap.entrySet()) {
Object val = resultMap.get(entry.getValue());
result.put(entry.getValue(),val);
}
return result;
}
}