Java导入导出Excel表与下载导入模板（poi方式）

一.导入Excel表数据

1.ServiceImpl层代码

public List<HtTicketInvoiceManagement> importInvoice(InputStream is) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        List<HtTicketInvoiceManagement> htTicketInvoiceManagements=new ArrayList<>();
        Workbook workbook=null;
        //得到一个工作表
        Sheet sheet=null;
        try {
            workbook= WorkbookFactory.create(is);
            sheet=workbook.getSheetAt(0);
        }catch (Exception ex){
            ex.printStackTrace();
        }
        //获得表头
        Row rowHead=sheet.getRow(0);
        //判断表头是否正确
        if (rowHead.getPhysicalNumberOfCells()!=12){
            System.out.println("表头数量不正确！");
        }
        //获得数据的总行数
        int totalRowNum=sheet.getLastRowNum();
        //获得所有数据
        for (int i=1;i<=totalRowNum;i++){
            HtTicketInvoiceManagement htTicketInvoiceManagement=new HtTicketInvoiceManagement();
            //获得第一行对象
            Row row=sheet.getRow(i);
            //获得第一行第0列的string类型对象
            Cell cell=row.getCell(0);
            cell.setCellType(CellType.STRING);
            String projectName=cell.getStringCellValue();
            htTicketInvoiceManagement.setProjectName(projectName);//项目名称

            cell=row.getCell(1);
            cell.setCellType(CellType.NUMERIC);
            Date ticketInvoiceDate=cell.getDateCellValue();
            String date=sdf.format(ticketInvoiceDate);
            Date newDate=sdf.parse(date);//转换日期格式
            Date resultDate=new Date(newDate.getTime());
            htTicketInvoiceManagement.setTicketInvoiceDate(resultDate);//开票/收票日期

            cell=row.getCell(2);
            cell.setCellType(CellType.STRING);
            String invoiceSerial=cell.getStringCellValue();
            htTicketInvoiceManagement.setInvoiceSerial(invoiceSerial);//发票代码

            cell=row.getCell(3);
            cell.setCellType(CellType.STRING);
            String invoiceUnit=cell.getStringCellValue();
            htTicketInvoiceManagement.setInvoiceUnit(invoiceUnit);//开票单位

            cell=row.getCell(4);
            cell.setCellType(CellType.STRING);
            String invoiceType=cell.getStringCellValue();
            String type = null;
            if (invoiceType.equals("增值税专用发票")){
                 type="1";
            }else if (invoiceType.equals("增值税普通发票")){
                type="2";
            }
            htTicketInvoiceManagement.setInvoiceType(type);//发票类型

            cell=row.getCell(5);
            cell.setCellType(CellType.NUMERIC);
            BigDecimal invoiceMoney=BigDecimal.valueOf(cell.getNumericCellValue());
            htTicketInvoiceManagement.setInvoiceMoney(invoiceMoney);//发票金额

            cell=row.getCell(6);
            cell.setCellType(CellType.STRING);
            String ticketPoint=cell.getStringCellValue();
            htTicketInvoiceManagement.setTicketPoint(ticketPoint);//税率

            cell=row.getCell(7);
            cell.setCellType(CellType.STRING);
            String invoiceNumber=cell.getStringCellValue();
            htTicketInvoiceManagement.setInvoiceNumber(invoiceNumber);//发票号码

            cell=row.getCell(8);
            cell.setCellType(CellType.STRING);
            String invoiceContent=cell.getStringCellValue();
            htTicketInvoiceManagement.setInvoiceContent(invoiceContent);//商品名称

            cell=row.getCell(9);
            cell.setCellType(CellType.STRING);
            String taxpayerNumber=cell.getStringCellValue();
            htTicketInvoiceManagement.setTaxpayerNumber(taxpayerNumber);//纳税人识别号

            cell=row.getCell(10);
            cell.setCellType(CellType.STRING);
            String remark=cell.getStringCellValue();
            htTicketInvoiceManagement.setRemark(remark);//备注

            cell=row.getCell(11);
            cell.setCellType(CellType.STRING);
            String contractId=cell.getStringCellValue();
            String cd=htContractMapper.getContractIdByName(contractId);//根据合同名称获取合同id
            htTicketInvoiceManagement.setContractId(cd);//合同id

            htTicketInvoiceManagement.setStatus(0);//状态
            htTicketInvoiceManagement.setFlag(1);//开票OR收票  现在默认是开票
            //htTicketInvoiceManagement.setCurrency("1");//币种

            htTicketInvoiceManagements.add(htTicketInvoiceManagement);//所有数据添加到集合
        }
        System.out.println("导入数据->"+htTicketInvoiceManagements+"\n");
        return htTicketInvoiceManagements;
    }

2.Controlle层代码

