Java导出2003、2007版Excel

落日映苍穹つ 2022-06-07 10:26 206阅读 0赞

创建用于添加表头的实体类

/**
     * excel导出标题显示值
     * @author Terisadeng
     *
     */
    public class KeyValue {
    	private String key;
    	private String value;
    	public String getKey() {
    		return key;
    	}
    	
    	public KeyValue(){}
    	
    	public KeyValue(String key, String value) {
    		super();
    		this.key = key;
    		this.value = value;
    	}
    
    	public void setKey(String key) {
    		this.key = key;
    	}
    	public String getValue() {
    		return value;
    	}
    	public void setValue(String value) {
    		this.value = value;
    	}
    }

定义生成文件名的方法

/**
    	 * 
    	 * Description: 设置导出xls格式Excel文件的文件名 by Terisadeng
    	 * Implement: 
    	 * @return 
    	 * @see
    	 */ 
    public static String getFileName() {
    		SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddHHmm");
    		StringBuilder sb = new StringBuilder();
    		sb.append(sf.format(System.currentTimeMillis()));
    		sb.append(".xls");
    		return sb.toString();
    	}
    	
    /**
    	 * 
    	 * Description: 设置导出xlsx格式Excel文件的文件名 by Terisadeng
    	 * Implement: 
    	 * @return 
    	 * @see
    	 */
    	public static String getFileName2007() {
            SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddHHmm");
            StringBuilder sb = new StringBuilder();
            sb.append(sf.format(System.currentTimeMillis()));
            sb.append(".xlsx");
            return sb.toString();
        }

输出流设置导出文件名

/**
    	 * 设置头信息
    	 * 
    	 * @param response
    	 *            HttpServletResponse
    	 * @param fileName
    	 *            默认的文件名称
    	 */
    	public static void setExcelContentType(HttpServletResponse response,
    			String fileName) {
    		try {
    			fileName = new String(fileName.getBytes(), "utf-8");
    		} catch (UnsupportedEncodingException e) {
    			// should no happen
    		}
    		response.reset();
    		response.setContentType("application/msexcel;charset=utf-8");
    		response.setHeader("Content-disposition", "attachment;filename= "
    				+ fileName);
    	}

1、导出2003版Excel

入参是前台传入的HttpServletResponse对象、需要导出的List<Map<String, Object>>类型的数据集、List<KeyValue>类型的表头、excel中sheet的名称

public static void write(HttpServletResponse response,
    			List<Map<String, Object>> listMaps, List<KeyValue> keyValueList,String title) {
    		//设置导出的文件名，按当前时间设置
    		setExcelContentType(response, getFileName());
    		//创建工作簿对象
    		WritableWorkbook wwbook = null;
    		OutputStream os = null;
    		try {
    		  //设置输出流对象
    			os = response.getOutputStream();
    			//根据输出流对象创建初始化工作簿对象
    			wwbook = jxl.Workbook.createWorkbook(os);
    			//根据入参title创建Sheet对象
    			WritableSheet wsheet = wwbook.createSheet(title, 0);// set sheet
    			//根据入参keyValueList设置表头
    			for (int i = 0; i < keyValueList.size(); i++) { // set header title
    				jxl.write.Label titleCell = new jxl.write.Label(i, 0,
    						keyValueList.get(i).getValue());
    				wsheet.addCell(titleCell);
    			}
    			//按照表头创建行对象
    			for (int i = 1; i <= listMaps.size(); i++) { // set value
    				Map<String, Object> map = listMaps.get(i - 1);
    				for (int j = 0; j < keyValueList.size(); j++) {
    					KeyValue keyValue = keyValueList.get(j);
    					jxl.write.Label valueCell = new jxl.write.Label(j, i, (map
    							.get(keyValue.getKey()) != null) ? map.get(
    							keyValue.getKey()).toString() : "");
    					//添加到sheet中
    					wsheet.addCell(valueCell);
    					
    				}
    			}
    			
    			wwbook.write();
    		} catch (IOException e) {
    			e.printStackTrace();
    		} catch (RowsExceededException e) {
    			e.printStackTrace();
    		} catch (WriteException e) {
    			e.printStackTrace();
    		} catch (IllegalArgumentException e) {
    			e.printStackTrace();
    		} finally {
    			try {
    				wwbook.close();
    				os.close();
    			} catch (IOException ie) {
    				ie.printStackTrace();
    			} catch (WriteException e) {
    				e.printStackTrace();
    			}
    		}
    	}

2、导出2007版Excel  
入参是前台传入的HttpServletResponse对象、需要导出的List<Map<String, Object>>类型的数据集、List<KeyValue>类型的表头、excel中sheet的名称

public static void write2007(HttpServletResponse response,
                List<Map<String, Object>> listMaps, List<KeyValue> keyValueList,String title) {
            //设置导出的文件名，按当前时间设置
            setExcelContentType(response, getFileName2007());
            //创建工作簿对象
            XSSFWorkbook wb = new XSSFWorkbook();
            OutputStream os = null;
            try {
            		//设置输出流对象
                os = response.getOutputStream();
                //根据入参title创建Sheet对象
                XSSFSheet sheet = wb.createSheet(title);// set sheet
                //创建Sheet对象的第一行
                XSSFRow rowtitle = sheet.createRow(0);
                //根据入参keyValueList设置表头
                for (int i = 0; i < keyValueList.size(); i++) { // set header title
                    XSSFCell cell = rowtitle.createCell(i);
                    cell.setCellValue(keyValueList.get(i).getValue());
                }
                //从第二行开始按照表头创建行对象
                for (int i = 1; i <= listMaps.size(); i++) { // set value
                    Map<String, Object> map = listMaps.get(i - 1);
                    XSSFRow rowValue = sheet.createRow(i);
                    for (int j = 0; j < keyValueList.size(); j++) {
                        KeyValue keyValue = keyValueList.get(j);
                        XSSFCell cell = rowValue.createCell(j);
                        cell.setCellValue((map.get(keyValue.getKey()) != null) ? map.get(
                            keyValue.getKey()).toString() : "");
                        
                    }
                }
                //写入输出流对象，导出
                wb.write(os);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }finally {
                try {
                    wb.close();
                    os.close();
                } catch (IOException ie) {
                    ie.printStackTrace();
                }catch (Exception e2) {
                    // TODO: handle exception
                    e2.printStackTrace();
                }
            }
        }

最后前台就可以下载导出的文件