jsp中Excel导入问题

我的资源里分享了个 jsp 导出 excel 源码，还有个 java 解析 execl 至 jsp 源码、java 版 excel 导入数据库源码
楼主去看看，参考下

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>EB页面导出为EXCEL文档的方法</title>
<script type="text/javascript">

</script>
</head> <body>
<div id="tableExcel">
<table id="test" width="100%" border="1" cellspacing="0" cellpadding="0">
      <tr>
          <td colspan="5" align="center">WEB页面导出为EXCEL文档的方法</td>
      </tr>
      <tr>
          <td>列标题1</td>
          <td>列标题2</td>
          <td>列标题3</td>
          <td>列标题4</td>
          <td>列标题5</td>
      </tr>
      <tr>
          <td>aaa</td>
          <td>bbb</td>
          <td>ccc</td>
          <td>ddd</td>
          <td>eee</td>
      </tr>
      <tr>
          <td>AAA</td>
          <td>BBB</td>
          <td>CCC</td>
          <td>DDD</td>
          <td>EEE</td>
      </tr>
      <tr>
          <td>FFF</td>
          <td>GGG</td>
          <td>HHH</td>
          <td>III</td>
          <td>JJJ</td>
      </tr>
      <tr>
        <td>aaa</td>
        <td>bbb</td>
        <td>ccc</td>
        <td>ddd</td>
        <td>eee</td>
      </tr>
      <tr>
        <td>AAA</td>
        <td>BBB</td>
        <td>CCC</td>
        <td>DDD</td>
        <td>EEE</td>
      </tr>
      <tr>
        <td colspan="5">FFFGGGHHHIIIJJJ</td>
      </tr>

      <tr>
        <td>aaa</td>
        <td>bbb</td>
        <td>ccc</td>
        <td>ddd</td>
        <td>eee</td>
      </tr>
      <tr>
        <td>AAA</td>
        <td>BBB</td>
        <td rowspan="4">CCCHHHcccccc</td>
        <td>DDD</td>
        <td>EEE</td>
      </tr>
      <tr>
        <td>FFF</td>
        <td>GGG</td>
        <td>III</td>
        <td>JJJ</td>
      </tr>

      <tr>
        <td>aaa</td>
        <td>bbb</td>
        <td>ddd</td>
        <td>eee</td>
      </tr>
      <tr>
        <td>aaa</td>
        <td>bbb</td>
        <td>ddd</td>
        <td>eee</td>
      </tr>
      <tr>
        <td>AAA</td>
        <td>BBB</td>
        <td>CCC</td>
        <td>DDD</td>
        <td>EEE</td>
      </tr>
      <tr>
        <td>FFF</td>
        <td>GGG</td>
        <td>HHH</td>
        <td>III</td>
        <td>JJJ</td>
      </tr>
</table>
</div>
<input type="button" value="另存为 Excel" onclick="saveCode(tableExcel)">
</body>
</html>

jsp有插件的吧~
去google看看~

我呢，还是按照以前的惯例------开源框架。如果是office 2007向后的，推荐使用 Apache POI
如果是office 2003向前的，可以使用 jexcelapi, 也可以使用Apache POI ，不过它的Jar包相对于jexcelapi有点的。

