有时一个软件应用程序需要生成Microsoft Excel文件格式的报告。例如,一个公司开发的应用程序将财务部门需要所有输出生成自己的Excel。Apache POI是一种流行的API,它允许程序员使用Java程序创建,修改和显示MS Office文件。
Apache POI组件
Apache POI包含类和方法,来将MS Office所有OLE 2文档复合。此API组件的列表如下。
· POIFS (较差混淆技术实现文件系统) : 此组件是所有其他POI元件的基本因素。它被用来明确地读取不同的文件。
· HSSF (可怕的电子表格格式) : 它被用来读取和写入MS-Excel文件的xls格式。
· XSSF (XML格式) : 它是用于MS-Excel中XLSX文件格式。
· HPSF (可怕的属性设置格式) : 它用来提取MS-Office文件属性设置。
· HWPF (可怕的字处理器格式) : 它是用来读取和写入MS-Word的文档扩展名的文件。
· XWPF (XML字处理器格式) : 它是用来读取和写入MS-Word的docx扩展名的文件。
· HSLF (可怕的幻灯片版式格式) : 它是用于读取,创建和编辑PowerPoint演示文稿。
· HDGF (可怕的图表格式) : 它包含类和方法为MS-Visio的二进制文件。
· HPBF (可怕的出版商格式) : 它被用来读取和写入MS-Publisher文件。
本教程将指导使用Java Excel文件完成工作过程。因此,本教程仅限于HSSF和XSSF组件, 注:旧版本的POI支持二进制文件格式,如DOC,XLS,PPT等从版本3.5起,POI支持微软Office的OOXML文件格式,如DOCX,XLSX,PPTX等。如Apache POI,还有由不同的供应商为Excel文件的生成提供的其他库。这些措施包括Aspose面向Java的Aspose,JXL 通过共享库由JExcel团队开发。
新建工程Excel2013
Excel2013的项目结构
源码部分:
Common.java
package com.h264.common;
public class Common {
public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
public static final String OFFICE_EXCEL_2013_POSTFIX = "xlsx";
public static final String EMPTY = "";
public static final String POINT = ".";
public static final String Excel_PATH = "excelFiles";
public static final String STUDENT_INFO_XLS_PATH =Excel_PATH+"/student_info" + POINT + OFFICE_EXCEL_2003_POSTFIX;
public static final String STUDENT_INFO_XLSX_PATH =Excel_PATH+"/student_info" + POINT + OFFICE_EXCEL_2013_POSTFIX;
public static final String NOT_EXCEL_FILE = " : Not the Excel file!";
public static final String PROCESSING = "Processing...";
}
InsertExcel.java
package com.h264.excel;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.h264.excel.vo.Student;
public class InsertExcel {
public static void insert2Excel(List<Student> lstStu) throws Exception{
//获得总列数
int CountColumnNum = lstStu.size();
//创建Excel文档
XSSFWorkbook xwb = new XSSFWorkbook();
Student student = null;
//sheet对应一个工作页
XSSFSheet sheet = xwb.createSheet("abc");
XSSFRow firstrow = sheet.createRow(0);//下标为0的行开始
XSSFCell[] firstcell = new XSSFCell[CountColumnNum];
String[] names = new String[CountColumnNum];
names[0] = "学号";
names[1] = "姓名";
names[2] = "年龄";
names[3] = "成绩";
for (int j = 0; j < CountColumnNum; j++) {
firstcell[j] = firstrow.createCell(j);
firstcell[j].setCellValue(new XSSFRichTextString(names[j]));
}
for (int i = 0; i < lstStu.size(); i++) {
// 创建一行
XSSFRow row = sheet.createRow(i + 1);
// 得到要插入的每一条记录
student = lstStu.get(i);
for (int colu = 0; colu <= 3; colu++) {
// 在一行内循环
XSSFCell xNo = row.createCell(0);
xNo.setCellValue(student.getNo());
XSSFCell xName = row.createCell(1);
xName.setCellValue(student.getName());
XSSFCell xAge = row.createCell(2);
xAge.setCellValue(student.getAge());
XSSFCell xScore = row.createCell(3);
xScore.setCellValue(student.getScore());
}
}
//创建文件输出流,准备输出电子表格
OutputStream out = new FileOutputStream("excelFiles/abc.xlsx");
xwb.write(out);
out.close();
System.out.println("数据库导出成功");
}
}
package com.h264.excel;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.h264.excel.vo.Student;
public class InsertExcel {
public static void insert2Excel(List<Student> lstStu) throws Exception{
//获得总列数
int CountColumnNum = lstStu.size();
//创建Excel文档
XSSFWorkbook xwb = new XSSFWorkbook();
Student student = null;
//sheet对应一个工作页
XSSFSheet sheet = xwb.createSheet("abc");
XSSFRow firstrow = sheet.createRow(0);//下标为0的行开始
XSSFCell[] firstcell = new XSSFCell[CountColumnNum];
String[] names = new String[CountColumnNum];
names[0] = "学号";
names[1] = "姓名";
names[2] = "年龄";
names[3] = "成绩";
for (int j = 0; j < CountColumnNum; j++) {
firstcell[j] = firstrow.createCell(j);
firstcell[j].setCellValue(new XSSFRichTextString(names[j]));
}
for (int i = 0; i < lstStu.size(); i++) {
// 创建一行
XSSFRow row = sheet.createRow(i + 1);
// 得到要插入的每一条记录
student = lstStu.get(i);
for (int colu = 0; colu <= 3; colu++) {
// 在一行内循环
XSSFCell xNo = row.createCell(0);
xNo.setCellValue(student.getNo());
XSSFCell xName = row.createCell(1);
xName.setCellValue(student.getName());
XSSFCell xAge = row.createCell(2);
xAge.setCellValue(student.getAge());
XSSFCell xScore = row.createCell(3);
xScore.setCellValue(student.getScore());
}
}
//创建文件输出流,准备输出电子表格
OutputStream out = new FileOutputStream("excelFiles/abc.xlsx");
xwb.write(out);
out.close();
System.out.println("数据库导出成功");
}
}
ReadExcel.java
package com.h264.excel;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.h264.common.Common;
import com.h264.excel.util.Util;
import com.h264.excel.vo.Student;
public class ReadExcel {
/**
* read the Excel file
* @param path the path of the Excel file
* @return
* @throws IOException
*/
public List<Student> readExcel(String path) throws IOException{
if(path==null||Common.EMPTY.equals(path)){
return null;
}else{
String postfix = Util.getPostfix(path);
if(!Common.EMPTY.equals(postfix)){
if(Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){
return readXls(path);
}
if(Common.OFFICE_EXCEL_2013_POSTFIX.equals(postfix)){
return readXlsx(path);
}
}
}
return null;
}
/**
* Read the Excel 2003-2007
* @param path the path of the excel file
* @return
* @throws IOException
*/
public List<Student> readXls(String path) throws IOException{
System.out.println(Common.PROCESSING+path);
InputStream is = new FileInputStream(path);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
Student student = null;
List<Student> list = new ArrayList<Student>();
//Read the Sheet
for(int numSheet = 0;numSheet<hssfWorkbook.getNumberOfSheets();numSheet++){
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if(hssfSheet ==null){
continue;
}
//Read the Row
for(int rowNum = 1;rowNum<=hssfSheet.getLastRowNum();rowNum++){
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if(hssfRow!=null){
student = new Student();
HSSFCell no = hssfRow.getCell(0);
HSSFCell name = hssfRow.getCell(1);
HSSFCell age = hssfRow.getCell(2);
HSSFCell score = hssfRow.getCell(3);
student.setNo(getValue(no));
student.setName(getValue(name));
student.setAge(getValue(age));
student.setScore(Float.valueOf(getValue(score)));
list.add(student);
}
}
}
return list;
}
/**
* Read the Excel 2013
* @param path the path of the excel file
* @return
* @throws IOException
*/
public List<Student> readXlsx(String path) throws IOException{
System.out.println(Common.PROCESSING+path);
InputStream is = new FileInputStream(path);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
Student student = null;
List<Student> list = new ArrayList<Student>();
//Read the Sheet
for(int numSheet = 0;numSheet<xssfWorkbook.getNumberOfSheets();numSheet++){
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if(xssfSheet == null){
continue;
}
//Read the Row
for(int rowNum = 1;rowNum<=xssfSheet.getLastRowNum();rowNum++){
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if(xssfRow != null){
student = new Student();
XSSFCell no = xssfRow.getCell(0);
XSSFCell name = xssfRow.getCell(1);
XSSFCell age = xssfRow.getCell(2);
XSSFCell score = xssfRow.getCell(3);
student.setNo(getValue(no));
student.setName(getValue(name));
student.setAge(getValue(age));
student.setScore(Float.valueOf(getValue(score)));
list.add(student);
}
}
}
return list;
}
private String getValue(HSSFCell hssfCell){
if(hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN){
return String.valueOf(hssfCell.getBooleanCellValue());
}else if(hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC){
return String.valueOf(hssfCell.getNumericCellValue());
}else{
return String.valueOf(hssfCell.getStringCellValue());
}
}
private String getValue(XSSFCell xssfRow){
if(xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN){
return String.valueOf(xssfRow.getBooleanCellValue());
}else if(xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC){
return String.valueOf(xssfRow.getNumericCellValue());
}else{
return String.valueOf(xssfRow.getStringCellValue());
}
}
}
Client.java
package com.h264.excel.client;
import java.io.IOException;
import java.util.List;
import com.h264.common.Common;
import com.h264.excel.InsertExcel;
import com.h264.excel.ReadExcel;
import com.h264.excel.vo.Student;
public class Client {
/**
* @param args
*/
public static void main(String[] args) throws IOException{
// TODO Auto-generated method stub
String excel2003_2007 = Common.STUDENT_INFO_XLS_PATH;
String excel2013 = Common.STUDENT_INFO_XLSX_PATH;
/*
// read the 2003-2007 excel
List<Student> list = new ReadExcel().readExcel(excel2003_2007);
if (list != null) {
for (Student student : list) {
System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " + student.getScore());
}
}
System.out.println("======================================");
// read the 2013 excel
List<Student> list1 = new ReadExcel().readExcel(excel2013);
if (list1 != null) {
for (Student student : list1) {
System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " + student.getScore());
}
}
*/
InsertExcel ie = new InsertExcel();
Student student = null;
List<Student> list1 = new ReadExcel().readExcel(excel2013);
try {
ie.insert2Excel(list1);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Util.java
package com.h264.excel.util;
import com.h264.common.Common;
public class Util {
public static String getPostfix(String path){
if(path==null||Common.EMPTY.equals(path.trim())){
return Common.EMPTY;
}
if(path.contains(Common.POINT)){
return path.substring(path.lastIndexOf(Common.POINT)+1,path.length());
}
return Common.EMPTY;
}
}
Student.java
package com.h264.excel.vo;
public class Student {
/**
* id
*/
private Integer id;
/**
* 学号
*/
private String no;
/**
* 姓名
*/
private String name;
/**
* 年龄
*/
private String age;
/**
* 成绩
*/
private float score;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getNo() {
return no;
}
public void setNo(String no) {
this.no = no;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public float getScore() {
return score;
}
public void setScore(float score) {
this.score = score;
}
}
所用的excel数据
Reference: