package com.huawei.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.util.Date;

import org.apache.catalina.startup.SetContextPropertiesRule;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class TestExcel02 {

private Workbook wb = null;
private String path = "F:/test.xls";

//在所有的Test方法执行之前调用
@Before
public void createWorkBook(){
this.wb = new HSSFWorkbook();
}

//在所有的test方法执行之后调用
@After
public void writeWorkBook() throws Exception{
FileOutputStream out = new FileOutputStream(new File(this.path));
this.wb.write(out);
out.flush();
out.close();
this.wb.close();
}
//测试生成第一个工作簿
@Test
public void createFisrtWorkBook(){
this.path = "F:/first.xls";
}
//测试生成第一个 单元格
@Test
public void createCell(){
this.path = "F:/test_cell.xls";
Sheet sheet = this.wb.createSheet("First");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("this is a first");
}
//创建一个日期类型的单元格
@Test
public void createDateCell(){
Sheet sheet = this.wb.createSheet("日期");
//得到一个CreationHelper 帮助器
CreationHelper helper = this.wb.getCreationHelper();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
//创建单元格样式
CellStyle style = this.wb.createCellStyle();
//设置日期的格式话
style.setDataFormat(helper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));

cell.setCellValue(new Date());
cell.setCellStyle(style);
}
@Test
public void createWorkBookOfUsersInfo(){

String[][] data = new String[][]{
{
"123456789",
"李四",
"lisi@lisi.com",
"20",
"男"
},{
"2",
"李四2",
"lisi2@lisi.com",
"30",
"女"
},{
"3",
"李四3",
"lisi3@lisi.com",
"22",
"男"
},{
"4",
"李四4",
"lisi4@lisi.com",
"24",
"男"
},{
"5",
"李四5",
"lisi5@lisi.com",
"35",
"女"
}
};

String []headers = new String[]{"ID","用户名","邮箱","年龄","性别"};

//创建工作表
Sheet sheet = this.wb.createSheet("用户信息");

sheet.setColumnWidth(0, 256*8);

//创建title
Row title = sheet.createRow(0);
//创建单元格样式
CellStyle tStyle = this.wb.createCellStyle();
//设置水平居中
tStyle.setAlignment(CellStyle.ALIGN_CENTER);
tStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

title.setHeight((short)(40*20));

Cell tCell = title.createCell(0);
tCell.setCellValue("用户信息表");
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(0,0,0,headers.length-1));
tCell.setCellStyle(tStyle);
//设置表头
Row header = sheet.createRow(1);
for(int i=0;i<headers.length;i++){
Cell cell = header.createCell(i);
cell.setCellValue(headers[i]);
}

for(int i=0;i<data.length;i++){
//创建行
Row row = sheet.createRow(i+2);
for(int j=0;j<data[i].length;j++){
//创建单元格
Cell cell = row.createCell(j);
//设置数据
cell.setCellValue(data[i][j]);
}

}

this.path = "F:/users.xls";

}
}