需求1:依据给定的excel,将其内容写入mysql数据库;
第一步:在mysql中建数据表,注意字段的命名,主键设置自增长,数据库表结构如下:
第二步:添加pom依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.2</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
<dependency>
<groupId>org.springframework.data</groupId>
<artifactId>spring-data-commons</artifactId>
<version>2.1.6.RELEASE</version>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>
第三步:借助mybatis逆向工程自动生成代码
①配置数据源,连接本地数据库
②选中对应数据表
②填写包名,勾选需要生成的文件
第四步:上传文件并写入数据库核心逻辑
FormulaController.java部分代码:
@RequestMapping(value = "/uploadFiles")
public @ResponseBody
Map<String ,Object> uploadExcl(HttpServletRequest request, @RequestParam("file") MultipartFile file){
Map<String ,Object> result = new HashMap<>();
String path = request.getSession().getServletContext().getRealPath("/");
try{
// 如果文件不为空,写入上传路径
if(!file.isEmpty()){
result = formulaService.uploadFiles(file);
}else {
result.put("code","1");
result.put("message","上传文件为空!");
}
}catch (Exception e){
e.printStackTrace();
}
if (result.get("code").equals("0")){
//根据时间戳创建新的文件名,这样即便是第二次上传相同名称的文件,也不会把第一次的文件覆盖了
//也可以用UUID创建
String fileName = System.currentTimeMillis() + file.getOriginalFilename();
//通过req.getServletContext().getRealPath("") 获取当前项目的真实路径,然后拼接前面的文件名
String destFileName = request.getContextPath()+ "uploaded" + File.separator + fileName;
System.out.println(request.getServletPath());
System.out.println(request.getServletContext());
System.out.println(request.getServletContext().getRealPath(""));
System.out.println(request.getServletContext().getRealPath("/"));
System.out.println(request.getContextPath());
System.out.println(destFileName);
//第一次运行的时候,这个文件所在的目录往往是不存在的,这里需要创建一下目录
File destFile = new File(destFileName);
destFile.getParentFile().mkdirs();
System.out.println(destFile);
//把浏览器上传的文件复制到希望的位置
try {
file.transferTo(destFile);
} catch (IOException e) {
e.printStackTrace();
}
System.out.println(fileName);
}
return result;
}
FormulaServiceImpl.java部分代码
@Override
public Map<String, Object> uploadFiles(MultipartFile file) {
Map<String,Object> ruslt = new HashMap<>();
try {
String fileName = file.getOriginalFilename();
//判断文件格式并获取工作簿
Workbook workbook;
if(fileName.endsWith("xls")){
workbook = new HSSFWorkbook(file.getInputStream());
}else if(fileName.endsWith("xlsx")){
workbook = new XSSFWorkbook(file.getInputStream());
} else {
ruslt.put("code","1");
ruslt.put("message","文件格式非excl");
return ruslt;
}
//判断第一页不为空
if(null != workbook.getSheetAt(0)){
//读取excl第二行,从1开始
for(int rowNumofSheet = 1;rowNumofSheet <=workbook.getSheetAt(0).getLastRowNum();rowNumofSheet++){
if (null != workbook.getSheetAt(0).getRow(rowNumofSheet)) {
//定义行,并赋值
Row aRow = workbook.getSheetAt(0).getRow(rowNumofSheet);
Formula formula = new Formula();
System.out.println(aRow.getLastCellNum());
for(int cellNumofRow=0;cellNumofRow<aRow.getLastCellNum();cellNumofRow++){
//读取rowNumOfSheet值所对应行的数据
//获得行的列数
Cell xCell = aRow.getCell(cellNumofRow);
xCell.setCellType(CellType.STRING);
Object cell_val;
if(cellNumofRow == 1){
if(xCell != null && !xCell.toString().trim().isEmpty()){
cell_val = xCell.getStringCellValue();
if(cell_val != null){
String temp = (String)cell_val;
formula.setPatientId(Integer.parseInt(temp));
}
}
}
if(cellNumofRow == 2){
if(xCell != null && !xCell.toString().trim().isEmpty()){
cell_val = xCell.getStringCellValue();
if(cell_val != null){
String medname = (String)cell_val;
formula.setMedicineName(medname);
}
}
}
if(cellNumofRow == 3){
if(xCell != null && !xCell.toString().trim().isEmpty()){
cell_val = xCell.getStringCellValue();
if(cell_val != null){
String gfh = (String)cell_val;
formula.setNormalizeName(gfh);
}
}
}
if(cellNumofRow == 4){
if(xCell != null && !xCell.toString().trim().isEmpty()){
cell_val = xCell.getStringCellValue();
if(cell_val != null){
String fl = (String)cell_val;
formula.setClassification(fl);
}
}
}
}
formulaDao.insert(formula);
}
}
ruslt.put("code","0");
ruslt.put("message","成功插入数据库!");
}else {
ruslt.put("code","1");
ruslt.put("message","第一页EXCL无数据!");
}
}catch (Exception e){
e.printStackTrace();
ruslt.put("code","1");
ruslt.put("message",e.getMessage());
}
return ruslt;
}
部分页面代码:
home.html
<a href="/upload2">上传文件</a> <a th:href="@{'/results/download'}">导出文件</a>
upload2.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<div class="panel panel-primary">
<!-- .panel-heading 面板头信息。 -->
<div class="panel-heading">
<!-- .panel-title 面板标题。 -->
<h1 class="panel-title">上传excel并插入到数据库</h1>
</div>
</div>
<form class="form-horizontal" action="/formula/uploadFiles" enctype="multipart/form-data" method="post">
<div class="form-group">
<div class="input-group col-md-4">
<span class="input-group-addon">
<i class="glyphicon glyphicon-search"></i>
</span>
<input class="form-control" placeholder="请选择文件" type="file" name="file"/>
</div>
</div>
<div class="form-group">
<div class="col-md-4">
<div class="btn-group btn-group-justified" >
<div class="btn-group" >
<button type="submit" class="btn btn-success" id="submitbtn">
<span class="glyphicon glyphicon-share"></span> 文件上传</button>
</div>
</div>
</div>
</div>
</form>
需求2:将数据库的数据导出并写入excel
导出文件模板如下:
同理,重复上述第一步和第三步,为导出结果创建实体类和相关代码;
实体类需要添加@Data和@ExcelProperty的注解,便于数据写入excel。本项目的results实体类如下:
@Data
public class Results implements Serializable {
private static final long serialVersionUID = -15605474560361154L;
@ExcelProperty(value="ID", index=0)
private Integer rid;
@ExcelProperty(value="住院号", index=1)
private Integer pid;
@ExcelProperty(value="中药名称", index=2)
private String name;
@ExcelProperty(value="基本信息代码", index=3)
private String infoCodes;
@ExcelProperty(value="药性代码", index=4)
private String propertyCodes;
@ExcelProperty(value="功能代码", index=5)
private String functionCodes;
@ExcelProperty(value="主治代码", index=6)
private String treatmentCodes;
@ExcelProperty(value="禁忌代码", index=7)
private String tabooCodes;
@ExcelProperty(value="贮藏方式代码", index=8)
private String storageCodes;
public Integer getRid() {
return rid;
}
public void setRid(Integer rid) {
this.rid = rid;
}
public Integer getPid() {
return pid;
}
public void setPid(Integer pid) {
this.pid = pid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getInfoCodes() {
return infoCodes;
}
public void setInfoCodes(String infoCodes) {
this.infoCodes = infoCodes;
}
public String getPropertyCodes() {
return propertyCodes;
}
public void setPropertyCodes(String propertyCodes) {
this.propertyCodes = propertyCodes;
}
public String getFunctionCodes() {
return functionCodes;
}
public void setFunctionCodes(String functionCodes) {
this.functionCodes = functionCodes;
}
public String getTreatmentCodes() {
return treatmentCodes;
}
public void setTreatmentCodes(String treatmentCodes) {
this.treatmentCodes = treatmentCodes;
}
public String getTabooCodes() {
return tabooCodes;
}
public void setTabooCodes(String tabooCodes) {
this.tabooCodes = tabooCodes;
}
public String getStorageCodes() {
return storageCodes;
}
public void setStorageCodes(String storageCodes) {
this.storageCodes = storageCodes;
}
}
数据导出的核心逻辑:
①ResultsDao.java中添加导出方法
void downloadExcel(HttpServletResponse response);
②ResultsService.java中添加导出方法
//导出
void downloadExcel(HttpServletResponse response);
③ResultsServiceImpl.java中实现导出逻辑
@Override
public void downloadExcel(HttpServletResponse response) {
try {
List<Results> rlist = new ArrayList<>();
rlist = resultsDao.getList();
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("编码结果", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream()).head(Results.class).excelType(ExcelTypeEnum.XLSX).sheet("编码信息").doWrite(rlist);
} catch (Exception e) {
System.err.println("导出文件异常"+e);
}
}
④ResultsController.java
@GetMapping("/download")
public void downloadExcel(HttpServletResponse response) {
resultsService.downloadExcel(response);
}
遇到的问题:
①数据库语法错误:check the manual that corresponds to your MySQL server version for the right syntax to use near 'function, treatment, taboo, storage)
解决办法:修改function, treatment, taboo, storage这四个字段的名字