@PostMapping("/import")
    public AjaxResult importInvoice(@RequestParam(value = "file",required = true) MultipartFile file) throws Exception {
        String fileName = file.getOriginalFilename();
        if (!fileName.toUpperCase().endsWith(".XLS") && !fileName.toUpperCase().endsWith(".XLSX")) {
            return AjaxResult.error("文件类型错误");
        } else {
            InputStream is = file.getInputStream();
            List<HtTicketInvoiceManagement> list = iHtTicketInvoiceManagementService.importInvoice(is);
            iHtTicketInvoiceManagementService.saveList(list);//批量新增数据方法
        }
        return AjaxResult.success();
    }

3.pojo层记得打上注解（@Excel注解）

/** 项目名称 */
@Excel(name = "项目名称")
private String projectName;

/** 收票/开票时间 */
@JsonFormat(pattern = "yyyy-MM-dd")
@Excel(name = "开票/收票时间",dateFormat = "yyyy-MM-dd")
private Date ticketInvoiceDate;

/** 开票编号 */
@Excel(name = "开票编号")
private String invoiceSerial;

/** 开票单位 */
@Excel(name = "开票单位")
private String invoiceUnit;

/** 发票类型（1.增值税专用发票2.增值税普通发票） */
@Excel(name = "发票类型", width = 50,readConverterExp = "1=增值税专用发票,2=增值税普通发票")
private String invoiceType;

二.导出成Excel表

1.ServiceImpl层代码

 public void exportInvoice(List<HtTicketInvoiceManagement> list, String path)throws Exception{
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        XSSFWorkbook workbook=new XSSFWorkbook();
        XSSFSheet sheet=workbook.createSheet();

        XSSFRow row=sheet.createRow(0);
        row.createCell(0).setCellValue("项目名称");
        row.createCell(1).setCellValue("开票/收票时间");
        row.createCell(2).setCellValue("发票代码");
        row.createCell(3).setCellValue("开票单位");
        row.createCell(4).setCellValue("发票类型");
        row.createCell(5).setCellValue("开票金额");
        row.createCell(6).setCellValue("税率");
        row.createCell(7).setCellValue("发票号码");
        row.createCell(8).setCellValue("商品名称");
        row.createCell(9).setCellValue("发票抬头");
        row.createCell(10).setCellValue("纳税人识别号");
        row.createCell(11).setCellValue("电话");
        row.createCell(12).setCellValue("地址");
        row.createCell(13).setCellValue("开户行");
        row.createCell(14).setCellValue("银行账号");
        row.createCell(15).setCellValue("寄送方式");
        row.createCell(16).setCellValue("种类");
        row.createCell(17).setCellValue("开票OR收票");
        row.createCell(18).setCellValue("备注");
        row.createCell(19).setCellValue("合同名称");

        for (int i=0;i<list.size();i++){
            XSSFRow row1 = sheet.createRow(i + 1);
            row1.createCell(0).setCellValue(list.get(i).getProjectName());
            String dete=sdf.format(list.get(i).getTicketInvoiceDate());//开票时间
            row1.createCell(1).setCellValue(dete);
            row1.createCell(2).setCellValue(list.get(i).getInvoiceSerial());
            row1.createCell(3).setCellValue(list.get(i).getInvoiceUnit());
            String type=null;
            if (list.get(i).getInvoiceType().equals("1")||list.get(i).getInvoiceType()=="1"){
                type="增值税专用发票";
            }else if(list.get(i).getInvoiceType().equals("2")||list.get(i).getInvoiceType()=="2"){
                type="增值税普通发票";
            }
            row1.createCell(4).setCellValue(type);//发票类型
            row1.createCell(5).setCellValue(list.get(i).getInvoiceMoney().toString());
            row1.createCell(6).setCellValue(list.get(i).getTicketPoint()+"%");//税率
            row1.createCell(7).setCellValue(list.get(i).getInvoiceNumber());
            row1.createCell(8).setCellValue(list.get(i).getInvoiceContent());
            row1.createCell(9).setCellValue(list.get(i).getInvoiceTitle());
            row1.createCell(10).setCellValue(list.get(i).getTaxpayerNumber());
            row1.createCell(11).setCellValue(list.get(i).getPhone());
            row1.createCell(12).setCellValue(list.get(i).getAddress());
            row1.createCell(13).setCellValue(list.get(i).getBankDeposit());
            row1.createCell(14).setCellValue(list.get(i).getBankAccount());
            String send=null;
            if (list.get(i).getSendWay()==null){
                send=null;
            }else if (list.get(i).getSendWay()==0){
                send="邮寄";
            }else if (list.get(i).getSendWay()==1) {
                send = "专人送达";
            }
            row1.createCell(15).setCellValue(send);//寄送方式
            String zl=null;
            if (list.get(i).getCategory().equals("1")||list.get(i).getCategory()=="1"){
                zl="未收款";
            }else if (list.get(i).getCategory().equals("2")||list.get(i).getCategory()=="2"){
                zl="已收款";
            }else if (list.get(i).getCategory().equals("3")||list.get(i).getCategory()=="3"){
                zl="部分收款";
            }
            row1.createCell(16).setCellValue(zl);//种类
            String f=null;
            if (list.get(i).getFlag()==1){
                f="开票";
            }else if (list.get(i).getFlag()==2){
                f="收票";
            }
            row1.createCell(17).setCellValue(f);//开票OR收票
            row1.createCell(18).setCellValue(list.get(i).getRemark());
            String contractName=htContractMapper.getContractNameById(list.get(i).getContractId());//合同名称
            row1.createCell(19).setCellValue(contractName);
        }
        FileOutputStream fileOutputStream=new FileOutputStream(path);
        workbook.write(fileOutputStream);

        fileOutputStream.flush();
        fileOutputStream.close();
        workbook.close();
    }

