Showing posts with label excel. Show all posts
Showing posts with label excel. Show all posts

How can i generate Excel (.XLS) file in Apache ofbiz by Using Apache POI

For that i have writtten Java Event in Apache ofbiz. The complete code and Step i have mentioned below

1. Enter below code in Controller.xml 

 <request-map uri="paymentRecieptReport.xls">
         <security https="true" auth="true"/>
    <event type="java" path="org.ofbiz.accounting.reports.PaymentRecieptReport" invoke="createPayementRecieptReport" />
         <response name="success" type="view" value="paymentRecieptReport"/>
         <response name="error" type="view" value="paymentRecieptReport"/>
    </request-map>

    <view-map name="paymentRecieptReport" type="screen" page="component://accounting/widget/CommonScreens.xml#paymentRecieptReport"/>


2. Write below java code for generate .XLS file in Java event code

package org.ofbiz.accounting.reports;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javolution.util.FastList;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.ofbiz.base.util.UtilMisc;
import org.ofbiz.entity.GenericDelegator;
import org.ofbiz.entity.GenericValue;
import org.ofbiz.entity.condition.EntityCondition;
import org.ofbiz.entity.condition.EntityConditionList;
import org.ofbiz.entity.condition.EntityExpr;
import org.ofbiz.entity.condition.EntityOperator;
import org.ofbiz.entity.util.EntityUtil;
public class PaymentRecieptReport {
	

	public static ArrayList<HashMap> getPaymentRecieptList(GenericDelegator delegator, List<GenericValue>  paymentList)
	{
		ArrayList<HashMap>  finalList=new ArrayList<HashMap>();
		
		
		for(int i=0; i<paymentList.size(); i++)
		{
			String paymentId="";
			String paymentMethodId="";
			HashMap map= new HashMap();
			
			GenericValue payment = paymentList.get(i);
			paymentId=payment.getString("paymentId");
			paymentMethodId=payment.getString("paymentMethodId");
			map.put("paymentId", payment.getString("paymentId"));
			map.put("effectiveDate", payment.getString("effectiveDate"));
			map.put("checkNumber", payment.getString("checkNumber"));
			map.put("checkDate", payment.getString("checkDate"));
			map.put("checkAmount", payment.getString("amount"));
			map.put("modeOfPayment", payment.getString("modeOfPayment"));
			map.put("paymentRefNum", payment.getString("paymentRefNum"));
			
			String partyId="";
			if(payment.getString("outGoingPayment").equals("Y"))
			{
				map.put("partyId", payment.getString("partyIdTo"));
				partyId= payment.getString("partyIdTo");
				map.put("paymentType", "OUTGOING");
			
			}
			else
			{
				map.put("partyId", payment.getString("partyIdFrom"));
				partyId=payment.getString("partyIdFrom");
				map.put("paymentType", "INCOMING");
			}
			try
			{
		List<GenericValue>  partyList=delegator.findList("PartyNameView", EntityCondition.makeCondition("partyId", EntityOperator.EQUALS, partyId), null, null, null, false);
		
		if(partyList.size()>0)
		{
			GenericValue party=EntityUtil.getFirst(partyList);
			map.put("partyName", party.getString("groupName"));
			
			if(party.getString("groupName") ==null || party.getString("groupName")=="" )
			{
				try
				{
				List<GenericValue>  personList=delegator.findList("Person", EntityCondition.makeCondition("partyId", EntityOperator.EQUALS, partyId), null, null, null, false);
				if(personList.size()>0)
				{
					GenericValue person=EntityUtil.getFirst(personList);
					map.put("partyName", person.getString("firstName")+" "+person.getString("lastName"));
				}
				}
				catch(Exception ex)
				{
					
				}
			}
		}
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
				
			}
			
			
			try
			{
		List<GenericValue>  tdsList=delegator.findList("TdsHistory", EntityCondition.makeCondition("paymentId", EntityOperator.EQUALS, paymentId), null, null, null, false);
		if(tdsList.size()>0)
		{
			GenericValue tds=EntityUtil.getFirst(tdsList);
			map.put("tdsSection", tds.getString("sectionOfAct"));
			map.put("tdsGl", tds.getString("overrideGlAccountId"));
			map.put("tdsAmount", tds.getString("tdsAmount"));
			
		}
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
			}
			
			String glId="";	
			String glName="";
			try
			{
			
			List<GenericValue>  glList=delegator.findList("PaymentMethod", EntityCondition.makeCondition("paymentMethodId", EntityOperator.EQUALS, paymentMethodId), null, null, null, false);
			if(glList.size()>0)
			{
			GenericValue gl=EntityUtil.getFirst(glList);
			glId=gl.getString("glAccountId");
			map.put("glAccountId",glId);
			}
			List<GenericValue>  glNameList=delegator.findList("GlAccount", EntityCondition.makeCondition("glAccountId", EntityOperator.EQUALS, glId), null, null, null, false);
			
			if(glNameList.size()>0)
			{
			GenericValue glNameValue=EntityUtil.getFirst(glNameList);
			glName=glNameValue.getString("accountName");
			map.put("glAccountIdName",glName);
			map.put("accountCode", glNameValue.getString("accountCode"));
			map.put("glDesc", glNameValue.getString("description"));
			}
			}
			catch(Exception ex)
			{
			ex.printStackTrace();	
			}
			
			finalList.add(map);
			
		}
		
		
		
		return finalList;
		
	}
	
