List of usage examples for org.apache.poi.ss.usermodel Row createCell
Cell createCell(int column);
From source file:com.jshuabo.reportcenter.server.service.automoblie.impl.DefaultBranchLineDrawoutRecordServiceImpl.java
@Override public void importToExcel(Map<String, Object> exprotMap, HttpServletResponse response, HttpServletRequest request, String[] title, String excelName) { User user = SecurityContextHolder.getCurrentUser(); if (StringUtils.isEmpty(exprotMap.get("subStation"))) { exprotMap.put("subStation", user.getId()); }/*from w w w . j a v a 2 s.c o m*/ Map<String, Object> pageData = drawoutRecordDataMapper.exprotPageData(exprotMap); SXSSFWorkbook workbook = new SXSSFWorkbook(1000); // keep 100 rows in memory, exceeding rows int flag = 1; // int rowCount = 150000; // ?sheet long count = (Long) pageData.get("COUNT"); long pageSize = rowCount; // ?? long page = 1; if (rowCount < count) { if (count % pageSize == 0) { } else { page = count / pageSize + 1; } // } Sheet sh = workbook.createSheet(excelName + System.currentTimeMillis()); Row row = sh.createRow((short) 0); // Cell cell = null; CellStyle[] cs = POIUtils.cellStyle(workbook); POIUtils.createHeard(sh, row, cell, cs, title); for (int i = 1; i <= page; i++) { long offset = (i - 1) * pageSize; long rows = pageSize; exprotMap.put("offset", Long.valueOf(offset)); exprotMap.put("rows", Long.valueOf(rows)); // ?? List<BranchLineDrawoutRecord> drawoutRecordDataList = drawoutRecordDataMapper.exprot2Excel(exprotMap); if (drawoutRecordDataList.isEmpty()) continue; for (int rownum = 0; rownum < drawoutRecordDataList.size(); rownum++) { BranchLineDrawoutRecord bdr = drawoutRecordDataList.get(rownum); String data = DateFormatUtils.format(bdr.getDate(), DateFormatUtils.ymd); String subStation = bdr.getExtendProp3(); // ? ?sheet if (flag > pageSize) { flag = 1; sh = workbook.createSheet(excelName + System.currentTimeMillis()); row = sh.createRow((short) 0); POIUtils.createHeard(sh, row, cell, cs, title); } row = sh.createRow(flag); for (int cellnum = 0; cellnum < title.length; cellnum++) { cell = row.createCell(0); cell.setCellValue(subStation); cell.setCellStyle(cs[1]); cell = row.createCell(1); cell.setCellValue(data); cell.setCellStyle(cs[1]); cell = row.createCell(2); cell.setCellValue(bdr.getLicenseNo()); cell.setCellStyle(cs[1]); cell = row.createCell(3); cell.setCellValue(bdr.getDriver()); cell.setCellStyle(cs[1]); cell = row.createCell(4); cell.setCellValue(bdr.getLine()); cell.setCellStyle(cs[1]); cell = row.createCell(5); cell.setCellValue(bdr.getGpsNo()); cell.setCellStyle(cs[1]); cell = row.createCell(6); cell.setCellValue(bdr.getStartTime()); cell.setCellStyle(cs[1]); cell = row.createCell(7); cell.setCellValue(bdr.getStartMileage()); cell.setCellStyle(cs[1]); cell = row.createCell(8); cell.setCellValue(bdr.getStopTime()); cell.setCellStyle(cs[1]); cell = row.createCell(9); cell.setCellValue(bdr.getStopMileage()); cell.setCellStyle(cs[1]); cell = row.createCell(10); cell.setCellValue(bdr.getMileage()); cell.setCellStyle(cs[1]); cell = row.createCell(11); String temp = bdr.getExtendProp2(); cell.setCellValue(StringUtils.isEmpty(temp) ? 0D : Double.valueOf(temp)); cell.setCellStyle(cs[1]); cell = row.createCell(12); cell.setCellValue(bdr.getTicketQuantity()); cell.setCellStyle(cs[1]); cell = row.createCell(13); cell.setCellValue(bdr.getHallQuantity()); cell.setCellStyle(cs[1]); cell = row.createCell(14); cell.setCellValue(bdr.getPieQuantity()); cell.setCellStyle(cs[1]); cell = row.createCell(15); cell.setCellValue(bdr.getPcsQuantity()); cell.setCellStyle(cs[1]); cell = row.createCell(16); cell.setCellValue(bdr.getFuelPrice()); cell.setCellStyle(cs[1]); cell = row.createCell(17); cell.setCellValue(bdr.getFuelCosts()); cell.setCellStyle(cs[1]); cell = row.createCell(18); cell.setCellValue(bdr.getDay()); cell.setCellStyle(cs[1]); cell = row.createCell(19); cell.setCellValue(bdr.getRentalFee()); cell.setCellStyle(cs[1]); cell = row.createCell(20); cell.setCellValue(bdr.getParkingFee()); cell.setCellStyle(cs[1]); cell = row.createCell(21); cell.setCellValue(bdr.getAward()); cell.setCellStyle(cs[1]); cell = row.createCell(22); cell.setCellValue(bdr.getIsReim()); cell.setCellStyle(cs[1]); cell = row.createCell(23); cell.setCellValue(bdr.getNoDelivery()); cell.setCellStyle(cs[1]); cell = row.createCell(24); cell.setCellValue(bdr.getRefusal()); cell.setCellStyle(cs[1]); cell = row.createCell(25); cell.setCellValue(bdr.getVoteSign()); cell.setCellStyle(cs[1]); cell = row.createCell(26); cell.setCellValue(bdr.getRemarks()); cell.setCellStyle(cs[1]); cell = row.createCell(27); cell.setCellValue(bdr.getExtendProp1()); cell.setCellStyle(cs[1]); } flag++; } } POIUtils.exprot(workbook, response, excelName); }
From source file:com.jslsolucoes.tagria.lib.grid.exporter.impl.ExcelExporter.java
License:Apache License
private void header(Sheet sheet, Workbook workbook) { Row sheetRow = sheet.createRow(0); int cell = 0; for (Header header : table.getHeaders()) { Cell sheetCel = sheetRow.createCell(cell); sheetCel.setCellValue(header.getContent()); CellStyle cellStyle = workbook.createCellStyle(); if ("center".equals(header.getAlign())) cellStyle.setAlignment(CellStyle.ALIGN_CENTER); else if ("left".equals(header.getAlign())) cellStyle.setAlignment(CellStyle.ALIGN_LEFT); else if ("right".equals(header.getAlign())) cellStyle.setAlignment(CellStyle.ALIGN_RIGHT); sheetCel.setCellStyle(cellStyle); cell++;// w w w .j av a 2 s . c o m } }
From source file:com.jslsolucoes.tagria.lib.grid.exporter.impl.ExcelExporter.java
License:Apache License
private void body(Sheet sheet, Workbook workbook) { int line = 1; for (com.jslsolucoes.tagria.lib.grid.exporter.model.Row row : table.getRows()) { Row sheetRow = sheet.createRow(line); int cell = 0; for (Column column : row.getColumns()) { Cell sheetCel = sheetRow.createCell(cell); sheetCel.setCellValue(column.getContent()); CellStyle cellStyle = workbook.createCellStyle(); if ("center".equals(column.getAlign())) cellStyle.setAlignment(CellStyle.ALIGN_CENTER); else if ("left".equals(column.getAlign())) cellStyle.setAlignment(CellStyle.ALIGN_LEFT); else if ("right".equals(column.getAlign())) cellStyle.setAlignment(CellStyle.ALIGN_RIGHT); sheetCel.setCellStyle(cellStyle); cell++;// ww w . j av a2 s . c o m } line++; } }
From source file:com.jubination.service.CallMaintainService.java
public boolean createCallExcel(List<Call> list) { System.out.println("*******com.jubination.service.CallMaintainService.createCallExcel()"); FileOutputStream out = null;//from w w w . j a v a 2s . com HSSFWorkbook workbook = null; String excelOutputFilePath = excelOutputDirectory + "data.xls"; String excelOutputBuildFilePath = excelOutputBuildDirectory + "data.xls"; boolean flag = false; try { workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Sample sheet"); Call[] messageArray = new Call[list.size()]; list.toArray(messageArray); Map<String, Object[]> data = new LinkedHashMap<>(); Integer index = 1; data.put(index.toString(), new Object[] { "CallFrom", "CallTo", "Status", "TrackStatus", "CallType", "DailWhomNumber", "DailCallDuration", "Message", "DateCreated", "AnsweredBy", "StartTime", "EndTime", "DateUpdated", "Duration", "Price", "Direction", "Digits", "Sid", "Uri", "RecordingUrl", "PhoneNumberSid", "AccountSid", "ForwardedFrom", "CallerName", "ParentCallSid" }); index++; for (Call message : messageArray) { data.put(index.toString(), new Object[] { message.getCallFrom(), message.getCallTo(), message.getStatus(), message.getTrackStatus(), message.getCallType(), message.getDialWhomNumber(), message.getDialCallDuration(), message.getMessage(), message.getDateCreated(), message.getAnsweredBy(), message.getStartTime(), message.getEndTime(), message.getDateUpdated(), message.getDuration(), message.getPrice(), message.getDirection(), message.getDigits(), message.getSid(), message.getUri(), message.getRecordingUrl(), message.getPhoneNumberSid(), message.getAccountSid(), message.getForwardedFrom(), message.getCallerName(), message.getParentCallSid() }); index++; } Set<String> keyset = data.keySet(); int rownum = 0; for (String key : keyset) { Row row = sheet.createRow(rownum++); Object[] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof Date) cell.setCellValue((Date) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Double) cell.setCellValue((Double) obj); } } out = new FileOutputStream(new File(excelOutputFilePath)); workbook.write(out); out = new FileOutputStream(new File(excelOutputBuildFilePath)); workbook.write(out); flag = true; System.out.println("Excel written successfully.."); } catch (Exception e) { e.printStackTrace(); } finally { try { if (workbook != null) { workbook.close(); } } catch (Exception e) { } try { if (out != null) { out.close(); } } catch (Exception e) { } } return flag; }
From source file:com.jubination.service.CallMaintainService.java
public boolean createClientExcel(String date) { System.out.println("*******com.jubination.service.CallMaintainService.createClientExcel()"); FileOutputStream out = null;//from w w w. ja v a 2 s . co m HSSFWorkbook workbook = null; String excelOutputFilePath = excelOutputDirectory + "client.xls"; String excelOutputBuildFilePath = excelOutputBuildDirectory + "client.xls"; boolean flag = false; try { workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Client Sheet"); Map<String, Object[]> data = doReportingOperation(getClientDumpForDisplay(date)); Set<String> keyset = data.keySet(); int rownum = 0; for (String key : keyset) { Row row = sheet.createRow(rownum++); Object[] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof Date) cell.setCellValue((Date) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Double) cell.setCellValue((Double) obj); } } out = new FileOutputStream(new File(excelOutputFilePath)); workbook.write(out); out = new FileOutputStream(new File(excelOutputBuildFilePath)); workbook.write(out); flag = true; System.out.println("Excel written successfully.."); } catch (Exception e) { e.printStackTrace(); } finally { try { if (workbook != null) { workbook.close(); } } catch (Exception e) { e.printStackTrace(); } try { if (out != null) { out.close(); } } catch (Exception e) { e.printStackTrace(); } } return flag; }
From source file:com.jubination.service.CallMaintainService.java
public boolean createClientExcelAllLead(String date) { System.out.println("*******com.jubination.service.CallMaintainService.createClientExcelAllLead()"); FileOutputStream out = null;/* w w w .ja va 2 s .c om*/ HSSFWorkbook workbook = null; String excelOutputFilePath = excelOutputDirectory + "client.xls"; String excelOutputBuildFilePath = excelOutputBuildDirectory + "client.xls"; boolean flag = false; try { workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Client Sheet"); List<Lead> list = getLeadDumpForDisplay(date); Lead[] messageArray = new Lead[list.size()]; list.toArray(messageArray); Map<String, Object[]> data = new LinkedHashMap<>(); Integer index = 1; data.put(index.toString(), new Object[] { "Lead id", "Name", "Number", "Email", "Campaign Name", "Pub Id", "Source", "Date", "City", "Affiliate Status", "Picked up by", "Follow ups left", "Client comment", "Lead comment", "Follow up date", "Status", "Date", "Status-1", "Date-1", " Status-2", "Date-2", " Status-3", "Date-3", " Status-4", "Date-4", " Status-5", "Date-5", " Status-6", "Date-6", " Status-7", "Date-7", " Status-8", "Date-8", " Status-9", "Date-9", " Status-10", "Date-10", " Status-11", "Date-11", " Status-12", "Date-12", " Status-13", "Date-13", " Status-14", "Date-14", " Status-15", "Date-15", "", "Final Status Beta" }); index++; for (Lead lead : messageArray) { String[] leadDetailsArray = new String[] { "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "" }; String[] dateDetailsArray = new String[] { "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "" }; String affiliateDetails = "WIP"; for (int i = 0; i < 20; i++) { dateDetailsArray[i] = ""; leadDetailsArray[i] = ""; } int count = 0; String caller = ""; if (lead != null) { if (lead.getCall().size() > 0) { for (int i = lead.getCall().size() - 1; i >= 0; i--) { if (count < 15) { Call call = lead.getCall().get(i); if (call == null) { break; } System.out.println(count + " " + i + " " + call.getDateCreated()); ////////change to allow all lead sent to thyrocare leads/////////// //////////////////////// if (call.getStatus() != null && call.getStatus().contains("busy")) { leadDetailsArray[count] = "Busy"; dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); if (lead.getCall().size() >= operator.getCount() - 1 && i == lead.getCall().size() - 1) { affiliateDetails = "Disconnecting the call"; } } else if (call.getStatus() != null && call.getStatus().contains("failed")) { leadDetailsArray[count] = "Failed"; dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); if (lead.getCall().size() >= operator.getCount() - 1 && i == lead.getCall().size() - 1) { affiliateDetails = "Not Reachable"; } } else if (call.getStatus() != null && call.getStatus().contains("no-answer")) { leadDetailsArray[count] = "No Answer"; dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); if (lead.getCall().size() >= operator.getCount() - 1 && i == lead.getCall().size() - 1) { affiliateDetails = "Ringing"; } } else if (call.getStatus() != null && call.getStatus().contains("completed") && call.getCallType().contains("trans")) { leadDetailsArray[count] = "Hanged up while greetings"; dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); if (lead.getCall().size() >= operator.getCount() - 1 && i == lead.getCall().size() - 1) { affiliateDetails = "Disconnecting the call"; } } else if (call.getTrackStatus() != null && call.getTrackStatus().contains("did not speak") && call.getCallType().contains("client-hangup")) { leadDetailsArray[count] = "Hanged up while connecting"; dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); if (lead.getCall().size() >= operator.getCount() - 1 && i == lead.getCall().size() - 1) { affiliateDetails = "Disconnecting the call"; } } else if (call.getTrackStatus() != null && call.getTrackStatus().contains("did not speak") && call.getCallType().contains("incomplete")) { leadDetailsArray[count] = "We missed client's call"; dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); if (lead.getCall().size() >= operator.getCount() - 1 && i == lead.getCall().size() - 1) { affiliateDetails = "WIP"; } caller = call.getDialWhomNumber(); } else if (call.getTrackStatus() != null && call.getTrackStatus().contains("spoke")) { if (lead.getLeadStatus() != null && (lead.getLeadStatus().contains("Follow up/Call back") || lead.getLeadStatus().contains("Lead sent to Thyrocare") || lead.getLeadStatus().contains("Not interested") || lead.getLeadStatus().contains("Not registered") || lead.getLeadStatus().contains("Language not recognizable") || lead.getLeadStatus().contains("No Service") || lead.getLeadStatus().contains("Customer complained") || lead.getLeadStatus().contains("Disapproved") || lead.getLeadStatus().contains("Rescheduled"))) { leadDetailsArray[count] = lead.getLeadStatus(); if (lead.getLeadStatus().contains("Lead sent to Thyrocare") || lead.getLeadStatus().contains("Rescheduled")) { affiliateDetails = "Interested"; } else { affiliateDetails = lead.getLeadStatus(); } dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); } else { if (i == lead.getCall().size() - 1) { leadDetailsArray[count] = "Spoke but not updated"; dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); affiliateDetails = "Spoke but not updated"; } else { leadDetailsArray[count] = lead.getLeadStatus() + ":"; dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); } } caller = call.getDialWhomNumber(); } else { if (i == lead.getCall().size() - 1) { if (lead.getLeadStatus() != null) { caller = call.getDialWhomNumber(); if (lead.getLeadStatus() != null && (lead.getLeadStatus().contains("Follow up/Call back") || lead.getLeadStatus().contains("Not interested") || lead.getLeadStatus().contains("Not registered") || lead.getLeadStatus() .contains("Language not recognizable") || lead.getLeadStatus().contains("No Service") || lead.getLeadStatus().contains("Customer complained") || lead.getLeadStatus().contains("Disapproved"))) { affiliateDetails = lead.getLeadStatus(); } else if (lead.getLeadStatus().contains("Lead sent to Thyrocare") || lead.getLeadStatus().contains("Rescheduled")) { affiliateDetails = "Interested"; } else if (lead.getLeadStatus().contains("Busy")) { affiliateDetails = "Disconnecting the call"; } else if (lead.getLeadStatus().contains("Failed")) { affiliateDetails = "Not Reachable"; } else if (lead.getLeadStatus().contains("No Answer")) { affiliateDetails = "Ringing"; } else if (lead.getLeadStatus().contains("Hanged up while greetings")) { affiliateDetails = "Disconnecting the call"; } else if (lead.getLeadStatus() .contains("Hanged up while connecting")) { affiliateDetails = "Disconnecting the call"; } else if (lead.getLeadStatus().contains("Spoke but not updated")) { affiliateDetails = "Spoke but not updated"; } else { affiliateDetails = lead.getLeadStatus(); } leadDetailsArray[count] = lead.getLeadStatus(); dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); } else { leadDetailsArray[count] = call.getStatus() + "%"; dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); caller = call.getDialWhomNumber(); affiliateDetails = lead.getLeadStatus(); } } else { leadDetailsArray[count] = lead.getLeadStatus() + "$"; dateDetailsArray[count] = call.getDateCreated() + " " + call.getDuration(); } } if (lead.getLeadStatus() != null && (lead.getLeadStatus().contains("Lead sent to Thyrocare") || lead.getLeadStatus().contains("Rescheduled"))) { affiliateDetails = "Interested"; } if (lead.getFollowUpDate() != null && !affiliateDetails.contains("") && !lead.getLeadStatus().contains("Follow up/Call back") && !lead.getLeadStatus().contains("Not interested") && !lead.getLeadStatus().contains("Not registered") && !lead.getLeadStatus().contains("Language not recognizable") && !lead.getLeadStatus().contains("No Service") && !lead.getLeadStatus().contains("Customer complained") && !lead.getLeadStatus().contains("Disapproved")) { affiliateDetails = "Follow up/Call back"; } if (lead.isMissedAppointment() != null && lead.isMissedAppointment() && lead.getCount() < 1) { affiliateDetails = "Missed Appointment"; } count++; } } } data.put(index.toString(), new Object[] { lead.getLeadId(), lead.getClient().getName(), lead.getClient().getPhoneNumber(), lead.getClient().getEmailId(), lead.getClient().getCampaignName(), lead.getClient().getPubId(), lead.getClient().getSource(), lead.getClient().getDateCreation(), lead.getClient().getCity(), affiliateDetails, caller, Integer.toString(lead.getCount()), lead.getClient().getInitialComments(), lead.getComments(), lead.getFollowUpDate(), leadDetailsArray[0], dateDetailsArray[0], leadDetailsArray[1], dateDetailsArray[1], leadDetailsArray[2], dateDetailsArray[2], leadDetailsArray[3], dateDetailsArray[3], leadDetailsArray[4], dateDetailsArray[4], leadDetailsArray[5], dateDetailsArray[5], leadDetailsArray[6], dateDetailsArray[6], leadDetailsArray[7], dateDetailsArray[7], leadDetailsArray[8], dateDetailsArray[8], leadDetailsArray[9], dateDetailsArray[9], leadDetailsArray[10], dateDetailsArray[10], leadDetailsArray[11], dateDetailsArray[11], leadDetailsArray[12], dateDetailsArray[12], leadDetailsArray[13], dateDetailsArray[13], leadDetailsArray[14], dateDetailsArray[14], leadDetailsArray[15], dateDetailsArray[15], "", lead.getLeadStatus() }); index++; lead = null; } } list = null; Set<String> keyset = data.keySet(); int rownum = 0; for (String key : keyset) { Row row = sheet.createRow(rownum++); Object[] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof Date) cell.setCellValue((Date) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Double) cell.setCellValue((Double) obj); } } out = new FileOutputStream(new File(excelOutputFilePath)); workbook.write(out); out = new FileOutputStream(new File(excelOutputBuildFilePath)); workbook.write(out); flag = true; System.out.println("Excel written successfully.."); } catch (Exception e) { e.printStackTrace(); } finally { try { if (workbook != null) { workbook.close(); } } catch (Exception e) { e.printStackTrace(); } try { if (out != null) { out.close(); } } catch (Exception e) { e.printStackTrace(); } } return flag; }
From source file:com.jubinationre.service.AdminMaintainService.java
public boolean createExcel(List<CallAPIMessage> list) { boolean flag = false; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Sample sheet"); CallAPIMessage[] messageArray = new CallAPIMessage[list.size()]; list.toArray(messageArray);/*w w w .j ava 2s .c om*/ Map<String, Object[]> data = new LinkedHashMap<>(); Integer index = 1; data.put(index.toString(), new Object[] { "CallFrom", "CallTo", "Status", "TrackStatus", "CallType", "DailWhomNumber", "DailCallDuration", "Message", "DateCreated", "AnsweredBy", "StartTime", "EndTime", "DateUpdated", "Duration", "Price", "Direction", "Digits", "Sid", "Uri", "RecordingUrl", "PhoneNumberSid", "AccountSid", "ForwardedFrom", "CallerName", "ParentCallSid" }); index++; for (CallAPIMessage message : messageArray) { data.put(index.toString(), new Object[] { message.getCallFrom(), message.getCallTo(), message.getStatus(), message.getTrackStatus(), message.getCallType(), message.getDailWhomNumber(), message.getDailCallDuration(), message.getMessage(), message.getDateCreated(), message.getAnsweredBy(), message.getStartTime(), message.getEndTime(), message.getDateUpdated(), message.getDuration(), message.getPrice(), message.getDirection(), message.getDigits(), message.getSid(), message.getUri(), message.getRecordingUrl(), message.getPhoneNumberSid(), message.getAccountSid(), message.getForwardedFrom(), message.getCallerName(), message.getParentCallSid() }); index++; } Set<String> keyset = data.keySet(); int rownum = 0; for (String key : keyset) { Row row = sheet.createRow(rownum++); Object[] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof Date) cell.setCellValue((Date) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Double) cell.setCellValue((Double) obj); } } try { FileOutputStream out = new FileOutputStream(new File(excelOutputFilePath)); workbook.write(out); out.close(); flag = true; System.out.println("Excel written successfully.."); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return flag; }
From source file:com.jwm123.loggly.reporter.ReportGenerator.java
License:Apache License
public byte[] build(List<Map<String, String>> row) throws IOException { List<String> colLabels = new ArrayList<String>(); Sheet reportSheet = workbook.getSheet("Report"); if (reportSheet == null) { reportSheet = workbook.createSheet("Report"); }//from ww w.ja va 2 s .com Row firstRow = reportSheet.getRow(0); if (firstRow == null) { firstRow = reportSheet.createRow(0); int cellNum = 0; for (Map<String, String> col : row) { for (String key : col.keySet()) { Cell cell = firstRow.createCell(cellNum++); setCellValue(cell, key); } } } for (int i = 0; i < firstRow.getLastCellNum(); i++) { Cell cell = firstRow.getCell(i); if (cell != null) { colLabels.add(cell.getStringCellValue()); } } Row newRow = reportSheet.createRow(reportSheet.getLastRowNum() + 1); int lastIndex = -1; for (Map<String, String> col : row) { for (String key : col.keySet()) { int colNum = -1; Cell cell = null; if (colLabels.contains(key)) { colNum = colLabels.indexOf(key); lastIndex = colNum; } if (colNum == -1) { lastIndex++; colNum = lastIndex; shiftColumns(reportSheet, colNum, key); colLabels.add(colNum, key); } cell = newRow.getCell(colNum); if (cell == null) { cell = newRow.createCell(colNum); } setCellValue(cell, col.get(key)); } } for (int i = 0; i < firstRow.getLastCellNum(); i++) { reportSheet.autoSizeColumn(i); } ByteArrayOutputStream baos = new ByteArrayOutputStream(); workbook.write(baos); return baos.toByteArray(); }
From source file:com.jwm123.loggly.reporter.ReportGenerator.java
License:Apache License
private void shiftColumns(Sheet reportSheet, int colNum, String key) { boolean firstRow = true; for (Row row : reportSheet) { for (int i = row.getLastCellNum() + 1; i >= colNum; i--) { Cell nextCell = row.getCell(i + 1); Cell oldCell = row.getCell(i); if (oldCell != null) { if (nextCell == null) { nextCell = row.createCell(i + 1); }//from w ww. jav a 2 s . co m setCellValue(nextCell, oldCell.getStringCellValue()); if (firstRow && i == colNum) { setCellValue(oldCell, key); firstRow = false; } else { setCellValue(oldCell, ""); } } else if (firstRow && i == colNum) { oldCell = row.createCell(i); setCellValue(oldCell, key); firstRow = false; } } } }
From source file:com.kafeidev.test.BusinessPlan.java
License:Apache License
@Test public static void main(String[] args) throws Exception { Workbook wb;/*from ww w .jav a 2s .com*/ if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Business Plan"); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); for (int i = 0; i < titles.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); } //columns for 11 weeks starting from 9-Nov Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); // { // String inputDate = "2010-Nov-04 01:32:27"; // Date date = new SimpleDateFormat("yyyy-MMM-dd HH:mm:ss", new Locale("en,EN")).parse(inputDate); // String str= new SimpleDateFormat("dd.MMM.yyyy HH:mm:ss").format(date); // System.out.println("data:"+str); // // } calendar.setTime(fmt.parse("19-Nov")); calendar.set(Calendar.YEAR, year); for (int i = 0; i < 11; i++) { Cell cell = headerRow.createCell(titles.length + i); cell.setCellValue(calendar); cell.setCellStyle(styles.get("header_date")); calendar.roll(Calendar.WEEK_OF_YEAR, true); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1; for (int i = 0; i < data.length; i++, rownum++) { row = sheet.createRow(rownum); if (data[i] == null) continue; for (int j = 0; j < data[i].length; j++) { cell = row.createCell(j); String styleName; boolean isHeader = i == 0 || data[i - 1] == null; switch (j) { case 0: if (isHeader) { styleName = "cell_b"; cell.setCellValue(Double.parseDouble(data[i][j])); } else { styleName = "cell_normal"; cell.setCellValue(data[i][j]); } break; case 1: if (isHeader) { styleName = i == 0 ? "cell_h" : "cell_bb"; } else { styleName = "cell_indented"; } cell.setCellValue(data[i][j]); break; case 2: styleName = isHeader ? "cell_b" : "cell_normal"; cell.setCellValue(data[i][j]); break; case 3: styleName = isHeader ? "cell_b_centered" : "cell_normal_centered"; cell.setCellValue(Integer.parseInt(data[i][j])); break; case 4: { calendar.setTime(fmt.parse(data[i][j])); calendar.set(Calendar.YEAR, year); cell.setCellValue(calendar); styleName = isHeader ? "cell_b_date" : "cell_normal_date"; break; } case 5: { int r = rownum + 1; String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")"; cell.setCellFormula(fmla); styleName = isHeader ? "cell_bg" : "cell_g"; break; } default: styleName = data[i][j] != null ? "cell_blue" : "cell_normal"; } cell.setCellStyle(styles.get(styleName)); } } //group rows for each phase, row numbers are 0-based sheet.groupRow(4, 6); sheet.groupRow(9, 13); sheet.groupRow(16, 18); //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 256 * 6); sheet.setColumnWidth(1, 256 * 33); sheet.setColumnWidth(2, 256 * 20); sheet.setZoom(3, 4); // Write the output to a file String file = "businessplan.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }