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); } }
public void testFormulaGeneral() throws IOException { // perhaps this testcase belongs on TestHSSFName HSSFWorkbook wb = new HSSFWorkbook(); HSSFName name = wb.createName(); wb.createSheet("Sheet1"); name.setNameName("test"); name.setRefersToFormula("Sheet1!A1+Sheet1!A2"); assertEquals("Sheet1!A1+Sheet1!A2", name.getRefersToFormula()); name.setRefersToFormula("5*6"); assertEquals("5*6", name.getRefersToFormula()); wb.close(); }
public void testDgRecordNumShapes() throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); try { HSSFSheet sheet = wb.createSheet(); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); EscherAggregate aggregate = HSSFTestHelper.getEscherAggregate(patriarch); EscherDgRecord dgRecord = (EscherDgRecord) aggregate.getEscherRecord(0).getChild(0); assertEquals(dgRecord.getNumShapes(), 1); } finally { wb.close(); } }
public void testFormulaRelAbs_bug46174() throws IOException { // perhaps this testcase belongs on TestHSSFName HSSFWorkbook wb = new HSSFWorkbook(); HSSFName name = wb.createName(); wb.createSheet("Sheet1"); name.setNameName("test"); name.setRefersToFormula("Sheet1!$B$3"); if (name.getRefersToFormula().equals("Sheet1!B3")) { fail("Identified bug 46174"); } assertEquals("Sheet1!$B$3", name.getRefersToFormula()); wb.close(); }
public void testBug45312() throws Exception { HSSFWorkbook wb = new HSSFWorkbook(); try { HSSFSheet sheet = wb.createSheet(); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); { HSSFClientAnchor a1 = new HSSFClientAnchor(); a1.setAnchor((short) 1, 1, 0, 0, (short) 1, 1, 512, 100); HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1); shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); } { HSSFClientAnchor a1 = new HSSFClientAnchor(); a1.setAnchor((short) 1, 1, 512, 0, (short) 1, 1, 1024, 100); HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1); shape1.setFlipVertical(true); shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); } { HSSFClientAnchor a1 = new HSSFClientAnchor(); a1.setAnchor((short) 2, 2, 0, 0, (short) 2, 2, 512, 100); HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1); shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); } { HSSFClientAnchor a1 = new HSSFClientAnchor(); a1.setAnchor((short) 2, 2, 0, 100, (short) 2, 2, 512, 200); HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1); shape1.setFlipHorizontal(true); shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); } /*OutputStream stream = new FileOutputStream("/tmp/45312.xls"); try { wb.write(stream); } finally { stream.close(); }*/ checkWorkbookBack(wb); } finally { wb.close(); } }
public void testOpt() throws Exception { HSSFWorkbook wb = new HSSFWorkbook(); try { // create a sheet with a text box HSSFSheet sheet = wb.createSheet(); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFTextbox textbox = patriarch.createTextbox(new HSSFClientAnchor()); EscherOptRecord opt1 = HSSFTestHelper.getOptRecord(textbox); EscherOptRecord opt2 = HSSFTestHelper.getEscherContainer(textbox).getChildById(EscherOptRecord.RECORD_ID); assertSame(opt1, opt2); } finally { wb.close(); } }
public void testShapeContainerImplementsIterable() throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); try { HSSFSheet sheet = wb.createSheet(); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); patriarch.createSimpleShape(new HSSFClientAnchor()); patriarch.createSimpleShape(new HSSFClientAnchor()); int i = 2; for (HSSFShape shape : patriarch) { i--; } assertEquals(i, 0); } finally { wb.close(); } }
public String getZhFrom4GYsb() throws IOException { // 预算表中 第三行 B列的名称为: 单项工程名称:SXZH001TL新建、共址2G、共址其他运营商的(F)(D)宏站基站 // path1: 预算表 path2 3g4g基础信息 String results = ""; String result2 = ""; FileInputStream fise = new FileInputStream(excelPath); HSSFWorkbook wb = new HSSFWorkbook(fise); HSSFWorkbook wb2 = new HSSFWorkbook(new FileInputStream(excelPath2)); HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(wb); HSSFFormulaEvaluator e2 = new HSSFFormulaEvaluator(wb2); // TODO: 将此处的文件名替换为从参数读取的文件名 String[] strArray = new String[2]; String[] paths = this.excelPath.split("\\\\"); strArray[0] = paths[paths.length - 1]; // System.out.println(strArray[0]); String[] paths1 = this.excelPath2.split("\\\\"); // strArray[1] = paths1[paths1.length-1]; // System.out.println(strArray[1]); HSSFFormulaEvaluator[] evals = new HSSFFormulaEvaluator[2]; evals[0] = e; evals[1] = e2; Sheet sheet = wb.getSheetAt(7); Row r = null; r = sheet.getRow(2); Cell cell2 = r.getCell(1); Cell cell8 = r.getCell(8); // System.out.println(cell8.toString()); String[] path1 = cell8.toString().split("\'"); String path12 = path1[3]; String[] path2 = path12.split("]"); String path21 = path2[0]; String path3 = path21.substring(1); strArray[1] = path3; // System.out.println(path3); HSSFFormulaEvaluator.setupEnvironment(strArray, evals); if (cell2.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { results = e.evaluate(cell2).getStringValue(); // results = cell2.getStringCellValue(); } else if (cell2.getCellType() == HSSFCell.CELL_TYPE_STRING) { results = cell2.toString(); } System.out.println(result2); // TL TLD TLFD SXZH017TL 前面有7位 TL- conditions // TL-1、TL-2、TL-3 TLD-1 TLD-2 TLD-3 if (results.contains("TLFD")) { int end = results.indexOf("TLFD"); end = end + 4; int begin = end - 12; result2 = results.substring(begin + 1, end); } else if (results.contains("TL-")) { int end = results.indexOf("TL-"); int count = 11; boolean flag = true; end = end + 3; System.out.println(results.substring(end - 1, end)); for (int k = 1; k < 5; k++) { end = end + 1; System.out.println(results.substring(end - 1, end)); flag = isNumeric(results.substring(end - 1, end)); if (flag) { count++; } else { break; } } int begin = end - count; result2 = results.substring(begin + 1, end); } else if (results.contains("TLD-")) { int end = results.indexOf("TLD-"); int count = 12; boolean flag = true; end = end + 4; System.out.println(results.substring(end - 1, end)); for (int k = 1; k < 5; k++) { end = end + 1; System.out.println(results.substring(end - 1, end)); flag = isNumeric(results.substring(end - 1, end)); if (flag) { count++; } else { break; } } int begin = end - count; result2 = results.substring(begin + 1, end); } else if (results.contains("TLD")) { int end = results.indexOf("TLD"); end = end + 3; int begin = end - 11; result2 = results.substring(begin + 1, end); } else if (results.contains("TL")) { // System.out.println(results+"!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"); int end = results.indexOf("TL"); // System.out.println(end); end = end + 2; // System.out.println(results.substring(end-1, end)); int begin = end - 10; result2 = results.substring(begin + 1, end); } wb2.close(); wb.close(); fise.close(); return result2; }
@Override public List<Artifact> getArtifactList(Project project, File spreadsheet) throws IOException { xlsFile = spreadsheet; BufferedInputStream bis = null; HSSFWorkbook workBook = null; List<Artifact> artifacts = new ArrayList<Artifact>(); try { bis = new BufferedInputStream(new FileInputStream(xlsFile)); workBook = new HSSFWorkbook(bis); HSSFSheet sheet = workBook.getSheetAt(0); Map<ColumnType, Integer> columnHeaders = getColumnInfo(sheet); Integer typeColumn = columnHeaders.get(ColumnType.TYPE_COLUMN); Integer classifierColumn = columnHeaders.get(ColumnType.CLASSIFIER_COLUMN); Integer digestColumn = columnHeaders.get(ColumnType.DIGEST_COLUMN); String groupId = ""; String artifactId = ""; String type = JAR; String version = ""; String classifier = ""; String digest = ""; for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); ++i) { HSSFRow row = sheet.getRow(i); if (row != null) { HSSFCell cell = row.getCell(columnHeaders.get(ColumnType.GROUP_COLUMN).intValue()); if (cell != null) { String gId = cell.getStringCellValue().trim(); if (!gId.isEmpty()) { groupId = gId; } } cell = row.getCell(columnHeaders.get(ColumnType.ARTIFACT_COLUMN).intValue()); if (cell != null) { String aId = cell.getStringCellValue().trim(); if (!aId.isEmpty()) { artifactId = aId; } } cell = row.getCell(columnHeaders.get(ColumnType.VERSION_COLUMN).intValue()); if (cell != null) { String v; if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { v = String.valueOf(cell.getNumericCellValue()); } else { v = cell.getStringCellValue().trim(); } if (!v.isEmpty()) { version = v; } } cell = (typeColumn != null) ? row.getCell(typeColumn.intValue()) : null; if (cell != null) { type = cell.getStringCellValue().trim(); } cell = (classifierColumn != null) ? row.getCell(classifierColumn.intValue()) : null; if (cell != null) { classifier = cell.getStringCellValue().trim(); } cell = (digestColumn != null) ? row.getCell(digestColumn.intValue()) : null; if (cell != null) { digest = cell.getStringCellValue().trim(); } if (groupId.isEmpty() || artifactId.isEmpty() || version.isEmpty()) { if (groupId.isEmpty() || version.isEmpty()) { project.log( "Row " + row.getRowNum() + ": Invalid artifact specified: [groupId: " + groupId + ", artifactId: " + artifactId + ", classifier: " + classifier + ", version: " + version + ", digest: " + digest + "]"); } } else { artifacts.add(new Artifact(groupId, artifactId, type, classifier, version, digest)); } } artifactId = ""; classifier = ""; digest = ""; type = JAR; } project.log(sheet.getLastRowNum() + " rows read from " + xlsFile, Project.MSG_VERBOSE); } finally { if (workBook != null) { workBook.close(); } Closer.close(bis); } return artifacts; }
public static void writeExcelFile(String fileLocation) { try { FileOutputStream fileOut = new FileOutputStream(fileLocation); HSSFWorkbook workbook = new HSSFWorkbook(); Font bold = workbook.createFont(); // Create font bold.setBoldweight(Font.BOLDWEIGHT_BOLD); // Make font bold CellStyle correctCell = workbook.createCellStyle(); correctCell.setFillForegroundColor(HSSFColor.GREEN.index); correctCell.setFillBackgroundColor(HSSFColor.GREEN.index); correctCell.setFillPattern(CellStyle.SOLID_FOREGROUND); CellStyle incorrectCell = workbook.createCellStyle(); incorrectCell.setFillForegroundColor(HSSFColor.RED.index); incorrectCell.setFillBackgroundColor(HSSFColor.RED.index); incorrectCell.setFillPattern(CellStyle.SOLID_FOREGROUND); CellStyle classificationCells = workbook.createCellStyle(); classificationCells.setFillForegroundColor(HSSFColor.YELLOW.index); classificationCells.setFillBackgroundColor(HSSFColor.YELLOW.index); classificationCells.setFillPattern(CellStyle.SOLID_FOREGROUND); CellStyle attributeNameCells = workbook.createCellStyle(); attributeNameCells.setFont(bold); CellStyle classificationAttributeCell = workbook.createCellStyle(); classificationAttributeCell.setFillForegroundColor(HSSFColor.YELLOW.index); classificationAttributeCell.setFillBackgroundColor(HSSFColor.YELLOW.index); classificationAttributeCell.setFillPattern(CellStyle.SOLID_FOREGROUND); classificationAttributeCell.setFont(bold); Sheet worksheet = workbook.createSheet("Results"); Row currRow = worksheet.createRow(0); for (int attribute = 0; attribute < metadataLL.size() + 1; attribute++) { Cell currCell = currRow.createCell(attribute); if (attribute < metadataLL.size()) { currCell.setCellValue(metadataLL.get(attribute)[0]); if (metadataLL.get(attribute)[2].compareTo("classifier") == 0) { currCell.setCellStyle(classificationAttributeCell); } else { currCell.setCellStyle(attributeNameCells); } } else { currCell.setCellValue("Guessed Classification"); currCell.setCellStyle(attributeNameCells); } } int correct = 0; int incorrect = 0; for (int node = 0; node < testDataLL.size(); node++) { currRow = worksheet.createRow(node + 1); // Offset by one since first row is header data int classifierCompleted = 0; // Necessary for if data does not end in classifier for (int attribute = 0; attribute < metadataLL.size() + 2; attribute++) // +1 for the row for guessed data +1 for the row that contains { Cell currCell = currRow.createCell(attribute); if (attribute < metadataLL.size()) // Print testingData { if (metadataLL.get(attribute)[2].compareTo("classifier") == 0) { currCell.setCellValue(actualClassifications.get(node)); currCell.setCellStyle(classificationCells); classifierCompleted++; } else { currCell.setCellValue(testDataLL.get(node)[attribute - classifierCompleted]); } } else if (attribute == metadataLL.size()) // Print guessed classification { currCell.setCellValue(guessedClassifications.get(node)); if (guessedClassifications.get(node).compareTo(actualClassifications.get(node)) == 0) { currCell.setCellStyle(correctCell); correct++; } else { currCell.setCellStyle(incorrectCell); incorrect++; } if (node == testDataLL.size() - 1) // If this is the last loop { double precentRight = (double) correct / (correct + incorrect); currRow = worksheet.createRow(node + 2); currCell = currRow.createCell(attribute); currCell.setCellValue(precentRight); if (precentRight > .90) { correctCell.setDataFormat(workbook.createDataFormat().getFormat("0.000%")); currCell.setCellStyle(correctCell); } else { incorrectCell.setDataFormat(workbook.createDataFormat().getFormat("0.000%")); currCell.setCellStyle(incorrectCell); } } } else if (attribute == metadataLL.size() + 1) // Print potential bad training data if the flag is true { if (unseenDataFlag.get(node)) { currCell.setCellValue( "This node an attribute value not in the training set, classifier selected is based on most frequent classifier. If laplacian smoothing is 1 or more this likely wont happen"); // TODO make this a bit shorter } } } } worksheet = workbook.createSheet("Training Data"); currRow = worksheet.createRow(0); for (int attribute = 0; attribute < metadataLL.size(); attribute++) { Cell currCell = currRow.createCell(attribute); currCell.setCellValue(metadataLL.get(attribute)[0]); currCell.setCellStyle(attributeNameCells); } for (int node = 0; node < trainingDataLL.size(); node++) { currRow = worksheet.createRow(node + 1); // Offset by one since first row is header data int classifierCompleted = 0; // Necessary for if data does not end in classifier for (int attribute = 0; attribute < metadataLL.size(); attribute++) { Cell currCell = currRow.createCell(attribute); if (metadataLL.get(attribute)[2].compareTo("classifier") == 0) { currCell.setCellValue(knownClassifications.get(node)); classifierCompleted++; } else { currCell.setCellValue(trainingDataLL.get(node)[attribute - classifierCompleted]); } } } worksheet = workbook.createSheet("Likelihood"); currRow = worksheet.createRow(0); int largestAttributeSize = 0; for (int attribute = 0; attribute < classifier.size(); attribute++) { if (classifier.get(attribute).size() > largestAttributeSize) { largestAttributeSize = classifier.get(attribute).size(); } } // Label attributes along the top for (int i = 0; i < metadataLL.size(); i++) { if (i == 0) { Cell currCell = currRow.createCell(i); // currCell.setCellValue("Attributes"); currCell.setCellStyle(attributeNameCells); } else { Cell currCell = currRow.createCell(i); currCell.setCellValue( metadataLL .get(i - 1)[0]); // -1 since the first cell does not contain a attribute name currCell.setCellStyle(attributeNameCells); } } // List possible classifications on the side and classification likelihoods at the end for (int i = 0; i < (largestAttributeSize * (classificationTypes.size() + 1) + classificationTypes.size() + 1); i++) // +1 since the first row of each stride lists each attributes string of what // occurrence the likelihoods are displaying { // +classificationTypes.size() so we can list the classification types likelihood at the end currRow = worksheet.createRow(i + 1); // +1 since first row is attribute names Cell currCell = currRow.createCell(0); int currentClassificationType = i % (classificationTypes.size() + 1); // +1 since the first row of each stride lists each attributes string of // what occurrence the likelihoods are displaying // List the classification type of each row along the side if (i < largestAttributeSize * (classificationTypes.size() + 1)) // +1 since the first row of each stride lists each attributes string of // what occurrence the likelihoods are displaying { for (int j = 0; j < classificationTypes.size() + 1; j++) // +1 since the first row of each stride lists each attributes string of what // occurrence the likelihoods are displaying { if (currentClassificationType == 0) { // Do nothing for now may have it say something later } else if (currentClassificationType == j) { currCell.setCellValue( classificationTypes.get( j - 1)); // -1 since the first cell does not contain a classification type } } } else // List the likelihood of each classification at the end { for (int j = 0; j < classificationTypes.size() + 1; j++) // +1 since the first row of each stride lists each attributes string of what // occurrence the likelihoods are displaying { if (currentClassificationType == 0) { // Do nothing for now may have it say value later } else if (currentClassificationType == j) { currCell.setCellValue( "Likelihood of: " + classificationTypes.get(j - 1) + " is " + classificationLikelihood.get( j - 1)); // -1 since the first cell does not contain a classification type } } } currCell.setCellStyle(attributeNameCells); } // List the data for (int attribute = 0; attribute < classifier.size(); attribute++) { for (int occurrences = 0; occurrences < classifier.get(attribute).size(); occurrences++) { for (int classification = 0; classification < classifier.get(attribute).get(occurrences).length; classification++) { currRow = worksheet.getRow( (occurrences * classifier.get(attribute).get(occurrences).length + classification) + 1); // +1 since first row is attribute names Cell currCell = currRow.createCell( (attribute) + 1); // TODO figure out why this errors out at i:0 j:4 k:0 // largestAttributeSize:105 on kidney dataset currCell.setCellValue(classifier.get(attribute).get(occurrences)[classification]); } } } workbook.write(fileOut); workbook.close(); workbook.close(); } catch (FileNotFoundException e) { System.out.println("Error file not found"); e.printStackTrace(); System.exit(0); } catch (IOException e) { System.out.println("Unable to output file, is the output destination writelocked?"); e.printStackTrace(); System.exit(0); } }
public void testCorrectOrderInOptRecord() throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); try { HSSFSheet sheet = wb.createSheet(); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFTextbox textbox = patriarch.createTextbox(new HSSFClientAnchor()); EscherOptRecord opt = HSSFTestHelper.getOptRecord(textbox); String opt1Str = opt.toXml(); textbox.setFillColor(textbox.getFillColor()); EscherContainerRecord container = HSSFTestHelper.getEscherContainer(textbox); EscherOptRecord optRecord = container.getChildById(EscherOptRecord.RECORD_ID); assertEquals(opt1Str, optRecord.toXml()); textbox.setLineStyle(textbox.getLineStyle()); assertEquals(opt1Str, optRecord.toXml()); textbox.setLineWidth(textbox.getLineWidth()); assertEquals(opt1Str, optRecord.toXml()); textbox.setLineStyleColor(textbox.getLineStyleColor()); assertEquals(opt1Str, optRecord.toXml()); } finally { wb.close(); } }