POI操作Excel（支持03和07+）

以前项目中用过Excel导入导出过2003版本的，最近研究了一下能够兼容07+ 版本的操作方法。

首先下载POI的jar包，官方网站: http://jakarta.apache.org/poi/，在项目中导入jar包，如下：

 

 

 

 

 

 

 

 

 

 

 

 

 

下面直接上代码

 

1.创建Workbook工厂类

package com.songhn.poi.util;

import java.io.File;
import java.io.FileInputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author songhn
 *
 * 创建文档
 *
 */
public class WorkbookFactory {

	private Workbook workBook = null;
	private String excelFilePath = null;
	
	/**
	 * 创建文档WorkBook(读文档)
	 * @param excelFilePath
	 * @return
	 * @throws Exception
	 */
	public Workbook create(String excelFilePath) throws Exception{
		
		this.excelFilePath = excelFilePath;
		File file = new File(this.excelFilePath);
		FileInputStream fis = new FileInputStream(file);
		
		//2003版的Excel
		if(excelFilePath.endsWith(".xls")){
			
			workBook = new HSSFWorkbook(fis);
			
		//2007版以后的Excel	
		}else if(excelFilePath.endsWith(".xlsx")){
			
			workBook = new XSSFWorkbook(fis);
			
		}
		
		return workBook;
		
	}
	
	/**
	 * 创建文档WorkBook(写文档)
	 * @param excelFilePath
	 * @return
	 * @throws Exception
	 */
	public Workbook create(String fileName , boolean flag) throws Exception{
		
		this.excelFilePath = fileName;
		
		//2003版的Excel
		if(excelFilePath.endsWith(".xls")){
			
			workBook = new HSSFWorkbook();
			
		//2007版以后的Excel	
		}else if(excelFilePath.endsWith(".xlsx")){
			
			workBook = new XSSFWorkbook();
			
		}
		
		return workBook;
		
	}
	
}

 2.导入Excel文档

/**
 * 
 */
package com.songhn.poi.util;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

/**
 * @author songhn
 * 
 *         读取Excel工具类(兼容2003以及以后的版本Excel)
 * 
 */
public class ExcelReadUtil {

	/**
	 * 读取每一个Sheet的数据
	 * 
	 * @param workBook
	 * @return
	 */
	public Map<String, Object> readSheet(Workbook workBook) {

		Map<String, Object> sheetMap = new HashMap<>();
		int _sheetNUM = workBook.getNumberOfSheets();
		for (int i = 0; i < _sheetNUM; i++) {
			Sheet sheet = workBook.getSheetAt(i);
			String sheetName = sheet.getSheetName();
			List<String[]> list = null;
			if (null != sheet) {
				list = readRow(sheet);
			}
			sheetMap.put(sheetName, list);
		}

		return sheetMap;

	}

	/**
	 * 读取每一行数据
	 * 
	 * @param workBook
	 * @return
	 */
	public List<String[]> readRow(Sheet sheet) {

		// 获取到最后一行的的行号, 行号是从0开始的
		int _rowNum = sheet.getLastRowNum();
		List<String[]> list = new ArrayList<String[]>();
		for (int i = 0; i <= _rowNum; i++) {
			Row row = sheet.getRow(i);
			String[] obj = null;
			if (null != row) {
				obj = readCells(row);
			}
			list.add(obj);

		}

		return list;

	}

	/**
	 * 读取每一行的单元格数据
	 * 
	 * @param row
	 * @return
	 */
	private String[] readCells(Row row) {

		int _cellCount = row.getPhysicalNumberOfCells();
		String[] values = new String[_cellCount];
		for (int i = 0; i < _cellCount; i++) {
			Cell cell = row.getCell(i);
			if (null != cell) {
				String value = null;
				switch (cell.getCellType()) {
				case Cell.CELL_TYPE_FORMULA:
					value = "" + cell.getCellFormula();
					break;
				case Cell.CELL_TYPE_NUMERIC:
					// 判断是否是日期格式数据
					if (DateUtil.isCellDateFormatted(cell)) {
						value = DateUtil.formatDate(cell.getDateCellValue(),"yyyy-MM-dd");
					} else {
						value = "" + cell.getNumericCellValue();
					}
					break;
				case Cell.CELL_TYPE_STRING:
					value = "" + cell.getStringCellValue();
					break;
				case Cell.CELL_TYPE_BOOLEAN:
					value = "" + cell.getBooleanCellValue();
					break;
				case Cell.CELL_TYPE_BLANK:
					value = "";
				default:
				}
				values[i] = value;
			}
		}
		return values;

	}

}

 3.导出Excel文档

   接口：

