引入的jar maven坐标(pom.xml )
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
实现
import com.synda.smp.log.entity.SyndaLog;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.util.List;
public interface LogService {
XSSFWorkbook creatExcel(String fileName, List<SyndaLog> ids);
}
import com.synda.smp.log.entity.SyndaLog;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
@Service
public class LogServiceImpl implements LogService{
@Override
public XSSFWorkbook creatExcel(String fileName, List<SyndaLog> list) {
XSSFWorkbook sheets = new XSSFWorkbook();
//创建一张表
Sheet sheet = sheets.createSheet(fileName);
//创建表头,创建第一行,起始为0
Row titleRow =sheet.createRow(0);
Class<SyndaLog> logClass=SyndaLog.class;
Field[] declaredFields = logClass.getDeclaredFields();
List<String> methodNames=new ArrayList<>();
int cells=0;
for (int i=0;i<declaredFields.length;i++){
cells++;
titleRow.createCell(i).setCellValue(declaredFields[i].getName());
methodNames.add("get"+declaredFields[i].getName().substring(0,1).toUpperCase()+declaredFields[i].getName().substring(1));
}
int cell = 1;
for (SyndaLog syndaLog : list) {
//从第二行开始保存数据
Row row = sheet.createRow(cell);
for (int i=0;i<methodNames.size();i++){
Method method = null;
Object invokeValue =null;
try {
method = syndaLog.getClass().getMethod(methodNames.get(i));
invokeValue=method.invoke(syndaLog, new Object[0]);
String value=invokeValue.toString();
System.out.println(methodNames.get(i)+":"+value);
//将数据库的数据遍历出来
row.createCell(i).setCellValue(value);
} catch (Exception e) {
e.printStackTrace();
}
}
cell++;
}
return sheets;
}
}
import com.synda.smp.log.repository.SyndaLogInterface;
import com.synda.smp.log.service.LogService;
import io.swagger.annotations.ApiParam;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.Assert;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.util.List;
import com.synda.smp.log.entity.SyndaLog;
import org.springframework.web.bind.annotation.RestController;
/**
* <p>
* 控制器
* </p>
*
* @author gdc
* @since 2020-07-14
*/
@RestController
@RequestMapping("/log")
public class SyndaLogController {
@Autowired
private LogService logService;
@Resource
private SyndaLogInterface repository;
public void export(HttpServletResponse response,
@ApiParam(name = "ids", value = "需要导出的id集合", required = true)
@RequestParam(name = "ids")
String[] ids,
@ApiParam(name = "fileName", value = "导出文件名(无后缀)", required = true)
@RequestParam(name = "fileName")
String fileName){
List<SyndaLog> syndaLogs = repository.findAllByIdInAndExportTagEquals(ids,0);
String result=syndaLogs.size()>0?"result":null;
Assert.notNull(result,"无数据导出");
XSSFWorkbook sheets = logService.creatExcel(fileName,syndaLogs);
this.setBrowser(response,sheets,fileName);
}
private void setBrowser(HttpServletResponse response, XSSFWorkbook workbook, String fileName) {
try {
//清空response
response.reset();
//设置response的Header
response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
OutputStream os = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/vnd.ms-excel;charset=gb2312");
//将excel写入到输出流中
workbook.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}