JAVA导出excel 直接弹出下载框

川长思鸟来 2022-05-22 11:35 210阅读 0赞

效果展示：

![70][]

![70 1][]

1.首先准备jar包

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.8</version>
    </dependency>

2.前台页面访问 导出excel的接口

通过window.location.href来访问接口，直接弹出下载框

// 导出数据
    $('#outExcel').click(function(){
        var status = $('#status').val();
        window.location.href = baseUrl  + '/order/excel/orderrdExportExcel?status="+status;
    });

3.后台通过获取response中的OutStream 流 ，将excel文件写到流里面，代码如下:

@GetMapping("/orderrdExportExcel")
    public void grouppExportExcel(HttpServletRequest request,Orderrd orderrd,HttpServletResponse response) throws Exception{
        List<Orderrd> grouppList = orderrdService.selectAllForLayUI(orderrd);
        //导出文件的标题
        String title = "退款订单"+DateUtil.getSimpleDate01(new Date())+".xlsx";
        //设置表格标题行
        String[] headers = new String[] {"序号","流水编号","用户账号","用户姓名","收款账号","账户余额","退款金额","手续费","申请时间"};
        List<Object[]> dataList = new ArrayList<Object[]>();
        Object[] objs = null;
        for (int i = 0; i < grouppList.size(); i++) {
        	objs = new Object[headers.length];
    	objs[0] = 0;//设置序号,在工具类中会出现
        	objs[1] = grouppList.get(i).getOrderrdCode();
        	objs[2] = grouppList.get(i).getUsername();
        	objs[3] = grouppList.get(i).getUserName();
        	objs[4] = grouppList.get(i).getAccount();
        	objs[5] = grouppList.get(i).getPremain();
        	objs[6] = grouppList.get(i).getPricePre();
        	objs[7] = grouppList.get(i).getPricePay();
        	objs[8] = grouppList.get(i).getCreateTime();
        	dataList.add(objs);//数据添加到excel表格
        }
          //使用流将数据导出
            OutputStream out = null;
            try {
                //防止中文乱码
                String headStr = "attachment; filename=\"" + new String( title.getBytes("gb2312"), "ISO8859-1" ) + "\"";
                response.setContentType("octets/stream");
                response.setContentType("APPLICATION/OCTET-STREAM");
                response.setHeader("Content-Disposition", headStr);
                out = response.getOutputStream();
                ExportExcelSeedBack ex = new ExportExcelSeedBack(title, headers, dataList);//没有标题
                ex.export(out);
                out.flush();    
                out.close();    
              } catch (Exception e) {
                e.printStackTrace();
              }
       
    	}

4.工具类如下：

