package com.tebon.ams.util;
import lombok.extern.slf4j.Slf4j;
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.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.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;
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;
/**
* @description: ${description}
* @author: dfz
* @create: 2018-12-14
**/
@Slf4j
public class XLSX2CSV {
/**
* Uses the XSSF Event SAX helpers to do most of the work
* of parsing the Sheet XML, and outputs the contents
* as a (basic) CSV.
*/
private List<String[]> rows = new ArrayList<String[]>();
private final OPCPackage xlsxPackage;
/**
* Number of columns to read starting with leftmost
*/
private int[] minColumns;
/**
* Destination for data
*/
private class SheetToCSV implements SheetContentsHandler {
private String[] record;
private int minColumns;
private int thisColumn = 0;
public SheetToCSV(int minColumns) {
super();
this.minColumns = minColumns;
}
@Override
public void startRow(int rowNum) {
record = new String[this.minColumns];
// System.out.println("################################:"+rowNum);
}
@Override
public void endRow(int rowNum) {
thisColumn = 0;
if (!ObjectUtil.isEmpty(this.record) && !ObjectUtil.isEmpty(this.record[0])) {
rows.add(this.record);
}
//一行结束要把此属性置""
preXy = "";
//System.out.println("**********************************");
}
//前一个单元格的xy
private String preXy = "";
//当前单元格的xy
private String currXy = "";
//前一个单元格的x
private String preX = "";
//当前单元格的x
private String currX = "";
//两个不为空的单元格之间隔了多少个空的单元格
private int flag = 0;
@Override
public void cell(String cellReference, String formattedValue, XSSFComment comment) {
//与判断空单元格有关
if ("".equals(preXy)) {
preXy = cellReference;
}
currXy = cellReference;
preX = preXy.replaceAll("\\d", "").trim();
currX = currXy.replaceAll("\\d", "").trim();
int preAscii = 0;
int curAscii = 0;
preAscii = excelColStrToNum(preX, preX.length());
curAscii = excelColStrToNum(currX, currX.length());
flag = curAscii - preAscii;
if (flag != 0 && flag != 1 && flag > 0) {
//isSkipCeil = true;
for (int i = 0; i < (flag - 1); i++) {
//appStr = appStr + ",";
record[thisColumn] = null;
thisColumn++;
}
}
preXy = cellReference;
if (thisColumn < this.minColumns)
record[thisColumn] = formattedValue.trim();
thisColumn++;
}
@Override
public void headerFooter(String text, boolean isHeader, String tagName) {
// Skip, no headers or footers in CSV
}
}
/**
* Creates a new XLSX -> CSV converter
*
* @param pkg The XLSX package to process
* @param minColumns The minimum number of columns to output, or -1 for no minimum
*/
public XLSX2CSV(OPCPackage pkg, int... minColumns) {
this.xlsxPackage = pkg;
this.minColumns = minColumns;
}
/**
* Parses and shows the content of one sheet
* using the specified styles and shared-strings tables.
*
* @param styles
* @param strings
* @param sheetInputStream
*/
public void processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, SheetContentsHandler sheetHandler, InputStream sheetInputStream)
throws IOException, ParserConfigurationException, 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());
}
}
/**
* Initiates the processing of the XLS workbook file to CSV.
*
* @throws IOException
* @throws OpenXML4JException
* @throws ParserConfigurationException
* @throws SAXException
*/
public Map<Integer, List<String[]>> process() throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
Map<Integer, List<String[]>> map = new HashMap<>();
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;
int length = 0;
while (iter.hasNext()) {
InputStream stream = iter.next();
String sheetName = iter.getSheetName();
//this.output.println();
//this.output.println(sheetName + " [index=" + index + "]:");
if (this.minColumns.length < index + 1) {
log.info("请求参数不符合sheet页数...直接退出");
return map;
}
processSheet(styles, strings, new SheetToCSV(this.minColumns[index]), stream);
stream.close();
//map.put(index, this.rows.subList(length, this.rows.size()));
length = length + this.rows.size();
map.put(index, this.rows);
this.rows = new ArrayList<String[]>();
++index;
}
return map;
}
/**
* 得到excel的记录
*
* @param excelPath
* @param minColumns 输出多少列
* @return
* @throws Exception
*/
public static Map<Integer, List<String[]>> getRecords(String excelPath, int... minColumns) throws Exception {
File xlsxFile = new File(excelPath);
if (!xlsxFile.exists()) {
System.err.println("Not found or not a file: " + xlsxFile.getPath());
return null;
}
// The package open is instantaneous, as it should be.
OPCPackage p = OPCPackage.open(xlsxFile.getPath(), PackageAccess.READ);
XLSX2CSV xlsx2csv = new XLSX2CSV(p, minColumns);
Map<Integer, List<String[]>> map = xlsx2csv.process();
p.close();
return map;
}
/**
* 得到excel的记录
*
* @param minColumns 输出多少列
* @return
* @throws Exception
*/
public static Map<Integer, List<String[]>> getRecords(File file, int... minColumns) throws Exception {
OPCPackage p = OPCPackage.open(file);
XLSX2CSV xlsx2csv = new XLSX2CSV(p, minColumns);
Map<Integer, List<String[]>> map = xlsx2csv.process();
p.close();
return map;
}
/**
* Excel列号数字与字母互换
* Excel column index begin 1
*
* @param colStr
* @param length
* @return
*/
public static int excelColStrToNum(String colStr, int length) {
int num = 0;
int result = 0;
for (int i = 0; i < length; i++) {
char ch = colStr.charAt(length - i - 1);
num = (int) (ch - 'A' + 1);
num *= Math.pow(26, i);
result += num;
}
return result;
}
public static void main(String[] args) throws Exception {
Map<Integer, List<String[]>> map = getRecords("C:\\Users\\lenovo\\Desktop\\333.xlsx", 63, 4, 4);
int preAscii = excelColStrToNum("AC", 2);
int curAscii = excelColStrToNum("Z", 1);
System.out.println(preAscii);
System.out.println(curAscii);
}
}
参考文档：https://blog.csdn.net/daiyutage/article/details/53023020