利用poi插件操作Excel

我用过的操作Excel的插件有jxl和poi，其中jxl只对office 2007以前的版本提供支持，无法操作

office 2007及以后的版本。poi插件是apache 基金会的开源项目，对微软的office提供了很好的

支持。

下面以一组实例来说明poi如何操作Excel。
Constants.java

package com.utils;

/**
 * 程序中用到的一些常量
 * @author yangjianzhou
 *
 */
public class Constants {
	
	public static final String IN_FILE_PATH="WebRoot/readXLS";
	
	public static final String OUT_FILE_PATH="WebRoot/writeXLS";
	
	public static final String IN_FILE_PATH_MAX="WebRoot/readXLSMax";
	
	public static final String FILE_SUFFIX=".xls";

}

FileUtil.java

package com.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

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;

public class FileUtil {

	/**
	 * 获取指定路径下的所有excel文件名
	 * @param filePath
	 * @return
	 */
	public List<String> getAllFileName(String filePath){

		File directory = new File(filePath);
		File [] files = directory.listFiles();
		List<String> fileNameList = new ArrayList<String>();
		for(int i =0;i<files.length;i++){
			int index = files[i].getName().indexOf(Constants.FILE_SUFFIX);
			if(index>=0){
				fileNameList.add(filePath+"/"+files[i].getName());
			}
		}
		return fileNameList;
	}

	/**
	 * 得到指定文件名的文件
	 * @param fileNameList
	 * @return
	 */
	public List<File> getAllFiles(List<String> fileNameList){

		List<File> fileList = new ArrayList<File>();
		for(int i =0;i<fileNameList.size();i++){
			File file = new File(fileNameList.get(i));
			fileList.add(file);
		}
		return fileList;
	}

	/**
	 * 读取指定文件，经过一些处理，并将数据写入一个新文件
	 * @param file
	 * @param filePath
	 */
	public void readAndWriteFile(File file,String filePath) {
		
		 FileInputStream fileInputStream = null;
		try{
		     fileInputStream = new FileInputStream(file);
		     XSSFWorkbook rXSSWorkbook = new XSSFWorkbook(fileInputStream);
		     
		     int sheetNumber = rXSSWorkbook.getNumberOfSheets();
		     
		     for(int i=0;i<sheetNumber;i++){
		    	 XSSFSheet rXSSFSheet = rXSSWorkbook.getSheetAt(i);
		    	 String  sheetName = rXSSFSheet.getSheetName();
		    	 System.out.println(sheetName);
		    	 int rowNumber = rXSSFSheet.getPhysicalNumberOfRows();
		    	 XSSFRow rXSSFRow0 = rXSSFSheet.getRow(0);
		    	 
		    	 XSSFWorkbook wXSSFWorkbook = new XSSFWorkbook();
		    	 XSSFSheet wSheet = wXSSFWorkbook.createSheet();
		    	 XSSFRow wXSSFRow0 = wSheet.createRow(0);
		    	 XSSFCell wXSSFCell0 = wXSSFRow0.createCell(0);
		    	 XSSFCell wXSSFCell1 = wXSSFRow0.createCell(1);
		    	 XSSFCell wXSSFCell2 = wXSSFRow0.createCell(2);
		    	 XSSFCell wXSSFCell3 = wXSSFRow0.createCell(3);
		    	 wXSSFCell0.setCellValue(rXSSFRow0.getCell(0).getStringCellValue());
		    	 wXSSFCell1.setCellValue("最大");
		    	 wXSSFCell2.setCellValue("平均");
		    	 wXSSFCell3.setCellValue("最小");
		    	 
		    	 int columnNumber = rXSSFRow0.getPhysicalNumberOfCells();
		    	 int count = (columnNumber-4)/3;
		    	 System.out.println(count);
		    	 
		    	 for(int j=1;j<rowNumber;j++){
		    		 XSSFRow rXSSFRowN = rXSSFSheet.getRow(j);
		    		 for(int n=1;n<=count;n++){
		    			 XSSFRow wXSSFRowN = wSheet.createRow((j-1)*count+n);
		    			XSSFCell wXSSFCellN0 =  wXSSFRowN.createCell(0);
		    			XSSFCell wXSSFCellN1 =  wXSSFRowN.createCell(1);
		    			XSSFCell wXSSFCellN2 =  wXSSFRowN.createCell(2);
		    			XSSFCell wXSSFCellN3 =  wXSSFRowN.createCell(3);
		    			System.out.println(rXSSFRowN.getCell(0).getStringCellValue()+(n-1)*5+"分");
		    			wXSSFCellN0.setCellValue(rXSSFRowN.getCell(0).getStringCellValue()+(n-1)*5+"分");
		    			wXSSFCellN1.setCellValue(rXSSFRowN.getCell((n-1)*3+1).getNumericCellValue());
		    			wXSSFCellN2.setCellValue(rXSSFRowN.getCell((n-1)*3+2).getNumericCellValue());
		    			wXSSFCellN3.setCellValue(rXSSFRowN.getCell((n-1)*3+3).getNumericCellValue());
		    		 }
		    	 }
		    	 
		    	 String fileName = file.getName().substring(0, file.getName().length()-4)+"---"+sheetName+Constants.FILE_SUFFIX;
		    	 OutputStream outputStream = new FileOutputStream(new File(filePath+"/"+fileName));
		    	 wXSSFWorkbook.write(outputStream);
		    	 outputStream.close();
			}
		     fileInputStream.close();
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(fileInputStream!=null){
				try{
					fileInputStream.close();
				}catch (Exception e) {
					e.printStackTrace();
				}
			}
		}

	}
	
