关于excel数据导入到数据库案例

package com.integral.util;


import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


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;


/**
 * 从EXCEL导入到数据库
 * 
 * @project:IntegralSystem
 * @class:ReadExcelUtil
 * @description：
 * @author:xuzn
 * @date:2017-1-22 下午12:46:20
 * @modify:
 * @version:
 * 
 */
public class ReadExcelUtil {


/**
* @param filepath
*            //文件路径
* @param filename
*            //文件名
* @param startrow
*            //开始行号
* @param startcol
*            //开始列号
* @param sheetnum
*            //sheet
* @return list
*/
@SuppressWarnings("deprecation")
public static List<Map<String, Object>> readExcel(String filepath,
String filename, int startrow, int startcol, int sheetnum) {
List<Map<String, Object>> varList = new ArrayList<Map<String, Object>>();


try {
File target = new File(filepath, filename);
FileInputStream fi = new FileInputStream(target);
HSSFWorkbook wb = new HSSFWorkbook(fi);
HSSFSheet sheet = wb.getSheetAt(sheetnum); // sheet 从0开始
int rowNum = sheet.getLastRowNum() + 1; // 取得最后一行的行号


for (int i = startrow; i < rowNum; i++) { // 行循环开始


Map<String, Object> map = new HashMap<String, Object>();
HSSFRow row = sheet.getRow(i); // 行
int cellNum = row.getLastCellNum(); // 每行的最后一个单元格位置


for (int j = startcol; j < cellNum; j++) { // 列循环开始


HSSFCell cell = row.getCell(Short.parseShort(j + ""));
String cellValue = null;
if (null != cell) {
switch (cell.getCellType()) { // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
case 0:
cellValue = String.valueOf((int) cell
.getNumericCellValue());
break;
case 1:
cellValue = cell.getStringCellValue();
break;
case 2:
cellValue = cell.getNumericCellValue() + "";
// cellValue =
// String.valueOf(cell.getDateCellValue());
break;
case 3:
cellValue = "";
break;
case 4:
cellValue = String.valueOf(cell
.getBooleanCellValue());
break;
case 5:
cellValue = String
.valueOf(cell.getErrorCellValue());
break;
}
} else {
cellValue = "";
}


map.put("var" + j, cellValue);


}
varList.add(map);
}


} catch (Exception e) {
System.out.println(e);
}


return varList;
}

}

/****************************************************************************以上为工具类********************************************************************************/

package com.integral.controller;


import java.util.List;
import java.util.Map;


import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;


import com.integral.constant.constants;
import com.integral.entity.Salary;
import com.integral.service.SalaryService;
import com.integral.util.FileUpload;
import com.integral.util.PathUtil;
import com.integral.util.ReadExcelUtil;


/**
 * 导入数据库
 * 
 * @project:IntegralSystem
 * @class:RExcelController
 * @description：
 * @author:xuzn
 * @date:2017-1-22 下午12:46:49
 * @modify:
 * @version:
 * 
 */
@Controller
@RequestMapping(value = "/test")
public class RExcelController {


@Autowired
private SalaryService salaryService;


/**
* 从EXCEL导入到数据库
*/
@RequestMapping(value = "/readExcel")
public String readExcel(
@RequestParam(value = "excel", required = false) MultipartFile file)
throws Exception {
if (null != file && !file.isEmpty()) {
String filePath = PathUtil.getClasspath() + constants.FILEPATHFILE; // 文件上传路径
String fileName = FileUpload.fileUp(file, filePath, "excel"); // 执行上传


List<Map<String, Object>> list = ReadExcelUtil.readExcel(filePath,
fileName, 2, 0, 0); 


Salary salary = new Salary();
/**
* "id", var0 "uid", var1 "name", var2 "dept", var3 "jop", var4
* "salary", var5 "award_salary", var6 "sum_salary", var7 "created",
* var8 "updated" var9
*/


for (int i = 0; i < list.size(); i++) {
salary.setUid(list.get(i).get("var" + 1).toString());
salary.setName(list.get(i).get("var" + 2).toString());
salary.setDept(list.get(i).get("var" + 3).toString());
salary.setJob(list.get(i).get("var" + 4).toString());
salary.setSalary(list.get(i).get("var" + 5).toString());
salary.setAwardSalary(list.get(i).get("var" + 6).toString());
salary.setSumSalary(list.get(i).get("var" + 7).toString());
System.out.println(list.get(i).get("var" + 0));
salaryService.txInsertSalary(salary);
}
}
return "ReadExcelSuccess";
}


@RequestMapping("goReadExcle")
public String goReadExcle(HttpServletRequest request,
HttpServletResponse response) {
return "UploadExcel";
}
}

/********************************************************************************以上是实现***************************************************************************************/