POI操作Excel

1.POU操作Excel

1.创建工作薄HSSFWorkbook workbook = new HSSFWorkbook();2.创建工作表HSSFSheet sheet = workbook.createSheet("用户列表");3.创建行,并且设置头标题HSSFRow row = sheet.createRow(0);4.创建单元格HSSFCell cell = row.createCell(0);

---

2.POI操作 Excel样式

 1.1创建合并单元格对象   CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 4); 1.2头标题演示   HSSFCellStyle cellStyle = createCellStyle(workbook, (short) 16); 1.3列标题样式   HSSFCellStyle cellStyle1 = createCellStyle(workbook, (short) 13); 1.4加载合并单元格对象   sheet.addMergedRegion(cellRangeAddress); 1.5设置默认列宽   sheet.setDefaultColumnWidth(25); 1.设置单元格样式   cell.setCellStyle(cellStyle);   cell.setCellValue("用户列表");

---

需要jar包:

xmlbeans-2.6.0.jarpoi-ooxml-3.11-20141221.jarpoi-ooxml-schemas-3.11-20141221.jarpoi-3.11-20141221.jar

---

写操作

reated by 朱博文 on 2017/7/21. */public class ExcelUtil {    /**     * 导出用户的所有列表到excel     * @param userList 用户列表     * @param outputStream 输出流     */    public static void exportExcel(List<User> userList, ServletOutputStream outputStream) {        //1.创建工作薄        HSSFWorkbook workbook = new HSSFWorkbook();        //1.1创建合并单元格对象        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 4);        //1.2头标题演示        HSSFCellStyle cellStyle = createCellStyle(workbook, (short) 16);        //1.3列标题样式        HSSFCellStyle cellStyle1 = createCellStyle(workbook, (short) 13);        //2.创建工作表        HSSFSheet sheet = workbook.createSheet("用户列表");        //加载合并单元格对象        sheet.addMergedRegion(cellRangeAddress);        //设置默认列宽        sheet.setDefaultColumnWidth(25);        //3.创建行,并且设置头标题        HSSFRow row = sheet.createRow(0);        HSSFRow row1 = sheet.createRow(1);        //4.创建单元格        HSSFCell cell = row.createCell(0);        cell.setCellStyle(cellStyle);        cell.setCellValue("用户列表");        String[] titles = {"用户名", "账号", "所属部门", "性别", "电子邮箱"};        for(int i = 0; i < titles.length; i++) {            HSSFCell cell1 = row1.createCell(i);            cell1.setCellStyle(cellStyle1);            cell1.setCellValue(titles[i]);        }        //4.创建单元格;将用户列表写入excel        if (userList != null) {            for(int j = 0 ; j < userList.size();j++) {                HSSFRow row2 = sheet.createRow(j + 2);                HSSFCell cell1 = row2.createCell(0);                cell1.setCellValue(userList.get(j).getName());                HSSFCell cell2 = row2.createCell(1);                cell2.setCellValue(userList.get(j).getAccount());                HSSFCell cell3 = row2.createCell(2);                cell3.setCellValue(userList.get(j).getDept());                HSSFCell cell4 = row2.createCell(3);                cell4.setCellValue(userList.get(j).getMobile());                HSSFCell cell5 = row2.createCell(4);                cell5.setCellValue(userList.get(j).isGender()?"男":"女");                HSSFCell cell6 = row2.createCell(5);                cell6.setCellValue(userList.get(j).getEmail());                HSSFCell cell7 = row2.createCell(5);                cell7.setCellValue(userList.get(j).getBirthday());            }        }        try {            workbook.write(outputStream);        } catch (IOException e) {            e.printStackTrace();        }finally {            try {                workbook.close();            } catch (IOException e) {                e.printStackTrace();            }        }    }    /**     * 创建单元格样式     *     * @param workbook 工作薄     * @param fontSize 字体大小     */    private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontSize) {        //1.2头样式        HSSFCellStyle style = workbook.createCellStyle();        style.setAlignment(CellStyle.ALIGN_CENTER);        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);        //1.2.1创建字体        HSSFFont font = workbook.createFont();        font.setBoldweight(Font.BOLDWEIGHT_BOLD);        font.setFontHeightInPoints(fontSize);        //加载字体        style.setFont(font);        return style;    }}

---

读操作

public static  void importExcel(File userExcel, String userExcelFileName,FileInputStream fileInputStream) throws IOException {        boolean is03Excel = userExcelFileName.matches("^.+\\.(?i)(xls)$");        //1.读取工作薄         fileInputStream = new FileInputStream(userExcel);        Workbook workbook = is03Excel ? new HSSFWorkbook(fileInputStream) : new XSSFWorkbook(fileInputStream);        Sheet sheet = workbook.getSheetAt(0);        if (sheet.getPhysicalNumberOfRows() > 2) {            for (int k = 2; k < sheet.getPhysicalNumberOfRows(); k++) {                User user = new User();                Row row = sheet.getRow(k);                user.setName(row.getCell(0).getStringCellValue());                user.setAccount(row.getCell(1).getStringCellValue());                user.setDept(row.getCell(2).getStringCellValue());                try {                    user.setMobile(row.getCell(3).getStringCellValue());                } catch (Exception e) {                    user.setMobile(BigDecimal.valueOf                            (row.getCell(3).getNumericCellValue()).toString());                    e.printStackTrace();                }                user.setGender(row.getCell(4).getStringCellValue().equals("男"));                user.setEmail(row.getCell(5).getStringCellValue());                if (row.getCell(6).getStringCellValue() != null) {                    user.setBirthday(row.getCell(6).getDateCellValue());                }                //默认用户密码为123456                user.setPassword("123456");                user.setState(User.USER_STATE_VALID);                new UserServiceImpl().save(user);            }        }        workbook.close();        fileInputStream.close();    }

导出文件:

    //导入数据    public void exportExcel() throws IOException {        //1.查找用户列表        userList = userService.findObjects();        //2.导入        HttpServletResponse response = ServletActionContext.getResponse();        //响应的类型        response.setContentType("appliction/x-execl");        //设置响应的请求头        response.setHeader("content-Disposition", "attchment;filename=" + new String("用户名称.xlsx".getBytes(),"ISO-8859-1"));        ServletOutputStream outputStream = response.getOutputStream();        userService.exportExcel(userList, outputStream);        if (outputStream != null) {            outputStream.close();        }    }

总结:

单元格属于行，行属于工作表，工作表属于工作薄样属于式是属于工作薄，运用于单元格；字体是属于工作簿，通过样式运用于单元格