/** * @param srcSheet the sheet to copy. * @param destSheet the sheet to create. * @param srcRow the row to copy. * @param destRow the row to create. * @param styleMap - */ public static void copyRow( XSSFSheet srcSheet, XSSFSheet destSheet, XSSFRow srcRow, XSSFRow destRow, Map<Integer, XSSFCellStyle> styleMap) { Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>(); destRow.setHeight(srcRow.getHeight()); for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) { XSSFCell oldCell = srcRow.getCell(j); XSSFCell newCell = destRow.getCell(j); if (oldCell != null) { if (newCell == null) { newCell = destRow.createCell(j); } copyCell(oldCell, newCell, styleMap); CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex()); if (mergedRegion != null) { CellRangeAddress newMergedRegion = new CellRangeAddress( mergedRegion.getFirstRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastRow(), mergedRegion.getLastColumn()); CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion); if (isNewMergedRegion(wrapper, mergedRegions)) { mergedRegions.add(wrapper); destSheet.addMergedRegion(wrapper.range); } } } } }
public ArrayList<String> working_Section( int SOOWDLocation, int EOOWDLocation, XSSFWorkbook workbook) { ArrayList<String> workingSection = new ArrayList<String>(); XSSFRow row; XSSFCell cell; XSSFSheet Sheet = workbook.getSheet("Operation_Standard"); String genrateFormula; for (int start = (SOOWDLocation - 1); start < EOOWDLocation; start++) { try { row = Sheet.getRow(start); cell = row.getCell(2); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: genrateFormula = "Operation_Standard!C" + (row.getRowNum() + 1); workingSection.add(genrateFormula); break; case Cell.CELL_TYPE_BLANK: break; default: System.out.println("Error"); break; } } catch (Exception e) { System.out.println(e.getMessage()); e.printStackTrace(); } } return workingSection; }
/** * Compares two <code>XSSFRow</code> objects. Two rows are equal if they belong to the same * worksheet and their row indexes are equal. * * @param row the <code>XSSFRow</code> to be compared. * @return the value <code>0</code> if the row number of this <code>XSSFRow</code> is equal to the * row number of the argument <code>XSSFRow</code>; a value less than <code>0</code> if the * row number of this this <code>XSSFRow</code> is numerically less than the row number of the * argument <code>XSSFRow</code>; and a value greater than <code>0</code> if the row number of * this this <code>XSSFRow</code> is numerically greater than the row number of the argument * <code>XSSFRow</code>. * @throws IllegalArgumentException if the argument row belongs to a different worksheet */ public int compareTo(XSSFRow row) { int thisVal = this.getRowNum(); if (row.getSheet() != getSheet()) throw new IllegalArgumentException("The compared rows must belong to the same XSSFSheet"); int anotherVal = row.getRowNum(); return (thisVal < anotherVal ? -1 : (thisVal == anotherVal ? 0 : 1)); }
private XSSFRow createRowFrom(IRecordable o) { XSSFRow lastRow, r; String[] data; if (dicoLocations.containsKey(o.getClassement())) lastRow = dicoLocations.get(o.getClassement()); else { XSSFSheet logSheet = wb.createSheet(o.getClassement()); lastRow = logSheet.createRow(0); data = o.getTitles(); fillRowWith(lastRow, data); dicoLocations.put(o.getClassement(), lastRow); } r = lastRow.getSheet().createRow(lastRow.getRowNum() + 1); data = o.getRecords(); fillRowWith(r, data); dicoLocations.replace(o.getClassement(), r); return r; }
private void readExcel() throws IOException, URISyntaxException { URL inputStream = this.getClass().getResource("ReleaseNotes-002.xlsx"); File file = new File(inputStream.toURI()); FileInputStream fIP = new FileInputStream(file); XSSFRow row; // Get the workbook instance for XLSX file XSSFWorkbook workbook = new XSSFWorkbook(fIP); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { XSSFSheet spreadsheet = workbook.getSheetAt(i); // System.out.println(spreadsheet.getSheetName()); if (!spreadsheet.getSheetName().equals("EAR SAI")) { continue; } StringBuilder sb = new StringBuilder(); Iterator<Row> rowIterator = spreadsheet.iterator(); while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); System.out.println(row.getRowNum() + " " + row.getRowStyle()); if (row.getRowStyle() != null) { System.out.println(row.getRowNum() + " " + row.getRowStyle().getBorderBottom()); } Iterator<org.apache.poi.ss.usermodel.Cell> cellIterator = row.cellIterator(); if (row.getRowNum() < 4) { if (row.getRowNum() == 3) analyzeTable(row); continue; } while (cellIterator.hasNext()) { org.apache.poi.ss.usermodel.Cell cell = cellIterator.next(); if (cell.getColumnIndex() == NAME_COLUMN) { if (!(sb.lastIndexOf("\n") == sb.length() - 1)) sb.append("\n"); } if (cell.getColumnIndex() < VALUE_COLUMN) { sb.append(readCells(cell)); continue; } if (cell.getColumnIndex() == VALUE_COLUMN) { sb.append(" -> " + readCells(cell)); if (!cellIterator.hasNext()) { sb.append("\n"); } } if (cell.getColumnIndex() == STATUS_COLUMN) { sb.append(" => " + readCells(cell)); if (!cellIterator.hasNext()) { sb.append("\n"); } } // if (!cellIterator.hasNext()) { // sb.append("\n"); // } } // while (cellIterator.hasNext()) { // Cell cell = cellIterator.next(); // sb.append(readCells(cell)); // if (cellIterator.hasNext()) { // // if (cell.getColumnIndex() == 5 && cell.getCellType() != 3) { // sb.append("\t->\t"); // sb.append(readCells(cell)); // if (!cellIterator.hasNext()) { // sb.append("\t=>\tstate\n"); // continue; // } // cell = cellIterator.next(); // if (cell.getCellType() != 3) { // sb.append("\t=>\t"); // sb.append(readCells(cell)); // sb.append("\n"); // } else { // sb.append("\t=>\tstate\n"); // } // continue; // } // sb.append(readCells(cell)); // } else { // sb.append("\\"); // } // } } // System.out.println(); System.out.println(sb); } if (file.isFile() && file.exists()) { System.out.println("openworkbook.xlsx file open successfully."); } else { System.out.println("Error to open openworkbook.xlsx file."); } }
/** 导入Excel表格 */ @SuppressWarnings("unused") public String intoDB() throws IOException { String uploadPath = ServletActionContext.getServletContext().getRealPath("/upload"); isImpSuccess = false; // 判断数据导入是否成功 errCount = 0; // 判断导入数据模板是否对应 // 基于myFile创建一个文件输入流 InputStream is = new FileInputStream(myFile); // 设置目标文件 File toFile = new File(uploadPath, this.getMyFileFileName()); String caseName = null; // 活动名称 String caseDesc = null; // 活动描述 Timestamp caseSt = null; // 活动开始时间 Timestamp caseEt = null; // 活动结束时间 String sysUserId = null; // 创建/修改用户ID Timestamp sysDt = null; // 修改时间 Integer status = null; // 活动状态 String caseCode = null; // 活动编码 Double ratioNew = null; // 新款占比 Integer num = null; // 参与款数 Date date = new Date(); // 创建一个时间对象,获取到当前的时间 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); // 设置时间显示格式 String str = sdf.format(date); // 将当前时间格式化为需要的类型 sysDt = Timestamp.valueOf(str); sysUserId = ParaCasePAction.getCurrentUserName(); /** 2007版的读取方法 */ int k = 0; int flag = 0; // 指示指针所访问的位置 if (myFile != null) { try { Workbook workbook = WorkbookFactory.create(toFile); intolist.clear(); // Workbook workbook = new XSSFWorkbook(is);//初始化workbook对象 for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++) { // 读取每一个sheet if (null != workbook.getSheetAt(numSheets)) { XSSFSheet aSheet = (XSSFSheet) workbook.getSheetAt(numSheets); // 定义Sheet对象 for (int rowNumOfSheet = 1; rowNumOfSheet <= aSheet.getLastRowNum(); rowNumOfSheet++) { // 进入当前sheet的行的循环 if (null != aSheet.getRow(rowNumOfSheet)) { XSSFRow aRow = aSheet.getRow(rowNumOfSheet); // 定义行,并赋值 for (int cellNumOfRow = 0; cellNumOfRow <= aRow.getLastCellNum(); cellNumOfRow++) { // 读取rowNumOfSheet值所对应行的数据 XSSFCell xCell = aRow.getCell(cellNumOfRow); // 获得行的列数 // //获得列值 // System.out.println("type="+xCell.getCellType()); if (null != aRow.getCell(cellNumOfRow)) { // 如果rowNumOfSheet的值为0,则读取表头,判断excel的格式和预定格式是否相符 if (rowNumOfSheet == 1) { if (xCell.getCellType() == XSSFCell.CELL_TYPE_STRING) { /** * 一下根据从Excel的各列命名是否符合要求:如下面匹配: 活动名称,活动描述,活动开始时间, 活动结束时间,操作用户, * 修改时间,活动状态,活动编码,新款占比 ,参与款数,备注开始时间,备注结束时间 */ if (cellNumOfRow == 0) { if (xCell .getStringCellValue() .replace('\t', ' ') .replace('\n', ' ') .replace('\r', ' ') .trim() .equals("活动名称")) { flag++; } else { errCount++; } } else if (cellNumOfRow == 1) { if (xCell .getStringCellValue() .replace('\t', ' ') .replace('\n', ' ') .replace('\r', ' ') .trim() .equals("活动描述")) { flag++; } else { errCount++; } } else if (cellNumOfRow == 2) { if (xCell .getStringCellValue() .replace('\t', ' ') .replace('\n', ' ') .replace('\r', ' ') .trim() .equals("活动开始时间")) { flag++; } else { errCount++; } } else if (cellNumOfRow == 3) { if (xCell .getStringCellValue() .replace('\t', ' ') .replace('\n', ' ') .replace('\r', ' ') .trim() .equals("活动结束时间")) { flag++; } else { errCount++; } } else if (cellNumOfRow == 4) { if (xCell .getStringCellValue() .replace('\t', ' ') .replace('\n', ' ') .replace('\r', ' ') .trim() .equals("活动状态")) { flag++; } else { errCount++; } } else if (cellNumOfRow == 5) { if (xCell .getStringCellValue() .replace('\t', ' ') .replace('\n', ' ') .replace('\r', ' ') .trim() .equals("活动编码")) { flag++; } else { errCount++; } } else if (cellNumOfRow == 6) { if (xCell .getStringCellValue() .replace('\t', ' ') .replace('\n', ' ') .replace('\r', ' ') .trim() .equals("新款占比")) { flag++; } else { errCount++; } } else if (cellNumOfRow == 7) { if (xCell .getStringCellValue() .replace('\t', ' ') .replace('\n', ' ') .replace('\r', ' ') .trim() .equals("参与款数")) { flag++; } else { errCount++; } } } } else { // rowNumOfSheet != 0 即开始打印内容 // 获取excel中每列的值,并赋予相应的变量,如下的赋值的ID,name,sex, // Dormitory,sept; if (xCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { // 为数值型 System.out.println( "===============进入XSSFCell .CELL_TYPE_NUMERIC模块============"); switch (cellNumOfRow) { case 2: Date St = (Date) xCell.getDateCellValue(); // 对日期处理 caseSt = new Timestamp(St.getTime()); break; case 3: Date Et = (Date) xCell.getDateCellValue(); // 对日期处理 caseEt = new Timestamp(Et.getTime()); break; case 4: status = (int) xCell.getNumericCellValue(); break; case 6: ratioNew = xCell.getNumericCellValue(); break; case 7: num = (int) xCell.getNumericCellValue(); break; } } else if (xCell.getCellType() == XSSFCell.CELL_TYPE_STRING) { // 为字符串型 System.out.println( "===============进入XSSFCell .CELL_TYPE_STRING模块============"); switch (cellNumOfRow) { case 0: caseName = xCell .getStringCellValue() .replace('\t', ' ') .replace('\n', ' ') .replace('\r', ' ') .trim(); break; case 1: caseDesc = xCell .getStringCellValue() .replace('\t', ' ') .replace('\n', ' ') .replace('\r', ' ') .trim(); break; case 5: caseCode = xCell .getStringCellValue() .replace('\t', ' ') .replace('\n', ' ') .replace('\r', ' ') .trim(); break; } } else if (xCell.getCellType() == XSSFCell.CELL_TYPE_BLANK) { System.out.println( "提示:在Sheet" + (numSheets + 1) + "中的第" + (rowNumOfSheet + 1) + "行的第" + (cellNumOfRow + 1) + "列的值为空,请查看核对是否符合约定要求"); switch (cellNumOfRow) { case 0: caseName = ""; break; case 1: caseDesc = ""; break; case 2: caseSt = null; break; case 3: caseEt = null; break; case 4: status = null; break; case 5: caseCode = ""; break; } } } } } // 判断各个元素被赋值,如果放入数据库,就直接使用数据的插入的函数就可以了。 if (aRow.getRowNum() > 1) { ParaDt pd = new ParaDt(); pd.setCaseName(caseName); pd.setCaseDesc(caseDesc); pd.setCaseEt(caseEt); pd.setCaseSt(caseSt); pd.setSysUserId(sysUserId); pd.setSysDt(sysDt); pd.setStatus(status); pd.setCaseCode(caseCode); pd.setRatioNew(ratioNew); pd.setNum(num); pd.setCaseCode(caseCode); intolist.add(pd); } } // 获得一行,即读取每一行 } // 读取每一个sheet } } refreshList = "paraCaseDtgetParaDtAll"; titleName = "营销活动实例"; if (errCount > 0) { msg = "导入数据与模板不符,导入失败!"; } else { // 调用sever方法 isImpSuccess = paraDtService.addOneBoat(intolist, 500); if (isImpSuccess) { msg = "营销活动实例导入成功!"; } else { msg = "营销活动实例导入失败!"; } } } catch (Exception e) { e.printStackTrace(); } } return "importExcel"; }
@POST @Path("/documents") @Consumes({MediaType.MULTIPART_FORM_DATA}) @Produces({MediaType.APPLICATION_JSON}) public String createUploadFile( @HeaderParam("Content-Length") Long fileSize, @FormDataParam("file") InputStream inputStream, @FormDataParam("file") FormDataContentDisposition fileDetails, @FormDataParam("file") FormDataBodyPart bodyPart, @FormDataParam("name") String name, @FormDataParam("description") String description) { FileUtils.validateFileSizeWithinPermissibleRange( fileSize, name, ApiConstants.MAX_FILE_UPLOAD_SIZE_IN_MB); int i; /*DocumentCommand documentCommand = new DocumentCommand(null, null, null, null, name, fileDetails.getFileName(), fileSize, bodyPart.getMediaType().toString(), description, null);*/ try { String fileUploadLocation = FileUtils.generateXlsFileDirectory(); String fileName = fileDetails.getFileName(); if (!new File(fileUploadLocation).isDirectory()) { new File(fileUploadLocation).mkdirs(); } String fileLocation = FileUtils.saveToFileSystem(inputStream, fileUploadLocation, fileName); InputStream excelFileToRead = new FileInputStream(fileLocation); XSSFWorkbook wb = new XSSFWorkbook(excelFileToRead); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; XSSFCell cell; String serialno = "0"; int countno = Integer.parseInt(serialno); if (countno == 0) { countno = countno + 2; } else if (countno == 1) { countno = countno + 1; } System.out.println("Excel Row No is: " + countno); Iterator rows = sheet.rowIterator(); Vector<XSSFCell> v = new Vector<XSSFCell>(); if (countno > 0) { countno = countno - 1; } while (rows.hasNext()) { row = (XSSFRow) rows.next(); i = row.getRowNum(); if (i > 0) { if (i >= countno) { Iterator cells = row.cellIterator(); while (cells.hasNext()) { cell = (XSSFCell) cells.next(); if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { // System.out.print(cell.getStringCellValue() + // " "); v.add(cell); } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { // System.out.print(cell.getNumericCellValue() + // " "); v.add(cell); } else { v.add(cell); } } // ItemDetailsCommand itemdetails=new // ItemDetailsCommand(Integer.parseInt(v.elementAt(0).toString()),v.elementAt(1).toString(),Integer.parseInt(v.elementAt(2).toString()),v.elementAt(3).toString(),v.elementAt(4).toString(),v.elementAt(5).toString(),Integer.parseInt(v.elementAt(6).toString()),Integer.parseInt(v.elementAt(7).toString()),Integer.parseInt(v.elementAt(8).toString()),v.elementAt(9).toString(),null); List<ItemDetailsCommand> ItemDetailsCommandList = new ArrayList<ItemDetailsCommand>(); /*for(i=0;i<10;i++) {*/ /* Iterator iterator = v.iterator(); while(iterator.hasNext()) { System.out.println(v.elementAt(0).toString()); System.out.println( v.elementAt(1).toString()); System.out.println( v.elementAt(2).toString()); System.out.println( v.elementAt(3).toString()); System.out.println( v.elementAt(4).toString()); System.out.println( v.elementAt(5).toString()); System.out.println( v.elementAt(6).toString()); System.out.println( v.elementAt(7).toString()); System.out.println( v.elementAt(8).toString()); System.out.println( v.elementAt(9).toString()); // itemDetails = ItemDetails.create(command.getItemMasterId(), command.getSerialNumber(), command.getGrnId(),command.getProvisioningSerialNumber(), command.getQuality(),command.getStatus(), command.getOfficeId(), command.getClientId(), command.getWarranty(), command.getRemark()); //ItemDetailsCommand itemdetails=new ItemDetailsCommand(Long.parseLong(v.elementAt(0).toString()), v.elementAt(1).toString(), Long.parseLong(v.elementAt(2).toString()), v.elementAt(3).toString(), v.elementAt(4).toString(), v.elementAt(5).toString(), Long.parseLong(v.elementAt(6).toString()), Long.parseLong(v.elementAt(7).toString()), Long.parseLong(v.elementAt(8).toString()), v.elementAt(9).toString()); //ItemDetailsCommand itemdetails=new ItemDetailsCommand(Long.parseLong(v.elementAt(0).toString()), v.elementAt(1).toString(), Long.parseLong(v.elementAt(2).toString()), v.elementAt(3).toString(), v.elementAt(4).toString(), v.elementAt(5).toString(), Long.parseLong(v.elementAt(6).toString()), Long.parseLong(v.elementAt(7).toString()), Long.parseLong(v.elementAt(8).toString()), v.elementAt(9).toString()); //CommandProcessingResult id = this.itemDetailsWritePlatformService.addItem(itemdetails); //ItemDetailsCommandList.add(itemdetails); ItemDetailsCommand itemDetailsCommand=new ItemDetailsCommand(); new Double(v.elementAt(0).toString()).longValue(); itemDetailsCommand.setItemMasterId(new Double(v.elementAt(0).toString()).longValue()); itemDetailsCommand.setSerialNumber(v.elementAt(1).toString()); itemDetailsCommand.setGrnId(new Double(v.elementAt(2).toString()).longValue()); itemDetailsCommand.setProvisioningSerialNumber( v.elementAt(3).toString()); itemDetailsCommand.setQuality( v.elementAt(4).toString()); itemDetailsCommand.setRemark(v.elementAt(9).toString()); itemDetailsCommand.setStatus(v.elementAt(5).toString()); itemDetailsCommand.setOfficeId(new Double(v.elementAt(6).toString()).longValue()); itemDetailsCommand.setClientId(new Double(v.elementAt(7).toString()).longValue()); itemDetailsCommand.setWarranty(new Double(v.elementAt(8).toString()).longValue()); ItemDetailsCommandList.add(itemDetailsCommand); CommandProcessingResult id = this.itemDetailsWritePlatformService.addItem(itemDetailsCommand); } */ } } } Iterator iterator = v.iterator(); while (iterator.hasNext()) { System.out.println(v.elementAt(0).toString()); System.out.println(v.elementAt(1).toString()); System.out.println(v.elementAt(2).toString()); System.out.println(v.elementAt(3).toString()); System.out.println(v.elementAt(4).toString()); System.out.println(v.elementAt(5).toString()); System.out.println(v.elementAt(6).toString()); System.out.println(v.elementAt(7).toString()); System.out.println(v.elementAt(8).toString()); System.out.println(v.elementAt(9).toString()); // itemDetails = ItemDetails.create(command.getItemMasterId(), command.getSerialNumber(), // command.getGrnId(),command.getProvisioningSerialNumber(), // command.getQuality(),command.getStatus(), command.getOfficeId(), command.getClientId(), // command.getWarranty(), command.getRemark()); // ItemDetailsCommand itemdetails=new // ItemDetailsCommand(Long.parseLong(v.elementAt(0).toString()), v.elementAt(1).toString(), // Long.parseLong(v.elementAt(2).toString()), v.elementAt(3).toString(), // v.elementAt(4).toString(), v.elementAt(5).toString(), // Long.parseLong(v.elementAt(6).toString()), Long.parseLong(v.elementAt(7).toString()), // Long.parseLong(v.elementAt(8).toString()), v.elementAt(9).toString()); // ItemDetailsCommand itemdetails=new // ItemDetailsCommand(Long.parseLong(v.elementAt(0).toString()), v.elementAt(1).toString(), // Long.parseLong(v.elementAt(2).toString()), v.elementAt(3).toString(), // v.elementAt(4).toString(), v.elementAt(5).toString(), // Long.parseLong(v.elementAt(6).toString()), Long.parseLong(v.elementAt(7).toString()), // Long.parseLong(v.elementAt(8).toString()), v.elementAt(9).toString()); // CommandProcessingResult id = this.itemDetailsWritePlatformService.addItem(itemdetails); // ItemDetailsCommandList.add(itemdetails); ItemDetailsCommand itemDetailsCommand = new ItemDetailsCommand(); new Double(v.elementAt(0).toString()).longValue(); itemDetailsCommand.setItemMasterId(new Double(v.elementAt(0).toString()).longValue()); itemDetailsCommand.setSerialNumber(v.elementAt(1).toString()); itemDetailsCommand.setGrnId(new Double(v.elementAt(2).toString()).longValue()); itemDetailsCommand.setProvisioningSerialNumber(v.elementAt(3).toString()); itemDetailsCommand.setQuality(v.elementAt(4).toString()); itemDetailsCommand.setRemark(v.elementAt(9).toString()); itemDetailsCommand.setStatus(v.elementAt(5).toString()); itemDetailsCommand.setOfficeId(new Double(v.elementAt(6).toString()).longValue()); itemDetailsCommand.setClientId(new Double(v.elementAt(7).toString()).longValue()); itemDetailsCommand.setWarranty(new Double(v.elementAt(8).toString()).longValue()); // ItemDetailsCommandList.add(itemDetailsCommand); CommandProcessingResult id = this.itemDetailsWritePlatformService.addItem(itemDetailsCommand); } } catch (Exception e) { e.printStackTrace(); } /** * TODO: also need to have a backup and stop reading from stream after max size is reached to * protect against malicious clients */ /** TODO: need to extract the actual file type and determine if they are permissable */ /// ItemDetailsCommand itemDetailsCommand=new ItemDetailsCommand() // DocumentCommand documentCommand = new DocumentCommand(null, null, entityType, entityId, name, // fileDetails.getFileName(), fileSize, /// bodyPart.getMediaType().toString(), description, null); // Long documentId = this.documentWritePlatformService.createDocument(documentCommand, // inputStream); // return this.toApiJsonSerializer.serialize(CommandProcessingResult.resourceResult(1, null)); return null; }