本文共 10962 字，大约阅读时间需要 36 分钟。
public class Test { /** 存储路径 */ private static String path = "E:/Student.xls"; /** 列表信息 */ private static List mList; /** 工作簿 */ private static HSSFWorkbook workbook; /** 格式化时间 */ static SimpleDateFormat dateFormat; static { mList = new ArrayList (); dateFormat = new SimpleDateFormat("yyyy-mm-dd"); try { Student user1 = new Student(1, "张三", 16, dateFormat.parse("1997-03-12")); Student user2 = new Student(2, "李四", 17, dateFormat.parse("1996-08-12")); Student user3 = new Student(3, "王五", 26, dateFormat.parse("1985-11-12")); mList.add(user1); mList.add(user2); mList.add(user3); } catch (ParseException e) { e.printStackTrace(); } } /** * 主函数 */ public static void main(String[] args) { // 创建一个工作簿 workbook = new HSSFWorkbook(); // 第二步, 在WebBook中添加一个sheet,对应Excel中的sheet HSSFSheet sheet = workbook.createSheet("学生表一"); // 第三步, 在sheet中添加表头第0行 HSSFRow row = sheet.createRow(0); // 第四步, 创建单元格, 并设置值表头, 设置表头居中 HSSFCellStyle style = workbook.createCellStyle(); // 创建一个居中显示的头 style.setAlignment(HorizontalAlignment.CENTER); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 创建第一行 HSSFCell cell = row.createCell(0); cell.setCellValue("学号"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("姓名"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("年龄"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("生日"); cell.setCellStyle(style); String[] textlist = { "16", "17", "26", "20", "23" }; // 创建下拉列表 sheet = setHSSFValidation(sheet, textlist, 0, 500, 2, 2); // 写入实体数据 for (int i = 0; i < mList.size(); i++) { row = sheet.createRow(i + 1); Student student = mList.get(i); // 第四步,创建单元格并设置值 row.createCell(0).setCellValue(student.getId()); row.createCell(1).setCellValue(student.getName()); row.createCell(2).setCellValue(student.getAge()); row.createCell(3).setCellValue(dateFormat.format(student.getBirth())); } try { FileOutputStream fos = new FileOutputStream(path); workbook.write(fos); fos.close(); } catch (Exception e) { e.printStackTrace(); } } /** * 设置某些列的值只能输入预制的数据,显示下拉框. * @param sheet 要设置的sheet. * @param textlist 下拉框显示的内容 * @param firstRow 开始行 * @param endRow 结束行 * @param firstCol 开始列 * @param endCol 结束列 * @return 设置好的sheet. */ private static HSSFSheet setHSSFValidation(HSSFSheet sheet, String[] textlist, int firstRow, int lastRow, int firstCol, int lastCol) { // 加载下拉列表内容 DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist); // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列 CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol); // 数据有效性对象 HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint); sheet.addValidationData(dataValidation); return sheet; } /** * 设置单元格上提示 * @param sheet 要设置的sheet. * @param promptTitle 标题 * @param promptContent 内容 * @param firstRow 开始行 * @param endRow 结束行 * @param firstCol 开始列 * @param endCol 结束列 * @return 设置好的sheet. */ public static HSSFSheet setHSSFPrompt(HSSFSheet sheet, String promptTitle, String promptContent, int firstRow, int endRow ,int firstCol,int endCol) { // 构造constraint对象 DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("BB1"); // 四个参数分别是：起始行、终止行、起始列、终止列 CellRangeAddressList regions = new CellRangeAddressList(firstRow,endRow,firstCol, endCol); // 数据有效性对象 HSSFDataValidation data_validation_view = new HSSFDataValidation(regions,constraint); data_validation_view.createPromptBox(promptTitle, promptContent); sheet.addValidationData(data_validation_view); return sheet; }} /** * 读取Excel表格的功能类 */ public class ExcelReader { private POIFSFileSystem fs; private HSSFWorkbook wb; private HSSFSheet sheet; private HSSFRow row; /** * 读取Excel表格表头的内容 * @param InputStream * @return String 表头内容的数组 */ public String[] readExcelTitle(InputStream is) { try { fs = new POIFSFileSystem(is); wb = new HSSFWorkbook(fs); } catch (IOException e) { e.printStackTrace(); } sheet = wb.getSheetAt(0); row = sheet.getRow(0); // 标题总列数 int colNum = row.getPhysicalNumberOfCells(); System.out.println("colNum:" + colNum); String[] title = new String[colNum]; for (int i = 0; i < colNum; i++) { //title[i] = getStringCellValue(row.getCell((short) i)); title[i] = getCellFormatValue(row.getCell((short) i)); } return title; } /** * 读取Excel数据内容 * @param InputStream * @return Map 包含单元格数据内容的Map对象 */ public Map readExcelContent(InputStream is) { Map content = new HashMap (); String str = ""; try { fs = new POIFSFileSystem(is); wb = new HSSFWorkbook(fs); } catch (IOException e) { e.printStackTrace(); } sheet = wb.getSheetAt(0); // 得到总行数 int rowNum = sheet.getLastRowNum(); row = sheet.getRow(0); int colNum = row.getPhysicalNumberOfCells(); // 正文内容应该从第二行开始,第一行为表头的标题 for (int i = 1; i <= rowNum; i++) { row = sheet.getRow(i); int j = 0; while (j < colNum) { // 每个单元格的数据内容用"-"分割开，以后需要时用String类的replace()方法还原数据 // 也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean // str += getStringCellValue(row.getCell((short) j)).trim() + // "-"; str += getCellFormatValue(row.getCell((short) j)).trim() + " "; j++; } content.put(i, str); str = ""; } return content; } /** * 获取单元格数据内容为字符串类型的数据 * * @param cell Excel单元格 * @return String 单元格数据内容 */ private String getStringCellValue(HSSFCell cell) { if (null == cell) { return ""; } String strCell = ""; switch (cell.getCellTypeEnum()) { case STRING: strCell = cell.getStringCellValue(); break; case NUMERIC: strCell = String.valueOf(cell.getNumericCellValue()); break; case BOOLEAN: strCell = String.valueOf(cell.getBooleanCellValue()); break; case BLANK: strCell = ""; break; default: strCell = ""; break; } if (strCell.equals("") || strCell == null) { return ""; } return strCell; } /** * 获取单元格数据内容为日期类型的数据 * * @param cell * Excel单元格 * @return String 单元格数据内容 */ private String getDateCellValue(HSSFCell cell) { String result = ""; try { CellType cellType = cell.getCellTypeEnum(); if (cellType == CellType.NUMERIC) { Date date = cell.getDateCellValue(); result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1) + "-" + date.getDate(); } else if (cellType == CellType.STRING) { String date = getStringCellValue(cell); result = date.replaceAll("[年月]", "-").replace("日", "").trim(); } else if (cellType == CellType.BLANK) { result = ""; } } catch (Exception e) { System.out.println("日期格式不正确!"); e.printStackTrace(); } return result; } /** * 根据HSSFCell类型设置数据 * @param cell * @return */ private String getCellFormatValue(HSSFCell cell) { String cellvalue = ""; if (cell != null) { // 判断当前Cell的Type switch (cell.getCellTypeEnum()) { // 如果当前Cell的Type为NUMERIC case NUMERIC: case FORMULA: { // 判断当前的cell是否为Date if (HSSFDateUtil.isCellDateFormatted(cell)) { // 如果是Date类型则，转化为Data格式 //方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00 //cellvalue = cell.getDateCellValue().toLocaleString(); //方法2：这样子的data格式是不带带时分秒的：2011-10-12 Date date = cell.getDateCellValue(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); cellvalue = sdf.format(date); } // 如果是纯数字 else { // 取得当前Cell的数值 cellvalue = String.valueOf(cell.getNumericCellValue()); } break; } // 如果当前Cell的Type为STRIN case STRING: // 取得当前的Cell字符串 cellvalue = cell.getRichStringCellValue().getString(); break; // 默认的Cell值 default: cellvalue = " "; } } else { cellvalue = ""; } return cellvalue; } /** 存储路径 */ private static String path = "E:/Student.xls"; public static void main(String[] args) { try { // 对读取Excel表格标题测试 InputStream is = new FileInputStream(path); ExcelReader excelReader = new ExcelReader(); String[] title = excelReader.readExcelTitle(is); System.out.println("获得Excel表格的标题:"); for (String s : title) { System.out.print(s + " "); } System.out.println(""); // 对读取Excel表格内容测试 InputStream is2 = new FileInputStream(path); Map map = excelReader.readExcelContent(is2); System.out.println("获得Excel表格的内容:"); for (int i = 1; i <= map.size(); i++) { System.out.println(map.get(i)); } } catch (FileNotFoundException e) { System.out.println("未找到指定路径的文件!"); e.printStackTrace(); } } } 转载地址：http://iwwao.baihongyu.com/