使用poi导出Excel

ゝ一纸荒年。 2022-06-05 05:22 238阅读 0赞

1.需要导入的包，要注意jar包版本要一致

poi-3.9.jar

poi-ooxml-shemas-3.9.jar

xmlbeans-2.3.0.jar

2.工具类

package com.utils;  
      
    import java.io.BufferedInputStream;
    import java.io.BufferedOutputStream;
    import java.io.ByteArrayInputStream;
    import java.io.ByteArrayOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.math.BigDecimal;
    import java.util.Date;
    import java.util.Iterator;
    import java.util.Map;
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.Font;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.streaming.SXSSFCell;
    import org.apache.poi.xssf.streaming.SXSSFRow;
    import org.apache.poi.xssf.streaming.SXSSFSheet;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    
    import com.alibaba.fastjson.JSONArray;
    import com.alibaba.fastjson.JSONObject;
    
    public class ExcelUtil{
    	
    	public static String NO_DEFINE = "no_define";//未定义的字段
        public static String DEFAULT_DATE_PATTERN="yyyy年MM月dd日";//默认日期格式
        public static int DEFAULT_COLOUMN_WIDTH = 17;
        
    	public static void exportExcelX(String title,Map<String, String> headMap,JSONArray jsonArray,String datePattern,int colWidth, OutputStream out) {
            if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN;
            // 声明一个工作薄
            SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
            workbook.setCompressTempFiles(true);
             //表头样式
            CellStyle titleStyle = workbook.createCellStyle();
            titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            Font titleFont = workbook.createFont();
            titleFont.setFontHeightInPoints((short) 20);
            titleFont.setBoldweight((short) 700);
            titleStyle.setFont(titleFont);
            // 列头样式
            CellStyle headerStyle = workbook.createCellStyle();
            
    //        headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            Font headerFont = workbook.createFont();
            headerFont.setFontHeightInPoints((short) 12);
            headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            headerStyle.setFont(headerFont);
            
            // 单元格样式
            CellStyle cellStyle = workbook.createCellStyle();
            
    //        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            Font cellFont = workbook.createFont();
            cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
            cellStyle.setFont(cellFont);
            
            
            // 生成一个(带标题)表格
            SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet();
            //设置列宽
            int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//至少字节数
            int[] arrColWidth = new int[headMap.size()];
            // 产生表格标题行,以及设置列宽
            String[] properties = new String[headMap.size()];
            String[] headers = new String[headMap.size()];
            int ii = 0;
            for (Iterator<String> iter = headMap.keySet().iterator(); iter
                    .hasNext();) {
                String fieldName = iter.next();
    
                properties[ii] = fieldName;
                headers[ii] = headMap.get(fieldName);
    
                int bytes = fieldName.getBytes().length;
                arrColWidth[ii] =  bytes < minBytes ? minBytes : bytes;
                sheet.setColumnWidth(ii,arrColWidth[ii]*256);
                ii++;
            }
            // 遍历集合数据，产生数据行
            int rowIndex = 0;
            for (Object obj : jsonArray) {
                if(rowIndex == 65535 || rowIndex == 0){
                    if ( rowIndex != 0 ) sheet = (SXSSFSheet) workbook.createSheet();//如果数据超过了，则在第二页显示
    
                    SXSSFRow titleRow = (SXSSFRow) sheet.createRow(0);//表头 rowIndex=0
                    titleRow.createCell(0).setCellValue(title);
                    titleRow.getCell(0).setCellStyle(titleStyle);
                    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
    
                    SXSSFRow headerRow = (SXSSFRow) sheet.createRow(1); //列头 rowIndex =1
                    for(int i=0;i<headers.length;i++)
                    {
                        headerRow.createCell(i).setCellValue(headers[i]);
                        headerRow.getCell(i).setCellStyle(headerStyle);
    
                    }
                    rowIndex = 2;//数据内容从 rowIndex=2开始
                }
                JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
                SXSSFRow dataRow = (SXSSFRow) sheet.createRow(rowIndex);
                for (int i = 0; i < properties.length; i++)
                {
                    SXSSFCell newCell = (SXSSFCell) dataRow.createCell(i);
    
                    Object o =  jo.get(properties[i]);
                    String cellValue = ""; 
                    if(o==null) cellValue = "";
                    else if(o instanceof Date) cellValue = DateUtil.format((Date)o);
                    else if(o instanceof Float || o instanceof Double) 
                        cellValue= new BigDecimal(o.toString()).setScale(2,BigDecimal.ROUND_HALF_UP).toString();
                    else cellValue = o.toString();
    
                    newCell.setCellValue(cellValue);
                    newCell.setCellStyle(cellStyle);
                }
                rowIndex++;
            }
            // 自动调整宽度
            /*for (int i = 0; i < headers.length; i++) {
                sheet.autoSizeColumn(i);
            }*/
            try {
                workbook.write(out);
                workbook.dispose();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        //Web 导出excel
        public static void downloadExcelFile(String title,Map<String,String> headMap,JSONArray ja,HttpServletResponse response){
            try {
                ByteArrayOutputStream os = new ByteArrayOutputStream();
                ExcelUtil.exportExcelX(title,headMap,ja,null,0,os);
                byte[] content = os.toByteArray();
                InputStream is = new ByteArrayInputStream(content);
                // 设置response参数，可以打开下载页面
                response.reset();
    
                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); 
                response.setHeader("Content-Disposition", "attachment;filename="+DateUtil.format(new Date(), DateUtil.DATE_FOMATE_YYYYMMDDHHMMSS) + new String((title + ".xlsx").getBytes(), "iso-8859-1"));
                response.setContentLength(content.length);
                ServletOutputStream outputStream = response.getOutputStream();
                BufferedInputStream bis = new BufferedInputStream(is);
                BufferedOutputStream bos = new BufferedOutputStream(outputStream);
                byte[] buff = new byte[8192];
                int bytesRead;
                while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                    bos.write(buff, 0, bytesRead);
    
                }
                bis.close();
                bos.close();
                outputStream.flush();
                outputStream.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        
       /* public static void main(String[] args) throws IOException {
            int count = 10;
            JSONArray ja = new JSONArray();
            for(int i=0;i<10;i++){
                Map<String, Object> s = new HashMap<String, Object>();
                s.put("name", "zhangsan");
                s.put("age",12);
                s.put("birthday",new Date());
                ja.add(s);
            }
            Map<String,String> headMap = new LinkedHashMap<String,String>();
            headMap.put("name","姓名");
            headMap.put("age","年龄");
            headMap.put("birthday","生日");
    
            String title = "测试";
            OutputStream outXlsx = new FileOutputStream("D://bb.xlsx");
            System.out.println("正在导出xlsx....");
            Date d2 = new Date();
            ExcelUtil.exportExcelX(title,headMap,ja,null,0,outXlsx);
            System.out.println("共"+count+"条数据,执行"+(new Date().getTime()-d2.getTime())+"ms");
            outXlsx.close();
    
        }*/
    }

3.控制层的写法

//导出对账订单列表
        @RequestMapping("partOrderExport")
        @ResponseBody
        public void partOrderExport(HttpServletRequest request, HttpServletResponse response){
        	try {
        		List<Map<String, Object>> mapList = orderEntityServiceImpl.getOrderEntityAndModeExport(tupleList);
        		if(CollectionUtils.isNotEmpty(mapList)){
        			Map<String,String> headMap = new LinkedHashMap<String,String>();
        			headMap.put("creationtime", "下单时间");
        			headMap.put("id", "订单id");
        			headMap.put("total", "交易金额（单位元）");
        			headMap.put("fee", "手续费");
        			headMap.put("poundage", "承担方");
        			headMap.put("subject", "商品名称");
        			headMap.put("month", "分期数");
        			headMap.put("username", "商户账号");
        			headMap.put("customer", "顾客姓名");
        			headMap.put("alipay", "支付宝账号");
        			headMap.put("ordertype_name", "支付类型");
        			headMap.put("status_name", "交易状态");
        			JSONArray ja = new JSONArray();
        			ja.addAll(mapList);
        			String title = "订单信息";
        			ExcelUtil.downloadExcelFile(title,headMap,ja,response);
        		}
        	} catch (Exception e) {
        		e.printStackTrace();
        	}
        }

4.页面方法

function exportE(){
    		var keyword = $("#keyword").val()
    		var starttime = $("#startTime").val();
    		var endtime = $("#endTime").val();
    		window.open(serverWeb+"order/partExport?keyword="+keyword+"&starttime="+starttime+"&endtime="+endtime,"_blank");
    	}