首先引入pom文件中需要引入的jar文件
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
在页面新增导入导出按钮
<form class="layui-form" action="" id="exportTeacher" method="post">
<div class="layui-inline">
<button type="button" class="layui-btn" id="addBtn">新增</button>
</div>
<div class="layui-inline">
所属院系名称:
<input type="hidden" name="ssyxmc" id="ssyxmc">
<div class="layui-input-inline">
<select name="ssyxdm" id="ssyxdm" lay-search lay-filter="ssyxfilter">
</select>
</div>
</div>
<div class="layui-inline">
<button type="button" class="layui-btn" id="export">导出</button>
</div>
</form>
<form class="layui-form" action="" id="importTeacher" method="post" enctype="multipart/form-data">
<input type="file" id="file" name="file">
<div class="layui-inline">
<button type="button" class="layui-btn" id="import">导入</button>
</div>
</form>
js文件按钮触发事件
$('#export').click(function () {
var ssyxmc = $("#ssyxmc").val();
var url=CTX+'teacher/export?ssyxmc='+ssyxmc;
$('#exportTeacher').attr("action",url);
$('#exportTeacher').submit();
});
$('#import').click(function () {
top.layerAlert('正在导入,请稍等!', true);
var formData = new FormData();
formData.append('file', $('#file')[0].files[0]);
$.ajax({
type: "POST",//方法类型
url: CTX+'teacher/import',//url
cache: false,
data: formData,
processData: false,
contentType: false,
success: function (result) {
if (result.code == 200) {
top.layerAlert('导入成功!', true);
getList();
};
},
error : function() {
alert("异常!");
}
});
});
后台controller写法
@PostMapping("/export")
public String export(HttpServletResponse response, HttpServletRequest request) throws IOException {
String ssyxmc = request.getParameter("ssyxmc");
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("111");
Date d = new Date();
String fileName = "111" + ".xls";
int rowNum = 1;
String[] headers = { "序号","编号","姓名","性别","民族"};//导出的Excel头部,这个要根据自己项目改一下
HSSFRow row = sheet.createRow(0);
for (short i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
//根据条件查询列表页集合
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
List<Teacher> te_list;
if(ssyxmc == null){
te_list = teacherService.findAll();
}else {
Condition condition = new Condition(Teacher.class);
condition.createCriteria()
.andEqualTo("ssyxmc", ssyxmc);
te_list = teacherService.findByCondition(condition);
}
for(int i=0;i<te_list.size();i++){
Teacher teacher = te_list.get(i);
row = sheet.createRow(i+1);
row.createCell(0).setCellValue(i+1);
row.createCell(1).setCellValue(teacher.getBh());
row.createCell(2).setCellValue(teacher.getXm());
row.createCell(4).setCellValue(teacher.getMz());
row.createCell(5).setCellValue(sdf.format(teacher.getCsrq()));
row.createCell(6).setCellValue(teacher.getSfzh());
row.createCell(8).setCellValue(teacher.getZw());
row.createCell(9).setCellValue(teacher.getLxdh());
row.createCell(10).setCellValue(teacher.getLxdz());
row.createCell(11).setCellValue(teacher.getEmail());
row.createCell(12).setCellValue(sdf.format(teacher.getCjsj()));
row.createCell(13).setCellValue(teacher.getCjr());
row.createCell(14).setCellValue(sdf.format(teacher.getGxsj()));
row.createCell(15).setCellValue(teacher.getGxr());
row.createCell(16).setCellValue(teacher.getFlag());
row.createCell(17).setCellValue(teacher.getSsyxmc());
row.createCell(18).setCellValue(teacher.getSsyxdm());
row.createCell(19).setCellValue(teacher.getZpurl());
//row.createCell(20).setCellValue(teacher.getTx());
}
response.setContentType("application/octet-stream;charset=UTF-8");
response.setHeader("Content-disposition", "attachment;filename="+new String(fileName.getBytes("gb2312"), "ISO8859-1"));
response.flushBuffer();
workbook.write(response.getOutputStream());
return null;
}
@PostMapping("/import")
public Result add(@RequestParam("file") MultipartFile file,HttpServletRequest request) throws Exception {
//检查文件
checkFile(file);
//获得Workbook工作薄对象
Workbook work = getWorkBook(file);
if (null == work) {
throw new Exception("创建Excel工作薄为空!");
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
//遍历Excel中所有的sheet
for (int i = 0; i < work.getNumberOfSheets(); i++) {
System.out.println(work.getNumberOfSheets());//获取execl总数
System.out.println(work.getSheetName(i));//获取execl sheet名称
//String sheetName = work.getSheetName(i);
sheet = work.getSheetAt(i);
if (sheet == null) {
continue;
}
//遍历当前sheet中的所有行
for (int j = 1; j <= sheet.getLastRowNum(); j++) {
System.out.println(sheet.getLastRowNum());
row = sheet.getRow(j);
if (row == null) {
continue;
}
Teacher teacher = new Teacher();
if (row.getCell(1)!=null){
teacher.setSsyxmc(getCellValue(row.getCell(1)));
}if(row.getCell(2)!=null){
teacher.setBh(getCellValue(row.getCell(2)));
}if(row.getCell(3)!=null){
teacher.setXm(getCellValue(row.getCell(3)));
}if(row.getCell(4)!=null){
teacher.setSfzh(getCellValue(row.getCell(4)));
}if(row.getCell(5)!=null){
teacher.setXb(getCellValue(row.getCell(5)));
}if(row.getCell(6)!=null){
teacher.setMz(getCellValue(row.getCell(6)));
}if(row.getCell(7)!=null){
teacher.setZzmm(getCellValue(row.getCell(7)));
}if(row.getCell(8)!=null){
teacher.setLxdh(getCellValue(row.getCell(8)));
}if(row.getCell(9)!=null){
teacher.setLxdz(getCellValue(row.getCell(9)));
}if(row.getCell(10)!=null){
teacher.setEmail(getCellValue(row.getCell(10)));
}
}
}
List<Teacher> list = teacherService.findAll();
return ResultGenerator.genSuccessResult();
}
public static String getCellValue(Cell cell){
String cellValue = "";
if(cell == null){
return cellValue;
}
//把数字当成String来读,避免出现1读成1.0的情况
if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
cell.setCellType(Cell.CELL_TYPE_STRING);
}
//判断数据的类型
switch (cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC: //数字
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING: //字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN: //Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA: //公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK: //空值
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR: //故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
public static Workbook getWorkBook(MultipartFile file) {
//获得文件名
String fileName = file.getOriginalFilename();
//创建Workbook工作薄对象,表示整个excel
Workbook workbook = null;
try {
//获取excel文件的io流
InputStream is = file.getInputStream();
//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
if(fileName.endsWith("xls")){
//2003
workbook = new HSSFWorkbook(is);
}else if(fileName.endsWith("xlsx")){
//2007
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
logger.info(e.getMessage());
}
return workbook;
}
public static void checkFile(MultipartFile file) throws IOException{
//判断文件是否存在
if(null == file){
logger.error("文件不存在!");
throw new FileNotFoundException("文件不存在!");
}
//获得文件名
String fileName = file.getOriginalFilename();
//判断文件是否是excel文件
if(!fileName.endsWith("xls") && !fileName.endsWith("xlsx")){
logger.error(fileName + "不是excel文件");
throw new IOException(fileName + "不是excel文件");
}
}