导出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;
    }
}

 

posted on 2020-12-08 09:46  LJD泊水  阅读(124)  评论(0编辑  收藏  举报