2.Controller层代码

 @GetMapping("/exportInvoice")
    public AjaxResult exportInvoice(@Validated HtTicketInvoiceManagement htTicketInvoiceManagement){
        List<HtTicketInvoiceManagement> list = iHtTicketInvoiceManagementService.findInvoiceAndContractAndMail(htTicketInvoiceManagement);
        try {
            File desktopDir= FileSystemView.getFileSystemView().getHomeDirectory();//获取系统桌面路径
            String desktoPath=desktopDir.getAbsolutePath();
            String path=desktoPath+"\\发票导出.xlsx";
            iHtTicketInvoiceManagementService.exportInvoice(list,path);
        }catch (Exception e){
            e.printStackTrace();
        }
        return AjaxResult.success();
    }

导出方式还可以用自带的工具类来导出如下（MybatisPlus框架）

@ApiOperation("导出发票/退票管理列表")
    @PreAuthorize("@ss.hasPermi('contract:management:export')")
    @Log(title = "发票/退票管理", businessType = BusinessType.EXPORT)
    @GetMapping("/export")
    public void export(@Validated HtTicketInvoiceManagementQueryBo bo, HttpServletResponse response) {
        List<HtTicketInvoiceManagement> list = iHtTicketInvoiceManagementService.queryList(bo);
        List<HtTicketInvoiceManagementVo> ts = BeanUtil.copyToList(list, HtTicketInvoiceManagementVo.class);
        ExcelUtil<HtTicketInvoiceManagementVo> util = new ExcelUtil<HtTicketInvoiceManagementVo>(HtTicketInvoiceManagementVo.class);
        util.exportExcel(response, ts, "发票导出");
    }

三.下载Excel模板

1.ServiceImpl层代码

/**
     * 下载发票导入模板
     */
    public void downLoadExcel(HttpServletResponse response,HttpServletRequest request)throws Exception{
        String fileName ="发票导入模板.xlsx";
        ServletOutputStream out;
        response.setCharacterEncoding("UTF-8");
        //response.setContentType("multipart/form-data");
        response.addHeader("Content-Disposition","attachment;fileName="+fileName);
        //response.setContentType("application/octet-seream");
        //response.setContentType("text/html");
        response.setContentType("application/vnd.ms-excel");
        try{
            String filePath = getClass().getResource("/excelModels/" + fileName).getPath();//文件在项目中的存放路径
            String userAgent = request.getHeader("User-Agent").toLowerCase();
            if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
                fileName = URLEncoder.encode(fileName, "UTF-8");
            } else {
                // 非IE浏览器的处理：
                fileName = new String((fileName).getBytes("UTF-8"), "ISO-8859-1");
            }
            fileName=URLEncoder.encode("发票导入模板.xlsx","utf-8");//这里转换一下UTF-8前端显示中文
            filePath = URLDecoder.decode(filePath, "UTF-8");
            response.setHeader("Content-Disposition","attachment;fileName="+fileName);
            FileInputStream inputStream = new FileInputStream(filePath);
            out = response.getOutputStream();
            int b = 0;
            byte[] buffer = new byte[1024];
            while ((b = inputStream.read(buffer)) != -1) {
                // 写到输出流(out)中
                out.write(buffer, 0, b);
            }
            inputStream.close();

            if (out != null) {
                out.flush();
                out.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

2.Controller层代码

/**
     * 下载发票导入模板
     * @param response
     * @throws Exception
     */
    @GetMapping("/downLoadExcel")
    public void downLoadExcel(HttpServletResponse response, HttpServletRequest request)throws Exception{
        iHtTicketInvoiceManagementService.downLoadExcel(response,request);
    }