JXL 导出EXCEL
需要把320排班表导出到 EXCEL
使用的是 JXL
系统中已经有了一个工具类可以把 List 的 数据写入到 INPUTSTREAM中。
定义好表头和对应的字段名称。
ByteArrayOutputStream stream = new ByteArrayOutputStream();
String[] titleNames = new String[]{"XXX", "XXXX", "XXXXX", "XXXX", "XXXX"};
String[] fields = new String[]{"flixxxxNo", "flxxxxDate", "pxxxxtName", "coxxCount", "shxxtCxxnt"};
List<CrewAssignLogDetail2> details = crewAssignLogService.queryAssignLogDetail2sByAssignLogId(assignLogId);
ExcelWriter.writeExcel(stream, titleNames, fields, details);
InputStream is = new ByteArrayInputStream(stream.toByteArray());
但是, 发现这种只支持属性为数据立即可取, 我这边许多的属性是List类型, 不能直接得到。
于是改造,把字段和属性写到MAP中,
ByteArrayOutputStream stream = new ByteArrayOutputStream();
String[] titleNames = new String[]{"套组编号", "批次", "组基准月",
"单位", "姓名", "可排月", "技术标准", "单位", "姓名", "可排月", "技术标准",
"报到日期",
"日期", "内容", "日期", "内容", "日期", "内容",
"日期", "时间", "场次", "教员", "单位",
"日期", "时间", "场次", "教员", "单位",
"日期", "时间", "场次", "检察员", "单位",
"执照签注类型"};
String[] fields = new String[]{"suite_no", "batch_no", "bench_Month",
"unitName0", "pilotName0", "month0", "technicalStandard0", "unitName1", "pilotName1", "month1", "technicalStandard1",//学员
"checkin_date",
"t_date0", "t_content0", "t_date1", "t_content1", "t_date2", "t_content2",//理论课
"s_date0", "s_time0", "s_no0", "teacherName0", "unitName0", //场次
"s_date1", "s_time1", "s_no1", "teacherName1", "unitName1",
"s_date2", "s_time2", "s_no2", "teacherName2", "unitName2",
"sign_property"};
List<Map<String, Object>> mapList = new ArrayList<Map<String,Object>>();
for(SuitAdminView suitView : simulatorResList){
Map<String, Object> map = new HashMap<String, Object>();
map.put("suite_no", suitView.getSuite_no());map.put("batch_no", suitView.getBatch_no());map.put("bench_Month", suitView.getBench_Month());
for(int i=0;i<suitView.getStudentList().length;i++){
map.put("unitName"+i, suitView.getStudentList()[i].getUnitName());
map.put("pilotName"+i, suitView.getStudentList()[i].getPilotName());
map.put("month"+i, suitView.getStudentList()[i].getMonth());
map.put("technicalStandard"+i, suitView.getStudentList()[i].getTechnicalStandard());
}
map.put("checkin_date", suitView.getCheckin_date());
for(int i=0;i<suitView.getTheoryDesViewList().length;i++){
map.put("t_date"+i, suitView.getTheoryDesViewList()[i].getT_date());
map.put("t_content"+i, suitView.getTheoryDesViewList()[i].getT_content());
}
for(int i=0;i<suitView.getTraining_simDesViewList().length;i++){
map.put("s_date"+i, suitView.getTraining_simDesViewList()[i].getS_date());
map.put("s_time"+i, suitView.getTraining_simDesViewList()[i].getS_time());
map.put("s_no"+i, suitView.getTraining_simDesViewList()[i].getS_no());
map.put("teacherName"+i, suitView.getTraining_simDesViewList()[i].getTeacherName());
map.put("unitName"+i, suitView.getTraining_simDesViewList()[i].getUnitName());
}
mapList.add(map);
}
A320ExcelWriter.writeExcel(stream, titleNames, fields, mapList);
is = new ByteArrayInputStream(stream.toByteArray());
fileName = URLEncoder.encode((fixed_year + "A320总排班.xls"),GlobalConfig.getDefaultWebEncoding());
输出之后, 发现文本格式的单元格双击后, 会变成数字格式的。
查到需要用一个格式设置类
WritableCellFormat cellFormat = new WritableCellFormat(NumberFormats.TEXT);
new Label(column, row, String.valueOf(object), cellFormat);
通过阅读jxl文档,问题已经解决,附代码如下:
WritableCellFormat contentFromart = new WritableCellFormat(NumberFormats.TEXT);
jxl.write.Label labelCFC2 = new jxl.write.Label(0,15, " 01234567890123456789 ", contentFromart);
sheet.addCell(labelCFC2);
NumberFormats.TEXT是一个强制显示文本的格式,上面的代码将显示的数字为01234567890123456789 ,而非1234567890123456789。
最近不忙, 改进了一下, 增加了子对象的访问支持, 且集合对象可以通过 XX[0],,,, 来访问集合下标的访问,支持数组和列表。
于是,上面的代码可以改进, 不用再采取 MAP 的方式再次封装了,直接把对象传递进去, 使用定义的Field名称进行取值。
ByteArrayOutputStream stream = new ByteArrayOutputStream();
String[] titleNames = new String[]{"套组编号", "批次", "组基准月",
"单位", "姓名", "可排月", "技术标准", "单位", "姓名", "可排月", "技术标准",
"报到日期",
"日期", "内容", "日期", "内容", "日期", "内容",
"日期", "时间", "场次", "教员", "单位",
"日期", "时间", "场次", "教员", "单位",
"日期", "时间", "场次", "检察员", "单位",
"执照签注类型"};
String[] fields = new String[]{"suite_no", "batch_no", "bench_Month",
"studentList[0].unitName", "studentList[0].pilotName", "studentList[0].month", "studentList[0].technicalStandard",
"studentList[1].unitName", "studentList[1].pilotName", "studentList[1].month", "studentList[1].technicalStandard",//学员
"checkin_date",
"theoryDesViewList[0].t_date", "theoryDesViewList[0].t_content",
"theoryDesViewList[1].t_date", "theoryDesViewList[1].t_content",
"theoryDesViewList[2].t_date", "theoryDesViewList[2].t_content",//理论课
"training_simDesViewList[0].s_date", "training_simDesViewList[0].s_time", "training_simDesViewList[0].s_no", "training_simDesViewList[0].teacherName", "training_simDesViewList[0].unitName", //场次
"training_simDesViewList[1].s_date", "training_simDesViewList[1].s_time", "training_simDesViewList[1].s_no", "training_simDesViewList[1].teacherName", "training_simDesViewList[1].unitName",
"training_simDesViewList[2].s_date", "training_simDesViewList[2].s_time", "training_simDesViewList[2].s_no", "training_simDesViewList[2].teacherName", "training_simDesViewList[2].unitName",
"sign_property"};
如果数据量不是很大, 可以使用
更加方便维护。
但是这种方式如果数据量比较大, 就会出现内存占用过大的问题。
上一篇: 利用poi数据导出到excel demo