HSSF----Horrible Spreadsheet Format  极讨厌的电子表格格式

package cn.itcast.poi;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
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;

public class ExcelPoi {
    /**
     *
     * @param file 文件的路径
     * @throws Exception
     */
    public void outExcel(File file) throws Exception{
        //创建一个输出流，要把生成的文件输出到那个地方
        FileOutputStream fos = new FileOutputStream(file);
        //生成一个表格对象
        HSSFWorkbook wb = new HSSFWorkbook();
        //每个文件必须包含一个工作表
        //创建一个工作表
        HSSFSheet sheet = wb.createSheet("first sheet");
        //创建行，参数说明的是第几行
        HSSFRow row = sheet.createRow(0);
        //创建单元格，参数说明的是第几个单元格
        HSSFCell cell = row.createCell(0);
        //设置单元格的内容
        cell.setCellValue("第一个值");
        row.createCell(1).setCellValue(false);
        row.createCell(2).setCellValue(2312313.55);
        //一下两行设置貌似没有问题实际上是有问题的
       
        row.createCell(3).setCellValue(Calendar.getInstance());
        row.createCell(4).setCellValue(new Date());
        //对数据进行格式化（style,风格，样式）
        //获得wb的日期格式对象
        HSSFDataFormat format = wb.createDataFormat();
        cell = row.getCell(2);

　　　//创建样式
        HSSFCellStyle style = wb.createCellStyle();
        style.setDataFormat(format.getFormat("#,###.00"));
        //把样式添加到单元格中
        cell.setCellStyle(style);
        //设置日期的格式
        format.getFormat("yyyy-MM-dd");
        style = wb.createCellStyle();
        style.setDataFormat(format.getFormat("yyyy-MM-dd hh:mm:ss"));
        row.getCell(3).setCellStyle(style);
        row.getCell(4).setCellStyle(style);
        
        //设置列宽
        sheet.setColumnWidth(1, 4000);
        sheet.setColumnWidth(2, 4000);
        sheet.setColumnWidth(3, 5000);
        sheet.setColumnWidth(4, 5000);
        
        //设置文本的对其方式--左上对其
        row = sheet.createRow(1);//第二行
        row.setHeightInPoints(100);//设置行的高
        cell = row.createCell(0);//第二行第1个单元格
        style = wb.createCellStyle();
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        cell.setCellValue("左上对其方式");
        cell.setCellStyle(style);       
        //设置文本的对齐方式：--居中
        cell = row.createCell(1);//第二行第2个单元格
        style = wb.createCellStyle();
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cell.setCellValue("居中对其方式");
        cell.setCellStyle(style);
        //设置文本的对齐方式：--右下
        //row = sheet.createRow(1);
        System.out.println(row);
        cell = row.createCell(2);
        System.out.println(cell);
        style = wb.createCellStyle();
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM);
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        cell.setCellValue("右下对其方式");
        cell.setCellStyle(style);

        //设置字体
        HSSFFont font = wb.createFont();
        font.setFontName("宋体");
        //font.setFontHeight((short)30);
        font.setColor(HSSFColor.GREEN.index);
        style = row.getCell(1).getCellStyle();
        style.setFont(font);
        //设置旋转
        style.setRotation((short)10);
        //设置自动列宽
        //**sheet.autoSizeColumn((short)1);可以使用的
        //设置自动换行,是单元格的属性
        row.getCell(2).getCellStyle().setWrapText(true);//这样它就自动换行了
        
        //设置边框的颜色
        row = sheet.createRow(2);
        cell = row.createCell(1);
        style = wb.createCellStyle();
        //左边框为红色
        style.setRightBorderColor(HSSFColor.RED.index);
        style.setBorderRight(HSSFCellStyle.BORDER_DOUBLE);
       
        style.setLeftBorderColor(HSSFColor.GREEN.index);
        style.setBorderLeft(HSSFCellStyle.BORDER_DASH_DOT_DOT);
        //设置粗细
        //style.setBorderRight(HSSFCellStyle.BORDER_THICK);
        cell.setCellStyle(style);
        
        //移动 从第几行到第几行，第三个值是-1.是向上移动，正是向下移动
        sheet.shiftRows(1, 2, 1);
       
        row = sheet.createRow(3);
        row.createCell(0);
        cell = row.getCell(0);
        cell.setCellValue(11);
        row.createCell(1);
        cell = row.getCell(1);
        cell.setCellValue(12);
        row.createCell(2);
        cell = row.getCell(2);
        cell.setCellValue(13);
        //取1,2,3的平局值
        cell = row.createCell(3);
        cell.setCellFormula("average(A4:C4)");
        //总和
        cell = row.createCell(4);
        cell.setCellFormula("sum(A4:C4)");
        //拆分窗格
        //1.上边距
        //2.下边距
        //3.
        //4.
        //5.
        //sheet.createSplitPane(1000, 2000, 3, 4, 3);
        //冻结窗格       
        sheet.createFreezePane(1, 1);
        //把以上设计好的对象写道输出流中去        
        wb.write(fos);
        //关闭流
        fos.close();
    }
    public static void main(String[] args) {
        ExcelPoi ep = new ExcelPoi();
        try {
            ep.outExcel(new File("F:/test.xls"));
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        List<Person> persons = new ArrayList<Person>();
        persons.add(new Person(1,"wangguang",false,24));
        persons.add(new Person(1,"王广",false,24));
        persons.add(new Person(1,"wa哈哈guang",false,24));
        persons.add(new Person(1,"wangguang",false,24));
        try {
            ep.createExcel(persons);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }
    public void createExcel(List<Person> persons) throws Exception {
        String[] titles = {"ID","姓名","婚否","年龄"};
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        HSSFRow row = sheet.createRow(0);
        for(int i=0;i<titles.length;i++) {
            row.createCell(i).setCellValue(titles[i]);
        }
        int i = 1;
        for(Person person:persons) {
            row = sheet.createRow(i++);
            row.createCell(0).setCellValue(person.getId());
            row.createCell(1).setCellValue(person.getName());
            row.createCell(2).setCellValue(person.isMarr());
            row.createCell(3).setCellValue(person.getAge());
        }
        FileOutputStream fos = new FileOutputStream("d:/person.xls");
        wb.write(fos);
        fos.close();
        System.out.println("-------------------------------");
    }
}
class Person {
    public Integer id;
    public String name;
    public boolean marr;
    public int age;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public boolean isMarr() {
        return marr;
    }
    public void setMarr(boolean marr) {
        this.marr = marr;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public Person() {
       
    }
    public Person(Integer id, String name, boolean marr, int age) {
        super();
        this.id = id;
        this.name = name;
        this.marr = marr;
        this.age = age;
    }
   
}