导出 excel 文件效果:
关键方法:
public static void insertThreeCascadeSelectData( Workbook workbook,
TreeMap<String, TreeMap<String,List<String>>> selectTree,
Integer colIndex_firstSelect,
Integer colIndex_secondSelect,
Integer colIndex_thirdSelect ) {
if( selectTree == null ||
selectTree.size() == 0 ||
colIndex_firstSelect == null ||
colIndex_secondSelect == null ||
colIndex_thirdSelect == null ){
return;
}
Sheet sheet = workbook.getSheetAt(0);
if( sheet == null ){
return;
}
String hiddenSheetName = "hiddensheet";
Sheet hiddenSheet = workbook.createSheet( hiddenSheetName );
// 将 selectTree 数据写到 隐藏 sheet 中
Set<String> firstSelectNames = selectTree.keySet();
// 都写到第一列,每列最多可以写65535行,写满了再写到第二列,以此类推...
// 插入一级下拉框名称数据
int rowIndex = 0;
int colIndex = 0;
Row row = getOrCreateRow(hiddenSheet, rowIndex++);
int beginRowIndex = rowIndex + 1;
int endRowIndex = 0;
String formulaName = "total" + firstSelectNames.size() + "firstSelect";
String formulaName_firstSelect = formulaName;
row.createCell( colIndex ).setCellValue( formulaName );
for( String firstSelectName:firstSelectNames ){
row = getOrCreateRow(hiddenSheet, rowIndex++);
row.createCell( colIndex ).setCellValue( firstSelectName );
}
endRowIndex = rowIndex;
String colName = getColNameByColIndex(colIndex);
String formula = hiddenSheetName + "!$" + colName + "$" + beginRowIndex + ":$" + colName + "$" + endRowIndex;
bindFormula( workbook,formulaName,formula );
// 插入二级下拉框名称数据
for( String firstSelectName:firstSelectNames ){
TreeMap<String, List<String>> map_secondSelectName_thirdSelectNames = selectTree.get( firstSelectName );
Set<String> secondSelectNames = map_secondSelectName_thirdSelectNames.keySet();
row = getOrCreateRow(hiddenSheet, rowIndex++);
formulaName = firstSelectName;
row.createCell( colIndex ).setCellValue( formulaName );
beginRowIndex = rowIndex + 1;
for( String secondSelectName:secondSelectNames ){
row = getOrCreateRow( hiddenSheet,rowIndex++ );
row.createCell( colIndex ).setCellValue( secondSelectName );
}
endRowIndex = rowIndex;
colName = getColNameByColIndex( colIndex );
formula = hiddenSheetName + "!$" + colName + "$" + beginRowIndex + ":$" + colName + "$" + endRowIndex;
bindFormula( workbook,formulaName,formula );
}
// 插入三级下拉框名称数据
for( String firstSelectName:firstSelectNames ){
TreeMap<String, List<String>> map_secondSelectName_thirdSelectNames = selectTree.get( firstSelectName );
Set<String> secondSelectNames = map_secondSelectName_thirdSelectNames.keySet();
for( String secondSelectName:secondSelectNames ){
List<String> thirdSelectNames = map_secondSelectName_thirdSelectNames.get( secondSelectName );
if( rowIndex >= XLSX_MAX_ROW_NUM ){
rowIndex = 0;
colIndex++;
}
row = getOrCreateRow( hiddenSheet, rowIndex++ );
formulaName = secondSelectName;
row.createCell( colIndex ).setCellValue( formulaName );
beginRowIndex = rowIndex + 1;
for( String thirdSelectName:thirdSelectNames ){
row = getOrCreateRow( hiddenSheet, rowIndex++ );
row.createCell( colIndex ).setCellValue( thirdSelectName );
}
endRowIndex = rowIndex;
colName = getColNameByColIndex( colIndex );
formula = hiddenSheetName + "!$" + colName + "$" + beginRowIndex + ":$" + colName + "$" + endRowIndex;
bindFormula( workbook,formulaName,formula );
}
}
XSSFDataValidationHelper helper = new XSSFDataValidationHelper( ( XSSFSheet ) sheet );
// 设置第1个下拉框
XSSFDataValidationConstraint constraint_firstSelect = new XSSFDataValidationConstraint( DataValidationConstraint.ValidationType.LIST, formulaName_firstSelect );
CellRangeAddressList region_firstSelect = new CellRangeAddressList( 2, XLSX_MAX_ROW_NUM, colIndex_firstSelect, colIndex_firstSelect );
XSSFDataValidation validation_firstSelect = (XSSFDataValidation) helper.createValidation( constraint_firstSelect, region_firstSelect );
validation_firstSelect.setShowErrorBox( true );
sheet.addValidationData( validation_firstSelect );
// 设置第2个下拉框
colName = getColNameByColIndex( colIndex_firstSelect );
// String indirect = "INDIRECT($" + colName + "2)";
String indirect = "INDIRECT(\"" + excel_formula_prefix + "\"&$" + colName + "2)";
System.out.println( indirect );
XSSFDataValidationConstraint constraint_secondSelect = (XSSFDataValidationConstraint) helper.createFormulaListConstraint( indirect );
// XSSFDataValidationConstraint constraint_secondSelect = new XSSFDataValidationConstraint( DataValidationConstraint.ValidationType.LIST,indirect );
CellRangeAddressList region_secondSelect = new CellRangeAddressList( 1, XLSX_MAX_ROW_NUM, colIndex_secondSelect, colIndex_secondSelect );
XSSFDataValidation validation_secondSelect = (XSSFDataValidation) helper.createValidation( constraint_secondSelect, region_secondSelect );
validation_secondSelect.setShowErrorBox( true );
sheet.addValidationData( validation_secondSelect );
// 设置第3个下拉框
colName = getColNameByColIndex( colIndex_secondSelect );
// indirect = "INDIRECT($" + colName + "2)";
indirect = "INDIRECT(\"" + excel_formula_prefix + "\"&$" + colName + "2)";
System.out.println( indirect );
XSSFDataValidationConstraint constraint_thirdSelect = (XSSFDataValidationConstraint) helper.createFormulaListConstraint( indirect );
// XSSFDataValidationConstraint constraint_thirdSelect = new XSSFDataValidationConstraint( DataValidationConstraint.ValidationType.LIST,indirect );
CellRangeAddressList region_thirdSelect = new CellRangeAddressList( 1, XLSX_MAX_ROW_NUM, colIndex_thirdSelect, colIndex_thirdSelect );
XSSFDataValidation validation_thirdSelect = (XSSFDataValidation) helper.createValidation( constraint_thirdSelect, region_thirdSelect );
validation_thirdSelect.setShowErrorBox( true );
sheet.addValidationData( validation_thirdSelect );
// 隐藏临时数据sheet
int sheetCount = workbook.getNumberOfSheets();
for (int i = 0; i < sheetCount; i++) {
Sheet currSheet = workbook.getSheetAt(i);
if( currSheet.getSheetName().equals( hiddenSheetName ) ){
workbook.setSheetHidden(i, true);
}
}
}
原理:
该工具类会在 excel 中新建一个隐藏的 sheet,当三级联动数据如下所示时:
{
"河南省": {
“郑州市”: [ "二七区","登封市","新郑市" ],
“洛阳市”: [ "洛龙区","涧西区" ]
},
"陕西省": {
"西安市": [ "未央区","长安区","高陵区" ]
}
}
隐藏 sheet 的内容如下所示:
该工具还会向 excel 中添加几个公式,此时公式如下所示:
公式 "total2firstSelect" = "hiddensheet!$A$2:$A$3"
公式 "河南省" = "hiddensheet!$A$5:$A$6"
公式 "陕西省" = "hiddensheet!$A$8:$A$8"
公式 "洛阳市" = "hiddensheet!$A$10:$A$11"
公式 "郑州市" = "hiddensheet!$A$13:$A$15"
公式 "西安市" = "hiddensheet!$A$17:$A$19"
INDIRECT($E2)
INDIRECT($F2)
其中 "河南省" = "hiddensheet!$A$5:$A$6"的含义是点击 "省份" 列的 "河南省" 时,会使用隐藏sheet 中 A5~A6范围内的数据( 即洛阳市、郑州市 )作为 "城市" 列中的下拉列表的数据内容,当然只有这个公式是不行的,还需要下面代码建立触发:
// 设置点击省份列的指定省份时,城市列的城市列表下拉框对应更新
String indirect = "INDIRECT($E2)";
XSSFDataValidationConstraint constraint = (XSSFDataValidationConstraint) helper.createFormulaListConstraint( indirect );
CellRangeAddressList region= new CellRangeAddressList( 1, 60000, 5, 5 );
XSSFDataValidation validation = (XSSFDataValidation) helper.createValidation( constraint, region );
validation.setShowErrorBox( true );
sheet.addValidationData( validation );
其中 $E2 表示省份列中点击的具体的省份名称,
当有时候省份列的数据不是省份的名称,而是纯数字的省份编码,例如河南省为 1000,则我们期望生成的函数为:
"1000" = "hiddensheet!$A$5:$A$6"
但是很抱歉,excel 的公式名称有限制,不能以数字开头,所以这里考虑加前缀,例如对省份编码加加 "xxx" 前缀,则生成的公式为:
"xxx1000" = "hiddensheet!$A$5:$A$6"
但是对应的 String indirect = "INDIRECT($E2)" 的地方也要修改,因为 $E2 表示的省份列具体的值,比如是 "1000",但是没有名称为 "1000" 的公式( 有名称为 "xxx1000" 的公式 ),所以需要将 $E2 前面加也加一个 "xxx" 前缀,具体就是 "xxx"&$E2( & 是拼接的意思 ),即:
String indirect = "INDIRECT(\"xxx\"&$E2)";
重新输出的公式和 INDIRECT 信息为:
公式 "total2firstSelect" = "hiddensheet!$A$2:$A$3"
公式 "xxx河南省" = "hiddensheet!$A$5:$A$6"
公式 "xxx陕西省" = "hiddensheet!$A$8:$A$8"
公式 "xxx洛阳市" = "hiddensheet!$A$10:$A$11"
公式 "xxx郑州市" = "hiddensheet!$A$13:$A$15"
公式 "xxx西安市" = "hiddensheet!$A$17:$A$19"
INDIRECT("xxx"&$E2)
INDIRECT("xxx"&$F2)
完整代码:
ExcelUtils.java:
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.alibaba.fastjson.JSONObject;
import com.goldwind.ipark.common.util.TimeNodeUtil;
import com.pumch.ipark.pojo.model.ExcelCellRange;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataValidation;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.ParseException;
import java.util.*;
//Excel导入导出工具类
public class ExcelUtils {
private static final String excel_formula_prefix="xxx";
private static final Integer XLSX_MAX_ROW_NUM = 60000;
/**
* 功能描述:复杂导出Excel,包括文件名以及表名。创建表头
* @param list 导出的实体类
* @param title 表头名称
* @param sheetName sheet表名
* @param pojoClass 映射的实体类
* @param isCreateHeader 是否创建表头
* @param fileName
* @param response
* @return
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) {
ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.HSSF);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(list, pojoClass, fileName, null,response, exportParams);
}
/**
* 功能描述:复杂导出Excel,包括文件名以及表名,不创建表头
* @param list 导出的实体类
* @param title 表头名称
* @param sheetName sheet表名
* @param pojoClass 映射的实体类
* @param fileName
* @param response
* @return
*/
public static void exportExcel(List<?> list,
String title,
String sheetName,
Class<?> pojoClass,
String fileName,
Map<ExcelCellRange,List<String>> selectMap,
HttpServletResponse response) {
defaultExport(list, pojoClass, fileName, selectMap,response, new ExportParams(title, sheetName, ExcelType.XSSF));
}
/**
* @param modles
* @param title
* @param sheetName
* @param modleClass
* @param saveFilePath
* @param colIndex_firstSelect 第一列是0
* @param colIndex_secondSelect 第一列是0
* @param colIndex_thirdSelect 第一列是0
* @param selectTree
*/
public static void exportExcelWithThreeCascadeSelect( List<?> modles,
String title,
String sheetName,
Class<?> modleClass,
String fileName,
Integer colIndex_firstSelect,
Integer colIndex_secondSelect,
Integer colIndex_thirdSelect,
TreeMap<String,TreeMap<String,List<String>>> selectTree,
HttpServletResponse response ) {
ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, modleClass, modles);
// 添加三级联动下拉框
ExcelUtils.insertThreeCascadeSelectData( workbook,selectTree,colIndex_firstSelect,colIndex_secondSelect,colIndex_thirdSelect );
// 下载内存中的 workbook 到输出流
downLoadExcel( fileName,response,workbook );
}
public static void exportExcelWithSelect(List<?> list,
String title,
String sheetName,
Class<?> pojoClass,
String fileName,
Map<Integer,List<String>> selectMap,
HttpServletResponse response) {
defaultExport(list, pojoClass, fileName, null,response, new ExportParams(title, sheetName, ExcelType.XSSF));
}
/**
* 功能描述:Map 集合导出
*
* @param list 实体集合
* @param fileName 导出的文件名称
* @param response
* @return
*/
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
defaultExport(list, fileName, response);
}
/**
* 功能描述:默认导出方法
* @param list 导出的实体集合
* @param fileName 导出的文件名
* @param pojoClass pojo实体
* @param exportParams ExportParams封装实体
* @param response
* @return
*/
private static void defaultExport(List<?> list,
Class<?> pojoClass,
String fileName,
Map<ExcelCellRange,List<String>> selectMap,
HttpServletResponse response,
ExportParams exportParams) {
long t1 = System.currentTimeMillis();
System.out.println( "开始导出excel..." );
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
long t2 = System.currentTimeMillis();
System.out.println( "导出excel完毕...导出耗时:" + ( t2 - t1 ) + "毫秒" );
// //添加自定义样式
// exportParams.setStyle(ExcelExportStylerUtil.class);
// 添加下拉框
// ExcelUtils.insertSelectList( workbook,selectMap );
ExcelUtils.insertSelectListForLargeSelect( workbook,selectMap );
if (workbook != null) {
long t3 = System.currentTimeMillis();
System.out.println( "开始下载excel..." );
downLoadExcel(fileName, response, workbook);
long t4 = System.currentTimeMillis();
System.out.println( "下载excel完毕...下载耗时:" + ( t4 - t3 ) + "毫秒" );
}
}
public static void insertSelectListForLargeSelect( Workbook workbook,Map<ExcelCellRange, List<String>> selectMap) {
if( selectMap == null || selectMap.size() == 0 ){
return;
}
Sheet sheet = workbook.getSheetAt(0);
if( sheet == null ){
return;
}
Set<ExcelCellRange> excelCellRanges = selectMap.keySet();
for( ExcelCellRange excelCellRange:excelCellRanges ){
Integer beginRow = excelCellRange.getBeginRow();
Integer endRow = excelCellRange.getEndRow();
Integer beginCol = excelCellRange.getBeginCol();
Integer endCol = excelCellRange.getEndCol();
String hiddenSheetName = "hiddensheet" + System.currentTimeMillis();
Sheet hiddenSheet = workbook.createSheet( hiddenSheetName );
List<String> selectList = selectMap.get(excelCellRange);
int selctSize = selectList.size();
for (int i = 0; i < selctSize; i++) {
String selectValue = selectList.get(i);
hiddenSheet.createRow( i )
.createCell( 0 )
.setCellValue( selectValue );
}
// 添加名称管理器
// Name name = workbook.createName();
// name.setNameName(hiddenSheetName);
// String formulaText = "formulaText_beginRow" + beginRow + "_endRow" + endRow + "_beginCol" + beginCol + "_endCol" + endCol;
String formula = hiddenSheetName + "!$A$1:$A$" + selctSize;
// name.setRefersToFormula( formulaText );
//设置下拉不受限制
XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint( DataValidationConstraint.ValidationType.LIST, formula );
// 设置区域边界
CellRangeAddressList region = new CellRangeAddressList(beginRow, endRow, beginCol, endCol);
XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet) sheet);
XSSFDataValidation validation = (XSSFDataValidation) helper.createValidation(constraint, region);
// 输入非法数据时,弹窗警告框
validation.setShowErrorBox(true);
sheet.addValidationData(validation);
int sheetCount = workbook.getNumberOfSheets();
for (int i = 0; i < sheetCount; i++) {
Sheet currSheet = workbook.getSheetAt(i);
if( currSheet.getSheetName().equals( hiddenSheetName ) ){
workbook.setSheetHidden(i, true);
}
}
}
}
public static void insertThreeCascadeSelectData( Workbook workbook,
TreeMap<String, TreeMap<String,List<String>>> selectTree,
Integer colIndex_firstSelect,
Integer colIndex_secondSelect,
Integer colIndex_thirdSelect ) {
if( selectTree == null ||
selectTree.size() == 0 ||
colIndex_firstSelect == null ||
colIndex_secondSelect == null ||
colIndex_thirdSelect == null ){
return;
}
Sheet sheet = workbook.getSheetAt(0);
if( sheet == null ){
return;
}
String hiddenSheetName = "hiddensheet";
Sheet hiddenSheet = workbook.createSheet( hiddenSheetName );
// 将 selectTree 数据写到 隐藏 sheet 中
Set<String> firstSelectNames = selectTree.keySet();
// 都写到第一列,每列最多可以写65535行,写满了再写到第二列,以此类推...
// 插入一级下拉框名称数据
int rowIndex = 0;
int colIndex = 0;
Row row = getOrCreateRow(hiddenSheet, rowIndex++);
int beginRowIndex = rowIndex + 1;
int endRowIndex = 0;
String formulaName = "total" + firstSelectNames.size() + "firstSelect";
String formulaName_firstSelect = formulaName;
row.createCell( colIndex ).setCellValue( formulaName );
for( String firstSelectName:firstSelectNames ){
row = getOrCreateRow(hiddenSheet, rowIndex++);
row.createCell( colIndex ).setCellValue( firstSelectName );
}
endRowIndex = rowIndex;
String colName = getColNameByColIndex(colIndex);
String formula = hiddenSheetName + "!$" + colName + "$" + beginRowIndex + ":$" + colName + "$" + endRowIndex;
bindFormula( workbook,formulaName,formula );
// 插入二级下拉框名称数据
for( String firstSelectName:firstSelectNames ){
TreeMap<String, List<String>> map_secondSelectName_thirdSelectNames = selectTree.get( firstSelectName );
Set<String> secondSelectNames = map_secondSelectName_thirdSelectNames.keySet();
row = getOrCreateRow(hiddenSheet, rowIndex++);
formulaName = firstSelectName;
row.createCell( colIndex ).setCellValue( formulaName );
beginRowIndex = rowIndex + 1;
for( String secondSelectName:secondSelectNames ){
row = getOrCreateRow( hiddenSheet,rowIndex++ );
row.createCell( colIndex ).setCellValue( secondSelectName );
}
endRowIndex = rowIndex;
colName = getColNameByColIndex( colIndex );
formula = hiddenSheetName + "!$" + colName + "$" + beginRowIndex + ":$" + colName + "$" + endRowIndex;
bindFormula( workbook,formulaName,formula );
}
// 插入三级下拉框名称数据
for( String firstSelectName:firstSelectNames ){
TreeMap<String, List<String>> map_secondSelectName_thirdSelectNames = selectTree.get( firstSelectName );
Set<String> secondSelectNames = map_secondSelectName_thirdSelectNames.keySet();
for( String secondSelectName:secondSelectNames ){
List<String> thirdSelectNames = map_secondSelectName_thirdSelectNames.get( secondSelectName );
if( rowIndex >= XLSX_MAX_ROW_NUM ){
rowIndex = 0;
colIndex++;
}
row = getOrCreateRow( hiddenSheet, rowIndex++ );
formulaName = secondSelectName;
row.createCell( colIndex ).setCellValue( formulaName );
beginRowIndex = rowIndex + 1;
for( String thirdSelectName:thirdSelectNames ){
row = getOrCreateRow( hiddenSheet, rowIndex++ );
row.createCell( colIndex ).setCellValue( thirdSelectName );
}
endRowIndex = rowIndex;
colName = getColNameByColIndex( colIndex );
formula = hiddenSheetName + "!$" + colName + "$" + beginRowIndex + ":$" + colName + "$" + endRowIndex;
bindFormula( workbook,formulaName,formula );
}
}
XSSFDataValidationHelper helper = new XSSFDataValidationHelper( ( XSSFSheet ) sheet );
// 设置第1个下拉框
XSSFDataValidationConstraint constraint_firstSelect = new XSSFDataValidationConstraint( DataValidationConstraint.ValidationType.LIST, formulaName_firstSelect );
CellRangeAddressList region_firstSelect = new CellRangeAddressList( 2, XLSX_MAX_ROW_NUM, colIndex_firstSelect, colIndex_firstSelect );
XSSFDataValidation validation_firstSelect = (XSSFDataValidation) helper.createValidation( constraint_firstSelect, region_firstSelect );
validation_firstSelect.setShowErrorBox( true );
sheet.addValidationData( validation_firstSelect );
// 设置第2个下拉框
colName = getColNameByColIndex( colIndex_firstSelect );
// String indirect = "INDIRECT($" + colName + "2)";
String indirect = "INDIRECT(\"" + excel_formula_prefix + "\"&$" + colName + "2)";
System.out.println( indirect );
XSSFDataValidationConstraint constraint_secondSelect = (XSSFDataValidationConstraint) helper.createFormulaListConstraint( indirect );
// XSSFDataValidationConstraint constraint_secondSelect = new XSSFDataValidationConstraint( DataValidationConstraint.ValidationType.LIST,indirect );
CellRangeAddressList region_secondSelect = new CellRangeAddressList( 1, XLSX_MAX_ROW_NUM, colIndex_secondSelect, colIndex_secondSelect );
XSSFDataValidation validation_secondSelect = (XSSFDataValidation) helper.createValidation( constraint_secondSelect, region_secondSelect );
validation_secondSelect.setShowErrorBox( true );
sheet.addValidationData( validation_secondSelect );
// 设置第3个下拉框
colName = getColNameByColIndex( colIndex_secondSelect );
// indirect = "INDIRECT($" + colName + "2)";
indirect = "INDIRECT(\"" + excel_formula_prefix + "\"&$" + colName + "2)";
System.out.println( indirect );
XSSFDataValidationConstraint constraint_thirdSelect = (XSSFDataValidationConstraint) helper.createFormulaListConstraint( indirect );
// XSSFDataValidationConstraint constraint_thirdSelect = new XSSFDataValidationConstraint( DataValidationConstraint.ValidationType.LIST,indirect );
CellRangeAddressList region_thirdSelect = new CellRangeAddressList( 1, XLSX_MAX_ROW_NUM, colIndex_thirdSelect, colIndex_thirdSelect );
XSSFDataValidation validation_thirdSelect = (XSSFDataValidation) helper.createValidation( constraint_thirdSelect, region_thirdSelect );
validation_thirdSelect.setShowErrorBox( true );
sheet.addValidationData( validation_thirdSelect );
// 隐藏临时数据sheet
int sheetCount = workbook.getNumberOfSheets();
for (int i = 0; i < sheetCount; i++) {
Sheet currSheet = workbook.getSheetAt(i);
if( currSheet.getSheetName().equals( hiddenSheetName ) ){
workbook.setSheetHidden(i, true);
}
}
}
/**
*
* @param colIndex 0 表示第 1 列
* @return
*/
private static String getColNameByColIndex( int colIndex ) {
StringBuilder s = new StringBuilder();
while ( colIndex >= 26 ) {
s.insert(0, ( char ) ( 'A' + colIndex % 26 ) );
colIndex = colIndex / 26 - 1;
}
s.insert(0, ( char ) ( 'A' + colIndex ) );
return s.toString();
}
private static void bindFormula( Workbook workbook,String formulaName, String formula ) {
Name name = workbook.createName();
if( !( formulaName.startsWith( "total" ) && formulaName.endsWith( "firstSelect" ) ) ){
formulaName = excel_formula_prefix + formulaName;
}
name.setNameName( formulaName );
name.setRefersToFormula( formula );
System.out.println( "bind \"" + formulaName + "\" for formula \"" + formula + "\"" );
}
private static Row getOrCreateRow( Sheet sheet,int rowIndex ){
Row row = sheet.getRow(rowIndex);
if( row != null ){
return row;
}
return sheet.createRow( rowIndex );
}
private static void insertSelectList( Workbook workbook,Map<ExcelCellRange, List<String>> selectMap) {
if( selectMap == null || selectMap.size() == 0 ){
return;
}
Sheet sheet = workbook.getSheetAt(0);
if( sheet == null ){
return;
}
Set<ExcelCellRange> excelCellRanges = selectMap.keySet();
for( ExcelCellRange excelCellRange:excelCellRanges ){
List<String> selectList = selectMap.get( excelCellRange );
// 生成下拉框范围
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(excelCellRange.getBeginRow(),
excelCellRange.getEndRow(),
excelCellRange.getBeginCol(),
excelCellRange.getEndCol());
// 生成下拉框内容
int size = selectList.size();
String[] selectArray = new String[ size ];
for( int i=0;i<size;i++ ){
selectArray[ i ] = selectList.get( i );
}
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint( selectArray );
XSSFDataValidation xssfDataValidation = (XSSFDataValidation) dvHelper.createValidation( dvConstraint, cellRangeAddressList );
sheet.addValidationData( xssfDataValidation );
}
}
/**
* 功能描述:Excel导出
* @param fileName 文件名称
* @param response
* @param workbook Excel对象
* @return
*/
public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
ServletOutputStream outputStream = null;
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
outputStream = response.getOutputStream();
workbook.write( outputStream );
} catch (IOException e) {
throw new RuntimeException(e);
}finally {
if( workbook != null ){
try {
workbook.close();
}catch ( Exception e ){
e.printStackTrace();
}
}
if( outputStream != null ){
try {
outputStream.close();
}catch ( Exception e ){
e.printStackTrace();
}
}
}
}
/**
* 功能描述:默认导出方法
* @param list 导出的实体集合
* @param fileName 导出的文件名
* @param response
* @return
*/
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
if (workbook != null) ;
downLoadExcel(fileName, response, workbook);
}
/**
* 功能描述:根据文件路径来导入Excel
* @param filePath 文件路径
* @param titleRows 表标题的行数
* @param headerRows 表头行数
* @param pojoClass Excel实体类
* @return
*/
public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
//判断文件是否存在
if (StringUtils.isBlank(filePath)) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
} catch (NoSuchElementException e) {
throw new RuntimeException("模板不能为空");
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
/**
* 功能描述:根据接收的Excel文件来导入Excel,并封装成实体类
* @param file 上传的文件
* @param titleRows 表标题的行数
* @param headerRows 表头行数
* @param pojoClass Excel实体类
* @return
*/
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
if (file == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
} catch (NoSuchElementException e) {
throw new RuntimeException("excel文件不能为空");
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
return list;
}
public static String jointExcelTitleName(String startTime,String endTime, String outType,String titleName){
Date startDate = new Date();
Date endDate = new Date();
try {
startDate = TimeNodeUtil.shortSdf.parse(startTime);
endDate = TimeNodeUtil.shortSdf.parse(endTime);
} catch (ParseException e) {
e.printStackTrace();
}
String startString = TimeNodeUtil.ymdSdf.format(startDate) ;
String endString = TimeNodeUtil.ymdSdf.format(endDate) ;
String titleString = "";
titleString = titleName+startString+"-"+endString;
return titleString;
}
}
ExcelCellRange.java:
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.Getter;
import lombok.Setter;
import java.io.Serializable;
@Getter
@Setter
@ApiModel( value = "ExcelCellRange",description = "ExcelCellRange")
public class ExcelCellRange implements Serializable {
@ApiModelProperty( "开始行号,0表示第1行" )
private Integer beginRow;
@ApiModelProperty( "结束行号" )
private Integer endRow;
@ApiModelProperty( "开始列号,0表示第1列" )
private Integer beginCol;
@ApiModelProperty( "结束列号" )
private Integer endCol;
public ExcelCellRange(Integer beginRow, Integer endRow, Integer beginCol, Integer endCol) {
this.beginRow = beginRow;
this.endRow = endRow;
this.beginCol = beginCol;
this.endCol = endCol;
}
}
使用示例:
TestController.java:
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.*;
@RestController
@RequestMapping( "/test" )
public class TestController {
@ApiOperation(httpMethod = "GET", value = "testImport")
@GetMapping( "/testImport" )
public String testImport(HttpServletResponse response){
/*
{
"河南省": {
“郑州市”: [ "二七区","登封市","新郑市" ],
“洛阳市”: [ "洛龙区","涧西区" ]
},
"陕西省": {
"西安市": [ "未央区","长安区","高陵区" ]
}
}
*/
TreeMap<String, TreeMap<String, List<String>>> selectTree = new TreeMap<>();
TreeMap<String, List<String>> map_henan = new TreeMap<>();
map_henan.put( "郑州市",JSONArray.parseArray( "[ \"二七区\",\"登封市\",\"新郑市\" ]",String.class ) );
map_henan.put( "洛阳市",JSONArray.parseArray( "[ \"洛龙区\",\"涧西区\" ]",String.class ) );
TreeMap<String, List<String>> map_shanxi = new TreeMap<>();
map_shanxi.put( "西安市",JSONArray.parseArray( "[ \"未央区\",\"长安区\",\"高陵区\" ]",String.class ) );
selectTree.put( "河南省",map_henan );
selectTree.put( "陕西省",map_shanxi );
String filename="人员列表.xlsx";
List<PersonModel> personModels = new ArrayList<>();
personModels.add( new PersonModel( "张三", "10001", "15800000001", "我是张三", "河南省","郑州市","二七区" ) );
personModels.add( new PersonModel( "李四", "10002", "15800000002", "我是李四", "河南省","郑州市","登封市" ) );
personModels.add( new PersonModel( "王五", "10003", "15800000003", "我是王五", "河南省","郑州市","新郑市" ) );
personModels.add( new PersonModel( "赵六", "10004", "15800000004", "我是赵六", "河南省","洛阳市","洛龙区" ) );
personModels.add( new PersonModel( "小明", "10005", "15800000005", "我是小明", "河南省","洛阳市","涧西区" ) );
personModels.add( new PersonModel( "小张", "10006", "15800000006", "我是小张", "陕西省","西安市","未央区" ) );
personModels.add( new PersonModel( "小红", "10007", "15800000007", "我是小红", "陕西省","西安市","长安区" ) );
personModels.add( new PersonModel( "小蓝", "10008", "15800000008", "我是小蓝", "陕西省","西安市","高陵区" ) );
ExcelUtils.exportExcelWithThreeCascadeSelect( personModels,"人员列表","人员列表", PersonModel.class,filename,4,5,6,selectTree, response);
return null;
}
}
使用示例中用到的类:
PersonMode.java:
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Getter;
import lombok.Setter;
@Getter
@Setter
public class PersonModel {
@Excel(name = "人员姓名",width= 15.00,orderNum = "1" )
private String personName;
@Excel(name = "人员工号",width= 15.00,orderNum = "2" )
private String personOa;
@Excel(name = "人员联系方式",width= 15.00,orderNum = "3" )
private String personPhone;
@Excel(name = "人员备注",width= 30.00,orderNum = "4" )
private String remark;
@Excel(name = "所属省份",width= 25.00,orderNum = "5" )
private String provinceName;
@Excel(name = "所属城市",width= 25.00,orderNum = "6" )
private String cityName;
@Excel(name = "所属区县",width= 25.00,orderNum = "7" )
private String areaName;
private int rowNum;
private String errorMsg;
public PersonModel() {
}
public PersonModel(String personName, String personOa, String personPhone, String remark, String provinceName, String cityName, String areaName) {
this.personName = personName;
this.personOa = personOa;
this.personPhone = personPhone;
this.remark = remark;
this.provinceName = provinceName;
this.cityName = cityName;
this.areaName = areaName;
}
}