java excel导入建表_excel 文件导入数据库(java)

excel文件导入数据库！

先上传，后解析，再对sql建表，插入数据，导入完成，删除上传的临时表！

谢谢指点！

1.导入jar

jxl.jar

poi-poi-2.5.1jar

poi-contrib-2.5.1.jar

poi-scratchpad-2.5.1.jar

struts2.0的包

2.配置web.xml

xmlns="http://java.sun.com/xml/ns/j2ee"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee

http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">

index.jsp

struts

org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter

struts

/*

在web-app下新建一个upload文件夹用于放置临时文件

3.struts.xml

/p>

"-//Apache Software Foundation//DTD Struts Configuration 2.1//EN"

"http://struts.apache.org/dtds/struts-2.1.dtd">

jiexi

/MyJsp.jsp

4.FileUploadAction.java

package action;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.io.OutputStream;

import org.apache.struts2.ServletActionContext;

import com.opensymphony.xwork2.ActionSupport;

public class FileUploadAction extends ActionSupport{

private File file;

public File getFile() {

return file;

}

public void setFile(File file) {

this.file = file;

}

public String getFileFileName() {

return fileFileName;

}

public void setFileFileName(String fileFileName) {

this.fileFileName = fileFileName;

}

public String getFileContentType() {

return fileContentType;

}

public void setFileContentType(String fileContentType) {

this.fileContentType = fileContentType;

}

private String fileFileName;

private String fileContentType;

public String upload(){

try {

InputStream is= new FileInputStream(file);

String root=ServletActionContext.getServletContext().getRealPath("/upload");

File destFile=new File(root, this.getFileFileName());

OutputStream os=new FileOutputStream(destFile);

byte[] buffer = new byte[1024];

int index = 0;

try {

while ((index = is.read(buffer)) > 0) {

os.write(buffer, 0, index);

}

is.close();

os.close();

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

} catch (FileNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return "scsucc";

}

}

5.FileJieXiAction.java

package action;

import java.io.File;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Statement;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

import org.apache.struts2.ServletActionContext;

import util.ConnDB;

import com.opensymphony.xwork2.Action;

public class FileJieXiAction implements Action{

private File file;

public File getFile() {

return file;

}

public void setFile(File file) {

this.file = file;

}

public String getFileFileName() {

return fileFileName;

}

public void setFileFileName(String fileFileName) {

this.fileFileName = fileFileName;

}

private String fileFileName;

public String execute() throws Exception {

// TODO Auto-generated method stub

String realpath=ServletActionContext.getServletContext().getRealPath("/upload");

Workbook wb=Workbook.getWorkbook(new File(realpath+"/"+fileFileName));

Sheet sheet=wb.getSheet(0);

int rowNum=sheet.getRows();

int colNum=sheet.getColumns();

System.out.println("colNum:"+colNum);

//  //建表

String createTableSql=getCreateTableSql(rowNum,colNum,sheet);

Connection conn=ConnDB.getConnection();

System.out.println(createTableSql);

Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

st.execute(createTableSql);

st.close();

System.out.println("上传的文件："+fileFileName);

//获取数据进行插入操作?????????????????????

PreparedStatement ps = null;

for(int i=1;i

String insertSql=getInsertSql(i,colNum,sheet);

System.out.println(insertSql);

ps = conn.prepareStatement(insertSql);

ps.executeUpdate();

}

//删除临时文件

new File(realpath+"/"+fileFileName).delete();

ps.close();

conn.close();

return "jxsucc";

}

//配置插入数据的sql

private String getInsertSql(int i, int colNum, Sheet sheet) {

// TODO Auto-generated method stub

String insertSql="insert into "+fileFileName.substring(0, fileFileName.length()-4)+" values( "+i+",";//2=j+1

String temp="";

for(int j=0;j

Cell c= sheet.getCell(j,i);// "j,0" "i" 先插入一行数据//2=j+1

String strValue="'"+c.getContents();

System.out.println("strValue:"+ strValue);

temp=temp+strValue+"',";

}

insertSql=insertSql+temp.substring(0,temp.length()-1)+");";

return insertSql;

}

//配置创建表的sql

private String getCreateTableSql(int rowNum, int colNum ,Sheet st) {

// TODO Auto-generated method stub

String createTableSql="create table "+fileFileName.substring(0, fileFileName.length()-4)+" (id bigint primary key auto_increment,";

String temp="";

for(int j=0;j

Cell c= st.getCell(j,0);

String strValue=c.getContents();

temp=temp+strValue+" varchar(50) default null,";

}

createTableSql=createTableSql+temp.substring(0, temp.length()-1)+") ENGINE=InnoDB DEFAULT CHARSET=utf8;";

return createTableSql;

}

}

6.上传的页面 upload.jsp

String path = request.getContextPath();

String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

%>

My JSP 'index.jsp' starting page

文件上传

请选择文件：

第一次写！请高手多多指点…………感激不尽！！！！