http://fruitking.iteye.com/blog/811931
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),然后在“添加”数据,设置数据的有效性选项(数据-数据有效性-序列;来源填写“=省份”)
比如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),然后在“添加”数据,设置数据的有效性选项(数据-数据有效性-序列;来源填写“=省份”)
![](http://dl.iteye.com/upload/attachment/348658/91153736-fa90-37ec-b052-fc0459505846.gif)
![点击查看原始大小图片](http://dl.iteye.com/upload/attachment/348660/cc3c4964-d80b-367d-8214-e96040c5268f.gif)
- 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 = { "四川","成都","绵阳","自贡","泸州","宜宾","攀枝花","