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

一.导入Excel表数据

1.ServiceImpl层代码

public List importInvoice(InputStream is) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        List 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 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;


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

二.导出成Excel表

1.ServiceImpl层代码

 public void exportInvoice(List 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 
2.Controller层代码
 
 @GetMapping("/exportInvoice")
    public AjaxResult exportInvoice(@Validated HtTicketInvoiceManagement htTicketInvoiceManagement){
        List 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 list = iHtTicketInvoiceManagementService.queryList(bo);
        List ts = BeanUtil.copyToList(list, HtTicketInvoiceManagementVo.class);
        ExcelUtil util = new ExcelUtil(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层代码
 
    @GetMapping("/downLoadExcel")
    public void downLoadExcel(HttpServletResponse response, HttpServletRequest request)throws Exception{
        iHtTicketInvoiceManagementService.downLoadExcel(response,request);
    }