读取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>
打印输出: