Skip to content

Commit cd69b02

Browse files
TommyLemonTommyLemon
committed
Java：MultiDataSource 查数据来生成 CVAuto 导出的 Excel 表格
1 parent 82192ca commit cd69b02

File tree

3 files changed

+119
-43
lines changed

3 files changed

+119
-43
lines changed

‎APIJSON-Java-Server/APIJSONBoot-MultiDataSource/src/main/java/apijson/ExcelUtil.java‎

Lines changed: 40 additions & 40 deletions
Original file line numberDiff line numberDiff line change
@@ -49,7 +49,7 @@ public class ExcelUtil {
4949

5050
public static void main(String[] args) throws IOException {
5151
String fileName = "CVAuto_Report_Enhanced_" + System.currentTimeMillis() + ".xlsx";
52-
generateCVAutoReport("", fileName);
52+
generateCVAutoReport(getMockDetailData(), "", fileName);
5353
System.out.println("报告生成成功！文件路径: " + fileName);
5454
}
5555

@@ -65,25 +65,25 @@ public static void main(String[] args) throws IOException {
6565
private static final int JSON_COLUMN_INDEX = 11;
6666

6767

68-
public static String newCVAutoReportWithTemplate() throws IOException {
69-
return newCVAutoReportWithTemplate(null);
68+
public static String newCVAutoReportWithTemplate(List<DetailItem> list) throws IOException {
69+
return newCVAutoReportWithTemplate(list, null);
7070
}
71-
public static String newCVAutoReportWithTemplate(String dir) throws IOException {
72-
return newCVAutoReportWithTemplate(dir,null);
71+
public static String newCVAutoReportWithTemplate(List<DetailItem> list, String dir) throws IOException {
72+
return newCVAutoReportWithTemplate(list, dir,null);
7373
}
74-
public static String newCVAutoReportWithTemplate(String dir, String outputFile) throws IOException {
75-
return newCVAutoReportWithTemplate(dir, outputFile, null);
74+
public static String newCVAutoReportWithTemplate(List<DetailItem> list, String dir, String outputFile) throws IOException {
75+
return newCVAutoReportWithTemplate(list, dir, outputFile, null);
7676
}
77-
public static String newCVAutoReportWithTemplate(String dir, String outputFile, String templateFile) throws IOException {
78-
try {
79-
return fillCVAutoReport(dir, outputFile, templateFile);
80-
} catch (Exception e) {
81-
e.printStackTrace();
82-
return generateCVAutoReport(dir, outputFile);
83-
}
77+
public static String newCVAutoReportWithTemplate(List<DetailItem> list, String dir, String outputFile, String templateFile) throws IOException {
78+
// try {
79+
// return fillCVAutoReport(list, dir, outputFile, templateFile);
80+
// } catch (Exception e) {
81+
// e.printStackTrace();
82+
return generateCVAutoReport(list, dir, outputFile);
83+
// }
8484
}
8585

86-
public static String fillCVAutoReport(String dir, String outputFile, String templateFile) throws IOException {
86+
public static String fillCVAutoReport(List<DetailItem> list, String dir, String outputFile, String templateFile) throws IOException {
8787
if (StringUtil.isNotEmpty(dir, false) && ! dir.endsWith(File.separator)) {
8888
dir += File.separator;
8989
}
@@ -104,31 +104,31 @@ public static String fillCVAutoReport(String dir, String outputFile, String temp
104104
List<LinkedHashMap<String, Object>> summaryList = new ArrayList<>();
105105
summaryList.add(new LinkedHashMap<String, Object>() {{
106106
put("标签", "person");
107-
put("目标数", Math.round(100*Math.random()));
108-
put("正确数", Math.round(70*Math.random()));
109-
put("误报数", Math.round(20*Math.random()));
107+
// put("目标数", Math.round(100*Math.random()));
108+
// put("正确数", Math.round(70*Math.random()));
109+
// put("误报数", Math.round(20*Math.random()));
110110
}});
111111
summaryList.add(new LinkedHashMap<String, Object>() {{
112112
put("标签", "car");
113-
put("目标数", Math.round(22*Math.random()));
114-
put("正确数", Math.round(17*Math.random()));
115-
put("误报数", Math.round(0*Math.random()));
113+
// put("目标数", Math.round(22*Math.random()));
114+
// put("正确数", Math.round(17*Math.random()));
115+
// put("误报数", Math.round(0*Math.random()));
116116
}});
117117
summaryList.add(new LinkedHashMap<String, Object>() {{
118118
put("标签", "bike");
119-
put("目标数", Math.round(50*Math.random()));
120-
put("正确数", Math.round(24*Math.random()));
121-
put("误报数", Math.round(13*Math.random()));
119+
// put("目标数", Math.round(50*Math.random()));
120+
// put("正确数", Math.round(24*Math.random()));
121+
// put("误报数", Math.round(13*Math.random()));
122122
}});
123123

124124
// 第二部分：图片详情
125125
List<Map<String, Object>> detailList = new ArrayList<>();
126126
detailList.add(new LinkedHashMap<String, Object>() {{
127127
put("原图", "img0.jpg");
128128
put("渲染图", "img0_res.jpg");
129-
put("目标数", Math.round(20*Math.random()));
130-
put("正确数", Math.round(10*Math.random()));
131-
put("误报数", Math.round(3*Math.random()));
129+
// put("目标数", Math.round(20*Math.random()));
130+
// put("正确数", Math.round(10*Math.random()));
131+
// put("误报数", Math.round(3*Math.random()));
132132
// put("漏检数", Math.round(10*Math.random()));
133133
put("JSON 结果", "{\"bboxes\":[{\"id\":1,\"label\":\"person\",\"score\":0.92}]}");
134134
put("核对", "✔");
@@ -137,9 +137,9 @@ public static String fillCVAutoReport(String dir, String outputFile, String temp
137137
detailList.add(new LinkedHashMap<String, Object>() {{
138138
put("原图", "img1.jpg");
139139
put("渲染图", "img1_res.jpg");
140-
put("目标数", Math.round(5*Math.random()));
141-
put("正确数", Math.round(4*Math.random()));
142-
put("误报数", Math.round(2*Math.random()));
140+
// put("目标数", Math.round(5*Math.random()));
141+
// put("正确数", Math.round(4*Math.random()));
142+
// put("误报数", Math.round(2*Math.random()));
143143
// put("漏检数", Math.round(1*Math.random()));
144144
put("JSON 结果", "{\"bboxes\":[{\"id\":1,\"label\":\"person\",\"score\":0.85}]}");
145145
put("核对", "×");
@@ -202,7 +202,7 @@ public void doAfterAllAnalysed(AnalysisContext context) {}
202202
try {
203203
// 设置临时目录，避免 macOS 沙盒问题
204204
System.setProperty("java.io.tmpdir", File.separator + "tmp");
205-
return fillCVAutoReport(Objects.equals(dir, File.separator + "tmp") ? "" : File.separator + "tmp", outputFile, templateFile);
205+
return fillCVAutoReport(list, Objects.equals(dir, File.separator + "tmp") ? "" : File.separator + "tmp", outputFile, templateFile);
206206
} catch (Throwable e2) {
207207
e2.printStackTrace();
208208
String msg2 = Objects.equals(dir, "") ? null : StringUtil.noBlank(e2.getMessage()).toLowerCase();
@@ -211,7 +211,7 @@ public void doAfterAllAnalysed(AnalysisContext context) {}
211211
}
212212

213213
try {
214-
return fillCVAutoReport("", outputFile, templateFile);
214+
return fillCVAutoReport(list, "", outputFile, templateFile);
215215
} catch (Throwable e3) {
216216
e3.printStackTrace();
217217
throw new ExcelGenerateException(e.getMessage() + "; \n" + e2.getMessage(), e3);
@@ -223,7 +223,7 @@ public void doAfterAllAnalysed(AnalysisContext context) {}
223223
return outputFile;
224224
}
225225

226-
public static String generateCVAutoReport(String dir, String fileName) throws IOException {
226+
public static String generateCVAutoReport(List<DetailItem> list, String dir, String fileName) throws IOException {
227227
if (StringUtil.isNotEmpty(dir, false) && ! dir.endsWith(File.separator)) {
228228
dir += File.separator;
229229
}
@@ -235,7 +235,7 @@ public static String generateCVAutoReport(String dir, String fileName) throws IO
235235
}
236236

237237
// 模拟从数据库或服务获取的详情数据
238-
List<DetailItem> detailItems = getMockDetailData();
238+
List<DetailItem> detailItems = list; // getMockDetailData();
239239

240240
// 动态计算详情区域的表头行索引
241241
int detailHeaderRowIndex = 1 + 6 + 10; // 标题区(1) + 统计区(标题+6行数据) + 空白行(1) = 17
@@ -259,7 +259,7 @@ public static String generateCVAutoReport(String dir, String fileName) throws IO
259259
try {
260260
// 设置临时目录，避免 macOS 沙盒问题
261261
System.setProperty("java.io.tmpdir", File.separator + "tmp");
262-
return generateCVAutoReport(Objects.equals(dir, File.separator + "tmp") ? "" : File.separator + "tmp", fileName);
262+
return generateCVAutoReport(list, Objects.equals(dir, File.separator + "tmp") ? "" : File.separator + "tmp", fileName);
263263
} catch (Throwable e2) {
264264
e2.printStackTrace();
265265
String msg2 = Objects.equals(dir, "") ? null : StringUtil.noBlank(e2.getMessage()).toLowerCase();
@@ -268,7 +268,7 @@ public static String generateCVAutoReport(String dir, String fileName) throws IO
268268
}
269269

270270
try {
271-
return generateCVAutoReport("", fileName);
271+
return generateCVAutoReport(list, "", fileName);
272272
} catch (Throwable e3) {
273273
e3.printStackTrace();
274274
throw new ExcelGenerateException(e.getMessage() + "; \n" + e2.getMessage(), e3);
@@ -298,9 +298,9 @@ private static List<List<Object>> prepareData(List<DetailItem> detailItems, int
298298
// 定义详情数据区域范围，用于统计公式
299299
int detailDataEndRow = detailDataStartRow + detailItems.size() - 1;
300300
// 注意：因为新增了“核对”列，详情数据列向右平移
301-
String targetRange = String.format("D%d:D%d", detailDataStartRow, detailDataEndRow); // 目标数范围
302-
String correctRange = String.format("E%d:E%d", detailDataStartRow, detailDataEndRow); // 正确数范围
303-
String fpRange = String.format("F%d:F%d", detailDataStartRow, detailDataEndRow); // 误报数范围
301+
String targetRange = String.format("C%d:C%d", detailDataStartRow, detailDataEndRow); // 目标数范围
302+
String correctRange = String.format("D%d:D%d", detailDataStartRow, detailDataEndRow); // 正确数范围
303+
String fpRange = String.format("E%d:E%d", detailDataStartRow, detailDataEndRow); // 误报数范围
304304

305305
// 动态生成每一行统计数据
306306
list.add(createSummaryRow("总计", "SUM", 3, targetRange, correctRange, fpRange));
@@ -386,7 +386,7 @@ private static List<DetailItem> getMockDetailData() {
386386
return list;
387387
}
388388

389-
private static class DetailItem {
389+
public static class DetailItem {
390390
private final String imageName;
391391
private final String renderName;
392392
private final int targetCount;

‎APIJSON-Java-Server/APIJSONBoot-MultiDataSource/src/main/java/apijson/boot/FileController.java‎

Lines changed: 78 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,9 @@
88
//import javax.annotation.PostConstruct;
99

1010
import apijson.ExcelUtil;
11+
import apijson.RequestMethod;
1112
import apijson.StringUtil;
13+
import com.alibaba.fastjson2.JSONArray;
1214
import org.apache.commons.io.FileUtils;
1315
import org.springframework.core.io.InputStreamResource;
1416
import org.springframework.http.HttpHeaders;
@@ -146,7 +148,8 @@ public ResponseEntity<Object> download(@PathVariable(name = "fileName") String f
146148
}
147149

148150
HttpHeaders headers = new HttpHeaders();
149-
headers.add("Content-Disposition", String.format("attachment;filename=\"%s;filename*=UTF_8''%s", fileName, encodedFileName));
151+
// headers.add("Content-Disposition", String.format("attachment;filename=\"%s;filename*=UTF_8''%s", fileName, encodedFileName));
152+
headers.add("Content-Disposition", String.format("attachment;filename=\"%s", fileName));
150153
headers.add("Cache-Control", "public, max-age=86400");
151154
// headers.add("Cache-Control", "no-cache,no-store,must-revalidate");
152155
// headers.add("Pragma", "no-cache");
@@ -200,7 +203,80 @@ public ResponseEntity<Object> downloadCVReport(@PathVariable(name = "reportId")
200203
}
201204

202205
if ((file.exists() ? file.length() : 0) < 10*1024) {
203-
String filePath = ExcelUtil.newCVAutoReportWithTemplate(fileUploadRootDir, name);
206+
JSONObject request = new JSONObject();
207+
208+
{ // TestRecord <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
209+
JSONObject testRecord = new JSONObject();
210+
testRecord.put("reportId", reportId);
211+
testRecord.put("@column", "documentId");
212+
request.put("TestRecord", testRecord);
213+
} // TestRecord >>>>>>>>>>>>>>>>>>>>>>>>>>>>>
214+
215+
{ // [] <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
216+
JSONObject item = new JSONObject();
217+
item.put("count", 3);
218+
item.put("join", "&/TestRecord");
219+
220+
{ // Random <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
221+
JSONObject random = new JSONObject();
222+
random.put("documentId@", "TestRecord/documentId");
223+
random.put("@column", "id,img");
224+
random.put("@order", "date-");
225+
random.put("@combine", "file[>,img[>");
226+
random.put("file[>", 0);
227+
random.put("img[>", 0);
228+
item.put("Random", random);
229+
} // Random >>>>>>>>>>>>>>>>>>>>>>>>>>>>>
230+
231+
{ // TestRecord <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
232+
JSONObject testRecord = new JSONObject();
233+
testRecord.put("randomId@", "/Random/id");
234+
testRecord.put("@column", "id,total,correct,wrong,compare,response");
235+
testRecord.put("reportId", reportId);
236+
testRecord.put("total>=", 0);
237+
testRecord.put("correct>=", 0);
238+
testRecord.put("wrong>=", 0);
239+
testRecord.put("@order", "date-");
240+
item.put("TestRecord", testRecord);
241+
} // TestRecord >>>>>>>>>>>>>>>>>>>>>>>>>>>>>
242+
243+
request.put("[]", item);
244+
} // [] >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
245+
246+
DemoParser parser = new DemoParser(RequestMethod.GET, false);
247+
JSONObject response = parser.parseResponse(request);
248+
JSONArray array = response.getJSONArray("[]");
249+
250+
List<ExcelUtil.DetailItem> list = new ArrayList<>();
251+
if (array != null) {
252+
for (int i = 0; i < array.size(); i++) {
253+
JSONObject item = array.getJSONObject(i);
254+
JSONObject random = item == null ? null : item.getJSONObject("Random");
255+
JSONObject testRecord = item == null ? null : item.getJSONObject("TestRecord");
256+
if (random == null) {
257+
random = new JSONObject();
258+
}
259+
if (testRecord == null) {
260+
testRecord = new JSONObject();
261+
}
262+
263+
String fn = random.getString("file");
264+
int ind = fn.lastIndexOf(".");
265+
String nfn = fn.substring(0, ind) + "_render" + fn.substring(ind);
266+
list.add(new ExcelUtil.DetailItem(
267+
fn,
268+
nfn, // TODO 调用 JSONResponse.js 来渲染
269+
testRecord.getIntValue("total"),
270+
testRecord.getIntValue("correct"),
271+
testRecord.getIntValue("wrong"),
272+
testRecord.getString("response"),
273+
"✅",
274+
testRecord.getString("compare")
275+
));
276+
}
277+
}
278+
279+
String filePath = ExcelUtil.newCVAutoReportWithTemplate(list, fileUploadRootDir, name);
204280
if (! Objects.equals(filePath, path)) {
205281
try {
206282
File sourceFile = new File(filePath);

‎APIJSON-Java-Server/APIJSONBoot-MultiDataSource/src/main/resources/static/cv/js/server.js‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -65,7 +65,7 @@ function update() {
6565
+ '\nRandom & Order: ' + App.randomDoneCount + ' / ' + App.randomAllCount + ' = ' + (100*randomProgress) + '%';
6666
};
6767

68-
const PORT = 3000;
68+
const PORT = 3003;
6969

7070
var done = false;
7171
const app = new Koa();

0 commit comments

Comments
 (0)