JXL 导出EXCEL

需要把320排班表导出到 EXCEL 

 

使用的是 JXL

 

系统中已经有了一个工具类可以把 List 的 数据写入到 ＩＮＰＵＴＳＴＲＥＡＭ中。　

 

定义好表头和对应的字段名称。　

 

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类型，　不能直接得到。

于是改造，把字段和属性写到ＭＡＰ中，　

 

 

		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"};
		

 

 

 

 

如果数据量不是很大， 可以使用

 

基于模板的excel导出

 

更加方便维护。

 

但是这种方式如果数据量比较大， 就会出现内存占用过大的问题。