查询数据库打印报表到Excel中
查询数据库
int[] type = new int[]{66,68,69};
String mac = request.getParameter("mac");
String strStart = request.getParameter("start");
String strEnd = request.getParameter("end");
Date start = DateUtil.StringtoDate(request.getParameter("start"),
DateUtil.FORMAT_TWO);
Date end = DateUtil.StringtoDate(request.getParameter("end"),
DateUtil.FORMAT_TWO);
List<LinkedHashMap<String,Object>> list = new ArrayList<LinkedHashMap<String,Object>>();
for(int i = 0; i < type.length;i++){
List<LogFlowDevice> devices = flowDeviceService.findByTime(mac, start, end, type[i]);
if(devices.size() == 0) continue;
LinkedHashMap<String,Object> map = new LinkedHashMap<String, Object>();
String sn = devices.get(0).getMac();
int rssiMax = devices.get(0).getRssi();
int rssiMin = devices.get(0).getRssi();
int snrMax = devices.get(0).getLqi();
int snrMin = devices.get(0).getLqi();
float rssiAver = 0;
float snrAver = 0;
for(int j = 0;j<devices.size();j++){
LogFlowDevice device = devices.get(j);
rssiMax = rssiMax<device.getRssi()?device.getRssi():rssiMax;
rssiMin = rssiMin>device.getRssi()?device.getRssi():rssiMin;
snrMax = snrMax<device.getLqi()?device.getLqi():snrMax;
snrMin = snrMin>device.getLqi()?device.getLqi():snrMin;
rssiAver += device.getRssi();
snrAver += device.getLqi();
}
rssiAver = rssiAver/devices.size();
snrAver = snrAver/devices.size();
map.put("type", "消息类型"+type[i]);
map.put("sn",sn );
map.put("rssimax",rssiMax );
map.put("rssimin",rssiMin );
map.put("rssiaver", rssiAver);
map.put("snrmax", snrMax);
map.put("snrmin", snrMin);
map.put("snraver", snrAver);
map.put("number", devices.size());
list.add(map);
}
Test.dooutof(list);
Test测试类，生成excel报表，注意需要读入jxl.jar包
PS：下边代码中的注释没有删除就是为了看得更明白
public class Test {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
//dooutof();
}
public static String dooutof(List<LinkedHashMap<String,Object>> list) {
// 文件名称与路径
String realPath = "E:";
String fileName = "tongji.xls";
File file = new File(realPath + "//nms//");// 导出文件存放的位置
if (!file.exists()) {
file.mkdirs();
}
realPath = realPath + "//nms//" + fileName;
// 建立工作薄并写表头
try {
WritableWorkbook wwb = Workbook.createWorkbook(new File(realPath));
WritableSheet ws = wwb.createSheet("Sheet1", 0);// 建立工作簿
// 写表头
jxl.write.Label labe11 = new jxl.write.Label(0, 0, "");
jxl.write.Label labe12 = new jxl.write.Label(1, 0, "sn短号");
jxl.write.Label labe13 = new jxl.write.Label(2, 0, "rssi最大值");
jxl.write.Label labe14 = new jxl.write.Label(3, 0, "rssi最小值");
jxl.write.Label labe15 = new jxl.write.Label(4, 0, "rssi平均值");
jxl.write.Label labe16 = new jxl.write.Label(5, 0, "snr最大值");
jxl.write.Label labe17 = new jxl.write.Label(6, 0, "snr最小值");
jxl.write.Label labe18 = new jxl.write.Label(7, 0, "snr平均值");
jxl.write.Label labe19 = new jxl.write.Label(8, 0, "消息个数");
ws.addCell(labe11);// 放入工作簿
ws.addCell(labe12);
ws.addCell(labe13);
ws.addCell(labe14);
ws.addCell(labe15);
ws.addCell(labe16);
ws.addCell(labe17);
ws.addCell(labe18);
ws.addCell(labe19);
if(list.size() == 0){
// 写入Exel工作表
wwb.write();
// 关闭Excel工作薄对象
wwb.close();
return null;
}
int k = 1;
System.out.println(list.size());
for(int i = 0;i < list.size(); i++){
int j = 0;
jxl.write.Label labe;
LinkedHashMap<String, Object> map = list.get(i);
System.out.println(map.get("type").toString());
labe = new jxl.write.Label(j++, k, map.get("type").toString());
ws.addCell(labe);
labe = new jxl.write.Label(j++, k, map.get("sn").toString());
ws.addCell(labe);
labe = new jxl.write.Label(j++, k, map.get("rssimax").toString());
ws.addCell(labe);
labe = new jxl.write.Label(j++, k, map.get("rssimin").toString());
ws.addCell(labe);
labe = new jxl.write.Label(j++, k, map.get("rssiaver").toString());
ws.addCell(labe);
labe = new jxl.write.Label(j++, k, map.get("snrmax").toString());
ws.addCell(labe);
labe = new jxl.write.Label(j++, k, map.get("snrmin").toString());
ws.addCell(labe);
labe = new jxl.write.Label(j++, k, map.get("snraver").toString());
ws.addCell(labe);
labe = new jxl.write.Label(j++, k, map.get("number").toString());
ws.addCell(labe);
k++;
}
//
// jxl.write.Label labe21 = new jxl.write.Label(0, 1, "消息类型66");
// jxl.write.Label labe22 = new jxl.write.Label(1, 1, "");
// jxl.write.Label labe23 = new jxl.write.Label(2, 1, "");
// jxl.write.Label labe24 = new jxl.write.Label(3, 1, "");
// jxl.write.Label labe25 = new jxl.write.Label(4, 1, "");
// jxl.write.Label labe26 = new jxl.write.Label(5, 1, "");
// jxl.write.Label labe27 = new jxl.write.Label(6, 1, "");
// jxl.write.Label labe28 = new jxl.write.Label(7, 1, "");
// jxl.write.Label labe29 = new jxl.write.Label(8, 1, "");
//
//
//
//
// jxl.write.Label labe31 = new jxl.write.Label(0, 2, "消息类型68");
// jxl.write.Label labe32 = new jxl.write.Label(1, 2, "");
// jxl.write.Label labe33 = new jxl.write.Label(2, 2, "");
// jxl.write.Label labe34 = new jxl.write.Label(3, 2, "");
// jxl.write.Label labe35 = new jxl.write.Label(4, 2, "");
// jxl.write.Label labe36 = new jxl.write.Label(5, 2, "");
// jxl.write.Label labe37 = new jxl.write.Label(6, 2, "");
// jxl.write.Label labe38 = new jxl.write.Label(7, 2, "");
// jxl.write.Label labe39 = new jxl.write.Label(8, 2, "");
//
//
//
//
//
// jxl.write.Label labe41 = new jxl.write.Label(0, 3, "消息类型69");
// jxl.write.Label labe42 = new jxl.write.Label(1, 3, "");
// jxl.write.Label labe43 = new jxl.write.Label(2, 3, "");
// jxl.write.Label labe44 = new jxl.write.Label(3, 3, "");
// jxl.write.Label labe45 = new jxl.write.Label(4, 3, "");
// jxl.write.Label labe46 = new jxl.write.Label(5, 3, "");
// jxl.write.Label labe47 = new jxl.write.Label(6, 3, "");
// jxl.write.Label labe48 = new jxl.write.Label(7, 3, "");
// jxl.write.Label labe49 = new jxl.write.Label(8, 3, "");
// ws.addCell(labe21);// 放入工作簿
// ws.addCell(labe22);
// ws.addCell(labe23);
// ws.addCell(labe24);
// ws.addCell(labe25);
// ws.addCell(labe26);
// ws.addCell(labe27);
// ws.addCell(labe28);
// ws.addCell(labe29);
//
// ws.addCell(labe31);// 放入工作簿
// ws.addCell(labe32);
// ws.addCell(labe33);
// ws.addCell(labe34);
// ws.addCell(labe35);
// ws.addCell(labe36);
// ws.addCell(labe37);
// ws.addCell(labe38);
// ws.addCell(labe39);
//
// ws.addCell(labe41);// 放入工作簿
// ws.addCell(labe42);
// ws.addCell(labe43);
// ws.addCell(labe44);
// ws.addCell(labe45);
// ws.addCell(labe46);
// ws.addCell(labe47);
// ws.addCell(labe48);
// ws.addCell(labe49);
// 写入Exel工作表
wwb.write();
// 关闭Excel工作薄对象
wwb.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}// 此处建立路径
return null;
}
}
生成的tongji.xls文件如下图所示
http://www.cnblogs.com/makexu/
浙公网安备 33010602011771号