杜XX看到我发表的code 提出了宝贵意见,根据他所说的,昨晚上对代码进行了改进,哈哈 看着舒服多了
package javaservlets.production.mpexcel;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javaservlets.common.util.Debug;
import javaservlets.common.util.SimpleServlet;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.CellFormat;
import jxl.write.Label;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
@SuppressWarnings({ "unchecked", "serial" })
public class MP_PC_jxl extends HttpServlet {
List specList = new ArrayList();
Map <String,Object>headMap = new HashMap<String,Object>();
int k = 3;
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String no = null;
Enumeration enumeration = request.getParameterNames();
while (enumeration.hasMoreElements()) {
String paramName = (String) enumeration.nextElement();
if (Debug.Level > 0) {
System.out.println("request parameter [" + paramName + "]=" +
request.getParameter(paramName));
}
}
ArrayList skuList = (ArrayList<String>)request.getAttribute("columnDisp");
ArrayList sku = (ArrayList<String>)request.getAttribute("column");
ArrayList sku2 = new ArrayList();
for(int j = 0;j<sku.size();j++){
System.out.println("sku value ===>"+sku.get(j));
String skutag = "_"+sku.get(j);
sku2.add(skutag);
}
for(int j = 0;j<sku.size();j++){
System.out.println("skuList value ===>"+skuList.get(j));
}
int mycol = skuList.size();
no = request.getParameter("MP_ID");
OutputStream out = null;
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
Map<String,Object>getStyleMap = this.excelStyle();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=PC_List.xls");
String myFmt=new SimpleDateFormat("yyyy年MM月dd日 HH時mm分ss秒").format(new Date());
System.out.println("開始創建excel,創建時間為:"+myFmt);
WritableWorkbook w = Workbook.createWorkbook(response.getOutputStream());
WritableSheet s = w.createSheet("PC list", 0);
s.getSettings().setDefaultColumnWidth(20); //设置列的宽度默认为15
try {
con = SimpleServlet.axaptaCP.getConnection();
stmt = con.createStatement();
stmt.execute("{call " + SimpleServlet.axaptaCP.elbaseDB + ".dbo.el_mp_pc_report_view( '" +no + "')}");
System.out.println("調用存儲過程:--->"+"{call " + SimpleServlet.axaptaCP.elbaseDB + ".dbo.el_mp_pc_report_view( '" +no + "')}");
//获取表头显示信息
Statement stmt2 = null;
ResultSet rs2 = null;
stmt2 = con.createStatement();
rs2 = stmt2.executeQuery("select PROJECT_ID, PROJECT_DESC from " + SimpleServlet.axaptaCP.elbaseDB + ".DBO.mp_header where mp_id = '" + no + "'");
while(rs2.next()){
s.mergeCells(3, 0, 6, 1);
s.addCell(new Label(3, 0, rs2.getString(1),(CellFormat)getStyleMap.get("tablehead")));
s.mergeCells(7, 0, 7+mycol, 1);
s.addCell(new Label(7, 0, rs2.getString(2),(CellFormat)getStyleMap.get("tablehead")));
}
int tag = 1;
Boolean label = true;
while(label){
int i = 0;
i = stmt.getUpdateCount();//包含更新计数的 int 值。如果返回的结果是一个结果集对象或没有更多结果,则返回 -1。
boolean up_label = false;
if(i != -1){
up_label = stmt.getMoreResults();//移动到此SQLServerStatement 对象的下一个结果。如果返回一个结果及
System.out.println("tset getMoreResults()返回值:"+up_label); //false false true
continue;
}
boolean flag_label = false;
rs = stmt.getResultSet();
if(rs != null){
ResultSetMetaData rsmd = rs.getMetaData();//獲取列名 和列對應的類型
if(tag == 1){
//處理第一個結果集
headMap = this.getInfoFromRs1(rs,s,mycol);
System.out.println("dataInfo"+headMap.get("dataInfo"));
System.out.println("headInfo"+headMap.get("headInfo"));
System.out.println("isHaveCosmeticCode"+headMap.get("isHaveCosmeticCode"));
}else if(tag == 2 ){
List<String> speColumnlist = new ArrayList<String>();
System.out.println("當前rs 為:"+rs);
specList = this.specTypeColumn(speColumnlist, rs);
for(int j = 0 ;j<specList.size();j++){
//處理第二個結果集
System.out.println("specList value is :"+ specList.get(j));
}
}else if(tag ==3){
//處理第三個結果集
System.out.println("rs3 :"+this.returnPDate(rs,s,mycol));
}else if(tag ==4){
System.out.println("rs4 :"+this.returnCDate(rs,s,mycol));
}else if(tag ==5){
Map<String,Object> getColumnMap = this.setColumnToExcel(s,mycol,skuList,sku,sku2);
this.setValueToExcel(getColumnMap,rs,s);
}
tag ++;
flag_label = stmt.getMoreResults();
continue;
}
label = up_label|| flag_label;
}
w.write();
String myFmt2=new SimpleDateFormat("yyyy年MM月dd日 HH時mm分ss秒").format(new Date());
System.out.println("excel創建完成,結束時間為:"+myFmt2);
w.close();
} catch (Exception e) {
e.printStackTrace();
}finally {
//释放资源
SimpleServlet.axaptaCP.free(con);
if (out != null)
out.close();
if (stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
//---------------------------------------------------------style---------------------------------------------------
public Map<String,Object> excelStyle(){
Map<String,Object> style = new HashMap<String,Object>();
try {
//table head
jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.GREEN);
jxl.write.WritableCellFormat wchB = new jxl.write.WritableCellFormat(wfc);
wchB.setAlignment(jxl.format.Alignment.CENTRE);
style.put("tablehead", wchB);
jxl.write.WritableFont wfc1 = new jxl.write.WritableFont(WritableFont.TIMES,10,WritableFont.BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED);
jxl.write.WritableCellFormat wchB1 = new jxl.write.WritableCellFormat(wfc1);
wchB1.setAlignment(jxl.format.Alignment.RIGHT);
wchB1.setBackground(jxl.format.Colour.GRAY_25);
style.put("tag", wchB1);
jxl.write.WritableCellFormat wcsB = new jxl.write.WritableCellFormat();
wcsB.setWrap(true);
wcsB.setAlignment(jxl.format.Alignment.CENTRE);
wcsB.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wcsB.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
style.put("titleStyle", wcsB);
jxl.write.WritableCellFormat wcsB0 = new jxl.write.WritableCellFormat();
wcsB0.setWrap(true);
wcsB0.setAlignment(jxl.format.Alignment.CENTRE);
wcsB0.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wcsB0.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
wcsB0.setBackground(jxl.format.Colour.SKY_BLUE);
style.put("cellbackcolorstyle", wcsB0);
jxl.write.WritableCellFormat wcsB2 = new jxl.write.WritableCellFormat();
wcsB2.setAlignment(jxl.format.Alignment.CENTRE);
wcsB2.setWrap(true);
wcsB2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
style.put("voidStyle", wcsB2);
//date smalldatetime
jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd");//yyy-MM-dd hh:mm:ss 年月日时分秒
jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
wcfDF.setAlignment(jxl.format.Alignment.CENTRE);
wcfDF.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
style.put("dateStyle", wcfDF);
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return style;
}
//---------------------------------------------------------style---------------------------------------------------
//獲取第一個結果集中的標頭信息
public Map<String,Object> getInfoFromRs1(ResultSet rs,WritableSheet s,int mycol){
Map <String,Object> map = new HashMap<String,Object>();
try {
ResultSetMetaData rs_meta = rs.getMetaData();
int rsCount = rs_meta.getColumnCount();
if(rs.next()){
for(int i = 0;i<rsCount;i++){
if(i == 0){
String dataInfo = rs.getString(i+1).substring(0, 10);
System.out.println("截取字符串為:---》"+dataInfo);
map.put("dataInfo", dataInfo);
try {
s.mergeCells(13+mycol, 2, 15+mycol, 2);
s.addCell(new Label(13+mycol,2,dataInfo));
} catch (RowsExceededException e1) {
e1.printStackTrace();
} catch (WriteException e1) {
e1.printStackTrace();
}
}else if(i ==1){
String headInfo = rs.getString(i+1);
map.put("headInfo", headInfo);
try {
s.mergeCells(0, 0, 1, 0);
s.addCell(new Label(0,0,rs.getString(i+1)));
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}else {
String isHaveCosmeticCode = rs.getString(i+1);
map.put("isHaveCosmeticCode", isHaveCosmeticCode);
}
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return map;
}
//处理第二个结果集
@SuppressWarnings("unchecked")
public List specTypeColumn(List speColumnlist, ResultSet rs){
try {
int i = 0;
while(rs.next()){
System.out.println("函數中的rs 結果集為:"+rs);
i++;
String specColumn = rs.getString(1);
speColumnlist.add(specColumn);
}
} catch (SQLException e) {
e.printStackTrace();
}
return speColumnlist;
}
//处理第三个結果集
public int returnPDate(ResultSet rs,WritableSheet s,int mycol){
int pDate = 0;
try {
while(rs.next()){
pDate = rs.getInt(2);
try {
s.mergeCells(16+mycol, 0, 17+mycol, 2);
s.addCell(new Label(16+mycol,0,"Pkg + Version release within \n"+pDate +" Days"));
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
return pDate;
} catch (SQLException e) {
e.printStackTrace();
return 0;
}
}
//处理第四个结果集
public int returnCDate (ResultSet rs,WritableSheet s,int mycol){
int cDate = 0;
try {
while(rs.next()){
cDate = rs.getInt(2);
if(headMap.get("isHaveCosmeticCode").equals("yes")){
try {
s.mergeCells(18+mycol, 0, 19+mycol, 2);
s.addCell(new Label(18+mycol,0,"Cosmetic release within \n"+cDate+" Days"));
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
}
return cDate;
} catch (SQLException e) {
e.printStackTrace();
return 0;
}
}
//寫入excel 列名
public Map<String,Object> setColumnToExcel(WritableSheet s,int mycol,List skuList,List sku,List sku2){
Map<String,Object> columnMap = new HashMap<String,Object>();
s.getSettings().setDefaultColumnWidth(20); //设置列的宽度默认为15
Map<String,Object>styleMap = this.excelStyle();
try {
s.setRowView(3, 750);
} catch (RowsExceededException e1) {
e1.printStackTrace();
}
// 列名
String []cellColumn = {"PC#","Revision","CustP/O#","Custmer","Country","Mktg Share","Item#","MIX"};
String []sqlColumn = {"PC_NO","PC_VERSION","PO_CUSTOMER","NAME_CUSTOMER","country","MO_MARKET_ID","ITEM","MIX"};
List<String> cellColumnList = new ArrayList<String>();
List<String> sqlColumnList = new ArrayList<String>();
for(int i = 0; i<cellColumn.length;i++){
cellColumnList.add(cellColumn[i]);
sqlColumnList.add(sqlColumn[i]);
}
cellColumnList.addAll(skuList);
sqlColumnList.addAll(sku2);
String []cellColumn2 = {"Inn","Out","PC qty","Ctn","Rev.Details","Request Ship Date","Insp.Date","Customer Insp.Date"};
String []sqlColumn2 = {"QTY_INNER","QTY_OUTER","PC_QTY","ctn","REV_DESC","SHIP_DATE","INSP_DATE","CUSTOMER_INSP_DATE"};
for(int i = 0 ;i<cellColumn2.length;i++){
cellColumnList.add(cellColumn2[i]);
sqlColumnList.add(sqlColumn2[i]);
}
cellColumnList.add("Packaging");
cellColumnList.add("Packaging Release Status");
sqlColumnList.add("packageingcode");
sqlColumnList.add("Packaging release status");
//判斷欄位cosmeticcode對應的value 是否全部為空,若否添加以下兩個欄位
if(headMap.get("isHaveCosmeticCode").equals("yes")){
cellColumnList.add("Cosmetic Version");
cellColumnList.add("Cosmetic Release Status");
sqlColumnList.add("cosmeticcode");
sqlColumnList.add("Cosmetic release status");
}
//添加动态列
cellColumnList.addAll(specList);
sqlColumnList.addAll(specList);
//对应添加sku
cellColumnList.addAll(skuList);
sqlColumnList.addAll(sku);
columnMap.put("cellColumnList", cellColumnList);
columnMap.put("sqlColumnList", sqlColumnList);
int lastcellcol = 0;
for(int i = 0 ;i<cellColumnList.size();i++){
try {
s.addCell(new Label(i,3,cellColumnList.get(i).toString(),(CellFormat) styleMap.get("titleStyle")));
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
if(i==cellColumnList.size()-1){
lastcellcol= i;
System.out.println("最後一個單元格的位置是:"+lastcellcol);
}
}
try {
s.mergeCells(lastcellcol-mycol+1,0,lastcellcol,2);
s.addCell(new Label(lastcellcol-mycol+1,0,"CONTENT BREAKDOWN",(CellFormat) styleMap.get("tag")));
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
return columnMap;
}
//寫入數據
@SuppressWarnings({ "unchecked", "deprecation" })
public void setValueToExcel(Map getColumnMap,ResultSet rs,WritableSheet s){
try {
Map<String,Object>cellstyleMap = this.excelStyle();
ResultSetMetaData rs_meta = rs.getMetaData();
List<String> column = (List<String>) getColumnMap.get("sqlColumnList");
Map<String,Object> sqltypeMap = new HashMap<String,Object>();
List<String>sqllistTag = new ArrayList<String>();
//获取所有列及列名类型
for(int i = 0;i<rs_meta.getColumnCount(); i++){
String sqlname = rs_meta.getColumnName(i+1);
String sqltypeName = rs_meta.getColumnTypeName(i+1);//获取所有列及列名的类型
System.out.println("列名为:"+sqlname+"列名类型为:"+sqltypeName);
if(sqltypeName.equals("varchar")||sqltypeName.equals("nvarchar")){
sqltypeMap.put(sqlname, "String");
sqllistTag.add(sqlname);
}else if(sqltypeName.equals("smalldatetime")||sqltypeName.equals("datetime")){
sqltypeMap.put(sqlname, "Date");
sqllistTag.add(sqlname);
}else if(sqltypeName.equals("numeric")||sqltypeName.equals("int")){
sqltypeMap.put(sqlname, "int");
sqllistTag.add(sqlname);
}
}
while(rs.next()){
k++;
for(int i = 0 ;i<column.size();i++){
try {
if(sqllistTag.contains(column.get(i))){
if(rs.getString("PC_STATUS").equals("Void")){ //判斷狀態是否為Void
if(sqltypeMap.get(column.get(i))=="String"){
s.addCell(new Label(i,k,rs.getString(column.get(i)),(CellFormat)cellstyleMap.get("cellbackcolorstyle")));
}else if(sqltypeMap.get(column.get(i))=="Date"){
System.out.println("測試date 字段"+column.get(i)+"value :"+rs.getDate(column.get(i)));
s.addCell(new jxl.write.DateTime(i , k, rs.getDate(column.get(i)),(CellFormat)cellstyleMap.get("dateStyle")));
}else if(sqltypeMap.get(column.get(i))=="int"){
s.addCell(new Label(i,k,String.valueOf(rs.getInt(column.get(i))),(CellFormat)cellstyleMap.get("cellbackcolorstyle")));
}
}else{
if(sqltypeMap.get(column.get(i))=="String"){
s.addCell(new Label(i,k,rs.getString(column.get(i)),(CellFormat)cellstyleMap.get("titleStyle")));
}else if(sqltypeMap.get(column.get(i))=="Date"){
System.out.println("測試void狀態下的date 字段--》"+column.get(i)+"value :"+rs.getDate(column.get(i)));
s.addCell(new jxl.write.DateTime(i , k, rs.getDate(column.get(i)),(CellFormat)cellstyleMap.get("dateStyle")));
}else if(sqltypeMap.get(column.get(i))=="int"){
s.addCell(new Label(i,k,String.valueOf(rs.getInt(column.get(i))),(CellFormat)cellstyleMap.get("titleStyle")));
}
}
}
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
}
k =3;
} catch (SQLException e) {
e.printStackTrace();
}
}
}