java后台处理excel表格的读写操作

public class ExcelUtil {

    public static void writeBookExcel(List<Book> list, HttpServletResponse response){
        try {
            String filename= "用户查询结果.xlsx";
            //创建一个excel的内存对象
            Workbook wb = null;

            if (filename.endsWith("xls")){
                wb = new HSSFWorkbook();
            }else{
                wb = new XSSFWorkbook();
            }

            //创建sheet页
            Sheet sheet = wb.createSheet("用户查询结果");
            //添加表头
            String titleRow[] ={"编号","姓名","密码"};

            Row row = sheet.createRow(0);//第一行
            Cell cell= row.createCell(0);
            cell.setCellValue("用户查询结果表");
            //设置行高
            row.setHeight((short) 540);//540=27像素
            CellStyle style = wb.createCellStyle();

            style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
            style.setAlignment(HorizontalAlignment.CENTER);//水平居中
            style.setWrapText(true);

            cell.setCellStyle(style);

            //定义字体
            Font font = wb.createFont();
            font.setFontName("微软雅黑");
            font.setFontHeight((short) 280);
            style.setFont(font);

            //合并单元格
            sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
            //第二行数据
            row = sheet.createRow(1);
            for (int i = 0; i < titleRow.length; i++) {
                cell = row.createCell(i);
                cell.setCellValue(titleRow[i]);
                cell.setCellStyle(style);
                sheet.setColumnWidth(1,20*256);
            }
            row.setHeight((short) 540);

            for (int i = 0; i < list.size(); i++) {
                row = sheet.createRow(i+2);
                row.setHeight((short) 500);
                row.createCell(0).setCellValue(list.get(i).getId());
                //后面自己补齐
            }
            //response.sethead需要写
            OutputStream stream = response.getOutputStream();
            wb.write(stream);
            stream.close();
            



        } catch (Exception e) {
            e.printStackTrace();
        }


    }

    public static List<Book> readBookExcel(File file){
        List<Book> list = new ArrayList<>();

        try {
            FileInputStream inputStream =new FileInputStream(file);

            //定义一个空的excel文件
            Workbook wb = null;

            //根据excel文件的扩展名，进行不同的实例化
            if (file.getName().endsWith("xls")){//xls是老版的excel文件扩展名
                wb = new HSSFWorkbook(inputStream);
            }else{
                wb = new XSSFWorkbook(inputStream);//针对xlsx格式的excel文件
            }

            //获取第一个sheet页
            Sheet sheet = wb.getSheetAt(0);

            //从当前的sheet页中，获取所有行数据
            Iterator<Row> rows=  sheet.rowIterator();

            //由于表格中数据的前两行是没有用的数据，因此向下移动2行数据
            rows.next();
            rows.next();

            Book book=null;
            //通过循环的方式处理数据
            while (rows.hasNext()){
                Row row = rows.next();
                book = new Book();
                book.setId(row.getCell(0).getStringCellValue());
                book.setBookname(row.getCell(1).getStringCellValue());
                book.setBookauthor(row.getCell(2).getStringCellValue());
                book.setBookprice((double) row.getCell(3).getNumericCellValue());
                list.add(book);
            }


        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
}