@Override public String getPriceDate(String fileName) { try { HSSFSheet sheet = getSheet(fileName, 0); String marker = "Дата"; int numOfRows = sheet.getPhysicalNumberOfRows() > 10 ? 10 : sheet.getPhysicalNumberOfRows(); String dirtyDate = null; for (int i = priceConfig.getRowToStart(Brand.STELS); i < numOfRows; i++) { Row row = sheet.getRow(i); if (row.getPhysicalNumberOfCells() > 1) { Cell cell = row.getCell(0); if (cell.getCellType() == Cell.CELL_TYPE_STRING) { String stringCellValue = cell.getStringCellValue(); if (stringCellValue.contains(marker)) { dirtyDate = stringCellValue; break; } } } } if (dirtyDate != null) { return dirtyDate.trim(); } } catch (Exception ex) { LOGGER.error("FAIL to get price date - return today"); return new Date().toString(); } return new Date().toString(); }
/** * POI:解析Excel文件中的数据并把每行数据封装成一个实体 * * @param fis 文件输入流 * @return List<EmployeeInfo> Excel中数据封装实体的集合 */ public static List<String> importControlsByPoi(InputStream fis) { // 这里是解析出来的Excel的数据存放的List集合 List<String> controls = new ArrayList<String>(); try { // 创建Excel工作薄 HSSFWorkbook hwb = new HSSFWorkbook(fis); // 得到第一个工作表 HSSFSheet sheet = hwb.getSheetAt(0); HSSFRow row = null; // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数 for (int i = 0; i < hwb.getNumberOfSheets(); i++) { sheet = hwb.getSheetAt(i); // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) { row = sheet.getRow(j); // 此方法调用getCellValue(HSSFCell cell)对解析出来的数据进行判断,并做相应的处理 if (ImportExcelByPoi.getCellValue(row.getCell(0)) != null && !"".equals(ImportExcelByPoi.getCellValue(row.getCell(0)))) { controls.add(String.valueOf(ImportExcelByPoi.getCellValue(row.getCell(0)))); } } } } catch (IOException e) { e.printStackTrace(); } return controls; }
/** * 读取 Excel文件内容 * * @param excel_name * @return * @throws Exception */ public static List<List<Object>> readExcelByInputStream(InputStream inputstream) throws Exception { // 结果集 List<List<Object>> list = new ArrayList<List<Object>>(); HSSFWorkbook hssfworkbook = new HSSFWorkbook(inputstream); // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数 HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0); // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 // //System.out.println("excel行数: "+hssfsheet.getPhysicalNumberOfRows()); for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) { HSSFRow hssfrow = hssfsheet.getRow(j); if (hssfrow != null) { int col = hssfrow.getPhysicalNumberOfCells(); // 单行数据 List<Object> arrayString = new ArrayList<Object>(); for (int i = 0; i < col; i++) { HSSFCell cell = hssfrow.getCell(i); if (cell == null) { arrayString.add(""); } else if (cell.getCellType() == 0) { arrayString.add(new Double(cell.getNumericCellValue()).toString()); } else { // 如果EXCEL表格中的数据类型为字符串型 arrayString.add(cell.getStringCellValue().trim()); } } list.add(arrayString); } } return list; }
/** * 判断报表是否可以输出 * * @param sheet -- 报表对象 * @return */ public static boolean isAllowOut(HSSFSheet sheet) { boolean ret = true; if (sheet.getPhysicalNumberOfRows() > 50000) { _log.showWarn("报表输出行数超出了最大行数:50000!"); ret = false; } return ret; }
public void mapChampsdeExcel() { openFichierOrdreDB2(); msg(nomTableDb2 + " mapChampsdeExcel dans la table memoire"); // $NON-NLS-1$ if (wb != null) { HSSFSheet sheet = wb.getSheet(nomTableDb2); // HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell cell; int rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); int cols = xlsNombreDeColonne(sheet, rows); Excel2Plat = createArrayList(cols); Plat2Excel = createArrayList(cols); rowEnTete = -1; DB2debutOrdreDB2(); for (int r = 0; r < rows; r++) { row = sheet.getRow(r); if (row != null) { if (rowEnTete == -1 && (r == 0 || r == 1)) { xlsRechercheEntete(row, cols, r); } else { if (rowEnTete >= 0) { msgProg( nomTableDb2 + " (" + r + ") rows d fichier excel lu "); //$NON-NLS-1$ //$NON-NLS-2$ for (int c = 0; c < Excel2Plat.size(); c++) { if (Excel2Plat.get(c) > -1) { cell = row.getCell(c); if (cell != null) { xlsPopulateValeur(cell, c); } } } ecrireLigne2Plat(); DB2addOrdreDB2(); for (int i = 0; i < nbChamps; i++) { champs.get(i).valeurduchamp = ""; } } } } } DB2ecrireOrdreDB2(); } DB2fermetureOrdreDB2(); }
public void executeJob(Connection con) throws Exception { // input ���� String fileName = "./기초자료/기초자료.xls"; String sheetName = "GHOST_TABLES"; FileInputStream fis = null; HSSFWorkbook wb = null; HSSFSheet sheet = null; // output ���� String deleteSQL = "delete from ghost_tables"; String insertSQL = "insert into ghost_tables values(?,?,?)"; PreparedStatement pstmt = null; Statement stmt = null; try { // Input �ڷ� �غ� fis = new FileInputStream(fileName); wb = new HSSFWorkbook(fis); sheet = wb.getSheet(sheetName); // �����ڷ� All ���� stmt = con.createStatement(); stmt.execute(deleteSQL); // Insert�� SQL Statement ���� pstmt = con.prepareStatement(insertSQL); HSSFRow row = null; Bean bean = null; for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); readCount++; if (row == null) continue; bean = getBean(row); pstmt.setString(1, bean.tableName); pstmt.setString(2, bean.tableDesc); pstmt.setString(3, bean.shortName); pstmt.executeUpdate(); writeCount++; System.out.printf("[%d][%s][%s][%s]\n", i, bean.tableName, bean.tableDesc, bean.shortName); } } finally { if (wb != null) wb.close(); if (fis != null) fis.close(); JDBCUtil.close(stmt); JDBCUtil.close(pstmt); } }
static StringBuilder readSheet(HSSFSheet sheet) { StringBuilder json_sheet = new StringBuilder(1024 * 100); int row_length = sheet.getPhysicalNumberOfRows(); Iterator<Row> rows = sheet.rowIterator(); HSSFRow row; if (rows.hasNext()) row = (HSSFRow) rows.next(); else return null; int col_length = row.getPhysicalNumberOfCells(); Iterator<Cell> cells = row.cellIterator(); HSSFCell cell; if (cells.hasNext()) cell = (HSSFCell) cells.next(); else return null; int first_row = cell.getRowIndex(); json_sheet.append("{'categories':["); a: for (int x = first_row; x < row_length; x++) { row = sheet.getRow(x); if (x > first_row) json_sheet.append("{"); for (int y = 0; y < col_length; y++) { cell = row.getCell(y); String value = "空白"; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: value = "" + cell.getNumericCellValue(); // Log.v("数字",value); break; case HSSFCell.CELL_TYPE_BOOLEAN: // Log.v("布尔",""+cell.getBooleanCellValue()); break a; case HSSFCell.CELL_TYPE_STRING: value = "'" + cell.getStringCellValue() + "'"; // Log.v("文本",value); break; } if (y == 0) { if (x == first_row) continue; else json_sheet.append("'name':" + value + ",'type':'line','data':["); } else json_sheet.append(value + ","); } json_sheet.replace(json_sheet.length() - 1, json_sheet.length(), "]"); if (x == first_row) json_sheet.append("}\n["); else json_sheet.append("},"); } json_sheet.replace(json_sheet.length() - 1, json_sheet.length(), "]"); // Log.v("JSON",json_sheet.toString()); return json_sheet; }
/** * 读取 Excel文件内容 * * @param excel_name * @return * @throws Exception */ public static List<String[]> readExcel(String excel_name) throws Exception { // 结果集 List<String[]> list = new ArrayList<String[]>(); HSSFWorkbook hssfworkbook = new HSSFWorkbook(new FileInputStream(excel_name)); // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数 HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0); // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) { HSSFRow hssfrow = hssfsheet.getRow(j); if (hssfrow != null) { int col = hssfrow.getPhysicalNumberOfCells(); // 单行数据 String[] arrayString = new String[col]; for (int i = 0; i < col; i++) { HSSFCell cell = hssfrow.getCell(i); if (cell == null) { arrayString[i] = ""; } else if (cell.getCellType() == 0) { // arrayString[i] = new Double(cell.getNumericCellValue()).toString(); if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) { if (HSSFDateUtil.isCellDateFormatted(cell)) { Date d = cell.getDateCellValue(); // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd"); DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); arrayString[i] = formater.format(d); } else { arrayString[i] = new BigDecimal(cell.getNumericCellValue()).longValue() + ""; } } } else { // 如果EXCEL表格中的数据类型为字符串型 arrayString[i] = cell.getStringCellValue().trim(); } } list.add(arrayString); } } return list; }
public String saveRoomImp(HSSFSheet sheet) throws Exception { String result = ""; try { int rowCount = sheet.getPhysicalNumberOfRows(); if (rowCount > 0) { int savenum = 0; for (int i = 1; i < rowCount; i++) { HSSFRow row = sheet.getRow(i); try { String schoolArea = ""; // 校区 String build = ""; // 楼宇 String roomName = ""; // 教室 schoolArea = row.getCell((short) 0).getStringCellValue().trim(); // 校区 build = row.getCell((short) 1).getStringCellValue().trim(); // 楼宇 roomName = row.getCell((short) 2).getStringCellValue().trim(); // 教室 if (findOneRoom(schoolArea, build, roomName) != null) { result = result + " 第" + i + "行教室已存在,未导入。<br>"; continue; } else { TArea area = areaService.findAreaByName(schoolArea); TRoom room = new TRoom(); room.setSchoolArea(area); room.setBuild(build); room.setRoomName(roomName); // room.setStatus(status); roomDAO.save(room); } savenum++; } catch (Exception e) { result = result + " 第" + i + "行,数据导入失败。<br>"; } } result = result + " 成功导入" + savenum + "行。<br>"; } else { result = result + " 没有找到有效的记录。<br>"; } } catch (Exception e) { result = result + "导入出现异常中断。<br>"; } return result; }
public String ifRoomCanImp(HSSFSheet sheet) throws Exception { String result = ""; try { int rowCount = sheet.getPhysicalNumberOfRows(); if (rowCount > 0) { for (int i = 1; i < rowCount; i++) { HSSFRow row = sheet.getRow(i); try { String schoolArea = ""; // 校区 String build = ""; // 楼宇 String roomName = ""; // 教室 schoolArea = row.getCell((short) 0).getStringCellValue().trim(); // 校区 build = row.getCell((short) 1).getStringCellValue().trim(); // 楼宇 roomName = row.getCell((short) 2).getStringCellValue().trim(); // 教室 TArea area = areaService.findAreaByName(schoolArea); if (area == null) { result = result + "第" + i + "行校区名称错误,请核对校区名称。<br>"; break; } } catch (NumberFormatException ne) { result = result + "第" + i + "行数字数据非文本类型,修改文本类型后再导入。<br> "; } catch (NullPointerException n) { result = result + "第" + i + "行有空数据,请填充后再导入。<br>"; } } } else { result = "没有找到有效的记录。<br>"; } } catch (Exception e) { result = result + "Excel文件检查异常,无法导入。<br>"; } if (!"".equals(result)) { result = result + "导入失败。"; } return result; }
public void batchUploadHolHoldays() throws IOException { if (file != null && file.length() > 0) { HSSFRow row; // HSSFCell cell; InputStream is = new FileInputStream(file); HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); // 获得excel中的第一张表 HSSFSheet sheet = hssfWorkbook.getSheetAt(0); List<HolHoliday> saveList = new ArrayList<HolHoliday>(); String yearError = ""; String repeatError = ""; String dayError = ""; String loginNameError = ""; String deptError = ""; String operatorId = servletRequest.getParameter("userId"); for (int i = sheet.getFirstRowNum() + 1; i < sheet.getPhysicalNumberOfRows(); i++) { HolHoliday holHoliday = new HolHoliday(); row = sheet.getRow(i); String year = getCellData(row, 0); // 年度 String loginName = getCellData(row, 1); // 工号 String dayCounts = getCellData(row, 2); // 天数 String remark = getCellData(row, 3); // 备注 String reg = "[0-9]{4}"; Pattern p = Pattern.compile(reg); Matcher m = p.matcher(year); boolean yearFlag = m.matches(); if (!yearFlag) { yearError += (i + 1) + ","; continue; } List<CsUser> userList = holHolidayService.findUsersByLoginName(loginName); if (userList == null || userList.size() < 1) { loginNameError += (i + 1) + ","; continue; } CsUser csUser = userList.get(0); List<HolHoliday> list = holHolidayService.findByYearAndHolPersonId(year, csUser.getId() + ""); if (list != null && list.size() > 0) { repeatError += (i + 1) + ","; continue; } // holHoliday.setHolName(csUser.getName()); // holHoliday.setHolLoginName(csUser.getLoginName()); // holHoliday.setDeptName(csUser.getDept()); holHoliday.setHolId(csUser.getId() + ""); // holHoliday.setHolId(loginName); if (!StringUtils.isNumeric(dayCounts)) { dayCounts += (i + 1) + ","; continue; } holHoliday.setHolYear(year); holHoliday.setHolDays(Long.valueOf(dayCounts)); holHoliday.setRemark(remark); HolHoliday last = this.holHolidayService.findLastHolidaysSetByholPersonId(loginName); // 同工号,上一次的数据 if (last != null) { holHoliday.setHolDaysLeft(last.getHolDaysLeft() + holHoliday.getHolDays()); holHoliday.setHolDaysWait(last.getHolDaysWait()); } else { holHoliday.setHolDaysLeft(holHoliday.getHolDays()); holHoliday.setHolDaysWait(0l); } holHoliday.setRemoved(0l); if (StringUtils.isNotEmpty(operatorId)) { holHoliday.setOperator(Long.valueOf(operatorId)); } holHoliday.setOperateTime(sdf.format(new Date())); saveList.add(holHoliday); } servletResponse.setCharacterEncoding("utf-8"); // 务必,防止返回文件名是乱码 servletResponse.setContentType("ajax"); String msg = ""; if (StringUtils.isEmpty(yearError) && StringUtils.isEmpty(dayError) && StringUtils.isEmpty(repeatError) && StringUtils.isEmpty(loginNameError)) { try { holHolidayService.saveAll(saveList); msg = "上传成功!共有" + saveList.size() + "条数据入库!"; servletResponse.getWriter().write("{\"message\":\"success\",\"info\":\"" + msg + "\"}"); } catch (Exception e) { e.printStackTrace(); } } else { String error1 = "", error2 = "", error3 = "", error4 = ""; if (StringUtils.isNotEmpty(yearError)) { error1 = "第" + yearError.substring(0, yearError.length() - 1) + "行,年份格式错误!"; } if (StringUtils.isNotEmpty(loginNameError)) { error4 = "第" + loginNameError.substring(0, loginNameError.length() - 1) + "行,该用户不存在!"; } if (StringUtils.isNotEmpty(repeatError)) { error2 = "第" + repeatError.substring(0, repeatError.length() - 1) + "行,数据重复!该用户在该年份下已设置过公休!"; } if (StringUtils.isNotEmpty(dayError)) { error3 = "第" + dayError.substring(0, dayError.length() - 1) + "行,公休天数格式错误!"; } servletResponse .getWriter() .write( "{\"message\":\"error\",\"error1\":\"" + error1 + "\",\"error2\":\"" + error2 + "\",\"error3\":\"" + error3 + "\",\"error4\":\"" + error4 + "\"}"); } } }
public List<T> importExcel(File file) { List<T> dist = new ArrayList<T>(); // 文件输入流 if (file != null) { try { // 得到目标目标类的所有的字段列表 Field[] fields = clazz.getDeclaredFields(); // 将所有标有Annotation的字段,也就是允许导入数据的字段,放入到一个map中 Map<String, Method> fieldMap = new HashMap<String, Method>(); // 循环读取所有字段 for (Field field : fields) { // 得到单个字段上的Annotation ExcelAnnotation excelAnnotation = field.getAnnotation(ExcelAnnotation.class); // 如果标识了Annotationd if (excelAnnotation != null) { String fieldName = field.getName(); // 构造设置了Annotation的字段的Setter方法 String setMethodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); // 构造调用的method Method setMethod = clazz.getMethod(setMethodName, new Class[] {field.getType()}); // 将这个method以Annotaion的名字为key来存入 fieldMap.put(excelAnnotation.exportName(), setMethod); } } FileInputStream fileInputStream = new FileInputStream(file); // POIFSFileSystem这是POIFS类库的主要类,它管理文件系统的整个生命周期。 POIFSFileSystem fs = new POIFSFileSystem(fileInputStream); HSSFWorkbook wb = new HSSFWorkbook(fs); // 将标题的文字内容放入到一个map中 Map<Integer, String> titleMap = new HashMap<Integer, String>(); // for (int k = 0; k < wb.getNumberOfSheets(); k++) { // 获取第一个Sheet HSSFSheet sheet = wb.getSheetAt(0); int rows = sheet.getPhysicalNumberOfRows(); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row != null) { int cells = 27; // 判断整行单元格是否全为空,true为全为空 if (!isAllCellNULL(cells, row)) { // 得到传入类的实例 T tObject = clazz.newInstance(); // 序号 Method method = clazz.getMethod("setSerialNumber", new Class[] {String.class}); method.invoke(tObject, ((row.getRowNum() + 1) + "")); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); if (cell != null) { // 获取列名 if (r == 0) { titleMap.put(cell.getColumnIndex(), cell.getStringCellValue()); } else { // 这里得到此列的对应的标题 String titleString = titleMap.get(c); if (titleString != null) { titleString = titleString.trim(); } // 如果这一列的标题和类中的某一列的Annotation相同,那么则调用此类的的set方法,进行设值 if (fieldMap.containsKey(titleString)) { Method setMethod = fieldMap.get(titleString); // 得到setter方法的参数 Type[] types = setMethod.getGenericParameterTypes(); // 只要一个参数 String xclass = String.valueOf(types[0]); // 判断参数类型 if ("class java.lang.String".equals(xclass)) { // 设置所有的cell为文本形式 cell.setCellType(HSSFCell.CELL_TYPE_STRING); if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { setMethod.invoke( tObject, String.valueOf(cell.getNumericCellValue()) .substring( 0, String.valueOf(cell.getNumericCellValue()).lastIndexOf("."))); } else { setMethod.invoke( tObject, cell.getStringCellValue().equals("") ? "" : cell.getStringCellValue()); } } else if ("class java.util.Date".equals(xclass)) { if (titleString.equals("入学时间") || titleString.equals("推送日期") || titleString.equals("日期")) { if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { setMethod.invoke(tObject, cell.getDateCellValue()); } else { if (cell.getStringCellValue() != null && !"".equals(cell.getStringCellValue())) { try { if (cell.getStringCellValue().trim().length() == 10) { setMethod.invoke( tObject, DateUtil.StringToDate( cell.getStringCellValue(), "yyyy-MM-dd")); } else if (cell.getStringCellValue().trim().length() == 8) { setMethod.invoke( tObject, DateUtil.StringToDate(cell.getStringCellValue(), "yyyyMMdd")); } else { } } catch (Exception e) { System.out.println(cell.getStringCellValue() + "日期错误"); setMethod.invoke(tObject, null); } } } } } else if ("class java.lang.Boolean".equals(xclass)) { Boolean boolName = true; if ("否".equals(cell.getStringCellValue())) { boolName = false; } setMethod.invoke(tObject, boolName); } else if ("class java.lang.Integer".equals(xclass) || "int".equals(xclass)) { // 设置所有的cell为文本形式 cell.setCellType(HSSFCell.CELL_TYPE_STRING); if (titleString.equals("民族")) { setMethod.invoke( tObject, ConstantsPeopleMap.getCode(cell.getStringCellValue().trim())); } if (titleString.equals("入学方式")) { setMethod.invoke( tObject, ConstantsEntranceWayMap.getCode(cell.getStringCellValue().trim())); } if (titleString.equals("政治面貌")) { setMethod.invoke( tObject, ConstantsPoliticalMap.getCode(cell.getStringCellValue().trim())); } if (titleString.equals("证件类型")) { setMethod.invoke( tObject, ConstantsIDMap.getCode(cell.getStringCellValue().trim())); } if (titleString.equals("学习中心")) { setMethod.invoke(tObject, cell.getStringCellValue().trim()); } if (titleString.equals("报读院校")) { setMethod.invoke(tObject, cell.getStringCellValue().trim()); } if (titleString.equals("招生批次")) { setMethod.invoke(tObject, cell.getStringCellValue().trim()); } if (titleString.equals("学籍批次")) { setMethod.invoke(tObject, cell.getStringCellValue().trim()); } if (titleString.equals("层次")) { setMethod.invoke(tObject, cell.getStringCellValue().trim()); } if (titleString.equals("专业")) { setMethod.invoke(tObject, cell.getStringCellValue().trim()); } if (titleString.equals("是否免试生")) { if ("否".equals(cell.getStringCellValue())) { setMethod.invoke(tObject, 0); } if ("是".equals(cell.getStringCellValue())) { setMethod.invoke(tObject, 1); } } if (titleString.equals("性别")) { if ("男".equals(cell.getStringCellValue())) { setMethod.invoke(tObject, Constants.SEX_MALE); } if ("女".equals(cell.getStringCellValue())) { setMethod.invoke(tObject, Constants.SEX_FAMALE); } } } else if ("class java.lang.Long".equals(xclass)) { setMethod.invoke(tObject, new Long(cell.getStringCellValue())); } else { // } } } } } if (r != 0) { dist.add(tObject); } } } } // } } catch (Exception e) { e.printStackTrace(); } } return dist; }
/** * 复制来源表格中第1个SHEET中的图片到目标表格中的第1个SHEET中。 * * @param destBook -- 目标表格 * @param srcBook -- 来源表格 */ private static void copySheetImage(HSSFWorkbook destBook, HSSFWorkbook srcBook) { // 来源表单 HSSFSheet srcSheet = srcBook.getSheetAt(0); // 目标表单 HSSFSheet destSheet = destBook.getSheetAt(0); // 需要偏移的行数 int endRowNum = destSheet.getPhysicalNumberOfRows(); // 取来源表单中的图片对象 List<HSSFPicture> lsSrcPicture = getAllPicture(srcSheet); _log.showDebug("----------source picture size:" + lsSrcPicture.size()); // 取所有子图形数据,如果是主从报表且明细数据占多页时,则会报空指针错误 List<HSSFPictureData> lsPicData = null; try { lsPicData = srcBook.getAllPictures(); } catch (Exception e) { // e.printStackTrace(); _log.showWarn("由于表单明细有多页,造成临时表的图片数据取不到,只能采用原表第1个图替代!"); // 原表中也没有图片,则不处理图片复制了 lsPicData = destBook.getAllPictures(); if (lsPicData == null || lsPicData.isEmpty()) return; // 只取原表中第1个图片信息 List<HSSFPictureData> destData = FactoryUtil.newList(); for (int i = 0, n = lsSrcPicture.size(); i < n; i++) { destData.add(lsPicData.get(0)); } lsPicData = destData; } if (lsPicData == null || lsPicData.isEmpty()) return; _log.showDebug("----------source data size:" + lsPicData.size()); // data数量可能大于图片数量 if (lsSrcPicture.size() > lsPicData.size()) { _log.showWarn("图片数量与数据数量不符!"); return; } // 取图片管理器 HSSFPatriarch destDraw = destSheet.getDrawingPatriarch(); if (destDraw == null) { destDraw = destSheet.createDrawingPatriarch(); } // 取原目标表单中的图片对象 List<HSSFPicture> lsDestPicture = getAllPicture(destSheet); int index = lsDestPicture.size(); for (int i = 0, n = lsSrcPicture.size(); i < n; i++) { // 取图片对象 HSSFPicture picture = lsSrcPicture.get(i); // 根据图片序号取图片数据 HSSFPictureData picdata = lsPicData.get(i); // 取图片字节信息 byte[] datas = picdata.getData(); // 取图片位置信息 HSSFClientAnchor anchor = (HSSFClientAnchor) picture.getAnchor(); // 添加行偏移值 anchor.setRow1(anchor.getRow1() + endRowNum); anchor.setRow2(anchor.getRow2() + endRowNum); // 插入新图片,返回的新图片序号无效 destBook.addPicture(datas, picdata.getFormat()); // 上面代码中新建图片的序号没有考虑原有图片数量,所以取原图片数量+1作为新图片的序号 index++; _log.showDebug("---------copy new image index=" + index); destDraw.createPicture(anchor, index); } }
/** * 添加临时表格内容 * * @param mainSheet -- 原表单内容 * @param subSheet -- 临时表单内容 * @return */ public static HSSFSheet appendSheet(HSSFSheet mainSheet, HSSFSheet subSheet) { if (mainSheet == null || subSheet == null) return null; // 判断报表是否允许输出 if (!isAllowOut(mainSheet)) return mainSheet; // 原报表的最后一行 int endRowNum = mainSheet.getPhysicalNumberOfRows(); HSSFRow sourow = null, descrow = null; HSSFCell sourcell = null, descell = null, orgcell = null; int i = 0, offsetcnt = 0; // 复制表格中的图片 copySheetImage(mainSheet.getWorkbook(), subSheet.getWorkbook()); // 设置以合并的单元格 CellRangeAddress range = null; int mergedNum = subSheet.getNumMergedRegions(); for (i = 0; i < mergedNum; i++) { range = subSheet.getMergedRegion(i); range.setFirstRow(range.getFirstRow() + endRowNum); range.setLastRow(range.getLastRow() + endRowNum); mainSheet.addMergedRegion(range); } range = null; // int k = 0; // 设置相关参数 mainSheet.setAlternativeExpression(subSheet.getAlternateExpression()); mainSheet.setAlternativeFormula(subSheet.getAlternateFormula()); mainSheet.setAutobreaks(subSheet.getAutobreaks()); mainSheet.setDialog(subSheet.getDialog()); mainSheet.setDisplayGuts(subSheet.getDisplayGuts()); mainSheet.setFitToPage(subSheet.getFitToPage()); for (java.util.Iterator<Row> iterow = subSheet.rowIterator(); iterow.hasNext(); ) { sourow = (HSSFRow) iterow.next(); offsetcnt = sourow.getRowNum() + endRowNum; descrow = mainSheet.createRow(offsetcnt); descrow.setHeight(sourow.getHeight()); descrow.setHeightInPoints(sourow.getHeightInPoints()); java.util.Iterator<Cell> iter = sourow.cellIterator(); while (iter.hasNext()) { sourcell = (HSSFCell) iter.next(); int column = sourcell.getColumnIndex(); descell = descrow.createCell(column); // 取模板中的单元格,与来源表单位置相同 int row = sourcell.getRowIndex(); orgcell = mainSheet.getRow(row).getCell(column); if (orgcell != null) { // 取模板中的类型赋值 descell.setCellType(orgcell.getCellType()); // 取模板中的样式赋值 descell.setCellStyle(orgcell.getCellStyle()); } else { _log.showWarn("module xls [{0}, {1}] cell is null!", row, column); } if (sourcell.getCellType() == HSSFCell.CELL_TYPE_STRING) descell.setCellValue(sourcell.getStringCellValue()); else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) descell.setCellValue(sourcell.getNumericCellValue()); else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_BLANK) ; } sourow = null; sourcell = null; descrow = null; orgcell = null; } return mainSheet; }
/** * 读取97-2003格式 * * @param filePath 文件路径 * @throws java.io.IOException */ @SuppressWarnings("rawtypes") public static List<Map> readExcel2003(String filePath) throws IOException { // 返回结果集 List<Map> valueList = new ArrayList<Map>(); FileInputStream fis = null; try { fis = new FileInputStream(filePath); HSSFWorkbook wookbook = new HSSFWorkbook(fis); // 创建对Excel工作簿文件的引用 HSSFSheet sheet = wookbook.getSheetAt(0); // 在Excel文档中,第一张工作表的缺省索引是0 int rows = sheet.getPhysicalNumberOfRows(); // 获取到Excel文件中的所有行数 Map<Integer, String> keys = new HashMap<Integer, String>(); int cells = 0; // 遍历行(第1行 表头) 准备Map里的key HSSFRow firstRow = sheet.getRow(0); if (firstRow != null) { // 获取到Excel文件中的所有的列 cells = firstRow.getPhysicalNumberOfCells(); // 遍历列 for (int j = 0; j < cells; j++) { // 获取到列的值 try { HSSFCell cell = firstRow.getCell(j); String cellValue = getCellValue(cell); keys.put(j, cellValue); } catch (Exception e) { e.printStackTrace(); } } } // 遍历行(从第二行开始) for (int i = 1; i < rows; i++) { // 读取左上端单元格(从第二行开始) HSSFRow row = sheet.getRow(i); // 行不为空 if (row != null) { // 准备当前行 所储存值的map Map<String, Object> val = new HashMap<String, Object>(); boolean isValidRow = false; // 遍历列 for (int j = 0; j < cells; j++) { // 获取到列的值 try { HSSFCell cell = row.getCell(j); String cellValue = getCellValue(cell); val.put(keys.get(j), cellValue); if (!isValidRow && cellValue != null && cellValue.trim().length() > 0) { isValidRow = true; } } catch (Exception e) { e.printStackTrace(); } } // 第I行所有的列数据读取完毕,放入valuelist if (isValidRow) { valueList.add(val); } } } } catch (IOException e) { e.printStackTrace(); } finally { fis.close(); } return valueList; }
/** * 从一行开始,到全部数据,列的范围指定 * * @param sheet * @param startRowIndex * @param startCellIndex * @param endCellIndex * @return */ public static List<String[]> getCellsStringValue( HSSFSheet sheet, int startRowIndex, int startCellIndex, int endCellIndex) { // 最后一行,就是全部行数-1 int endRowIndex = sheet.getPhysicalNumberOfRows() - 1; return getCellsStringValue(sheet, startRowIndex, startCellIndex, endRowIndex, endCellIndex); }
@Test public void testSheet() { try { truckService = ctx.getBean(TruckService.class); annualDataService = ctx.getBean(AnnualDataFleetFuelService.class); personService = ctx.getBean(PersonService.class); StringTokenizer stringTokenizer; SimpleDateFormat simpleFormat = (SimpleDateFormat) DateFormat.getDateInstance(); simpleFormat.applyPattern("dd-MMM-yyyy"); // Open the Excel File URL url = this.getClass().getResource("/setupfleet/Fleet_Data_Annual.xls"); FileInputStream fileInputStream = new FileInputStream(url.getFile()); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); // Open Spreadsheet Work book // Process each Sheet at a time. API FAILS to move to next sheets for (int j = 16; j <= 16; j++) { Date datee = null; String truckId = null; String driverPersonId = null; BigDecimal amount; Integer closingMileage; // Get the first Sheet(0) HSSFSheet worksheet = workbook.getSheetAt(j); // First Sheet is 0 , second is 1 and so on vehicleNumber = worksheet.getRow(4).getCell(0).toString().trim(); employeeNumber = worksheet.getRow(4).getCell(4).toString().trim(); System.out.println( "\n\n" + "Begin processing Sheet: " + j + " i.e. " + vehicleNumber + ", Driver: " + employeeNumber); // Get the Truck based of the VEHICLEnUMBER truck = truckService.findByVehicleNumber(vehicleNumber); // Set this as Start Milleage for Truck if (truck != null) { truckId = truck.getId(); } // Get personId driver = personService.findDriverWithEmployeeNumber(employeeNumber); if (driver != null) { driverPersonId = driver.getId(); } if (truck != null && driver != null) { // iterated through all the rows for (int i = 4; i <= worksheet.getPhysicalNumberOfRows(); i++) { // if Row 6, index is (5) try { datee = simpleFormat.parse( worksheet.getRow(i).getCell(1).toString().trim()); // String to Date } catch (ParseException ex) { Logger.getLogger(Setup_DailyInputSheetOne.class.getName()) .log(Level.SEVERE, null, ex); List<AnnualDataFleetFuel> annualDataFleetFuelList = annualDataService.findAll(); for (AnnualDataFleetFuel annualDataFuel : annualDataFleetFuelList) { System.out.println( "READING DATABASE - TruckId: " + annualDataFuel.getTruckId() + ". Date: " + annualDataFuel.getTransactionDate() + ". Amount: " + annualDataFuel.getMonthlyFuelCost() + ". Closing Mileage: " + annualDataFuel.getClosingMileage() + ". Driver Person ID: " + annualDataFuel.getDriverPersonId()); } } // amount = new BigDecimal(worksheet.getRow(i).getCell(2).toString().trim()); if (amount.compareTo(new BigDecimal("0.0")) == 0) { amount = new BigDecimal("0.00"); } // String mileageToken = worksheet.getRow(i).getCell(3).toString().trim(); stringTokenizer = new StringTokenizer(mileageToken, "."); closingMileage = Integer.parseInt(stringTokenizer.nextElement().toString()); // System.out.println( "TruckId: " + truckId + ". Date: " + datee + ". Amount: " + amount.setScale(2, BigDecimal.ROUND_HALF_UP) + ". Closing Mileage: " + closingMileage + ". Driver Person ID: " + driverPersonId); // Build and Persist the AnnualDataFleetFuel Object AnnualDataFleetFuel annualData = createAnnualDataEntity( datee, amount.setScale(2, BigDecimal.ROUND_HALF_UP), closingMileage, truckId, driverPersonId); annualDataService.persist(annualData); // // Build and Update the Truck Object // addTruckAnnualData(annualData); } } else { if (truck != null) { System.out.println("Truck NOT FOUND"); } if (driver != null) { System.out.println("DRIVER NOT FOUND"); } } } fileInputStream.close(); } catch (FileNotFoundException e) { System.out.println("\n\n" + "File Not Found in function setupDailyInputs()"); System.out.println("" + e); } catch (IOException e) { System.out.println("\n\n" + "Problem Reading File: /setupfleet/Fleet_Data_Annual.xls"); System.out.println("" + e); } }