poi excel 多级联动

最新推荐文章于 2025-06-08 10:36:58 发布
原创最新推荐文章于 2025-06-08 10:36:58 发布 · 3.6k 阅读
0 ·
CC 4.0 BY-SA版权
JAVASE学习笔记同时被 2 个专栏收录
24 篇文章
订阅专栏
office
1 篇文章
订阅专栏
本文介绍了一个使用 Java 实现的 Excel 文件生成器，能够创建包含下拉列表、级联选择等功能的复杂 Excel 表格。该程序通过读取模板文件，并利用 Apache POI 库动态设置工作表的数据验证规则来实现。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

 
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;


import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
 
 
 


public class ExportExcel {
public static String outputFile = "d:\\shengcheng.xls "; // 生成的文件
public static String fileToBeRead = "d:\\aa.xls ";// 模板




private static String EXCEL_HIDE_SHEET_NAME = "paraSheet"; 
private static String fistName = ""; 



    //设置下拉列表的内容   
private static String[] provinceList = {"省份","浙江","山东","江西","江苏","四川"};     
private static String[] zjProvinceList = {"浙江","杭州","宁波","温州"};   
private static String[] sdProvinceList = {"山东","济南","青岛","烟台"};   
private static String[] jxProvinceList = {"江西","南昌","新余","鹰潭","抚州"};   
private static String[] jsProvinceList = {"江苏","南京","苏州","无锡"};  
private static String[] scProvinceList = {"四川","成都","绵阳","自贡"}; 
private static String[] jsCityList = {"无锡","西湖","杭州","断桥"}; 

private static String[] scCityList = {"成都","北川","青川","汶川"}; 
static ArrayList ztList;
static ArrayList kmList;
static ArrayList zkmList;

public static void main(String[] args) {
ztList=new ArrayList();
ztList.add(provinceList);

zkmList=new ArrayList();
zkmList.add(scCityList);
zkmList.add(jsCityList);
kmList=new ArrayList(); 
kmList.add(zjProvinceList);
kmList.add(sdProvinceList);
kmList.add(jxProvinceList);
kmList.add(jsProvinceList);
kmList.add(scProvinceList);

List  lst=new ArrayList();
lst.add(ztList);
lst.add(kmList);
lst.add(zkmList);
HSSFWorkbook wb=null;
 try {
wb = new HSSFWorkbook(new FileInputStream(
fileToBeRead));
} catch (FileNotFoundException e1) {
e1.printStackTrace();
} catch (IOException e1) {
e1.printStackTrace();
}
creatExcelHidePage(wb,false,lst);   //创建隐藏sheet
setDataValidation(wb,3,new int[]{1,2,3});  //设置校验
FileOutputStream fOut;//输出
try {
fOut = new FileOutputStream(outputFile);
wb.write(fOut);
fOut.flush();
fOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
}catch (Exception e) {
e.printStackTrace();
}

}
/**
* 
* @param workbook
* @param flag 创建的参数sheet是否隐藏
*/
    public static void creatExcelHidePage(Workbook workbook,boolean flag,List lst){   
        Sheet hideInfoSheet = workbook.createSheet(EXCEL_HIDE_SHEET_NAME);//隐藏一些信息   
        //创建参数sheet 里面内容 
        int row=0;
for(int i=0;i<lst.size();i++){
List m=(List) lst.get(i);
for(int j=0;j<m.size();j++){
if(i==0){
String[] t=(String[])m.get(j);
fistName=t[0];
}
Row r=hideInfoSheet.createRow(row++);
        creatRow(r, (String[])m.get(j));   
}
}        
        row=1;
for(int i=0;i<lst.size();i++){
List m=(List) lst.get(i);
for(int j=0;j<m.size();j++){
String[] temp=(String[]) m.get(j);
creatExcelName(workbook, temp[0], row++,2,temp.length);
}
}
        workbook.setSheetHidden(workbook.getSheetIndex(EXCEL_HIDE_SHEET_NAME), flag);   
    }  
    
    /**  
     * 创建excel名称
     * @param workbook  
     */  
    private static void creatExcelName(Workbook workbook,String cellName,int row,int start,int end){   
    Name name;   
    name = workbook.createName();   
    name.setNameName(cellName);   
    name.setRefersToFormula(getNamestr(cellName,row,start,end));   
    }   
    private static String getNamestr(String sheetName,int row,int start,int end){
    return EXCEL_HIDE_SHEET_NAME+"!"+"$"+CellReference.convertNumToColString(start-1) +"$"+row+":"+"$"+CellReference.convertNumToColString(end-1)+"$"+row;
    }
    
    
    /**  
     * 创建一行数据
     * @param currentRow  
     * @param textList  
     */  
    private static void creatRow(Row currentRow,String[] textList){   
        if(textList!=null&&textList.length>0){   
            int i = 0;   
            for(int j=0;j<textList.length;j++){   
                Cell userNameLableCell = currentRow.createCell(i++);   
                userNameLableCell.setCellValue(textList[j]);   
            }   
        }   
    }
   /**
    * 
    * @param wb
    * @param startRow  超始行
    * @param cols     级联的列号数组
    */
    public static void setDataValidation(Workbook wb,int startRow,int[] cols){   
        int sheetIndex = wb.getNumberOfSheets();  
        String[] colNames=getColName(cols);
        if(sheetIndex>0){   
                Sheet sheet = wb.getSheetAt(0);  
                DataValidation data_validation = null;
                for(int row=startRow;row<3002;row++){
                   data_validation = getDataValidationByFormula(fistName,row,cols[0]);   
                   sheet.addValidationData(data_validation);  
                     for(int i=0;i<colNames.length-1;i++){
                     data_validation = getDataValidationByFormula("INDIRECT("+colNames[i]+row+")",row,cols[i]+1);   
                     sheet.addValidationData(data_validation);  
                     
                     }
                }
//                }   
        }   
    }  
    static String[] getColName(int[] colIndex){
    String[] colName=new String[colIndex.length];
    for(int i=0;i<colIndex.length;i++){
    colName[i]=CellReference.convertNumToColString(colIndex[i]-1) ;
    }
    return colName;
    
    }
/**
 * 
 * @param formulaString  公式
 * @param naturalRowIndex 验证的行
 * @param naturalColumnIndex 验证的列
 * @return
 */
    private static DataValidation getDataValidationByFormula(String formulaString,int naturalRowIndex,int naturalColumnIndex){   
        //加载下拉列表内容     
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(formulaString);    
        //设置数据有效性加载在哪个单元格上。     
        //四个参数分别是：起始行、终止行、起始列、终止列     
        int firstRow = naturalRowIndex-1;   
        int lastRow = naturalRowIndex-1;   
        int firstCol = naturalColumnIndex-1;   
        int lastCol = naturalColumnIndex-1;   
        CellRangeAddressList regions=new CellRangeAddressList(firstRow,lastRow,firstCol,lastCol);     
        //数据有效性对象    
        DataValidation data_validation = new HSSFDataValidation(regions,constraint);   
        //设置输入信息提示信息   
        data_validation.createPromptBox("提示","请使用下拉方式选择合适的值！");   
        //设置输入错误提示信息   
        data_validation.createErrorBox("提示","你输入的值不合法，请下拉选择合适的值！");   
        return data_validation;   
    }   
    
    
}