所需依赖，注意这里的版本不能低于4.0，最好用4.1.2，或者高板本的也行
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>controller层
// 下载用户信息模板excel /user/downUserInfoTemp
@GetMapping(value = "/downUserInfoTemp")
public void downUserInfoTemp(HttpServletResponse response ){
response.addHeader("Content-Disposition", "attachment;filename=" + UriEncoder.encode("用户模板信息"+UUID.randomUUID()+".xls"));
byte[] bytes = userService.downUserInfoTemp( );
OutputStream out = null;
try {
out = new BufferedOutputStream(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
out.write(bytes);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}service层
import entity.SysDept;
import entity.SysMajor;
import entity.SysPost;
import entity.SysRole;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.io.*;
import java.util.*;
public class ExportExcel {
/**
* 存放下拉内容的excel隐藏工作表名称
*/
private static final String HIDDEN = "hidden";
/**
* excel 标题
*/
public static final String[] EXCEL_HEADER = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z",
"AA","AB","AC","AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ",
"BA","BB","BC","BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ"};
// 下载用户信息模板excel
public byte[] downUserInfoTemp() {
// excel 标题 用户名 姓名 性别 岗位 专业 归属部门id（从组织机构sheet中选择） 角色id（从系统角色sheet中选择，多个时用英文逗号分隔）
String[] header = new String[]{"用户名","姓名","性别","岗位id（从组织机构sheet中选择）","专业","归属部门id（从组织机构sheet中选择）","角色id（从组织机构sheet中选择）" };
// 字段下拉内容 key 标题 value Map key data 下拉内容 column 字段对应的列数
Map<String, Map<String, Object>> pullDownFields = new HashMap<>( );
if (header != null && header.length != 0) {
// 对需要下拉的字段单独处理
for (int i = 0; i < header.length; i++) {
List<Map<String, Object>> result = null;
// 存放下拉数据和标题的关联关系
Map<String, Object> filedInfo = new HashMap<>(2);
if (header[i].equals("性别")) {
// 固定从 sys_dict_data 获取下拉数据 获取性别
String[] data = new String[]{"男","女","未知"};
filedInfo.put("data", data);
filedInfo.put("column", i);
pullDownFields.put(header[i], filedInfo);
} else if (header[i].equals("专业")) {
SysMajor sysMajor = new SysMajor();
List<SysMajor> majorList = null; // sysMajorMapper.selectMajor(sysMajor);
if (majorList != null && majorList.size() != 0) {
String[] data =majorList.stream().map(x -> x.getName()).toArray(String[]::new);
filedInfo.put("data", data);
filedInfo.put("column", i);
pullDownFields.put(header[i], filedInfo);
}
}
}
}
HSSFWorkbook workbook = exportExcelPullDownMore(header, "用户信息模板", pullDownFields);
// 生成一个表格
HSSFSheet sheet = workbook.getSheetAt(0);
// 设置表格默认列宽度为35个字节
sheet.setDefaultColumnWidth((short) 35);
// 标题样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
//设置单元格格式为文本格式
HSSFDataFormat format = workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("@"));
// 设置文本居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 给每列设置默认的样式
for (int i = 0; i < header.length; i++) {
sheet.setDefaultColumnStyle(i, cellStyle);
}
// 创建第一行（标题格式）
HSSFRow row = sheet.createRow(0);
// 设置行高
row.setHeight((short) 700);
// 给标题设置字体样式
HSSFFont font = workbook.createFont();
font.setColor((short) 2);
//设置字体高
font.setFontHeight((short) 300);
// 设置字体大小
font.setFontHeightInPoints((short) 20);
// 标题样式
HSSFCellStyle style = workbook.createCellStyle();
// 产生表格标题行
for (int i = 0; i < header.length; i++) {
HSSFCell cell = row.createCell((short) i);
// 设置文本居中
style.setAlignment(HorizontalAlignment.CENTER);
style.setFont(font);
cell.setCellStyle(style);
sheet.setColumnWidth(i , 8000);
HSSFRichTextString text = new HSSFRichTextString(header[i]);
cell.setCellValue(text.toString());
}
// sheet 2 3 4
String[] headers3 = new String[]{"角色id","角色名称"};
String[] header4 = new String[]{"组织机构id","组织机构名称"};
String[] header5 = new String[]{"岗位d","岗位名称"};
// 系统角色
List<List<String>> resultListRole = new ArrayList<>();
List<SysRole> sysRoles = null; // roleMapper.selectAllSysRole();
for (SysRole sysRole : sysRoles) {
List<String> roleList = new ArrayList<>();
roleList.add( String.valueOf( sysRole.getRoleId() ) );
roleList.add( sysRole.getRoleName() );
resultListRole.add( roleList );
}
//组织机构
List<SysDept> sysDepts = null; // deptMapper.selectAllSysDept ( );
List<List<String>>resultListDept = new ArrayList<>();
for (SysDept sysDept : sysDepts) {
List<String> deptList = new ArrayList<>();
deptList.add( String.valueOf( sysDept.getDeptId() ) );
deptList.add( sysDept.getDeptName() );
resultListDept.add( deptList );
}
//岗位信息
List<SysPost> sysPosts = null; // postMapper.selectPostAll();
List<List<String>> resultListPost = new ArrayList<>();
for (SysPost sysPost : sysPosts) {
List<String> postList = new ArrayList<>();
postList.add( String.valueOf( sysPost.getPostId() ) );
postList.add( sysPost.getPostName() );
resultListPost.add( postList );
}
exportExcel(workbook, workbook.getNumberOfSheets(), "系统角色", headers3 , resultListRole);
exportExcel(workbook, workbook.getNumberOfSheets(), "组织机构", header4 , resultListDept);
exportExcel(workbook, workbook.getNumberOfSheets(), "岗位信息", header5 , resultListPost);
ByteArrayOutputStream byteStream = new ByteArrayOutputStream(1024);
try {
workbook.write(byteStream);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
byteStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return byteStream.toByteArray();
}
/**
* @Title: exportExcel
* @Description: 导出Excel的方法
* @author: liuct @ 2019-01-29
* @param workbook
* @param sheetNum (sheet的位置，0表示第一个表格中的第一个sheet)
* @param sheetTitle （sheet的名称）
* @param headers （表格的标题）
* @param result （表格的数据）
* @throws Exception
*/
public void exportExcel(HSSFWorkbook workbook, int sheetNum, String sheetTitle, String[] headers , List<List<String>> result) {
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(sheetNum, sheetTitle);
// 设置表格默认列宽度为35个字节
sheet.setDefaultColumnWidth((short) 35);
// 标题样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
//设置单元格格式为文本格式
HSSFDataFormat format = workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("@"));
// 设置文本居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 给每列设置默认的样式
for (int i = 0; i < headers.length; i++) {
sheet.setDefaultColumnStyle(i, cellStyle);
}
// 创建第一行（标题格式）
HSSFRow row = sheet.createRow(0);
// 设置行高
row.setHeight((short) 700);
// 给标题设置字体样式
HSSFFont font = workbook.createFont();
font.setColor((short) 2);
//设置字体高
font.setFontHeight((short) 300);
// 设置字体大小
font.setFontHeightInPoints((short) 20);
// 标题样式
HSSFCellStyle style = workbook.createCellStyle();
// 产生表格标题行
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell((short) i);
// 设置文本居中
style.setAlignment(HorizontalAlignment.CENTER);
style.setFont(font);
cell.setCellStyle(style);
sheet.setColumnWidth(i , 8000);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text.toString());
}
// 遍历集合数据，产生数据行
if (result != null) {
int index = 1;
//遍历获取行
for (List<String> m : result) {
// 行数据
HSSFRow rows = sheet.createRow(index);
int cellIndex = 0;
//遍历行 获取列
for (String str : m) {
HSSFCell cell = rows.createCell((short) cellIndex);
cell.setCellValue(str.toString());
cellIndex++;
}
index++;
}
}
}
/**
* @return org.apache.poi.hssf.usermodel.HSSFWorkbook
* @Author fatCountry
* @Description 导出带下拉框的HSSFWorkbook 下拉内容可以超过255字节
* @Date 10:39 2021/9/23
* @Param [tableHeader 标题 , sheetName 工作表名, pullDownFields 字段下拉内容 key 标题 value Map key data 下拉内容 column 字段对应的列数]
**/
public static HSSFWorkbook exportExcelPullDownMore(String[] tableHeader, String sheetName, Map<String, Map<String, Object>> pullDownFields) {
short cellNumber = (short) tableHeader.length;
// 创建excel文件对象
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建excel中新的sheet工作表
HSSFSheet sheet = workbook.createSheet(sheetName);
// 单元格设置样式cellStyle
HSSFCellStyle cellStyle = workbook.createCellStyle();
//设置单元格格式为文本格式
HSSFDataFormat format = workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("@"));
HorizontalAlignment center = HorizontalAlignment.CENTER;
// 设置文本居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 给每列设置默认的样式
for (int i = 0; i < tableHeader.length; i++) {
sheet.setDefaultColumnStyle(i, cellStyle);
}
// 设置标题以及样式
setExcelStyle(tableHeader, cellNumber, workbook, sheet);
// dictTable下拉生成
if (pullDownFields != null && pullDownFields.size() != 0) {
for (int i = 0; i < tableHeader.length; i++) {
if (pullDownFields.containsKey(tableHeader[i])) {
// 创建隐藏的工作表 存放下拉内容
HSSFSheet hidden = workbook.createSheet(HIDDEN+i);
// 通过标题获取下拉内容
Map<String, Object> stringObjectMap = pullDownFields.get(tableHeader[i]);
String[] data = (String[])stringObjectMap.get("data");
// 将下拉内容存到隐藏的工作表中
for (int i1 = 0; i1 < data.length; i1++) {
HSSFRow row = hidden.createRow(i1);
row.createCell(i).setCellValue(data[i1]);
}
// 创建名称，可被其他单元格引用
Name namedCell = workbook.createName();
namedCell.setNameName(HIDDEN+i);
// 设置名称引用的公式
namedCell.setRefersToFormula(HIDDEN+i+"!$"+EXCEL_HEADER[i]+"$1:$"+EXCEL_HEADER[i]+"$"+data.length);
// 加载特定名称的数据
DVConstraint constraint = DVConstraint.createFormulaListConstraint(HIDDEN+i);
// 设置下拉框的作用区域 (开始行数，结束行数，开始列数，结束列数)
CellRangeAddressList regions = new CellRangeAddressList(1, 10000, (Integer) stringObjectMap.get("column"), (Integer) stringObjectMap.get("column"));
// 绑定下拉框和作用区域
HSSFDataValidation data_validation = new HSSFDataValidation(regions, constraint);
// 对sheet页生效
sheet.addValidationData(data_validation);
}
}
}
// 循环将存储下拉内容的sheet隐藏
for (int i = 1; i < workbook.getNumberOfSheets(); i++) {
workbook.setSheetHidden(i,true);
}
return workbook;
}
/**
* 设置 单元格样式
*
* @return void
* @Author fatCountry
* @Description
* @Date 10:39 2021/9/23
* @Param [tableHeader, cellNumber, workbook, sheet]
**/
public static void setExcelStyle(String[] tableHeader, short cellNumber, HSSFWorkbook workbook, HSSFSheet sheet) {
// 创建第一行
HSSFRow row = sheet.createRow(0);
// 设置行高
row.setHeight((short) 400);
// 给标题设置字体样式
HSSFFont font = workbook.createFont();
font.setColor((short) Short.MAX_VALUE);
font.setFontHeight((short) 350);
// 标题设置样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
// 循环创建单元格 设置标题 以及样式
for (int k = 0; k < cellNumber; k++) {
HSSFCell cell = row.createCell(k);
cell.setCellValue(tableHeader[k]);
sheet.setColumnWidth(k, 8000);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
}
}
}本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
