使用POI操作EXECL做数据的导入与导出

 使用POI操作EXECL做数据的导入与导出

        

    一: 所需JAR包

       poi-3.9-20121203.jar

      poi-ooxml-3.9-20121203.jar

      poi-ooxml-schemas-3.9-20121203.jar

   二:调用

    1 导入

       List<EpsEnterprise> epsList = excelService.importExcel(importSetInfo);

    2 导出

    excelService.exportExcel(setInfo);

 POIExcelService

package com.wonders.framework.component.execl.service;

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

import com.wonders.framework.component.execl.entity.bo.ExportSetInfo;
import com.wonders.framework.component.execl.entity.bo.ImportSetInfo;

public interface POIExcelService {
	// 字段类型——日期型
	public final int FIELD_DATE_TYPE = 1;
	// 字段类型——数据字典类型
	public final int FIELD_DIC_TYPE = 0;

	public <T> List<T> importExcel(ImportSetInfo setInfo);

	public void exportExcel(ExportSetInfo setInfo) throws IOException,
			IllegalArgumentException, IllegalAccessException;
}

       POIExcelServiceImpl

       ImportSetInfo  setInfo  数据导入对象

       ExportSetInfo  setInfo  数据导出对象

       

package com.wonders.framework.component.execl.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.wonders.framework.component.execl.entity.bo.ExportSetInfo;
import com.wonders.framework.component.execl.entity.bo.ImportSetInfo;
import com.wonders.framework.component.execl.service.POIExcelService;
import com.wonders.framework.dictionary.service.DictionaryService;
import com.wonders.framework.dictionary.service.impl.DictionaryServiceImpl;
import com.wonders.framework.util.DateUtil;
import com.wonders.framework.util.ReflectionUtils;

@Component("excelService")
public class POIExcelServiceImpl implements POIExcelService {
	/** 总行数 */
	private int totalRows = 0;

	/** 总列数 */
	private int totalCells = 0;
	// 设置读excel的起始行数
	private final int READ_BEGIN_ROW = 1;
	// 设置读excel的起始列数
	private final int READ_BEGIN_CELL = 1;
	// 设置写excel的起始行数
	private final int WRITE_BEGIN_ROW = 1;
	// 设置写excel的起始列数
	private final int WRITE_BEGIN_CELL = 0;
	// 与2003的兼容
	// private boolean isExcel2003 = true;
	// 内容行样式
	private static CellStyle contentStyle;
	// 内容行字体
	private static Font contentFont;
	private Workbook wb;
	@Autowired
	private DictionaryService dictionaryService;
	private static Logger logger = Logger.getLogger(DictionaryServiceImpl.class
			.getName());

	/** */
	/** 构造方法 */
	public POIExcelServiceImpl() {
	}

	private void importInit(String fileName) {
		init(fileName);
	}

