文章目录
1.全局设置标题和内容字体格式
通过WriteCellStyle 的dataFormat属性和BuiltinFormats指定字体格式
这种单元格有内容时字体才会生效,无内容时还是"常规"格式
private static WriteHandler templateWriteHandler;
static {
//表头样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 11);
headWriteFont.setBold(true);
headWriteCellStyle.setWriteFont(headWriteFont);
//边框
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
headWriteCellStyle.setBorderRight(BorderStyle.THIN);
//前景色
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
//是否换行
headWriteCellStyle.setWrapped(true);
headWriteCellStyle.setLocked(true);
//表体样式
WriteCellStyle bodyWriteCellStyle = new WriteCellStyle();
//设置数据格式索引
bodyWriteCellStyle.setDataFormat((short)49);
templateWriteHandler = new HorizontalCellStyleStrategy(headWriteCellStyle,bodyWriteCellStyle);
}
//快速根据索引获取字符串、或根据字符串获取索引
public static void main(String[] args) {
int builtinFormat = BuiltinFormats.getBuiltinFormat("h:mm:ss AM/PM");
System.out.println(builtinFormat);
String builtinFormat1 = BuiltinFormats.getBuiltinFormat(49);
System.out.println(builtinFormat1);
}
记得注册
excelWriterBuilder.registerWriteHandler(templateWriteHandler);
2.个性化设置某一列格式
继承 AbstractVerticalCellStyleStrategy ,实现个性化方法 单独设置某一列
package com.example.easyexceldemo.bo;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.AbstractVerticalCellStyleStrategy;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
public class DemoVerticalCellStyleStrategy extends AbstractVerticalCellStyleStrategy {
@Override
protected WriteCellStyle headCellStyle(Head head) {
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 11);
headWriteFont.setBold(true);
headWriteCellStyle.setWriteFont(headWriteFont);
//边框
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
headWriteCellStyle.setBorderRight(BorderStyle.THIN);
//前景色
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
//是否换行
headWriteCellStyle.setWrapped(true);
headWriteCellStyle.setLocked(true);
return headWriteCellStyle;
}
@Override
protected WriteCellStyle contentCellStyle(Head head) {
Integer columnIndex = head.getColumnIndex();
//这里只单独设置第4列,看情况自己修改,DateFormat取值见 demo1 的 BuiltinFormats 类
if(3 == columnIndex){
WriteCellStyle bodyWriteCellStyle = new WriteCellStyle();
//设置数据格式索引
bodyWriteCellStyle.setDataFormat((short)49);
return bodyWriteCellStyle;
}else {
return new WriteCellStyle();
}
}
}
记得注册
excelWriterBuilder.registerWriteHandler(templateWriteHandler);
3.无内容时 (预制模板,流形式写会)
其实我遇到的场景,就只是简单的空白模板,网上找了好多为无内容excel设置文本格式的资料,都没有解决。后来干脆把模板上传到resource。
@GetMapping("/invoiceTemplateDownload2")
public void templateDownload2(HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("模板", "UTF-8").replaceAll("\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
byte[] fileToByteArray = FileUtils.readFileToByteArray(new File("src/main/resources/invoiceTemplate.xlsx"));
response.getOutputStream().write(fileToByteArray);
}