利用POI创建Excel文件
最近自学了利用POI创建Excel文件，并成功的写出了XLS文件，现分享出来，大家共同学习。
在eclipse的集成开发环境中，将下载好的POI中的jar包导入到指定的java工程项目中。一切准备就绪，即可开始写代码了。
我要完成创建的文件内容以下截图给出：
方法如下：
1,创建Data类，以保存从第四行起每行的数据。
package com.liu.p1;
public class Data
{
//num代表每项内容的序号，p1~p12代表每行十二个数据项
private int num;
private String p1;
private String p2;
private String p3;
private int p4;
private int p5;
private double p6;
private int p7;
private int p8;
private double p9;
private String p10;
private int p11;
private int p12;
public Data(int num,String p1, String p2, String p3, int p4, int p5, double p6,
int p7, int p8, double p9, String p10, int p11, int p12)
{
this.num=num;
this.p1 = p1;
this.p2 = p2;
this.p3 = p3;
this.p4 = p4;
this.p5 = p5;
this.p6 = p6;
this.p7 = p7;
this.p8 = p8;
this.p9 = p9;
this.p10 = p10;
this.p11 = p11;
this.p12 = p12;
}
public int getNum()
{
return num;
}
public String getP1()
{
return p1;
}
public String getP2()
{
return p2;
}
public String getP3()
{
return p3;
}
public int getP4()
{
return p4;
}
public int getP5()
{
return p5;
}
public double getP6()
{
return p6;
}
public int getP7()
{
return p7;
}
public int getP8()
{
return p8;
}
public double getP9()
{
return p9;
}
public String getP10()
{
return p10;
}
public int getP11()
{
return p11;
}
public int getP12()
{
return p12;
}
}
2，创建CreExcel 类，主方法实现文件创建。
public class CreExcel
{
public static void main(String[] args)
{
/** Excel 文件要存放的位置，假定在当前目录下*/
String outputFile="test.xls";
FileOutputStream fos=null;
try
{
// 创建新的Excel 工作簿
HSSFWorkbook workbook=new HSSFWorkbook();
// 如要新建一名为"效益指标"的工作表，其语句为：
HSSFSheet sheet =workbook.createSheet("通报表");
HSSFDataFormat format = workbook.createDataFormat();
HSSFCellStyle cellStyle = workbook.createCellStyle();
HSSFCellStyle cellStyle1 = workbook.createCellStyle();
HSSFCellStyle cellStyle2 = workbook.createCellStyle();
HSSFCellStyle cellStyle3 = workbook.createCellStyle();
HSSFCellStyle cellStyle4 = workbook.createCellStyle();
HSSFCellStyle cellStyle5 = workbook.createCellStyle();
HSSFCellStyle cellStyle6 = workbook.createCellStyle();
//为cellStyle添加格式
//设置背景色
// cellStyle.setFillBackgroundColor((short)15);
// cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//设置边框
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//设置居中
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 左右居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
//设置字体0
HSSFFont font=workbook.createFont();
font.setColor(HSSFFont.COLOR_RED);
font.setFontName("楷体");
font.setFontHeightInPoints((short) 13);//设置字体大小
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//设置加粗
//选择需要用到的字体格式
cellStyle.setFont(font);
//设置自动换行
cellStyle.setWrapText(true);
//为cellStyle1添加格式
//指定日期格式
cellStyle1.setDataFormat(format.getFormat("yyyy年mm月dd日"));
//设置边框
cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle2.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);//上边框
cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//设置字体1
HSSFFont font1=workbook.createFont();
font1.setColor(HSSFFont.COLOR_NORMAL);
font1.setFontName("楷体");
font1.setFontHeightInPoints((short) 10);//设置字体大小
font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//设置加粗
//选择需要用到的字体格式
cellStyle1.setFont(font1);
//水平左置
cellStyle1.setAlignment(HSSFCellStyle.ALIGN_LEFT);
//设置自动换行
cellStyle1.setWrapText(true);
//为cellStyle2添加格式
//设置前景色
cellStyle2.setFillForegroundColor(HSSFColor.WHITE.index);
//设置背景色
cellStyle2.setFillBackgroundColor(HSSFColor.BLUE.index);
cellStyle2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//设置边框
cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle2.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);//上边框
cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//设置居中
cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 左右居中
cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
//设置字体2
HSSFFont font2=workbook.createFont();
font2.setColor(HSSFFont.COLOR_NORMAL);
font2.setFontName("楷体");
font2.setFontHeightInPoints((short) 10);//设置字体大小
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//设置不加粗
//选择需要用到的字体格式
cellStyle2.setFont(font2);
//设置自动换行
cellStyle2.setWrapText(true);
//为cellStyle3添加格式
//设置前景色
cellStyle3.setFillForegroundColor(HSSFColor.WHITE.index);
//设置背景色
cellStyle3.setFillBackgroundColor(HSSFColor.BLUE.index);
cellStyle3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//设置边框
cellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle3.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle3.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//设置居中
cellStyle3.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 左右居中
cellStyle3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
//选择需要用到的字体格式
cellStyle3.setFont(font2);
//设置自动换行
cellStyle3.setWrapText(true);
//为cellStyle4添加格式
//设置前景色
cellStyle4.setFillForegroundColor(HSSFColor.LIME.index);
//设置背景色
cellStyle4.setFillBackgroundColor(HSSFColor.BLUE.index);
cellStyle4.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//设置边框
cellStyle4.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle4.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle4.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle4.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//设置居中
cellStyle4.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 左右居中
cellStyle4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
//选择需要用到的字体格式
cellStyle4.setFont(font2);
//设置自动换行
cellStyle4.setWrapText(true);
//为cellStyle5添加格式
//设置前景色
cellStyle5.setFillForegroundColor(HSSFColor.GREEN.index);
//设置背景色
cellStyle5.setFillBackgroundColor(HSSFColor.BLUE.index);
cellStyle5.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//设置边框
cellStyle5.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle5.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle5.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle5.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//设置居中
cellStyle5.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 左右居中
cellStyle5.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
//选择需要用到的字体格式
cellStyle5.setFont(font2);
//设置自动换行
cellStyle5.setWrapText(true);
//添加文档内容
//添加标题，并使用cellStyle格式
HSSFCell cell=getPosition(workbook,sheet,0,0);
cell.setCellValue("中華電信網路容量及使用現況雙週報表");
cell.setCellStyle(cellStyle);
//添加日期，并使用cellStyle1格式
HSSFCell cell1=getPosition(workbook,sheet,1,10);
cell1.setCellValue(new Date());
cell1.setCellStyle(cellStyle1);
//添加第三行内容
HSSFCell[] cells0=new HSSFCell[13];
for(int i=0;i<13;i++)
{
cells0[i]=getPosition(workbook,sheet,2,i);
cells0[i].setCellStyle(cellStyle2);
}
cells0[1].setCellValue("项目");
cells0[2].setCellValue("单位");
cells0[3].setCellValue("分公司");
cells0[4].setCellValue("至90年12月累計數");
cells0[5].setCellValue("91年預定建設數");
cells0[6].setCellValue("建設執行現況(%)");
cells0[7].setCellValue("91年當月累計設備總量");
cells0[8].setCellValue("當週累計使用總量");
cells0[9].setCellValue("使用率%");
cells0[10].setCellValue("備註");
cells0[11].setCellValue("91實際累計建設量");
cells0[12].setCellValue("累計淨增");
//创建容器，存储每行的数据
ArrayList<Data> list=new ArrayList();
//获取数据对象，添加到list容器中
list.add(new Data(1,"市話交換機門號數(POTS)","門","北分",8253465,1137000,0.2184,8463465,6723911,7.7945,"固网",248300,91300));
list.add(new Data(1,"市話交換機門號數(POTS)","門","北分",8253465,1137000,0.2184,8463465,6723911,7.7945,"固网",248300,91300));
list.add(new Data(1,"市話交換機門號數(POTS)","門","北分",8253465,1137000,0.2184,8463465,6723911,7.7945,"固网",248300,91300));
list.add(new Data(2,"市話交換機門號數(POTS)","門","北分",8253465,1137000,0.2184,8463465,6723911,7.7945,"固网",248300,91300));
list.add(new Data(2,"市話交換機門號數(POTS)","門","北分",8253465,1137000,0.2184,8463465,6723911,7.7945,"固网",248300,91300));
list.add(new Data(2,"市話交換機門號數(POTS)","門","北分",8253465,1137000,0.2184,8463465,6723911,7.7945,"固网",248300,91300));
list.add(new Data(3,"市話交換機門號數(POTS)","門","北分",8253465,1137000,0.2184,8463465,6723911,7.7945,"固网",248300,91300));
list.add(new Data(3,"市話交換機門號數(POTS)","門","北分",8253465,1137000,0.2184,8463465,6723911,7.7945,"固网",248300,91300));
list.add(new Data(3,"市話交換機門號數(POTS)","門","北分",8253465,1137000,0.2184,8463465,6723911,7.7945,"固网",248300,91300));
list.add(new Data(4,"市話交換機門號數(POTS)","門","北分",8253465,1137000,0.2184,8463465,6723911,7.7945,"固网",248300,91300));
list.add(new Data(4,"市話交換機門號數(POTS)","門","北分",8253465,1137000,0.2184,8463465,6723911,7.7945,"固网",248300,91300));
list.add(new Data(4,"市話交換機門號數(POTS)","門","北分",8253465,1137000,0.2184,8463465,6723911,7.7945,"固网",248300,91300));
list.add(new Data(5,"市話交換機門號數(POTS)","門","北分",8253465,1137000,0.2184,8463465,6723911,7.7945,"固网",248300,91300));
list.add(new Data(6,"市話交換機門號數(POTS)","門","北分",8253465,1137000,0.2184,8463465,6723911,7.7945,"固网",248300,91300));
list.add(new Data(6,"市話交換機門號數(POTS)","門","北分",8253465,1137000,0.2184,8463465,6723911,7.7945,"固网",248300,91300));
list.add(new Data(7,"市話交換機門號數(POTS)","門","北分",8253465,1137000,0.2184,8463465,6723911,7.7945,"固网",248300,91300));
list.add(new Data(7,"市話交換機門號數(POTS)","門","北分",8253465,1137000,0.2184,8463465,6723911,7.7945,"固网",248300,91300));
list.add(new Data(7,"市話交換機門號數(POTS)","門","北分",8253465,1137000,0.2184,8463465,6723911,7.7945,"固网",248300,91300));
list.add(new Data(8,"市話交換機門號數(POTS)","門","北分",8253465,1137000,0.2184,8463465,6723911,7.7945,"固网",248300,91300));
list.add(new Data(9,"市話交換機門號數(POTS)","門","北分",8253465,1137000,0.2184,8463465,6723911,7.7945,"固网",248300,91300));
list.add(new Data(10,"市話交換機門號數(POTS)","門","北分",8253465,1137000,0.2184,8463465,6723911,7.7945,"固网",248300,91300));
//将所有行的数据录入 依照第四行的思路
//获得所有的Data对象
for(int x=3;x<list.size()+3;x++)
{
//创建cells数组，用来存储第x+1行单元格
HSSFCell[] cells=new HSSFCell[13];
//为此数组单元格分配位置
for(int i=0;i<13;i++)
{
cells[i]=getPosition(workbook,sheet,x,i);
if((i>2&&i<10||i>10)&&(x==3||x==6||x==9||x==12||x==18))
{
cells[i].setCellStyle(cellStyle4);
}
else if((i>=0&&i<10)&&(x==15||x==21||x==22))
{
cells[i].setCellStyle(cellStyle5);
}
else
cells[i].setCellStyle(cellStyle3);
}
//从list容器里获取第x+1行数据
Data data=list.get(x-3);
//为每个单元格添加内容，并指定格式
cells[0].setCellValue(data.getNum());
cells[1].setCellValue(data.getP1());
cells[2].setCellValue(data.getP2());
cells[3].setCellValue(data.getP3());
cells[4].setCellValue(data.getP4());
cells[5].setCellValue(data.getP5());
cells[6].setCellValue(data.getP6());
cells[7].setCellValue(data.getP7());
cells[8].setCellValue(data.getP8());
cells[9].setCellValue(data.getP9());
cells[10].setCellValue(data.getP10());
cells[11].setCellValue(data.getP11());
cells[12].setCellValue(data.getP12());
}
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(0,0,0,10));
for(int i=0;i<3;i++)
{
sheet.addMergedRegion(new CellRangeAddress(3,5,i,i));
sheet.addMergedRegion(new CellRangeAddress(6,8,i,i));
sheet.addMergedRegion(new CellRangeAddress(9,11,i,i));
sheet.addMergedRegion(new CellRangeAddress(12,14,i,i));
}
sheet.addMergedRegion(new CellRangeAddress(3,14,10,10));
sheet.addMergedRegion(new CellRangeAddress(16,17,0,0));
sheet.addMergedRegion(new CellRangeAddress(16,17,10,10));
sheet.addMergedRegion(new CellRangeAddress(18,20,0,0));
sheet.addMergedRegion(new CellRangeAddress(18,20,1,1));
sheet.addMergedRegion(new CellRangeAddress(18,20,10,10));
//设置列宽:
/*
*一个参数代表列id(从0开始),
* 第2个参数代表宽度值
* 参考 ："2012-08-10"的宽度为2500
*/
sheet.setColumnWidth(0, 800);
sheet.setColumnWidth(1, 6000);
sheet.setColumnWidth(2, 1000);
for(int i=3;i<13;i++)
{
if(i==10)
sheet.setColumnWidth(i, 9000);
else
sheet.setColumnWidth(i, 3000);
}
// 新建一输出文件流
fos=new FileOutputStream(new File(outputFile));
// 把相应的Excel 工作簿存盘
workbook.write(fos);
fos.flush();
}
catch(Exception e)
{
}
finally
{
try
{
// 操作结束，关闭文件
fos.close();
}
catch (IOException e)
{
e.printStackTrace();
}
}
}
public static HSSFCell getPosition(HSSFWorkbook workbook,HSSFSheet sheet,int row,int col)
{
//在索引row的位置创建行
HSSFRow r=sheet.createRow(row);
//在索引col的位置创建单元
HSSFCell cell=r.createCell(col);
return cell;
}
}运行以上代码，即可获得截图所示文件！
查看13道真题和解析
