利用Apache POI 操作Excel, 获取数据，并生成 sql 语句。
先上个动图
excel.gif
看完动图，可以更明白这个任务。
准备相关jar包
到Apache下载 POI 需要的相关jar包
poi-3.15.jar
poi-ooxml-3.15.jar
poi-ooxml-schemas-3.15.jar
poi-scratchpad-3.15.jar
xmlbeans-2.6.0.jar
commons-collections4-4.1.jar
以及文件上传需要的jar包
commons-fileupload-1.3.1.jar
commons-io-2.5.jar
需要在你的classpath中引入最新的 commons-fileupload.x.x.jar 包文件。
下载地址为：http://commons.apache.org/fileupload/
需要在你的classpath中引入最新的 commons-io-x.x.jar 。
下载地址为：http://commons.apache.org/io/
数据库jar包
com.mysql.jdbc_5.1.5.jar
json jar包
json-20160810.jar
将这些jar包放于
WebContent
-- Web-INT
-- lib (放于这个文件夹内)
构建提交 excel 文件表单页面 importdata.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link rel="stylesheet" type="text/css" href="asset/semantic.min.css">
<script src="asset/jquery.min.js"></script>
<script src="asset/semantic.min.js"></script>
<title>Import Data</title>
</head>
<body>
<h2 class="ui center aligned icon header">
<i class="file excel outline icon olive"> </i> Import Data Though
Excel
</h2>
<div class="ui two column centered grid">
<div class="column">
<div class="">
<form class="ui form" enctype="multipart/form-data" action="/ChartDemo/ImportData" method="post">
<div class="field">
<label>Import Excel File</label>
<input type="file" name="file" placeholder="">
<input type="hidden" name="action" value="importAction">
</div>
<button class="ui button" type="submit">Submit</button>
</form>
</div>
</div>
<div class="four column centered row">
<div class="column">${message}</div>
</div>
</div>
</body>
</html>
importdata.png
页面比较简单，但注意 因为是提交文件，需要在form 内加上
enctype="multipart/form-data"
action 提交到 ImportData 这个 servlet处理
action="/ChartDemo/ImportData"
构建显示结果页面 resultdata.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@page import="org.json.JSONArray"%>
<%@page import="org.json.JSONObject"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link rel="stylesheet" type="text/css" href="asset/semantic.min.css">
<script src="asset/jquery.min.js"></script>
<script src="asset/semantic.min.js"></script>
<title>Import Data</title>
</head>
<body>
<h2 class="ui center aligned icon header">
<i class="file excel outline icon olive"> </i> ${course}
</h2>
<div class="ui two column centered grid">
<div class="column">
<%
Object result = request.getAttribute("resultData");
//out.print(result);
JSONArray array = (JSONArray) result;
JSONObject jsonObj = null;
%>
<table class="ui selectable celled table">
<thead>
<tr>
<th>studentid</th>
<th>quiz</th>
<th>assignment</th>
<th>lab</th>
<th>exam</th>
<th>final</th>
</tr>
</thead>
<tbody>
<%
for (int i = 0; i < array.length(); i++) {
jsonObj = (JSONObject) array.get(i);
%>
<tr>
<td>
<h4 class="ui image header">
<img src="asset/images/lena.png" class="ui mini rounded image">
<div class="content">
<%
out.print(jsonObj.get("studentid"));
%>
</div>
</h4>
</td>
<td>
<%
out.print(jsonObj.get("quiz"));
%>
</td>
<td>
<%
out.print(jsonObj.get("assignment"));
%>
</td>
<td>
<%
out.print(jsonObj.get("lab"));
%>
</td>
<td>
<%
out.print(jsonObj.get("exam"));
%>
</td>
<td>
<%
out.print(jsonObj.get("final"));
%>
</td>
</tr>
<%
}
%>
</tbody>
</table>
</div>
<div class="four column centered row">
<div class="column"></div>
<div class="column"></div>
</div>
</div>
</body>
</html>
resultdata.png
这个页面是显示数据表格。
由于我们用到JSONArray, 所以在页面开头我们需要导入
<%@page import="org.json.JSONArray"%>
<%@page import="org.json.JSONObject"%>
获取从servlet传递过来的 JSONArray 对象
<%
Object result = request.getAttribute("resultData");
//out.print(result);
JSONArray array = (JSONArray) result;
JSONObject jsonObj = null;
%>
构建table，并循环输出结果
<%
for (int i = 0; i < array.length(); i++) {
jsonObj = (JSONObject) array.get(i);
%>
注意：上面这一片段代码， 这里for循环 ，这一部分 是 { ， 并没有马上 结束 }
接下来在循环体内 输出相关信息
<tr>
<td>
<h4 class="ui image header">
<img src="asset/images/lena.png" class="ui mini rounded image">
<div class="content">
<%
out.print(jsonObj.get("studentid"));
%>
</div>
</h4>
</td>
<td>
<%
out.print(jsonObj.get("quiz"));
%>
</td>
<td>
<%
out.print(jsonObj.get("assignment"));
%>
</td>
<td>
<%
out.print(jsonObj.get("lab"));
%>
</td>
<td>
<%
out.print(jsonObj.get("exam"));
%>
</td>
<td>
<%
out.print(jsonObj.get("final"));
%>
</td>
</tr>
其中里面 分别对应输出了 studentid, quiz, assignment,lab,exam和 final的值
<%
out.print(jsonObj.get("studentid"));
%>
<%
out.print(jsonObj.get("qzui"));
%>
<%
out.print(jsonObj.get("assignment"));
%>
<%
out.print(jsonObj.get("lab"));
%>
<%
out.print(jsonObj.get("exam"));
%>
<%
out.print(jsonObj.get("final"));
%>
最后别忘了加上 循环体的 闭合 }
<%
}
%>
编写 ImportData.java 这个servlet
package cn.hejing.servlet;
import java.io.File;
import java.io.IOException;
import java.util.Iterator;
import java.util.List;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.json.JSONArray;
import cn.hejing.util.ReadWriteExcel;
/**
* Servlet implementation class ImportData
*/
@WebServlet("/ImportData")
public class ImportData extends HttpServlet {
private static final long serialVersionUID = 1L;
private static final String EXCEL_XLS = "xls";
private static final String EXCEL_XLSX = "xlsx";
/**
* @see HttpServlet#HttpServlet()
*/
public ImportData() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
//allow file types
String[] allowTypes = new String[] { "xls", "xlsx" };
DiskFileItemFactory factory = new DiskFileItemFactory();
ServletFileUpload upload = new ServletFileUpload(factory);
// file store path
String path = "C://hejing//work//workshop//java-project//ChartDemo";
JSONArray array = null;
try {
List<FileItem> items = (List<FileItem>) upload.parseRequest(request);
Iterator itr = items.iterator();
while (itr.hasNext()) {
FileItem item = (FileItem) itr.next();
if (item.isFormField()) {
} else {
// check the upload file
if (item.getName() != null && !item.getName().equals("")) {
String name = item.getName();
name = name.substring(name.lastIndexOf("\\") + 1);
System.out.println(name);
String type = name.substring(name.lastIndexOf('.') + 1);
boolean flag = false;
for (String at : allowTypes) {
if (at.equals(type)) {
flag = true;
}
}
// file type not match
if (flag == false) {
System.out.println("文件格式不支持");
request.setAttribute("message", "文件格式不支持");
RequestDispatcher rd = request.getRequestDispatcher("importdata.jsp");
rd.forward(request, response);
} else {
int start = name.lastIndexOf("\\");
String filename = name.substring(start + 1);
// save the upload file into target directory
File file = new File(path + "\\" + filename);
item.write(file);
request.setAttribute("course", name);
// 调用ReadWriteExcel的静态方法 readExcel()去处理excel文件
array = ReadWriteExcel.readExcel(path, name);
//获取 JSONArray 传递给resultdata.jsp页面
RequestDispatcher rd = request.getRequestDispatcher("resultdata.jsp");
request.setAttribute("resultData", array);
rd.forward(request, response);
}
} else {
System.out.println("请选择待上传文件");
request.setAttribute("message", "请选择待上传文件");
RequestDispatcher rd = request.getRequestDispatcher("importdata.jsp");
rd.forward(request, response);
}
}
}
} catch (Exception e) {
e.printStackTrace();
request.setAttribute("message", "文件上传失败");
}
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
代码比较简单，部分代码稍微解释。
这里表示，将文件保存到指定位置。
File file = new File(path + "\\" + filename);
item.write(file);
调用ReadWriteExcel的静态方法 readExcel()去处理excel文件 。
后面会介绍 ReadWriteExcel这个类
array = ReadWriteExcel.readExcel(path, name);
获取 JSONArray 传递给resultdata.jsp页面
RequestDispatcher rd = request.getRequestDispatcher("resultdata.jsp");
request.setAttribute("resultData", array);
rd.forward(request, response);
编写 ReadWriteExcel.java
package cn.hejing.util;
/**
* @author hejing
* @email 2010jing@gmail.com
* @date datetime 2016年9月24日 下午11:10:41
* @description
* @version 1.0
* @since
*/
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.JSONArray;
import org.json.JSONObject;
public class ReadWriteExcel {
private static final String EXCEL_XLS = "xls";
private static final String EXCEL_XLSX = "xlsx";
/**
* 判断Excel的版本,获取Workbook
*
* @param in
* @param filename
* @return
* @throws IOException
*/
public static Workbook getWorkbok(InputStream in, File file) throws IOException {
Workbook wb = null;
if (file.getName().endsWith(EXCEL_XLS)) { // Excel 2003
wb = new HSSFWorkbook(in);
} else if (file.getName().endsWith(EXCEL_XLSX)) { // Excel 2007/2010
wb = new XSSFWorkbook(in);
}
return wb;
}
/**
* 判断文件是否是excel
*
* @throws Exception
*/
public static void checkExcelVaild(File file) throws Exception {
if (!file.exists()) {
throw new Exception("文件不存在");
}
if (!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))) {
throw new Exception("文件不是Excel");
}
}
/**
* 读取Excel测试，兼容 Excel 2003/2007/2010
*
* @throws FileNotFoundException
* @throws Exception
*/
@SuppressWarnings("finally")
public static JSONArray readExcel(String path, String name) throws Exception {
String sqlfile =path+ "//" + name.substring(0,name.lastIndexOf(".")) +".txt";
System.out.println(sqlfile);
//String sqlfile = path + "//" + name + "InsertSQL.txt";
BufferedWriter bw = new BufferedWriter(new FileWriter(new File(sqlfile)));
String filename = path + "//" + name;
JSONArray array =null;
try {
// 同时支持Excel 2003、2007
File excelFile = new File(filename); // 创建文件对象
FileInputStream is = new FileInputStream(excelFile); // 文件流
checkExcelVaild(excelFile);
Workbook workbook = getWorkbok(is, excelFile);
// Workbook workbook = WorkbookFactory.create(is); // 这种方式
// Excel2003/2007/2010都是可以处理的
int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量
/**
* 设置当前excel中sheet的下标：0开始
*/
Sheet sheet = workbook.getSheetAt(0); // 遍历第一个Sheet
Row row;
Cell cell1;
int rows = sheet.getLastRowNum();
// json数组
array = new JSONArray();
//用于存储excel表，第一行字段内容
String[] tag = new String[20];
int tagNum =0;
JSONObject jsonObj = null;
System.out.println(rows);
for (int icount = 0; icount <= rows; icount++) {
jsonObj = new JSONObject();
row = sheet.getRow(icount);
int line = row.getPhysicalNumberOfCells();
// System.out.println(line);
for (int j = 0; j < line; j++) {
cell1 = row.getCell(j);
if (icount == 0) {
tagNum = line;
tag[j] = cell1.toString();
} else {
jsonObj.put(tag[j], cell1);
}
}
if (icount != 0) {
array.put(jsonObj);
}
}
//System.out.println(array.toString());
String tableName="grade";
writeSql(tag,array,bw,tagNum,tableName);
bw.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
bw.close();
return array;
}
}
/**
*
* @Title: writeSql
* @Description: TODO
* @param @param tag
* @param @param array
* @param @param bw
* @param @param tagNum
* @param @param tableName 参数
* @return void 返回类型
* @throws
*/
private static void writeSql(String[] tag, JSONArray array, BufferedWriter bw, int tagNum,String tableName) {
// TODO Auto-generated method stub
String sql = "";
sql = "INSERT INTO `" + tableName + "` ( ";
for(int i = 0;i<tagNum;i++){
if(i != tagNum-1){
sql += "`"+tag[i]+"`" +", ";
}else{
sql += "`"+tag[i]+"`" +" ";
}
}
sql += ") VALUES";
JSONObject jsonObj = null;
for(int i=0;i<array.length();i++){
jsonObj = (JSONObject) array.get(i);
for(int j=0;j<tagNum;j++){
if(j==0){
sql += " ( ";
sql += "'" +jsonObj.get(tag[j])+"'" + " ,";
}else if(j == tagNum-1){
sql += "'" +jsonObj.get(tag[j])+"'" +" )";
}else{
sql += "'" +jsonObj.get(tag[j])+"'" +" ,";
}
}
if(i != array.length()-1){
sql += " ,";
}else{
sql += " ";
}
}
System.out.print(sql);
try {
bw.write(sql);
bw.newLine();
} catch (IOException e) {
e.printStackTrace();
}
}
}
定义两个静态常亮标示 xls 和 xlsx
private static final String EXCEL_XLS = "xls";
private static final String EXCEL_XLSX = "xlsx";
getWorkbok 静态方法，判断Excel的版本,并返回Workbook
public static Workbook getWorkbok(InputStream in, File file) throws IOException {
Workbook wb = null;
if (file.getName().endsWith(EXCEL_XLS)) { // Excel 2003
wb = new HSSFWorkbook(in);
} else if (file.getName().endsWith(EXCEL_XLSX)) { // Excel 2007/2010
wb = new XSSFWorkbook(in);
}
return wb;
}
判断文件是否为 excel
public static void checkExcelVaild(File file) throws Exception {
if (!file.exists()) {
throw new Exception("文件不存在");
}
if (!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))) {
throw new Exception("文件不是Excel");
}
}
具体获取 excel里面每一行的数据
@SuppressWarnings("finally")
public static JSONArray readExcel(String path, String name) throws Exception {
String sqlfile =path+ "//" + name.substring(0,name.lastIndexOf(".")) +".txt";
System.out.println(sqlfile);
//String sqlfile = path + "//" + name + "InsertSQL.txt";
BufferedWriter bw = new BufferedWriter(new FileWriter(new File(sqlfile)));
String filename = path + "//" + name;
JSONArray array =null;
try {
// 同时支持Excel 2003、2007
File excelFile = new File(filename); // 创建文件对象
FileInputStream is = new FileInputStream(excelFile); // 文件流
checkExcelVaild(excelFile);
Workbook workbook = getWorkbok(is, excelFile);
// Workbook workbook = WorkbookFactory.create(is); // 这种方式
// Excel2003/2007/2010都是可以处理的
int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量
/**
* 设置当前excel中sheet的下标：0开始
*/
Sheet sheet = workbook.getSheetAt(0); // 遍历第一个Sheet
Row row;
Cell cell1;
int rows = sheet.getLastRowNum();
// json数组
array = new JSONArray();
//用于存储excel表，第一行字段内容
String[] tag = new String[20];
int tagNum =0;
JSONObject jsonObj = null;
System.out.println(rows);
for (int icount = 0; icount <= rows; icount++) {
jsonObj = new JSONObject();
row = sheet.getRow(icount);
int line = row.getPhysicalNumberOfCells();
// System.out.println(line);
for (int j = 0; j < line; j++) {
cell1 = row.getCell(j);
if (icount == 0) {
tagNum = line;
tag[j] = cell1.toString();
} else {
jsonObj.put(tag[j], cell1);
}
}
if (icount != 0) {
array.put(jsonObj);
}
}
//System.out.println(array.toString());
String tableName="grade";
writeSql(tag,array,bw,tagNum,tableName);
bw.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
bw.close();
return array;
}
}
获取excel文档有多少个 sheet
int sheetCount = workbook.getNumberOfSheets();
获取第一个sheet
Sheet sheet = workbook.getSheetAt(0);
定义 Row row 行变量 和 Cell cell1变量
并获取到有多少行数据
Row row;
Cell cell1;
int rows = sheet.getLastRowNum();
获取当前行，并获得该行有多少个cell
row = sheet.getRow(icount);
int line = row.getPhysicalNumberOfCells();
获取cell的值
cell1 = row.getCell(j);
每获取一行数据，构建一个json对象，并且在遍历每一行内的cell，存入键值对
jsonObj = new JSONObject();
jsonObj.put(tag[j], cell1);
遍历完每一行的数据之后，将json对象 放于 JSONArray 数组内
JSONArray array =null;
array.put(jsonObj);
构建MySQL语句
/**
*
* @Title: writeSql
* @Description: TODO
* @param @param tag
* @param @param array
* @param @param bw
* @param @param tagNum
* @param @param tableName 参数
* @return void 返回类型
* @throws
*/
private static void writeSql(String[] tag, JSONArray array, BufferedWriter bw, int tagNum,String tableName) {
// TODO Auto-generated method stub
String sql = "";
sql = "INSERT INTO `" + tableName + "` ( ";
for(int i = 0;i<tagNum;i++){
if(i != tagNum-1){
sql += "`"+tag[i]+"`" +", ";
}else{
sql += "`"+tag[i]+"`" +" ";
}
}
sql += ") VALUES";
JSONObject jsonObj = null;
for(int i=0;i<array.length();i++){
jsonObj = (JSONObject) array.get(i);
for(int j=0;j<tagNum;j++){
if(j==0){
sql += " ( ";
sql += "'" +jsonObj.get(tag[j])+"'" + " ,";
}else if(j == tagNum-1){
sql += "'" +jsonObj.get(tag[j])+"'" +" )";
}else{
sql += "'" +jsonObj.get(tag[j])+"'" +" ,";
}
}
if(i != array.length()-1){
sql += " ,";
}else{
sql += " ";
}
}
System.out.print(sql);
try {
bw.write(sql);
bw.newLine();
} catch (IOException e) {
e.printStackTrace();
}
}
tag是一个数组，存储与数据库字段对应的值
array是 JSONArray
bw是 BufferedWriter
tagNum是记录tag数组实际存了多少个数据
第一部分先构建 sql语句前半部分
sql = "INSERT INTO `" + tableName + "` ( ";
for(int i = 0;i<tagNum;i++){
if(i != tagNum-1){
sql += "`"+tag[i]+"`" +", ";
}else{
sql += "`"+tag[i]+"`" +" ";
}
}
比如 得到 这部分
INSERT INTO grade ( studentid, quiz, assignment, lab, exam, final
接着拼接上VALUES 部分
sql += ") VALUES";
JSONObject jsonObj = null;
for(int i=0;i<array.length();i++){
jsonObj = (JSONObject) array.get(i);
for(int j=0;j<tagNum;j++){
if(j==0){
sql += " ( ";
sql += "'" +jsonObj.get(tag[j])+"'" + " ,";
}else if(j == tagNum-1){
sql += "'" +jsonObj.get(tag[j])+"'" +" )";
}else{
sql += "'" +jsonObj.get(tag[j])+"'" +" ,";
}
}
if(i != array.length()-1){
sql += " ,";
}else{
sql += " ";
}
}
最后就可以得到需要的SQL语句，例如
INSERT
INTO
`grade`(
`studentid`,
`quiz`,
`assignment`,
`lab`,
`exam`,
`final`
)
VALUES(
'1430001022',
'10.0',
'23.0',
'40.0',
'18.0',
'91.0'
),(
'1430001030',
'8.0',
'20.0',
'38.0',
'15.0',
'81.0'
)
既然这里能够得到MySQL 语句，就不在这里演示插入数据到数据库的过程了。