给大家一个类
package pms2.gdzc;import java.sql.*;
import java.io.*;
import jxl.*;
import jxl.write.*;
import common.Global;
import pms2.uploadfile.*;
import javax.servlet.http.*;
import log.Logger;
import java.util.*;
/**
* Author ct
* Create 2009-07-15
* 用于Excel 转换
*
*/
public class ExcelConvers{

private Connection con = null;
private Statement st = null;
private ResultSet rs = null;
private String sql = null;
private pms2.uploadfile.UploadFile uf = null; private String filename = ""; private List list = new ArrayList();

public List importDetail(HttpServletRequest request,HttpServletResponse response,Integer radioValue) throws Exception{

if(request == null || request.getContentType().toLowerCase().indexOf("multipart/form-data") == -1){
throw new Exception("Exception at importDetail: Not request found or request is not multipart/form-data type");
}
//String path = request.getParameter("filename");
//String source_path = request.getSession().getServletContext().getRealPath("/files/upload/") + "/a.xls";
jxl.Workbook wb = null;
jxl.Sheet sheet = null;
try{

uf = new UploadFile(request,response);
uf.save();

if(uf.getFiles().length < 1){
return null;
}
filename = uf.getFileName();//得到文件名字
wb = jxl.Workbook.getWorkbook(new File(uf.getFullFileName()));

//InputStream is = new FileInputStream(new File(source_path));
//wb = jxl.Workbook.getWorkbook(is);
//if(radioValue.intValue()==0){ //如果是0施工阶段信息；1是网元信息
sheet = wb.getSheet(0);
int rows = sheet.getRows();
Importdata data0 = new Importdata();
list.add(data0);
list.add(data0);
list.add(data0);
for(int i=2;i<rows;i++){//从第三行开始有数据
Importdata data = new Importdata(); data.zy = sheet.getCell(0,i).getContents().trim();
data.xmzt = sheet.getCell(1,i).getContents().trim();
data.gcmc = sheet.getCell(2,i).getContents().trim();
data.gcbh = sheet.getCell(3,i).getContents().trim();
data.lxsj = sheet.getCell(4,i).getContents().trim();
data.lxje = sheet.getCell(5,i).getContents().trim();
data.ljtzwce = sheet.getCell(6,i).getContents().trim();
data.sgcgly = sheet.getCell(7,i).getContents().trim();
data.sgdw = sheet.getCell(8,i).getContents().trim();
data.jhcysj = sheet.getCell(9,i).getContents().trim();
data.cysj = sheet.getCell(10,i).getContents().trim();
data.jhzysj = sheet.getCell(11,i).getContents().trim(); if(data!=null){
list.add(data);
}
}

}catch(Exception ex){
throw new Exception("Exception at importDetail:" + ex.toString());
}finally{
if(wb != null){
wb.close();
wb = null;
}
}
return list;
}

public String exportDetail(HttpServletRequest request,HttpServletResponse response,List list) throws Exception{

Importdata []data = (Importdata[])list.toArray(new Importdata[list.size()]);
PrintWriter out = response.getWriter();
String source_path = request.getSession().getServletContext().getRealPath("/files/upload/") + File.separator + filename;
String target_path = request.getSession().getServletContext().getRealPath("/files/download/") + File.separator + request.getRemoteAddr() + ".xls"; FileOutputStream target_file = null;
jxl.Workbook owb = null;
jxl.write.WritableWorkbook wb = null;
jxl.write.WritableSheet sheet = null;
try{

target_file = new FileOutputStream(target_path);

owb = Workbook.getWorkbook(new File(source_path));
wb = Workbook.createWorkbook(target_file,owb);
sheet = wb.getSheet(0);

con = (new common.DatabaseConnectionPool()).getConnection();
st = con.createStatement();

for(int i=3;i<data.length;i++ ){

String gcbh = data[i].gcbh.trim();

java.lang.Long gc_id =null;
sql = "select t.id, t2.name from td00_zhxx t,ta03_employee t2 where t.xmjl_id = t2.id and gcbh ='"+gcbh+"'";
rs = st.executeQuery(sql);
if(rs.next()){
if(rs.getString(2)!=null && !rs.getString(2).equals("")){
data[i].xmjl = rs.getString(2);//得到项目经理
}else{
data[i].xmjl = new String("");
} if(rs.getBigDecimal(1)!=null){
gc_id = new java.lang.Long(String.valueOf(rs.getBigDecimal(1)));
}
}

if(gc_id!=null){
//取工程的最后经过的节点ID：
java.lang.Long lastid = null;
sql = "select node_id from tb05_operflowseq where id = (select max(id) from tb05_operflowseq "
+" where node_id in(select t.id from tb02_operflownode t,ta04_role t1 "
+" where t.role_id = t1.id and t1.module_id >100 and t1.module_id < 112) and gc_id ="+gc_id + ")";
rs = st.executeQuery(sql);
if(rs.next()){
if(rs.getBigDecimal(1)!=null){
lastid = new java.lang.Long(String.valueOf(rs.getBigDecimal(1)));
}
if(lastid!=null){
//response.getWriter().println("lastid"+lastid);
// 表单名称－节点名称－办理状态
String bdmc = "";
String jdmc = "";
String blzt = "";
sql = "select t3.name,t1.name,t.node_status from tb05_operflowseq t,tb02_operflownode t1,ta04_role t2,ta05_module t3 "
+" where t.node_id = t1.id and t1.role_id = t2.id and t2.module_id = t3.id and t1.id ="+lastid ;
rs = st.executeQuery(sql);
if(rs.next()){
bdmc = rs.getString(1);
jdmc = rs.getString(2);
if(rs.getInt(3)==1){
blzt = new String("新建");
}else if(rs.getInt(3)==2){
blzt = new String("发送");
}else if(rs.getInt(3)==6){
blzt = new String("待办");
}else if(rs.getInt(3)==7){
blzt = new String("在办");
}else if(rs.getInt(3)==8){
blzt = new String("办结");
}else{
blzt = new String("回复");
} }

data[i].ggzt= new String(bdmc+"-"+jdmc.trim()+"-"+blzt);
}
}else{
data[i].ggzt = new String("");
}

}else{
data[i].ggzt = new String("");
}
sheet.addCell(new jxl.write.Label(13,i-1,data[i].ggzt,this.getTextCellAlignLeftFormat()));
sheet.addCell(new jxl.write.Label(14,i-1,data[i].xmjl,this.getTextCellAlignLeftFormat())); }

wb.write();
wb.close();
owb.close();
owb = null; return new String("/pmsee/files/download/"+ File.separator + request.getRemoteAddr() + ".xls");
}catch(Exception ex){
return ("Error:" +ex.getMessage());
}finally{
if(target_file != null){
target_file.close();
target_file = null;
}
this.finalize();
}

}

public void finalize() throws Exception{
if(rs != null){
rs.close();
rs = null;
}
if(st != null){
st.close();
st = null;
}
if(con != null){
con.close();
con = null;
}
if(uf != null){
uf.destory();
uf = null;
}
}

class Importdata{
public String zy;//专业
public String xmzt;//项目状态
public String gcmc;//工程名称
public String gcbh;//工程编号
public String lxsj;//立项时间
public String lxje;//立项金额
public String ljtzwce;//累计投资完成额
public String sgcgly;//市工程管理员
public String sgdw; //施工单位
public String jhcysj;//计划初验时间
public String cysj;//初验时间
public String jhzysj;//计划终验时间 public String ggzt;//公管状态
public String xmjl;//项目经理
}
/**
* 设定文本域格式wcf_Text，有边框，居中对齐
*/
private WritableCellFormat getTextCellAlignLeftFormat() throws WriteException {
WritableFont wf = new WritableFont(WritableFont.createFont("宋体"), 9, WritableFont.NO_BOLD, false);
WritableCellFormat wcf_Text = new WritableCellFormat(wf);
wcf_Text.setAlignment(Alignment.LEFT);
wcf_Text.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf_Text.setBorder(Border.ALL, BorderLineStyle.THIN);
return wcf_Text;
}}

调试易

jsp中Excel导入问题

解决方案 »