1.问题描述
工作中遇到需要使用Java poi读写Excel文件的问题，因为需求中有要求在写文件时创建下拉选择框。按照传统的直接使用List集合保存下拉选择框的选项，再通过poi本身的方法将选择框的选项添加到下拉框中。
一开始编写demo测试时只有几个选项，可以正确的添加下拉框。后来把方法搬到项目中，发现创建的文件中的下拉框的选项数据不能正确的显示。通过对比两个文件，发现唯一的不同点就是下拉框选项的数量存在差异。通过一番查询，发现poi添加下拉框选项的数量被控制在20个以内（官方说法好像是256个字节？记不太清楚了，如果说错了，请大佬批评指正orz）。
2.选项数量(<=20)较少的方法
- 向Excel中写数据，简单样例
/**
* 将lines的内容写入excel中
*
* @param outputPath excel存放路径
* @param lines 需要写入execl的文件
* @throws IOException
*/
public synchronized void writeInExcel(String outputPath, Collection<String[]> lines) throws IOException {
@SuppressWarnings("resource")
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
// 写入Excel表格开头
Row firstRow = sheet.createRow(0);
ArrayList<String> arrayList = new ArrayList<String>();
String[] excelHeadList = {"a","b","c","d","e"};
for (int i = 0; i < excelHeadList.length; i++) {
arrayList.add(excelHeadList[i]);
}
String[] arr = new String[arrayList.size()];
for (int i = 0; i < arrayList.size(); i++) {
arr[i] = arrayList.get(i);
}
writeInRow(firstRow, arr);
if (lines != null && lines.size() > 0) {
// 写入Excel表格内容
AtomicInteger i = new AtomicInteger(1);
lines.forEach(line -> {
Row row = sheet.createRow(i.get());
writeInRow(row, line);
i.set(i.incrementAndGet());
});
DataValidationHelper helper = sheet.getDataValidationHelper();
// 创建审核结果下拉框
createDropDownBox(sheet, helper, checkResultList, 1, i.get() - 1, arr.length - 6, arr.length - 6);
}
// 写入指定的文件
workbook.write(new FileOutputStream(outputPath));
}- 给Excel添加下拉框的方法
/**
* 给Excel的添加下拉框
*
* @param sheet
* @param helper
* @param list 下拉框需要的数据
* @param firstRow 首行
* @param lastRow 尾行
* @param firstCol 首列
* @param lastCol 尾列
*/
private void createDropDownBox(Sheet sheet, DataValidationHelper helper, String[] list, int firstRow, int lastRow,
int firstCol, int lastCol) {
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
DataValidationConstraint constraint = helper.createExplicitListConstraint(list);
DataValidation validation = helper.createValidation(constraint, addressList);
if (validation instanceof XSSFDataValidation) {
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
} else {
validation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(validation);
}- 把数组的内容写到Excel的行
/**
* 将数组中的内容写入excel的行
*
* @param row 行
* @param line 需要写入的数据
*/
public static void writeInRow(Row row, String[] line) {
if (line != null) {
for (int i = 0; i < line.length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(line[i]);
}
}
}通过以上三个方法就可以实现向Excel中写入数据，并给指定的列添加下拉框
- 全部代码
package com.learning;
import org.apache.poi.hssf.util.CellRangeAddressList;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
/**
* @author: alex
* @date: 2020/8/7 下午5:55
*/
public class Test {
/**
* 将lines的内容写入excel中
*
* @param outputPath excel存放路径
* @param lines 需要写入execl的文件
* @throws IOException
*/
public synchronized void writeInExcel(String outputPath, Collection<String[]> lines) throws IOException {
@SuppressWarnings("resource")
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
// 写入Excel表格开头
Row firstRow = sheet.createRow(0);
ArrayList<String> arrayList = new ArrayList<>();
// 创建单元格标题数组
String[] excelHeadList = {"row1","row2","row3","row4","row5"};
for (int i = 0; i < excelHeadList.length; i++) {
arrayList.add(excelHeadList[i]);
}
String[] arr = new String[arrayList.size()];
for (int i = 0; i < arrayList.size(); i++) {
arr[i] = arrayList.get(i);
}
writeInRow(firstRow, arr);
String[] selectList = {"option1","option2","option3","option4","option5"};
if (lines != null && lines.size() > 0) {
// 写入Excel表格内容
AtomicInteger i = new AtomicInteger(1);
lines.forEach(line -> {
Row row = sheet.createRow(i.get());
writeInRow(row, line);
i.set(i.incrementAndGet());
});
DataValidationHelper helper = sheet.getDataValidationHelper();
// 创建审核结果下拉框
createDropDownBox(sheet, helper, selectList, 1, i.get() - 1, arr.length - 6, arr.length - 6);
}
// 写入指定的文件
workbook.write(new FileOutputStream(outputPath));
}
/**
* 给Excel的添加下拉框
*
* @param sheet
* @param helper
* @param list 下拉框需要的数据
* @param firstRow 首行
* @param lastRow 尾行
* @param firstCol 首列
* @param lastCol 尾列
*/
private void createDropDownBox(Sheet sheet, DataValidationHelper helper, String[] list, int firstRow, int lastRow,
int firstCol, int lastCol) {
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
DataValidationConstraint constraint = helper.createExplicitListConstraint(list);
DataValidation validation = helper.createValidation(constraint, addressList);
if (validation instanceof XSSFDataValidation) {
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
} else {
validation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(validation);
}
/**
* 将数组中的内容写入excel的行
*
* @param row 行
* @param line 需要写入的数据
*/
public static void writeInRow(Row row, String[] line) {
if (line != null) {
for (int i = 0; i < line.length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(line[i]);
}
}
}
public static void main(String[] args) {
System.out.println("hello world!!");
Test test = new Test();
List<String[]> list = new ArrayList<>();
String[] strings = {"aaa","bbb","ccc","ddd","eee"};
list.add(strings);
try {
test.writeInExcel("result/test.xlsx",list);
} catch (IOException e) {
e.printStackTrace();
}
}
}实现效果如下图所示：
3.下拉框选项的数量超过了20个
上边的方法只适用于下拉框选项的数量在20个以内的情况，如果选项的数量超过了20个，那么需要使用隐藏sheet的方式来实现。即：使用一个隐藏的sheet表来存储下拉框的选项数量，再通过定位引用的方式来获取到选项的值。
只需要在上面的writeInExcel方法中增加下面的代码段嘛，即可实现在Excel中创建一个隐藏的sheet。
// 创建一个隐藏的sheet，存放下拉框选项
Sheet hiddenSheet = workbook.createSheet("hiddenSelect");
// 把下拉框列表数据放进隐藏sheet
Cell cell = null;
for (int i = 0; i < selectList.length; i++) {
Row row = hiddenSheet.createRow(i);
cell = row.createCell(0);
cell.setCellValue(selectList[i]);
}
Name nameCell = workbook.createName();
nameCell.setNameName(hiddenSheet.getSheetName());
nameCell.setRefersToFormula(hiddenSheet.getSheetName() + "!$A$1:$A$" + selectList.length);
workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet), true);创建了隐藏的sheet，并进行定位之后，我们还需要对createDropDownBox方法进行修改：
/**
* 给Excel的添加下拉框
*
* @param sheet
* @param helper
* @param list 下拉框需要的数据
* @param firstRow 首行
* @param lastRow 尾行
* @param firstCol 首列
* @param lastCol 尾列
*/
private void createDropDownBox(Sheet sheet, DataValidationHelper helper, String[] list, int firstRow, int lastRow,
int firstCol, int lastCol) {
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
DataValidationConstraint constraint = null;
DataValidation validation = null;
// 为了适配xls和xlsx不同版本的Excel（即2003和2007版本的）
if (sheet instanceof XSSFSheet || sheet instanceof SXSSFSheet) {
constraint = helper.createFormulaListConstraint(hiddenSheet.getSheetName());
validation = helper.createValidation(constraint, addressList);
} else {
constraint = DVConstraint.createFormulaListConstraint(hiddenSheet.getSheetName());
validation = new HSSFDataValidation(addressList, constraint);
}
if (validation instanceof XSSFDataValidation) {
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
} else {
validation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(validation);
}通过以上的方法就可以实现超过20个选项数量的下拉框的创建。
真正用到项目中，还发现了一个问题：在2003版的Exce（即后缀名为：xls）中是可以看见隐藏sheet表的名称的，但是在新版的Office中（即后缀名为：xlsx）中，是看不见隐藏sheet表的。如下图所示：
我还是太菜了，不知道是什么原因。如果有大佬知道，请指教。
如果有错误欢迎大家指出，有疑问欢迎交流讨论！
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
