Background: SpringBoot
: Fastfds
There is a need to do such a function today. I am stumbling upon it. I will record it so that I can find it later.
1. The import package meven library cannot download the jar, so you need to specify the path in the project
coordinate
Jar package address: https://pan.baidu.com/s/1l5W4GRr-CCCaMi1D9BHZjw?pwd=6666
2.ExcelWriter excelWriter = EasyExcel.write().excelType(ExcelTypeEnum.XLS).withTemplate(“C:\Users\ZHOULIJIA\Desktop\mb.xls”).file(os).build(); excelWriter How to get InputStream problem
As written below, the focus is on the problem that ByteArrayOutputStream is empty. The reason is that WriteSheet is not closed.
Test class
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.biotechleague.commons.exception.DaoException;
import com.biotechleague.fdfs.FastDFSClient;
import com.biotechleague.fileupload.pojo.FileInfo;
import com.biotechleague.order.pojo.Order;
import com.biotechleague.utils.PdfUtil;
import lombok.Data;
import org.elasticsearch.search.sort.MinAndMax;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* @ClassName TEXT
* @description:
* @author: mr.zhou
* @create: 2023-10-25 10:54
* @Version 1.0
**/
public class TEXT {
public static void main(String[] args) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String fileName = "BillingPage" + System.currentTimeMillis() + ".xls";
String pdfFileName = "BillingPage" + System.currentTimeMillis() + ".pdf";
ByteArrayOutputStream os = new ByteArrayOutputStream();
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcel.write().excelType(ExcelTypeEnum.XLS).withTemplate("C:\Users\ZHOULIJIA\Desktop\mb.xls").file(os).build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();
Map<String, Object> map = new HashMap<>();
map.put("invoiceDate", "invoiceDate");
map.put("balanceDue", 666);
map.put("zf", "CC");
map.put("pnon", "33333232323232323");
map.put("netTerm", "30");
map.put("dueDate", sdf.format(new Date()));
map.put("orderDate", sdf.format(new Date()));
map.put("orderId","3233");
map.put("subtotal", 111);
excelWriter.fill(map, writeSheet);
excelWriter.fill( new FillWrapper(dataList2(5)), fillConfig, writeSheet);
excelWriter.finish();
byte[] buffer = os.toByteArray();
InputStream inputStream = new ByteArrayInputStream(buffer);
PdfUtil.excel2pdf(inputStream, pdfFileName,null);
}catch (Exception e){
throw new DaoException("Failed to generate invoice");
}finally {
excelWriter.finish();
}
}
private static List<DemoDate> dataList2(int i) {
List<DemoDate> dataList = new ArrayList<DemoDate>();
for (int j = 0; j < i; j + + ) {
DemoDate fillData = new DemoDate();
if (j == 0) {
fillData.setOrderId("orderId1");
fillData.setOrderDate("orderDate1");
} else {
fillData.setOrderId("");
fillData.setOrderDate("");
}
fillData.setShipTo("shipTo");
fillData.setBillTo("billTo3");
fillData.setCatalogName("catalogName3");
fillData.setDescription("description3");
fillData.setQty("qty3");
fillData.setRate(33);
fillData.setAmount(44);
dataList.add(fillData);
}
return dataList;
}
@Data
private static class DemoDate {
String shipTo;
String billTo;
String catalogName;
String description;
String qty;
Integer rate;
Integer amount;
String orderDate;
String orderId;
}
}
Tools
package com.biotechleague.utils;
import com.aspose.cells.License;
import com.aspose.cells.PdfSaveOptions;
import com.aspose.cells.Workbook;
import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import com.biotechleague.commons.pojo.BioTechLeagueResult;
import com.biotechleague.fdfs.FastDFSClient;
import org.apache.commons.io.IOUtils;
import org.springframework.beans.factory.annotation.Value;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.Date;
/**
* @ClassName PDFUtil
* @description:
* @author: mr.zhou
* @create: 2023-10-24 15:40
* @Version 1.0
**/
public class PdfUtil {
/**
* excel to pdf
*
* @param inputStream file stream
* @param fileName pdf file name
* @param convertSheets Sheet to be converted
*/
public static String[] excel2pdf(InputStream inputStream, String fileName, int[] convertSheets) {
String[] result = null;
try {
//Verify License
getLicense();
Workbook wb = new Workbook(inputStream);
String pdfFilePath = "C:\Users\ZHOULIJIA\Desktop\mb.pdf";
FileOutputStream fileOS = new FileOutputStream(pdfFilePath);
PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();
pdfSaveOptions.setOnePagePerSheet(true);
if (null != convertSheets) {
printSheetPage(wb, convertSheets);
}
wb.save(fileOS, pdfSaveOptions);
IOUtils.copy(inputStream, fileOS);
// result = FastDFSClient.uploadFile(inputStream, fileName);
fileOS.close();
inputStream.close();
System.out.println("convert success");
} catch (Exception e) {
System.out.println("convert failed");
e.printStackTrace();
}
return result;
}
/**
* Get the generated pdf file path, which defaults to the same directory as the source file
*
* @param excelFilePath excel file
* @return generated pdf file
*/
private static String getPdfFilePath(String excelFilePath) {
return excelFilePath.split(".")[0] + ".pdf";
}
/**
* Obtain license to remove watermark
* If verification is not performed, the converted PDF document will have a watermark.
*/
private static void getLicense() {
String licenseFilePath = "excel-license.xml";
try {
InputStream is = PdfUtil.class.getClassLoader().getResourceAsStream(licenseFilePath);
License license = new License();
license.setLicense(is);
} catch (Exception e) {
System.out.println("license verify failed");
e.printStackTrace();
}
}
/**
* Hide unnecessary sheet pages in the workbook.
*
* @param sheets Display the sheet array of the page
*/
private static void printSheetPage(Workbook wb, int[] sheets) {
for (int i = 1; i < wb.getWorksheets().getCount(); i + + ) {
wb.getWorksheets().get(i).setVisible(false);
}
if (null == sheets || sheets.length == 0) {
wb.getWorksheets().get(0).setVisible(true);
} else {
for (int i = 0; i < sheets.length; i + + ) {
wb.getWorksheets().get(i).setVisible(true);
}
}
}
}
Effect
The knowledge points of the article match the official knowledge files, and you can further learn related knowledge. Java Skill TreeHomepageOverview 138,179 people are learning the system