1-测试入门 新建excel
package dream.poi.test;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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;
public class NewPOI {
/** Excel 文件要存放的位置,假定在E盘下*/
public static String outputFile = "E:\\POI\\poi.xls";
public static void main(String[] args) {
// TODO Auto-generated method stub
try {
// 创建新的Excel 工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 在Excel工作簿中建一工作表,其名为缺省值
// 如要新建一名为"效益指标"的工作表,其语句为:
/* HSSFSheet sheet = workbook.createSheet("效益指标");*/
HSSFSheet sheet = workbook.createSheet();
// 在索引0的位置创建行(最顶端的行)
HSSFRow row = sheet.createRow((short)0);
//创建字体 红色 加粗
HSSFFont font = workbook.createFont();
font.setColor(HSSFFont.COLOR_RED);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//创建格式
HSSFCellStyle cellStyle= workbook.createCellStyle();
cellStyle.setFont(font);
//在索引0的位置创建单元格(左上端)
HSSFCell cell = row.createCell((short)0);
//应用单元格样式
cell.setCellStyle(cellStyle);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 在单元格中输入一些内容
cell.setCellValue("测试");
// 新建一输出文件流
FileOutputStream fOut = new FileOutputStream(outputFile);
// 把相应的Excel 工作簿存盘
workbook.write(fOut);
fOut.flush();
// 操作结束,关闭文件
fOut.close();
System.out.println("文件生成...");
} catch (Exception e) {
System.out.println("已运行 xlCreate() : " + e);
}
}
}
2.读取excel
package dream.poi.test;
import java.io.FileInputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ReadPOI {
/** Excel文件的存放位置。注意是反斜线*/
public static String fileToBeRead = "E:\\POI\\poi.xls";
public static void main(String args[]) {
try {
// 创建对Excel工作簿文件的引用
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead));
// 创建对工作表的引用。
// 本例是按名引用(让我们假定那张表有着缺省名"Sheet")
HSSFSheet sheet = workbook.getSheet("Sheet0");
// 也可用getSheetAt(int index)按索引引用,
// 在Excel文档中,第一张工作表的缺省索引是0,
// 其语句为:HSSFSheet sheet = workbook.getSheetAt(0);
// 读取左上端单元
HSSFRow row = sheet.getRow(0);
HSSFCell cell = row.getCell((short)0);
// 输出单元内容,cell.getStringCellValue()就是取所在单元的值
System.out.println("左上端单元是: " + cell.getStringCellValue());
} catch (Exception e) {
System.out.println("已运行xlRead() : " + e);
}
}
}
1-POI入门<span style="font-size:18px;">package dream.poi.demo;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
public class ExportPOI {
public static void main(String[] args) {
// TODO Auto-generated method stub
//创建一个student对象的集合 模拟数据
List<Student> studentList = new ArrayList<Student>();
//循环模拟赋值
for(int i=0;i<5;i++){
//创建一个student对象
Student student = new Student();
//添加数据
student.setId(i);
student.setName("我叫POI"+i);
student.setSex("男");
student.setAge(i);
student.setStartTime(new Date());
//加入集合
studentList.add(student);
}
//创建excel工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一个工作表 命名为poi
HSSFSheet poiSheet = workbook.createSheet("poi");
//设置列宽
poiSheet.setColumnWidth(4, 20*256);
//创建标题行
HSSFRow row = poiSheet.createRow(0);
//首行单元格
row.createCell(0).setCellValue("ID");
row.createCell(1).setCellValue("姓名");
row.createCell(2).setCellValue("性别");
row.createCell(3).setCellValue("年龄");
row.createCell(4).setCellValue("入学时间");
//定义i行
int i=1;
// 格式化时间
CreationHelper helper = workbook.getCreationHelper();
CellStyle style = workbook.createCellStyle();
style.setDataFormat(helper.createDataFormat().getFormat("yyyy-MM-dd hh:mm:ss"));
for (Student student : studentList) {
//创建行
HSSFRow createRow = poiSheet.createRow((short)i);
//加载数据
createRow.createCell(0).setCellValue(student.getId());
createRow.createCell(1).setCellValue(student.getName());
createRow.createCell(2).setCellValue(student.getSex());
createRow.createCell(3).setCellValue(student.getAge());
HSSFCell createCell4 = createRow.createCell(4);
createCell4.setCellStyle(style);
createCell4.setCellValue(student.getStartTime());
//变量加1
i++;
}
try {
//文件输出流
FileOutputStream fileOutputStream = new FileOutputStream("E:\\POI\\student.xls");
//输出excel
workbook.write(fileOutputStream);
fileOutputStream.flush();
//关闭输出流
fileOutputStream.close();
System.out.println("文件已保存");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}</span><pre name="code" class="java">
2-导入
<span style="font-size:18px;">package dream.poi.demo;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ImportPOI {
public static void main(String[] args) throws IOException {
//创建excel工作簿引用
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream("E:\\POI\\student.xls"));
//获取工作表
HSSFSheet poiSheet = workbook.getSheet("poi");
//创建一个student对象的集合 模拟数据
List<Student> studentList = new ArrayList<Student>();
//循环取值
for(int i=1;i<=5;i++){
//创建student对象
Student student=new Student();
//得到工作表,第i行
HSSFRow row = poiSheet.getRow((short)i);
//获取第一个值
int id = (int) row.getCell(0).getNumericCellValue();
student.setId(id);
HSSFCell name = row.getCell(1);
student.setName(name.toString());
HSSFCell sex = row.getCell(2);
student.setSex(sex.toString());
int age = (int) row.getCell(3).getNumericCellValue();
student.setAge(age);
//获取日期类型
Date startTime = row.getCell(4).getDateCellValue();
student.setStartTime(startTime);
//集合中添加对象
studentList.add(student);
}
//输出
System.out.println(studentList);
}
}</span>
3-entity
package dream.poi.demo;
import java.util.Date;
public class Student {
//编号
private Integer id;
//姓名
private String name;
//性别
private String sex;
//年龄
private Integer age;
//时间
private Date startTime;
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 String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Date getStartTime() {
return startTime;
}
public void setStartTime(Date startTime) {
this.startTime = startTime;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", sex=" + sex + ", age=" + age + ", startTime=" + startTime
+ "]";
}
}