jsp使用SmartUpload上傳EXCEL並儲存到資料庫.

batchlook.jsp代碼：<form name="form1" action="uploadtoserver.jsp" method="post" ENCTYPE="multipart/form-data"> //一定要加上 ENCTYPE="multipart/form-data" 上傳excel<input type="file" name="TEST" SIZE="40" id="upfile"> <input type="submit" value="上傳"> </form>

uploadtoserver.jsp代碼<%@ page contentType="text/html;charset=gbk" %> <%@ page import="com.upload.jspsmart.upload.*"%> <jsp:useBean id="mySmartUpload" scope="page" class="com.upload.jspsmart.upload.SmartUpload" /> <%@page import="sms.batch.FilePreviewMsg"%> <%@page import="java.util.ArrayList"%> <%@page import="sms.batch.BatchLook"%> <%@page import="sms.batch.BatchLookDao"%> <%@page import="java.util.Date"%> <%@page import="java.util.List"%> <%@page import="java.text.SimpleDateFormat"%> <%@page import="java.io.PrintWriter"%> <% mySmartUpload.initialize(pageContext); //上傳檔案初始化 mySmartUpload.upload();//上傳檔案 String path=pageContext.getServletContext().getRealPath(""); path=path.substring(0,path.lastIndexOf("//")); path=path+"//data//sendfiles//";//上傳檔案路徑 com.upload.jspsmart.upload.File myFile = mySmartUpload.getFiles().getFile(0);//得到上傳的檔案 String fileName=myFile.getFileName(); java.text.SimpleDateFormat simpleDateFormat = new java.text.SimpleDateFormat("yyyyMM");//日期轉換，用來命名檔案 String strDate = simpleDateFormat.format(new Date());//以日期命名檔案 fileName = fileName.substring(fileName.lastIndexOf("."),fileName.length()); fileName=strDate+fileName; java.io.File file = new java.io.File(path);//在伺服器上建立目錄 if(!file.isDirectory()){ file.mkdirs(); } path= path + fileName;//檔案的完整路徑 if(file .exists()){ myFile.saveAs(path);//將上傳的檔案儲存到伺服器 FilePreviewMsg filePreviewMsg = new FilePreviewMsg();//jxl對象對來解析excel ArrayList list = filePreviewMsg.getPreviewMsgList(path);//解析excel BatchLook batchLook;//每一行即一個看板對象 //刪除之前置入的 batchLook =(BatchLook)list.get(0); batchLookDao.save(batchLook)//資料庫操作，儲存到資料庫 } %>

