List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum
@Override public int getLastRowNum()
From source file:phoenixplcscadahelper.AnaEkran.java
private void dosyaSecButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_dosyaSecButtonActionPerformed opcName = getOpcName();//from www . ja va2 s . c o m modbusMasterName = getModbusName(); opcNameLabel.setText("OPC Ad = " + opcName); visuOpcTextArea.setText(""); visuVarTextArea.setText(""); JFileChooser jfc = new JFileChooser(); jfc.setDialogTitle("Ltfen IO Liste Dosyas Sein"); jfc.setCurrentDirectory(new java.io.File(".")); jfc.setFileFilter(new FileNameExtensionFilter("Excel Files", "xls")); int returnVal = jfc.showOpenDialog(this); if (returnVal == JFileChooser.APPROVE_OPTION) { File xlsFile = jfc.getSelectedFile(); //.. java.io.FileInputStream file; try { file = new java.io.FileInputStream(xlsFile); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); int maxRowNr = sheet.getLastRowNum(); digitalTagCount = 0; modbusInNr = 1000; modbusOutNr = 0; // esa tag dosyas esaWb = new HSSFWorkbook(); fillEsaWb(); // esa alarm dosyas esaAWb = new HSSFWorkbook(); fillEsaAWb(); // esa analog tag dosyas esaAnalogWb = new HSSFWorkbook(); fillEsaAnalogWb(); for (int i = 0; i < maxRowNr; i++) { if (sheet.getRow(i) == null) { } else if (sheet.getRow(i).getCell(3) == null) { } else { HSSFCell cell = sheet.getRow(i).getCell(3); if (cell.toString().startsWith("O")) { //DIGITAL OUTPUT digitalOutput(sheet.getRow(i)); } if (cell.toString().startsWith("I")) { //DIGITAL INPUT digitalInput(sheet.getRow(i)); } if (cell.toString().startsWith("AI")) { //ANALOG INPUT analogInput(sheet.getRow(i)); } if (cell.toString().startsWith("AO")) { //ANALOG OUTPUT analogOutput(sheet.getRow(i)); } } } FileOutputStream fileOut = new FileOutputStream(xlsFile.getParent() + "\\esaTag.xls"); esaWb.write(fileOut); fileOut.close(); FileOutputStream fileAOut = new FileOutputStream(xlsFile.getParent() + "\\esaAlarms.xls"); esaAWb.write(fileAOut); fileAOut.close(); FileOutputStream fileAnalogOut = new FileOutputStream(xlsFile.getParent() + "\\esaAnalogTag.xls"); esaAnalogWb.write(fileAnalogOut); fileAnalogOut.close(); } catch (FileNotFoundException ex) { Logger.getLogger(AnaEkran.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(AnaEkran.class.getName()).log(Level.SEVERE, null, ex); } } }
From source file:POI.Sheet.java
/** * HSSFSheetSheet//from w w w. j a va2s . c o m * * @param sheet */ public Sheet(HSSFSheet sheet) { this.sheet = sheet; this.firstRowNum = sheet.getFirstRowNum(); this.lastRowNum = sheet.getLastRowNum(); }
From source file:pt.ist.expenditureTrackingSystem.presentationTier.actions.organization.OrganizationAction.java
License:Open Source License
public final ActionForward downloadUnitResponsibles(final ActionMapping mapping, final ActionForm form, final HttpServletRequest request, final HttpServletResponse response) throws IOException, SQLException { response.setContentType("application/xls "); response.setHeader("Content-disposition", "attachment; filename=ResponsaveisUnidades.xls"); final ServletOutputStream outputStream = response.getOutputStream(); final HSSFWorkbook workbook = new HSSFWorkbook(); final ExcelStyle excelStyle = new ExcelStyle(workbook); final HSSFSheet sheet = workbook.createSheet("Responsaveis"); sheet.setDefaultColumnWidth(20);// ww w.j a v a2 s. c om final HSSFRow row = sheet.createRow(sheet.getLastRowNum()); createHeaderCell(excelStyle, row, 0, "Centro de Custo"); createHeaderCell(excelStyle, row, 1, "Unidade"); createHeaderCell(excelStyle, row, 2, "Responsvel Aprovao"); createHeaderCell(excelStyle, row, 4, "Responsvel Despesa"); sheet.addMergedRegion(new CellRangeAddress(0, 0, 2, 3)); sheet.addMergedRegion(new CellRangeAddress(0, 0, 4, 5)); for (final Unit unit : ExpenditureTrackingSystem.getInstance().getTopLevelUnitsSet()) { writeUnitResponsibleInfo(excelStyle, sheet, unit); } workbook.write(outputStream); outputStream.flush(); outputStream.close(); return null; }
From source file:pt.ist.expenditureTrackingSystem.presentationTier.actions.organization.OrganizationAction.java
License:Open Source License
private void writeUnitResponsibleInfo(final ExcelStyle excelStyle, final HSSFSheet sheet, final Unit unit, final Set<Unit> processed) { final int rowIndex = sheet.getLastRowNum() + 1; HSSFRow row = sheet.createRow(rowIndex); if (unit instanceof CostCenter) { final CostCenter costCenter = (CostCenter) unit; createCell(excelStyle, row, 0, costCenter.getCostCenter()); }//from w w w . ja va 2s. com createCell(excelStyle, row, 1, unit.getName()); final List<Person>[] approvalsAndAuthorizations = getApprovalsAndAuthorizations(unit); final List<Person> approvals = approvalsAndAuthorizations[0]; final List<Person> authorizations = approvalsAndAuthorizations[1]; Collections.sort(approvals, Person.COMPARATOR_BY_NAME); Collections.sort(authorizations, Person.COMPARATOR_BY_NAME); for (int i = 0; i < approvals.size() || i < authorizations.size(); i++) { final Person approval = i < approvals.size() ? approvals.get(i) : null; final Person authorization = i < authorizations.size() ? authorizations.get(i) : null; if (i > 0) { row = sheet.createRow(sheet.getLastRowNum() + 1); } if (approval != null) { createCell(excelStyle, row, 2, approval.getUsername()); createCell(excelStyle, row, 3, approval.getUser().getDisplayName()); } if (authorization != null) { createCell(excelStyle, row, 4, authorization.getUsername()); createCell(excelStyle, row, 5, authorization.getUser().getDisplayName()); } } final int totalRows = Math.max(approvals.size(), authorizations.size()); if (totalRows > 0) { sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex + totalRows - 1, 0, 0)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex + totalRows - 1, 1, 1)); } for (final Unit subUnit : sortUnitsByCostCenter(unit.getSubUnitsSet())) { if (!processed.contains(subUnit)) { processed.add(subUnit); if (!(subUnit instanceof Project)) { writeUnitResponsibleInfo(excelStyle, sheet, subUnit, processed); } } } }
From source file:punchcardrecords.ui.PunchCardRecordsMainFrame.java
License:Open Source License
/** * ?excel(2003)/*from w w w. j av a 2 s . c o m*/ * @param excelFile ??Excel * @param single ?? */ private Map<String, double[]> parseExcel42003(File excelFile, boolean single) { Map<String, double[]> result = new HashMap<String, double[]>(); try { File copyExcelFile = null; HSSFWorkbook copyWorkBook = null; if (single) {// ?? // ?,?, addMessage(""); copyExcelFile = new File( excelFile.getAbsolutePath().substring(0, excelFile.getAbsolutePath().lastIndexOf("\\")) + "/.xlsx"); FileUtils.copyFile(excelFile, copyExcelFile); // copyWorkBook = new HSSFWorkbook(new FileInputStream(copyExcelFile)); } // ? HSSFWorkbook workBook = new HSSFWorkbook(new FileInputStream(excelFile)); HSSFSheet sheet = workBook.getSheetAt(0); int rows = sheet.getLastRowNum(); if (rows >= 6) { // 6,??? // ?3,? String dateStr = sheet.getRow(2).getCell(2).getStringCellValue(); int month = -1; // ? int year = -1;// ? if (single) {// ??. if (StringUtils.isNotBlank(dateStr)) { addMessage("??:" + dateStr); String[] dates = dateStr.split("~"); month = Integer.parseInt(dates[0].split("\\/")[1]);// ?? year = Integer.parseInt(dates[0].split("\\/")[0]);// ?? } else { addMessage( "??,??,?"); } // ?,?? // ,?? int maxValue = (rows - 6) / 2; progressBar.setMaximum(maxValue); } int days = sheet.getRow(3).getLastCellNum(); // ? SimpleDateFormat punchFormat = new SimpleDateFormat("HH:mm"); // ?,,,? String[] title = { "", "", "?" }; if (single) {// ?? if (copyWorkBook != null) { for (int i = 0; i < title.length; i++) { copyWorkBook.getSheetAt(0).getRow(4).createCell(days + i).setCellValue(title[i]); HSSFCellStyle cellStyle = copyWorkBook.createCellStyle(); cellStyle .cloneStyleFrom(copyWorkBook.getSheetAt(0).getRow(4).getCell(0).getCellStyle()); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); copyWorkBook.getSheetAt(0).getRow(4).getCell(days + i).setCellStyle(cellStyle); copyWorkBook.getSheetAt(0).autoSizeColumn((short) (days + i)); } } } for (int i = 4; i < rows; i = i + 2) { // //,?,?+2 String userName = sheet.getRow(i).getCell(10).getStringCellValue();// ?? String userNum = sheet.getRow(i).getCell(2).getStringCellValue();// ? if (single) {// ?? addMessage("?:" + userName + "<?:" + userNum + ">"); // ?? addBar(1); } // ??,i+1 HSSFRow recordRow = sheet.getRow(i + 1); // double punchDays = 0; // (?),? double punchHours = 0, avgHours = 0; // ??? for (int j = 0; j < days; j++) {// ??? if (single) {// ?? // ?, // ?,??,?? if (month != -1 && year != -1) { // ??? if (isWeekEnd(year, month, j + 1)) { if (copyWorkBook != null) { // , HSSFCellStyle weekend = copyWorkBook.createCellStyle(); weekend.cloneStyleFrom( copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j).getCellStyle()); weekend.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); HSSFPalette paltette = copyWorkBook.getCustomPalette(); byte[] color = { (byte) (0xff & 21), (byte) (0xff & 225), (byte) (0xff & 216) }; paltette.setColorAtIndex((short) 9, color[0], color[1], color[2]); weekend.setFillForegroundColor((short) 9); copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j).setCellStyle(weekend); } } } } // ??? String record = recordRow.getCell(j).getStringCellValue();// ? if (StringUtils.isNotBlank(record)) {// ??,?? String[] records = record.split("\n"); // ???,,? if (records.length >= 2) { try { // ?start,?end,?ls,??le Date end = punchFormat.parse(records[records.length - 1]), start = punchFormat.parse(records[0]); Date ls = punchFormat.parse("11:40"), le = punchFormat.parse("13:00"); // ?:??? if (start.after(ls) && end.before(le)) { // ? if (single) {// ?? if (null != copyWorkBook) { // ?,??,?? HSSFCellStyle excepitonStyle = copyWorkBook.createCellStyle(); excepitonStyle.cloneStyleFrom(copyWorkBook.getSheetAt(0) .getRow(i + 1).getCell(j).getCellStyle()); excepitonStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); if (month != -1 && year != -1) { // ??? if (isWeekEnd(year, month, j + 1)) { // , excepitonStyle.setFillForegroundColor( IndexedColors.PINK.getIndex()); } else { excepitonStyle.setFillForegroundColor( IndexedColors.RED.getIndex()); } } copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j) .setCellStyle(excepitonStyle); } } } else { //??? punchDays = punchDays + 1; // ? long ms = end.getTime() - start.getTime();//???? // ??,???,? long mins = 75 * 60 * 1000;//?75 // ??,??? if (start.before(ls) && end.before(le)) { // ???? mins = end.getTime() - ls.getTime(); } // ??,??? if (start.after(ls) && end.after(le)) { // ???,?:??-? if (start.before(le)) { mins = le.getTime() - start.getTime(); } else if (start.after(ls)) { // ???,?0 mins = 0; } } ms = ms - mins;// ?? punchHours = punchHours + (double) ms / (3600 * 1000); // (?) } } catch (ParseException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); } } else {// ?, if (single) {// ?? if (null != copyWorkBook) { // ?,??,?? HSSFCellStyle excepitonStyle = copyWorkBook.createCellStyle(); excepitonStyle.cloneStyleFrom( copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j).getCellStyle()); excepitonStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); if (month != -1 && year != -1) { // ??? if (isWeekEnd(year, month, j + 1)) { // , excepitonStyle .setFillForegroundColor(IndexedColors.PINK.getIndex()); } else { excepitonStyle.setFillForegroundColor(IndexedColors.RED.getIndex()); } } copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j) .setCellStyle(excepitonStyle); } } } } } // ? if (punchDays > 0) { // ???? punchHours = new BigDecimal(punchHours).setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue(); avgHours = new BigDecimal(punchHours / punchDays).setScale(1, BigDecimal.ROUND_HALF_UP) .doubleValue(); } double[] values = { punchDays, punchHours, avgHours }; result.put(userNum + ":" + userName, values); if (single) {// ?? addMessage(":" + userName + "<?:" + userNum + ">??,:" + "D:" + punchDays + ",H:" + punchHours + ",AH:" + avgHours); if (copyWorkBook != null) { for (int v = 0; v < values.length; v++) { copyWorkBook.getSheetAt(0).getRow(i + 1).createCell(days + v) .setCellValue(values[v]); HSSFCellStyle cellStyle = copyWorkBook.createCellStyle(); cellStyle.cloneStyleFrom( copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(0).getCellStyle()); cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(days + v).setCellStyle(cellStyle); } } } } if (single) {// ?? // ?? // ,? addMessage("?,??"); if (copyWorkBook != null) { FileOutputStream out = new FileOutputStream(copyExcelFile); copyWorkBook.write(out); out.close(); } // ???,?? JFileChooser fileSaveChooser = new JFileChooser(); fileSaveChooser.setDialogTitle("?"); fileSaveChooser.setSelectedFile(new File( excelFile.getAbsolutePath().substring(0, excelFile.getAbsolutePath().lastIndexOf(".")) + "-.xls")); String[] saveType = { "xls" }; fileSaveChooser.setAcceptAllFileFilterUsed(false); fileSaveChooser.setFileFilter(new FileNameExtensionFilter("*.xls", saveType)); int saveResult = fileSaveChooser.showSaveDialog(this); if (saveResult == JFileChooser.APPROVE_OPTION) { File saveFile = fileSaveChooser.getSelectedFile(); // ??? String saveFilePath = saveFile.getAbsolutePath(); addMessage("?,??->" + saveFilePath); FileUtils.copyFile(copyExcelFile, saveFile); Object[] options = { "", "", ",?" }; int response = JOptionPane.showOptionDialog(this, "??,???", "?", JOptionPane.YES_OPTION, JOptionPane.QUESTION_MESSAGE, null, options, options[0]); if (0 == response) {// // ?? addMessage(",??"); Desktop.getDesktop().open(saveFile); } else if (1 == response) {// addMessage(",??"); String[] cmd = new String[5]; cmd[0] = "cmd"; cmd[1] = "/c"; cmd[2] = "start"; cmd[3] = " "; cmd[4] = saveFile.getAbsolutePath().substring(0, saveFile.getAbsolutePath().lastIndexOf("\\")); Runtime.getRuntime().exec(cmd); } else { alert("??,?()"); } } else { // ??,? clearMessage(); fileName.setText(""); // ??? addMessage("??"); } // ??? if (copyExcelFile != null) { copyExcelFile.delete(); } } } else { // excel???,??????? alert("????!"); } } catch (FileNotFoundException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); alert(",??"); } catch (IOException | OfficeXmlFileException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); alert(":" + ex.getMessage()); } return result; }
From source file:ro.nextreports.engine.exporter.util.XlsUtil.java
License:Apache License
/** * Copy a sheet to another sheet at a specific (row, column) position * /*from w w w .j av a 2s . c om*/ * @param parentSheet the sheet to copy into * @param parentSheetRow the row inside parentSheet where we start to copy * @param parentSheetColumn the column inside parentSheet where we start to copy * @param sheet the sheet that is copied * @param copyStyle true to copy the style * @return column number */ public static int copyToSheet(HSSFSheet parentSheet, int parentSheetRow, int parentSheetColumn, HSSFSheet sheet, boolean copyStyle) { int maxColumnNum = 0; Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null; for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { HSSFRow srcRow = sheet.getRow(i); HSSFRow destRow; // subreport is not the first cell in a row if ((parentSheetColumn > 0) && (i == sheet.getFirstRowNum())) { destRow = parentSheet.getRow(parentSheetRow); } else { destRow = parentSheet.getRow(parentSheetRow + i); if (destRow == null) { destRow = parentSheet.createRow(parentSheetRow + i); } } if (srcRow != null) { XlsUtil.copyRow(sheet, parentSheet, parentSheetRow, parentSheetColumn, srcRow, destRow, styleMap); if (srcRow.getLastCellNum() > maxColumnNum) { maxColumnNum = srcRow.getLastCellNum(); } } } for (int i = 0; i <= maxColumnNum; i++) { parentSheet.setColumnWidth(i, sheet.getColumnWidth(i)); } return maxColumnNum; }
From source file:ro.nextreports.engine.exporter.XlsExporter.java
License:Apache License
private void renderCell(BandElement bandElement, String bandName, Object value, int gridRow, int sheetRow, int sheetColumn, int rowSpan, int colSpan, boolean image) { if (bandElement instanceof ReportBandElement) { colSpan = 1;// w w w. ja v a 2 s . c o m } HSSFCellStyle cellStyle = buildBandElementStyle(bandElement, value, gridRow, sheetColumn, colSpan); // if we have a subreport on the current grid row we have to take care of the sheetColumn if (ReportLayout.HEADER_BAND_NAME.equals(bandName) && (gridRow == prevSubreportFirstRow) && (prevSubreportLastColumn != -1)) { sheetColumn = prevSubreportLastColumn - prevSubreportFirstColumn - 1 + sheetColumn; } HSSFCell c = xlsRow.createCell(sheetColumn); if (image) { if ((value == null) || "".equals(value)) { c.setCellType(HSSFCell.CELL_TYPE_STRING); c.setCellValue(new HSSFRichTextString(IMAGE_NOT_FOUND)); } else { try { ImageBandElement ibe = (ImageBandElement) bandElement; byte[] imageBytes = getImage((String) value, ibe.getWidth(), ibe.getHeight()); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) sheetColumn, sheetRow, (short) (sheetColumn + colSpan), (sheetRow + rowSpan)); int index = wb.addPicture(imageBytes, HSSFWorkbook.PICTURE_TYPE_JPEG); // image is created over the cells, so if it's height is bigger we set the row height short height = xlsRow.getHeight(); int realImageHeight = getRealImageSize((String) value)[1]; if (ibe.isScaled()) { realImageHeight = ibe.getHeight(); } short imageHeight = (short) (realImageHeight * POINTS_FOR_PIXEL / 2.5); if (imageHeight > height) { xlsRow.setHeight(imageHeight); } HSSFPicture picture = patriarch.createPicture(anchor, index); picture.resize(); anchor.setAnchorType(2); } catch (Exception ex) { c.setCellType(HSSFCell.CELL_TYPE_STRING); c.setCellValue(new HSSFRichTextString(IMAGE_NOT_LOADED)); } } if (cellStyle != null) { c.setCellStyle(cellStyle); } } else { if (bandElement instanceof HyperlinkBandElement) { Hyperlink hyp = ((HyperlinkBandElement) bandElement).getHyperlink(); HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); link.setAddress(hyp.getUrl()); c.setHyperlink(link); c.setCellValue(new HSSFRichTextString(hyp.getText())); c.setCellType(HSSFCell.CELL_TYPE_STRING); } else if (bandElement instanceof ReportBandElement) { Report report = ((ReportBandElement) bandElement).getReport(); ExporterBean eb = null; try { eb = getSubreportExporterBean(report, true); XlsExporter subExporter = new XlsExporter(eb, cellStyle); subExporter.export(); HSSFSheet subreportSheet = subExporter.getSubreportSheet(); if (ReportLayout.HEADER_BAND_NAME.equals(bandName) && (gridRow == prevSubreportFirstRow)) { // other subreports on the same header line after the first sheetColumn = prevSubreportLastColumn; sheetRow -= addedPageRows; pageRow -= addedPageRows; addedPageRows = 0; } else { addedPageRows = subreportSheet.getLastRowNum(); pageRow += addedPageRows; // if subreport is not on the first column we merge all cells in the columns before, between the rows subreport occupies if (sheetColumn > 0) { for (int i = 0; i <= sheetColumn - 1; i++) { CellRangeAddress cra = new CellRangeAddress(sheetRow, pageRow, i, i); regions.add(new XlsRegion(cra, null)); } } } int cols = XlsUtil.copyToSheet(xlsSheet, sheetRow, sheetColumn, subreportSheet); addRegions(xlsSheet, subExporter.getSubreportRegions(), wb); if (ReportLayout.HEADER_BAND_NAME.equals(bandName)) { prevSubreportFirstRow = gridRow; prevSubreportFirstColumn = sheetColumn; prevSubreportLastColumn = sheetColumn + cols; } } catch (Exception e) { e.printStackTrace(); } finally { if ((eb != null) && (eb.getResult() != null)) { eb.getResult().close(); } } } else if (bandElement instanceof ImageColumnBandElement) { try { ImageColumnBandElement icbe = (ImageColumnBandElement) bandElement; String v = StringUtil.getValueAsString(value, null); if (StringUtil.BLOB.equals(v)) { c.setCellType(HSSFCell.CELL_TYPE_STRING); c.setCellValue(new HSSFRichTextString(StringUtil.BLOB)); } else { byte[] imageD = StringUtil.decodeImage(v); byte[] imageBytes = getImage(imageD, icbe.getWidth(), icbe.getHeight()); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) sheetColumn, sheetRow, (short) (sheetColumn + colSpan), (sheetRow + rowSpan)); int index = wb.addPicture(imageBytes, HSSFWorkbook.PICTURE_TYPE_JPEG); // image is created over the cells, so if it's height is bigger we set the row height short height = xlsRow.getHeight(); int realImageHeight = getRealImageSize(imageBytes)[1]; if (icbe.isScaled()) { realImageHeight = icbe.getHeight(); } short imageHeight = (short) (realImageHeight * POINTS_FOR_PIXEL / 2.5); if (imageHeight > height) { xlsRow.setHeight(imageHeight); } HSSFPicture picture = patriarch.createPicture(anchor, index); picture.resize(); anchor.setAnchorType(2); } } catch (Exception e) { e.printStackTrace(); c.setCellType(HSSFCell.CELL_TYPE_STRING); c.setCellValue(new HSSFRichTextString(IMAGE_NOT_LOADED)); } } else { if (value == null) { c.setCellType(HSSFCell.CELL_TYPE_STRING); c.setCellValue(new HSSFRichTextString("")); } else if (value instanceof Number) { c.setCellType(HSSFCell.CELL_TYPE_NUMERIC); c.setCellValue(((Number) value).doubleValue()); } else { String pattern = null; if (bandElement instanceof FieldBandElement) { FieldBandElement fbe = (FieldBandElement) bandElement; pattern = fbe.getPattern(); } if ((value instanceof java.sql.Date) || (value instanceof java.sql.Timestamp)) { Date date; if (value instanceof java.sql.Date) { date = new Date(((java.sql.Date) value).getTime()); } else { date = (java.sql.Timestamp) value; } if (cellStyle != null) { if (pattern == null) { // use default pattern if none selected Locale locale = Locale.getDefault(); pattern = ((SimpleDateFormat) DateFormat.getDateInstance(SimpleDateFormat.MEDIUM, locale)).toPattern(); } else { pattern = StringUtil.getI18nString(pattern, getReportLanguage()); } cellStyle.setDataFormat(wb.createDataFormat().getFormat(pattern)); } c.setCellValue(date); } else { c.setCellType(HSSFCell.CELL_TYPE_STRING); String text = StringUtil.getValueAsString(value, pattern); if ((bandElement != null) && bandElement.isWrapText()) { // try to interpret new line characters // \\n is used here to be possible to add in designer grid cell with \n if (text.contains("\\n") || text.contains("\n") || text.contains("\r") || text.contains("\r\n")) { String crLf = Character.toString((char) 13) + Character.toString((char) 10); int lines = countLines(text); if (text.contains("\r\n")) { text = text.replaceAll("\r\n", crLf); } else { text = text.replaceAll("(\n)|(\r)|(\\\\n)", crLf); } c.setCellValue(text); cellStyle.setWrapText(true); xlsRow.setHeightInPoints( lines * (cellStyle.getFont(wb).getFontHeightInPoints() + 3)); } else { c.setCellValue(new HSSFRichTextString(text)); } } else { c.setCellValue(new HSSFRichTextString(text)); } } } } if (cellStyle != null) { if (bandElement != null) { cellStyle.setRotation(bandElement.getTextRotation()); } if (!(bandElement instanceof ReportBandElement)) { c.setCellStyle(cellStyle); } } if ((rowSpan > 1) || (colSpan > 1)) { CellRangeAddress cra = new CellRangeAddress(sheetRow, sheetRow + rowSpan - 1, sheetColumn, sheetColumn + colSpan - 1); Border beBorder = bandElement.getBorder(); if (hasRowRenderConditions(bandElement, gridRow, value)) { // for row render conditions we must keep the row border beBorder = border; } regions.add(new XlsRegion(cra, beBorder)); } } }
From source file:rocky.sizecounter.SizeCounterUtil.java
License:Apache License
/** * getNmTC./* w w w. j a v a 2s . c o m*/ * * @param filePath path of UT file. * @return 0 */ public static int countNmTC(String filePath, String reportSheet, String rowText) { FileInputStream fis = null; int rowOfReport = 0, nmtc = -1; try { fis = new FileInputStream(filePath); int i = 0; if (CommonUtil.getExtension(filePath).equals("xls")) { try { HSSFWorkbook doc = new HSSFWorkbook(fis); HSSFSheet sheet = doc.getSheet(reportSheet); for (i = 0; i <= sheet.getLastRowNum(); i++) { if (sheet.getRow(i) == null) { continue; } if (sheet.getRow(i).getCell(2) == null) { continue; } if (sheet.getRow(i).getCell(2).getStringCellValue() == null) { continue; } if (sheet.getRow(i).getCell(2).getStringCellValue().equals(rowText)) { rowOfReport = i; } } nmtc = (int) sheet.getRow(rowOfReport).getCell(9).getNumericCellValue(); } catch (Exception e) { LOG.warn("Can not count number of UTC in file: " + filePath, e); } } else if (CommonUtil.getExtension(filePath).equals("xlsx")) { try { XSSFWorkbook doc = new XSSFWorkbook(fis); XSSFSheet sheet = doc.getSheet(reportSheet); for (i = 0; i <= sheet.getLastRowNum(); i++) { if (sheet.getRow(i).getCell(2).getStringCellValue().equals(rowText)) { rowOfReport = i; } } nmtc = (int) sheet.getRow(rowOfReport).getCell(9).getNumericCellValue(); } catch (Exception e) { LOG.warn("Can not count number of UTC in file: " + filePath, e); } } } catch (FileNotFoundException ex) { LOG.warn("Invalid when reading file.", ex); } finally { if (fis != null) { try { fis.close(); } catch (IOException ex) { LOG.warn("Close the file input stream", ex); } } } return nmtc; }
From source file:senselogic.excelbundle.ExcelImporter.java
License:Apache License
/** * Fetch all LanguageFiles in the specified language from the specified * sheet and add them to the specified LanguagePack. *//* w w w . ja va2s .c o m*/ public void getFromSheet(HSSFSheet sheet, String language, LanguagePack pack) { //First, let's fetch all of the defined rows List<HSSFRow> rows = new ArrayList<HSSFRow>(); int lastRow = sheet.getLastRowNum(); for (int i = sheet.getFirstRowNum(); i <= lastRow; i++) { //Don't add empty rows HSSFRow r = sheet.getRow(i); if ((r != null) && (r.getPhysicalNumberOfCells() != 0)) { rows.add(r); } } //Now go, through them one at a time ListIterator<HSSFRow> it = rows.listIterator(); while (it.hasNext()) { HSSFRow r = it.next(); HSSFCell c = r.getCell(0); String value = getString(c); if ((value.charAt(0) != '/') && (value.charAt(0) != '\\')) continue; String bundlePath = value; //Let's see if this language is included if (!it.hasNext()) break; r = it.next(); int valueCol = getIndexOf(r, language); //Skip to next if the language isn't included in this bundle if (valueCol < 0) continue; LanguageFile langFile = new LanguageFile(bundlePath, language); //Now, search through all the keys while (it.hasNext()) { r = it.next(); String firstCell = getString(r.getCell(0)); if (firstCell == null) continue; //If this is a bundle path, rewind and break the loop to parse //the next bundle if ((firstCell.charAt(0) == '/') || (firstCell.charAt(0) == '\\')) { it.previous(); break; } langFile.setValue(firstCell, getString(r.getCell(valueCol))); } if (!langFile.getPairs().isEmpty()) pack.addLanguageFile(langFile); } }
From source file:shouQiSystem.user.ReadDriverEXL.java
public ArrayList<Driver> readXls(String filePath) throws IOException { InputStream is = new FileInputStream(filePath); HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); Driver driver = null;// ww w .j a v a 2 s . co m ArrayList<Driver> list = new ArrayList<Driver>(); for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow != null) { driver = new Driver(); HSSFCell employeeCardNum = hssfRow.getCell(0); HSSFCell name = hssfRow.getCell(1); HSSFCell sex = hssfRow.getCell(2); HSSFCell ID = hssfRow.getCell(3); HSSFCell birthDate = hssfRow.getCell(4); HSSFCell nation = hssfRow.getCell(5); HSSFCell education = hssfRow.getCell(6); HSSFCell politicsStatus = hssfRow.getCell(7); HSSFCell workPlace = hssfRow.getCell(8); HSSFCell phone = hssfRow.getCell(9); HSSFCell address = hssfRow.getCell(10); HSSFCell workType = hssfRow.getCell(11); HSSFCell workerDegreeTech = hssfRow.getCell(12); HSSFCell beginWorkDate = hssfRow.getCell(13); HSSFCell enterWorkPlaceDate = hssfRow.getCell(14); HSSFCell domicilePlace = hssfRow.getCell(15); HSSFCell postalcode = hssfRow.getCell(16); HSSFCell drivingLicenseFileNum = hssfRow.getCell(17); HSSFCell carNum = hssfRow.getCell(18); HSSFCell singleDouble = hssfRow.getCell(19); driver.setEmployeeCardNum(getValue(employeeCardNum)); driver.setName(getValue(name)); driver.setSex(getValue(sex)); driver.setID(getValue(ID)); driver.setBirthDate(getValue(birthDate)); driver.setNation(getValue(nation)); driver.setEducation(getValue(education)); driver.setPoliticsStatus(getValue(politicsStatus)); driver.setWorkPlace(getValue(workPlace)); driver.setPhone(getValue(phone)); driver.setAddress(getValue(address)); driver.setWorkType(getValue(workType)); driver.setWorkerDegreeTech(getValue(workerDegreeTech)); driver.setBeginWorkDate(getValue(beginWorkDate)); driver.setEnterWorkPlaceDate(getValue(enterWorkPlaceDate)); driver.setDomicilePlace(getValue(domicilePlace)); driver.setPostalcode(getValue(postalcode)); driver.setDrivingLicenseFileNum(getValue(drivingLicenseFileNum)); driver.setCarNum(getValue(carNum)); driver.setSingleDouble(getValue(singleDouble)); list.add(driver); } System.out.println("********************************"); } return list; } return list; }