poi excl表格的批量导入，批量导出，自定义导出
controller:
import lombok.extern.log4j.Log4j;
import org.apache.poi.hssf.usermodel.;
import org.apache.poi.ss.usermodel.;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
@RestController
@ResponseBody
@Log4j
@RequestMapping("/user")
public class UserController {
@Resource
private UserService userService;
@Resource
private Result result;
@RequestMapping("/queryAll")
public List<User> queryAll(){
List<User> users = userService.queryAll();
return users;
}
//导出
@RequestMapping("/download")
public void queryAll(HttpServletResponse response) throws IOException {
List<User> users = userService.queryAll();
//创建工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
//创建工作表
HSSFSheet sheet = workbook.createSheet("用户信息");
//创建导出样式
CellStyle cellStyle = workbook.createCellStyle();
CreationHelper creationHelper = workbook.getCreationHelper();
cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("yyyy-MM-dd"));
//设置列宽 第一个参数：列索引 第二个参数：列宽
sheet.setColumnWidth(2, 4500);
//创建字体
HSSFFont font = workbook.createFont();
//设置字体颜色
font.setColor(HSSFFont.COLOR_RED);
//设置加粗
font.setBold(true);
//设置字体
font.setFontName("宋体");
//管理字体样式
cellStyle.setFont(font);
//创建文件名
String fileName="user"+new SimpleDateFormat("yyyy-MM-dd").format(new Date())+".xls";
//设置行
int rowNum=1;
//表头
String[] headers={"用户编号","用户姓名","昵称","性别","手机号码","头像"," 注册日期","签名","密码","所在地","私盐","状态"};
//添加到表中
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
//在表中存放数据放入对应的列
for (User user : users) {
HSSFRow row1 = sheet.createRow(rowNum);
row1.createCell(0).setCellValue(user.getId());
row1.createCell(1).setCellValue(user.getName());
row1.createCell(2).setCellValue(user.getNickName());
row1.createCell(3).setCellValue(user.getSex());
row1.createCell(4).setCellValue(user.getPhone());
row1.createCell(5).setCellValue(user.getImg());
row1.createCell(7).setCellValue(user.getSignature());
row1.createCell(8).setCellValue(user.getPassword());
row1.createCell(9).setCellValue(user.getLocation());
row1.createCell(10).setCellValue(user.getSalt());
row1.createCell(11).setCellValue(user.getStatuts());
cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd"));
HSSFCell cell = row1.createCell(6);
cell.setCellStyle(cellStyle);
cell.setCellValue(user.getCreateDate());
rowNum ++;
}
response.setContentType("application/octet-stream");//vnd-ms.excel
response.setHeader("Content-disposition","attachment;filename="+fileName);
workbook.write(response.getOutputStream());
workbook.close();
}
//导入
@RequestMapping("/upload")
public @ResponseBody Result upload(MultipartFile fileup) throws IOException {
try{
//获取文件流
InputStream inputStream = fileup.getInputStream();
//创建工作表
Workbook workbook = new HSSFWorkbook(inputStream);
//
CreationHelper creationHelper = workbook.getCreationHelper();
//创建cell样式
CellStyle cellStyle = workbook.createCellStyle();
//设置日期样式
cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd"));
//根据传入的文件获取文件名
String filename = fileup.getOriginalFilename();
//将.前后进行split
String[] split=filename.split("\\.");
List<User> list=new ArrayList<User>();
User user=new User();
log.info(filename);
//InputStream files = new FileInputStream("D:\\upload\\"+filename);
Sheet sheet=workbook.getSheet("用户信息");
Row row=null;
for (int i = 1; i <=sheet.getLastRowNum(); i++) {
row=sheet.getRow(i);
//"用户编号","用户姓名","昵称","性别","手机号码","头像"," 注册日期","签名","密码","所在地","私盐","状态"
user.setId(row.getCell(0).getStringCellValue());
user.setName(row.getCell(1).getStringCellValue());
user.setNickName(row.getCell(2).getStringCellValue());
user.setSex(row.getCell(3).getStringCellValue());
user.setPhone(row.getCell(4).getStringCellValue());
user.setImg(row.getCell(5).getStringCellValue());
user.setSignature(row.getCell(7).getStringCellValue());
user.setPassword(row.getCell(8).getStringCellValue());
user.setLocation(row.getCell(9).getStringCellValue());
user.setSalt(row.getCell(10).getStringCellValue());
user.setStatuts(row.getCell(11).getStringCellValue());
Cell cell = row.getCell(6);
cell.setCellStyle(cellStyle);
user.setCreateDate(cell.getDateCellValue());
list.add(user);
}
if (list.size()!= 0) {
userService.addUsers(list);
result.setMessage("批量导入成功");
}
} catch (Exception e) {
e.printStackTrace();
result.setSuccess(false);
result.setMessage(e.getMessage());
}
return result;
}
//自定义
@RequestMapping("/customExport")
// 参数一：需要查询的列信息 参数二：标题栏内容
public @ResponseBody Result uploadExcel(String titles, String columns,HttpServletResponse response){
try {
String fileName="user"+new SimpleDateFormat("yyyy-MM-dd").format(new Date())+".xls";
// 根据列信息，查用户信息
// select ${queryCondition} select cmfz_user
List<User> list =userService.queryByColumns(columns);
Workbook workbook = new HSSFWorkbook();
//样式处理
CellStyle cellStyle=workbook.createCellStyle();
cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("yyyy-MM-dd"));
//创建sheet
Sheet sheet = workbook.createSheet("用户信息");
// 处理标题栏
Row row = sheet.createRow(0);
// 获取所有标题集，split各个标题
String[] title = titles.split(",");
for (int i = 0; i < title.length; i++) {
//给标题栏每个单元格赋值
Cell cell = row.createCell(i);
cell.setCellValue(title[i]);
}
// 获取导出字段
String[] column = columns.split(",");
// 处理数据行
for (int i = 1; i <= list.size(); i++) {
// 获得数据行对象
Row dataRow = sheet.createRow(i);
// 获取 User 对象
User user = list.get(i - 1);
//通过类的对象获取；类对象
Class<? extends User> c = user.getClass();
// 拿到字段名
for (int j = 0; j < column.length; j++) {
// user,id 拆分字段 前台传来的字段之间，相连，所以进行split
Cell cell=row.createCell(j);
// 处理 get 方法名
String name = "get";
String cName=column[j];
// 获取属性的get名字
String getStringName = name + cName.substring(0, 1).toUpperCase() + cName.substring(1, cName.length());
//获取该属性的值 第一个参数是get属性名，第二个参数是 方法中的参数
Method method = c.getMethod(getStringName, null);
// 方法调用invoke 获得对象
Object obj = method.invoke(user, null);
if(obj==null)continue;
if(obj instanceof Date){
cell.setCellStyle(cellStyle);
cell.setCellValue((Date) obj);
}else{
cell.setCellValue(obj.toString());
}
}
}
// 设置 response
response.setContentType("application/vnd.ms-excel");
response.setHeader("content-disposition","attachment;filename=" + fileName);
workbook.write(response.getOutputStream());
workbook.close();
result.setMessage("自定义导入成功");
} catch (Exception e) {
e.printStackTrace();
result.setSuccess(false);
result.setMessage(e.getMessage());
}
return result;
}
}
sevice实现层：
import lombok.extern.log4j.Log4j;
import org.apache.commons.codec.digest.DigestUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import javax.annotation.Resource;
import java.util.Date;
import java.util.List;
import java.util.UUID;
@Service
@Transactional
@Log4j
public class UserServiceImpl implements UserService {
@Resource
private UserDao userDao;
@Override
public void addUser(User user) {
user.setId(UUID.randomUUID().toString());
user.setCreateDate(new Date());
String salt= RandomSaltUtil.generetRandomSaltCode();
user.setSalt(salt);
user.setPassword(DigestUtils.md5Hex(user.getPassword()+salt));
userDao.insertOne(user);
}
@Override
public void addUsers(List<User> users) {
userDao.insertUsers(users);
}
@Override
public void dropOne(String id) {
userDao.deleteOne(id);
}
@Override
public void modifyUser(User user) {
String salt= RandomSaltUtil.generetRandomSaltCode();
user.setSalt(salt);
user.setPassword(DigestUtils.md5Hex(user.getPassword()+salt));
userDao.updateOne(user);
}
@Override
@Transactional(propagation = Propagation.SUPPORTS)
public List<User> queryAll() {
return userDao.findAll();
}
@Override
@Transactional(propagation = Propagation.SUPPORTS)
public List<User> queryByColumns(String columns) {
log.info(columns);
return userDao.findByColumns(columns);
}
}
Dao:
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface UserDao extends BaseDao {
//@Select(“select ${columns} from t_user”)
List findByColumns(@Param(“columns”)String columns);//自定义导出
void insertUsers(List users);
}
Mapper:
<批量导入>
insert into t_user (id,phone,name,nickName,sex,createDate,img,signature,password,location,salt,statuts)
values
#{item.id},#{item.phone},#{item.name},#{item.nickName},#{item.sex},#{item.createDate},#{item.img},#{item.signature},#{item.password},#{item.location},#{item.salt},#{item.statuts}
<z自定义导出>
select ${columns} from t_user
<查询所有用于展示并进行批量导出>
select id,phone,name,nickName,sex,createDate,img,signature,password,location,salt,statuts from t_user
<jsp页面 ajax>
<自定义页面 >
<%@ page language=“java” contentType=“text/html; charset=utf-8” pageEncoding=“utf-8”%>
<展示所有页面>
<%@ page language=“java” contentType=“text/html; charset=utf-8”
pageEncoding=“utf-8”%>
<%-- 根据子菜单保存的路径 点击时页面载入 --%>
<%–用来用户信息展示表格–%>
<%–用户修改的对话框–%>
<导入页面>
<%@page isELIgnored=“false” contentType=“text/html;UTF-8” pageEncoding=“UTF-8” %>
导入文件<input type="file" name="fileup" />
</table>
5802
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
