模版
导出效果
说明
生成pdf文件时会按照sheet页的顺序依次排序到pdf文件中
原理
后端使用技术poi，引入依赖
下面展示一些 内联代码片。
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
<exclusions>
<exclusion>
<artifactId>poi</artifactId>
<groupId>org.apache.poi</groupId>
</exclusion>
<exclusion>
<artifactId>poi-ooxml-schemas</artifactId>
<groupId>org.apache.poi</groupId>
</exclusion>
</exclusions>
</dependency>
<!--excel转pdf-->
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls.free</artifactId>
<version>3.9.1</version>
</dependency>转成pdf
// 浏览器下载
//设置响应头
response.setCharacterEncoding("UTF-8");
response.setContentType("application/octet-stream;charset=UTF-8");
// response.setHeader("Content-Type", "application/octet-stream");
try {
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode("审批表", "UTF-8"));
//定义一个字节输出流
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
workbook.write(outputStream);
//将字节数组放置到内存里面
ByteArrayInputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray());
//导入xls包引用Workbook区分poi提供的Workbook
com.spire.xls.Workbook wb = new com.spire.xls.Workbook();
wb.loadFromStream(inputStream);
//设置字段在一页中显示全
wb.getConverterSetting().setSheetFitToPage(true);
//定义字节输出流存放pdf输出流
ByteArrayOutputStream pdfOutputStream = new ByteArrayOutputStream();
wb.saveToStream(pdfOutputStream, FileFormat.PDF);
outputStream.close();
inputStream.close();
pdfOutputStream.close();
} catch (Exception e) {
e.printStackTrace();
}controller
@ApiOperation("导出PDF")
@GetMapping("/Model/Config/Word/{id}")
public void ftlToOnePDF(HttpServletResponse response,@PathVariable("id") String id) throws Exception{
tJlzfTujianMeterService.generatorAdmissionCard(id, response);
}```
# 业务方法
```java
//数据源
List<TJlzfTujianMeterExportVO> meterExportVO = tJlzfTujianMeterMapper.getInfo(id);
if (StringUtil.isNotEmpty(meterExportVO)) {
int n = meterExportVO.size();
// 模板名称
String templateName = "中期支付申请模版.xlsx";
String path = "";
// 获取操作系统名称，根据系统名称确定模板存放的路径
String systemName = System.getProperty("os.name");
if (systemName.toUpperCase().startsWith("WIN")) {
path = "D:/pdf/";
} else {
path = "服务器存放模版路径";
}
InputStream input = new FileInputStream(path + templateName);
InputStream is = null;
XSSFWorkbook workbook = null;
XSSFSheet sheet1 = null;
XSSFSheet sheet2 = null;
try {
workbook = new XSSFWorkbook(input);// 创建个workbook，
// 获取第一个sheet
sheet1 = workbook.getSheetAt(0);
sheet2 = workbook.getSheetAt(1);
} catch (Exception e1) {
e1.printStackTrace();
}
if (sheet1 != null) {
// 画图的顶级管理器，一个sheet只能获取一个，插入图片的话必须创建这个
Drawing drawing = sheet1.createDrawingPatriarch();
// 给模板写数据
//初始化行和列
XSSFRow row = sheet1.getRow(1);
if (row == null) {
row = sheet1.createRow(1);
}
XSSFCell cell = row.getCell(0);
if (cell == null) {
cell = row.createCell(0);
}
//单元格插入图片
String companyId = stringToSplit(meterExportVO.get(0).getConstructionId());
try {
//获取图片路径
String imageUrl = FileConvertBase64.conversionString(
baseProjectStampJlzfService.getInfoByOrgId(companyId).getStamp(), temporaryFilePath);
if (StringUtil.isNotEmpty(imageUrl)){
//转成流文件
InputStream inputStream = new FileInputStream(imageUrl);
byte[] bytes = IOUtils.toByteArray(inputStream);
//插入到哪个单元格，以及设计图片大小
CreatePicture1(workbook, bytes, 5, 0, 7, 1, drawing);
}
} catch (NullPointerException e) {
log.error("未查询到电子签章信息！");
e.printStackTrace();
}
//给单元格插入数据举例，例如给第一行第一列的单元格插入数据
row = sheet1.getRow(1);
cell = row.createCell(1);
cell.setCellValue(meterExportVO.get(0).getProjectName());
//按照需要给特定的单元格加数据
····
···
···
//动态数据依次添加行数据
for (int m = 0; m < meterExportVO.size(); m++) {
sheet1.shiftRows(5 + m, sheet1.getLastRowNum(), 1);
TJlzfTujianMeterExportVO vo = meterExportVO.get(m);
row = sheet1.createRow((int) m + 5);
// 序号
cell = row.createCell(0);
cell.setCellValue(m + 1);
XSSFExportUtils.setArroundBorder1(workbook, sheet1, m + 5, 0);
// 章次和名称
cell = row.createCell(1);
cell.setCellValue(vo.getSubtitleNa());
//单元格样式
XSSFExportUtils.setArroundBorder1(workbook, sheet1, m + 5, 1);
// 原有总金额
cell = row.createCell(2);
cell.setCellValue(vo.getCombinedPrice().toString());
XSSFExportUtils.setArroundBorder1(workbook, sheet1, m + 5, 2);
XSSFExportUtils.setArroundBorder1(workbook, sheet1, m + 5, 3);
XSSFExportUtils.setArroundBorder1(workbook, sheet1, m + 5, 4);
// 到本期末完成金额
cell = row.createCell(5);
cell.setCellValue(vo.getToThisPeriodComPrice().toString());
XSSFExportUtils.setArroundBorder1(workbook, sheet1, m + 5, 5);
// 到本期末完成占总金额
cell = row.createCell(6);
cell.setCellValue(vo.getToThisPeriodComPer());
XSSFExportUtils.setArroundBorder1(workbook, sheet1, m + 5, 6);
// 到上期末完成金额
cell = row.createCell(7);
cell.setCellValue(vo.getToLastPeriodComPrice().toString());
XSSFExportUtils.setArroundBorder1(workbook, sheet1, m + 5, 7);
//到上期末完成占总金额
cell = row.createCell(8);
cell.setCellValue(vo.getToLastPeriodComPer());
XSSFExportUtils.setArroundBorder1(workbook, sheet1, m + 5, 8);
// 本期完成金额
cell = row.createCell(9);
cell.setCellValue(vo.getThisPeriodComPrice().toString());
XSSFExportUtils.setArroundBorder1(workbook, sheet1, m + 5, 9);
// 本期完成占总金额
cell = row.createCell(10);
cell.setCellValue(vo.getThisPeriodComPer());
XSSFExportUtils.setArroundBorder1(workbook, sheet1, m + 5, 10);
XSSFExportUtils.setArroundBorder1(workbook, sheet1, m + 5, 11);
}
// 浏览器下载
//设置响应头
response.setCharacterEncoding("UTF-8");
response.setContentType("application/octet-stream;charset=UTF-8");
// response.setHeader("Content-Type", "application/octet-stream");
try {
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode("审批表", "UTF-8"));
//定义一个字节输出流
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
workbook.write(outputStream);
//将字节数组放置到内存里面
ByteArrayInputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray());
//导入xls包引用Workbook区分poi提供的Workbook
com.spire.xls.Workbook wb = new com.spire.xls.Workbook();
wb.loadFromStream(inputStream);
//设置字段在一页中显示全
wb.getConverterSetting().setSheetFitToPage(true);
//定义字节输出流存放pdf输出流
ByteArrayOutputStream pdfOutputStream = new ByteArrayOutputStream();
wb.saveToStream(pdfOutputStream, FileFormat.PDF);
//将pdf字节数组放置到内存里面
ByteArrayInputStream pdfInputStream = new ByteArrayInputStream(pdfOutputStream.toByteArray());
PdfReader reader = new PdfReader(pdfInputStream);
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
PdfStamper stamper = new PdfStamper(reader, byteArrayOutputStream);
FileOutputStream fos = new FileOutputStream(folderPath+"中期支付报表"+".pdf");
fos.write(pdfOutputStream.toByteArray());
fos.close();
outputStream.close();
inputStream.close();
pdfOutputStream.close();
pdfInputStream.close();
stamper.close();
reader.close();
} catch (Exception e) {
e.printStackTrace();
}
}公共方法
/**
* excel单元格填充盖章图片
*
* @param workbook
* @param bytes
* @param col1
* @param row1
* @param col2
* @param row2
* @param drawing
*/
public static void CreatePicture1(XSSFWorkbook workbook, byte[] bytes, int col1, int row1, int col2, int row2, Drawing drawing) {
// 这里根据实际需求选择图片类型
int pictureIdx = workbook.addPicture(bytes, workbook.PICTURE_TYPE_PNG);
ClientAnchor anchor = new XSSFClientAnchor
(0, 0, 102400, 102400, col1, row1, col2, row2);
// 插入图片
Picture pict = drawing.createPicture(anchor, pictureIdx);
// 调整图片占单元格百分比的大小，1.0就是100%
pict.resize(1, 2.9);
}
/**
* 单元格样式设计
* @param workBook
* @param sheet
* @param row
* @param column
*/
public static void setArroundBorder1(XSSFWorkbook workBook, XSSFSheet sheet,
int row, int column) {
XSSFCell currentCell = sheet.getRow(row).getCell(column);
if(currentCell==null){
currentCell=sheet.getRow(row).createCell(column);
}
XSSFCellStyle style = workBook.createCellStyle();
style.setBorderBottom(BorderStyle.THIN); //下边框
style.setBorderLeft(BorderStyle.THIN);//左边框
style.setBorderTop(BorderStyle.THIN);//上边框
style.setBorderRight(BorderStyle.THIN);//右边框
style.setAlignment(HorizontalAlignment.CENTER); // 居中
currentCell.setCellStyle(style);
}
/**
* @return TODO java.lang.String
* @Description TODO
* 金额转大写
*/
public static String convert (double money) {
String[] CN_UPPER_NUMBER = { "零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖" };
String[] CN_UPPER_MONETRAY_UNIT = { "分", "角", "元", "拾", "佰", "仟", "万", "拾", "佰", "仟", "亿", "拾", "佰", "仟" };
String CN_FULL = "整";
String CN_NEGATIVE = "负";
int MONEY_PRECISION = 2;
String CN_ZERO_FULL = "零元" + CN_FULL;
boolean negative = false;
if (money < 0) {
negative = true;
money = -money;
}
long moneyPart = (long) (money * Math.pow(10, MONEY_PRECISION));
if (moneyPart == 0) {
return CN_ZERO_FULL;
}
String strMoney = String.valueOf(moneyPart);
int len = strMoney.length();
if (len > 15) {
throw new IllegalArgumentException("金额太大，无法处理。");
}
int i = 0;
StringBuilder sb = new StringBuilder();
while (i < len) {
int n = strMoney.charAt(i) - '0';
if (n < 0 || n > 9) {
throw new IllegalArgumentException("金额格式错误。");
}
if (n != 0) {
sb.append(CN_UPPER_NUMBER[n]).append(CN_UPPER_MONETRAY_UNIT[len - 1 - i]);
} else {
if (len - 1 - i == 2 || len - 1 - i == 6 || len - 1 - i == 10) {
sb.append(CN_UPPER_MONETRAY_UNIT[len - 1 - i]);
}
}
i++;
}
if (sb.toString().endsWith(CN_UPPER_MONETRAY_UNIT[0])) {
sb.append(CN_FULL);
}
if (negative) {
sb.insert(0, CN_NEGATIVE);
}
// 获取字符串的长度
int lenght = sb.toString().length();
// 使用 charAt() 方法获取最后一个字符
String lastChar = sb.toString().charAt(lenght - 1) + "";
if("元".equals(lastChar)){
return sb.toString()+"整";
}else if("整".equals(lastChar)){
return sb.toString().replace("整","");
}
return sb.toString();
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
