大学刚毕业，进入公司之后，师傅要求在已有的项目上增加Excel表格导入数据的功能，借此机会将遇到的问题做一个简单的总结。
在实现该功能时先对实现思路进行梳理。
1、获取Excel数据
2、对获取到的数据进行分析
3、如何将数据插入数据库
4、如何校验之后插入数据库，出现异常以什么样的形式反馈给用户
以上是实现的基本思路（菜鸟一枚，如有不对请给予指导，谢谢）
1、由于每个用户使用的Excel版本不一样，所以需要对不同的版本进行处理，这段代码是copy大神的代码。
public class InsertByExcel {
//默认单元格内容为数字时格式
private static DecimalFormat df = new DecimalFormat("0");
// 默认单元格格式化日期字符串
private static SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss");
// 格式化数字
private static DecimalFormat nf = new DecimalFormat("0.00");
public static ArrayList<ArrayList<Object>> readExcel(File file){
if(file == null){
return null;
}
if(file.getName().endsWith("xlsx")){
//处理ecxel2007
return readExcel2013(file);
}else{
//处理ecxel2013
return readExcel2003(file);
}
}
/*
* @return 将返回结果存储在ArrayList内，存储结构与二位数组类似
* lists.get(0).get(0)表示过去Excel中0行0列单元格
*/
public static ArrayList<ArrayList<Object>> readExcel2003(File file){
try{
ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
ArrayList<Object> colList;
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row;
HSSFCell cell;
Object value;
for(int i = sheet.getFirstRowNum() , rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows() ; i++ ){
row = sheet.getRow(i);
colList = new ArrayList<Object>();
if(row == null){
//当读取行为空时
if(i != sheet.getPhysicalNumberOfRows()){//判断是否是最后一行
rowList.add(colList);
}
continue;
}else{
rowCount++;
}
for( int j = row.getFirstCellNum() ; j <= row.getLastCellNum() ;j++){
cell = row.getCell(j);
if(cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK){
//当该单元格为空
if(j != row.getLastCellNum()){//判断是否是该行中最后一个单元格
colList.add("");
}
continue;
}
switch(cell.getCellType()){
case XSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("General".equals(cell.getCellStyle()
.getDataFormatString())) {
value = nf.format(cell.getNumericCellValue());
} else {
value = sdf.format(HSSFDateUtil.getJavaDate(cell
.getNumericCellValue()));
}
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
value = Boolean.valueOf(cell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_BLANK:
value = "";
break;
default:
value = cell.toString();
}// end switch
colList.add(value);
}//end for j
rowList.add(colList);
}//end for i
return rowList;
}catch(Exception e){
return null;
}
}
public static ArrayList<ArrayList<Object>> readExcel2013(File file){
try{
ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
ArrayList<Object> colList;
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
XSSFSheet sheet = wb.getSheetAt(0);
XSSFRow row;
XSSFCell cell;
Object value;
for(int i = sheet.getFirstRowNum() , rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows() ; i++ ){
row = sheet.getRow(i);
colList = new ArrayList<Object>();
if(row == null){
//当读取行为空时
if(i != sheet.getPhysicalNumberOfRows()){//判断是否是最后一行
rowList.add(colList);
}
continue;
}else{
rowCount++;
}
for( int j = row.getFirstCellNum() ; j <= row.getLastCellNum() ;j++){
cell = row.getCell(j);
if(cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK){
//当该单元格为空
if(j != row.getLastCellNum()){//判断是否是该行中最后一个单元格
colList.add("");
}
continue;
}
switch(cell.getCellType()){
case XSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("General".equals(cell.getCellStyle()
.getDataFormatString())) {
value = nf.format(cell.getNumericCellValue());
} else {
value = sdf.format(HSSFDateUtil.getJavaDate(cell
.getNumericCellValue()));
}
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
value = Boolean.valueOf(cell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_BLANK:
value = "";
break;
default:
value = cell.toString();
}// end switch
colList.add(value);
}//end for j
rowList.add(colList);
}//end for i
return rowList;
}catch(Exception e){
System.out.println("exception");
return null;
}
} public static DecimalFormat getDf() {
return df;
}
public static void setDf(DecimalFormat df) {
InsertByExcel.df = df;
}
public static SimpleDateFormat getSdf() {
return sdf;
}
public static void setSdf(SimpleDateFormat sdf) {
InsertByExcel.sdf = sdf;
}
public static DecimalFormat getNf() {
return nf;
}
public static void setNf(DecimalFormat nf) {
InsertByExcel.nf = nf;
}
}public static void main(String[] args) {
File file = new File("C:/Users/../Desktop/新建 Microsoft Excel 工作表.xlsx");
ArrayList<ArrayList<Object>> result = Excel.readExcel(file);
for(int i = 0 ;i < result.size() ;i++){
System.out.printf("\n");
for(int j = 0;j<result.get(i).size(); j++){
System.out.printf(result.get(i).get(j).toString());
System.out.printf("\t");
}
}
}控制台输出之后，发现输出的为float类型，整数后也都有.00，那么表格中需要录入手机号或者学号这样的一串数字，一定也是包含.00，所以需要将其转换为整型，查看获取Excel的代码发现其是对Excel表格内容进行遍历，只获取Excel表格中的数据，由此分析得出，一定是Excel表格在编写的时候有格式问题，通过查看每一列的数据格式发现确实需要对格式进行标准化才可以进行正确的导入，如图：
（一定要是文本类型，即使数据库字段为int类型，也不能选择数值，int类型通过后台强制类型转换即可）
通过以上处理，已经实现正确的获取表格中的数值，接下来就是实现对数据的插入。
3、以上只是实现了获取Excel数据的功能，但由于该项目基于SSM框架，所以需要从前端上传，再获取，在这个过程中也遇到了很多问题。
前端：前端利用jsp技术结合bootstrap框架来实现。由于html5中上传的图表太丑了，所以就用bootstrap中的按钮来实现这个功能。
<div style="margin-right:5px" class="btn btn-info glyphicon-folder-open" id="Excel"
οnclick="selectFile()">Excel批量增加</div> function selectFile()
{
document.getElementById("select").click();
} 配合原有的html5上传文件的按钮（隐藏一下就好）
<form id="myForm" method="POST" enctype="multipart/form-data">
<input type="file" id="select" name="file" οnchange="doUpload()" style="display:none">
</form>这样即可选择文件。
4、通过后台Controller获取上传的文件
/**
* 上传Excel文件
* @param cp
* @return
*/
@RequestMapping("/uploadExcel")
@ResponseBody
public MResult InsertByExcel(@RequestParam("file") MultipartFile file,HttpServletRequest request,CustomPrep cp){
MResult mr = new MResult();
try {
mr = customprepservice.ExcelAdd(file, request, cp);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return mr;
}这里很关键，刚开始做的时候以为直接获取，直接将Excel文件获取到就可以了，但是延续这个思路执行下去的时候，发现由于文件类型的错误，无法获取文件。通过百度发现，前端传至后台的文件类型为MultipartFile而我们要获取的类型为File，当然也会有其他的多种情况详情请参照以下几位大神：
springmvc上传文件的三种方式https://www.cnblogs.com/fjsnail/p/3491033.html
springMVC上传文件大小限制以及超过限制后的自定义处理点击打开链接
所以，一定要在工程下面建立一个临时的文件夹，通过路径来获取文件，不能通过直接获取文件来实现功能。举一反三，不管是图片还是其他文件类型都要有这样的思路。
5、编写逻辑实现获取文件，进行数据导入
@Override
public MResult ExcelAdd(MultipartFile file, HttpServletRequest request,CustomPrep cp) throws FileNotFoundException {
MResult mr = new MResult();
int flag = 0;
String testphone = "^1([358][0-9]|4[579]|66|7[0135678]|9[89])[0-9]{8}$";
String testname = "^[\u4E00-\u9FA5A-Za-z]+$";
String testsid = "^[0-9]*$/";
String testyear = "^(19|20)\\d{2}$$";
if(file.isEmpty()){
throw new FileNotFoundException();
}
else if(file.getOriginalFilename().indexOf("xlsx")<0||file.getOriginalFilename().indexOf("xls")<0){
mr.setStatus(3);//文件类型错误
}
else {
String path = request.getSession().getServletContext().getRealPath("/")+"upload/"+file.getOriginalFilename();
try{
file.transferTo(new File(path));
}catch (IllegalStateException e) {
e.printStackTrace();
// TODO: handle exception
}catch (IOException e) {
// TODO: handle exception
e.printStackTrace();
}
File file1 = new File(path);
ArrayList<ArrayList<Object>> result = iby.readExcel(file1);
for(int i = 0;i<result.size();i++){
for(int j = 0;j<result.get(i).size();j++){
String Phone = (String)result.get(i).get(0);
String Sid = (String)result.get(i).get(1);
String Year = ((String)result.get(i).get(4));
String Name = (String)result.get(i).get(2);
boolean rsphone = Phone.matches(testphone);
boolean rsname = Name.matches(testname);
boolean rssid = Sid.matches(testname);
boolean rsyear = Year.matches(testyear);
//不符合输出标准的数据，不进行插入并且终止，提醒用户
if(rssid&&rsname&&rsyear&&rsphone&&Phone!=null&&Sid!=null&&Year!=null&&Name!=null){
cp.setPrepPhone(Phone);
cp.setPrepSid(Sid);
cp.setPrepName(Name);
cp.setPrepAddress((String)result.get(i).get(3));
cp.setPrepTstart(Integer.parseInt(Year));
cp.setPrepState(0);
cp.setPrepActive(1);
if(customprepmapper.selectByPrimaryKey(cp.getPrepPhone())!=null&&cp.getPrepActive()==1){}
else {
addPrep(cp);
flag = 1;
}
}
else{
flag = 5;
break;
}
}
}
mr.setStatus(flag);
}
return mr;
}在数据导入的时候的基本思路：
1、判断是否获取到Excel文件。返回结果集
1.1、判断获取到的文件格式是否正确。返回结果集
1.1.1、对获取的数据进行判断是否符合要求。返回结果集
1.1.1.1、判断是否存在该用户且标志位为1（正常），存在的跳过，不存在的新增。
1.1.1.2、判断是否存在该用户且标志位为0（被删除），更新标志位。与上返回相同结果集。
1.1.2、不符合要求跳出循环。返回结果集
根据循环的包含关系编写编号。
最后附上js
//上传文件
function doUpload(){
var file = new FormData($('#myForm')[0]);
$.ajax({
url : "uploadExcel.action",
type : "POST",
dataType:"json",
cache: false,
processData: false,
contentType: false,
data : file,
success : function(req) {
if(req.status == 1){
alert("添加成功");
}
else if(req.status == 3){
alert("请选择xlsx或xls后缀的文件");
}else if(req.status == 5){
alert("该excel中有不规范信息，请检查后重新上传");
}
}
});
};新手第一次发博客，如有不足请多多指教，提出宝贵建议，谢谢！
相关的jar包
6318
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
