package com.proj;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.rh.core.base.Bean;
import com.rh.core.base.Context;
import com.rh.core.serv.CommonServ;
import com.rh.core.serv.OutBean;
import com.rh.core.serv.ParamBean;
/**
* 项目成本统计
* @××× */
public class ProjectCost extends CommonServ {
/**
* 根据所选项目统计成本并导入excel表中
* @param paramBean ParamBean 通过js传入的项目信息
* @return
*/
@SuppressWarnings("deprecation")
public OutBean list (ParamBean paramBean) {
//创建一个工作簿
HSSFWorkbook workBook =new HSSFWorkbook();
//创建一个工作表,名为:第一页
HSSFSheet sheet =workBook.createSheet("第一页");
sheet.setColumnWidth((short)0,(short)5000);
sheet.setColumnWidth((short)1,(short)4000);
// 设置字体
HSSFFont headfont = workBook.createFont();
headfont.setFontName("黑体");
// 字体大小
headfont.setFontHeightInPoints((short) 10);
// 加粗
headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//表格第一行样式
HSSFCellStyle headstyle = workBook.createCellStyle();
headstyle.setFont(headfont);
// 左右居中
headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 上下居中
headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
headstyle.setLocked(true);
// 自动换行
headstyle.setWrapText(true);
//表格中间内容样式
HSSFCellStyle centerstyle = workBook.createCellStyle();
// 左右居中
centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 上下居中
centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//创建一个单元格,从0开始
HSSFRow row =sheet.createRow((short)0);
//构造一个数组设置第一行之后的单元格
HSSFCell cell[] =new HSSFCell[2];
for(int i=0 ;i <2 ;i++){
cell[i] =row.createCell((short) i);
cell[i].setCellStyle(headstyle);
}
cell[0].setCellValue("成本类型");
cell[1].setCellValue("成本(元)");
//获得从数据库中查询出来的数据
String sql1 = "select C.COST_TYPE, A.PROJ_COST"+
" from PJ_PROJ_COST A,PJ_PROJ_INFO B,PJ_COST_CAT C"+
" where A.PROJ_ID = B.PROJ_ID and A.COST_ID = C.COST_ID and A.PROJ_ID ='"+paramBean.get("PROJ_ID")+"'";
String sql2 = "select sum(PROJ_COST) COST from PJ_PROJ_COST where PROJ_ID ='"+paramBean.get("PROJ_ID")+"'" ;
// OutBean
List<Bean> list1= Context.getExecutor().query(sql1);
List<Bean> list2= Context.getExecutor().query(sql2);
if(list1 !=null && list1.size() >0){
System.out.println("--------4-----");
//循环list中的数据
for(int i =0;i<list1.size();i++){
HSSFRow dataRow =sheet.createRow(i+1);
HSSFCell data[] =new HSSFCell[list1.size()];
System.out.println("--------555555-----");
for(int j= 0;j <2 ; j++){
System.out.println("--------666666-----");
data[j] =dataRow.createCell((short) j);
data[j].setCellStyle(centerstyle);
System.out.println("--------6-----");
}
data[0].setCellValue(list1.get(i).getStr("COST_TYPE"));
data[1].setCellValue(list1.get(i).getStr("PROJ_COST"));
System.out.println("--------7-----");
}
//创建表格最后一行成本总计
HSSFRow dataRow =sheet.createRow(list1.size()+1);
//构造一个数组设置第一行之后的单元格
HSSFCell cell2[] =new HSSFCell[2];
for(int i=0 ;i <2 ;i++){
cell2[i] =dataRow.createCell((short) i);
cell2[i].setCellStyle(headstyle);
}
cell2[0].setCellValue("总计");
cell2[1].setCellValue(list2.get(0).getStr("COST"));
System.out.println("list的长度"+list2.size()+"======"+list2.get(0).getStr("COST"));
try {
//输出成XLS文件
File file =new File("e:\\cost.xls");
FileOutputStream fos = new FileOutputStream(file);
System.out.println("--------8-----");
//写入数据,并关闭文件
workBook.write(fos);
fos.close();
System.out.println("----------");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
System.out.println("-----++++++++++++++++++-----");
}
return null;
}
}