读取excel表格进行数据库数据的插入

读取excel表格的工具类

package com.imooc.utils;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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;

/**
 * 
  * @ClassName: ExcelUtil 
  * @Description: 对Excel进行读,写
  * @date 2018年5月16日 下午2:36:13 
  *
 */
public class ExcelUtil {
	private static XSSFWorkbook xb;
	private static XSSFSheet xs;
	private static XSSFRow xr;

	/**
	 * 读取Excel表格表头的内容
	 * @param is
	 * @return 表头内容的数组String类型
	 */
	public static String[] readExcelTitle(InputStream is) {
		try {
			xb = new XSSFWorkbook(is);
		} catch (IOException e) {
			e.printStackTrace();
		}
		// Sheet工作表
		xs = xb.getSheetAt(0);
		// 获取首行标题
		xr = xs.getRow(0);
		// 标题总列数
		int colNum = xr.getPhysicalNumberOfCells();
		String[] title = new String[colNum];
		for (int i = 0; i < colNum; i++) {
			title[i] = getCellFormatValue(xr.getCell((short) i));
		}
		return title;
	}

	/**
	 * 根据XSSFCell类型设置数据
	 * @param xssfCell
	 * @return
	 */
	private static String getCellFormatValue(XSSFCell xssfCell) {
		String cellvalue = "";
		if (xssfCell != null) {
			// 判断当前Cell的Type
			switch (xssfCell.getCellType()) {
			// 如果当前Cell的Type为NUMERIC
			case XSSFCell.CELL_TYPE_NUMERIC:
			case XSSFCell.CELL_TYPE_FORMULA: {
				// 判断当前的cell是否为Date
				// 如果是Date类型则，转化为Data格式
				if (HSSFDateUtil.isCellDateFormatted(xssfCell)) {
					// 方法1：data格式是带时分秒的：2011-10-12 0:00:00
					// 方法2：格式是不带带时分秒的：2011-10-12
					Date date = xssfCell.getDateCellValue();
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					cellvalue = sdf.format(date);
				} else {
					// 如果是纯数字,取得当前Cell的数值
					DecimalFormat df = new DecimalFormat("0");
					cellvalue = df.format(xssfCell.getNumericCellValue());
				}
				break;
			}
			// 如果当前Cell的Type为STRING
			case XSSFCell.CELL_TYPE_STRING:
				// 取得当前的Cell字符串
				cellvalue = xssfCell.getRichStringCellValue().getString();
				break;
			default:
				// 默认的Cell值
				cellvalue = " ";
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;
	}

	/**
	 * 读取Excel数据内容(资源管理导入专用)
	 * @return
	 */
	public static List<Map> readExcelContent(InputStream is,int[] columns,String[] title) {
		try {
			xb = new XSSFWorkbook(is);
		} catch (IOException e) {
			e.printStackTrace();
		}
		List<Map> list = new ArrayList<Map>();
		// Sheet工作表
		xs = xb.getSheetAt(0);
		// 总行数
		int rowNum = xs.getLastRowNum();
		// 获取标题
		if (title != null && title.length > 0) {
			String str = null;
			// 总列数
			int colNum = xr.getPhysicalNumberOfCells();
			Map<String, String> content = null;
			// 正文内容应该从第二行开始,第一行为表头的标题
			for (int i = 1; i <= rowNum; i++) {
				content = new HashMap<String, String>();
				xr = xs.getRow(i);
				for (int index : columns) {
					str = getCellFormatValue(xr.getCell((short) index)).trim();
					content.put(title[index], str);
				}
				list.add(content);
			}
		}
		return list;
	}
}




/*
 *
 *以下为测试方法
 *
 */

package com.imooc.test;

import com.imooc.utils.ExcelUtil;


import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.List;
import java.util.Map;


public class ExcelUtilTest {

	public static void main(String[] args) throws Exception {
        readExcelTitleTest();//测试表头信息
        readExcelContentTest();//进行sql语句打印
	}
	public static void readExcelTitleTest() throws Exception{
		File file = new File("F:/test.xlsx");
		InputStream is = new FileInputStream(file);
		String[] title = ExcelUtil.readExcelTitle(is);
		for (String string : title) {
			System.out.println(string);
		}
	}

	public static   void readExcelContentTest() throws Exception{
		File file = new File("F:/test.xlsx");
		String[] title = ExcelUtil.readExcelTitle(new FileInputStream(file));
		List<Map> list = ExcelUtil.readExcelContent(new FileInputStream(file), new int[]{0,1,2},title);
		for (Map map : list) {
            System.out.println("insert into user(name,age,score) values"+"("+map.get(title[0])+","+map.get(title[1])+","+map.get(title[2])+");");
		}
	}
}
           

需要导入的jar包：具体我也不知道要哪个包，程序能跑，请见谅，自己经常用。

<!--处理2003 excel-->
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.16</version>
</dependency>
<!--处理2007 excel-->
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>3.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-collections4 -->
<dependency>
   <groupId>org.apache.commons</groupId>
   <artifactId>commons-collections4</artifactId>
   <version>4.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans -->
<dependency>
   <groupId>org.apache.xmlbeans</groupId>
   <artifactId>xmlbeans</artifactId>
   <version>2.6.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.17-beta1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml-schemas</artifactId>
   <version>3.17</version>
</dependency>

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.17-beta1</version>
</dependency>
           

打印输出：