package com.ysstect.test.poi;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.hssf.util.Region;
import com.ysstect.test.po.Student;
public class StudentExcel {
public static String outputFile = "d:\\student.xls";
public static void main(String[] args) {
try{
FileOutputStream out = new FileOutputStream(outputFile);
Student student1 = new Student("张三", "男", 18, "三年二班", 99);
Student student2 = new Student("lisi", "男", 18, "三年二班", 99);
List<Student> students = new ArrayList<Student>();
students.add(student1);
students.add(student2);
createExcel createExcel = new createExcel();
HSSFWorkbook workbook = createExcel.createExcel(students);
workbook.write(out);
out.flush();
out.close();
System.out.println("创建完成");
}catch(Exception e){
}
}
}
class createExcel{
public HSSFWorkbook createExcel(List<Student> list){
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("学生信息表");
//创建样式
//字体样式
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//表格样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
//指定单元格居中对齐
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//指定单元格垂直居中对齐
cellStyle.setWrapText(true);//指定单元格自动换行
cellStyle.setFont(font);
//创建表头
HSSFRow headerRow = sheet.createRow(0);
headerRow.setHeightInPoints(25f);//设置行高度
HSSFCell nameHeader = headerRow.createCell((short) 0);
nameHeader.setCellValue("学生");
nameHeader.setCellStyle(cellStyle);
HSSFCell sexHeader = headerRow.createCell((short) 1);
sexHeader.setCellValue("性别");
sexHeader.setCellStyle(cellStyle);
HSSFCell ageHeader = headerRow.createCell((short) 2);
ageHeader.setCellValue("年龄");
ageHeader.setCellStyle(cellStyle);
HSSFCell classHeader = headerRow.createCell((short) 3);
classHeader.setCellValue("班级");
classHeader.setCellStyle(cellStyle);
HSSFCell scoreHeader = headerRow.createCell((short) 4);
scoreHeader.setCellValue("成绩");
scoreHeader.setCellStyle(cellStyle);
for (int i = 0; i < list.size(); i++) {
HSSFRow row = sheet.createRow(i+1);
row.setHeightInPoints(20f);
Student student = list.get(i);
HSSFCell nameCell= row.createCell((short) 0);
nameCell.setCellValue(student.getName());
nameCell.setCellStyle(cellStyle);
HSSFCell sexCell= row.createCell((short) 1);
sexCell.setCellValue(student.getGender());
sexCell.setCellStyle(cellStyle);
HSSFCell ageCell= row.createCell((short) 2);
ageCell.setCellValue(student.getAge());
ageCell.setCellStyle(cellStyle);
HSSFCell classCell= row.createCell((short) 3);
classCell.setCellValue(student.getSclass());
classCell.setCellStyle(cellStyle);
HSSFCell scoreCell= row.createCell((short) 4);
scoreCell.setCellValue(student.getScore());
scoreCell.setCellStyle(cellStyle);
}
//合并单元格获得最后一行
sheet.getLastRowNum();
//分别代表起始行、起始列、结束行、结束列
sheet.addMergedRegion(new Region(1, (short)3, sheet.getLastRowNum(), (short)3));
return workbook;
}
}
效果如下