excel导入导出 - 详解
excel导入导出
导出
1.从数据库中读取到数据
2.通过指定的jar包 向excel文件写入数据
(excel非纯文本格式 需要按位写入文件和文件格式标签)
导入
1.通过jar包读取到excel文件中的格式(读取到java对象中)
2.向数据库写入
1使用POI 传统jar包 操作比较繁琐 (了解即可)
导出
@Test
public void f1(){
//导出测试
SqlSession sqlSession = MyBatisHealper.getSqlSession();
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
List students = mapper.queryAllByLimit(0, 50000);
//System.out.println(students);
MyBatisHealper.backSqlSession(sqlSession);
//通过POI 写excel文件
//Excel中，最大的最上层的对象，是文档本身
// Workbook(Excel本身) → Sheet 页签工作表 → 行 → 单元格
//最终的数据，是存储在单元格中
//找到Workbook，找到Sheet页签 ，找行，找单元格
String path = "d://save/stu_"+System.currentTimeMillis()+".xlsx";
try(XSSFWorkbook workbook = new XSSFWorkbook()){
// ↑ 初始化workbook对象，创造了 文档 对象
// 因为使用了try……resource的语法，所以，使用完成之后，资源会自动关闭
// 创建，文档下的页签
XSSFSheet sheet = workbook.createSheet("stu_1");
//创建 行 第0行
Row row0 = sheet.createRow(0);
// 单元格， 第0行用来表示表头，写每个字段代表的含义
row0.createCell(0).setCellValue("ID");
row0.createCell(1).setCellValue("姓名");
row0.createCell(2).setCellValue("年龄");
row0.createCell(3).setCellValue("地址");
//头结束，循环创建表格
for (int i = 0;i
导入
@Test
public void f2() throws Exception {
//导入的文件
String filePath = "d://save/stu_1762393964846.xlsx";
//根据文件路径，获取文档对象
Workbook workbook = WorkbookFactory.create(new FileInputStream(filePath));
// 获取 第 0 个 页签对象
Sheet sheet = workbook.getSheetAt(0);
//统计，当前的页签，一共有多少行
int rowsNum = sheet.getPhysicalNumberOfRows();
//SqlSession sqlSession = MyBatisHealper.getSqlSession();
SqlSession sqlSession =MyBatisHealper.getBatchSqlSession();
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
for (int i=1;i
2hutools工具
基于POI 简化原生POI的复杂操作
导出
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.javasm.dao.StudentDao;
import com.javasm.entity.Student;
import com.javasm.utils.MyBatisHealper;
import org.apache.ibatis.session.SqlSession;
import org.junit.jupiter.api.Test;
import java.util.List;
/**
* @className: HutoolsTest
* @author: gfs
* @date: 2025/11/6 10:28
* @version: 0.1
* @since: jdk17
* @description:
*/
public class HutoolsTest {
@Test
public void f1(){
//导出
SqlSession sqlSession = MyBatisHealper.getSqlSession();
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
List students = mapper.queryAllByLimit(0, 50000);
//System.out.println(students);
MyBatisHealper.backSqlSession(sqlSession);
String path = "d://save/stu_"+System.currentTimeMillis()+".xlsx";
//二次封装 加了默认规则 简化了代码
//简化导出 直接按数据库存的格式 导出数据
//写的对象，写到哪里
ExcelWriter writer = ExcelUtil.getWriter(path);
//填充数据，开始写
writer.write(students);
//关闭流
writer.close();
}
@Test
public void f2(){
//导出
SqlSession sqlSession = MyBatisHealper.getSqlSession();
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
List students = mapper.queryAllByLimit(0, 50000);
//System.out.println(students);
MyBatisHealper.backSqlSession(sqlSession);
String path = "d://save/stu_"+System.currentTimeMillis()+".xlsx";
//二次封装 加了默认规则 简化了代码
//简化导出 直接按数据库存的格式 导出数据
//写的对象，写到哪里
ExcelWriter writer = ExcelUtil.getWriter(path);
writer.addHeaderAlias("name","姓名");
writer.addHeaderAlias("age","年龄");
writer.addHeaderAlias("address","地址");
//填充数据，开始写
writer.write(students);
//关闭流
writer.close();
}
}
导入
//导入
@Test
void f3(){
//导入的文件
String filePath = "d://save/stu_1762396265151.xlsx";
//读取文件
ExcelReader reader = ExcelUtil.getReader(filePath);
//头信息是默认值，可以和Student类中的属性名 匹配上。
// 从 reader对象中，获取数据
List studentList = reader.readAll(Student.class);
//批量存储
SqlSession sqlSession = MyBatisHealper.getBatchSqlSession();
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
for(Student stu: studentList){
mapper.insert(stu);
}
MyBatisHealper.backAndCommitSqlSession(sqlSession);
}
//导入
@Test
void f4(){
//导入的文件
String filePath = "d://save/stu_1762396426348.xlsx";
//读取文件
ExcelReader reader = ExcelUtil.getReader(filePath);
//头信息是默认值，可以和Student类中的属性名 匹配上。
// 从 reader对象中，获取数据
//因为头信息没有匹配，需要手动指明，哪个属性和哪个头信息匹配
reader.addHeaderAlias("姓名","name");
reader.addHeaderAlias("年龄","age");
reader.addHeaderAlias("地址","address");
List studentList = reader.readAll(Student.class);
//批量存储
SqlSession sqlSession = MyBatisHealper.getBatchSqlSession();
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
for(Student stu: studentList){
mapper.insert2(stu);
}
MyBatisHealper.backAndCommitSqlSession(sqlSession);
}
3easyExcel工具
不依赖POI 擅长大量数据操作 (节省内存)
导出
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcel.*;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.javasm.dao.AdminUserDao;
import com.javasm.dao.StudentDao;
import com.javasm.entity.AdminUser;
import com.javasm.entity.PageInfo;
import com.javasm.entity.Student;
import com.javasm.utils.MyBatisHealper;
import org.apache.ibatis.session.SqlSession;
import org.junit.jupiter.api.Test;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.List;
/**
* @className: EasyExcelTest
* @author: gfs
* @date: 2025/11/6 10:45
* @version: 0.1
* @since: jdk17
* @description:
*/
public class EasyExcelTest {
@Test
public void f1(){
//导出
SqlSession sqlSession = MyBatisHealper.getSqlSession();
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
List students = mapper.queryAllByLimit(0, 50000);
//System.out.println(students);
MyBatisHealper.backSqlSession(sqlSession);
String path = "d://save/stu_"+System.currentTimeMillis()+".xlsx";
//easyExcel
//导出
EasyExcel.write(path, Student.class)//配置了输出的路径，和数据的泛型
.sheet(0,"学生数据1")//配置导出到第几个页签，以及页签的名字
.doWrite(students);//写
}
@Test
public void f2(){
String path = "d://save/stu_"+System.currentTimeMillis()+".xlsx";
SqlSession sqlSession = MyBatisHealper.getSqlSession();
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
try(ExcelWriter excelWriter = EasyExcel.write(path, Student.class).build()) {
//配置 每页n条数据，存入
int pageSize = 10000;
//查询 一共多少条数据
int count = 50000;
//计算 多少个页签
long total = PageInfo.getTotalPage(count,pageSize);
//分5个sheet写excel
for(int i = 0;i students = mapper.queryAllByLimit(((i+1)-1)*pageSize, pageSize);
excelWriter.write(students,sheet);
}
MyBatisHealper.backSqlSession(sqlSession);
}
}
@Test
public void f3(){
//导出
SqlSession sqlSession = MyBatisHealper.getSqlSession();
AdminUserDao mapper = sqlSession.getMapper(AdminUserDao.class);
List listUser = mapper.listUserByCondition(new AdminUser(),0,10);
//System.out.println(students);
MyBatisHealper.backSqlSession(sqlSession);
//给url参数赋值
listUser.forEach(user->{
try {
if(user.getHeadImg()!=null)
user.setHeadImgURL(new URL(user.getHeadImg()));
} catch (MalformedURLException e) {
throw new RuntimeException(e);
}
});
String path = "d://save/user_"+System.currentTimeMillis()+".xlsx";
//easyExcel
//导出
EasyExcel.write(path, AdminUser.class)//配置了输出的路径，和数据的泛型
.sheet(0,"学生数据1")//配置导出到第几个页签，以及页签的名字
.doWrite(listUser);//写
}
}
导入
@Test
public void f4(){
SqlSession sqlSession = MyBatisHealper.getBatchSqlSession();
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
//导入的文件
String filePath = "d://save/stu_1762397388350.xlsx";
EasyExcel.read(filePath, Student.class,new PageReadListener(list->{
//默认情况下，每100条 读取1次，list 就是100条数据
for(Student stu:list){
mapper.insert2(stu);
}
})).sheet().doRead();
MyBatisHealper.backAndCommitSqlSession(sqlSession);
}
@Test
public void f5(){
//导入的文件
String filePath = "d://save/stu_1762397388350.xlsx";
EasyExcel.read(filePath, Student.class, new ReadListener(){
//每次存储1000条数据
private static final int saveSize = 10000;
List cacheStudent = new ArrayList<>(saveSize);
@Override
public void invoke(Student student, AnalysisContext analysisContext) {
//每条干什么
cacheStudent.add(student);
if(cacheStudent.size()>=saveSize){
//保存
saveData();
//清空缓冲区
cacheStudent = new ArrayList<>(saveSize);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
//全部读完干什么
//保存
saveData();
}
private void saveData(){
SqlSession sqlSession = MyBatisHealper.getBatchSqlSession();
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
for(Student stu: cacheStudent){
mapper.insert(stu);
}
System.out.println("分一批..........");
MyBatisHealper.backAndCommitSqlSession(sqlSession);
}
}).sheet().doRead();
}
注意: f5会降低调入效率 但是主要解决服务器内存不足的问题 通过分批处理 防止一次向java对象中加载数据过大 导致虚拟机崩溃
4word导出(了解)
hutools导出
import cn.hutool.core.io.FileUtil;
import cn.hutool.poi.word.Word07Writer;
import com.javasm.dao.StudentDao;
import com.javasm.entity.ClassInfo;
import com.javasm.entity.Student;
import com.javasm.utils.MyBatisHealper;
import freemarker.template.Configuration;
import freemarker.template.Template;
import org.apache.ibatis.session.SqlSession;
import org.junit.jupiter.api.Test;
import java.awt.*;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @className: WordTest
* @author: gfs
* @date: 2025/11/6 11:38
* @version: 0.1
* @since: jdk17
* @description:
*/
public class WordTest {
@Test
public void f1(){
Word07Writer writer = new Word07Writer();
writer.addText(new Font("宋体",Font.BOLD,40),"我是标题1","你好世界");
writer.addText(new Font("黑体",Font.PLAIN,26),"窗前明月光","疑是地上霜");
//写出去
writer.flush(FileUtil.file("d://save/t1.docx"));
writer.close();
}
}
Freemarker导出
需要使用不同文件的模板文件 和Freemarker的特殊标签结合使用
操作比较繁琐 但是可以适配各种文档格式(参考视频案例操作)
import cn.hutool.core.io.FileUtil;
import cn.hutool.poi.word.Word07Writer;
import com.javasm.dao.StudentDao;
import com.javasm.entity.ClassInfo;
import com.javasm.entity.Student;
import com.javasm.utils.MyBatisHealper;
import freemarker.template.Configuration;
import freemarker.template.Template;
import org.apache.ibatis.session.SqlSession;
import org.junit.jupiter.api.Test;
import java.awt.*;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @className: WordTest
* @author: gfs
* @date: 2025/11/6 11:38
* @version: 0.1
* @since: jdk17
* @description:
*/
public class WordTest {
@Test
public void f2()throws Exception {
ClassInfo classInfo = new ClassInfo("老B一班","Java之父");
Map datamap = new HashMap<>();
datamap.put("classInfo",classInfo);
datamap.put("msg","我最强我最棒....");
SqlSession sqlSession = MyBatisHealper.getSqlSession();
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
List students = mapper.queryAllByLimit(0, 100);
//System.out.println(students);
MyBatisHealper.backSqlSession(sqlSession);
datamap.put("userList",students);
//导出的文件路径
String savePath = "d://save/freemarker_2.docx";
//获取 导出的流对象
OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(savePath),"utf-8");
//配置Freemarker 的版本
//import freemarker.template.Configuration;
Configuration configuration = new Configuration(Configuration.VERSION_2_3_21);
//配置编码格式
configuration.setDefaultEncoding("utf-8");
//加载模板来源
configuration.setClassForTemplateLoading(this.getClass(),"/docx");
//生成模板对象
Template template = configuration.getTemplate("template2.xml");
//向模板对象中写数据
template.process(datamap,writer);
writer.close();
}
@Test
public void f3()throws Exception {
ClassInfo classInfo = new ClassInfo("老B一班","Java之父");
Map datamap = new HashMap<>();
datamap.put("classInfo",classInfo);
datamap.put("msg","我最强我最棒....");
//导出的文件路径
String savePath = "d://save/freemarker_1.docx";
//获取 导出的流对象
OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(savePath),"utf-8");
//配置Freemarker 的版本
//import freemarker.template.Configuration;
Configuration configuration = new Configuration(Configuration.VERSION_2_3_21);
//配置编码格式
configuration.setDefaultEncoding("utf-8");
//加载模板来源
configuration.setClassForTemplateLoading(this.getClass(),"/docx");
//生成模板对象
Template template = configuration.getTemplate("template.xml");
//向模板对象中写数据
template.process(datamap,writer);
writer.close();
}
}
5页面导入导出
注意:
导入时:上传文件到服务端 再配合导入的jar包 写入数据
导出时:需要让浏览器下载文件 需要固定的响应头格式
package com.javasm.controller.stdentsDemo;
import com.alibaba.excel.EasyExcel;
import com.javasm.entity.Student;
import com.javasm.service.StudentService;
import com.javasm.service.impl.StudentServiceImpl;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
/**
* @className: StudentsExport
* @author: gfs
* @date: 2025/11/6 15:03
* @version: 0.1
* @since: jdk17
* @description:
*/
@WebServlet("/studentsExport")
public class StudentsExport extends HttpServlet {
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//查出要导出的数据
StudentService studentService = new StudentServiceImpl();
List students = studentService.listStudents();
//设置 导出的 头信息
resp.setContentType("application/vnd.openxmlformates-officedocument.spreadsheetml.sheet");
//文件名 防止乱码
String fileName = URLEncoder.encode("学生数据_"+System.currentTimeMillis()+".xlsx","UTF-8").replaceAll("\\+","%20");
//头信息，添加内容
resp.setHeader("Content-Disposition","attachment;filename*=utf-8''"+fileName);
//获得输出流 使用easyExcel输出excel文件
ServletOutputStream outputStream = resp.getOutputStream();
EasyExcel.write(outputStream, Student.class)
.sheet("学生信息")
.doWrite(students);
outputStream.close();
}
}
6图表插件
数据可视化处理 (优化显示 在页面中画图表)
echarts 百度的图表插件 (文档原生中文 通过json设置参数 画图表)
注意:echarts使用原生js 不依赖其他框架 (与vue没有集成 所以没有vue中响应式数据效果 如果要重新加载图表 需要手动重新加载)
echarts基本示例:
<script setup>
import { ref,onMounted } from 'vue'
import * as echarts from 'echarts';
onMounted(()=>{
// 基于准备好的dom，初始化echarts实例
let myChart = echarts.init(document.getElementById('main'));
// 配置项
// 不同的图表 配置项不同
let option = {
xAxis: {
type: 'category',
data: ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun']
},
yAxis: {
type: 'value'
},
series: [
{
data: [150, 230, 224, 218, 135, 147, 260],
type: 'line'
}
]
};
//画图表
myChart.setOption(option);
})
</script>
开发图表步骤
1先找图例示例
2根据配置项手册做修改
3修改完成 放入自己的页面中
<script setup>
import { ref,onMounted } from 'vue'
import * as echarts from 'echarts';
onMounted(()=>{
// 基于准备好的dom，初始化echarts实例
let myChart = echarts.init(document.getElementById('main'));
// 配置项
let option = {
title: {
text: '一周出勤图'
},
tooltip: {
trigger: 'axis'
},
legend: {
data: ['901教室', '902教室', '903教室']
},
grid: {
left: '3%',
right: '4%',
bottom: '3%',
containLabel: true
},
toolbox: {
feature: {
saveAsImage: {},
restore: {} ,
}
},
xAxis: {
type: 'category',
data: ['Mon', 'Tue', 'Wed', 'Thu', 'Fri']
},
yAxis: {
type: 'value'
},
series: [
{
name: '901教室',
type: 'bar',
data: [12, 13, 10, 13, 11]
},
{
name: '902教室',
type: 'bar',
data: [15, 14, 13, 12, 11]
},
{
name: '903教室',
type: 'bar',
data: [11, 12, 13, 14, 15]
}
]
};
//画图表
myChart.setOption(option);
})
</script>
注意 需要配合接口做成动态数据
<script setup>
import { ref,onMounted } from 'vue'
import * as echarts from 'echarts';
let myChart
/*
[
{ value: 1048, name: '王者荣耀' },
{ value: 735, name: '和平精英' },
{ value: 580, name: '英雄联盟' },
{ value: 484, name: '原神' },
{ value: 300, name: '蛋仔派对' }
]
*/
const pieData = {dataList:[]}
const drawEcharts = ()=>{
// 配置项
let option = {
title: {
text: '游戏下单量对比',
subtext: '当日',
left: 'center'
},
tooltip: {
trigger: 'item'
},
legend: {
orient: 'vertical',
left: 'left'
},
series: [
{
name: '下单量',
type: 'pie',
radius: '60%',
data:pieData.dataList ,
emphasis: {
itemStyle: {
shadowBlur: 10,
shadowOffsetX: 10,
shadowColor: 'rgba(0, 255, 0, 0.1)'
}
}
}
]
}
//画图表
myChart.setOption(option);
}
onMounted(()=>{
// 基于准备好的dom，初始化echarts实例
myChart = echarts.init(document.getElementById('main'));
//发送接口 取数据
//let resp = get("/game/pie")
let myDataFromBaseProj =[{gameName:"王者荣耀",gameCount:111},
{gameName:"和平精英",gameCount:222},
{gameName:"英雄联盟",gameCount:333},
{gameName:"蛋仔派对",gameCount:444}
]
//前端赋值时 可以根据实际要求重新组织数据
myDataFromBaseProj.forEach(item=>{
pieData.dataList.push({value:item.gameCount,name:item.gameName})
})
//给数据赋值
// pieData.dataList = [
// { value: 111, name: '王者荣耀' },
// { value: 222, name: '和平精英' },
// { value: 3333, name: '英雄联盟' },
// { value: 44, name: '原神' },
// { value: 55, name: '蛋仔派对' }
// ]
//重新绘图
drawEcharts()
})
</script>
需求:
根据日期 统计每个游戏的下单量
在页面通过图表展示
浙公网安备 33010602011771号