效果如下:
直接上代码，自己看
工具类：
import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.hj.chain.platform.vo.check.CheckFactorInfoVo;
import org.hj.chain.platform.vo.check.CheckFactorSubsetVo;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
@Slf4j
public class PoiUtil<T> {
private static final String MAIN_SHEET = "信息";
private static final String SHEET_MAP = "仪器";
/**
* 计算formula
*
* @param offset 偏移量，如果给0，表示从A列开始，1，就是从B列
* @param rowId 第几行
* @param colCount 一共多少列
* @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
*/
private static String getRange(int offset, int rowId, int colCount) {
char start = (char) ('A' + offset);
if (colCount <= 25) {
char end = (char) (start + colCount - 1);
return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
} else {
char endPrefix = 'A';
char endSuffix = 'A';
if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间，包括边界（仅两次字母表计算）
if ((colCount - 25) % 26 == 0) {// 边界值
endSuffix = (char) ('A' + 25);
} else {
endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
}
} else {// 51以上
if ((colCount - 25) % 26 == 0) {
endSuffix = (char) ('A' + 25);
endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
} else {
endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
endPrefix = (char) (endPrefix + (colCount - 25) / 26);
}
}
return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
}
}
//-----------------------------------------------------------
/**
* 导出三级联动and单个下拉框的excel
*
* @param name 文件名称
* @param headers 表头
* @param mapOneList 一级所有内容
* @param map 三级联动对应内容
* @param list 导出数据
*/
public static <T> void export(HttpServletResponse response, String name, List<String> headers, List<String> mapOneList, Map<String, LinkedList<String>> map, List<T> list) {
// 1.创建Excel
// 1)创建workbook
HSSFWorkbook hssfWorkBook = new HSSFWorkbook();
// 2)创建sheet
HSSFSheet mainSheet = hssfWorkBook.createSheet(MAIN_SHEET);// 主sheet
AtomicInteger add = new AtomicInteger();
//获取行数
for (int i = 0; i < list.size(); i++) {
CheckFactorInfoVo checkFactorInfoVo = (CheckFactorInfoVo) list.get(i);
if ("1".equals(checkFactorInfoVo.getIsFactor())) {
if (ObjectUtil.isNotEmpty(checkFactorInfoVo.getFactorSubsetVos())) {
add.addAndGet(checkFactorInfoVo.getFactorSubsetVos().size());
} else {
add.addAndGet(1);
}
} else {
add.addAndGet(1);
}
}
// 用于展示
//2 创建表头，供用户输入
initHeaders(hssfWorkBook, mainSheet, headers);
//导出数据到主sheet
setMainSheet(mainSheet, list);
//三级联动 sheet
HSSFSheet mapSheet = hssfWorkBook.createSheet(SHEET_MAP);
// true:隐藏/false:显示
//仪器关系sheet
hssfWorkBook.setSheetHidden(hssfWorkBook.getSheetIndex(mapSheet), true);// 设置sheet是否隐藏
// 3.写入数据
writeData(hssfWorkBook, mapSheet, mapOneList, map);// 将数据写入隐藏的sheet中并做好关联关系
// 4.设置数据有效性
setDataValid(hssfWorkBook, mainSheet, mapOneList, map, add.get());
response.reset();
try (OutputStream output = response.getOutputStream()) {
response.setContentType("application/octet-stream;charset=utf-8");
response.setHeader("Content-Disposition", "attachment; filename=" + name);
output.flush();
hssfWorkBook.write(output);
} catch (IOException e) {
log.error("导出失败!");
e.printStackTrace();
}
response.setCharacterEncoding("UTF-8");
}
/**
* 生成主页面表头
*
* @param wb
* @param mainSheet
* @param headers
*/
private static void initHeaders(HSSFWorkbook wb, HSSFSheet mainSheet, List<String> headers) {
//表头样式
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
//字体样式
HSSFFont fontStyle = wb.createFont();
fontStyle.setFontName("微软雅黑");
fontStyle.setFontHeightInPoints((short) 12);
fontStyle.setBold(true);
style.setFont(fontStyle);
//生成主内容
HSSFRow rowFirst = mainSheet.createRow(0);//第一个sheet的第一行为标题
mainSheet.createFreezePane(0, 1, 0, 1); //冻结第一行
CellRangeAddress region1 = new CellRangeAddress(0, 0, 5, 6);
CellRangeAddress region2 = new CellRangeAddress(0, 0, 9, 11);
mainSheet.addMergedRegion(region1);
mainSheet.addMergedRegion(region2);
//写标题
for (int i = 0; i < headers.size(); i++) {
HSSFCell cell;
if (i < 6) {
cell = rowFirst.createCell(i); //获取第一行的每个单元格
} else if (i >= headers.size() - 3) {
cell = rowFirst.createCell(i + 3);
} else {
cell = rowFirst.createCell(i + 1);
}
mainSheet.setColumnWidth(i, 4000); //设置每列的列宽
if (i == 3) {
mainSheet.setColumnWidth(i, 6000);
}
cell.setCellStyle(style); //加样式
cell.setCellValue(headers.get(i)); //往单元格里写数据
}
}
private static <T> void setMainSheet(HSSFSheet mainSheet, List<T> list) {
HSSFCellStyle style = mainSheet.getWorkbook().createCellStyle();
style.setVerticalAlignment(VerticalAlignment.CENTER); // 创建一个居中格式
HSSFFont font = mainSheet.getWorkbook().createFont();
font.setFontName("宋体");
style.setFont(font);
//同系套餐因子占用的总行数
AtomicInteger add = new AtomicInteger();
for (int j = 0; j < list.size(); j++) {
//行
Row row = mainSheet.createRow(j + 1 + add.get());
CheckFactorInfoVo obj = (CheckFactorInfoVo) list.get(j);
if (obj != null) {
//因子数据填充
if (!"1".equals(obj.getIsFactor())) {
for (int i = 0; i <= 13; i++) {
Cell cell = row.createCell(i);
cell.setCellStyle(style);
switch (i) {
case 0:
cell.setCellValue(obj.getSampleNo());
break;
case 1:
case 5:
cell.setCellValue(obj.getFactorName());
break;
case 2:
cell.setCellValue(obj.getStandardNo());
break;
case 3:
cell.setCellValue(obj.getStandardName());
break;
case 4:
cell.setCellValue(obj.getFactorRemark());
break;
case 6:
if (ObjectUtil.isNotNull(obj.getCheckRes())) {
JSONObject jsonObject = JSONObject.parseObject(obj.getCheckRes());
String v1 = jsonObject.getString("v1");
String v2 = jsonObject.getString("v2");
if (ObjectUtil.isNull(v2)|| "".equals(v2)) {
cell.setCellValue(v1);
} else {
String initial = getInitial(v1, v2);
cell.setCellValue(initial);
}
}
break;
case 7:
cell.setCellValue(obj.getUnitName());
break;
case 8:
if (ObjectUtil.isNotNull(obj.getRemark())) {
cell.setCellValue(obj.getRemark());
}
break;
case 9:
if (ObjectUtil.isNotNull(obj.getCheckEquipment())) {
JSONObject jsonObject = JSONObject.parseObject(obj.getCheckEquipment());
cell.setCellValue(jsonObject.getString("equipmentName"));
}
break;
case 10:
if (ObjectUtil.isNotNull(obj.getCheckEquipment())) {
JSONObject jsonObject = JSONObject.parseObject(obj.getCheckEquipment());
cell.setCellValue(jsonObject.getString("equipmentModel"));
}
break;
case 11:
if (ObjectUtil.isNotNull(obj.getCheckEquipment())) {
JSONObject jsonObject = JSONObject.parseObject(obj.getCheckEquipment());
cell.setCellValue(jsonObject.getString("equipmentNumber"));
}
break;
case 12:
cell.setCellValue(obj.getCheckFactorId());
break;
case 13:
cell.setCellValue("1".equals(obj.getIsFactor()) ? "是" : "否");
break;
}
}
} else {
//套餐内容填充
for (int i = 0; i < 5; i++) {
Cell cell = row.createCell(i);
cell.setCellStyle(style);
switch (i) {
case 0:
cell.setCellValue(obj.getSampleNo());
break;
case 1:
cell.setCellValue(obj.getFactorName());
break;
case 2:
cell.setCellValue(obj.getStandardNo());
break;
case 3:
cell.setCellValue(obj.getStandardName());
break;
case 4:
cell.setCellValue(obj.getFactorRemark());
break;
}
}
List<CheckFactorSubsetVo> factorSubsetVos = obj.getFactorSubsetVos();
row.createCell(12).setCellValue(obj.getCheckFactorId());
//套餐因子填充
if (ObjectUtil.isNotEmpty(factorSubsetVos)) {
for (int i = 0; i < factorSubsetVos.size(); i++) {
if (i != 0) {
row = mainSheet.createRow(j + 1 + add.get() + i);
}
//填充因子名称
Cell cell5 = row.createCell(5);
cell5.setCellValue(factorSubsetVos.get(i).getFactorName());
cell5.setCellStyle(style);
Cell cell7 = row.createCell(7);
cell7.setCellValue(factorSubsetVos.get(i).getUnitName());
cell7.setCellStyle(style);
Cell cell12 = row.createCell(12);
cell12.setCellValue(obj.getCheckFactorId());
cell12.setCellStyle(style);
Cell cell13 = row.createCell(13);
cell13.setCellValue("1".equals(obj.getIsFactor()) ? "是" : "否");
cell13.setCellStyle(style);
Cell cell14 = row.createCell(14);
cell14.setCellValue(factorSubsetVos.get(i).getId());
cell14.setCellStyle(style);
if (ObjectUtil.isNotNull(factorSubsetVos.get(i).getCheckSubRes())) {
JSONObject jsonObject = JSONObject.parseObject(factorSubsetVos.get(i).getCheckSubRes());
String v1 = jsonObject.getString("v1");
String v2 = jsonObject.getString("v2");
Cell cell6 = row.createCell(6);
if (ObjectUtil.isNull(v2)|| "".equals(v2)) {
cell6.setCellValue(v1);
} else {
String initial = getInitial(v1, v2);
cell6.setCellValue(initial);
}
cell6.setCellStyle(style);
}
if (ObjectUtil.isNotNull(obj.getRemark())) {
Cell cell8 = row.createCell(8);
cell8.setCellStyle(style);
cell8.setCellValue(obj.getRemark());
}
if (ObjectUtil.isNotNull(obj.getCheckEquipment())) {
JSONObject jsonObject = JSONObject.parseObject(obj.getCheckEquipment());
Cell cell9 = row.createCell(9);
cell9.setCellStyle(style);
cell9.setCellValue(jsonObject.getString("equipmentName"));
Cell cell10 = row.createCell(10);
cell10.setCellStyle(style);
cell10.setCellValue(jsonObject.getString("equipmentModel"));
Cell cell11 = row.createCell(11);
cell11.setCellStyle(style);
cell11.setCellValue(jsonObject.getString("equipmentNumber"));
}
}
}
//累加套餐因子所占行数
int oldAdd = add.getAndAdd(factorSubsetVos.size() - 1);
// 四个参数分别是：起始行、终止行、起始列、终止列.
CellRangeAddress region1 = new CellRangeAddress(j + oldAdd + 1, j + add.get() + 1, 0, 0);
CellRangeAddress region2 = new CellRangeAddress(j + oldAdd + 1, j + add.get() + 1, 1, 1);
CellRangeAddress region3 = new CellRangeAddress(j + oldAdd + 1, j + add.get() + 1, 2, 2);
CellRangeAddress region4 = new CellRangeAddress(j + oldAdd + 1, j + add.get() + 1, 3, 3);
CellRangeAddress region5 = new CellRangeAddress(j + oldAdd + 1, j + add.get() + 1, 4, 4);
CellRangeAddress region6 = new CellRangeAddress(j + oldAdd + 1, j + add.get() + 1, 8, 8);
CellRangeAddress region7 = new CellRangeAddress(j + oldAdd + 1, j + add.get() + 1, 9, 9);
CellRangeAddress region8 = new CellRangeAddress(j + oldAdd + 1, j + add.get() + 1, 10, 10);
CellRangeAddress region9 = new CellRangeAddress(j + oldAdd + 1, j + add.get() + 1, 11, 11);
mainSheet.addMergedRegion(region1);
mainSheet.addMergedRegion(region2);
mainSheet.addMergedRegion(region3);
mainSheet.addMergedRegion(region4);
mainSheet.addMergedRegion(region5);
mainSheet.addMergedRegion(region6);
mainSheet.addMergedRegion(region7);
mainSheet.addMergedRegion(region8);
mainSheet.addMergedRegion(region9);
}
}
mainSheet.setColumnHidden(12, true);
mainSheet.setColumnHidden(13, true);
mainSheet.setColumnHidden(14, true);
}
}
private static void setDataValid(HSSFWorkbook HSSFWorkBook, HSSFSheet mainSheet, List<String> firstList, Map<String, LinkedList<String>> siteMap, Integer line) {
//设置一级下拉
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper((HSSFSheet) mainSheet);
String[] dataArray = firstList.toArray(new String[0]);
HSSFSheet hidden = HSSFWorkBook.createSheet("hidden");
HSSFCell cell = null;
for (int i = 0, length = dataArray.length; i < length; i++) {
String name = dataArray[i];
HSSFRow row = hidden.createRow(i);
cell = row.createCell(0);
cell.setCellValue(name);
}
Name namedCell = HSSFWorkBook.createName();
namedCell.setNameName("hidden");
namedCell.setRefersToFormula("hidden!$A$1:$A$" + dataArray.length);
//加载数据,将名称为hidden的
DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");
// 四个参数分别是：起始行、终止行、起始列、终止列.
// 1 (一级下拉框代表从excel第1+1行开始) line(一级下拉框代表从excel第1+10行结束) 9(代表第几列开始，0是第一列，1是第二列) 1(代表第几列结束，0是第一列，1是第二列)
CellRangeAddressList firstRangeAddressList = new CellRangeAddressList(1, line, 9, 9);
DataValidation firstDataValidation = dvHelper.createValidation(constraint, firstRangeAddressList);
firstDataValidation.createErrorBox("error", "请选择正确");
firstDataValidation.setShowErrorBox(true);
// firstDataValidation.setSuppressDropDownArrow(true);
//将第二个sheet设置为隐藏
// true:隐藏/false:显示
HSSFWorkBook.setSheetHidden(HSSFWorkBook.getSheetIndex(hidden), true);// 设置sheet是否隐藏
// HSSFWorkBook.setSheetHidden(1, true);
mainSheet.addValidationData(firstDataValidation);
// 设置第二级、第三级下拉
// i <= 10 ,10代表第二级、第三级下拉框到10+1行结束
for (int i = 0; i <= 10; i++) {
setDataValidation('J', mainSheet, i + 1, 10);// "J"是指父类所在的列，i+1初始值为1代表从第2行开始，10要与“J”对应，为J的列号加1，假如第一个参数为“C”，那么最后一个参数就3
}
}
/**
* 设置有效性
*
* @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
* @param sheet
* @param rowNum 行数
* @param colNum 列数
*/
private static void setDataValidation(char offset, HSSFSheet sheet, int rowNum, int colNum) {
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);
DataValidation dataValidationList1;
DataValidation dataValidationList2;
dataValidationList1 = getDataValidationByFormula("INDIRECT($" + offset + (rowNum) + ")", rowNum, colNum, dvHelper);
dataValidationList2 = getDataValidationByFormula("INDIRECT($" + (char) (offset + 1) + (rowNum) + ")", rowNum, colNum + 1, dvHelper);
sheet.addValidationData(dataValidationList1);
sheet.addValidationData(dataValidationList2);
}
private static DataValidation getDataValidationByFormula(String formulaString, int naturalRowIndex, int naturalColumnIndex, HSSFDataValidationHelper dvHelper) {
DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formulaString);
CellRangeAddressList regions = new CellRangeAddressList(naturalRowIndex, 65535, naturalColumnIndex, naturalColumnIndex);
HSSFDataValidation data_validation_list = (HSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
data_validation_list.setEmptyCellAllowed(false);
if (data_validation_list instanceof HSSFDataValidation) {
// data_validation_list.setSuppressDropDownArrow(true);
data_validation_list.setShowErrorBox(true);
} else {
// data_validation_list.setSuppressDropDownArrow(false);
}
// 设置输入信息提示信息
data_validation_list.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");
return data_validation_list;
}
private static void writeData(HSSFWorkbook hssfWorkBook, HSSFSheet mapSheet, List<String> firstList, Map<String, LinkedList<String>> subMap) {
//循环将父数据写入siteSheet的第1行中
int siteRowId = 0;
HSSFRow firstRow = mapSheet.createRow(siteRowId++);
firstRow.createCell(0).setCellValue("父列表");
for (int i = 0; i < firstList.size(); i++) {
firstRow.createCell(i + 1).setCellValue(firstList.get(i));
}
// 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
Iterator<String> keyIterator = subMap.keySet().iterator();
while (keyIterator.hasNext()) {
String key = keyIterator.next();
List<String> son = subMap.get(key);
HSSFRow siteRow = mapSheet.createRow(siteRowId++);
siteRow.createCell(0).setCellValue(key);
for (int i = 0; i < son.size(); i++) {
siteRow.createCell(i + 1).setCellValue(son.get(i));
}
// 添加名称管理器
String range = getRange(1, siteRowId, son.size());
Name name = hssfWorkBook.createName();
name.setNameName(key);
String formula = mapSheet.getSheetName() + "!" + range;
name.setRefersToFormula(formula);
}
}
private static String getInitial(String a, String b) {
if (ObjectUtil.isNull(b)|| "".equals(b)) {
return a;
} else {
StringBuffer sb = new StringBuffer();
return sb.append(a).append("*").append("10^").append(b).toString();
}
}
}
使用方法:
根据自身业务进行组装数据
public void export(HttpServletResponse response, CheckFactorSearchVo searchVo) {
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");
String dateString = format.format(new Date());
//文件名称
String name = dateString+".xls";
//excel 表头
List<String> headers = Arrays.asList("样品编号","检测因子","标准号", "检测标准", "备注信息", "检测结果","单位","备注","检测设备","检测因子id","因子套餐","套餐因子id");
JSONArray tree = (JSONArray)equipmentInfoService.laboratoryEquipmentTree().getResult();
LinkedList<String> firstList = new LinkedList<>();
HashMap<String, LinkedList<String>> subMap = new HashMap();
tree.forEach( object->{
String firstName = ((JSONObject) object).getString("name");
firstList.add(firstName);
JSONArray children = ((JSONObject) object).getJSONArray("children");
LinkedList<String> subList = new LinkedList<>();
children.forEach( subObject ->{
String secondName = ((JSONObject) subObject).getString("name");
LinkedList<String> thirdList = new LinkedList<>();
subList.add(secondName);
JSONArray secondChildren = ((JSONObject) subObject).getJSONArray("children");
secondChildren.forEach( thirdObject->{
String thirdName = ((EquipmentTreeVo)thirdObject).getEquipmentNumber();
thirdList.add(thirdName);
});
subMap.put(secondName,thirdList);
});
subMap.put(firstName,subList);
});
PoiUtil.export(response,name,headers,firstList,subMap,factorInfoVos);
}
导出内容如下:
有两个隐藏的表格用来实现级联下拉
