java实现的导出Excel工具类

不知不觉一个多月没更新博客了,因为这段时间博主辞职了,过着醉生梦死般的生活。哈哈哈哈

今天开始正式入职新公司,翻看着公司以前写好的项目,准备熟悉一下业务以及代码风格,不知道怎么回事,越看越蛋疼,代码里充斥着大量重复的逻辑功能。还有就是莫名其妙就会有中文首字母缩写的变量名、方法名、文件名。崩溃。(谷歌翻译,百度翻译是个好东西)。然后最后重点关注了一下util工具类这个包,哎,工具类真的太少了,由此可见,这项目是很少用工具类的。可能是业务需要的少,但我觉得更大的可能还是开发人员懒得封装提取代码吧。(毕竟一顿复制粘贴可谓是神级操作,让我这个程序员放弃复制去写代码,不可能的,别想了  ~^o^~)

废话不多说,看代码,如何通过编写更少的代码去实现下载excel表功能

给你们看一下公司的一个controller层源代码,只贴了两个映射method功能(我没改动前):

  @GetMapping("/UserExcelDownloads")
    public void downloadAllClassmate(HttpServletResponse response) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("信息表");
        sheet.setColumnWidth(0, 3766);
        sheet.setColumnWidth(1, 3766);
        sheet.setColumnWidth(2, 3766);
        sheet.setColumnWidth(3, 3766);
        sheet.setColumnWidth(4, 3766);
        sheet.setColumnWidth(5, 3766);
        sheet.setColumnWidth(6, 3766);
        sheet.setColumnWidth(7, 3766);
        sheet.setColumnWidth(8, 3766);


        List<Map> classmateList = saleService.teacherinfor();

        String fileName = URLEncoder.encode("入库单Excel表");//设置要导出的文件的名字
        //新增数据行,并且设置单元格数据

        int rowNum = 1;

        String[] headers = {"", "投售人姓名", "单据类型", "未验货数量", "已验货数量", "净重", "应付金额", "已付金额", "剩余金额"};
        //headers表示excel表中第一行的表头

        HSSFRow row = sheet.createRow(0);
        //在excel表中添加表头

        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        //在表中存放查询到的数据放入对应的列
        for (Map teacher : classmateList) {
            HSSFRow row1 = sheet.createRow(rowNum);
            row1.createCell(0).setCellValue(rowNum);
            row1.createCell(1).setCellValue(Util.Object2String(teacher.get("name")));
            row1.createCell(2).setCellValue(Util.Object2String(teacher.get("bill_type")));
            row1.createCell(3).setCellValue(Util.Object2String(teacher.get("not")));
            row1.createCell(4).setCellValue(Util.Object2String(teacher.get("one")));
            row1.createCell(5).setCellValue(Util.Object2String(teacher.get("check_weight")));
            row1.createCell(6).setCellValue(Util.Object2String(teacher.get("money")));

            row1.createCell(7).setCellValue(Util.Object2String(teacher.get("payment_amount")));
            row1.createCell(8).setCellValue(Util.Object2String(teacher.get("alwaysmoney")));
            rowNum++;
        }

        response.setContentType("application/octet-stream;charset=utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + new String("入库单Excel表") + ".xls");//fileName
        response.flushBuffer();
        workbook.write(response.getOutputStream());

    }


@GetMapping("/dispatchSurplusExcel")
    public void download(HttpServletResponse response) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("信息表");
        sheet.setColumnWidth(0, 3766);
        sheet.setColumnWidth(1, 3766);
        sheet.setColumnWidth(2, 3766);
        sheet.setColumnWidth(3, 3766);
        sheet.setColumnWidth(4, 3766);
        sheet.setColumnWidth(5, 3766);


        List<Map> classmateList = dispatchService.dispatchSurplusExcelList();

        //设置要导出的文件的名字
        String fileName = "汇款单Excel表";
        //新增数据行,并且设置单元格数据

        int rowNum = 1;

        String[] headers = {"", "投售人名称", "汇款单据号", "汇款时间", "投售人预付金额", "剩余金额"};
        //headers表示excel表中第一行的表头

        HSSFRow row = sheet.createRow(0);
        //在excel表中添加表头

        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        //在表中存放查询到的数据放入对应的列
        for (Map teacher : classmateList) {
            HSSFRow row1 = sheet.createRow(rowNum);
            row1.createCell(0).setCellValue(rowNum);
            row1.createCell(1).setCellValue(Util.Object2String(teacher.get("seller_name")));
            row1.createCell(2).setCellValue(Util.Object2String(teacher.get("surpius_id")));
            row1.createCell(3).setCellValue(Util.Object2String(teacher.get("surplus_time")));
            row1.createCell(4).setCellValue(Util.Object2String(teacher.get("seller_prepaid_money")));
            row1.createCell(5).setCellValue(Util.Object2String(teacher.get("surplus_noney")));
            rowNum++;
        }

        response.setContentType("application/octet-stream;charset=utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".xls");
        response.flushBuffer();
        workbook.write(response.getOutputStream());

    }

 

