List of usage examples for org.apache.poi.ss.usermodel Sheet getDefaultRowHeight
short getDefaultRowHeight();
From source file:com.canoo.webtest.plugins.exceltest.ExcelStructureFilter.java
License:Open Source License
public void doExecute() throws Exception { final Workbook excelWorkbook = getExcelWorkbook(); final DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder(); final Document doc = builder.newDocument(); final Element root = doc.createElement("excelWorkbook"); doc.appendChild(root);/*from w w w . java 2 s . co m*/ //root.setAttribute("backup", String.valueOf(excelWorkbook.getBackupFlag())); root.setAttribute("backup", String.valueOf(Boolean.FALSE)); root.setAttribute("numberOfFonts", String.valueOf(excelWorkbook.getNumberOfFonts())); root.setAttribute("numberOfCellStyles", String.valueOf(excelWorkbook.getNumCellStyles())); root.setAttribute("numberOfNames", String.valueOf(excelWorkbook.getNumberOfNames())); final Element sheets = doc.createElement("sheets"); for (int i = 0; i < excelWorkbook.getNumberOfSheets(); i++) { final Sheet sheetAt = excelWorkbook.getSheetAt(i); final Element sheetElement = doc.createElement("sheet"); sheetElement.setAttribute("index", String.valueOf(i)); sheetElement.setAttribute("name", excelWorkbook.getSheetName(i)); sheetElement.setAttribute("firstRow", String.valueOf(sheetAt.getFirstRowNum())); sheetElement.setAttribute("lastRow", String.valueOf(sheetAt.getLastRowNum())); sheetElement.setAttribute("physicalRows", String.valueOf(sheetAt.getPhysicalNumberOfRows())); sheetElement.setAttribute("defaultRowHeight", String.valueOf(sheetAt.getDefaultRowHeight())); sheetElement.setAttribute("defaultColumnWidth", String.valueOf(sheetAt.getDefaultColumnWidth())); sheetElement.setAttribute("fitToPage", String.valueOf(sheetAt.getFitToPage())); sheets.appendChild(sheetElement); } root.appendChild(sheets); final StringWriter sw = new StringWriter(); writeXmlFile(doc, sw); ContextHelper.defineAsCurrentResponse(getContext(), sw.toString(), "text/xml", getClass()); }
From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.SummaryReportGenerator.java
License:Apache License
/** * Creates Monthly Sheet/*ww w. j a va 2 s. c om*/ */ private void createMonthlySheet(Workbook p_workbook, Sheet p_sheet, ReportSearchOptions p_options, Map<String, ReportWordCount> p_wordCounts) throws Exception { List<String> searchMonths = p_options.getMonths(); CellStyle style = getHeaderStyle(p_workbook); CellStyle style1 = getHeaderStyle(p_workbook, null, null, null, null); CellStyle styleTB = getHeaderStyle(p_workbook, CellStyle.BORDER_THIN, null, CellStyle.BORDER_THIN, null); CellStyle styleTB2 = getHeaderStyle(p_workbook, CellStyle.BORDER_THIN, null, CellStyle.BORDER_THIN, null); styleTB2.setAlignment(CellStyle.ALIGN_RIGHT); CellStyle styleLR = getHeaderStyle(p_workbook, null, CellStyle.BORDER_THIN, null, CellStyle.BORDER_THIN); int row = ROWNUMBER, column = 0; p_sheet.setColumnWidth(0, 12 * 256); Cell cell_A_Header = getCell(getRow(p_sheet, row), column++); cell_A_Header.setCellValue(bundle.getString("lb_sumOfTotal")); cell_A_Header.setCellStyle(style); p_sheet.addMergedRegion(new CellRangeAddress(row, row, column, column + searchMonths.size())); setRegionStyle(p_sheet, new CellRangeAddress(row, row, column, column + searchMonths.size()), style); Cell cell_B_Header = getCell(getRow(p_sheet, row), column); cell_B_Header.setCellValue(bundle.getString("lb_month")); cell_B_Header.setCellStyle(style); row++; column = 0; Cell cell_A = getCell(getRow(p_sheet, row), column++); cell_A.setCellValue(bundle.getString("lb_lang")); cell_A.setCellStyle(style); for (String yearAndMonth : searchMonths) { Cell cell_Month = getCell(getRow(p_sheet, row), column++); cell_Month.setCellValue(Double.valueOf(yearAndMonth.substring(4))); cell_Month.setCellStyle(styleTB); } p_sheet.setColumnWidth(column, 10 * 256); Cell cell_LocaleTotal = getCell(getRow(p_sheet, row), column++); cell_LocaleTotal.setCellValue(bundle.getString("lb_grandTotal")); cell_LocaleTotal.setCellStyle(style); // Adds a hidden column, for Excel Sum Check Error. Row hiddenRow = getRow(p_sheet, ++row); hiddenRow.setZeroHeight(isHidden); getRow(p_sheet, row - 1).setHeight(p_sheet.getDefaultRowHeight()); int dataRow = ++row; column = 0; double totalWordCount = 0; Set<String> locales = getLocals(p_wordCounts); for (String locale : locales) { Cell cell_A_Locale = getCell(getRow(p_sheet, row), column++); cell_A_Locale.setCellValue(locale); cell_A_Locale.setCellStyle(styleLR); for (String yearAndMonth : searchMonths) { ReportWordCount reportWordCount = p_wordCounts.get(getWordCountMapKey(locale, yearAndMonth)); if (reportWordCount != null) { totalWordCount = reportWordCount.getTradosTotalWordCount(); } addNumberCell(p_sheet, column++, row, totalWordCount, style1); totalWordCount = 0; } Cell cell_LocaleTotal_Month = getCell(getRow(p_sheet, row), column); cell_LocaleTotal_Month.setCellFormula(getSumOfRow(1, column - 1, row)); cell_LocaleTotal_Month.setCellStyle(styleLR); row++; column = 0; } if (row > (ROWNUMBER + 3)) { column = 0; Cell cell_GrandTotal = getCell(getRow(p_sheet, row), column++); cell_GrandTotal.setCellValue(bundle.getString("lb_grandTotal")); cell_GrandTotal.setCellStyle(style); for (int i = 0; i < searchMonths.size(); i++) { Cell cell_MonthTotal = getCell(getRow(p_sheet, row), column); cell_MonthTotal.setCellFormula(getSumOfColumn(dataRow, row - 1, column)); cell_MonthTotal.setCellStyle(styleTB); column++; } Cell cell_Total = getCell(getRow(p_sheet, row), column); cell_Total.setCellFormula(getSumOfColumn(dataRow, row - 1, column)); cell_Total.setCellStyle(style); } }
From source file:com.ideaspymes.proyecttemplate.stock.web.ProductoConsultaBean.java
@Override public Workbook getWorkBook() { Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("My Sample Excel"); List<CatalogoProductos> lista = (List<CatalogoProductos>) getDetalles(); sheet.setDefaultRowHeight((short) (sheet.getDefaultRowHeight() * new Short("6"))); org.apache.poi.ss.usermodel.Font fontTitulo = wb.createFont(); fontTitulo.setFontHeightInPoints((short) 12); fontTitulo.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); org.apache.poi.ss.usermodel.Font fontTituloPricipal = wb.createFont(); fontTituloPricipal.setFontHeightInPoints((short) 22); fontTituloPricipal.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); DataFormat format = wb.createDataFormat(); CellStyle styleTituloPrincipal = wb.createCellStyle(); styleTituloPrincipal.setFont(fontTituloPricipal); styleTituloPrincipal.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleTituloPrincipal.setAlignment(CellStyle.ALIGN_CENTER); CellStyle styleTitulo = wb.createCellStyle(); styleTitulo.setFont(fontTitulo);// w w w . j a v a 2 s. co m styleTitulo.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleTitulo.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex()); styleTitulo.setFillPattern(CellStyle.SOLID_FOREGROUND); styleTitulo.setWrapText(true); CellStyle styleNumero = wb.createCellStyle(); styleNumero.setDataFormat(format.getFormat("#,##0")); styleNumero.setWrapText(true); styleNumero.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleNumero.setAlignment(CellStyle.ALIGN_CENTER); CellStyle styleFecha = wb.createCellStyle(); styleFecha.setDataFormat(format.getFormat("dd/MM/yyyy")); styleFecha.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleFecha.setAlignment(CellStyle.ALIGN_CENTER); CellStyle style = wb.createCellStyle(); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setWrapText(true); CellStyle styleCenter = wb.createCellStyle(); styleCenter.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleCenter.setAlignment(CellStyle.ALIGN_CENTER); styleCenter.setWrapText(true); Row rowTitle = sheet.createRow(0); Cell cellTitle = rowTitle.createCell(1); cellTitle.setCellStyle(styleTituloPrincipal); sheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based) 1, //last row (0-based) 1, //first column (0-based) 11 //last column (0-based) )); cellTitle.setCellValue("Listado de Activos"); int i = 2; Row row0 = sheet.createRow(i); row0.setHeight((short) 500); Cell cell1 = row0.createCell(1); cell1.setCellValue("Foto"); cell1.setCellStyle(styleTitulo); Cell cellFecha = row0.createCell(3); cellFecha.setCellValue("Fecha Ingreso"); cellFecha.setCellStyle(styleTitulo); Cell cellFechaCarga = row0.createCell(4); cellFechaCarga.setCellValue("Fecha Carga"); cellFechaCarga.setCellStyle(styleTitulo); Cell cell3 = row0.createCell(5); cell3.setCellValue("Nombre"); cell3.setCellStyle(styleTitulo); Cell cell4 = row0.createCell(6); cell4.setCellValue("Cdigo"); cell4.setCellStyle(styleTitulo); Cell cell5 = row0.createCell(7); cell5.setCellValue("Descripcin"); cell5.setCellStyle(styleTitulo); Cell cell6 = row0.createCell(8); cell6.setCellValue("Es Regalo?"); cell6.setCellStyle(styleTitulo); Cell cell7 = row0.createCell(9); cell7.setCellValue("Familia"); cell7.setCellStyle(styleTitulo); Cell cell8 = row0.createCell(10); cell8.setCellValue("Ubicaciones"); cell8.setCellStyle(styleTitulo); Cell cell9 = row0.createCell(11); cell9.setCellValue("Stock"); cell9.setCellStyle(styleTitulo); for (CatalogoProductos cp : lista) { int indexFila = i + 1; if (cp.getImagen() != null) { int pictureIdx = wb.addPicture(cp.getImagen(), Workbook.PICTURE_TYPE_PNG); CreationHelper helper = wb.getCreationHelper(); //Creates the top-level drawing patriarch. Drawing drawing = sheet.createDrawingPatriarch(); //Create an anchor that is attached to the worksheet ClientAnchor anchor = helper.createClientAnchor(); //set top-left corner for the image anchor.setCol1(1); anchor.setRow1(indexFila); //Creates a picture Picture pict = drawing.createPicture(anchor, pictureIdx); //Reset the image to the original size pict.resize(0.4); } Row row1 = sheet.createRow(indexFila); row1.setHeightInPoints(80f); Cell cellColFecha = row1.createCell(3); if (cp.getFecha() != null) { cellColFecha.setCellValue(cp.getFecha()); cellColFecha.setCellStyle(styleFecha); } else { cellColFecha.setCellValue(""); cellColFecha.setCellStyle(styleFecha); } Cell cellColFechaCarga = row1.createCell(4); if (cp.getFechaCarga() != null) { cellColFechaCarga.setCellValue(cp.getFechaCarga()); cellColFechaCarga.setCellStyle(styleFecha); } else { cellColFechaCarga.setCellValue(""); cellColFechaCarga.setCellStyle(styleFecha); } Cell cellCol1 = row1.createCell(5); cellCol1.setCellValue(cp.getProducto()); cellCol1.setCellStyle(style); Cell cellCol2 = row1.createCell(6); cellCol2.setCellValue(cp.getCodigo()); cellCol2.setCellStyle(styleNumero); Cell cellCol3 = row1.createCell(7); cellCol3.setCellValue(cp.getDescripcion()); cellCol3.setCellStyle(style); Cell cellCol4 = row1.createCell(8); cellCol4.setCellValue(cp.isEsRegalo() ? "SI" : "NO"); cellCol4.setCellStyle(styleCenter); Cell cellCol5 = row1.createCell(9); cellCol5.setCellValue(cp.getFamilia()); cellCol5.setCellStyle(style); Cell cellCol6 = row1.createCell(10); cellCol6.setCellValue(cp.getUbicaciones()); cellCol6.setCellStyle(style); Cell cellCol7 = row1.createCell(11); cellCol7.setCellValue(cp.getStock()); cellCol7.setCellStyle(styleNumero); i++; } sheet.setColumnWidth(1, 4000); sheet.setColumnWidth(2, 0); sheet.setColumnWidth(3, 4000); sheet.setColumnWidth(4, 4000); sheet.setColumnWidth(5, 10000); sheet.setColumnWidth(6, 3000); sheet.setColumnWidth(7, 10000); sheet.setColumnWidth(8, 3500); sheet.setColumnWidth(9, 6000); sheet.setColumnWidth(10, 10000); sheet.setColumnWidth(11, 2000); return wb; }
From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java
License:Apache License
/** * [Flow #-3] ? ? : ?? ?(? ?, Border? ?, ? ?, )? *//*from w ww .ja va2 s .c om*/ @Test public void testWriteExcelFileAttribute() throws Exception { try { LOGGER.debug("testWriteExcelFileAttribute start...."); short rowheight = 40 * 10; int columnwidth = 30; StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xls"); // delete file if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); LOGGER.debug("Delete file....{}", sb.toString()); } Workbook wb = new HSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); wb.createSheet("second sheet"); // ? ? sheet1.setDefaultRowHeight(rowheight); sheet1.setDefaultColumnWidth(columnwidth); Font f2 = wb.createFont(); CellStyle cs = wb.createCellStyle(); cs = wb.createCellStyle(); cs.setFont(f2); cs.setWrapText(true); // cs.setAlignment(CellStyle.ALIGN_RIGHT); cs.setFillPattern(CellStyle.DIAMONDS); // ? // ? ? cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); // cs.setFillBackgroundColor(new HSSFColor.RED().getIndex()); // sheet1.setDefaultColumnStyle((short) 0, cs); Workbook tmp = excelService.createWorkbook(wb, sb.toString()); Sheet sheetTmp1 = tmp.getSheetAt(0); assertEquals(rowheight, sheetTmp1.getDefaultRowHeight()); assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth()); CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1)); LOGGER.debug("getAlignment : {}", cs1.getAlignment()); assertEquals(CellStyle.ALIGN_RIGHT, cs1.getAlignment()); LOGGER.debug("getFillPattern : {}", cs1.getFillPattern()); assertEquals(CellStyle.DIAMONDS, cs1.getFillPattern()); LOGGER.debug("getFillForegroundColor : {}", cs1.getFillForegroundColor()); LOGGER.debug("getFillBackgroundColor : {}", cs1.getFillBackgroundColor()); assertEquals(new HSSFColor.BLUE().getIndex(), cs1.getFillForegroundColor()); assertEquals(new HSSFColor.RED().getIndex(), cs1.getFillBackgroundColor()); } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testWriteExcelFileAttribute end...."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelSXSSFServiceTest.java
License:Apache License
/** * [Flow #-3] ? ? : ?? ?(? ?, Border? ?, ? ?, )? *//*from w w w . j a v a 2 s . c o m*/ @Test public void testWriteExcelFileAttribute() throws Exception { try { log.debug("testWriteExcelFileAttribute start...."); short rowheight = 40; int columnwidth = 30; StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xlsx"); // delete file if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); log.debug("Delete file...." + sb.toString()); } SXSSFWorkbook wb = new SXSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); wb.createSheet("second sheet"); // ? ? sheet1.setDefaultRowHeight(rowheight); sheet1.setDefaultColumnWidth(columnwidth); Font f2 = wb.createFont(); CellStyle cs = wb.createCellStyle(); cs = wb.createCellStyle(); cs.setFont(f2); cs.setWrapText(true); // cs.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cs.setFillPattern(HSSFCellStyle.DIAMONDS); // ? // ? ? cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); // cs.setFillBackgroundColor(new HSSFColor.RED().getIndex()); // sheet1.setDefaultColumnStyle((short) 0, cs); Workbook tmp = excelService.createSXSSFWorkbook(wb, sb.toString()); Sheet sheetTmp1 = tmp.getSheetAt(0); assertEquals(rowheight, sheetTmp1.getDefaultRowHeight()); assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth()); CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1)); log.debug("getAlignment : " + cs1.getAlignment()); assertEquals(HSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment()); log.debug("getFillPattern : " + cs1.getFillPattern()); assertEquals(HSSFCellStyle.DIAMONDS, cs1.getFillPattern()); log.debug("getFillForegroundColor : " + cs1.getFillForegroundColor()); log.debug("getFillBackgroundColor : " + cs1.getFillBackgroundColor()); assertEquals(new HSSFColor.BLUE().getIndex(), cs1.getFillForegroundColor()); assertEquals(new HSSFColor.RED().getIndex(), cs1.getFillBackgroundColor()); } catch (Exception e) { log.error(e.toString()); throw new Exception(e); } finally { log.debug("testWriteExcelFileAttribute end...."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java
License:Apache License
/** * [Flow #-3] ? ? : ?? ?(? ?, Border? ?, ? ?, )? *///from www. j av a2 s . c o m @Test public void testWriteExcelFileAttribute() throws Exception { try { LOGGER.debug("testWriteExcelFileAttribute start...."); short rowheight = 40 * 10; int columnwidth = 30; StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xlsx"); // delete file if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); LOGGER.debug("Delete file....{}", sb.toString()); } XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet1 = wb.createSheet("new sheet"); wb.createSheet("second sheet"); // ? ? sheet1.setDefaultRowHeight(rowheight); sheet1.setDefaultColumnWidth(columnwidth); Font f2 = wb.createFont(); XSSFCellStyle cs = wb.createCellStyle(); cs.setFont(f2); cs.setWrapText(true); // cs.setAlignment(CellStyle.ALIGN_RIGHT); cs.setFillPattern(CellStyle.DIAMONDS); // ? XSSFRow r1 = sheet1.createRow(0); r1.createCell(0); // ? ? cs.setFillForegroundColor(IndexedColors.BLUE.getIndex()); // cs.setFillBackgroundColor(IndexedColors.RED.getIndex()); // sheet1.setDefaultColumnStyle((short) 0, cs); Workbook tmp = excelService.createWorkbook(wb, sb.toString()); Sheet sheetTmp1 = tmp.getSheetAt(0); assertEquals(rowheight, sheetTmp1.getDefaultRowHeight()); assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth()); CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1)); LOGGER.debug("getAlignment : {}", cs1.getAlignment()); assertEquals(XSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment()); LOGGER.debug("getFillPattern : {}", cs1.getFillPattern()); assertEquals(XSSFCellStyle.DIAMONDS, cs1.getFillPattern()); LOGGER.debug("getFillForegroundColor : {}", cs1.getFillForegroundColor()); LOGGER.debug("getFillBackgroundColor : {}", cs1.getFillBackgroundColor()); LOGGER.debug( "XSSFWorkbook.getFillBackgroundColor(), XSSFColor().getIndexed() ? ? 0 ? ?"); assertEquals(IndexedColors.BLUE.getIndex(), cs1.getFillForegroundColor()); assertEquals(IndexedColors.RED.getIndex(), cs1.getFillBackgroundColor()); } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testWriteExcelFileAttribute end...."); } }
From source file:org.tiefaces.components.websheet.service.WebSheetLoader.java
License:MIT License
/** * Setup row info./*from w w w. ja v a 2 s . co m*/ * * @param facesRow * the faces row * @param sheet1 * the sheet 1 * @param row * the row * @param rowIndex * the row index * @param allowAdd * the allow add */ private void setupRowInfo(final FacesRow facesRow, final Sheet sheet1, final Row row, final int rowIndex, final boolean allowAdd) { facesRow.setAllowAdd(allowAdd); if (row != null) { facesRow.setRendered(!row.getZeroHeight()); facesRow.setRowheight(row.getHeight()); int rowNum = ConfigurationUtility.getOriginalRowNumInHiddenColumn(row); facesRow.setOriginRowIndex(rowNum); } else { facesRow.setRendered(true); facesRow.setRowheight(sheet1.getDefaultRowHeight()); facesRow.setOriginRowIndex(rowIndex); } }