public String ExcelTaskReport(String orderid) throws RowsExceededException, WriteException, IOException{
String reportFileName = null;
File folder = null;
File file = null;
WritableWorkbook workbook = null;
try {
PATask task = this.get(Long.parseLong(orderid));
StringBuffer sb = new StringBuffer();
if(task.getBranchId()!=null){
sb.append(" select apt.order_name,api.index_level,decode(api.index_type,'1','定量','2','定性'), ");
sb.append(" api.index_name,aptd.weight_factor,aptd.target_value,api.parent_id,api.id ");
sb.append(" from ASY_PA_TASK apt inner join ASY_PA_TASK_DETAIL aptd on apt.id = aptd.order_id ");
sb.append(" inner join ASY_PA_INDEX api on aptd.index_id = api.id inner join ASY_BRANCH_INFO abi ");
sb.append(" on apt.branch_id = abi.id and apt.id = ");
sb.append(orderid);
sb.append(" order by api.id,api.index_level ");
}else{
sb.append(" select apt.order_name,api.index_level,decode(api.index_type,'1','定量','2','定性'), ");
sb.append(" api.index_name,aptd.weight_factor,aptd.target_value,api.parent_id,api.id ");
sb.append(" from ASY_PA_TASK apt inner join ASY_PA_TASK_DETAIL aptd on apt.id = aptd.order_id ");
sb.append(" inner join ASY_PA_INDEX api on aptd.index_id = api.id inner join asy_branch_dept abd on abd.id = apt.department_id ");
sb.append(" and apt.id = ");
sb.append(orderid);
sb.append(" order by api.id,api.index_level ");
}
SQLQuery query = baseDao.getHibernate().getSessionFactory()
.getCurrentSession().createSQLQuery(sb.toString());
List<Object[]> result = query.list();
//一级指标list
List<PATaskExcel> reports = new ArrayList<PATaskExcel>();
//二级指标list
List<PATaskExcel> reports1 = new ArrayList<PATaskExcel>();
//三级指标list
List<PATaskExcel> reports2 = new ArrayList<PATaskExcel>();
//指标分区
for (Object[] r : result) {
PATaskExcel report = wrapperExcelTaskReport(r);
if(report.getIndex_level().equals("1")){
reports.add(report);
}
if(report.getIndex_level().equals("2")){
reports1.add(report);
}
if(report.getIndex_level().equals("3")){
reports2.add(report);
}
}
SecUser secUser = RequestHandler.getContextRequestHandler().getCurrentUser();
BranchInfo branchInfo = baseDao.findUniqueBy(BranchInfo.class,
"id", new Long(secUser.getBranchId().getId()), false);
String branchCode = StringUtils.trim(branchInfo.getCode());
String filename = returnfilename(reports);
// ## File ##
reportFileName = Constants.reportRoot + File.separator + branchCode + File.separator;
folder = new File(reportFileName);
if (!folder.exists()) {
folder.mkdirs();
}
SimpleDateFormat sdf = new SimpleDateFormat("yyMMddHHmmss");
reportFileName +="KHZB_" + sdf.format(new Date()) + ".xls";
file = new File(reportFileName);
if (file.exists()) {
file.delete();
}
// ## 样式 ##
// 大标题的格式
WritableCellFormat headerFormat = new WritableCellFormat(
new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD,false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
headerFormat.setAlignment(Alignment.CENTRE);
headerFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
// 列标题的格式
WritableCellFormat columnFormat = new WritableCellFormat(
new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD,false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
columnFormat.setAlignment(Alignment.CENTRE);
columnFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
columnFormat.setBorder(Border.ALL, BorderLineStyle.THIN,Colour.BLACK);
// ## Write Excel ##
workbook = Workbook.createWorkbook(file);
WritableSheet sheet = workbook.createSheet("sheet0", 0);
// 写表头
//sheet.mergeCells(0, 0, 5, 0);
//sheet.addCell(new Label(0, 0,filename,headerFormat));
//有3级指标,必然有2级指标和1级指标
int rowIndex2 = 0;
if(reports2.size()!=0 && reports1.size()!=0 && reports.size()!=0){
//头部
sheet.mergeCells(0, 0, 5, 0);
sheet.addCell(new Label(0, 0,filename,
headerFormat));
String[] labels = { "一级指标", "权重%","二级指标", "三级指标","指标类型","年度目标值",};
for (int i = 0; i < labels.length; i++) {
sheet.addCell(new Label(i, 1, labels[i], columnFormat));
}
//身体
int rowIndex = 2;
//链型指标表
//一级指标
for(int index = 0 ;index<reports.size();index++){
int a = 0,b = 0;int c =0;int d = 0;
List<PATaskExcel> reports3 =new ArrayList<PATaskExcel>();
PATaskExcel taskExcel = reports.get(index);
reports3.add(taskExcel);
String taskid = taskExcel.getTaskid();
//有木有2级指标
for(int index2 = 0;index2<reports1.size();index2++){
PATaskExcel taskExcel2 = reports1.get(index2);
String parent_id = taskExcel2.getParent_id();
String taskid1 = taskExcel2.getTaskid();
if(taskid.equals(parent_id)){
reports3.add(taskExcel2);
a++;
}else{
continue;
}
//有木有三级指标
for(int index3 = 0;index3<reports2.size();index3++){
PATaskExcel taskExcel3 = reports2.get(index3);
if(taskid1.equals(taskExcel3.getParent_id())){
reports3.add(taskExcel3);
b++;
}else{
continue;
}
}
}
//mergeCells column, row column1, row1
//有2级指标,有3级指标
for(int index4 = 0;index4<reports3.size();index4++){
PATaskExcel taskExcel4 = reports3.get(index4);
sheet.setColumnView(0, 20);
sheet.setColumnView(1, 10);
sheet.setColumnView(2, 20);
sheet.setColumnView(3, 20);
sheet.setColumnView(4, 10);
sheet.setColumnView(5, 90);
if(a==0){
if(taskExcel4.getIndex_level().equals("1")){
sheet.addCell(new Label(0, rowIndex, taskExcel4.getIndex_name(), columnFormat));
sheet.addCell(new Label(1, rowIndex, taskExcel4.getWeight_factor(), columnFormat));
sheet.addCell(new Label(2, rowIndex, "", columnFormat));
sheet.addCell(new Label(3, rowIndex, "", columnFormat));
sheet.addCell(new Label(4, rowIndex, taskExcel4.getIndex_type(), columnFormat));
sheet.addCell(new Label(5, rowIndex, taskExcel4.getTarget_value(), columnFormat));
rowIndex++;
}
}else if(a!=0){
if(b!=0){
if(a<b){
if(taskExcel4.getIndex_level().equals("1")){
if(b%2==1){
int count = 0;
int q = 0;
for(int index5 = 0;index5<reports3.size();index5++){
PATaskExcel taskExcel5 = reports3.get(index5);
if(taskExcel5.getIndex_level().equals("2")){
count = this.getCountlevel(orderid, taskExcel5.getTaskid(),"3",task);
}
}
if(count == 0){
sheet.mergeCells(0, rowIndex, 0, rowIndex+b);
sheet.mergeCells(1, rowIndex, 1, rowIndex+b);
sheet.addCell(new Label(0, rowIndex, taskExcel4.getIndex_name(), columnFormat));
sheet.addCell(new Label(1, rowIndex, taskExcel4.getWeight_factor(), columnFormat));
sheet.addCell(new Label(2, rowIndex, "", columnFormat));
sheet.addCell(new Label(3, rowIndex, "", columnFormat));
sheet.addCell(new Label(4, rowIndex, taskExcel4.getIndex_type(), columnFormat));
sheet.addCell(new Label(5, rowIndex, taskExcel4.getTarget_value(), columnFormat));
}else{
sheet.mergeCells(0, rowIndex, 0, rowIndex+b-1);
sheet.mergeCells(1, rowIndex, 1, rowIndex+b-1);
sheet.addCell(new Label(0, rowIndex, taskExcel4.getIndex_name(), columnFormat));
sheet.addCell(new Label(1, rowIndex, taskExcel4.getWeight_factor(), columnFormat));
sheet.addCell(new Label(2, rowIndex, "", columnFormat));
sheet.addCell(new Label(3, rowIndex, "", columnFormat));
sheet.addCell(new Label(4, rowIndex, taskExcel4.getIndex_type(), columnFormat));
sheet.addCell(new Label(5, rowIndex, taskExcel4.getTarget_value(), columnFormat));
}
}else{
sheet.mergeCells(0, rowIndex, 0, rowIndex+b-1);
sheet.mergeCells(1, rowIndex, 1, rowIndex+b-1);
sheet.addCell(new Label(0, rowIndex, taskExcel4.getIndex_name(), columnFormat));
sheet.addCell(new Label(1, rowIndex, taskExcel4.getWeight_factor(), columnFormat));
sheet.addCell(new Label(2, rowIndex, "", columnFormat));
sheet.addCell(new Label(3, rowIndex, "", columnFormat));
sheet.addCell(new Label(4, rowIndex, taskExcel4.getIndex_type(), columnFormat));
sheet.addCell(new Label(5, rowIndex, taskExcel4.getTarget_value(), columnFormat));
}
}
}else if(a==b){
if(taskExcel4.getIndex_level().equals("1")){
if(b%2==1){
sheet.mergeCells(0, rowIndex, 0, rowIndex+b-1-a+b);
sheet.mergeCells(1, rowIndex, 1, rowIndex+b-1-a+b);
sheet.addCell(new Label(0, rowIndex, taskExcel4.getIndex_name(), columnFormat));
sheet.addCell(new Label(1, rowIndex, taskExcel4.getWeight_factor(), columnFormat));
sheet.addCell(new Label(2, rowIndex, "", columnFormat));
sheet.addCell(new Label(3, rowIndex, "", columnFormat));
sheet.addCell(new Label(4, rowIndex, taskExcel4.getIndex_type(), columnFormat));
sheet.addCell(new Label(5, rowIndex, taskExcel4.getTarget_value(), columnFormat));
}else{
sheet.mergeCells(0, rowIndex, 0, rowIndex+b);
sheet.mergeCells(1, rowIndex, 1, rowIndex+b);
sheet.addCell(new Label(0, rowIndex, taskExcel4.getIndex_name(), columnFormat));
sheet.addCell(new Label(1, rowIndex, taskExcel4.getWeight_factor(), columnFormat));
sheet.addCell(new Label(2, rowIndex, "", columnFormat));
sheet.addCell(new Label(3, rowIndex, "", columnFormat));
sheet.addCell(new Label(4, rowIndex, taskExcel4.getIndex_type(), columnFormat));
sheet.addCell(new Label(5, rowIndex, taskExcel4.getTarget_value(), columnFormat));
}
}
}else{
if(taskExcel4.getIndex_level().equals("1")){
sheet.mergeCells(0, rowIndex, 0, rowIndex+a-1);
sheet.mergeCells(1, rowIndex, 1, rowIndex+a-1);
sheet.addCell(new Label(0, rowIndex, taskExcel4.getIndex_name(), columnFormat));
sheet.addCell(new Label(1, rowIndex, taskExcel4.getWeight_factor(), columnFormat));
sheet.addCell(new Label(2, rowIndex, "", columnFormat));
sheet.addCell(new Label(3, rowIndex, "", columnFormat));
sheet.addCell(new Label(4, rowIndex, taskExcel4.getIndex_type(), columnFormat));
sheet.addCell(new Label(5, rowIndex, taskExcel4.getTarget_value(), columnFormat));
}
}
}else{
if(taskExcel4.getIndex_level().equals("1")){
sheet.mergeCells(0, rowIndex, 0, rowIndex+a-1);
sheet.mergeCells(1, rowIndex, 1, rowIndex+a-1);
sheet.addCell(new Label(0, rowIndex, taskExcel4.getIndex_name(), columnFormat));
sheet.addCell(new Label(1, rowIndex, taskExcel4.getWeight_factor(), columnFormat));
sheet.addCell(new Label(2, rowIndex, "", columnFormat));
sheet.addCell(new Label(3, rowIndex, "", columnFormat));
sheet.addCell(new Label(4, rowIndex, taskExcel4.getIndex_type(), columnFormat));
sheet.addCell(new Label(5, rowIndex, taskExcel4.getTarget_value(), columnFormat));
}
}
}
//二级指标
if(taskExcel4.getIndex_level().equals("2")){
int count = this.getCountlevel(orderid, taskExcel4.getTaskid(),"3",task);
if(count>1){
//rowIndex = rowIndex + count;
Range mergeCells = sheet.mergeCells(2, rowIndex, 2, rowIndex+count-1);
System.out.println(mergeCells.toString());
sheet.addCell(new Label(2, rowIndex, taskExcel4.getIndex_name(), columnFormat));
}else if(count == 1){
sheet.addCell(new Label(2, rowIndex, taskExcel4.getIndex_name(), columnFormat));
}else if(count==0){
sheet.addCell(new Label(2, rowIndex, taskExcel4.getIndex_name(), columnFormat));
sheet.addCell(new Label(3, rowIndex, "", columnFormat));
sheet.addCell(new Label(4, rowIndex, taskExcel4.getIndex_type(), columnFormat));
sheet.addCell(new Label(5, rowIndex, taskExcel4.getTarget_value(), columnFormat));
rowIndex++;
}
}
//三级指标
if(taskExcel4.getIndex_level().equals("3")){
if(d==0){
sheet.addCell(new Label(3, rowIndex, taskExcel4.getIndex_name(), columnFormat));
sheet.addCell(new Label(4, rowIndex, taskExcel4.getIndex_type(), columnFormat));
sheet.addCell(new Label(5, rowIndex, taskExcel4.getTarget_value(), columnFormat));
}
if(d>0){
sheet.addCell(new Label(3, rowIndex, taskExcel4.getIndex_name(), columnFormat));
sheet.addCell(new Label(4, rowIndex, taskExcel4.getIndex_type(), columnFormat));
sheet.addCell(new Label(5, rowIndex, taskExcel4.getTarget_value(), columnFormat));
}
d++;
rowIndex++;
}
}
}
//只有2级指标,没有3级指标
}else if(reports1.size()!=0 && reports2.size()==0 && reports.size()!=0){
//头部
sheet.mergeCells(0, 0, 4, 0);
sheet.addCell(new Label(0, 0,filename,
headerFormat));
String[] labels = { "一级指标", "权重%","二级指标","指标类型","年度目标值",};
for (int i = 0; i < labels.length; i++) {
sheet.addCell(new Label(i, 1, labels[i], columnFormat));
}
//身体
int rowIndex = 2;
//链型指标表
for(int index = 0 ;index<reports.size();index++){
//sheet.setColumnView(rowIndex, 60);
int a = 0,b = 0;
List<PATaskExcel> reports3 =new ArrayList<PATaskExcel>();
PATaskExcel taskExcel = reports.get(index);
reports3.add(taskExcel);
String taskid = taskExcel.getTaskid();
for(int index2 = 0;index2<reports1.size();index2++){
PATaskExcel taskExcel2 = reports1.get(index2);
String parent_id = taskExcel2.getParent_id();
String taskid1 = taskExcel2.getTaskid();
if(taskid.equals(parent_id)){
reports3.add(taskExcel2);
a++;
}else{
continue;
}
for(int index3 = 0;index3<reports2.size();index3++){
b++;
PATaskExcel taskExcel3 = reports2.get(index2);
if(taskid1.equals(taskExcel3.getParent_id())){
reports3.add(taskExcel3);
}else{
continue;
}
}
}
int c = 0;
for(int index4 = 0;index4<reports3.size();index4++){
PATaskExcel taskExcel4 = reports3.get(index4);
sheet.setColumnView(0, 20);
sheet.setColumnView(1, 10);
sheet.setColumnView(2, 20);
sheet.setColumnView(3, 10);
sheet.setColumnView(4, 90);
if(a == 0){
if(taskExcel4.getIndex_level().equals("1")){
sheet.addCell(new Label(0, rowIndex, taskExcel4.getIndex_name(), columnFormat));
sheet.addCell(new Label(1, rowIndex, taskExcel4.getWeight_factor(), columnFormat));
sheet.addCell(new Label(2, rowIndex, "", columnFormat));
sheet.addCell(new Label(3, rowIndex, taskExcel4.getIndex_type(), columnFormat));
sheet.addCell(new Label(4, rowIndex, taskExcel4.getTarget_value(), columnFormat));
rowIndex++;
}
}else{
if(taskExcel4.getIndex_level().equals("1")){
sheet.mergeCells(0, rowIndex, 0, rowIndex+a-1);
sheet.mergeCells(1, rowIndex, 1, rowIndex+a-1);
sheet.addCell(new Label(0, rowIndex, taskExcel4.getIndex_name(), columnFormat));
sheet.addCell(new Label(1, rowIndex, taskExcel4.getWeight_factor(), columnFormat));
sheet.addCell(new Label(2, rowIndex, "", columnFormat));
sheet.addCell(new Label(3, rowIndex, taskExcel4.getIndex_type(), columnFormat));
sheet.addCell(new Label(4, rowIndex, taskExcel4.getTarget_value(), columnFormat));
}
}
if(taskExcel4.getIndex_level().equals("2")){
if(c==0){
sheet.addCell(new Label(2, rowIndex, taskExcel4.getIndex_name(), columnFormat));
sheet.addCell(new Label(3, rowIndex, taskExcel4.getIndex_type(), columnFormat));
sheet.addCell(new Label(4, rowIndex, taskExcel4.getTarget_value(), columnFormat));
}
if(c>0){
//int j=++rowIndex;
sheet.addCell(new Label(2, rowIndex, taskExcel4.getIndex_name(), columnFormat));
sheet.addCell(new Label(3, rowIndex, taskExcel4.getIndex_type(), columnFormat));
sheet.addCell(new Label(4, rowIndex, taskExcel4.getTarget_value(), columnFormat));
}
c++;
rowIndex++;
}
}
}
//只有1级指标
}else if(reports.size()!=0 && reports1.size()==0 && reports2.size()==0){
//头部
sheet.mergeCells(0, 0, 3, 0);
sheet.addCell(new Label(0, 0,filename,
headerFormat));
String[] labels = { "一级指标", "权重%","指标类型","年度目标值",};
for (int i = 0; i < labels.length; i++) {
sheet.addCell(new Label(i, 1, labels[i], columnFormat));
}
//身体
int rowIndex = 2;
for(int index = 0 ;index<reports.size();index++){
List<PATaskExcel> reports3 =new ArrayList<PATaskExcel>();
PATaskExcel taskExcel = reports.get(index);
reports3.add(taskExcel);
for(int index4 = 0;index4<reports3.size();index4++){
sheet.setColumnView(0, 20);
sheet.setColumnView(1, 10);
sheet.setColumnView(2, 10);
sheet.setColumnView(3, 90);
PATaskExcel taskExcel4 = reports3.get(index4);
sheet.addCell(new Label(0, rowIndex, taskExcel4.getIndex_name(), columnFormat));
sheet.addCell(new Label(1, rowIndex, taskExcel4.getWeight_factor()+"%", columnFormat));
sheet.addCell(new Label(2, rowIndex, taskExcel4.getIndex_type(), columnFormat));
sheet.addCell(new Label(3, rowIndex, taskExcel4.getTarget_value(), columnFormat));
rowIndex++;
}
}
}
workbook.write();
} finally {
if (folder != null)
folder = null;
if (file != null)
file = null;
if (workbook != null) {
workbook.close();
workbook = null;
}
}
return reportFileName;
}