import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFRichTextString;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hssf.util.HSSFColor;
    
    public class ExportExcelSeedBack {
    
        //显示的导出表的标题
        private String title;
        //导出表的列名
        private String[] rowName ;
    
        private List<Object[]>  dataList = new ArrayList<Object[]>();
    
        HttpServletResponse  response;
    
    
        //构造方法，传入要导出的数据
        public ExportExcelSeedBack(String title,String[] rowName,List<Object[]> dataList){
            this.dataList = dataList;
            this.rowName = rowName;
            this.title = title;
        }
    
        /*
         * 导出数据
         * */
        public void export(OutputStream out) throws Exception{
            try{
                HSSFWorkbook workbook = new HSSFWorkbook();                     // 创建工作簿对象
                HSSFSheet sheet = workbook.createSheet(title);                  // 创建工作表
    
                // 产生表格标题行
        //          HSSFRow rowm = sheet.createRow(0);
        //          HSSFCell cellTiltle = rowm.createCell(0);
    
                //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】
                HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
                HSSFCellStyle style = this.getStyle(workbook);                  //单元格样式对象
    
        //          sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1)));//合并单元格  
        //          cellTiltle.setCellStyle(columnTopStyle);
        //          cellTiltle.setCellValue(title);
    
                // 定义所需列数
                int columnNum = rowName.length;
                HSSFRow rowRowName = sheet.createRow(0);                // 在索引2的位置创建行(最顶端的行开始的第二行)
    
                // 将列头设置到sheet的单元格中
                for(int n=0;n<columnNum;n++){
                    HSSFCell  cellRowName = rowRowName.createCell(n);               //创建列头对应个数的单元格
                    cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);             //设置列头单元格的数据类型
                    HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
                    cellRowName.setCellValue(text);                                 //设置列头单元格的值
                    cellRowName.setCellStyle(columnTopStyle);                       //设置列头单元格样式
                }
    
                //将查询出的数据设置到sheet对应的单元格中
                for(int i=0;i<dataList.size();i++){
                    Object[] obj = dataList.get(i);//遍历每个对象
                    HSSFRow row = sheet.createRow(i+1);//创建所需的行数（从第二行开始写数据）
    
                    for(int j=0; j<obj.length; j++){
                        HSSFCell  cell = null;   //设置单元格的数据类型
                        if(j == 0){
                            cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(i+1); 
                        }else{
                            cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                            if(!"".equals(obj[j]) && obj[j] != null){
                                cell.setCellValue(obj[j].toString());                       //设置单元格的值
                            }
                        }
                        cell.setCellStyle(style);                                   //设置单元格样式
                    }
                }
                //让列宽随着导出的列长自动适应
                for (int colNum = 0; colNum < columnNum; colNum++) {
                    int columnWidth = sheet.getColumnWidth(colNum) / 256;
                    for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                        HSSFRow currentRow;
                        //当前行未被使用过
                        if (sheet.getRow(rowNum) == null) {
                            currentRow = sheet.createRow(rowNum);
                        } else {
                            currentRow = sheet.getRow(rowNum);
                        }
         //                 if (currentRow.getCell(colNum) != null) {
         //                     HSSFCell currentCell = currentRow.getCell(colNum);
        //                      if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
        //                          int length =     currentCell.getStringCellValue().getBytes().length;
        //                          if (columnWidth < length) {
        //                              columnWidth = length;
        //                          }
        //                      }
        //                  }
                        if (currentRow.getCell(colNum) != null) {
                            HSSFCell currentCell = currentRow.getCell(colNum);
                            if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                                int length = 0;
                                try {
                                    length = currentCell.getStringCellValue().getBytes().length;
                                } catch (Exception e) {
                                    //e.printStackTrace();
                                }
                                if (columnWidth < length) {
                                    columnWidth = length;
                                }
                            }
                        }
    
                    }
                    if(colNum == 0){
                        sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
                    }else{
                        sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
                    }
                }
                if(workbook !=null){
                    try{
                       workbook.write(out);
                    }catch (IOException e) {
                        e.printStackTrace();
                    }
                }
    
            }catch(Exception e){
                e.printStackTrace();
            }
            finally{
                out.close();
            }
        }
    
        /* 
         * 列头单元格样式
         */    
        public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
    
              // 设置字体
              HSSFFont font = workbook.createFont();
              //设置字体大小
              font.setFontHeightInPoints((short)11);
              //字体加粗
              font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
              //设置字体名字 
              font.setFontName("Courier New");
              //设置样式; 
              HSSFCellStyle style = workbook.createCellStyle();
              //设置底边框; 
              style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
              //设置底边框颜色;  
              style.setBottomBorderColor(HSSFColor.BLACK.index);
              //设置左边框;   
              style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
              //设置左边框颜色; 
              style.setLeftBorderColor(HSSFColor.BLACK.index);
              //设置右边框; 
              style.setBorderRight(HSSFCellStyle.BORDER_THIN);
              //设置右边框颜色; 
              style.setRightBorderColor(HSSFColor.BLACK.index);
              //设置顶边框; 
              style.setBorderTop(HSSFCellStyle.BORDER_THIN);
              //设置顶边框颜色;  
              style.setTopBorderColor(HSSFColor.BLACK.index);
              //在样式用应用设置的字体;  
              style.setFont(font);
              //设置自动换行; 
              style.setWrapText(false);
              //设置水平对齐的样式为居中对齐;  
              style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
              //设置垂直对齐的样式为居中对齐; 
              style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    
              return style;
    
        }
    
        /*  
         * 列数据信息单元格样式
         */  
        public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
              // 设置字体
              HSSFFont font = workbook.createFont();
              //设置字体大小
              //font.setFontHeightInPoints((short)10);
              //字体加粗
              //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
              //设置字体名字 
              font.setFontName("Courier New");
              //设置样式; 
              HSSFCellStyle style = workbook.createCellStyle();
              //设置底边框; 
              style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
              //设置底边框颜色;  
              style.setBottomBorderColor(HSSFColor.BLACK.index);
              //设置左边框;   
              style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
              //设置左边框颜色; 
              style.setLeftBorderColor(HSSFColor.BLACK.index);
              //设置右边框; 
              style.setBorderRight(HSSFCellStyle.BORDER_THIN);
              //设置右边框颜色; 
              style.setRightBorderColor(HSSFColor.BLACK.index);
              //设置顶边框; 
              style.setBorderTop(HSSFCellStyle.BORDER_THIN);
              //设置顶边框颜色;  
              style.setTopBorderColor(HSSFColor.BLACK.index);
              //在样式用应用设置的字体;  
              style.setFont(font);
              //设置自动换行; 
              style.setWrapText(false);
              //设置水平对齐的样式为居中对齐;  
              style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
              //设置垂直对齐的样式为居中对齐; 
              style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    
              return style;
    
              }
        
        
       
       }

[70]: /images/20220522/bc907334fe6d4163bbb51c768deb41e5.png
[70 1]: /images/20220522/f1bd29ed5a4847619ab034088ffef2d5.png