PIO的EXCEL的写入和导出

PIO的EXCEL的字体样式，边框样式，背景色样式，下拉选择，下拉级联选择，日期格式验证，下拉验证，验证错误提示，输入信息提示等功能

代码如下

package com.fruitking.excel;import java.io.FileOutputStream;import java.io.IOException;import org.apache.poi.ss.usermodel.Workbook;public class ExcelCreateMain {/** * @param args */public static void main(String[] args) throws IOException{Workbook wb = ExcelCreateUtil.createTemplateWorkbook();ExcelHidePageUtil.creatExcelHidePage(wb);ExcelCreateUtil.setDataValidation(wb); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("student_template.xls"); wb.write(fileOut); fileOut.close();}}

package com.fruitking.excel;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.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.DataValidation;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.IndexedColors;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 ExcelCreateUtil {/** * 创建一个预定义格式的模板文件的工作薄 * @return */public static Workbook createTemplateWorkbook(){Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("学生信息表-1"); // 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(ExcelParameter.TITLE_STUDENT_NO); cell.setCellStyle(ExcelCreateUtil.getTitleStyle(wb)); cell = row.createCell(1); cell.setCellValue(ExcelParameter.TITLE_STUDENT_NAME); cell.setCellStyle(ExcelCreateUtil.getTitleStyle(wb)); cell = row.createCell(2); cell.setCellValue(ExcelParameter.TITLE_STUDENT_SEX); cell.setCellStyle(ExcelCreateUtil.getTitleStyle(wb)); cell = row.createCell(3); cell.setCellValue(ExcelParameter.TITLE_STUDENT_AGE); cell.setCellStyle(ExcelCreateUtil.getTitleStyle(wb)); cell = row.createCell(4); cell.setCellValue(ExcelParameter.TITLE_BIRTHDAY); cell.setCellStyle(ExcelCreateUtil.getTitleStyle(wb)); cell = row.createCell(5); cell.setCellValue(ExcelParameter.TITLE_PROVINCE_NAME); cell.setCellStyle(ExcelCreateUtil.getTitleStyle(wb)); cell = row.createCell(6); cell.setCellValue(ExcelParameter.TITLE_CITY_NAME); cell.setCellStyle(ExcelCreateUtil.getTitleStyle(wb)); cell = row.createCell(7); cell.setCellValue(ExcelParameter.TITLE_COLLAGE_NAME); cell.setCellStyle(ExcelCreateUtil.getTitleStyle(wb)); cell = row.createCell(8); cell.setCellValue(ExcelParameter.TITLE_MAJOR_NAME); cell.setCellStyle(ExcelCreateUtil.getTitleStyle(wb)); cell = row.createCell(9); cell.setCellValue(ExcelParameter.TITLE_REMARK); cell.setCellStyle(ExcelCreateUtil.getTitleStyle(wb)); row = sheet.createRow(1); cell = row.createCell(0); cell.setCellValue("12009007001"); cell.setCellStyle(ExcelCreateUtil.getValueStyle(wb)); cell = row.createCell(1); cell.setCellValue("许果"); cell.setCellStyle(ExcelCreateUtil.getValueStyle(wb)); cell = row.createCell(2); cell.setCellValue("男"); cell.setCellStyle(ExcelCreateUtil.getValueStyle(wb)); cell = row.createCell(3); cell.setCellValue("28"); cell.setCellStyle(ExcelCreateUtil.getValueStyle(wb)); cell = row.createCell(4); cell.setCellValue("2010-11-23"); cell.setCellStyle(ExcelCreateUtil.getValueStyle(wb)); cell = row.createCell(5); cell.setCellValue("四川"); cell.setCellStyle(ExcelCreateUtil.getValueStyle(wb)); cell = row.createCell(6); cell.setCellValue("巴中"); cell.setCellStyle(ExcelCreateUtil.getValueStyle(wb)); cell = row.createCell(7); cell.setCellValue("四川理工学院"); cell.setCellStyle(ExcelCreateUtil.getValueStyle(wb)); cell = row.createCell(8); cell.setCellValue("计算机科学与技术专业"); cell.setCellStyle(ExcelCreateUtil.getValueStyle(wb)); cell = row.createCell(9); cell.setCellValue("此人喜欢美女"); cell.setCellStyle(ExcelCreateUtil.getValueStyle(wb)); sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4); sheet.autoSizeColumn(5); sheet.autoSizeColumn(6); sheet.autoSizeColumn(7); sheet.autoSizeColumn(8); sheet.autoSizeColumn(9); return wb;}/** * 添加数据验证选项 * @param sheet */public static void setDataValidation(Workbook wb){int sheetIndex = wb.getNumberOfSheets();if(sheetIndex>0){for(int i=0;i<sheetIndex;i++){Sheet sheet = wb.getSheetAt(i);if(!ExcelParameter.EXCEL_HIDE_SHEET_NAME.equals(sheet.getSheetName())){//省份选项添加验证数据 DataValidation data_validation_list = ExcelCreateUtil.getDataValidationByFormula(ExcelParameter.HIDE_SHEET_NAME_PROVINCE,2,6); sheet.addValidationData(data_validation_list); //城市选项添加验证数据 data_validation_list = ExcelCreateUtil.getDataValidationByFormula("INDIRECT(F2)",2,7); sheet.addValidationData(data_validation_list); //性别添加验证数据 data_validation_list = ExcelCreateUtil.getDataValidationByFormula(ExcelParameter.HIDE_SHEET_NAME_SEX,2,3); sheet.addValidationData(data_validation_list); //有效时间项的格式验证 data_validation_list = ExcelCreateUtil.getDataValidationByDate(2,5); sheet.addValidationData(data_validation_list);}}}}/** * 设置模板文件的横向表头单元格的样式 * @param wb * @return */private static CellStyle getTitleStyle(Workbook wb){CellStyle style = wb.createCellStyle();//对齐方式设置style.setAlignment(CellStyle.ALIGN_CENTER);//边框颜色和宽度设置style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); //设置背景颜色 style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); //粗体字设置 Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); return style;}/** * 设置模板文件的输入项表格样式 * @param wb * @return */private static CellStyle getValueStyle(Workbook wb){CellStyle style = wb.createCellStyle();//对齐方式设置style.setAlignment(CellStyle.ALIGN_LEFT);//边框颜色和宽度设置style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BROWN.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BROWN.getIndex()); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BROWN.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BROWN.getIndex()); return style;}/** * 使用已定义的数据源方式设置一个数据验证 * @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_list = new HSSFDataValidation(regions,constraint); //设置输入信息提示信息 data_validation_list.createPromptBox("下拉选择提示","请使用下拉方式选择合适的值！"); //设置输入错误提示信息 data_validation_list.createErrorBox("选择错误提示","你输入的值未在备选列表中，请下拉选择合适的值！"); return data_validation_list; } private static DataValidation getDataValidationByDate(int naturalRowIndex,int naturalColumnIndex){ //加载下拉列表内容 DVConstraint constraint = DVConstraint.createDateConstraint(DVConstraint.OperatorType.BETWEEN,"1900-01-01", "5000-01-01", "yyyy-mm-dd"); //设置数据有效性加载在哪个单元格上。 //四个参数分别是：起始行、终止行、起始列、终止列 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); //设置输入信息提示信息 data_validation_list.createPromptBox("日期格式提示","请按照'yyyy-mm-dd'格式输入日期值！"); //设置输入错误提示信息 data_validation_list.createErrorBox("日期格式错误提示","你输入的日期格式不符合'yyyy-mm-dd'格式规范，请重新输入！"); return data_validation_list; }}

package com.fruitking.excel;import org.apache.poi.ss.usermodel.Cell;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;public class ExcelHidePageUtil {/** * 创建一个隐藏页和隐藏数据集 * @param workbook */public static void creatExcelHidePage(Workbook workbook){ Sheet hideInfoSheet = workbook.createSheet(ExcelParameter.EXCEL_HIDE_SHEET_NAME);//隐藏一些信息 //在隐藏页设置选择信息 //第一行设置性别信息 Row sexRow = hideInfoSheet.createRow(0); ExcelHidePageUtil.creatRow(sexRow, ExcelParameter.sexList); //第二行设置省份名称列表 Row provinceNameRow = hideInfoSheet.createRow(1); ExcelHidePageUtil.creatRow(provinceNameRow, ExcelParameter.provinceList); //一下行设置城市名称列表 Row cityNameRow = hideInfoSheet.createRow(2); ExcelHidePageUtil.creatRow(cityNameRow, ExcelParameter.zjProvinceList); cityNameRow = hideInfoSheet.createRow(3); ExcelHidePageUtil.creatRow(cityNameRow, ExcelParameter.sdProvinceList); cityNameRow = hideInfoSheet.createRow(4); ExcelHidePageUtil.creatRow(cityNameRow, ExcelParameter.jxProvinceList); cityNameRow = hideInfoSheet.createRow(5); ExcelHidePageUtil.creatRow(cityNameRow, ExcelParameter.jsProvinceList); cityNameRow = hideInfoSheet.createRow(6); ExcelHidePageUtil.creatRow(cityNameRow, ExcelParameter.scProvinceList); //名称管理 //第一行设置性别信息 ExcelHidePageUtil.creatExcelNameList(workbook, ExcelParameter.HIDE_SHEET_NAME_SEX, 1, ExcelParameter.sexList.length, false); //第二行设置省份名称列表 ExcelHidePageUtil.creatExcelNameList(workbook, ExcelParameter.HIDE_SHEET_NAME_PROVINCE, 2, ExcelParameter.provinceList.length, false); //以后动态大小设置省份对应的城市列表 ExcelHidePageUtil.creatExcelNameList(workbook, ExcelParameter.provinceList[0], 3, ExcelParameter.zjProvinceList.length, true); ExcelHidePageUtil.creatExcelNameList(workbook, ExcelParameter.provinceList[1], 4, ExcelParameter.sdProvinceList.length, true); ExcelHidePageUtil.creatExcelNameList(workbook, ExcelParameter.provinceList[2], 5, ExcelParameter.jxProvinceList.length, true); ExcelHidePageUtil.creatExcelNameList(workbook, ExcelParameter.provinceList[3], 6, ExcelParameter.jsProvinceList.length, true); ExcelHidePageUtil.creatExcelNameList(workbook, ExcelParameter.provinceList[4], 7, ExcelParameter.scProvinceList.length, true); //设置隐藏页标志 workbook.setSheetHidden(workbook.getSheetIndex(ExcelParameter.EXCEL_HIDE_SHEET_NAME), 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(ExcelParameter.EXCEL_HIDE_SHEET_NAME+"!"+ExcelHidePageUtil.creatExcelNameList(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); } }else{ endSuffix = (char)('A'+size%26-1); if(size>52&&size/26>0){ endPrefix = (char)(endPrefix + size/26-1); } } return "$"+start+"$"+order+":$"+endPrefix+endSuffix+"$"+order; } } } /** * 创建一列数据 * @param currentRow * @param textList */ private static void creatRow(Row currentRow,String[] textList){ if(textList!=null&&textList.length>0){ int i = 0; for(String cellValue : textList){ Cell userNameLableCell = currentRow.createCell(i++); userNameLableCell.setCellValue(cellValue); } } } /** * 创建一列数据 * @param currentRow * @param textList */ private static void creatRow(Row currentRow,String headerName,String[] textList){ if(headerName!=null){ Cell userNameLableCell = currentRow.createCell(0); userNameLableCell.setCellValue(headerName); } if(textList!=null&&textList.length>0){ int i = 1; for(String cellValue : textList){ Cell userNameLableCell = currentRow.createCell(i++); userNameLableCell.setCellValue(cellValue); } } }}

package com.fruitking.excel;public class ExcelParameter {public static String TITLE_STUDENT_NO = "学生学号";public static String TITLE_STUDENT_NAME = "学生姓名";public static String TITLE_STUDENT_SEX = "性别";public static String TITLE_STUDENT_AGE = "年龄";public static String TITLE_BIRTHDAY = "出生日期";public static String TITLE_PROVINCE_NAME = "出生省份";public static String TITLE_CITY_NAME = "出生城市";public static String TITLE_COLLAGE_NAME = "所在学院";public static String TITLE_MAJOR_NAME = "专业名称";public static String TITLE_REMARK = "备注信息";public static String EXCEL_HIDE_SHEET_NAME = "excelhidesheetname";public static String HIDE_SHEET_NAME_SEX = "sexList";public static String HIDE_SHEET_NAME_PROVINCE = "provinceList";//设置下拉列表的内容public static String[] sexList = {"男","女"};public static String[] provinceList = {"浙江","山东","江西","江苏","四川"};public static String[] zjProvinceList = {"浙江","杭州","宁波","温州","台州","绍兴","金华","湖州","丽水","衢州","舟山"};public static String[] sdProvinceList = {"山东","济南","青岛","烟台","东营","菏泽","淄博","济宁","威海"};public static String[] jxProvinceList = {"江西","南昌","新余","鹰潭","抚州"};public static String[] jsProvinceList = {"江苏","南京","苏州","无锡","常州","南通","泰州","连云港","徐州"};public static String[] scProvinceList = {"四川","成都","绵阳","自贡","泸州","宜宾","攀枝花","广安","达州","广元","遂宁"};}

package com.fruitking.excel;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.poifs.filesystem.POIFSFileSystem;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;public class ExcelParserUtil {/** * 解析规则包模板文件 * @param templateFileInputStream * @return * @throws IOException */public static List<Student> parseTemplateFile(InputStream templateFileInputStream)throws IOException{POIFSFileSystem pioFSFileSystem = new POIFSFileSystem(templateFileInputStream);Workbook workbook = new HSSFWorkbook(pioFSFileSystem);int sheetSize = workbook.getNumberOfSheets();List<Student> studentList = new ArrayList<Student>();if(sheetSize>0){Student student = null;Sheet studentSheet = null;for(int i=0;i<sheetSize;i++){studentSheet = workbook.getSheetAt(i);if(ExcelParameter.EXCEL_HIDE_SHEET_NAME.equals(studentSheet.getSheetName())){continue;}student = ExcelParserUtil.getStudentBySheet(studentSheet);studentList.add(student);}}return studentList;}/** * 解析一个工作薄的信息 * @param studentSheet * @return */private static Student getStudentBySheet(Sheet studentSheet){Student student = new Student();for (int i = studentSheet.getFirstRowNum(); i <= studentSheet.getLastRowNum(); i++) {Row row = studentSheet.getRow(i);if(i==0){continue;}Cell cell = null;cell = row.getCell(0);student.setStudentNo(ExcelParserUtil.getCellValue(cell).trim());cell = row.getCell(1);student.setStudentName(ExcelParserUtil.getCellValue(cell).trim());cell = row.getCell(2);student.setStudentSex(ExcelParserUtil.getCellValue(cell).trim());cell = row.getCell(3);student.setStudentAge(ExcelParserUtil.getCellValue(cell).trim());cell = row.getCell(4);student.setBirthday(ExcelParserUtil.getCellValue(cell).trim());cell = row.getCell(5);student.setProvinceName(ExcelParserUtil.getCellValue(cell).trim());cell = row.getCell(6);student.setCityName(ExcelParserUtil.getCellValue(cell).trim());cell = row.getCell(7);student.setCollageName(ExcelParserUtil.getCellValue(cell).trim());cell = row.getCell(8);student.setMajorName(ExcelParserUtil.getCellValue(cell).trim());cell = row.getCell(9);student.setRemark(ExcelParserUtil.getCellValue(cell).trim());}return student;}/** * 获得单元格字符串值 * @param cell * @return */private static String getCellValue(Cell cell){switch (cell.getCellType()) {case Cell.CELL_TYPE_FORMULA:return cell.getCellFormula();case Cell.CELL_TYPE_NUMERIC:return ""+(long)cell.getNumericCellValue();case Cell.CELL_TYPE_STRING:return cell.getStringCellValue();case Cell.CELL_TYPE_BOOLEAN:return ""+cell.getBooleanCellValue();case Cell.CELL_TYPE_BLANK:return "";default:return "";}}}

package com.fruitking.excel;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.util.List;public class ExcelReaMain {/** * @param args */public static void main(String[] args) throws IOException {InputStream inp = new FileInputStream("student_template.xls");print(ExcelParserUtil.parseTemplateFile(inp));inp.close();}/** * 控制台打印一个工作台解析信息 * @param studentList */public static void print(List<Student> studentList){if(studentList!=null&&!studentList.isEmpty()){for(Student student:studentList){System.out.print(student.getStudentNo());System.out.print(" ----- ");System.out.print(student.getStudentName());System.out.print(" ----- ");System.out.print(student.getStudentSex());System.out.print(" ----- ");System.out.print(student.getStudentAge());System.out.print(" ----- ");System.out.print(student.getBirthday());System.out.print(" ----- ");System.out.print(student.getProvinceName());System.out.print(" ----- ");System.out.print(student.getCityName());System.out.print(" ----- ");System.out.print(student.getCollageName());System.out.print(" ----- ");System.out.print(student.getMajorName());System.out.print(" ----- ");System.out.println(student.getRemark());}}}}

package com.fruitking.excel;public class Student {private String studentNo;//学生学号private String studentName;//学生姓名 private String studentSex;//性别 private String provinceName;//出生省份 private String cityName;//出生城市 private String collageName;//所在学院 private String majorName;//专业名称 private String studentAge ;//年龄 private String birthday;//出生日期 private String remark;//备注信息 public String getStudentNo() {return studentNo;}public void setStudentNo(String studentNo) {this.studentNo = studentNo;}public String getStudentName() {return studentName;}public void setStudentName(String studentName) {this.studentName = studentName;}public String getStudentSex() {return studentSex;}public void setStudentSex(String studentSex) {this.studentSex = studentSex;}public String getProvinceName() {return provinceName;}public void setProvinceName(String provinceName) {this.provinceName = provinceName;}public String getCityName() {return cityName;}public void setCityName(String cityName) {this.cityName = cityName;}public String getCollageName() {return collageName;}public void setCollageName(String collageName) {this.collageName = collageName;}public String getMajorName() {return majorName;}public void setMajorName(String majorName) {this.majorName = majorName;}public String getStudentAge() {return studentAge;}public void setStudentAge(String studentAge) {this.studentAge = studentAge;}public String getBirthday() {return birthday;}public void setBirthday(String birthday) {this.birthday = birthday;}public String getRemark() {return remark;}public void setRemark(String remark) {this.remark = remark;}}