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: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>