java生成带有下拉列表框的Excel
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
package wstar.wwwrot.util;
import java.io.FileOutputStream;
import java.util.HashMap;
import ermodel.Row;
import ermodel.*;
import ermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
public class CreateExcelMoBusiness {
private static String EXCEL_HIDE_SHEET_NAME = "excelhidesheetname"; private static String HIDE_SHEET_NAME_PROVINCE = "provinceList";
private HashMap map = new HashMap();
//设置下拉列表的内容
private static String[] provinceList = {"浙江","山东"};
public static void main(String[] args) {
//使用事例
Workbook wb = new HSSFWorkbook();
createExcelMo(wb);
creatExcelHidePage(wb);
setDataValidation(wb);
FileOutputStream fileOut;
try {
fileOut = new FileOutputStream("d://test1.xls");
wb.write(fileOut);
fileOut.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void createExcelMo(Workbook wb){
Sheet sheet = wb.createSheet("用户分类添加批导");
// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("手机号码");
cell = row.createCell(1);
cell.setCellValue("所属父类");
}
/**
* 设置模板文件的横向表头单元格的样式
* @param wb
* @return
*/
public static void creatExcelHidePage(Workbook workbook){ Sheet hideInfoSheet =
workbook.createSheet(EXCEL_HIDE_SHEET_NAME);//隐藏一些信息//在隐藏页设置选择信息
//第二行设置省份名称列表
Row provinceNameRow = hideInfoSheet.createRow(1);
creatRow(provinceNameRow, provinceList);
//第二行设置省份名称列表
creatExcelNameList(workbook, HIDE_SHEET_NAME_PROVINCE, 2, provinceList.length, false);
//设置隐藏页标志
workbook.setSheetHidden(workbook.getSheetIndex(EXCEL_HIDE_SHEET_N AME), true);
}
/**
* 创建一个名称
* @param workbook
*/
private static void creatExcelNameList(Workbook workbook,String nameCode,int order,int size,boolean cascadeFlag){
Name name;
name = workbook.createName();
name.setNameName(nameCode);
name.setRefersToFormula(EXCEL_HIDE_SHEET_NAME+"!"+creatExcelName List(order,size,cascadeFlag));
}
/**
* 名称数据行列计算表达式
* @param workbook
*/
private static String creatExcelNameList(int order,int size,boolean cascadeFlag){
char start = 'A';
if(cascadeFlag){
start = 'B';
if(size<=25){
char end = (char)(start+size-1);
return "$"+start+"$"+order+":$"+end+"$"+order;
}else{
char endPrefix = 'A';
char endSuffix = 'A';
if((size-25)/26==0||size==51){//26-51之间，包括边界（仅两次字母表计算）
if((size-25)%26==0){//边界值
endSuffix = (char)('A'+25);
}else{
endSuffix = (char)('A'+(size-25)%26-1);
}
}else{//51以上
if((size-25)%26==0){
endSuffix = (char)('A'+25);
endPrefix = (char)(endPrefix + (size-25)/26 - 1);
}else{
endSuffix = (char)('A'+(size-25)%26-1);
endPrefix = (char)(endPrefix + (size-25)/26);
}
}
return
"$"+start+"$"+order+":$"+endPrefix+endSuffix+"$"+order;
}
}else{
if(size<=26){
char end = (char)(start+size-1);
return "$"+start+"$"+order+":$"+end+"$"+order;
}else{
char endPrefix = 'A';
char endSuffix = 'A';
if(size%26==0){
endSuffix = (char)('A'+25);
if(size>52&&size/26>0){
endPrefix = (char)(endPrefix + size/26-2);
}