List of usage examples for org.apache.poi.hssf.usermodel HSSFFont BOLDWEIGHT_BOLD
short BOLDWEIGHT_BOLD
To view the source code for org.apache.poi.hssf.usermodel HSSFFont BOLDWEIGHT_BOLD.
Click Source Link
From source file:org.openmrs.module.tracnetreporting.utils.exports.ExportToExcelUtil.java
License:Open Source License
/** * Exports data to the Excel File//from ww w. j av a 2s . com * * @throws IOException * @see org.openmrs.module.tracnetreporting.service.TracNetIndicatorService#exportDataToExcelFile(java.util.Map) */ @SuppressWarnings("deprecation") public static void exportDataToExcelFile(HttpServletRequest request, HttpServletResponse response, Map<String, Integer> indicatorsList, String filename, String title, String startDate, String endDate) throws IOException { HSSFWorkbook workbook = new HSSFWorkbook(); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=\"" + filename + "\""); HSSFSheet sheet = workbook.createSheet(title); int count = 0; sheet.setDisplayRowColHeadings(true); //Setting Style HSSFFont font = workbook.createFont(); HSSFCellStyle cellStyle = workbook.createCellStyle(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFFont.COLOR_RED); cellStyle.setFillForegroundColor((short) 0xA); // Title HSSFRow row = sheet.createRow((short) 0); HSSFCell cell = row.createCell((short) 0); cell.setCellValue(""); row.setRowStyle(cellStyle); row.createCell((short) 1).setCellValue("" + title + "(Between " + startDate + " and " + endDate + ")"); // Headers row = sheet.createRow((short) 2); row.createCell((short) 0).setCellValue("#"); row.createCell((short) 1).setCellValue("INDICATOR NAME"); row.createCell((short) 2).setCellValue("INDICATOR"); for (String indicator : indicatorsList.keySet()) { count++; row = sheet.createRow((short) count + 3); row.createCell((short) 0).setCellValue(count); row.createCell((short) 1).setCellValue(indicator.toString()); row.createCell((short) 2).setCellValue(indicatorsList.get(indicator).toString()); } OutputStream outputStream = response.getOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); }
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 */// www .jav a 2 s . c om 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.impl.OtherExamExportServiceImpl.java
License:Open Source License
private void prepareStyleAndFont(HSSFWorkbook workbook) { HSSFFont font_common = xlsFont(workbook, "", (short) 11); style_hirizonal_common = xlsBorderStyle(workbook, "", 0); style_hirizonal_common.setAlignment(HSSFCellStyle.ALIGN_CENTER); style_hirizonal_common.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style_hirizonal_common.setFont(font_common); style_hirizonal_common.setWrapText(true); HSSFFont font_bold = xlsFont(workbook, "", (short) 11); font_bold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style_hirizonal_bold = xlsBorderStyle(workbook, "", 0); style_hirizonal_bold.setAlignment(HSSFCellStyle.ALIGN_CENTER); style_hirizonal_bold.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style_hirizonal_bold.setFont(font_bold); }
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 ww w . j av a 2s . 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.projectforge.excel.XlsContentProvider.java
License:Open Source License
private void createFonts() { FONT_HEADER = workbook.createFont(); FONT_HEADER.setFontHeightInPoints(FONT_HEADER_SIZE); FONT_HEADER.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); FONT_NORMAL_BOLD = workbook.createFont(); FONT_NORMAL_BOLD.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); FONT_WHITE_BOLD = workbook.createFont(); FONT_WHITE_BOLD.setColor(HSSFColor.WHITE.index); FONT_WHITE_BOLD.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); FONT_RED = workbook.createFont();/* w ww. j av a 2s .co m*/ FONT_RED.setColor(HSSFColor.RED.index); FONT_RED_BOLD = workbook.createFont(); FONT_RED_BOLD.setColor(HSSFColor.RED.index); FONT_RED_BOLD.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); FONT_NORMAL = workbook.createFont(); }
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 va2 s . 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 w w .j a v a 2 s . c o m return cellStyle; }
From source file:org.sevenorcas.style.app.mod.ss.SpreadSheet.java
/** * Set a cell style as header/*from ww w .j a va 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.sharegov.cirm.utils.ExcelExportUtil.java
License:Apache License
public void exportData(OutputStream out, Json allData) throws IOException { //Set the filename Date dt = new Date(); SimpleDateFormat fmt = new SimpleDateFormat("MM-dd-yyyy"); String filename = fmt.format(dt); // Create Excel Workbook and Sheet HSSFWorkbook wb = new HSSFWorkbook(); sheet = wb.createSheet(filename);// ww w . j a v a2 s .com HSSFHeader header = sheet.getHeader(); header.setCenter(filename); HSSFFont boldFont = wb.createFont(); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle boldStyle = wb.createCellStyle(); boldStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); boldStyle.setFont(boldFont); boldStyle.setWrapText(true); //Start : populate the spreadsheet int rowCounter = 0; rowCounter = searchCriteriaRows(allData, boldStyle); rowCounter = headerRow(allData, boldStyle, rowCounter); int headingsRowSplitter = rowCounter; rowCounter = dataRows(allData, rowCounter); //end : populate the spreadsheet // Freeze Panes on Header Row sheet.createFreezePane(0, headingsRowSplitter); // Row 1 Repeats on each page wb.setRepeatingRowsAndColumns(0, 0, 0, 0, headingsRowSplitter); // Set Print Area, Footer int colCount = allData.at("metaData").at("columns").asInteger(); wb.setPrintArea(0, 0, colCount, 0, rowCounter); HSSFFooter footer = sheet.getFooter(); footer.setCenter("Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages()); // Fit Sheet to 1 page wide but very long sheet.setAutobreaks(true); HSSFPrintSetup ps = sheet.getPrintSetup(); ps.setFitWidth((short) 1); ps.setFitHeight((short) 9999); sheet.setGridsPrinted(true); sheet.setHorizontallyCenter(true); ps.setPaperSize(HSSFPrintSetup.LETTER_PAPERSIZE); if (colCount > 5) { ps.setLandscape(true); } if (colCount > 10) { ps.setPaperSize(HSSFPrintSetup.LEGAL_PAPERSIZE); } if (colCount > 14) { ps.setPaperSize(HSSFPrintSetup.EXECUTIVE_PAPERSIZE); } // Set Margins ps.setHeaderMargin((double) .35); ps.setFooterMargin((double) .35); sheet.setMargin(HSSFSheet.TopMargin, (double) .50); sheet.setMargin(HSSFSheet.BottomMargin, (double) .50); sheet.setMargin(HSSFSheet.LeftMargin, (double) .50); sheet.setMargin(HSSFSheet.RightMargin, (double) .50); // Write out the spreadsheet wb.write(out); out.close(); }
From source file:org.talend.mdm.webapp.browserecords.server.servlet.DownloadData.java
License:Open Source License
@Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { XSSFWorkbook workbook = new XSSFWorkbook(); cs = workbook.createCellStyle();/*from w ww.j a v a 2 s. co m*/ XSSFFont f = workbook.createFont(); f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cs.setFont(f); XSSFSheet sheet = workbook.createSheet(SHEET_LABEL); sheet.setDefaultColumnWidth((short) 20); XSSFRow row = sheet.createRow((short) 0); try { setParameter(request); response.reset(); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //$NON-NLS-1$ response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\""); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ fillHeader(row); fillSheet(sheet); } catch (Exception e) { LOG.error(e.getMessage(), e); } OutputStream out = response.getOutputStream(); workbook.write(out); out.close(); }