目录
1.在LogService文件基础上,新增logDownload方法
2.在LogController文件基础上,新增logDownload方法
3.改造一下LogService.upLogLoad方法,实现文件上传+文件解析
工作上我们经常会遇到,数据导入数据库,或者报表导出。这期,我就到大家用springboot实现“文件上传+下载+解析"xls"文件”。
一、文件上传
1.创建一个 LogService,先实现文件上传功能服务
@Service
public class LogService {
/**
* 文件上传
*
* @param file
* @return
* @throws Exception
*/
public ResultVo upLogLoad(MultipartFile file) throws Exception {
if (file == null || file.isEmpty()) {
return ResultVo.error(1, "未选择需上传的日志文件");
}
//获取绝对路径
String filePath = new File("src/main/java/com/example/thyy/file").getAbsolutePath();
//获取文件名
String fileName = file.getOriginalFilename();
//判断文件类型,这里我们只要xls文件
if (fileName != null && !fileName.endsWith(".xls"))
return ResultVo.error(1, "请选择“xls”文件");
//获取文件上传名称
File fileUpload = new File(filePath, fileName);
if (fileUpload.exists()) {
return ResultVo.error(1, "上传的日志文件已存在");
}
try {
//上传文件
file.transferTo(fileUpload);
return ResultVo.success();
} catch (IOException e) {
return ResultVo.error(1, "上传日志文件到服务器失败" + e.toString());
}
}
}
注意:filePath是指上传的文件,存到那个目录上。我这里我只写相对路径,然后后在通过File.getAbsolutePath()获取文件全路径。
2.创建一个控制器LogController
RestController
@RequestMapping("/log")
public class LogController {
@Autowired
LogService logService;
@PostMapping(value = "/upload")
public ResultVo logUpload(@RequestParam("file") MultipartFile file) throws Exception {
return logService.upLogLoad(file);
}
}
3.然后我们就可以启动项目,访问对应的url。
我用postman测试如下
然后就能看到我们上传的文件了
二、下载
1.在LogService文件基础上,新增logDownload方法
/**
* 下载文件
* @param name
* @param response
* @return
* @throws Exception
*/
public ResultVo logDownload(String name, HttpServletResponse response) throws Exception {
//根据name,找到文件目录下,对应的文件
File file = new File("src/main/java/com/example/thyy/file/" + File.separator + name);
if (!file.exists()) {
return ResultVo.error(1, name + "文件不存在");
}
// 声明传输的内内容类型
response.setContentType("application/force-download");
response.addHeader("Content-Disposition", "attachment;fileName=" + name);
byte[] buffer = new byte[1024];
try (FileInputStream fis = new FileInputStream(file);
BufferedInputStream bis = new BufferedInputStream(fis)) {
OutputStream os = response.getOutputStream();
int i = bis.read(buffer);
while (i != -1) {
os.write(buffer, 0, i);
i = bis.read(buffer);
}
}
return ResultVo.success();
}
2.在LogController文件基础上,新增logDownload方法
@GetMapping(value = "/downlod/{name}")
public ResultVo logDownload(@PathVariable String name, HttpServletResponse response) throws Exception {
return logService.logDownload(name, response);
}
3.访问下载url
url正确时
url错误时
注意:在下载URL后加上我们要下载的文件名。
三、解析xls文件
1.导入poi.jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
为方便写测试,再httpclient包
<dependency>
<groupId>org.apache.httpcomponents</groupId>
<artifactId>httpclient</artifactId>
<version>4.5.6</version>
</dependency>
2.创建一个解析xls的工具类
public class HandleFile{
/**
* 解析Excel文件
*
* @param in
* @param fileName
*/
public static List<List<Object>> parseExcel(InputStream in, String fileName) throws Exception {
List list = null;
Workbook work = null;
list = new ArrayList<>();
//创建Excel工作薄
work = 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++) {
String value = "";
cell = row.getCell(y);
switch (cell.getCellType()) {
// 数字
case HSSFCell.CELL_TYPE_NUMERIC:
//如果为时间格式的内容
if (HSSFDateUtil.isCellDateFormatted(cell)) {
//注:format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制,若要24小时制,则把小h变为H即可,yyyy-MM-dd HH:mm:ss
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
value=sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
li.add(value);
break;
}else {
value= String.valueOf(cell.getNumericCellValue());
String[] split = value.split("\\.");
//整型不保留小数部分
if (split[1].length()==1 && split[1].equals("0")){
value = split[0];
li.add(value);
break;
}
li.add(value);
break;
}
// 字符串
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
li.add(value);
break;
// Boolean
case HSSFCell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue() + "";
li.add(value);
break;
// 公式
case HSSFCell.CELL_TYPE_FORMULA:
value = cell.getCellFormula() + "";
li.add(value);
break;
// 空值
case HSSFCell.CELL_TYPE_BLANK:
value = "";
li.add(value);
break;
// 故障
case HSSFCell.CELL_TYPE_ERROR:
value = "非法字符";
li.add(value);
break;
default:
value = "未知类型";
li.add(value);
break;
}
}
list.add(li);
}
}
return list;
}
/**
* 判断文件格式
*
* @param inStr
* @param fileName
* @return
* @throws Exception
*/
public static 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;
}
}
3.使用HandleFile工具类
写个测试类
@Test
public void testExel() throws Exception {
// File file= new File("C:/Users/Administrator/Documents/新建 XLS 工作表.xls");
File file = new File("C:/Users/Administrator/Documents/Tencent Files/229068393/FileRecv/202110-205.xlsx");
if (!file.exists()) {
throw new Exception("不存在文件");
}
try {
FileInputStream fileInputStream = new FileInputStream(file);
MultipartFile multipartFile = new MockMultipartFile("copy" + file.getName(), file.getName(), ContentType.APPLICATION_OCTET_STREAM.toString(), fileInputStream);
InputStream inputStream = multipartFile.getInputStream();
//将文件的所有数据处理成了List<List<Object>> list
List<List<Object>> list = HandleFile.parseExcel(inputStream, multipartFile.getOriginalFilename());
inputStream.close();
for (int i = 0; i < list.size(); i++) {
List<Object> lo = list.get(i);
System.out.println(lo);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
注意:file是我们要解析的文件的路径
然后我们就得到了文件的内容了
3.改造一下LogService.upLogLoad方法,实现文件上传+文件解析
/**
* 文件上传
*
* @param file
* @return
* @throws Exception
*/
public ResultVo upLogLoad(MultipartFile file) throws Exception {
if (file == null || file.isEmpty()) {
return ResultVo.error(1, "未选择需上传的日志文件");
}
//获取绝对路径
String filePath = new File("src/main/java/com/example/springboot4/file").getAbsolutePath();
//获取上传时文件名
String fileName = file.getOriginalFilename();
//判断文件类型,这里我们只要xls文件
if (fileName != null && !fileName.endsWith(".xls"))
return ResultVo.error(1, "请选择“xls”文件");
//获取文件上传名称
File fileUpload = new File(filePath, fileName);
if (fileUpload.exists()) {
return ResultVo.error(1, "上传的日志文件已存在");
}
//改造start
InputStream inputStream = file.getInputStream();
List<List<Object>> list = HandleFile.parseExcel(inputStream, file.getOriginalFilename());
inputStream.close();
for (int i = 0; i < list.size(); i++) {
List<Object> lo = list.get(i);
//可以将数据遍历出来后,存储到数据库中
System.out.println(lo);
}
//改造end
//上传文件
try {
file.transferTo(fileUpload);
return ResultVo.success();
} catch (IOException e) {
return ResultVo.error(1, "上传日志文件到服务器失败" + e.toString());
}
}
4.最后启动服务,再上传一次文件
四、生成Excel文件
1、场景描述
比如我想导入下面的list集合数据到Excel。
2、新建@Excel注解
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 自定义导出Excel数据注解
* @author sugar
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Excel
{
/**
* 导出到Excel中时,列的名字.默认值为“未命名”
*/
public String name() default "未命名";
}
3、实体类上使用注解
package com.example.springboot4.entity;
import com.example.springboot4.annotation.Excel;
/**
* 三年消耗量
*/
public class ConsumptionEntity {
@Excel(name="物料")
public String material;
@Excel(name="组织")
public String org;
@Excel(name="数量")
public String qty;
@Excel(name="金额")
public String amount;
public ConsumptionEntity() {
}
public ConsumptionEntity(String material, String org, String qty, String amount) {
this.material = material;
this.org = org;
this.qty = qty;
this.amount = amount;
}
@Override
public String toString() {
return "ConsumptionEntity{" +
"material='" + material + '\'' +
", org='" + org + '\'' +
", qty='" + qty + '\'' +
", amount='" + amount + '\'' +
'}';
}
public String getMaterial() {
return material;
}
public void setMaterial(String material) {
this.material = material;
}
public String getOrg() {
return org;
}
public void setOrg(String org) {
this.org = org;
}
public String getQty() {
return qty;
}
public void setQty(String qty) {
this.qty = qty;
}
public String getAmount() {
return amount;
}
public void setAmount(String amount) {
this.amount = amount;
}
}
注解上name属性是到我们创建表格的时候会用到
4、新建生成表格工具类
package com.example.springboot4.util;
import com.example.springboot4.annotation.Excel;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
/**
* @ClassName: ExportExcelUtil
* @Description 构造Excel数据工具类
* @Author sugar
* @Date 2022/3/1021:55
*/
public class ExportExcelUtil<T> {
/**
* 实体对象
*/
public Class<T> clazz;
public void exportExcel(String title, List<T> dataset, OutputStream out, String pattern)
throws Exception {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为20个字节
sheet.setDefaultColumnWidth((short) 20);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 生成并设置另一个样式
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
HSSFFont font2 = workbook.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style2.setFont(font2);
if (dataset == null || dataset.size() == 0)
return;
T tempT = dataset.get(0);
//得到Object对象所有声明的字段
Field[] fields = tempT.getClass().getDeclaredFields();
//得到所有被@Excel注解到的字段
List<Field> tempFields = new ArrayList<>();
for (Field field : fields) {
if (field.isAnnotationPresent(Excel.class)) {
tempFields.add(field);
}
}
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < tempFields.size(); i++) {
Excel attr = tempFields.get(i).getAnnotation(Excel.class);
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(attr.name());
cell.setCellValue(text);
}
// 遍历集合数据,产生数据行
Iterator<T> it = dataset.iterator();
int index = 0;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
T t = (T) it.next();
for (int i = 0; i < tempFields.size(); i++) {
//列
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style2);
String fieldName = tempFields.get(i).getName();
String getMethodName =
"get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[]{});
Object value = getMethod.invoke(t, new Object[]{});
// 判断值的类型后进行强制类型转换
String textValue = null;
if (value == null) {
cell.setCellValue("");
}
if (value instanceof Integer) {
int intValue = (Integer) value;
cell.setCellValue(intValue);
} else if (value instanceof Float) {
float fValue = (Float) value;
cell.setCellValue(fValue);
} else if (value instanceof Double) {
double dValue = (Double) value;
cell.setCellValue(dValue);
} else if (value instanceof Long) {
long longValue = (Long) value;
cell.setCellValue(longValue);
} else if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
cell.setCellValue(textValue);
} else {
// 其它数据类型都当作字符串简单处理
textValue = value == null ? "" : value.toString();
cell.setCellValue(textValue);
}
}
}
workbook.write(out);
}
}
5、在LogController新增export功能
@GetMapping(value = "/export")
public String exportExcelByTime(HttpServletResponse response) {
String title = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
response.reset();
response.setHeader("Content-disposition", "attachment; filename=" + title + ".xls");
response.setContentType("application/x-xls");
ConsumptionEntity consumptionEntity=new ConsumptionEntity("012002","Org-012","2012","2000.3");
ConsumptionEntity consumptionEntity1=new ConsumptionEntity("012002","Org-012","2012","2000.3");
ConsumptionEntity consumptionEntity2=new ConsumptionEntity("012002","Org-012","2012","2000.3");
ConsumptionEntity consumptionEntity3=new ConsumptionEntity("012002","Org-012","2012","2000.3");
//查询到list数据
List<ConsumptionEntity> list = new ArrayList<>();
list.add(consumptionEntity);
list.add(consumptionEntity1);
list.add(consumptionEntity2);
list.add(consumptionEntity3);
if (list == null || list.size() < 1) {
return "没有查到数据";
}
try {
// 输出Excel文件
OutputStream out = response.getOutputStream();
ExportExcelUtil util = new ExportExcelUtil();
util.exportExcel(title, list, out, "yyyy-MM-dd");
out.close();
} catch (Exception e) {
e.printStackTrace();
}
return "导出成功";
}
6、最后看效果