java 使用 poi 导出Excel 根据相同内容动态合并单元格
程序员文章站
2024-02-28 18:45:58
...
import com.maidanli.common.utils.DateUtil;
import com.maidanli.common.utils.StringUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.ByteArrayOutputStream;
import java.lang.reflect.Method;
import java.util.*;
import java.util.Map.Entry;
public class OfficeUtil {
/**
*
* @param dataList 数据
* @param headNameMap 标题
* @param type 类型 1 xls 2 xlsx
* @param mergeIndex 需要合并的列 从1开始 0是序号
* @return
* @throws Exception
*/
public static byte[] toCourierExcel(List<?> dataList, Map<String, String> headNameMap, int type,int[] mergeIndex) 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("数据列表");
sheet.setColumnWidth(2, 25000);
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);
rowHead.createCell(0).setCellValue("序号");
Iterator<Entry<String, String>> iterator = headNameMap.entrySet().iterator();
int c = 1;
while (iterator.hasNext()) {
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);
row.createCell(0).setCellValue(i + 1);
for (int m = 0; m < methodList.size(); m++) {
Object value = methodList.get(m).invoke(object);
Cell cell = row.createCell(m + 1);
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 (StringUtil.isEmpty(str)){
str = row.getCell(mergeIndex[i]).getStringCellValue();
if (str.equals(sheet.getRow(2).getCell(1).getStringCellValue())){
strBeginIndex=row.getRowNum();
}
}else if (str.equals(row.getCell(mergeIndex[i]).getStringCellValue())){
if (strBeginIndex==0){
strBeginIndex=sheet.getRow(j-1).getRowNum();
}
if (sheet.getLastRowNum()==j){
//末尾合并
strBeginIndex =strBeginIndex-1;
strEndIndex =strEndIndex+1;
CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex, mergeIndex[i], mergeIndex[i]);
sheet.addMergedRegion(region);
}
}else if (!str.equals(row.getCell(mergeIndex[i]).getStringCellValue())){
strEndIndex=row.getRowNum();
if (start==0&&strBeginIndex>0&&strEndIndex>0){
//首行合并
CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex-1, mergeIndex[i], mergeIndex[i]);
sheet.addMergedRegion(region);
strBeginIndex=0;
start=1;
}else if (strBeginIndex>0&&strEndIndex>0){
//中间行合并
strEndIndex =strEndIndex-1;
CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex, mergeIndex[i], mergeIndex[i]);
sheet.addMergedRegion(region);
strBeginIndex=0;
}
str=row.getCell(mergeIndex[i]).getStringCellValue();
}
j++;
}
}
ByteArrayOutputStream baos = new ByteArrayOutputStream();
workbook.write(baos);
workbook.close();
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 = DateUtil.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());
}
}
}
}
public static void main(String[] args) throws Exception {
List<Model> list=new ArrayList<>();
Model model0=new Model("2201812011052199002","中国浙江15","富氢水杯");
list.add(model0);
Model model1=new Model("2201812011052199002","中国浙江17","果蔬肉类智能生态仪");
list.add(model1);
Model model2=new Model("2201812011052199002","中国南通16","果蔬肉类智能生态仪");
list.add(model2);
Model model3=new Model("2201812011107273028","中国浙江18","量子眼镜");
list.add(model3);
Model model4=new Model("2201812011107273028","中国浙江18","汽车负氧离子氧吧");
list.add(model4);
Model model5=new Model("2201812011107563899","中国浙江19","天下美抗菌套盒");
list.add(model5);
Model model6=new Model("2201812011108176372","中国浙江20","参元颗粒(0.8g*30瓶)");
list.add(model6);
Model model7=new Model("2201812011107563899","中国浙江21","米饭脱糖仪");
list.add(model7);
Map<String,String> map=new HashMap<>();
map.put("orderNo","订单号");
map.put("changjia","厂家");
map.put("goods","商品");
byte[] bytes = OfficeUtil.toCourierExcel(list, map, 1,new int[]{1,2});
File file=new File("D:\\demo.xls");
if(file.exists()){
file.delete();
}
FileOutputStream fos = new FileOutputStream(file);
fos.write(bytes,0,bytes.length);
fos.flush();
fos.close();
}
public class Model {
private String orderNo;
private String changjia;
private String goods;
public Model(String orderNo, String changjia, String goods) {
this.orderNo = orderNo;
this.changjia = changjia;
this.goods = goods;
}
public String getOrderNo() { return orderNo;
}
public void setOrderNo(String orderNo) {
this.orderNo = orderNo;
}
public String getChangjia() {
return changjia;
}
public void setChangjia(String changjia) {
this.changjia = changjia;
}
public String getGoods() {
return goods;
}
public void setGoods(String goods) {
this.goods = goods;
}
}