概述
目录
-
- 后台代码实现导出excel文件到指定目录（导出）
- 后台代码实现读指定目录的excel文件（导入）
- 前端vue实现在浏览器导出excel（导出）
- 后端代码实现在浏览器导出excel（导出）
首先在pom文件引入依赖
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
</dependencies>
后台代码实现导出excel文件到指定目录（导出）
先建一个dog类
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Dog {
// index 表示这个属性是在excel的第几列（从0开始），value 表示在当前列的表头名称
@ExcelProperty("狗编号")
private Integer dogNo;
@ExcelProperty("狗名称")
private String dogName;
@ExcelProperty("狗年龄")
private Integer dogAge;
}
然后测试类
import com.alibaba.excel.EasyExcel;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;
public class Client {
public static void main(String[] args) {
//写入的路径
String fileName = "D:Dog.xlsx";
EasyExcel.write(fileName,Dog.class).sheet("狗").doWrite(getLists());
}
//模仿从后台拿到一个Dog对象的集合 (我生成了5个一样的dog狗)
public static List<Dog> getLists(){
return Stream.generate(()->new Dog(2,"阿花",28)).limit(5).collect(Collectors.toList());
}
}
运行之后会看到路径下有个Dog.xlsx文件，效果如下：
后台代码实现读指定目录的excel文件（导入）
创建EasyListener类
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.Map;
public class EasyListener extends AnalysisEventListener<Dog> {
//一行一行读取excel内容
@Override
public void invoke(Dog dog, AnalysisContext analysisContext) {
System.out.println("数据"+dog);
}
//读取表头内容
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
System.out.println("表头:"+headMap);
}
//读取完之后
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("我读完了");
}
}
测试类代码：
public class Client {
public static void main(String[] args) {
//写入的路径
String fileName = "D:Dog.xlsx";
// EasyExcel.write(fileName,Dog.class).sheet("狗").doWrite(getLists());
EasyExcel.read(fileName,Dog.class,new EasyListener()).sheet("狗").doRead();
}
//模仿从后台拿到一个Dog对象的集合 (我生成了5个一样的dog狗)
public static List<Dog> getLists(){
return Stream.generate(()->new Dog(2,"阿花",28)).limit(5).collect(Collectors.toList());
}
}
输出结果如下：
前端vue实现在浏览器导出excel（导出）
npm 下载 file-saver 和 xlsx 这两个依赖文件
npm install file-saver@1.3.8 xlsx@0.14.1 --save
导出js文件代码存放在这
Export2Excel.js代码整理好了，copy过去即可
/* eslint-disable */
import { saveAs } from 'file-saver'
import XLSX from 'xlsx'
function generateArray(table) {
var out = [];
var rows = table.querySelectorAll('tr');
var ranges = [];
for (var R = 0; R < rows.length; ++R) {
var outRow = [];
var row = rows[R];
var columns = row.querySelectorAll('td');
for (var C = 0; C < columns.length; ++C) {
var cell = columns[C];
var colspan = cell.getAttribute('colspan');
var rowspan = cell.getAttribute('rowspan');
cell.setAttribute('mso-number-format','@');//mso-number-format:'@';
var cellValue = cell.innerText;
if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;
//Skip ranges
ranges.forEach(function (range) {
if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {
for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
}
});
//Handle Row Span
if (rowspan || colspan) {
rowspan = rowspan || 1;
colspan = colspan || 1;
ranges.push({s: {r: R, c: outRow.length}, e: {r: R + rowspan - 1, c: outRow.length + colspan - 1}});
}
;
//Handle Value
outRow.push(cellValue !== "" ? cellValue : null);
//Handle Colspan
if (colspan) for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
}
out.push(outRow);
}
return [out, ranges];
};
function datenum(v, date1904) {
if (date1904) v += 1462;
var epoch = Date.parse(v);
return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}
function sheet_from_array_of_arrays(data, opts) {
var ws = {};
var range = {s: {c: 10000000, r: 10000000}, e: {c: 0, r: 0}};
for (var R = 0; R != data.length; ++R) {
for (var C = 0; C != data[R].length; ++C) {
if (range.s.r > R) range.s.r = R;
if (range.s.c > C) range.s.c = C;
if (range.e.r < R) range.e.r = R;
if (range.e.c < C) range.e.c = C;
var cell = {v: data[R][C]};
if (cell.v == null) continue;
var cell_ref = XLSX.utils.encode_cell({c: C, r: R});
if (typeof cell.v === 'number') cell.t = 'n';
else if (typeof cell.v === 'boolean') cell.t = 'b';
else if (cell.v instanceof Date) {
cell.t = 'n';
cell.z = XLSX.SSF._table[14];
cell.v = datenum(cell.v);
}
else cell.t = 's';
ws[cell_ref] = cell;
}
}
if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
return ws;
}
function Workbook() {
if (!(this instanceof Workbook)) return new Workbook();
this.SheetNames = [];
this.Sheets = {};
}
function s2ab(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
export function export_table_to_excel(id) {
var theTable = document.getElementById(id);
var oo = generateArray(theTable);
var ranges = oo[1];
/* original data */
var data = oo[0];
var ws_name = "SheetJS";
var wb = new Workbook(), ws = sheet_from_array_of_arrays(data);
/* add ranges to worksheet */
// ws['!cols'] = ['apple', 'banan'];
ws['!merges'] = ranges;
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
var wbout = XLSX.write(wb, {bookType: 'xlsx', bookSST: false, type: 'binary'});
saveAs(new Blob([s2ab(wbout)], {type: "application/octet-stream"}), "test.xlsx")
}
export function export_json_to_excel({header, data, filename='excel-list', autoWidth=true}={}) {
/* original data */
data=[...data]
data.unshift(header);
var ws_name = "SheetJS";
var wb = new Workbook(), ws = sheet_from_array_of_arrays(data);
if(autoWidth){
/*设置worksheet每列的最大宽度*/
const colWidth = data.map(row => row.map(val => {
/*先判断是否为null/undefined*/
if (val == null) {
return {'wch': 10};
}
/*再判断是否为中文*/
else if (val.toString().charCodeAt(0) > 255) {
return {'wch': val.toString().length * 2};
} else {
return {'wch': val.toString().length};
}
}))
/*以第一行为初始值*/
let result = colWidth[0];
for (let i = 1; i < colWidth.length; i++) {
for (let j = 0; j < colWidth[i].length; j++) {
if (result[j]['wch'] < colWidth[i][j]['wch']) {
result[j]['wch'] = colWidth[i][j]['wch'];
}
}
}
ws['!cols'] = result;
}
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
var wbout = XLSX.write(wb, {bookType: 'xlsx', bookSST: false, type: 'binary'});
saveAs(new Blob([s2ab(wbout)], {type: "application/octet-stream"}), filename + ".xlsx");
}
在utils下建一个公共的导出js，代码也整理好了
/*
* @method 表格数据导出excel
* @param {object} option 参数配置
* @params {array} tHeader 表头文字 默认 []
* @params {array} filterVal 对应字段名 默认 []
* @params {array} table 表格数据 默认 []
* @params {string} fileName 导出excel文件名 默认 excel-file
* @param {function} 导出成功回调
* */
const formatJson = (filterVal, jsonData) => {
return jsonData.map(v => filterVal.map(j => v[j]))
}
export const exportFile = (option = {}, cb) => {
import('@/vendor/Export2Excel').then(excel => {
const tHeader = option.tHeader || [] // 对应表格输出的title
console.log(tHeader);
const filterVal = option.filterVal || []
const table = option.table || []
const data = formatJson(filterVal, table)
// const autoWidth = option.autoWidth || true //列宽是否自适应
const autoWidth = option.autoWidth == undefined ? true : option.autoWidth //列宽是否自适应
excel.export_json_to_excel({
header: tHeader,
data,
filename: option.fileName || 'excel-file',
autoWidth:autoWidth
})
cb && cb()
})
}
然后在vue页面加导出按钮
<el-button size="mini" icon="el-icon-download" type="primary" @click="exportData" :loading="exportLoading">导出</el-button>
引入
import { exportFile } from "@/utils/exportExcel"; //导出
import moment from 'moment';
在data加
exportLoading:false
在methods里
//导出
exportData(){
this.exportLoading = true
let list = [
{dogNo:2,dogName:"阿花",dogAge:28},
{dogNo:3,dogName:"阿花1",dogAge:3},
{dogNo:4,dogName:"阿花2",dogAge:5},
{dogNo:5,dogName:"阿花3",dogAge:9},
]
console.log(list)
this.exportExcel(list)
},
exportExcel(table) { //导出模板和参数列名
const option = {
tHeader: [
"狗编号",
"狗名称",
"狗年龄",
],
filterVal: [
"dogNo",
"dogName",
"dogAge",
],
table: table,
fileName: "狗" + moment().format('YYYY-MM-DD HH_mm_ss')
};
exportFile(option, () => {
this.exportLoading = false;
});
},
浏览器效果：
后端代码实现在浏览器导出excel（导出）
新建一个springboot项目，用mybatisplus先搭好框架。
然后添加依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.6</version>
</dependency>
这里我把核心代码都放到controller上写，其它的都是spring boot + mybatisPlus的简单的搭建
package com.cjw.cjwExport.contoller;
import com.cjw.cjwExport.model.Student;
import com.cjw.cjwExport.service.ExportService;
import org.apache.poi.hssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
@RestController
@RequestMapping("/Export")
public class ExportController {
@Autowired
private ExportService exportService;
@RequestMapping("/test01")
@GetMapping
public void exportTest01(HttpServletResponse response){
exportService.exportTest01();
System.out.println("=====export====");
List<Student> list = exportService.list(); //做一个简单的查询
// Excel标题
String[] title = {"id", "课程id", "标题", "课本","时间"};
// Excel文件名
String fileName = "测试.xls";
// sheet名
String sheetName = "测试1";
// 将数据放到数组中
String[][] content = new String[list.size()][title.length];
for (int i = 0; i < list.size(); i++) {
Date date = new Date();
SimpleDateFormat dateFormat = new SimpleDateFormat("y-M-d H:m:s");
String format = dateFormat.format(date);
Student monthReportModel = list.get(i);
content[i][0] = monthReportModel.getId();
content[i][1] = monthReportModel.getCourseId();
content[i][2] = monthReportModel.getTitle();
content[i][3] = monthReportModel.getSort().toString();
content[i][4] = format;
}
// 导出Excel
try {
HSSFWorkbook hssfWorkbook = getHSSFWorkbook(sheetName, title, content, null);
fileName = new String(fileName.getBytes(), "ISO8859-1");
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
OutputStream outputStream = response.getOutputStream();
hssfWorkbook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook workbook) {
// 创建一个HSSFWorkbook，对应一个Excel文件
if (workbook == null) {
workbook = new HSSFWorkbook();
}
// 在workbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = workbook.createSheet(sheetName);
// 在sheet中添加表头第0行
HSSFRow row = sheet.createRow(0);
// 创建单元格，并设置值表头 设置表头居中
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 声明列对象
HSSFCell cell = null;
// 创建标题
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(cellStyle);
}
// 创建内容
for (int i = 0; i < values.length; i++) {
row = sheet.createRow(i + 1);
for (int j = 0; j < values[i].length; j++) {
// 将内容按顺序赋给对应的列对象
row.createCell(j).setCellValue(values[i][j]);
}
}
return workbook;
}
}
在浏览器输入地址测试：
结果：
先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦
最后
以上就是激昂外套为你收集整理的Excel导入和导出的全部内容，希望文章能够帮你解决Excel导入和导出所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复