liboqi
- 浏览: 32613 次
- 性别:
- 来自: 湖南
-
文章分类
最新评论
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;
}
分享到：
发表评论
-
HttpClient容易忽视的细节——连接关闭
2012-03-02 09:15 583HttpClient client = new HttpCli ... -
HTTPClient的多线程编程
2012-03-02 09:09 1451Axis2的连接主要采用了HttpClient进行与服务器的服 ... -
HttpClient超时区别
2012-03-02 09:07 1045HttpClient 4 设置超时 httpclient 超时 ... -
HttpClient Theading
2012-03-01 10:49 641有技术兴趣的 请加28830308群. 这篇文章概括了怎样在 ... -
HttpClient使用
2012-03-01 10:43 677有技术兴趣的 请加28830308群. HttpClien ... -
线程请求执行,一个多线程程执行HTTP请求的例子。
2012-03-01 10:40 1722package cn.lake.util; import ... -
httpclient 4 下载 文件
2012-03-01 10:38 896import java.io.File; import ja ... -
使用了httpclient实现的上传商品的demo
2012-03-01 10:36 1119package com.taobao.top.sample.u ... -
socket简介
2012-02-07 10:58 657第一步 充分理解Socket ... -
转载socket
2012-02-07 10:57 532对TCP/IP、UDP、Socket编程这些词你不会很陌生吧？ ... -
Java的synchronized关键字：同步机制总结
2012-02-07 10:54 540不久前用到了同步，现在回过头来对JAVA中的同步做个总结，以对 ... -
JAVA RMI
2012-02-07 10:53 549Java RMI 指的是远程方法调用 (Remote Meth ... -
如何快速的为现有数据库建立数据字典？
2011-12-20 13:55 960大部分项目在验收时都需要向客户提供一份详细的数据字典，而编写数 ... -
在java中获取客户端真实的IP地址
2011-12-20 13:55 525public static String getRemoteA ... -
用JavaMail的API发送邮件
2011-12-20 13:55 5041、MimeMessage的包装类 public c ... -
在项目中整合FreeMarker框架
2011-12-16 15:40 561FreeMarker是一个用Java编写的模板引擎，既可以 ... -
Commons FTP范例
2011-12-16 15:33 482public class FtpUtil { privat ... -
Java实现的图片生成器
2011-12-16 15:32 489一、本图片生成器具有以下功能特性： 1、可以设置图 ... -
jacob学习总结
2011-12-16 15:27 407JACOB 就是 JAVA-COM Bridge的缩写，提供自 ... -
长短信发送范例
2011-12-16 15:25 662一、关键代码： private synchronized v ...
相关推荐
jxl模版生成excel,采用类似EL表达式的方式生成模版
java利用jxl生成excel文件,代码直接导入就可以跑（修改自己的生成的ecxel地址）
使用jxl生成excel的通用方法，参数List<?>数据,LinkedHashMap,String>实体类属性名和中文列名的键值对
资源包含：（1）一个txt转为excel Demo（2）将解析出的数据写入excel表格里所需要架包jxl。
poi jxl 生成EXCEL 报表 POI 用的JAR poi-3.6-20091214.jar jxl 用到的jar jxl-2.6.jar
例子如下: Object是一个对象实体类,如Studnet.java. List 是一个对象集合,后面的是输出路径. WriteExcel rw=new WriteExcel(); rw.writeExcel(new Object(), List, "f:/a.xls","库存表");
jxl是对excel强有力的操纵工具，它具有丰富的API可以满足对excel的各种开发需要。
自己封装的方法，调用 XlHelper.getXl(List<?> voList , Object head , OutputStream ops)方法，直接返回需要的excel，而不需要再去写jxl的方法。第一个参数voList 是excel里的内容，第二个参数head是excel表头。第...
用jxl生成EXCEL表格，一个非常简单易懂的写法。
java JXL导出Excel源码及jfreechart 生成折线图，饼图 java JXL导出Excel源码及jfreechart 生成折线图，饼图 java JXL导出Excel源码及jfreechart 生成折线图，饼图
JXL生成excel报表，是一种专门用来制作excle的报表工具插件，用来处理导入到处操作
NULL 博文链接：https://heisetoufa.iteye.com/blog/1932093
jxl对下拉列表的读写操作以及相应的修改功能
该插件是基于jxl的，进过扩展后自需要4行代码即可生成简单的execl表，不用再去了解jxl里面复杂的方法
JXL操作EXCEL 数据库导出EXCEL相关文件一些详细资料
Java生成excel jxl 代码Java生成excel jxl 代码
利用jxl操作excel文件，提取exce文件数据生成txt文件。
java通过jxl生成excel实例,读取excel,复制,修改excel文件
生成Excel 2000标准格式 支持字体、数字、日期操作 能够修饰单元格属性 支持图像和图表 应该说以上功能已经能够大致满足我们的需要。最关键的是这套API是纯Java的，并不依赖Windows系统，即使运行在Linux下，它...
java用于生成EXCEL等文件格式的包，直接导入到java工程即可，简单方便，另外还配有jxl api说明文档