java通过jxl导出excel表格

在日常工作中，经常会碰到需要将数据导出至excel表格中，便于打印等功能

下面就通jxl来将编辑excel表格并且导出

public String createExcel(File savePath, List<JSONObject> data) {
        if (data == null) {
            throw new BaseException("保存失败");
        }

        WritableWorkbook writableWorkbook = null;
        try {
            // 创建表格
            writableWorkbook = Workbook.createWorkbook(savePath);
            // 创建Sheet 
            WritableSheet writableSheet = writableWorkbook.createSheet("sheet1", 0);
            //单元格格式
            WritableCellFormat centerCellFormat = centerCellFormat();

            writeTitle(writableSheet);
            writeSecondRows(writableSheet);
            writeTableTitle(writableSheet, centerCellFormat);

            int row = 3;
            int cel = 0;
            Iterator<JSONObject> iterator = data.iterator();
            //填充表格数据
            while (iterator.hasNext()) {
                cel = 0;
                JSONObject detailedData = iterator.next();

                //保存格式为文本
                Label textLabel = new Label(cel, row, detailedData.getString("text"), centerCellFormat);
                //列宽
                writableSheet.setColumnView(cel, 14);

                //保存格式为数字
                Number numberCount = new Number(++cel, row, Integer.parseInt(detailedData.getString("number")), centerCellFormat);
                //列宽
                writableSheet.setColumnView(cel, 14);

                writableSheet.addCell(textLabel);
                writableSheet.addCell(numberCount);

                //行高
                writableSheet.setRowView(row, 600);
                row++;
            }

            writableWorkbook.write();
            return "SAVESUCCESS";
        } catch (IOException e) {
            logger.error("保存文件失败", e);
        } catch (RowsExceededException e) {
            logger.error("保存文件失败", e);
        } catch (WriteException e) {
            logger.error("保存文件失败", e);
        } finally {
            if (writableWorkbook != null) {
                try {
                    writableWorkbook.close();
                } catch (WriteException e) {
                    logger.error("close WritableWorkbook error", e);
                } catch (IOException e) {
                    logger.error("close WritableWorkbook error", e);
                }
            }
        }
    }

通常表格组成： 第一行表格标题， 第二行重要的几个数据，比如日期，之类的，第三行表格的列标题，后面就是表格数据

这里先画表格第一行的标题

/**
     * 
     * writeTitle:画表格第一行标题 <br/>
     * @param writableSheet
     * @throws WriteException
     */
    private static void writeTitle(WritableSheet writableSheet) throws WriteException {
        // 设置单元格的样式
        WritableCellFormat titleCellFormat = new WritableCellFormat();
        // 设置水平居中
        titleCellFormat.setAlignment(Alignment.CENTRE);
        // 设置垂直居中
        titleCellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
        // 加边框
        titleCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
        titleCellFormat.setFont(new WritableFont(WritableFont.createFont("宋体"), 26, WritableFont.BOLD));

        //合并第一行的单元格，两列，合并两列
        writableSheet.mergeCells(0, 0, 2, 0);
        Label titleLabel = new Label(0, 0, "表格第一行标题", titleCellFormat);
        writableSheet.addCell(titleLabel);
    }

然后再画第二行的数据

/**
     * 
     * writeSecondRows: 画表格第二行 <br/>
     * 
     * @param writableSheet
     * @param optDate
     *              操作日期
     * @throws WriteException
     */
    private void writeSecondRows(WritableSheet writableSheet, String optDate) throws WriteException {
        // 设置单元格的样式
        WritableCellFormat cellFormat = new WritableCellFormat();
        // 设置水平居中
        cellFormat.setAlignment(Alignment.LEFT);
        // 设置垂直居中
        cellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
        // 加边框
        cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
        cellFormat.setFont(new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.BOLD));

        StringBuffer optTime = new StringBuffer();

        //处理时间
        optTime.append(optDate.substring(0, 4)).append("-").append(optDate.substring(4, 6))
                .append("-").append(optDate.substring(6, 8));

        //合并第二行的单元格
        writableSheet.mergeCells(0, 1, 2, 1);
        Label optDateLabel = new Label(0, 1, "日期：" + optTime, cellFormat);
        writableSheet.addCell(optDateLabel);
    }

最后画第三行

/**
     * 
     * writeTableTitle: 画表格第三行 列标题 <br/>
     * 
     * @param writableSheet
     * @param centerCellFormat
     * @throws WriteException
     */
    private void writeTableTitle(WritableSheet writableSheet, WritableCellFormat centerCellFormat)
            throws WriteException {
        Label textLabel = new Label(0, 2, "text", centerCellFormat);
        Label numberCount = new Label(1, 2, "number", centerCellFormat);

        writableSheet.addCell(textLabel);
        writableSheet.addCell(numberCount);
    }

最后，还有两个比较常用的格式

    private static WritableCellFormat centerCellFormat() throws WriteException {
        WritableCellFormat cellFormat = new WritableCellFormat();
        // 内容水平居中
        cellFormat.setAlignment(Alignment.CENTRE);
        // 内容垂直居中
        cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
        // 加边框
        cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
        cellFormat.setFont(new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.NO_BOLD));
        // 自动换行
        cellFormat.setWrap(true);

        return cellFormat;
    }

private static WritableCellFormat cellLeftFormat() throws WriteException {
        // 设置普通字体，大小11，黑色
        WritableFont fontSize = new WritableFont(WritableFont.TIMES, 11);
        WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(fontSize);
        // 设置单元格样式
        wcfFC.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
        wcfFC.setAlignment(jxl.format.Alignment.LEFT);
        wcfFC.setWrap(true);
        return wcfFC;
    }

读取excel

public static void read (Workbook book) {  
        int rows;  
        //得到Excel中工作表数  
        int sheetNumber = book.getNumberOfSheets();  
        //得到每个表的名称  
        String [] sheetNameList = book.getSheetNames();  
        //得到每个工作表  
        Sheet [] sheetList = book.getSheets();  
          
        //遍历每个单元格，得到单元格的内容，并输出。注意得到单元格的内容是以字符串形式返回的。  
        for(int i = 0;i < sheetNumber;i++) {  
            System.out.println("############## " + sheetNameList[i] + " ##############");  
            //得到该工作表中数据的行数  
            rows = sheetList[i].getRows();  
            for(int j = 0;j < rows;j++) {  
                //得到每一行的单元格  
                Cell [] cellList = sheetList[i].getRow(j);  
                for (Cell cell : cellList) {  
                    System.out.print(cell.getContents() + "  ");  
                }  
                System.out.println();  
            }            
        }        
    }  
    public static void main(String [] args) throws IOException, BiffException  {  
        try {  
            //文件D://JEtest//read.xls 要存在，并且含有数据。  
            File file = new File("D://JEtest//read.xls");  
            //得到工作簿  
            Workbook book = Workbook.getWorkbook(file);   
            TRead.read(book);  
            book.close();              
        }catch (IOException | BiffException e) {  
            System.out.println("Exception:  " + e);  
            throw e;  
        }  
    }