poi导出Excel多页sheet工具类

╰半夏微凉° 2022-02-20 06:06 530阅读 0赞

## ExcelExportUtil.calss ##

package com.common.excel;
    
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    import org.apache.poi.ss.usermodel.Workbook;
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.*;
    import java.net.URLEncoder;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    
    public class ExcelExportUtil {
        /**
         *
         * @Title: exportManySheetExcel
         * @Description: 可生成单个、多个sheet
         * @param @param file 导出文件路径
         * @param @param mysheets
         * @return void
         * @throws
         */
        public static Workbook exportManySheetExcel(List<ExcelExp> mysheets){
    
            HSSFWorkbook wb = new HSSFWorkbook();//创建工作薄
            List<ExcelExp> sheets = mysheets;
    
            //表头样式
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
            //字体样式
            HSSFFont fontStyle = wb.createFont();
            fontStyle.setFontName("微软雅黑");
            fontStyle.setFontHeightInPoints((short)12);
            //fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            style.setFont(fontStyle);
    
            for(ExcelExp excel: sheets){
                //新建一个sheet
                HSSFSheet sheet = wb.createSheet(excel.getFileName());//获取该sheet名称
    
                String[] handers = excel.getHanders();//获取sheet的标题名
                HSSFRow rowFirst = sheet.createRow(0);//第一个sheet的第一行为标题
                //写标题
                for(int i=0;i<handers.length;i++){
                    //获取第一行的每个单元格
                    HSSFCell cell = rowFirst.createCell(i);
                    //往单元格里写数据
                    cell.setCellValue(handers[i]);
                    cell.setCellStyle(style); //加样式
                    sheet.setColumnWidth(i, 4000); //设置每列的列宽
                }
    
                //写数据集
                List<String[]> dataset = excel.getDataset();
                for(int i=0;i<dataset.size();i++){
                    String[] data = dataset.get(i);//获取该对象
    
                    //创建数据行
                    HSSFRow row = sheet.createRow(i+1);
    
                    for(int j=0;j<data.length;j++){
                        //设置对应单元格的值
                        row.createCell(j).setCellValue(data[j]);
                    }
                }
            }
            return wb;
        }
    
        public static void outputXls(Workbook workbook, String fileName, HttpServletResponse response, HttpServletRequest request) {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
    
            try {
                workbook.write(os);
                byte[] content = os.toByteArray();
                InputStream is = new ByteArrayInputStream(content);
                // 设置response参数，可以打开下载页面
                response.reset();
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
                response.setHeader("Content-Disposition", "attachment;filename="+encodeFileName(fileName + ".xls",request));
                ServletOutputStream out = response.getOutputStream();
                BufferedInputStream bis = null;
                BufferedOutputStream bos = null;
                try {
                    bis = new BufferedInputStream(is);
                    bos = new BufferedOutputStream(out);
                    byte[] buff = new byte[2048];
                    int bytesRead;
                    // Simple read/write loop.
                    while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                        bos.write(buff, 0, bytesRead);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    if (bis != null)
                        bis.close();
                    if (bos != null)
                        bos.close();
                }
            } catch (Exception e1) {
                e1.printStackTrace();
            }
        }
        
        public static String encodeFileName(String fileName,HttpServletRequest request)
    			throws UnsupportedEncodingException {
    		String agent = request.getHeader("USER-AGENT");
    		if (null != agent && -1 != agent.indexOf("MSIE")) {
    			return URLEncoder.encode(fileName, "UTF-8");
    		} else if (null != agent && -1 != agent.indexOf("Mozilla")) {
    			return "=?UTF-8?B?"
    					+ (new String(
    					org.apache.commons.codec.binary.Base64
    							.encodeBase64(fileName.getBytes("UTF-8"))))
    					+ "?=";
    		} else {
    			return fileName;
    		}
    	}
        
        /**
         * 把list<map>封装成list<String[]>
         * 由于我的结果集是List<Map<String,Object>>,所以我写了这个个方法,把它转换成List<String[]>
         * @param list 要封装的list
         * @param strNumber String[]的长度
         * @return
         */
        public static List<String []> listUtils(List<Map<String,Object>> list,String [] strKey){
        	
        	if(list!=null && list.size()>0) {//如果list有值
        		
        		List<String []> strList=new ArrayList<String []>();//实例化一个list<string[]>
        		
        		for(Map<String,Object> map:list) {//遍历数组
        			
        			String [] str= new String[strKey.length];//实力一个string[]
        			
        			Integer count=0;//作为str的下标,每次从0开始
        			
        			for(String s:strKey) {//遍历map中的key
        				str[count]=map.get(s).toString();//把map的value赋值到str数组中
        				count++;//str的下标+1
        			}
        			
        			if(str!=null) {//如果str有值,添加到strList
        				strList.add(str);
        			}
        		}
        		if(strList!=null&&strList.size()>0) {//如果strList有值,返回strList
        			return strList;
        		}
        	}
        	
        	return null;
        }
    
    }

