//带联动要下拉框的所在列(参数5)
List<Integer> indexList = new ArrayList<>();
indexList.add(4);
indexList.add(5);
//必填列所在列list(参数6)
List<Integer> requiredList = new ArrayList<>();
requiredList.add(0);
requiredList.add(1);
//空的list(参数6)
List list1 = new ArrayList();
//参数1：表头list
//参数2：Map<一级的列数，一级的List>
//参数3：Map<一级的名称，关联二级的List>
//参数4：空的list
//参数5：带联动要下拉框的所在列list（目的设置内容准确性校验和填充数据）
//参数6：必填列所在列list（目的表头显示*号）
//参数7：不带联动但是有下拉框的数据，Map<所在列，下拉框数据List>
HSSFWorkbook workbook = PoiUtils.export(headers, oneMap, map, list1, indexList, requiredList, recommendLevelList, functionTypelList);
//将excel写入，这个工具类好像只支持.xls的。希望有大佬优化，现在模板下出来太大
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "UTF-8"));
OutputStream os = null;
try {
os = response.getOutputStream();
workbook.write(os);
os.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
IOUtils.closeQuietly(os);
}
package com.iflytek.site.common.utils.excel;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.util.*;
/**
* 导入模板联动工具类
* @author fmzhang2
* @date 2022/7/7
**/
public class PoiUtils {
private static final String MAIN_SHEET = "软件信息";
private static final String SHEET_MAP = "map";
private static final String ATTRIBUTE_STATUS = "attribute";
/**
* 计算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;
}
}
/**
* 导出二级联动or三级联动or单个下拉框的excel
* @param headers 表头list
* @param mapOneList Map<一级的列数，一级的List>
* @param map Map<一级的名称，关联二级的List>
* @param list 空的list
* @param indexList 带联动要下拉框的所在列list（目的设置内容准确性校验和填充数据）
* @param requiredList 必填列所在列list（目的表头显示*号）
* @param dataList 不带联动但是有下拉框的数据，Map<所在列，下拉框数据List>
* @author fmzhang2
* @date 2022/8/4
**/
public static HSSFWorkbook export(List<String> headers, Map<Integer,List<String>> mapOneList, Map<String, List<String>> map, List list,List<Integer> indexList,List<Integer> requiredList,Map<Integer,List<String>>... dataList) {
// List<String> licenses,List<String> isOpens
// 属性单个下拉数据
// 2.创建Excel
// 1)创建workbook
HSSFWorkbook workbook = new HSSFWorkbook();
// 2)创建sheet
HSSFSheet sheet = workbook.createSheet(MAIN_SHEET);// 主sheet
// 用于展示
//2.1 创建表头，供用户输入
initHeaders(workbook, sheet, headers,requiredList);
//导出数据到主sheet
setMainSheet(sheet, list);
//三级联动 sheet
HSSFSheet mapSheet = workbook.createSheet(SHEET_MAP);
// true:隐藏/false:显示
//省市区关系sheet
workbook.setSheetHidden(workbook.getSheetIndex(mapSheet), true);// 设置sheet是否隐藏
// 3.写入数据
writeData(workbook, mapSheet, map);// 将数据写入隐藏的sheet中并做好关联关系
for (int i = 0; i < dataList.length; i++) {
HSSFSheet attributeSheet = workbook.createSheet(ATTRIBUTE_STATUS+i);
//单个下拉框sheet
workbook.setSheetHidden(workbook.getSheetIndex(attributeSheet), true);// 设置sheet是否隐藏
//3.1 写入单个下拉数据
writeDropDownData(workbook, attributeSheet, dataList[i], ATTRIBUTE_STATUS+i);
}
setDataValid(workbook, sheet, mapOneList,indexList);
return workbook;
}
/**
* 生成主页面表头
*
* @param wb
* @param sheet
* @param headers 表头
* @param requiredList 必填列的list
*
*/
private static void initHeaders(HSSFWorkbook wb, HSSFSheet sheet, List<String> headers,List<Integer> requiredList) {
//表头样式
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
// 顶边栏
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
// 右边栏
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
// 底边栏
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
// 左边栏
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
//单元格背景色
style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//字体样式
HSSFFont fontStyle = wb.createFont();
fontStyle.setFontName("微软雅黑");
fontStyle.setFontHeightInPoints((short) 12);
// fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(fontStyle);
//生成主内容
HSSFRow row = sheet.createRow(0);//第一个sheet的第一行为标题
sheet.createFreezePane(0, 1, 0, 1); //冻结第一行
//写标题
String value=null;
for (int i = 0; i < headers.size(); i++) {
HSSFCell cell = row.createCell(i); //获取第一行的每个单元格
sheet.setColumnWidth(i, 4000); //设置每列的列宽
cell.setCellStyle(style); //加样式
value = headers.get(i);
HSSFFont font1 = wb.createFont();
font1.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
for (int integer : requiredList) {
if(i == integer){
HSSFRichTextString richString = new HSSFRichTextString("*"+value);
HSSFFont font = wb.createFont();
font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
//0到1 表示星号设置为红色
richString.applyFont(0,1,font);
//1到字符串长度 表示其他部分设置为黑色
richString.applyFont(1, value.length()+1,font1);
cell.setCellValue(richString);
break;
}else {
HSSFRichTextString richString = new HSSFRichTextString(value);
richString.applyFont(0, value.length(), font1);
cell.setCellValue(richString);
}
}
}
}
private static void setMainSheet(HSSFSheet mainSheet, List list) {
for (int j = 0; j < list.size(); j++) {
//行
Row row = mainSheet.createRow(j + 1);
Object obj = list.get(j);
if (obj != null) {
Field[] fields = obj.getClass().getDeclaredFields();
try {
for (int i = 0; i < fields.length; i++) {
if (i == 0) {
//excel 第一列 为序号
Cell cell = row.createCell(i);
cell.setCellValue(j + 1);
} else {
Field f = fields[i];
f.setAccessible(true);
String name = f.getName();
Object value = f.get(obj);
if (value == null) {
value = "";
}
//列
Cell cell = row.createCell(i);
cell.setCellValue(value.toString());
}
}
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
}
private static void setDataValid(HSSFWorkbook workbook, HSSFSheet mainSheet, Map<Integer, List<String>> headMapList,List<Integer> indexList) {
//设置省份下拉
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper((HSSFSheet) mainSheet);
List<String> headList=new ArrayList<>();
//一级联动所在的列，默认为0
Integer index=0;
for (Integer s : headMapList.keySet()) {
headList.addAll(headMapList.get(s));
index=s;
}
String[] dataArray = headList.toArray(new String[0]);
HSSFSheet hidden = workbook.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 = workbook.createName();
namedCell.setNameName("hidden");
namedCell.setRefersToFormula("hidden!$A$1:$A$" + dataArray.length);
//加载数据,将名称为hidden的
DataValidationConstraint constraint = dvHelper.createFormulaListConstraint("hidden");
// 四个参数分别是：起始行、终止行、起始列、终止列.
// 1 (省份下拉框代表从excel第1+1行开始) 10(省份下拉框代表从excel第1+10行结束) 1(代表第几列开始，0是第一列，1是第二列) 1(代表第几列结束，0是第一列，1是第二列)
CellRangeAddressList provinceRangeAddressList = new CellRangeAddressList(1, 60000, index, index);
DataValidation provinceDataValidation = dvHelper.createValidation(constraint, provinceRangeAddressList);
provinceDataValidation.createErrorBox("Error", "请选择或输入有效的选项!");
provinceDataValidation.setShowErrorBox(true);
// provinceDataValidation.setSuppressDropDownArrow(true);
//将第二个sheet设置为隐藏
// true:隐藏/false:显示
workbook.setSheetHidden(workbook.getSheetIndex(hidden), true);// 设置sheet是否隐藏
// HSSFWorkBook.setSheetHidden(1, true);
mainSheet.addValidationData(provinceDataValidation);
for (int i = 0; i < indexList.size(); i++) {
CellRangeAddressList attributeRangeAddressList=null;
DataValidationConstraint attributeConstraint = dvHelper.createFormulaListConstraint(ATTRIBUTE_STATUS+i);
// 四个参数分别是：起始行、终止行、起始列、终止列
// 1 (下拉框代表从excel第1+1行开始) 10(下拉框代表从excel第1+10行结束) 5(代表第几列开始，0是第一列，1是第二列) 5(代表第几列结束，0是第一列，1是第二列)
attributeRangeAddressList = new CellRangeAddressList(1, 60000, indexList.get(i), indexList.get(i));
HSSFDataValidation attributeDataValidation = (HSSFDataValidation) dvHelper.createValidation(attributeConstraint, attributeRangeAddressList);
attributeDataValidation.createErrorBox("Error", "请选择或输入有效的选项!");
// genderDataValidation.setSuppressDropDownArrow(true);
mainSheet.addValidationData(attributeDataValidation);
}
// 设置市、区下拉
// i <= 10 ,10代表市、区下拉框到10+1行结束
for (int i = 0; i <= 60000; i++) {
setDataValidation('C', mainSheet, i + 1, 3);// "B"是指父类所在的列，i+1初始值为1代表从第2行开始，2要与“B”对应，为B的列号加1，假如第一个参数为“C”，那么最后一个参数就3
}
}
/*titleStyle.setFillForegroundColor(IndexedColors.RED1.getIndex())*
* 设置有效性
*
* @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("Error", "请选择或输入有效的选项!");
return data_validation_list;
}
/**
* 循环单个下拉框的数据写入genderSheet的第A列中
* @param workbook
* @param sheet
* @param listMap
* @param name
* @author fmzhang2
* @date 2022/8/4
**/
private static void writeDropDownData(HSSFWorkbook workbook, HSSFSheet sheet, Map<Integer,List<String>> listMap, String name) {
//循环单个下拉框的数据写入genderSheet的第A列中
List<?> list=new ArrayList<>();
for (Integer integer : listMap.keySet()) {
list = listMap.get(integer);
}
for (int i = 0; i < list.size(); i++) {
HSSFRow genderRow = sheet.createRow(i);
genderRow.createCell(0).setCellValue(list.get(i).toString());
}
initGenderMapping(workbook, sheet.getSheetName(), list.size(), name);// 创建数据规则
}
private static void writeData(HSSFWorkbook workbook, HSSFSheet mapSheet,Map<String, List<String>> siteMap) {
//循环将父数据写入siteSheet的第1行中
int siteRowId = 0;
// HSSFRow provinceRow = mapSheet.createRow(siteRowId++);
// provinceRow.createCell(0).setCellValue("父列表");
// for (int i = 0; i < provinceList.size(); i++) {
// provinceRow.createCell(i + 1).setCellValue(provinceList.get(i));
// }
// 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
Iterator<String> keyIterator = siteMap.keySet().iterator();
while (keyIterator.hasNext()) {
String key = keyIterator.next();
List<String> son = siteMap.get(key);
if (CollectionUtils.isNotEmpty(son)) {
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 = workbook.createName();
name.setNameName(key);
String formula = mapSheet.getSheetName() + "!" + range;
name.setRefersToFormula(formula);
}
}
}
/**
* 创建下拉数据规则
*
* @param workbook
* @param genderSheetName
* @param genderQuantity
* @param name
*/
private static void initGenderMapping(HSSFWorkbook workbook, String genderSheetName, int genderQuantity, String name) {
Name genderName = workbook.createName();
genderName.setNameName(name);
genderName.setRefersToFormula(genderSheetName + "!$A$1:$A$" + genderQuantity);
}
// 测试时间格式
private static void setDateFormat(HSSFWorkbook HSSFWorkbook, HSSFSheet mainsheet) {
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(mainsheet);
// 四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(1, 60, 4, 4);
DataValidationConstraint dvConstraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "1900-01-01", "5000-12-31", "yyyy-MM-dd");
HSSFDataValidation dataValidation = (HSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
// dataValidation.setSuppressDropDownArrow(false);
dataValidation.createPromptBox("输入提示", "请填写日期格式'yyyy-mm-dd'");
dataValidation.setShowPromptBox(true);
dataValidation.createErrorBox("日期格式错误提示", "你输入的日期格式不符合'yyyy-mm-dd'格式规范，请重新输入！");
dataValidation.setShowErrorBox(true);
mainsheet.addValidationData(dataValidation);
}
// 测试数字规则
private static void setNumberFormat(HSSFWorkbook HSSFWorkbook, HSSFSheet mainsheet, List<Integer> colNum) {
if (colNum.size() > 0) {
for (Integer index : colNum) {
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(mainsheet);
// 四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(1, 60, index, index);
DataValidationConstraint dvConstraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL, DataValidationConstraint.OperatorType.BETWEEN, "0.0001", "100000");
HSSFDataValidation dataValidation = (HSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
// dataValidation.setSuppressDropDownArrow(false);
dataValidation.createPromptBox("", "请填写数字(至多4位小数)!");
dataValidation.setShowPromptBox(true);
dataValidation.createErrorBox("数字格式错误提示", "你输入的数字不正确,请重新输入!");
dataValidation.setShowErrorBox(true);
mainsheet.addValidationData(dataValidation);
}
}
}
/**
* 导入文件
*
* @param inputStream
* @return
* @throws Exception
*/
public static List importExcel(InputStream inputStream) throws Exception {
List returnList = new ArrayList();
//根据指定的文件输入流导入Excel从而产生Workbook对象
Workbook wb0 = new HSSFWorkbook(inputStream);
//获取Excel文档中的第一个表单
Sheet sht0 = wb0.getSheetAt(0);
//对Sheet中的每一行进行迭代
List rowList = null;
for (Row r : sht0) {
//如果当前行的行号（从0开始）未达到2（第三行）则从新循环
if (r.getRowNum() < 1) {
continue;
}
rowList = new ArrayList();
//非空判断
Cell c0 = r.getCell(0, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (c0 != null) {
// 将区域编号的cell中的内容当做字符串处理
r.getCell(0).setCellType(CellType.STRING);
rowList.add(r.getCell(0).getStringCellValue());
}
Cell c1 = r.getCell(1, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (c1 != null) {
r.getCell(1).setCellType(CellType.STRING);
rowList.add(r.getCell(1).getStringCellValue());
} else {
break;
}
Cell c2 = r.getCell(2, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (c2 != null) {
r.getCell(2).setCellType(CellType.STRING);
rowList.add(r.getCell(2).getStringCellValue());
}
Cell c3 = r.getCell(3, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (c3 != null) {
r.getCell(3).setCellType(CellType.STRING);
rowList.add(r.getCell(3).getStringCellValue());
}
Cell c4 = r.getCell(4, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (c4 != null) {
r.getCell(4).setCellType(CellType.STRING);
rowList.add(r.getCell(4).getStringCellValue());
}
Cell c5 = r.getCell(5, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (c5 != null) {
r.getCell(5).setCellType(CellType.STRING);
rowList.add(r.getCell(5).getStringCellValue());
}
returnList.add(rowList);
}
return returnList;
}
/**
* 下载文件
*
* @param response
* @param request
* @param filePath 文件地址
* @throws Exception
*/
public static void downloadFile(HttpServletResponse response, HttpServletRequest request, String filePath) {
try {
//获取文件
File file = new File(filePath);
String fileName = file.getName();
response.reset();
ServletOutputStream out = response.getOutputStream();
request.setCharacterEncoding("UTF-8");
int BUFFER = 1024 * 10;
byte data[] = new byte[BUFFER];
BufferedInputStream bis = null;
//获取文件输入流
InputStream inputStream = new BufferedInputStream(new FileInputStream(filePath));
// 以流的形式下载文件。
DataInputStream fis = new DataInputStream(inputStream);
int read;
bis = new BufferedInputStream(fis, BUFFER);
response.setContentType("application/OCTET-STREAM");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
while ((read = bis.read(data)) != -1) {
out.write(data, 0, read);
}
fis.close();
bis.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
File file = new File(filePath);
//删除临时文件
if (file.exists()) {
file.delete();
}
}
}
}
一二级联动poi下载模板
最新推荐文章于 2024-11-13 13:42:35 发布
扫一扫
1202
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
