List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getSheetName
@Override
public String getSheetName()
From source file:com.accenture.control.ExtraiPlanilha.java
public void extraiConfPlanilha(String diretorio) throws IOException, ClassNotFoundException, SQLException { boolean existedados = false; ManipulaDadosSQLite bdLite = new ManipulaDadosSQLite(); FileInputStream arquivo;/*from w w w .ja v a 2 s.co m*/ arquivo = new FileInputStream(new File(diretorio)); System.out.println(diretorio); XSSFWorkbook workbook = new XSSFWorkbook(arquivo); XSSFSheet sheetConf = workbook.getSheetAt(2); int linha = 7; int numCelula = 1; //Capturando dados da Complexidade na celula A1 int i = 1; CellReference cellReference = new CellReference("A" + i); Row row = sheetConf.getRow(cellReference.getRow()); Cell cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); i++; bdLite.deletaTabelaConf("TB_COMPLEXIDADE"); while (cell != null) { cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); existedados = true; bdLite.insertTabelaConf("TB_COMPLEXIDADE", "DESC_COMPLEXIDADE", cell.getStringCellValue()); i++; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); } bdLite.deletaTabelaConf("TB_AUTOMATIZAVEL"); //Capturando dados Automatizvel na celula A7 i = 7; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); bdLite.deletaTabelaConf("TB_AUTOMATIZAVEL"); i++; while (cell != null) { cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); existedados = true; bdLite.insertTabelaConf("TB_AUTOMATIZAVEL", "DESC_AUTOMATIZAVEL", cell.getStringCellValue()); i++; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); } //Capturando dados type i = 12; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); bdLite.deletaTabelaConf("TB_TYPE"); i++; while (cell != null) { cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); existedados = true; bdLite.insertTabelaConf("TB_TYPE", "DESC_TYPE", cell.getStringCellValue()); i++; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); } //Capturando dados TRG i = 17; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); bdLite.deletaTabelaConf("TB_TRG"); i++; while (cell != null) { cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); existedados = true; bdLite.insertTabelaConf("TB_TRG", "DESC_TRG", cell.getStringCellValue()); i++; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); } //Capturando dados CADEIA i = 17; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); bdLite.deletaTabelaConf("TB_CADEIA"); i++; while (cell != null) { cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); existedados = true; bdLite.insertTabelaConf("TB_CADEIA", "DESC_CADEIA", cell.getStringCellValue()); i++; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); } //Capturando dados TP REQUISITO i = 32; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); bdLite.deletaTabelaConf("TB_TP_REQUISITO"); i++; while (cell != null) { cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); existedados = true; bdLite.insertTabelaConf("TB_TP_REQUISITO", "DESC_TP_REQUISITO", cell.getStringCellValue()); i++; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); } //Capturando dados criao/alterao i = 27; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); bdLite.deletaTabelaConf("TB_CRIACAO"); i++; while (cell != null) { cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); existedados = true; bdLite.insertTabelaConf("TB_CRIACAO", "DESC_CRIACAO", cell.getStringCellValue()); i++; cellReference = new CellReference("A" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); } //Capturando dados SISTEMA MASTER i = 1; cellReference = new CellReference("C" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); bdLite.deletaTabelaConf("TB_SISTEMA_MASTER"); i++; while (cell != null) { cellReference = new CellReference("C" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); existedados = true; bdLite.insertTabelaConf("TB_SISTEMA_MASTER", "DESC_SISTEMA_MASTER", cell.getStringCellValue()); i++; cellReference = new CellReference("C" + i); row = sheetConf.getRow(cellReference.getRow()); if (row == null) { cell = null; } else { cell = row.getCell(cellReference.getCol(), row.RETURN_BLANK_AS_NULL); } } //Capturando dados FUNCIONALIDADE i = 1; cellReference = new CellReference("E" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); bdLite.deletaTabelaConf("TB_FUNCIONALIDADE"); i++; while (cell != null) { cellReference = new CellReference("E" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); existedados = true; bdLite.insertTabelaConf("TB_FUNCIONALIDADE", "DESC_FUNCIONALIDADE", cell.getStringCellValue()); i++; cellReference = new CellReference("E" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); } //Capturando dados CENARIO INTEGRADO i = 1; cellReference = new CellReference("G" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); bdLite.deletaTabelaConf("TB_CENARIO_INTEGRADO"); i++; while (cell != null) { cellReference = new CellReference("G" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); System.out.println(cell.getStringCellValue()); existedados = true; bdLite.insertTabelaConf("TB_CENARIO_INTEGRADO", "DESC_CENARIO_INTEGRADO", cell.getStringCellValue()); i++; cellReference = new CellReference("G" + i); row = sheetConf.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); } if (existedados == true) { bdLite.insertVersaCarga(sheetConf.getSheetName()); } }
From source file:com.axelor.apps.admin.service.ViewDocExportService.java
License:Open Source License
private void updateDocMap(MetaFile docFile) { try {// www . j a v a 2 s . com File doc = MetaFiles.getPath(docFile).toFile(); FileInputStream inSteam = new FileInputStream(doc); XSSFWorkbook book = new XSSFWorkbook(inSteam); oldBook = book; for (XSSFSheet sheet : book) { String lastKey = sheet.getSheetName(); Iterator<Row> rowIter = sheet.rowIterator(); while (rowIter.hasNext()) { Row row = rowIter.next(); String key = null; if (row.getRowNum() == 0) { key = sheet.getSheetName(); } else { String name = getCellValue(row.getCell(4)); if (Strings.isNullOrEmpty(name)) { name = getCellValue(row.getCell(5)); } String type = getCellValue(row.getCell(3)); if (type == null) { continue; } type = type.trim(); key = getCellValue(row.getCell(1)) + "," + getCellValue(row.getCell(2)) + "," + type + "," + name; if (addComment(lastKey, type, row)) { continue; } else { lastKey = key; } } docMap.put(key, row.getRowNum()); } } } catch (IOException e) { e.printStackTrace(); } }
From source file:com.dotosoft.dotoquiz.tools.OldApp.java
License:Apache License
private void processPicasa() { if (DATA_TYPE.EXCEL.toString().equals(settings.getDataType())) { log.info("process data from excel!"); } else if (DATA_TYPE.GOOGLESHEET.toString().equals(settings.getDataType())) { log.info("process data from googlesheet!"); }/* w ww . j a va 2s . c o m*/ // variable for googlesheet GooglesheetClient googlesheetClient = null; WorksheetEntry fullSheet = null; // variable for excel FileInputStream file = null; XSSFWorkbook workbook = null; XSSFSheet sheet = null; // variable for DB Session session = null; Transaction trx = null; APPLICATION_TYPE type = APPLICATION_TYPE.valueOf(settings.getApplicationType()); try { if (APPLICATION_TYPE.DB.toString().equals(settings.getApplicationType())) { session = HibernateUtil.getSessionFactory().openSession(); trx = session.beginTransaction(); } if (DATA_TYPE.EXCEL.toString().equals(settings.getDataType())) { file = new FileInputStream(settings.getSyncDataFile()); workbook = new XSSFWorkbook(file); } else if (DATA_TYPE.GOOGLESHEET.toString().equals(settings.getDataType())) { googlesheetClient = new GooglesheetClient(auth, settings.getSyncDataFile()); } int index = 0; // -------------------------------------------------------------------------- // Extract Achievement ------------------------------------------------------ // -------------------------------------------------------------------------- // parent folder if (!APPLICATION_TYPE.SHOW_COLUMN_HEADER.toString().equals(settings.getApplicationType())) { DataTopicsParser topicAchievement = new DataTopicsParser(QuizParserConstant.ACHIEVEMENT_NAME, QuizParserConstant.EMPTY_STRING, QuizParserConstant.EMPTY_STRING, QuizParserConstant.EMPTY_STRING, QuizParserConstant.ACHIEVEMENT_NAME, QuizParserConstant.ACHIEVEMENT_DESCRIPTION, QuizParserConstant.ACHIEVEMENT_IMAGE_URL, QuizConstant.NO, new java.util.Date(), QuizConstant.SYSTEM_USER, QuizConstant.NO, type); topicAchievement = syncTopicToPicasa(topicAchievement); topicMapByTopicId.put(topicAchievement.getId(), topicAchievement); } List listRow = null; for (String achievementTab : settings.getStructure().getTabAchievements().split(";")) { String sheetName = ""; if (DATA_TYPE.EXCEL.toString().equals(settings.getDataType())) { sheet = workbook.getSheetAt(Integer.parseInt(achievementTab)); listRow = Lists.newArrayList(sheet.iterator()); sheetName = sheet.getSheetName(); } else if (DATA_TYPE.GOOGLESHEET.toString().equals(settings.getDataType())) { fullSheet = (WorksheetEntry) googlesheetClient.getWorksheet(Integer.parseInt(achievementTab)); listRow = googlesheetClient.getListRows(fullSheet); sheetName = fullSheet.getTitle().getPlainText(); } for (Object row : listRow) { if (showColumnHeader(row, sheetName)) break; ParameterAchievementParser achievement = DotoQuizStructure.convertDataToAchievement(row, settings); if (achievement != null) { if (type == APPLICATION_TYPE.DB) { session.saveOrUpdate(achievement.toParameterAchievements()); log.info("Save or update achievement: " + achievement); } else if (type == APPLICATION_TYPE.SYNC) { achievement = syncAchievementToPicasa(achievement); if (!QuizConstant.YES.equals(achievement.getIsProcessed())) { GooglesheetClient.updateSyncPicasa(settings, QuizParserConstant.PARSE_ACHIEVEMENT, row, achievement.getPicasaId(), achievement.getImagePicasaUrl(), QuizConstant.YES); } } } } } trx = CommitDB(trx, session, true); for (String dataTab : settings.getStructure().getTabTopics().split(";")) { // -------------------------------------------------------------------------- // Extract Topic // -------------------------------------------------------------------------- index = 0; String sheetName = ""; if (DATA_TYPE.EXCEL.toString().equals(settings.getDataType())) { sheet = workbook.getSheetAt(Integer.parseInt(dataTab)); listRow = Lists.newArrayList(sheet.iterator()); sheetName = sheet.getSheetName(); } else if (DATA_TYPE.GOOGLESHEET.toString().equals(settings.getDataType())) { fullSheet = (WorksheetEntry) googlesheetClient.getWorksheet(Integer.parseInt(dataTab)); listRow = googlesheetClient.getListRows(fullSheet); sheetName = fullSheet.getTitle().getPlainText(); } for (Object row : listRow) { if (showColumnHeader(row, sheetName)) break; DataTopicsParser topic = DotoQuizStructure.convertDataToTopics(row, settings); if (topic != null) { if (type == APPLICATION_TYPE.DB) { if (StringUtils.hasValue(topic.getTopicParentId())) { topic.setDatTopics(topicMapByTopicId.get(topic.getTopicParentId()).toDataTopics()); } session.saveOrUpdate(topic.toDataTopics()); log.info("Save or update topic: " + topic); } else if (type == APPLICATION_TYPE.SYNC) { topic = syncTopicToPicasa(topic); if (!QuizConstant.YES.equals(topic.getIsProcessed())) { GooglesheetClient.updateSyncPicasa(settings, QuizParserConstant.PARSE_TOPIC, row, topic.getPicasaId(), topic.getImagePicasaUrl(), QuizConstant.YES); } } topicMapByTopicId.put(topic.getId(), topic); } } } trx = CommitDB(trx, session, true); for (String dataTab : settings.getStructure().getTabQuestions().split(";")) { // -------------------------------------------------------------------------- // Extract QuestionAnswers // -------------------------------------------------------------------------- index = 0; String sheetName = ""; if (DATA_TYPE.EXCEL.toString().equals(settings.getDataType())) { sheet = workbook.getSheetAt(Integer.parseInt(dataTab)); listRow = Lists.newArrayList(sheet.iterator()); sheetName = sheet.getSheetName(); } else if (DATA_TYPE.GOOGLESHEET.toString().equals(settings.getDataType())) { fullSheet = (WorksheetEntry) googlesheetClient.getWorksheet(Integer.parseInt(dataTab)); listRow = googlesheetClient.getListRows(fullSheet); sheetName = fullSheet.getTitle().getPlainText(); } for (Object row : listRow) { if (showColumnHeader(row, sheetName)) break; DataQuestionsParser questionAnswer = DotoQuizStructure.convertDataToAnswerQuestion(row, settings); if (questionAnswer != null) { if (type == APPLICATION_TYPE.DB) { questionAnswer.setMtQuestionType(HibernateUtil.getQuestionTypeByName(session, questionAnswer.getQuestionTypeData())); session.saveOrUpdate(questionAnswer.toDataQuestion()); log.info("Save or update QuestionAnswers: " + questionAnswer); for (String topicId : questionAnswer.getTopics()) { DataTopicsParser datTopic = topicMapByTopicId.get(topicId); HibernateUtil.saveOrUpdateTopicQuestionData(session, datTopic.toDataTopics(), questionAnswer.toDataQuestion()); } } else if (type == APPLICATION_TYPE.SYNC) { questionAnswer = syncQuestionAnswersToPicasa(questionAnswer); if (!QuizConstant.YES.equals(questionAnswer.getIsProcessed())) { GooglesheetClient.updateSyncPicasa(settings, QuizParserConstant.PARSE_QUESTION_ANSWER, row, questionAnswer.getPicasaId(), questionAnswer.getImagePicasaUrl(), QuizConstant.YES); } } } } } trx = CommitDB(trx, session, false); if (DATA_TYPE.EXCEL.toString().equals(settings.getDataType())) { file.close(); log.info("Save data to file..."); FileOutputStream fos = new FileOutputStream(settings.getSyncDataFile()); workbook.write(fos); fos.close(); } log.info("Done"); } catch (Exception e) { trx.rollback(); session.close(); e.printStackTrace(); } System.exit(0); }
From source file:com.hp.autonomy.frontend.reports.powerpoint.PowerPointServiceImpl.java
License:MIT License
/** * Internal implementation to add a sunburst chart (actually a doughnut chart) to a slide, based on a template. * @param template the parsed template information. * @param slide the slide to add to.//from w w w . j a va 2 s .c o m * @param anchor optional bounding rectangle to draw onto, in PowerPoint coordinates. * If null, we'll use the bounds from the original template chart. * @param data the sunburst data. * @param shapeId the slide shape ID, should be unique within the slide. * @param relId the relation ID to the chart data. * @throws TemplateLoadException if we can't create the sunburst; most likely due to an invalid template. */ private static void addSunburst(final SlideShowTemplate template, final XSLFSlide slide, final Rectangle2D.Double anchor, final SunburstData data, final int shapeId, final String relId) throws TemplateLoadException { final String[] categories = data.getCategories(); final double[] values = data.getValues(); final String title = data.getTitle(); slide.getXmlObject().getCSld().getSpTree().addNewGraphicFrame() .set(template.getDoughnutChartShapeXML(relId, shapeId, "chart" + shapeId, anchor)); final XSSFWorkbook workbook = new XSSFWorkbook(); final XSSFSheet sheet = workbook.createSheet(); final XSLFChart baseChart = template.getDoughnutChart(); final CTChartSpace chartSpace = (CTChartSpace) baseChart.getCTChartSpace().copy(); final CTChart ctChart = chartSpace.getChart(); final CTPlotArea plotArea = ctChart.getPlotArea(); if (StringUtils.isEmpty(title)) { if (ctChart.getAutoTitleDeleted() != null) { ctChart.getAutoTitleDeleted().setVal(true); } ctChart.unsetTitle(); } final CTDoughnutChart donutChart = plotArea.getDoughnutChartArray(0); final CTPieSer series = donutChart.getSerArray(0); final CTStrRef strRef = series.getTx().getStrRef(); strRef.getStrCache().getPtArray(0).setV(title); sheet.createRow(0).createCell(1).setCellValue(title); strRef.setF(new CellReference(sheet.getSheetName(), 0, 1, true, true).formatAsString()); final CTStrRef categoryRef = series.getCat().getStrRef(); final CTStrData categoryData = categoryRef.getStrCache(); final CTNumRef numRef = series.getVal().getNumRef(); final CTNumData numericData = numRef.getNumCache(); final String[] fillColors = data.getColors(); final String[] strokeColors = data.getStrokeColors(); final boolean overrideFill = ArrayUtils.isNotEmpty(fillColors); final boolean overrideStroke = ArrayUtils.isNotEmpty(strokeColors); final boolean overrideColors = overrideFill || overrideStroke; final List<CTDPt> dPtList = series.getDPtList(); final CTDPt templatePt = (CTDPt) dPtList.get(0).copy(); if (overrideColors) { dPtList.clear(); final CTShapeProperties spPr = templatePt.getSpPr(); final CTLineProperties ln = spPr.getLn(); // We need to unset any styles on the existing template if (overrideFill) { unsetSpPrFills(spPr); } if (overrideStroke) { unsetLineFills(ln); } } categoryData.setPtArray(null); numericData.setPtArray(null); CTLegend legend = null; final int[] showInLegend = data.getShowInLegend(); int nextLegendToShow = 0, nextLegendToShowIdx = -1; if (showInLegend != null) { // We need to write legendEntry elements to hide the legend for chart series we don't want. // Note this only works in PowerPoint, and not OpenOffice. legend = ctChart.isSetLegend() ? ctChart.getLegend() : ctChart.addNewLegend(); Arrays.sort(showInLegend); nextLegendToShow = showInLegend[++nextLegendToShowIdx]; } for (int idx = 0; idx < values.length; ++idx) { final CTStrVal categoryPoint = categoryData.addNewPt(); categoryPoint.setIdx(idx); categoryPoint.setV(categories[idx]); final CTNumVal numericPoint = numericData.addNewPt(); numericPoint.setIdx(idx); numericPoint.setV(Double.toString(values[idx])); if (overrideColors) { final CTDPt copiedPt = (CTDPt) templatePt.copy(); copiedPt.getIdx().setVal(idx); if (overrideFill) { final Color color = Color.decode(fillColors[idx % fillColors.length]); final CTSolidColorFillProperties fillClr = copiedPt.getSpPr().addNewSolidFill(); fillClr.addNewSrgbClr().setVal( new byte[] { (byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue() }); } if (overrideStroke) { final Color strokeColor = Color.decode(strokeColors[idx % strokeColors.length]); final CTSolidColorFillProperties strokeClr = copiedPt.getSpPr().getLn().addNewSolidFill(); strokeClr.addNewSrgbClr().setVal(new byte[] { (byte) strokeColor.getRed(), (byte) strokeColor.getGreen(), (byte) strokeColor.getBlue() }); } dPtList.add(copiedPt); } if (legend != null) { // We're hiding some legend elements. Should we show this index? if (nextLegendToShow == idx) { // We show this index, find the next one to show. ++nextLegendToShowIdx; if (nextLegendToShowIdx < showInLegend.length) { nextLegendToShow = showInLegend[nextLegendToShowIdx]; } } else { // We hide this index. If there's already a matching legend entry in the XML, update it, // otherwise we create a new legend entry. boolean found = false; for (int ii = 0, max = legend.sizeOfLegendEntryArray(); ii < max; ++ii) { final CTLegendEntry legendEntry = legend.getLegendEntryArray(ii); final CTUnsignedInt idxLegend = legendEntry.getIdx(); if (idxLegend != null && idxLegend.getVal() == idx) { found = true; if (legendEntry.isSetDelete()) { legendEntry.getDelete().setVal(true); } else { legendEntry.addNewDelete().setVal(true); } } } if (!found) { final CTLegendEntry idxLegend = legend.addNewLegendEntry(); idxLegend.addNewIdx().setVal(idx); idxLegend.addNewDelete().setVal(true); } } } XSSFRow row = sheet.createRow(idx + 1); row.createCell(0).setCellValue(categories[idx]); row.createCell(1).setCellValue(values[idx]); } categoryData.getPtCount().setVal(categories.length); numericData.getPtCount().setVal(values.length); categoryRef.setF(new CellRangeAddress(1, values.length, 0, 0).formatAsString(sheet.getSheetName(), true)); numRef.setF(new CellRangeAddress(1, values.length, 1, 1).formatAsString(sheet.getSheetName(), true)); try { writeChart(template.getSlideShow(), slide, baseChart, chartSpace, workbook, relId); } catch (IOException | InvalidFormatException e) { throw new TemplateLoadException("Error writing chart in loaded template", e); } }
From source file:com.hp.autonomy.frontend.reports.powerpoint.PowerPointServiceImpl.java
License:MIT License
/** * Utility function to update a scatterplot line's data series. * @param data the datagraph data./* ww w . j a va2 s . c o m*/ * @param sheet the Excel sheet which contains corresponding data from the scatterplot data series. * @param seriesIdx the index of the data in the dategraph data. * @param series the XML object representing the series in the chart. */ private static void updateCTScatterSer(final DategraphData data, final XSSFSheet sheet, final int seriesIdx, final CTScatterSer series) { final String sheetName = sheet.getSheetName(); // the series idx starts from 0 final DategraphData.Row row = data.getRows().get(seriesIdx); final String title = row.getLabel(); final Color color = Color.decode(row.getColor()); series.getOrder().setVal(seriesIdx); series.getIdx().setVal(seriesIdx); final CTSolidColorFillProperties fill = series.getSpPr().getLn().getSolidFill(); // We have to set any possible colour type, PowerPoint throws an error if there's multiple fills, and we don't // know what colour type the user may have used in their template slide. if (fill.getSchemeClr() != null) { fill.unsetSchemeClr(); } if (fill.getSrgbClr() != null) { fill.unsetSrgbClr(); } if (fill.getHslClr() != null) { fill.unsetHslClr(); } if (fill.getPrstClr() != null) { fill.unsetPrstClr(); } if (fill.getScrgbClr() != null) { fill.unsetScrgbClr(); } if (fill.getSysClr() != null) { fill.unsetSysClr(); } final CTSRgbColor fillClr = fill.addNewSrgbClr(); final byte[] colorBytes = { (byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue() }; fillClr.setVal(colorBytes); final CTMarker marker = series.getMarker(); if (marker != null) { final CTShapeProperties markerSpPr = marker.getSpPr(); unsetSpPrFills(markerSpPr); markerSpPr.addNewSolidFill().addNewSrgbClr().setVal(colorBytes); final CTLineProperties markerLn = markerSpPr.getLn(); if (markerLn != null) { unsetLineFills(markerLn); markerLn.addNewSolidFill().addNewSrgbClr().setVal(colorBytes); } } final CTStrRef strRef = series.getTx().getStrRef(); strRef.getStrCache().getPtArray()[0].setV(title); strRef.setF(new CellReference(sheetName, 0, seriesIdx + 1, true, true).formatAsString()); final long[] timestamps = data.getTimestamps(); { final CTNumRef timestampCatNumRef = series.getXVal().getNumRef(); timestampCatNumRef.setF(new AreaReference(new CellReference(sheetName, 1, 0, true, true), new CellReference(sheetName, 1 + timestamps.length, 0, true, true)).formatAsString()); final CTNumData timeStampCatNumCache = timestampCatNumRef.getNumCache(); timeStampCatNumCache.getPtCount().setVal(timestamps.length); timeStampCatNumCache.setPtArray(null); for (int ii = 0; ii < timestamps.length; ++ii) { final CTNumVal pt = timeStampCatNumCache.addNewPt(); pt.setIdx(ii); pt.setV(sheet.getRow(1 + ii).getCell(0).getRawValue()); } } { final double[] seriesData = row.getValues(); final CTNumRef valuesNumRef = series.getYVal().getNumRef(); valuesNumRef.setF(new AreaReference(new CellReference(sheetName, 1, seriesIdx + 1, true, true), new CellReference(sheetName, 1 + timestamps.length, seriesIdx + 1, true, true)) .formatAsString()); final CTNumData valuesNumCache = valuesNumRef.getNumCache(); valuesNumCache.getPtCount().setVal(timestamps.length); valuesNumCache.setPtArray(null); for (int ii = 0; ii < timestamps.length; ++ii) { final CTNumVal pt = valuesNumCache.addNewPt(); pt.setIdx(ii); pt.setV(Double.toString(seriesData[ii])); } } }
From source file:com.infovity.iep.loader.util.SupplierLoaderUtil.java
public static ArrayList<String[]> getDataFromFile(File inputFile, String sheetName) { ArrayList<String[]> data = new ArrayList<String[]>(); File selectedFile = null;/* w ww . ja v a 2 s. c o m*/ FileInputStream fis = null; ; XSSFWorkbook workbook = null; //inputFile = new File("C:\\Users\\INFOVITY-USER-029\\Desktop\\DataLoader\\Consolidated Supplier Data Capture Template v4.0.xlsx"); boolean sheetFound = false; XSSFSheet sheet = null; try { int sheetIndex = -1; fis = new FileInputStream(inputFile); workbook = new XSSFWorkbook(fis); int noOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < noOfSheets; i++) { sheet = workbook.getSheetAt(i); if (sheet.getSheetName().equals(sheetName)) { sheetFound = true; sheetIndex = i; selectedFile = inputFile; break; } } XSSFWorkbook myWorkBook; try { myWorkBook = new XSSFWorkbook(selectedFile); // Return first sheet from the XLSX workbook // XSSFSheet mySheet = myWorkBook.getSheetAt(0); // Get iterator to all the rows in current sheet Iterator<Row> rowIterator = sheet.iterator(); String query; String[] values = null; // Traversing over each row of XLSX file while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (rowHasData(row) && (row.getRowNum() >= 9)) { int endColumn = row.getLastCellNum(); int startColumn = row.getFirstCellNum(); // For each row, iterate through each columns values = new String[endColumn + 2]; for (int i = startColumn; i < endColumn; i++) { String cellValue; Cell cell = row.getCell(i); values[0] = Integer.toString(row.getRowNum() + 1); if (cell != null) { if (cell.getCellType() == cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)) { DateFormat df = new SimpleDateFormat("yyyy/MM/dd"); Date cellDateValue = cell.getDateCellValue(); cellValue = df.format(cellDateValue); } else { cell.setCellType(cell.CELL_TYPE_STRING); cellValue = cell.getStringCellValue().replaceAll("'", ""); } if (!"".equals(cellValue) && cellValue != null) { values[i + 1] = cellValue; } else if (cellValue.isEmpty() || "".equals(cellValue)) { values[i + 1] = ""; } } else { values[i + 1] = ""; } } data.add(values); } } } catch (InvalidFormatException ex) { Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex); } } catch (IOException ex) { Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex); } finally { try { fis.close(); workbook.close(); } catch (IOException ex) { Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex); } } return data; }
From source file:com.niles.excel2json.objects.ExcelFile.java
License:Open Source License
public void process() throws Exception { File excel = new File(path); FileInputStream fis = SystemTools.getFileInputStream(excel); XSSFWorkbook wb = null;//from ww w . j av a 2s . co m try { wb = new XSSFWorkbook(fis); } catch (IOException ex) { logger.error("Unable to process file [{}]\r\n{}", path, ex.getMessage()); return; } this.fileName = excel.getName().replace(StringConstants.XLSX, ""); this.folderPath = fileName + " Files"; folderPath = SystemTools.createFolder(folderPath); int sheetCount = wb.getNumberOfSheets(); int currentSheetNumber = 0; int rowCount = 0; int columnCount = 0; HashMap<Integer, String> headers = new HashMap<Integer, String>(); /* * Itterate through the Excel sheets here and convert them to JSON */ while (currentSheetNumber < sheetCount) { XSSFSheet current = wb.getSheetAt(currentSheetNumber); sheetName = current.getSheetName(); // System.out.println(sheetName); if (current.getRow(0) == null) { // logger.error("[{}] Sheet contains no data", sheetName); } else { //logger.info("[{}] Processing sheet", sheetName); rowCount = current.getLastRowNum() + 1; columnCount = current.getRow(0).getPhysicalNumberOfCells(); // System.out.println("Col Count: " + columnCount); // System.out.println("Row Count: " + rowCount); JSONArray myJSONArray = new JSONArray(); JSONObject currentJSONObject = null; for (int a = 0; a < rowCount; a++) { XSSFRow currentRow = current.getRow(a); if (a == 0) { logger.info("[{}] Loading header information", sheetName); for (int currentCellNumber = 0; currentCellNumber < columnCount; currentCellNumber++) { XSSFCell currentCell = currentRow.getCell(currentCellNumber); String header = "Header" + currentCellNumber; if (currentCell != null) { if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) { header = currentCell.getStringCellValue(); } if (currentCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { header = currentCell.getNumericCellValue() + ""; } } headers.put(currentCellNumber, header.replaceAll(" ", "")); } } else { currentJSONObject = new JSONObject(); for (int currentCellNumber = 0; currentCellNumber < columnCount; currentCellNumber++) { XSSFCell currentCell = currentRow.getCell(currentCellNumber); String value = ""; if (currentCell != null) { if (currentCell.getCellType() != XSSFCell.CELL_TYPE_ERROR && currentCell.getCellType() != XSSFCell.CELL_TYPE_FORMULA) { if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) { value = currentCell.getStringCellValue(); } if (currentCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { value = currentCell.getNumericCellValue() + ""; } if (currentCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) { value = currentCell.getBooleanCellValue() + ""; } if (value == null) { value = ""; } } } currentJSONObject.put(headers.get(currentCellNumber), value); } myJSONArray.add(currentJSONObject); } } writeToJson(myJSONArray); } currentSheetNumber++; } }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
public boolean deleteTestSuitesFromXLSX(String filePath, String testSuiteName) { boolean hasTrue = false; Iterator<Row> rowIterator; try {/* w w w . j a va 2s .c o m*/ FileInputStream myInput = new FileInputStream(filePath); OPCPackage opc = OPCPackage.open(myInput); XSSFWorkbook myWorkBook = new XSSFWorkbook(opc); XSSFSheet mySheet = myWorkBook.getSheetAt(0); rowIterator = mySheet.rowIterator(); for (int i = 0; i <= 2; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next = rowIterator.next(); if (getValue(next.getCell(2)).equalsIgnoreCase(testSuiteName)) { mySheet.removeRow(next); int rowNum = next.getRowNum(); int newNum = rowNum + 1; mySheet.shiftRows(newNum, mySheet.getLastRowNum(), -1); int numberOfSheets = myWorkBook.getNumberOfSheets(); for (int j = 0; j < numberOfSheets; j++) { XSSFSheet mySheet1 = myWorkBook.getSheetAt(j); if (mySheet1.getSheetName().equalsIgnoreCase(testSuiteName)) { myWorkBook.removeSheetAt(j); hasTrue = true; break; } } myInput.close(); FileOutputStream outFile = new FileOutputStream(filePath); myWorkBook.write(outFile); outFile.close(); break; } } } catch (Exception e) { } return hasTrue; }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
public boolean deleteTestCasesFromXLSX(String filePath, String testSuiteName, String testCaseId) { boolean hasTrue = false; List<TestCase> testCases = new ArrayList<TestCase>(); TestCase tstCase = new TestCase(); Iterator<Row> rowIterator; try {//from w ww.j av a2 s. c o m FileInputStream myInput = new FileInputStream(filePath); OPCPackage opc = OPCPackage.open(myInput); XSSFWorkbook myWorkBook = new XSSFWorkbook(opc); int numberOfSheets = myWorkBook.getNumberOfSheets(); for (int j = 0; j < numberOfSheets; j++) { XSSFSheet mySheet = myWorkBook.getSheetAt(j); if (mySheet.getSheetName().equals(testSuiteName)) { rowIterator = mySheet.rowIterator(); for (int i = 0; i <= 23; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next = rowIterator.next(); if (testCaseId.equalsIgnoreCase(getValue(next.getCell(3)))) { mySheet.removeRow(next); int rowNum = next.getRowNum(); int newNum = rowNum + 1; XSSFRow row = mySheet.getRow(newNum); if (row != null) { mySheet.shiftRows(newNum, mySheet.getLastRowNum(), -1); } hasTrue = true; break; } } } } if (hasTrue) { for (int j = 0; j < numberOfSheets; j++) { XSSFSheet myXSSFSheet = myWorkBook.getSheetAt(j); if (myXSSFSheet.getSheetName().equals(testSuiteName)) { rowIterator = myXSSFSheet.rowIterator(); for (int i = 0; i <= 23; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next = rowIterator.next(); TestCase createObject = readTest(next); testCases.add(createObject); } float totalPass = 0; float totalFail = 0; float totalNotApplicable = 0; float totalBlocked = 0; int totalTestCases = testCases.size(); for (TestCase testCase : testCases) { String testCaseStatus = testCase.getStatus(); if (testCaseStatus.equalsIgnoreCase("success")) { totalPass = totalPass + 1; } else if (testCaseStatus.equalsIgnoreCase("failure")) { totalFail = totalFail + 1; } else if (testCaseStatus.equalsIgnoreCase("notApplicable")) { totalNotApplicable = totalNotApplicable + 1; } else if (testCaseStatus.equalsIgnoreCase("blocked")) { totalBlocked = totalBlocked + 1; } } if (tstCase.getStatus().equalsIgnoreCase("success")) { totalPass = totalPass - 1; } else if (tstCase.getStatus().equalsIgnoreCase("failure")) { totalFail = totalFail - 1; } else if (tstCase.getStatus().equalsIgnoreCase("notApplicable")) { totalNotApplicable = totalNotApplicable - 1; } else if (tstCase.getStatus().equalsIgnoreCase("blocked")) { totalBlocked = totalBlocked - 1; } XSSFSheet mySheet1 = myWorkBook.getSheetAt(0); rowIterator = mySheet1.rowIterator(); for (int i = 0; i <= 2; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next1 = rowIterator.next(); if (StringUtils.isNotEmpty(getValue(next1.getCell(2))) && !getValue(next1.getCell(2)).equalsIgnoreCase("Total")) { TestSuite createObject = createObject(next1); if (StringUtils.isNotEmpty(tstCase.getTestCaseId()) && createObject.getName().equals(testSuiteName)) { updateIndex(totalPass, totalFail, totalNotApplicable, totalBlocked, next1, totalTestCases, "delete"); } } } } } myInput.close(); FileOutputStream outFile = new FileOutputStream(filePath); myWorkBook.write(outFile); outFile.close(); } } catch (Exception e) { } return hasTrue; }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
private void updateTestCaseToXLSX(String fileName, com.photon.phresco.commons.model.TestCase tstCase, List<TestCase> testCases, StringBuilder sb) throws FileNotFoundException, InvalidFormatException, IOException, UnknownHostException, PhrescoException { FileInputStream myInput = new FileInputStream(sb.toString()); OPCPackage opc = OPCPackage.open(myInput); XSSFWorkbook myWorkBook = new XSSFWorkbook(opc); int numberOfSheets = myWorkBook.getNumberOfSheets(); for (int j = 0; j < numberOfSheets; j++) { XSSFSheet mySheet = myWorkBook.getSheetAt(j); if (mySheet.getSheetName().equals(fileName)) { Iterator<Row> rowIterator = mySheet.rowIterator(); for (int i = 0; i <= 23; i++) { rowIterator.next();//from w ww . ja va2 s . co m } while (rowIterator.hasNext()) { Row next = rowIterator.next(); if (StringUtils.isNotEmpty(getValue(next.getCell(1)))) { TestCase createObject = readTest(next); testCases.add(createObject); if (tstCase != null && createObject.getTestCaseId().equals(tstCase.getTestCaseId())) { Cell preConditionsCell = next.getCell(5); preConditionsCell.setCellValue(tstCase.getPreconditions()); Cell stepsCell = next.getCell(6); stepsCell.setCellValue(tstCase.getSteps()); Cell expectedCell = next.getCell(9); expectedCell.setCellValue(tstCase.getExpectedResult()); Cell actualCell = next.getCell(10); actualCell.setCellValue(tstCase.getActualResult()); Cell statusCell = next.getCell(11); statusCell.setCellValue(tstCase.getStatus()); Cell commentCell = next.getCell(12); commentCell.setCellValue(tstCase.getBugComment()); } } } if (StringUtils.isNotEmpty(tstCase.getTestCaseId())) { float totalPass = 0; float totalFail = 0; float totalNotApplicable = 0; float totalBlocked = 0; for (TestCase testCase : testCases) { String testCaseStatus = testCase.getStatus(); String testId = tstCase.getTestCaseId(); String status = tstCase.getStatus(); if (testCaseStatus.equalsIgnoreCase("success") && !testCase.getTestCaseId().equalsIgnoreCase(testId)) { totalPass = totalPass + 1; } else if (testCaseStatus.equalsIgnoreCase("failure") && !testCase.getTestCaseId().equalsIgnoreCase(testId)) { totalFail = totalFail + 1; } else if (testCaseStatus.equalsIgnoreCase("notApplicable") && !testCase.getTestCaseId().equalsIgnoreCase(testId)) { totalNotApplicable = totalNotApplicable + 1; } else if (testCaseStatus.equalsIgnoreCase("blocked") && !testCase.getTestCaseId().equalsIgnoreCase(testId)) { totalBlocked = totalBlocked + 1; } if (testCase.getTestCaseId().equals(testId) && !testCase.getStatus().equalsIgnoreCase("success") && status.equalsIgnoreCase("success")) { totalPass = totalPass + 1; } else if (testCase.getTestCaseId().equals(testId) && !testCase.getStatus().equalsIgnoreCase("failure") && status.equalsIgnoreCase("failure")) { totalFail = totalFail + 1; } else if (testCase.getTestCaseId().equals(testId) && !testCase.getStatus().equalsIgnoreCase("notApplicable") && status.equalsIgnoreCase("notApplicable")) { totalNotApplicable = totalNotApplicable + 1; } else if (testCase.getTestCaseId().equals(testId) && !testCase.getStatus().equalsIgnoreCase("blocked") && status.equalsIgnoreCase("blocked")) { totalBlocked = totalBlocked + 1; } XSSFSheet mySheet1 = myWorkBook.getSheetAt(0); rowIterator = mySheet1.rowIterator(); for (int i = 0; i <= 2; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next1 = rowIterator.next(); if (StringUtils.isNotEmpty(getValue(next1.getCell(2))) && !getValue(next1.getCell(2)).equalsIgnoreCase("Total")) { TestSuite createObject = createObject(next1); if (StringUtils.isNotEmpty(testId) && createObject.getName().equals(fileName)) { updateIndex(totalPass, totalFail, totalNotApplicable, totalBlocked, next1, 0, ""); } } } } } if (StringUtils.isNotEmpty(tstCase.getTestCaseId())) { myInput.close(); FileOutputStream outFile = new FileOutputStream(sb.toString()); myWorkBook.write(outFile); outFile.close(); } } } }