关于 jsp servlet 将Excel表格内容写进数据库，将数据库导出到Excel中。（一）

最新推荐文章于 2018-12-17 16:24:02 发布

sunxylove

最新推荐文章于 2018-12-17 16:24:02 发布

阅读量1.3k

点赞数

转自：http://blog.chinaunix.net/uid-28794959-id-3773177.html

从EXCEL到数据库，引入poi.jar
jsp如下

点击(此处)折叠或打开

<form enctype="multipart/form-data" name=testform method=post action=Testaction>
<table>
<tr>
<td><font size=2>批量上传：</font><input type="file" name="test" size="10"><br></td>
<td><input type="submit" name="批量上传" size="10"value="批量上传"><br></td></tr></table><br>
</form>

Servlet如下

点击(此处)折叠或打开

package control;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.io.RandomAccessFile;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class Testaction extends HttpServlet {
/**
*
*/
private static final long serialVersionUID = 1L;
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
}
/**
* The doPost method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to post.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//接收上传文件内容中临时文件的文件名
String tempFileName = new String("tempFileName");
//tempfile 对象指向临时文件
File tempFile = new File("D:/"+tempFileName);
//outputfile 文件输出流指向这个临时文件
FileOutputStream outputStream = new FileOutputStream(tempFile);
//得到客服端提交的所有数据
InputStream fileSourcel = request.getInputStream();
//将得到的客服端数据写入临时文件
byte b[] = new byte[1000];
int n ;
while ((n=fileSourcel.read(b))!=-1){
outputStream.write(b,0,n);
}
//关闭输出流和输入流
outputStream.close();
fileSourcel.close();
//randomFile对象指向临时文件
RandomAccessFile randomFile = new RandomAccessFile(tempFile,"r");
//读取临时文件的第一行数据
randomFile.readLine();
//读取临时文件的第二行数据，这行数据中包含了文件的路径和文件名
String filePath = randomFile.readLine();
System.out.println(filePath);
//得到文件名
int position = filePath.lastIndexOf('\\');
CodeToString codeToString = new CodeToString();
String filename = codeToString.codeString(filePath.substring(position,filePath.length()-1));
//重新定位读取文件指针到文件头
randomFile.seek(0);
//得到第四行回车符的位置，这是上传文件数据的开始位置
long forthEnterPosition = 0;
int forth = 1;
while((n=randomFile.readByte())!=-1&&(forth<=4)){
if(n=='\n'){
forthEnterPosition = randomFile.getFilePointer();
forth++;
}
}
//生成上传文件的目录
File fileupLoad = new File("F:/MyEclipse/Manager/WebRoot/file","upLoad");
fileupLoad.mkdir();
//saveFile 对象指向要保存的文件
File saveFile = new File("F:/MyEclipse/Manager/WebRoot/file/upLoad",filename);
RandomAccessFile randomAccessFile = new RandomAccessFile(saveFile,"rw");
//找到上传文件数据的结束位置，即倒数第四行
randomFile.seek(randomFile.length());
long endPosition = randomFile.getFilePointer();
int j = 1;
while((endPosition>=0)&&(j<=4)){
endPosition--;
randomFile.seek(endPosition);
if(randomFile.readByte()=='\n'){
j++;
}
}
//从上传文件数据的开始位置到结束位置，把数据写入到要保存的文件中
randomFile.seek(forthEnterPosition);
long startPoint = randomFile.getFilePointer();
while(startPoint<endPosition){
randomAccessFile.write(randomFile.readByte());
startPoint = randomFile.getFilePointer();
}
randomAccessFile.close();
randomFile.close();
tempFile.delete();
TestExcel t=new TestExcel();
t.add();
}
}

真正的核心代码，分析EXCEL

点击(此处)折叠或打开

package control;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.PrintWriter;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import db.DB;
import db.Test_table;
public class TestExcel {
//记录类的输出信息?
static Log log = LogFactory.getLog(TestExcel.class);
//获取Excel文档的路径?
public static String filePath = "F://MyEclipse//Manager//WebRoot//file//upLoad//test.xls";
public void add() {
try {
// 创建对Excel工作簿文件的引用?
HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(filePath));
// 在Excel文档中，第一张工作表的缺省索引是0
// 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);?
HSSFSheet sheet = wookbook.getSheet("Sheet1");
//获取到Excel文件中的所有行数
int rows = sheet.getPhysicalNumberOfRows();
//遍历行
for (int i = 0; i < rows; i++) {
// 读取左上端单元格?
HSSFRow row = sheet.getRow(i);
// 行不为空
if (row != null) {
//获取到Excel文件中的所有的列?
int cells = row.getPhysicalNumberOfCells();
String value = "";
//遍历列?
for (int j = 0; j < cells; j++) {
//获取到列的值?
HSSFCell cell = row.getCell(j);
if (cell != null) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
break;
case HSSFCell.CELL_TYPE_NUMERIC:
value += cell.getNumericCellValue() + ",";
break;
case HSSFCell.CELL_TYPE_STRING:
value += cell.getStringCellValue() + ",";
break;
default:
value += "0";
break;
}
}
}
// 将数据插入到sqlserver数据库中
String[] val = value.split(",");
DB db=new DB();
Test_table jBean=new Test_table();
String sql ="insert into test_table(num1,num2,num3) values('"+val[0]+"','"+val[1]+"','"+val[2]+"')";
int count=db.getInserttest(sql, jBean);
System.out.println("------------------"+sql);
if(count>0){
//关闭文件输入、输出
}
}}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}

DB数据库连接类

点击(此处)折叠或打开

public class DB {
private static DB db = null;
private static Connection conn = null;
private static PreparedStatement pstmt = null;
private static Statement stmt = null;
//单例模式,new DB类的时候,创建唯一对象,只初始化一次,
//注意:不要显式闭此static中的Connection和Statement对象,否则抛空指针异常
static{
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn=DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=Market" ,"sa","a");
stmt = conn.createStatement();
System.out.println("--------初始化---------");
} catch (ClassNotFoundException e) {
System.out.println("---------- 加载数据库驱动类时发生异常: ----------");
e.printStackTrace();
} catch (SQLException e) {
System.out.println("------------ getConnection()方法发生异常--------------");
}
}
public PreparedStatement prepareStmt(String sql){
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(sql);
} catch (SQLException e) {
System.out.println("-------------prepareStmt()方法发生异常-------------------");
e.printStackTrace();
}
return pstmt;
}
//执行查询所有记录操作
public ResultSet exeQuery(String sql){
ResultSet rs = null;
try {
rs = stmt.executeQuery(sql);
} catch (SQLException e) {
System.out.println("------------exeQuery()方法发生异常: --------------------");
e.printStackTrace();
}
return rs;
}
public void exeUpdate(String sql){
try{
stmt.executeUpdate(sql);
} catch(SQLException e){
System.out.println("------------- exeUpdate()方法发生异常------------------");
e.printStackTrace();
}
}
//关闭PreparedStatement对象
public void closePstmt(PreparedStatement pstmt){
try{
pstmt.close();
pstmt = null;
}catch(SQLException e){
System.out.println("-------------------- DB.closePstmt()方法发生异常 -------------------------");
e.printStackTrace();
}
}
//关闭ResultSet对象
public void closeRs(ResultSet rs){
try{
if(rs!=null)
rs.close();
// rs = null;
}catch(SQLException e){
System.out.println("-------------------- DB.closePstmt()方法发生异常 -------------------------");
e.printStackTrace();
}
}
public Connection getDB()
{
return conn ;
}