SpringBoot中的导入导出
java导出word文档
1 先准备好一个导出Word文档的模板。
例如：
2.打开doc文件后 ，文件中的另存为，然后选择 保存类型为2003 版本的(*.xml)
3、 刚生成的xml文件里面比较乱，要整理一下，方法如下：
使用Eclipse/idea ,新建一个jsp ,把xml里面的东西覆盖更新刚才的jsp ,ctrl+Shift+F/ ctrl+alt+L 把文件整理一下，在拷贝出来，放到刚才的xml。文件内容就排好了，然后找到刚才我们写的name，sex,adress等地方，修改成为 n a m e , {name} ,name,{sex},R{adress} (这些其实就是占位符，等下我们把我们的实际数据以一对应 的替换掉)，
注意：需要注意的是 在列表上方，需要定义集合遍历的名称，例如你后台定义的list 是
<#list listTotol as notice>
例如时间：${notice.Day}
关于自增序号的是：${notice_index+1}
</list>
4.xml完成后.将格式改成ftl格式 （个人基本情况.xml ==>修改成：basic.ftl )(名字最好不要用中文)
5.将 basic.ftl 放到 com.xxx.template文件下(具体放的位置看项目具体结构是怎么样的)
6、导入jar包：freemarker-2.3.18.jar （可自行下载，或者问我要都行）
/**
* @Description 查询得分表--导出通报
* @Author xuwz
* @Date 2021/11/16 14:14
*/
@RequestMapping(value = "/exportScoreWord", method = RequestMethod.POST)
public void exportScoreWord(@RequestBody CheckWorkQueryVO param, HttpServletResponse response) {
try {
SocreWordBean queryDetail = new SocreWordBean();
queryDetail.setNjOnlineRate("100%");
if (null == queryDetail) {
return;
}
Configuration configuration = new Configuration();
//获取保存ftl文件的文件夹
configuration.setClassForTemplateLoading(this.getClass(), "/fileExportFormworks");
configuration.setDefaultEncoding("utf-8");
//获取ftl文件
Template t = configuration.getTemplate("serviceInspection.ftl", "utf-8");
response.reset();
response.setHeader("Content-Disposition",
"attachment;filename=\"" + new String("亡人事故快报.doc".getBytes("GBK"), "iso8859-1") + "\"");
response.setContentType("application/x-download;charset=GBK");
response.setCharacterEncoding("utf-8");
Writer out = response.getWriter();
Map<String, Object> dataMap = new HashMap<>();
dataMap.put("fatality", queryDetail);
t.process(dataMap, out);
}catch (Exception e){
e.printStackTrace();
}
}
Hutool工具excel导入数据库
package cn.microvideo.eventaq.module.radarevent.controller;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;
@CrossOrigin
@RestController
@RequestMapping("/excel")
@Slf4j
public class excelImport {
@PostMapping("/importFile")
public void importFile(@RequestParam("file") MultipartFile file) throws IOException {
//fileName 文件名
String fileName = file.getOriginalFilename();
boolean xlsx = fileName.endsWith(".xlsx");
if (!xlsx) {
log.error("请上传以.xlsx结尾的文件");
}
//得到文件流
InputStream inputStream = file.getResource().getInputStream();
ExcelReader reader = ExcelUtil.getReader(inputStream);
//hutool读取excel 1：表示表格头所在行，2：从第几行开始读取，2147483647：行的最大值
//因为自定义了表格头别名，所以只能使用map接收，如果没有设置别名，可以使用实体接收
List<Map<String, Object>> readAll = reader.read(0,0,2147483647);
for (int i = 0; i < readAll.size(); i++) {
Map<String, Object> quMap = readAll.get(i);
//获取表格中的数据
String repos = quMap.get("编号").toString();
String quType = quMap.get("数据").toString();
}
}
}
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>4.1.19</version>
easypoi之easy导入数据库
@PostMapping("/upload")
public Boolean upload(@RequestParam("file") MultipartFile multipartFile) throws Exception {
ImportParams params = new ImportParams();
params.setHeadRows(1);
// params.setTitleRows(0);
List<TestBean> result = ExcelImportUtil.importExcel(multipartFile.getInputStream(),
TestBean.class, params);
System.out.println(JSONUtil.toJsonStr(result));
return true;
}
package cn.microvideo.eventaq.module.radarevent.controller;
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
@Data
public class TestBean {
@Excel(name = "编号")
private String id;
@Excel(name = "数据")
private String name;
}
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.2.0</version>
</dependency>
导出excel文件、POI设置单元格式
package com.microvideo.qwpt.qwglmanage.service.impl;
import com.microvideo.qwpt.qwglmanage.bean.*;
import com.microvideo.qwpt.qwglmanage.service.ExportService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;
@Service
public class ExportServiceImpl implements ExportService {
/**
* 警车在线率导出
*/
@Override
public void exportCarOnline(List<CarOnlineRateVO> resList, HttpServletResponse response, DutyCarOnlineStatQueryVO param) {
XSSFWorkbook book = new XSSFWorkbook();
XSSFSheet sheet = book.createSheet("车辆信息");
sheet.setColumnWidth(0, 10 * 256);
sheet.setColumnWidth(1, 20 * 256);
sheet.setColumnWidth(2, 40 * 256);
sheet.setColumnWidth(3, 40 * 256);
sheet.setColumnWidth(4, 40 * 256);
sheet.setColumnWidth(5, 30 * 256);
String time="("+param.getStartDateStr()+" 至 "+param.getEndDateStr()+")";
buildTitle(book, sheet,time);
buildContent(book, sheet, resList);
respDataStream(book, response);
}
/**
* @Description 构建标题行
* @Author xuwz
* @Date 2022/4/28 10:19
*/
private void buildTitle(XSSFWorkbook book, XSSFSheet sheet,String time) {
// 生成标题，并单元格合并
CellRangeAddress title0Region = new CellRangeAddress(0, 0, 0, 5);
sheet.addMergedRegion(title0Region);
CellRangeAddress title1LeftRegion = new CellRangeAddress(1, 1, 0, 5);
sheet.addMergedRegion(title1LeftRegion);
// 构建第一行标题
XSSFRow title0Row0 = sheet.createRow(0);
XSSFCell title0Row0Cell0 = title0Row0.createCell(0);
title0Row0Cell0.setCellValue("警车在线率统计表");
// 设置样式
CellStyle title0Row0Cell0Style = getInitCellStyle(book);
Font title0Row0Cell0Font = book.createFont();
title0Row0Cell0Font.setFontName("宋体");
// title0Row0Cell0Font.setBold(true);
title0Row0Cell0Font.setFontHeightInPoints((short) 26);
title0Row0Cell0Style.setFont(title0Row0Cell0Font);
title0Row0Cell0.setCellStyle(title0Row0Cell0Style);
title0Row0.createCell(1).setCellStyle(title0Row0Cell0Style);
title0Row0.createCell(2).setCellStyle(title0Row0Cell0Style);
title0Row0.createCell(3).setCellStyle(title0Row0Cell0Style);
title0Row0.createCell(4).setCellStyle(title0Row0Cell0Style);
title0Row0.createCell(5).setCellStyle(title0Row0Cell0Style);
XSSFRow title1Row0 = sheet.createRow(1);
// 构建第二行第一列标题
XSSFCell title1Row0Cell0 = title1Row0.createCell(0);
title1Row0Cell0.setCellValue(time);
// 设置样式
CellStyle title1RowCellStyle = getInitCellStyle(book);
Font title1RowCellFont = book.createFont();
title1RowCellFont.setFontName("宋体");
title1RowCellFont.setFontHeightInPoints((short) 18);
title1RowCellStyle.setFont(title1RowCellFont);
title1Row0Cell0.setCellStyle(title1RowCellStyle);
title1Row0.createCell(1).setCellStyle(title1RowCellStyle);
title1Row0.createCell(2).setCellStyle(title1RowCellStyle);
title1Row0.createCell(3).setCellStyle(title1RowCellStyle);
title1Row0.createCell(4).setCellStyle(title1RowCellStyle);
title1Row0.createCell(5).setCellStyle(title1RowCellStyle);
// 构建第三行标题
List<String> title2ValArr = Arrays.asList("序号", "所属机构", "应到警车在线数（辆）", "实到警车在线数（辆）", "在线率（百分比）","总分（满分40）");
XSSFRow title2Row0 = sheet.createRow(2);
// 设置样式
CellStyle title2RowCellStyle = getInitCellStyle(book);
Font title2RowCellFont = book.createFont();
title2RowCellFont.setFontName("宋体");
title2RowCellFont.setFontHeightInPoints((short) 18);
title2RowCellStyle.setFont(title2RowCellFont);
for (int cellIndex = 0; cellIndex < title2ValArr.size(); cellIndex++) {
String title2Val = title2ValArr.get(cellIndex);
XSSFCell cell = title2Row0.createCell(cellIndex);
cell.setCellValue(title2Val);
cell.setCellStyle(title2RowCellStyle);
}
}
// 构建内容行
//警车在线率
private void buildContent(XSSFWorkbook book, XSSFSheet sheet, List<CarOnlineRateVO> list) {
CellStyle cellStyle = getInitCellStyle(book);
Font title2RowCellFont = book.createFont();
title2RowCellFont.setFontName("宋体");
title2RowCellFont.setFontHeightInPoints((short) 14);
cellStyle.setFont(title2RowCellFont);
CellStyle cellStyleColor = getInitCellStyle(book);
Font title2RowCellFontColor = book.createFont();
title2RowCellFontColor.setFontName("宋体");
title2RowCellFontColor.setFontHeightInPoints((short) 14);
cellStyleColor.setFont(title2RowCellFontColor);
cellStyleColor.setFillForegroundColor(IndexedColors.AQUA.getIndex());
cellStyleColor.setFillPattern(FillPatternType.forInt(13));
int startDataIndex = 1;
int startRowIndex = 3;
for (CarOnlineRateVO bean : list) {
XSSFRow row = sheet.createRow(startRowIndex);
String brigadeName = bean.getBrigadeName();
// 序号
XSSFCell c0 = row.createCell(0);
c0.setCellValue(startDataIndex);
if (brigadeName.contains("支队")){
c0.setCellStyle(cellStyleColor);
}else {
c0.setCellStyle(cellStyle);
}
// 所属机构
XSSFCell c1 = row.createCell(1);
c1.setCellValue(bean.getBrigadeName());
if (brigadeName.contains("支队")){
c1.setCellStyle(cellStyleColor);
}else {
c1.setCellStyle(cellStyle);
}
// 应到警车在线数（辆）
XSSFCell c2 = row.createCell(2);
c2.setCellValue(bean.getShouldCarNum());
if (brigadeName.contains("支队")){
c2.setCellStyle(cellStyleColor);
}else {
c2.setCellStyle(cellStyle);
}
// 实到警车在线数（辆）
XSSFCell c3 = row.createCell(3);
c3.setCellValue(bean.getRealCarNum());
if (brigadeName.contains("支队")){
c3.setCellStyle(cellStyleColor);
}else {
c3.setCellStyle(cellStyle);
}
// 在线率（百分比）
XSSFCell c4 = row.createCell(4);
if(StringUtils.isEmpty(bean.getOnlineRate())){
c4.setCellValue("");
if (brigadeName.contains("支队")){
c4.setCellStyle(cellStyleColor);
}else {
c4.setCellStyle(cellStyle);
}
}else {
c4.setCellValue(bean.getOnlineRate());
if (brigadeName.contains("支队")){
c4.setCellStyle(cellStyleColor);
}else {
c4.setCellStyle(cellStyle);
}
c4.setCellStyle(cellStyle);
}
// 总分（满分40）
XSSFCell c5 = row.createCell(5);
c5.setCellValue(bean.getScore());
if (brigadeName.contains("支队")){
c5.setCellStyle(cellStyleColor);
}else {
c5.setCellStyle(cellStyle);
}
startDataIndex++;
startRowIndex++;
}
}
/**
* @Description 构建标题行
* @Author xuwz
* @Date 2022/4/28 10:19
*/
private void buildTitleCheck(XSSFWorkbook book, XSSFSheet sheet,String time,CheckWorkQueryVO param) {
// 生成标题，并单元格合并
CellRangeAddress title0Region = new CellRangeAddress(0, 0, 0, 5);
sheet.addMergedRegion(title0Region);
CellRangeAddress title1LeftRegion = new CellRangeAddress(1, 1, 0, 5);
sheet.addMergedRegion(title1LeftRegion);
// 构建第一行标题
XSSFRow title0Row0 = sheet.createRow(0);
XSSFCell title0Row0Cell0 = title0Row0.createCell(0);
if(param.getType()==1){
title0Row0Cell0.setCellValue("日间考勤统计表");
}
if(param.getType()==2){
title0Row0Cell0.setCellValue("夜间考勤统计表");
}
// 设置样式
CellStyle title0Row0Cell0Style = getInitCellStyle(book);
Font title0Row0Cell0Font = book.createFont();
title0Row0Cell0Font.setFontName("宋体");
// title0Row0Cell0Font.setBold(true);
title0Row0Cell0Font.setFontHeightInPoints((short) 26);
title0Row0Cell0Style.setFont(title0Row0Cell0Font);
title0Row0Cell0.setCellStyle(title0Row0Cell0Style);
title0Row0.createCell(1).setCellStyle(title0Row0Cell0Style);
title0Row0.createCell(2).setCellStyle(title0Row0Cell0Style);
title0Row0.createCell(3).setCellStyle(title0Row0Cell0Style);
title0Row0.createCell(4).setCellStyle(title0Row0Cell0Style);
title0Row0.createCell(5).setCellStyle(title0Row0Cell0Style);
XSSFRow title1Row0 = sheet.createRow(1);
// 构建第二行第一列标题
XSSFCell title1Row0Cell0 = title1Row0.createCell(0);
title1Row0Cell0.setCellValue(time);
// 设置样式
CellStyle title1RowCellStyle = getInitCellStyle(book);
Font title1RowCellFont = book.createFont();
title1RowCellFont.setFontName("宋体");
title1RowCellFont.setFontHeightInPoints((short) 18);
title1RowCellStyle.setFont(title1RowCellFont);
title1Row0Cell0.setCellStyle(title1RowCellStyle);
title1Row0.createCell(1).setCellStyle(title1RowCellStyle);
title1Row0.createCell(2).setCellStyle(title1RowCellStyle);
title1Row0.createCell(3).setCellStyle(title1RowCellStyle);
title1Row0.createCell(4).setCellStyle(title1RowCellStyle);
title1Row0.createCell(5).setCellStyle(title1RowCellStyle);
// 构建第三行标题
List<String> title2ValArr=new LinkedList<>();
if(param.getType()==1) {
title2ValArr = Arrays.asList("序号", "所属机构", "累计巡逻车次", "累计巡逻时长（分钟）", "累计巡逻里程（公里）", "总分（满分20）");
}
if(param.getType()==2) {
title2ValArr = Arrays.asList("序号", "所属机构", "累计巡逻车次", "累计巡逻时长（分钟）", "累计巡逻里程（公里）", "总分（满分30）");
}
XSSFRow title2Row0 = sheet.createRow(2);
// 设置样式
CellStyle title2RowCellStyle = getInitCellStyle(book);
Font title2RowCellFont = book.createFont();
title2RowCellFont.setFontName("宋体");
title2RowCellFont.setFontHeightInPoints((short) 18);
title2RowCellStyle.setFont(title2RowCellFont);
for (int cellIndex = 0; cellIndex < title2ValArr.size(); cellIndex++) {
String title2Val = title2ValArr.get(cellIndex);
XSSFCell cell = title2Row0.createCell(cellIndex);
cell.setCellValue(title2Val);
cell.setCellStyle(title2RowCellStyle);
}
}
/**
* @Description 获取单元格样式
* @Author xuwz
* @Date 2022/4/28 10:03
*/
private CellStyle getInitCellStyle(XSSFWorkbook book) {
CellStyle style = book.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
return style;
}
/**
* @Description 返回数据流
* @Author xuwz
* @Date 2022/4/28 10:58
*/
private void respDataStream(XSSFWorkbook book, HttpServletResponse response) {
OutputStream out = null;
try {
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("警车在线率统计表", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName) + ".xlsx");
out = response.getOutputStream();
book.write(out);
} catch (IOException e) {
} finally {
try {
if (null != out) {
out.close();
}
} catch (IOException e) {
}
}
}
}
2676
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
