导入Excel文件后进行批量修改数据
程序员文章站
2022-03-15 13:30:06
...
1、jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8-beta5</version>
</dependency>2、html代码
<input type="file" id="file1" name="myfiles" />
<input type="button" id="import" value="导入EXCEL" /><script type="text/javascript" src="/resources/js/jquery-1.7.min.js"></script>
<script type="text/javascript" src="/resources/js/ajaxfileupload.js"></script>
<script type="text/javascript">
jQuery(function () {
$("#import").click(function () {
if ($("#file1").val().length > 0) {
ajaxFileUpload();
}
else {
alert("请选择需要导入的Excel文件!");
}
})
})
function ajaxFileUpload(){
$("#import").attr("value","处理中...");
$.ajaxFileUpload({
url: 'import', //用于文件上传的服务器端请求地址
secureuri: false, //是否需要安全协议，一般设置为false
fileElementId: 'file1', //文件上传域的ID
dataType: 'json', //返回值类型 一般设置为json
success: function (data, status) //服务器成功响应处理函数
{
$("#import").attr("disabled",false);
$("#import").attr("value","导入EXCEL");
if(data == 2){
alert("上传成功");
}else{
alert("上传失败");
}
},
error: function (data, status, e)//服务器响应失败处理函数
{
alert("服务器繁忙");
}
});
}
</script>4、后台代码
（1）、Controller层
import java.io.File;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.io.FileUtils;
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.wenfeng.bean.User;
import com.wenfeng.service.PoiService;
import com.wenfeng.service.UserService;
@Controller
public class PoiController {
@Autowired
private UserService userservice; //用户
@Autowired
private PoiService<Object> poiService; //poi
/**
* 导入
*/
@RequestMapping("resources/poi/import")
public void importList(@RequestParam MultipartFile[] myfiles, HttpServletRequest request, HttpServletResponse response)
throws Exception{
response.setContentType("text/plain; charset=UTF-8");
PrintWriter out = null;
try {
out = response.getWriter();
//文件存放地址
String realPath =request.getSession().getServletContext().getRealPath("/resources/uploadFile/");
//上传文件的原名(即上传前的文件名字)
String originalFilename = null;
for(MultipartFile myfile : myfiles){
if(myfile.isEmpty()){
throw new Exception("请选择需要导入的Excel文件后上传");
}else{
originalFilename = myfile.getOriginalFilename();
System.out.println("文件原名: " + originalFilename);
System.out.println("文件名称: " + myfile.getName());
System.out.println("文件长度: " + myfile.getSize());
System.out.println("文件类型: " + myfile.getContentType());
System.out.println("========================================");
}
try{
//保存文件
FileUtils.copyInputStreamToFile(myfile.getInputStream(), new File(realPath, originalFilename));
}catch(Exception e){
throw new Exception("文件上传失败，请重试！");
}
//解析excel文件到 List
List<Object> list = poiService.getListFromExcel(myfile, User.class);
if(list!=null&&list.size()>0){
List<User> imporDatas=new ArrayList<User>();
User data=null;
for(Object o : list) {
data=new User();
if(o!=null){
data=(User)o;
imporDatas.add(data);
}
}
//批量更新
if(imporDatas.size() > 0) {
for(User u:imporDatas){
long id = Long.parseLong(u.getUserid()+"");
User user = userservice.findById(id);
if(null != user && !"".equals(user)){
user.setPassword(u.getPassword());
//根据用户id修改密码
userservice.updateUser(user, id);
}
}
}
}
}
} catch (Exception e) {
out.write("1");
e.printStackTrace();
}
out.write("2");
}
}
（2）、dao层（用户service层就省略了）
/**
* 根据用户id修改密码
*/
public void updateUser(User user, long id) throws Exception {
Map<String,Object> updateParam = new HashMap<String,Object>();
if(StringUtils.isNotEmpty(user.getPassword())){
updateParam.put("pw", user.getPassword());
}
udao.updateUser(updateParam,id);
}import java.util.Map;
import org.apache.ibatis.annotations.Param;
import com.wenfeng.bean.User;
public interface UserDao {
/**
* 根据用户id查询信息
* @param id
* @return
* @throws Exception
*/
User findById(@Param("id")long id) throws Exception;
/**
* 根据用户id修改密码
* @param updateParam
* @param id
* @return
*/
void updateUser(@Param("updateParam")Map<String, Object> updateParam, @Param("id")long id) throws Exception;
}（3）、mapper层
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wenfeng.dao.UserDao">
<sql id="Base_Column_List">
user_id as userid,user_name as username,user_password as password,user_email as useremail
</sql>
<!-- 根据用户id查询信息 -->
<select id="findById" resultType="com.wenfeng.bean.User" parameterType="Integer">
SELECT <include refid="Base_Column_List"/> FROM t_user
<if test="null != id">
<where>
AND user_id =${id}
</where>
</if>
</select>
<!-- 根据用户id修改密码 -->
<select id="updateUser">
UPDATE t_user SET
<if test="updateParam.pw != null and updateParam.pw != '' ">
user_password ='${updateParam.pw}'
</if>
<if test="null != id">
WHERE user_id =${id}
</if>
</select>
</mapper>
（4）、service层（导出）
import java.util.List;
import org.springframework.web.multipart.MultipartFile;
import com.wenfeng.bean.User;
public interface PoiService<T> {
/**
* 解析excel到 List
* @param excel 被解析的excel文件对象
* @param clazz 对象类型
* @return
* @throws Exception
*/
public List<Object> getListFromExcel(MultipartFile excel, Class<User> clazz) throws Exception;
}import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
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;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import com.wenfeng.bean.User;
import com.wenfeng.service.PoiService;
@Service
public class PoiServiceImpl implements PoiService<Object> {
/**
* 导入
*/
public List<Object> getListFromExcel(MultipartFile excel, Class<User> clazz) throws Exception {
if ( null == excel ||
!(excel.getOriginalFilename().toLowerCase().endsWith(".xls")
|| excel.getOriginalFilename().toLowerCase().endsWith(".xlsx")) ) {
return null;
}
InputStream is = excel.getInputStream(); //直接获取文件数据流
HSSFWorkbook wb = new HSSFWorkbook(is);
Object obj = null;
String setMethodName = "";
Method setMethod = null;
Field field = null;
List<Object> objs = new ArrayList<Object>();
for (int i = 0; i < wb.getNumberOfSheets(); i ++) {
HSSFSheet sheet = wb.getSheetAt(i); //得到excel中的sheet
if (null == sheet) {
continue;
}
for (int j = 1; j <= sheet.getLastRowNum(); j ++) {
HSSFRow row = sheet.getRow(j); //得到一行数据
if (null == row) {
continue;
}
obj = clazz.newInstance();
for (int k = 0; k < clazz.getDeclaredFields().length; k++) {
if (null == row.getCell(k)) {
continue;
}
field = clazz.getDeclaredFields()[k];
// if (field.getName().equalsIgnoreCase("id")) {
// continue;
// }
setMethodName = "set"+field.getName().substring(0,1).toUpperCase()+field.getName().substring(1); //setter方法
//System.out.println(setMethodName);
if (field.getType().getName().equalsIgnoreCase("java.lang.String")) { //如果字段类型为字符串
setMethod = clazz.getMethod(setMethodName,String.class); //到setter方法
if (row.getCell(k).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
setMethod.invoke(obj, String.valueOf(row.getCell(k).getNumericCellValue())); //得到单元格数据
} else if (row.getCell(k).getCellType() == HSSFCell.CELL_TYPE_STRING) {
setMethod.invoke(obj, row.getCell(k).getStringCellValue());
}
} else if (field.getType().getName().equalsIgnoreCase("java.lang.Integer")) {
setMethod = clazz.getMethod(setMethodName,Integer.class);
if (row.getCell(k).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
setMethod.invoke(obj, (int)row.getCell(k).getNumericCellValue());
} else if (row.getCell(k).getCellType() == HSSFCell.CELL_TYPE_STRING) {
setMethod.invoke(obj, Integer.valueOf(row.getCell(k).getStringCellValue()));
}
} else if (field.getType().getName().equalsIgnoreCase("int")) {
setMethod = clazz.getMethod(setMethodName,int.class);
if (row.getCell(k).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
setMethod.invoke(obj, (int)row.getCell(k).getNumericCellValue());
} else if (row.getCell(k).getCellType() == HSSFCell.CELL_TYPE_STRING) {
setMethod.invoke(obj, Integer.valueOf(row.getCell(k).getStringCellValue()));
}
} else if (field.getType().getName().equalsIgnoreCase("long")) {
setMethod = clazz.getMethod(setMethodName,long.class);
if (row.getCell(k).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
setMethod.invoke(obj, (long)row.getCell(k).getNumericCellValue());
} else if (row.getCell(k).getCellType() == HSSFCell.CELL_TYPE_STRING) {
setMethod.invoke(obj, Long.valueOf(row.getCell(k).getStringCellValue()));
}
} else if (field.getType().getName().equalsIgnoreCase("float")) {
setMethod = clazz.getMethod(setMethodName,float.class);
if (row.getCell(k).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
setMethod.invoke(obj, (float)row.getCell(k).getNumericCellValue());
} else if (row.getCell(k).getCellType() == HSSFCell.CELL_TYPE_STRING) {
setMethod.invoke(obj, Float.valueOf(row.getCell(k).getStringCellValue()));
}
} else if (field.getType().getName().equalsIgnoreCase("double")) {
setMethod = clazz.getMethod(setMethodName,double.class);
if (row.getCell(k).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
setMethod.invoke(obj, row.getCell(k).getNumericCellValue());
} else if (row.getCell(k).getCellType() == HSSFCell.CELL_TYPE_STRING) {
setMethod.invoke(obj, Double.valueOf(row.getCell(k).getStringCellValue()));
}
} else if (field.getType().getName().equalsIgnoreCase("boolean")) {
setMethod = clazz.getMethod(setMethodName,boolean.class);
if (row.getCell(k).getCellType() == 4) {
setMethod.invoke(obj, row.getCell(k).getBooleanCellValue());
} else if (row.getCell(k).getCellType() == HSSFCell.CELL_TYPE_STRING) {
setMethod.invoke(obj, Boolean.valueOf(row.getCell(k).getStringCellValue()));
}
} else if (field.getType().getName().equalsIgnoreCase("java.util.Date")) {
setMethod = clazz.getMethod(setMethodName,java.util.Date.class);
setMethod.invoke(obj, row.getCell(k).getDateCellValue());
}
}
objs.add(obj);
}
}
if(objs.size() > 0) {
return objs;
} else {
return null;
}
}
}