pom
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.nihaorz</groupId>
<artifactId>XLSXDemo</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>
</project>
XLSX2Data.java
package com.nihaorz;
import org.apache.log4j.Logger;
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.openxml4j.opc.PackageAccess;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.util.SAXHelper;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import javax.xml.parsers.ParserConfigurationException;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* Created by Nihaorz on 2017/8/4.
*/
public class XLSX2Data {
private Logger logger = Logger.getLogger(XLSX2Data.class);
private File xlsxFile;
private final String NULL_FILE_MSG = "文件对象为null或者文件不存在,请检查文件对象!";
private OPCPackage xlsxPackage;
private List<String> xlsxTitles;
private List<Map<String, String>> xlsxData;
public XLSX2Data(File xlsxFile) {
this.xlsxFile = xlsxFile;
if(check()) {
xlsxTitles = new ArrayList();
xlsxData = new ArrayList();
try {
this.xlsxPackage = OPCPackage.open(this.xlsxFile.getPath(), PackageAccess.READ);
try {
process();
xlsxData.remove(0);
} catch (IOException e) {
logger.error(e.getMessage(), e);
} catch (SAXException e) {
logger.error(e.getMessage(), e);
} catch (OpenXML4JException e) {
logger.error(e.getMessage(), e);
}
try {
this.xlsxPackage.close();
} catch (IOException e) {
logger.error(e.getMessage(), e);
}
} catch (InvalidFormatException e) {
logger.error(e.getMessage(), e);
}
}else {
logger.error(NULL_FILE_MSG);
}
}
private void process() throws IOException, SAXException, OpenXML4JException {
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(this.xlsxPackage);
XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
StylesTable styles = xssfReader.getStylesTable();
XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
int index = 0;
while (iter.hasNext()) {
InputStream stream = iter.next();
processSheet(styles, strings, new XLSX2Data.SheetToCSV(), stream);
stream.close();
++index;
}
}
public void processSheet(
StylesTable styles,
ReadOnlySharedStringsTable strings,
XSSFSheetXMLHandler.SheetContentsHandler sheetHandler,
InputStream sheetInputStream) throws IOException, SAXException {
DataFormatter formatter = new DataFormatter();
InputSource sheetSource = new InputSource(sheetInputStream);
try {
XMLReader sheetParser = SAXHelper.newXMLReader();
ContentHandler handler = new XSSFSheetXMLHandler(
styles, null, strings, sheetHandler, formatter, false);
sheetParser.setContentHandler(handler);
sheetParser.parse(sheetSource);
} catch(ParserConfigurationException e) {
throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
}
}
private class SheetToCSV implements XSSFSheetXMLHandler.SheetContentsHandler {
private boolean firstCellOfRow = false;
private int currentRow = -1;
private int currentCol = -1;
private void outputMissingRows(int number) {
}
@Override
public void startRow(int rowNum) {
// If there were gaps, output the missing rows
outputMissingRows(rowNum-currentRow-1);
// Prepare for this row
firstCellOfRow = true;
currentRow = rowNum;
currentCol = -1;
xlsxData.add(currentRow, new HashMap());
}
@Override
public void endRow(int rowNum) {
// Ensure the minimum number of columns
}
@Override
public void cell(String cellReference, String formattedValue,
XSSFComment comment) {
if (firstCellOfRow) {
firstCellOfRow = false;
}
// gracefully handle missing CellRef here in a similar way as XSSFCell does
if(cellReference == null) {
cellReference = new CellAddress(currentRow, currentCol).formatAsString();
}
// Did we miss any cells?
int thisCol = (new CellReference(cellReference)).getCol();
currentCol = thisCol;
String regEx = "[a-zA-Z]{1,}[1]";
Pattern pattern = Pattern.compile(regEx);
Matcher matcher = pattern.matcher(cellReference);
boolean rs = matcher.matches();
if(rs){
xlsxTitles.add(formattedValue);
}else{
xlsxData.get(currentRow).put("col" + currentCol, formattedValue);
}
}
@Override
public void headerFooter(String text, boolean isHeader, String tagName) {
// Skip, no headers or footers in CSV
}
}
public String[] getXlsxTitles(){
if(check()) {
return xlsxTitles.toArray(new String[xlsxTitles.size()]);
}else {
logger.error(NULL_FILE_MSG);
return null;
}
}
public List<Map<String, String>> getXlsxData(){
if(check()) {
return xlsxData;
}else {
logger.error(NULL_FILE_MSG);
return null;
}
}
public File getXlsxFile() {
return xlsxFile;
}
private boolean check(){
boolean result = false;
if(xlsxFile != null && xlsxFile.exists()) {
result = true;
}
return result;
}
}
XLSX2DataTest.java
package com.nihaorz;
import java.io.File;
import java.util.List;
/**
* Created by Nihaorz on 2017/8/4.
*/
public class XLSX2DataTest {
public static void main(String[] args) {
File file = new File("C:\\Users\\Nihaorz\\Desktop\\人员表.xlsx");
XLSX2Data x2d = new XLSX2Data(file);
String[] titles = x2d.getXlsxTitles();
List data = x2d.getXlsxData();
System.out.println("titles:" + titles);
System.out.println("data:" + data);
}
}
XLSX2Data