POI导入导出Excel文件（二）

最近工作中用到的POI导入导出Excel文件的地方比较到，所以就乘机总结一下，在这里先说以导出Excel的情况简单的说一下，现在就直接上代码，在代码上面做注释了。

    /**
     * 处理导出的数据信息
     * @param list
     * @return
     */
    private int rownum = 0;   //在这里是创建一个全局变量，用来表示创建的行数
    private void exportExcelData(String title,List<String> headerList,List<Yangbyj> dataList,HttpServletResponse response, String fileName) throws Exception{
        //创建excel文件
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(500);
        //创建sheet页面
        Sheet createSheet  = sxssfWorkbook.createSheet("Export");
        //创建excel的文件样式
        Map<String, CellStyle> createStyles = createStyles(sxssfWorkbook);
        //创建标题
        if(StringUtils.isNotBlank(title)){
            //创建sheet中的行
            Row titleRow = createSheet.createRow(rownum++);
            titleRow.setHeightInPoints(30);  //设置行高
            Cell titleCell = titleRow.createCell(0);  //创建标题的单元格
            titleCell.setCellStyle(createStyles.get("title"));   //设置单元格的样式
            titleCell.setCellValue(title);  //给单元格赋值
            //合并单元格
            createSheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
                    titleRow.getRowNum(),titleRow.getRowNum(),headerList.size()-1));
        }
        //创建表头
        if(headerList == null||headerList.size()==0){
            throw new RuntimeException("headerList not null!");
        }
        //创建表头行
        Row headerRow = createSheet.createRow(rownum++);
        headerRow.setHeightInPoints(16);
        for(int i=0;i<headerList.size();i++){
            Cell cell = headerRow.createCell(i);
            cell.setCellStyle(createStyles.get("header"));
            cell.setCellValue(headerList.get(i));
            /*给表格添加批注
             * Comment comment = createSheet.createDrawingPatriarch().createCellComment(
                    new XSSFClientAnchor(0, 0, 0, 0, 3, 3, 5, 6));
            cell.setCellComment(comment);*/
            createSheet.autoSizeColumn(i);  //自动调整列宽
        }
        //设置宽度
        for(int i=0;i<headerList.size();i++){
            int colWidth = createSheet.getColumnWidth(i)*2;
            createSheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
        }
        //处理数据信息
        for (Yangbyj y : dataList) {
                Row row = createSheet.createRow(rownum++);
                CellStyle style = createStyles.get("data2");
                Cell oneCell = row.createCell(0);
                DataFormat format = sxssfWorkbook.createDataFormat();
                style.setDataFormat(format.getFormat("yyyy/MM/dd"));
                oneCell.setCellValue((Date) y.getShij());
                oneCell.setCellStyle(style);
                Cell twoCell = row.createCell(1);
                twoCell.setCellValue( y.getYunj());
                twoCell.setCellStyle(style);
        }
        //通过浏览器将数据写出来
        response.reset();
        response.setContentType("application/octet-stream; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename="+Encodes.urlEncode(fileName));
        sxssfWorkbook.write(response.getOutputStream());
    }

下面是excel的样式，可以直接拿来用。

private Map<String, CellStyle> createStyles(Workbook wb) {
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

        CellStyle style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        Font titleFont = wb.createFont();
        titleFont.setFontName("Arial");
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style.setFont(titleFont);
        styles.put("title", style);

        style = wb.createCellStyle();
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        styles.put("data", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(CellStyle.ALIGN_LEFT);
        styles.put("data1", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(CellStyle.ALIGN_CENTER);
        styles.put("data2", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        styles.put("data3", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
//      style.setWrapText(true);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);

        return styles;
    }

上面只是简单的说以下我这里用到的部分，后续的自己也在学习中，多多斧正。