1、Excel模版:
选中单元格,将单元格的名称"C3"改为"name"(自己根据业务需求更改)
2、Java代码:
/**
* 模版式EXCEL值替换
* @return
*/
public static void updateExcel(String localUrl, String fileName, Map<String, String> params) {
fileName = fileName==null?String.valueOf(new Date().getTime() / 1000):fileName;
try {
File f = new File(localUrl);
InputStream inputStream = new FileInputStream(f);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0); //如果是.xlsx文件使用这个
List<XSSFName> allNames = xssfWorkbook.getAllNames();
for (XSSFName xssfName : allNames) {
String ref = xssfName.getRefersToFormula();
AreaReference area = new AreaReference(ref);
xssfSheet.getRow(area.getFirstCell().getRow()).getCell(area.getFirstCell().getCol()).setCellValue(params.get(xssfName.getNameName()));
}
ByteArrayOutputStream bos = new ByteArrayOutputStream();
/* 生成临时文件 */
FileOutputStream out = null;
String localPath = null;
try {
File pathurl = new File(ResourceUtils.getURL("classpath:").getPath());
String path = pathurl + "/static/" + fileName + ".xlsx";
localPath = path;
out = new FileOutputStream(localPath);
xssfWorkbook.write(out);
System.out.println(localPath);
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (out != null) {
xssfWorkbook.close();
out.flush();
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}catch(Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
Map<String, String> params = new HashMap<>();
params.put("name", "测试名称");
params.put("address", "测试地址");
params.put("phone", "18888888888");
updateExcel("E:\\audit.xlsx", null,params);
}
3、效果: