1，excel文件的上传
需要借助jar包：commons-fileupload-1.2.1.jar以及commons-io-1.3.2.jar
前端的html文件
<form id="file_form" action="UpdFile" enctype="multipart/form-data"
method="post">
<input type="file" name="file" id="file_input" />
<input type="submit" value="文件上传" id='upFile-btn'>
</form>
2，利用ajax提交文件
为了在本页面提交文件，利用到jquery.js以及jqurey-form.js
前端验证以及提交的javascript代码如下：
<script type="text/javascript">
$(function() { $("#file_form").submit(
function() {
//首先验证文件格式
var fileName = $('#file_input').val();
if (fileName === '') {
alert('请选择文件');
return false;
}
var fileType = (fileName.substring(fileName
.lastIndexOf(".") + , fileName.length))
.toLowerCase();
if (fileType !== 'xls' && fileType !== 'xlsx') {
alert('文件格式不正确，excel文件！');
return false;
} $("#file_form").ajaxSubmit({
dataType : "json",
success : function(data, textStatus) {
if (data['result'] === 'OK') {
console.log('上传文件成功');
} else {
console.log('文件格式错误');
}
return false;
}
});
return false;
}); });
</script>
3，后台利用poi文件对excel文件进行读取（导入数据库的过程暂略）
使用到的相关jar包有：
poi-3.5-beta5.jar; poi-contrib-3.5-beta5.jar; poi-ooxml-3.5-beta5.jar poi-scratchpad-3.5-beta5.jar
如果是97-03版本，后缀为xls的excel文件，以上jar就已足够，如果还需要读取07版本及以后，后缀为xlsx的excel，还需要借助于jar包：
xbean.jar
dom4j-1.6.1.jar
对应网站后台的servlet处理文件：
package com.bobo.servlet; import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Date;
import java.util.List;
import java.util.Random; import javax.servlet.ServletException;
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.FileItemFactory;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload; import com.bobo.helper.ExcelHelper; public class UpdFile extends HttpServlet { /**
* The doGet method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to get.
*
* @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 doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException { process(request, response);
} /**
* 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 {
process(request, response);
} private void process(HttpServletRequest request,
HttpServletResponse response) throws IOException {
response.setCharacterEncoding("utf-8");
response.setContentType("application/json");
PrintWriter out = response.getWriter();
FileItemFactory factory = new DiskFileItemFactory();
ServletFileUpload upload = new ServletFileUpload(factory);
upload.setHeaderEncoding(request.getCharacterEncoding());
ExcelHelper helper = new ExcelHelper();
try {
List<FileItem> list = upload.parseRequest(request);
for (int i = ; i < list.size(); i++) {
FileItem item = list.get(i);
if (item.getName().endsWith(".xls")||item.getName().endsWith(".xlsx")) {
// 说明是文件,不过这里最好限制一下
//helper.importXls(item.getInputStream());
helper.importXlsx(item.getInputStream());
out.write("{\"result\":\"OK\"}");
} else {
// 说明文件格式不符合要求
out.write("{\"result\":\"Invalid\"}");
}
}
out.flush();
out.close(); } catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} }
4，其中helper.importXlsx(item.getInputStream())的方法如下：
// 读取单元格的值
private String getValue(Cell cell) {
String result = ""; switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
result = cell.getBooleanCellValue() + "";
break;
case Cell.CELL_TYPE_STRING:
result = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
result = cell.getCellFormula();
break;
case Cell.CELL_TYPE_NUMERIC:
// 可能是普通数字，也可能是日期
if (HSSFDateUtil.isCellDateFormatted(cell)) {
result = DateUtil.getJavaDate(cell.getNumericCellValue())
.toString();
} else {
result = cell.getNumericCellValue() + "";
}
break;
}
return result;
} /***
* 这种方法支持03，和07版本的excel读取
* 但是对于合并的单元格，除了第一行第一列之外，其他部分读取的值为空
* @param is
*/
public void importXlsx(InputStream is) {
try {
Workbook wb = WorkbookFactory.create(is);
// OPCPackage pkg = OPCPackage.open(is);
// XSSFWorkbook wb = new XSSFWorkbook(pkg);
for (int i = , len = wb.getNumberOfSheets(); i < len; i++) {
Sheet sheet = wb.getSheetAt(i);
for (int j = ; j <= sheet.getLastRowNum(); j++) {
if (sheet == null) {
return;
}
Row row = sheet.getRow(j);
if(row==null){
return;
}
// 读取每一个单元格
for (int k = ; k < row.getLastCellNum(); k++) {
Cell cell = row.getCell(k);
if (cell == null) {
return;
}
System.out.print(getValue(cell)); }
System.out.println();
}
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
1）该方法可以读取xls文件，也可以读取xlsx文件
2）对于合并的单元格，对于xls文件有对应的方法解决，xlsx文件，还没有发现相关的解决方案，只能除了第一行第一列之外，其他都为空字符串这种方法读取了
处理合并单元格部分的代码：
/**
* 判断是否是合并的单元格，如果是的话，返回合并区域，否则返回空（仅适用于）
*
* @param sheet
* @param cellRow
* @param cellColumn
* @return
*/
private CellRangeAddress isMerged(Sheet sheet, Cell cell) { CellRangeAddress result = null;
CellRangeAddress cra = null;
int cellRow = cell.getRowIndex();
int cellColumn = cell.getColumnIndex();
int mergedNum = sheet.getNumMergedRegions();
for (int i = ; i < mergedNum; i++) {
// 如果是xlsx的格式，怎么办？
cra = ((HSSFSheet) sheet).getMergedRegion(i);
if (cellRow >= cra.getFirstRow() && cellRow <= cra.getLastRow()
&& cellColumn >= cra.getFirstColumn()
&& cellColumn <= cra.getLastColumn()) {
result = cra;
}
}
return result;
} private String getCellValue(Sheet sheet, Cell cell) {
String result = "";
// 判断是否是合并的单元格
CellRangeAddress cra = null;
if ((cra = isMerged(sheet, cell)) != null) {
Cell fcell = sheet.getRow(cra.getFirstRow()).getCell(
cra.getFirstColumn());
result = getValue(fcell);
} else {
result = getValue(cell);
}
return result;
}