poi导入导出

03版Excel 导出

package com.ning;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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;
import org.joda.time.DateTime;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriteTest03 {
	/**
	 * 03版本
	 */
	public void test03() throws IOException {
		// 创建一个工作薄
		Workbook workbook = new HSSFWorkbook();
		// 创建一个工作表
		Sheet sheet = workbook.createSheet("学员统计表");
		// 创建 一行
		Row row = sheet.createRow(0);
		// 创建单元格 一列
		Cell cell = row.createCell(0);
//        二列
		Cell cell1 = row.createCell(1);
		Cell cell4 = row.createCell(2);

		cell.setCellValue("姓名");
		cell1.setCellValue("年龄");
		cell4.setCellValue("入学时间");

//        第二行第一列第二列
		Row row1 = sheet.createRow(1);
		Cell cell2 = row1.createCell(0);
		Cell cell3 = row1.createCell(1);
		Cell cell5 = row1.createCell(2);

		cell2.setCellValue("杨先生");
		cell3.setCellValue("18");
		String s = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
		cell5.setCellValue(s);

//        生成表
		FileOutputStream fileOutputStream = new FileOutputStream("D:\\file\\" + "学员统计表03.xls");
		workbook.write(fileOutputStream);
		fileOutputStream.close();

	}

	public void ExcelWriteTest03BigData() throws IOException {
		long begin = System.currentTimeMillis();
		Workbook workbook = new HSSFWorkbook();
		Sheet sheet = workbook.createSheet("big");

		for (int i = 0; i < 65536; i++) {
			Row row = sheet.createRow(i);
			for (int j = 0; j < 10; j++) {
				Cell cell = row.createCell(j);
				cell.setCellValue(j);
			}
		}
		FileOutputStream fileOutputStream = new FileOutputStream("D:\\file\\" + "big03.xls");
		workbook.write(fileOutputStream);
		long end = System.currentTimeMillis();
		System.out.println((double) (end - begin) / 1000);
	}

	public static void main(String[] args) throws IOException {
//     new ExcelWriteTest03().test03();
		new ExcelWriteTest03().ExcelWriteTest03BigData();

	}
}

07版Excel 写出

package com.ning;


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;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;

import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriteTest07 {
	/**
	 * 07版本
	 */
	public void test03() throws IOException {
		// 创建一个工作薄
		Workbook workbook = new XSSFWorkbook();
		// 创建一个工作表
		Sheet sheet = workbook.createSheet("学员统计表");
		// 创建 一行
		Row row = sheet.createRow(0);
		// 创建单元格 一列
		Cell cell = row.createCell(0);
//        二列
		Cell cell1 = row.createCell(1);
		Cell cell4 = row.createCell(2);

		cell.setCellValue("姓名");
		cell1.setCellValue("年龄");
		cell4.setCellValue("入学时间");

//        第二行第一列第二列
		Row row1 = sheet.createRow(1);
		Cell cell2 = row1.createCell(0);
		Cell cell3 = row1.createCell(1);
		Cell cell5 = row1.createCell(2);

		cell2.setCellValue("杨先生");
		cell3.setCellValue("18");
		String s = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
		cell5.setCellValue(s);

//        生成表
		FileOutputStream fileOutputStream = new FileOutputStream("D:\\file\\" + "学员统计表03.xlsx");
		workbook.write(fileOutputStream);
		fileOutputStream.close();

	}

	public void ExcelWriteTest07BigData() throws IOException {
		long begin = System.currentTimeMillis();
		Workbook workbook = new XSSFWorkbook();
		Sheet sheet = workbook.createSheet("big");

		for (int i = 0; i < 65536; i++) {
			Row row = sheet.createRow(i);
			for (int j = 0; j < 10; j++) {
				Cell cell = row.createCell(j);
				cell.setCellValue(j);
			}
		}
		FileOutputStream fileOutputStream = new FileOutputStream("D:\\file\\" + "big03.xlsx");
		workbook.write(fileOutputStream);
		fileOutputStream.close();
		long end = System.currentTimeMillis();
		System.out.println((double) (end - begin) / 1000);
	}

	public static void main(String[] args) throws IOException {
//        new ExcelWriteTest07().test03();
		new ExcelWriteTest07().ExcelWriteTest07BigData();
	}
}

