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

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

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

## All 8 Replies

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

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.

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?

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++)
{

if(cal.get(Calendar.DAY_OF_WEEK)>=8)
{
}

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

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

weekEndEff++;
}
else
{
repFmtImplInst.setMonRprtCellDblValue(rowIndex,colIndex,0.00);
}
}
else
{
if(effortDate==(outerloop+1))
{
repFmtImplInst.setCellDataStyle();
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.....

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.

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.

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.