POIExcel导出合并单元格

/*** * &＃64;param dataList 数据* &＃64;param headNameMap 标题* &＃64;param type 类型 1 xls 2 xlsx* &＃64;param mergeIndex 需要合并的列 从1开始 0是序号* &＃64;param col 以哪一列为准进行合并* &＃64;return* &＃64;throws Exception*/public static byte[] toExcel(List dataList, Map headNameMap, int type,int[] mergeIndex,int col) throws Exception {Workbook workbook;if (type &＃61;&＃61; 1) {workbook &＃61; new XSSFWorkbook();} else if (type &＃61;&＃61; 2) {workbook &＃61; new SXSSFWorkbook();} else {workbook &＃61; new HSSFWorkbook();}List methodList &＃61; null;Sheet sheet &＃61; workbook.createSheet("数据列表");int index &＃61; sheet.getPhysicalNumberOfRows();for (int i &＃61; 0; i ();Row rowHead &＃61; sheet.createRow(index);Iterator> iterator &＃61; headNameMap.entrySet().iterator();int c &＃61; 0;while (iterator.hasNext()) {Map.Entry entry &＃61; iterator.next();for (int m &＃61; 0; m 0&&strEndIndex>0){strEndIndex &＃61;strEndIndex-1;if(strBeginIndex!&＃61;strEndIndex){//首行合并CellRangeAddress region &＃61; new CellRangeAddress(strBeginIndex, strEndIndex, mergeIndex[i], mergeIndex[i]);sheet.addMergedRegion(region);}strBeginIndex&＃61;0;start&＃61;1;}else if (strBeginIndex>0&&strEndIndex>0){//中间行合并strEndIndex &＃61;strEndIndex-1;if(strBeginIndex!&＃61;strEndIndex){CellRangeAddress region &＃61; new CellRangeAddress(strBeginIndex, strEndIndex, mergeIndex[i], mergeIndex[i]);sheet.addMergedRegion(region);}strBeginIndex&＃61;0;}str&＃61;row.getCell(col).getStringCellValue();}j&＃43;&＃43;;}}ByteArrayOutputStream baos &＃61; new ByteArrayOutputStream();workbook.write(baos);workbook.close();System.out.println("export finish!");return baos.toByteArray();}

private static Object getValue(Object value) {Object textValue &＃61; "";if (value !&＃61; null) {if (value instanceof Boolean) {textValue &＃61; (Boolean) value ? "是" : "否";} else if (value instanceof Date) {textValue &＃61; DateUtils.format((Date) value, "yyyy-MM-dd HH:mm:ss");} else if (value instanceof String) {String val &＃61; (String) value;textValue &＃61; StringUtil.isEmpty(val) || "null".equalsIgnoreCase(val) ? "" : val;} else {textValue &＃61; value;}}return textValue;}

private static void setCellValue(Cell cell, Object value) {if (value !&＃61; null) {if (value instanceof Integer) {cell.setCellValue((Integer) value);} else if (value instanceof Boolean) {Boolean booleanValue &＃61; (Boolean) value;cell.setCellValue(booleanValue);} else if (value instanceof Date) {Date dateValue &＃61; (Date) value;cell.setCellValue(dateValue);} else if (value instanceof Float) {Float floatValue &＃61; (Float) value;cell.setCellValue(floatValue);} else if (value instanceof Double) {Double doubleValue &＃61; (Double) value;cell.setCellValue(doubleValue);} else if (value instanceof Long) {Long longValue &＃61; (Long) value;cell.setCellValue(longValue);} else {cell.setCellValue(value.toString());}}}

test&＃xff1a;

public static void main(String[] args) throws Exception {List list&＃61;new ArrayList<>();Model model0&＃61;new Model("2201812011052199002","中国南通16","富氢水杯",new BigDecimal("0.00"),new BigDecimal("2"));list.add(model0);Model model1&＃61;new Model("220181201105219900","中国南通1","果蔬肉类智能生态仪",null,new BigDecimal("20"));list.add(model1);Model model2&＃61;new Model("220181201105219900","中国南通1","果蔬肉类智能生态仪",new BigDecimal("100"),new BigDecimal("200"));list.add(model2);Model model3&＃61;new Model("2201812011107273028","中国浙江18","量子眼镜",new BigDecimal("3"),new BigDecimal("4"));list.add(model3);Model model4&＃61;new Model("2201812011107273028","中国浙江18","汽车负氧离子氧吧",new BigDecimal("5"),new BigDecimal("2"));list.add(model4);Model model5&＃61;new Model("2201812011107563899","中国浙江19","天下美抗菌套盒",new BigDecimal("1"),new BigDecimal("7"));list.add(model5);Model model6&＃61;new Model("2201812011108176372","中国浙江20","参元颗粒(0.8g*30瓶)",new BigDecimal("8"),new BigDecimal("3"));list.add(model6);Model model7&＃61;new Model("2201812011107563899","中国浙江21","米饭脱糖仪",new BigDecimal("4"),new BigDecimal("6"));list.add(model7);Model model8&＃61;new Model("2201812011107563899","中国浙江21","米饭脱糖1仪",new BigDecimal("4"),new BigDecimal("6"));list.add(model8);Model model&＃61;new Model();Map map&＃61;getFieldAnnotation(model);byte[] bytes &＃61; toExcel(list, map, 1,new int[]{0,1},0);File file&＃61;new File("D:\\demo1.xls");if(file.exists()){file.delete();}FileOutputStream fos &＃61; new FileOutputStream(file);fos.write(bytes,0,bytes.length);fos.flush();fos.close();}

Model:

package com.eg.cdt.saas.operator.common.model;import lombok.Data;import java.io.Serializable;
import java.math.BigDecimal;
import com.alibaba.excel.annotation.ExcelProperty;&＃64;Data
public class Model implements Serializable {&＃64;ExcelProperty(value &＃61; {"订单号"},index &＃61; 1)private String orderNo;&＃64;ExcelProperty(value &＃61; {"厂家"},index &＃61; 2)private String changJia;&＃64;ExcelProperty(value &＃61; {"商品"},index &＃61; 3)private String goods;&＃64;ExcelProperty(value &＃61; {"价格1"},index &＃61; 4)private BigDecimal price1;&＃64;ExcelProperty(value &＃61; {"价格2"},index &＃61; 5)private BigDecimal price2;public Model() {}public Model(String orderNo, String changJia, String goods, BigDecimal price1, BigDecimal price2) {this.orderNo &＃61; orderNo;this.changJia &＃61; changJia;this.goods &＃61; goods;this.price1 &＃61; price1;this.price2 &＃61; price2;}
}

public static Map getFieldAnnotation(Object object) {Field[] fields &＃61; object.getClass().getDeclaredFields();Map resultMap &＃61; new LinkedHashMap();List fieldList &＃61; Arrays.stream(fields).filter(field -> {ExcelProperty annotation &＃61; field.getAnnotation(ExcelProperty.class);if (annotation !&＃61; null && annotation.index() > 0) {field.setAccessible(true);return true;}return false;}).sorted(Comparator.comparing(field -> {int index &＃61; 0;ExcelProperty annotation &＃61; field.getAnnotation(ExcelProperty.class);if (annotation !&＃61; null) {index &＃61; annotation.index();}return index;})).collect(Collectors.toList());fieldList.forEach(e->{resultMap.put(e.getName(), e.getAnnotation(ExcelProperty.class).value()[0]);});return resultMap;}