poi处理excel导出

2023-09-16 阅读 22 评论 0

摘要：一分钟说明本文主要针对利用poi组件导出excel方式进行说明。涉及知识点:- poi组件- spring4.0知识 poi组件样式 CellStylecellStyle_topic_head = workbook.createCellStyle(); cellStyle_topic_head.setWrapText(true);//自动换行 cellStyle_topic_head.setAlignment(

一分钟说明

本文主要针对利用poi组件导出excel方式进行说明。
涉及知识点:- poi组件- spring4.0知识

poi组件

样式

CellStylecellStyle_topic_head = workbook.createCellStyle();
cellStyle_topic_head.setWrapText(true);//自动换行
cellStyle_topic_head.setAlignment(CellStyle.ALIGN_LEFT);//水平居中
cellStyle_topic_head.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
cellStyle_topic_head.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
cellStyle_topic_head.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
cellStyle_topic_head.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
cellStyle_topic_head.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 
sheet.getRow(NumberConstants.ZERO).setHeightInPoints(Float.valueOf("144.25"));//第一行的行高

合并单元格


//第一行_第一个合并单元格
PoiUtil.setCellRangeAddressInfo(new CellRangeAddress(0, 0, 0, 1), workbook, cellStyle_data, sheet, "内容值");//封装的方法
public static void setCellRangeAddressInfo(CellRangeAddress cellRangeAddress, Workbook workbook, CellStyle cellStyle, Sheet sheet, String value) {sheet.addMergedRegion(cellRangeAddress);PoiUtil.setBorder(1, cellRangeAddress, sheet, workbook);//边框Cell cell = sheet.getRow(cellRangeAddress.getFirstRow()).getCell(cellRangeAddress.getFirstColumn());cell.setCellStyle(cellStyle);cell.setCellValue(value);
}

锁行和锁列

//锁行和锁列
sheet.createFreezePane(2, 4);

中文文件名

//锁行和锁列
response.setHeader("Content-disposition", "attachment;filename=" + new String("报表名称".getBytes(),"iso-8859-1") + ".xls");

spring4.0知识

渲染视图和处理数据

controller代码如下：

    /*** 表格导出* * @return*/@RequestMapping(value = "/downLoad", method = RequestMethod.GET)public ModelAndView downLoad(@Valid BaseDownloadDTO baseDownloadDTO, ModelMap model, HttpServletRequest request) {BaseDownloadVO baseDownloadVO = topicService.getDownLoadData(baseDownloadDTO);model.put("data", baseDownloadVO);return new ModelAndView(new TopicViewExcel(redisTemplate), model);  }

view代码如下：

public class TopicViewExcel extends AbstractXlsView {private RedisTemplate<String, Object> redisTemplate;public TopicViewExcel(RedisTemplate<String, Object> redisTemplate) {this.redisTemplate = redisTemplate;}@Overrideprotected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {//设置数据 BaseDownloadVO baseDownloadVO = (BaseDownloadVO) model.get("data"); //导出response.setHeader("Content-disposition", "attachment;filename=" + new String("专题数据报表".getBytes(),"iso-8859-1") + ".xls");     }