本地文件写数据
package com.qiangesoft.easyexcel.write;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.*;
import com.alibaba.excel.util.FileUtils;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.qiangesoft.easyexcel.util.ResourceFileUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
@Slf4j
public class ExcelWrite {
public static void write(String fileName) throws IOException {
EasyExcel.write(fileName, WriteData.class)
.sheet("模板")
.doWrite(() -> {
return data();
});
}
public static void write1(String fileName) throws IOException {
EasyExcel.write(fileName, WriteData.class)
.sheet("模板")
.doWrite(data());
}
public static void write2(String fileName) throws IOException {
try (ExcelWriter excelWriter = EasyExcel.write(fileName, WriteData.class).build()) {
WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
excelWriter.write(data(), writeSheet);
}
}
public static void writeRepeat(String fileName) throws IOException {
try (ExcelWriter excelWriter = EasyExcel.write(fileName, WriteData.class).build()) {
WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
for (int i = 0; i < 5; i++) {
List<WriteData> data = data();
excelWriter.write(data, writeSheet);
}
}
}
public static void writeSameSheet(String fileName) throws IOException {
try (ExcelWriter excelWriter = EasyExcel.write(fileName, WriteData.class).build()) {
for (int i = 0; i < 5; i++) {
WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).build();
List<WriteData> data = data();
excelWriter.write(data, writeSheet);
}
}
}
public static void writeManySheet(String fileName) throws IOException {
try (ExcelWriter excelWriter = EasyExcel.write(fileName).build()) {
for (int i = 0; i < 5; i++) {
WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).head(WriteData.class).build();
List<WriteData> data = data();
excelWriter.write(data, writeSheet);
}
}
}
public static void writeWithImage(String fileName) throws IOException {
String imagePath = ResourceFileUtil.getClassPathByClassLoader() + "demo/img.jpg";
try (InputStream inputStream = ResourceFileUtil.getResourceByClassPathResource("demo/img.jpg")) {
List<ImageWriteData> list = ListUtils.newArrayList();
ImageWriteData imageWriteData = new ImageWriteData();
File file = new File(imagePath);
imageWriteData.setFile(file);
imageWriteData.setUrl(new URL("https://www.oicfx.cn/img/logo.49129107.png"));
imageWriteData.setByteArray(FileUtils.readFileToByteArray(file));
imageWriteData.setString(imagePath);
imageWriteData.setInputStream(inputStream);
WriteCellData<Void> writeCellData = new WriteCellData<>();
writeCellData.setType(CellDataTypeEnum.STRING);
writeCellData.setStringValue("额外的放一些文字");
List<ImageData> imageDataList = new ArrayList<>();
ImageData imageData = new ImageData();
imageData.setImage(FileUtils.readFileToByteArray(file));
imageData.setImageType(ImageData.ImageType.PICTURE_TYPE_JPEG);
imageDataList.add(imageData);
writeCellData.setImageDataList(imageDataList);
imageWriteData.setWriteCellDataFile(writeCellData);
list.add(imageWriteData);
EasyExcel.write(fileName, ImageWriteData.class).sheet().doWrite(list);
}
}
public static void writeWithTemplate(String fileName, String templateName) throws IOException {
String path = ResourceFileUtil.getClassPathByClassLoader() + templateName;
EasyExcel.write(fileName, WriteData.class).withTemplate(path).sheet().doWrite(data());
}
public static void writeCellData(String fileName) {
ExcelWriteCellData excelWriteCellData = new ExcelWriteCellData();
WriteCellData<String> hyperlink = new WriteCellData<>("官方网站");
excelWriteCellData.setHyperlink(hyperlink);
HyperlinkData hyperlinkData = new HyperlinkData();
hyperlink.setHyperlinkData(hyperlinkData);
hyperlinkData.setAddress("https://github.com/alibaba/easyexcel");
hyperlinkData.setHyperlinkType(HyperlinkData.HyperlinkType.URL);
WriteCellData<String> comment = new WriteCellData<>("备注的单元格信息");
excelWriteCellData.setCommentData(comment);
CommentData commentData = new CommentData();
comment.setCommentData(commentData);
commentData.setAuthor("Jiaju Zhuang");
commentData.setRichTextStringData(new RichTextStringData("这是一个备注"));
commentData.setRelativeLastColumnIndex(1);
commentData.setRelativeLastRowIndex(1);
WriteCellData<String> formula = new WriteCellData<>();
excelWriteCellData.setFormulaData(formula);
FormulaData formulaData = new FormulaData();
formula.setFormulaData(formulaData);
formulaData.setFormulaValue("REPLACE(123456789,1,1,2)");
WriteCellData<String> writeCellStyle = new WriteCellData<>("单元格样式");
writeCellStyle.setType(CellDataTypeEnum.STRING);
excelWriteCellData.setWriteCellStyle(writeCellStyle);
WriteCellStyle writeCellStyleData = new WriteCellStyle();
writeCellStyle.setWriteCellStyle(writeCellStyleData);
writeCellStyleData.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
writeCellStyleData.setFillForegroundColor(IndexedColors.GREEN.getIndex());
WriteCellData<String> richTest = new WriteCellData<>();
richTest.setType(CellDataTypeEnum.RICH_TEXT_STRING);
excelWriteCellData.setRichText(richTest);
RichTextStringData richTextStringData = new RichTextStringData();
richTest.setRichTextStringDataValue(richTextStringData);
richTextStringData.setTextString("红色绿色默认");
WriteFont writeFont = new WriteFont();
writeFont.setColor(IndexedColors.RED.getIndex());
richTextStringData.applyFont(0, 2, writeFont);
writeFont = new WriteFont();
writeFont.setColor(IndexedColors.GREEN.getIndex());
richTextStringData.applyFont(2, 4, writeFont);
List<ExcelWriteCellData> data = new ArrayList<>();
data.add(excelWriteCellData);
EasyExcel.write(fileName, ExcelWriteCellData.class).inMemory(true).sheet("模板").doWrite(data);
}
public static void writeTable(String fileName) {
try (ExcelWriter excelWriter = EasyExcel.write(fileName, WriteData.class).build()) {
WriteSheet writeSheet = EasyExcel.writerSheet("模板").needHead(Boolean.FALSE).build();
WriteTable writeTable0 = EasyExcel.writerTable(0).needHead(Boolean.TRUE).build();
WriteTable writeTable1 = EasyExcel.writerTable(1).needHead(Boolean.TRUE).build();
excelWriter.write(data(), writeSheet, writeTable0);
excelWriter.write(data(), writeSheet, writeTable1);
}
}
public static void writeComment(String fileName) {
EasyExcel.write(fileName, WriteData.class)
.inMemory(Boolean.TRUE)
.registerWriteHandler(new CommentWriteHandler())
.sheet("模板")
.doWrite(data());
}
private static List<WriteData> data() {
List<WriteData> list = ListUtils.newArrayList();
for (int i = 0; i < 10; i++) {
WriteData data = new WriteData();
data.setString("字符串" + i);
data.setDate(new Date());
data.setDoubleData(0.56);
list.add(data);
}
return list;
}
}
导出文件
@GetMapping("/write")
public void write() throws IOException {
ExcelWrite.write("D:/excel/write.xlsx");
ExcelWrite.write1("D:/excel/write1.xlsx");
ExcelWrite.write2("D:/excel/write2.xlsx");
ExcelWrite.writeRepeat("D:/excel/writeRepeat.xlsx");
ExcelWrite.writeSameSheet("D:/excel/writeSameSheet.xlsx");
ExcelWrite.writeManySheet("D:/excel/writeManySheet.xlsx");
ExcelWrite.writeWithImage("D:/excel/writeWithImage.xlsx");
ExcelWrite.writeCellData("D:/excel/writeCellData.xlsx");
ExcelWrite.writeWithTemplate("D:/excel/writeWithTemplate.xlsx", "demo/demo.xlsx");
ExcelWrite.writeTable("D:/excel/writeTable.xlsx");
ExcelWrite.writeComment("D:/excel/writeComment.xlsx");
}
@GetMapping("/download")
public void download(HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("测试", StandardCharsets.UTF_8).replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), WriteData.class).sheet("模板").doWrite(data());
}
private List<WriteData> data() {
List<WriteData> list = ListUtils.newArrayList();
for (int i = 0; i < 10; i++) {
WriteData data = new WriteData();
data.setString("字符串" + 0);
data.setDate(new Date());
data.setDoubleData(0.56);
list.add(data);
}
return list;
}