写点什么

一篇文章搞定 Java 处理 Excel 的各种疑难杂症

用户头像
知春秋
关注
发布于: 2020 年 05 月 16 日


文章教程源码可以关注上方微信公众号:*程序员小兔* 回复关键字:poi,即可免费获取。

简介


Apache POI 是Apache软件基金会的开放源码函式库,POI 提供 API 给 Java 程序对 Microsoft Office 格式档案读和写的功能。官网网站:[http://poi.apache.org/index.html](http://poi.apache.org/components/index.html)

Maven


<!-- Excel的2003版本Jar --><!-- https://mvnrepository.com/artifact/org.apache.poi/poi --><dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi</artifactId>    <version>4.1.2</version></dependency>
<!-- Excel的2007版本Jar --><!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --><dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.2</version></dependency>
复制代码


Gradle


// Excel的2003版本Jar// https://mvnrepository.com/artifact/org.apache.poi/poicompile group: 'org.apache.poi', name: 'poi', version: '4.1.2'
// Excel的2007版本Jar// https://mvnrepository.com/artifact/org.apache.poi/poi-ooxmlcompile group: 'org.apache.poi', name: 'poi-ooxml', version: '4.1.2'
复制代码


快速体验


创建 Excel


		// 创建工作簿        Workbook wb = new HSSFWorkbook(); //new XSSFWorkbook(); 分别是07和03对象        // 创建工作表,在工作簿对象中创建才可以,这里创建2个        Sheet sheet1 = wb.createSheet("工作表1");        Sheet sheet2 = wb.createSheet("工作表2");        sheet1.createRow(0).createCell(0).setCellValue("第1行,第1个单元格数据");        sheet1.createRow(1).createCell(0).setCellValue("第2行,第1个单元格数据");        try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {            // 将创建好的Excel表格写出            wb.write(fileOut);        } catch (FileNotFoundException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        }
复制代码


结果

读取 Excel


这里使用上面 创建 Excel 输出的 Excel 表格作为数据源读取数据。


		try (InputStream fileInput = new FileInputStream("workbook.xls")) {            // 根据输入流创建工作簿            Workbook wb = new HSSFWorkbook(fileInput);            // 读取第一个工作表            Sheet sheet1 = wb.getSheetAt(0);            // 读取第一行第一个单元格的信息            String value1 = sheet1.getRow(0).getCell(0).getStringCellValue();            System.out.println(MessageFormat.format("第一行第一个单元格信息:{0}", value1));            // 读取第二行第一个单元格的信息            String value2 = sheet1.getRow(1).getCell(0).getStringCellValue();            System.out.println(MessageFormat.format("第二行第一个单元格信息:{0}", value2));        } catch (FileNotFoundException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        }
复制代码


结果

开发指南


读取 Excel 方法对比


打开工作簿时,无论是.xls 还是.xlsx 格式都可以从 File 或 InputStream 加载工作簿。使用 File 对象可以降低内存消耗,而 InputStream 则需要更多的内存,因为它必须缓冲整个 Excel 文件。


读取 Excel 的时候建议使用WorkbookFactory对象,这个对象可以识别.xls 和.xlsx 两种格式的 Excel。


        // 使用 File 方法加载Excel        Workbook wb1 = WorkbookFactory.create(new File("workbook.xls"));
// 使用 InputStream 方法加载Excel,这种方式需要更多的内存,如果Excel表很大的时候将占用服务器性能 Workbook wb2 = WorkbookFactory.create(new FileInputStream("workbook.xls"));
复制代码


上面的方式加载 Excel 是无法控制生命周期,所以建议使用一下方法加载 Excel。POIFSFileSystemOPCPackage 对象来控制生命周期。


        // HSSFWorkbook File方式        POIFSFileSystem fs1 = new POIFSFileSystem(new File("workbook.xls"));        Workbook wb1 = new HSSFWorkbook(fs1.getRoot(), true);		// do something        // 关闭        fs1.close();
// HSSFWorkbook InputStream方式 POIFSFileSystem fs2 = new POIFSFileSystem(new FileInputStream("workbook.xls")); Workbook wb2 = new HSSFWorkbook(fs2.getRoot(), true); // do something // 关闭
// XSSFWorkbook File方式 OPCPackage open1 = OPCPackage.open(new File("workbook.xls")); Workbook wb3 = new XSSFWorkbook(open1); // do something // 关闭 open1.close();
// XSSFWorkbook InputStream方式 OPCPackage open2 = OPCPackage.open(new FileInputStream("workbook.xls")); Workbook wb4 = new XSSFWorkbook(open2); // do something // 关闭 open2.close();
复制代码


WorkbookFactory对象的create方法也支持POIFSFileSystemOPCPackage 参数。

读取 Excel 的所有 Sheet

方式一


第一种方式是使用自带的sheetIterator/rowIterator/cellIterator迭代器的方式获得数据。这种方式会跳过空行和空单元格。


        try (Workbook wb = WorkbookFactory.create(new File("workbook.xls"))) {            Iterator<Sheet> iterator = wb.sheetIterator();            // 遍历多个Shell            while (iterator.hasNext()) {                Iterator<Row> rowIterator = iterator.next().rowIterator();                // 遍历多个Row                while (rowIterator.hasNext()) {                    // 方式一:拿到row对象                    Row row = rowIterator.next();                    String value = row.getCell(0).getStringCellValue();
// 方式二:获取每一个单元格的值 Iterator<Cell> cellIterator = rowIterator.next().cellIterator(); // 遍历每一列 while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); } } } } catch (InvalidFormatException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); }
复制代码


方式二


第二种方式是使用for-each的方式隐式遍历。这种方式会跳过空行和空单元格。


        try (Workbook wb = WorkbookFactory.create(new File("workbook.xls"))) {            for (Sheet sheet : wb) {                for (Row row : sheet) {                    for (Cell cell : row) {                                            }                }            }        } catch (InvalidFormatException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        }
复制代码


读取单元格的值


获取单元格的内容,首先需要知道单元格的类型。如果向 字符串 单元格获取 数值 的内容。那么就会抛出 NumberFormatException异常。因此需要先确定单元格的类型,才通过getter方法获取单元格的内容。


        // 创建一个数据格式化对象        DataFormatter formatter = new DataFormatter();        try (Workbook wb = new HSSFWorkbook(new FileInputStream("workbook.xls"))) {            Sheet sheet = wb.getSheetAt(0);            for (Row row : sheet) {                for (Cell cell : row) {                    // 获取单元格的编号如A1,B1,A2,B2                    CellReference cellReference = new CellReference(row.getRowNum(), cell.getColumnIndex());                    System.out.print(cellReference.formatAsString());                    System.out.print(" - ");
// 格式单元格的值,作为字符串返回,不要用这个方法读取值,因为精度只保留4位 String text = formatter.formatCellValue(cell); System.out.println(text); // 区分不同类型的单元格数值格式,分别处理 switch (cell.getCellTypeEnum()) { case STRING: // 字符串类型 System.out.println(cell.getRichStringCellValue().getString()); break; case NUMERIC: // 数值类型 // 如果是日期类型的双精度,另外判断是否为日期类型 if (DateUtil.isCellDateFormatted(cell)) { System.out.println(cell.getDateCellValue()); } else { System.out.println(cell.getNumericCellValue()); } break; case BOOLEAN: // 布尔值类型 System.out.println(cell.getBooleanCellValue()); break; case FORMULA: // 公式类型 System.out.println(cell.getCellFormula()); break; case BLANK: // 空字符串 System.out.println(); break; default: System.out.println(); } } } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); }
复制代码


提取 Excel 表所有单元格内容

对于 Excel 表的单元格内容提取,之前的做法就是遍历RowCell。POI 提供了方法一次性读取整个 Excel 表的所有内容。


        try (InputStream inp = new FileInputStream("workbook.xls");             HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp))) {            ExcelExtractor extractor = new ExcelExtractor(wb);            extractor.setFormulasNotResults(true);            extractor.setIncludeSheetNames(false);            String text = extractor.getText();            System.out.println(text);        } catch (FileNotFoundException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        }
复制代码


Cell 支持的基础数据类型


cell 除了支持基础的数据类型外,还支持输出公式、图片、下拉菜单等特殊的单元格样式。下面会持续讲解。对于 Cell 中的内容如果需要换行,可以使用\n符号。


		// 创建工作簿        Workbook wb = new HSSFWorkbook(); //new XSSFWorkbook(); 分别是07和03对象        // 创建工作表,在工作簿对象中创建才可以,这里创建2个        Sheet sheet1 = wb.createSheet("工作表1");        Row row1 = sheet1.createRow(0);        // 数值类型        row1.createCell(0).setCellValue(100.10);        // 日期类型,将日期的值设置为等价的Excel表的值。        // 如果不设置日期格式,那么就是日期将变为数值        row1.createCell(1).setCellValue(new Date());        // 设置单元格的日期值        row1.createCell(2).setCellValue(Calendar.getInstance());        // 字符串类型        row1.createCell(3).setCellValue("string text");        // 布尔值类型        row1.createCell(4).setCellValue(true);        try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {            // 将创建好的Excel表格写出            wb.write(fileOut);        } catch (FileNotFoundException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        }
复制代码


结果

日期格式化

        // 创建工作簿        Workbook wb = new HSSFWorkbook(); //new XSSFWorkbook(); 分别是07和03对象        // 创建工作表        Sheet sheet1 = wb.createSheet("工作表1");        // 日期不格式化输出默认样式        Row row1 = sheet1.createRow(0);        row1.createCell(0).setCellValue(new Date());
// --------------------------S 自定义单元格格式[方式1]-------------------------- // 创建一个单元格样式,那个单元格需要这个样式就设置到那个单元格 CellStyle cellStyle1 = wb.createCellStyle(); // 从workbook中创建DateFormat对象 DataFormat dataFormat = wb.createDataFormat(); // 给cellStyle表格样式设置好日期格式类型 cellStyle1.setDataFormat(dataFormat.getFormat("yyyy-MM-dd HH:mm:ss"));
// 使用上面创建好的cellStyle样式格式化时间 Cell cell1 = row1.createCell(1); cell1.setCellStyle(cellStyle1); cell1.setCellValue(new Date()); // --------------------------E 自定义单元格格式[方式1]--------------------------
// --------------------------S 自定义单元格格式[方式2]-------------------------- // 获取一个创建者助手,这个对象可以处理WorkBook的所需的各种类实例化 CreationHelper creationHelper = wb.getCreationHelper(); // 创建一个日期格式对象 DataFormat dfm = creationHelper.createDataFormat(); // 创建一个单元格格式化对象 CellStyle cellStyle2 = wb.createCellStyle(); cellStyle2.setDataFormat(dfm.getFormat("m/d/yy h:mm"));
// 设置日期 Cell cell2 = row1.createCell(2); cell2.setCellStyle(cellStyle2); cell2.setCellValue(new Date()); // --------------------------E 自定义单元格格式[方式2]-------------------------- try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { // 将创建好的Excel表格写出 wb.write(fileOut); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); }
复制代码

结果

单元格对齐


单元格样式主要由CellStyle对象来控制。其中单元格对齐主要由setAlignment *水平对齐* 和setVerticalAlignment 垂直对齐 两个参数控制。


    // 抽取公共方法    private static void createCell(Workbook wb, Row row, int column, HorizontalAlignment halign, VerticalAlignment valign) {        Cell cell = row.createCell(column);        cell.setCellValue("Vertical And Align It");        // 创建单元格样式        CellStyle cellStyle = wb.createCellStyle();        // 设置水平对齐方式        cellStyle.setAlignment(halign);        // 设置垂直对齐方式        cellStyle.setVerticalAlignment(valign);        cell.setCellStyle(cellStyle);    }
复制代码


        Workbook wb = new HSSFWorkbook();        Sheet sheet = wb.createSheet();        Row row = sheet.createRow(1);        // 将行高设置为50        row.setHeightInPoints(50);        // 创建不同的对齐方式,具体的组合以及字段含义可以查询官方API,下面是列出了常用的组合        createCell(wb, row, 0, HorizontalAlignment.CENTER, VerticalAlignment.BOTTOM);        createCell(wb, row, 1, HorizontalAlignment.CENTER_SELECTION, VerticalAlignment.BOTTOM);        createCell(wb, row, 2, HorizontalAlignment.FILL, VerticalAlignment.CENTER);        createCell(wb, row, 3, HorizontalAlignment.GENERAL, VerticalAlignment.CENTER);        createCell(wb, row, 4, HorizontalAlignment.JUSTIFY, VerticalAlignment.JUSTIFY);        createCell(wb, row, 5, HorizontalAlignment.LEFT, VerticalAlignment.TOP);        createCell(wb, row, 6, HorizontalAlignment.RIGHT, VerticalAlignment.TOP);        createCell(wb, row, 7, HorizontalAlignment.CENTER, VerticalAlignment.CENTER);        // Write the output to a file        try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {            wb.write(fileOut);        } catch (FileNotFoundException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        }        wb.close();
复制代码


结果

设置单元格边框样式


        Workbook wb = new HSSFWorkbook();        Sheet sheet = wb.createSheet();        Row row = sheet.createRow(1);        Cell cell = row.createCell(1);        cell.setCellValue("string text");        // 单元格的边框CellStyle支持的方法有8个        // 分别是“上“”下“”左“”右”的边框类型和颜色        // 创建一个单元格样式        CellStyle cellStyle = wb.createCellStyle();        // 左 边框的类型 破折号+圆点        cellStyle.setBorderLeft(BorderStyle.DASH_DOT);        // 右 边框的类型 破折号        cellStyle.setBorderRight(BorderStyle.DASHED);        // 上 边框的类型 圆点        cellStyle.setBorderTop(BorderStyle.DOTTED);        // 下 边框的类型 双线条        cellStyle.setBorderBottom(BorderStyle.DOUBLE);        // 左 边框的颜色        cellStyle.setLeftBorderColor(IndexedColors.AQUA.index);        // 右 边框的颜色        cellStyle.setRightBorderColor(IndexedColors.AUTOMATIC.index);        // 上 边框的颜色        cellStyle.setTopBorderColor(IndexedColors.BLUE.index);        // 下 边框的颜色        cellStyle.setBottomBorderColor(IndexedColors.DARK_BLUE.index);
cell.setCellStyle(cellStyle); try (OutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } wb.close();
复制代码


结果

设置单元格的属性

设置单元格的属性其实就是另外一种方法给单元格添加样式。


        Workbook workbook = new HSSFWorkbook();        Sheet sheet = workbook.createSheet("Sheet1");        Map<String, Object> properties = new HashMap<String, Object>();        // 单元格周边的边框样式        properties.put(CellUtil.BORDER_TOP, BorderStyle.MEDIUM);        properties.put(CellUtil.BORDER_BOTTOM, BorderStyle.MEDIUM);        properties.put(CellUtil.BORDER_LEFT, BorderStyle.MEDIUM);        properties.put(CellUtil.BORDER_RIGHT, BorderStyle.MEDIUM);        // 单元格边框的颜色        properties.put(CellUtil.TOP_BORDER_COLOR, IndexedColors.RED.getIndex());        properties.put(CellUtil.BOTTOM_BORDER_COLOR, IndexedColors.RED.getIndex());        properties.put(CellUtil.LEFT_BORDER_COLOR, IndexedColors.RED.getIndex());        properties.put(CellUtil.RIGHT_BORDER_COLOR, IndexedColors.RED.getIndex());        // 将B2设置为这个样式        Row row = sheet.createRow(1);        Cell cell = row.createCell(1);        cell.setCellValue("String text");        CellUtil.setCellStyleProperties(cell, properties);        try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {            // 将创建好的Excel表格写出            workbook.write(fileOut);        } catch (FileNotFoundException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        }
复制代码


结果

单元格填充


单元格的填充两种:背景填充,前景填充。其中的填充风格可以看FillPatternType类。填充的颜色可以看IndexedColors类。


        try (Workbook wb = new HSSFWorkbook();             OutputStream out = new FileOutputStream("workbook.xls")) {            Sheet sheet = wb.createSheet("sheet 0");            Row row = sheet.createRow(1);            // 创建单元格背景填充样式            CellStyle cellStyle1 = wb.createCellStyle();            // 设置填充颜色            cellStyle1.setFillBackgroundColor(IndexedColors.BLUE.getIndex());            // 设置填充样式            cellStyle1.setFillPattern(FillPatternType.BIG_SPOTS);            Cell cell1 = row.createCell(1);            cell1.setCellStyle(cellStyle1);            cell1.setCellValue("填充背景颜色");
// 创建单元格前景色填充样式 CellStyle cellStyle2 = wb.createCellStyle(); // 设置填充颜色 cellStyle2.setFillForegroundColor(IndexedColors.AQUA.getIndex()); // 设置填充样式 cellStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND); Cell cell2 = row.createCell(2); cell2.setCellStyle(cellStyle2); cell2.setCellValue("填充前景颜色");
// 生成Excel表格 wb.write(out); } catch (IOException e) { e.printStackTrace(); }
复制代码


结果

合并单元格


合并单元格需要操作多行多列,而包含多行多列的对象就是Sheet。所有合并单元格是对Sheet操作。


        try (Workbook wb = new HSSFWorkbook();             OutputStream out = new FileOutputStream("workbook.xls")) {            Sheet sheet = wb.createSheet("sheet 0");            Row row = sheet.createRow(1);            Cell cell = row.createCell(1);            cell.setCellValue("Merging Cell");
// 合并单元格,需要4个参数,分别是上下左右 4个坐标,坐标都是从0开始计算 // 合并单元格操作是在Sheet对象中操作的,因为只有Sheet才有多个行和多个列 // 创建单元格合并地址,4个参数依次:开始行,结束行,开始列,结束列 CellRangeAddress cellRangeAddress = new CellRangeAddress(1, 1, 1, 2); // 给Sheet添加合并单元格对象 sheet.addMergedRegion(cellRangeAddress);
// 更加便捷的方式合并单元格,只需要对角的坐标就可以 CellRangeAddress cellRangeAddress1 = CellRangeAddress.valueOf("D1:E4"); sheet.addMergedRegion(cellRangeAddress1);
// 对于合并了的单元格,有工具类很方便的设置边框样式和颜色 RegionUtil.setBorderBottom(BorderStyle.MEDIUM_DASHED, cellRangeAddress, sheet); RegionUtil.setBorderTop(BorderStyle.MEDIUM_DASHED, cellRangeAddress, sheet); RegionUtil.setBorderLeft(BorderStyle.MEDIUM_DASHED, cellRangeAddress, sheet); RegionUtil.setBorderRight(BorderStyle.MEDIUM_DASHED, cellRangeAddress, sheet); RegionUtil.setBottomBorderColor(IndexedColors.AQUA.getIndex(), cellRangeAddress, sheet); RegionUtil.setTopBorderColor(IndexedColors.AQUA.getIndex(), cellRangeAddress, sheet); RegionUtil.setLeftBorderColor(IndexedColors.AQUA.getIndex(), cellRangeAddress, sheet); RegionUtil.setRightBorderColor(IndexedColors.AQUA.getIndex(), cellRangeAddress, sheet);
// 生成Excel表格 wb.write(out); } catch (IOException e) { e.printStackTrace(); }
复制代码


结果

自定义字体样式


字体的设置主要是根据 Excel 表中存在的属性去配置。


        try (Workbook wb = new HSSFWorkbook();             OutputStream out = new FileOutputStream("workbook.xls")) {            Sheet sheet = wb.createSheet("sheet 0");            Row row = sheet.createRow(1);            Cell cell = row.createCell(1);
// 创建一个新的字体,并添加到工作簿的字体中 Font font = wb.createFont(); // 颜色 font.setColor(IndexedColors.BLUE.getIndex()); // 高度 font.setFontHeightInPoints((short) 32); // 给字体一个名字 font.setFontName("chun qiu"); // 加粗 font.setBold(true); // 斜体 font.setItalic(true);
// 所有的单元格样式都要通过这个对象配置 CellStyle cellStyle = wb.createCellStyle(); cellStyle.setFont(font);
cell.setCellStyle(cellStyle); cell.setCellValue("Create Cell Fonts");
// 生成Excel表格 wb.write(out); } catch (IOException e) { e.printStackTrace(); }
复制代码


结果

扩展:自定义颜色


HSSFWorkbook getCustomPalette 方法可以获取到一个调色板类HSSFColor,这个类的addColor方法接受三个参数,分别是、*绿*、三个色调。


XSSFWorkbook中则是通过XSSFFont setColor(XSSFColor xssfColor)方法定义、*绿*、三个色调。


特殊设置


sheet 放大或缩小


sheet1.setZoom(75); // 缩放为75%
复制代码


冻结窗口


// 参数一:分割的水平位置// 参数二:分割的垂直位置sheet1.createFreezePane(3, 2);// 参数一:分割的水平位置// 参数二:分割的垂直位置// 参数三:左列在右窗口中可见// 参数四:顶部在底部窗口中可见sheet1.createFreezePane(3, 2, 3, 2);
复制代码


切割窗口


// 将一个Excel切割为2个一样的,数据同步// 参数一:切割的水平位置// 参数二:切割的垂直位置// 参数三:顶部在做底部可见// 参数四:左边在右边可见// 参数五:活动面板。可选值:PANE_LOWER_RIGHT, PANE_UPPER_RIGHT, PANE_LOWER_LEFT, PANE_UPPER_LEFTsheet2.createSplitPane(2000, 2000, 0, 0, Sheet.PANE_LOWER_LEFT);
复制代码


添加图片


添加图片是绘画工具支持的,添加图片使用 createPicture 方法。目前支持的图片类型有:PNG/JPG/DIB。


图片读取使用 workbook.getAllPictures 方法。


        try (Workbook wb = new HSSFWorkbook();             InputStream is = new FileInputStream("fill-cell.jpg");             OutputStream fileOut = new FileOutputStream("workbook.xls")) {            // 添加图片数据到工作簿中            int picture = wb.addPicture(IOUtils.toByteArray(is), Workbook.PICTURE_TYPE_JPEG);
CreationHelper helper = wb.getCreationHelper(); Sheet sheet = wb.createSheet();
// 创建一个绘画工具 Drawing drawing = sheet.createDrawingPatriarch(); // 添加一个绘画的区域 ClientAnchor anchor = helper.createClientAnchor();
// 图片左上角坐标(第二行,第三列开始) anchor.setRow1(2); anchor.setCol1(3);
Picture picture1 = drawing.createPicture(anchor, picture);
// 自动调整图片左上角大小 picture1.resize();
wb.write(fileOut); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); }
复制代码


结果

调整列宽适应内容


Sheet sheet = workbook.getSheetAt(0);sheet.autoSizeColumn(0); // 调整第一类宽度自适应内容sheet.autoSizeColumn(1); // 调整第二类宽度自适应内容
复制代码


### 读取超链接内容


Sheet sheet = workbook.getSheetAt(0);Cell cell = sheet.getRow(0).getCell(0);Hyperlink link = cell.getHyperlink();if(link != null){    System.out.println(link.getAddress());}
复制代码


创建超链接


Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();CreationHelper createHelper = wb.getCreationHelper();//cell style for hyperlinks//by default hyperlinks are blue and underlinedCellStyle hlink_style = wb.createCellStyle();Font hlink_font = wb.createFont();hlink_font.setUnderline(Font.U_SINGLE);hlink_font.setColor(IndexedColors.BLUE.getIndex());hlink_style.setFont(hlink_font);Cell cell;Sheet sheet = wb.createSheet("Hyperlinks");
// 创建URL超链接cell = sheet.createRow(0).createCell(0);cell.setCellValue("URL Link");Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);link.setAddress("https://poi.apache.org/");cell.setHyperlink(link);cell.setCellStyle(hlink_style);
// 创建链接到本地目录文件的链接cell = sheet.createRow(1).createCell(0);cell.setCellValue("File Link");link = createHelper.createHyperlink(Hyperlink.LINK_FILE);link.setAddress("link1.xls");cell.setHyperlink(link);cell.setCellStyle(hlink_style);
// 邮箱的链接cell = sheet.createRow(2).createCell(0);cell.setCellValue("Email Link");link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL);
// 笔记,如果有文本请编码link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");cell.setHyperlink(link);cell.setCellStyle(hlink_style);
// 链接到工作簿中的一个位置// 创建一个工作表Sheet sheet2 = wb.createSheet("Target Sheet");sheet2.createRow(0).createCell(0).setCellValue("Target Cell");cell = sheet.createRow(3).createCell(0);cell.setCellValue("Worksheet Link");Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);link2.setAddress("'Target Sheet'!A1");cell.setHyperlink(link2);cell.setCellStyle(hlink_style);try (OutputStream out = new FileOutputStream("hyperinks.xlsx")) { wb.write(out);}wb.close();
复制代码


限制单元格的输入值

方式一

        try (HSSFWorkbook wb = new HSSFWorkbook();             OutputStream fileOut = new FileOutputStream("workbook.xls")) {            HSSFSheet sheet = wb.createSheet("数据校验");            // 校验的单元格范围            /**             * 参数1:起始行             * 参数2:结束行             * 参数3:起始列             * 参数4:结束列             */            CellRangeAddressList addressList = new CellRangeAddressList(                    0, 0, 0, 0);            // 创建校验单元格的取值范围            DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(                    new String[]{"10", "20", "30"});            // 设置校验的单元格和取值范围的关系            DataValidation dataValidation = new HSSFDataValidation                    (addressList, dvConstraint);            // 用于列表校验对象            dataValidation.setSuppressDropDownArrow(true);            // 设置错误的提示样式,STOP一旦填错就不允许操作其他单元格,只能继续填写正确            dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);            // 设置错误提示表格内容            dataValidation.createErrorBox("错误提示标题", "你的输入有误");            // 鼠标出现再单元格的时候出现这个提示            dataValidation.createPromptBox("内容输入的值提示标题:", "你只可以输入[10,20,30]");            // 允许提示框出现            dataValidation.setShowPromptBox(true);            sheet.addValidationData(dataValidation);
wb.write(fileOut); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); }
复制代码


结果



方式二:下拉选项框


限制单元格输入的值,并且提供一个下拉选项框给用户选择。


        try (HSSFWorkbook wb = new HSSFWorkbook();             OutputStream fileOut = new FileOutputStream("workbook.xls")) {            HSSFSheet sheet = wb.createSheet("下拉列表");            // 校验的单元格范围            /**             * 参数1:起始行             * 参数2:结束行             * 参数3:起始列             * 参数4:结束列             */            CellRangeAddressList addressList = new CellRangeAddressList(                    0, 0, 0, 0);            // 创建校验单元格的取值范围            DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(                    new String[]{"10", "20", "30"});            // 用于列表校验对象            DataValidation dataValidation = new HSSFDataValidation                    (addressList, dvConstraint);            // 设置错误的提示样式,STOP一旦填错就不允许操作其他单元格,只能继续填写正确            dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);            dataValidation.setSuppressDropDownArrow(false);            sheet.addValidationData(dataValidation);
wb.write(fileOut); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); }
复制代码



文章教程源码可以关注上方微信公众号:*程序员小兔* 回复关键字:poi,即可免费获取。


发布于: 2020 年 05 月 16 日阅读数: 310
用户头像

知春秋

关注

不忘初心,方得始终。初心易得,始终难守。 2020.04.29 加入

Java高级工程师,从业多年主要负责互联网电商、金融系统开发与架构设计。对分布式存储、分布式缓存、分布式消息中间件、分布式搜索引擎 / 实时流式计算、容器技术有一定的研究和认识,对主流开源框架源码熟悉。

评论

发布
暂无评论
一篇文章搞定Java处理Excel的各种疑难杂症