public static String createCourseUnitExl(String name, String filePath,List<CourseUnit> list ) {

String[] head0 = new String[] { "年级及专业", "总人数", "大班数", "小班数", "课程编号","课程", "总学时",

"总学分", "已完成","本学期" ,"周学时","上课起止周次","任课教师","教学组长","备注"};

String[] head1 = new String[] { "学时", "学分", "总学时", "理论","实验" ,"课外","学分","理论","实验"};

String[] head2 = new String[] { "姓名", "职称", "学时", "姓名", "职称", "学时" };

String[] headnum0 = new String[] { "0,2,0,0", "0,2,1,1","0,2,2,2","0,2,3,3","0,2,4,4","0,2,5,5","0,2,6,6","0,2,7,7","0,0,8,9","0,0,10,14"

,"0,2,15,15","0,2,16,16","0,0,17,22","0,2,23,23","0,2,24,24" };//对应excel中的行和列，下表从0开始{"开始行,结束行,开始列,结束列"}

String[] headnum1 = new String[] {"1,2,8,8", "1,2,9,9", "1,2,10,10","1,2,11,11","1,2,12,12","1,2,13,13","1,2,14,14","1,1,17,19"

,"1,1,20,22"};

// String[] headnum2 = new String[] { "3,3,19,19", "3,3,20,20", "3,3,21,21","3,3,22,22" };

try {

HSSFWorkbook wb = reportXls(list,head0,headnum0, head1, headnum1,head2);

// 第六步，将文件存到指定位置

String newFileName = DateUtil.DateTimeToStringNOSSS(new Date());

String path = filePath + File.separator + newFileName + ".xls";

FileOutputStream fout = new FileOutputStream(path);

wb.write(fout);

fout.close();

return path;

} catch (Exception e) {

e.printStackTrace();

}

return "";

}

public static HSSFWorkbook reportXls( List<CourseUnit> dataList, String[] head0, String[] headnum0,

String[] head1, String[] headnum1, String[] head2)

throws Exception {

HSSFWorkbook workbook = new HSSFWorkbook();

HSSFSheet sheet = workbook.createSheet("任务书");// 创建一个表

// 普通单元格样式（中文）

HSSFFont font2 = workbook.createFont();

font2.setFontName("宋体");

font2.setFontHeightInPoints((short) 12);

HSSFCellStyle style1 = workbook.createCellStyle();

style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中

style1.setWrapText(true); // 换行

style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中

HSSFCellStyle style2 = workbook.createCellStyle();

style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框

style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框

style2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框

style2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

style2.setFont(font2);

style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中

style2.setWrapText(true); // 换行

style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中

sheet.setDefaultRowHeight((short)360);//设置行高

HSSFRow row = sheet.createRow(0);

row.setHeight((short) 0x349);

HSSFCell cell = row.createCell(0);

for (int i = 0; i < 24; i++) {

cell = row.createCell(i);

if(i<8)

cell.setCellValue(head0[i]);

if(i==8)

cell.setCellValue(head0[8]);

if(i==10)

cell.setCellValue(head0[9]);

if(i==15)

cell.setCellValue(head0[10]);

if(i==16)

cell.setCellValue(head0[11]);

if(i==17)

cell.setCellValue(head0[12]);

}

//动态合并单元格

for (int i = 0; i < headnum0.length; i++) {

String[] temp = headnum0[i].split(",");

Integer startrow = Integer.parseInt(temp[0]);

Integer overrow = Integer.parseInt(temp[1]);

Integer startcol = Integer.parseInt(temp[2]);

Integer overcol = Integer.parseInt(temp[3]);

sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,

startcol, overcol));

}

//设置合并单元格的参数并初始化带边框的表头（这样做可以避免因为合并单元格后有的单元格的边框显示不出来）

row = sheet.createRow(1);

for (int i = 0; i < 24; i++) {

cell = row.createCell(i);

cell.setCellStyle(style2);

if(i > 7 && i< 15) {

cell.setCellValue(head1[i-6]);

}

if(i==17) cell.setCellValue(head1[7]);

if(i==20) cell.setCellValue(head1[8]);

}

//动态合并单元格

for (int i = 0; i < headnum1.length; i++) {

String[] temp = headnum1[i].split(",");

Integer startrow = Integer.parseInt(temp[0]);

Integer overrow = Integer.parseInt(temp[1]);

Integer startcol = Integer.parseInt(temp[2]);

Integer overcol = Integer.parseInt(temp[3]);

sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,

startcol, overcol));

}

row = sheet.createRow(2);

for (int i = 0; i < 24; i++) {

cell = row.createCell(i);

if(i > 16 && i< 23) {

cell.setCellValue(head2[i-17]);

}

}

// 设置列值-内容

int i = 3,r=3;

for (int index = 0;index<dataList.size();index++) {

CourseUnit unit = dataList.get(index);

List<CourseUnitTeacher> list= unit.getTeacherList();

int size = list.size();

r+=size;

row = sheet.createRow(i );

List<String> itemList = unit.getItems();

for (int j = 0; j < size; j++) {

row = sheet.createRow(i);

i++;

for (int k = 0; k < 24; k++) {

cell = row.createCell(k);

if (k<17&&j==0){

cell.setCellValue(itemList.get(k));

sheet.addMergedRegion(new CellRangeAddress(i-1, r-1 , k,k));

}else{

setTeacher(list.get(j),cell,k);

}

cell.setCellStyle(style1);

}

}

}

return workbook;

}

public static void setTeacher(CourseUnitTeacher t, HSSFCell cell,int k ){

if(t.getCourseCategory() == CourseCategoryEnum.THEORY.getCategory()){

if(k==17)

cell.setCellValue(t.getTeacherName());

if(k==18)

cell.setCellValue(t.getPositionName());

if(k==19)

cell.setCellValue(t.getClassHour());

}else if(t.getCourseCategory() ==CourseCategoryEnum.LAB.getCategory()){

if(k==20)

cell.setCellValue(t.getTeacherName());

if(k==21)

cell.setCellValue(t.getPositionName());

if(k==22)

cell.setCellValue(t.getClassHour());

}

}