支持生成xls、xlsx两种excel
支持下拉框选择
本demo基于springboot测试
原理：当我们需要生成带下拉框的excel，我们可以把需要下拉选择的数据放到sheet2，将sheet2的这些存放待绑定数据的单元格绑定到sheet1的需要下拉的单元格，而sheet2可以根据需求是否隐藏
pom.xml
代码语言：html复制<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>测试单元
代码语言：java复制@SpringBootTest
@RunWith(SpringRunner.class)
public class TestDemo {
@Autowired
private TestServiceImpl testService;
@Test
public void test1() {
testService.createExcel();
}
}TestServiceImpl
代码语言：java复制@Service
public class TestServiceImpl {
public void createExcel() {
String fileName = "d:/test/员工信息表.xlsx";
String[] heads = {"姓名", "性别", "证件类型", "证件号码", "服务结束时间", "参保地", "民族"};
//下拉框数据
String[] str1 = {"男", "女", "未知"};
String[] str2 = {"北京", "上海", "广州", "深圳", "武汉", "长沙", "湘潭"};
String[] str3 = {"01-汉族", "02-蒙古族", "03-回族", "04-藏族", "05-维吾尔族", "06-苗族", "07-彝族", "08-壮族", "09-布依族",
"10-朝鲜族", "11-满族", "12-侗族", "13-瑶族", "14-白族", "15-土家族", "16-哈尼族", "17-哈萨克族", "18-傣族", "19-黎族", "20-傈僳族",
"21-佤族", "22-畲族", "23-高山族", "24-拉祜族", "25-水族", "26-东乡族", "27-纳西族", "28-景颇族", "29-柯尔克孜族", "30-土族",
"31-达斡尔族", "32-仫佬族", "33-羌族", "34-布朗族", "35-撒拉族", "36-毛难族", "37-仡佬族", "38-锡伯族", "39-阿昌族", "40-普米族",
"41-塔吉克族", "42-怒族", "43-乌孜别克族", "44-俄罗斯族", "45-鄂温克族", "46-德昂族", "47-保安族", "48-裕固族", "49-京族", "50-塔塔尔族",
"51-独龙族", "52-鄂伦春族", "53-赫哲族", "54-门巴族", "55-珞巴族", "56-基诺族", "98-外国血统", "99-其他"};
HashMap<Integer, String[]> dataMap = new HashMap<>();
dataMap.put(1, str1);
dataMap.put(5, str2);
dataMap.put(6, str3);
ExcelUtils.createExcel(fileName, heads, dataMap);
}
}ExcelUtils
代码语言：java复制public class ExcelUtils {
public static void createExcel(String filePathAndName, String[] heads, Map<Integer, String[]> dataMap) {
Workbook wb;
String fileType = filePathAndName.substring(filePathAndName.lastIndexOf(".") 1);
if ("xlsx".equalsIgnoreCase(fileType)) {
wb = new XSSFWorkbook();
} else if ("xls".equalsIgnoreCase(fileType)) {
wb = new HSSFWorkbook();
} else {
return;
}
Sheet sheet1 = wb.createSheet("Sheet1");
// 样式
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
Font fontStyle = wb.createFont();
fontStyle.setFontName("微软雅黑");
fontStyle.setFontHeightInPoints((short) 12);
cellStyle.setFont(fontStyle);
// head行
Row firstRow = sheet1.createRow(0);
for (int i = 0; i < heads.length; i ) {
Cell cell = firstRow.createCell(i);
sheet1.setColumnWidth(i, 4000);
cell.setCellStyle(cellStyle);
cell.setCellValue(heads[i]);
}
Sheet sheet2 = wb.createSheet("Sheet2");
// 设置sheet2页可见性
// wb.setSheetVisibility(1, SheetVisibility.HIDDEN);
int index = 0;
for (Map.Entry<Integer, String[]> entry : dataMap.entrySet()) {
Integer key = entry.getKey();
String[] value = entry.getValue();
String formula = "Sheet2!$" (char) ('A' index) "$" (index 1) ":$" (char) ('A' index) "$" value.length;
sheet1.addValidationData(createDataValidation(fileType, formula, key, key));
for (int i = 0; i < value.length; i ) {
Row row = sheet2.getRow(i);
if (null == row) {
row = sheet2.createRow(i);
sheet2.setColumnWidth(i, 4000);
}
row.createCell(index).setCellValue(value[i]);
}
index ;
}
writeDataToFile(filePathAndName, wb);
}
/**
* 将工作薄数据写入文件
*/
private static void writeDataToFile(String filePathAndName, Workbook workbook) {
try {
File file = new File(filePathAndName);
if (!file.getParentFile().exists() && file.getParentFile().mkdirs()) {
System.out.println("路径不存在，新建。。。");
}
if (!file.exists() && file.createNewFile()) {
System.out.println("文件不存在，新建。。。");
}
FileOutputStream out = new FileOutputStream(file);
out.flush();
workbook.write(out);
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 设置下拉框绑定的数据，支持xls、xlsx
*
* @param formula 绑定表达式
*/
private static DataValidation createDataValidation(String fileType, String formula, int firstCol, int endCol) {
CellRangeAddressList cellRange = new CellRangeAddressList(1, 5000, firstCol, endCol);
if ("xlsx".equalsIgnoreCase(fileType)) {
XSSFDataValidationHelper helper = new XSSFDataValidationHelper(null);
DataValidationConstraint constraint = helper.createFormulaListConstraint(formula);
return helper.createValidation(constraint, cellRange);
}
DVConstraint constraint = DVConstraint.createFormulaListConstraint(formula);
return new HSSFDataValidation(cellRange, constraint);
}
}执行效果
