word中加入excel附件
excel单元格中插入图片
word freemarker
Excel poi 制作, jxl 插入图片
压缩图片/宽高,动态控制单元格高度与宽度
1.word 需要模板
手动创建word excel .向word中插入excel附件并另存为 word xml 格式
替换符替换 文字 和附件base64内容
有两个binaryData标签 一个是base64,另一个是附件显示的样式
import com.ruoyi.common.utils.DownLoadFileUtil;
import com.ruoyi.common.utils.minio.MinioUtil;
import com.ruoyi.common.utils.poi.CompressImageUtil;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.system.domain.BBuildingDict;
import com.ruoyi.system.domain.ImgFile;
import com.ruoyi.system.domain.TShop;
import com.ruoyi.system.domain.vo.BManageDTO;
import com.ruoyi.system.domain.vo.BManageVo;
import com.ruoyi.system.domain.vo.TShopCheckInfoVo;
import com.ruoyi.system.mapper.BBuildingDictMapper;
import com.ruoyi.system.mapper.BManageMapper;
import com.ruoyi.system.mapper.TShopMapper;
import com.ruoyi.system.service.ImgExcelService;
import io.minio.PutObjectArgs;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.lang.Boolean;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.*;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.stream.Collectors;
/**
* word附件导出
*/
@ResponseBody
@RequestMapping(value = "/generateReports", method = RequestMethod.GET)
public void generateReports(HttpServletRequest request, HttpServletResponse response) throws Exception {
//查询数据
Map condition = getSelection(request);
//生成excel
Long num = System.currentTimeMillis();
String filename = num + ".xls";
sendExcel(request, response, filename);
//制作word
WordUtil wordUtil = new WordUtil();
Map<String, Object> dataMap = new HashMap<String, Object>();
/**wordTemplate*/
Department dept = (Department) request.getSession().getAttribute("CURRENT_DEPT");
// List<Map> mapList = sortingService.findShopInfoBySort2(condition);
// if (mapList.size() > 0) {
//向word中添加数据map
Date startTime = DateUtil.parseTime(start);
Date endTime = DateUtil.parseTime(end);
String startTime1 = new SimpleDateFormat("yyyy年MM月dd日").format(startTime);
String endTime1 = new SimpleDateFormat("yyyy年MM月dd日").format(endTime);
dataMap.put("startTime", startTime1);
dataMap.put("endTime", endTime1);
// }
//调试
// Thread.sleep(1000);
// dataMap.put("excelEnclosure", wordUtil.getFileStr(filePath + "副本" + filename));
//写入
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/msword");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("函告" + num + ".doc", "UTF-8"));
} catch (Exception ex) {
ex.printStackTrace();
}
wordUtil.createDoc(dataMap, response, "wordTemplate", filePath + "函告" + num + ".doc");
//删除临时文件
File file = new File(filePath + "副本" + filename);
if (file.exists()) {
file.delete();
}
}
public class WordUtil {
public Configuration configure=null;
public WordUtil(){
// configure=new Configuration(Configuration.VERSION_2_3_22);
configure=new Configuration();
configure.setDefaultEncoding("utf-8");
}
//创建word
public void createDoc( Map<String,Object> dataMap,HttpServletResponse response, String downloadType, String savePath){
try {
//加载需要装填的模板
Template template=null;
//设置模板装置方法和路径，FreeMarker支持多种模板装载方法。可以从servlet，classpath,数据库装载。
//加载模板文件，放在template下
configure.setClassForTemplateLoading(this.getClass(), "/com/~/util/template");
//设置对象包装器
//configure.setObjectWrapper(new DefaultObjectWrapper());
//设置异常处理器
configure.setTemplateExceptionHandler(TemplateExceptionHandler.IGNORE_HANDLER);
//定义Template对象，注意模板类型名字与downloadType要一致
template=configure.getTemplate(downloadType + ".xml");
File outFile=new File(savePath);
Writer out=null;
//指定编码表需使用转换流，转换流对象要接收一个字节输出流
out = response.getWriter();
template.process(dataMap, out);
out.close();
} catch (IOException e) {
e.printStackTrace();
} catch (TemplateException e) {
e.printStackTrace();
}
}
}
/**
* 制作excel
*/
public void sendExcel(HttpServletRequest request, HttpServletResponse response, String filename) {
//condition 筛选条件
Map condition = getSelection(request);
//获取数据
List<TShopCheckInfo> list = this.tShopCheckInfoService.selectShopInfoList(condition);
//列头
String[] title = { "是否", "图片",};
String sheetName = "sheet1";
String[][] content = new String[list.size()][2];
try {
int nu = 50;
if (list.size() < nu) {
nu = list.size();
}
//循环添加数据 数据库存的图片地址
for (int i = 0; i < list.size(); i++) {
content[i][0] = list.get(i).getEscape() ? (StringUtils.isNotEmpty(list.get(i).getEscapeText()) ? "是(" + list.get(i).getEscapeText() + ")" : "是") : "否";
content[i][1] = list.get(i).getEscapeImgs();
}
} catch (Exception e) {
e.printStackTrace();
}
HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, null);
try {
// TODO: 2020/7/29 地址需要更换
//输出Excel文件
// 这里是先将文件输出到服务器本地，再用WritableWorkbook进行图片处理
FileOutputStream output = new FileOutputStream(filePath + filename);
wb.write(output);
output.flush();
//测试
Thread.sleep(1000);
//插入图片到excel
List<String> nameList = new ArrayList<>();
excelImgUpdate(filePath, filename, list, nameList);
//响应到客户端
// FileInputStream inputStream = new FileInputStream(filePath + "副本" + filename);
// org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(inputStream);
//setHeader
// downLoadExcel("附件" + filename, response, workbook);
// FileOutputStream output = new FileOutputStream(filePath + filename);
//删除临时文件
// nameList.add(filePath + "副本" + filename);
nameList.add(filePath + filename);
delTemporary(nameList);
} catch (Exception e) {
e.printStackTrace();
}
}
package com.ztwx.ezxf.util;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
public class ExcelUtil {
/**
* 导出Excel
*/
public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb) {
// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
if (wb == null)
wb = new HSSFWorkbook();
// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(sheetName);
//设置单元格的宽度
sheet.setDefaultColumnWidth(18);
//设置第一列宽度
sheet.setColumnWidth(0, 252*40+323);
//设置第四列宽度
sheet.setColumnWidth(3, 252*25+323);
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
HSSFRow row = sheet.createRow(0);
// 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
//声明列对象
HSSFCell cell = null;
HSSFCell cell2 = null;
HSSFFont font = wb.createFont();
//创建标题
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
//字体
// font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 12);
style.setFont(font);
cell.setCellStyle(style);
}
//创建内容
for (int i = 0; i < values.length; i++) {
row = sheet.createRow(i + 1);
//清空字体
// font.setBoldweight((short)400);
// font.setBold(false);
// style.setFont(font);
for (int j = 0; j < values[i].length; j++) {
//将内容按顺序赋给对应的列对象
cell2 = row.createCell(j);
if(StringUtils.isNotBlank(values[i][j])){
cell2.setCellValue(values[i][j]);
}else{
cell2.setCellValue("/");
}
// 内容设置样式
cell2.setCellStyle(style);
}
}
return wb;
}
}
//向excel 加入图片
public List<String> excelImgUpdate(String filePath, String filename, List<TShopCheckInfo> list, List<String> nameList) throws Exception {
Workbook wb = Workbook.getWorkbook(new File(filePath + filename)); // 获得原始文档
//创建副本;
WritableWorkbook workbook = Workbook.createWorkbook(new File(filePath + "副本" + filename), wb);
WritableSheet sheet = workbook.getSheet(0);
//调用图片插入函数 图片插入的行列
//用map存放有图片时候对应的行信息 列固定
// Map<Integer, ArrayList> map = new HashMap();
for (int i = 0; i < list.size(); i++) {
//8 插入对应的列
String json9 = list.get(i).getEscapeImgs();
ArrayList arrayList9 = getImgSrc(json9);
if (arrayList9.size() > 0) {
nameList = imgExcelService.addPictureToExcel(nameList, sheet, arrayList9, i + 1, 8);
}
}
//写入Excel表格中;
workbook.write();
//关闭流;
workbook.close();
return nameList;
}
//图片压缩与写入 单元格控制
public List<String> addPictureToExcel(List<String> nameList,WritableSheet picSheet, ArrayList pictureFilePaths, double cellRow, double cellCol)
throws Exception {
final double cellSpace = 0.02;//图片之间的间隔 占比
double picWidthMax = 0;
double picHeightSum = 0;//空出图片 离上下边框的距离
ImgFile[] imgFiles = new ImgFile[pictureFilePaths.size()];
//存放临时文件名
for (int i = 0; i < pictureFilePaths.size(); i++) {
ImgFile imgFile = new ImgFile();
//下载图片到本地
Long da = System.currentTimeMillis();
// String filePath = AddressUtil.getMacOrWin();
String path = filePath + da + ".jpg";
nameList.add(path);
downloadPicture(pictureFilePaths.get(i).toString(), path);
//图片压缩
// Thumbnails.of(path).size(300,300).toFile(path);
CompressImageUtil.reduceImg(path,path,0,0,0.5f);
File imageFile = new File(path);
// 读入图片
BufferedImage picImage = ImageIO.read(imageFile);
ByteArrayOutputStream pngByteArray = new ByteArrayOutputStream();
//将其他图片格式写成png的形式
ImageIO.write(picImage, "PNG", pngByteArray);
imgFile.setPngByteArray(pngByteArray);
// 取得图片的像素高度，宽度
//这里设置图片在单元格的尺寸 原理不清楚 该值为具体实验值
double picWidth = picImage.getWidth() * 0.06;
// double picWidth = picImage.getWidth() ;
double picHeight = picImage.getHeight() * 7;
// double picHeight = picImage.getHeight() ;
imgFile.setHeigth(picHeight);
imgFile.setWidth(picWidth);
//汇总
if (picWidth > picWidthMax) {
picWidthMax = picWidth;
}
picHeightSum += picHeight;
imgFiles[i] = imgFile;
}
WritableFont font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.RED);
WritableCellFormat cellFormat = new WritableCellFormat(font);
//设置背景颜色;
cellFormat.setBackground(Colour.WHITE);
//设置边框;
cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
//设置自动换行;
cellFormat.setWrap(true);
//设置文字居中对齐方式;
cellFormat.setAlignment(Alignment.CENTRE);
//设置垂直居中;
cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
Label imageLabel = new Label((int) cellCol, (int) cellRow, "", cellFormat);
picSheet.addCell(imageLabel);
//获取图片需要插入的单元格
// WritableCell cell =picSheet.getWritableCell(0, 0);
//设置单元格宽高
picSheet.setColumnView((int) cellCol, (int) picWidthMax);//列宽
picSheet.setRowView((int) cellRow, (int) picHeightSum);//行高
double widthStart = cellSpace;//开始宽度
double heightStart = cellSpace;//开始高度
//插入图片
for (ImgFile imgFile0 : imgFiles) {
double heigthFact = imgFile0.getHeigth() / picHeightSum;//实际高度
double widthFact = imgFile0.getWidth() / picWidthMax;
//图片高度压缩了cellSpace+moreHeight,目的是为了该图片高度不超出单元格
if (heightStart + heigthFact >= 1) {
double moreHeight = heightStart + heigthFact - 1.00;
heigthFact -= moreHeight;
heigthFact -= cellSpace;
}
//图片宽度压缩了cellSpace,目的是为了该图片宽度不超出单元格
if (widthFact >= 1) {
widthFact -= cellSpace;
}
//生成图片对象
WritableImage image = new WritableImage(cellCol + widthStart, cellRow + heightStart,
widthFact, heigthFact, imgFile0.getPngByteArray().toByteArray());
//将图片对象插入到sheet
picSheet.addImage(image);
//开始高度累加，获取下一张图片的起始高度（相对该单元格）
heightStart += heigthFact;
heightStart += cellSpace;//图片直接间隔为cellSpace
}
return nameList;
}
private static void downloadPicture(String urlList, String path) {
URL url = null;
try {
url = new URL(urlList);
DataInputStream dataInputStream = new DataInputStream(url.openStream());
FileOutputStream fileOutputStream = new FileOutputStream(new File(path));
ByteArrayOutputStream output = new ByteArrayOutputStream();
byte[] buffer = new byte[1024];
int length;
while ((length = dataInputStream.read(buffer)) > 0) {
output.write(buffer, 0, length);
}
BASE64Encoder encoder = new BASE64Encoder();
String encode = encoder.encode(buffer);//返回Base64编码过的字节数组字符串
System.out.println(encode);
fileOutputStream.write(output.toByteArray());
dataInputStream.close();
fileOutputStream.close();
} catch (MalformedURLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