这样的代码可见是非常不美观的,代码耦合度高,重复代码多,对程序员开发来说,是非常耗时耗力的,于是我就花了一些功夫把他提取成一个工具类

/**
 * @author hz
 * @Description:  封装 excel文件导出
 * @create 2019-07-05
 */
public class ExcelUtil {
    private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     *
     * @param flag  true 第一列数据为数字序号,false 第一列为正常数据显示
     * @param workbook  HSSFWorkbook对象
     * @param fileName  下载后文件名称
     * @param sheetName 工作簿sheet表名称 null为默认名称“sheet0”
     * @param sheetCount  表列数总合
     * @param headers    表第一列 标题名称数组
     * @param list    表需要填入数据对象
     * @param tableNames 根据标题名称一一对应数据库映射字段(为了从map.get(key)取值)
     * @param response
     * @return
     */
    public static boolean  downLoadExcel(boolean flag,HSSFWorkbook workbook,String fileName, String sheetName, int sheetCount, String headers[],List<Map> list,String tableNames[],HttpServletResponse response)
            throws Exception{
        //第一列为数字序号
        if(flag){
            if(sheetCount!=headers.length || sheetCount-1 != tableNames.length){
                log.info("第一列为序号时,sheetCount参数和headers数组长度一致,且比tableNames数组长度大1,请核对代码");
                System.out.println("请核对sheet长度和headers,tableNames");
                return false;
            }
        }
        //第一列为正常数据,不为数字
        if(flag==false){
            if(sheetCount!=headers.length || sheetCount != tableNames.length){
                log.info("sheetCount参数和headers数组tableNames数组长度应一致,请核对代码");
                System.out.println("请核对sheet长度,headers,tableNames");
                return false;
            }
        }
        int rowNum = 1;
        HSSFSheet sheet = null;
        if(sheetName==null){
            sheet = workbook.createSheet();//默认名称 “sheet0”
        }else{
            sheet = workbook.createSheet(sheetName);
        }
        for(int i=0;i<sheetCount;i++){
            sheet.setColumnWidth(i, 3766);
        }
        HSSFRow row = sheet.createRow(0);//设置第一行所有标题字段
        //表格每列所有字段名称赋值
        for (int i = 0; i < headers.length; i++) {
            row.createCell(i).setCellValue(new HSSFRichTextString(headers[i]));
        }
        for (Map map : list) {
            HSSFRow row1 = sheet.createRow(rowNum);
            if(flag){
                for(int i=0;i<headers.length;i++){
                    if(i==0){
                        row1.createCell(i).setCellValue(rowNum);
                        continue;
                    }
                    row1.createCell(i).setCellValue(Util.Object2String(map.get(tableNames[i-1])));
                }

            }else{
                for(int i=0;i<headers.length;i++){
                    row1.createCell(i).setCellValue(Util.Object2String(map.get(tableNames[i])));
                }
            }
            rowNum++;
        }
        response.setContentType("application/octet-stream;charset=utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".xls");
        response.flushBuffer();
        workbook.write(response.getOutputStream());
        return true;
    }

    }
}

 

@Controller
public class TestController {
    private static final Logger log = LoggerFactory.getLogger(TestController .class);

    @RequestMapping("/downLoadExcel")
    public void downLoadExcel(HttpServletResponse response){
        HSSFWorkbook workbook = new HSSFWorkbook();
        int rowNum = 1;
        String headers[] = {"","姓名", "性别", "年龄"};
        String tableNames[]={"name","sex","age"};
        //在表中存放查询到的数据放入对应的列
        List<Map> list = new ArrayList<Map>();
        Map map = new HashMap();
        map.put("name", "黄先生");
        map.put("sex", "男");
        map.put("age", "23");
        list.add(map);
        try{
            Boolean flag = ExcelUtil.downLoadExcel(true,workbook, "用户表","信息表", 4, headers,list,tableNames,response);
        }catch (Exception e){
            e.printStackTrace();
            log.info("downLoadExcel()方法中ExcelUtil.downLoadExcel()出现异常");
        }
    }

}

一个简单的excel下载工具类就这样封装好了。果然看起来舒服多了。哈哈哈哈

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值