Java 导出Excel 和 打包导出

/**
 * Excel工具类
 * @title 
 * @filename ExcelUtils.java
 * @version 1.0
 */
public class ExcelUtils {

	/**
	 * 
	 * @param response
	 * @param fileName			文件名称
	 * @param headNameMap		表头
	 * @param list				DTO数据
	 */
	@SuppressWarnings("unchecked")
	public static <T> void exportXlsxByBean(HttpServletResponse response, String fileName, 
			Map<String, String> headNameMap, List<T> list){
		
		List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
		
		try {
			for(T t: list){
				if(t instanceof Map){
					dataList.add((Map<String, Object>) t);
				}else{
					Map<String, Object> map = new HashMap<String, Object>();
					Field[] fields = t.getClass().getDeclaredFields();
					if(fields != null){
						for(Field field : fields){
							field.setAccessible(true);
							map.put(field.getName(), field.get(t));
						}
					}
					dataList.add(map);
				}
			}
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		
		exportXlsx(response, fileName, headNameMap, dataList);
		
	}
	
	/**
	 * 
	 * @param response
	 * @param fileName
	 * @param headNameMap
	 * @param dataList
	 */
	public static void exportXlsx(HttpServletResponse response, String fileName, 
			Map<String, String> headNameMap, List<Map<String, Object>> dataList){
		
		Workbook workbook = exportXlsx(fileName, headNameMap, dataList);
		
		response.setContentType("application/binary;charset=ISO8859_1");
		
		OutputStream outputStream = null;
		
		try {
			outputStream = response.getOutputStream();
			String fn = new String(fileName.getBytes(), "ISO8859_1");
			response.setHeader("Content-disposition", "attachment; filename=" + fn  + ".xlsx");
			workbook.write(outputStream);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(outputStream != null){
				try {
					outputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	
	/**
	 * 创建多个Excel文件，并压缩
	 * @param response 
	 * @param fileName Excel 文件名称数组
	 * @param headNameMap  Excel 表头名称
	 * @param dataList Excel 数据
	 * @param zipName 压缩包名称
	 */
	public static void exportXlsxZip(HttpServletResponse response, String[] fileName, 
			Map<String, String> headNameMap, HashMap<Integer,List<Map<String, Object>>> dataList,String zipName){
		//空值判断
		if(zipName == null || zipName == ""){
			//为空设置默认名称
			zipName = "zipData.zip";
		}else{
			zipName += ".zip";
		}
		try {
			zipName = new String(zipName.getBytes("GB2312"), "ISO8859_1");
		} catch (UnsupportedEncodingException e1) {			
			e1.printStackTrace();
		}
		response.setHeader("Content-Disposition","attachment;filename=" + zipName);
        response.setContentType("application/binary;charset=ISO8859_1");	
        response.setContentType("application/octet-stream ");
		response.setHeader("Connection", "close"); // 表示不能用浏览器直接打开
		response.setHeader("Accept-Ranges", "bytes");// 告诉客户端允许断点续传多线程连接下载
		response.setCharacterEncoding("UTF-8");
		
		System.out.println("zipName="+zipName);
		Integer size = fileName.length;
		OutputStream outputStream = null;
		UUID uuid = UUID.randomUUID();
		String strUuid = uuid.toString();
		String path = UploadUtil.getUploadPath("/uploads")+"/"+strUuid+"/";
		CopyFileUtil.createDir(path);
		// 要被压缩的文件夹  
        File file = new File(path);
		try {
			outputStream = response.getOutputStream();
			for (int i = 0; i < size; i++) {			
				exportXlsx(path, fileName[i], headNameMap, dataList.get(i));			
			}			
	        InputStream input = null;  
	        ZipOutputStream zipOut = new ZipOutputStream(outputStream);  
	        if(file.isDirectory()){
	            File[] files = file.listFiles();  
	            for(int i = 0; i < files.length; i++){  
	                input = new FileInputStream(files[i]);  
	                zipOut.putNextEntry(new ZipEntry(file.getName() + File.separator + files[i].getName()));  
	                int temp = 0;  
	                while((temp = input.read()) != -1){  
	                    zipOut.write(temp);  
	                }  
	                input.close();
	                //删除文件
	                files[i].delete();
	            }
	        }	                
	        zipOut.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(outputStream != null){
				try {
					outputStream.close();
					if(file != null){
						//删除临时目录
						file.delete();
					}
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	/**
	 * 导出数据
	 * @param headNameMap
	 * @param dataList
	 */
	public static Workbook exportXlsx(String sheetName, Map<String, String> headNameMap, List<Map<String, Object>> dataList){
		Workbook workbook = new XSSFWorkbook();
		workbook = exportXlsx(workbook,sheetName,headNameMap,dataList);
		return workbook;
	}
	
	/**
	 * 导出数据,多Sheet
	 * @param headNameMap
	 * @param dataList
	 */
	public static Workbook exportXlsx(Workbook workbook, String sheetName, Map<String, String> headNameMap, List<Map<String, Object>> dataList){
				
		Sheet sheet = workbook.createSheet(sheetName);
		
		Set<String> keys = headNameMap.keySet();
		
		int i = 0, j = 0;
		Row row = sheet.createRow(i++);
		//表头
		for(String key : keys){
			Cell cell = row.createCell(j++);
			cell.setCellValue(headNameMap.get(key));
		}
		
		//内容
		if(dataList != null && !dataList.isEmpty()){
			for(Map<String, Object> map : dataList){
				row = sheet.createRow(i++);
				j = 0;
				for(String key : keys){
					Cell cell = row.createCell(j++);
					setCellValue(cell, map.get(key));
				}
			}
		}
		
		return workbook;
	}
	
	/**
	 * 导出数据
	 * @param headNameMap
	 * @param dataList
	 */
	public static void exportXlsx(String path, String sheetName, Map<String, String> headNameMap, List<Map<String, Object>> dataList){
		Workbook workbook = new XSSFWorkbook();
		workbook = exportXlsx(workbook,sheetName,headNameMap,dataList);
		FileOutputStream os = null;
		try {			
			os = new FileOutputStream(path + sheetName + ".xlsx");
			//8、输入excel			
			workbook.write(os);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			try {
				if(os != null){
					os.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
	
	private static void setCellValue(Cell cell, Object obj){
		if(obj == null){
		}else if(obj instanceof String){
			cell.setCellValue((String) obj);
		}else if(obj instanceof Date){
			Date date = (Date) obj;
			if(date != null){
				cell.setCellValue(DateUtils.dfDateTime.format(date));
			}
		}else if(obj instanceof Calendar){
			Calendar calendar = (Calendar) obj;
			if(calendar != null){
				cell.setCellValue(DateUtils.dfDateTime.format(calendar.getTime()));
			}
		}else if(obj instanceof Timestamp){
			Timestamp timestamp = (Timestamp) obj;
			if(timestamp != null){
				cell.setCellValue(DateUtils.dfDateTime.format(new Date(timestamp.getTime())));	
			}
		}else if(obj instanceof Double){
			cell.setCellValue((Double) obj);
		}else{
			cell.setCellValue(obj.toString());
		}
	}
	
	/**
	 * 读取excel
	 * @param path
	 * @param startIdx
	 * @return
	 */
	public static List<List<String>> readXlsx(String path, int startIdx){
		
		try {
			InputStream is = new FileInputStream(path);
			
			return readXlsx(is, startIdx);
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	@SuppressWarnings("resource")
	public static List<List<String>> readXlsx(InputStream is, int startIdx){
		
		List<List<String>> list = new ArrayList<List<String>>();
		
		try {
			
			XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
			
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
			if(xssfSheet == null){
				return list;
			}
			
			int endIdx = xssfSheet.getLastRowNum() + 1;
			if(endIdx > startIdx){
				for(; startIdx < endIdx; startIdx++){
					XSSFRow xssfRow = xssfSheet.getRow(startIdx);
					if(xssfRow != null){
						List<String> rowList = new ArrayList<String>();
						int colNum = xssfRow.getLastCellNum();
						boolean isAdd = false;
						for(int i=0; i<colNum; i++){
							XSSFCell cell = xssfRow.getCell(i);
							String str = getValue(cell);
							rowList.add(str);
							if(StringUtils.isNotBlank(str)){
								isAdd = true;
							}
						}
						if(isAdd){
							list.add(rowList);	
						}
					}
				}
			}
			
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if(is != null){
				try {
					is.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}
	
	 private static String getValue(XSSFCell xssFCell) {
		 String str = null;
		 if(xssFCell == null){
			 return str;
		 }
		 if (xssFCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
			 str = String.valueOf(xssFCell.getBooleanCellValue());
		 } else if (xssFCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
			 str = String.valueOf(new DecimalFormat("#").format(xssFCell.getNumericCellValue()));
		 } else {
			 str = String.valueOf(xssFCell.getStringCellValue());
		 }
		 return StringUtils.trim(str);
	}
	
}