List of usage examples for org.apache.poi.xssf.usermodel XSSFRow setHeight
@Override public void setHeight(short height)
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java
License:Open Source License
private XSSFRow copyRow(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum, int destinationRowNum) { XSSFRow sourceRow = worksheet.getRow(sourceRowNum); worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1, true, false); XSSFRow newRow = worksheet.createRow(destinationRowNum); // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell XSSFCell oldCell = sourceRow.getCell(i); XSSFCell newCell = newRow.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null;/*from ww w .ja v a 2 s .c o m*/ continue; } // Copy style from old cell and apply to new cell XSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // Set the cell data type newCell.setCellType(oldCell.getCellType()); } // If there are are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } newRow.setHeight(sourceRow.getHeight()); return newRow; }
From source file:Import.Utils.XSSFConvert.java
/** * @param srcSheet the sheet to copy.//from ww w . j av a 2 s . c o m * @param destSheet the sheet to create. * @param srcRow the row to copy. * @param destRow the row to create. * @param styleMap - */ public static void copyRow(HSSFSheet srcSheet, XSSFSheet destSheet, HSSFRow srcRow, XSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) { // manage a list of merged zone in order to not insert two times a // merged zone Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>(); destRow.setHeight(srcRow.getHeight()); // pour chaque row for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) { HSSFCell oldCell = srcRow.getCell(j); // ancienne cell XSSFCell newCell = destRow.getCell(j); // new cell if (oldCell != null) { if (newCell == null) { newCell = destRow.createCell(j); } // copy chaque cell copyCell(oldCell, newCell, styleMap); // copy les informations de fusion entre les cellules // System.out.println("row num: " + srcRow.getRowNum() + // " , col: " + (short)oldCell.getColumnIndex()); CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex()); if (mergedRegion != null) { // System.out.println("Selected merged region: " + // mergedRegion.toString()); CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getLastRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastColumn()); // System.out.println("New merged region: " + // newMergedRegion.toString()); CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion); if (isNewMergedRegion(wrapper, mergedRegions)) { mergedRegions.add(wrapper); destSheet.addMergedRegion(wrapper.range); } } } } }
From source file:mvjce.Excel_operations.java
public static void fill_exceldata(XSSFWorkbook workbook, XSSFSheet spreadsheet) { try {// www.j a v a2 s. c o m Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection("jdbc:mysql://localhost/Sample_data", "root", "root"); Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet detail = st.executeQuery( "select attendance.USN,Student_info.Name,attendance.sub1_class,attendance.sub2_class," + " attendance.sub3_class,attendance.sub4_class,attendance.sub5_class,attendance.sub6_class,attendance.sub7_class,attendance.sub8_class from attendance inner join " + " Student_info on attendance.USN = Student_info.USN where class='" + Writesheet.sec + "' and semester=" + Writesheet.sem_string + " ;"); int i = 1, j = 8; XSSFFont font = workbook.createFont(); font.setFontName("Arial"); font.setBold(true); XSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(XSSFCellStyle.ALIGN_LEFT); style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); style.setWrapText(true); style.setFont(font); XSSFCell cell; while (detail.next()) { XSSFRow row = spreadsheet.createRow((short) j); row.setHeight((short) 900); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue(i); i++; cell.setCellStyle(style); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue(detail.getString(1)); cell.setCellStyle(style); cell = (XSSFCell) row.createCell((short) 2); cell.setCellValue(detail.getString(2)); cell.setCellStyle(style); cell = (XSSFCell) row.createCell((short) 3); cell.setCellValue(detail.getString(3)); cell.setCellStyle(style); cell = (XSSFCell) row.createCell((short) 5); cell.setCellValue(detail.getString(4)); cell.setCellStyle(style); cell = (XSSFCell) row.createCell((short) 7); cell.setCellValue(detail.getString(5)); cell.setCellStyle(style); cell = (XSSFCell) row.createCell((short) 9); cell.setCellValue(detail.getString(6)); cell.setCellStyle(style); cell = (XSSFCell) row.createCell((short) 11); cell.setCellValue(detail.getString(7)); cell.setCellStyle(style); cell = (XSSFCell) row.createCell((short) 13); cell.setCellValue(detail.getString(8)); cell.setCellStyle(style); cell = (XSSFCell) row.createCell((short) 15); cell.setCellValue(detail.getString(9)); cell.setCellStyle(style); cell = (XSSFCell) row.createCell((short) 17); cell.setCellValue(detail.getString(10)); cell.setCellStyle(style); j++; } } catch (Exception e) { System.out.println(e); } }
From source file:mvjce.internal_sheet.java
public static void internal_details(XSSFWorkbook workbook) { XSSFSheet spreadsheet = workbook.createSheet("test_excel_internal"); XSSFRow row = spreadsheet.createRow((short) 0); XSSFCell cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("MVJ College of Bangalore- 560067"); //MEARGING CELLS spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 19)); XSSFFont font = workbook.createFont(); font.setFontName("Arial"); font.setBold(true);//w w w. j a v a 2 s. c o m XSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); style.setWrapText(true); style.setFont(font); cell.setCellStyle(style); Excel_operations.set_subcode(); row = spreadsheet.createRow((short) 1); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Department of " + dept_name); spreadsheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 19)); cell.setCellStyle(style); row = spreadsheet.createRow(3); row.setHeight((short) 600); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("Semester: \n" + sem_string + sec); cell.setCellStyle(style); row = spreadsheet.createRow(4); String[] text = new String[3]; text[0] = "SI.No"; text[1] = "USN"; text[2] = "STUDENT NAME"; for (int i = 0; i < 3; i++) { cell = (XSSFCell) row.createCell((short) i); cell.setCellValue(text[i]); cell.setCellStyle(style); spreadsheet.addMergedRegion(new CellRangeAddress(4, 5, i, i)); } XSSFRow row2 = spreadsheet.createRow((short) 5); for (int i = 0, j = 3; j <= 23; j += 4) { cell = row.createCell((short) j); cell.setCellValue(sub[i]); i++; cell.setCellStyle(style); cell = row2.createCell(j); cell.setCellValue("T1"); cell.setCellStyle(style); cell = row2.createCell(j + 1); cell.setCellValue("T2"); cell.setCellStyle(style); cell = row2.createCell(j + 2); cell.setCellValue("T3"); cell.setCellStyle(style); cell = row2.createCell(j + 3); cell.setCellValue("Avg"); cell.setCellStyle(style); spreadsheet.addMergedRegion(new CellRangeAddress(4, 4, j, j + 3)); Excel_operations.insert_internals(workbook, spreadsheet); } }
From source file:mvjce.Writesheet.java
public static void writesheet() { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet(sem_string + sec); XSSFRow row = spreadsheet.createRow((short) 0); XSSFCell cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("MVJ College of Bangalore- 560067"); //MEARGING CELLS spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 19)); XSSFFont font = workbook.createFont(); font.setFontName("Arial"); font.setBold(true);/*from w w w.ja v a2 s . co m*/ XSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); style.setWrapText(true); style.setFont(font); cell.setCellStyle(style); Excel_operations.set_subcode(); for (int i = 0; i < 8; i++) { internal_sheet.sub[i] = sub[i]; } internal_sheet.dept_name = dept_name; internal_sheet.sec = sec; internal_sheet.sem_string = sem_string; internal_sheet.internal_details(workbook); row = spreadsheet.createRow((short) 1); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Department of " + dept_name); spreadsheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 19)); cell.setCellStyle(style); row = spreadsheet.createRow(4); row.setHeight((short) 600); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("Semester: \n" + sem_string + sec); cell.setCellStyle(style); row = spreadsheet.createRow(5); String[] text = new String[3]; text[0] = "SI.No"; text[1] = "USN"; text[2] = "STUDENT\nNAME"; for (int i = 0; i < 3; i++) { cell = (XSSFCell) row.createCell((short) i); cell.setCellValue(text[i]); cell.setCellStyle(style); spreadsheet.addMergedRegion(new CellRangeAddress(5, 7, i, i)); } int j = 0; XSSFRow row1 = spreadsheet.createRow((short) 6); row1.setHeight((short) 1000); XSSFRow row2 = spreadsheet.createRow((short) 7); row2.setHeight((short) 1000); for (int i = 3; i < 18; i++) { cell = row1.createCell((short) i); cell.setCellValue("Total no. of classes"); cell.setCellStyle(style); cell = row2.createCell((short) i); cell.setCellValue("No.of Classes attended"); cell.setCellStyle(style); cell = row2.createCell((short) i + 1); cell.setCellValue("%"); cell.setCellStyle(style); cell = row.createCell((short) i); cell.setCellValue(sub[j]); j++; cell.setCellStyle(style); spreadsheet.addMergedRegion(new CellRangeAddress(5, 5, i, i + 1)); i++; } cell = row1.createCell((short) 19); cell.setCellValue("%"); cell.setCellStyle(style); cell = row.createCell((short) 19); cell.setCellValue("AVG"); cell.setCellStyle(style); spreadsheet.addMergedRegion(new CellRangeAddress(5, 5, 19, 19)); Excel_operations.fill_exceldata(workbook, spreadsheet); try { FileOutputStream out = new FileOutputStream(new File("test_excel.xlsx")); workbook.write(out); out.close(); } catch (Exception e) { Database.print_error("Excel_output_stream"); } System.out.println("typesofcells.xlsx written successfully"); }
From source file:nc.noumea.mairie.appock.util.StockSpreadsheetExporter.java
License:Open Source License
private static int generateHeader(XSSFSheet worksheet, XSSFWorkbook workbook, int rowNum) { // Now add/* ww w. j a v a2 s . c o m*/ XSSFRow row = worksheet.createRow(rowNum); XSSFCell cell; XSSFCellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.index); headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); headerStyle.setBorderBottom(BorderStyle.MEDIUM); headerStyle.setBorderLeft(BorderStyle.MEDIUM); headerStyle.setBorderRight(BorderStyle.MEDIUM); headerStyle.setBorderTop(BorderStyle.MEDIUM); headerStyle.setAlignment(HorizontalAlignment.CENTER); headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); XSSFFont txtFont = workbook.createFont(); txtFont.setFontName("calibri"); txtFont.setFontHeightInPoints((short) 9); txtFont.setBold(true); headerStyle.setFont(txtFont); cell = row.createCell(0); cell.setCellValue("Photo"); cell.setCellStyle(headerStyle); worksheet.setColumnWidth(0, ConvertImageUnits.pixel2WidthUnits(COLUMN_WIDTH_PX));//4387 cell = row.createCell(1); cell.setCellValue("Rfrence"); cell.setCellStyle(headerStyle); cell = row.createCell(2); cell.setCellValue("Libell"); cell.setCellStyle(headerStyle); cell = row.createCell(3); cell.setCellValue("Stock\n Appock"); cell.setCellStyle(headerStyle); cell.getCellStyle().setWrapText(true); cell = row.createCell(4); cell.setCellValue("Stock\n rel"); cell.setCellStyle(headerStyle); cell.getCellStyle().setWrapText(true); row.setHeight((short) 600); return rowNum + 1; }
From source file:nc.noumea.mairie.appock.util.StockSpreadsheetExporter.java
License:Open Source License
private static void createRow(XSSFSheet worksheet, XSSFWorkbook workbook, ArticleStock article, CatalogueService catalogueService, int rowNumber) throws IOException { int col = 0;//from w ww .j a v a 2 s . c o m XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); XSSFCellStyle cellImageStyle = workbook.createCellStyle(); cellImageStyle.setBorderBottom(BorderStyle.THIN); cellImageStyle.setBorderLeft(BorderStyle.THIN); cellImageStyle.setBorderRight(BorderStyle.THIN); cellImageStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellImageStyle.setAlignment(HorizontalAlignment.CENTER); if (rowNumber != 1) { cellStyle.setBorderTop(BorderStyle.THIN); } XSSFFont txtFont = workbook.createFont(); txtFont.setFontName("calibri"); txtFont.setFontHeightInPoints((short) 9); txtFont.setBold(false); cellStyle.setFont(txtFont); XSSFRow row = worksheet.createRow(rowNumber); row.setHeight((short) ROW_HEIGHT_TWIPS);//80px 1600 // Photo File image = null; try { image = catalogueService.getFilePieceJointe(article.getArticleCatalogue().getPhotoArticleCatalogue()); } catch (IllegalArgumentException e) { log.warn("No image to display for article " + article.getArticleCatalogue().getLibelle()); } XSSFCell cell = row.createCell(col); cell.setCellStyle(cellImageStyle); if (image != null) addImage(workbook, worksheet, image, rowNumber); col = col + 1; // Rfrence cell = row.createCell(col); cell.setCellStyle(cellStyle); col = col + 1; cell.setCellValue(article.getReferenceArticleStock()); // Libell cell = row.createCell(col); cell.setCellStyle(cellStyle); col = col + 1; cell.setCellValue(article.getArticleCatalogue().getLibelle()); // Appock Stock cell = row.createCell(col); cell.setCellStyle(cellStyle); col = col + 1; cell.setCellValue(article.getQuantiteStock()); cell.setCellType(CellType.NUMERIC); // Stock reel cell = row.createCell(col); cell.setCellStyle(cellStyle); cell.setCellType(CellType.NUMERIC); }
From source file:net.mcnewfamily.rmcnew.shared.Util.java
License:Open Source License
public static void copyXSSFRow(XSSFRow srcRow, XSSFRow destRow) { for (Cell srcCell : srcRow) { XSSFCell destCell = destRow.createCell(srcCell.getColumnIndex()); copyXSSFCell((XSSFCell) srcCell, destCell); }// w w w .j a v a 2s . c om destRow.setHeight(srcRow.getHeight()); }
From source file:org.apache.ofbiz.pricat.AbstractPricatParser.java
License:Apache License
public void writeCommentsToFile(XSSFWorkbook workbook, XSSFSheet sheet) { report.println();//from w w w.ja v a2 s .c om report.print(UtilProperties.getMessage(resource, "WriteCommentsBackToExcel", locale), InterfaceReport.FORMAT_NOTE); FileOutputStream fos = null; XSSFCreationHelper factory = workbook.getCreationHelper(); XSSFFont boldFont = workbook.createFont(); boldFont.setFontName("Arial"); boldFont.setBold(true); boldFont.setCharSet(134); boldFont.setFontHeightInPoints((short) 9); XSSFFont plainFont = workbook.createFont(); plainFont.setFontName("Arial"); plainFont.setCharSet(134); plainFont.setFontHeightInPoints((short) 9); XSSFSheet errorSheet = null; if (errorMessages.keySet().size() > 0) { String errorSheetName = UtilDateTime.nowDateString("yyyy-MM-dd HHmm") + " Errors"; errorSheetName = WorkbookUtil.createSafeSheetName(errorSheetName); errorSheet = workbook.createSheet(errorSheetName); workbook.setSheetOrder(errorSheetName, 0); workbook.setActiveSheet(workbook.getSheetIndex(errorSheetName)); XSSFDrawing drawingPatriarch = errorSheet.getDrawingPatriarch(); if (drawingPatriarch == null) { drawingPatriarch = errorSheet.createDrawingPatriarch(); } for (int i = 0; i <= getHeaderRowNo(); i++) { XSSFRow newRow = errorSheet.createRow(i); XSSFRow row = sheet.getRow(i); newRow.setHeight(row.getHeight()); copyRow(row, newRow, factory, drawingPatriarch); } // copy merged regions for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.getFirstRow() < getHeaderRowNo()) { errorSheet.addMergedRegion(mergedRegion); } } // copy images List<XSSFPictureData> pics = workbook.getAllPictures(); List<XSSFShape> shapes = sheet.getDrawingPatriarch().getShapes(); for (int i = 0; i < shapes.size(); i++) { XSSFShape shape = shapes.get(i); XSSFAnchor anchor = shape.getAnchor(); if (shape instanceof XSSFPicture && anchor instanceof XSSFClientAnchor) { XSSFPicture pic = (XSSFPicture) shape; XSSFClientAnchor clientAnchor = (XSSFClientAnchor) anchor; if (clientAnchor.getRow1() < getHeaderRowNo()) { for (int j = 0; j < pics.size(); j++) { XSSFPictureData picture = pics.get(j); if (picture.getPackagePart().getPartName() .equals(pic.getPictureData().getPackagePart().getPartName())) { drawingPatriarch.createPicture(clientAnchor, j); } } } } } } try { // set comments in the original sheet XSSFDrawing patriarch = sheet.getDrawingPatriarch(); for (CellReference cell : errorMessages.keySet()) { if (cell != null && errorMessages.get(cell) != null) { XSSFComment comment = sheet.getCellComment(new CellAddress(cell.getRow(), cell.getCol())); boolean isNewComment = false; if (comment == null) { XSSFClientAnchor anchor = factory.createClientAnchor(); anchor.setDx1(100); anchor.setDx2(100); anchor.setDy1(100); anchor.setDy2(100); anchor.setCol1(cell.getCol()); anchor.setCol2(cell.getCol() + 4); anchor.setRow1(cell.getRow()); anchor.setRow2(cell.getRow() + 4); anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE); comment = patriarch.createCellComment(anchor); isNewComment = true; } XSSFRichTextString rts = factory.createRichTextString("OFBiz PriCat:\n"); rts.applyFont(boldFont); rts.append(errorMessages.get(cell), plainFont); comment.setString(rts); comment.setAuthor("Apache OFBiz PriCat"); if (isNewComment) { sheet.getRow(cell.getRow()).getCell(cell.getCol()).setCellComment(comment); OFBizPricatUtil.formatCommentShape(sheet, cell); } } } // set comments in the new error sheet XSSFDrawing errorPatriarch = errorSheet.getDrawingPatriarch(); int newRowNum = getHeaderRowNo() + 1; Map<Integer, Integer> rowMapping = new HashMap<Integer, Integer>(); for (CellReference cell : errorMessages.keySet()) { if (cell != null && errorMessages.get(cell) != null) { XSSFRow row = sheet.getRow(cell.getRow()); Integer rowNum = Integer.valueOf(row.getRowNum()); int errorRow = newRowNum; if (rowMapping.containsKey(rowNum)) { errorRow = rowMapping.get(rowNum).intValue(); } else { XSSFRow newRow = errorSheet.getRow(errorRow); if (newRow == null) { newRow = errorSheet.createRow(errorRow); } rowMapping.put(rowNum, Integer.valueOf(errorRow)); newRow.setHeight(row.getHeight()); copyRow(row, newRow, factory, errorPatriarch); newRowNum++; } } } // write to file if (sequenceNum > 0L) { File commentedExcel = FileUtil.getFile(tempFilesFolder + userLoginId + "/" + sequenceNum + ".xlsx"); fos = new FileOutputStream(commentedExcel); workbook.write(fos); } else { fos = new FileOutputStream(pricatFile); workbook.write(fos); } fos.flush(); fos.close(); workbook.close(); } catch (FileNotFoundException e) { report.println(e); Debug.logError(e, module); } catch (IOException e) { report.println(e); Debug.logError(e, module); } finally { if (fos != null) { try { fos.close(); } catch (IOException e) { Debug.logError(e, module); } } if (workbook != null) { try { workbook.close(); } catch (IOException e) { Debug.logError(e, module); } } } report.println(UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK); report.println(); }
From source file:org.kuali.test.runner.output.PoiHelper.java
License:Educational Community License
private void copyRow(XSSFRow srcRow, XSSFRow destRow, Map<Integer, XSSFCellStyle> styleMap) { destRow.setHeight(srcRow.getHeight()); for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) { XSSFCell oldCell = srcRow.getCell(j); XSSFCell newCell = destRow.getCell(j); if (oldCell != null) { if (newCell == null) { newCell = destRow.createCell(j); }//from w w w . j av a2 s. c o m copyCell(oldCell, newCell, styleMap); } } }