	private void init(String fileName) {
		File file = new File(fileName);
		/** 根据版本选择创建Workbook的方式 */
		try {
			wb = fileName.matches("^.+\\.(?i)(xlsx)$") ? new XSSFWorkbook(
					new FileInputStream(file)) : new HSSFWorkbook(
					new FileInputStream(file));
		} catch (FileNotFoundException e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		} catch (IOException e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
	}

	private void exportInit(String fileName) {
		init(fileName);
		contentStyle = wb.createCellStyle();
		contentFont = wb.createFont();
		initContentCellStyle();
		initContentFont();
	}

	/**
	 * 校验文件
	 * 
	 * @param fileName
	 * @return
	 */
	public String checkExcelFile(String fileName) {
		/** */
		/** 检查文件名是否为空或者是否是Excel格式的文件 */
		if (fileName == null || !fileName.matches("^.+\\.(?i)((xls)|(xlsx))$")) {
			return "不是合法的excel文件！";
		}
		/** 检查文件是否存在 */
		File file = new File(fileName);
		if (file == null || !file.exists()) {
			return "文件不存在！";
		}
		return null;
	}

	/**
	 * <ul>
	 * <li>Description:[根据文件名读取excel文件]</li>
	 * <ul>
	 * 
	 * @param fileName
	 * @return
	 * @throws Exception
	 */
	public <T> List<T> importExcel(ImportSetInfo setInfo) {
		importInit(setInfo.getFileName());
		List<T> dataLst = new ArrayList<T>();
		/** 得到第一个shell */
		Sheet sheet = wb.getSheetAt(0);
		this.totalRows = sheet.getPhysicalNumberOfRows();
		if (this.totalRows >= 1 && sheet.getRow(0) != null) {
			this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
		}

		String[] fieldNames = setInfo.getFieldNames();
		Map<String, Integer> fieldTypes = setInfo.getFieldTypes();
		Map<String, String> dicTypes = setInfo.getDicTypes();
		/** 循环Excel的行 */
		for (int r = READ_BEGIN_ROW; r < this.totalRows; r++) {	
			Row row = sheet.getRow(r);
			if (row == null) {
				continue;
			}

			int cells = row.getPhysicalNumberOfCells();
			 Class<?> clz = setInfo.getClz();
			 Object obj = null;
			 try {
			 obj = clz.newInstance();
			 } catch (InstantiationException e1) {
			 logger.error(e1.getMessage());
			 e1.printStackTrace();
			 } catch (IllegalAccessException e1) {
			 logger.error(e1.getMessage());
			 e1.printStackTrace();
			 }
			/** */
			/** 循环Excel的列 */
			for (short i = 0; i < cells; i++) {
				Cell cell = row.getCell(i);
				String cellValue = "";
				if (cell == null) {
					// continue;
				}

				/** 处理数字型的,自动去零 */
				if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
					/** */
					/** 在excel里,日期也是数字,在此要进行判断 */
					if (HSSFDateUtil.isCellDateFormatted(cell)) {
						cellValue = getRightStr(cell.getDateCellValue() + "");
					} else {
						cellValue = getRightStr(cell.getNumericCellValue() + "");
					}
				}
				/** */
				/** 处理字符串型 */
				else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
					cellValue = cell.getStringCellValue();
				}
				/** */
				/** 处理布尔型 */
				else if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
					cellValue = cell.getBooleanCellValue() + "";
				}
				/** */
				/** 其它的,非以上几种数据类型 */
				else {
					cellValue = null;
				}
				if(cellValue==null||cellValue.equals("")){
					continue;
				}
				Object value = cellValue;
				if (fieldTypes != null) {
					Integer fieldType = fieldTypes.get(fieldNames[i]);
					// 以下是类型转换
					if (fieldType != null && this.FIELD_DIC_TYPE == fieldType) {
						value = dictionaryService.getKey(
								dicTypes.get(fieldNames[i]), cellValue);
					} else if (fieldType != null
							&& this.FIELD_DATE_TYPE == fieldType) {
						try {
							value = DateUtil.parse(cellValue, "yyyy-MM-dd");
						} catch (ParseException e) {
							logger.error(e.getMessage());
							e.printStackTrace();
						}
					} else {
						// TODO
					}
				}
				ReflectionUtils.invokeSetterMethod(obj, fieldNames[i], value);
			}
			dataLst.add((T)obj);
		}
		return dataLst;
	}

	/** */
	/**
	 * <ul>
	 * <li>Description:[得到总行数]</li>
	 * <ul>
	 * 
	 * @return
	 */
	public int getTotalRows() {
		return totalRows;
	}

	/** */
	/**
	 * <ul>
	 * <li>Description:[得到总列数]</li>
	 * <ul>
	 * 
	 * @return
	 */
	public int getTotalCells() {
		return totalCells;
	}

	/**
	 * <ul>
	 * <li>Description:[正确地处理整数后自动加零的情况]</li>
	 * <li>Created by [Huyvanpull] [Jan 20, 2010]</li>
	 * <li>Midified by [modifier] [modified time]</li>
	 * <ul>
	 * 
	 * @param sNum
	 * @return
	 */
	private String getRightStr(String sNum) {
		DecimalFormat decimalFormat = new DecimalFormat("#.000000");
		String resultStr = decimalFormat.format(new Double(sNum));
		if (resultStr.matches("^[-+]?\\d+\\.[0]+$")) {
			resultStr = resultStr.substring(0, resultStr.indexOf("."));
		}
		return resultStr;
	}

	/**
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 * @Description: 将Map里的集合对象数据输出Excel数据流
	 */
	@SuppressWarnings({ "unchecked" })
	public void exportExcel(ExportSetInfo setInfo) throws IOException,
			IllegalArgumentException, IllegalAccessException {
		String fileName = setInfo.getFileTemplateName();
		exportInit(fileName);

		List<?> dataList = setInfo.getDataList();
		String[] fieldNames = setInfo.getFieldNames();
		Map<String, Integer> fieldTypes = setInfo.getFieldTypes();
		Map<String, String> dicTypes = setInfo.getDicTypes();
		// String[] sheetNames = new String[setInfo.getFieldNames().size()];
		/*
		 * int sheetNameNum = 0; for (Entry<String, Integer> entry : fieldNames)
		 * { sheetNames[sheetNameNum] = entry.getKey(); sheetNameNum++; }
		 */
		// HSSFSheet[] sheets = createSheets(setInfo.getFieldNames().size(),
		// sheetNames);
		// int sheetNum = 0;
		Sheet sheet = wb.getSheetAt(0);
		int rowNum = WRITE_BEGIN_ROW;
		for (Object obj : dataList) {
			Row contentRow = sheet.createRow(rowNum);
			contentRow.setHeight((short) 300);
			Cell[] cells = createCells(contentRow, fieldNames.length);
			if (fieldNames != null) {
				for (int i = 0, count = fieldNames.length; i < count; i++) {
					Object value = ReflectionUtils.invokeGetterMethod(obj,
							fieldNames[i]);
					if (fieldTypes != null) {
						Integer fieldType = fieldTypes.get(fieldNames[i]);
						// 以下是类型转换
						if (fieldType != null
								&& this.FIELD_DIC_TYPE == fieldType) {
							if(!org.springframework.util.StringUtils.isEmpty(value)){
							value = dictionaryService
									.getValue(dicTypes.get(fieldNames[i]),
											(String) value);
							}
						} else if (fieldType != null
								&& this.FIELD_DATE_TYPE == fieldType) {
							if(!org.springframework.util.StringUtils.isEmpty(value)){
								value = DateUtil.format((Date) value, "yyyy-MM-dd");
							}
							
						} else {
							// TODO
						}
					}
					cells[i].setCellValue(value == null ? "" : value.toString());
				}
				rowNum++;
			}
		
			// adjustColumnSize(sheets, sheetNum, fieldNames); // 自动调整列宽
			// sheetNum++;
		}
		String outFileName = setInfo.getOutFileName();
		if (outFileName != null && !StringUtils.isEmpty(outFileName)) {
			FileOutputStream out = new FileOutputStream(outFileName);
			wb.write(out);
		}
	}

	/**
	 * @Description: 初始化内容行样式
	 */
	private static void initContentCellStyle() {
//		contentStyle.setAlignment(CellStyle.ALIGN_CENTER);
//		contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		contentStyle.setFont(contentFont);
//		contentStyle.setBorderTop(CellStyle.BORDER_THIN);
//		contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
//		contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
//		contentStyle.setBorderRight(CellStyle.BORDER_THIN);
//		contentStyle.setTopBorderColor(IndexedColors.BLUE.index);
//		contentStyle.setBottomBorderColor(IndexedColors.BLUE.index);
//		contentStyle.setLeftBorderColor(IndexedColors.BLUE.index);
//		contentStyle.setRightBorderColor(IndexedColors.BLUE.index);
		contentStyle.setWrapText(true); // 字段换行
	}

	/**
	 * @Description: 初始化内容行字体
	 */
	private static void initContentFont() {
		contentFont.setFontName("宋体");
		contentFont.setFontHeightInPoints((short) 10);
		contentFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
		contentFont.setCharSet(Font.DEFAULT_CHARSET);
		//contentFont.setColor(IndexedColors.BLUE_GREY.index);
	}

	/**
	 * @Description: 创建所有的Sheet
	 */
	// private Sheet[] createSheets(int num, String[] names) {
	// Sheet[] sheets = new Sheet[num];
	// for (int i = 0; i < num; i++) {
	// sheets[i] = wb.createSheet(names[i]);
	// }
	// return sheets;
	// }
	/**
	 * @Description: 创建内容行的每一列
	 */
	private static Cell[] createCells(Row contentRow, int num) {
		Cell[] cells = new Cell[num];
		for (int i = 0, len = cells.length; i < len; i++) {
			cells[i] = contentRow.createCell(i);
			cells[i].setCellStyle(contentStyle);
		}
		return cells;
	}
}

