POI导出excel(含表头，冰冻，动态列，动态数据)

一时失言乱红尘 2021-12-14 14:43 1271阅读 0赞

分享POI导出excel(含表头，冰冻，动态列，动态数据)  
首先导入 POI ：

<dependency>
    		    <groupId>org.apache.poi</groupId>
    		    <artifactId>poi</artifactId>
    		    <version>3.17</version>
    		</dependency>
    		
    		
    		<dependency>
    		    <groupId>org.apache.poi</groupId>
    		    <artifactId>poi-scratchpad</artifactId>
    		    <version>3.17</version>
    		</dependency>
    		
    		
    		<dependency>
    		    <groupId>org.apache.poi</groupId>
    		    <artifactId>poi-ooxml</artifactId>
    		    <version>3.17</version>
    		</dependency>
    		
    		<dependency>
    		    <groupId>org.apache.poi</groupId>
    		    <artifactId>poi-ooxml-schemas</artifactId>
    		    <version>3.17</version>
    		</dependency>

导出的方法：

//导出数据
    	@SuppressWarnings("deprecation")
    	public Map<String, Object> exportRecordTask(HttpServletResponse response, Map<String, Object> param) {
    		Map<String, Object> mp = new ConcurrentHashMap<>(10000000);
    		
    		//这里默认是从前台传过来的，自定义列
    		/*@SuppressWarnings("unchecked")
    		List<String> headers = (List<String>) param.get("headers");
    		if( param.get("headers") == null){
    			mp.put("msg", 1017);
    			return mp;
    		}*/
    		
    		@SuppressWarnings("resource")
    		HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("导出数据");
            sheet.setDefaultColumnWidth(30);//默认列宽
            /*sheet.setColumnWidth(1, 30*256);//设置第几个列宽
            sheet.setColumnWidth(5, 30*256);
            sheet.setColumnWidth(8, 50*256);
            sheet.setColumnWidth(9, 50*256);
            sheet.setColumnWidth(14, 100*256);*/
            //sheet.protectSheet("123456");//设置密码
            HSSFCellStyle style = workbook.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);    
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setWrapText(true);
            style.setFillForegroundColor((short)43);
            style.setFillForegroundColor(HSSFColor.YELLOW.index);
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            style.setLocked(true); 
            
            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 16);
            font.setColor(HSSFColor.RED.index);
            font.setFontName("宋体");
            font.setBold(true);
    
            HSSFFont font2 = workbook.createFont();
            font2.setFontHeightInPoints((short) 12);
            font2.setFontName("宋体");
    
            style.setFont(font);
            
            List<你的实体类> list = null;//这里是查出来的动态数据，需要跟你的列名对应
    		//获取数据长度，获取列长度
            int listSize = list.size(), headSize = headers.size();
            
            //sheet.createFreezePane(headSize, 1);//设置冰冻列
            sheet.createFreezePane( 0, 1, 0, 1 );//设置冰冻列
            //导出excel名称，这里可以用UUID
            String fileName = "excel.xls";
    
            String[] headers = { "列1", "列2", "列3"};
    
            HSSFRow row = sheet.createRow(0);//设置第一行
            row.setHeightInPoints(40);//默认行高
            
            //在表中存放查询到的数据放入对应的列
            for (int i = 0;i < listSize; i++) {
            	HSSFRow row1 = sheet.createRow(i+1);//这里之所以 i+1是避免加入数据将第一行表头给替换
                row1.setHeightInPoints(50);
                for(int j = 0;j < headSize;j++) {
            		HSSFCell cell = row.createCell(j);//遍历列
                    HSSFRichTextString text = new HSSFRichTextString(headers.get(j));//获取列名
                    cell.setCellValue(text);//加内容到列中
                    cell.setCellStyle(style);//加上面定义的样式
            		row1.createCell(j).getCellStyle().setWrapText(true);//设置自动换行
                	row1.createCell(j).getCellStyle().setVerticalAlignment(VerticalAlignment.CENTER);//设置居中
                	row1.createCell(j).getCellStyle().setFont(font2);//设置样式
                	if("列1".equals(headers.get(j))) {
                		row1.createCell(j).setCellValue(list.get(i).get列1());
                	}else if("列2".equals(headers.get(j))) {
                		row1.createCell(j).setCellValue(list.get(i).get列2());
                	}else if("列3".equals(headers.get(j))) {
                		row1.createCell(j).setCellValue(list.get(i).get列3());
                	}
                }
                //row1.createCell(0).getCellStyle().setAlignment(HorizontalAlignment.CENTER);        
    		}
    
            //response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setContentType("application/octet-stream");//设置打印
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            try {
    			response.flushBuffer();
    	        workbook.write(response.getOutputStream());//输出
    	        response.getOutputStream().close();
    	        mp.put("msg", 200);
    		} catch (IOException e) {
    			//mp.put("msg", 500);
    		}
    		return mp;//最后返回map ，方法可以直接 无返回类型
    	}

冰冻的方法详解：

/ * *
    *创建一个分割(freezepane)。任何现有的冷冻epane或拆分窗格都被覆盖。<p>
    *
    *如果colSplit和rowSplit都为零，则删除现有的冻结窗格
    *
    * @param colSplit的水平位置。
    * @param rowSplit的垂直位置。
    * @param Left tmostcolumn在右窗格中显示左列。
    * @param topRow顶部行在底部窗格中可见
    * /
        @Override
        public void createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow) {
            validateColumn(colSplit);
            validateRow(rowSplit);
            if (leftmostColumn < colSplit)
                throw new IllegalArgumentException("leftmostColumn parameter must not be less than colSplit parameter");
            if (topRow < rowSplit)
                throw new IllegalArgumentException("topRow parameter must not be less than leftmostColumn parameter");
            getSheet().createFreezePane(colSplit, rowSplit, topRow, leftmostColumn);
        }

sheet.createFreezePane( 0, 1, 0, 1 );//设置第一行冰冻  
由于最近比较忙，所以就贴出了代码没有做详细的解释，后续更新  
有问题可以在下面留言，每天在线