springboot从数据库导出excel
一、导入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
二、html代码
<table border="0" style="margin-top:4px; margin-left: 18px"> <tr> <td><a href="#" class="easyui-linkbutton" onclick="downloadfile();">数据导出</a></td> </tr> </table>
三、js代码
<script> function downloadfile(){ window.location.href="/UserExcelDownloads"; } </script>
四、Controller代码
@RequestMapping(value = "UserExcelDownloads", method = RequestMethod.GET) public void downloadAllClassmate(HttpServletResponse response) throws IOException { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("信息表"); List<UserTable> classmateList = service.all(); String fileName = "用户表" + ".xls";//设置要导出的文件的名字 //新增数据行，并且设置单元格数据 int rowNum = 1; String[] headers = { "id", "姓名", "密码", "身份证","手机","邮箱","性别","爱好","注册时间"}; //headers表示excel表中第一行的表头 HSSFRow row = sheet.createRow(0); //在excel表中添加表头 for(int i=0;i<headers.length;i++){ HSSFCell cell = row.createCell(i); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); } //在表中存放查询到的数据放入对应的列 for (UserTable user : classmateList) { HSSFRow row1 = sheet.createRow(rowNum); row1.createCell(0).setCellValue(user.getId()); row1.createCell(1).setCellValue(user.getName()); row1.createCell(2).setCellValue(user.getPwd()); row1.createCell(3).setCellValue(user.getIdcard()); row1.createCell(4).setCellValue(user.getPhone()); row1.createCell(5).setCellValue(user.getEmail()); row1.createCell(6).setCellValue(user.getSex()); row1.createCell(7).setCellValue(user.getHobby()); Date date=user.getTime(); SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String time=sdf.format(date); row1.createCell(8).setCellValue(time); rowNum++; } response.setContentType("application/octet-stream"); response.setHeader("Content-disposition", "attachment;filename=" + fileName); response.flushBuffer(); workbook.write(response.getOutputStream()); }
五、service代码
public List<UserTable> all(){ return mapper.selectByExample(null); }
六、User实体类
package com.text.pojo; import java.util.Date; public class UserTable { private Integer id; private String name; private String pwd; private String idcard; private String phone; private String email; private String sex; private String hobby; private Date time; private String timeS; public String getTimeS() { return timeS; } public void setTimeS(String timeS) { this.timeS = timeS; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name == null ? null : name.trim(); } public String getPwd() { return pwd; } public void setPwd(String pwd) { this.pwd = pwd == null ? null : pwd.trim(); } public String getIdcard() { return idcard; } public void setIdcard(String idcard) { this.idcard = idcard == null ? null : idcard.trim(); } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone == null ? null : phone.trim(); } public String getEmail() { return email; } public void setEmail(String email) { this.email = email == null ? null : email.trim(); } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex == null ? null : sex.trim(); } public String getHobby() { return hobby; } public void setHobby(String hobby) { this.hobby = hobby == null ? null : hobby.trim(); } public Date getTime() { return time; } public void setTime(Date time) { this.time = time; } }
package com.text.controller;
import com.text.service.ImportService;
import com.text.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import java.io.File;
import java.io.InputStream;
import java.util.List;
@Controller
public class ImportController {
@Autowired
private ImportService importService;
@Autowired
UserService service;
@RequestMapping("/upload")
public String uploadExcel( Model model, MultipartFile file) throws Exception {
String filePath="C:\\Users\\Administrator\\Desktop";
//获得原始图片的拓展名
String originalFileName=file.getOriginalFilename();
//封装上传文件位置的全路径
File targetFile=new File(filePath,originalFileName);
InputStream inputStream = file.getInputStream();
List<List<Object>> list = importService.getBankListByExcel(inputStream, originalFileName);
inputStream.close();
for (int i = 0; i < list.size(); i++) {
List<Object> lo = list.get(i);
for(int j=0;j<lo.size();j++){
Object o = lo.get(j);
}
}
return "main";
}
}
package com.text.service;
import com.microsoft.schemas.office.visio.x2012.main.CellType;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
/**
* @author: curry
* @Date: 2018/8/16
*/
@Service
public class ImportService {
/**
* 处理上传的文件
*
* @param in
* @param fileName
* @return
* @throws Exception
*/
public List getBankListByExcel(InputStream in, String fileName) throws Exception {
List list = new ArrayList<>();
//创建Excel工作薄
Workbook work = this.getWorkbook(in, fileName);
if (null == work) {
throw new Exception("创建Excel工作薄为空！");
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if (sheet == null) {
continue;
}
for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
row = sheet.getRow(j);
if (row == null || row.getFirstCellNum() == j) {
continue;
}
List<Object> li = new ArrayList<>();
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
li.add(cell);
}
list.add(li);
}
}
work.close();
return list;
}
/**
* 判断文件格式
*
* @param inStr
* @param fileName
* @return
* @throws Exception
*/
public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
Workbook workbook = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if (".xls".equals(fileType)) {
workbook = new HSSFWorkbook(inStr);
} else if (".xlsx".equals(fileType)) {
workbook = new XSSFWorkbook(inStr);
} else {
throw new Exception("请上传excel文件！");
}
return workbook;
}
}
