POI操作excel
java操作excel是很常见的，那么下面给出一些代码来操作excel表
首先是写
/** * 根据一个从数据库中查出的集合，把它写成excel * @param db */ public void writerExcel(List<BusinessExtrac> db,String filePath){ try{ String[] key = {"ID","商家","提现金额","提现时间","开户省份","开户城市","开户支行","开户人","银行卡号","提现状态"}; File file = new File(filePath); if(!file.exists()){ file.createNewFile(); } XSSFWorkbook book = new XSSFWorkbook(); XSSFSheet sheet = book.createSheet("商家提现"); XSSFCell cell = null; //锁定表头，这样表头就会固定在最上方，第一个参数是列，第二个参数是行，这里固定了两行 sheet.createFreezePane(0, 2); //设置第二列的宽度，也就是商家列的宽度 sheet.setColumnWidth(1, 8000); //设置第三列的宽度 sheet.setColumnWidth(2, 4000); //设置第四列的宽度，也就是提现时间 sheet.setColumnWidth(3, 8000); sheet.setColumnWidth(4, 3000); sheet.setColumnWidth(5, 3000); sheet.setColumnWidth(6, 3000); sheet.setColumnWidth(7, 3000); //设置第五列的宽度，也就是银行卡号 sheet.setColumnWidth(8, 7000); //审核状态 sheet.setColumnWidth(9, 3000); //设置第一列，把id列隐藏 sheet.setColumnHidden(0, true); //合并第一列的单元格 sheet.addMergedRegion(new CellRangeAddress(0,0,0,9)); XSSFRow title = sheet.createRow(0); //设置第一行的行高 title.setHeightInPoints(70); XSSFCell tc = title.createCell(0); tc.setCellStyle(this.getTitleStyle(book)); tc.setCellValue("绿色区域外的内容只作提示，请不要更改，绿色区域内容只允许填写两个值，请不要写其它值(1 为提现失败，2为提现成功，无论您填写什么值，都请确保财务部门正确处理)"); //标题栏 XSSFRow row = sheet.createRow(1); //填写key for(int i=0;i<key.length;i++){ cell = row.createCell(i); cell.setCellValue(key[i]); cell.setCellStyle(this.getKeyStyle(book)); } //填写value for(int i=0;i<db.size();i++){ row = sheet.createRow(i+2); BusinessExtrac extrac = db.get(i); //数据库id cell = row.createCell(0); cell.setCellStyle(this.getBorderStyle(book)); cell.setCellValue(extrac.getId()); //商家名字 cell = row.createCell(1); cell.setCellStyle(this.getBorderStyle(book)); cell.setCellValue(extrac.getBusinessName()); //提现金额 cell = row.createCell(2); cell.setCellStyle(this.getBorderStyle(book)); cell.setCellValue(extrac.getExtrac_money()); //提现时间 cell = row.createCell(3); cell.setCellStyle(this.getBorderStyle(book)); cell.setCellValue(DateUtils.formatDateByFormat(extrac.getExtrac_time(), "yyyy-MM-dd HH:mm:ss")); //开户省 cell = row.createCell(4); cell.setCellStyle(this.getBorderStyle(book)); cell.setCellValue(extrac.getBankProvince()); //开户城市 cell = row.createCell(5); cell.setCellStyle(this.getBorderStyle(book)); cell.setCellValue(extrac.getBankCity()); //开户支行 cell = row.createCell(6); cell.setCellStyle(this.getBorderStyle(book)); cell.setCellValue(extrac.getBankChildName()); //开户人 cell = row.createCell(7); cell.setCellStyle(this.getBorderStyle(book)); cell.setCellValue(extrac.getAccountUser()); //银行卡号 cell = row.createCell(8); cell.setCellStyle(this.getBorderStyle(book)); cell.setCellValue(EncryptionUtils.decrypt(extrac.getBank_card_no(), EncryptionUtils.backkey)); //审核状态 cell = row.createCell(9); cell.setCellStyle(this.getBorderStyle(book)); cell.setCellValue(extrac.getExtrac_status().toString()); cell.setCellStyle(this.getStatusStyle(book)); } book.write(new FileOutputStream(file)); book.close(); }catch(IOException e){ e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } } /** * 得到审核状态的默认样式 * @return */ public XSSFCellStyle getStatusStyle(XSSFWorkbook book){ XSSFCellStyle style = book.createCellStyle(); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); // style.setFillBackgroundColor((short)13); style.setFillForegroundColor(IndexedColors.GREEN.index); style.setBorderTop(XSSFCellStyle.BORDER_THIN); style.setBorderRight(XSSFCellStyle.BORDER_THIN); style.setBorderBottom(XSSFCellStyle.BORDER_THIN); style.setBorderLeft(XSSFCellStyle.BORDER_THIN); return style; } /** * 得到标题样式 * @param style * @return */ public XSSFCellStyle getTitleStyle(XSSFWorkbook book){ XSSFCellStyle style = book.createCellStyle(); style.setBorderTop(XSSFCellStyle.BORDER_THIN); style.setBorderRight(XSSFCellStyle.BORDER_THIN); style.setBorderBottom(XSSFCellStyle.BORDER_THIN); style.setBorderLeft(XSSFCellStyle.BORDER_THIN); //自动换行 style.setWrapText(true); //字体 XSSFFont font = book.createFont(); font.setFontHeightInPoints((short)16); font.setColor(IndexedColors.RED.index); style.setFont(font); return style; } /** * 得到key的样式 * @param book * @return */ public XSSFCellStyle getKeyStyle(XSSFWorkbook book){ XSSFCellStyle style = book.createCellStyle(); style.setBorderTop(XSSFCellStyle.BORDER_THIN); style.setBorderRight(XSSFCellStyle.BORDER_THIN); style.setBorderBottom(XSSFCellStyle.BORDER_THIN); style.setBorderLeft(XSSFCellStyle.BORDER_THIN); //字体 XSSFFont font = book.createFont(); font.setColor(IndexedColors.BLUE.index); font.setFontHeightInPoints((short)15); style.setFont(font); return style; } /** * 得到边框的样式 * @param book * @return */ public XSSFCellStyle getBorderStyle(XSSFWorkbook book){ XSSFCellStyle style = book.createCellStyle(); style.setBorderTop(XSSFCellStyle.BORDER_THIN); style.setBorderRight(XSSFCellStyle.BORDER_THIN); style.setBorderBottom(XSSFCellStyle.BORDER_THIN); style.setBorderLeft(XSSFCellStyle.BORDER_THIN); return style; }
再接下来是读:
public int uploadExcel(MultipartFile excel,String tempPath) throws IOException { //判断文件是否有上传 String fileName = excel.getOriginalFilename(); if(fileName == null || fileName.trim().equals("")){ return -1; } //把文件保存在临时目录，但是操作完之后要删除这个文件 UploadUtil.saveFileByPath(excel, tempPath); XSSFWorkbook book = new XSSFWorkbook(tempPath); XSSFSheet sheet = book.getSheetAt(0); //得到共有多少行 int rowNum = sheet.getLastRowNum(); List<BusinessExtrac> list = new ArrayList<BusinessExtrac>(); for(int i=2;i<=rowNum;i++){ BusinessExtrac extrac = new BusinessExtrac(); XSSFRow row = sheet.getRow(i); //id String is = cellStringUtil(row.getCell(0)).toString(); double log = Double.parseDouble(is); extrac.setId((int)log); //商家名称 String businessName = cellStringUtil(row.getCell(1)); extrac.setBusinessName(businessName); //提现金额 String money = cellStringUtil(row.getCell(2)); extrac.setExtrac_money(Double.parseDouble(money)); //提现时间 String date = cellStringUtil(row.getCell(3)); extrac.setExtrac_time(DateUtils.parseDate(date, "yyyy-MM-dd HH:mm:ss")); //开户省份 String province = cellStringUtil(row.getCell(4)); extrac.setBankProvince(province); //开户城市 String city = cellStringUtil(row.getCell(5)); extrac.setBankCity(city); //开户支行 String bankChild = cellStringUtil(row.getCell(6)); extrac.setBankChildName(bankChild); //开户人 String person = cellStringUtil(row.getCell(7)); extrac.setAccountUser(person); //银行卡号 String account = cellStringUtil(row.getCell(8)); extrac.setBank_card_no(account); //提现状态 int status = (int)Double.parseDouble(cellStringUtil(row.getCell(9)).trim()); //如果状态不为1 和 2，则报错 if(status != 1 && status != 2){ book.close(); return 10; } extrac.setExtrac_status(status); list.add(extrac); } book.close(); new File(tempPath).delete(); System.out.println("excel上传成功!"); System.out.println(list); //数据读取成功，所以需要把excel删除 //处理数据库中的提现成功，或者提现失败 for(BusinessExtrac xls : list){ //判断属于这条纪录的数据，在数据库中的状态，如果不是2，则返回错误 int status = extrac_Dao.getStatusById(xls.getId()); if(status!=2){ //上传过已经上传过的数据 return 11; }else{ //需要更改数据库中的记录，把状态改为上传成功，或者上传失败 int tus = xls.getExtrac_status() == 1 ? 4 : 3; if(xls.getExtrac_status() == 1){ //提现失败 //状态改为提现失败 extrac_Dao.setStatusById(xls.getId(),4); }else if(xls.getExtrac_status() == 2){ //操作成功，把状态改为提现成功 extrac_Dao.setStatusById(xls.getId(),3); Double extrac_money = extrac_Dao.getExtracMoneyById(xls.getId()); int businessId = extrac_Dao.getBusinessById(xls.getId()); extrac_Dao.updateBusinessExtrac(extrac_money,businessId); } } } return 1; }