	public void readAndWriteFileMax(File file,String filePath){
		 FileInputStream fileInputStream = null;
			try{
			     fileInputStream = new FileInputStream(file);
			     XSSFWorkbook rXSSWorkbook = new XSSFWorkbook(fileInputStream);
			     
			     int sheetNumber = rXSSWorkbook.getNumberOfSheets();
			     
			     for(int i=0;i<sheetNumber;i++){
			    	 XSSFSheet rXSSFSheet = rXSSWorkbook.getSheetAt(i);
			    	 String  sheetName = rXSSFSheet.getSheetName();
			    	 int rowNumber = rXSSFSheet.getPhysicalNumberOfRows();
			    	 XSSFRow rXSSFRow0 = rXSSFSheet.getRow(0);
			    	 
			    	 XSSFWorkbook wXSSFWorkbook = new XSSFWorkbook();
			    	 XSSFSheet wSheet = wXSSFWorkbook.createSheet();
			    	 XSSFRow wXSSFRow0 = wSheet.createRow(0);
			    	 XSSFCell wXSSFCell0 = wXSSFRow0.createCell(0);
			    	 XSSFCell wXSSFCell1 = wXSSFRow0.createCell(1);
			    	 wXSSFCell0.setCellValue(rXSSFRow0.getCell(0).getStringCellValue());
			    	 wXSSFCell1.setCellValue("最大");
			    	 
			    	 int columnNumber = rXSSFRow0.getPhysicalNumberOfCells();
			    	 int count = columnNumber-4;
			    	 System.out.println(count);
			    	 
			    	 for(int j=1;j<rowNumber;j++){
			    		 XSSFRow rXSSFRowN = rXSSFSheet.getRow(j);
			    		 for(int n=1;n<=count;n++){
			    			XSSFRow wXSSFRowN = wSheet.createRow((j-1)*count+n);
			    			XSSFCell wXSSFCellN0 =  wXSSFRowN.createCell(0);
			    			XSSFCell wXSSFCellN1 =  wXSSFRowN.createCell(1);
			    			wXSSFCellN0.setCellValue(rXSSFRowN.getCell(0).getStringCellValue()+(n-1)*5+"分");
			    			wXSSFCellN1.setCellValue(rXSSFRowN.getCell(n).getNumericCellValue());
			    		 }
			    	 }
			    	 
			    	 String fileName = file.getName().substring(0, file.getName().length()-4)+"---"+sheetName+Constants.FILE_SUFFIX;
			    	 OutputStream outputStream = new FileOutputStream(new File(filePath+"/"+fileName));
			    	 wXSSFWorkbook.write(outputStream);
			    	 outputStream.close();
				}
			     fileInputStream.close();
			}catch (Exception e) {
				e.printStackTrace();
			}finally{
				if(fileInputStream!=null){
					try{
						fileInputStream.close();
					}catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
	}

}

Test.java

package com.test;

import java.io.File;
import java.io.IOException;
import java.util.List;

import com.utils.Constants;
import com.utils.FileUtil;

public class Test {
	
	public static void main(String[] args) throws IOException {
		
		FileUtil fu = new FileUtil();
		List<String> fileNameList  = fu.getAllFileName(Constants.IN_FILE_PATH);
		List<File> fileList = fu.getAllFiles(fileNameList);
		
		for(int i =0;i<fileList.size();i++){
			fu.readAndWriteFile(fileList.get(i), Constants.OUT_FILE_PATH);
		}
		
		List<String> fileNameListMax  = fu.getAllFileName(Constants.IN_FILE_PATH_MAX);
		List<File> fileListMax = fu.getAllFiles(fileNameListMax);
		
		for(int i =0;i<fileListMax.size();i++){
			fu.readAndWriteFileMax(fileListMax.get(i), Constants.OUT_FILE_PATH);
		}
		
	}
}

原始Excel文件



处理之后得到的Excel文件