package com.songhn.poi.inter;

import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

/**
 * 写Excel文档
 * @author songhn
 *
 */
public interface ExceWriteInter {

	/**
	 * 创建Excel文档
	 * @param excelFilePath
	 */
	public void createExcel(Workbook workBook,List<String[]> list)throws Exception;
}

    2003实现：

/**
 * 
 */
package com.songhn.poi.util;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Workbook;

import com.songhn.poi.inter.ExceWriteInter;

/**
 * @author songhn
 *
 */
public class ExcelWriteUtil2003 implements ExceWriteInter {

	@Override
	public void createExcel(Workbook workBook,List<String[]> list)throws Exception {
		
		HSSFSheet sheet = (HSSFSheet) workBook.createSheet();
		RichTextString richString = null;
		Font font = null;
		CellStyle style = null;
		for (int i = 0; i < list.size(); i++) {
			
			if(i == 0){
				font = workBook.createFont();
				font.setFontHeightInPoints((short)24); //字体大小
			    font.setFontName("楷体");
			    font.setBoldweight(Font.BOLDWEIGHT_BOLD); //粗体
			    font.setColor(HSSFColor.GREEN.index);    //绿字
				style = workBook.createCellStyle();
				style.setFont(font);
				String[] values = (String[])list.get(i);
				HSSFRow row = (HSSFRow) sheet.createRow(i);
				for (int j = 0; j < values.length; j++) {
					String string = values[j];
					HSSFCell cell = row.createCell(j);
					richString = new HSSFRichTextString(string);
					cell.setCellValue(richString);
					cell.setCellStyle(style);
				}
			}else{
				String[] values = (String[])list.get(i);
				HSSFRow row = (HSSFRow) sheet.createRow(i);
				for (int j = 0; j < values.length; j++) {
					String string = values[j];
					HSSFCell cell = row.createCell(j);
					richString = new HSSFRichTextString(string);
					cell.setCellValue(richString);
				}
			}
			
			
		}
		
	}

	

}

   2007+实现：

/**
 * 
 */
package com.songhn.poi.util;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Workbook;
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 com.songhn.poi.inter.ExceWriteInter;

/**
 * @author songhn
 *
 */
public class ExcelWriteUtil2007 implements ExceWriteInter {

	@Override
	public void createExcel(Workbook workBook,List<String[]> list)throws Exception {
		
		XSSFSheet sheet = (XSSFSheet) workBook.createSheet();
		RichTextString richString = null;
		Font font = null;
		CellStyle style = null;
		for (int i = 0; i < list.size(); i++) {
			//设置表头
			if(i == 0){
				font = workBook.createFont();
				font.setFontHeightInPoints((short)12); //字体大小
			    font.setFontName("微软雅黑");
			    font.setBoldweight(Font.BOLDWEIGHT_BOLD); //粗体
			    font.setColor(HSSFColor.GREEN.index);    //绿字
				String[] values = (String[])list.get(i);
				XSSFRow row = (XSSFRow) sheet.createRow(i+1);
				style = workBook.createCellStyle();
				style.setFont(font);
				style.setBorderBottom(CellStyle.BORDER_DOUBLE);
				for (int j = 0; j < values.length; j++) {
					String string = values[j];
					XSSFCell cell = row.createCell(j+1);
					richString = new XSSFRichTextString(string);
					cell.setCellValue(richString);
					cell.setCellStyle(style);
				}
			}else{
				font = workBook.createFont();
				font.setFontHeightInPoints((short)12); //字体大小
			    font.setFontName("微软雅黑");
			    style = workBook.createCellStyle();
				style.setFont(font);
				style.setBorderBottom(CellStyle.BORDER_THIN);
				String[] values = (String[])list.get(i);
				XSSFRow row = (XSSFRow) sheet.createRow(i+1);
				for (int j = 0; j < values.length; j++) {
					String string = values[j];
					XSSFCell cell = row.createCell(j+1);
					richString = new XSSFRichTextString(string);
					cell.setCellValue(richString);
					cell.setCellStyle(style);
				}
			}
			
			
		}
		
	}

	

}

 用到的工具类：

