column wise total in excel sheet

[rocky4everurs]

how to calculate columnwise total in excel sheet in java using HSSFworkbook

[masijade]

Uhm, read each cell in the column and total them?

[rocky4everurs]

Hi.
Actually i am generating the excel report for an employee total efforts spent in an organistaion.
so for that i need to cal row wise total and column wise total. i calculated row wise total and i need the help in calculating column wise total.
Can u pls figure it out. Thanx.

[peter_budo]

You can always use formulas (setCellFormula) to help you with it...

[masijade]

Hi.
Actually i am generating the excel report for an employee total efforts spent in an organistaion.
so for that i need to cal row wise total and column wise total. i calculated row wise total and i need the help in calculating column wise total.
Can u pls figure it out. Thanx.

Uhm, so transpose that logic?

[rocky4everurs]

hi this is the code i had written:

repFmtImplInst.setCellDataStyle();

for(int i=0; i<tsInfoArr.size(); i++)
{
	cal.setTime(dtUtil.strToUtilDate(startdate));
	tsInfoInst = (TimeSheetInfoBean)tsInfoArr.get(i);
	effortCal.setTime(dtUtil.strToUtilDate(tsInfoInst.getEffortDate()));
	reqInst = tsInfoInst.getReqInst();

	if(prevReqId!=reqInst.getId())
	{
		rowIndex = rowIndex +1;
		grandTotal+=rowTotEff;
		rowTotEff=0.00;
		sNo+=1;

		colIndex = (short)(stColIndex);
		repFmtImplInst.setCellDataStyle();
		repFmtImplInst.setMonRprtCellData(rowIndex,colIndex,String.valueOf(sNo));

		colIndex = (short)(colIndex + sInc);
		repFmtImplInst.setCellDataStyle();
		repFmtImplInst.setMonRprtCellData(rowIndex,colIndex,reqInst.getName());

		colIndex = (short)(colIndex + sInc);
		repFmtImplInst.setCellDataStyle();
		if( tsInfoInst.getChargeNo() != null )
		{
			repFmtImplInst.setMonRprtCellData(rowIndex,colIndex,tsInfoInst.getChargeNo()+"");
		}
		else
		{
			repFmtImplInst.setMonRprtCellData(rowIndex,colIndex,"");
		}

		colIndex = (short)(colIndex + sInc);
		repFmtImplInst.setCellDataStyle();
		repFmtImplInst.setMonRprtCellData(rowIndex,colIndex,reqInst.getStartDate());

		colIndex = (short)(colIndex + sInc);
		repFmtImplInst.setCellDataStyle();
		repFmtImplInst.setMonRprtCellData(rowIndex,colIndex,reqInst.getEndDate());

		colIndex = (short)(colIndex + sInc);
		repFmtImplInst.setCellDataStyle();
		repFmtImplInst.setMonRprtCellDblValue(rowIndex,colIndex,reqInst.getEstHrs());
		colIndex = (short)(colIndex + sInc);
	}
	else
	{
		rowIndex = rowIndex;
		colIndex = (short)(stColIndex + 6);
	}

	a=0;
	effortDate=effortCal.get(Calendar.DATE);

	for(int outerloop=0;outerloop<MonthEndDate;outerloop++)
	{
		cal.add(Calendar.DAY_OF_WEEK,a);

		if(cal.get(Calendar.DAY_OF_WEEK)>=8)
		{
			cal.add(Calendar.DAY_OF_WEEK,-(cal.get(Calendar.DAY_OF_WEEK)));
		}

		if(cal.get(Calendar.DAY_OF_WEEK)==1 || cal.get(Calendar.DAY_OF_WEEK)==7)
		{
			if(effortDate ==(outerloop+1))
			{
				repFmtImplInst.setColHeaderCellStyle(HSSFColor.TURQUOISE.index);
				repFmtImplInst.setMonRprtCellDblValue(rowIndex,colIndex,tsInfoInst.getEffortHours());
				rowTotEff += tsInfoInst.getEffortHours();

				colEff1 = tsInfoInst.getEffortHours();
				System.out.println("colEff1111================"+colEff1);

				weekEndEff++;
			}
			else
			{
				repFmtImplInst.setColHeaderCellStyle(HSSFColor.TURQUOISE.index);
				repFmtImplInst.setMonRprtCellDblValue(rowIndex,colIndex,0.00);
			}
		}
		else
		{
			if(effortDate==(outerloop+1))
			{
				repFmtImplInst.setCellDataStyle();
				repFmtImplInst.setColHeader(rowIndex,colIndex,String.valueOf(tsInfoInst.getEffortHours()));
				rowTotEff += tsInfoInst.getEffortHours();

				colEff2 = tsInfoInst.getEffortHours();
				System.out.println("colEff2================"+colEff2);

				for(int k=0; k<holInfoArr.size(); k++)
				{
					holInfoInst = (HolidayInfoBean)holInfoArr.get(k);
					holCal.setTime(dtUtil.strToUtilDate(holInfoInst.getStartDate()));
					holStDate=holCal.get(Calendar.DATE);
					holCal.setTime(dtUtil.strToUtilDate(holInfoInst.getEndDate()));
					holEndDate=holCal.get(Calendar.DATE);
					for(int hol=holStDate;hol<=holEndDate;hol++)
					{
						if(hol==(outerloop+1))
						{
							effOnHol++;							
						}
					}
				}
			}
			else
			{
				repFmtImplInst.setCellDataStyle();
				repFmtImplInst.setMonRprtCellDblValue(rowIndex,colIndex,0.00);
			}
			//colEff2=0.00;
		}
		a=1;
		colIndex = (short)(colIndex + sInc);
	}

	repFmtImplInst.setCellDataStyle();
	repFmtImplInst.setMonRprtCellDblValue(rowIndex,colIndex,rowTotEff);

	prevReqId=reqInst.getId();

	colTotEff = colEff1 + colEff2;
	System.out.println("colTotEff======"+colTotEff);
}
grandTotal+=rowTotEff;

can u figure it out. where i have to calculate the column wise total.....

[masijade]

Take this

given the data

int[][] data = new int[][] {
    { 1, 2, 3 },
    { 4, 5, 6 },
    { 7, 8, 9 } };

You would do the row/col totals as follows:

int[] colTotals = new int[data.length];
		for (int i = 0; i < data.length; i++) {
			int rowTotal = 0;
			for (int j = 0; j < data[i].length; j++) {
				System.out.print(" " + data[i][j] + " ");
				rowTotal += data[i][j];
				colTotals[j] += data[i][j];
			}
			System.out.println(rowTotal);
		}
		for (int i = 0; i < colTotals.length; i++) {
			System.out.print(colTotals[i] + " ");
		}

Now modify that principle to use with POI, which is exactly the same, simply replacing i/j with the row/column index and of course cell methods instead of array access. Now, that is much more than I usually give as code, so the rest you are going to have to do.

[rocky4everurs]

Thanx for the help. But till now i was unable to get the total...
since my efforts day is not a dimensional array...can u suggest me the other way in order to i was able to calculate.

[masijade]

What is a spreadsheet except a glorified 2-dimensional array. Sorry, but I can help no further.