POI Excel导出合并单元格
程序员文章站
2024-02-19 11:10:28
...
/**
*
* @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("2201812011052199002","中国南通16","富氢水杯",new BigDecimal("0.00"),new BigDecimal("2"));
list.add(model0);
Model model1=new Model("220181201105219900","中国南通1","果蔬肉类智能生态仪",null,new BigDecimal("20"));
list.add(model1);
Model model2=new Model("220181201105219900","中国南通1","果蔬肉类智能生态仪",new BigDecimal("100"),new BigDecimal("200"));
list.add(model2);
Model model3=new Model("2201812011107273028","中国浙江18","量子眼镜",new BigDecimal("3"),new BigDecimal("4"));
list.add(model3);
Model model4=new Model("2201812011107273028","中国浙江18","汽车负氧离子氧吧",new BigDecimal("5"),new BigDecimal("2"));
list.add(model4);
Model model5=new Model("2201812011107563899","中国浙江19","天下美抗菌套盒",new BigDecimal("1"),new BigDecimal("7"));
list.add(model5);
Model model6=new Model("2201812011108176372","中国浙江20","参元颗粒(0.8g*30瓶)",new BigDecimal("8"),new BigDecimal("3"));
list.add(model6);
Model model7=new Model("2201812011107563899","中国浙江21","米饭脱糖仪",new BigDecimal("4"),new BigDecimal("6"));
list.add(model7);
Model model8=new Model("2201812011107563899","中国浙江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.saas.operator.common.model;
import lombok.Data;
import java.io.Serializable;
import java.math.BigDecimal;
import com.alibaba.excel.annotation.ExcelProperty;
@Data
public 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(Comparator.comparing(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;
}