FilePreviewMsg.java代碼：package sms.batch; import java.io.*; import java.net.Authenticator; import java.net.HttpURLConnection; import java.net.URL; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Vector; import com.common.GlobalValueManager; import jxl.*; import jxl.format.Alignment; import jxl.format.Colour; import jxl.format.UnderlineStyle; import jxl.format.VerticalAlignment; import jxl.read.biff.BiffException; 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; /* * author lzf by 2010-10-21 * jxl讀取上傳excel內容 */ public class FilePreviewMsg { //讀取EXCEL public ArrayList getPreviewMsgList(String fileName) { System.out.println(fileName); ArrayList list = new ArrayList(); SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy"); SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd"); try { Workbook book = Workbook.getWorkbook(new File(fileName)); Sheet sheet = book.getSheet(0); // 獲得第一個工作表對象 int rows = sheet.getRows(); BatchLook batchLook ; for (int i = 0; i < rows - 1; i++) { Cell[] cell = sheet.getRow(i); batchLook = new BatchLook(); if (cell.length == 0) { continue; } if(sheet.getCell(0, 0).getContents().trim().contains("日期")){ batchLook.setBatch_look_date(sdf1.format(sdf.parse(sheet.getCell(0, i+1).getContents().trim()))); } if(sheet.getCell(1, 0).getContents().trim().contains("預測需求")){ batchLook.setBefor_need(Integer.parseInt(sheet.getCell(1, i+1).getContents().trim())); } if(sheet.getCell(2, 0).getContents().trim().contains("承接需求")){ batchLook.setContinue_need(Integer.parseInt(sheet.getCell(2, i+1).getContents().trim())); } if(sheet.getCell(3, 0).getContents().trim().contains("承接能力")){ batchLook.setContinue_ability(Integer.parseInt(sheet.getCell(3, i+1).getContents().trim())); } if(sheet.getCell(4, 0).getContents().trim().contains("剩餘資源")){ batchLook.setLeave_resource(Integer.parseInt(sheet.getCell(4, i+1).getContents().trim())); } list.add(batchLook); } } catch (BiffException e) { System.out.println("excel 不符合格式"); e.printStackTrace(); } catch (IOException e) { System.out.println("excel 不符合格式"); e.printStackTrace(); }catch(Exception ex){ ex.printStackTrace(); } return list; } public void exportExcel(String fileName,ArrayList content) { // String fileName = "D://myexcel2.xls" ; WritableWorkbook wwb; FileOutputStream fos; try { fos = new FileOutputStream(fileName); wwb = Workbook.createWorkbook(fos); WritableSheet ws = wwb.createSheet("sheet1",100); // 建立一個工作表 // 設定儲存格的文字格式 WritableFont wf = new WritableFont(WritableFont.ARIAL,12,WritableFont.NO_BOLD,false, UnderlineStyle.NO_UNDERLINE,Colour.BLUE); WritableCellFormat wcf = new WritableCellFormat(wf); wcf.setVerticalAlignment(VerticalAlignment.CENTRE); wcf.setAlignment(Alignment.CENTRE); ArrayList list; for(int i=0;i<content.size();i++){ list = (ArrayList)content.get(i); if(i == 0) wcf = new WritableCellFormat(); for(int j=0;j<list.size();j++){ ws.addCell(new Label(j,i , list.get(j).toString(), wcf)); } } wwb.write(); wwb.close(); } catch (IOException e){ } catch (RowsExceededException e){ } catch (WriteException e){} } }  

BatchLook.javapackage sms.batch; /** 記錄月度資源區 * @author lzf * */ public class BatchLook { private int batch_look_id; //群發項目編號 private int ent_id;//企業ID private String batch_look_date;//日期 private int befor_need;//預測需求 private int continue_need;//承接需求 private int continue_ability;//承接能力 private int leave_resource ;//剩餘資源 private String resource_vlaue;//資源伐值 public String getResource_vlaue() { return resource_vlaue; } public String getResource_vlaue_color() { if (resource_vlaue == null || resource_vlaue.equals("")){ return "color : #FF9933"; }else if(Float.parseFloat(resource_vlaue) < 0){ return "color:red"; }else if( Float.parseFloat(resource_vlaue) >1000){ return "color:green"; }else if( Float.parseFloat(resource_vlaue) >=0 && Float.parseFloat(resource_vlaue)<100 ){ return "color : #FF9933"; }else{ return ""; } } public void setResource_vlaue(String resourceVlaue) { resource_vlaue = resourceVlaue; } public int getBatch_look_id() { return batch_look_id; } public void setBatch_look_id(int batchLookId) { batch_look_id = batchLookId; } public int getEnt_id() { return ent_id; } public void setEnt_id(int entId) { ent_id = entId; } public String getBatch_look_date() { return batch_look_date; } public void setBatch_look_date(String batchLookDate) { batch_look_date = batchLookDate; } public int getBefor_need() { return befor_need; } public void setBefor_need(int beforNeed) { befor_need = beforNeed; } public int getContinue_need() { return continue_need; } public void setContinue_need(int continueNeed) { continue_need = continueNeed; } public int getContinue_ability() { return continue_ability; } public void setContinue_ability(int continueAbility) { continue_ability = continueAbility; } public int getLeave_resource() { return leave_resource; } public void setLeave_resource(int leaveResource) { leave_resource = leaveResource; } }