一.下载
public void Downloads(HttpServletResponse response , String url){ try { File file = new File(url); String str = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()); String name=str+".xlsx"; if(file.exists()){ //判断文件父目录是否存在 response.setContentType("application/force-download"); response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(name, "UTF-8")); response.setHeader("Pragma", URLEncoder.encode(name, "UTF-8")); byte[] buffer = new byte[1024]; FileInputStream fis = null; //文件输入流 BufferedInputStream bis = null; OutputStream os = null; //输出流 os = response.getOutputStream(); fis = new FileInputStream(file); bis = new BufferedInputStream(fis); int i = bis.read(buffer); while(i != -1){ os.write(buffer); i = bis.read(buffer); } bis.close(); fis.close(); } }catch (Exception e){ e.printStackTrace(); logger.warn("sys:zydownload:Downloads--userId:"+ ShiroUtils.getUserId()+"===="+e.getMessage()); } }
二,ExcelUtils工具类
package com.zy.common.utils; import cn.afterturn.easypoi.excel.ExcelExportUtil; import cn.afterturn.easypoi.excel.entity.ExportParams; import com.zy.modules.sys.entity.SysUserEntity; import com.zy.modules.sys.excel.ScAdminProxyBean; import org.apache.commons.lang.StringUtils; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.BeanUtils; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import java.io.*; import java.net.URLEncoder; import java.text.DecimalFormat; import java.util.*; /** * excel工具类 * * @author Mark sunlightcs@gmail.com * @since 2018-03-24 */ public class ExcelUtils { private static Logger logger = LoggerFactory.getLogger(com.zy.common.utils.ExcelUtils.class); /** * Excel导出 * * @param response response * @param fileName 文件名 * @param list 数据List * @param pojoClass 对象Class */ public static void exportExcel(HttpServletResponse response, String fileName, Collection<?> list, Class<?> pojoClass) throws IOException { Workbook workbook = ExcelExportUtil.exportBigExcel(new ExportParams(), pojoClass, list); ExcelExportUtil.closeExportBigExcel(); response.setCharacterEncoding("UTF-8"); response.setHeader("content-Type", "application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx"); response.setHeader("Pragma", URLEncoder.encode(fileName, "UTF-8") + ".xlsx"); ServletOutputStream out = response.getOutputStream(); workbook.write(out); out.flush(); } public static void exportExcel( String fileName, Collection<?> list, Class<?> pojoClass) throws IOException { FileOutputStream fouts = new FileOutputStream(fileName); Workbook workbook = ExcelExportUtil.exportBigExcel(new ExportParams(), pojoClass, list); ExcelExportUtil.closeExportBigExcel(); workbook.write(fouts); fouts.flush(); fouts.close(); } /** * Excel导出,先sourceList转换成List<targetClass>,再导出 * * @param response response * @param fileName 文件名 * @param sourceList 原数据List * @param targetClass 目标对象Class */ public static void exportExcelToTarget(HttpServletResponse response, String fileName, Collection<?> sourceList, Class<?> targetClass) throws Exception { List targetList = new ArrayList<>(sourceList.size()); for(Object source : sourceList){ Object target = targetClass.newInstance(); BeanUtils.copyProperties(source, target); targetList.add(target); } exportExcel(response, fileName, targetList, targetClass); } /** * Excel导出,先sourceList转换成List<targetClass>,再导出 ** @param fileName 文件名 * @param sourceList 原数据List * @param targetClass 目标对象Class */ public static void exportExcelToTarget3(String fileName, Collection<?> sourceList, Class<?> targetClass) throws Exception { List targetList = new ArrayList<>(sourceList.size()); for(Object source : sourceList){ Object target = targetClass.newInstance(); BeanUtils.copyProperties(source, target); targetList.add(target); } exportExcel(fileName, targetList, targetClass); } /** * 解析excel * @param file * @return */ public static List<String[]> parseExcel(File file){ //获得Workbook工作薄对象 Workbook wb=getWorkbook(file); List<String[]> list=new ArrayList<>(); if(wb == null){ logger.error("不支持的文件类型"); return null; } for (int sheetNum = 0;sheetNum<wb.getNumberOfSheets();sheetNum++){ //获得当前sheet工作表 Sheet sheet = wb.getSheetAt(sheetNum); if (sheet==null){ continue; } //获得当前sheet的开始行 int firstRowNum = sheet.getFirstRowNum(); //获得当前sheet的结束行 int lastRowNum = sheet.getLastRowNum(); //跳过第一行 for(int i = firstRowNum+1;i<=lastRowNum;i++){ Row row= sheet.getRow(i); if (row==null){ continue; } //开始列 int firstCellNum = row.getFirstCellNum(); //结束列 int lastCellNum = row.getLastCellNum(); String[] cells=new String[lastCellNum]; for (int j = firstCellNum; j <lastCellNum ; j++) { Cell cell =row.getCell(j); cells[j]=getCellValue(cell); } list.add(cells); } } return list; } /** * 解析数据格式 * @param cell * @return */ private static String getCellValue(Cell cell){ if (cell == null) { return ""; } switch (cell.getCellTypeEnum()) { case NUMERIC: //cell.setCellType(Cell.CELL_TYPE_STRING); DecimalFormat df = new DecimalFormat("0"); return String.valueOf(df.format(cell.getNumericCellValue())); case STRING: return String.valueOf(cell.getStringCellValue()); case BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case FORMULA: return String.valueOf(cell.getCellFormula()); case BLANK: return ""; case ERROR: return "非法字符"; default: return "未知类型"; } } private static Workbook getWorkbook(File file) { try(InputStream in = new FileInputStream(file)){ String fileName = file.getName(); if(fileName.endsWith(".xls")){ return new HSSFWorkbook(new BufferedInputStream(in)); }else if (fileName.endsWith(".xlsx")){ return new XSSFWorkbook(new BufferedInputStream(in)); }else if (fileName.endsWith(".csv")){ return null; } } catch (IOException e) { logger.error("Excel文件解析出错",e); } return null; } }