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

来源：互联网发布：win10安装ubuntu共存编辑：程序博客网时间：2024/06/05 15:40

简单记录下使用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);    }}

阅读全文

0 0