AgentController.java
10.8 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
package com.tianbo.imfClient.controller;
import com.tianbo.imfClient.dao.AGENTSTATISTICSMapper;
import com.tianbo.imfClient.dao.ARRIVEDSECONDARYMapper;
import com.tianbo.imfClient.model.ORIGINMANIFESTMASTER;
import com.tianbo.imfClient.model.ResultJson;
import com.tianbo.util.POI.ExportExcelUtil;
import lombok.extern.slf4j.Slf4j;
import oracle.sql.DATE;
import org.apache.poi.hssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 出港运抵服务接口
*/
@Slf4j
@RestController
@RequestMapping("/agent")
public class AgentController {
@Autowired
private AGENTSTATISTICSMapper agentstatisticsMapper;
/**
* 查询统计出港业务数据接口
*/
@GetMapping("/analysis")
public void analysis(@RequestParam(value = "startdate",required = true) String startdate,
@RequestParam(value = "enddate",required = true) String enddate,
HttpServletResponse httpServletResponse){
Map map = new HashMap();
map.put("startdate",startdate);
map.put("enddate",enddate);
List<ORIGINMANIFESTMASTER> list = agentstatisticsMapper.statistics(map);
exportExcel(list,httpServletResponse);
}
public void exportExcel(List<ORIGINMANIFESTMASTER> list,HttpServletResponse response){
// 创建工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建表
HSSFSheet sheet = workbook.createSheet("export");
// 创建行
HSSFRow row = sheet.createRow(0);
// 创建单元格样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
// 表头
String[] head = {
"运单号",
"航空托运人名称",
"航空销售代理人名称",
"航协编号",
"航空公司运输证明",
"运输条件鉴定书",
"承运人",
"航班号",
"航班日期",
"航班起始站",
"航班目的站",
"运单件数",
"运单重量",
"操作时间",
"关区",
"预配品名",
"收货人公司",
"收货人地址",
"收货人城市",
"收货人电话",
"收货人国家",
"收货人名称",
"发货人名称",
"发货人地址",
"发货人城市",
"发货人国家",
"发货人电话",
"发货人名称",
"海关回执状态",
"海关回执信息",
"发货人编码",
"收货人编码",
"货物品名",
"二级类名称",
"一级类名称",
"代理人三字码",
"代理人全称",
"代理人类别",
"代理联系人",
"代理联系人电话"
};
HSSFCell cell;
// 设置表头
for (int i = 0; i < head.length; i++) {
cell = row.createCell(i);
cell.setCellValue(head[i]);
cell.setCellStyle(cellStyle);
// 设置单元格宽度
sheet.setColumnWidth(i, 4000);
}
// 设置表格内容
for (int i = 0; i < list.size(); i++){
row = sheet.createRow( i + 1);
ORIGINMANIFESTMASTER originmanifestmaster = list.get(i);
// 这里是内容设置，替换则自己的数据即可
String[] excelTitle = new String[40];
excelTitle[0] = originmanifestmaster.getAutoid();
excelTitle[1] = originmanifestmaster.getWaybillnomaster();
excelTitle[2] = originmanifestmaster.getSegment();
excelTitle[3] = originmanifestmaster.getOriginatingstation();
excelTitle[4] = originmanifestmaster.getDestinationstation();
excelTitle[5] = originmanifestmaster.getTotalweight();
excelTitle[6] = originmanifestmaster.getTotalpiece();
excelTitle[7] = originmanifestmaster.getManifesttotalpiece();
excelTitle[8] = originmanifestmaster.getManifesttotalweight();
excelTitle[9] = originmanifestmaster.getFlightno();
excelTitle[10] = originmanifestmaster.getProductname();
excelTitle[11] = originmanifestmaster.getCustomsstatus();
excelTitle[12] = originmanifestmaster.getCarrier1();
excelTitle[13] = originmanifestmaster.getArrivalstation1();
excelTitle[14] = originmanifestmaster.getCarrier2();
excelTitle[15] = originmanifestmaster.getArrivalstation2();
excelTitle[16] = originmanifestmaster.getCarrier3();
excelTitle[17] = originmanifestmaster.getArrivalstation3();
excelTitle[18] = originmanifestmaster.getPaymode();
excelTitle[19] = originmanifestmaster.getCustomscode();
excelTitle[20] = originmanifestmaster.getSpecialgoodscode();
excelTitle[21] = originmanifestmaster.getShippername();
excelTitle[22] = originmanifestmaster.getShipperaddress();
excelTitle[23] = originmanifestmaster.getConsigneename();
excelTitle[24] = originmanifestmaster.getConsigneeaddress();
excelTitle[25] = originmanifestmaster.getReceiptinformation();
excelTitle[26] = originmanifestmaster.getSpecificConsigneePhone();
excelTitle[27] = originmanifestmaster.getConsigneePhone();
excelTitle[28] = originmanifestmaster.getStatus();
excelTitle[29] = originmanifestmaster.getIsbatch();
excelTitle[30] = originmanifestmaster.getOriginatingstationBill();
excelTitle[31] = originmanifestmaster.getDestinationstationBill();
excelTitle[32] = originmanifestmaster.getReportorder();
excelTitle[33] = originmanifestmaster.getIslast();
excelTitle[34] = originmanifestmaster.getShipperCode();
excelTitle[35] = originmanifestmaster.getShipperCountrycode();
excelTitle[36] = originmanifestmaster.getShipperPhone();
excelTitle[37] = originmanifestmaster.getShipperFax();
excelTitle[38] = originmanifestmaster.getConsigneeCode();
excelTitle[39] = originmanifestmaster.getConsigneeCountrycode();
for (int j = 0; j < excelTitle.length; j++){
row.createCell(j).setCellValue(excelTitle[j]);
}
}
// 设置文件名
String title ="export.xls";
try {
String fileName = new String(title.getBytes(StandardCharsets.UTF_8), "ISO-8859-1");
fileName = URLEncoder.encode(fileName,"utf-8");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setCharacterEncoding("utf-8");
//默认Excel名称
response.setHeader("Content-Disposition", "attachment;filename="+fileName+";"+"filename*=utf-8''"+fileName);
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
try{
OutputStream os = response.getOutputStream();
workbook.write(os);
os.flush();
os.close();
log.info("导出完毕");
}catch (IOException e) {
e.printStackTrace();
}
}
public void export(List<ORIGINMANIFESTMASTER> list,HttpServletResponse response){
String[] header = {
"运单号",
"航空托运人名称",
"航空销售代理人名称",
"航协编号",
"航空公司运输证明",
"运输条件鉴定书",
"承运人",
"航班号",
"航班日期",
"航班起始站",
"航班目的站",
"运单件数",
"运单重量",
"操作时间",
"关区",
"预配品名",
"收货人公司",
"收货人地址",
"收货人城市",
"收货人电话",
"收货人国家",
"收货人名称",
"发货人名称",
"发货人地址",
"发货人城市",
"发货人国家",
"发货人电话",
"发货人名称",
"海关回执状态",
"海关回执信息",
"发货人编码",
"收货人编码",
"货物品名",
"二级类名称",
"一级类名称",
"代理人三字码",
"代理人全称",
"代理人类别",
"代理联系人",
"代理联系人电话"
};
String[] columns = {
"autoid",
"waybillnomaster",
"segment",
"originatingstation",
"destinationstation",
"totalweight",
"totalpiece",
"manifesttotalpiece",
"manifesttotalweight",
"flightno",
"productname",
"customsstatus",
"carrier1",
"arrivalstation1",
"carrier2",
"arrivalstation2",
"carrier3",
"arrivalstation3",
"paymode",
"customscode",
"specialgoodscode",
"shippername",
"shipperaddress",
"consigneename",
"consigneeaddress",
"receiptinformation",
"specificConsigneePhone",
"consigneePhone",
"status",
"isbatch",
"originatingstationBill",
"destinationstationBill",
"reportorder",
"islast",
"shipperCode",
"shipperCountrycode",
"shipperPhone",
"shipperFax",
"consigneeCode",
"consigneeCountrycode"
};
ExportExcelUtil exportExcelUtil = new ExportExcelUtil();
try{
exportExcelUtil.export("export.xls","export",header,columns,list,response);
}catch (Exception e){
e.printStackTrace();
}
}
}