说明:POI导入是在 Rose框架里面做的,但没用到DAO层.Services 注入到 IndexController里很方便,但是要到数据源xml里配置 自动扫描包的配置.
这个导入功能没什么问题.就是导入数据到数据库时中文会乱码.困扰了我很久. 未能明白. 希望可以帮到正在接触到POI的童鞋们,同时也希望大虾们可以解我的疑惑,感激不尽.
poi版本 :poi-3.8.jar
1.项目结构:
2.IndexController.java
package com.poi.controllers;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import net.paoding.rose.web.Invocation;
import net.paoding.rose.web.annotation.Param;
import net.paoding.rose.web.annotation.Path;
import net.paoding.rose.web.annotation.rest.Get;
import net.paoding.rose.web.annotation.rest.Post;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.multipart.MultipartFile;
import com.poi.services.JDBCConn;
import com.poi.services.XlsMain;
import com.pojo.XlsDto;
/**
* Index 入口
* @author <a href="mailto:qihao@ssreader.cn">chenqihao</a>
*@version 2013-9-3
*/
@Path("index")
public class IndexController {
@Autowired
XlsMain slsmain;
@Get("toDb")
public String excelToDB() {
return "excelToDB";
}
/**
* POI导入Excel
* @param inv
* @param files
* @return
* @throws IOException
* @throws SQLException
*/
@SuppressWarnings("unchecked")
@Post("toDbGo")
public String toDBGo(Invocation inv, @Param("files") MultipartFile files) throws IOException, SQLException {
System.out.println(files.getContentType());
XlsDto xls = null;
List list = slsmain.readXls(files);
JDBCConn myjdbc = new JDBCConn();
myjdbc.getConnection();
PreparedStatement prep = myjdbc.getConnection().prepareStatement("insert into XlsDto (xh,xm,yxsmc,kcm,cj) values (?,?,?,?,?)");
int count = 0;
for (int i = 0; i < list.size(); i++) {
xls = (XlsDto) list.get(i);
System.out.println(xls.getXh() + " " + xls.getXm() + " " + xls.getYxsmc() + " " + xls.getKcm() + " " + xls.getCj());
// 数据是取到了,然后就是直接插入到数据库当中了,
// prep.setInt(0, Integer.parseInt(xls.getXh()));
// String Xm = new String(xls.getXm().getBytes("ISO8859_1"),
// "utf-8");
prep.setString(1, xls.getXh());
prep.setString(2, xls.getXm());
prep.setString(3, xls.getYxsmc());
prep.setString(4, xls.getKcm());
// prep.setInt(5, xls.getKch());
prep.setFloat(5, xls.getCj());
count = prep.executeUpdate();
}
if (count > 0) {
return "@" + "导入成功!";
}
else {
return "@" + "导入失败!";
}
}
}
3.xlsDto.java
package com.poi.services;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
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 org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import com.pojo.XlsDto;
/**
* 导入excel中数据到数据库
* @author <a href="mailto:qihao@ssreader.cn">chenqihao</a>
*@version 2013-9-3
*/
@Service
public class XlsMain {
/**
* 读取xls文件内容
*
* @return List<XlsDto>对象
* @throws IOException 输入/输出(i/o)异常
*/
public List<XlsDto> readXls(MultipartFile f) throws IOException {
InputStream is = f.getInputStream();
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
XlsDto xlsDto = null;
List<XlsDto> list = new ArrayList<XlsDto>();
// 循环工作表Sheet hssfWorkbook.getNumberOfSheets():行数
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行Row rowNum =1 :因为第一行一般都是标题
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
xlsDto = new XlsDto();// 实体类
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
// 学号
HSSFCell xh = hssfRow.getCell(0);
if (xh == null) {
continue;
}
xlsDto.setXh(getValue(xh));
// 姓名
HSSFCell xm = hssfRow.getCell(1);
if (xm == null) {
continue;
}
xlsDto.setXm(getValue(xm));
// 学院
HSSFCell yxsmc = hssfRow.getCell(2);
if (yxsmc == null) {
continue;
}
xlsDto.setYxsmc(getValue(yxsmc));
// 课程号
// HSSFCell kch = hssfRow.getCell(3);
// System.out.println(kch);
// xlsDto.setKch(Integer.parseInt(getValue(kch)));
// 课程名
HSSFCell kcm = hssfRow.getCell(4);
if (kcm == null) {
continue;
}
xlsDto.setKcm(getValue(kcm));
// 成绩
HSSFCell cj = hssfRow.getCell(5);
if (cj == null) {
continue;
}
xlsDto.setCj(Float.parseFloat(getValue(cj)));
list.add(xlsDto);
}
}
return list;
}
/**
* 得到Excel表中的值
*
* @param hssfCell Excel中的每一个格子
* @return Excel中每一个格子中的值
*/
@SuppressWarnings("static-access")
private String getValue(HSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
// 返回布尔类型的值
return String.valueOf(hssfCell.getBooleanCellValue());
}
else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
// 返回数值类型的值
return String.valueOf(hssfCell.getNumericCellValue());
}
else {
// 返回字符串类型的值
return String.valueOf(hssfCell.getStringCellValue());
}
}
}
4.mysql链接类
JDBCConn.java
package com.poi.services;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/***
* 连接MySql数据库
*@author <a href="mailto:qihao@ssreader.cn">chenqihao</a>
*@version 2013-3-9
*/
public class JDBCConn {
public Connection getConnection() {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/books?useUnicode=true&characterEncoding=utf-8";
String user = "root";
String password = "123";
Connection conn = null;
try {
Class.forName(driver);
}
catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(url, user, password);
if (!conn.isClosed())
System.out.println("Succeeded connecting to the Database!");
}
catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public static void main(String[] args) {
JDBCConn jdbc = new JDBCConn();
System.out.println(jdbc.getConnection());
}
}
5.excelToDB.vm
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>无标题文档</title>
<script src="/js/jquery-1.4.2.min.js"></script>
<script src="/js/pageUtil.js"></script>
<script src="/js/jxl.js"></script>
<script>
function check(){
var f = document.getElementById("file");
alert(f.value);
return false;
}
</script>
</head>
<body>##
<form action="toDbGo" method="Post" enctype="multipart/form-data">
<label> 上传图片 </label>
<input name="files" type="file" id="file"/><br>
<input type="submit" value="Submit"/>
</form>
</body>
</html>