	public static String createPayementRecieptReport(HttpServletRequest request,HttpServletResponse response) throws IOException
	{
		GenericDelegator delegator = (GenericDelegator) request.getAttribute("delegator");
		List<GenericValue> paymentList = new FastList<GenericValue>();
		String fromDate=request.getParameter("fromDate");
		String toDate=request.getParameter("toDate");
		System.out.println("Fromdate   : "+fromDate +"  :"+toDate);
		String orgainsationParty=request.getParameter("organizationPartyId");
		List<String> errors = new ArrayList<String>();
		request.setAttribute("_ERROR_MESSAGE_LIST_", errors);
		System.out.println("coming party id "+orgainsationParty);
		if(orgainsationParty ==null || orgainsationParty=="")
		{
			errors.add("Please select organisation id");
			return "success";
		}
		
		
		if(!request.getParameter("fromDate").isEmpty())
		{
			
			if(request.getParameter("toDate").isEmpty())
			{
				errors.add("Please select ToDate");
				return "success";
			}
		}
		
		if(!request.getParameter("toDate").isEmpty())
		{
			
			if(request.getParameter("fromDate").isEmpty())
			{
				errors.add("Please select FromDate");
				return "success";
			}
		}
		
		
	String companyName="";
		try
		{
	 List<GenericValue>  partyList=delegator.findList("PartyNameView", EntityCondition.makeCondition("partyId", EntityOperator.EQUALS, orgainsationParty), null, null, null, false);
	if(partyList.size()>0)
	{
		GenericValue party=EntityUtil.getFirst(partyList);
		companyName= party.getString("groupName");
		
	}
		}
		catch(Exception ex)
		{
		ex.printStackTrace();	
		}
		
		EntityExpr dateRange = null;
		try
		{
		EntityConditionList<EntityExpr> exprs = EntityCondition.makeCondition(UtilMisc.toList(
	    EntityCondition.makeCondition("partyIdFrom", EntityOperator.EQUALS, orgainsationParty),
	    EntityCondition.makeCondition("partyIdTo", EntityOperator.EQUALS, orgainsationParty)), EntityOperator.OR);
		
		 if(!request.getParameter("fromDate").isEmpty() && !request.getParameter("toDate").isEmpty()){
			 fromDate=fromDate+" 23:59:59";
			 toDate=toDate+" 23:59:59";
			  EntityConditionList<EntityExpr> dateRangeForReport = EntityCondition.makeCondition(UtilMisc.toList(
		                EntityCondition.makeCondition("effectiveDate", EntityOperator.GREATER_THAN_EQUAL_TO,fromDate),
		                EntityCondition.makeCondition("effectiveDate", EntityOperator.LESS_THAN_EQUAL_TO, toDate)), EntityOperator.AND);
			  dateRange = EntityCondition.makeCondition(exprs, EntityOperator.AND, dateRangeForReport);
			  paymentList=delegator.findList("Payment", dateRange, null, null, null, false); 
			  }
		 else
		 {
			 paymentList=delegator.findList("Payment", exprs, null, null, null, false);
		 }
	//	paymentList=delegator.findList("Payment", exprs, null, null, null, false);
		}
		
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		ArrayList finalList=new ArrayList();
		finalList=getPaymentRecieptList(delegator,paymentList );
	    System.out.println("size of payment list is   "+paymentList.size());
	    String periodDate="";
		if(!request.getParameter("fromDate").isEmpty() && !request.getParameter("toDate").isEmpty())
		 {
			periodDate= request.getParameter("fromDate").toString()+" to "+request.getParameter("toDate");
		 }
		else
		{
			periodDate ="         to                  ";
		}
 
 ///////////////////////////////*******************************************
		// code to create a fixed excel start
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("Payment Reciept Report");
		HSSFCellStyle style = wb.createCellStyle();
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

		HSSFCellStyle styleHeader = wb.createCellStyle();
		HSSFFont font = wb.createFont();
		String fontName = "Arial";
		font.setFontName(fontName);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontHeight((short) 200);
		styleHeader.setFont(font);
		CellRangeAddress region = new CellRangeAddress(1, 1, 0, 6);
		sheet.addMergedRegion(region);
		CellRangeAddress region1 = new CellRangeAddress(2, 2, 0, 6);
		sheet.addMergedRegion(region1);
		CellRangeAddress region2 = new CellRangeAddress(3, 3, 0, 6);
		sheet.addMergedRegion(region2);
		
		HSSFRow companyheader = sheet.createRow(1);
		HSSFCell companyheaderCell = companyheader.createCell(0);
		HSSFCell companyheaderCell_1 = companyheader.createCell(1);
		companyheaderCell_1.setCellStyle(styleHeader);
		HSSFCell companyheaderCell_2 = companyheader.createCell(2);
		companyheaderCell_2.setCellStyle(styleHeader);
		HSSFCell companyheaderCell_3 = companyheader.createCell(3);
		companyheaderCell_3.setCellStyle(styleHeader);
		HSSFCell companyheaderCell_4 = companyheader.createCell(4);
		companyheaderCell_4.setCellStyle(styleHeader);
		HSSFCell companyheaderCell_5 = companyheader.createCell(5);
		companyheaderCell_5.setCellStyle(styleHeader);
		companyheader.setHeight((short) 600);
		HSSFRichTextString companyheaderCode = new HSSFRichTextString(companyName);
		companyheaderCell.setCellValue(companyheaderCode);
		companyheaderCell.setCellStyle(styleHeader);
		
		
		HSSFRow header = sheet.createRow(2);
		HSSFCell headerCell = header.createCell(0);
		HSSFCell headerCell_1 = header.createCell(1);
		headerCell_1.setCellStyle(styleHeader);
		HSSFCell headerCell_2 = header.createCell(2);
		headerCell_2.setCellStyle(styleHeader);
		HSSFCell headerCell_3 = header.createCell(3);
		headerCell_3.setCellStyle(styleHeader);
		HSSFCell headerCell_4 = header.createCell(4);
		headerCell_4.setCellStyle(styleHeader);
		HSSFCell headerCell_5 = header.createCell(5);
		headerCell_5.setCellStyle(styleHeader);
		header.setHeight((short) 300);
		HSSFRichTextString headerCode = new HSSFRichTextString("Payment Register (Cash & Bank)");
		headerCell.setCellValue(headerCode);
		headerCell.setCellStyle(styleHeader);

		HSSFRow ledger = sheet.createRow(3);
		HSSFCell ledgerCell = ledger.createCell(0);
		HSSFCell ledgerCell_1 = ledger.createCell(1);
		ledgerCell_1.setCellStyle(styleHeader);
		HSSFCell ledgerCell_2 = ledger.createCell(2);
		ledgerCell_2.setCellStyle(styleHeader);
		HSSFCell ledgerCell_3 = ledger.createCell(3);
		ledgerCell_3.setCellStyle(styleHeader);
		HSSFCell ledgerCell_4 = ledger.createCell(4);
		ledgerCell_4.setCellStyle(styleHeader);
		HSSFCell ledgerCell_5 = ledger.createCell(5);
		ledgerCell_5.setCellStyle(styleHeader);

		ledger.setHeight((short) 300);
		HSSFRichTextString ledgerCode = new HSSFRichTextString("Period :"+periodDate);
		ledgerCell.setCellValue(ledgerCode);
		ledgerCell.setCellStyle(styleHeader);

	
		HSSFRow period = sheet.createRow(4);
		HSSFCell periodCell = period.createCell(0);

		HSSFCell periodCell_1 = ledger.createCell(1);
		periodCell_1.setCellStyle(styleHeader);
		HSSFCell periodCell_2 = ledger.createCell(2);
		periodCell_2.setCellStyle(styleHeader);
		HSSFCell periodCell_3 = ledger.createCell(3);
		periodCell_3.setCellStyle(styleHeader);
		HSSFCell periodCell_4 = ledger.createCell(4);
		periodCell_4.setCellStyle(styleHeader);
		HSSFCell periodCell_5 = ledger.createCell(5);
		periodCell_5.setCellStyle(styleHeader);

		/*period.setHeight((short) 300);
		HSSFRichTextString periodString = new HSSFRichTextString("To Date :"+toDate);
		periodCell.setCellValue(periodString);
		periodCell.setCellStyle(styleHeader);
*/
		HSSFCellStyle dataStyleHeader = wb.createCellStyle();
		HSSFFont headerFont = wb.createFont();
		String headerFontName = "Arial";
		headerFont.setFontName(headerFontName);
		headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		headerFont.setFontHeight((short) 200);
		dataStyleHeader.setFont(headerFont);

	
		HSSFRow TableHeader = sheet.createRow(7);
		HSSFCell custCodeCell = TableHeader.createCell(0);
		HSSFRichTextString custCodeString = new HSSFRichTextString("S.NO");
		custCodeCell.setCellValue(custCodeString);
		custCodeCell.setCellStyle(dataStyleHeader);

	    // Payment outgoing/Incoming
		HSSFCell paymentTypeCell = TableHeader.createCell(1);
		HSSFRichTextString docTypeHeaderpaymentTypeCellString = new HSSFRichTextString("Payment Type");
		paymentTypeCell.setCellValue(docTypeHeaderpaymentTypeCellString);
		paymentTypeCell.setCellStyle(dataStyleHeader);
		
		// HSSFRow docTypeHeader = sheet.createRow(12);
		HSSFCell docTypeHeaderCell = TableHeader.createCell(2);
		HSSFRichTextString docTypeHeaderCellString = new HSSFRichTextString("Bank / Cash GL");
		docTypeHeaderCell.setCellValue(docTypeHeaderCellString);
		docTypeHeaderCell.setCellStyle(dataStyleHeader);

		// HSSFRow docNoHeader = sheet.createRow(12);
		HSSFCell docNoHeaderCell = TableHeader.createCell(3);
		HSSFRichTextString docNoHeaderCellString = new HSSFRichTextString("Bank / Cash Name");
		docNoHeaderCell.setCellValue(docNoHeaderCellString);
		docNoHeaderCell.setCellStyle(dataStyleHeader);

		// HSSFRow referenceNoHeader = sheet.createRow(12);
		HSSFCell referenceNoHeaderCell = TableHeader.createCell(4);
		HSSFRichTextString referenceNoHeaderString = new HSSFRichTextString("Ch No.");
		referenceNoHeaderCell.setCellValue(referenceNoHeaderString);
		referenceNoHeaderCell.setCellStyle(dataStyleHeader);

		// HSSFRow referenceDateHeader = sheet.createRow(12);
		HSSFCell referenceDateHeaderCell = TableHeader.createCell(5);
		HSSFRichTextString referenceDateHeaderString = new HSSFRichTextString("Ch. Date");
		referenceDateHeaderCell.setCellValue(referenceDateHeaderString);
		referenceDateHeaderCell.setCellStyle(dataStyleHeader);

		// HSSFRow departmentHeader = sheet.createRow(12);
		HSSFCell departmentHeaderCell = TableHeader.createCell(6);
		HSSFRichTextString departmentHeaderString = new HSSFRichTextString("Party ID");
		departmentHeaderCell.setCellValue(departmentHeaderString);
		departmentHeaderCell.setCellStyle(dataStyleHeader);

		// HSSFRow descriptionHeader = sheet.createRow(12);
		HSSFCell descriptionHeaderCell = TableHeader.createCell(7);
		HSSFRichTextString descriptionHeaderString = new HSSFRichTextString("Party Name");
		descriptionHeaderCell.setCellValue(descriptionHeaderString);
		descriptionHeaderCell.setCellStyle(dataStyleHeader);

		// HSSFRow drHeader = sheet.createRow(12);
		HSSFCell drCell = TableHeader.createCell(8);
		HSSFRichTextString drString = new HSSFRichTextString("GL Code");
		drCell.setCellValue(drString);
		drCell.setCellStyle(dataStyleHeader);

		// HSSFRow crHeader = sheet.createRow(12);
		HSSFCell crCell = TableHeader.createCell(9);
		HSSFRichTextString crString = new HSSFRichTextString("GL Desc");
		crCell.setCellValue(crString);
		crCell.setCellStyle(dataStyleHeader);
		
		HSSFCell tdsCell = TableHeader.createCell(10);
		HSSFRichTextString tdsString = new HSSFRichTextString("TDS Section");
		tdsCell.setCellValue(tdsString);
		tdsCell.setCellStyle(dataStyleHeader);
		
		HSSFCell tdsCell1 = TableHeader.createCell(11);
		HSSFRichTextString tdsString1 = new HSSFRichTextString("TDS GL");
		tdsCell1.setCellValue(tdsString1);
		tdsCell1.setCellStyle(dataStyleHeader);
		HSSFCell tdsCell2 = TableHeader.createCell(12);
		HSSFRichTextString tdsString2 = new HSSFRichTextString("Doc Date");
		tdsCell2.setCellValue(tdsString2);
		tdsCell2.setCellStyle(dataStyleHeader);
		
		HSSFCell tdsCell3 = TableHeader.createCell(13);
		HSSFRichTextString tdsString3 = new HSSFRichTextString("Doc Number");
		tdsCell3.setCellValue(tdsString3);
		tdsCell3.setCellStyle(dataStyleHeader);
		
		HSSFCell tdsCell4 = TableHeader.createCell(14);
		HSSFRichTextString tdsString4 = new HSSFRichTextString("Reference No.");
		tdsCell4.setCellValue(tdsString4);
		tdsCell4.setCellStyle(dataStyleHeader);
		
		
		HSSFCell tdsCell5 = TableHeader.createCell(15);
		HSSFRichTextString tdsString5 = new HSSFRichTextString("Mode of Payment");
		tdsCell5.setCellValue(tdsString5);
		tdsCell5.setCellStyle(dataStyleHeader);
		
		HSSFCell tdsCell6 = TableHeader.createCell(16);
		HSSFRichTextString tdsString6 = new HSSFRichTextString("TDS Amount");
		tdsCell6.setCellValue(tdsString6);
		tdsCell6.setCellStyle(dataStyleHeader);
		
		HSSFCell tdsCell7 = TableHeader.createCell(17);
		HSSFRichTextString tdsString7 = new HSSFRichTextString("Cheque Amount");
		tdsCell7.setCellValue(tdsString7);
		tdsCell7.setCellStyle(dataStyleHeader);
		
		/*HSSFCell tdsCell8 = TableHeader.createCell(17);
		HSSFRichTextString tdsString8 = new HSSFRichTextString("glDesc");
		tdsCell8.setCellValue(tdsString8);
		tdsCell8.setCellStyle(dataStyleHeader);*/
		
		HSSFCell tdsCell9 = TableHeader.createCell(18);
		HSSFRichTextString tdsString9= new HSSFRichTextString("Credit / Return");
		tdsCell9.setCellValue(tdsString9);
		tdsCell9.setCellStyle(dataStyleHeader);
		HSSFCell tdsCell10 = TableHeader.createCell(19);
		HSSFRichTextString tdsString10 = new HSSFRichTextString("Reconcilation Status");
		tdsCell10.setCellValue(tdsString10);
		tdsCell10.setCellStyle(dataStyleHeader);
		//sheet.createFreezePane(6, 8);
        int sNo=1;
        int RowNo=8;	      
        for(int i=0; i<finalList.size(); i++)
        {
        	HashMap map= (HashMap) finalList.get(i);
        	int cell=0;
        	HSSFRow runningHeader = sheet.createRow(RowNo);
        	
  		    HSSFCell sNoCell = runningHeader.createCell(cell);
  		    HSSFRichTextString sNoString=new HSSFRichTextString(Integer.toString(sNo));
  		    sNoCell.setCellValue(sNoString);
  		    cell++;
  		    
  		    
  		    HSSFCell paymentTypeValueCell = runningHeader.createCell(cell);
		    HSSFRichTextString paymentTypeValueString=new HSSFRichTextString((String) map.get("paymentType"));
		    paymentTypeValueCell.setCellValue(paymentTypeValueString);
		    cell++;
        	
  		    HSSFCell glAccountCell = runningHeader.createCell(cell);
		    HSSFRichTextString glAccountString=new HSSFRichTextString((String) map.get("glAccountId"));
		    glAccountCell.setCellValue(glAccountString);
		    cell++;
		    
		    HSSFCell glAccountNameCell = runningHeader.createCell(cell);
		    HSSFRichTextString glAccountNameString=new HSSFRichTextString((String) map.get("glAccountIdName"));
		    glAccountNameCell.setCellValue(glAccountNameString);
		    cell++;
		    
		    HSSFCell chNoCell = runningHeader.createCell(cell);
		    HSSFRichTextString chNoString=new HSSFRichTextString((String) map.get("checkNumber"));
		    chNoCell.setCellValue(chNoString);
		    cell++;
		    
		    HSSFCell chNoDateCell = runningHeader.createCell(cell);
		    HSSFRichTextString chNoDateString=new HSSFRichTextString((String) map.get("checkDate"));
		    chNoDateCell.setCellValue(chNoDateString);
		    cell++;
		    HSSFCell partyIdCell = runningHeader.createCell(cell);
		    HSSFRichTextString partyIdString=new HSSFRichTextString((String) map.get("partyId"));
		    partyIdCell.setCellValue(partyIdString);
		    cell++;
		    
		    HSSFCell partyNameCell = runningHeader.createCell(cell);
		    HSSFRichTextString partyNameString=new HSSFRichTextString((String) map.get("partyName"));
		    partyNameCell.setCellValue(partyNameString);
		    cell++;
		    
		    HSSFCell glCodeCell = runningHeader.createCell(cell);
		    HSSFRichTextString glCodeString=new HSSFRichTextString("");
		    glCodeCell.setCellValue(glCodeString);
		    cell++;
		    
		    HSSFCell glDescCell = runningHeader.createCell(cell);
		    HSSFRichTextString glDescString=new HSSFRichTextString("");
		    glDescCell.setCellValue(glDescString);
		    cell++;
		    
		    HSSFCell tdsSectionCell = runningHeader.createCell(cell);
		    HSSFRichTextString tdsSectionString=new HSSFRichTextString((String) map.get("tdsSection"));
		    tdsSectionCell.setCellValue(tdsSectionString);
		    cell++;
		    
		    
		    HSSFCell tdsGlCell = runningHeader.createCell(cell);
		    HSSFRichTextString tdsGlString=new HSSFRichTextString((String) map.get("tdsGl"));
		    tdsGlCell.setCellValue(tdsGlString);
		    cell++;
		    
		    HSSFCell docDateCell = runningHeader.createCell(cell);
		    HSSFRichTextString docDateString=new HSSFRichTextString((String) map.get("effectiveDate"));
		    docDateCell.setCellValue(docDateString);
		    cell++;
		    
		    HSSFCell docNumberCell = runningHeader.createCell(cell);
		    HSSFRichTextString docNumberString=new HSSFRichTextString((String) map.get("paymentId"));
		    docNumberCell.setCellValue(docNumberString);
		    cell++;
		    
		    HSSFCell refnoCell = runningHeader.createCell(cell);
		    HSSFRichTextString refnoString=new HSSFRichTextString((String) map.get("paymentRefNum"));
		    refnoCell.setCellValue(refnoString);
		    cell++;
		    
		    HSSFCell modePaymentCell = runningHeader.createCell(cell);
		    HSSFRichTextString modePaymentString=new HSSFRichTextString((String) map.get("modeOfPayment"));
		    modePaymentCell.setCellValue(modePaymentString);
		    cell++;
		    
		    HSSFCell tdsAmountCell = runningHeader.createCell(cell);
		    HSSFRichTextString tdsAmountString=new HSSFRichTextString((String) map.get("tdsAmount"));
		    tdsAmountCell.setCellValue(tdsAmountString);
		    cell++;
		    
		    HSSFCell checkAmountCell = runningHeader.createCell(cell);
		    HSSFRichTextString checkAmountString=new HSSFRichTextString((String) map.get("checkAmount"));
		    checkAmountCell.setCellValue(Double.valueOf((String) map.get("checkAmount")));
		    cell++;
		    
		    HSSFCell descCell = runningHeader.createCell(cell);
		    HSSFRichTextString descString=new HSSFRichTextString((String) map.get("desc"));
		    descCell.setCellValue(descString);
		    cell++;
		    
		    HSSFCell creditReturnCell = runningHeader.createCell(cell);
		    HSSFRichTextString creditReturnString=new HSSFRichTextString("");
		    creditReturnCell.setCellValue(creditReturnString);
		    cell++;
		    
		    HSSFCell reconsilationCell = runningHeader.createCell(cell);
		    HSSFRichTextString reconsilationString=new HSSFRichTextString("");
		    reconsilationCell.setCellValue(reconsilationString);
		    cell++;
      	
        	
  		  RowNo++;
  		  sNo++; 
        }
        
		

				String serverPath_1 = System.getProperty("ofbiz.home");
				String serverFilePath = serverPath_1
						+ "/applications/documents/register.xls";

				FileOutputStream fileOut = new FileOutputStream(serverFilePath);
				wb.write(fileOut);
				fileOut.close();
				// Code to save a file in document directory end

				// Code To download a file
				// Map<String, Object> parameters = UtilHttp.getParameterMap(request);
				String FileName = (String) request.getParameter("FileName");
				FileName = "register.xls";

				OutputStream os;
				try {
					os = response.getOutputStream();
					String mimeType = "application/octet-stream";
					response.setContentType(mimeType);
					String serverPath = System.getProperty("ofbiz.home");
					serverPath = serverPath + "/applications/documents/" + FileName;
					File file = new File(serverPath);
					InputStream i = new FileInputStream(file);
					int readBytes = 0;

					while ((readBytes = i.read()) != -1) {
						os.write(readBytes);
					}
					request.setAttribute("_EVENT_MESSAGE_",
							"File Transfered Successfully");

					i.close();

					os.flush();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					// e.printStackTrace();
				}
				// creating a fixed excel end

		
		return "success";
	}

}