二、可以导入xlsx,是我同事的没仔细看 需要的可以研究下 首先要导入poi 和poi-ooxml的jar包
package com.cmdi.dataloader;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Service;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;
@Service
@Scope("singleton")
public class XlsxImport extends ImportData {
private SharedStringsTable sst;
private String lastContents;
private boolean nextIsString;
private int sheetIndex = -1;
private List<String> rowlist = new ArrayList<String>();
private int curRow = 0;
private int curCol = 0;
private List<List<String>> list = new ArrayList<List<String>>();
private XlsxImport(){
get_import_list().add(this);
}
//private static XlsxImport csvImport=new XlsxImport();
//只遍历一个sheet,其中sheetId为要遍历的sheet索引,从1开始,1-3 并以List<List<String>>类型传出excel的值
public List<List<String>> read(String filePath) {
// 验证文件的合法性
if(!validateExcel(filePath)){
return null;
}
int sheetId = 1;
OPCPackage pkg=null;
try {
pkg = OPCPackage.open(filePath);
} catch (InvalidFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
XSSFReader r=null;
try {
r = new XSSFReader(pkg);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (OpenXML4JException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
SharedStringsTable sst=null;
try {
sst = r.getSharedStringsTable();
} catch (InvalidFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
XMLReader parser=null;
try {
parser = fetchSheetParser(sst);
} catch (SAXException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// rId2 found by processing the Workbook
// 根据 rId# 或 rSheet# 查找sheet
InputStream sheet2=null;
try {
sheet2 = r.getSheet("rId"+sheetId);
} catch (InvalidFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
sheetIndex++;
InputSource sheetSource = new InputSource(sheet2);
try {
parser.parse(sheetSource);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SAXException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
sheet2.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
//excel记录行操作方法,以sheet索引,行索引和行元素列表为参数,对sheet的一行元素进行操作,元素为String类型 将每行数据放进tempList;
public void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException{
List<String> tempList = new ArrayList<String>();
// 如果当前行不是第一行
if(curRow > 0){
for (int i = 0; i < rowlist.size(); i++) {
tempList.add(rowlist.get(i));
}
list.add(tempList);
}
}
/**
* 遍历 excel 文件
*/
public void process(String filename) throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
Iterator<InputStream> sheets = r.getSheetsData();
while (sheets.hasNext()) {
curRow = 0;
sheetIndex++;
InputStream sheet = sheets.next();
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
sheet.close();
}
}
public XMLReader fetchSheetParser(SharedStringsTable sst)
throws SAXException {
XMLReader parser = XMLReaderFactory
.createXMLReader("org.apache.xerces.parsers.SAXParser");
this.sst = sst;
parser.setContentHandler(this);
return parser;
}
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
// c => 单元格
if (name.equals("c")) {
// 如果下一个元素是 SST 的索引,则将nextIsString标记为true
String cellType = attributes.getValue("t");
if (cellType != null && cellType.equals("s")) {
nextIsString = true;
} else {
nextIsString = false;
}
}
// 置空
lastContents = "";
}
public void endElement(String uri, String localName, String name)
throws SAXException {
// 根据SST的索引值的到单元格的真正要存储的字符串
// 这时characters()方法可能会被调用多次
if (nextIsString) {
try {
int idx = Integer.parseInt(lastContents);
lastContents = new XSSFRichTextString(sst.getEntryAt(idx))
.toString();
} catch (Exception e) {
}
}
// v => 单元格的值,如果单元格是字符串则v标签的值为该字符串在SST中的索引
// 将单元格内容加入rowlist中,在这之前先去掉字符串前后的空白符
if (name.equals("v")) {
String value = lastContents.trim();
value = value.equals("")?" ":value;
rowlist.add(curCol, value);
curCol++;
}else {
//如果标签名称为 row ,这说明已到行尾,调用 optRows() 方法
if (name.equals("row")) {
try {
optRows(sheetIndex,curRow,rowlist);
} catch (SQLException e) {
e.printStackTrace();
}
rowlist.clear();
curRow++;
curCol = 0;
}
}
}
public void characters(char[] ch, int start, int length)
throws SAXException {
//得到单元格内容的值
lastContents += new String(ch, start, length);
}
public boolean validateExcel(String filePath) {
// TODO Auto-generated method stub
if (filePath == null
|| !(isExcel2007(filePath))) {
return false;
}
/** 检查文件是否存在 */
File file = new File(filePath);
if (file == null || !file.exists()) {
return false;
}
return true;
}
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
}