jsp 中poi 实现导入导出excel

本例试用poi 3.0,jdk1.4,weblogic8.13,sqlserver2000

1.excel导入数据

导入时候注意数字类型和字符类型的区别，数字类型要在excel中设置数字类型，对应数据库中也应该建立对应类型

<%@ page import="com.guanri.database.*,java.util.*,com.guanri.util.*,com.jspsmart.upload.*" contentType="text/html; charset=GBK" %>
<%@ page import="java.util.*,java.io.*,org.apache.poi.poifs.filesystem.*,org.apache.poi.hssf.usermodel.*" %>
<jsp:include page="/admin/checkLogin.jsp"/>
<%

 

String result = "";
String contracId = "";
String projectname = "";
double projectscale = 0;
String projecttype="";
String projectconunit="";
double designtime = 0;
String designphase = "";
double completedtime = 0;
String participants = "";
String positionofpro = "";
String dutiesas = "";

int inum = 0;
int unum = 0;
int rnum = 0;
int snum = 0;
String rmstr = "";

Table tb = new Table();
Vector vc = null;
Hashtable rows = new Hashtable();
String sql = "";

if(request.getMethod().equalsIgnoreCase("POST")) {
 String sdir = "/download/xlsdir/";  //文件保存目录
 String  filesep=System.getProperty("file.separator");
 String ssdir = filesep+"download"+filesep+"xlsdir"+filesep;
 Calendar c = Calendar.getInstance(); 
 String fname = c.getTimeInMillis()+".xls";
 String sfname = sdir + fname;
 String ss = "xls"; //支持的文件后缀名
 try{
  SmartUpload su = new SmartUpload();
  su.initialize(pageContext);
  su.setAllowedFilesList(ss);
  su.upload();
  com.jspsmart.upload.File file = su.getFiles().getFile(0);
  if(!file.isMissing()){  
   file.saveAs(sfname);
 //  System.out.println(application.getRealPath("/")+ssdir+fname );
    FileInputStream finput = new FileInputStream(application.getRealPath("/")+ssdir+fname );
    //设定FileINputStream读取Excel档
    POIFSFileSystem fs = new POIFSFileSystem( finput );
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);
    //读取第一个工作表，宣告其为sheet
    finput.close();
    HSSFRow row=null;
    //宣告一列
    HSSFCell cell=null;
    //宣告一个储存格
    short i=0;
    short y=0;
 
    //以巢状迴圈读取所有储存格资料
    System.out.println("sheet.getLastRowNum()="+sheet.getLastRowNum());
    for (i=1;i<=sheet.getLastRowNum();i++)
    {
      row=sheet.getRow(i);
      for (y=1;y<=row.getLastCellNum();y++)
      {
         cell=row.getCell(y);
         switch(y){
           case 1:   //合同号
            if(cell!=null){
             contracId=(cell.getStringCellValue()).trim();
            }
            break;
           case 2:   //项目名