参考:
http://www.cnblogs.com/zhenmingliu/archive/2012/04/25/2469396.html
http://blog.csdn.net/hantiannan/article/details/5312133
先来个实体类,模拟数据用的:
import java.util.Date;
/** * @author 作者 :I_T_T_I
* @date 创建时间:2016年7月27日 下午6:15:09
* */
public class Student {
private int id;
private String name;
private int age;
private Date birth;
public Student()
{
}
public Student(int id, String name, int age, Date birth)
{
this.id = id;
this.name = name;
this.age = age;
this.birth = birth;
}
public int getId()
{
return id;
}
public void setId(int id)
{
this.id = id;
}
public String getName()
{
return name;
}
public void setName(String name)
{
this.name = name;
}
public int getAge()
{
return age;
}
public void setAge(int age)
{
this.age = age;
}
public Date getBirth()
{
return birth;
}
public void setBirth(Date birth)
{
this.birth = birth;
}
}
/** * @author 作者 :I_T_T_I
* @date 创建时间:2016年7月27日 下午6:16:34
* */
import java.io.FileOutputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.CellStyle;
public class CreateExcel
{
/**
* @功能:手工构建一个简单格式的Excel
*/
private static List<Student> getStudent() throws Exception
{
List<Student> list = new ArrayList<Student>();
SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");
Student user1 = new Student(1, "张华", 10, df.parse("2006-07-12"));
Student user2 = new Student(2, "小红", 20, df.parse("1996-08-12"));
Student user3 = new Student(3, "小明", 30, df.parse("1986-11-12"));
list.add(user1);
list.add(user2);
list.add(user3);
return list;
}
@SuppressWarnings("deprecation")
public static void main(String[] args) throws Exception
{
//创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
//在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("学生表一");
//在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow((short) 1);
//设置第三列宽度(从0开始)
sheet.setColumnWidth(3, 40*256);
/**
* 设置首行样式
*/
HSSFRow rows = sheet.createRow((short) 0);
HSSFCell cells = rows.createCell(0);
// 单元格合并
// 四个参数分别是:起始行,起始列,结束行,结束列
sheet.addMergedRegion(new Region(0, (short) ( 0), 0,(short) (3)));
//设置高度
rows.setHeight((short) 1200);
//表格风格对象
CellStyle cellStyles = wb.createCellStyle();
//颜色
cellStyles.setFillForegroundColor(HSSFColor.GOLD.index);
//有上下边框,无左右
// cellStyles.setBorderBottom(HSSFCellStyle.BORDER_THICK);
// cellStyles.setBorderTop(HSSFCellStyle.BORDER_THICK);
cellStyles.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置单元格为水平对齐的类
//字体对象
HSSFFont fs = wb.createFont();
fs.setFontHeightInPoints( (short) 50);//字号
fs.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
// fs.setFontName("");//设置字体
// fs.setColor(IndexedColors.BLUE_GREY.getIndex());//字体颜色
cellStyles.setFont(fs);
cells.setCellValue("学生表");
cells.setCellStyle(cellStyles);
// 设置单元格样式
CellStyle cellStyle = wb.createCellStyle();
//颜色
cellStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
//填充模式
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//有上下边框,无左右
// cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THICK);
// cellStyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
// cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//字体对象
HSSFFont f = wb.createFont();
f.setFontHeightInPoints((short) 14);//字号
f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
cellStyle.setFont(f);
HSSFCell cell = row.createCell(0);
cell.setCellValue("学号");
cell.setCellStyle(cellStyle);
cell = row.createCell(1);
cell.setCellValue("姓名");
cell.setCellStyle(cellStyle);
cell = row.createCell(2);
cell.setCellValue("年龄");
cell.setCellStyle(cellStyle);
cell = row.createCell(3);
cell.setCellValue("生日");
cell.setCellStyle(cellStyle);
//写入实体数据 实际应用中这些数据从数据库得到,
List<Student> list = CreateSimpleExcelToDisk.getStudent();
HSSFFont ff = wb.createFont();
// f.setFontHeightInPoints((short) 11);//字号
ff.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
/**
* 设置编号的格式:c1
* 字体加粗,有边框
*/
CellStyle c1 = wb.createCellStyle();
// c1.setBorderBottom(HSSFCellStyle.BORDER_THICK);
// c1.setBorderLeft(HSSFCellStyle.BORDER_THICK);
// c1.setBorderRight(HSSFCellStyle.BORDER_THICK);
// c1.setBorderTop(HSSFCellStyle.BORDER_THICK);
c1.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
c1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
c1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
c1.setFont(ff);
/**
* 设置值的格式:c2
* 有边框
*/
CellStyle c2 = wb.createCellStyle();
// c2.setBorderBottom(HSSFCellStyle.BORDER_THICK);
// c2.setBorderLeft(HSSFCellStyle.BORDER_THICK);
// c2.setBorderRight(HSSFCellStyle.BORDER_THICK);
// c2.setBorderTop(HSSFCellStyle.BORDER_THICK);
//颜色
c2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
//填充模式
c2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
c2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
for (int i = 0; i < list.size(); i++)
{
row = sheet.createRow((int) i + 2);
Student stu = (Student) list.get(i);
// 创建单元格,并设置值
cell = row.createCell(0);
cell.setCellValue((double) stu.getId());
cell.setCellStyle(c1);
cell = row.createCell(1);
cell.setCellValue(stu.getName());
cell.setCellStyle(c2);
cell = row.createCell(2);
cell.setCellValue((double) stu.getAge());
cell.setCellStyle(c2);
cell = row.createCell(3);
cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(stu
.getBirth()));
cell.setCellStyle(c2);
// 奇偶行不同颜色
// i++;
// if(i < list.size()){
// row = sheet.createRow((int) i + 2);
// stu = (Student) list.get(i);
// // 创建单元格,并设置值
// cell = row.createCell(0);
// cell.setCellValue((double) stu.getId());
// cell.setCellStyle(c1);
//
// cell = row.createCell(1);
// cell.setCellValue(stu.getName());
// cell.setCellStyle(c2);
//
// cell = row.createCell(2);
// cell.setCellValue((double) stu.getAge());
// cell.setCellStyle(c2);
//
// cell = row.createCell(3);
// cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(stu
// .getBirth()));
// cell.setCellStyle(c2);
// }
}
// 将文件存到指定位置
try
{
Date dat = new Date();
DateFormat format = new SimpleDateFormat("yyyy-MM-dd_HHmmss");
String time = format.format(dat);
String path = "E:/students_"+time+".xls";
FileOutputStream fout = new FileOutputStream(path);
wb.write(fout);
fout.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
案例下载:
http://pan.baidu.com/s/1mhCD4b2 密码:4qr8