需要导入excel相应的jar包
form表单需要加上enctype=”multipart/form-data”这个属性
//封装上传文件对象
private File userExcel;
// 封装上传文件类型的属性
private String userExcelContentType;
// 封装上传文件名的属性
private String userExcelFileName;
action中所需的字段
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
action下载的方法
public String exportExcel() throws IOException{
//1.查询用户
List<User> userlist = userService.findObjects();
//2.导出excel
HttpServletResponse response = ServletActionContext.getResponse();
response.setContentType("application/x-execl");
response.setHeader("Content-Disposition", "attachment;filename=" + new String("用户列表.xls".getBytes(), "ISO-8859-1"));
ServletOutputStream outputStream = response.getOutputStream();
try {
userService.exportExcel(userlist,outputStream); //封装的工具类
} catch (Exception e) {
e.printStackTrace();
}
if(outputStream!=null){
outputStream.close();
}
return null;
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
工具类
public class ExcelUtil {
public static void exportExcelUsers(List<User> userlist,ServletOutputStream outputStream) throws Exception{
//1.创建工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 1.1创建合并单元格
CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 6);
// 1.2创建标题样式
CellStyle style1 = createStyle(workbook, 16);
// 1.3创建列标题样式
CellStyle style2 = createStyle(workbook, 12);
// 2.创建工作表
Sheet sheet = workbook.createSheet("hello world");
// 设置默认列宽
sheet.setDefaultColumnWidth(15);
// 2.1将合并单元格作用于sheet
sheet.addMergedRegion(cra);
// 3.创建行
Row row = sheet.createRow(0);
// 4.创建单元格
Cell cell = row.createCell(0);
// 5.向单元格写入数据
cell.setCellValue("用户列表");
// 6.将单元格加入样式
cell.setCellStyle(style1);
// 写列标题
String[] titles = { "用户名", "帐号", "所属部门", "性别", "手机号码", "电子邮箱", "生日" };
// 创建列标题行
Row row2 = sheet.createRow(1);
for (int i = 0; i < titles.length; i++) {
// 创建单元格
Cell cell1 = row2.createCell(i);
// 向单元格写入数据
cell1.setCellValue(titles[i]);
// 将单元格加入样式
cell1.setCellStyle(style2);
}
for (int i = 0; i < userlist.size(); i++) {
User user = userlist.get(i);
Row rowdata = sheet.createRow(i + 2);
// 姓名
Cell cell0 = rowdata.createCell(0);
cell0.setCellValue(user.getName());
// 账号
Cell cell1 = rowdata.createCell(1);
cell1.setCellValue(user.getAccount());
// 部门
Cell cell2 = rowdata.createCell(2);
cell2.setCellValue(user.getDept());
// 性别
Cell cell3 = rowdata.createCell(3);
cell3.setCellValue(user.isGender() ? "男" : "女");
// 手机
Cell cell4 = rowdata.createCell(4);
cell4.setCellValue(user.getMobile());
// 电邮
Cell cell5 = rowdata.createCell(5);
cell5.setCellValue(user.getEmail());
// 生日
Cell cell6 = rowdata.createCell(6);
if(user.getBirthday()!=null){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
cell6.setCellValue(sdf.format(user.getBirthday()));
}else{
cell6.setCellValue("");
}
}
// 6.写入硬盘文件
workbook.write(outputStream);
workbook.close();
}
//设置样式方法
public static CellStyle createStyle(Workbook workbook,int fontsize){
//1.2设置单元格样式
CellStyle style = workbook.createCellStyle();
// 设置水平居中
style.setAlignment(CellStyle.ALIGN_CENTER);
// 设置垂直居中
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
// 1.3设置字体
Font font = workbook.createFont();
// 设置字体为ARIAL
font.setFontName(HSSFFont.FONT_ARIAL);
// 设置粗体
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
// 设置字体颜色
//font.setColor(HSSFColor.BLUE.index);
// 设置字体大小
font.setFontHeightInPoints((short) fontsize);
// 将字体加入样式
style.setFont(font);
return style;
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
excel上传方法
public String importExcel(){
//先判断上传的文件是不是excel
if(userExcelFileName.matches("^.+\\.(?i)(xls|xlsx)$")){
try {
userService.importExcel(userExcel,userExcelFileName);
} catch (Exception e) {
e.printStackTrace();
}
}
return "list";
}
//dervice中的方法
public void importExcel(File userExcel, String userExcelFileName) throws Exception {
FileInputStream fis = new FileInputStream(userExcel);
boolean b = userExcelFileName.matches("^.+\\.xlsx$");
//1.创建工作薄
Workbook workbook = null;
workbook=b?new XSSFWorkbook(fis):new HSSFWorkbook(fis);
//2.获取工作表
Sheet sheet = workbook.getSheetAt(0);
int nums = sheet.getPhysicalNumberOfRows();
System.out.println(nums);
if(nums>2){
User user = null;
for(int i=2;i<nums;i++){
user = new User();
Row row = sheet.getRow(i);
//4.获取单元格
Cell namecell = row.getCell(0);
String name = namecell.getStringCellValue();
user.setName(name);
Cell accountcell = row.getCell(1);
String account = accountcell.getStringCellValue();
user.setAccount(account);
Cell deptcell = row.getCell(2);
String dept = deptcell.getStringCellValue();
user.setDept(dept);
Cell gendercell = row.getCell(3);
String gender = gendercell.getStringCellValue();
user.setGender(gender.equals("男"));
Cell mobilecell = row.getCell(4);
String mobile = null;
try {
mobile = mobilecell.getStringCellValue();
} catch (Exception e) {
mobile = BigDecimal.valueOf(mobilecell.getNumericCellValue()).toString();
}
user.setMobile(mobile);
Cell emailcell = row.getCell(5);
String email = emailcell.getStringCellValue();
user.setEmail(email);
Cell birthdaycell = row.getCell(6);
String birthday = birthdaycell.getStringCellValue();
if(birthday!=null){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
user.setBirthday(sdf.parse(birthday));
}
//设置用户状态
user.setState(User.USER_STATE_VALID);
//设置用户默认密码
user.setPassword("123456");
userDao.save(user);
}
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77