读取excel文件到数据库 和 下载 excel文件

Poi解析2003时使用的是HSSFCell，而2007的则是 XSSFCell，是完全不同的两套API
必须先要判断excel的类型，不过 HSSFWorkbook 和 XSSFWorkbook 实现的接口都是一样的Workbook，直接在实例化接口的时候有点区别其他时候没有任何差异。

第一部分是读取excel到数据库

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.poifs.filesystem.POIFSFileSystem;
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 testFile {

	public static List<List<Object>> readExcel(File file) throws IOException {
		String fileName = file.getName();
		String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
		if ("xls".equals(extension)) {
			return read2003Excel(file);
		} else if ("xlsx".equals(extension)) {
			return read2007Excel(file);
		} else {
			throw new IOException("不支持的文件类型");
		}
	}

	/**
	 * 读取 office 2003 excel
	 * 
	 * @throws IOException
	 * @throws FileNotFoundException
	 */
	private static List<List<Object>> read2003Excel(File file) throws IOException {

		List<List<Object>> list = new LinkedList<List<Object>>();
		Object value = null;
		POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(file));
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem);
		HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);

		int rowstart = hssfSheet.getFirstRowNum();
		int rowEnd = hssfSheet.getLastRowNum();
		int cellStart = 0;
		int cellEnd = 0;
		for (int i = rowstart; i <= rowEnd; i++) {
			List<Object> linked = new LinkedList<Object>();
			HSSFRow row = hssfSheet.getRow(i);
			if (null == row)
				continue;
			if (i == 0) {
				cellStart = row.getFirstCellNum();
				cellEnd = row.getLastCellNum();
			}

			for (int k = cellStart; k <= cellEnd; k++) {
				HSSFCell cell = row.getCell(k);
				if (null == cell || null == cell.toString() || "".equals(cell.toString())) {
					linked.add("无");
					continue;
				}

				switch (cell.getCellType()) {
				case HSSFCell.CELL_TYPE_NUMERIC: // 数字
					System.out.print(cell.getNumericCellValue() + "   ");
					value = cell.getNumericCellValue();
					break;
				case HSSFCell.CELL_TYPE_STRING: // 字符串

					value = cell.getStringCellValue();
					break;
				case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean

					value = cell.getBooleanCellValue();
					break;
				case HSSFCell.CELL_TYPE_FORMULA: // 公式

					value = cell.getCellFormula();
					break;
				case HSSFCell.CELL_TYPE_BLANK: // 空值

					System.out.println(" ");
					value = "";// 导入不能为空

					break;
				default:
					value = cell.toString();
				}
				value = cell.toString();
				if (value == null || "".equals(value)) {
					value = "无";// 导入不能为空
				}
				linked.add(value);
			}
			System.out.print("\n");
			list.add(linked);
		}
		hssfWorkbook.close();
		poifsFileSystem.close();
		return list;
	}

	/**
	 * 读取Office 2007 excel
	 */
	private static List<List<Object>> read2007Excel(File file) throws IOException {
		List<List<Object>> list = new LinkedList<List<Object>>();
		// 构造 XSSFWorkbook 对象，strPath 传入文件路径
		XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
		// 读取第一章表格内容
		XSSFSheet sheet = xwb.getSheetAt(0);
		Object value = null;
		XSSFRow row = null;
		XSSFCell cell = null;
		int counter = 0;
		for (int i = sheet.getFirstRowNum(); counter < sheet.getPhysicalNumberOfRows(); i++) {
			if (i == 0) {
				// 跳过第一行
				continue;
			}
			row = sheet.getRow(i);
			if (row == null) {
				break;
			}
			List<Object> linked = new LinkedList<Object>();
			for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
				cell = row.getCell(j);
				if (cell == null) {
					value = "无";// 导入不能为空
					linked.add(value);
					continue;
				}
				DecimalFormat df = new DecimalFormat("0");// 格式化 number String
															// 字符
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");// 格式化日期字符串
				DecimalFormat nf = new DecimalFormat("0");// 格式化数字

				if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
					if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
						SimpleDateFormat sdf2 = null;
						if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
							sdf2 = new SimpleDateFormat("HH:mm");
						} else {// 日期
							Date date = cell.getDateCellValue();
							value = sdf.format(date);
						}
					} else
						value = cell.toString();
				} else
					value = cell.toString();

				if (value == null || "".equals(value)) {
					value = "无";// 导入不能为空
				}
				linked.add(value);
			}
			list.add(linked);
		}
		return list;
	}

}

第二部分下载excel文件