导入

public void ReaderTest07() throws IOException {
		FileInputStream fileInputStream = new FileInputStream(path + "学员统计表03.xlsx");
		// 获取工作薄
		Workbook workbook = new XSSFWorkbook(fileInputStream);
		// 获取一页
		Sheet sheet = workbook.getSheetAt(0);
		// 获取一行
		Row row = sheet.getRow(0);
		// 获取一列
		Cell cell = row.getCell(0);

		//读取值的时候应该判断类型
		String stringCellValue = cell.getStringCellValue();
		System.out.println(stringCellValue);

		fileInputStream.close();

	}

import org.apache.poi.hssf.usermodel.HSSFCell;
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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.Date;

public class ExcelReaderTypeTest {
	private String path = "D:\\file\\";

	//每个单元格类型
	@Test
	public void ReaderCellType(FileInputStream fileInputStream) throws IOException {
		// 03版
	 fileInputStream = new FileInputStream(path + "学员统计表03.xls");
		// 07 版
// fileInputStream = new FileInputStream(path + "学员统计表03.xlsx");
		// 03获取工作薄
		Workbook workbook = new HSSFWorkbook(fileInputStream);
//		07版
//		Workbook workbook = new XSSFWorkbook(fileInputStream);
		// 获取shell 页
		Sheet sheet = workbook.getSheetAt(0);
		// 获取每一行
		Row row = sheet.getRow(0);
		if (row != null) {
			int cells = row.getPhysicalNumberOfCells();
			for (int i = 0; i < cells; i++) {
				Cell cell = row.getCell(i);
				if (cell != null) {
					int cellType = cell.getCellType();
					String stringCellValue = cell.getStringCellValue();
					System.out.print(stringCellValue + "|");
				}
			}
			System.out.println();
		}

		int rows = sheet.getPhysicalNumberOfRows();
		for (int i = 0; i < rows; i++) {
			Row row1 = sheet.getRow(i);
			if (row1 != null) {
				int cells = row1.getPhysicalNumberOfCells();
				for (int j = 0; j < cells; j++) {
					Cell cell = row1.getCell(j);
					if (cell != null) {
						int cellType = cell.getCellType();
						String cellValue = null;
						switch (cellType) {
							case HSSFCell.CELL_TYPE_STRING:
								System.out.println("[字符串]");
								cellValue = cell.getStringCellValue();
								break;
							case HSSFCell.CELL_TYPE_BOOLEAN:
								System.out.println("[布尔]");
								cellValue = String.valueOf(cell.getBooleanCellValue());
								break;
							case HSSFCell.CELL_TYPE_BLANK:
								System.out.println("[空]");
								break;
							case HSSFCell.CELL_TYPE_NUMERIC://(日期,数字)
								System.out.println("[数字]");

								//是不是日期
								if (HSSFDateUtil.isCellDateFormatted(cell)) {
									Date date = cell.getDateCellValue();
									cellValue = new DateTime(date).toString("yyyy-MM-dd");
								} else {
									// 不是日期防止数字过长
									cell.setCellValue(HSSFCell.CELL_TYPE_STRING);
									cellValue = cell.toString();
								}
								break;
							case HSSFCell.CELL_TYPE_ERROR:
								System.out.println("[数据类型错误]");
								break;
						}
						System.out.println(cellValue);
					}
				}
			}
		}
		fileInputStream.close();
	}
}

Excel 函数

	public void test() throws IOException{
		FileInputStream fileInputStream = new FileInputStream(path + "学员统计表03.xls");
		Workbook workbook = new HSSFWorkbook(fileInputStream);
		Sheet sheetAt = workbook.getSheetAt(0);
		Row row = sheetAt.getRow(0);
		Cell cell = row.getCell(3);

		// 拿到计算公式
	FormulaEvaluator FormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);

	//拿到单元格类型
		int cellType = cell.getCellType();
		switch (cellType){
			case Cell.CELL_TYPE_FORMULA:
				String cellFormula = cell.getCellFormula();
				System.out.println(cellFormula);
				CellValue evaluate = FormulaEvaluator.evaluate(cell);
				String s = evaluate.toString();
				System.out.println(s);
				break;
		}
		fileInputStream.close();
	}