Java读写Excel文件

Java读写Excel文件也需使用Apache poi.jar，点击此处下载点击打开链接

代码如下：

package com.cy.util;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFCell;
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.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 ExcelUtil {
	// 定义单元格为数字时的格式
	private static DecimalFormat df = new DecimalFormat("0");
	// 定义单元格为日期时的格式
	private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	// 定义单元格为小数时的格式
	private static DecimalFormat nf = new DecimalFormat("0.00");

	public static ArrayList<ArrayList<Object>> readExcel(File file) {
		if (file == null) {
			return null;
		}
		if (file.getName().endsWith("xlsx")) {
			return readExcel2007(file);
		} else {
			return readExcel2003(file);
		}
	}

	// 读取2003版本的excel文件
	public static ArrayList<ArrayList<Object>> readExcel2003(File file) {
		try {
			// rowList存储整个Excel文件的数据
			ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
			// colList存储一行的数据
			ArrayList<Object> colList;
			HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
			// 读取sheet1中的内容
			HSSFSheet sheet = wb.getSheetAt(0);
			// 每一行
			HSSFRow row;
			// 每个单元格
			HSSFCell cell;
			Object value;
			// 遍历所有数据的第一行到最后一行
			for (int i = sheet.getFirstRowNum(), rowCount = 0; rowCount < sheet
					.getPhysicalNumberOfRows(); i++) {
				// 得到当前行
				row = sheet.getRow(i);
				colList = new ArrayList<Object>();
				// 如果当前行数据为空，并且不是最后一行，那么直接将空的colList加入rowList中
				if (row == null) {
					if (i != sheet.getPhysicalNumberOfRows()) {
						rowList.add(colList);
					}
					continue;
				} else {
					//下移至下一行
					rowCount++;
				}
				//遍历当前行的列
				for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
					//得到当前的单元格
					cell = row.getCell(j);
					//当前单元格为空
					if (cell == null
							|| cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
						if (j != row.getLastCellNum()) {
							//判断是否为该行最后一个单元格
							colList.add("");
						}
						continue;
					}
					//判断单元格中内容的数据类型
					switch (cell.getCellType()) {
					case XSSFCell.CELL_TYPE_STRING:
						value = cell.getStringCellValue();
						break;
					case XSSFCell.CELL_TYPE_NUMERIC:
						if ("@".equals(cell.getCellStyle()
								.getDataFormatString())) {
							value = df.format(cell.getNumericCellValue());
						} else if ("General".equals(cell.getCellStyle()
								.getDataFormatString())) {
							value = nf.format(cell.getNumericCellValue());
						} else {
							value = sdf.format(HSSFDateUtil.getJavaDate(cell
									.getNumericCellValue()));
						}
						break;
					case XSSFCell.CELL_TYPE_BOOLEAN:
						value = Boolean.valueOf(cell.getBooleanCellValue());
						break;
					case XSSFCell.CELL_TYPE_BLANK:
						value = "";
						break;
					default:
						value = cell.toString();
					}
					colList.add(value);
				}
				rowList.add(colList);
			}
			return rowList;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	public static ArrayList<ArrayList<Object>> readExcel2007(File file) {
		try {
			ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
			ArrayList<Object> colList;
			XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
			XSSFSheet sheet = wb.getSheetAt(0);
			XSSFRow row;
			XSSFCell cell;
			Object value;
			for (int i = sheet.getFirstRowNum(), rowCount = 0; rowCount <= sheet
					.getLastRowNum(); i++) {
				row = sheet.getRow(i);
				colList = new ArrayList<Object>();
				if (row == null) {
					if (i != sheet.getPhysicalNumberOfRows()) {
						rowList.add(colList);
					}
					continue;
				} else {
					rowCount++;
				}
				for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
					cell = row.getCell(j);
					if (cell == null
							|| cell.getCellType() == XSSFCell.CELL_TYPE_BLANK) {
						if (j != row.getLastCellNum()) {
							colList.add("");
						}
						continue;
					}
					switch (cell.getCellType()) {
					case XSSFCell.CELL_TYPE_STRING:
						value = cell.getStringCellValue();
						break;
					case XSSFCell.CELL_TYPE_NUMERIC:
						if ("@".equals(cell.getCellStyle()
								.getDataFormatString())) {
							value = df.format(cell.getNumericCellValue());
						} else if ("General".equals(cell.getCellStyle()
								.getDataFormatString())) {
							value = nf.format(cell.getNumericCellValue());
						} else {
							value = sdf.format(HSSFDateUtil.getJavaDate(cell
									.getNumericCellValue()));
						}
						break;
					case XSSFCell.CELL_TYPE_BOOLEAN:
						value = Boolean.valueOf(cell.getBooleanCellValue());
						break;
					case XSSFCell.CELL_TYPE_BLANK:
						value = "";
						break;
					default:
						value = cell.toString();
					}
					colList.add(value);
				}
				rowList.add(colList);
			}
			return rowList;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	//将数据写入Excel文件中
	public static void writeExcel(ArrayList<ArrayList<Object>> result,String path){
		if(result == null){
			return;
		}
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("sheet1");
		for(int i = 0 ;i < result.size() ; i++){
			 HSSFRow row = sheet.createRow(i);
			if(result.get(i) != null){
				for(int j = 0; j < result.get(i).size() ; j ++){
					HSSFCell cell = row.createCell(j);
					cell.setCellValue(result.get(i).get(j).toString());
				}
			}
		}
		ByteArrayOutputStream os = new ByteArrayOutputStream();
        try
        {
            wb.write(os);
        } catch (Exception e){
            e.printStackTrace();
        }
        byte[] content = os.toByteArray();
        File file = new File(path);//Excel文件生成后存储的位置。
        OutputStream fos  = null;
        try
        {
            fos = new FileOutputStream(file);
            fos.write(content);
            os.close();
            fos.close();
        }catch (Exception e){
            e.printStackTrace();
        }           
	}

	public static DecimalFormat getDf() {
		return df;
	}

	public static void setDf(DecimalFormat df) {
		ExcelUtil.df = df;
	}

	public static SimpleDateFormat getSdf() {
		return sdf;
	}

	public static void setSdf(SimpleDateFormat sdf) {
		ExcelUtil.sdf = sdf;
	}

	public static DecimalFormat getNf() {
		return nf;
	}

	public static void setNf(DecimalFormat nf) {
		ExcelUtil.nf = nf;
	}
	
	public static void main(String[] args) {
		try{
			File file = new File("****.xls");
			ArrayList<ArrayList<Object>> test = readExcel(file);
			for(int i = 0;i < test.size();i++){
				for(int j = 0;j < test.get(i).size();j++){
					System.out.print(test.get(i).get(j)+"\t");
				}
				System.out.println();
			}
		}
		catch(Exception e){
			e.printStackTrace();
		}
	}

}