List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createSheet
@Override
public HSSFSheet createSheet(String sheetname)
From source file:com.quix.aia.cn.imo.mapper.InterviewMaintenance.java
License:Open Source License
/** * <p>Method is used to create csv to store registration list for 3rd interview </p> * @param HttpServletRequest request //from ww w. ja v a 2s. c om * @return csv path as String */ public String createCsv(ArrayList regList, HttpServletRequest request) { log.log(Level.INFO, "Csv Generation Starts"); String xlsPath = ""; DateFormat df = new SimpleDateFormat("yyyy-MM-dd_HH.mm.ss"); FileWriter fw = null; /*BufferedWriter writer=null; String serverFilename = request.getRealPath("/"); if(!serverFilename.endsWith("/")) serverFilename = serverFilename + File.separator; try{ serverFilename = serverFilename + "resources" + File.separator + "downloads" + File.separator + "csv"; File dir = new File(serverFilename); if(!dir.exists()) dir.mkdirs(); serverFilename = serverFilename + File.separator +df.format(new Date()) + ".csv" ; File file = new File(serverFilename); if(!file.exists()) file.createNewFile(); writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file), "UTF-8")); writer.write("Interview Session Name|"); writer.write("Interview Date|"); writer.write("Start Time|"); writer.write("End Time|"); writer.write("Candidate Name|"); writer.write("Servicing Agent|"); writer.write("Agent Name|"); writer.write("BU|"); writer.write("District|"); writer.write("Branch|"); writer.write("City|"); writer.write("Office|"); writer.write("SSC|"); writer.write("Agency Leader Code|"); writer.write("Agency Leader Name|"); writer.write("Source of Referral|"); writer.write("Age|"); writer.write("Date of Birth|"); writer.write("Gender|"); writer.write("Contact Number|"); writer.write("CC Test Result|"); writer.write("Recruitment Scheme|"); writer.newLine(); if(regList!=null && regList.size()>0){ Iterator iteObj= regList.iterator(); for(int i=0;i<regList.size();i++){ Object [] obj= (Object []) iteObj.next(); writer.write(obj[0]+"|"); //Interview Session Name if(obj[1]!=null ) writer.write(LMSUtil.convertDateToString((Date)obj[1]) +"|"); //Interview Date else writer.write("|"); if(obj[2]!=null ) writer.write(LMSUtil.converDateIntoHHMMAMPM((Date)obj[2]) +"|");//Start Time else writer.write("|"); if(obj[3]!=null) writer.write(LMSUtil.converDateIntoHHMMAMPM((Date)obj[3]) +"|");//End Time else writer.write("|"); if(obj[4]!=null)writer.write(obj[4]+"|");else writer.write("|");//Candidate Name if(obj[5]!=null)writer.write(obj[5]+"|");else writer.write("|");//Servicing Agent if(obj[6]!=null)writer.write(obj[6]+"|");else writer.write("|");//Agent Name if(obj[7]!=null)writer.write(obj[7]+"|");else writer.write("|");//BU if(obj[8]!=null)writer.write(obj[8]+"|");else writer.write("|");//District if(obj[9]!=null)writer.write(obj[9]+"|");else writer.write("|");//Branch if(obj[10]!=null)writer.write(obj[10]+"|");else writer.write("|");//City if(obj[11]!=null)writer.write(obj[11]+"|");else writer.write("|");//Office if(obj[12]!=null)writer.write(obj[12]+"|");else writer.write("|");//SSC if(obj[13]!=null)writer.write(obj[13]+"|");else writer.write("|");//Agency Leader Code if(obj[14]!=null)writer.write(obj[14]+"|");else writer.write("|");//Agency Leader Name if(obj[15]!=null)writer.write(obj[15]+"|");else writer.write("|");//Source of Referral if(obj[16]!=null)writer.write(obj[16]+"|");else writer.write("|");//Age if(obj[17]!=null ) writer.write(LMSUtil.convertDateToString((Date)obj[17]) +"|");//DOB else writer.write("|"); String gender = ""; if(obj[18]!=null){ if("F".equalsIgnoreCase((String)obj[18])) gender = "Female"; else if("M".equalsIgnoreCase((String)obj[18]))//Gender gender = "Male"; } writer.write(gender+"|"); if(obj[19]!=null)writer.write(obj[19]+"|");else writer.write("|");//Contact Number if(obj[20]!=null)writer.write(obj[20]+"|");else writer.write("|");//CC Test Result if(obj[21]!=null)writer.write(obj[21]+"|");else writer.write("|");//Recruitment Scheme writer.newLine(); } }else{ log.log(Level.INFO,"InterviewMaintenace - > createCsv : No candidate registration for 3rd interview"); } }catch(Exception e){ log.log(Level.SEVERE, e.getMessage()); e.printStackTrace();LogsMaintenance logsMain=new LogsMaintenance(); StringWriter errors = new StringWriter(); e.printStackTrace(new PrintWriter(errors)); logsMain.insertLogs("InterViewMaintenance",Level.SEVERE+"",errors.toString()); }finally{ try{ if(writer != null){ writer.flush(); } if(writer != null){ writer.close(); } if(fw!=null) fw.close(); }catch(Exception ioExc){ log.log(Level.SEVERE, ioExc.getMessage()); ioExc.printStackTrace(); } } */ ArrayList arList = null; ArrayList al = null; String thisLine; int count = 0; try { xlsPath = request.getRealPath("/") + "resources" + File.separator + "downloads" + File.separator + "xls"; File dir = new File(xlsPath); if (!dir.exists()) dir.mkdirs(); //df.format(new Date()) xlsPath = xlsPath + File.separator + df.format(new Date()) + ".xls"; HSSFWorkbook hwb = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet) hwb.createSheet("Sheet1"); int rownum = 0; HSSFRow row = sheet.createRow(rownum++); int cellnum = 0; HSSFCell cell = row.createCell((short) cellnum++); cell.setCellValue("? ?"); cell = row.createCell((short) cellnum++); cell.setCellValue("? "); cell = row.createCell((short) cellnum++); cell.setCellValue(" "); cell = row.createCell((short) cellnum++); cell.setCellValue("? "); cell = row.createCell((short) cellnum++); cell.setCellValue(" ??"); cell = row.createCell((short) cellnum++); cell.setCellValue("??"); cell = row.createCell((short) cellnum++); cell.setCellValue("????"); cell = row.createCell((short) cellnum++); cell.setCellValue("?"); cell = row.createCell((short) cellnum++); cell.setCellValue(""); cell = row.createCell((short) cellnum++); cell.setCellValue("?"); cell = row.createCell((short) cellnum++); cell.setCellValue(""); cell = row.createCell((short) cellnum++); cell.setCellValue("?"); cell = row.createCell((short) cellnum++); cell.setCellValue("SSC"); cell = row.createCell((short) cellnum++); cell.setCellValue(""); cell = row.createCell((short) cellnum++); cell.setCellValue("??"); cell = row.createCell((short) cellnum++); cell.setCellValue("?? ?"); cell = row.createCell((short) cellnum++); cell.setCellValue("Age"); cell = row.createCell((short) cellnum++); cell.setCellValue(""); cell = row.createCell((short) cellnum++); cell.setCellValue(""); cell = row.createCell((short) cellnum++); cell.setCellValue("? ??"); cell = row.createCell((short) cellnum++); cell.setCellValue("CC "); cell = row.createCell((short) cellnum++); cell.setCellValue("?"); cell = row.createCell((short) cellnum++); if (regList != null && regList.size() > 0) { Iterator iteObj = regList.iterator(); for (int i = 0; i < regList.size(); i++) { row = sheet.createRow(rownum++); cellnum = 0; Object[] obj = (Object[]) iteObj.next(); //writer.write(obj[0]+"|"); //Interview Session Name if (obj[0] instanceof String) cell = row.createCell((short) cellnum++); cell.setCellValue((String) obj[0]); if (obj[1] != null) { //writer.write(LMSUtil.convertDateToString((Date)obj[1]) +"|"); //Interview Date if (obj[1] instanceof Date) { cell = row.createCell((short) cellnum++); cell.setCellValue(LMSUtil.convertDateToString((Date) obj[1])); } } else { cell = row.createCell((short) cellnum++); cell.setCellValue(""); } if (obj[2] != null) { //writer.write(LMSUtil.converDateIntoHHMMAMPM((Date)obj[2]) +"|");//Start Time if (obj[2] instanceof Date) { cell = row.createCell((short) cellnum++); cell.setCellValue(LMSUtil.converDateIntoHHMMAMPM((Date) obj[2])); } } else { //writer.write("|"); cell = row.createCell((short) cellnum++); cell.setCellValue(""); } if (obj[3] != null) { //writer.write(LMSUtil.converDateIntoHHMMAMPM((Date)obj[3]) +"|");//End Time if (obj[3] instanceof Date) { cell = row.createCell((short) cellnum++); cell.setCellValue(LMSUtil.converDateIntoHHMMAMPM((Date) obj[3])); } } else { //writer.write("|"); cell = row.createCell((short) cellnum++); cell.setCellValue(""); } if (obj[4] != null) { //writer.write(obj[4]+"|");//Candidate Name cell = row.createCell((short) cellnum++); cell.setCellValue((String) obj[4]); } else { cell = row.createCell((short) cellnum++); cell.setCellValue(""); } if (obj[5] != null) { //Servicing Agent cell = row.createCell((short) cellnum++); cell.setCellValue((String) obj[5]); } else { cell = row.createCell((short) cellnum++); cell.setCellValue(""); } if (obj[6] != null) { //Agent Name cell = row.createCell((short) cellnum++); cell.setCellValue((String) obj[6]); } else { cell = row.createCell((short) cellnum++); cell.setCellValue(""); } if (obj[7] != null) { //BU cell = row.createCell((short) cellnum++); cell.setCellValue((String) obj[7]); } else { cell = row.createCell((short) cellnum++); cell.setCellValue(""); } if (obj[8] != null) { //District cell = row.createCell((short) cellnum++); cell.setCellValue((String) obj[8]); } else { cell = row.createCell((short) cellnum++); cell.setCellValue(""); } if (obj[9] != null) { //Branch cell = row.createCell((short) cellnum++); cell.setCellValue((String) obj[9]); } else { cell = row.createCell((short) cellnum++); cell.setCellValue(""); } if (obj[10] != null) { //City cell = row.createCell((short) cellnum++); cell.setCellValue((String) obj[10]); } else { cell = row.createCell((short) cellnum++); cell.setCellValue(""); } if (obj[11] != null) { //Office cell = row.createCell((short) cellnum++); cell.setCellValue((String) obj[11]); } else { cell = row.createCell((short) cellnum++); cell.setCellValue(""); } if (obj[12] != null) { //SSC cell = row.createCell((short) cellnum++); cell.setCellValue((String) obj[12]); } else { cell = row.createCell((short) cellnum++); cell.setCellValue(""); } if (obj[13] != null) { //Agency Leader Code cell = row.createCell((short) cellnum++); cell.setCellValue((String) obj[13]); } else { cell = row.createCell((short) cellnum++); cell.setCellValue(""); } if (obj[14] != null) { //Agency Leader Name cell = row.createCell((short) cellnum++); cell.setCellValue((String) obj[14]); } else { cell = row.createCell((short) cellnum++); cell.setCellValue(""); } if (obj[15] != null) { //Source of Referral cell = row.createCell((short) cellnum++); cell.setCellValue((String) obj[15]); } else { cell = row.createCell((short) cellnum++); cell.setCellValue(""); } if (obj[16] != null) { //Age cell = row.createCell((short) cellnum++); cell.setCellValue((Integer) obj[16]); } else { cell = row.createCell((short) cellnum++); cell.setCellValue(""); } if (obj[17] != null) { //writer.write(LMSUtil.convertDateToString((Date)obj[17]) +"|");//DOB if (obj[3] instanceof Date) { cell = row.createCell((short) cellnum++); cell.setCellValue(LMSUtil.convertDateToString((Date) obj[17])); } } else { cell = row.createCell((short) cellnum++); cell.setCellValue(""); } String gender = ""; if (obj[18] != null) { if ("F".equalsIgnoreCase((String) obj[18])) gender = "Female"; else if ("M".equalsIgnoreCase((String) obj[18]))//Gender gender = "Male"; } cell = row.createCell((short) cellnum++); cell.setCellValue(gender); if (obj[19] != null) { //Contact Number cell = row.createCell((short) cellnum++); cell.setCellValue((String) obj[19]); } else { cell = row.createCell((short) cellnum++); cell.setCellValue(""); } if (obj[20] != null) { //CC Test Result cell = row.createCell((short) cellnum++); cell.setCellValue((String) obj[20]); } else { cell = row.createCell((short) cellnum++); cell.setCellValue(""); } if (obj[21] != null) { //Recruitment Scheme cell = row.createCell((short) cellnum++); cell.setCellValue((String) obj[21]); } else { cell = row.createCell((short) cellnum++); cell.setCellValue(""); } } } FileOutputStream fileOut = new FileOutputStream(xlsPath); hwb.write(fileOut); fileOut.close(); System.out.println("Your excel file has been generated"); } catch (Exception ex) { ex.printStackTrace(); } //main method ends return xlsPath; }
From source file:com.safetys.framework.jmesa.view.excel.ExcelView.java
License:Apache License
public Object render() { HSSFWorkbook workbook = new HSSFWorkbook(); Table table = this.getTable(); String caption = table.getCaption(); if (StringUtils.isEmpty(caption)) { caption = "JMesa Export"; }/*from www . j a v a2 s . c o m*/ HSSFSheet sheet = workbook.createSheet(caption); Row row = table.getRow(); row.getRowRenderer(); List<Column> columns = table.getRow().getColumns(); // renderer header HSSFRow hssfRow = sheet.createRow(0); int columncount = 0; for (Column col : columns) { HSSFCell cell = hssfRow.createCell((short) columncount++); cell.setCellValue(new HSSFRichTextString(col.getTitle())); } // renderer body Collection<?> items = getCoreContext().getPageItems(); int rowcount = 1; for (Object item : items) { HSSFRow r = sheet.createRow(rowcount++); columncount = 0; for (Column col : columns) { HSSFCell cell = r.createCell((short) columncount++); Object value = col.getCellRenderer().render(item, rowcount); if (value == null) { value = ""; } if (value instanceof Number) { Double number = Double.valueOf(value.toString()); cell.setCellValue(number); } else { cell.setCellValue(new HSSFRichTextString(value.toString())); } } } return workbook; }
From source file:com.sammyun.ExcelView.java
License:Open Source License
/** * ?Excel/* w w w .ja v a2 s.c om*/ * * @param model ? * @param workbook workbook * @param request request * @param response response */ public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Assert.notEmpty(properties); HSSFSheet sheet; if (StringUtils.isNotEmpty(sheetName)) { sheet = workbook.createSheet(sheetName); } else { sheet = workbook.createSheet(); } int rowNumber = 0; if (titles != null && titles.length > 0) { HSSFRow header = sheet.createRow(rowNumber); header.setHeight((short) 400); for (int i = 0; i < properties.length; i++) { HSSFCell cell = header.createCell(i); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); cell.setCellStyle(cellStyle); if (i == 0) { HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFComment comment = patriarch .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4)); comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B" + "y" + " " + "M" + "o" + "S" + "ho" + "o" + "p")); cell.setCellComment(comment); } if (titles.length > i && titles[i] != null) { cell.setCellValue(titles[i]); } else { cell.setCellValue(properties[i]); } if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } rowNumber++; } if (data != null) { for (Object item : data) { HSSFRow row = sheet.createRow(rowNumber); for (int i = 0; i < properties.length; i++) { HSSFCell cell = row.createCell(i); if (converters != null && converters.length > i && converters[i] != null) { Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]); ConvertUtils.register(converters[i], clazz); cell.setCellValue(BeanUtils.getProperty(item, properties[i])); ConvertUtils.deregister(clazz); if (clazz.equals(Date.class)) { DateConverter dateConverter = new DateConverter(); dateConverter.setPattern(DEFAULT_DATE_PATTERN); ConvertUtils.register(dateConverter, Date.class); } } else { cell.setCellValue(BeanUtils.getProperty(item, properties[i])); } if (rowNumber == 0 || rowNumber == 1) { if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } } rowNumber++; } } if (contents != null && contents.length > 0) { rowNumber++; for (String content : contents) { HSSFRow row = sheet.createRow(rowNumber); HSSFCell cell = row.createCell(0); HSSFCellStyle cellStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.GREY_50_PERCENT.index); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell.setCellValue(content); rowNumber++; } } response.setContentType("application/force-download"); if (StringUtils.isNotEmpty(filename)) { response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8")); } else { response.setHeader("Content-disposition", "attachment"); } }
From source file:com.save.reports.ExportDataGridToExcel.java
public void workSheet() { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Sample sheet"); int rownum = 1; for (Object itemId : getDataGrid().getContainerDataSource().getItemIds()) { Row row = sheet.createRow(rownum); int cellcount = 0; if ((rownum - 1) == 0) { Row rowHeader = sheet.createRow(rownum - 1); for (Object propertyId : getDataGrid().getContainerDataSource().getContainerPropertyIds()) { Cell cell = rowHeader.createCell(cellcount); cell.setCellValue(propertyId.toString().toUpperCase()); sheet.autoSizeColumn(cellcount); cellcount++;//from w w w . j a v a2 s .co m } } Item item = getDataGrid().getContainerDataSource().getItem(itemId); int cellnum = 0; for (Object propertyId : item.getItemPropertyIds()) { Cell cell = row.createCell(cellnum); if (propertyId.equals("employee")) { cell.setCellValue(item.getItemProperty(propertyId).getValue().toString().toUpperCase()); } else { cell.setCellValue((item.getItemProperty(propertyId).getValue() == null) ? " " : item.getItemProperty(propertyId).getValue().toString()); } sheet.autoSizeColumn(cellnum); cellnum++; } rownum++; } try { Date date = new Date(); file = File.createTempFile("file-" + date.getTime(), ".xls"); FileOutputStream fos = new FileOutputStream(file.getAbsolutePath()); workbook.write(fos); fos.flush(); fos.close(); } catch (FileNotFoundException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } }
From source file:com.screenslicer.common.Spreadsheet.java
License:Open Source License
public static byte[] xls(List<List<String>> rows) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Sheet 1"); int curRow = 0; for (List<String> row : rows) { Row xlsRow = sheet.createRow(curRow); int curCell = 0; for (String cell : row) { Cell xlsCell = xlsRow.createCell(curCell); if (CommonUtil.isEmpty(cell)) { xlsCell.setCellValue(""); } else if (CommonUtil.isUrl(cell)) { xlsCell.setCellFormula("HYPERLINK(\"" + cell + "\")"); } else { xlsCell.setCellValue(cell); }// ww w . jav a2 s . co m ++curCell; } ++curRow; } ByteArrayOutputStream out = new ByteArrayOutputStream(); try { try { workbook.write(out); } catch (IOException e) { Log.exception(e); } return out.toByteArray(); } finally { try { out.close(); } catch (IOException e) { Log.exception(e); } } }
From source file:com.seer.datacruncher.factories.streams.SchemaStreamsExcel.java
License:Open Source License
@Override public byte[] getDownloadableStreams() { if (maxVertical == 0) return null; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet("Sheet 1"); HSSFRow headerRow = s.createRow(0);/*from w w w . java 2 s . co m*/ HSSFCellStyle style = wb.createCellStyle(); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); for (int i = 0; i < maxVertical; i++) { Document doc = getNewDomDocument(); plainListChilds(doc, i, schemaEnt.getIdSchema(), linkedFieldsPaths); NodeList childList = DomToOtherFormat.getRootNodeOfDocument(doc).getChildNodes(); HSSFRow row = s.createRow(i + 1); for (int j = 0; j < childList.getLength(); j++) { Node child = childList.item(j); if (i == 0) { HSSFCell cell = headerRow.createCell(j); cell.setCellValue(child.getNodeName()); cell.setCellStyle(style); } HSSFCell cell = row.createCell(j); cell.setCellValue(child.getTextContent()); } } ByteArrayOutputStream bos = new ByteArrayOutputStream(); try { wb.write(bos); } catch (IOException e) { log.error("IO Exception, excel generation", e); } finally { try { bos.close(); } catch (IOException e) { log.error("IO stream closure exception, excel generation", e); } } return bos.toByteArray(); }
From source file:com.seer.datacruncher.spring.DownloadStreamController.java
License:Open Source License
private byte[] getDataForXMLFile(long idSchema, String datastream) { Document document = null;/*from www. j a v a 2 s .c o m*/ try { DocumentBuilderFactory docFactory = DocumentBuilderFactory.newInstance(); DocumentBuilder docBuilder = docFactory.newDocumentBuilder(); document = docBuilder.parse(new InputSource(new StringReader(datastream))); } catch (Exception ex) { ex.printStackTrace(); } HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Data"); HSSFRow headerRow = sheet.createRow(0); List<SchemaFieldEntity> listFields = schemaFieldsDao.listSchemaFields(idSchema); int colCounter = 0; HSSFRow row = sheet.createRow(1); String tagValue; for (SchemaFieldEntity instance : listFields) { tagValue = document.getElementsByTagName(instance.getName()).item(0).getTextContent(); headerRow.createCell(colCounter).setCellValue(instance.getName()); row.createCell(colCounter++).setCellValue(tagValue); } ByteArrayOutputStream bos = new ByteArrayOutputStream(); try { wb.write(bos); bos.close(); } catch (Exception ioexception2) { } return bos.toByteArray(); }
From source file:com.servoy.extensions.plugins.excelxport.ExportSpecifyFilePanel.java
License:Open Source License
public static HSSFWorkbook populateWb(IFoundSet foundSet, String[] dataProviders, byte[] templateXLS, String[] outputColumnNames, String sheetName, int startRow, int startColumn) throws IOException { HSSFWorkbook hwb; if (templateXLS == null) { hwb = new HSSFWorkbook(); } else {/*from www . ja v a 2 s.c o m*/ InputStream buff = new ByteArrayInputStream(templateXLS); hwb = new HSSFWorkbook(buff); } if (sheetName == null) sheetName = "Servoy Data"; HSSFSheet sheet = hwb.getSheet(sheetName); if (sheet == null) sheet = hwb.createSheet(sheetName); sheet.setActive(true); if (outputColumnNames != null && outputColumnNames.length != dataProviders.length) { throw new RuntimeException( "The arrays 'output column names' and 'data provider ids' must have the same length."); //$NON-NLS-1$ } String[] columnNames = outputColumnNames != null ? outputColumnNames : dataProviders; HSSFRow header = sheet.createRow((short) 0 + startRow); for (int k = 0; k < columnNames.length; k++) { HSSFCell cell = header.createCell((short) (k + startColumn)); cell.setCellValue(columnNames[k]); } for (int i = 0; i < foundSet.getSize(); i++) { HSSFRow row = sheet.createRow((short) (i + 1 + startRow)); IRecord s = foundSet.getRecord(i); for (int k = 0; k < dataProviders.length; k++) { HSSFCell cell = row.createCell((short) (k + startColumn)); Object obj = s.getValue(dataProviders[k]); if (obj instanceof Date) { HSSFCellStyle cellStyle = hwb.createCellStyle(); cellStyle.setDataFormat((short) 16); cell.setCellValue((Date) obj); cell.setCellStyle(cellStyle); } else if (obj instanceof String) { cell.setCellValue((String) obj); } else if (obj instanceof Number) { cell.setCellValue(((Number) obj).doubleValue()); } else { cell.setCellValue(""); //$NON-NLS-1$ } } } return hwb; }
From source file:com.sevenorcas.openstyle.app.service.spreadsheet.SpreadSheetServiceImp.java
/** * Export sheet to workbook/* w w w.j a v a2s. c om*/ * @param spreadSheet * @param workbook * @throws Exception */ private void exportSpreadSheet(List<SpreadSheet> sheets, HSSFWorkbook wb) throws Exception { for (SpreadSheet ss : sheets) { HSSFSheet sheet = wb.createSheet(ss.getSheetname()); ss.setWorkBook(wb); ss.createFreezePane(sheet); /*-*************************************************************** * Default column widths ****************************************************************/ for (int column = 0; column <= ss.getLastColumn(); column++) { if (ss.getColumnWidth(column) != -1) { sheet.setColumnWidth(column, ss.getColumnWidth(column)); } } /*-*************************************************************** * Output row data ****************************************************************/ for (int row = 0; row <= ss.getLastRow(); row++) { for (int column = 0; column <= ss.getLastColumn(); column++) { HSSFRow sheetRow = sheet.getRow(row); if (sheetRow == null) { sheetRow = sheet.createRow(row); } HSSFCell cell = sheetRow.createCell(column); SpreadsheetCell cellX = ss.getCell(column, row); if (cellX != null) { if (cellX.getCellRangeAddress() != null) { sheet.addMergedRegion(cellX.getCellRangeAddress()); } //Ex HSSFCellStyle style = cellX.getCellStyle(wb); cell.setCellStyle(style); boolean set = ss.getColumnWidth(column) == -1; if (set && cellX.isHeader() && cellX.getWidth() != null) { sheet.setColumnWidth(column, cellX.getWidth()); } cellX.setCellValue(cell, wb); } else { HSSFCellStyle style = ss.getCellStyleDefault(wb, row, column); cell.setCellStyle(style); } } } } }
From source file:com.simeosoft.util.XlsUtils.java
License:Open Source License
public static void addXlsWorksheet(HSSFWorkbook wb, String sheetName, ArrayList<ArrayList<Object>> data) { HSSFSheet s = wb.createSheet(sheetName); HSSFRow r = null;/* ww w. j a v a 2 s. c o m*/ HSSFCell c = null; int i = 0; HSSFDataFormat df = wb.createDataFormat(); HSSFCellStyle cs = wb.createCellStyle(); for (ArrayList<Object> record : data) { r = s.createRow(i); i++; short y = 0; /* * tipi dato previsti: null,String,Date,Integer,Time,Timestamp * - gli altri tipi restituiscono errore */ for (Object obj : record) { c = r.createCell(y); y++; if (obj == null) { c.setCellValue(""); continue; } if (obj instanceof String) { c.setCellValue((String) obj); continue; } // MODIF - java.util.Date o java.sql.Date? if (obj instanceof java.sql.Date || obj instanceof java.sql.Timestamp || obj instanceof java.sql.Time) { HSSFCellStyle csd = wb.createCellStyle(); csd.setDataFormat(HSSFDataFormat.getBuiltinFormat("mm/dd/yyyy")); c.setCellValue((java.sql.Date) obj); c.setCellStyle(csd); continue; } if (obj instanceof Integer) { c.setCellValue(((Integer) obj).doubleValue()); continue; } if (obj instanceof BigDecimal) { c.setCellValue(((BigDecimal) obj).doubleValue()); cs.setDataFormat(df.getFormat("######0.0000")); c.setCellStyle(cs); continue; } // default - non previsto c.setCellValue("ERRORE: TIPO NON PREVISTO: " + obj.getClass()); } } }