POI导入Excel

说明: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:[e-mail]">chenqihao</a> *@version 2013-9-3 */@Path("index")public class IndexController {@AutowiredXlsMain 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:[e-mail]">chenqihao</a> *@version 2013-9-3 */@Servicepublic 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:[e-mail]">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 blocke.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>