- 浏览: 123335 次
- 性别:
- 来自: 广州
文章分类
- 全部博客 (130)
- JUnit4学习 (0)
- Spring3.X学习 (2)
- 日记 (1)
- 文学类 (2)
- Java (15)
- Thingking In Java (11)
- org.apache.poi (4)
- XML (2)
- Log4j (1)
- Jar包收集 (2)
- ExtJs (1)
- 汇编语言 (11)
- 开发工具 (0)
- 电子书 (2)
- Oracle (6)
- Ajax (1)
- Jquery (2)
- myBatis (1)
- Spring2.5学习 (6)
- Tomcat (1)
- MyEclipse (1)
- JSP (1)
- Linux shell 脚本攻略 (7)
- Python3 (2)
- HTML5 (5)
- JavaScript (7)
- Hadoop-1.2.1 (2)
- Python2.7 (12)
- Django (3)
- 软件安装 (1)
- 高级Bash脚本编程指南 (7)
- Linux命令 (3)
- Ansible (2)
- MySQL (2)
- 病历 (1)
- 操作系统 (1)
- CSS (0)
- CSS3 (0)
- 面试题 (1)
最新评论
-
hw1287789687:
http://www.cnblogs.com/hccwu/p/ ...
Java获取真实的IP地址 -
liubey:
String ip = request.getHeader(& ...
Java获取真实的IP地址 -
bewithme:
我记得uploadify这破东西只能在chrome浏览器中才有 ...
Struts2结合Jquery.uploadify上传插件的应用 -
MrLee23:
http://mrlee23.iteye.com/admin/ ...
Struts2结合Jquery.uploadify上传插件的应用 -
crysik:
import com.eshore.ppm.model.com ...
Struts2结合Jquery.uploadify上传插件的应用
//根据账期导出各类Excel
package com.poson.cb.ext.export;
import java.io.File;
import java.io.UnsupportedEncodingException;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import javax.servlet.http.HttpServletRequest;
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.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import com.poson.cb.ext.basic.ExportRequest;
import com.poson.cb.lib.DBConn;
import com.poson.cb.lib.log.Log;
import com.poson.cb.util.XmlParse;
import com.poson.report.ExcelHandler;
import com.poson.report.Table;
public class FileClassCountExport extends ExportRequest {
private String typeId;
private String billingCycle;
// 根据账期解析出两个日期值
private String pre_cycle;
private String last_cycle;
private String sql1String, sql2String, sql3String, sql4String, sql5String,sql6String;
public void getInputs(HttpServletRequest request) throws Exception {
typeId = request.getParameter("typeId");
billingCycle = request.getParameter("billingCycle");
}
public File doExport() throws Exception {
DBConn db=new DBConn();
File file = null;
try {
//解析账期
setPreAndLastCycle();
//初始化脚本
initSqlString();
Log.info("========开始打印============");
Log.info("sql1String="+sql1String);
Log.info("sql2String="+sql2String);
Log.info("sql3String="+sql3String);
Log.info("sql4String="+sql4String);
Log.info("sql5String="+sql5String);
Log.info("sql6String="+sql6String);
Log.info("========结束打印============");
HSSFWorkbook wb = new HSSFWorkbook();
//设置Excel列格式
HSSFCellStyle cellStyle1=wb.createCellStyle();
cellStyle1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle1.setFillForegroundColor(HSSFColor.BRIGHT_GREEN.index);
cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER); //左右居中
cellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 上下居中
//cellStyle1.setWrapText(true); //设置自动换行
HSSFCellStyle cellStyle2=wb.createCellStyle();
cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
String sheetName=billingCycle.substring(0,4)+"年"+billingCycle.substring(4,6)+"月份";
if (typeId.equals("1")) {
db.prepareStatement(sql1String);
db.executeSql(sql2String);
int s3=db.executeSql(sql3String);
int s4=db.executeSql(sql4String);
System.out.println("是否删除:s3="+s3+" ; 是否插入:s4="+s4);
// 生成Excel文件
Table t1 = new Table("IVPN用户资料");
t1.addColumn("prov");
t1.addColumn("audit_result");
t1.addColumn("serv_id");
t1.addColumn("cust_id");
t1.addColumn("acct_id");
t1.addColumn("acc_nbr");
t1.addColumn("product_code");
t1.addColumn("state");
t1.addColumn("state_date");
t1.addColumn("attr_value(月限额为单位元)");
Table t2 = new Table("渣打银行");
t2.addColumn("prov");
t2.addColumn("audit_result");
t2.addColumn("serv_id");
t2.addColumn("cust_id");
t2.addColumn("acct_id");
t2.addColumn("acc_nbr");
t2.addColumn("product_code");
t2.addColumn("state");
t2.addColumn("state_date");
ResultSet rSet1 = db.getResultSet(sql5String);
t1.setData(rSet1);
ResultSet rSet2 = db.getResultSet(sql6String);
t2.setData(rSet2);
ExcelHandler excel = new ExcelHandler(wb);
HSSFSheet sheet1 = wb.createSheet("渣打银行");
HSSFSheet sheet2 = wb.createSheet("一致部分");
excel.writeTable(sheet1, t2, true);
excel.writeTable(sheet2, t1, true);
file = writeExcelFile(wb);
System.out.println("文件绝对路径: "+ file.getAbsolutePath());
} else if (typeId.equals("2")) {
ExcelHandler excel = new ExcelHandler(wb);
HSSFSheet sheet1=wb.createSheet("错误消息分类统计"+billingCycle);
HSSFRow row1=sheet1.createRow(0);
HSSFRow row2=sheet1.createRow(1);
HSSFRow row3=sheet1.createRow(2);
HSSFRow row4=sheet1.createRow(3);
HSSFRow row5=sheet1.createRow(4);
HSSFRow row6=sheet1.createRow(5);
//设置列宽
HSSFFont font=wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); //加粗
//设置第一行
String[] header1={"消息方向","消息接口","失败消息类型","消息数","总数"};
for(int i=0;i<5;i++){
HSSFCell cell=row1.createCell((short)i);
cell.setCellValue(new HSSFRichTextString(header1[i]));
cell.setCellStyle(cellStyle1);
}
//合并第二行和第三行
sheet1.addMergedRegion(new Region((short)1,(short)0,(short)2,(short)0)); //合并A2,A3
sheet1.addMergedRegion(new Region((short)1,(short)1,(short)2,(short)1)); //合并B2,B3
sheet1.addMergedRegion(new Region((short)1,(short)4,(short)2,(short)4)); //和并 E2,E3
//合并第四行和第五行
sheet1.addMergedRegion(new Region((short)3,(short)0,(short)4,(short)0)); //和并 A4,A5
sheet1.addMergedRegion(new Region((short)3,(short)1,(short)4,(short)1)); //和并 B4,B5
sheet1.addMergedRegion(new Region((short)3,(short)4,(short)4,(short)4)); //和并 E4,E5
sheet1.addMergedRegion(new Region((short)5,(short)0,(short)5,(short)3)); //和并 A6,D6
//添加单元格名称
row2.createCell((short)0).setCellValue(new HSSFRichTextString("接收"));
row2.createCell((short)1).setCellValue(new HSSFRichTextString("与集团CRM接口"));
row2.createCell((short)2).setCellValue(new HSSFRichTextString("业务级失败"));
row3.createCell((short)2).setCellValue(new HSSFRichTextString("系统级失败"));
row4.createCell((short)0).setCellValue(new HSSFRichTextString("发送"));
row4.createCell((short)1).setCellValue(new HSSFRichTextString("与省计费接口"));
row4.createCell((short)2).setCellValue(new HSSFRichTextString("业务级失败"));
row5.createCell((short)2).setCellValue(new HSSFRichTextString("系统级失败"));
row6.createCell((short)0).setCellValue(new HSSFRichTextString("总计"));
sheet1.setColumnWidth((short)1, (short)5000);
sheet1.setColumnWidth((short)2, (short)5000);
//第二个sheet页 =========================================================
HSSFSheet sheet2= wb.createSheet("统计分类详细信息");
String[] header2={"消息类型","错误描述","消息数","涉及省份"};
HSSFRow shRow1= sheet2.createRow(0);
shRow1.setHeightInPoints(25f);
shRow1.createCell((short)1).setCellValue(new HSSFRichTextString("接收-与集团CRM接口-业务级失败"));
shRow1.getCell((short)1).setCellStyle(cellStyle1);
HSSFRow shRow2=sheet2.createRow((short)1);
for(int i=0;i<4;i++){
HSSFCell cell= shRow2.createCell((short)i);
cell.setCellValue(new HSSFRichTextString(header2[i]));
cell.setCellStyle(cellStyle1);
}
//声明一个变量 控制行数
int var_Row=2;
int rece_sys_count=0; //接收系统级失败
int rece_serv_count=0; //接收业务级失败
int send_sys_count=0; //发送系统级失败
int send_serv_count=0; //发送业务级失败
//执行sql1String 1业务级 (集团发过来)
ResultSet set1= db.getResultSet(sql1String);
while(set1.next()){
HSSFRow row=sheet2.createRow(var_Row++);
row.createCell((short)0).setCellValue(new HSSFRichTextString(set1.getString(1)));
row.createCell((short)1).setCellValue(new HSSFRichTextString(set1.getString(2)));
row.createCell((short)2).setCellValue(set1.getInt(3));
rece_serv_count= rece_serv_count+ set1.getInt(3);
}
sheet2.createRow(var_Row++); //添加一空行
HSSFRow shRow3=sheet2.createRow(var_Row++);
shRow3.createCell((short)1).setCellValue(new HSSFRichTextString("小计"));
shRow3.createCell((short)2).setCellValue(rece_serv_count);
sheet2.createRow(var_Row++); //创建一个空行
//执行sql3String 3系统级(集团发过来)
HSSFRow shRow5=sheet2.createRow(var_Row++);
shRow5.createCell((short)1).setCellValue(new HSSFRichTextString("接收-与集团CRM接口-系统级失败"));
shRow5.getCell((short)1).setCellStyle(cellStyle1);
HSSFRow shRow6=sheet2.createRow(var_Row++);
for(int i=0;i<4;i++){
HSSFCell cell= shRow6.createCell((short)i);
cell.setCellValue(new HSSFRichTextString(header2[i]));
cell.setCellStyle(cellStyle1);
}
ResultSet set2= db.getResultSet(sql3String);
while(set2.next()){
HSSFRow row=sheet2.createRow(var_Row++);
row.createCell((short)0).setCellValue(new HSSFRichTextString(set2.getString(1)));
row.createCell((short)1).setCellValue(new HSSFRichTextString(set2.getString(2)));
row.createCell((short)2).setCellValue(set2.getInt(3));
rece_sys_count= rece_sys_count+ set2.getInt(3);
}
sheet2.createRow(var_Row++); //添加一空行
HSSFRow shRow7=sheet2.createRow(var_Row++);
shRow7.createCell((short)1).setCellValue(new HSSFRichTextString("小计"));
shRow7.createCell((short)2).setCellValue(rece_sys_count);
sheet2.createRow(var_Row++); //创建一个空行
//执行sql2String 2业务级(发送给省)
HSSFRow shRow8=sheet2.createRow(var_Row++);
shRow8.createCell((short)1).setCellValue(new HSSFRichTextString("发送-与省计费接口-业务级失败"));
shRow8.getCell((short)1).setCellStyle(cellStyle1);
HSSFRow shRow9=sheet2.createRow(var_Row++);
for(int i=0;i<4;i++){
HSSFCell cell= shRow9.createCell((short)i);
cell.setCellValue(new HSSFRichTextString(header2[i]));
cell.setCellStyle(cellStyle1);
}
ResultSet set3= db.getResultSet(sql2String);
while(set3.next()){
HSSFRow row=sheet2.createRow(var_Row++);
row.createCell((short)0).setCellValue(new HSSFRichTextString(set3.getString(1)));
row.createCell((short)1).setCellValue(new HSSFRichTextString(set3.getString(2)));
row.createCell((short)2).setCellValue(set3.getInt(3));
send_serv_count= send_serv_count+ set3.getInt(3);
}
sheet2.createRow(var_Row++); //创建一个空行
HSSFRow shRow10=sheet2.createRow(var_Row++);
shRow10.createCell((short)1).setCellValue(new HSSFRichTextString("小计"));
shRow10.createCell((short)2).setCellValue(send_serv_count);
sheet2.createRow(var_Row++); //创建一个空行
//执行sql4String 4系统级(发送给省)
HSSFRow shRow11=sheet2.createRow(var_Row++);
shRow11.createCell((short)1).setCellValue(new HSSFRichTextString("发送-与省计费接口-系统级失败"));
shRow11.getCell((short)1).setCellStyle(cellStyle1);
HSSFRow shRow12=sheet2.createRow(var_Row++);
for(int i=0;i<4;i++){
HSSFCell cell= shRow12.createCell((short)i);
cell.setCellValue(new HSSFRichTextString(header2[i]));
cell.setCellStyle(cellStyle1);
}
ResultSet set4= db.getResultSet(sql4String);
while(set4.next()){
HSSFRow row=sheet2.createRow(var_Row++);
row.createCell((short)0).setCellValue(new HSSFRichTextString(set4.getString(1)));
row.createCell((short)1).setCellValue(new HSSFRichTextString(set4.getString(2)));
row.createCell((short)2).setCellValue(set4.getInt(3));
send_sys_count= send_sys_count+ set4.getInt(3);
}
sheet2.createRow(var_Row++); //创建一个空行
HSSFRow shRow13=sheet2.createRow(var_Row++);
shRow13.createCell((short)1).setCellValue(new HSSFRichTextString("小计"));
shRow13.getCell((short)1).setCellStyle(cellStyle2);
shRow13.createCell((short)2).setCellValue(send_sys_count);
//最后将统计接口写入到第一个sheet页中
row2.createCell((short)3).setCellValue(rece_serv_count);
row3.createCell((short)3).setCellValue(rece_sys_count);
row4.createCell((short)3).setCellValue(send_serv_count);
row5.createCell((short)3).setCellValue(send_sys_count);
row2.createCell((short)4).setCellValue(rece_serv_count+rece_sys_count);
row4.createCell((short)4).setCellValue(send_serv_count+send_sys_count);
row6.createCell((short)4).setCellValue(send_serv_count+send_sys_count+rece_serv_count+rece_sys_count);
//调整单元格宽度
sheet2.setColumnWidth((short)0, (short)5000);
sheet2.setColumnWidth((short)1, (short)8000);
file=writeExcelFile(wb);
} else if (typeId.equals("3")) {
HSSFSheet sheet1= wb.createSheet(sheetName+"错单统计");
HSSFRow row1= sheet1.createRow((short)0);
sheet1.addMergedRegion(new Region((short)0,(short)0,(short)0,(short)3));
ResultSet set1= db.getResultSet(sql1String);
int sumRecord=0;
while(set1.next()){
sumRecord=set1.getInt(1);
}
row1.createCell((short)0).setCellValue(new HSSFRichTextString(sheetName+"(正确清单总数为"+sumRecord+")"));
HSSFRow row2= sheet1.createRow(sheet1.getLastRowNum()+1);
String[] header1={"省份","文件名","错误码","错误描述"};
for(int i=0;i<4;i++){
HSSFCell cell=row2.createCell((short)i);
cell.setCellValue(new HSSFRichTextString(header1[i]));
cell.setCellStyle(cellStyle1);
}
ResultSet set2=db.getResultSet(sql2String);
while(set2.next()){
HSSFRow row= sheet1.createRow(sheet1.getLastRowNum()+1);
row.createCell((short)0).setCellValue(new HSSFRichTextString(set2.getString(1)));
row.createCell((short)1).setCellValue(new HSSFRichTextString(set2.getString(2)));
String tempString=set2.getString(3);
row.createCell((short)2).setCellValue(new HSSFRichTextString(tempString));
row.createCell((short)3).setCellValue(new HSSFRichTextString(getErrorMsg(tempString)));
}
sheet1.createRow(sheet1.getLastRowNum()+1); //加一个空行
HSSFRow row3= sheet1.createRow(sheet1.getLastRowNum()+1);
String[] header2={"错单类型","错单描述","错单数"};
for(int i=0;i<3;i++){
HSSFCell cell=row3.createCell((short)i);
cell.setCellValue(new HSSFRichTextString(header2[i]));
cell.setCellStyle(cellStyle1);
}
/* row3.createCell((short)0).setCellValue(new HSSFRichTextString("错单类型"));
row3.createCell((short)1).setCellValue(new HSSFRichTextString("错单描述"));
row3.createCell((short)2).setCellValue(new HSSFRichTextString("错单数"));*/
ResultSet set3=db.getResultSet(sql3String);
int count=0;
while(set3.next()){
HSSFRow row= sheet1.createRow(sheet1.getLastRowNum()+1);
row.createCell((short)0).setCellValue(new HSSFRichTextString(set3.getString(1)));
String tempString=set3.getString(1);
row.createCell((short)1).setCellValue(new HSSFRichTextString(getErrorMsg(tempString)));
row.createCell((short)2).setCellValue(new HSSFRichTextString(tempString));
count=count+set3.getInt(2);
}
HSSFRow row4= sheet1.createRow(sheet1.getLastRowNum()+1);
row4.createCell((short)1).setCellValue(new HSSFRichTextString("错单总数:"));
row4.createCell((short)2).setCellValue(count);
sheet1.setColumnWidth((short)1,(short)12000);
sheet1.setColumnWidth((short)3,(short)12000);
file=writeExcelFile(wb);
} else if (typeId.equals("4")) {
int j=1,k=1,m=1; //用于生成序列这一列
HSSFSheet sheet=wb.createSheet(sheetName+"清单文件错单统计");
HSSFRow row1=sheet.createRow((short)0);
String[] headerStr1={"序号","省份","错单文件数","错单占比"};
String[] headerStr2={"序号","错误类型","错误描述","错单文件数","错单占比"};
String[] headerStr3={"错单产生时间 ","错单汇总"};
for(int i=0;i<4;i++){
HSSFCell cell=row1.createCell((short)i);
cell.setCellValue(new HSSFRichTextString(headerStr1[i]));
cell.setCellStyle(cellStyle1);
}
ResultSet set1=db.getConn().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE).executeQuery(sql1String);
int countSet1=0;
//得到set1的中分组记录数之和
while(set1.next()){
countSet1=countSet1+set1.getInt(2);
}
ResultSet set11=db.getResultSet(sql1String);
while(set11.next()){
HSSFRow row= sheet.createRow(j);
row.createCell((short)0).setCellValue(j++); //序列
row.createCell((short)1).setCellValue(set11.getString(1));
row.createCell((short)2).setCellValue(set11.getString(2));
row.createCell((short)3).setCellValue(getPercent(set11.getInt(2),countSet1));
}
HSSFRow row2=sheet.createRow(j);
sheet.addMergedRegion(new Region((short)j,(short)0,(short)j,(short)1));
row2.createCell((short)0).setCellValue(new HSSFRichTextString("合计"));
row2.getCell((short)0).setCellStyle(cellStyle2);
row2.createCell((short)2).setCellValue(countSet1);
//构建第二个表格
for(int i=6;i<11;i++){
row1.createCell((short)i).setCellStyle(cellStyle1);
}
row1.getCell((short)6).setCellValue(new HSSFRichTextString(headerStr2[0]));
row1.getCell((short)7).setCellValue(new HSSFRichTextString(headerStr2[1]));
row1.getCell((short)8).setCellValue(new HSSFRichTextString(headerStr2[2]));
row1.getCell((short)9).setCellValue(new HSSFRichTextString(headerStr2[3]));
row1.getCell((short)10).setCellValue(new HSSFRichTextString(headerStr2[4]));
ResultSet set2=db.getConn().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE).executeQuery(sql2String);
int countSet2=0;
//得到set1的中分组记录数之和
while(set2.next()){
countSet2=countSet2+set2.getInt(2);
}
ResultSet set22=db.getResultSet(sql2String);
while(set22.next()){
HSSFRow row;
if((sheet.getRow(k))!=null){
row=sheet.getRow(k);
}else {
row=sheet.createRow(k);
}
row.createCell((short)6).setCellValue(k++);
row.createCell((short)7).setCellValue(new HSSFRichTextString(set22.getString(1)));
row.createCell((short)8).setCellValue(new HSSFRichTextString(getErrorMsg(set22.getString(1))));
row.createCell((short)9).setCellValue(set22.getInt(2));
row.createCell((short)10).setCellValue(getPercent(set22.getInt(2), countSet2));
}
HSSFRow row3;
if((sheet.getRow(k))!=null){
row3=sheet.getRow(k);
}else {
row3=sheet.createRow(k);
}
sheet.addMergedRegion(new Region((short)k,(short)6,(short)k,(short)8));
row3.createCell((short)6).setCellValue(new HSSFRichTextString("合计"));
row3.getCell((short)6).setCellStyle(cellStyle2);
row3.createCell((short)9).setCellValue(countSet2);
//构建第三个图表
row1.createCell((short)13).setCellValue(new HSSFRichTextString(headerStr3[0]));
row1.getCell((short)13).setCellStyle(cellStyle1);
row1.createCell((short)14).setCellValue(new HSSFRichTextString(headerStr3[1]));
row1.getCell((short)14).setCellStyle(cellStyle1);
ResultSet set3=db.getResultSet(sql3String);
int countSet3=0;
while(set3.next()){
HSSFRow row;
if((sheet.getRow(m))!=null){
row=sheet.getRow(m);
}else {
row=sheet.createRow(m);
}
row.createCell((short)13).setCellValue(new HSSFRichTextString(set3.getString(1)));
row.createCell((short)14).setCellValue(set3.getInt(2));
countSet3=countSet3+set3.getInt(2);
m++;
}
HSSFRow row4;
if((sheet.getRow(m))!=null){
row4=sheet.getRow(m);
}else {
row4=sheet.createRow(m);
}
row4.createCell((short)13).setCellValue(new HSSFRichTextString("合计"));
row4.getCell((short)13).setCellStyle(cellStyle2);
row4.createCell((short)14).setCellValue(countSet3);
sheet.setColumnWidth((short)2, (short)3000);
sheet.setColumnWidth((short)3, (short)3000);
sheet.setColumnWidth((short)8, (short)12000);
sheet.setColumnWidth((short)13, (short)5000);
file=writeExcelFile(wb);
}
return file;
} finally {
db.close();
}
}
public String getExportName() {
return "ivpn";
}
public String getFolderName() {
return "ivpn";
}
public String getNewFileName() {
String str="";
SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMddHHmmss");
if(typeId.equals("1")){
str="IVPN"+getCodeCovert("用户资料")+billingCycle+".xls";
}
if(typeId.equals("2")){
str=getCodeCovert("错误消息分类统计")+billingCycle+".xls";
}
if(typeId.equals("3")){
str=getCodeCovert("错单统计")+billingCycle+".xls";
}
if(typeId.equals("4")){
str=getCodeCovert("错单清单文件")+billingCycle+".xls";
}
return str;
}
public File writeExcelFile(HSSFWorkbook wb) throws Exception {
return super.writeExcelFile(wb);
}
// 对账期进行处理
// 初始化sqlString ;
public void initSqlString() throws Exception {
XmlParse xmParse = XmlParse.getInstance();
// [1]IVPN用户资料统计
if (typeId.equals("1")) {
// 处理脚本1
sql1String = xmParse.getTextContentById("ivpnTruncateSql");
// 处理脚本2
String var1 = xmParse.getTextContentById("ivpnFirstInsertSql");
// 替换参数
sql2String = var1.replaceAll("p_lastCycle", last_cycle);
//处理脚本3
String var3=xmParse.getTextContentById("ivpnDeleteSql");
sql3String=var3.replaceAll("p_preCycle", pre_cycle);
// 处理脚本4
String var4 = xmParse.getTextContentById("ivpnSecondInsertSql");
// 替换参数
sql4String = var4.replaceAll("p_lastCycle", last_cycle);
// 一致性用户
sql5String = xmParse.getTextContentById("queryUniformitySql");
// 渣打银行
sql6String = xmParse.getTextContentById("queryCharteredBank");
}
// [2]错误消息分类统计
if (typeId.equals("2")) {
//1业务级 (集团发过来)
String var1=xmParse.getTextContentById("errMsgServFromGroup");
sql1String=var1.replaceAll("p_billingCycle",billingCycle);
//2业务级(发送给省)
String var2=xmParse.getTextContentById("errMsgServToProv");
sql2String=var2.replaceAll("p_billingCycle",billingCycle);
//3系统级(集团发过来)
String var3=xmParse.getTextContentById("errMsgSysFromGroup");
sql3String=var3.replaceAll("p_billingCycle",billingCycle);
//4系统级(发送给省)
String var4=xmParse.getTextContentById("errMsgSysToProv");
sql4String=var4.replaceAll("p_billingCycle",billingCycle);
}
// [3]错单统计
if (typeId.equals("3")) {
//正确清单总数
String var1=xmParse.getTextContentById("rightBillCountSql");
sql1String = var1.replaceAll("p_billingCycle",billingCycle);
//错误账单文件
String var2=xmParse.getTextContentById("errBillFileByProSql");
sql2String = var2.replaceAll("tl_error_record","tl_error_record_"+billingCycle);
//
String var3=xmParse.getTextContentById("errBillByCodeSql");
sql3String = var3.replaceAll("tl_error_record","tl_error_record_"+billingCycle);
}
// [4]清单错单文件统计
if (typeId.equals("4")) {
String var1=xmParse.getTextContentById("errorBilingForProvSql");
sql1String =var1.replaceAll("tl_error_record","tl_error_record_"+billingCycle);
String var2=xmParse.getTextContentById("errorBilingForEtypeSql");
sql2String =var2.replaceAll("tl_error_record","tl_error_record_"+billingCycle);
String var3=xmParse.getTextContentById("errorBilingForDateSql");
sql3String =var3.replaceAll("tl_error_record","tl_error_record_"+billingCycle);
}
}
/*
* 返回统计账期。如果小于15号则返回上个月的日期,否则返回当月日期
*/
public String getBillingCycle() {
// 根据传过来的账期进行解析,如果传过来的是当月的账期,则根据日期进行判断
Calendar c = Calendar.getInstance();
int year = c.get(Calendar.YEAR);
int month = c.get(Calendar.MONTH) + 1;
int day = c.get(Calendar.DAY_OF_MONTH);
if (day < 15) {
if (month == 1) {
month = 12;
year = year - 1;
} else {
month = month - 1;
}
}
if (month < 10) {
return year + "0" + month;
} else {
return year + "" + month;
}
}
// 根据传过来的账期设置过滤时间段
public void setPreAndLastCycle() {
System.out.println("处理前:last_cycle="+last_cycle +" pre_cycle="+pre_cycle+ " billingCycle="+billingCycle);
if (!billingCycle.equals("") && null != billingCycle) {
// 解析传过来的日期
System.out.println("billingCycle=" + billingCycle);
int year = Integer.parseInt(billingCycle.substring(0, 4));
int month = Integer.parseInt(billingCycle.substring(4, 6));
// 得到当前时间
Calendar c = Calendar.getInstance();
int cur_year = c.get(Calendar.YEAR);
int cur_month = c.get(Calendar.MONTH) + 1;
int cur_day = c.get(Calendar.DAY_OF_MONTH);
// 如果传过来的账期为当月账期
if (year == cur_year && month == cur_month) {
// 说明要查的是当月或上月的账期
if (cur_day < 15) {
// 查上月的数据
//例如传过来的是201307 则last_cycle=20130701 pre_cycle=20130601
last_cycle=cur_year+""+(cur_month>10?cur_month+"":"0"+cur_month)+"01";
pre_cycle=(cur_month==1?cur_year-1:cur_year)+""+(cur_month==1?12+"":(cur_month-1>10?(cur_month-1)+"":"0"+(cur_month-1)))+"01" ;
//last_cycle = (cur_month == 1 ? cur_year - 1 : cur_year)+ ""+ (cur_month == 1 ? "12": (cur_month - 1 < 10 ? ("0" + (cur_month - 1)): (cur_month - 1) + "")) + "30";
//pre_cycle = (cur_month == 1 ? cur_year - 1 : cur_year)+ ""+ (cur_month == 1 ? "12": (cur_month - 1 < 10 ? ("0" + (cur_month - 1)): (cur_month - 1) + "")) + "01";
} else {
//例如传过来的是201307 则last_cycle=20130801 pre_cycle=20130701
last_cycle=(cur_month==12?cur_year+1:cur_year)+""+(cur_month+1>12?"0"+1:(cur_month+1>10?(cur_month+1+""):"0"+(cur_month+1)))+"01";
pre_cycle=cur_year+""+(cur_month>10?cur_month+"":"0"+cur_month)+"01";
//last_cycle = cur_year+ ""+ (cur_month < 10 ? ("0" + (cur_month)): (cur_month) + "") + "30";
//pre_cycle = cur_year+ ""+ (cur_month < 10 ? ("0" + (cur_month)): (cur_month) + "") + "01";
}
} else {
// 如果传的是以往的账期,则按以往的账期进行处理 例如传过来的账期为 201306 则last_cycle=20130701 pre_cycle=20130601
last_cycle=(month==12?year+1:year)+""+(month==12?"0"+1:(month+1>10?month+1+"":"0"+(month+1)))+"01";
pre_cycle=year+(month>10?month+"":"0"+month)+"01";
//last_cycle = year + ""+ (month < 10 ? ("0" + month) : month + "") + "30";
//pre_cycle = year + ""+ (month < 10 ? ("0" + month) : month + "") + "01";
}
}
System.out.println("处理后:last_cycle="+last_cycle +" pre_cycle="+pre_cycle);
}
public void show() {
System.out.println("last_cycle=" + last_cycle);
System.out.println("pre_cycle=" + pre_cycle);
}
public String getErrorMsg(String errCode){
System.out.println("传过来的错误码为:"+errCode);
String valString="";
if(errCode.equals("F600")){
valString="记录重单";
}else if(errCode.equals("F700")){
valString="记录无主:根据话单中servId或者号码找不到用户资料";
}else if(errCode.equals("F701")){
valString="无效用户:用户存在,但在本账期未生效或已过期";
}else if(errCode.equals("F702")){
valString="资料不匹配:用户存在,但是话单中字段与资料不一致";
}else {
valString="字段错误:"+"第"+Integer.parseInt(errCode.substring(1,3))+"个字段错误";
}
return valString;
}
public String getCodeCovert(String fileName){
String name="";
try {
name= new String(fileName.getBytes("GBK"),"ISO8859-1");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("转换后的文件名为:"+name);
return name;
}
//返回百分比
public static String getPercent(int dividend, int divisor){
if (dividend==0 || divisor==0) {
return "";
}else{
DecimalFormat myformat = null;
myformat= (DecimalFormat)NumberFormat.getPercentInstance();
myformat.applyPattern("0%"); //0表示加的小数点,00表示两位小数点,你用00试一下你就知道效果
myformat.setMaximumFractionDigits(2);//这个1的意识是保存结果到小数点后几位(精确度)
double rat = (double)dividend/(double)divisor;
return (myformat.format(rat)).toString();
}
}
public static void main(String[] args) {
// System.out.println(new FileClassCountExport().getLastBillingCycle());
// new FileClassCountExport().setPreAndLastCycle();
/*FileClassCountExport cExport = new FileClassCountExport();
cExport.setPreAndLastCycle();
cExport.show();*/
//System.out.println(getPercent(0, 0));
}
}
2013-08-01 19:24 记 @jinrongdajie31.xichengqu.beijing
package com.poson.cb.ext.export;
import java.io.File;
import java.io.UnsupportedEncodingException;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import javax.servlet.http.HttpServletRequest;
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.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import com.poson.cb.ext.basic.ExportRequest;
import com.poson.cb.lib.DBConn;
import com.poson.cb.lib.log.Log;
import com.poson.cb.util.XmlParse;
import com.poson.report.ExcelHandler;
import com.poson.report.Table;
public class FileClassCountExport extends ExportRequest {
private String typeId;
private String billingCycle;
// 根据账期解析出两个日期值
private String pre_cycle;
private String last_cycle;
private String sql1String, sql2String, sql3String, sql4String, sql5String,sql6String;
public void getInputs(HttpServletRequest request) throws Exception {
typeId = request.getParameter("typeId");
billingCycle = request.getParameter("billingCycle");
}
public File doExport() throws Exception {
DBConn db=new DBConn();
File file = null;
try {
//解析账期
setPreAndLastCycle();
//初始化脚本
initSqlString();
Log.info("========开始打印============");
Log.info("sql1String="+sql1String);
Log.info("sql2String="+sql2String);
Log.info("sql3String="+sql3String);
Log.info("sql4String="+sql4String);
Log.info("sql5String="+sql5String);
Log.info("sql6String="+sql6String);
Log.info("========结束打印============");
HSSFWorkbook wb = new HSSFWorkbook();
//设置Excel列格式
HSSFCellStyle cellStyle1=wb.createCellStyle();
cellStyle1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle1.setFillForegroundColor(HSSFColor.BRIGHT_GREEN.index);
cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER); //左右居中
cellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 上下居中
//cellStyle1.setWrapText(true); //设置自动换行
HSSFCellStyle cellStyle2=wb.createCellStyle();
cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
String sheetName=billingCycle.substring(0,4)+"年"+billingCycle.substring(4,6)+"月份";
if (typeId.equals("1")) {
db.prepareStatement(sql1String);
db.executeSql(sql2String);
int s3=db.executeSql(sql3String);
int s4=db.executeSql(sql4String);
System.out.println("是否删除:s3="+s3+" ; 是否插入:s4="+s4);
// 生成Excel文件
Table t1 = new Table("IVPN用户资料");
t1.addColumn("prov");
t1.addColumn("audit_result");
t1.addColumn("serv_id");
t1.addColumn("cust_id");
t1.addColumn("acct_id");
t1.addColumn("acc_nbr");
t1.addColumn("product_code");
t1.addColumn("state");
t1.addColumn("state_date");
t1.addColumn("attr_value(月限额为单位元)");
Table t2 = new Table("渣打银行");
t2.addColumn("prov");
t2.addColumn("audit_result");
t2.addColumn("serv_id");
t2.addColumn("cust_id");
t2.addColumn("acct_id");
t2.addColumn("acc_nbr");
t2.addColumn("product_code");
t2.addColumn("state");
t2.addColumn("state_date");
ResultSet rSet1 = db.getResultSet(sql5String);
t1.setData(rSet1);
ResultSet rSet2 = db.getResultSet(sql6String);
t2.setData(rSet2);
ExcelHandler excel = new ExcelHandler(wb);
HSSFSheet sheet1 = wb.createSheet("渣打银行");
HSSFSheet sheet2 = wb.createSheet("一致部分");
excel.writeTable(sheet1, t2, true);
excel.writeTable(sheet2, t1, true);
file = writeExcelFile(wb);
System.out.println("文件绝对路径: "+ file.getAbsolutePath());
} else if (typeId.equals("2")) {
ExcelHandler excel = new ExcelHandler(wb);
HSSFSheet sheet1=wb.createSheet("错误消息分类统计"+billingCycle);
HSSFRow row1=sheet1.createRow(0);
HSSFRow row2=sheet1.createRow(1);
HSSFRow row3=sheet1.createRow(2);
HSSFRow row4=sheet1.createRow(3);
HSSFRow row5=sheet1.createRow(4);
HSSFRow row6=sheet1.createRow(5);
//设置列宽
HSSFFont font=wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); //加粗
//设置第一行
String[] header1={"消息方向","消息接口","失败消息类型","消息数","总数"};
for(int i=0;i<5;i++){
HSSFCell cell=row1.createCell((short)i);
cell.setCellValue(new HSSFRichTextString(header1[i]));
cell.setCellStyle(cellStyle1);
}
//合并第二行和第三行
sheet1.addMergedRegion(new Region((short)1,(short)0,(short)2,(short)0)); //合并A2,A3
sheet1.addMergedRegion(new Region((short)1,(short)1,(short)2,(short)1)); //合并B2,B3
sheet1.addMergedRegion(new Region((short)1,(short)4,(short)2,(short)4)); //和并 E2,E3
//合并第四行和第五行
sheet1.addMergedRegion(new Region((short)3,(short)0,(short)4,(short)0)); //和并 A4,A5
sheet1.addMergedRegion(new Region((short)3,(short)1,(short)4,(short)1)); //和并 B4,B5
sheet1.addMergedRegion(new Region((short)3,(short)4,(short)4,(short)4)); //和并 E4,E5
sheet1.addMergedRegion(new Region((short)5,(short)0,(short)5,(short)3)); //和并 A6,D6
//添加单元格名称
row2.createCell((short)0).setCellValue(new HSSFRichTextString("接收"));
row2.createCell((short)1).setCellValue(new HSSFRichTextString("与集团CRM接口"));
row2.createCell((short)2).setCellValue(new HSSFRichTextString("业务级失败"));
row3.createCell((short)2).setCellValue(new HSSFRichTextString("系统级失败"));
row4.createCell((short)0).setCellValue(new HSSFRichTextString("发送"));
row4.createCell((short)1).setCellValue(new HSSFRichTextString("与省计费接口"));
row4.createCell((short)2).setCellValue(new HSSFRichTextString("业务级失败"));
row5.createCell((short)2).setCellValue(new HSSFRichTextString("系统级失败"));
row6.createCell((short)0).setCellValue(new HSSFRichTextString("总计"));
sheet1.setColumnWidth((short)1, (short)5000);
sheet1.setColumnWidth((short)2, (short)5000);
//第二个sheet页 =========================================================
HSSFSheet sheet2= wb.createSheet("统计分类详细信息");
String[] header2={"消息类型","错误描述","消息数","涉及省份"};
HSSFRow shRow1= sheet2.createRow(0);
shRow1.setHeightInPoints(25f);
shRow1.createCell((short)1).setCellValue(new HSSFRichTextString("接收-与集团CRM接口-业务级失败"));
shRow1.getCell((short)1).setCellStyle(cellStyle1);
HSSFRow shRow2=sheet2.createRow((short)1);
for(int i=0;i<4;i++){
HSSFCell cell= shRow2.createCell((short)i);
cell.setCellValue(new HSSFRichTextString(header2[i]));
cell.setCellStyle(cellStyle1);
}
//声明一个变量 控制行数
int var_Row=2;
int rece_sys_count=0; //接收系统级失败
int rece_serv_count=0; //接收业务级失败
int send_sys_count=0; //发送系统级失败
int send_serv_count=0; //发送业务级失败
//执行sql1String 1业务级 (集团发过来)
ResultSet set1= db.getResultSet(sql1String);
while(set1.next()){
HSSFRow row=sheet2.createRow(var_Row++);
row.createCell((short)0).setCellValue(new HSSFRichTextString(set1.getString(1)));
row.createCell((short)1).setCellValue(new HSSFRichTextString(set1.getString(2)));
row.createCell((short)2).setCellValue(set1.getInt(3));
rece_serv_count= rece_serv_count+ set1.getInt(3);
}
sheet2.createRow(var_Row++); //添加一空行
HSSFRow shRow3=sheet2.createRow(var_Row++);
shRow3.createCell((short)1).setCellValue(new HSSFRichTextString("小计"));
shRow3.createCell((short)2).setCellValue(rece_serv_count);
sheet2.createRow(var_Row++); //创建一个空行
//执行sql3String 3系统级(集团发过来)
HSSFRow shRow5=sheet2.createRow(var_Row++);
shRow5.createCell((short)1).setCellValue(new HSSFRichTextString("接收-与集团CRM接口-系统级失败"));
shRow5.getCell((short)1).setCellStyle(cellStyle1);
HSSFRow shRow6=sheet2.createRow(var_Row++);
for(int i=0;i<4;i++){
HSSFCell cell= shRow6.createCell((short)i);
cell.setCellValue(new HSSFRichTextString(header2[i]));
cell.setCellStyle(cellStyle1);
}
ResultSet set2= db.getResultSet(sql3String);
while(set2.next()){
HSSFRow row=sheet2.createRow(var_Row++);
row.createCell((short)0).setCellValue(new HSSFRichTextString(set2.getString(1)));
row.createCell((short)1).setCellValue(new HSSFRichTextString(set2.getString(2)));
row.createCell((short)2).setCellValue(set2.getInt(3));
rece_sys_count= rece_sys_count+ set2.getInt(3);
}
sheet2.createRow(var_Row++); //添加一空行
HSSFRow shRow7=sheet2.createRow(var_Row++);
shRow7.createCell((short)1).setCellValue(new HSSFRichTextString("小计"));
shRow7.createCell((short)2).setCellValue(rece_sys_count);
sheet2.createRow(var_Row++); //创建一个空行
//执行sql2String 2业务级(发送给省)
HSSFRow shRow8=sheet2.createRow(var_Row++);
shRow8.createCell((short)1).setCellValue(new HSSFRichTextString("发送-与省计费接口-业务级失败"));
shRow8.getCell((short)1).setCellStyle(cellStyle1);
HSSFRow shRow9=sheet2.createRow(var_Row++);
for(int i=0;i<4;i++){
HSSFCell cell= shRow9.createCell((short)i);
cell.setCellValue(new HSSFRichTextString(header2[i]));
cell.setCellStyle(cellStyle1);
}
ResultSet set3= db.getResultSet(sql2String);
while(set3.next()){
HSSFRow row=sheet2.createRow(var_Row++);
row.createCell((short)0).setCellValue(new HSSFRichTextString(set3.getString(1)));
row.createCell((short)1).setCellValue(new HSSFRichTextString(set3.getString(2)));
row.createCell((short)2).setCellValue(set3.getInt(3));
send_serv_count= send_serv_count+ set3.getInt(3);
}
sheet2.createRow(var_Row++); //创建一个空行
HSSFRow shRow10=sheet2.createRow(var_Row++);
shRow10.createCell((short)1).setCellValue(new HSSFRichTextString("小计"));
shRow10.createCell((short)2).setCellValue(send_serv_count);
sheet2.createRow(var_Row++); //创建一个空行
//执行sql4String 4系统级(发送给省)
HSSFRow shRow11=sheet2.createRow(var_Row++);
shRow11.createCell((short)1).setCellValue(new HSSFRichTextString("发送-与省计费接口-系统级失败"));
shRow11.getCell((short)1).setCellStyle(cellStyle1);
HSSFRow shRow12=sheet2.createRow(var_Row++);
for(int i=0;i<4;i++){
HSSFCell cell= shRow12.createCell((short)i);
cell.setCellValue(new HSSFRichTextString(header2[i]));
cell.setCellStyle(cellStyle1);
}
ResultSet set4= db.getResultSet(sql4String);
while(set4.next()){
HSSFRow row=sheet2.createRow(var_Row++);
row.createCell((short)0).setCellValue(new HSSFRichTextString(set4.getString(1)));
row.createCell((short)1).setCellValue(new HSSFRichTextString(set4.getString(2)));
row.createCell((short)2).setCellValue(set4.getInt(3));
send_sys_count= send_sys_count+ set4.getInt(3);
}
sheet2.createRow(var_Row++); //创建一个空行
HSSFRow shRow13=sheet2.createRow(var_Row++);
shRow13.createCell((short)1).setCellValue(new HSSFRichTextString("小计"));
shRow13.getCell((short)1).setCellStyle(cellStyle2);
shRow13.createCell((short)2).setCellValue(send_sys_count);
//最后将统计接口写入到第一个sheet页中
row2.createCell((short)3).setCellValue(rece_serv_count);
row3.createCell((short)3).setCellValue(rece_sys_count);
row4.createCell((short)3).setCellValue(send_serv_count);
row5.createCell((short)3).setCellValue(send_sys_count);
row2.createCell((short)4).setCellValue(rece_serv_count+rece_sys_count);
row4.createCell((short)4).setCellValue(send_serv_count+send_sys_count);
row6.createCell((short)4).setCellValue(send_serv_count+send_sys_count+rece_serv_count+rece_sys_count);
//调整单元格宽度
sheet2.setColumnWidth((short)0, (short)5000);
sheet2.setColumnWidth((short)1, (short)8000);
file=writeExcelFile(wb);
} else if (typeId.equals("3")) {
HSSFSheet sheet1= wb.createSheet(sheetName+"错单统计");
HSSFRow row1= sheet1.createRow((short)0);
sheet1.addMergedRegion(new Region((short)0,(short)0,(short)0,(short)3));
ResultSet set1= db.getResultSet(sql1String);
int sumRecord=0;
while(set1.next()){
sumRecord=set1.getInt(1);
}
row1.createCell((short)0).setCellValue(new HSSFRichTextString(sheetName+"(正确清单总数为"+sumRecord+")"));
HSSFRow row2= sheet1.createRow(sheet1.getLastRowNum()+1);
String[] header1={"省份","文件名","错误码","错误描述"};
for(int i=0;i<4;i++){
HSSFCell cell=row2.createCell((short)i);
cell.setCellValue(new HSSFRichTextString(header1[i]));
cell.setCellStyle(cellStyle1);
}
ResultSet set2=db.getResultSet(sql2String);
while(set2.next()){
HSSFRow row= sheet1.createRow(sheet1.getLastRowNum()+1);
row.createCell((short)0).setCellValue(new HSSFRichTextString(set2.getString(1)));
row.createCell((short)1).setCellValue(new HSSFRichTextString(set2.getString(2)));
String tempString=set2.getString(3);
row.createCell((short)2).setCellValue(new HSSFRichTextString(tempString));
row.createCell((short)3).setCellValue(new HSSFRichTextString(getErrorMsg(tempString)));
}
sheet1.createRow(sheet1.getLastRowNum()+1); //加一个空行
HSSFRow row3= sheet1.createRow(sheet1.getLastRowNum()+1);
String[] header2={"错单类型","错单描述","错单数"};
for(int i=0;i<3;i++){
HSSFCell cell=row3.createCell((short)i);
cell.setCellValue(new HSSFRichTextString(header2[i]));
cell.setCellStyle(cellStyle1);
}
/* row3.createCell((short)0).setCellValue(new HSSFRichTextString("错单类型"));
row3.createCell((short)1).setCellValue(new HSSFRichTextString("错单描述"));
row3.createCell((short)2).setCellValue(new HSSFRichTextString("错单数"));*/
ResultSet set3=db.getResultSet(sql3String);
int count=0;
while(set3.next()){
HSSFRow row= sheet1.createRow(sheet1.getLastRowNum()+1);
row.createCell((short)0).setCellValue(new HSSFRichTextString(set3.getString(1)));
String tempString=set3.getString(1);
row.createCell((short)1).setCellValue(new HSSFRichTextString(getErrorMsg(tempString)));
row.createCell((short)2).setCellValue(new HSSFRichTextString(tempString));
count=count+set3.getInt(2);
}
HSSFRow row4= sheet1.createRow(sheet1.getLastRowNum()+1);
row4.createCell((short)1).setCellValue(new HSSFRichTextString("错单总数:"));
row4.createCell((short)2).setCellValue(count);
sheet1.setColumnWidth((short)1,(short)12000);
sheet1.setColumnWidth((short)3,(short)12000);
file=writeExcelFile(wb);
} else if (typeId.equals("4")) {
int j=1,k=1,m=1; //用于生成序列这一列
HSSFSheet sheet=wb.createSheet(sheetName+"清单文件错单统计");
HSSFRow row1=sheet.createRow((short)0);
String[] headerStr1={"序号","省份","错单文件数","错单占比"};
String[] headerStr2={"序号","错误类型","错误描述","错单文件数","错单占比"};
String[] headerStr3={"错单产生时间 ","错单汇总"};
for(int i=0;i<4;i++){
HSSFCell cell=row1.createCell((short)i);
cell.setCellValue(new HSSFRichTextString(headerStr1[i]));
cell.setCellStyle(cellStyle1);
}
ResultSet set1=db.getConn().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE).executeQuery(sql1String);
int countSet1=0;
//得到set1的中分组记录数之和
while(set1.next()){
countSet1=countSet1+set1.getInt(2);
}
ResultSet set11=db.getResultSet(sql1String);
while(set11.next()){
HSSFRow row= sheet.createRow(j);
row.createCell((short)0).setCellValue(j++); //序列
row.createCell((short)1).setCellValue(set11.getString(1));
row.createCell((short)2).setCellValue(set11.getString(2));
row.createCell((short)3).setCellValue(getPercent(set11.getInt(2),countSet1));
}
HSSFRow row2=sheet.createRow(j);
sheet.addMergedRegion(new Region((short)j,(short)0,(short)j,(short)1));
row2.createCell((short)0).setCellValue(new HSSFRichTextString("合计"));
row2.getCell((short)0).setCellStyle(cellStyle2);
row2.createCell((short)2).setCellValue(countSet1);
//构建第二个表格
for(int i=6;i<11;i++){
row1.createCell((short)i).setCellStyle(cellStyle1);
}
row1.getCell((short)6).setCellValue(new HSSFRichTextString(headerStr2[0]));
row1.getCell((short)7).setCellValue(new HSSFRichTextString(headerStr2[1]));
row1.getCell((short)8).setCellValue(new HSSFRichTextString(headerStr2[2]));
row1.getCell((short)9).setCellValue(new HSSFRichTextString(headerStr2[3]));
row1.getCell((short)10).setCellValue(new HSSFRichTextString(headerStr2[4]));
ResultSet set2=db.getConn().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE).executeQuery(sql2String);
int countSet2=0;
//得到set1的中分组记录数之和
while(set2.next()){
countSet2=countSet2+set2.getInt(2);
}
ResultSet set22=db.getResultSet(sql2String);
while(set22.next()){
HSSFRow row;
if((sheet.getRow(k))!=null){
row=sheet.getRow(k);
}else {
row=sheet.createRow(k);
}
row.createCell((short)6).setCellValue(k++);
row.createCell((short)7).setCellValue(new HSSFRichTextString(set22.getString(1)));
row.createCell((short)8).setCellValue(new HSSFRichTextString(getErrorMsg(set22.getString(1))));
row.createCell((short)9).setCellValue(set22.getInt(2));
row.createCell((short)10).setCellValue(getPercent(set22.getInt(2), countSet2));
}
HSSFRow row3;
if((sheet.getRow(k))!=null){
row3=sheet.getRow(k);
}else {
row3=sheet.createRow(k);
}
sheet.addMergedRegion(new Region((short)k,(short)6,(short)k,(short)8));
row3.createCell((short)6).setCellValue(new HSSFRichTextString("合计"));
row3.getCell((short)6).setCellStyle(cellStyle2);
row3.createCell((short)9).setCellValue(countSet2);
//构建第三个图表
row1.createCell((short)13).setCellValue(new HSSFRichTextString(headerStr3[0]));
row1.getCell((short)13).setCellStyle(cellStyle1);
row1.createCell((short)14).setCellValue(new HSSFRichTextString(headerStr3[1]));
row1.getCell((short)14).setCellStyle(cellStyle1);
ResultSet set3=db.getResultSet(sql3String);
int countSet3=0;
while(set3.next()){
HSSFRow row;
if((sheet.getRow(m))!=null){
row=sheet.getRow(m);
}else {
row=sheet.createRow(m);
}
row.createCell((short)13).setCellValue(new HSSFRichTextString(set3.getString(1)));
row.createCell((short)14).setCellValue(set3.getInt(2));
countSet3=countSet3+set3.getInt(2);
m++;
}
HSSFRow row4;
if((sheet.getRow(m))!=null){
row4=sheet.getRow(m);
}else {
row4=sheet.createRow(m);
}
row4.createCell((short)13).setCellValue(new HSSFRichTextString("合计"));
row4.getCell((short)13).setCellStyle(cellStyle2);
row4.createCell((short)14).setCellValue(countSet3);
sheet.setColumnWidth((short)2, (short)3000);
sheet.setColumnWidth((short)3, (short)3000);
sheet.setColumnWidth((short)8, (short)12000);
sheet.setColumnWidth((short)13, (short)5000);
file=writeExcelFile(wb);
}
return file;
} finally {
db.close();
}
}
public String getExportName() {
return "ivpn";
}
public String getFolderName() {
return "ivpn";
}
public String getNewFileName() {
String str="";
SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMddHHmmss");
if(typeId.equals("1")){
str="IVPN"+getCodeCovert("用户资料")+billingCycle+".xls";
}
if(typeId.equals("2")){
str=getCodeCovert("错误消息分类统计")+billingCycle+".xls";
}
if(typeId.equals("3")){
str=getCodeCovert("错单统计")+billingCycle+".xls";
}
if(typeId.equals("4")){
str=getCodeCovert("错单清单文件")+billingCycle+".xls";
}
return str;
}
public File writeExcelFile(HSSFWorkbook wb) throws Exception {
return super.writeExcelFile(wb);
}
// 对账期进行处理
// 初始化sqlString ;
public void initSqlString() throws Exception {
XmlParse xmParse = XmlParse.getInstance();
// [1]IVPN用户资料统计
if (typeId.equals("1")) {
// 处理脚本1
sql1String = xmParse.getTextContentById("ivpnTruncateSql");
// 处理脚本2
String var1 = xmParse.getTextContentById("ivpnFirstInsertSql");
// 替换参数
sql2String = var1.replaceAll("p_lastCycle", last_cycle);
//处理脚本3
String var3=xmParse.getTextContentById("ivpnDeleteSql");
sql3String=var3.replaceAll("p_preCycle", pre_cycle);
// 处理脚本4
String var4 = xmParse.getTextContentById("ivpnSecondInsertSql");
// 替换参数
sql4String = var4.replaceAll("p_lastCycle", last_cycle);
// 一致性用户
sql5String = xmParse.getTextContentById("queryUniformitySql");
// 渣打银行
sql6String = xmParse.getTextContentById("queryCharteredBank");
}
// [2]错误消息分类统计
if (typeId.equals("2")) {
//1业务级 (集团发过来)
String var1=xmParse.getTextContentById("errMsgServFromGroup");
sql1String=var1.replaceAll("p_billingCycle",billingCycle);
//2业务级(发送给省)
String var2=xmParse.getTextContentById("errMsgServToProv");
sql2String=var2.replaceAll("p_billingCycle",billingCycle);
//3系统级(集团发过来)
String var3=xmParse.getTextContentById("errMsgSysFromGroup");
sql3String=var3.replaceAll("p_billingCycle",billingCycle);
//4系统级(发送给省)
String var4=xmParse.getTextContentById("errMsgSysToProv");
sql4String=var4.replaceAll("p_billingCycle",billingCycle);
}
// [3]错单统计
if (typeId.equals("3")) {
//正确清单总数
String var1=xmParse.getTextContentById("rightBillCountSql");
sql1String = var1.replaceAll("p_billingCycle",billingCycle);
//错误账单文件
String var2=xmParse.getTextContentById("errBillFileByProSql");
sql2String = var2.replaceAll("tl_error_record","tl_error_record_"+billingCycle);
//
String var3=xmParse.getTextContentById("errBillByCodeSql");
sql3String = var3.replaceAll("tl_error_record","tl_error_record_"+billingCycle);
}
// [4]清单错单文件统计
if (typeId.equals("4")) {
String var1=xmParse.getTextContentById("errorBilingForProvSql");
sql1String =var1.replaceAll("tl_error_record","tl_error_record_"+billingCycle);
String var2=xmParse.getTextContentById("errorBilingForEtypeSql");
sql2String =var2.replaceAll("tl_error_record","tl_error_record_"+billingCycle);
String var3=xmParse.getTextContentById("errorBilingForDateSql");
sql3String =var3.replaceAll("tl_error_record","tl_error_record_"+billingCycle);
}
}
/*
* 返回统计账期。如果小于15号则返回上个月的日期,否则返回当月日期
*/
public String getBillingCycle() {
// 根据传过来的账期进行解析,如果传过来的是当月的账期,则根据日期进行判断
Calendar c = Calendar.getInstance();
int year = c.get(Calendar.YEAR);
int month = c.get(Calendar.MONTH) + 1;
int day = c.get(Calendar.DAY_OF_MONTH);
if (day < 15) {
if (month == 1) {
month = 12;
year = year - 1;
} else {
month = month - 1;
}
}
if (month < 10) {
return year + "0" + month;
} else {
return year + "" + month;
}
}
// 根据传过来的账期设置过滤时间段
public void setPreAndLastCycle() {
System.out.println("处理前:last_cycle="+last_cycle +" pre_cycle="+pre_cycle+ " billingCycle="+billingCycle);
if (!billingCycle.equals("") && null != billingCycle) {
// 解析传过来的日期
System.out.println("billingCycle=" + billingCycle);
int year = Integer.parseInt(billingCycle.substring(0, 4));
int month = Integer.parseInt(billingCycle.substring(4, 6));
// 得到当前时间
Calendar c = Calendar.getInstance();
int cur_year = c.get(Calendar.YEAR);
int cur_month = c.get(Calendar.MONTH) + 1;
int cur_day = c.get(Calendar.DAY_OF_MONTH);
// 如果传过来的账期为当月账期
if (year == cur_year && month == cur_month) {
// 说明要查的是当月或上月的账期
if (cur_day < 15) {
// 查上月的数据
//例如传过来的是201307 则last_cycle=20130701 pre_cycle=20130601
last_cycle=cur_year+""+(cur_month>10?cur_month+"":"0"+cur_month)+"01";
pre_cycle=(cur_month==1?cur_year-1:cur_year)+""+(cur_month==1?12+"":(cur_month-1>10?(cur_month-1)+"":"0"+(cur_month-1)))+"01" ;
//last_cycle = (cur_month == 1 ? cur_year - 1 : cur_year)+ ""+ (cur_month == 1 ? "12": (cur_month - 1 < 10 ? ("0" + (cur_month - 1)): (cur_month - 1) + "")) + "30";
//pre_cycle = (cur_month == 1 ? cur_year - 1 : cur_year)+ ""+ (cur_month == 1 ? "12": (cur_month - 1 < 10 ? ("0" + (cur_month - 1)): (cur_month - 1) + "")) + "01";
} else {
//例如传过来的是201307 则last_cycle=20130801 pre_cycle=20130701
last_cycle=(cur_month==12?cur_year+1:cur_year)+""+(cur_month+1>12?"0"+1:(cur_month+1>10?(cur_month+1+""):"0"+(cur_month+1)))+"01";
pre_cycle=cur_year+""+(cur_month>10?cur_month+"":"0"+cur_month)+"01";
//last_cycle = cur_year+ ""+ (cur_month < 10 ? ("0" + (cur_month)): (cur_month) + "") + "30";
//pre_cycle = cur_year+ ""+ (cur_month < 10 ? ("0" + (cur_month)): (cur_month) + "") + "01";
}
} else {
// 如果传的是以往的账期,则按以往的账期进行处理 例如传过来的账期为 201306 则last_cycle=20130701 pre_cycle=20130601
last_cycle=(month==12?year+1:year)+""+(month==12?"0"+1:(month+1>10?month+1+"":"0"+(month+1)))+"01";
pre_cycle=year+(month>10?month+"":"0"+month)+"01";
//last_cycle = year + ""+ (month < 10 ? ("0" + month) : month + "") + "30";
//pre_cycle = year + ""+ (month < 10 ? ("0" + month) : month + "") + "01";
}
}
System.out.println("处理后:last_cycle="+last_cycle +" pre_cycle="+pre_cycle);
}
public void show() {
System.out.println("last_cycle=" + last_cycle);
System.out.println("pre_cycle=" + pre_cycle);
}
public String getErrorMsg(String errCode){
System.out.println("传过来的错误码为:"+errCode);
String valString="";
if(errCode.equals("F600")){
valString="记录重单";
}else if(errCode.equals("F700")){
valString="记录无主:根据话单中servId或者号码找不到用户资料";
}else if(errCode.equals("F701")){
valString="无效用户:用户存在,但在本账期未生效或已过期";
}else if(errCode.equals("F702")){
valString="资料不匹配:用户存在,但是话单中字段与资料不一致";
}else {
valString="字段错误:"+"第"+Integer.parseInt(errCode.substring(1,3))+"个字段错误";
}
return valString;
}
public String getCodeCovert(String fileName){
String name="";
try {
name= new String(fileName.getBytes("GBK"),"ISO8859-1");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("转换后的文件名为:"+name);
return name;
}
//返回百分比
public static String getPercent(int dividend, int divisor){
if (dividend==0 || divisor==0) {
return "";
}else{
DecimalFormat myformat = null;
myformat= (DecimalFormat)NumberFormat.getPercentInstance();
myformat.applyPattern("0%"); //0表示加的小数点,00表示两位小数点,你用00试一下你就知道效果
myformat.setMaximumFractionDigits(2);//这个1的意识是保存结果到小数点后几位(精确度)
double rat = (double)dividend/(double)divisor;
return (myformat.format(rat)).toString();
}
}
public static void main(String[] args) {
// System.out.println(new FileClassCountExport().getLastBillingCycle());
// new FileClassCountExport().setPreAndLastCycle();
/*FileClassCountExport cExport = new FileClassCountExport();
cExport.setPreAndLastCycle();
cExport.show();*/
//System.out.println(getPercent(0, 0));
}
}
2013-08-01 19:24 记 @jinrongdajie31.xichengqu.beijing
相关推荐
Apache POI是一个开源项目,主要用于处理Microsoft Office格式的文件,特别是Excel文档。"org.apache.poi3.9.jar"是这个项目的一个版本,适用于Java环境,它提供了对Office文档的读取、写入和修改功能。在Java编程中...
Apache POI 是一个开源项目,专门用于处理微软的Office文档格式,如Word(.docx)、Excel(.xlsx)和PowerPoint(.pptx)。在Java环境中,它提供了一个强大的API,使得开发者能够读取、写入和操作这些文档。在给定的...
这个"org.apache.poi"依赖包是Java开发者在处理这些文件时不可或缺的工具。它提供了丰富的API,使得在Java环境中读取、写入和操作Office文档变得轻而易举。 Apache POI 3.17 版本是该项目的一个稳定版本,包含了...
Apache POI 是一个开源项目,专门用于处理Microsoft Office格式的文件,如Word(.docx)、Excel(.xlsx)和PowerPoint(.pptx)。在Java环境中,它提供了一组API,使得开发者能够读取、写入以及操作这些文件。"org....
在实际应用中,"org.apache.poi.xwpf.converter-0.9.8.jar"通常会与其他Apache POI库一起使用,如"HSSF"(处理旧版Excel .xls文件)和"XSSF"(处理新式Excel .xlsx文件)。开发者需要在他们的Java项目中引入这个JAR...
在Java开发中,Apache POI库是处理Excel文件的重要工具,尤其在需要进行大量数据导入导出或自动化报告生成的场景下。"org.apache.poi-ooxml-schemas-3.9.jar" 是这个项目中的一个组件,它包含了用于解析和生成Open ...
这个“org.apache.poi 3.17最新官方版文件操作jar包”包含了处理这些文件类型所需的Java库。下面将详细介绍Apache POI 3.17版本中的核心知识点。 1. **文件格式支持**: - Excel处理:Apache POI提供了HSSF(旧版...
android 使用poi读取高版本excel, 解决以下这两个错误 java.lang.NoClassDefFoundError: Failed resolution of: Ljavax/xml/stream/XMLEventFactory; at org.apache.poi.openxml4j.opc.internal.marshallers....
Java POI 实现 Excel 导入导出 Java POI 是一个流行的 Java 库,用于处理 Microsoft Office 文件格式,包括 Excel 文件。在本文中,我们将详细介绍如何使用 Java POI 实现 Excel 导入导出功能。 1. 什么是 Java ...
在本案例中,我们关注的是如何使用 Apache POI 库来导出 Excel 文件,特别是根据模板导出和简单列表导出。下面将详细介绍这个过程。 1. **Apache POI 概述** Apache POI 提供了 Java API 来读写 Microsoft Office ...
Apache POI 是用Java编写的免费开源的跨平台的 Java API,Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写,意为“简洁版的模糊实现...
### 使用Apache POI进行Excel导入导出操作 在IT领域,数据处理是极其重要的环节之一,而Excel作为数据处理工具中的佼佼者,在各种应用场景中都占有举足轻重的地位。对于Java开发者而言,能够有效地读取、写入Excel...
在快逸报表v4.0或润乾报表中,导出Excel时可能会需要这个poi2 jar包
<groupId>org.apache.poi <artifactId>poi-ooxml <version>3.10-FINAL 利用3.10-FINAL版本的poi,导出逻辑主要在FileDownloadUtils的createExcelFile方法
通过以上步骤,我们成功地实现了使用Apache POI在Java中导出Excel文件,并实现了自动换行的功能。这种方式不仅可以提高工作效率,还能确保数据的准确性和完整性。 #### 六、注意事项与优化建议 - **兼容性问题**:...
Apache POI是一个流行的Java库,用于读取、创建和修改Microsoft Office格式的文件,特别是Excel工作簿。在本文中,我们将深入探讨如何使用POI库将图片插入到Excel文档中,以便创建包含图像的丰富报告。 首先,让...
标题“poi分多个sheet导出excel”所指的就是如何使用POI库来动态地生成多个sheet,并根据设定的阈值(如50000条记录)进行切换。 首先,要使用Apache POI,你需要在项目中引入对应的依赖。如果你使用的是Maven,...
在实际应用中,Apache POI 往往用于数据分析、报表生成、数据导入导出等场景,尤其在需要在服务器端生成或处理Excel文件时,它是Java开发者的重要工具。通过熟练掌握Apache POI,你可以提高Java应用程序与Excel交互...
Apache POI 提供了丰富的API来支持这些功能,使得开发者能够根据需求定制化Excel导出。在处理大数据量时,需要注意内存管理,因为Apache POI会将整个Excel文件加载到内存中,可能需要使用SXSSF低内存模型来避免内存...
例如,你可以利用Selenium自动化地运行测试,并将测试结果导出到Excel文件中,然后用POI进行分析和处理。 总结来说,Apache POI是Java程序员处理Excel文件的强大工具,能够方便地进行数据读取、写入和修改。结合...