本文介绍了如何使用Apache POI库将数据库数据填充到Excel模板中并下载，以及如何将特定数据写入指定单元格。涉及内容包括模板文件、数据实体、工具类的使用方法，以及图片的嵌入。案例详尽，适用于Java后端开发人员进行数据导出操作。
无障碍阅读文章方式
关注微信公众号： 张家的小伙子
回复：85942
案例 1
将数据库中查出的多条数据写入到指定的excel模板文件中，并将写入数据的文件在浏览器中下载。
准备
pom
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10-FINAL</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.57</version>
</dependency>
模板文件
在服务器中存在以下格式的excel模板文件：
需要将数据库中查询到的数据写入到上图红色区域（填充数据的区域）。
数据表对应的实体
public class TCount{
private Integer id;//编号
private Integer type ;//类型 0.道路 1.设施 2.其他
private String typeChild;//类型子类
private String name; // 名称
private String area;//所属区
private String street;//街道
private String location;//位置
private String remark;//描述
private String image;//图片
private String method;//排查方式
private String way;//措施
// todo 省略 getter 、setter、toString
}
工具类
其中参数中fileConfig 字段配置的格式如下：
注意：
1. 工具只会将配置在json 中的字段对应的数据写入到excel中。建议将实体类的字段全部配置。
2. 数据放入excel中的列号下标要和模板中的一致。
{
"id": { // 字段名做为键
"columnIndex":0, // 对应放入excel中的列号下标，列号下标从0开始
"isPicFields": false, //此字段的值是否是图片 ，false非图片; true图片。标记为图片后，此字段的值应为可访问的图片地址。若为线上地址，则会发起http请求将图片嵌入单元格中。若为本地地址，则会读取本地图片嵌入单元格中。
"fixedValue": "", //该字段的固定值，设置固定值后，会覆盖数据库中查出的数据值，填入excel表的单元格中
"valueFormat": {} //数据格式化，例如性别在库中为1或0，填入表中需要变成 男和女时，需要在此配置
},
// 数据格式化举例
"type": {
"columnIndex":1,
"isPicFields": false,
"fixedValue": "",
"valueFormat": {
"0": "道路", // 格式化的格式为： 数据库中的值作为键：格式化后的值
"1": "设施",
"2": "其他"
}
},
//固定值举例
"area":{
"columnIndex":4, //图片对应在模板中的列标（从0开始数）为4
"isPicFields": false,
"fixedValue": "广西壮族自治区",
"valueFormat": {}
},
// 图片举例
"image":{
"columnIndex":8, //图片对应在模板中的列标（从0开始数）为8
"isPicFields": true,
"fixedValue": "",
"valueFormat": {}
}
}
public class ExportIntoExcleTemplateUtil{
private static final String XLS = "xls", XLSX = "xlsx";
/**
* 指定某一个文件模板，把数据填入文件模板中，将填写后的文件下载
* @param templatePath 模板文件的路径 包含文件名和类型
* @param data 需要导出的数据
* @param dataStartRow 开始填入数据的行号
* @param fileConfig 字段配置
*
*/
public static void exportUseExcelTemplate(
String templatePath,
List<?> data,
Integer dataStartRow,
JSONObject fileConfig,
HttpServletResponse response
) throws IOException, InvalidFormatException {
// 获取Excel后缀名
String fileType = templatePath.substring(templatePath.lastIndexOf(".") + 1, templatePath.length()).toLowerCase();
if (Arrays.asList(XLS, XLSX).contains(fileType) == false) {
System.out.printin("文件后缀名不正确")
return ;
}
if (fileType.equals(XLS)) {
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(templatePath));
HSSFSheet sheet = workbook.getSheetAt(0); //获取第1个数据表
//构建每行数据
for (Object dataItem : data) {
if(Objects.isNull(dataItem)) continue;
JSONObject dataItemJsonObject = (JSONObject) JSONObject.toJSON(dataItem);
if(Objects.isNull(dataItemJsonObject)) continue;
//获取填入数据位置的开始行对象
HSSFRow row = sheet.getRow(dataStartRow);
//循环字段
Integer finalDataStartRow = dataStartRow;
fileConfig.forEach((k, v)->{
JSONObject valueJson = (JSONObject) v;
String fieldData = dataItemJsonObject.getString(k);//取出当前字段的数据
if (StringUtils.hasText(fieldData) == false) return;
Integer columnIndex = valueJson.getInteger("columnIndex");//获取该字段对应的列号
// 判断是否存在固定值
String fixedValue = valueJson.getString("fixedValue"); //获取该字段对应的固定值
if(StringUtils.hasText(fixedValue)){
// 存在固定值，将固定值写入单元格
row.getCell(columnIndex).setCellValue(fixedValue);
return;
}
Boolean isPicFields = valueJson.getBoolean("isPicFields");//获取该字段是否属于图片
if(isPicFields){
// 属于图片，则将图片放入单元格 finalDataStartRow：当前行号 columnIndex 当前列号
imageInsertIntoExcel( workbook, sheet, finalDataStartRow, Short.valueOf(columnIndex.toString()), fieldData );
return;
}
JSONObject valueFormat = valueJson.getJSONObject("valueFormat");//获取该字段是否存在值转换
if (valueFormat.isEmpty() == false){
//存在数值转换,根据数据库中的数据取出需要转换后的数据
String formatData = valueFormat.getString(fieldData); // 转换后的数据
// 将转换后的数据存入单元格
row.getCell(columnIndex).setCellValue(formatData);
return;
}
// 普通数据则直接存入单元格
row.getCell(columnIndex).setCellValue(fieldData);
});
// 一行数据结束之后，继续下一行，行号+1
dataStartRow++;
}
// 设置文件输出头
response.addHeader("Content-Disposition", "attachment;filename=" + System.currentTimeMillis() + ".xls");
OutputStream out = null;
try {
out = response.getOutputStream();
workbook.write(out);
out.close();
} catch (IOException e) {
// Auto-generated catch block
e.printStackTrace();
}finally {
out.close();
}
}else{
XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(templatePath));
XSSFSheet sheet = workbook.getSheetAt(0);//获取第1个数据表
//构建每行数据
for (Object dataItem : data) {
if (Objects.isNull(dataItem)) continue;
JSONObject dataItemJsonObject = (JSONObject) JSONObject.toJSON(dataItem);
if (Objects.isNull(dataItemJsonObject)) continue;
//获取填入数据位置的开始行对象
XSSFRow row = sheet.getRow(dataStartRow);
//循环字段
Integer finalDataStartRow = dataStartRow;
fileConfig.forEach((k, v)->{
JSONObject valueJson = (JSONObject) v;
String fieldData = dataItemJsonObject.getString(k);//取出当前字段的数据
if (StringUtils.hasText(fieldData) == false) return;
Integer columnIndex = valueJson.getInteger("columnIndex");//获取该字段对应的列号
// 判断是否存在固定值
String fixedValue = valueJson.getString("fixedValue"); //获取该字段对应的固定值
if(StringUtils.hasText(fixedValue)){
// 存在固定值，将固定值写入单元格
row.getCell(columnIndex).setCellValue(fixedValue);
return;
}
Boolean isPicFields = valueJson.getBoolean("isPicFields");//获取该字段是否属于图片
if(isPicFields){
// 属于图片，则将图片放入单元格 finalDataStartRow：当前行号 columnIndex 当前列号
imageInsertIntoExcel( workbook, sheet, finalDataStartRow, Short.valueOf(columnIndex.toString()), fieldData );
return;
}
JSONObject valueFormat = valueJson.getJSONObject("valueFormat");//获取该字段是否存在值转换
if (valueFormat.isEmpty() == false){
//存在数值转换,根据数据库中的数据取出需要转换后的数据
String formatData = valueFormat.getString(fieldData); // 转换后的数据
// 将转换后的数据存入单元格
row.getCell(columnIndex).setCellValue(formatData);
return;
}
// 普通数据则直接存入单元格
row.getCell(columnIndex).setCellValue(fieldData);
});
// 一行数据结束之后，继续下一行，行号+1
dataStartRow++;
}
// 设置文件输出头
response.addHeader("Content-Disposition", "attachment;filename=" + System.currentTimeMillis() + ".xls");
OutputStream out = null;
try {
out = response.getOutputStream();
workbook.write(out);
out.close();
} catch (IOException e) {
// Auto-generated catch block
e.printStackTrace();
}finally {
out.close();
}
}
}
/**
* 图片嵌入excel xlsx格式
*/
private static void imageInsertIntoExcel(XSSFWorkbook workbook,XSSFSheet sheet,
Integer rowIndex, Short columnIndex,
String fieldData ){
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
//判断是否为 http开始或者https开始的图片路径，如果是，发起网络请求加载图片，否在视为本地图片
//XSSFPatriarch patriarch = sheet.createDrawingPatriarch();
XSSFDrawing patriarch = sheet.createDrawingPatriarch();
/*HSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2)
dx1 dy1 起始单元格中的x,y坐标.
dx2 dy2 结束单元格中的x,y坐标
col1,row1 指定起始的单元格，下标从0开始
col2,row2 指定结束的单元格 ，下标从0开始*/
int dx1 = 20 ,dy1 = 20 ,dx2 = 1003,dy2 = 235;
short col1 = columnIndex;
int row1 = rowIndex;
HSSFClientAnchor anchor = new HSSFClientAnchor(20, 20, 1003, 235, col1,row1 , col1,row1 );
anchor.setAnchorType(3);
try {
if (fieldData.indexOf("https") == -1 && fieldData.indexOf("http") == -1) { //path中不存在http或https，视为本地地址
BufferedImage bufferImg = ImageIO.read(new File(fieldData));
ImageIO.write(bufferImg, "jpg", byteArrayOutputStream);
//插入图片
patriarch.createPicture(anchor, workbook.addPicture(byteArrayOutputStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
} else {
System.out.println("发起网络请求加载图片")
//path中存在http或https，发起网络请求
URL urlObj = new URL(fieldData);
HttpURLConnection conn = (HttpURLConnection) urlObj.openConnection();
conn.setRequestMethod("GET");
InputStream inStream = conn.getInputStream();
byte[] byteData = readInputStream(inStream);
//插入图片
patriarch.createPicture(anchor, workbook.addPicture(byteData, HSSFWorkbook.PICTURE_TYPE_JPEG));
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 图片嵌入excel xls格式
*/
private static void imageInsertIntoExcel(HSSFWorkbook workbook,HSSFSheet sheet,
Integer rowIndex, Short columnIndex,
String fieldData ){
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
//判断是否为 http开始或者https开始的图片路径，如果是，发起网络请求加载图片，否在视为本地图片
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
/*HSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2)
dx1 dy1 起始单元格中的x,y坐标.
dx2 dy2 结束单元格中的x,y坐标
col1,row1 指定起始的单元格，下标从0开始
col2,row2 指定结束的单元格 ，下标从0开始*/
int dx1 = 20 ,dy1 = 20 ,dx2 = 1003,dy2 = 235;
short col1 = columnIndex;
int row1 = rowIndex;
HSSFClientAnchor anchor = new HSSFClientAnchor(20, 20, 1003, 235, col1,row1 , col1,row1 );
anchor.setAnchorType(3);
try {
if (fieldData.indexOf("https") == -1 && fieldData.indexOf("http") == -1) { //path中不存在http或https，视为本地地址
BufferedImage bufferImg = ImageIO.read(new File(fieldData));
ImageIO.write(bufferImg, "jpg", byteArrayOutputStream);
//插入图片
patriarch.createPicture(anchor, workbook.addPicture(byteArrayOutputStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
} else {
System.out.println("发起网络请求加载图片")
//path中存在http或https，发起网络请求
URL urlObj = new URL(fieldData);
HttpURLConnection conn = (HttpURLConnection) urlObj.openConnection();
conn.setRequestMethod("GET");
InputStream inStream = conn.getInputStream();
byte[] byteData = readInputStream(inStream);
//插入图片
patriarch.createPicture(anchor, workbook.addPicture(byteData, HSSFWorkbook.PICTURE_TYPE_JPEG));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
使用
创建springboot 项目，使用spring mvc。
创建一个接口。代码如下：
@RequestMapping(value = "export")
@RestController
@CrossOrigin
public class ExportController {
@RequestMapping(value = "testExport")
public void testExport(HttpServletResponse response, HttpServletRequest request) throws IOException, InvalidFormatException {
String templatePath = "I:\\zhang\\yhtemplate.xls"; //模板文件的地址
List<TCount> data = new ArrayList<>(); // 数据库查出来数据
Integer dataStartRow = 8; //开始填充数据的区域行号下标。从0开始数
JSONObject fileConfig = JSONObject.parseObject("这里填入json配置");
//调用工具类
ExportIntoExcleTemplateUtil.exportUseExcelTemplate(templatePath,data,dataStartRow,fileConfig,response);
}
}
案例2
将指定数据写入某个excel的单元格中
准备
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
工具类代码
/**
* 将内容写入excel指定的sheet表中指定的单元格中
*
* @param data 写入的数据
* @param rowIndex 行下标
* @param cellIndex 列下标
* @param sheetIndex sheet表下标
* @param file 文件路径 ：E:\\zhang\\项目需求\\泥头车\\template.xls
*/
public static boolean writeIntoExcelCell(String data, Integer sheetIndex,
Integer rowIndex, Integer cellIndex, String file) {
boolean flag = false;
// 获取Excel后缀名
String fileType = file.substring(file.lastIndexOf(".") + 1, file.length());
if (Arrays.asList(XLS, XLSX).contains(fileType) == false) {
log.warn("文件后缀名不正确");
return flag;
}
if (fileType.equals(XLS)) {
try {
// 创建Excel的工作书册 Workbook,对应到一个excel文档
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
HSSFSheet sheet = wb.getSheetAt(sheetIndex);
HSSFRow row = sheet.getRow( rowIndex);//行
HSSFCell cell = row.getCell(cellIndex);//获取指定列
// todo
cell.setCellValue(data);
FileOutputStream os;
os = new FileOutputStream(file);
wb.write(os);
os.close();
flag = true;
} catch (Exception e) {
e.printStackTrace();
}
} else {
try {
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
XSSFSheet sheet = wb.getSheetAt(sheetIndex);
XSSFRow row1 = sheet.getRow(rowIndex);
Cell cell = row1.getCell(cellIndex);
cell.setCellValue(data);
FileOutputStream os;
os = new FileOutputStream(file);
wb.write(os);
os.close();
flag = true;
} catch (IOException e) {
e.printStackTrace();
}
}
return flag;
}
/***
** 扩展 ----
* 将内容写入excel指定的sheet表中指定的单元格中
*
* @param data 写入的数据
* @param rowIndex 行下标
* @param cellIndex 列下标
* @param sheetIndex sheet表下标
* @param getDataCellIndex 获取数据的单元格号
* @param file 文件路径 ：E:\\zhang\\项目需求\\泥头车\\template.xls
*/
public static boolean writeIntoCell(String data, Integer getDataCellIndex, Integer sheetIndex, Integer rowIndex, Integer cellIndex, String file) {
boolean flag = false;
// 获取Excel后缀名
String fileType = file.substring(file.lastIndexOf(".") + 1, file.length());
if (Arrays.asList(XLS, XLSX).contains(fileType) == false) {
log.warn("文件后缀名不正确");
return flag;
}
if (fileType.equals(XLS)) {
try {
// 创建Excel的工作书册 Workbook,对应到一个excel文档
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
HSSFSheet sheet = wb.getSheetAt(sheetIndex);
int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();//数据结束的行
HSSFRow row1 = sheet.getRow(sheet.getFirstRowNum());//表头行
int physicalNumberOfCells = row1.getPhysicalNumberOfCells();//数据结束列
//从第二行开始
for (int h = 1; h < physicalNumberOfRows; h++) {
HSSFRow row = sheet.getRow(h); //获取出每一行
HSSFCell cell = row.getCell(cellIndex);//获取指定列
if (cell == null) cell = row.createCell(cellIndex);
HSSFCell getDataCell = row.getCell(getDataCellIndex); //指定行列对应的单元格
String basiData = convertCellValueToString(getDataCell); //得到该单元格数据
// todo 用 basiData 查找百度坐标、
StringJoiner sj = new StringJoiner(",");
if(StringUtil.isNotEmpty(basiData)){
Map<String, BigDecimal> coordinate = EntCoordSyncJob.getCoordinate(basiData);
if(coordinate==null || coordinate.isEmpty()) continue;
BigDecimal lat = coordinate.get("lat");
BigDecimal lng = coordinate.get("lng");
sj.add(lat.toString());
sj.add(lng.toString());
}
String pos = sj.toString();
cell.setCellValue(pos);
FileOutputStream os;
os = new FileOutputStream(file);
wb.write(os);
os.close();
flag = true;
}
} catch (Exception e) {
e.printStackTrace();
}
} else {
try {
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
XSSFSheet sheet = wb.getSheetAt(sheetIndex);
int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();//数据结束的行
XSSFRow row1 = sheet.getRow(sheet.getFirstRowNum());//表头行
int physicalNumberOfCells = row1.getPhysicalNumberOfCells();//数据结束列
//从第二行开始
for (int h = 1; h < physicalNumberOfRows; h++) {
XSSFRow row = sheet.getRow(h); //获取每一行
XSSFCell cell = row.getCell(cellIndex);//获取指定列
if (cell == null) cell = row.createCell(cellIndex);
XSSFCell getDataCell = row.getCell(getDataCellIndex);//指定行列对应的单元格
String basiData = convertCellValueToString(getDataCell);//得到该单元格数据
cell.setCellValue(pos);
FileOutputStream os;
os = new FileOutputStream(file);
wb.write(os);
os.close();
flag = true;
}
} catch (IOException e) {
e.printStackTrace();
}
}
return flag;
}
扫一扫
1065
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
