HSSF----Horrible Spreadsheet Format 极讨厌的电子表格格式
| 序号 | 对象 | 方法 | 备注 |
| 1 | HSSFWorkbook |
HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("first sheet"); HSSFDataFormat format = wb.createDataFormat(); HSSFCellStyle style = wb.createCellStyle(); HSSFFont font = wb.createFont(); wb.write(fos); |
//创建一个工作表 //获得wb的日期格式对象 //创建样式 //设置字体 //把以上设计好的对象写道输出流中去 |
| 2 | HSSFSheet |
HSSFRow row = sheet.createRow(0); sheet.setColumnWidth(1, 4000); sheet.createFreezePane(1, 1); sheet.autoSizeColumn((short)1); |
//创建行，参数说明的是第几行 //设置列宽 //冻结窗格 //设置自动列宽 |
| 3 | HSSFRow |
HSSFCell cell = row.createCell(0); cell = row.getCell(2); |
//创建单元格，参数说明的是第几个单元格 //获取单元格 |
| 4 | HSSFCell |
cell.setCellValue(13); cell.setCellFormula("average(A4:C4)"); cell.setCellFormula("sum(A4:C4)"); |
//设置单元格内容 //取1,2,3的平局值 //总和 |
| 5 | HSSFCellStyle |
style.setDataFormat(format.getFormat("#,###.00")); style.setDataFormat(format.getFormat("yyyy-MM-dd hh:mm:ss")); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM); style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); style.setFont(font); style.setRotation((short)10); style.setWrapText(true); |
//设置单元格文本内容样式 //设置单元格文本内容样式 //设置单元格文本对其方式 //设置单元格文本对其方式 左上对其 //设置单元格文本对其方式 //设置单元格文本对其方式 居中对其 //设置单元格文本对其方式 //设置单元格文本对其方式 右下对其 //设置单元格文本颜色 //设置旋转
//设置自动换行 |
| 6 | HSSFFont |
font.setFontName("宋体"); font.setFontHeight((short)30); font.setColor(HSSFColor.GREEN.index); |
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;
}
}
