导出Excel
项目说明:
1、后台框架springboot
2、构建工具gradle
3、前端框架layui
4、jdk版本是11
项目层次结构
相关依赖包
dependencies {
implementation 'org.springframework.boot:spring-boot-starter-web'
implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'
implementation 'org.apache.commons:commons-lang3:3.8.1'
implementation 'org.springframework.boot:spring-boot-starter-json'
implementation 'org.apache.poi:poi-ooxml:3.14'
implementation 'org.apache.poi:poi:3.14'
testImplementation('org.springframework.boot:spring-boot-starter-test') {
exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
}
}
导出excel的工具类
/**
* excel导出工具类
*
*/
public class ExportExcel {
//导出表的标题
private String title;
//导出表的列名
private String[] colName;
private List<Object[]> dataList=new ArrayList<>();
//构造函数。传入要导入的数据
public ExportExcel(String title, String[] colName, List<Object[]> dataList) {
this.title = title;
this.colName = colName;
this.dataList = dataList;
}
/**
* 导出数据
*/
public void export(OutputStream out){
//工作薄对象
Workbook workbook = new XSSFWorkbook();
var sheet = workbook.createSheet(title);
//产生表格标题行
var rowTitle = sheet.createRow(0);
rowTitle.setHeightInPoints(30);
var cellTitle = rowTitle.createCell(0);
//表格样式地定义
var getTitleTopStyle = this.getTitleTopStyle(workbook);
var columnTopStyle = this.getColumnTopStyle(workbook);
var style = this.getStyle(workbook);
cellTitle.setCellStyle(getTitleTopStyle);
cellTitle.setCellValue(title);
sheet.addMergedRegion(new CellRangeAddress(0,
0, 0, colName.length-1));
//定义所需列数
var columnNum = colName.length;
var rowRowNmae = sheet.createRow(1);
//将列头设置到表格的单元格中
for (var i = 0; i < columnNum; i++) {
var cellRowName = rowRowNmae.createCell(i);
cellRowName.setCellType(Cell.CELL_TYPE_STRING);
RichTextString text = new XSSFRichTextString(colName[i]);
cellRowName.setCellValue(text);
cellRowName.setCellStyle(columnTopStyle);
}
//将查询的数据设置到sheet对应的单元格中
if (dataList.size()>0){
for (var i = 0; i < dataList.size(); i++) {
var objects = dataList.get(i);//遍历每个对象
var row = sheet.createRow(i + 2);
for (var a = 0; a < objects.length; a++) {
var cell = row.createCell(a, HSSFCell.CELL_TYPE_STRING);
if (!"".equals(objects[a]) && objects[a] != null) {
cell.setCellValue(objects[a].toString());
} else {
cell.setCellValue("");
}
cell.setCellStyle(style);
}
}
}
//让列宽随着导出的列长自动适应
for (var colNum = 0; colNum < columnNum; colNum++) {
var columnWidth = sheet.getColumnWidth(colNum) / 256;
for (var rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++){
Row currentRow;
if (sheet.getRow(rowNum) == null){
currentRow=sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(colNum) != null) {
Cell currentCell = currentRow.getCell(colNum);
if (currentCell.getCellType() ==Cell.CELL_TYPE_STRING) {
var length=0;
if (currentCell.getStringCellValue()!=null){
length = currentCell.getStringCellValue().getBytes().length;
}
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
sheet.setColumnWidth(colNum,(columnWidth + 4) * 256);
}
if (workbook != null){
try {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
}
}
}
/*
*列头单元格样式
*/
public CellStyle getColumnTopStyle(Workbook workbook) {
var styles = new HashMap<String, CellStyle>();
// 设置样式
CellStyle style = workbook.createCellStyle();
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
Font dataFont = workbook.createFont();
dataFont.setFontName("Arial");
dataFont.setFontHeightInPoints((short) 10);
style.setFont(dataFont);
styles.put("data", style);
// 设置样式
style = workbook.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
// style.setWrapText(true);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
// 设置字体
Font headerFont = workbook.createFont();
headerFont.setFontName("Arial");
headerFont.setFontHeightInPoints((short) 10);
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
headerFont.setColor(IndexedColors.WHITE.getIndex());
style.setFont(headerFont);
return style;
}
/*
*标题单元格样式
*/
public CellStyle getTitleTopStyle(Workbook workbook) {
// 设置字体
Font font = workbook.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) 13);
// 字体加粗
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
// 设置字体名字
font.setFontName("Arial");
// 设置样式
CellStyle style = workbook.createCellStyle();
//设置单元格的水平对齐类型
style.setAlignment(CellStyle.ALIGN_CENTER);
//设置单元格的垂直对齐类型
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
// 设置低边框
style.setBorderBottom(CellStyle.BORDER_THIN);
// 设置低边框颜色
style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
// 设置右边框
style.setBorderRight(CellStyle.BORDER_THIN);
// 设置顶边框
//style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
// 设置顶边框颜色
//style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
// 在样式中应用设置的字体
style.setFont(font);
// 设置自动换行
style.setWrapText(false);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
return style;
}
public CellStyle getStyle(Workbook workbook) {
// 设置字体
Font font = workbook.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) 10);
// 字体加粗
//font.setBoldweight(Font.BOLDWEIGHT_BOLD);
// 设置字体名字
font.setFontName("Arial");
// 设置样式;
CellStyle style = workbook.createCellStyle();
// 设置底边框;
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
// 设置底边框颜色;
style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
// 设置左边框;
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
// 设置左边框颜色;
style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
// 设置右边框;
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
// 设置右边框颜色;
style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
// 设置顶边框;
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
// 设置顶边框颜色;
style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
// 在样式用应用设置的字体;
style.setFont(font);
// 设置自动换行;
style.setWrapText(false);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
return style;
}
}
Service层
@Service
public class ExcelService {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelService.class);
//excel标题
private String title = "";
//表格数据
private List<Object[]> dataList;
//excel列头信息
private String[] colsName;
/**
* 导出excel
*/
public void exportExcel(HttpServletResponse response){
title = "导入标题";
dataList = new ArrayList<Object[]>();
colsName = new String[] { "书籍编号", "借书人", "借书时间", "预计还书时间", "借书状态" };
for (int i = 0; i < 6; i++) {
var objects = new Object[colsName.length];
objects[0] = "HK200"+i;
objects[1] = "小明"+i;
Timestamp currentTimestamp=Timestamp.valueOf(LocalDateTime.now());
objects[2] = currentTimestamp;
objects[3] = "未定";
if (i%2 == 0) {
objects[4] = "借书中";
}else {
objects[4] = "已还书";
}
dataList.add(objects);
}
try {
response.reset();
response.setContentType("application/octet-stream; charset=utf-8");
response.setHeader("Content-disposition", "attachment;filename="+ URLEncoder.encode("导出表名.xlsx", "UTF-8"));
var out = response.getOutputStream();
var exportExcel = new ExportExcel(title, colsName, dataList);
exportExcel.export(out);
out.flush();
out.close();
LOGGER.info("导出Excel成功");
} catch (IOException e) {
e.printStackTrace();
LOGGER.info("导出Excel失败");
}
}
/**
*导入excel
*/
public JsonNode importExcel(MultipartFile file){
var root= JsonUtil.OBJECT_MAPPER.createObjectNode();
root.put("result","fail");
if (file.isEmpty()){
root.put("cause","必要参数未找到");
return root;
}
ImportExcel excel;
try {
excel=new ImportExcel(file);
}catch (Exception e){
root.put("cause","文件识别失败");
return root;
}
try {
dataList=excel.getColNames();
} catch (IOException e) {
e.printStackTrace();
root.put("cause","数据解析失败");
return root;
}
//dataList表示拿到excel的数据列表,这里不做存储,只输出
dataList.forEach(data->{
for (var i=0;i<data.length-1;i++){
System.out.print(data[i]+"\t");
}
System.out.println();
});
root.put("result","success");
return root;
}
}
Controller层
@Controller
public class ExcelController {
private ExcelService excelService;;
@Autowired
public ExcelController(ExcelService excelService) {
this.excelService = excelService;
}
@GetMapping(value = "export",produces = "application/json;charset=UTF-8")
public void export(HttpServletResponse response) {
excelService.exportExcel(response);
}
@PostMapping(value = "import",produces = "application/json;charset=UTF-8")
@ResponseBody
public JsonNode importAsset(@RequestParam("file") MultipartFile multipartFile) {
return excelService.importExcel(multipartFile);
}
@GetMapping("/index")
public String index() {
return "web/index";
}
}
前端页面
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>excel的导入与导出</title>
<link rel="stylesheet" th:href="@{/layui/css/layui.css}" media="all" />
</head>
<body>
<button onclick="exportExcel();"><h4>导出Excel</h4></button></br>
<fieldset class="layui-elem-field " style="margin-top: 30px;">
<legend>选择要导入的资产文件</legend>
</fieldset>
<div class="layui-upload">
<button type="button" class="layui-btn " id="test8"><i class="layui-icon"></i>选择文件</button>
<button type="button" class="layui-btn" id="test9">开始上传</button>
</div>
</body>
<script type="text/javascript" th:src="@{/layui/layui.js}"></script>
<script>
layui.use('upload', function() {
var $ = layui.jquery,
upload = layui.upload;
upload.render({
elem: '#test8',
url: 'http://localhost/import', //改成您自己的上传接口
auto:false,
accept: 'file',
bindAction: '#test9',
done: function(res){
if (res['result'] == 'fail'){
layer.msg(res['cause']);
} else{
layer.msg('上传成功');
}
console.log(res)
}
});
})
//excel导出
function exportExcel() {
window.location.href="http://localhost/export";
}
</script>
</html>
最后实现效果
导入excel的工具类(其它方法在上面类中已经写好)
/**
* 导入excel内容的工具类
* @author ClowLAY
* create date 2020/3/25
*/
public class ImportExcel {
//文件对象
private MultipartFile file;
/**
* 工作薄对象
*/
private Workbook workbook;
public ImportExcel(MultipartFile file){
this.file = file;
}
/**
* 返回除标题的外的所有数据,第一个对象数组为列数据
* @return
*/
public List<Object[]> getColNames() throws IOException {
var dataList=new ArrayList<Object[]>();
var rowSize=0;
//根据excel表格式新建表对象
var in = new BufferedInputStream(file.getInputStream());
if (StringUtils.isBlank(file.getOriginalFilename())) {
throw new IOException("Import file is empty!");
} else if (file.getOriginalFilename().toLowerCase().endsWith("xls")) {
this.workbook = new HSSFWorkbook(in);
} else if (file.getOriginalFilename().toLowerCase().endsWith("xlsx")) {
this.workbook = new XSSFWorkbook(in);
} else {
throw new IOException("Invalid import file type!");
}
Cell cell=null;
for (var sheetIndex=0;sheetIndex<workbook.getNumberOfSheets();sheetIndex++){
//获取指定索引的表对象
var sheetAt = workbook.getSheetAt(sheetIndex);
//第一行作为标题,不取
for (var rowIndex=1;rowIndex<=sheetAt.getLastRowNum();rowIndex++) {
var row=sheetAt.getRow(rowIndex);
if (row == null){
continue;
}
//获取一列单元格数量
var tempRowSize = row.getLastCellNum();
if (tempRowSize > rowSize){
rowSize = tempRowSize;
}
var values=new Object[rowSize];
boolean hasValue = false;
for (var columnIndex = 0 ;columnIndex < row.getLastCellNum(); columnIndex++ ){
Object value="";
cell = row.getCell(columnIndex);
if (cell != null){
switch (cell.getCellType()){
case Cell.CELL_TYPE_STRING :
value = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC :
if (HSSFDateUtil.isCellDateFormatted(cell)){
var date=cell.getDateCellValue();
if (date != null){
value = new SimpleDateFormat("yyyy-MM-dd").format(date);
}else
value="";
} else {
value= new DecimalFormat("0").format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_FORMULA :
value=cell.getCellFormula();
break;
case Cell.CELL_TYPE_BOOLEAN :
value=cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK :
value="";
break;
case Cell.CELL_TYPE_ERROR :
value=cell.getErrorCellValue();
break;
default :
value="";
}
}
//如果当前行的第一列为空,则跳过
if (columnIndex == 0 && value.toString().trim().equals("")) {
break;
}
values[columnIndex] = value.toString().trim();
hasValue = true;
}
if (hasValue) {
dataList.add(values);
}
}
}
in.close();
return dataList;
}
/**
* 导入测试
*/
/*public static void main(String[] args) throws Throwable {
ImportExcel excel = new ImportExcel(new File("D:/2020-03-23.xlsx"));
var dataList=excel.getColNames();
System.out.println("size="+dataList.size());
dataList.forEach(data->{
for (var i=0;i<data.length-1;i++){
System.out.print(i+","+data[i]+"\t");
}
System.out.println();
});
}*/
}
最后把获取的表内容输出到控制台