package com.songhn.poi.util;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;

/**
 * @author songhn
 *
 * 日期工具
 */
public class DateUtil {

	/**
	 * 判断是否是日期格式数据
	 * @param cell
	 * @return
	 */
	public static boolean isCellDateFormatted(Cell cell){
		
		if (HSSFDateUtil.isCellDateFormatted(cell)) {
			return true;
		}
		return false;
		
	}
	
	/**
	 * 将日期格式的数据按照预定的格式进行转换
	 * @param date
	 * @param formatGeshi
	 * @return
	 */
	public static String formatDate(Date date , String formatGeshi){
		
		SimpleDateFormat format =new SimpleDateFormat(formatGeshi);
		
		return format.format(date);
		
	}
	
}

 测试代码：

/**
 * 
 */
package com.songhn.poi.test;

import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.ss.usermodel.Workbook;
import com.songhn.poi.util.ExcelReadUtil;
import com.songhn.poi.util.WorkbookFactory;


/**
 * @author songhn
 *
 */
public class ReadExcelTest {

	/**
	 * @param args
	 */
	public static void main(String[] args) {

		String excelPath = "E:\\testExcel.xls";
		WorkbookFactory factory = new WorkbookFactory();
		Map map = null;
		try {
			Workbook workBook= factory.create(excelPath);
			ExcelReadUtil util = new ExcelReadUtil();
			map = util.readSheet(workBook);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		Set set = map.keySet();
		for(Iterator it=set.iterator();it.hasNext();){

			String key = (String)it.next();
			System.out.println("Sheet名称：" + key); 
			List<String[]> list = (List<String[]>)map.get(key);
			for (int i = 0; i < list.size(); i++) {
				String[] str = list.get(i);
				String obj = "";
				for (int j = 0; j < str.length; j++) {
					obj = obj + str[j] + ",";
				}
				System.out.println("第"+(i+1)+"行:"+obj);
			}

		}

	}

}

 

package com.songhn.poi.test;

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

import org.apache.poi.ss.usermodel.Workbook;

import com.songhn.poi.inter.ExceWriteInter;
import com.songhn.poi.util.ExcelWriteUtil2003;
import com.songhn.poi.util.ExcelWriteUtil2007;
import com.songhn.poi.util.WorkbookFactory;


public class WriteExcelTest {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		
		List<String[]> list = new ArrayList<String[]>();
		String[]  title = new String[5];
		title[0] = "标题1";
		title[1] = "标题2";
		title[2] = "标题3";
		title[3] = "标题4";
		title[4] = "标题5";
		list.add(title);
		for (int i = 1; i < 10; i++) {
			String[]  str = new String[5];
			str[0] = "张三"+i;
			str[1] = "李四"+i;
			str[2] = "王五"+i;
			str[3] = "赵六"+i;
			str[4] = "冯七"+i;
			list.add(str);
		}
		ExceWriteInter write = new ExcelWriteUtil2007();
		String excelFilePath = "E:\\";
		String excelFileName = "test.xlsx";
		try {
			WorkbookFactory workBookFactory =new WorkbookFactory();
			Workbook workbook = workBookFactory.create(excelFileName,false);
			write.createExcel(workbook, list);
			workbook.write(new FileOutputStream(new File(excelFilePath+excelFileName))); 
			System.out.println("创建成功!!!");
		} catch (Exception e) {
			System.out.println("创建失败!!!");
			e.printStackTrace();
		}

	}

}