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对象对来解析excelArrayList list = filePreviewMsg.getPreviewMsgList(path);//解析excelBatchLook 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 {//读取EXCELpublic 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;//企业IDprivate 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;}}