java poi读写Excel

1.引入java POI依赖

<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.14</version>
		</dependency>

2.读取Excel文件内容 

file为Excel文件名，index为Excel中工作表顺序（从0开始），IO流读写文件后必须关闭流，不然会出现内存溢出等问题。

此处使用XSSFWorkBook 对象，Excel文件格式为 .xlsx，读取.xls格式的换成HSSFWorkbook 即可

Excel文件内容存放在list集合中，对list集合进行下一步操作即可。

public List<Object[]> readForeignExcel(File file, Integer index) {
        ArrayList<Object[]> list = Lists.newArrayList();
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(file);
            XSSFWorkbook wb = new XSSFWorkbook(fis);
            XSSFSheet sheet = wb.getSheetAt(index);
            //第一行为标题
            int firstRowIndex = sheet.getFirstRowNum() + 1;   //第1行是列名，不读
            int lastRowIndex = sheet.getLastRowNum();
            for (int i = firstRowIndex; i <= lastRowIndex; i++) {
                Object[] obj = new Object[5];
                Row row = sheet.getRow(i);
                if (row != null) {
                    obj[0] = (row.getCell(0).toString().trim());
                    list.add(obj);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (fis != null) {
                    fis.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

3.写入并下载Excel文件

try {
            String desc = Category.valueOf(category).getDesc();
            //Excel 表名
            String fileName = bname+ desc + name + "gdi" + "-" + format + "-"+ ".xls";
            ByteArrayOutputStream os = showManageService.getInfoExcel(name,category,sta,date);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
            ServletOutputStream out = response.getOutputStream();
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (IOException e) {
            return "失败";
        } finally {
            try {
                if (bis != null)
                    bis.close();
                if (bos != null) ;
                bos.close();
            } catch (Exception e) {
                return "in error";
            }
        }

name，category，sta等都是数据库查询条件，可自行更改。 

public ByteArrayOutputStream getInfoExcel(String name,String category,Integer sta,Date day) throws IOException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        List<Show> shows = Lists.newArrayList();
        if (sta == 0) {   
            shows = showRepo.findByCategoryAndOfflineDateAndReleaseStatus(category);
        }
        if (sta == 1 || sta == 2 || sta == 3) {   
            shows = showRepo.findByCategoryAndReleaseStatus(category, sta);
        }   
        List<Integer> ids = shows.stream().map(i -> i.getId()).collect(Collectors.toList());
        
        HSSFWorkbook wb = new HSSFWorkbook();
        String sheetName = "信息表";
        HSSFSheet sheet = wb.createSheet(sheetName);
        String[] title = "a","b","c","d"};   //列名
        //获取表头行
        HSSFRow titleRow = sheet.createRow(0);
        //创建单元格，设置style居中，字体，单元格大小等
        CellStyle style = wb.createCellStyle();
        Cell cell = null;
        //把已经写好的标题行写入excel文件中
        for (int i = 0; i < title.length; i++) {
            cell = titleRow.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }
        //设置单元格宽度自适应，在此基础上把宽度调至1.5倍
        for (int i = 0; i < title.length; i++) {
            sheet.autoSizeColumn(i, true);
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 15 / 10);
        }
        //数据库查询结果  作为Excel内容
        List<ShowLogs> showList = Lists.newArrayList();       
        showList = this.findByCategoryAndDayAndShowId(category, day,ids);
        //把从数据库中取得的数据一一写入excel文件中
        HSSFRow data = null;
        for (int i = 0; i < showList .size(); i++) {
            //创建list.size()行数据
            data = sheet.createRow(i+1);
            //把值一一写进单元格里
            //设置第一列为自动递增的序号
            data.createCell(0).setCellValue(showList .get(i).getxxxx());
            data.createCell(1).setCellValue(showList .get(i).getxxxx());
            data.createCell(2).setCellValue(showList .get(i).getxxxx());
            data.createCell(3).setCellValue(showList .get(i).getxxxx());
            data.createCell(4).setCellValue(showList .get(i).getxxxx());                    
            //把时间转换为指定格式的字符串再写入excel文件中
            if (showList.get(i).getDay() != null) {
                data.createCell(5).setCellValue(sdf.format(showList.get(i).getDay()));
            }
        }
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        wb.write(os);
        return os;
    }