1、pom.xml 引入依赖
<!--导出Excel -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>
2、拆分list集合，数据达到一定数量分sheet页
public static List<List<String[]>> Lists(List<String[]> list, int len) {
if (list == null || list.size() == 0 || len < 1) {
return null;
}
List<List<String[]>> result = new ArrayList<List<String[]>>();
int size = list.size();
int count = (size + len - 1) / len;
for (int i = 0; i < count; i++) {
List<String[]> subList = list.subList(i * len, ((i + 1) * len > size ? size : len * (i + 1)));
result.add(subList);
}
return result;
}
3、导出功能
/**
* 带合并单元格的导出
* @param excelName sheet表名称
* @param fields 表头的定义集合
* @param dataList 数据集合
* @return workbook
*/
@SuppressWarnings("deprecation")//不检测过期方法
public static HSSFWorkbook setExportExcelMerge(HSSFWorkbook wb,String excelName, String[] fields, List<String[]> dataList){
//HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(excelName);
//sheet.createFreezePane(0, 1, 0, 1);//窗口冻结
/*CellRangeAddress cra = new CellRangeAddress(0, 1, 0, fields.length - 1);
sheet.setAutoFilter(cra);//自动筛选*/
//在sheet中添加表头
HSSFRow row = sheet.createRow(0);
//创建单元格，并设置值表头
HSSFCellStyle cellHeadStyle = wb.createCellStyle();
cellHeadStyle.setAlignment(HorizontalAlignment.CENTER);
HSSFFont fontAt = wb.getFontAt(0);
fontAt.setCharSet(HSSFFont.DEFAULT_CHARSET);
fontAt.setFontHeightInPoints((short) 12);//更改默认字体大小
fontAt.setFontName("宋体");
fontAt.setBold(true);//加粗
cellHeadStyle.setFont(fontAt);
//设置列表内容样式
HSSFCellStyle cellBodyStyle = wb.createCellStyle();
cellBodyStyle.setVerticalAlignment(VerticalAlignment.CENTER); //上下居中
HSSFFont fontAt2 = wb.createFont();
fontAt2.setCharSet(HSSFFont.DEFAULT_CHARSET);
fontAt2.setFontHeightInPoints((short) 12);//更改默认字体大小
fontAt2.setFontName("宋体");
fontAt2.setBold(false);
cellBodyStyle.setFont(fontAt2);
HSSFCell cell =null;
//设置表头
if(fields != null && fields.length>0){
for (int i = 0; i <fields.length ; i++) {
cell = row.createCell(i);
cell.setCellValue(fields[i]);
cell.setCellStyle(cellHeadStyle);
//sheet.autoSizeColumn(i);
if(i==1 ){
sheet.setColumnWidth(i,2500);
}else if(i==0 || i==2){
sheet.setColumnWidth(i,6500);
}else if(i==3){
sheet.setDefaultColumnWidth(60);
}
}
}
Pattern pattern = Pattern.compile("^\\d+$");
int num = 1; //相同用户数量标记
int startRow = 0; //合并单元格数据开始行
//写入实体数据
if(dataList !=null && dataList.size()>0){
for (int i = 0; i <dataList.size() ; i++) {
row = sheet.createRow(i+1);
String[] arr = dataList.get(i);
//创建单元格，并设置值
for (int j = 0; j < arr.length; j++) {
cell = row.createCell(j);
cell.setCellStyle(cellBodyStyle);
if(arr[j] != null){
Matcher matcher = pattern.matcher(arr[j]);
if(matcher.matches()){
cell.setCellValue(Double.parseDouble(arr[j]));
}else{
cell.setCellValue(arr[j]);
}
}else{
cell.setCellValue(arr[j]);
}
//sheet.autoSizeColumn(j);//内容自适应,加上导出变慢
}
//合并单元格，名称和上一条比较是否相同，行数：i代表上一行，i+1代表当前行；数据：i代表当前数据，i-1代表上一行数据。
if(i>0){
if(dataList.get(i)[0].equals(dataList.get(i-1)[0])){
//判断是否是最后一行数据
if(i==dataList.size()-1){
if(num>=1){//判断之前是否有重复名称
startRow = i+1-num;
//合并之前相同用户单元格加上最后一行
CellRangeAddress region = new CellRangeAddress(startRow,i+1,0,0);//起始行,结束行,起始列,结束列
CellRangeAddress region1 = new CellRangeAddress(startRow,i+1,1,1);//起始行,结束行,起始列,结束列
sheet.addMergedRegion(region);
sheet.addMergedRegion(region1);
sheet.getRow(startRow).getCell(0).setCellValue(dataList.get(i-1)[0]);
sheet.getRow(startRow).getCell(1).setCellValue(dataList.get(i-1)[1]);
}
}else{
//看名称重复出现几次
num +=1;
}
}else{
//判断之前是否有重复名称
if(num>1){
startRow = i+1-num;
//合并之前相同用户单元格
CellRangeAddress region = new CellRangeAddress(startRow,i,0,0);//起始行,结束行,起始列,结束列
CellRangeAddress region1 = new CellRangeAddress(startRow,i,1,1);//起始行,结束行,起始列,结束列
sheet.addMergedRegion(region);
sheet.addMergedRegion(region1);
sheet.getRow(startRow).getCell(0).setCellValue(dataList.get(i-1)[0]);
sheet.getRow(startRow).getCell(1).setCellValue(dataList.get(i-1)[1]);
}
//重置num
num = 1;
}
}
}
}
return wb;
}
4、执行导出
/**
* @param workbook 取上边工具类方法的 webbook
* @param fileName 导出Excel的文件名称
* @throws IOException
*/
public static void beginExportExcel(HttpServletRequest request, HttpServletResponse response, HSSFWorkbook workbook, String fileName){
OutputStream os = null;
try {
response.reset();
fileName = fileName +new SimpleDateFormat("yyyyMMdd").format(new Date());
String userAgent = request.getHeader("user-agent").toLowerCase();
if (userAgent.contains("msie") || userAgent.contains("like gecko") ) {
// win10 ie edge 浏览器 和其他系统的ie
fileName = URLEncoder.encode(fileName, "UTF-8");
} else {
fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
}
response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
os = response.getOutputStream();
workbook.write(os);
} catch (Exception e) {
throw new RuntimeException("导出异常");
}finally {
try {
os.flush();
workbook.close();
}catch (Exception e){
throw new RuntimeException("导出异常");
}
}
}
5、开始导出
//定义表头
String[] roleHead = {"角色名称","角色状态","报表名称","报表路径"};
List<String[]> roleDatalist = new ArrayList<>();
if(list.size()>0 && list !=null){
for(Map<String, Object> m:list){
String[] dataArr = new String[roleHead.length];
dataArr[0] = m.get("ROLE_NAME")!=null?m.get("ROLE_NAME").toString():"-";
dataArr[1] = m.get("STATE")!=null? (m.get("STATE").toString().equals("0")?"启用":"停用"):"-";
dataArr[2] = m.get("MENU_NAME")!=null?m.get("MENU_NAME").toString():"-";
dataArr[3] = m.get("PATH")!=null?m.get("PATH").toString():"-";
roleDatalist.add(dataArr);
}
}
//每个sheet页数据达到60000条时分页
List<List<String[]>> partition = ExcelUtil.Lists(roleDatalist, 60000);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFWorkbook workbook = null;
if(partition !=null && partition.size()>0){
for(int i=0;i<partition.size();i++){
//往Excel存数
workbook = ExcelUtil.setExportExcelMerge(wb,"sheet_"+(i+1), roleHead,partition.get(i));
}
}
//开始导出
ExcelUtil.beginExportExcel(request,response,workbook,"Excel名称");
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/15922.html