/**
	 * 下载Excel文件
	 * @param		导出的数据对象List-->objList
	 * @param		模板对象UploadAndDownLoadColumnVo
	 * @param		文件导出路径path
	 * */
	@Override
	public void downLoadExcel(List<Object> objList , List<UploadAndDownLoadColumnVo> uploadAndDownLoadColumnVoList  , String path) {
		if(StringUtils.isBlank(path)){
			throw new ScubeBizException("导出路径错误,导出失败！");
		}
		String bookName = "";
		if(objList==null||objList.size()<=0){
			throw new ScubeBizException("要导出的内容为空,导出失败！");
		}else{
			//创建工作表格对象
			HSSFWorkbook workBook = new HSSFWorkbook();
			//创建Sheet页
			for(int i =0 ; i<uploadAndDownLoadColumnVoList.size() ; i++){
				UploadAndDownLoadColumnVo uploadAndDownLoadColumnVo = uploadAndDownLoadColumnVoList.get(i);
				bookName = uploadAndDownLoadColumnVo.getBookName();
				HSSFSheet sheet = workBook.createSheet(uploadAndDownLoadColumnVo.getSheetName());
				HSSFRow row = sheet.createRow(0);
				Class<? extends UploadAndDownLoadColumnVo> voClass = uploadAndDownLoadColumnVo.getClass();
				try {
					Method[] mms = voClass.getDeclaredMethods();
			//填充Sheet标题
					for(int j = 0 ; j<mms.length ; j++){
						Method mm = mms[j];
						String methodName = mm.getName();
						if(methodName.contains("getColumnName")){
							String columnName = (String)mm.invoke(uploadAndDownLoadColumnVo, new Object[]{});
							if(columnName!=null){
								String index = methodName.replace("getColumnName", "");
								int indexNo = Integer.parseInt(index);
								HSSFCell cell = row.createCell(indexNo-1);
								logger.info("methodName["+methodName+"]value["+columnName+"]index["+index+"]");
								cell.setCellValue(columnName);
							}
						}
					}
				} catch (Exception e) {
					e.printStackTrace();
				} 
			}
			//获取填充数据的Sheet
			HSSFSheet targetSheet = workBook.getSheetAt(0);
			//填充数据
			for(int k = 0 ; k<objList.size() ; k++){
				Object object = objList.get(k);
				Class<? extends Object> objClass = object.getClass();
				Method[] mms = objClass.getDeclaredMethods();
				HSSFRow targetRow = targetSheet.createRow(k+1);
				int fieldIndex = 0;
				String fieldValue = "";
				for(int i =0 ; i<mms.length ; i++){
					Method mm = mms[i];
					String mmName = mm.getName();
					String setFlg = "1";
					try {
						if(mmName.contains("get")&&(!mmName.contains("Index"))){
							//获取字段值
							fieldValue = (String)mm.invoke(object, new Object[]{});
							//获取字段索引
							String mName = mmName+"Index";
							Method temMM = objClass.getMethod(mName , new Class<?>[]{});
							fieldIndex = Integer.parseInt(temMM.invoke(object, new Object[]{}).toString());
							setFlg = "0";
						}else{
							setFlg = "1";
						}
					} catch (Exception e) {
						e.printStackTrace();
					}
					if(setFlg.equals("0")){
						//填充表格
						HSSFCell targetCell = targetRow.createCell(fieldIndex);
						targetCell.setCellValue(fieldValue);
					}
				}
				
			}
			if(StringUtils.isBlank(bookName)){
				bookName = new SimpleDateFormat("yyyMMddHHmmss").format(new Date())+"Export";
			}
			try {
				String fullPath = path+bookName+".xls";
				FileOutputStream output = new FileOutputStream(fullPath);
				workBook.write(output);
				output.flush();
				workBook.close();
			} catch (Exception e) {
				logger.error("导出文件失败！" , e);
			}
		}

	}

public class UploadAndDownLoadColumnVo {
		
		//表格名
		String bookName;
		
		//表格Sheet名
		String sheetName;
		
		//列名1
		String columnName1;
		
		//列名2
		String columnName2;
		
		//列名3
		String columnName3;
		
		//列名4
		String columnName4;
		
		//列名5
		String columnName5;
		
		//列名6
		String columnName6;
		
		//列名7
		String columnName7;
		
		//列名8
		String columnName8;
		
		//列名9
		String columnName9;
		
		//列名10
		String columnName10;
		
		//列名11
		String columnName11;
		
		//列名12
		String columnName12;
		
		//列名13
		String columnName13;
		
		//列名14
		String columnName14;
		
		//列名15
		String columnName15;
		
		//列名16
		String columnName16;
		
		//列名17
		String columnName17;
		
		//列名18
		String columnName18;
		
		//列名19
		String columnName19;
		
		//列名20
		String columnName20;

	}

省略get、set方法。

这两个都是项目上用到的，很好用。