Excel的导入导出功能

　　 java 操作 Excel 最常用的就是JXL(java excel api)和POI，今先看下JXL吧。

 　　JXL 用起来挺简单的，不过相应的其功能也并不是十分强大，对于一般的简单的excel操作还可以，对于太复杂的还是有些吃力，基本的操作也就是以下几项内容。

类介绍

　　先熟悉一下主要使用的类

（1）WritableWorkbook：抽象的工作薄类

（2）WritableSheet：抽象的工作表类

（3）WritableFont：设置字体的类

（4）WritableCellFormat：设置内容的背景颜色，表框，文字对齐等

（5）Label：单元格，可以通过（4）设置相关属性

（6）Workbook：导入相关，获取导入Excel数据的类

（7）Sheet：一般和Workbook配合使用，Workbook是整个Excel表格，Sheet就是其中一个工作表

实战

导出

1. 导出工具ExcelUtils

public class ExcelUtils {

    /**
     * 导出excel，需要第一行的title
     * @param fileName
     * @param names
     * @param title
     * @param objects
     * @return
     * @throws Exception
     */
    public static File exportObjects(String fileName, String[] names,
            String title, List<String[]> objects) throws Exception {
        File excelFile = new File("fileName.xls");
        //创建工作薄
        WritableWorkbook wtwb = Workbook.createWorkbook(excelFile);
        //获取工作表
        WritableSheet sheet = wtwb.createSheet(title, 0);
        //所有的列设置默认的列的宽度;
        sheet.getSettings().setDefaultColumnWidth(20);
        //字体
        WritableFont wfont = new WritableFont(WritableFont.createFont("楷书"), 15);
        //字体属性设置
        WritableCellFormat format = new WritableCellFormat(wfont);
        //文字属性设置
        WritableFont wfc = new WritableFont(WritableFont.ARIAL, 20,
                WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
                jxl.format.Colour.BLACK);
        //添加文字属性
        WritableCellFormat wcfFC = new WritableCellFormat(wfc);
        wcfFC.setAlignment(Alignment.CENTRE);
        wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);
        // CellView cellView = new CellView();
        // cellView.setAutosize(true); //设置自动大小
        format.setAlignment(Alignment.LEFT);
        format.setVerticalAlignment(VerticalAlignment.TOP);
        //首行单元格合并设置
        sheet.mergeCells(0, 0, names.length - 1, 0);
        sheet.addCell(new Label(0, 0, title, wcfFC));
        
        //从第二行开始
        int rowNum = 2;
        //将数据添加到每行Excel中
        for (int i = 0; i < names.length; i++) {
            sheet.addCell(new Label(i, 1, names[i], format));
        }
        for (int j = 0; j < objects.size(); j++) {
            String[] obj = objects.get(j);
            for (int h = 0; h < obj.length; h++) {
                sheet.addCell(new Label(h, rowNum, obj[h], format));
            }
            rowNum = rowNum + 1;

        }
        wtwb.write();
        wtwb.close();
        return excelFile;
    }

    /**
     * 导出excel，不需要第一行的title
     *
     * @param fileName
     * @param names
     * @param title
     * @param objects
     * @return
     * @throws Exception
     */
    public static File exportObjectsWithoutTitle(String fileName,
            String[] names, String title, List<String[]> objects)
            throws Exception {
        File excelFile = new File(fileName);
        WritableWorkbook wtwb = Workbook.createWorkbook(excelFile);
        WritableSheet sheet = wtwb.createSheet(title, 0);
        sheet.getSettings().setDefaultColumnWidth(20);

        // 第一行的格式
        WritableFont wfc = new WritableFont(WritableFont.ARIAL, 15,
                WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
                jxl.format.Colour.BLACK);
        WritableCellFormat wcfFC = new WritableCellFormat(wfc);
        wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);

        // 设置字体以及单元格格式
        WritableFont wfont = new WritableFont(WritableFont.createFont("楷书"), 15);
        WritableCellFormat format = new WritableCellFormat(wfont);
        format.setAlignment(Alignment.LEFT);
        format.setVerticalAlignment(VerticalAlignment.TOP);

        // 第一行写入标题
        for (int i = 0; i < names.length; i++) {
            sheet.addCell(new Label(i, 0, names[i], wcfFC));
        }

        // 其余行依次写入数据
        int rowNum = 1;
        for (int j = 0; j < objects.size(); j++) {
            String[] obj = objects.get(j);
            for (int h = 0; h < obj.length; h++) {
                sheet.addCell(new Label(h, rowNum, obj[h], format));
            }
            rowNum = rowNum + 1;
        }
        wtwb.write();
        wtwb.close();
        return excelFile;
    }
}

2. 导出下载工具

public class ExportExecUtil {
    public static void showExec(File excelFile,String fileName,HttpServletResponse response) throws Exception{
           response.setContentType("application/octet-stream");
           fileName = new String(fileName.getBytes("gbk"),"ISO8859_1");
           response.setHeader("Content-Disposition", "attachment;filename=\"" + fileName + ".xls" + "\"");
           FileInputStream fis = new FileInputStream(excelFile);
           OutputStream out = response.getOutputStream();

           int SIZE = 1024 * 1024;
           byte[] bytes = new byte[SIZE];
           int LENGTH = -1;
           while((LENGTH = fis.read(bytes)) != -1){
               out.write(bytes,0,LENGTH);
           }

           out.flush();
           fis.close();
    }
}

