Java 数据库报表导出统一方法

以你之姓@ 2023-06-24 04:42 81阅读 0赞

public static InputStream buildExcelDocument(List<LinkedHashMap<String, Object>> infos,List<String> headers) {
    
    		if(infos==null|| infos.size()==0)
    		return null;
    	    // 工作薄xlsx文件  
            XSSFWorkbook workbook = new XSSFWorkbook(); 
            XSSFSheet sheet = workbook.createSheet("sheet1");
            XSSFRow header = sheet.createRow(0);
    		
    		//样式
            XSSFCellStyle style = workbook.createCellStyle();
    		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    		
    		//设置列宽
    		sheet.autoSizeColumn(0);
    
    		
    		//标题列
    		header.setRowStyle(style);
    		header.setHeightInPoints((21));
    		for(int i=0;i<headers.size();i++){
    			header.createCell(i).setCellValue(headers.get(i));
    		}
    	
    		int rowNum=1;
    		
    
    		for (LinkedHashMap<String, Object> info : infos) {
    			XSSFRow row=sheet.createRow(rowNum);
    			row.setRowStyle(style);
    			row.setHeightInPoints((21));
    			for(int i=0;i<headers.size();i++){
    				// 放入数据
    				row.createCell(i).setCellValue((String)info.get(headers.get(i)));
    			}
    			rowNum++;
    		}
    		// 开始制作流
    		InputStream inputStream = null ;
    		//临时缓冲区
            ByteArrayOutputStream out = new ByteArrayOutputStream();
          //创建临时文件
            try {
    			workbook.write(out);
    	        byte [] bookByteAry = out.toByteArray();
    	        inputStream = new ByteArrayInputStream(bookByteAry);
    	        
            } catch (IOException e) {
            	throw new RuntimeException(e.getMessage());
    		} finally{
    			try {
    				out.close();
    				workbook.close();
    			} catch (IOException e) {
    				throw new RuntimeException("流关闭报错:"+e.getMessage());
    			}
    		}
            return inputStream;
    
    		
    	}

统一从数据库中查询数据

List<LinkedHashMap<String, Object>> getSmsLog7Day();

<select id="getSmsLog7Day" resultType="java.util.LinkedHashMap">  
  
SELECT  
IFNULL(cb.USERNAME,"") as '代理店',  
IFNULL(sl.PHONE,"") as '电话',  
date\_format( DATE\_ADD(sl.CREATED\_DATE,INTERVAL 11 HOUR), '%Y-%m-%d %H:%i:%S' ) as '发送时间',  
IFNULL(sl.MESSAGE,"") as '发送内容'

</select>

导出表格的封装方法