List of usage examples for org.apache.poi.ss.usermodel Cell getColumnIndex
int getColumnIndex();
From source file:org.patientview.radar.util.RadarPhase2ExcelDataToSqlMapper.java
License:Open Source License
public static void main(String params[]) { InputStream inp = null;//from w w w. ja va 2s .com try { /************** 1. first create the prd codes sql ***************/ // this is the file from radar originally called ERA_EDTA_new_PRD_codes_27042012_def for NDT_RADAR inp = new FileInputStream(BASE_PATH + "input/prd_codes_and_working_group.xls"); Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(0); // each list item represents a row to insert List<List<String>> dataList = new ArrayList<List<String>>(); //iterate through the rows in excel file for (Row row : sheet) { // ignore non data rows if (row.getRowNum() < FIRST_DATA_ROW) { continue; } else if (row.getRowNum() > LAST_DATA_ROW) { break; } List<String> values = new ArrayList<String>(); // iterate through cells for (int cn = 0; cn < row.getLastCellNum(); cn++) { Cell cell = row.getCell(cn); // ignore non data cells if (cn > 23) { break; } String value = ""; if (cell != null) { cell.setCellType(Cell.CELL_TYPE_STRING); value = cell.getStringCellValue(); } // convert x values to 1 which means true, or blank to 0 which means false if ((cn >= FIRST_BOOLEAN_FIELD && cn <= LAST_BOOLEAN_FIELD)) { value = value.equals("x") ? "1" : "0"; } values.add(value); } dataList.add(values); } StringBuilder outputText = new StringBuilder(); StringBuilder prdSql = new StringBuilder(); String sqlBaseInsert = "INSERT INTO rdr_prd_code(ERA_EDTA_PRD_code, ERA_EDTA_primaryRenalDiagnosisTerm, " + "histology, clinicalHistory, familyHistory, clinicalExam, biochemistry, immunology, " + "urineAnalysis, " + "imaging, geneTest, otherCriteriaAndNotes, " + "SNOMED_CT_conceptIdentifierForFocusConcept, " + "SNOMED_CT_fullySpecifiedName, " + "SNOMED_CT_expressionConstraint, majorHeading, mappingToOldPRDCode, " + "mappingToOldPRDTerm, ERA_EDTA_defaultSortOrder, geneticsHomeReferenceLink, " + "nationalCenterForBiotechnologyLink, ICD_10_code, ICD10_rubricTerm, alternativesearchTerms) " + "VALUES ("; int index = 0; // for each row in the data list create an sql insert statement for (List<String> row : dataList) { String sqlInsert = sqlBaseInsert; int valueIndex = 0; for (String value : row) { value = value.replace("'", "").replace("\"", ""); sqlInsert += "'" + value + "'" + (valueIndex != row.size() - 1 ? "," : ""); valueIndex++; } sqlInsert += ");" + System.getProperty("line.separator"); prdSql.append(sqlInsert); index++; } // append to output text - output text will eventually be written to a file outputText.append(prdSql + System.getProperty("line.separator")); /************** 2. create the working groups sql ***************/ Row row = sheet.getRow(1); List<String> workingGroups = new ArrayList<String>(); // iterate through all working groups for (Cell cell : row) { if (cell.getColumnIndex() < FIRST_WORKING_GROUP_INDEX) { continue; } else if (cell.getColumnIndex() > LAST_WORKING_GROUP_INDEX) { break; } cell.setCellType(Cell.CELL_TYPE_STRING); String value = cell.getStringCellValue(); value = value.replace("'", "\\'"); workingGroups.add(value); } // create sql for working groups sql insert String workingGroupSql = "" + System.getProperty("line.separator"); int workingGroupIndex = 0; for (String workingGroup : workingGroups) { String unitCode = workingGroup.split(" ")[0] + workingGroupIndex; // this is id, has to be unique workingGroupSql += "INSERT INTO unit(unitcode, name, shortName, sourceType) VALUES('" + unitCode + "', '" + workingGroup + "', '','radargroup');" + System.getProperty("line.separator"); workingGroupIndex++; } // append to output text - output text will eventually be written to a file outputText.append(workingGroupSql + System.getProperty("line.separator")); /************** 3. create the mapping table sql - this is the tricky bit! ***************/ List<List<String>> mappingData = new ArrayList<List<String>>(); // for each working group collect mapping values to working group for (int columnIndex = FIRST_WORKING_GROUP_INDEX; columnIndex < LAST_WORKING_GROUP_INDEX; columnIndex++) { List<String> list = new ArrayList<String>(); for (int rowIndex = FIRST_DATA_ROW; rowIndex <= LAST_DATA_ROW; rowIndex++) { Row mappingRow = sheet.getRow(rowIndex); Cell cell = mappingRow.getCell(columnIndex); String value = "0"; if (cell != null) { cell.setCellType(Cell.CELL_TYPE_STRING); value = cell.getStringCellValue(); } list.add(value); } mappingData.add(list); } // create list of prd ids List<String> prdIds = new ArrayList<String>(); for (int i = FIRST_DATA_ROW; i <= LAST_DATA_ROW; i++) { Row aRow = sheet.getRow(i); Cell cell = aRow.getCell(0); cell.setCellType(Cell.CELL_TYPE_STRING); String value = cell.getStringCellValue(); prdIds.add(value); } // create sql insert statements based on where working group and disease intersect String mappingSql = ""; String baseSql = "INSERT INTO rdr_diagnosis_mapping(workingGroup, PRDCode, ordering) VALUES("; for (int i = 0; i < mappingData.size(); i++) { String sql = ""; List<String> list = mappingData.get(i); for (int j = 0; j < list.size(); j++) { sql = baseSql; String value = list.get(j); if (!value.equals("0")) { sql += "'" + (workingGroups.get(i).split(" ")[0] + i) + "', '" + prdIds.get(j) + "','" + value + "');"; if (!sql.equals(baseSql)) { mappingSql += sql + System.getProperty("line.separator"); } } } } outputText.append(mappingSql); // output all sql stuff to file FileWriter fileWriter = new FileWriter(BASE_PATH + "output/phase2Data.sql"); BufferedWriter bufferedWriter = new BufferedWriter(fileWriter); bufferedWriter.write(outputText.toString()); //Close the output stream bufferedWriter.close(); } catch (Exception e) { //To change body of catch statement use File | Settings | File Templates. LOGGER.error(e.getMessage()); LOGGER.debug(e.getMessage(), e); } }
From source file:org.paxml.table.excel.ExcelCell.java
License:Open Source License
ExcelCell(Cell cell, ExcelRow row) { this.cell = cell; this.index = cell.getColumnIndex(); setRow(row); }
From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.java
License:Apache License
/** * Set specified cell format// ww w . j ava 2 s .c o m * * @param excelFieldFormat the specified format * @param cell the cell to set up format */ private void setDataFormat(String excelFieldFormat, Cell cell) { if (log.isDebug()) { logDebug(BaseMessages.getString(PKG, "ExcelWriterStep.Log.SetDataFormat", excelFieldFormat, CellReference.convertNumToColString(cell.getColumnIndex()), cell.getRowIndex())); } DataFormat format = data.wb.createDataFormat(); short formatIndex = format.getFormat(excelFieldFormat); CellStyle style = data.wb.createCellStyle(); style.cloneStyleFrom(cell.getCellStyle()); style.setDataFormat(formatIndex); cell.setCellStyle(style); }
From source file:org.pharmgkb.ItpcSheet.java
License:LGPL
protected void parseColumnIndexes() throws Exception { if (sf_logger.isDebugEnabled()) { sf_logger.debug("Parsing column indexes and headings"); }/* ww w . ja v a 2 s .com*/ Row headerRow = m_dataSheet.getRow(0); Iterator<Cell> headerCells = headerRow.cellIterator(); while (headerCells.hasNext()) { Cell headerCell = headerCells.next(); String header = headerCell.getStringCellValue(); int idx = headerCell.getColumnIndex(); for (Pattern pattern : sf_medPatterns.keySet()) { if (pattern.matcher(header).matches()) { medIdx.put(sf_medPatterns.get(pattern), idx); } } if (StringUtils.isNotEmpty(header)) { header = header.trim().toLowerCase(); } if (header.contains("subject id")) { subjectId = idx; } else if (header.equalsIgnoreCase("project site")) { projectSiteIdx = idx; } else if (header.contains("gender")) { genderIdx = idx; } else if (header.contains("age at diagnosis")) { ageIdx = idx; } else if (header.contains("race") && header.contains("omb")) { raceIdx = idx; } else if (header.equalsIgnoreCase("Metastatic Disease at Primary Disease")) { metastaticIdx = idx; } else if (header.contains("maximum dimension of tumor")) { tumorDimensionIdx = idx; } else if (header.equalsIgnoreCase("Number of Positive Nodes")) { numPositiveNodesIdx = idx; } else if (header.equalsIgnoreCase("Nottingham Grade")) { tumorGradingIdx = idx; } else if (header.equalsIgnoreCase("Progesterone Receptor")) { pgrStatusIdx = idx; } else if (header.equalsIgnoreCase("Radiation Treatment")) { radioIdx = idx; } else if (header.contains("menopause status at diagnosis")) { menoStatusIdx = idx; } else if (header.equals("estrogen receptor")) { erStatusIdx = idx; } else if (header.contains("intended tamoxifen duration")) { durationIdx = idx; } else if (header.contains("intended tamoxifen dose")) { tamoxDoseIdx = idx; } else if (header.contains("if tumor or tissue was dna source")) { tumorSourceIdx = idx; } else if (header.contains("blood or buccal cells")) { bloodSourceIdx = idx; } else if (header.contains("prior history of cancer")) { priorHistoryIdx = idx; } else if (header.contains("sites of prior cancer")) { priorSitesIdx = idx; } else if (header.contains("prior invasive breast cancer or dcis")) { priorDcisIdx = idx; } else if (header.equalsIgnoreCase("chemotherapy")) { chemoIdx = idx; } else if (header.contains("additional hormone or other treatment after breast surgery?")) { hormoneIdx = idx; } else if (header.contains("systemic therapy prior to surgery?")) { systemicTherIdx = idx; } else if (header.contains("annual physical exam after breast cancer surgery")) { followupIdx = idx; } else if (header.contains("time between definitive breast cancer surgery")) { timeBtwSurgTamoxIdx = idx; } else if (header.contains("first adjuvant endocrine therapy")) { firstAdjEndoTherIdx = idx; } else if (header.contains("project notes")) { projectNotesIdx = idx; } else if (header.equalsIgnoreCase("other cyp2d6 genotyping")) { otherGenoIdx = idx; } else if (header.contains("rs4986774") && !header.contains("source")) { rs4986774idx = idx; } else if (header.contains("rs1065852") && !header.contains("source")) { rs1065852idx = idx; } else if (header.contains("rs3892097") && !header.contains("source")) { rs3892097idx = idx; } else if (header.contains("rs5030655") && !header.contains("source")) { rs5030655idx = idx; } else if (header.contains("rs16947") && !header.contains("source")) { rs16947idx = idx; } else if (header.contains("rs28371706") && !header.contains("source")) { rs28371706idx = idx; } else if (header.contains("rs28371725") && !header.contains("source")) { rs28371725idx = idx; } else if (genotypeSourceHeaderTitles.contains(header)) { sampleSourceIdxs.add(idx); } else if (header.contains("cyp2d6 *5") && !header.contains("source")) { star5idx = idx; } else if (header.contains("fluoxetine")) { fluoxetineCol = idx; } else if (header.contains("paroxetine")) { paroxetineCol = idx; } else if (header.contains("quinidine")) { quinidienCol = idx; } else if (header.contains("buproprion")) { buproprionCol = idx; } else if (header.contains("duloxetine")) { duloxetineCol = idx; } else if (header.contains("cimetidine")) { cimetidineCol = idx; } else if (header.contains("sertraline")) { sertralineCol = idx; } else if (header.equals("citalopram")) { citalopramCol = idx; } else if (header.contains("amplichip call")) { amplichipidx = idx; } else if (header.equalsIgnoreCase("Additional cancer?")) { // column BP additionalCancerIdx = idx; } else if (header.contains("time from diagnosis to ipsilateral local or regional recurrence")) { // column BR addCxIpsilateralIdx = idx; } else if (header.contains("time from diagnosis to distant recurrence")) { // column BS addCxDistantRecurIdx = idx; } else if (header.contains("time from diagnosis to contralateral breast cancer")) { // column BT addCxContralateralIdx = idx; } else if (header.contains("time from diagnosis to second primary invasive cancer")) { // column BU addCxSecondInvasiveIdx = idx; } else if (header.contains("time from diagnosis to date of last disease evaluation")) { // column BX addCxLastEvalIdx = idx; } else if (header.equalsIgnoreCase("Time from diagnosis until death if the patient has died")) { // column CE daysDiagToDeathIdx = idx; } else if (header.equalsIgnoreCase("Has the patient died?")) { // column CD patientDiedIdx = idx; } else if (header.contains("enter disease-free survival time")) { // column BO diseaseFreeSurvivalTimeIdx = idx; } else if (header.contains("survival time if patient has not died")) { // column CI survivalNotDiedIdx = idx; } else if (header.equalsIgnoreCase("Cause of death if the patient has died")) { causeOfDeathIdx = idx; } } // new columns to add to the end of the template int startPgkbColsIdx = projectNotesIdx + 1; newFirstDiseaseEventIdx = startPgkbColsIdx; diagToEventCalcIdx = startPgkbColsIdx + 1; allele1finalIdx = startPgkbColsIdx + 2; allele2finalIdx = startPgkbColsIdx + 3; genotypeIdx = startPgkbColsIdx + 4; genoMetabStatusIdx = startPgkbColsIdx + 5; weakIdx = startPgkbColsIdx + 6; potentIdx = startPgkbColsIdx + 7; scoreIdx = startPgkbColsIdx + 8; metabStatusIdx = startPgkbColsIdx + 9; incAgeIdx = startPgkbColsIdx + 10; incNonmetaIdx = startPgkbColsIdx + 11; incPriorHistIdx = startPgkbColsIdx + 12; incErPosIdx = startPgkbColsIdx + 13; incSysTherIdx = startPgkbColsIdx + 14; incAdjTamoxIdx = startPgkbColsIdx + 15; incDurationIdx = startPgkbColsIdx + 16; incTamoxDoseIdx = startPgkbColsIdx + 17; incChemoIdx = startPgkbColsIdx + 18; incHormoneIdx = startPgkbColsIdx + 19; incDnaCollectionIdx = startPgkbColsIdx + 20; incFollowupIdx = startPgkbColsIdx + 21; incGenoDataAvailIdx = startPgkbColsIdx + 22; exclude1Idx = startPgkbColsIdx + 23; exclude2Idx = startPgkbColsIdx + 24; exclude3Idx = startPgkbColsIdx + 25; exclude4Idx = startPgkbColsIdx + 26; includeCrit1Idx = startPgkbColsIdx + 27; includeCrit2Idx = startPgkbColsIdx + 28; includeCrit3Idx = startPgkbColsIdx + 29; bfciIdx = startPgkbColsIdx + 30; genoSourceIdx = startPgkbColsIdx + 31; writeCellTitles(headerRow); styleCells(headerRow, startPgkbColsIdx, headerRow.getCell(0).getCellStyle()); // write the description row Row descrRow = m_dataSheet.getRow(1); writeCellDescr(descrRow); styleCells(descrRow, startPgkbColsIdx, descrRow.getCell(0).getCellStyle()); }
From source file:org.pharmgkb.ItpcSheet.java
License:LGPL
/** * Styles the given row with the Title Style specified in <code>getTitleStyle</code>. The <code>startIndex</code> * parameter specifies which column column to start applying the style on (0 = all columns) inclusively. * @param row an Excel Row//from www . ja v a2 s. c o m * @param startIndex the index of the column to start applying the style on * @param style the CellStyle to apply */ public void styleCells(Row row, int startIndex, CellStyle style) { Iterator<Cell> headerCells = row.cellIterator(); while (headerCells.hasNext()) { Cell headerCell = headerCells.next(); if (headerCell.getColumnIndex() >= startIndex) { headerCell.setCellStyle(style); } } }
From source file:org.sleuthkit.autopsy.report.ReportXLS.java
License:Apache License
@Override public String generateReport(ReportConfiguration reportconfig) throws ReportModuleException { config = reportconfig;//from w ww . j ava 2 s . co m ReportGen reportobj = new ReportGen(); reportobj.populateReport(reportconfig); HashMap<BlackboardArtifact, ArrayList<BlackboardAttribute>> report = reportobj.Results; Workbook wbtemp = new XSSFWorkbook(); int countGen = 0; int countBookmark = 0; int countCookie = 0; int countHistory = 0; int countDownload = 0; int countRecentObjects = 0; int countTrackPoint = 0; int countInstalled = 0; int countKeyword = 0; int countHash = 0; int countDevice = 0; int countEmail = 0; for (Entry<BlackboardArtifact, ArrayList<BlackboardAttribute>> entry : report.entrySet()) { if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_GEN_INFO.getTypeID()) { countGen++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_BOOKMARK .getTypeID()) { countBookmark++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_COOKIE.getTypeID()) { countCookie++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_HISTORY .getTypeID()) { countHistory++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_DOWNLOAD .getTypeID()) { countDownload++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_RECENT_OBJECT .getTypeID()) { countRecentObjects++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_TRACKPOINT.getTypeID()) { countTrackPoint++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_INSTALLED_PROG .getTypeID()) { countInstalled++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_KEYWORD_HIT .getTypeID()) { countKeyword++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_HASHSET_HIT .getTypeID()) { countHash++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_DEVICE_ATTACHED .getTypeID()) { countDevice++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_EMAIL_MSG.getTypeID()) { countEmail++; } } try { Case currentCase = Case.getCurrentCase(); // get the most updated case SleuthkitCase skCase = currentCase.getSleuthkitCase(); String caseName = currentCase.getName(); Integer imagecount = currentCase.getImageIDs().length; Integer filesystemcount = currentCase.getRootObjectsCount(); Integer totalfiles = skCase.countFsContentType(TskData.TSK_FS_META_TYPE_ENUM.TSK_FS_META_TYPE_REG); Integer totaldirs = skCase.countFsContentType(TskData.TSK_FS_META_TYPE_ENUM.TSK_FS_META_TYPE_DIR); DateFormat datetimeFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); DateFormat dateFormat = new SimpleDateFormat("MM-dd-yyyy-HH-mm-ss"); Date date = new Date(); String datetime = datetimeFormat.format(date); String datenotime = dateFormat.format(date); //The first summary report page Sheet sheetSummary = wbtemp.createSheet("Summary"); //Generate a sheet per artifact type // Sheet sheetGen = wbtemp.createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_GEN_INFO.getDisplayName()); Sheet sheetHash = wbtemp.createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_HASHSET_HIT.getDisplayName()); Sheet sheetDevice = wbtemp .createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_DEVICE_ATTACHED.getDisplayName()); Sheet sheetInstalled = wbtemp .createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_INSTALLED_PROG.getDisplayName()); Sheet sheetKeyword = wbtemp .createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_KEYWORD_HIT.getDisplayName()); // Sheet sheetTrackpoint = wbtemp.createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_TRACKPOINT.getDisplayName()); Sheet sheetRecent = wbtemp .createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_RECENT_OBJECT.getDisplayName()); Sheet sheetCookie = wbtemp .createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_COOKIE.getDisplayName()); Sheet sheetBookmark = wbtemp .createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_BOOKMARK.getDisplayName()); Sheet sheetDownload = wbtemp .createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_DOWNLOAD.getDisplayName()); Sheet sheetHistory = wbtemp .createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_HISTORY.getDisplayName()); Sheet sheetEmail = wbtemp.createSheet(BlackboardArtifact.ARTIFACT_TYPE.TSK_EMAIL_MSG.getDisplayName()); //Bold/underline cell style for the top header rows CellStyle style = wbtemp.createCellStyle(); style.setBorderBottom((short) 2); Font font = wbtemp.createFont(); font.setFontHeightInPoints((short) 14); font.setFontName("Arial"); font.setBoldweight((short) 2); style.setFont(font); //create 'default' style CellStyle defaultstyle = wbtemp.createCellStyle(); defaultstyle.setBorderBottom((short) 2); Font defaultfont = wbtemp.createFont(); defaultfont.setFontHeightInPoints((short) 14); defaultfont.setFontName("Arial"); defaultfont.setBoldweight((short) 2); defaultstyle.setFont(defaultfont); //create the rows in the worksheet for our records //Create first row and header // sheetGen.createRow(0); // sheetGen.getRow(0).createCell(0).setCellValue("Name"); // sheetGen.getRow(0).createCell(1).setCellValue("Value"); // sheetGen.getRow(0).createCell(2).setCellValue("Date/Time"); sheetSummary.setDefaultColumnStyle(1, defaultstyle); sheetSummary.createRow(0).setRowStyle(style); sheetSummary.getRow(0).createCell(0).setCellValue("Summary Information"); sheetSummary.getRow(0).createCell(1).setCellValue(caseName); //add some basic information sheetSummary.createRow(1).setRowStyle(defaultstyle); sheetSummary.getRow(1).createCell(0).setCellValue("# of Images"); sheetSummary.getRow(1).createCell(1).setCellValue(imagecount); sheetSummary.createRow(2); sheetSummary.getRow(2).createCell(0).setCellValue("Filesystems found"); sheetSummary.getRow(2).createCell(1).setCellValue(imagecount); sheetSummary.createRow(3); sheetSummary.getRow(3).createCell(0).setCellValue("# of Files"); sheetSummary.getRow(3).createCell(1).setCellValue(totalfiles); sheetSummary.createRow(4); sheetSummary.getRow(4).createCell(0).setCellValue("# of Directories"); sheetSummary.getRow(4).createCell(1).setCellValue(totaldirs); sheetSummary.createRow(5); sheetSummary.getRow(5).createCell(0).setCellValue("Date/Time"); sheetSummary.getRow(5).createCell(1).setCellValue(datetime); sheetHash.setDefaultColumnStyle(1, defaultstyle); sheetHash.createRow(0).setRowStyle(style); sheetHash.getRow(0).createCell(0).setCellValue("Name"); sheetHash.getRow(0).createCell(1).setCellValue("Size"); sheetHash.getRow(0).createCell(2).setCellValue("Hashset Name"); sheetDevice.setDefaultColumnStyle(1, defaultstyle); sheetDevice.createRow(0).setRowStyle(style); sheetDevice.getRow(0).createCell(0).setCellValue("Name"); sheetDevice.getRow(0).createCell(1).setCellValue("Serial #"); sheetDevice.getRow(0).createCell(2).setCellValue("Time"); sheetInstalled.setDefaultColumnStyle(1, defaultstyle); sheetInstalled.createRow(0).setRowStyle(style); sheetInstalled.getRow(0).createCell(0).setCellValue("Program Name"); sheetInstalled.getRow(0).createCell(1).setCellValue("Install Date/Time"); sheetKeyword.setDefaultColumnStyle(1, defaultstyle); sheetKeyword.createRow(0).setRowStyle(style); sheetKeyword.getRow(0).createCell(0).setCellValue("Keyword"); sheetKeyword.getRow(0).createCell(1).setCellValue("File Name"); sheetKeyword.getRow(0).createCell(2).setCellValue("Preview"); sheetKeyword.getRow(0).createCell(3).setCellValue("Keyword List"); sheetRecent.setDefaultColumnStyle(1, defaultstyle); sheetRecent.createRow(0).setRowStyle(style); sheetRecent.getRow(0).createCell(0).setCellValue("Name"); sheetRecent.getRow(0).createCell(1).setCellValue("Path"); sheetRecent.getRow(0).createCell(2).setCellValue("Related Shortcut"); sheetCookie.setDefaultColumnStyle(1, defaultstyle); sheetCookie.createRow(0).setRowStyle(style); sheetCookie.getRow(0).createCell(0).setCellValue("URL"); sheetCookie.getRow(0).createCell(1).setCellValue("Date"); sheetCookie.getRow(0).createCell(2).setCellValue("Name"); sheetCookie.getRow(0).createCell(3).setCellValue("Value"); sheetCookie.getRow(0).createCell(4).setCellValue("Program"); sheetBookmark.setDefaultColumnStyle(1, defaultstyle); sheetBookmark.createRow(0).setRowStyle(style); sheetBookmark.getRow(0).createCell(0).setCellValue("URL"); sheetBookmark.getRow(0).createCell(1).setCellValue("Title"); sheetBookmark.getRow(0).createCell(2).setCellValue("Program"); sheetDownload.setDefaultColumnStyle(1, defaultstyle); sheetDownload.createRow(0).setRowStyle(style); sheetDownload.getRow(0).createCell(0).setCellValue("File"); sheetDownload.getRow(0).createCell(1).setCellValue("Source"); sheetDownload.getRow(0).createCell(2).setCellValue("Time"); sheetDownload.getRow(0).createCell(3).setCellValue("Program"); sheetHistory.setDefaultColumnStyle(1, defaultstyle); sheetHistory.createRow(0).setRowStyle(style); sheetHistory.getRow(0).createCell(0).setCellValue("URL"); sheetHistory.getRow(0).createCell(1).setCellValue("Date"); sheetHistory.getRow(0).createCell(2).setCellValue("Referrer"); sheetHistory.getRow(0).createCell(3).setCellValue("Title"); sheetHistory.getRow(0).createCell(4).setCellValue("Program"); sheetEmail.setDefaultColumnStyle(1, defaultstyle); sheetEmail.createRow(0).setRowStyle(style); sheetEmail.getRow(0).createCell(0).setCellValue("From"); sheetEmail.getRow(0).createCell(1).setCellValue("To"); sheetEmail.getRow(0).createCell(2).setCellValue("Subject"); sheetEmail.getRow(0).createCell(3).setCellValue("Date/Time"); sheetEmail.getRow(0).createCell(4).setCellValue("Content"); sheetEmail.getRow(0).createCell(5).setCellValue("CC"); sheetEmail.getRow(0).createCell(6).setCellValue("BCC"); sheetEmail.getRow(0).createCell(7).setCellValue("Path"); for (int i = 0; i < wbtemp.getNumberOfSheets(); i++) { Sheet tempsheet = wbtemp.getSheetAt(i); tempsheet.setAutobreaks(true); for (Row temprow : tempsheet) { for (Cell cell : temprow) { cell.setCellStyle(style); tempsheet.autoSizeColumn(cell.getColumnIndex()); } } } int countedGen = 0; int countedBookmark = 0; int countedCookie = 0; int countedHistory = 0; int countedDownload = 0; int countedRecentObjects = 0; int countedTrackPoint = 0; int countedInstalled = 0; int countedKeyword = 0; int countedHash = 0; int countedDevice = 0; int countedEmail = 0; //start populating the sheets in the workbook for (Entry<BlackboardArtifact, ArrayList<BlackboardAttribute>> entry : report.entrySet()) { if (ReportFilter.cancel == true) { break; } int cc = 0; Long objId = entry.getKey().getObjectID(); AbstractFile file = skCase.getAbstractFileById(objId); String filename = file.getName(); Long filesize = file.getSize(); TreeMap<Integer, String> attributes = new TreeMap<Integer, String>(); // Get all the attributes, line them up to be added. Place empty string placeholders for each attribute type int n; for (n = 1; n <= 36; n++) { attributes.put(n, ""); } for (BlackboardAttribute tempatt : entry.getValue()) { if (ReportFilter.cancel == true) { break; } String value = ""; int type = tempatt.getAttributeTypeID(); if (tempatt.getAttributeTypeID() == BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DATETIME_RCVD .getTypeID() || tempatt.getAttributeTypeID() == BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DATETIME .getTypeID() || tempatt.getAttributeTypeID() == BlackboardAttribute.ATTRIBUTE_TYPE.TSK_LAST_ACCESSED .getTypeID()) { value = new java.text.SimpleDateFormat("MM/dd/yyyy HH:mm:ss") .format(new java.util.Date((tempatt.getValueLong()) * 1000)).toString(); } else { value = tempatt.getValueString(); } attributes.put(type, StringEscapeUtils.escapeXml(value)); cc++; } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_GEN_INFO .getTypeID()) { countedGen++; // Row temp = sheetGen.getRow(countedGen); } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_BOOKMARK .getTypeID()) { countedBookmark++; Row temp = sheetBookmark.createRow(countedBookmark); temp.createCell(0) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_URL.getTypeID())); temp.createCell(1) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_NAME.getTypeID())); temp.createCell(2).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_PROG_NAME.getTypeID())); } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_COOKIE .getTypeID()) { countedCookie++; Row temp = sheetCookie.createRow(countedCookie); temp.createCell(0) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_URL.getTypeID())); temp.createCell(1).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DATETIME.getTypeID())); temp.createCell(2) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_NAME.getTypeID())); temp.createCell(3) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_VALUE.getTypeID())); temp.createCell(4).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_PROG_NAME.getTypeID())); } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_HISTORY .getTypeID()) { countedHistory++; Row temp = sheetHistory.createRow(countedHistory); temp.createCell(0) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_URL.getTypeID())); temp.createCell(1).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_LAST_ACCESSED.getTypeID())); temp.createCell(2).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_REFERRER.getTypeID())); temp.createCell(3) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_NAME.getTypeID())); temp.createCell(4).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_PROG_NAME.getTypeID())); } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_DOWNLOAD .getTypeID()) { countedDownload++; Row temp = sheetDownload.createRow(countedDownload); temp.createCell(0) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_PATH.getTypeID())); temp.createCell(1) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_URL.getTypeID())); temp.createCell(2).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_LAST_ACCESSED.getTypeID())); temp.createCell(3).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_PROG_NAME.getTypeID())); } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_RECENT_OBJECT .getTypeID()) { countedRecentObjects++; Row temp = sheetRecent.createRow(countedRecentObjects); temp.createCell(0) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_NAME.getTypeID())); temp.createCell(1) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_PATH.getTypeID())); temp.createCell(2).setCellValue(file.getName()); temp.createCell(3).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_PROG_NAME.getTypeID())); } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_TRACKPOINT .getTypeID()) { // sheetTrackpoint.addContent(artifact); } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_INSTALLED_PROG .getTypeID()) { countedInstalled++; Row temp = sheetInstalled.createRow(countedInstalled); temp.createCell(0).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_PROG_NAME.getTypeID())); temp.createCell(1).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DATETIME.getTypeID())); } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_KEYWORD_HIT .getTypeID()) { countedKeyword++; Row temp = sheetKeyword.createRow(countedKeyword); temp.createCell(0).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_KEYWORD.getTypeID())); temp.createCell(1).setCellValue(filename); temp.createCell(2).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_KEYWORD_PREVIEW.getTypeID())); temp.createCell(3).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_SET_NAME.getTypeID())); } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_HASHSET_HIT .getTypeID()) { countedHash++; Row temp = sheetHash.createRow(countedHash); temp.createCell(0).setCellValue(file.getName().toString()); temp.createCell(1).setCellValue(filesize.toString()); temp.createCell(2).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_SET_NAME.getTypeID())); } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_DEVICE_ATTACHED .getTypeID()) { countedDevice++; Row temp = sheetDevice.createRow(countedDevice); temp.createCell(0).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DEVICE_MODEL.getTypeID())); temp.createCell(1).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DEVICE_ID.getTypeID())); temp.createCell(2).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DATETIME.getTypeID())); } if (entry.getKey().getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_EMAIL_MSG .getTypeID()) { countedEmail++; Row temp = sheetEmail.createRow(countedEmail); temp.createCell(0).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_EMAIL_FROM.getTypeID())); temp.createCell(1).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_EMAIL_TO.getTypeID())); temp.createCell(2).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_SUBJECT.getTypeID())); temp.createCell(3).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DATETIME_RCVD.getTypeID())); temp.createCell(4).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_EMAIL_CONTENT_PLAIN.getTypeID())); temp.createCell(5).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_EMAIL_CC.getTypeID())); temp.createCell(6).setCellValue( attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_EMAIL_BCC.getTypeID())); temp.createCell(7) .setCellValue(attributes.get(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_PATH.getTypeID())); } } //write out the report to the reports folder, set the wbtemp to the primary wb object wb = wbtemp; xlsPath = currentCase.getCaseDirectory() + File.separator + "Reports" + File.separator + caseName + "-" + datenotime + ".xlsx"; this.save(xlsPath); } catch (Exception E) { String test = E.toString(); } return xlsPath; }
From source file:org.smart.migrate.dao.impl.ExcelImportDao.java
@Override public List<String> findAllSourcePrimaryKeys(TableSetting tableSetting) { sourceData.clear();//from www . jav a 2 s.com List<String> pks = new ArrayList<String>(); Sheet sheet = sourceWorkbook.getSheet(tableSetting.getSourceTable()); if (sheet == null) { throw new RuntimeException("can not get sheet from " + tableSetting.getSourceTable()); } Row headRow = sheet.getRow(sheet.getFirstRowNum()); if (headRow != null) { int idColumn = ExcelUtils.cellIndexInRow("id", headRow); if (idColumn == -1) { throw new RuntimeException("sheet must have id column!"); } //initialize header map (key: column index,value: fieldname) Map<Integer, String> header = new HashMap<Integer, String>(); for (Cell cell : headRow) { header.put(cell.getColumnIndex(), cell.getStringCellValue()); } for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); String pk; if (row.getCell(idColumn).getCellType() == Cell.CELL_TYPE_NUMERIC) { Double did = row.getCell(idColumn).getNumericCellValue(); pk = String.valueOf(did.intValue()); } else { pk = row.getCell(idColumn).getStringCellValue(); } pks.add(pk); Map<String, Object> data = new HashMap<String, Object>(); for (Cell cell : row) { String fieldname = header.get(cell.getColumnIndex()); if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { Double dvalue = cell.getNumericCellValue(); String s = String.valueOf(dvalue); if (s.endsWith(".0")) { data.put(fieldname, dvalue.intValue()); } else { data.put(fieldname, dvalue); } } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { data.put(fieldname, cell.getBooleanCellValue()); } else { data.put(fieldname, cell.getStringCellValue()); } } sourceData.put(pk, data); } } return pks; }
From source file:org.smart.migrate.util.ExcelUtils.java
public static int cellIndexInRow(String cellContent, Row row) { for (Cell cell : row) { if (cell.getStringCellValue() != null && cell.getStringCellValue().equals(cellContent)) { return cell.getColumnIndex(); }/* w w w . j a v a2s . c o m*/ } return -1; }
From source file:org.specrunner.source.excel.SourceFactoryExcel.java
License:Open Source License
/** * Read header information.//from w w w . ja v a 2 s . co m * * @param table * The target table. * @param caption * The caption element. * @param spanMap * Map of span cells. * @param ignore * Set of cells to ignore. * @param ite * The row iterator. * @return The number of columns to read. */ protected int headers(Element table, Element caption, Map<String, Dimension> spanMap, Set<String> ignore, Iterator<Row> ite) { int result = 0; Element thead = new Element("thead"); table.appendChild(thead); { Element tr = new Element("tr"); thead.appendChild(tr); { if (ite.hasNext()) { Row row = ite.next(); result = row.getLastCellNum(); for (int i = 0; i < result; i++) { Cell cell = row.getCell(i); if (cell != null) { String key = cell.getRowIndex() + "," + cell.getColumnIndex(); if (ignore.contains(key)) { continue; } Element th = new Element("th"); tr.appendChild(th); th.appendChild(String.valueOf(extractVal(cell))); addAttributes(table, caption, tr, th, cell, spanMap.get(key)); } } } } } return result; }
From source file:org.specrunner.source.excel.SourceFactoryExcel.java
License:Open Source License
/** * Read content of a table, using the rows iterator and the number of * columns./* w w w . j a va 2 s.c o m*/ * * @param table * The table. * @param caption * The table caption. * @param spanMap * Map of span cells. * @param ignore * Set of cells to ignore. * @param ite * The row iterator. * @param columns * The number of columns to read. */ protected void readBody(Element table, Element caption, Map<String, Dimension> spanMap, Set<String> ignore, Iterator<Row> ite, int columns) { Element tbody = new Element("tbody"); table.appendChild(tbody); { while (ite.hasNext()) { Element tr = new Element("tr"); tbody.appendChild(tr); { Row row = ite.next(); // invalid lines return -1 in row.getFirstCellNum(). if (row.getFirstCellNum() < 0) { continue; } for (int k = 0; k < columns; k++) { Cell cell = row.getCell(k); String key = null; if (cell != null) { key = cell.getRowIndex() + "," + cell.getColumnIndex(); if (ignore.contains(key)) { continue; } } Element td = new Element("td"); tr.appendChild(td); td.appendChild(String.valueOf(extractVal(cell))); if (cell != null) { addAttributes(table, caption, tr, td, cell, spanMap.get(key)); } } } } } }