3. 导出请求Controller

 @RequestMapping(value = "/exportExcel")
    public void exportExcel(@RequestParam("supplier") String supplier,
                            @RequestParam("type") String type,
                            @RequestParam("phonenum") String phonenum,
                            @RequestParam("telephone") String telephone,
                            @RequestParam("description") String description,
                            HttpServletRequest request, HttpServletResponse response)throws Exception {
        BaseResponseInfo res = new BaseResponseInfo();
        Map<String, Object> map = new HashMap<String, Object>();
        String message = "成功";
        try {
            List<Supplier> dataList = supplierService.findByAll(supplier, type, phonenum, telephone, description);
            String[] names = {"名称", "类型", "联系人", "电话", "电子邮箱", "预收款", "期初应收", "期初应付", "备注", "传真", "手机", "地址", "纳税人识别号", "开户行", "账号", "税率", "状态"};
            String title = "信息报表";
            List<String[]> objects = new ArrayList<String[]>();
            if (null != dataList) {
                for (Supplier s : dataList) {
                    String[] objs = new String[17];
                    objs[0] = s.getSupplier();
                    objs[1] = s.getType();
                    objs[2] = s.getContacts();
                    objs[3] = s.getPhonenum();
                    objs[4] = s.getEmail();
                    objs[5] = s.getAdvancein() == null? "" : s.getAdvancein().toString();
                    objs[6] = s.getBeginneedget() == null? "" : s.getBeginneedget().toString();
                    objs[7] = s.getBeginneedpay() == null? "" : s.getBeginneedpay().toString();
                    objs[8] = s.getDescription();
                    objs[9] = s.getFax();
                    objs[10] = s.getTelephone();
                    objs[11] = s.getAddress();
                    objs[12] = s.getTaxnum();
                    objs[13] = s.getBankname();
                    objs[14] = s.getAccountnumber();
                    objs[15] = s.getTaxrate() == null? "" : s.getTaxrate().toString();
                    objs[16] = s.getEnabled() ? "启用" : "禁用";
                    objects.add(objs);
                }
            }
            File file = ExcelUtils.exportObjectsWithoutTitle(title, names, title, objects);
            ExportExecUtil.showExec(file, file.getName(), response);
            res.code = 200;
        } catch (Exception e) {
            e.printStackTrace();
            message = "导出失败";
            res.code = 500;
        } finally {
            map.put("message", message);
            res.data = map;
        }
    }

导入

    /**
     * 导入excel表格
     * @param supplierFile
     * @param response
     * @return
     */
    @RequestMapping(value = "/importExcelMember")
    public void importExcelMember(MultipartFile supplierFile,HttpServletResponse response) throws Exception{
        importFun(supplierFile);
        response.sendRedirect("../pages/manage/member.html");
    }

    public String importFun(MultipartFile supplierFile)throws Exception{
        
        BaseResponseInfo info = new BaseResponseInfo();
        Map<String, Object> data = new HashMap<String, Object>();
        String message = "成功";
        try {
            Sheet src = null;
            //文件合法性校验
            try {
                Workbook workbook = Workbook.getWorkbook(supplierFile.getInputStream());
                src = workbook.getSheet(0);
            } catch (Exception e) {
                message = "导入文件不合法，请检查";
                data.put("message", message);
                info.code = 400;
                info.data = data;
            }
            //每行中数据顺序 "名称","类型","联系人","电话","电子邮箱","预收款","期初应收","期初应付","备注","传真","手机","地址","纳税人识别号","开户行","账号","税率","状态"
            List<Supplier> sList = new ArrayList<Supplier>();
            for (int i = 1; i < src.getRows(); i++) {
                //实体类
                Supplier s = new Supplier();
                s.setSupplier(ExcelUtils.getContent(src, i, 0));
                s.setType(ExcelUtils.getContent(src, i, 1));
                s.setContacts(ExcelUtils.getContent(src, i, 2));
                s.setPhonenum(ExcelUtils.getContent(src, i, 3));
                s.setEmail(ExcelUtils.getContent(src, i, 4));
                s.setAdvancein(parseBigDecimalEx(ExcelUtils.getContent(src, i, 5)));
                s.setBeginneedget(parseBigDecimalEx(ExcelUtils.getContent(src, i, 6)));
                s.setBeginneedpay(parseBigDecimalEx(ExcelUtils.getContent(src, i, 7)));
                s.setDescription(ExcelUtils.getContent(src, i, 8));
                s.setFax(ExcelUtils.getContent(src, i, 9));
                s.setTelephone(ExcelUtils.getContent(src, i, 10));
                s.setAddress(ExcelUtils.getContent(src, i, 11));
                s.setTaxnum(ExcelUtils.getContent(src, i, 12));
                s.setBankname(ExcelUtils.getContent(src, i, 13));
                s.setAccountnumber(ExcelUtils.getContent(src, i, 14));
                s.setTaxrate(parseBigDecimalEx(ExcelUtils.getContent(src, i, 15)));
                String enabled = ExcelUtils.getContent(src, i, 16);
                s.setEnabled(enabled.equals("启用")? true: false);
                s.setIsystem(Byte.parseByte("1"));
                sList.add(s);
            }
            info = supplierService.importExcel(sList);
        } catch (Exception e) {
            e.printStackTrace();
            message = "导入失败";
            info.code = 500;
            data.put("message", message);
            info.data = data;
        }
        return null;
    }

 

基本使用就如上面，若觉得不足，可自行研究。