List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createFont
@Override
public HSSFFont createFont()
From source file:org.openswing.swing.export.java.ExportToExcel.java
License:Open Source License
private int prepareGrid(int rownum, HSSFWorkbook wb, HSSFSheet s, ExportOptions exportOptions, GridExportOptions opt) throws Throwable { // declare a row object reference HSSFRow r = null;/*from w w w. j a v a 2 s.co m*/ // declare a cell object reference HSSFCell c = null; // create 3 cell styles HSSFCellStyle csText = wb.createCellStyle(); csText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csText.setBorderBottom(HSSFCellStyle.BORDER_THIN); csText.setBorderLeft(HSSFCellStyle.BORDER_THIN); csText.setBorderRight(HSSFCellStyle.BORDER_THIN); csText.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csTitle = wb.createCellStyle(); csTitle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN); csTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN); csTitle.setBorderRight(HSSFCellStyle.BORDER_THIN); csTitle.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFFont f = wb.createFont(); f.setBoldweight(f.BOLDWEIGHT_NORMAL); csTitle.setFont(f); HSSFCellStyle csBool = wb.createCellStyle(); csBool.setBorderBottom(HSSFCellStyle.BORDER_THIN); csBool.setBorderLeft(HSSFCellStyle.BORDER_THIN); csBool.setBorderRight(HSSFCellStyle.BORDER_THIN); csBool.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csDecNum = wb.createCellStyle(); csDecNum.setDataFormat(wb.createDataFormat().getFormat("#,##0.#####")); csDecNum.setBorderBottom(HSSFCellStyle.BORDER_THIN); csDecNum.setBorderLeft(HSSFCellStyle.BORDER_THIN); csDecNum.setBorderRight(HSSFCellStyle.BORDER_THIN); csDecNum.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csIntNum = wb.createCellStyle(); csIntNum.setDataFormat(wb.createDataFormat().getFormat("#,##0")); csIntNum.setBorderBottom(HSSFCellStyle.BORDER_THIN); csIntNum.setBorderLeft(HSSFCellStyle.BORDER_THIN); csIntNum.setBorderRight(HSSFCellStyle.BORDER_THIN); csIntNum.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csDate = wb.createCellStyle(); csDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"/*opt.getDateFormat()*/)); csDate.setBorderBottom(HSSFCellStyle.BORDER_THIN); csDate.setBorderLeft(HSSFCellStyle.BORDER_THIN); csDate.setBorderRight(HSSFCellStyle.BORDER_THIN); csDate.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csTime = wb.createCellStyle(); csTime.setDataFormat(HSSFDataFormat .getBuiltinFormat(exportOptions.getTimeFormat().equals("HH:mm") ? "h:mm" : "h:mm AM/PM")); csTime.setBorderBottom(HSSFCellStyle.BORDER_THIN); csTime.setBorderLeft(HSSFCellStyle.BORDER_THIN); csTime.setBorderRight(HSSFCellStyle.BORDER_THIN); csTime.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csDateTime = wb.createCellStyle(); csDateTime.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"/*opt.getDateTimeFormat()*/)); csDateTime.setBorderBottom(HSSFCellStyle.BORDER_THIN); csDateTime.setBorderLeft(HSSFCellStyle.BORDER_THIN); csDateTime.setBorderRight(HSSFCellStyle.BORDER_THIN); csDateTime.setBorderTop(HSSFCellStyle.BORDER_THIN); // prepare vo getters methods... String methodName = null; String attributeName = null; Hashtable gettersMethods = new Hashtable(); Method[] voMethods = opt.getValueObjectType().getMethods(); for (int i = 0; i < voMethods.length; i++) { methodName = voMethods[i].getName(); if (methodName.startsWith("get")) { attributeName = methodName.substring(3, 4).toLowerCase() + methodName.substring(4); if (opt.getExportAttrColumns().contains(attributeName)) gettersMethods.put(attributeName, voMethods[i]); } } Response response = null; int start = 0; Object value = null; Object vo = null; int type; boolean firstRow = true; if (opt.getTitle() != null && !opt.getTitle().equals("")) { r = s.createRow(rownum); c = r.createCell((short) 0); try { c.setEncoding(HSSFWorkbook.ENCODING_UTF_16); } catch (NoSuchMethodError ex) { } c.setCellValue(opt.getTitle()); c.setCellStyle(csTitle); rownum++; rownum++; } String[] filters = opt.getFilteringConditions(); if (filters != null) { for (int i = 0; i < filters.length; i++) { r = s.createRow(rownum); c = r.createCell((short) 0); try { c.setEncoding(HSSFWorkbook.ENCODING_UTF_16); } catch (NoSuchMethodError ex) { } c.setCellValue(filters[i]); rownum++; } rownum++; } do { response = opt.getGridDataLocator().loadData(GridParams.NEXT_BLOCK_ACTION, start, opt.getFilteredColumns(), opt.getCurrentSortedColumns(), opt.getCurrentSortedVersusColumns(), opt.getValueObjectType(), opt.getOtherGridParams()); if (response.isError()) throw new Exception(response.getErrorMessage()); for (int j = 0; j < ((VOListResponse) response).getRows().size(); j++) { if (firstRow) { firstRow = false; // create the first row... r = s.createRow(rownum++); for (short i = 0; i < opt.getExportColumns().size(); i++) { c = r.createCell(i); try { c.setEncoding(HSSFWorkbook.ENCODING_UTF_16); } catch (NoSuchMethodError ex) { } c.setCellValue(opt.getExportColumns().get(i).toString()); c.setCellStyle(csTitle); } for (int k = 0; k < opt.getTopRows().size(); k++) { // create a row for each top rows... vo = opt.getTopRows().get(k); rownum = appendRow(wb, s, vo, exportOptions, opt, gettersMethods, csText, csBool, csDecNum, csIntNum, csDate, csTime, csDateTime, rownum, 0); } } // create a row vo = ((VOListResponse) response).getRows().get(j); rownum = appendRow(wb, s, vo, exportOptions, opt, gettersMethods, csText, csBool, csDecNum, csIntNum, csDate, csTime, csDateTime, rownum, 1); } start = start + ((VOListResponse) response).getRows().size(); if (!((VOListResponse) response).isMoreRows()) break; } while (rownum < opt.getMaxRows()); for (int j = 0; j < opt.getBottomRows().size(); j++) { // create a row for each bottom rows... vo = opt.getBottomRows().get(j); rownum = appendRow(wb, s, vo, exportOptions, opt, gettersMethods, csText, csBool, csDecNum, csIntNum, csDate, csTime, csDateTime, rownum, 2); } return rownum; }
From source file:org.opentaps.common.util.UtilCommon.java
License:Open Source License
/** * Creates an Excel document with a given column name list, and column data list. * The String objects in the column name list are used as Map keys to look up the corresponding * column header and data. The column data to be exported is a List of Map objects where * the first Map element contains column headers, and the rest has all the column data. * @param workBookName a String object as Excel file name * @param workSheetName a String object as the name of the Excel sheet * @param columnNameList a List of String objects as column names, they usually correspond to entity field names * @param data a List of Map objects to be exported where the first Map element contains column headers, * and the rest has all the column data. * @throws IOException if an error occurs *//*from w w w .java 2 s . com*/ public static void saveToExcel(final String workBookName, final String workSheetName, final List<String> columnNameList, final List<Map<String, Object>> data) throws IOException { if (StringUtils.isEmpty(workBookName)) { throw new IllegalArgumentException("Argument workBookName can't be empty"); } if (StringUtils.isEmpty(workSheetName)) { throw new IllegalArgumentException("Argument workSheetName can't be empty"); } if (columnNameList == null || columnNameList.isEmpty()) { throw new IllegalArgumentException("Argument columnNameList can't be empty"); } // the data list should have at least one element for the column headers if (data == null || data.isEmpty()) { throw new IllegalArgumentException("Argument data can't be empty"); } FileOutputStream fileOut = new FileOutputStream(new File(workBookName)); assert fileOut != null; HSSFWorkbook workBook = new HSSFWorkbook(); assert workBook != null; HSSFSheet workSheet = workBook.createSheet(workSheetName); assert workSheet != null; // create the header row HSSFRow headerRow = workSheet.createRow(0); assert workSheet != null; HSSFFont headerFont = workBook.createFont(); assert headerFont != null; headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setColor(HSSFColor.BLACK.index); HSSFCellStyle headerCellStyle = workBook.createCellStyle(); assert headerCellStyle != null; headerCellStyle.setFont(headerFont); // the first data list element should always be the column header map Map<String, Object> columnHeaderMap = data.get(0); if (columnHeaderMap != null) { for (short i = 0; i < columnNameList.size(); i++) { HSSFCell cell = headerRow.createCell(i); assert cell != null; cell.setCellStyle(headerCellStyle); Object columnHeaderTitle = columnHeaderMap.get(columnNameList.get(i)); if (columnHeaderTitle != null) { cell.setCellValue(new HSSFRichTextString(columnHeaderTitle.toString())); } } } // create data rows // column data starts from the second element if (data.size() > 1) { // Create the style used for dates. HSSFCellStyle dateCellStyle = workBook.createCellStyle(); String dateFormat = "mm/dd/yyyy hh:mm:ss"; HSSFDataFormat hsfDateFormat = workBook.createDataFormat(); short dateFormatIdx = hsfDateFormat.getFormat(dateFormat); if (dateFormatIdx == -1) { Debug.logWarning("Date format [" + dateFormat + "] could be found or created, try one of the pre-built instead:" + HSSFDataFormat.getBuiltinFormats(), MODULE); } dateCellStyle.setDataFormat(dateFormatIdx); for (int dataRowIndex = 1; dataRowIndex < data.size(); dataRowIndex++) { Map<String, Object> rowDataMap = data.get(dataRowIndex); if (rowDataMap == null) { continue; } HSSFRow dataRow = workSheet.createRow(dataRowIndex); assert dataRow != null; for (short i = 0; i < columnNameList.size(); i++) { HSSFCell cell = dataRow.createCell(i); assert cell != null; Object cellData = rowDataMap.get(columnNameList.get(i)); if (cellData != null) { // Note: dates are actually numeric values in Excel and so the cell need to have // a special style set so it actually displays as a date if (cellData instanceof Calendar) { cell.setCellStyle(dateCellStyle); cell.setCellValue((Calendar) cellData); } else if (cellData instanceof Date) { cell.setCellStyle(dateCellStyle); cell.setCellValue((Date) cellData); } else if (cellData instanceof BigDecimal) { cell.setCellValue(((BigDecimal) cellData).doubleValue()); } else if (cellData instanceof Double) { cell.setCellValue(((Double) cellData).doubleValue()); } else if (cellData instanceof Integer) { cell.setCellValue(((Integer) cellData).doubleValue()); } else if (cellData instanceof BigInteger) { cell.setCellValue(((BigInteger) cellData).doubleValue()); } else { cell.setCellValue(new HSSFRichTextString(cellData.toString())); } } } } } // auto size the column width if (columnHeaderMap != null) { for (short i = 0; i < columnNameList.size(); i++) { workSheet.autoSizeColumn(i); } } // create the Excel file workBook.write(fileOut); fileOut.close(); }
From source file:org.openurp.edu.other.service.OtherExamExportService.java
License:Open Source License
protected HSSFFont xlsFont(HSSFWorkbook workbook, String fontName, short height) { HSSFFont font = workbook.createFont(); font.setFontName(fontName);//from w w w. ja v a 2 s . c o m font.setFontHeightInPoints(height); return font; }
From source file:org.orbeon.oxf.processor.serializer.legacy.XLSSerializer.java
License:Open Source License
protected void readInput(final PipelineContext pipelineContext, ProcessorInput input, Config config, OutputStream outputStream) { try {//from www . j av a 2 s . co m Document dataDocument = readInputAsDOM4J(pipelineContext, INPUT_DATA); final DocumentWrapper wrapper = new DocumentWrapper(dataDocument, null, XPathCache.getGlobalConfiguration()); Document configDocument = readInputAsDOM4J(pipelineContext, INPUT_CONFIG); // Read template sheet String templateName = configDocument.getRootElement().attributeValue("template"); //String fileName = configDocument.getRootElement().attributeValue("filename"); InputStream templateInputStream = URLFactory.createURL(templateName).openStream(); final HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(templateInputStream)); final HSSFDataFormat dataFormat = workbook.createDataFormat(); templateInputStream.close(); int sheetIndex = 0; PooledXPathExpression expr = XPathCache.getXPathExpression(wrapper.getConfiguration(), wrapper, "/workbook/sheet", getLocationData()); List<Object> nodes = expr.evaluateToJavaReturnToPool(); for (Iterator i = nodes.iterator(); i.hasNext();) { final Element sheetElement = (Element) i.next(); HSSFSheet sheet = workbook.cloneSheet(0); workbook.setSheetName(sheetIndex + 1, sheetElement.attributeValue("name")); // Duplicate rows if we find a "repeat-row" in the config for (Iterator j = configDocument.selectNodes("/config/repeat-row").iterator(); j.hasNext();) { // Get info about row to repeat Element repeatRowElement = (Element) j.next(); final int rowNum = Integer.parseInt(repeatRowElement.attributeValue("row-num")); final String forEach = repeatRowElement.attributeValue("for-each"); HSSFRow templateRow = sheet.getRow(rowNum); int repeatCount = ((Double) sheetElement.selectObject("count(" + forEach + ")")).intValue(); // Move existing rows lower int lastRowNum = sheet.getLastRowNum(); for (int k = lastRowNum; k > rowNum; k--) { HSSFRow sourceRow = sheet.getRow(k); HSSFRow newRow = sheet.createRow(k + repeatCount - 1); XLSUtils.copyRow(workbook, newRow, sourceRow); } // Create rows, copying the template row for (int k = rowNum + 1; k < rowNum + repeatCount; k++) { HSSFRow newRow = sheet.createRow(k); XLSUtils.copyRow(workbook, newRow, templateRow); } // Modify the XPath expression on each row for (int k = rowNum; k < rowNum + repeatCount; k++) { HSSFRow newRow = sheet.getRow(k); for (short m = 0; m <= newRow.getLastCellNum(); m++) { HSSFCell cell = newRow.getCell(m); if (cell != null) { String currentFormat = dataFormat.getFormat(cell.getCellStyle().getDataFormat()); final Matcher matcher = FORMAT_XPATH.matcher(currentFormat); if (matcher.find()) { String newFormat = matcher.group(1) + "\"" + forEach + "[" + (k - rowNum + 1) + "]/" + matcher.group(2) + "\""; cell.getCellStyle().setDataFormat(dataFormat.getFormat(newFormat)); } } } } } // Set values in cells with an XPath expression XLSUtils.walk(dataFormat, sheet, new XLSUtils.Handler() { public void cell(HSSFCell cell, String sourceXPath, String targetXPath) { if (sourceXPath.charAt(0) == '/') sourceXPath = sourceXPath.substring(1); // Set cell value PooledXPathExpression expr = XPathCache.getXPathExpression(wrapper.getConfiguration(), wrapper.wrap(sheetElement), "string(" + sourceXPath + ")", getLocationData()); String newValue = (String) expr.evaluateSingleToJavaReturnToPoolOrNull(); if (newValue == null) { throw new OXFException("Nothing matches the XPath expression '" + sourceXPath + "' in the input document"); } try { cell.setCellValue(Double.parseDouble(newValue)); } catch (NumberFormatException e) { cell.setCellValue(newValue); } // Set cell format Object element = sheetElement.selectObject(sourceXPath); if (element instanceof Element) { // NOTE: We might want to support other properties here String bold = ((Element) element).attributeValue("bold"); if (bold != null) { HSSFFont originalFont = workbook.getFontAt(cell.getCellStyle().getFontIndex()); HSSFFont newFont = workbook.createFont(); XLSUtils.copyFont(newFont, originalFont); if ("true".equals(bold)) newFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cell.getCellStyle().setFont(newFont); } } } }); sheetIndex++; } workbook.removeSheetAt(0); // Write out the workbook workbook.write(outputStream); } catch (IOException e) { throw new OXFException(e); } }
From source file:org.rti.zcore.dar.report.StockUsageReport.java
License:Apache License
@Override public void getPatientRegister(Date beginDate, Date endDate, int siteId) { Connection conn = null;//from w w w . j a va2s. c o m try { conn = DatabaseUtils.getZEPRSConnection(org.rti.zcore.Constants.DATABASE_ADMIN_USERNAME); HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle boldStyle = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); boldStyle.setFont(font); HSSFCellStyle headerStyle = wb.createCellStyle(); headerStyle.setWrapText(true); headerStyle.setFont(font); //log.debug("Before getPatientStockMap:" + DateUtils.getTime()); HashMap<Long, List<StockControl>> stockMap = InventoryDAO.getPatientStockMap(conn, siteId, beginDate, endDate); //log.debug("Before EncountersDAO.getAll:" + DateUtils.getTime()); // loop through all of the items //List<Item> items = EncountersDAO.getAll(conn, Long.valueOf(131), "SQL_RETRIEVE_ALL_ADMIN131", Item.class); List<Item> items = null; Map queries = QueryLoader.instance().load("/" + Constants.SQL_GENERATED_PROPERTIES); String sql = (String) queries.get("SQL_RETRIEVE_ALL_ADMIN131") + " ORDER BY item.name"; ArrayList values = new ArrayList(); items = DatabaseUtils.getList(conn, Item.class, sql, values); //log.debug("Before looping through items:" + DateUtils.getTime()); //int j = 0; boolean generateReport = false; for (Item item : items) { Boolean useInReport = item.getUse_in_report(); Long itemId = item.getId(); useInReport = Boolean.TRUE; if ((useInReport != null) && (useInReport.booleanValue() == Boolean.TRUE)) { List<StockControl> patientStockChanges = stockMap.get(itemId); if (patientStockChanges == null) { patientStockChanges = new ArrayList<StockControl>(); } List<StockControl> stockChanges = InventoryDAO.getStockEncounterChanges(conn, itemId, siteId, beginDate, endDate, null, patientStockChanges); int stockChangesSize = stockChanges.size(); //if ((stockChangesSize > 0) || ((stockChangesSize == 0 && (item.getUse_in_report() != null) && (!item.getUse_in_report().equals(Boolean.FALSE))))) { if ((stockChangesSize > 0)) { generateReport = true; StockReport stockReport = InventoryDAO.generateStockSummary(conn, itemId, beginDate, stockChanges, false); // populate the common fields //HSSFSheet sheet = wb.getSheetAt(j); String fixedName = StringManipulation.escapeString(item.getName()).replace("/", " ") .replace("\\", " ").replace(",", "_").replace("[", "").replace("(", "-") .replace(")", "-"); int lenName = fixedName.length(); String itemIdString = String.valueOf(itemId); int lenItemId = itemIdString.length(); String uniqueName = null; if ((lenName + lenItemId) < 31) { uniqueName = fixedName + "_" + itemId; //log.debug(itemId + " size: " + uniqueName.length()); } else { int offset = (30 - lenItemId) - 1; if (lenName > offset) { uniqueName = fixedName.substring(0, offset) + "_" + itemIdString; //log.debug(itemId + " size: " + uniqueName.length()); } else { uniqueName = fixedName + "_" + itemId; } } HSSFSheet sheet = null; try { sheet = wb.createSheet(uniqueName); } catch (IllegalArgumentException e) { log.debug("Problem with name : " + uniqueName + " Error: " + e.getMessage()); //this.setType("error"); // e.printStackTrace(); } String code = ""; if (item.getCode() != null) { code = " (" + item.getCode() + ")"; } if (sheet != null) { //sheet.createRow(0).createCell(0).setCellValue(new HSSFRichTextString(item.getName() + code)); HSSFHeader header = sheet.getHeader(); header.setCenter(item.getName() + code); /*HSSFCell titleCell = sheet.createRow(0).createCell(0); titleCell.setCellStyle(boldStyle); titleCell.setCellValue(new HSSFRichTextString(item.getName() + code));*/ //sheet.createRow(2).createCell(0).setCellValue(new HSSFRichTextString("Beginning Balance")); HSSFRow row = sheet.createRow(0); row.setHeightInPoints((3 * sheet.getDefaultRowHeightInPoints())); HSSFCell cell = row.createCell(0); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Beginning \nBalance")); //sheet.getRow(0).createCell(1).setCellValue(new HSSFRichTextString("Quantity Received this period")); cell = row.createCell(1); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Quantity \nReceived \nthis period")); //sheet.getRow(0).createCell(2).setCellValue(new HSSFRichTextString("Quantity dispensed this period")); cell = row.createCell(2); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Quantity \ndispensed \nthis period")); //sheet.getRow(0).createCell(3).setCellValue(new HSSFRichTextString("Total Issued to Patients")); cell = row.createCell(3); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Total Issued \nto Patients")); //sheet.getRow(0).createCell(4).setCellValue(new HSSFRichTextString("Positive Adjustments")); cell = row.createCell(4); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Positive \nAdjustments")); //sheet.getRow(0).createCell(5).setCellValue(new HSSFRichTextString("Negative Adjustments")); cell = row.createCell(5); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Negative \nAdjustments")); //sheet.getRow(0).createCell(6).setCellValue(new HSSFRichTextString("Ending Balance / Physical Count")); cell = row.createCell(6); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Ending Balance \nPhysical Count")); sheet.autoSizeColumn((short) 0); sheet.autoSizeColumn((short) 1); sheet.autoSizeColumn((short) 2); sheet.autoSizeColumn((short) 3); sheet.autoSizeColumn((short) 4); sheet.autoSizeColumn((short) 5); sheet.autoSizeColumn((short) 6); sheet.createRow(1).createCell(0).setCellValue(stockReport.getBalanceBF()); sheet.getRow(1).createCell(1).setCellValue(stockReport.getAdditionsTotal()); sheet.getRow(1).createCell(2).setCellValue(stockReport.getDeletionsTotal()); sheet.getRow(1).createCell(3).setCellValue(stockReport.getTotalDispensed()); sheet.getRow(1).createCell(4).setCellValue(stockReport.getPosAdjustments()); sheet.getRow(1).createCell(5).setCellValue(stockReport.getNegAdjustments()); sheet.getRow(1).createCell(6).setCellValue(stockReport.getOnHand()); row = sheet.createRow(4); row.setHeightInPoints((3 * sheet.getDefaultRowHeightInPoints())); //sheet.createRow(4).createCell(0).setCellValue(new HSSFRichTextString("Date")); cell = row.createCell(0); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Date")); //sheet.getRow(4).createCell(1).setCellValue(new HSSFRichTextString("Type of Stock Change")); cell = row.createCell(1); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Type of \nStock \nChange")); //sheet.getRow(4).createCell(2).setCellValue(new HSSFRichTextString("Expiry Date")); cell = row.createCell(2); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Expiry \nDate")); //sheet.getRow(4).createCell(3).setCellValue(new HSSFRichTextString("Reference / Notes")); cell = row.createCell(3); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Reference/ \n Notes")); //sheet.getRow(4).createCell(4).setCellValue(new HSSFRichTextString("Additions")); cell = row.createCell(4); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Additions")); //sheet.getRow(4).createCell(5).setCellValue(new HSSFRichTextString("Subtractions")); cell = row.createCell(5); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Subtractions")); //sheet.getRow(4).createCell(6).setCellValue(new HSSFRichTextString("Recorded Balance")); cell = row.createCell(6); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Recorded \nBalance")); //sheet.getRow(4).createCell(7).setCellValue(new HSSFRichTextString("Calculated Balance")); cell = row.createCell(7); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Calculated \nBalance")); sheet.autoSizeColumn((short) 7); int k = 4; for (StockControl stockControl : stockChanges) { if (stockControl.getDate_of_record() != null) { k++; String stockChangeTypeString = null; Integer posAdjust = null; Integer negAdjust = null; if (stockControl.getType_of_change() != null) { int stockChangeType = stockControl.getType_of_change(); switch (stockChangeType) { case 3263: stockChangeTypeString = "Received"; posAdjust = stockControl.getChange_value(); break; case 3264: stockChangeTypeString = "Issued"; negAdjust = stockControl.getChange_value(); break; case 3265: stockChangeTypeString = "Losses"; negAdjust = stockControl.getChange_value(); break; case 3266: stockChangeTypeString = "Pos. Adjust."; posAdjust = stockControl.getChange_value(); break; case 3267: stockChangeTypeString = "Neg. Adjust."; negAdjust = stockControl.getChange_value(); break; case 3279: stockChangeTypeString = "Out-of-stock"; break; default: break; } } row = sheet.createRow(k); if (stockControl.getDate_of_record() != null) { Date dateRecord = stockControl.getDate_of_record(); row.createCell(0) .setCellValue(new HSSFRichTextString(dateRecord.toString())); } else { row.createCell(0).setCellValue(new HSSFRichTextString("")); } row.createCell(1).setCellValue(new HSSFRichTextString(stockChangeTypeString)); if (stockControl.getExpiry_date() != null) { Date expiryDate = stockControl.getExpiry_date(); row.createCell(2) .setCellValue(new HSSFRichTextString(expiryDate.toString())); } else { row.createCell(2).setCellValue(new HSSFRichTextString("")); } row.createCell(3).setCellValue(new HSSFRichTextString(stockControl.getNotes())); if (posAdjust != null) { row.createCell(4).setCellValue(posAdjust); } if (negAdjust != null) { row.createCell(5).setCellValue(negAdjust); } if (stockControl.getBalance() != null) { row.createCell(6).setCellValue(stockControl.getBalance()); } if (stockControl.getComputedBalance() != null) { row.createCell(7).setCellValue(stockControl.getComputedBalance()); } } } } } } //j++; } if (generateReport) { FileOutputStream stream = new FileOutputStream(this.getReportPath()); wb.write(stream); stream.close(); } else { this.setType("empty"); } } catch (Exception e) { e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { log.error(e); } } }
From source file:org.sakaiproject.assignment.impl.BaseAssignmentService.java
License:Educational Community License
private HSSFCellStyle setHeaderStyle(HSSFWorkbook sampleWorkBook) { //TO-DO read style information from sakai.properties HSSFFont font = sampleWorkBook.createFont(); font.setFontName(HSSFFont.FONT_ARIAL); font.setColor(IndexedColors.PLUM.getIndex()); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle cellStyle = sampleWorkBook.createCellStyle(); cellStyle.setFont(font);/*from w ww. ja v a 2 s . c o m*/ return cellStyle; }
From source file:org.sakaiproject.jsf.spreadsheet.SpreadsheetDataFileWriterXls.java
License:Educational Community License
private HSSFWorkbook getAsWorkbook(List<List<Object>> spreadsheetData) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet();/*from w w w . ja va2 s. c o m*/ HSSFCellStyle headerCs = wb.createCellStyle(); Iterator<List<Object>> dataIter = spreadsheetData.iterator(); // Set the header style headerCs.setBorderBottom(HSSFCellStyle.BORDER_THIN); headerCs.setFillBackgroundColor(HSSFColor.BLUE_GREY.index); // Set the font HSSFCellStyle cellStyle = null; String fontName = ServerConfigurationService.getString("spreadsheet.font"); if (fontName != null) { HSSFFont font = wb.createFont(); font.setFontName(fontName); headerCs.setFont(font); cellStyle = wb.createCellStyle(); cellStyle.setFont(font); } // By convention, the first list in the list contains column headers. HSSFRow headerRow = sheet.createRow((short) 0); List<Object> headerList = dataIter.next(); for (short i = 0; i < headerList.size(); i++) { HSSFCell headerCell = createCell(headerRow, i); headerCell.setCellValue((String) headerList.get(i)); headerCell.setCellStyle(headerCs); sheet.autoSizeColumn(i); } short rowPos = 1; while (dataIter.hasNext()) { List<Object> rowData = dataIter.next(); HSSFRow row = sheet.createRow(rowPos++); for (short i = 0; i < rowData.size(); i++) { HSSFCell cell = createCell(row, i); Object data = rowData.get(i); if (data != null) { if (data instanceof Double) { cell.setCellValue(((Double) data).doubleValue()); } else { cell.setCellValue(data.toString()); } if (cellStyle != null) { cell.setCellStyle(cellStyle); } } } } return wb; }
From source file:org.sentilo.web.catalog.view.ExcelBuilder.java
License:Open Source License
@SuppressWarnings("unchecked") @Override/*from w ww. j ava 2 s . c o m*/ protected void buildExcelDocument(final Map<String, Object> model, final HSSFWorkbook workbook, final HttpServletRequest request, final HttpServletResponse response) throws Exception { final List<List<String>> resourceList = (List<List<String>>) model.get(Constants.RESULT_LIST); final List<String> columnsKeys = buildColumnKeys(model); final boolean ignoreFirstValue = (!CollectionUtils.isEmpty(resourceList) && columnsKeys.size() != resourceList.get(0).size()); final HSSFSheet sheet = workbook.createSheet("list"); final HSSFRow header = sheet.createRow(0); final HSSFCellStyle style = workbook.createCellStyle(); final HSSFFont font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); // Call method to put the Column names Headers getHeaderExcel(header, style, columnsKeys); int i = 0; for (final List<String> rowValues : resourceList) { final HSSFRow row = sheet.createRow(++i); // put the content in the rows toExcelRow(rowValues, row, sheet, ignoreFirstValue); } for (int j = 0; j <= columnsKeys.size(); j++) { sheet.autoSizeColumn(j); } }
From source file:org.sevenorcas.style.app.mod.ss.SpreadSheet.java
/** * Set a cell style as header/* w w w .j a v a 2s .c o m*/ * * Thanks to http://www.experts-exchange.com/Programming/Languages/Java/Q_24242777.html * * @param workbook * @param style id * @param rowStyle * @param column * @param wb * @return */ protected void setStyleHeader(HSSFWorkbook wb, HSSFCellStyle style, int styleId) { //EX1, int row, int column, SpreadsheetCell cell){ HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style.setFont(font); style.setLocked(true); style.setBottomBorderColor(HSSFColor.WHITE.index); style.setLeftBorderColor(HSSFColor.WHITE.index); style.setRightBorderColor(HSSFColor.WHITE.index); style.setTopBorderColor(HSSFColor.WHITE.index); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); }
From source file:org.sevenorcas.style.mod.cust.CustListSS.java
/** * Output spreadsheet.//from w ww . j a v a2 s . c o m * * @throws Exception */ public List<SpreadSheet> getSpreadSheetList() throws Exception { SpreadSheet sheet = new SpreadSheet(lang.getLabel("CustS")); SpreadsheetCell cell = sheet.addCell(lang.getLabel("CustS"), STYLE_TITLE_CELL); cell.setCallback(new SpreadsheetCellCallBackI() { public HSSFRichTextString getCellValue(HSSFWorkbook wb, SpreadSheet sheet, HSSFCell cell, String value) { HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 16); HSSFCellStyle style = wb.createCellStyle(); style.setFont(font); cell.setCellStyle(style); return new HSSFRichTextString(value); } }); int cols = 5 + (config.isMultiCountry() ? 1 : 0); mergeColumns(sheet, cell, cols); sheet.incrementRow(); sheet.incrementRow(); sheet.setCol(0); cell = addCellHeader(sheet, FIELD_NR); cell.setBackgroundColorRGB(COLOL_GREY25); addCellHeader(sheet, FIELD_CODE); addCellHeader(sheet, FIELD_LOC); addCellHeader(sheet, FIELD_CTRY); addCellHeader(sheet, "Adr"); addCellHeader(sheet, "AdrC"); // for (ExportObject e: list.getExportObject()){ // sheet.incrementRow(); // sheet.setCol(0); // sheet.addCell (e.number, STYLE_DATA_CELL); // sheet.addCell (e.code, STYLE_DATA_CELL); // sheet.addCell (e.loc, STYLE_DATA_CELL); // if (config.isMultiCountry()){ // sheet.addCell (e.country, STYLE_DATA_CELL); // } // sheet.addCell (e.address, STYLE_DATA_CELL); // sheet.addCell (e.contact, STYLE_DATA_CELL); // } List<SpreadSheet> sheets = new ArrayList<SpreadSheet>(); sheets.add(sheet); return sheets; }