导出Excel
导出Excel
HSSFWorkbook与XSSFWorkbook，前一个可以用来解析以.xls结尾的excel，后一个可以用来解析.xlsx结尾的excel
<!-- HSSF 导出Execel --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.0.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.0.0</version> </dependency>
@Override public void exportExcel(HttpServletRequest request, HttpServletResponse response,StatisticalPageDto statisticalPageDto) { String indexCode = commonService.getIndexCodeByRegionCode(statisticalPageDto.getRegionCode()); Set<String> communityCodes = commonService.getCommunityCodeByIndexCode(indexCode); List<PersonVehicleStatistical> statisticalList = new ArrayList<>(); Wrapper<PersonVehicleStatistical> wrapper = new EntityWrapper<>(); if (!ObjectUtils.isEmpty(communityCodes) && communityCodes.size() > 0){ wrapper.in("community_code",communityCodes); } if (statisticalPageDto.getOrderType().equals("1")){ wrapper.orderBy("snap_person_count",false); }else if (statisticalPageDto.getOrderType().equals("2")){ wrapper.orderBy("snap_vehicle_count",false); }else if (statisticalPageDto.getOrderType().equals("4")){ wrapper.orderBy("vehicle_count",false); }else if (statisticalPageDto.getOrderType().equals("3")){ wrapper.orderBy("person_count",false); } if (statisticalPageDto.getExportType().equals("1")){ Page<PersonVehicleStatistical> page = new Page<>(); page.setCurrent(statisticalPageDto.getPageNo()); page.setSize(statisticalPageDto.getPageSize()); Page<PersonVehicleStatistical> statisticalPage = statisticalService.selectPage(page,wrapper); statisticalList = statisticalPage.getRecords(); }else { statisticalList = statisticalService.selectList(wrapper); } export(PersonVehicleStatistical.class,"statistical_export",statisticalList,request,response); }
/** * 导出数据 * @param clazz 数据模型 * @param data 数据 * @param response 相应 * @param <T> 数据的泛型 */ private static<T> void export(Class<T> clazz, String fileName, List<T> data, HttpServletRequest request, HttpServletResponse response){ try { List<PersonVehicleStatistical> personVehicleStatisticalList = (List<PersonVehicleStatistical>) data; List<List<String>> excelData = new ArrayList<>(); List<String> head = new ArrayList<>(); head.add("XXXX"); head.add("XXXX"); head.add("XXXX"); head.add("XXXX"); head.add("XXXX"); head.add("XXXX"); head.add("XXXX"); head.add("XXXX"); excelData.add(head); for (PersonVehicleStatistical statistical:personVehicleStatisticalList) { List<String> content = new ArrayList<>(); content.add(statistical.getCommunityCode()); content.add(statistical.getCommunityName()); content.add(statistical.getCommunityArea()); content.add( statistical.getCommunityAreaName()); content.add(String.valueOf(statistical.getSnapPersonCount())); content.add(String.valueOf(statistical.getSnapVehicleCount())); content.add(String.valueOf(statistical.getPersonCount())); content.add(String.valueOf(statistical.getVehicleCount())); excelData.add(content); } fileName = fileName + "_" + System.currentTimeMillis() + ".xls"; preDownLoad(response,excelData,"统计",fileName); } catch (Exception e) { logger.error("export error: ",e); } }
/** * Excel表格导出 * @param response HttpServletResponse对象 * @param excelData Excel表格的数据，封装为List<List<String>> * @param sheetName sheet的名字 * @param fileName 导出Excel的文件名 * @throws IOException 抛IO异常 */ public static void preDownLoad(HttpServletResponse response,List<List<String>> excelData, String sheetName,String fileName) throws IOException { //声明一个工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); //生成一个表格，设置表格名称 HSSFSheet sheet = workbook.createSheet(sheetName); //设置表格列宽度 sheet.setDefaultColumnWidth(20); //写入List<List<String>>中的数据 int rowIndex = 0; HSSFCellStyle styleTitle = ExcelUtils.getColumnTopStyle(workbook); HSSFCellStyle styleContent = ExcelUtils.getStyle(workbook); for (int t=0;t<excelData.size();t++) { List<String> data = excelData.get(t); //创建一个row行，然后自增1 HSSFRow row = sheet.createRow(rowIndex++); //遍历添加本行数据 for (int i = 0; i < data.size(); i++) { //创建一个单元格 HSSFCell cell = row.createCell(i); //创建一个内容对象 HSSFRichTextString text = new HSSFRichTextString(data.get(i)); //将内容对象的文字内容写入到单元格中 cell.setCellValue(text); if (t == 0){ cell.setCellStyle(styleTitle); }else { cell.setCellStyle(styleContent); } } } //准备将Excel的输出流通过response输出到页面下载 //八进制输出流 response.setContentType("application/octet-stream"); //设置导出Excel的名称 response.setHeader("Content-disposition", "attachment;filename=" + java.net.URLEncoder.encode(fileName, "UTF-8")); //刷新缓冲 response.flushBuffer(); //workbook将Excel写入到response的输出流中，供页面下载该Excel文件 workbook.write(response.getOutputStream()); //关闭workbook workbook.close(); }
import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.VerticalAlignment; /** * @Auther: * @Date: 2020/9/9 11:54 * @Description: */ public class ExcelUtils { /* * 列头单元格样式 */ public static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) { // 设置字体 HSSFFont font = workbook.createFont(); // 设置字体大小 font.setFontHeightInPoints((short) 11); // 字体加粗 font.setBold(true); // 设置字体名字 font.setFontName("Courier New"); // 设置样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置低边框 style.setBorderBottom(BorderStyle.THIN); // 设置低边框颜色 style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); // 设置右边框 style.setBorderRight(BorderStyle.THIN); // 设置顶边框 style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); // 设置顶边框颜色 style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); // 在样式中应用设置的字体 style.setFont(font); // 设置自动换行 style.setWrapText(false); // 设置水平对齐的样式为居中对齐； style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); return style; } public static 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(BorderStyle.THIN); // 设置底边框颜色; style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); // 设置左边框; style.setBorderLeft(BorderStyle.THIN); // 设置左边框颜色; style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); // 设置右边框; style.setBorderRight(BorderStyle.THIN); // 设置右边框颜色; style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); // 设置顶边框; style.setBorderTop(BorderStyle.THIN); // 设置顶边框颜色; style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); // 在样式用应用设置的字体; style.setFont(font); // 设置自动换行; style.setWrapText(false); // 设置水平对齐的样式为居中对齐; style.setAlignment(HorizontalAlignment.CENTER); // 设置垂直对齐的样式为居中对齐; style.setVerticalAlignment(VerticalAlignment.CENTER); return style; } }
