/*** * @param dataList 数据* @param headNameMap 标题* @param type 类型 1 xls 2 xlsx* @param mergeIndex 需要合并的列 从1开始 0是序号* @param col 以哪一列为准进行合并* @return* @throws Exception*/public static byte[] toExcel(List<?> dataList, Map<String, String> headNameMap, int type,int[] mergeIndex,int col) throws Exception {Workbook workbook;if (type == 1) {workbook = new XSSFWorkbook();} else if (type == 2) {workbook = new SXSSFWorkbook();} else {workbook = new HSSFWorkbook();}List<Method> methodList = null;Sheet sheet = workbook.createSheet("数据列表");int index = sheet.getPhysicalNumberOfRows();for (int i = 0; i < dataList.size(); i++) {Object object = dataList.get(i);if (methodList == null) {Method[] methods = object.getClass().getMethods();methodList = new ArrayList<>();Row rowHead = sheet.createRow(index);Iterator<Map.Entry<String, String>> iterator = headNameMap.entrySet().iterator();int c = 0;while (iterator.hasNext()) {Map.Entry<String, String> entry = iterator.next();for (int m = 0; m < methods.length; m++) {if (methods[m].getName().toLowerCase().equals(("get" + entry.getKey()).toLowerCase())) {methodList.add(methods[m]);Cell cell = rowHead.createCell(c);setCellValue(cell, entry.getValue());c++;}if (methods[m].getName().toLowerCase().equals(("getlist"))){Object invoke = methods[m].invoke(object);}}}}Row row = sheet.createRow(index + 1);for (int m = 0; m < methodList.size(); m++) {Object value = methodList.get(m).invoke(object);Cell cell = row.createCell(m );Object textValue = getValue(value);setCellValue(cell, textValue);}index++;}String str=null;int strBeginIndex=0;int strEndIndex=0;for (int i = 0; i <mergeIndex.length; i++) {int j=0;int start=0;for (Row row : sheet) {if (j==0){j++;continue ;}if (strBeginIndex==0){strBeginIndex=sheet.getRow(j-1).getRowNum();}// 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)// 行和列都是从0开始计数，且起始结束都会合并if (StringUtil.isEmpty(str)){str = row.getCell(col).getStringCellValue();if (str.equals(sheet.getRow(j).getCell(col).getStringCellValue())){strBeginIndex=row.getRowNum();}}else if (str.equals(row.getCell(col).getStringCellValue())){if (strBeginIndex==0){strBeginIndex=sheet.getRow(j-1).getRowNum();}if (sheet.getLastRowNum()==j){//末尾合并strBeginIndex =strBeginIndex-1;strEndIndex =strEndIndex+1;if(strBeginIndex!=strEndIndex){CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex, mergeIndex[i], mergeIndex[i]);sheet.addMergedRegion(region);}strBeginIndex=0;start=0;}}else if (!str.equals(row.getCell(col).getStringCellValue())){//需合并strEndIndex=row.getRowNum();if (start==0&&strBeginIndex>0&&strEndIndex>0){strEndIndex =strEndIndex-1;if(strBeginIndex!=strEndIndex){//首行合并CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex, mergeIndex[i], mergeIndex[i]);sheet.addMergedRegion(region);}strBeginIndex=0;start=1;}else if (strBeginIndex>0&&strEndIndex>0){//中间行合并strEndIndex =strEndIndex-1;if(strBeginIndex!=strEndIndex){CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex, mergeIndex[i], mergeIndex[i]);sheet.addMergedRegion(region);}strBeginIndex=0;}str=row.getCell(col).getStringCellValue();}j++;}}ByteArrayOutputStream baos = new ByteArrayOutputStream();workbook.write(baos);workbook.close();System.out.println("export finish!");return baos.toByteArray();}
private static Object getValue(Object value) {Object textValue = "";if (value != null) {if (value instanceof Boolean) {textValue = (Boolean) value ? "是" : "否";} else if (value instanceof Date) {textValue = DateUtils.format((Date) value, "yyyy-MM-dd HH:mm:ss");} else if (value instanceof String) {String val = (String) value;textValue = StringUtil.isEmpty(val) || "null".equalsIgnoreCase(val) ? "" : val;} else {textValue = value;}}return textValue;}
private static void setCellValue(Cell cell, Object value) {if (value != null) {if (value instanceof Integer) {cell.setCellValue((Integer) value);} else if (value instanceof Boolean) {Boolean booleanValue = (Boolean) value;cell.setCellValue(booleanValue);} else if (value instanceof Date) {Date dateValue = (Date) value;cell.setCellValue(dateValue);} else if (value instanceof Float) {Float floatValue = (Float) value;cell.setCellValue(floatValue);} else if (value instanceof Double) {Double doubleValue = (Double) value;cell.setCellValue(doubleValue);} else if (value instanceof Long) {Long longValue = (Long) value;cell.setCellValue(longValue);} else {cell.setCellValue(value.toString());}}}
test：
public static void main(String[] args) throws Exception {List<Model> list=new ArrayList<>();Model model0=new Model("21052199002","中国南通16","富氢水杯",new BigDecimal("0.00"),new BigDecimal("2"));list.add(model0);Model model1=new Model("2105219900","中国南通1","果蔬肉类智能生态仪",null,new BigDecimal("20"));list.add(model1);Model model2=new Model("2105219900","中国南通1","果蔬肉类智能生态仪",new BigDecimal("100"),new BigDecimal("200"));list.add(model2);Model model3=new Model("21107273028","中国浙江18","量子眼镜",new BigDecimal("3"),new BigDecimal("4"));list.add(model3);Model model4=new Model("21107273028","中国浙江18","汽车负氧离子氧吧",new BigDecimal("5"),new BigDecimal("2"));list.add(model4);Model model5=new Model("21107563899","中国浙江19","天下美抗菌套盒",new BigDecimal("1"),new BigDecimal("7"));list.add(model5);Model model6=new Model("21108176372","中国浙江20","参元颗粒(0.8g*30瓶)",new BigDecimal("8"),new BigDecimal("3"));list.add(model6);Model model7=new Model("21107563899","中国浙江21","米饭脱糖仪",new BigDecimal("4"),new BigDecimal("6"));list.add(model7);Model model8=new Model("21107563899","中国浙江21","米饭脱糖1仪",new BigDecimal("4"),new BigDecimal("6"));list.add(model8);Model model=new Model();Map<String,String> map=getFieldAnnotation(model);byte[] bytes = toExcel(list, map, 1,new int[]{0,1},0);File file=new File("D:\\demo1.xls");if(file.exists()){file.delete();}FileOutputStream fos = new FileOutputStream(file);fos.write(bytes,0,bytes.length);fos.flush();fos.close();}
Model:
package com.eg.cdt.mon.model;import lombok.Data;import java.io.Serializable;import java.math.BigDecimal;import com.alibaba.excel.annotation.ExcelProperty;@Datapublic class Model implements Serializable {@ExcelProperty(value = {"订单号"},index = 1)private String orderNo;@ExcelProperty(value = {"厂家"},index = 2)private String changJia;@ExcelProperty(value = {"商品"},index = 3)private String goods;@ExcelProperty(value = {"价格1"},index = 4)private BigDecimal price1;@ExcelProperty(value = {"价格2"},index = 5)private BigDecimal price2;public Model() {}public Model(String orderNo, String changJia, String goods, BigDecimal price1, BigDecimal price2) {this.orderNo = orderNo;this.changJia = changJia;this.goods = goods;this.price1 = price1;this.price2 = price2;}}
public static Map<String, String> getFieldAnnotation(Object object) {Field[] fields = object.getClass().getDeclaredFields();Map<String, String> resultMap = new LinkedHashMap();List<Field> fieldList = Arrays.stream(fields).filter(field -> {ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);if (annotation != null && annotation.index() > 0) {field.setAccessible(true);return true;}return false;}).sorted(paring(field -> {int index = 0;ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);if (annotation != null) {index = annotation.index();}return index;})).collect(Collectors.toList());fieldList.forEach(e->{resultMap.put(e.getName(), e.getAnnotation(ExcelProperty.class).value()[0]);});return resultMap;}
如果觉得《POI Excel导出合并单元格》对你有帮助，请点赞、收藏，并留下你的观点哦！