## ExcelExp ##

package com.common.excel;
    
    import java.util.List;
    
    public class ExcelExp {
        private String fileName;// sheet的名称
        private String[] handers;// sheet里的标题
        private List<String[]> dataset;// sheet里的数据集
    
        public ExcelExp(String fileName, String[] handers, List<String[]> dataset) {
            this.fileName = fileName;
            this.handers = handers;
            this.dataset = dataset;
        }
    
        public String getFileName() {
            return fileName;
        }
    
        public void setFileName(String fileName) {
            this.fileName = fileName;
        }
    
        public String[] getHanders() {
            return handers;
        }
    
        public void setHanders(String[] handers) {
            this.handers = handers;
        }
    
        public List<String[]> getDataset() {
            return dataset;
        }
    
        public void setDataset(List<String[]> dataset) {
            this.dataset = dataset;
        }
    }

## controller ##

package com.core.controller;
    
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    
    import org.apache.poi.ss.usermodel.Workbook;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.ResponseBody;
    
    @Controller
    public class controller {
    
    	/**
    	 * 业务逻辑实现层
    	 */
    	@Autowired
    	private Service service;
    	
    	/**
    	 * 导出
    	 * @return
    	 */
    	@ResponseBody
    	@RequestMapping(value = "/chartExcel", method = RequestMethod.GET)
    	public Object retailUserCompriseChartExcel() {
    	
    		try {
    			String fileName = "平台交易分析-交易构成-用户构成-趋势图";//导出的文件名
    			
    			//第一页sheet的数据
    			List<Map<String,Object>> list1=service.findByDataInfo();//调用service得到数据
    			
    			//第二页sheet的数据
    			List<Map<String,Object>> list2=service.findByDataInfo();//调用service得到数据
    			
    			//第三页sheet的数据
    			List<Map<String,Object>> list3=service.findByDataInfo();//调用service得到数据
    			
    			//导出头(第一行标题头)
    			String[] nameList = { "编号","姓名","年龄","性别"};
    			
    			//map的key
    			String[] str = { "id","name","age","sex"};
    			
    			//对象1-->第一页sheet的数据
    			ExcelExp e1 =null;
    			if(list1!=null&&list1.size()>0) {
    				List<String[]> list=ExcelExportUtil.listUtils(list1, str);
    				if(list!=null&&list.size()>0) {
    					e1 = new ExcelExp("第一页sheet", nameList,list);//这里的第一页指的是第一页的名字
    				}
    			}
    			
    			//对象2-->第二页sheet的数据
    			ExcelExp e2 =null;
    			if(list2!=null&&list2.size()>0) {
    				List<String[]> list=ExcelExportUtil.listUtils(list2, str);
    				if(list!=null&&list.size()>0) {
    					e2 = new ExcelExp("第二页sheet", nameList,list);//这里的第一页指的是第一页的名字
    				}
    			}
    			
    			//对象3-->第三页sheet的数据
    			ExcelExp e3 =null;
    			if(list3!=null&&list3.size()>0) {
    				List<String[]> list=ExcelExportUtil.listUtils(list3, str);
    				if(list!=null&&list.size()>0) {
    					e3 = new ExcelExp("第三页sheet", nameList,list);//这里的第一页指的是第一页的名字
    				}
    			}
    			
    			List<ExcelExp> mysheet = new ArrayList<ExcelExp>();
    	        
    			if(e1!=null) {//如果e1有值,就导出
    				mysheet.add(e1);
    			}
    			
    			if(e2!=null) {//如果e2有值,就导出
    				mysheet.add(e2);
    			}
    			
    			if(e3!=null) {//如果e3有值,就导出
    				mysheet.add(e3);
    			}
    			
    	        Workbook workbook=  ExcelExportUtil.exportManySheetExcel(mysheet); //生成sheet
    	        ExcelExportUtil.outputXls(workbook, fileName, response, request);
    			
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    		return null;
    	}
    	
    }

## pom.xml ##

<dependency>
    		<groupId>org.apache.poi</groupId>
    		<artifactId>poi</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>
    	
    	<dependency>
    		<groupId>org.apache.poi</groupId>
    		<artifactId>poi-scratchpad</artifactId>
    		<version>3.17</version>
    	</dependency>

完美!!!