Excel工具类
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
public class ExcelPOIUtil {
private final static String EXCEL03_XLS_EXTENSION = ".xls";
private final static String EXCEL07_XLSX_EXTENSION = ".xlsx";
private final static String EXCEL07_XLSM_EXTENSION = ".xlsm";
public ExcelPOIUtil() {
}
/**
* 检查excel文件
* 限制最大行数和列数，防止无限行或列导致的内存溢出
* @param file
* @throws Exception
*/
public static void checkExcel(File file) throws Exception {
String fileName = file.getName().toLowerCase();
try (InputStream inputStream = new FileInputStream(file)) {
if (fileName.endsWith(EXCEL03_XLS_EXTENSION)) {
ExcelXlsReader excelReader = new ExcelXlsReader();
excelReader.process(inputStream);
} else if (fileName.endsWith(EXCEL07_XLSX_EXTENSION) || fileName.endsWith(EXCEL07_XLSM_EXTENSION)) {
ExcelXlsxReader excelReader = new ExcelXlsxReader();
excelReader.process(inputStream);
} else {
throw new Exception("不支持的格式！");
}
}
}
/**
* 获取行
* @param sheet
* @param rowIndex
* @return
*/
public static Row getRow(Sheet sheet, int rowIndex) {
Row row = sheet.getRow(rowIndex);
if (row == null) {
row = sheet.createRow(rowIndex);
}
return row;
}
/**
* 获取单元格
* @param row
* @param cellIndex
* @return
*/
public static Cell getCell(Row row, int cellIndex) {
Cell cell = row.getCell(cellIndex);
if (cell == null) {
cell = row.createCell(cellIndex);
}
return cell;
}
/**
* 获取单元格的值
* @param cell
* @return
* @throws Exception
*/
public static String getCellValue(Cell cell) throws Exception {
String cellVal = "";
if (cell == null) {
return cellVal;
}
int cellType = cell.getCellType();
switch (cellType) {
case Cell.CELL_TYPE_STRING:
cellVal = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BLANK:
break;
case Cell.CELL_TYPE_NUMERIC:
if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd hh:mm:ss");
cellVal = sdf.format(cell.getDateCellValue());
} else {
HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
cellVal = dataFormatter.formatCellValue(cell);
}
break;
case Cell.CELL_TYPE_BOOLEAN:
cellVal = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
cell.setCellType(Cell.CELL_TYPE_STRING);
cellVal = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_ERROR:
default:
throw new Exception("单元格格式异常！");
}
return cellVal.trim();
}
/**
* 获取合并单元格
* @param sheet
* @param rowIndex
* @param cellIndex
* @return
*/
private static CellRangeAddress getMergedRegion(Sheet sheet, int rowIndex, int cellIndex) {
int firstRow = -1;
int lastRow = -1;
int firstColumn = -1;
int lastColumn = -1;
CellRangeAddress cellRangeAddress = null;
int numMergedRegions = sheet.getNumMergedRegions();
boolean isMergedRegion = false;
for (int i = 0; i < numMergedRegions; i++) {
cellRangeAddress = sheet.getMergedRegion(i);
firstRow = cellRangeAddress.getFirstRow();
lastRow = cellRangeAddress.getLastRow();
if (firstRow <= rowIndex && lastRow >= rowIndex) {
firstColumn = cellRangeAddress.getFirstColumn();
lastColumn = cellRangeAddress.getLastColumn();
if (firstColumn <= cellIndex && lastColumn >= cellIndex) {
isMergedRegion = true;
break;
}
}
}
return isMergedRegion ? cellRangeAddress : null;
}
/**
* 判断合并行
* @param cellRangeAddress
* @return
*/
public static boolean isMergedRow(CellRangeAddress cellRangeAddress) {
int firstColumn = cellRangeAddress.getFirstColumn();
int lastColumn = cellRangeAddress.getLastColumn();
return firstColumn == lastColumn;
}
/**
* 判断合并列
* @param cellRangeAddress
* @return
*/
public static boolean isMergedColumn(CellRangeAddress cellRangeAddress) {
int firstRow = cellRangeAddress.getFirstRow();
int lastRow = cellRangeAddress.getLastRow();
return firstRow == lastRow;
}
/**
* 获取合并单元格的值
* @param sheet
* @param cellRangeAddress
* @return
* @throws Exception
*/
public static String getMergedRegionVal(Sheet sheet,CellRangeAddress cellRangeAddress) throws Exception {
int firstRow = cellRangeAddress.getFirstRow();
int firstColumn = cellRangeAddress.getFirstColumn();
Row row = getRow(sheet, firstRow);
Cell cell = getCell(row, firstColumn);
return getCellValue(cell);
}
}
Excel2003获取最大行数和列数
import org.apache.poi.hssf.eventusermodel.*;
import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;
import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;
import org.apache.poi.hssf.record.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import java.io.InputStream;
public class ExcelXlsReader implements HSSFListener {
private static Integer MAX_ROW_NUMBER = 20000;
private static Integer MAX_COL_NUMBER = 100;
private Integer maxRowNumber = 0;
private Integer maxColNumber = 0;
private int totalRows = 0;
private int cellNum = 0;
public ExcelXlsReader() {
}
public static Integer getMaxRowNumber() {
return MAX_ROW_NUMBER;
}
public static Integer getMaxColNumber() {
return MAX_COL_NUMBER;
}
@Override
public void processRecord(Record record) {
switch (record.getSid()) {
case BOFRecord.sid:
BOFRecord br = (BOFRecord) record;
if (br.getType() == BOFRecord.TYPE_WORKSHEET) {
maxRowNumber = Math.max(maxRowNumber, totalRows);
totalRows = 0;
}
break;
case BlankRecord.sid:
case BoolErrRecord.sid:
case FormulaRecord.sid:
case StringRecord.sid:
case LabelRecord.sid:
case LabelSSTRecord.sid:
case NumberRecord.sid:
cellNum++;
break;
case BoundSheetRecord.sid:
case SSTRecord.sid:
default:
break;
}
if (record instanceof MissingCellDummyRecord) {
cellNum++;
}
if (record instanceof LastCellOfRowDummyRecord) {
totalRows++;
maxRowNumber = Math.max(maxRowNumber, totalRows);
maxColNumber = Math.max(maxColNumber, cellNum);
cellNum = 0;
}
}
public void process(InputStream inputStream) throws Exception {
MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this);
FormatTrackingHSSFListener formatListener = new FormatTrackingHSSFListener(listener);
HSSFEventFactory factory = new HSSFEventFactory();
HSSFRequest request = new HSSFRequest();
EventWorkbookBuilder.SheetRecordCollectingListener workbookBuildingListener = new EventWorkbookBuilder.SheetRecordCollectingListener(formatListener);
request.addListenerForAllRecords(workbookBuildingListener);
factory.processWorkbookEvents(request, new POIFSFileSystem(inputStream));
if (maxColNumber > MAX_COL_NUMBER || MAX_ROW_NUMBER < maxRowNumber) {
throw new Exception("允许的最大行数为" + MAX_ROW_NUMBER + "，最大列数为" + MAX_COL_NUMBER);
}
}
}
Excel2007获取最大行数和列数
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import java.io.InputStream;
public class ExcelXlsxReader extends DefaultHandler {
private static Integer MAX_ROW_NUMBER = 20000;
private static Integer MAX_COL_NUMBER = 100;
private final static String Q_NAME_C = "c";
private final static String Q_NAME_ROW = "row";
private int totalRows=0;
private int curCol = 0;
private Integer maxRowNumber = 0;
private Integer maxColNumber = 0;
public ExcelXlsxReader() {
}
public static Integer getMaxRowNumber() {
return MAX_ROW_NUMBER;
}
public static Integer getMaxColNumber() {
return MAX_COL_NUMBER;
}
@Override
public void startDocument() throws SAXException {
}
@Override
public void endElement(String uri, String localName, String qName) throws SAXException {
if (Q_NAME_C.equals(qName)) {
curCol++;
}
if (Q_NAME_ROW.equals(qName)) {
totalRows++;
maxRowNumber = Math.max(maxRowNumber, totalRows);
maxColNumber = Math.max(maxColNumber, curCol);
curCol = 0;
}
}
@Override
public void characters(char[] ch, int start, int length) throws SAXException {
}
public void process(InputStream inputStream) throws Exception {
OPCPackage pkg = OPCPackage.open(inputStream);
XSSFReader xssfReader = new XSSFReader(pkg);
SAXParserFactory parserFactory = SAXParserFactory.newInstance();
SAXParser parser = parserFactory.newSAXParser();
XMLReader xmlReader = parser.getXMLReader();
xmlReader.setContentHandler(this);
XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
while (sheets.hasNext()) {
totalRows = 0;
InputStream sheet = sheets.next();
InputSource sheetSource = new InputSource(sheet);
xmlReader.parse(sheetSource);
sheet.close();
}
if (maxColNumber > MAX_COL_NUMBER || MAX_ROW_NUMBER < maxRowNumber) {
throw new Exception("允许的最大行数为" + MAX_ROW_NUMBER + "，最大列数为" + MAX_COL_NUMBER);
}
}
}
网友评论