package com.wonders.framework.component.execl.entity.bo;

import java.util.Map;

/**
 * @Description: 封装Excel导入的设置信息
 */
public class ImportSetInfo {
	private String fileName;
	// 字段名称
	private String[] fieldNames;
	// 字段类型
	private Map<String,Integer> fieldTypes;
	// 数据字典类型
	private Map<String,String> dicTypes;
	// 导入的数据应转换的对象类型
	private Class<?> clz;

	public String getFileName() {
		return fileName;
	}

	public void setFileName(String fileName) {
		this.fileName = fileName;
	}

	public String[] getFieldNames() {
		return fieldNames;
	}

	public void setFieldNames(String[] fieldNames) {
		this.fieldNames = fieldNames;
	}

	public Map<String, Integer> getFieldTypes() {
		return fieldTypes;
	}

	public void setFieldTypes(Map<String, Integer> fieldTypes) {
		this.fieldTypes = fieldTypes;
	}

	public Map<String, String> getDicTypes() {
		return dicTypes;
	}

	public void setDicTypes(Map<String, String> dicTypes) {
		this.dicTypes = dicTypes;
	}

	public Class<?> getClz() {
		return clz;
	}

	public void setClz(Class<?> clz) {
		this.clz = clz;
	}

}

package com.wonders.framework.component.execl.entity.bo;

