List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setFont
public void setFont(HSSFFont font)
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.j ava 2 s . co m 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.orbeon.oxf.util.XLSUtils.java
License:Open Source License
public static void copyCell(HSSFWorkbook workbook, HSSFCell destination, HSSFCell source) { // Copy cell content destination.setCellType(source.getCellType()); switch (source.getCellType()) { case HSSFCell.CELL_TYPE_BOOLEAN: destination.setCellValue(source.getBooleanCellValue()); break;/*w w w .j a v a2 s . c om*/ case HSSFCell.CELL_TYPE_FORMULA: case HSSFCell.CELL_TYPE_STRING: destination.setCellValue(source.getStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: destination.setCellValue(source.getNumericCellValue()); break; } // Copy cell style HSSFCellStyle sourceCellStyle = source.getCellStyle(); HSSFCellStyle destinationCellStyle = workbook.createCellStyle(); destinationCellStyle.setAlignment(sourceCellStyle.getAlignment()); destinationCellStyle.setBorderBottom(sourceCellStyle.getBorderBottom()); destinationCellStyle.setBorderLeft(sourceCellStyle.getBorderLeft()); destinationCellStyle.setBorderRight(sourceCellStyle.getBorderRight()); destinationCellStyle.setBorderTop(sourceCellStyle.getBorderTop()); destinationCellStyle.setBottomBorderColor(sourceCellStyle.getBottomBorderColor()); destinationCellStyle.setDataFormat(sourceCellStyle.getDataFormat()); destinationCellStyle.setFillBackgroundColor(sourceCellStyle.getFillForegroundColor()); destinationCellStyle.setFillForegroundColor(sourceCellStyle.getFillForegroundColor()); destinationCellStyle.setFillPattern(sourceCellStyle.getFillPattern()); destinationCellStyle.setFont(workbook.getFontAt(sourceCellStyle.getFontIndex())); destinationCellStyle.setHidden(sourceCellStyle.getHidden()); destinationCellStyle.setIndention(sourceCellStyle.getIndention()); destinationCellStyle.setLeftBorderColor(sourceCellStyle.getLeftBorderColor()); destinationCellStyle.setLocked(sourceCellStyle.getLocked()); destinationCellStyle.setRightBorderColor(sourceCellStyle.getRightBorderColor()); destinationCellStyle.setRotation(sourceCellStyle.getRotation()); destinationCellStyle.setTopBorderColor(sourceCellStyle.getTopBorderColor()); destinationCellStyle.setVerticalAlignment(sourceCellStyle.getVerticalAlignment()); destinationCellStyle.setWrapText(sourceCellStyle.getWrapText()); destination.setCellStyle(destinationCellStyle); }
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;/* w ww. jav a2 s . c om*/ 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); 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 ww .j a va 2s . co 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/*w w w. j av a2s. co 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//from ww w .j a v a2s . co 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./*ww w . j av a 2 s .co 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; }
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);/*from ww w . ja v a 2 s .co m*/ 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.ExportingServlet.java
License:Open Source License
@Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { org.apache.log4j.Logger.getLogger(this.getClass()).info("SERVLET exporting for excel "); //$NON-NLS-1$ DateFormat df = new SimpleDateFormat("dd-MM-yyyy"); //$NON-NLS-1$ response.reset();//from w w w . j a v a 2 s .c o m response.setContentType("application/vnd.ms-excel"); //$NON-NLS-1$ String theReportFile = "Reporting_" + df.format(new Date()) + ".xls"; //$NON-NLS-1$ //$NON-NLS-2$ response.setHeader("Content-Disposition", "attachment; filename=\"" + theReportFile + "\""); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("new sheet"); //$NON-NLS-1$ sheet.setDefaultColumnWidth((short) 20); String parametersValues = request.getParameter("params"); //$NON-NLS-1$ if (parametersValues == null) { parametersValues = ""; //$NON-NLS-1$ } org.apache.log4j.Logger.getLogger(this.getClass()).debug("params =" + parametersValues); //$NON-NLS-1$ boolean splitEnd = false; String tmpSplit = parametersValues; Vector<String> paramVector = new Vector<String>(); while (!splitEnd) { int indexMatch = tmpSplit.indexOf("###"); //$NON-NLS-1$ if (indexMatch == -1) { paramVector.add(tmpSplit); splitEnd = true; } else { if (indexMatch > 0) { String tmpParam = tmpSplit.substring(0, indexMatch); paramVector.add(tmpParam); } else { paramVector.add(""); //$NON-NLS-1$ } if (indexMatch + 3 >= tmpSplit.length()) { tmpSplit = ""; //$NON-NLS-1$ } else { tmpSplit = tmpSplit.substring(indexMatch + 3); } } } // String []parameters = parametersValues.split("###"); String[] parameters = new String[paramVector.size()]; for (int i = 0; i < paramVector.size(); i++) { parameters[i] = paramVector.get(i); } org.apache.log4j.Logger.getLogger(this.getClass()).debug("nb params =" + parameters.length); //$NON-NLS-1$ try { WSDataClusterPK wsDataClusterPK = new WSDataClusterPK(); String entity = null; String contentWords = null; String keys = null; Long fromDate = new Long(-1); Long toDate = new Long(-1); String fkvalue = null; String dataObject = null; if (parametersValues != null && parametersValues.length() > 0) { JSONObject criteria = new JSONObject(parametersValues); Configuration configuration = Configuration.getConfiguration(); wsDataClusterPK.setPk(configuration.getCluster()); entity = !criteria.isNull("entity") ? (String) criteria.get("entity") : ""; //$NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$ keys = !criteria.isNull("key") && !"*".equals(criteria.get("key")) ? (String) criteria.get("key") //$NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$//$NON-NLS-4$ : ""; //$NON-NLS-1$ fkvalue = !criteria.isNull("fkvalue") && !"*".equals(criteria.get("fkvalue")) //$NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$ ? (String) criteria.get("fkvalue") //$NON-NLS-1$ : ""; //$NON-NLS-1$ dataObject = !criteria.isNull("dataObject") && !"*".equals(criteria.get("dataObject")) //$NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$ ? (String) criteria.get("dataObject") //$NON-NLS-1$ : ""; //$NON-NLS-1$ contentWords = !criteria.isNull("keyWords") ? (String) criteria.get("keyWords") : ""; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ if (!criteria.isNull("fromDate")) { //$NON-NLS-1$ String startDate = (String) criteria.get("fromDate"); //$NON-NLS-1$ SimpleDateFormat dataFmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //$NON-NLS-1$ java.util.Date date = dataFmt.parse(startDate); fromDate = date.getTime(); } if (!criteria.isNull("toDate")) { //$NON-NLS-1$ String endDate = (String) criteria.get("toDate"); //$NON-NLS-1$ SimpleDateFormat dataFmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //$NON-NLS-1$ java.util.Date date = dataFmt.parse(endDate); toDate = date.getTime(); } } BusinessConcept businessConcept = SchemaWebAgent.getInstance().getBusinessConcept(entity); Map<String, String> foreignKeyMap = businessConcept.getForeignKeyMap(); Set<String> foreignKeyXpath = foreignKeyMap.keySet(); Set<String> xpathes = new HashSet<String>(); for (String path : foreignKeyXpath) { String dataObjectPath = foreignKeyMap.get(path); if (dataObjectPath.indexOf(dataObject) != -1) { xpathes.add(path.substring(1)); } } List<String> types = SchemaWebAgent.getInstance().getBindingType(businessConcept.getE()); for (String type : types) { List<ReusableType> subTypes = SchemaWebAgent.getInstance().getMySubtypes(type); for (ReusableType reusableType : subTypes) { Map<String, String> fks = SchemaWebAgent.getInstance().getReferenceEntities(reusableType, dataObject); Collection<String> fkPaths = fks != null ? fks.keySet() : null; for (String fkpath : fkPaths) { if (fks.get(fkpath).indexOf(dataObject) != -1) { xpathes.add(fkpath); } } } } Map<String, String> inheritanceForeignKeyMap = businessConcept.getInheritanceForeignKeyMap(); for (Map.Entry<String, String> entry : inheritanceForeignKeyMap.entrySet()) { if (entry.getValue().indexOf(dataObject) != -1) { xpathes.add(entry.getKey().substring(1)); } } StringBuilder keysb = new StringBuilder(); keysb.append("$"); //$NON-NLS-1$ keysb.append(joinSet(xpathes, ",")); //$NON-NLS-1$ keysb.append("$"); //$NON-NLS-1$ keysb.append(fkvalue); WSItemPKsByCriteriaResponse results = Util.getPort().getItemPKsByFullCriteria( new WSGetItemPKsByFullCriteria(new WSGetItemPKsByCriteria(wsDataClusterPK, entity, contentWords, keysb.toString(), keys, fromDate, toDate, 0, Integer.MAX_VALUE), false)); // create a cell style HSSFCellStyle cs = wb.createCellStyle(); HSSFFont f = wb.createFont(); f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cs.setFont(f); HSSFRow row = sheet.createRow((short) 0); if (results.getResults().length > 0) { row.createCell((short) 0).setCellValue("date"); //$NON-NLS-1$ row.createCell((short) 1).setCellValue("entity"); //$NON-NLS-1$ row.createCell((short) 2).setCellValue("key"); //$NON-NLS-1$ } // set a style for these cells for (int i = 0; i < 3; i++) { row.getCell((short) i).setCellStyle(cs); } for (int i = 0; i < results.getResults().length; i++) { WSItemPKsByCriteriaResponseResults result = results.getResults()[i]; if (i == 0) { continue; } row = sheet.createRow((short) i); SimpleDateFormat dataFmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //$NON-NLS-1$ String date = dataFmt.format(result.getDate()); row.createCell((short) 0).setCellValue(date); row.createCell((short) 1).setCellValue(result.getWsItemPK().getConceptName()); String[] ids = result.getWsItemPK().getIds(); StringBuilder sb = new StringBuilder(); if (ids != null) { for (String id : ids) { sb.append(id); } } row.createCell((short) 2).setCellValue(sb.toString()); } } catch (RemoteException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } // Write the output OutputStream out = response.getOutputStream(); wb.write(out); out.close(); }