package com.software.cntrrep.EDI;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
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.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import com.software.cntrrep.dao.EDIBASEINFO;
import com.software.platform.db.ConnectionManager;
import com.software.platform.db.DatabaseConnection;
import com.software.platform.db.RecordSet;
import com.software.platform.utils.BusinessDate;
import com.software.platform.utils.Util;
public class ImpEDI extends HttpServlet {
private String msg = "";
@SuppressWarnings("unchecked")
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String ids = request.getParameter("ids");
String cntruser = request.getParameter("CntrUser");
ArrayList arraylist= new ArrayList();
String[] arrcntr=cntruser.split(",");
try {
for(String cntr:arrcntr){
arraylist.add(cntr);
}
if(!Util.isSameArray(arraylist)){ //判断是否为同一用箱人
msg="ERROR:Not single CntrUser !";
}
if (arrcntr[0].equals("MSC")) {
response.setHeader("Content-Disposition","attachment;filename=MSC_"+ BusinessDate.getNoFormatTime() + ".xls");
response.setContentType("application/vnd.ms-excel");
OutputStream os = response.getOutputStream();
WritableWorkbook wwb = Workbook.createWorkbook(os);
WritableSheet wsheet = wwb.createSheet("MSC’s EDI", 1);
try {
WritableFont wfont = new WritableFont(WritableFont.ARIAL,8, WritableFont.BOLD, false,
jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);
WritableCellFormat titleFormat = new WritableCellFormat(wfont);
titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
titleFormat.setAlignment(Alignment.CENTRE);
// 设置Excel表头
String[] title = { "Container#", "Depot", "ISO", "Vessel","Voyage","POL", "Date In" , "Estimate Date", "Estimate#"
, "Seq", "Job", "Cash", "Component", "Repair", "Description", "Material", "S1", "S2", "Unit", "Quantity"
, "IX", "Damage", "Hourly Rate", "Hrs", "Mat Amt", "Cur", "Total"};
for (int i = 0; i < title.length; i++) {
wsheet.setColumnView(1, 20); // 设置单元格宽度
Label excelTitle = new Label(i, 0, title[i],titleFormat);
wsheet.addCell(excelTitle);
}
String[] arrid=ids.split(",");
ConnectionManager cm = ConnectionManager.getInstance();
DatabaseConnection dc = cm.get();
try {
int a=1;
for(String id:arrid){
String sql=" select CNTRNO,'QDLH' ,'4310',VESSELNAME,'Voyage','POL'," +
"GATEINDATE," +
"ESTDATE,TRNSXN " +
" from EDIBASEINFO where billno='"+id+"'";
RecordSet recorda=dc.executeQuery(sql);
if(recorda.next()){
String sql2=" select rownum seq,'O','A', PARTCODE,REPCODE,DMGDESC,MATCODE,LENGTH,WIDTH,'CMT' Unit,QUANTITY,LOCCODE,DMGCODE,LABHOUR," +
" LABCOST,MATCOST,'USD',TOTALFEE from EDIGJINFO where billno='"+id+"'";
RecordSet recordb=dc.executeQuery(sql2);
while(recordb.next()){
if(recordb.isFirst()){
for(int j=0;j<9;j++){
Label excelcell=new Label(j,a,recorda.getString(j)==null?"":recorda.getString(j),titleFormat);
wsheet.addCell(excelcell);
}
}else{
for(int j=0;j<9;j++){
Label excelcell=new Label(j,a,"",titleFormat);
wsheet.addCell(excelcell);
}
}
for(int k=9;k<title.length;k++){
wsheet.setColumnView(1, 20);
Label excelcell=new Label(k,a,recordb.getString(k-9),titleFormat);
wsheet.addCell(excelcell);
}
a++;
}
recordb.close();
}
recorda.close();
}
} catch (RuntimeException e) {
e.printStackTrace();
}finally{
dc.close();
}
wwb.write();
wwb.close();
os.close();
response.flushBuffer();
} catch (RowsExceededException e) {
e.printStackTrace();
e.getMessage();
} catch (WriteException e) {
e.printStackTrace();
e.getMessage();
}
}
} catch (RuntimeException e) {
e.printStackTrace();
e.getMessage();
}
}
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
}
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
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.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import com.software.cntrrep.dao.EDIBASEINFO;
import com.software.platform.db.ConnectionManager;
import com.software.platform.db.DatabaseConnection;
import com.software.platform.db.RecordSet;
import com.software.platform.utils.BusinessDate;
import com.software.platform.utils.Util;
public class ImpEDI extends HttpServlet {
private String msg = "";
@SuppressWarnings("unchecked")
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String ids = request.getParameter("ids");
String cntruser = request.getParameter("CntrUser");
ArrayList arraylist= new ArrayList();
String[] arrcntr=cntruser.split(",");
try {
for(String cntr:arrcntr){
arraylist.add(cntr);
}
if(!Util.isSameArray(arraylist)){ //判断是否为同一用箱人
msg="ERROR:Not single CntrUser !";
}
if (arrcntr[0].equals("MSC")) {
response.setHeader("Content-Disposition","attachment;filename=MSC_"+ BusinessDate.getNoFormatTime() + ".xls");
response.setContentType("application/vnd.ms-excel");
OutputStream os = response.getOutputStream();
WritableWorkbook wwb = Workbook.createWorkbook(os);
WritableSheet wsheet = wwb.createSheet("MSC’s EDI", 1);
try {
WritableFont wfont = new WritableFont(WritableFont.ARIAL,8, WritableFont.BOLD, false,
jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);
WritableCellFormat titleFormat = new WritableCellFormat(wfont);
titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
titleFormat.setAlignment(Alignment.CENTRE);
// 设置Excel表头
String[] title = { "Container#", "Depot", "ISO", "Vessel","Voyage","POL", "Date In" , "Estimate Date", "Estimate#"
, "Seq", "Job", "Cash", "Component", "Repair", "Description", "Material", "S1", "S2", "Unit", "Quantity"
, "IX", "Damage", "Hourly Rate", "Hrs", "Mat Amt", "Cur", "Total"};
for (int i = 0; i < title.length; i++) {
wsheet.setColumnView(1, 20); // 设置单元格宽度
Label excelTitle = new Label(i, 0, title[i],titleFormat);
wsheet.addCell(excelTitle);
}
String[] arrid=ids.split(",");
ConnectionManager cm = ConnectionManager.getInstance();
DatabaseConnection dc = cm.get();
try {
int a=1;
for(String id:arrid){
String sql=" select CNTRNO,'QDLH' ,'4310',VESSELNAME,'Voyage','POL'," +
"GATEINDATE," +
"ESTDATE,TRNSXN " +
" from EDIBASEINFO where billno='"+id+"'";
RecordSet recorda=dc.executeQuery(sql);
if(recorda.next()){
String sql2=" select rownum seq,'O','A', PARTCODE,REPCODE,DMGDESC,MATCODE,LENGTH,WIDTH,'CMT' Unit,QUANTITY,LOCCODE,DMGCODE,LABHOUR," +
" LABCOST,MATCOST,'USD',TOTALFEE from EDIGJINFO where billno='"+id+"'";
RecordSet recordb=dc.executeQuery(sql2);
while(recordb.next()){
if(recordb.isFirst()){
for(int j=0;j<9;j++){
Label excelcell=new Label(j,a,recorda.getString(j)==null?"":recorda.getString(j),titleFormat);
wsheet.addCell(excelcell);
}
}else{
for(int j=0;j<9;j++){
Label excelcell=new Label(j,a,"",titleFormat);
wsheet.addCell(excelcell);
}
}
for(int k=9;k<title.length;k++){
wsheet.setColumnView(1, 20);
Label excelcell=new Label(k,a,recordb.getString(k-9),titleFormat);
wsheet.addCell(excelcell);
}
a++;
}
recordb.close();
}
recorda.close();
}
} catch (RuntimeException e) {
e.printStackTrace();
}finally{
dc.close();
}
wwb.write();
wwb.close();
os.close();
response.flushBuffer();
} catch (RowsExceededException e) {
e.printStackTrace();
e.getMessage();
} catch (WriteException e) {
e.printStackTrace();
e.getMessage();
}
}
} catch (RuntimeException e) {
e.printStackTrace();
e.getMessage();
}
}
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
}