Springmvc+POI导入Excel表到数据库, 第一次使用POI和上传文件,中间遇到了很多坎坷,不过最后还是完成了,记录一下。
先直接上代码吧,后续的问题我等会在讲。
首先需要有jar包,我没有用maven,百度都有包。
在使用POI时,因为现在是4.1版本的,我使用最新版的时候,字节符的输入流出了错,所有后来我又换了
POI3.10版的,就成功了!
JSP页面标签与JS调用
<input type="file" name="file" id="excel_file" accept="xlsx" >
<input type="button" onclick="check()" id="excel_button"value="导入">
JS代码
<script>
function check() {
var excel_file=$("#excel_file").val();
if(excel_file==""||excel_file.length==0){
layer.msg("请选择文件路径!",{icon:3});
return false;
}else if(excel_file.lastIndexOf('.xls')==-1||excel_file.lastIndexOf('.xlsx')==-1){
layer.msg("附件格式不符,请上传Excel表格",{icon:7});
return false;
}else{
upload();
parent.layer.closeAll();
return true;
}
}
function upload() {
var formData= new FormData($("#upload")[0]);
$.ajax({
url:"/addPUser.do"
,type:"post"
,data: formData
,async:false
,cache:false
,contentType:false
,processData: false
,beforeSend : function() {
console.log("正在进行,请稍候");
}
,success : function(e) {
if (e == "01") {
alert("导入成功");
} else {
alert("导入失败,请按照模板填值");
}
}
});
}
</script>
Controller层
@RequestMapping("/addPUser") //Excel批量添加用户
@ResponseBody
public String addPUser(@RequestParam MultipartFile file) throws IOException {
String flag = "02";// 上传标志
if (!file.isEmpty()) {
try {
String originalFilename = file.getOriginalFilename();// 原文件名字
System.out.println("文件名:"+originalFilename);
InputStream in=file.getInputStream();
User user=new User();
// flag=re.readExcel1(in,originalFilename);
List<ArrayList<Object>> list=null;
if (originalFilename.endsWith(".xls")) {
list = Excel.readExcel2003(in);
} else {
list = Excel.readExcel2007(in);
}
for (int i=0,j=list.size();i<j;i++){
List<Object> row = list.get(i);
user.setuUsername(row.get(0).toString());
user.setuPassword(row.get(1).toString());
user.setuPhone(row.get(2).toString());
user.setuSex(row.get(3).toString());
user.setuOrganizationid(Integer.parseInt(row.get(4).toString()));
user.setuLocal(row.get(5).toString());
int a=ump.insertSelective(user);
if(a>0){
flag="01";
}
}
} catch (Exception e) {
flag = "03";// 上传出错
e.printStackTrace();
}
}
return flag;
}
至此,如果插入数据成功返回flag到前端JSP页面判断.
Sevice业务层
两个工具类,兼容两个版本xslx2007+,和2003的.xsl格式
public static ArrayList<ArrayList<Object>> readExcel2007(InputStream is) {
try {
ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
ArrayList<Object> colList;
XSSFWorkbook wb = new XSSFWorkbook(is);
System.out.println("获取到了字符流");
XSSFSheet sheet = wb.getSheetAt(0);
XSSFRow row;
XSSFCell cell;
Object value = null;
for (int i = sheet.getFirstRowNum() + 1, rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
colList = new ArrayList<Object>();
if (row == null) {
if (i != sheet.getPhysicalNumberOfRows()) {
rowList.add(colList);
}
return rowList;
} 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;
}
if (null != cell) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
break;
} else {
Double d = cell.getNumericCellValue();
DecimalFormat df = new DecimalFormat("#.##");
value = df.format(d);
}
break;
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_FORMULA:
value = cell.getCellFormula() + "";
break;
case HSSFCell.CELL_TYPE_BLANK:
value = "";
break;
case HSSFCell.CELL_TYPE_ERROR:
value = "非法字符";
break;
default:
value = "未知类型";
break;
}
}
colList.add(value);
}
rowList.add(colList);
}
if (is != null) {
is.close();
}
return rowList;
} catch (Exception e) {
System.out.println("exception");
return null;
}
}
public static ArrayList<ArrayList<Object>> readExcel2003(InputStream is) {
try {
ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
ArrayList<Object> colList;
HSSFWorkbook wb = new HSSFWorkbook(is);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row;
HSSFCell cell;
Object value = null;
for (int i = sheet.getFirstRowNum() + 1, rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
colList = new ArrayList<Object>();
if (row == null) {
if (i != sheet.getPhysicalNumberOfRows()) {// 判断是否是最后一行
rowList.add(colList);
}
return rowList;
} 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;
}
if (null != cell) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
break;
} else {
Double d = cell.getNumericCellValue();
DecimalFormat df = new DecimalFormat("#.##");
value = df.format(d);
}
break;
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_FORMULA:
value = cell.getCellFormula() + "";
break;
case HSSFCell.CELL_TYPE_BLANK:
value = "";
break;
case HSSFCell.CELL_TYPE_ERROR:
value = "非法字符";
break;
default:
value = "未知类型";
break;
}
}
colList.add(value);
}
rowList.add(colList);
}
if (is != null) {
is.close();
}
return rowList;
} catch (Exception e) {
return null;
}
}
配置Spingmvc的上传文件配置
<bean id="multipartResolver"
class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<property name="defaultEncoding" value="utf-8"></property>
<property name="maxUploadSize" value="20000000"></property> <!-- 最大上传文件大小 20000kb,注意是多个文件的和-->
<property name="maxInMemorySize" value="10960"></property>
</bean>
代码差不多就结束了.