java 读写Excel文件（2007与2003）

java 读写Excel文件（2007与2003）

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelFileTest {
	public static void main(String[] args) {
		System.out.println("-------xlsx------");
		writeExcelFile("D:\\ReadFileTest\\ExcelFileTest.xlsx");
		readExcelFile("D:\\ReadFileTest\\ExcelFileTest.xlsx");
		System.out.println("-------xls------");
		writeExcelFile("D:\\ReadFileTest\\ExcelFileTest.xls");
		readExcelFile("D:\\ReadFileTest\\ExcelFileTest.xls");
	}

	// 写入数据
	public static void writeExcelFile(String fileName) {
		String headString[] = { "用户ID", "用户昵称", "用户手机号", "用户昵称", "用户手机号" };

		boolean isE2007 = false; // 判断是否是excel2007格式
		if (fileName.endsWith("xlsx")) {
			isE2007 = true;
		}

		Workbook workbook = null;// 根据文件格式(2003或者2007)来初始化

		if (isE2007) {
			workbook = new XSSFWorkbook();
		} else {
			workbook = new HSSFWorkbook();
		}

		Sheet sheet = workbook.createSheet("123");
		try {
			Row row = null;
			Cell cell = null;
			int Rows = 0;
			row = sheet.createRow(Rows);

			for (int i = 0; i < headString.length; i++) {
				cell = row.createCell(i);
				System.out.println("headString[" + i + "] == " + headString[i]);
				cell.setCellValue(headString[i]);
				cell.setCellType(XSSFCell.CELL_TYPE_STRING);

				CellStyle cellStyle = workbook.createCellStyle();
				cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
				cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
				cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 加上这个才能使颜色起作用
				cellStyle.setFillBackgroundColor(HSSFColor.GREEN.index);// 设定单元个背景颜色
				cellStyle.setFillForegroundColor(HSSFColor.BLUE.index);// 设置单元格显示颜色
				cell.setCellStyle(cellStyle);
			}

			FileOutputStream fileOut = new FileOutputStream(fileName);
			workbook.write(fileOut);
			fileOut.close();
		} catch (FileNotFoundException e) {
			System.out.println("找不到指定文件");
		} catch (IOException e) {
			System.out.println("读取文件失败");
		}
	}

	// 读取数据
	public static void readExcelFile(String fileName) {
		boolean isE2007 = false; // 判断是否是excel2007格式

		if (fileName.endsWith("xlsx")) {
			isE2007 = true;
		}

		try {
			System.out.println("------------InputStream");
			InputStream inputStream = new FileInputStream(fileName); // 建立输入流

			Workbook workbook = null;
			// 根据文件格式(2003或者2007)来初始化

			if (isE2007) {
				workbook = new XSSFWorkbook(inputStream);
			} else {
				workbook = new HSSFWorkbook(inputStream);
			}

			System.out.println("------------HSSFWorkbook");

			Sheet sheet = workbook.getSheetAt(0); // 获得第一个表单

			// 读取指定单元的数据
			Row row2 = sheet.getRow(0);
			Cell cell2 = row2.getCell(0);
			System.out.println("cellColumnIndex == " + cell2.getColumnIndex());
			System.out.println("cellColumnIndex == " + cell2.getCellType());
			System.out.println("cellColumnIndex == " + cell2.getStringCellValue());

			// 迭代器读取所有的数据
			Iterator<Row> rows = sheet.rowIterator(); // 获得第一个表单的迭代器
			while (rows.hasNext()) {
				Row row = rows.next(); // 获得行数据
				System.out.println("Row #" + row.getRowNum()); // 获得行号从0开始
				Iterator<Cell> cells = row.cellIterator(); // 获得第一行的迭代器
				while (cells.hasNext()) {
					Cell cell = cells.next();
					System.out.println("Cell #" + cell.getColumnIndex());
					switch (cell.getCellType()) { // 根据cell中的类型来输出数据
					case HSSFCell.CELL_TYPE_NUMERIC:
						System.out.println(cell.getNumericCellValue());
						break;
					case HSSFCell.CELL_TYPE_STRING:
						System.out.println(cell.getStringCellValue());
						break;
					case HSSFCell.CELL_TYPE_BOOLEAN:
						System.out.println(cell.getBooleanCellValue());
						break;
					case HSSFCell.CELL_TYPE_FORMULA:
						System.out.println(cell.getCellFormula());
						break;
					default:
						System.out.println("unsuported sell type");
						break;
					}
				}
			}
		} catch (IOException ex) {
			ex.printStackTrace();
		}
	}

}

相关jar包如下：