POI导入导出Excel到数据库

前面写了一篇： JXL导入导出Excel到数据库

今天再补充一个代码，用POI写的导入导出，前面的代码可以重用，这里只写POI的工具类

需要用到的第三方jar：poi-3.11-20141221.jar这个jar可以用来处理word、excel、ppt，能处理03/07的版本，上一篇文章中的jxl只能处理03版本的excel，03版本的excel最大行数为65535

PoiUtils.java，功能是导入导出excel到list集合，另外两个是测试方法

package com.example.Utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.junit.Test;

public class PoiUtils {
	@Test
	public void testpoiListToExl() throws Exception {
		List<Map> tableContent = new ArrayList<Map>();
		Map rowData1 = new LinkedHashMap();
		rowData1.put("id", "11111");
		rowData1.put("name", "aaaa");
		tableContent.add(rowData1);
		Map rowData2 = new LinkedHashMap();
		rowData2.put("id", "22222");
		rowData2.put("name", "bbbb");
		tableContent.add(rowData2);
		String outPutFileName = "\\testout.xls";
		poiListToExl(tableContent, outPutFileName);

	}

	@Test
	public void testpoiExlToList() throws Exception {
		String inPutFileName = "\\testout.xls";
		List<Map> list = poiExlToList(inPutFileName);
		System.out.println(list);
	}

	public static void poiListToExl(List<Map> tableContent,
			String outPutFileName) {
		// WritableWorkbook book = null;
		HSSFWorkbook workbook = null;
		try {
			File file = new File(System.getProperty("user.dir")
					+ outPutFileName);
			if (!file.exists()) {
				// 如果指定文件不存在，则新建该文件
				file.createNewFile();
			}

			FileOutputStream fos = new FileOutputStream(file);
			workbook = new HSSFWorkbook();
			Sheet sheet = workbook.createSheet("sheet1");

			List<String> tableHeader = new ArrayList<String>();
			if (tableContent.size() >= 1) {
				Map map = tableContent.get(0);
				Set keySet = map.keySet();
				for (Object keyName : keySet) {
					tableHeader.add(keyName.toString());
					System.out.println(keyName);
				}
			} else {
				return;
			}

			// 第一行写入表头
			CreationHelper createHelper = workbook.getCreationHelper();
			Row row = sheet.createRow((short) 0);
			for (int i = 0; i < tableHeader.size(); i++) {
				row.createCell(i).setCellValue(
						createHelper.createRichTextString(tableHeader.get(i)));
			}

			// 后续行写入数据
			for (int i = 0; i < tableContent.size(); i++) {
				Map map = tableContent.get(i);
				row = sheet.createRow(i + 1);
				for (int j = 0; j < tableHeader.size(); j++) {
					System.out.println(map.get(tableHeader.get(j)));
					row.createCell(j).setCellValue(
							createHelper.createRichTextString(map.get(
									tableHeader.get(j)).toString()));

				}

			}

			workbook.write(fos);
			System.out.println("工作簿写入数据成功！");
			workbook.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static List<Map> poiExlToList(String inPutFileName) {
		HSSFWorkbook workbook = null;
		List<Map> list = null;
		try {
			File file = new File(System.getProperty("user.dir") + inPutFileName);
			if (!file.exists()) {
				// 如果指定文件不存在，则新建该文件
				file.createNewFile();
			}

			FileInputStream fis = new FileInputStream(file);
			NPOIFSFileSystem fs = new NPOIFSFileSystem(fis);
			workbook = new HSSFWorkbook(fs.getRoot(), false);
			Sheet sheet = workbook.getSheetAt(0);
			int totalRows = sheet.getLastRowNum()+1;
			int totalColumns = sheet.getRow(0).getPhysicalNumberOfCells();
			System.out.println("行数："+totalRows);
			System.out.println("列数："+totalColumns);
			Row row = sheet.getRow(0);
			if (totalColumns <= 0) {
				return null;
			}
			// 读取第一行作为Map中的key
			List tableHeaderlist = new ArrayList();
			for (int i = 0; i < totalColumns; i++) {
				tableHeaderlist.add(row.getCell(i).getRichStringCellValue()
						.getString());
			}

			// 将每一行存为Map集合，然后存为list
			list = new ArrayList();
			Map rowData = new LinkedHashMap();
			for (int i = 1; i < totalRows; i++) {
				row = sheet.getRow(i);
				rowData = new LinkedHashMap(totalColumns);
				for (int j = 0; j < totalColumns; j++) {
					rowData.put(tableHeaderlist.get(j), row.getCell(j)
							.getRichStringCellValue().getString());
				}
				list.add(rowData);
			}

			System.out.println("工作簿读取数据成功！");

			workbook.close();// 关闭
		} catch (Exception e) {
			e.printStackTrace();
		}

		return list;
	}

}