简介
- 一种用java代码读写文档（Excel、word、PPT）的方式
读写Excel方面
- 针对不同的Excel版本，要采用不同的工具类。
- HSSFWorkbook：操作Excel2003以前的版本，扩展名为.xls
- XSSFWorkbook：操作2007的版本，扩展名是.xlsx
- 如果版本匹配错了，会抛出异常：
- org.apache.poi.openxml4j.exceptions.InvalidOperationException
- org.apache.poi.poifs.filesystem.OfficeXmlFileException
- 从POI 3.8版本开始，提供了一种基于XSSF的低内存占用的API—-SXSSF
- 当数据量超出65536条后，在使用HSSFWorkbook或XSSFWorkbook，程序会报OutOfMemoryError：Javaheap space;内存溢出错误。这时应该用SXSSFworkbook。
参考文章
读取各种文档的类
- HSSF–读写.xls格式文档
- XSSF–读写.xlsx格式文档
- HWPF–读写.doc格式文档
- HSLF–读写PPT
工作簿概念
- 打个比方，工作簿就像一本书或者一本账册，工作表就像其中的一张或一篇。工作簿中包含一个或多个工作表，工作表依托于工作簿存在。
- 新建Excel文档（.xls)其实就是新建工作簿,然后在里面可以有sheet1、sheet2等等。
代码实例
一个较为复杂的例子
public void excel() throws Exception{ String excelName = year+"泛珠赛全国总决赛终评评委打分审核表("+model+")"; //工作簿名称 Workbook wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); //设置各种单元格对应的样式 for (int i = 0; i < pingweiList.size(); i++) { //创建Excel表格 CreatePingweiScore(wb, styles, headers,pingweiList.get(i), model,year); } // 写出 String file = excelName+".xls"; //文件命名 FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); } /* * 设置各种单元格对应的样式 * （基本上样式都是应用于单元格的，没见过应用在行上的顶多设置下行高这样） */ private static Map<String, CellStyle> createStyles(Workbook wb){ Map<String, CellStyle> styles = new HashMap<>(); CellStyle style; /*设置标题格式*/ Font titleFont = wb.createFont(); //字号 titleFont.setFontHeightInPoints((short)18); //加粗 titleFont.setBold(true); //设置单元格样式 style = wb.createCellStyle(); //水平居中 style.setAlignment(HorizontalAlignment.CENTER); //垂直居中 style.setVerticalAlignment(VerticalAlignment.CENTER); //将字体样式加入到样式当中 style.setFont(titleFont); //将样式应用到标题当中 styles.put("title", style); /*设置表头格式*/ Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short)11); monthFont.setBold(true); //字体颜色 monthFont.setColor(IndexedColors.BLACK.getIndex()); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setFont(monthFont); //自动换行 style.setWrapText(true); //应用到表头 styles.put("header", style); /*设置单元格*/ CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setWrapText(true); //右边框 cellStyle.setBorderRight(BorderStyle.THIN); //右边框颜色 cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); //应用到单元格 styles.put("cell", cellStyle); return styles;//styles：应用单元-单元属性的Map集合 } /* * 设置Excel表格 */ private int CreatePingweiScore(Workbook wb, Map<String, CellStyle> styles, String[] headers,String pid, String model,int year){ //所有作品的分 List<PingweiScore> pingweiScoreList = pingweiScoreService.selectByPidAndModelAndYear(pid,model,year); String titleName = year+"泛珠赛全国总决赛终评评委打分审核表("+model+")"; String pingweiName = "评委"+pid; //打印设置 Sheet sheet = wb.createSheet(pingweiName); //pingweiName为表名 PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //true表示页面方向为横向，false为纵向 sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue(titleName); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$J$1")); //从A1到J1单元格合并；可以直接CellRangeAddress（起始行，终止行，起始列，终止列） //Second row Row secondRow = sheet.createRow(1); secondRow.setHeightInPoints(40); //行高 Cell secondCell = secondRow.createCell(0); secondCell.setCellValue("评委编号："+pid+" 评委签名："); secondCell.setCellStyle(styles.get("header")); //设置单元格样式 sheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$J$2")); //合并单元格，前面设置的样式都应用到了这些单元格上了 //header row Row headerRow = sheet.createRow(2); headerRow.setHeightInPoints(40); Cell headerCell; for (int i = 0; i < headers.length; i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(headers[i]); headerCell.setCellStyle(styles.get("header")); } //评委分数 int rownum = 3; //前三列为固定值先不管 for (int i = 0; i < pingweiScoreList.size(); i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < headers.length; j++) { Cell cell = row.createCell(j); //逐项分数写入 switch (j){ case 0: cell.setCellValue(pingweiScoreList.get(i).getProId());break; case 1: cell.setCellValue(pingweiScoreList.get(i).getBianHao());break; case 2: cell.setCellValue(pingweiScoreList.get(i).getProName());break; case 3: cell.setCellValue(pingweiScoreList.get(i).getOption1());break; case 4: cell.setCellValue(pingweiScoreList.get(i).getOption2());break; case 5: cell.setCellValue(pingweiScoreList.get(i).getOption3());break; case 6: cell.setCellValue(pingweiScoreList.get(i).getOption4());break; case 7: cell.setCellValue(pingweiScoreList.get(i).getOption5());break; case 8: cell.setCellValue(pingweiScoreList.get(i).getOption6());break; case 9: cell.setCellValue(pingweiScoreList.get(i).getTotalScore());break; } } } //所有行第1~3列的宽度，分别用来填写序号、作品编号和作品名称 sheet.setColumnWidth(0, 10*256); //10 characters wide sheet.setColumnWidth(1, 10*256); //10 characters wide sheet.setColumnWidth(2, 30*256); //30 characters wide //后面列的宽度 for (int i = 3; i < headers.length; i++) { sheet.setColumnWidth(i, 9*256); //9 characters wide } return 0;}
其中write(Outputstream)为HSSFWork唯一写出方法
一些样式设置参考
解析Excel保存到数据库
public class ResolveExcelServiceImpl implements ResolveExcelService { private static final String SUFFIX_2003=".xls"; private static final String SUFFIX_2007=".xlsx"; @Override public List<Works> resolveExcel(MultipartFile file) throws BusinessException { List<Works> list=new ArrayList<Works>(); if(file==null){ throw new BusinessException(ReturnCode.CODE_FAIL,"文件不存在！"); } //获取文件名 String originalFilename=file.getOriginalFilename(); Workbook workbook=null; //判断格式 try{ if(originalFilename.endsWith(SUFFIX_2003)){ /* getInputStream()返回InputStream 经测试，全部文件都返回ByteArrayInputStream */ workbook=new HSSFWorkbook(file.getInputStream()); }else if(originalFilename.endsWith(SUFFIX_2007)){ //不知道为什么这种格式会抛异常 workbook=new XSSFWorkbook(file.getInputStream()); } }catch (Exception e){ e.printStackTrace(); throw new BusinessException(ReturnCode.CODE_FAIL,"格式错误！"); } if(null==workbook){ throw new BusinessException(ReturnCode.CODE_FAIL,"格式错误！"); }else{ //获取所有工作表的数量 int numOfSheet=workbook.getNumberOfSheets(); //遍历这些表 for(int i=0;i<numOfSheet;i++){ //获取一个sheet Sheet sheet=workbook.getSheetAt(i); int lastRowNum=sheet.getLastRowNum(); //从第三行开始，第1行一般是标题，第二行是表头 for(int j=2;j<=lastRowNum;j++){ Row row=sheet.getRow(j); Works works=new Works(); //获取序号单元格 if(row.getCell(0)!=null){ row.getCell(0).setCellType(Cell.CELL_TYPE_STRING); //获取单元格内容（将内容当做字符串处理） String code=row.getCell(0).getStringCellValue(); //正则对比 boolean matche=Pattern.matches("^\\d{1,2}$",code); if(!matche){ throw new BusinessException(ReturnCode.CODE_FAIL,"序号错误！"); } works.setCode(code); } //编号 if(row.getCell(1)!=null){ row.getCell(1).setCellType(Cell.CELL_TYPE_STRING); String bianHao=row.getCell(1).getStringCellValue(); //校验编号长度 boolean matche=Pattern.matches("^\\d{6}$",bianHao); if(!matche){ throw new BusinessException(ReturnCode.CODE_FAIL,"序号错误！"); } works.setBianHao(bianHao); } //名称 if(row.getCell(2)!=null){ row.getCell(2).setCellType(Cell.CELL_TYPE_STRING); String name=row.getCell(2).getStringCellValue(); //正则对比 boolean matche=Pattern.matches("^[\\u4E00-\\u9FA5\\w\\-\\——]{1,}$",name); if(!matche){ throw new BusinessException(ReturnCode.CODE_FAIL,"作品名称错误！"); } works.setName(name); } //分赛区 if(row.getCell(3)!=null){ row.getCell(3).setCellType(Cell.CELL_TYPE_STRING); String district=row.getCell(3).getStringCellValue(); //正则对比 boolean matche=Pattern.matches("^[\\u4E00-\\u9FA5]{1,}$",district); if(!matche){ throw new BusinessException(ReturnCode.CODE_FAIL,"分赛区名称错误！"); } works.setPartName(district); } //学校 if(row.getCell(4)!=null){ row.getCell(4).setCellType(Cell.CELL_TYPE_STRING); String school=row.getCell(4).getStringCellValue(); //正则对比 boolean matche=Pattern.matches("^[\\u4E00-\\u9FA5]{1,}$",school); if(!matche){ throw new BusinessException(ReturnCode.CODE_FAIL,"学校名称错误！"); } works.setSchool(school); } //指导老师 if(row.getCell(5)!=null){ row.getCell(5).setCellType(Cell.CELL_TYPE_STRING); String teachers=row.getCell(5).getStringCellValue(); //正则对比 boolean matche=Pattern.matches("^[\\u4E00-\\u9FA5\\s,，、]{1,}$",teachers); if(!matche){ throw new BusinessException(ReturnCode.CODE_FAIL,"老师姓名格式错误！"); } works.setTeachers(teachers); } //学生 if(row.getCell(6)!=null){ row.getCell(6).setCellType(Cell.CELL_TYPE_STRING); String students=row.getCell(6).getStringCellValue(); //正则对比 boolean matche=Pattern.matches("^[\\u4E00-\\u9FA5\\s,，、]{1,}$",students); if(!matche){ throw new BusinessException(ReturnCode.CODE_FAIL,"学生姓名格式错误！"); } works.setStudents(students); } //组别 if(row.getCell(7)!=null){ row.getCell(7).setCellType(Cell.CELL_TYPE_STRING); String model=row.getCell(7).getStringCellValue(); //正则对比 boolean matche=Pattern.matches("^[\\u4E00-\\u9FA5]{1,}$",model); if(!matche){ throw new BusinessException(ReturnCode.CODE_FAIL,"组别格式错误！"); } works.setModel(model); } list.add(works); } } } return list; }
