package com.robert.util;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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.HSSFColor;
import com.robert.bean.Student;
public class ExcelUtil {
public HSSFWorkbook createExcel(ArrayList<Student> students) {
HSSFWorkbook wb = new HSSFWorkbook();
// create a new sheet
HSSFSheet s = wb.createSheet("workbook.xls");
// declare a row object reference
HSSFRow r = null;
// declare a cell object reference
HSSFCell c = null;
// create 2 cell styles
HSSFCellStyle cs = wb.createCellStyle();
HSSFCellStyle cs2 = wb.createCellStyle();
HSSFDataFormat df = wb.createDataFormat();
// create 2 fonts objects
HSSFFont f = wb.createFont();
HSSFFont f2 = wb.createFont();
// Set font 1 to 12 point type, blue and bold
f.setFontHeightInPoints((short) 12);
f.setColor(HSSFColor.RED.index);
f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// Set font 2 to 10 point type, red and bold
f2.setFontHeightInPoints((short) 10);
f2.setColor(HSSFColor.RED.index);
f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// Set cell style and formatting
cs.setFont(f);
cs.setDataFormat(df.getFormat("#,##0.0"));
// Set the other cell style and formatting
cs2.setBorderBottom(cs2.BORDER_THIN);
cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
cs2.setFont(f2);
// Define a few rows
for(short rownum = 0; rownum < students.size(); rownum++) {
HSSFRow hssf = s.createRow(rownum);
Student st = students.get(rownum);
for(int i = 0;i<3;i++){
HSSFCell c1 = hssf.createCell(1);
HSSFCell c2 = hssf.createCell(2);
HSSFCell c3 = hssf.createCell(3);
c1.setCellValue(st.getId());
c2.setCellValue(st.getName());
c3.setCellValue(st.getBrithday());
}
}
// Save
FileOutputStream out;
try {
out = new FileOutputStream("E:\\tomcat6\\webapps\\webproject\\temp\\workbook.xls");
wb.write(out);
out.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return wb;
}
}