Java Apache POI组件操作Excel

最新推荐文章于 2024-05-17 11:22:12 发布

DaveBobo

最新推荐文章于 2024-05-17 11:22:12 发布

阅读量1.5k

点赞数

分类专栏： J2EE 文章标签： POI Excel

本文链接：https://blog.csdn.net/DaveBobo/article/details/55189318

版权

J2EE 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

有时一个软件应用程序需要生成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数据

Java Apache POI组件操作Excel 配套的源码实例下载

Reference:

http://poi.apache.org/download.html

http://www.yiibai.com/apache_poi/

http://www.cnblogs.com/hongten/p/java_poi_excel_xls_xlsx.html

http://www.cnblogs.com/hongten/archive/2012/02/29/java_jxl.html

http://www.cnblogs.com/wuxinrui/archive/2011/03/20/1989326.html

DaveBobo

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
Java Apache POI组件操作Excel

Reference:http://www.yiibai.com/apache_poi/http://www.cnblogs.com/hongten/p/java_poi_excel_xls_xlsx.htmlhttp://www.cnblogs.com/hongten/archive/2012/02/29/java_jxl.htmlhttp://www.cnblogs.com/wu
复制链接

扫一扫