java使用jxl创建excel表格由于行列有限制，推荐使用poi

简单记录下 使用jxl创建excel遇到的坑

1、异常错误

2、异常分析 

出现了警告 Could not add cell at A257 because it exceeds the maximum column limit，原因在于jxl 使用的是excel 2003，excel 2003列限制和行限制分别是256和65536。当写257行时就提示了改异常。

3、解决 

使用excel 2007代替excel 2003,excel 2007限制行数和列数为1048576和16384。

由于jxl这个包创建的excel 是2003版的，因此引入了poi包来替换jxl。

4、jxl 和 poi创建excel的代码实现

以下分别介绍jxl 和 poi创建excel的方法：

jxl创建excel的代码：

package com.best.oasis.ltlv5.report;

import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.*;

import java.io.FileOutputStream;
import java.io.OutputStream;

public class JxlExcelTest {
    public static void main(String[] args) {
        WritableWorkbook workbook;
        String excelPath = "d:\\temp\\bb" + "测试" + System.currentTimeMillis() + ".xls";
        try {
            OutputStream os = new FileOutputStream(excelPath);
            workbook = Workbook.createWorkbook(os);

            WritableSheet ws = workbook.createSheet("test", workbook.getSheetNames().length);
            ws.getSettings().setDefaultColumnWidth(10);
            ws.getSettings().setHorizontalFreeze(2);
            ws.getSettings().setVerticalFreeze(2);

            //样式
            WritableCellFormat titleWritableCellFormat = createTileWritableCellFormat();
            WritableCellFormat textWritableCellFormat = createTextWritableCellFormat();

            int row = 0;
            for (int i = 0; i <= 100; i++) {
                //创建合并的单元格
                Label lbTitle = new Label(2 * i, row, "标题" + 2 * i, titleWritableCellFormat);
                ws.addCell(lbTitle);
                ws.mergeCells(2 * i, row, 2 * i + 1, row + 1); //startCol startRow endCol endRow
            }

            //添加普通单元格
            for (int j = 2; j < 100; j++) {
                for (int i = 0; i <= 100; i++) {
                    ws.addCell(new Label(2 * i, j, "内容" + j + "行" + (2 * i) + "列", textWritableCellFormat));
                    ws.addCell(new Label(2 * i + 1, j, "内容" + j + "行" + (2 * i + 1) + "列", textWritableCellFormat));
                }
            }

            workbook.write();
            workbook.close();
            //workbook.write(outputStream);
            //outputStream.close();
        } catch (Exception e) {
            System.out.println("It cause Error on WRITTING excel workbook: ");
            e.printStackTrace();
        }

    }

    private static WritableCellFormat createTileWritableCellFormat() throws WriteException {
        WritableFont font = new WritableFont(WritableFont.TIMES, 14, WritableFont.BOLD, false);
        font.setColour(Colour.RED);
        WritableCellFormat cellFormat = new WritableCellFormat(font);
        cellFormat.setBackground(jxl.format.Colour.LIGHT_TURQUOISE);

       setCommonWritableCellFormat(cellFormat);

        return cellFormat;
    }

    private static WritableCellFormat createTextWritableCellFormat() throws WriteException {
        WritableFont font = new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD, false);
        WritableCellFormat cellFormat = new WritableCellFormat(font);
        setCommonWritableCellFormat(cellFormat);
        return cellFormat;
    }

    private static void setCommonWritableCellFormat(WritableCellFormat cellFormat) throws WriteException {
        // 对齐方式
        cellFormat.setAlignment(jxl.format.Alignment.CENTRE);
        cellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
        // 边框
        cellFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
        // 自动换行
        cellFormat.setWrap(true);
    }

}

poi创建excel的代码：

package com.best.oasis.ltlv5.report;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;

public class PoiExcelTest {
    public static void main(String[] args) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("测试表");
        sheet.setDefaultColumnWidth(15); //设置默认列宽度
        sheet.createFreezePane(2, 2); //冻结窗格

        CellStyle cellStyle = createTitleCellStyle(workbook, 14, "宋体", XSSFFont.COLOR_RED, true, HSSFColor.LIGHT_TURQUOISE.index);
        CellStyle commonCelStyle = createCellStyle(workbook);
        Row row0 = sheet.createRow(0);
        for (int i = 0; i <= 100; i++) {
            //创建合并的单元格
            createMergeCell(sheet, row0, 2 * i, cellStyle, "标题" + i, 0, 1, 2 * i, 2 * i + 1);
        }

        for (int j = 2; j < 100; j++) {
            Row rowj = sheet.createRow(j);
            for (int i = 0; i <= 100; i++) {
                createStringCell(rowj, 2 * i, commonCelStyle, "内容" + j + "行" + (2 * i) + "列");
                createStringCell(rowj, 2 * i + 1, commonCelStyle, "内容" + j + "行" + (2 * i + 1) + "列");
            }
        }
        //将生成的excel写到磁盘上
        String excelPath = "d:\\temp\\bb" + "测试" + System.currentTimeMillis() + ".xlsx";
        try {
            FileOutputStream outputStream = new FileOutputStream(excelPath);
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            System.out.println("It cause Error on WRITTING excel workbook: ");
            e.printStackTrace();
        }

    }

    private static void createMergeCell(XSSFSheet sheet, Row row, int colIndex, CellStyle cellStyle, String cellValue,
            int startRow, int endRow, int startCol, int endCol) {
        //合并单元格
        CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, endRow, startCol, endCol);
        sheet.addMergedRegion(cellRangeAddress);
        //创建单元格
        createStringCell(row, colIndex, cellStyle, cellValue);
        //给合并的单元格设置样式
        setRegionStyle(sheet, cellRangeAddress, cellStyle);
    }

    /**
     * 创建单元格
     *
     * @param row       行
     * @param cellIndex 列索引
     * @param cellStyle 单元格样式
     * @param cellValue 单元格内容
     */
    private static void createStringCell(Row row, int cellIndex, CellStyle cellStyle, String cellValue) {
        Cell cell = row.createCell(cellIndex, Cell.CELL_TYPE_STRING);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(cellValue);
    }

    public static void setRegionStyle(Sheet sheet, CellRangeAddress region,
            CellStyle cs) {

        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {

            Row row = sheet.getRow(i);
            if (row == null) {
                row = sheet.createRow(i);
            }
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                org.apache.poi.ss.usermodel.Cell cell = row.getCell(j);
                if (cell == null) {
                    cell = row.createCell(j);
                    cell.setCellValue("");
                }
                cell.setCellStyle(cs);

            }
        }
    }

    /**
     * 创建单元格样式
     * @param workbook
     * @param fontSize
     * @param fontName
     * @param fontColor
     * @param isBold
     * @param background
     * @return
     */
    private static CellStyle createTitleCellStyle(XSSFWorkbook workbook, int fontSize, String fontName, short fontColor,
            boolean isBold,
            short background) {
        CellStyle cellStyle = workbook.createCellStyle();
        //设置字体
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) fontSize); //字体大小
        font.setFontName(fontName); //字体
        font.setColor(fontColor); //字体颜色
        if (isBold) {
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //是否加粗
        }

        //设置背景色
        cellStyle.setFillForegroundColor(background);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setFillBackgroundColor(background);

        setCommonCellStyle(cellStyle);
        return cellStyle;
    }

    private static CellStyle createCellStyle(XSSFWorkbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        setCommonCellStyle(cellStyle);
        return cellStyle;
    }

    private static void setCommonCellStyle(CellStyle cellStyle) {
        //对齐方式
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

        cellStyle.setWrapText(true);//自动换行

        // 设置边框
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
    }
}