import java.util.List;
import java.util.Map;

/**
 * @Description: 封装Excel导出的设置信息
 */
public class ExportSetInfo {

	private List<?> dataList;
    //字段名称
	private String[]  fieldNames;
	//字段类型
	private Map<String,Integer> fieldTypes;
	//数据字典类型
	private Map<String,String> dicTypes;

	private String outFileName;
	
	private String fileTemplateName;

	public String getFileTemplateName() {
		return fileTemplateName;
	}

	public void setFileTemplateName(String fileTemplateName) {
		this.fileTemplateName = fileTemplateName;
	}
	public String getOutFileName() {
		return outFileName;
	}

	public void setOutFileName(String outFileName) {
		this.outFileName = outFileName;
	}

	public List<?> getDataList() {
		return dataList;
	}

	public String[] getFieldNames() {
		return fieldNames;
	}

	public void setFieldNames(String[] fieldNames) {
		this.fieldNames = fieldNames;
	}

	public Map<String, String> getDicTypes() {
		return dicTypes;
	}

	public Map<String, Integer> getFieldTypes() {
		return fieldTypes;
	}

	public void setFieldTypes(Map<String, Integer> fieldTypes) {
		this.fieldTypes = fieldTypes;
	}

	public void setDicTypes(Map<String, String> dicTypes) {
		this.dicTypes = dicTypes;
	}

	public void setDataList(List<?> dataList) {
		this.dataList = dataList;
	}

}