讀取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>
           

列印輸出：