http://fruitking.iteye.com/blog/811931
Excel2003和Excel2007對下拉選擇和下拉級聯選擇的操作以及java程序的調用 JavaExcelApache
Excel2007和Excel2003的部分功能菜單有所調整
比如2003的“插入-名稱”，在2007中更為到“公式-定義的名稱”
比如2003的“插入-名稱-指定-首行”，在2007中更為到“公式-定義的名稱-根據所選內容創建-首行”
Excel功能點應用：
相對位置和絕對位置，特別在某個行列的數據是參考另外某個行列的數據而變動的，相對位置的表示方法：A8、B9等等，絕對位置的表示方法：$A$8、$B$9（就是使用美元符號$）
隱藏頁的數據引用。
2003中，假設sheet1是隱藏頁，并先定義好數據（公式-定義的名稱→定義；在sheet1中定義數據源名稱：省份；引用位置：=Sheet1!$A$1:$A$5），然后在“添加”數據，設置數據的有效性選項（數據-數據有效性-序列；來源填寫“=省份”）
2007中，假設sheet1是隱藏頁，并先定義好數據（插入→名稱→定義；在sheet1中定義數據源名稱：省份；引用位置：=Sheet1!$A$1:$A$5），然后在“添加”數據，設置數據的有效性選項（數據-數據有效性-序列；來源填寫“=省份”）
Java代碼 ?
package ?com.fruitking.caipiao; ???? import ?java.io.FileOutputStream; ??import ?java.io.IOException; ???? import ?org.apache.poi.hssf.usermodel.DVConstraint; ??import ?org.apache.poi.hssf.usermodel.HSSFCell; ??import ?org.apache.poi.hssf.usermodel.HSSFDataValidation; ??import ?org.apache.poi.hssf.usermodel.HSSFRow; ??import ?org.apache.poi.hssf.usermodel.HSSFSheet; ??import ?org.apache.poi.hssf.usermodel.HSSFWorkbook; ??import ?org.apache.poi.ss.usermodel.DataValidation; ??import ?org.apache.poi.ss.usermodel.Name; ??import ?org.apache.poi.ss.util.CellRangeAddressList; ???? public ?class ?TestExcelSelect?{ ???? ???? ?? ?????public ?static ?void ?main(String?[]?args)?throws ?IOException?{?? ?? ?????????HSSFWorkbook?workbook?=?new ?HSSFWorkbook();?? ?????????HSSFSheet?userinfosheet1?=?workbook.createSheet("用戶信息表-1" );?? ?????????HSSFSheet?userinfosheet2?=?workbook.createSheet("用戶信息表-2" );?? ??????????? ?????????TestExcelSelect.creatHideSheet(workbook,?"hideselectinfosheet" ); ?? ??????????? ?????????TestExcelSelect.creatExcelNameList(workbook); ?? ??????????? ?????????TestExcelSelect.creatAppRow(userinfosheet1,?"許果" ,1 ?????????TestExcelSelect.creatAppRow(userinfosheet1,?"劉德華" ,2 ?????????TestExcelSelect.creatAppRow(userinfosheet1,?"劉若英" ,3 ?????????TestExcelSelect.creatAppRow(userinfosheet2,?"張學友" ,1 ?????????TestExcelSelect.creatAppRow(userinfosheet2,?"林志玲" ,2 ?????????TestExcelSelect.creatAppRow(userinfosheet2,?"林熙蕾" ,3 ????????? ?? ??????????? ?????????FileOutputStream?out=new ?FileOutputStream("success.xls" );?? ?? ?????????workbook.write(out);?? ?? ?????????out.close(); ?? ?????} ?? ????? ?? ?????? ? ? ?? ?????public ?static ?void ?creatExcelNameList(HSSFWorkbook?workbook){ ?? ?????????? ?????????Name?name; ?? ?????????name?=?workbook.createName(); ?? ?????????name.setNameName("provinceInfo" ); ?? ?????????name.setRefersToFormula("hideselectinfosheet!$A$1:$E$1" ); ?? ?????????name?=?workbook.createName(); ?? ?????????name.setNameName("浙江" ); ?? ?????????name.setRefersToFormula("hideselectinfosheet!$B$2:$K$2" ); ?? ?????????name?=?workbook.createName(); ?? ?????????name.setNameName("山東" ); ?? ?????????name.setRefersToFormula("hideselectinfosheet!$B$3:$I$3" ); ?? ?????????name?=?workbook.createName(); ?? ?????????name.setNameName("江西" ); ?? ?????????name.setRefersToFormula("hideselectinfosheet!$B$4:$E$4" ); ?? ?????????name?=?workbook.createName(); ?? ?????????name.setNameName("江蘇" ); ?? ?????????name.setRefersToFormula("hideselectinfosheet!$B$5:$I$5" ); ?? ?????????name?=?workbook.createName(); ?? ?????????name.setNameName("四川" ); ?? ?????????name.setRefersToFormula("hideselectinfosheet!$B$6:$K$6" ); ?? ?????} ?? ????? ?? ????? ?? ?????? ? ? ? ?? ?????public ?static ?void ?creatHideSheet(HSSFWorkbook?workbook,String?hideSheetName){ ?? ?????????HSSFSheet?hideselectinfosheet?=?workbook.createSheet(hideSheetName);?? ??????????? ?????????String[]?provinceList?=?{"浙江" ,"山東" ,"江西" ,"江蘇" ,"四川" }; ?? ?????????String[]?zjProvinceList?=?{"浙江" ,"杭州" ,"寧波" ,"溫州" ,"臺州" ,"紹興" ,"金華" ,"湖州" ,"麗水" ,"衢州" ,"舟山" }; ?? ?????????String[]?sdProvinceList?=?{"山東" ,"濟南" ,"青島" ,"煙臺" ,"東營" ,"菏澤" ,"淄博" ,"濟寧" ,"威海" }; ?? ?????????String[]?jxProvinceList?=?{"江西" ,"南昌" ,"新余" ,"鷹潭" ,"撫州" }; ?? ?????????String[]?jsProvinceList?=?{"江蘇" ,"南京" ,"蘇州" ,"無錫" ,"常州" ,"南通" ,"泰州" ,"連云港" ,"徐州" }; ?? ?????????String[]?scProvinceList?=?{"四川" ,"成都" ,"綿陽" ,"自貢" ,"瀘州" ,"宜賓" ,"攀枝花" ,"廣安" ,"達州" ,"廣元" ,"遂寧" }; ?? ??????????? ?????????HSSFRow?provinceRow?=?hideselectinfosheet.createRow(0 ?????????TestExcelSelect.creatRow(provinceRow,?provinceList); ?? ?????????HSSFRow?zjProvinceRow?=?hideselectinfosheet.createRow(1 ?????????TestExcelSelect.creatRow(zjProvinceRow,?zjProvinceList); ?? ?????????HSSFRow?sdProvinceRow?=?hideselectinfosheet.createRow(2 ?????????TestExcelSelect.creatRow(sdProvinceRow,?sdProvinceList); ?? ?????????HSSFRow?jxProvinceRow?=?hideselectinfosheet.createRow(3 ?????????TestExcelSelect.creatRow(jxProvinceRow,?jxProvinceList); ?? ?????????HSSFRow?jsProvinceRow?=?hideselectinfosheet.createRow(4 ?????????TestExcelSelect.creatRow(jsProvinceRow,?jsProvinceList); ?? ?????????HSSFRow?scProvinceRow?=?hideselectinfosheet.createRow(5 ?????????TestExcelSelect.creatRow(scProvinceRow,?scProvinceList); ?? ??????????? ?????????workbook.setSheetHidden(workbook.getSheetIndex(hideSheetName),?true ); ?? ?????} ?? ????? ?? ?????? ? ? ? ?? ?????public ?static ?void ?creatAppRow(HSSFSheet?userinfosheet1,String?userName,int ?naturalRowIndex){ ?? ???????? ?? ??????????? ??????????? ?????????HSSFRow?row?=?userinfosheet1.createRow(naturalRowIndex-1 ?????????HSSFCell?userNameLableCell?=?row.createCell(0 ?????????userNameLableCell.setCellValue("用戶姓名:" ); ?? ?????????HSSFCell?userNameCell?=?row.createCell(1 ?????????userNameCell.setCellValue(userName); ?? ?????????HSSFCell?provinceLableCell?=?row.createCell(2 ?????????provinceLableCell.setCellValue("出生省份:" ); ?? ?????????HSSFCell?provinceCell?=?row.createCell(3 ?????????provinceCell.setCellValue("請選擇" ); ?? ?????????HSSFCell?cityLableCell?=?row.createCell(4 ?????????cityLableCell.setCellValue("出生城市:" ); ?? ?????????HSSFCell?cityCell?=?row.createCell(5 ?????????cityCell.setCellValue("請選擇" ); ?? ????????? ?? ??????????? ?????????DataValidation?data_validation_list?=?TestExcelSelect.getDataValidationByFormula("provinceInfo" ,naturalRowIndex,4 ??????????? ?????????userinfosheet1.addValidationData(data_validation_list); ?? ?????????DataValidation?data_validation_list2?=?TestExcelSelect.getDataValidationByFormula("INDIRECT($D" +naturalRowIndex+")" ,naturalRowIndex,6 ??????????? ?????????userinfosheet1.addValidationData(data_validation_list2); ?? ?????} ?? ????? ?? ?????? ? ? ? ?? ?????public ?static ?void ?creatRow(HSSFRow?currentRow,String[]?textList){ ?? ?????????if (textList!=null &&textList.length>0 ?????????????int ?i?=?0 ?????????????for (String?cellValue?:?textList){ ?? ?????????????????HSSFCell?userNameLableCell?=?currentRow.createCell(i++); ?? ?????????????????userNameLableCell.setCellValue(cellValue); ?? ?????????????} ?? ?????????} ?? ?????} ?? ????? ?? ?????? ? ? ? ? ? ?? ?????public ?static ?DataValidation?getDataValidationList(String[]?selectTextList,int ?naturalRowIndex,int ?naturalColumnIndex){ ?? ??????????? ?????????DVConstraint?constraint?=?DVConstraint.createExplicitListConstraint(selectTextList); ?? ??????????? ????????? ?? ?????????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_list?=?new ?HSSFDataValidation(regions,constraint);?? ?? ?????????return ?data_validation_list;?? ?? ?????} ?? ????? ?? ?????? ? ? ? ? ? ?? ?????public ?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_list?=?new ?HSSFDataValidation(regions,constraint); ?? ?????????return ?data_validation_list;?? ?? ?????} ?? }?? package com.fruitking.caipiao;import java.io.FileOutputStream;
import java.io.IOException;import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.util.CellRangeAddressList;public class TestExcelSelect {public static void main(String [] args) throws IOException { HSSFWorkbook workbook = new HSSFWorkbook();//excel文件對象 HSSFSheet userinfosheet1 = workbook.createSheet("用戶信息表-1");//工作表對象HSSFSheet userinfosheet2 = workbook.createSheet("用戶信息表-2");//工作表對象//創建一個隱藏頁和隱藏數據集TestExcelSelect.creatHideSheet(workbook, "hideselectinfosheet");//設置名稱數據集TestExcelSelect.creatExcelNameList(workbook);//創建一行數據TestExcelSelect.creatAppRow(userinfosheet1, "許果",1);TestExcelSelect.creatAppRow(userinfosheet1, "劉德華",2);TestExcelSelect.creatAppRow(userinfosheet1, "劉若英",3);TestExcelSelect.creatAppRow(userinfosheet2, "張學友",1);TestExcelSelect.creatAppRow(userinfosheet2, "林志玲",2);TestExcelSelect.creatAppRow(userinfosheet2, "林熙蕾",3);//生成輸入文件FileOutputStream out=new FileOutputStream("success.xls"); workbook.write(out); out.close();}/*** 名稱管理* @param workbook*/public static void creatExcelNameList(HSSFWorkbook workbook){//名稱管理Name name;name = workbook.createName();name.setNameName("provinceInfo");name.setRefersToFormula("hideselectinfosheet!$A$1:$E$1");name = workbook.createName();name.setNameName("浙江");name.setRefersToFormula("hideselectinfosheet!$B$2:$K$2");name = workbook.createName();name.setNameName("山東");name.setRefersToFormula("hideselectinfosheet!$B$3:$I$3");name = workbook.createName();name.setNameName("江西");name.setRefersToFormula("hideselectinfosheet!$B$4:$E$4");name = workbook.createName();name.setNameName("江蘇");name.setRefersToFormula("hideselectinfosheet!$B$5:$I$5");name = workbook.createName();name.setNameName("四川");name.setRefersToFormula("hideselectinfosheet!$B$6:$K$6");}/*** 創建隱藏頁和數據域* @param workbook* @param hideSheetName*/public static void creatHideSheet(HSSFWorkbook workbook,String hideSheetName){HSSFSheet hideselectinfosheet = workbook.createSheet(hideSheetName);//隱藏一些信息//設置下拉列表的內容 String[] provinceList = {"浙江","山東","江西","江蘇","四川"};String[] zjProvinceList = {"浙江","杭州","寧波","溫州","臺州","紹興","金華","湖州","麗水","衢州","舟山"};String[] sdProvinceList = {"山東","濟南","青島","煙臺","東營","菏澤","淄博","濟寧","威海"};String[] jxProvinceList = {"江西","南昌","新余","鷹潭","撫州"};String[] jsProvinceList = {"江蘇","南京","蘇州","無錫","常州","南通","泰州","連云港","徐州"};String[] scProvinceList = {"四川","成都","綿陽","自貢","瀘州","宜賓","攀枝花","廣安","達州","廣元","遂寧"};//在隱藏頁設置選擇信息HSSFRow provinceRow = hideselectinfosheet.createRow(0);TestExcelSelect.creatRow(provinceRow, provinceList);HSSFRow zjProvinceRow = hideselectinfosheet.createRow(1);TestExcelSelect.creatRow(zjProvinceRow, zjProvinceList);HSSFRow sdProvinceRow = hideselectinfosheet.createRow(2);TestExcelSelect.creatRow(sdProvinceRow, sdProvinceList);HSSFRow jxProvinceRow = hideselectinfosheet.createRow(3);TestExcelSelect.creatRow(jxProvinceRow, jxProvinceList);HSSFRow jsProvinceRow = hideselectinfosheet.createRow(4);TestExcelSelect.creatRow(jsProvinceRow, jsProvinceList);HSSFRow scProvinceRow = hideselectinfosheet.createRow(5);TestExcelSelect.creatRow(scProvinceRow, scProvinceList);//設置隱藏頁標志workbook.setSheetHidden(workbook.getSheetIndex(hideSheetName), true);}/*** 創建一列應用數據* @param userinfosheet1* @param userName*/public static void creatAppRow(HSSFSheet userinfosheet1,String userName,int naturalRowIndex){//構造一個信息輸入表單，用戶姓名，出生省份，出生城市//要求省份是可以下拉選擇的，出生城市根據所選擇的省份級聯下拉選擇//在第一行第一個單元格，插入下拉框HSSFRow row = userinfosheet1.createRow(naturalRowIndex-1);HSSFCell userNameLableCell = row.createCell(0);userNameLableCell.setCellValue("用戶姓名:");HSSFCell userNameCell = row.createCell(1);userNameCell.setCellValue(userName);HSSFCell provinceLableCell = row.createCell(2);provinceLableCell.setCellValue("出生省份:");HSSFCell provinceCell = row.createCell(3);provinceCell.setCellValue("請選擇");HSSFCell cityLableCell = row.createCell(4);cityLableCell.setCellValue("出生城市:");HSSFCell cityCell = row.createCell(5);cityCell.setCellValue("請選擇");//得到驗證對象 DataValidation data_validation_list = TestExcelSelect.getDataValidationByFormula("provinceInfo",naturalRowIndex,4);//工作表添加驗證數據 userinfosheet1.addValidationData(data_validation_list);DataValidation data_validation_list2 = TestExcelSelect.getDataValidationByFormula("INDIRECT($D"+naturalRowIndex+")",naturalRowIndex,6);//工作表添加驗證數據 userinfosheet1.addValidationData(data_validation_list2);}/*** 創建一列數據* @param currentRow* @param textList*/public static void creatRow(HSSFRow currentRow,String[] textList){if(textList!=null&&textList.length>0){int i = 0;for(String cellValue : textList){HSSFCell userNameLableCell = currentRow.createCell(i++);userNameLableCell.setCellValue(cellValue);}}}/*** 對Excel自然行列設置一個數據驗證（并出現下拉列表選擇格式）* @param selectTextList* @param naturalRowIndex* @param naturalColumnIndex* @return*/public static DataValidation getDataValidationList(String[] selectTextList,int naturalRowIndex,int naturalColumnIndex){//加載下拉列表內容 DVConstraint constraint = DVConstraint.createExplicitListConstraint(selectTextList);//設置數據有效性加載在哪個單元格上。 //四個參數分別是：起始行、終止行、起始列、終止列 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_list = new HSSFDataValidation(regions,constraint); return data_validation_list; }/*** 使用已定義的數據源方式設置一個數據驗證* @param formulaString* @param naturalRowIndex* @param naturalColumnIndex* @return*/public 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_list = new HSSFDataValidation(regions,constraint);return data_validation_list; }
}
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
http://ttaale.iteye.com/blog/836306
POI 操作Excel，創建下拉框 ExcelApache
Java代碼 ?
import ?java.io.FileOutputStream; ??import ?java.util.Date; ???? import ?org.apache.poi.hssf.usermodel.DVConstraint; ??import ?org.apache.poi.hssf.usermodel.HSSFCell; ??import ?org.apache.poi.hssf.usermodel.HSSFDataValidation; ??import ?org.apache.poi.hssf.usermodel.HSSFRow; ??import ?org.apache.poi.hssf.usermodel.HSSFSheet; ??import ?org.apache.poi.hssf.usermodel.HSSFWorkbook; ??import ?org.apache.poi.hssf.util.CellRangeAddressList; ???? public ?class ?POI ??{ ?? ?public ?static ?void ?main(String?[]?args) ?? ?{ ?? ??String?[]?list={"東軟" ,"華信" ,"SAP" ,"海輝" }; ?? ??new ?POI().createListBox(list); ?? ??return ; ?? ?} ?? ?public ?void ?createListBox?(String?[]?list) ?? ?{ ?? ???? ??HSSFWorkbook?wb?=?new ?HSSFWorkbook(); ?? ?????HSSFSheet?sheet?=?wb.createSheet("new?sheet" ); ?? ???? ?? ??????? ?????HSSFRow?row?=?sheet.createRow(0 ?????HSSFCell?cell?=?row.createCell(0 ???? ?? ??????? ?????cell.setCellValue("請選擇" );?? ?? ??????? ???? ?? ??????? ?????CellRangeAddressList?regions?=?new ?CellRangeAddressList(0 0 0 0 ???? ?? ??????? ?????DVConstraint?constraint?=?DVConstraint.createExplicitListConstraint(list); ?? ???? ?? ??????? ?????HSSFDataValidation?data_validation?=?new ?HSSFDataValidation(regions,constraint);? ?? ???? ?? ??????? ?????sheet.addValidationData(data_validation); ?? ?? ??????? ?????FileOutputStream?fileOut; ?? ??try ?{ ?? ???fileOut?=?new ?FileOutputStream("workbook.xls" ); ?? ???wb.write(fileOut); ?? ???fileOut.close(); ?? ??}?catch ?(Exception?e)?{ ?? ??? ?? ???e.printStackTrace(); ?? ??} ?? ?? ?? ???? ??System.out.println("Over" ); ?? ?} ?? }??＝＝＝＝＝＝＝＝＝＝＝＝＝＝
http://www.iteye.com/problems/65191
誰使用過POI3.8 設置EXCEL2007的數據有效性問題補充：
hudingchen 寫道
有問題嗎？ 我寫個例子，poi3.8,excel2007好用
Java代碼
public ?static ?void ?main(String[]?args)?{ ??????FileOutputStream?out?=?null ; ?? ????try ?{ ?? ?????????? ????????HSSFWorkbook?wb?=?new ?HSSFWorkbook(); ?? ?????????? ????????HSSFSheet?sheet?=?wb.createSheet("sheet1" ); ?? ?????????? ????????out?=?new ?FileOutputStream("C://aaa.xls" ); ?? ?????????? ????????HSSFDataValidation?validate?=?PoiTest.setValidate((short )?1 ????????????????(short )?1 short )?1 short )?1 ?????????? ????????sheet.addValidationData(validate); ?? ?????????? ????????wb.write(out); ?? ????????out.close(); ?? ????}?catch ?(FileNotFoundException?e)?{ ?? ????????e.printStackTrace(); ?? ????}?catch ?(IOException?e)?{ ?? ????????e.printStackTrace(); ?? ????}?finally ?{ ?? ????????if ?(out?!=?null )?{ ?? ????????????try ?{ ?? ????????????????out.close(); ?? ????????????}?catch ?(IOException?e)?{ ?? ?????????????????? ????????????????e.printStackTrace(); ?? ????????????} ?? ????????} ?? ????} ?? } ?? ?? public ?static ?HSSFDataValidation?setValidate(short ?beginRow, ??????????short ?beginCol,?short ?endRow,?short ?endCol)?{ ?? ?????? ????DVConstraint?constraint?=?DVConstraint.createNumericConstraint( ?? ????????????DVConstraint.ValidationType.INTEGER, ?? ????????????DVConstraint.OperatorType.BETWEEN,?"1" ,?"100" ); ?? ?????? ????CellRangeAddressList?regions?=?new ?CellRangeAddressList(beginRow, ?? ????????????beginCol,?endRow,?endCol); ?? ?????? ????HSSFDataValidation?ret?=?new ?HSSFDataValidation(regions,?constraint); ?? ????return ?ret; ?? }?? public static void main(String[] args) {FileOutputStream out = null;try {// excel對象HSSFWorkbook wb = new HSSFWorkbook();// sheet對象HSSFSheet sheet = wb.createSheet("sheet1");// 輸出excel對象out = new FileOutputStream("C://aaa.xls");// 取得規則HSSFDataValidation validate = PoiTest.setValidate((short) 1,(short) 1, (short) 1, (short) 1);// 設定規則sheet.addValidationData(validate);// 輸出excelwb.write(out);out.close();} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} finally {if (out != null) {try {out.close();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}public static HSSFDataValidation setValidate(short beginRow,short beginCol, short endRow, short endCol) {// 創建一個規則：1-100的數字DVConstraint constraint = DVConstraint.createNumericConstraint(DVConstraint.ValidationType.INTEGER,DVConstraint.OperatorType.BETWEEN, "1", "100");// 設定在哪個單元格生效CellRangeAddressList regions = new CellRangeAddressList(beginRow,beginCol, endRow, endCol);// 創建規則對象HSSFDataValidation ret = new HSSFDataValidation(regions, constraint);return ret;}
我之前的代碼，在2007下是好用的，不過沒有使用XSSF，我試著用XSSF寫了一下，但是生成的excel文件的數據有效性沒有加上，不過代碼我覺得沒有問題的，先看下
Java代碼 ?
public ?static ?void ?main(String[]?args)?{ ??????FileOutputStream?out?=?null ; ?? ????try ?{ ?? ?????????? ????????XSSFWorkbook?wb?=?new ?XSSFWorkbook(); ?? ?????????? ????????XSSFSheet?sheet?=?wb.createSheet("sheet1" ); ?? ?????????? ????????out?=?new ?FileOutputStream("C://aaa1.xls" ); ?? ?????????? ????????DataValidation?validate?=?PoiTest.setValidate(sheet,?(short )?1 ????????????????(short )?1 short )?1 short )?1 ?????????? ????????sheet.addValidationData(validate); ?? ????????wb.write(out); ?? ????????out.close(); ?? ????}?catch ?(FileNotFoundException?e)?{ ?? ????????e.printStackTrace(); ?? ????}?catch ?(IOException?e)?{ ?? ????????e.printStackTrace(); ?? ????}?finally ?{ ?? ????????if ?(out?!=?null )?{ ?? ????????????try ?{ ?? ????????????????out.close(); ?? ????????????}?catch ?(IOException?e)?{ ?? ?????????????????? ????????????????e.printStackTrace(); ?? ????????????} ?? ????????} ?? ????} ?? } ?? ?? public ?static ?DataValidation?setValidate(XSSFSheet?sheet,?short ?beginRow, ??????????short ?beginCol,?short ?endRow,?short ?endCol)?{ ?? ????XSSFDataValidationHelper?helper?=?new ?XSSFDataValidationHelper(sheet); ?? ????DataValidationConstraint?constraint?=?helper.createNumericConstraint( ?? ????????????ValidationType.INTEGER,?OperatorType.BETWEEN,?"1" ,?"100" ); ?? ????CellRangeAddressList?regions?=?new ?CellRangeAddressList(beginRow, ?? ????????????beginCol,?endRow,?endCol); ?? ????return ?helper.createValidation(constraint,?regions); ?? }??
public static void main(String[] args) {FileOutputStream out = null;try {// excel對象XSSFWorkbook wb = new XSSFWorkbook();// sheet對象XSSFSheet sheet = wb.createSheet("sheet1");// 輸出excel對象out = new FileOutputStream("C://aaa1.xls");// 取得規則DataValidation validate = PoiTest.setValidate(sheet, (short) 1,(short) 1, (short) 1, (short) 1);// 設定規則sheet.addValidationData(validate);wb.write(out);out.close();} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} finally {if (out != null) {try {out.close();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}public static DataValidation setValidate(XSSFSheet sheet, short beginRow,short beginCol, short endRow, short endCol) {XSSFDataValidationHelper helper = new XSSFDataValidationHelper(sheet);DataValidationConstraint constraint = helper.createNumericConstraint(ValidationType.INTEGER, OperatorType.BETWEEN, "1", "100");CellRangeAddressList regions = new CellRangeAddressList(beginRow,beginCol, endRow, endCol);return helper.createValidation(constraint, regions);}
后來我查了下文檔，http://poi.apache.org/spreadsheet/quick-guide.html#Validation
其中有這么一句
Currently - as of version 3.5 - the XSSF stream does not support data validations and neither it nor the HSSF stream allow data validations to be recovered from sheets
也就是說3.5中XSSF是不支持validations的，我懷疑3.8也還不支持，估計以后會修正吧，希望對你有幫助。
總結
以上是生活随笔 為你收集整理的POI 2003/2007 下拉列表 的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔 網站內容還不錯，歡迎將生活随笔 推薦給好友。
