List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet createRow
@Override public HSSFRow createRow(int rownum)
From source file:com.ibm.ioes.utilities.MailForDisConnectProvisioning.java
public HSSFWorkbook generateExcel_ACS(ArrayList<ACSMailTemplateDto> mailDataList) { HSSFWorkbook wb = null;/*w ww .ja v a 2s .c o m*/ try { ACSMailTemplateDto mailObjDto = null; wb = new HSSFWorkbook(); HSSFSheet mailProvisionSheet = wb.createSheet("Mail Disconnect"); HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFFont boldFont = wb.createFont(); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerCellStyle.setFont(boldFont); HSSFRow excelRow = null; HSSFCell excelCell = null; excelRow = mailProvisionSheet.createRow(0); excelRow = mailProvisionSheet.createRow(0); int i_cell = 0; excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Date of Creation")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Quote No./ISS Order No.")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Region")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Parent A/C ID(Billed)")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Child ID (Unbilled)")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Premium ACS ID")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Type of ID")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Customer Name")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Address")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("City State")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Pin")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Moderator Name")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Designation")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Email")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Phone No.")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Contact Person")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Designation")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Email")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Phone No.")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Account Manager Name")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Designation")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Email ID/ Phone No")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Web Conf (Y/N)")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Web Conf Rental")); int iCount = 1; for (int i = 0; i < mailDataList.size(); i++) { mailObjDto = mailDataList.get(i); excelRow = mailProvisionSheet.createRow(iCount++); i_cell = 0; excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getDateOfCreation()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getOrderNo()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getRegion()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getParentId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChildId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAcsId()))); /*excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString(""+Utility.fnCheckNull(mailObjDto.getServiceType()));*/ excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getTypeOfId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getCustomerName()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAddress()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getCity()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getPinNo()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonName()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonDesignation()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonEmailId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonPhoneNo()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpName()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpDesignation()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpEmail()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpPhone()))); excelCell = excelRow.createCell(i_cell++); excelCell .setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccMgrName()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccMgrDesignation()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccMgrphone()) + "/" + Utility.fnCheckNull(mailObjDto.getAccMgrEmailId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getWebConf()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getWebConfRental()))); } } catch (Exception ex) { Utility.LOG(true, true, "Error while fetching accounts for provisiong : " + new Date()); } return wb; }
From source file:com.ibm.ioes.utilities.MailForDisConnectProvisioning.java
public HSSFWorkbook generateExcel_VCS(ArrayList<ACSMailTemplateDto> mailDataList, String mailProvisioningFlag) { HSSFWorkbook wb = null;//from ww w .ja v a2 s .c o m try { ACSMailTemplateDto mailObjDto = null; wb = new HSSFWorkbook(); HSSFSheet mailProvisionSheet = wb.createSheet("Mail Disconnect"); HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFFont boldFont = wb.createFont(); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerCellStyle.setFont(boldFont); HSSFRow excelRow = null; HSSFCell excelCell = null; excelRow = mailProvisionSheet.createRow(0); excelRow = mailProvisionSheet.createRow(0); excelCell = excelRow.createCell(0); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Date of Creation")); excelCell = excelRow.createCell(1); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Account name")); excelCell = excelRow.createCell(2); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("ISS Order no")); excelCell = excelRow.createCell(3); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Product")); excelCell = excelRow.createCell(4); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Parent ID")); excelCell = excelRow.createCell(5); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Child ID")); excelCell = excelRow.createCell(6); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("VCS ID")); excelCell = excelRow.createCell(7); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Bundled VCS plan")); excelCell = excelRow.createCell(8); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Chairperson Name")); excelCell = excelRow.createCell(9); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Billing Person Name")); excelCell = excelRow.createCell(10); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Billing Address")); excelCell = excelRow.createCell(11); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("ID/HW address")); excelCell = excelRow.createCell(12); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Status")); int iCount = 1; for (int i = 0; i < mailDataList.size(); i++) { mailObjDto = mailDataList.get(i); excelRow = mailProvisionSheet.createRow(iCount++); excelCell = excelRow.createCell(0); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getDateOfCreation()))); excelCell = excelRow.createCell(1); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccountName()))); excelCell = excelRow.createCell(2); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getOrderNo()))); excelCell = excelRow.createCell(3); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getProductName()))); excelCell = excelRow.createCell(4); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getParentId()))); excelCell = excelRow.createCell(5); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChildId()))); excelCell = excelRow.createCell(6); excelCell .setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getMultipleId()))); excelCell = excelRow.createCell(7); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getPackageName()))); excelCell = excelRow.createCell(8); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonName()))); excelCell = excelRow.createCell(9); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpName()))); excelCell = excelRow.createCell(10); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBillingAddress()))); excelCell = excelRow.createCell(11); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getHwAddress()))); excelCell = excelRow.createCell(12); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getStatus()))); } } catch (Exception ex) { Utility.LOG(true, true, "Error while fetching accounts for provisiong : " + new Date()); } return wb; }
From source file:com.ibm.ioes.utilities.MailForProvisioning.java
public HSSFWorkbook generateExcel(ArrayList<ACSMailTemplateDto> mailDataList) { HSSFWorkbook wb = null;// w w w.j a v a2s . c om try { ACSMailTemplateDto mailObjDto = null; wb = new HSSFWorkbook(); HSSFSheet mailProvisionSheet = wb.createSheet("Mail Provision"); HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFFont boldFont = wb.createFont(); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerCellStyle.setFont(boldFont); HSSFRow excelRow = null; HSSFCell excelCell = null; excelRow = mailProvisionSheet.createRow(0); excelRow = mailProvisionSheet.createRow(0); int i_cell = 0; excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Date of Creation")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Quote No./ISS Order No.")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Region")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Parent A/C ID(Billed)")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Child ID (Unbilled)")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Premium ACS ID")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Type of ID")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Customer Name")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Address")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("City State")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Pin")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Moderator Name")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Designation")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Email")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Phone No.")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Contact Person")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Designation")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Email")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Phone No.")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Account Manager Name")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Designation")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Email ID/ Phone No")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Web Conf (Y/N)")); excelCell = excelRow.createCell(i_cell++); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Web Conf Rental")); int iCount = 1; for (int i = 0; i < mailDataList.size(); i++) { mailObjDto = mailDataList.get(i); excelRow = mailProvisionSheet.createRow(iCount++); i_cell = 0; excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getDateOfCreation()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getOrderNo()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getRegion()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getParentId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChildId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAcsId()))); /*excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString(""+Utility.fnCheckNull(mailObjDto.getServiceType()));*/ excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getTypeOfId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getCustomerName()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAddress()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getCity()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getPinNo()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonName()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonDesignation()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonEmailId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonPhoneNo()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpName()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpDesignation()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpEmail()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpPhone()))); excelCell = excelRow.createCell(i_cell++); excelCell .setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccMgrName()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccMgrDesignation()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccMgrphone()) + "/" + Utility.fnCheckNull(mailObjDto.getAccMgrEmailId()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getWebConf()))); excelCell = excelRow.createCell(i_cell++); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getWebConfRental()))); } } catch (Exception ex) { Utility.LOG(true, true, "Error while fetching accounts for provisiong : " + new Date()); } return wb; }
From source file:com.ibm.ioes.utilities.MailForProvisioning.java
public HSSFWorkbook generateExcel_Multiple(ArrayList<ACSMailTemplateDto> mailDataList, String mailProvisioningFlag) { HSSFWorkbook wb = null;/*from w w w .ja v a 2s.co m*/ try { ACSMailTemplateDto mailObjDto = null; wb = new HSSFWorkbook(); HSSFSheet mailProvisionSheet = wb.createSheet("Mail Provision"); HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFFont boldFont = wb.createFont(); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerCellStyle.setFont(boldFont); HSSFRow excelRow = null; HSSFCell excelCell = null; excelRow = mailProvisionSheet.createRow(0); excelRow = mailProvisionSheet.createRow(0); if ("PROVISIONING_MAIL_OVCC".equals(mailProvisioningFlag)) { excelCell = excelRow.createCell(0); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Date of Creation")); excelCell = excelRow.createCell(1); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Account name")); excelCell = excelRow.createCell(2); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("ISS Order no")); excelCell = excelRow.createCell(3); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Product")); excelCell = excelRow.createCell(4); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Parent ID")); excelCell = excelRow.createCell(5); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Child ID")); excelCell = excelRow.createCell(6); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("OVCC ID")); excelCell = excelRow.createCell(7); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Bundled OVCC plan")); excelCell = excelRow.createCell(8); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Chairperson Name")); excelCell = excelRow.createCell(9); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Billing Person Name")); excelCell = excelRow.createCell(10); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Billing Address")); excelCell = excelRow.createCell(11); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Status")); } else { excelCell = excelRow.createCell(0); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Date of Creation")); excelCell = excelRow.createCell(1); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Account name")); excelCell = excelRow.createCell(2); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("ISS Order no")); excelCell = excelRow.createCell(3); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Product")); excelCell = excelRow.createCell(4); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Parent ID")); excelCell = excelRow.createCell(5); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Child ID")); excelCell = excelRow.createCell(6); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("VCS ID")); excelCell = excelRow.createCell(7); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Bundled VCS plan")); excelCell = excelRow.createCell(8); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Chairperson Name")); excelCell = excelRow.createCell(9); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Billing Person Name")); excelCell = excelRow.createCell(10); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Billing Address")); excelCell = excelRow.createCell(11); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("ID/HW address")); excelCell = excelRow.createCell(12); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Status")); } int iCount = 1; for (int i = 0; i < mailDataList.size(); i++) { mailObjDto = mailDataList.get(i); excelRow = mailProvisionSheet.createRow(iCount++); if ("PROVISIONING_MAIL_OVCC".equals(mailProvisioningFlag)) { excelCell = excelRow.createCell(0); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getDateOfCreation()))); excelCell = excelRow.createCell(1); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccountName()))); excelCell = excelRow.createCell(2); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getOrderNo()))); excelCell = excelRow.createCell(3); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getProductName()))); excelCell = excelRow.createCell(4); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getParentId()))); excelCell = excelRow.createCell(5); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChildId()))); excelCell = excelRow.createCell(6); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getMultipleId()))); excelCell = excelRow.createCell(7); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getPackageName()))); excelCell = excelRow.createCell(8); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonName()))); excelCell = excelRow.createCell(9); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpName()))); excelCell = excelRow.createCell(10); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBillingAddress()))); excelCell = excelRow.createCell(11); excelCell .setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getStatus()))); } else { excelCell = excelRow.createCell(0); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getDateOfCreation()))); excelCell = excelRow.createCell(1); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccountName()))); excelCell = excelRow.createCell(2); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getOrderNo()))); excelCell = excelRow.createCell(3); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getProductName()))); excelCell = excelRow.createCell(4); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getParentId()))); excelCell = excelRow.createCell(5); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChildId()))); excelCell = excelRow.createCell(6); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getMultipleId()))); excelCell = excelRow.createCell(7); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getPackageName()))); excelCell = excelRow.createCell(8); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonName()))); excelCell = excelRow.createCell(9); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpName()))); excelCell = excelRow.createCell(10); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBillingAddress()))); excelCell = excelRow.createCell(11); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getHwAddress()))); excelCell = excelRow.createCell(12); excelCell .setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getStatus()))); } } } catch (Exception ex) { Utility.LOG(true, true, "Error while fetching accounts for provisiong : " + new Date()); } return wb; }
From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java
public void generarExcelVentasCantadas() throws IOException { cargaResumen();/*from www . j a v a 2 s .c o m*/ if (ventasCantadas == null || ventasCantadas.isEmpty()) { JsfUtil.addErrorMessage("No hay datos para generar"); } else { Map<String, List<ResumenVentasCantadas>> map2 = new HashMap<>(); for (ResumenVentasCantadas rc : ventasCantadas) { List<ResumenVentasCantadas> valueList = map2.get(rc.getTerritorio()); if (valueList == null) { valueList = new ArrayList<>(); valueList.add(rc); map2.put(rc.getTerritorio(), valueList); } else { valueList.add(rc); } } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Ventas Detalle"); configAnchoCols(sheet); //Fonts Font fontSubTitulo = workbook.createFont(); fontSubTitulo.setFontHeightInPoints((short) 8); fontSubTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD); //Fonts Font fontTotal3 = workbook.createFont(); fontTotal3.setFontHeightInPoints((short) 8); fontTotal3.setColor(HSSFColor.RED.index); fontTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD); //Fonts Font fontTerritorioTotal3 = workbook.createFont(); fontTerritorioTotal3.setFontHeightInPoints((short) 8); fontTerritorioTotal3.setColor(HSSFColor.ORANGE.index); fontTerritorioTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD); //Estilos DataFormat format = workbook.createDataFormat(); CellStyle styleTotal3 = workbook.createCellStyle(); styleTotal3.setFont(fontTotal3); styleTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setDataFormat(format.getFormat("0.0%")); styleTerritorioTotal3 = workbook.createCellStyle(); styleTerritorioTotal3.setFont(fontTerritorioTotal3); styleTerritorioTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleTerritorioTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); // styleTerritorioTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); // styleTerritorioTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); creaCabecera(workbook, sheet); String[] aCols20 = null; String[] aCols10 = null; String[] aColsGrue = null; String indicesTotales = ""; for (Map.Entry<String, List<ResumenVentasCantadas>> entry : map2.entrySet()) { ++indexRow; HSSFRow row = sheet.createRow((++indexRow)); HSSFCell cellTerr = row.createCell(indexCol); cellTerr.setCellValue(entry.getKey().toUpperCase()); cellTerr.setCellStyle(styleTerritorio); int indexInicioGrupo = indexRow + 2; List<ResumenVentasCantadas> detalles = entry.getValue(); Comparator<ResumenVentasCantadas> comp = new Comparator<ResumenVentasCantadas>() { @Override public int compare(ResumenVentasCantadas o1, ResumenVentasCantadas o2) { return o1.getOrden() > o2.getOrden() ? 1 : -1; } }; Collections.sort(detalles, comp); for (ResumenVentasCantadas rv : detalles) { cols20 = ""; cols10 = ""; colsGrue = ""; int indexFilaActual = ++indexRow; HSSFRow row1 = sheet.createRow((indexFilaActual)); HSSFCell cellZona = row1.createCell(indexCol + 0); cellZona.setCellValue(rv.getZona()); cellZona.setCellStyle(styleTitulo9); HSSFCell cellVendedor = row1.createCell(indexCol + 1); cellVendedor.setCellValue(rv.getVendedor()); cellVendedor.setCellStyle(styleTitulo9); HSSFCell cellBoletas = row1.createCell(indexCol + 2); cellBoletas.setCellValue(rv.getCantboletas()); cellBoletas.setCellStyle(styleCantidad); generarDetalles(row1, indexFilaActual, rv); } int indexFinGrupo = indexRow + 1; int indexTotal1 = ++indexRow; int indexTotal2 = ++indexRow; int indexTotal3 = ++indexRow; HSSFRow rowTotal1 = sheet.createRow(indexTotal1); HSSFRow rowTotal2 = sheet.createRow((indexTotal2)); HSSFRow rowTotal3 = sheet.createRow((indexTotal3)); aCols20 = cols20.split(","); aCols10 = cols10.split(","); aColsGrue = colsGrue.split(","); //TOTAL 1 generarTotal1(rowTotal1, indexInicioGrupo, indexFinGrupo, aColsGrue, aCols20, aCols10, cellTerr); //TOTAL 2 generarTotal2(rowTotal2, indexFinGrupo, aColsGrue, cellTerr); //TOTAL 3 generarTotal3(rowTotal3, indexFinGrupo, styleTotal3, cellTerr, sheet); //++indexRow; indicesTotales += (indexFinGrupo + 1) + ","; } String[] aIndexTotales = indicesTotales.split(","); //TOTAL pais int indexTotalPais = ++indexRow; HSSFRow rowTotalPais = sheet.createRow((indexTotalPais)); generarTotalPais(rowTotalPais, styleTotal3, sheet, aIndexTotales, aColsGrue, aCols20, aCols10); HttpServletResponse response = (HttpServletResponse) FacesContext.getCurrentInstance() .getExternalContext().getResponse(); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=Ventas Cantadas.xls"); workbook.write(response.getOutputStream()); response.getOutputStream().flush(); response.getOutputStream().close(); FacesContext.getCurrentInstance().responseComplete(); indexRow = 1; } }
From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java
public void generarExcelVentasCantadasDetalle() throws IOException { cargaResumenPorVendedor();//from w ww. java 2 s . co m if (ventasCantadas == null || ventasCantadas.isEmpty()) { JsfUtil.addErrorMessage("No hay datos para generar"); } else { Map<String, List<ResumenVentasCantadas>> map2 = new HashMap<>(); for (ResumenVentasCantadas rc : ventasCantadas) { List<ResumenVentasCantadas> valueList = map2.get(rc.getTerritorio()); if (valueList == null) { valueList = new ArrayList<>(); valueList.add(rc); map2.put(rc.getTerritorio(), valueList); } else { valueList.add(rc); } } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Ventas Detalle"); configAnchoCols(sheet); //Fonts Font fontSubTitulo = workbook.createFont(); fontSubTitulo.setFontHeightInPoints((short) 8); fontSubTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD); //Fonts Font fontTotal3 = workbook.createFont(); fontTotal3.setFontHeightInPoints((short) 8); fontTotal3.setColor(HSSFColor.RED.index); fontTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD); //Fonts Font fontTerritorioTotal3 = workbook.createFont(); fontTerritorioTotal3.setFontHeightInPoints((short) 8); fontTerritorioTotal3.setColor(HSSFColor.ORANGE.index); fontTerritorioTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD); //Estilos DataFormat format = workbook.createDataFormat(); CellStyle styleTotal3 = workbook.createCellStyle(); styleTotal3.setFont(fontTotal3); styleTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setDataFormat(format.getFormat("0.0%")); styleTerritorioTotal3 = workbook.createCellStyle(); styleTerritorioTotal3.setFont(fontTerritorioTotal3); styleTerritorioTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleTerritorioTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); // styleTerritorioTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); // styleTerritorioTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); creaCabecera(workbook, sheet); String[] aCols20 = null; String[] aCols10 = null; String[] aColsGrue = null; String indicesTotales = ""; for (Map.Entry<String, List<ResumenVentasCantadas>> entry : map2.entrySet()) { ++indexRow; HSSFRow row = sheet.createRow((++indexRow)); HSSFCell cellTerr = row.createCell(indexCol); cellTerr.setCellValue(entry.getKey().toUpperCase()); cellTerr.setCellStyle(styleTerritorio); int indexInicioGrupo = indexRow + 2; List<ResumenVentasCantadas> detalles = entry.getValue(); Comparator<ResumenVentasCantadas> comp = new Comparator<ResumenVentasCantadas>() { @Override public int compare(ResumenVentasCantadas o1, ResumenVentasCantadas o2) { return o1.getOrden() > o2.getOrden() ? 1 : -1; } }; Collections.sort(detalles, comp); for (ResumenVentasCantadas rv : detalles) { cols20 = ""; cols10 = ""; colsGrue = ""; int indexFilaActual = ++indexRow; HSSFRow row1 = sheet.createRow((indexFilaActual)); HSSFCell cellZona = row1.createCell(indexCol + 0); cellZona.setCellValue(rv.getZona()); cellZona.setCellStyle(styleTitulo9); HSSFCell cellVendedor = row1.createCell(indexCol + 1); cellVendedor.setCellValue(rv.getVendedor()); cellVendedor.setCellStyle(styleTitulo9); HSSFCell cellBoletas = row1.createCell(indexCol + 2); cellBoletas.setCellValue(rv.getCantboletas()); cellBoletas.setCellStyle(styleCantidad); generarDetalles(row1, indexFilaActual, rv); } int indexFinGrupo = indexRow + 1; int indexTotal1 = ++indexRow; int indexTotal2 = ++indexRow; int indexTotal3 = ++indexRow; HSSFRow rowTotal1 = sheet.createRow(indexTotal1); HSSFRow rowTotal2 = sheet.createRow((indexTotal2)); HSSFRow rowTotal3 = sheet.createRow((indexTotal3)); aCols20 = cols20.split(","); aCols10 = cols10.split(","); aColsGrue = colsGrue.split(","); //TOTAL 1 generarTotal1(rowTotal1, indexInicioGrupo, indexFinGrupo, aColsGrue, aCols20, aCols10, cellTerr); //TOTAL 2 generarTotal2(rowTotal2, indexFinGrupo, aColsGrue, cellTerr); //TOTAL 3 generarTotal3(rowTotal3, indexFinGrupo, styleTotal3, cellTerr, sheet); //++indexRow; indicesTotales += (indexFinGrupo + 1) + ","; } String[] aIndexTotales = indicesTotales.split(","); //TOTAL pais int indexTotalPais = ++indexRow; HSSFRow rowTotalPais = sheet.createRow((indexTotalPais)); generarTotalPais(rowTotalPais, styleTotal3, sheet, aIndexTotales, aColsGrue, aCols20, aCols10); HttpServletResponse response = (HttpServletResponse) FacesContext.getCurrentInstance() .getExternalContext().getResponse(); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=Ventas Cantadas.xls"); workbook.write(response.getOutputStream()); response.getOutputStream().flush(); response.getOutputStream().close(); FacesContext.getCurrentInstance().responseComplete(); indexRow = 1; } }
From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java
private void creaCabecera(HSSFWorkbook workbook, HSSFSheet sheet) { Font fontSubTitulo = workbook.createFont(); fontSubTitulo.setFontHeightInPoints((short) 8); fontSubTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fontSubTituloAzul = workbook.createFont(); fontSubTituloAzul.setFontHeightInPoints((short) 8); fontSubTituloAzul.setColor(HSSFColor.DARK_BLUE.index); fontSubTituloAzul.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fontSubTituloCaje = workbook.createFont(); fontSubTituloCaje.setFontHeightInPoints((short) 7); fontSubTituloCaje.setColor(HSSFColor.DARK_BLUE.index); fontSubTituloCaje.setBoldweight(Font.BOLDWEIGHT_BOLD); Font font7 = workbook.createFont(); font7.setFontHeightInPoints((short) 6); Font fontTerritorio = workbook.createFont(); fontTerritorio.setItalic(true);//from w w w . j a v a 2 s .c o m fontTerritorio.setFontHeightInPoints((short) 12); fontTerritorio.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fontTerritorioTotal1 = workbook.createFont(); fontTerritorioTotal1.setItalic(true); fontTerritorioTotal1.setFontHeightInPoints((short) 8); fontTerritorioTotal1.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fontTitulo9 = workbook.createFont(); fontTitulo9.setFontHeightInPoints((short) 8); Font fontTitulo = workbook.createFont(); fontTitulo.setFontHeightInPoints((short) 12); fontTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD); styleTitulo = workbook.createCellStyle(); styleTitulo.setFont(fontTitulo); styleTitulo.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleTerritorio = workbook.createCellStyle(); styleTerritorio.setFont(fontTerritorio); styleTerritorioTotal1 = workbook.createCellStyle(); styleTerritorioTotal1.setFont(fontTerritorioTotal1); styleTerritorioTotal1.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTerritorioTotal1.setBorderTop(HSSFCellStyle.BORDER_THIN); //styleCantidad.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTerritorioTotal1.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleTitulo9 = workbook.createCellStyle(); styleTitulo9.setFont(fontTitulo9); styleTitulo9.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTitulo9.setBorderTop(HSSFCellStyle.BORDER_THIN); //styleCantidad.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTitulo9.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleSubTitulo = workbook.createCellStyle(); styleSubTitulo.setFont(fontSubTitulo); styleSubTitulo.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleSubTitulo.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleSubTitulo.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleSubTitulo.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleSubTitulo.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleSubTituloCaje = workbook.createCellStyle(); styleSubTituloCaje.setFont(fontSubTituloCaje); styleSubTituloCaje.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleSubTituloCaje.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleSubTituloCaje.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleSubTituloCaje.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleSubTituloCaje.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleMergeCentrado = workbook.createCellStyle(); styleMergeCentrado.setFont(fontSubTituloAzul); styleMergeCentrado.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleMergeCentrado.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleMergeCentrado.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleMergeCentrado.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleMergeCentrado.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleMergeCentrado.setWrapText(true); styleMergeTotal = workbook.createCellStyle(); styleMergeTotal.setFont(fontSubTitulo); styleMergeTotal.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); styleMergeTotal.setFillPattern(CellStyle.SOLID_FOREGROUND); styleMergeTotal.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleMergeTotal.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleMergeTotal.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleMergeTotal.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleMergeTotal.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleMergeTotal.setAlignment(HSSFCellStyle.ALIGN_CENTER); DataFormat format = workbook.createDataFormat(); styleCantidadAzul = workbook.createCellStyle(); styleCantidadAzul.setFont(fontSubTituloAzul); styleCantidadAzul.setDataFormat(format.getFormat("#,##0")); styleCantidadAzul.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleCantidadAzul.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleCantidadAzul.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleCantidadAzul.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleCantidadAzul.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCantidadNegrita = workbook.createCellStyle(); styleCantidadNegrita.setFont(fontSubTitulo); styleCantidadNegrita.setDataFormat(format.getFormat("#,##0")); styleCantidadNegrita.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleCantidadNegrita.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleCantidadNegrita.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleCantidadNegrita.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleCantidadGris = workbook.createCellStyle(); styleCantidadGris.setFont(fontSubTitulo); styleCantidadGris.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); styleCantidadGris.setFillPattern(CellStyle.SOLID_FOREGROUND); styleCantidadGris.setDataFormat(format.getFormat("#,##0")); styleCantidadGris.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleCantidadGris.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleCantidadGris.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleCantidadGris.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleCantidad = workbook.createCellStyle(); styleCantidad.setFont(fontTitulo9); styleCantidad.setDataFormat(format.getFormat("#,##0")); styleCantidad.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCantidad.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCantidad.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCantidad.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCantidadGris1 = workbook.createCellStyle(); styleCantidadGris1.setFont(fontTitulo9); styleCantidadGris1.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); styleCantidadGris1.setFillPattern(CellStyle.SOLID_FOREGROUND); styleCantidadGris1.setDataFormat(format.getFormat("#,##0")); styleCantidadGris1.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCantidadGris1.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCantidadGris1.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCantidadGris1.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCantidadCaje = workbook.createCellStyle(); styleCantidadCaje.setFont(font7); styleCantidadCaje.setDataFormat(format.getFormat("#,##0")); styleCantidadCaje.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCantidadCaje.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCantidadCaje.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCantidadCaje.setBorderLeft(HSSFCellStyle.BORDER_THIN); HSSFRow rowTitulo = sheet.createRow(0); HSSFCell cellTitulo = rowTitulo.createCell(18); cellTitulo.setCellValue("CONTROL DIARIO DE VENTAS"); cellTitulo.setCellStyle(styleTitulo); HSSFRow rowFecha = sheet.createRow(++indexRow); HSSFCell cellFecha = rowFecha.createCell(18); // SimpleDateFormat sdf = new SimpleDateFormat("EEEE, dd 'de' MMMM 'de' yyyy", new Locale("es", "py")); SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy", new Locale("es", "py")); cellFecha.setCellValue("De " + sdf.format(inicio) + " al " + sdf.format(fin)); //Calendar calender = Calendar.getInstance(); //cellFecha.setCellValue(calender.get(getInicio())); cellFecha.setCellStyle(styleTitulo); ++indexRow; HSSFRow rowCabeceraMarca = sheet.createRow((++indexRow)); HSSFRow rowCabeceraProducto = sheet.createRow((++indexRow)); HSSFRow rowCabecerauM = sheet.createRow((++indexRow)); rowCabeceraProducto.setHeightInPoints((2 * sheet.getDefaultRowHeightInPoints())); HSSFCell cell0 = rowCabecerauM.createCell(indexCol); cell0.setCellValue("Zona"); cell0.setCellStyle(styleSubTitulo); HSSFCell cell1 = rowCabecerauM.createCell(indexCol + 1); cell1.setCellValue("Vendedor"); cell1.setCellStyle(styleSubTitulo); HSSFCell cell2 = rowCabecerauM.createCell(indexCol + 2); cell2.setCellValue("Boletas"); cell2.setCellStyle(styleSubTitulo); HSSFCell cell3 = rowCabeceraProducto.createCell(indexCol + 3); HSSFCell cell4 = rowCabeceraProducto.createCell(indexCol + 4); cell3.setCellValue("Palermo Red Box 20"); cell3.setCellStyle(styleMergeCentrado); cell4.setCellValue(""); cell4.setCellStyle(styleMergeCentrado); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 3) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 4) + "$" + indexRow)); HSSFCell cell3g = rowCabecerauM.createCell(indexCol + 3); cell3g.setCellValue("Grue"); cell3g.setCellStyle(styleMergeCentrado); HSSFCell cell3c = rowCabecerauM.createCell(indexCol + 4); cell3c.setCellValue("Caj"); cell3c.setCellStyle(styleSubTituloCaje); HSSFCell cell5 = (rowCabeceraProducto).createCell(indexCol + 5); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 5) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 6) + "$" + indexRow)); cell5.setCellValue("Palermo Blue Box 20"); cell5.setCellStyle(styleMergeCentrado); HSSFCell cell5g = rowCabecerauM.createCell(indexCol + 5); cell5g.setCellValue("Grue"); cell5g.setCellStyle(styleMergeCentrado); HSSFCell cell5c = rowCabecerauM.createCell(indexCol + 6); cell5c.setCellValue("Caj"); cell5c.setCellStyle(styleSubTituloCaje); HSSFCell cell7 = (rowCabeceraProducto).createCell(indexCol + 7); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 7) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 8) + "$" + indexRow)); cell7.setCellValue("Palermo Green Box 20"); cell7.setCellStyle(styleMergeCentrado); HSSFCell cell7g = rowCabecerauM.createCell(indexCol + 7); cell7g.setCellValue("Grue"); cell7g.setCellStyle(styleMergeCentrado); HSSFCell cell7c = rowCabecerauM.createCell(indexCol + 8); cell7c.setCellValue("Caj"); cell7c.setCellStyle(styleSubTituloCaje); HSSFCell cell9 = (rowCabeceraProducto).createCell(indexCol + 9); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 9) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 10) + "$" + indexRow)); cell9.setCellValue("Palermo Tres Box 20"); cell9.setCellStyle(styleMergeCentrado); HSSFCell cell9g = rowCabecerauM.createCell(indexCol + 9); cell9g.setCellValue("Grue"); cell9g.setCellStyle(styleMergeCentrado); HSSFCell cell9c = rowCabecerauM.createCell(indexCol + 10); cell9c.setCellValue("Caj"); cell9c.setCellStyle(styleSubTituloCaje); HSSFCell cell11 = (rowCabeceraProducto).createCell(indexCol + 11); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 11) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 12) + "$" + indexRow)); cell11.setCellValue("Palermo Duo Box 20"); cell11.setCellStyle(styleMergeCentrado); HSSFCell cell11g = rowCabecerauM.createCell(indexCol + 11); cell11g.setCellValue("Grue"); cell11g.setCellStyle(styleMergeCentrado); HSSFCell cell11c = rowCabecerauM.createCell(indexCol + 12); cell11c.setCellValue("Caj"); cell11c.setCellStyle(styleSubTituloCaje); HSSFCell cell13 = rowCabeceraProducto.createCell(indexCol + 13); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 13) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 14) + "$" + indexRow)); cell13.setCellValue("Palermo Red Box 10"); cell13.setCellStyle(styleMergeCentrado); HSSFCell cell13g = rowCabecerauM.createCell(indexCol + 13); cell13g.setCellValue("Grue"); cell13g.setCellStyle(styleMergeCentrado); HSSFCell cell13c = rowCabecerauM.createCell(indexCol + 14); cell13c.setCellValue("Caj"); cell13c.setCellStyle(styleSubTituloCaje); HSSFCell cell15 = (rowCabeceraProducto).createCell(indexCol + 15); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 15) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 16) + "$" + indexRow)); cell15.setCellValue("Palermo Blue Box 10"); cell15.setCellStyle(styleMergeCentrado); HSSFCell cell15g = rowCabecerauM.createCell(indexCol + 15); cell15g.setCellValue("Grue"); cell15g.setCellStyle(styleMergeCentrado); HSSFCell cell15c = rowCabecerauM.createCell(indexCol + 16); cell15c.setCellValue("Caj"); cell15c.setCellStyle(styleSubTituloCaje); HSSFCell cell17 = (rowCabeceraProducto).createCell(indexCol + 17); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 17) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 18) + "$" + indexRow)); cell17.setCellValue("Palermo Green Box 10"); cell17.setCellStyle(styleMergeCentrado); HSSFCell cell17g = rowCabecerauM.createCell(indexCol + 17); cell17g.setCellValue("Grue"); cell17g.setCellStyle(styleMergeCentrado); HSSFCell cell17c = rowCabecerauM.createCell(indexCol + 18); cell17c.setCellValue("Caj"); cell17c.setCellStyle(styleSubTituloCaje); HSSFCell cell19 = (rowCabeceraProducto).createCell(indexCol + 19); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 19) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 20) + "$" + indexRow)); cell19.setCellValue("Palermo Tres Box 10"); cell19.setCellStyle(styleMergeCentrado); HSSFCell cell19g = rowCabecerauM.createCell(indexCol + 19); cell19g.setCellValue("Grue"); cell19g.setCellStyle(styleMergeCentrado); HSSFCell cell19c = rowCabecerauM.createCell(indexCol + 20); cell19c.setCellValue("Caj"); cell19c.setCellStyle(styleSubTituloCaje); HSSFCell cell21 = (rowCabeceraProducto).createCell(indexCol + 21); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 21) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 22) + "$" + indexRow)); cell21.setCellValue("Palermo Duo Box 10"); cell21.setCellStyle(styleMergeCentrado); HSSFCell cell21g = rowCabecerauM.createCell(indexCol + 21); cell21g.setCellValue("Grue"); cell21g.setCellStyle(styleMergeCentrado); HSSFCell cell21c = rowCabecerauM.createCell(indexCol + 22); cell21c.setCellValue("Caj"); cell21c.setCellStyle(styleSubTituloCaje); HSSFCell cell23 = (rowCabeceraProducto).createCell(indexCol + 23); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 23) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 24) + "$" + indexRow)); cell23.setCellValue("San Marino TYPE Box 20"); cell23.setCellStyle(styleMergeCentrado); HSSFCell cell23g = rowCabecerauM.createCell(indexCol + 23); cell23g.setCellValue("Grue"); cell23g.setCellStyle(styleMergeCentrado); HSSFCell cell23c = rowCabecerauM.createCell(indexCol + 24); cell23c.setCellValue("Caj"); cell23c.setCellStyle(styleSubTituloCaje); HSSFCell cell25 = (rowCabeceraProducto).createCell(indexCol + 25); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 25) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 26) + "$" + indexRow)); cell25.setCellValue("San Marino TYPE Box 10"); cell25.setCellStyle(styleMergeCentrado); HSSFCell cell25g = rowCabecerauM.createCell(indexCol + 25); cell25g.setCellValue("Grue"); cell25g.setCellStyle(styleMergeCentrado); HSSFCell cell25c = rowCabecerauM.createCell(indexCol + 26); cell25c.setCellValue("Caj"); cell25c.setCellStyle(styleSubTituloCaje); HSSFCell cell27 = (rowCabeceraProducto).createCell(indexCol + 27); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 27) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 28) + "$" + indexRow)); cell27.setCellValue("San Marino TYPE Soft"); cell27.setCellStyle(styleMergeCentrado); HSSFCell cell27g = rowCabecerauM.createCell(indexCol + 27); cell27g.setCellValue("Grue"); cell27g.setCellStyle(styleMergeCentrado); HSSFCell cell27c = rowCabecerauM.createCell(indexCol + 28); cell27c.setCellValue("Caj"); cell27c.setCellStyle(styleSubTituloCaje); HSSFCell cell29 = (rowCabeceraProducto).createCell(indexCol + 29); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 29) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 30) + "$" + indexRow)); cell29.setCellValue("Kentucky Box 20"); cell29.setCellStyle(styleMergeCentrado); HSSFCell cell29g = rowCabecerauM.createCell(indexCol + 29); cell29g.setCellValue("Grue"); cell29g.setCellStyle(styleMergeCentrado); HSSFCell cell29c = rowCabecerauM.createCell(indexCol + 30); cell29c.setCellValue("Caj"); cell29c.setCellStyle(styleSubTituloCaje); HSSFCell cell31 = (rowCabeceraProducto).createCell(indexCol + 31); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 31) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 32) + "$" + indexRow)); cell31.setCellValue("Kentucky Box 10"); cell31.setCellStyle(styleMergeCentrado); HSSFCell cell31g = rowCabecerauM.createCell(indexCol + 31); cell31g.setCellValue("Grue"); cell31g.setCellStyle(styleMergeCentrado); HSSFCell cell31c = rowCabecerauM.createCell(indexCol + 32); cell31c.setCellValue("Caj"); cell31c.setCellStyle(styleSubTituloCaje); HSSFCell cell33 = (rowCabeceraProducto).createCell(indexCol + 33); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 33) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 34) + "$" + indexRow)); cell33.setCellValue("Kentucky Soft"); cell33.setCellStyle(styleMergeCentrado); HSSFCell cell33g = rowCabecerauM.createCell(indexCol + 33); cell33g.setCellValue("Grue"); cell33g.setCellStyle(styleMergeCentrado); HSSFCell cell33c = rowCabecerauM.createCell(indexCol + 34); cell33c.setCellValue("Caj"); cell33c.setCellStyle(styleSubTituloCaje); HSSFCell cell35 = rowCabeceraProducto.createCell(indexCol + 35); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 35) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 36) + "$" + indexRow)); cell35.setCellValue("Total"); cell35.setCellStyle(styleMergeTotal); HSSFCell cell38 = rowCabeceraProducto.createCell(indexCol + 36); cell38.setCellValue(""); cell38.setCellStyle(styleMergeTotal); HSSFCell cell35g = rowCabecerauM.createCell(indexCol + 35); cell35g.setCellValue("Gruesas"); cell35g.setCellStyle(styleSubTitulo); HSSFCell cell35c = rowCabecerauM.createCell(indexCol + 36); cell35c.setCellValue("Caje"); cell35c.setCellStyle(styleSubTitulo); HSSFCell cell37c = rowCabecerauM.createCell(indexCol + 37); cell37c.setCellValue("Cajas"); cell37c.setCellStyle(styleSubTitulo); HSSFCell cell38g = rowCabecerauM.createCell(indexCol + 38); cell38g.setCellValue("+gr"); cell38g.setCellStyle(styleSubTitulo); sheet.createFreezePane(2, 7); }
From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java
private void generarTotalPais(HSSFRow rowTotalPais, CellStyle styleTotal3, HSSFSheet sheet, String[] aIndexTotales, String[] aColsGrue, String[] aCols20, String[] aCols10) { //GeneraTotal1Pais HSSFCell cellTotal1Pais = rowTotalPais.createCell(indexCol); cellTotal1Pais.setCellValue("Total Pas"); cellTotal1Pais.setCellStyle(styleTerritorioTotal1); HSSFCell cellTotal1Boleta = rowTotalPais.createCell(indexCol + 2); cellTotal1Boleta.setCellType(HSSFCell.CELL_TYPE_FORMULA); String formBoletaTotal1Pais = ""; String formRed20Total1Paisg = ""; String formRed20Total1Paisc = ""; String formBlue20Total1Paisg = ""; String formBlue20Total1Paisc = ""; String formGreen20Total1Paisg = ""; String formGreen20Total1Paisc = ""; String formTres20Total1Paisg = ""; String formTres20Total1Paisc = ""; String formDuo20Total1Paisg = ""; String formDuo20Total1Paisc = ""; String formRed10Total1Paisg = ""; String formRed10Total1Paisc = ""; String formBlue10Total1Paisg = ""; String formBlue10Total1Paisc = ""; String formGreen10Total1Paisg = ""; String formGreen10Total1Paisc = ""; String formTres10Total1Paisg = ""; String formTres10Total1Paisc = ""; String formDuo10Total1Paisg = ""; String formDuo10Total1Paisc = ""; String formSM20Total1Paisg = ""; String formSM20Total1Paisc = ""; String formSM10Total1Paisg = ""; String formSM10Total1Paisc = ""; String formSMSoftTotal1Paisg = ""; String formSMSoftTotal1Paisc = ""; String formKY20Total1Paisg = ""; String formKY20Total1Paisc = ""; String formKY10Total1Paisg = ""; String formKY10Total1Paisc = ""; String formKYSoftTotal1Paisg = ""; String formKYSoftTotal1Paisc = ""; String formRed20Total2Paisg = ""; String formBlue20Total2Paisg = ""; String formGreen20Total2Paisg = ""; String formTres20Total2Paisg = ""; String formDuo20Total2Paisg = ""; String formRed10Total2Paisg = ""; String formBlue10Total2Paisg = ""; String formGreen10Total2Paisg = ""; String formTres10Total2Paisg = ""; String formDuo10Total2Paisg = ""; String formSM20Total2Paisg = ""; String formSM10Total2Paisg = ""; String formSMSoftTotal2Paisg = ""; String formKY20Total2Paisg = ""; String formKY10Total2Paisg = ""; String formKYSoftTotal2Paisg = ""; for (int i = 0; i < aIndexTotales.length; i++) { String indiceFila = aIndexTotales[i]; if (indiceFila != null && indiceFila.length() > 0) { formBoletaTotal1Pais += CellReference.convertNumToColString(indexCol + 2) + aIndexTotales[i] + "+"; formRed20Total1Paisg += CellReference.convertNumToColString(indexCol + 3) + aIndexTotales[i] + "+"; formRed20Total1Paisc += CellReference.convertNumToColString(indexCol + 4) + aIndexTotales[i] + "+"; // "(INT("+CellReference.convertNumToColString(indexCol + 4)+aIndexTotales[i]+")/10)"; formBlue20Total1Paisg += CellReference.convertNumToColString(indexCol + 5) + aIndexTotales[i] + "+"; formBlue20Total1Paisc += CellReference.convertNumToColString(indexCol + 6) + aIndexTotales[i] + "+"; formGreen20Total1Paisg += CellReference.convertNumToColString(indexCol + 7) + aIndexTotales[i] + "+"; formGreen20Total1Paisc += CellReference.convertNumToColString(indexCol + 8) + aIndexTotales[i] + "+"; formTres20Total1Paisg += CellReference.convertNumToColString(indexCol + 9) + aIndexTotales[i] + "+"; formTres20Total1Paisc += CellReference.convertNumToColString(indexCol + 10) + aIndexTotales[i] + "+"; formDuo20Total1Paisg += CellReference.convertNumToColString(indexCol + 11) + aIndexTotales[i] + "+"; formDuo20Total1Paisc += CellReference.convertNumToColString(indexCol + 12) + aIndexTotales[i] + "+"; formRed10Total1Paisg += CellReference.convertNumToColString(indexCol + 13) + aIndexTotales[i] + "+"; formRed10Total1Paisc += CellReference.convertNumToColString(indexCol + 14) + aIndexTotales[i] + "+"; // "(INT("+CellReference.convertNumToColString(indexCol + 4)+aIndexTotales[i]+")/10)"; formBlue10Total1Paisg += CellReference.convertNumToColString(indexCol + 15) + aIndexTotales[i] + "+"; formBlue10Total1Paisc += CellReference.convertNumToColString(indexCol + 16) + aIndexTotales[i] + "+"; formGreen10Total1Paisg += CellReference.convertNumToColString(indexCol + 17) + aIndexTotales[i] + "+"; formGreen10Total1Paisc += CellReference.convertNumToColString(indexCol + 18) + aIndexTotales[i] + "+"; formTres10Total1Paisg += CellReference.convertNumToColString(indexCol + 19) + aIndexTotales[i] + "+"; formTres10Total1Paisc += CellReference.convertNumToColString(indexCol + 20) + aIndexTotales[i] + "+"; formDuo10Total1Paisg += CellReference.convertNumToColString(indexCol + 21) + aIndexTotales[i] + "+"; formDuo10Total1Paisc += CellReference.convertNumToColString(indexCol + 22) + aIndexTotales[i] + "+"; formSM20Total1Paisg += CellReference.convertNumToColString(indexCol + 23) + aIndexTotales[i] + "+"; formSM20Total1Paisc += CellReference.convertNumToColString(indexCol + 24) + aIndexTotales[i] + "+"; formSM10Total1Paisg += CellReference.convertNumToColString(indexCol + 25) + aIndexTotales[i] + "+"; formSM10Total1Paisc += CellReference.convertNumToColString(indexCol + 26) + aIndexTotales[i] + "+"; formSMSoftTotal1Paisg += CellReference.convertNumToColString(indexCol + 27) + aIndexTotales[i] + "+"; formSMSoftTotal1Paisc += CellReference.convertNumToColString(indexCol + 28) + aIndexTotales[i] + "+"; formKY20Total1Paisg += CellReference.convertNumToColString(indexCol + 29) + aIndexTotales[i] + "+"; formKY20Total1Paisc += CellReference.convertNumToColString(indexCol + 30) + aIndexTotales[i] + "+"; formKY10Total1Paisg += CellReference.convertNumToColString(indexCol + 31) + aIndexTotales[i] + "+"; formKY10Total1Paisc += CellReference.convertNumToColString(indexCol + 32) + aIndexTotales[i] + "+"; formKYSoftTotal1Paisg += CellReference.convertNumToColString(indexCol + 33) + aIndexTotales[i] + "+"; formKYSoftTotal1Paisc += CellReference.convertNumToColString(indexCol + 34) + aIndexTotales[i] + "+"; }/*w w w. j a va 2 s.co m*/ } System.out.println("Formula Boletas 1 : " + formBoletaTotal1Pais); formRed20Total1Paisg += "0"; formRed20Total1Paisc += "0"; formRed20Total2Paisg = "(" + formRed20Total1Paisg + ")/50"; formRed20Total1Paisg = formRed20Total1Paisg + "+(INT((" + formRed20Total1Paisc + ")/10))"; formRed20Total1Paisc = "+(MOD((" + formRed20Total1Paisc + "),10))"; formBlue20Total1Paisg += "0"; formBlue20Total1Paisc += "0"; formBlue20Total2Paisg = "(" + formBlue20Total1Paisg + ")/50"; formBlue20Total1Paisg = formBlue20Total1Paisg + "+(INT((" + formBlue20Total1Paisc + ")/10))"; formBlue20Total1Paisc = "+(MOD((" + formBlue20Total1Paisc + "),10))"; formGreen20Total1Paisg += "0"; formGreen20Total1Paisc += "0"; formGreen20Total2Paisg = "(" + formGreen20Total1Paisg + ")/50"; formGreen20Total1Paisg = formGreen20Total1Paisg + "+(INT((" + formGreen20Total1Paisc + ")/10))"; formGreen20Total1Paisc = "+(MOD((" + formGreen20Total1Paisc + "),10))"; formTres20Total1Paisg += "0"; formTres20Total1Paisc += "0"; formTres20Total2Paisg = "(" + formTres20Total1Paisg + ")/50"; formTres20Total1Paisg = formTres20Total1Paisg + "+(INT((" + formTres20Total1Paisc + ")/10))"; formTres20Total1Paisc = "+(MOD((" + formTres20Total1Paisc + "),10))"; formDuo20Total1Paisg += "0"; formDuo20Total1Paisc += "0"; formDuo20Total2Paisg = "(" + formDuo20Total1Paisg + ")/50"; formDuo20Total1Paisg = formDuo20Total1Paisg + "+(INT((" + formDuo20Total1Paisc + ")/10))"; formDuo20Total1Paisc = "+(MOD((" + formDuo20Total1Paisc + "),10))"; formRed10Total1Paisg += "0"; formRed10Total1Paisc += "0"; formRed10Total2Paisg = "(" + formRed10Total1Paisg + ")/50"; formRed10Total1Paisg = formRed10Total1Paisg + "+(INT((" + formRed10Total1Paisc + ")/20))"; formRed10Total1Paisc = "+(MOD((" + formRed10Total1Paisc + "),20))"; formBlue10Total1Paisg += "0"; formBlue10Total1Paisc += "0"; formBlue10Total2Paisg = "(" + formBlue10Total1Paisg + ")/50"; formBlue10Total1Paisg = formBlue10Total1Paisg + "+(INT((" + formBlue10Total1Paisc + ")/20))"; formBlue10Total1Paisc = "+(MOD((" + formBlue10Total1Paisc + "),20))"; formGreen10Total1Paisg += "0"; formGreen10Total1Paisc += "0"; formGreen10Total2Paisg = "(" + formGreen10Total1Paisg + ")/50"; formGreen10Total1Paisg = formGreen10Total1Paisg + "+(INT((" + formGreen10Total1Paisc + ")/20))"; formGreen10Total1Paisc = "+(MOD((" + formGreen10Total1Paisc + "),20))"; formTres10Total1Paisg += "0"; formTres10Total1Paisc += "0"; formTres10Total2Paisg = "(" + formTres10Total1Paisg + ")/50"; formTres10Total1Paisg = formTres10Total1Paisg + "+(INT((" + formTres10Total1Paisc + ")/20))"; formTres10Total1Paisc = "+(MOD((" + formTres10Total1Paisc + "),20))"; formDuo10Total1Paisg += "0"; formDuo10Total1Paisc += "0"; formDuo10Total2Paisg = "(" + formDuo10Total1Paisg + ")/50"; formDuo10Total1Paisg = formDuo10Total1Paisg + "+(INT((" + formDuo10Total1Paisc + ")/20))"; formDuo10Total1Paisc = "+(MOD((" + formDuo10Total1Paisc + "),20))"; formSM20Total1Paisg += "0"; formSM20Total1Paisc += "0"; formSM20Total2Paisg = "(" + formSM20Total1Paisg + ")/50"; formSM20Total1Paisg = formSM20Total1Paisg + "+(INT((" + formSM20Total1Paisc + ")/10))"; formSM20Total1Paisc = "+(MOD((" + formSM20Total1Paisc + "),10))"; formSM10Total1Paisg += "0"; formSM10Total1Paisc += "0"; formSM10Total2Paisg = "(" + formSM10Total1Paisg + ")/50"; formSM10Total1Paisg = formSM10Total1Paisg + "+(INT((" + formSM10Total1Paisc + ")/20))"; formSM10Total1Paisc = "+(MOD((" + formSM10Total1Paisc + "),20))"; formSMSoftTotal1Paisg += "0"; formSMSoftTotal1Paisc += "0"; formSMSoftTotal2Paisg = "(" + formSMSoftTotal1Paisg + ")/50"; formSMSoftTotal1Paisg = formSMSoftTotal1Paisg + "+(INT((" + formSMSoftTotal1Paisc + ")/10))"; formSMSoftTotal1Paisc = "+(MOD((" + formSMSoftTotal1Paisc + "),10))"; formKY20Total1Paisg += "0"; formKY20Total1Paisc += "0"; formKY20Total2Paisg = "(" + formKY20Total1Paisg + ")/50"; formKY20Total1Paisg = formKY20Total1Paisg + "+(INT((" + formKY20Total1Paisc + ")/10))"; formKY20Total1Paisc = "+(MOD((" + formKY20Total1Paisc + "),10))"; formKY10Total1Paisg += "0"; formKY10Total1Paisc += "0"; formKY10Total2Paisg = "(" + formKY10Total1Paisg + ")/50"; formKY10Total1Paisg = formKY10Total1Paisg + "+(INT((" + formKY10Total1Paisc + ")/20))"; formKY10Total1Paisc = "+(MOD((" + formKY10Total1Paisc + "),20))"; formKYSoftTotal1Paisg += "0"; formKYSoftTotal1Paisc += "0"; formKYSoftTotal2Paisg = "(" + formKYSoftTotal1Paisg + ")/50"; formKYSoftTotal1Paisg = formKYSoftTotal1Paisg + "+(INT((" + formKYSoftTotal1Paisc + ")/10))"; formKYSoftTotal1Paisc = "+(MOD((" + formKYSoftTotal1Paisc + "),10))"; formBoletaTotal1Pais += "0"; cellTotal1Boleta.setCellFormula(formBoletaTotal1Pais); cellTotal1Boleta.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Red20g = rowTotalPais.createCell(indexCol + 3); cellTotal1Red20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1Red20c = rowTotalPais.createCell(indexCol + 4); cellTotal1Red20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Red20g.setCellFormula(formRed20Total1Paisg); cellTotal1Red20g.setCellStyle(styleCantidadAzul); cellTotal1Red20c.setCellFormula(formRed20Total1Paisc); cellTotal1Red20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Blue20g = rowTotalPais.createCell(indexCol + 5); cellTotal1Blue20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1Blue20c = rowTotalPais.createCell(indexCol + 6); cellTotal1Blue20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Blue20g.setCellFormula(formBlue20Total1Paisg); cellTotal1Blue20g.setCellStyle(styleCantidadAzul); cellTotal1Blue20c.setCellFormula(formBlue20Total1Paisc); cellTotal1Blue20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Green20g = rowTotalPais.createCell(indexCol + 7); cellTotal1Green20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1Green20c = rowTotalPais.createCell(indexCol + 8); cellTotal1Green20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Green20g.setCellFormula(formGreen20Total1Paisg); cellTotal1Green20g.setCellStyle(styleCantidadAzul); cellTotal1Green20c.setCellFormula(formGreen20Total1Paisc); cellTotal1Green20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Tres20g = rowTotalPais.createCell(indexCol + 9); cellTotal1Tres20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1Tres20c = rowTotalPais.createCell(indexCol + 10); cellTotal1Tres20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Tres20g.setCellFormula(formTres20Total1Paisg); cellTotal1Tres20g.setCellStyle(styleCantidadAzul); cellTotal1Tres20c.setCellFormula(formTres20Total1Paisc); cellTotal1Tres20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Duo20g = rowTotalPais.createCell(indexCol + 11); cellTotal1Duo20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1Duo20c = rowTotalPais.createCell(indexCol + 12); cellTotal1Duo20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Duo20g.setCellFormula(formDuo20Total1Paisg); cellTotal1Duo20g.setCellStyle(styleCantidadAzul); cellTotal1Duo20c.setCellFormula(formDuo20Total1Paisc); cellTotal1Duo20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Red10g = rowTotalPais.createCell(indexCol + 13); cellTotal1Red10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1Red10c = rowTotalPais.createCell(indexCol + 14); cellTotal1Red10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Red10g.setCellFormula(formRed10Total1Paisg); cellTotal1Red10g.setCellStyle(styleCantidadAzul); cellTotal1Red10c.setCellFormula(formRed10Total1Paisc); cellTotal1Red10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Blue10g = rowTotalPais.createCell(indexCol + 15); cellTotal1Blue10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1Blue10c = rowTotalPais.createCell(indexCol + 16); cellTotal1Blue10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Blue10g.setCellFormula(formBlue10Total1Paisg); cellTotal1Blue10g.setCellStyle(styleCantidadAzul); cellTotal1Blue10c.setCellFormula(formBlue10Total1Paisc); cellTotal1Blue10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Green10g = rowTotalPais.createCell(indexCol + 17); cellTotal1Green10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1Green10c = rowTotalPais.createCell(indexCol + 18); cellTotal1Green10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Green10g.setCellFormula(formGreen10Total1Paisg); cellTotal1Green10g.setCellStyle(styleCantidadAzul); cellTotal1Green10c.setCellFormula(formGreen10Total1Paisc); cellTotal1Green10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Tres10g = rowTotalPais.createCell(indexCol + 19); cellTotal1Tres10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1Tres10c = rowTotalPais.createCell(indexCol + 20); cellTotal1Tres10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Tres10g.setCellFormula(formTres10Total1Paisg); cellTotal1Tres10g.setCellStyle(styleCantidadAzul); cellTotal1Tres10c.setCellFormula(formTres10Total1Paisc); cellTotal1Tres10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Duo10g = rowTotalPais.createCell(indexCol + 21); cellTotal1Duo10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1Duo10c = rowTotalPais.createCell(indexCol + 22); cellTotal1Duo10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Duo10g.setCellFormula(formDuo10Total1Paisg); cellTotal1Duo10g.setCellStyle(styleCantidadAzul); cellTotal1Duo10c.setCellFormula(formDuo10Total1Paisc); cellTotal1Duo10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SM20g = rowTotalPais.createCell(indexCol + 23); cellTotal1SM20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1SM20c = rowTotalPais.createCell(indexCol + 24); cellTotal1SM20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SM20g.setCellFormula(formSM20Total1Paisg); cellTotal1SM20g.setCellStyle(styleCantidadAzul); cellTotal1SM20c.setCellFormula(formSM20Total1Paisc); cellTotal1SM20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SM10g = rowTotalPais.createCell(indexCol + 25); cellTotal1SM10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1SM10c = rowTotalPais.createCell(indexCol + 26); cellTotal1SM10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SM10g.setCellFormula(formSM10Total1Paisg); cellTotal1SM10g.setCellStyle(styleCantidadAzul); cellTotal1SM10c.setCellFormula(formSM10Total1Paisc); cellTotal1SM10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SMSoftg = rowTotalPais.createCell(indexCol + 27); cellTotal1SMSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1SMSoftc = rowTotalPais.createCell(indexCol + 28); cellTotal1SMSoftc.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SMSoftg.setCellFormula(formSMSoftTotal1Paisg); cellTotal1SMSoftg.setCellStyle(styleCantidadAzul); cellTotal1SMSoftc.setCellFormula(formSMSoftTotal1Paisc); cellTotal1SMSoftc.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1KY20g = rowTotalPais.createCell(indexCol + 29); cellTotal1KY20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1KY20c = rowTotalPais.createCell(indexCol + 30); cellTotal1KY20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1KY20g.setCellFormula(formKY20Total1Paisg); cellTotal1KY20g.setCellStyle(styleCantidadAzul); cellTotal1KY20c.setCellFormula(formKY20Total1Paisc); cellTotal1KY20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1KY10g = rowTotalPais.createCell(indexCol + 31); cellTotal1KY10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1KY10c = rowTotalPais.createCell(indexCol + 32); cellTotal1KY10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1KY10g.setCellFormula(formKY10Total1Paisg); cellTotal1KY10g.setCellStyle(styleCantidadAzul); cellTotal1KY10c.setCellFormula(formKY10Total1Paisc); cellTotal1KY10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1KYSoftg = rowTotalPais.createCell(indexCol + 33); cellTotal1KYSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1KYSoftc = rowTotalPais.createCell(indexCol + 34); cellTotal1KYSoftc.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1KYSoftg.setCellFormula(formKYSoftTotal1Paisg); cellTotal1KYSoftg.setCellStyle(styleCantidadAzul); cellTotal1KYSoftc.setCellFormula(formKYSoftTotal1Paisc); cellTotal1KYSoftc.setCellStyle(styleCantidadAzul); String formTotalPaisGrue = ""; if (aColsGrue != null) { for (int i = 0; i < aColsGrue.length; i++) { formTotalPaisGrue += aColsGrue[i] + (rowTotalPais.getRowNum() + 1) + "+"; System.out.println("Row pais: " + rowTotalPais.getRowNum() + 1); } formTotalPaisGrue += "0"; } String formTotalPais20 = ""; if (aCols20 != null) { for (int i = 0; i < aCols20.length; i++) { formTotalPais20 += aCols20[i] + (rowTotalPais.getRowNum() + 1) + "+"; } formTotalPais20 += "0"; } String formTotalPais10 = ""; if (aCols10 != null) { for (int i = 0; i < aCols10.length; i++) { formTotalPais10 += aCols10[i] + (rowTotalPais.getRowNum() + 1) + "+"; } formTotalPais10 += "0"; } System.out.println("form: " + formTotalPaisGrue); HSSFCell cellTotalPaisg = rowTotalPais.createCell(indexCol + 35); cellTotalPaisg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotalPaisg.setCellFormula("(INT((" + formTotalPais20 + ")/10))+" + "(INT((" + formTotalPais10 + ")/20)) +(" + formTotalPaisGrue + ")"); cellTotalPaisg.setCellStyle(styleCantidadGris); HSSFCell cellTotalPaisc = rowTotalPais.createCell(indexCol + 36); cellTotalPaisc.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotalPaisc.setCellFormula("MOD((" + formTotalPais10 + "),20)+ MOD((" + formTotalPais20 + "),10)"); cellTotalPaisc.setCellStyle(styleCantidadGris); HSSFCell cellTotalPaisca = rowTotalPais.createCell(indexCol + 37); cellTotalPaisca.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotalPaisca.setCellFormula("(INT(" + CellReference.convertNumToColString(indexCol + 35) + (rowTotalPais.getRowNum() + 1) + "/50))"); cellTotalPaisca.setCellStyle(styleCantidadNegrita); HSSFCell cellTotalPaisgr = rowTotalPais.createCell(indexCol + 38); cellTotalPaisgr.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotalPaisgr.setCellFormula("(MOD(" + CellReference.convertNumToColString(indexCol + 35) + (rowTotalPais.getRowNum() + 1) + ",50))"); cellTotalPaisgr.setCellStyle(styleCantidadNegrita); //GeneraTotal2Pais int indexTotal2Pais = ++indexRow; HSSFRow rowTotal2Pais = sheet.createRow((indexTotal2Pais)); HSSFCell cellTotal2Pais = rowTotal2Pais.createCell(indexCol); cellTotal2Pais.setCellValue("Total Pas - Cajas"); cellTotal2Pais.setCellStyle(styleTerritorioTotal1); HSSFCell cellTotal2PaisBoletas = rowTotal2Pais.createCell(indexCol + 2); cellTotal2PaisBoletas.setCellValue(""); cellTotal2PaisBoletas.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisRed20g = rowTotal2Pais.createCell(indexCol + 3); cellTotal2PaisRed20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisRed20g.setCellFormula(formRed20Total2Paisg); cellTotal2PaisRed20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisRed20c = rowTotal2Pais.createCell(indexCol + 4); cellTotal2PaisRed20c.setCellValue(""); cellTotal2PaisRed20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisBlue20g = rowTotal2Pais.createCell(indexCol + 5); cellTotal2PaisBlue20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisBlue20g.setCellFormula(formBlue20Total2Paisg); cellTotal2PaisBlue20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisBlue20c = rowTotal2Pais.createCell(indexCol + 6); cellTotal2PaisBlue20c.setCellValue(""); cellTotal2PaisBlue20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisGreen20g = rowTotal2Pais.createCell(indexCol + 7); cellTotal2PaisGreen20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisGreen20g.setCellFormula(formGreen20Total2Paisg); cellTotal2PaisGreen20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisGreen20c = rowTotal2Pais.createCell(indexCol + 8); cellTotal2PaisGreen20c.setCellValue(""); cellTotal2PaisGreen20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisTres20g = rowTotal2Pais.createCell(indexCol + 9); cellTotal2PaisTres20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisTres20g.setCellFormula(formTres20Total2Paisg); cellTotal2PaisTres20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisTres20c = rowTotal2Pais.createCell(indexCol + 10); cellTotal2PaisTres20c.setCellValue(""); cellTotal2PaisTres20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisDuo20g = rowTotal2Pais.createCell(indexCol + 11); cellTotal2PaisDuo20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisDuo20g.setCellFormula(formDuo20Total2Paisg); cellTotal2PaisDuo20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisDuo20c = rowTotal2Pais.createCell(indexCol + 12); cellTotal2PaisDuo20c.setCellValue(""); cellTotal2PaisDuo20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisRed10g = rowTotal2Pais.createCell(indexCol + 13); cellTotal2PaisRed10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisRed10g.setCellFormula(formRed10Total2Paisg); cellTotal2PaisRed10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisRed10c = rowTotal2Pais.createCell(indexCol + 14); cellTotal2PaisRed10c.setCellValue(""); cellTotal2PaisRed10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisBlue10g = rowTotal2Pais.createCell(indexCol + 15); cellTotal2PaisBlue10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisBlue10g.setCellFormula(formBlue10Total2Paisg); cellTotal2PaisBlue10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisBlue10c = rowTotal2Pais.createCell(indexCol + 16); cellTotal2PaisBlue10c.setCellValue(""); cellTotal2PaisBlue10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisGreen10g = rowTotal2Pais.createCell(indexCol + 17); cellTotal2PaisGreen10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisGreen10g.setCellFormula(formGreen10Total2Paisg); cellTotal2PaisGreen10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisGreen10c = rowTotal2Pais.createCell(indexCol + 18); cellTotal2PaisGreen10c.setCellValue(""); cellTotal2PaisGreen10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisTres10g = rowTotal2Pais.createCell(indexCol + 19); cellTotal2PaisTres10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisTres10g.setCellFormula(formTres10Total2Paisg); cellTotal2PaisTres10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisTres10c = rowTotal2Pais.createCell(indexCol + 20); cellTotal2PaisTres10c.setCellValue(""); cellTotal2PaisTres10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisDuo10g = rowTotal2Pais.createCell(indexCol + 21); cellTotal2PaisDuo10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisDuo10g.setCellFormula(formDuo10Total2Paisg); cellTotal2PaisDuo10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisDuo10c = rowTotal2Pais.createCell(indexCol + 22); cellTotal2PaisDuo10c.setCellValue(""); cellTotal2PaisDuo10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisSM20g = rowTotal2Pais.createCell(indexCol + 23); cellTotal2PaisSM20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisSM20g.setCellFormula(formSM20Total2Paisg); cellTotal2PaisSM20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisSM20c = rowTotal2Pais.createCell(indexCol + 24); cellTotal2PaisSM20c.setCellValue(""); cellTotal2PaisSM20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisSM10g = rowTotal2Pais.createCell(indexCol + 25); cellTotal2PaisSM10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisSM10g.setCellFormula(formSM10Total2Paisg); cellTotal2PaisSM10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisSM10c = rowTotal2Pais.createCell(indexCol + 26); cellTotal2PaisSM10c.setCellValue(""); cellTotal2PaisSM10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisSMSoftg = rowTotal2Pais.createCell(indexCol + 27); cellTotal2PaisSMSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisSMSoftg.setCellFormula(formSMSoftTotal2Paisg); cellTotal2PaisSMSoftg.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisSMSoftc = rowTotal2Pais.createCell(indexCol + 28); cellTotal2PaisSMSoftc.setCellValue(""); cellTotal2PaisSMSoftc.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisKY20g = rowTotal2Pais.createCell(indexCol + 29); cellTotal2PaisKY20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisKY20g.setCellFormula(formKY20Total2Paisg); cellTotal2PaisKY20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisKY20c = rowTotal2Pais.createCell(indexCol + 30); cellTotal2PaisKY20c.setCellValue(""); cellTotal2PaisKY20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisKY10g = rowTotal2Pais.createCell(indexCol + 31); cellTotal2PaisKY10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisKY10g.setCellFormula(formKY10Total2Paisg); cellTotal2PaisKY10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisKY10c = rowTotal2Pais.createCell(indexCol + 32); cellTotal2PaisKY10c.setCellValue(""); cellTotal2PaisKY10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisKYSoftg = rowTotal2Pais.createCell(indexCol + 33); cellTotal2PaisKYSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisKYSoftg.setCellFormula(formKYSoftTotal2Paisg); cellTotal2PaisKYSoftg.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisKYSoftc = rowTotal2Pais.createCell(indexCol + 34); cellTotal2PaisKYSoftc.setCellValue(""); cellTotal2PaisKYSoftc.setCellStyle(styleCantidadAzul); HSSFCell cellTotalPais2caja = rowTotal2Pais.createCell(indexCol + 37); cellTotalPais2caja.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotalPais2caja.setCellFormula("(INT(" + CellReference.convertNumToColString(indexCol + 35) + (rowTotalPais.getRowNum() + 1) + "/50))"); cellTotalPais2caja.setCellStyle(styleCantidadNegrita); //GeneraTotal3Pais int indexTotal3Pais = ++indexRow; HSSFRow rowTotal3Pais = sheet.createRow((indexTotal3Pais)); HSSFCell cellTotal3Pais = rowTotal3Pais.createCell(indexCol); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 1) + "$" + indexRow)); cellTotal3Pais.setCellValue("Part. De Emb / Pas"); cellTotal3Pais.setCellStyle(styleTerritorioTotal3); HSSFCell cellTotal3Pais1 = rowTotal3Pais.createCell(indexCol + 1); cellTotal3Pais1.setCellStyle(styleTerritorioTotal3); HSSFCell cellTotal3PaisBoletas = rowTotal3Pais.createCell(indexCol + 2); cellTotal3PaisBoletas.setCellValue(""); cellTotal3PaisBoletas.setCellStyle(styleTotal3); HSSFCell cellTotal3Red20g = rowTotal3Pais.createCell(indexCol + 3); cellTotal3Red20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Red20g.setCellFormula(CellReference.convertNumToColString(indexCol + 3) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3Red20g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisRed20c = rowTotal3Pais.createCell(indexCol + 4); cellTotal3PaisRed20c.setCellValue(""); cellTotal3PaisRed20c.setCellStyle(styleTotal3); HSSFCell cellTotal3Blue20g = rowTotal3Pais.createCell(indexCol + 5); cellTotal3Blue20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Blue20g.setCellFormula(CellReference.convertNumToColString(indexCol + 5) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3Blue20g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisBlue20c = rowTotal3Pais.createCell(indexCol + 6); cellTotal3PaisBlue20c.setCellValue(""); cellTotal3PaisBlue20c.setCellStyle(styleTotal3); HSSFCell cellTotal3Green20g = rowTotal3Pais.createCell(indexCol + 7); cellTotal3Green20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Green20g.setCellFormula(CellReference.convertNumToColString(indexCol + 7) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3Green20g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisGreen20c = rowTotal3Pais.createCell(indexCol + 8); cellTotal3PaisGreen20c.setCellValue(""); cellTotal3PaisGreen20c.setCellStyle(styleTotal3); HSSFCell cellTotal3Tres20g = rowTotal3Pais.createCell(indexCol + 9); cellTotal3Tres20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Tres20g.setCellFormula(CellReference.convertNumToColString(indexCol + 9) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3Tres20g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisTres20c = rowTotal3Pais.createCell(indexCol + 10); cellTotal3PaisTres20c.setCellValue(""); cellTotal3PaisTres20c.setCellStyle(styleTotal3); HSSFCell cellTotal3Duo20g = rowTotal3Pais.createCell(indexCol + 11); cellTotal3Duo20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Duo20g.setCellFormula(CellReference.convertNumToColString(indexCol + 11) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3Duo20g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisDuo20c = rowTotal3Pais.createCell(indexCol + 12); cellTotal3PaisDuo20c.setCellValue(""); cellTotal3PaisDuo20c.setCellStyle(styleTotal3); HSSFCell cellTotal3Red10g = rowTotal3Pais.createCell(indexCol + 13); cellTotal3Red10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Red10g.setCellFormula(CellReference.convertNumToColString(indexCol + 13) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3Red10g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisRed10c = rowTotal3Pais.createCell(indexCol + 14); cellTotal3PaisRed10c.setCellValue(""); cellTotal3PaisRed10c.setCellStyle(styleTotal3); HSSFCell cellTotal3Blue10g = rowTotal3Pais.createCell(indexCol + 15); cellTotal3Blue10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Blue10g.setCellFormula(CellReference.convertNumToColString(indexCol + 15) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3Blue10g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisBlue10c = rowTotal3Pais.createCell(indexCol + 16); cellTotal3PaisBlue10c.setCellValue(""); cellTotal3PaisBlue10c.setCellStyle(styleTotal3); HSSFCell cellTotal3Green10g = rowTotal3Pais.createCell(indexCol + 17); cellTotal3Green10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Green10g.setCellFormula(CellReference.convertNumToColString(indexCol + 17) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3Green10g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisGreen10c = rowTotal3Pais.createCell(indexCol + 18); cellTotal3PaisGreen10c.setCellValue(""); cellTotal3PaisGreen10c.setCellStyle(styleTotal3); HSSFCell cellTotal3Tres10g = rowTotal3Pais.createCell(indexCol + 19); cellTotal3Tres10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Tres10g.setCellFormula(CellReference.convertNumToColString(indexCol + 19) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3Tres10g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisTres10c = rowTotal3Pais.createCell(indexCol + 20); cellTotal3PaisTres10c.setCellValue(""); cellTotal3PaisTres10c.setCellStyle(styleTotal3); HSSFCell cellTotal3Duo10g = rowTotal3Pais.createCell(indexCol + 21); cellTotal3Duo10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Duo10g.setCellFormula(CellReference.convertNumToColString(indexCol + 21) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3Duo10g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisDuo10c = rowTotal3Pais.createCell(indexCol + 22); cellTotal3PaisDuo10c.setCellValue(""); cellTotal3PaisDuo10c.setCellStyle(styleTotal3); HSSFCell cellTotal3SM20g = rowTotal3Pais.createCell(indexCol + 23); cellTotal3SM20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3SM20g.setCellFormula(CellReference.convertNumToColString(indexCol + 23) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3SM20g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisSM20c = rowTotal3Pais.createCell(indexCol + 24); cellTotal3PaisSM20c.setCellValue(""); cellTotal3PaisSM20c.setCellStyle(styleTotal3); HSSFCell cellTotal3SM10g = rowTotal3Pais.createCell(indexCol + 25); cellTotal3SM10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3SM10g.setCellFormula(CellReference.convertNumToColString(indexCol + 25) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3SM10g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisSM10c = rowTotal3Pais.createCell(indexCol + 26); cellTotal3PaisSM10c.setCellValue(""); cellTotal3PaisSM10c.setCellStyle(styleTotal3); HSSFCell cellTotal3SMSoftg = rowTotal3Pais.createCell(indexCol + 27); cellTotal3SMSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3SMSoftg.setCellFormula(CellReference.convertNumToColString(indexCol + 27) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3SMSoftg.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisSMSoftc = rowTotal3Pais.createCell(indexCol + 28); cellTotal3PaisSMSoftc.setCellValue(""); cellTotal3PaisSMSoftc.setCellStyle(styleTotal3); HSSFCell cellTotal3KY20g = rowTotal3Pais.createCell(indexCol + 29); cellTotal3KY20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3KY20g.setCellFormula(CellReference.convertNumToColString(indexCol + 29) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3KY20g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisKY20c = rowTotal3Pais.createCell(indexCol + 30); cellTotal3PaisKY20c.setCellValue(""); cellTotal3PaisKY20c.setCellStyle(styleTotal3); HSSFCell cellTotal3KY10g = rowTotal3Pais.createCell(indexCol + 31); cellTotal3KY10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3KY10g.setCellFormula(CellReference.convertNumToColString(indexCol + 31) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3KY10g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisKY10c = rowTotal3Pais.createCell(indexCol + 32); cellTotal3PaisKY10c.setCellValue(""); cellTotal3PaisKY10c.setCellStyle(styleTotal3); HSSFCell cellTotal3KYSoftg = rowTotal3Pais.createCell(indexCol + 33); cellTotal3KYSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3KYSoftg.setCellFormula(CellReference.convertNumToColString(indexCol + 33) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3KYSoftg.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisKYSoftc = rowTotal3Pais.createCell(indexCol + 32); cellTotal3PaisKYSoftc.setCellValue(""); cellTotal3PaisKYSoftc.setCellStyle(styleTotal3); }
From source file:com.idega.block.datareport.business.SimpleReportBusinessBean.java
License:Open Source License
public void writeSimpleExcelFile(JRDataSource reportData, String nameOfReport, String filePathAndName, ReportDescription description) throws IOException { if (nameOfReport == null || "".equals(nameOfReport)) { nameOfReport = NAME_OF_REPORT;/*w ww.j a v a 2s . c o m*/ } HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(TextSoap.encodeToValidExcelSheetName(nameOfReport)); int rowIndex = 0; //-- Report Name --// // Create a row and put some cells in it. Rows are 0 based. HSSFRow row = sheet.createRow((short) rowIndex++); // Create a cell and put a value in it. HSSFCell cell = row.createCell((short) 0); // Create a new font and alter it. HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 24); font.setFontName(REPORT_FONT); font.setItalic(true); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // Fonts are set into a style so create a new one to use. HSSFCellStyle style = wb.createCellStyle(); style.setFont(font); // Create a cell and put a value in it. cell.setCellValue(nameOfReport); cell.setCellStyle(style); //-- Report Parameters --// rowIndex++; HSSFRow row1 = null; String parameterString = ""; List labels = description.getListOfHeaderParameterLabelKeys(); List parameters = description.getListOfHeaderParameterKeys(); Iterator labelIter = labels.iterator(); Iterator parameterIter = parameters.iterator(); boolean newLineForeEachParameter = description.doCreateNewLineForEachParameter(); while (labelIter.hasNext() && parameterIter.hasNext()) { String label = description.getParameterOrLabelName((String) labelIter.next()); String parameter = description.getParameterOrLabelName((String) parameterIter.next()); if (newLineForeEachParameter) { row1 = sheet.createRow((short) rowIndex++); row1.createCell((short) 0).setCellValue(label + " " + parameter); } else { parameterString += label + " " + parameter + " "; } } if (!newLineForeEachParameter) { row1 = sheet.createRow((short) rowIndex++); row1.createCell((short) 0).setCellValue(parameterString); } rowIndex++; //-- Report ColumnHeader --// List fields = description.getListOfFields(); HSSFRow headerRow = sheet.createRow((short) rowIndex++); HSSFCellStyle headerCellStyle = wb.createCellStyle(); headerCellStyle.setWrapText(true); headerCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); headerCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); HSSFFont headerCellFont = wb.createFont(); //headerCellFont.setFontHeightInPoints((short)12); headerCellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerCellStyle.setFont(headerCellFont); int colIndex = 0; int columnWithUnit = 256; // the unit is 1/256 of a character int numberOfCharactersPerLineInLongTextFields = 60; int numberOfCharactersPerLineInRatherLongTextFields = 35; int numberOfCharactersPerLineInUndifinedTextFields = 20; for (Iterator iter = fields.iterator(); iter.hasNext(); colIndex++) { ReportableField field = (ReportableField) iter.next(); HSSFCell headerCell = headerRow.createCell((short) colIndex); headerCell.setCellValue(description.getColumnName(field)); headerCell.setCellStyle(headerCellStyle); //column width int fieldsMaxChar = field.getMaxNumberOfCharacters(); int colWith = numberOfCharactersPerLineInRatherLongTextFields * columnWithUnit; //default, can be rather long text if (fieldsMaxChar > 0 && fieldsMaxChar < numberOfCharactersPerLineInRatherLongTextFields) { colWith = (fieldsMaxChar + 1) * columnWithUnit; // short fields } else if (fieldsMaxChar > 500) { // when the field is set to be able to contain very long text colWith = numberOfCharactersPerLineInLongTextFields * columnWithUnit; //can be very long text } else if (fieldsMaxChar < 0) { colWith = numberOfCharactersPerLineInUndifinedTextFields * columnWithUnit; } sheet.setColumnWidth((short) colIndex, (short) colWith); } //-- Report ColumnDetail --// try { HSSFCellStyle dataCellStyle = wb.createCellStyle(); dataCellStyle.setWrapText(true); dataCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); sheet.createFreezePane(0, rowIndex); while (reportData.next()) { HSSFRow dataRow = sheet.createRow((short) rowIndex++); colIndex = 0; for (Iterator iter = fields.iterator(); iter.hasNext(); colIndex++) { ReportableField field = (ReportableField) iter.next(); HSSFCell dataCell = dataRow.createCell((short) colIndex); Object fieldValue = reportData.getFieldValue(field); if (fieldValue != null) { dataCell.setCellValue(String.valueOf(fieldValue)); } dataCell.setCellStyle(dataCellStyle); } } } catch (JRException e) { //-- Exception fetching data --// HSSFRow exceptionRow = sheet.createRow((short) rowIndex++); HSSFCell exceptionCell = exceptionRow.createCell((short) 0); // Create a new font and alter it. HSSFFont exceptionFont = wb.createFont(); exceptionFont.setFontName(REPORT_FONT); exceptionFont.setItalic(true); // Fonts are set into a style so create a new one to use. HSSFCellStyle exceptionStyle = wb.createCellStyle(); exceptionStyle.setFont(exceptionFont); // Create a cell and put a value in it. exceptionCell.setCellValue("Error occurred while getting data. Check log for more details."); exceptionCell.setCellStyle(exceptionStyle); e.printStackTrace(); } // Write the output to a file FileOutputStream fileOut = new FileOutputStream(filePathAndName); wb.write(fileOut); fileOut.close(); }
From source file:com.inkubator.common.util.NewMain.java
/** * @param args the command line arguments */// w ww . j a v a 2 s. c o m public static void main(String[] args) throws IOException { File file1 = new File( "C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\JSON_Ek\\Surabaya\\Page1.txt"); File file2 = new File( "C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\JSON_Ek\\Surabaya\\Page2.txt"); // File file3 = new File("C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\json\\json\\menado\\page3.txt"); File file3 = new File( "C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\JSON_Ek\\Surabaya\\Page3.txt"); File file4 = new File( "C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\JSON_Ek\\Surabaya\\Page4.txt"); File file5 = new File( "C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\JSON_Ek\\Surabaya\\Page5.txt"); File file6 = new File( "C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\JSON_Ek\\Surabaya\\Page6.txt"); // File file7 = new File("C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\Bandung\\Bandung\\Page 7.txt"); // File file8 = new File("C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\Bandung\\Bandung\\Page 8.txt"); // File file9 = new File("C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\Bandung\\Bandung\\Page 9.txt"); // File file10 = new File("C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\Bandung\\Bandung\\Page 10.txt"); // File file11 = new File("C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\Bandung\\Bandung\\Page 11.txt"); // File file12 = new File("C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\Bandung\\Bandung\\Page 12.txt"); // File file13 = new File("C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\Bandung\\Bandung\\Page 13.txt"); // File file14 = new File("C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\Bandung\\Bandung\\Page 14.txt"); // File file15 = new File("C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\Bandung\\Bandung\\Page 15.txt"); // File file16 = new File("C:\\Users\\deni.fahri\\Downloads\\page16.txt"); // File file2 = new File("C:\\Users\\deni.fahri\\Documents\\hasil.txt"); String agoda = FilesUtil.getAsStringFromFile(file1); String agoda1 = FilesUtil.getAsStringFromFile(file2); String agoda2 = FilesUtil.getAsStringFromFile(file3); String agoda3 = FilesUtil.getAsStringFromFile(file4); String agoda4 = FilesUtil.getAsStringFromFile(file5); String agoda5 = FilesUtil.getAsStringFromFile(file6); // String agoda6 = FilesUtil.getAsStringFromFile(file7); // String agoda7 = FilesUtil.getAsStringFromFile(file8); // String agoda8 = FilesUtil.getAsStringFromFile(file9); // String agoda9 = FilesUtil.getAsStringFromFile(file10); // String agoda10 = FilesUtil.getAsStringFromFile(file11); // String agoda11 = FilesUtil.getAsStringFromFile(file12); // String agoda12 = FilesUtil.getAsStringFromFile(file13); // String agoda13 = FilesUtil.getAsStringFromFile(file14); // String agoda14 = FilesUtil.getAsStringFromFile(file15); // String agoda15 = FilesUtil.getAsStringFromFile(file16); //// System.out.println(" Test Nya adalah :" + agoda); //// String a=StringUtils.substringAfter("\"HotelTranslatedName\":", agoda); //// System.out.println(" hasil; "+a); //// // TODO code application logic here //// System.out.println("Nilai " + JsonConverter.getValueByKeyStatic(agoda, "HotelTranslatedName")); TypeToken<List<HotelModel>> token = new TypeToken<List<HotelModel>>() { }; Gson gson = new GsonBuilder().create(); // List<HotelModel> data = new ArrayList<>(); // HotelModel hotelModel = new HotelModel(); // hotelModel.setAddress("sdfsdffsfsdfsdfdsfdsf"); // hotelModel.setAccommodationName("Aku"); // HotelModel hotelModel1 = new HotelModel(); // hotelModel1.setAddress("sdfsdffsfsdfsdfdsfdsf"); // hotelModel1.setAccommodationName("Avvvku"); // HotelModel hotelModel2 = new HotelModel(); // hotelModel2.setAddress("sdfsdffsfsdfsdfdsfdsf"); // hotelModel2.setAccommodationName("Akvvvu"); // data.add(hotelModel); // data.add(hotelModel1); // data.add(hotelModel2); // String json = gson.toJson(data); List<HotelModel> total = new ArrayList<>(); List<HotelModel> data1 = new ArrayList<>(); List<HotelModel> data2 = new ArrayList<>(); List<HotelModel> data3 = new ArrayList<>(); List<HotelModel> data4 = new ArrayList<>(); List<HotelModel> data5 = new ArrayList<>(); List<HotelModel> data6 = new ArrayList<>(); List<HotelModel> data7 = new ArrayList<>(); List<HotelModel> data8 = new ArrayList<>(); List<HotelModel> data9 = new ArrayList<>(); List<HotelModel> data10 = new ArrayList<>(); List<HotelModel> data11 = new ArrayList<>(); List<HotelModel> data12 = new ArrayList<>(); List<HotelModel> data13 = new ArrayList<>(); List<HotelModel> data14 = new ArrayList<>(); List<HotelModel> data15 = new ArrayList<>(); List<HotelModel> data16 = new ArrayList<>(); data1 = gson.fromJson(agoda, token.getType()); data2 = gson.fromJson(agoda1, token.getType()); data3 = gson.fromJson(agoda2, token.getType()); data4 = gson.fromJson(agoda3, token.getType()); data5 = gson.fromJson(agoda4, token.getType()); data6 = gson.fromJson(agoda5, token.getType()); // data7 = gson.fromJson(agoda6, token.getType()); // data8 = gson.fromJson(agoda7, token.getType()); // data9 = gson.fromJson(agoda8, token.getType()); // data10 = gson.fromJson(agoda9, token.getType()); // data11 = gson.fromJson(agoda10, token.getType()); // data12 = gson.fromJson(agoda11, token.getType()); // data13 = gson.fromJson(agoda12, token.getType()); // data14 = gson.fromJson(agoda13, token.getType()); // data15 = gson.fromJson(agoda14, token.getType()); // data16 = gson.fromJson(agoda15, token.getType()); total.addAll(data1); total.addAll(data2); total.addAll(data3); total.addAll(data4); total.addAll(data5); total.addAll(data6); // total.addAll(data7); // total.addAll(data8); // total.addAll(data9); // total.addAll(data10); // total.addAll(data11); // total.addAll(data12); // total.addAll(data13); // total.addAll(data14); // total.addAll(data15); // total.addAll(data16); System.out.println(" Ukurannn nya " + total.size()); // System.out.println(" Ukurannya " + data2.size()); for (HotelModel mode : total) { System.out.println(mode); } // HotelModel hotelModel = gson.fromJson(agoda, HotelModel.class); // String Data = hotelModel.getHotelTranslatedName() + ";" + hotelModel.getStarRating() + ";" + hotelModel.getAddress() + ";" + hotelModel.getIsFreeWifi(); // FilesUtil.writeToFileFromString(file2, Data); // System.out.println(hotelModel); // HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Agoda Data Hotel Surabaya"); //// TreeMap<String, Object[]> datatoExel = new TreeMap<>(); int i = 1; // datatoExel.put("1", new Object[]{"Hotel Agoda Jakarta"}); datatoExel.put("1", new Object[] { "Nama Hotel", "Arena", "Alamat", "Rating", "Apakah Gratis Wifi", "Harga Mulai Dari", "Longitude", "Latitude" }); for (HotelModel mode : total) { datatoExel.put(String.valueOf(i + 1), new Object[] { mode.getHotelTranslatedName(), mode.getAreaName(), mode.getAddress(), mode.getStarRating(), mode.getIsFreeWifi(), mode.getTextPrice() + " " + mode.getCurrencyCode(), mode.getCoordinate().getLongitude(), mode.getCoordinate().getLatitude() }); i++; } // //// int i=1; //// for (HotelModel mode : data2) { //// datatoExel.put(String.valueOf(i), new Object[]{1d, "John", 1500000d}); ////// } //// //// datatoExel.put("4", new Object[]{3d, "Dean", 700000d}); //// Set<String> keyset = datatoExel.keySet(); int rownum = 0; for (String key : keyset) { Row row = sheet.createRow(rownum++); Object[] objArr = datatoExel.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("C:\\Users\\deni.fahri\\Documents\\Surabaya.xls")); workbook.write(out); out.close(); System.out.println("Excel written successfully.."); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }