Java使用POI导入导出Excel整理

Java 使用POI导入Excel

1.程序会自动读取Excel的每个Sheet，放入Map的key中。
Map<String, List<Map<String, String>>> maps = readExcel(0, 1, path);
2.每个Sheet表格的数据会放入List<Map<String, String>>，Key为列名。

//所需的Jar包
poi-3.17.jar
poi-ooxml-3.17.jar
xmlbeans-3.1.0.jar
commons-collections4-4.3.jar
poi-ooxml-schemas-3.17.jar

package com.util;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
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.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtils2 {

	public static final String EXCEL_2003 = "xls";
	public static final String EXCEL_2010 = "xlsx";

	public static void main(String[] args) {
		// 导入
		String path = "C:/Users/Administrator/Desktop/报名号.xlsx";
		Map<String, List<Map<String, String>>> maps = readExcel(0, 1, path);
		// List<Map<String, String>> list = map.get("Sheet1");
		for (Map.Entry<String, List<Map<String, String>>> entry : maps.entrySet()) {
			//TODO
			String sheetName = entry.getKey();
			for (Map<String, String> map : entry.getValue()) {
				System.out.println(map.get("工号"));
				System.out.println(map.get("教师姓名"));
			}
		}
		
		//导出
		// 导出Excel的列头
		List<String> titles = Arrays.asList("工号", "教师姓名");
		// 导出Excel的数据源
		List<Map<String, String>> values = new ArrayList<>();
		Map<String, String> map = new LinkedHashMap<>();
		map.put("工号", "123");
		map.put("教师姓名", "张三");
		values.add(map);
		ExcelUtils2.exportExcel("Sheet1", "C:\\导出.xlsx", titles, values);
	}

	/**
	 * 默认第一行为表头
	 * 
	 * @param path        路径
	 * @param headerStart 表头从第几行开始
	 * @param dataStart   数据从第几行开始
	 * @return Map<sheet1,List<Map<列名, 列值>>>
	 * @throws IOException
	 */
	public static Map<String, List<Map<String, String>>> readExcel(int headerStart, int dataStart, String path) {
		try {
			String postfix = getPostfix(path);
			if (EXCEL_2003.equals(postfix)) {
				return readXls2(headerStart, dataStart, path);
			} else if (EXCEL_2010.equals(postfix)) {
				return readXlsx2(headerStart, dataStart, path);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * Read the Excel 2010 Map<String,List<Map<String, String>>>
	 * Map<sheet1,List<Map<列名, 列值>>>
	 * 
	 * @throws Exception
	 */
	private static Map<String, List<Map<String, String>>> readXlsx(int headerStart, int dataStart, String path)
			throws IOException {
		InputStream is = new FileInputStream(path);
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
		// 全部数据
		Map<String, List<Map<String, String>>> dataMap = new HashMap<>();
		// 表头
		Map<Integer, String> titleMap = null;
		// 表的一行数据
		Map<String, String> map = null;
		// Read the Sheet
		for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
			List<Map<String, String>> list = new ArrayList<>();

			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
			if (xssfSheet == null) {
				continue;
			}

			// 把第0行昨为表头
			XSSFRow titleRow = xssfSheet.getRow(headerStart);
			if (titleRow == null) {
				continue;
			}
			titleMap = new HashMap<>();
			int cellCounter = 0;
			while (true) {
				XSSFCell cell = titleRow.getCell(cellCounter);
				String title = getValue(cell);
				if (StringUtils.isEmpty(title)) {
					break;
				}
				titleMap.put(cellCounter, title);
				cellCounter++;
			}
			// 从第startRow行读数据
			for (int rowNum = dataStart; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				XSSFRow xssfRow = xssfSheet.getRow(rowNum);
				if (xssfRow == null) {
					continue;
				}
				map = new HashMap<>();
				for (Map.Entry<Integer, String> entry : titleMap.entrySet()) {
					XSSFCell cell = xssfRow.getCell(entry.getKey());
					map.put(entry.getValue(), getValue(cell));
				}
				if (isNull(map)) {
					break;
				}
				list.add(map);
			}

			String sheetName = xssfSheet.getSheetName();
			dataMap.put(sheetName, list);
		}
		return dataMap;
	}

	/**
	 * Read the Excel 2010 Map<String,List<Map<String, String>>>
	 * Map<sheet1,List<Map<列名, 列值>>>
	 * 
	 * @throws Exception
	 */
	private static Map<String, List<Map<String, String>>> readXlsx2(int headerStart, int dataStart, String path)
			throws IOException {
		InputStream is = new FileInputStream(path);
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
		// 全部数据
		LinkedHashMap<String, List<Map<String, String>>> dataMap = new LinkedHashMap<>();
		// 表头
		LinkedHashMap<Integer, String> titleMap = null;
		// 表的一行数据
		LinkedHashMap<String, String> map = null;
		// Read the Sheet
		for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
			List<Map<String, String>> list = new ArrayList<>();

			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
			if (xssfSheet == null) {
				continue;
			}

			// 把第0行昨为表头
			XSSFRow titleRow = xssfSheet.getRow(headerStart);
			if (titleRow == null) {
				continue;
			}
			titleMap = new LinkedHashMap<>();
			int cellCounter = 0;
			while (true) {
				XSSFCell cell = titleRow.getCell(cellCounter);
				String title = getValue(cell);
				if (StringUtils.isEmpty(title)) {
					break;
				}
				titleMap.put(cellCounter, title);
				cellCounter++;
			}
			// 从第startRow行读数据
			for (int rowNum = dataStart; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				XSSFRow xssfRow = xssfSheet.getRow(rowNum);
				if (xssfRow == null) {
					continue;
				}
				map = new LinkedHashMap<>();
				for (Map.Entry<Integer, String> entry : titleMap.entrySet()) {
					XSSFCell cell = xssfRow.getCell(entry.getKey());
					map.put(entry.getValue(), getValue(cell));
				}

				if (isNull(map)) {
					break;
				}

				list.add(map);
			}

			String sheetName = xssfSheet.getSheetName();
			dataMap.put(sheetName, list);
		}
		return dataMap;
	}

	/**
	 * Read the Excel 2003-2007 Map<String,List<Map<String, String>>>
	 * Map<sheet1,List<Map<列名, 列值>>>
	 */
	private static Map<String, List<Map<String, String>>> readXls(int headerStart, int dataStart, String path)
			throws IOException {
		InputStream is = new FileInputStream(path);
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
		// 全部数据
		Map<String, List<Map<String, String>>> dataMap = new HashMap<>();
		// 表头
		Map<Integer, String> titleMap = null;
		// 表的一行数据
		Map<String, String> map = null;
		// Read the Sheet
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			List<Map<String, String>> list = new ArrayList<>();
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}
			// 把第0行昨为表头
			HSSFRow titleRow = hssfSheet.getRow(headerStart);
			if (titleRow == null) {
				continue;
			}
			titleMap = new HashMap<>();
			int cellCounter = 0;
			while (true) {
				HSSFCell cell = titleRow.getCell(cellCounter);
				String title = getValue(cell);
				if (StringUtils.isEmpty(title)) {
					break;
				}
				titleMap.put(cellCounter, title);
				cellCounter++;
			}

			// 从第startRow行读数据
			for (int rowNum = dataStart; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow == null) {
					continue;
				}
				map = new HashMap<>();
				for (Map.Entry<Integer, String> entry : titleMap.entrySet()) {
					HSSFCell cell = hssfRow.getCell(entry.getKey());
					map.put(entry.getValue(), getValue(cell));
				}
				if (isNull(map)) {
					break;
				}
				list.add(map);
			}

			String sheetName = hssfSheet.getSheetName();
			dataMap.put(sheetName, list);
		}
		return dataMap;
	}

	/**
	 * Read the Excel 2003-2007 Map<String,List<Map<String, String>>>
	 * Map<sheet1,List<Map<列名, 列值>>>
	 */
	private static Map<String, List<Map<String, String>>> readXls2(int headerStart, int dataStart, String path)
			throws IOException {
		InputStream is = new FileInputStream(path);
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
		// 全部数据
		LinkedHashMap<String, List<Map<String, String>>> dataMap = new LinkedHashMap<>();
		// 表头
		LinkedHashMap<Integer, String> titleMap = null;
		// 表的一行数据
		LinkedHashMap<String, String> map = null;
		// Read the Sheet
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			List<Map<String, String>> list = new ArrayList<>();
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}
			// 把第0行昨为表头
			HSSFRow titleRow = hssfSheet.getRow(headerStart);
			if (titleRow == null) {
				continue;
			}
			titleMap = new LinkedHashMap<>();
			int cellCounter = 0;
			while (true) {
				HSSFCell cell = titleRow.getCell(cellCounter);
				String title = getValue(cell);
				if (StringUtils.isEmpty(title)) {
					break;
				}
				titleMap.put(cellCounter, title);
				cellCounter++;
			}

			// 从第startRow行读数据
			for (int rowNum = dataStart; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow == null) {
					continue;
				}
				map = new LinkedHashMap<>();
				for (Map.Entry<Integer, String> entry : titleMap.entrySet()) {
					HSSFCell cell = hssfRow.getCell(entry.getKey());
					map.put(entry.getValue(), getValue(cell));
				}
				if (isNull(map)) {
					break;
				}
				list.add(map);
			}

			String sheetName = hssfSheet.getSheetName();
			dataMap.put(sheetName, list);
		}
		return dataMap;
	}

	@SuppressWarnings("static-access")
	private static String getValue(XSSFCell xssfRow) {
		if (xssfRow == null)
			return "";
		try {
			if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
				return String.valueOf(xssfRow.getBooleanCellValue());
			} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
				if (HSSFDateUtil.isCellDateFormatted(xssfRow)) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					String date = sdf.format(HSSFDateUtil.getJavaDate(xssfRow.getNumericCellValue()));
					return date;
				} else {
					BigDecimal bigDecimal = new BigDecimal(xssfRow.getNumericCellValue());
					String result = bigDecimal.toString();
					return result;
				}
			} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_FORMULA) {
				return xssfRow.getCellFormula();
			} else {
				return String.valueOf(xssfRow.getStringCellValue());
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	@SuppressWarnings("static-access")
	private static String getValue(HSSFCell hssfCell) {
		if (hssfCell == null)
			return "";
		try {
			if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
				return String.valueOf(hssfCell.getBooleanCellValue());
			} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
				if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					return sdf.format(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue()));
				} else {
					BigDecimal bigDecimal = new BigDecimal(hssfCell.getNumericCellValue());
					String result = bigDecimal.toString();
					return result;
				}
			} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_FORMULA) {
				return hssfCell.getCellFormula();
			} else {
				return String.valueOf(hssfCell.getStringCellValue());
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	private static String getPostfix(String path) {
		if (StringUtils.isEmpty(path)) {
			return "";
		}
		return path.substring(path.lastIndexOf(".") + 1, path.length());
	}

	/**
	 * 导出Excel到浏览器
	 * 
	 * @param response
	 * @param sheetName
	 * @param fileName  文件名
	 * @param titles
	 * @param values
	 */
	public static void exportExcel(HttpServletResponse response, String sheetName, String fileName, List<String> titles,
			List<Map<String, String>> values) {
		XSSFWorkbook wb = setHSSFWorkbook(sheetName, titles, values, null);

		// 将文件存到指定位置
		try {
			setResponseHeader(response, fileName);
			OutputStream os = response.getOutputStream();
			wb.write(os);
			os.flush();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 导出Excel到磁盘
	 * 
	 * @param sheetName
	 * @param filePath  绝对路径
	 * @param titles
	 * @param values
	 * @param colors
	 */
	public static void exportExcel(String sheetName, String filePath, List<String> titles,
			List<Map<String, String>> values) {
		XSSFWorkbook wb = setHSSFWorkbook(sheetName, titles, values, null);

		// 将文件存到指定位置
		FileOutputStream out = null;
		BufferedOutputStream buff = null;
		try {
			out = new FileOutputStream(new File(filePath));
			buff = new BufferedOutputStream(out);
			wb.write(buff);
			buff.flush();
			out.flush();
			buff.close();
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private static void setResponseHeader(HttpServletResponse response, String fileName) {
		try {
			try {
				fileName = new String(fileName.getBytes(), "ISO8859-1");
			} catch (Exception e) {
				e.printStackTrace();
			}
			response.setContentType("application/octet-stream;charset=ISO8859-1");
			response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
			response.addHeader("Pargam", "no-cache");
			response.addHeader("Cache-Control", "no-cache");
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	/**
	 * 写入Excel
	 * 
	 * @param sheetName
	 * @param title
	 * @param values    key 对应title中的列名
	 * @param wb
	 * @return
	 */
	private static XSSFWorkbook setHSSFWorkbook(String sheetName, List<String> title, List<Map<String, String>> values,
			XSSFWorkbook wb) {
		// 第一步，创建一个webbook，对应一个Excel文件
		if (wb == null) {
			wb = new XSSFWorkbook();
		}
		// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
		XSSFSheet sheet = wb.createSheet(sheetName);
		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
		XSSFRow row = sheet.createRow(0);
		// 第四步，创建单元格，并设置值表头 设置表头居中
		XSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式

		XSSFCell cell = null;
		// 创建标题
		for (int i = 0; i < title.size(); i++) {
			cell = row.createCell(i);
			cell.setCellValue(title.get(i));
			cell.setCellStyle(style);
		}

		// 创建内容
		for (int i = 0; i < values.size(); i++) {
			row = sheet.createRow(i + 1);

			for (int j = 0; j < title.size(); j++) {
				// 字段
				String field = title.get(j);
				// 值
				Map<String, String> model = values.get(i);
				String valstr = model.get(field);
				row.createCell(j).setCellValue(valstr);
			}
		}
		return wb;
	}

	private static boolean isNull(Map<String, String> map) {
		int size = map.keySet().size();
		int counter = 0;
		for (Map.Entry<String, String> entry : map.entrySet()) {
			if (StringUtils.isBlank(entry.getValue()))
				counter++;
		}
		return size == counter;
	}
}