Java 导出Excel 和 打包导出

/** * Excel工具类 * @title  * @filename ExcelUtils.java * @version 1.0 */public class ExcelUtils {/** *  * @param response * @param fileName文件名称 * @param headNameMap表头 * @param listDTO数据 */@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、输入excelworkbook.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);}}