package foundation.io.file;
|
|
import java.io.File;
|
import java.io.FileInputStream;
|
import java.text.DecimalFormat;
|
import java.text.SimpleDateFormat;
|
import java.util.Date;
|
|
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
|
import org.apache.poi.ss.usermodel.Cell;
|
import org.apache.poi.ss.usermodel.CellType;
|
import org.apache.poi.ss.usermodel.DateUtil;
|
import org.apache.poi.ss.usermodel.Row;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
|
import foundation.data.entity.Entity;
|
import foundation.data.entity.EntitySet;
|
import foundation.data.object.DataObject;
|
import foundation.io.define.DataIO;
|
import foundation.io.engine.IReadDataListener;
|
import foundation.io.engine.ISheetReader;
|
import foundation.io.engine.IWorkBookReader;
|
import foundation.io.mapping.MappingsRuntime;
|
import foundation.io.object.Titles;
|
import foundation.util.MapList;
|
|
|
public class BookStandardReader extends IWorkBookReader implements ISheetReader {
|
|
private FileInputStream inputStream;
|
private XSSFWorkbook workbook;
|
private MapList<String, Sheet> sheetList;
|
|
private DataIO dataIO;
|
private Sheet sheet;
|
private int titleRowNo;
|
private int dataRowNo;
|
private int maxRowNo;
|
private int currentRowNo;
|
private int[] fromIndex;
|
private int[] toIndex;
|
|
|
public BookStandardReader(File uploadFile, int batchCount) throws Exception {
|
inputStream = new FileInputStream(uploadFile);
|
workbook = new XSSFWorkbook(inputStream);
|
|
sheetList = new MapList<String, Sheet>();
|
for (Sheet sheet: workbook) {
|
sheetList.add(sheet.getSheetName(), sheet);
|
}
|
|
currentRowNo = -1;
|
}
|
|
@Override
|
public ISheetReader openSheetReader(DataIO dataIO) {
|
this.dataIO = dataIO;
|
|
this.titleRowNo = dataIO.getFromTitleRowNo();
|
this.dataRowNo = dataIO.getFromDataRowNo();
|
|
sheet = sheetList.get(dataIO.getFromName());
|
|
if (sheet == null) {
|
sheet = sheetList.get(0);
|
}
|
|
maxRowNo = sheet.getLastRowNum();
|
return this;
|
}
|
|
@Override
|
public Titles readTitles() {
|
Titles result = null;
|
|
Row titleRow = sheet.getRow(titleRowNo);
|
|
if (titleRow == null) {
|
return null;
|
}
|
|
result = new Titles();
|
int max = titleRow.getLastCellNum();
|
|
Cell cell = null;
|
|
for (int i = 0; i < max; i++) {
|
cell = titleRow.getCell(i);
|
|
if (cell == null) {
|
continue;
|
}
|
|
String title = null;
|
CellType cellType = cell.getCellType();
|
|
if (CellType.STRING == cellType) {
|
title = cell.getStringCellValue();
|
}
|
else if (CellType.NUMERIC == cellType) {
|
title = String.valueOf(cell.getNumericCellValue());
|
}
|
else if (CellType.BOOLEAN == cellType) {
|
title = String.valueOf(cell.getBooleanCellValue());
|
}
|
else {
|
title = null;
|
}
|
|
result.add(title, i);
|
}
|
|
return result;
|
}
|
|
@Override
|
public EntitySet readData(MappingsRuntime mappingsRuntime, int batchCount, IReadDataListener dataListener) throws Exception {
|
// if (currentRowNo < 0) {
|
// currentRowNo = dataRowNo;
|
// }
|
currentRowNo = dataRowNo;
|
|
DataObject dataObject = dataIO.getToTempDataObject();
|
EntitySet entitySet = dataObject.createTableEntitySet(batchCount);
|
|
Entity entity = null; int emptyCount = 0;
|
|
for (int i = 0; i < batchCount; i++) {
|
Row dataRow = sheet.getRow(currentRowNo);
|
|
if (dataRow == null) {
|
continue;
|
}
|
|
entity = entitySet.newEntity();
|
readOneDataRow(entity, dataRow);
|
|
if (entity.isEmpty()) {
|
emptyCount++;
|
|
if (emptyCount >= Max_Empty_Read_Times) {
|
break;
|
}
|
|
continue;
|
}
|
|
entity.set("line_no", currentRowNo + 1);
|
dataListener.onReadLine(entity);
|
|
entitySet.append(entity);
|
|
currentRowNo++;
|
if (currentRowNo > maxRowNo) {
|
break;
|
}
|
}
|
|
return entitySet;
|
}
|
|
private DecimalFormat decimalFormat_zero = new DecimalFormat("0");
|
private DecimalFormat decimalFormat_well = new DecimalFormat("#.#########");
|
private SimpleDateFormat simpleFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
|
private void readOneDataRow(Entity entity, Row dataRow) {
|
int size = fromIndex.length;
|
|
Cell cell = null; CellType cellType; Object value;
|
|
for (int i = 0; i < size; i++) {
|
cell = dataRow.getCell(fromIndex[i]);
|
|
if (cell == null) {
|
continue;
|
}
|
|
cellType = cell.getCellType();
|
|
if (CellType.STRING == cellType) {
|
value = cell.getStringCellValue();
|
|
entity.set(toIndex[i], value);
|
continue;
|
}
|
|
if (CellType.NUMERIC == cellType) {
|
if (DateUtil.isCellDateFormatted(cell)) {
|
Date tempValue = cell.getDateCellValue();
|
value = simpleFormat.format(tempValue);
|
|
entity.set(toIndex[i], value);
|
continue;
|
}
|
|
String dateFormat = cell.getCellStyle().getDataFormatString();
|
|
if ("@".equals(dateFormat)) {
|
value = decimalFormat_zero.format(cell.getNumericCellValue());
|
|
entity.set(toIndex[i], value);
|
continue;
|
}
|
else if ("General".equals(dateFormat)) {
|
value = decimalFormat_well.format(cell.getNumericCellValue());
|
|
entity.set(toIndex[i], value);
|
continue;
|
}
|
else if ("m/d/yy h:mm".equalsIgnoreCase(dateFormat)) {
|
value = simpleFormat.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
|
|
entity.set(toIndex[i], value);
|
continue;
|
}
|
|
value = cell.getNumericCellValue();
|
entity.set(toIndex[i], value);
|
continue;
|
}
|
|
if (CellType.BOOLEAN == cellType) {
|
value = cell.getBooleanCellValue();
|
entity.set(toIndex[i], value);
|
continue;
|
}
|
}
|
|
entity.set("row_no", dataRow.getRowNum());
|
}
|
|
@Override
|
protected void close() {
|
try {
|
workbook.close();
|
}
|
catch (Exception e) {
|
}
|
|
try {
|
inputStream.close();
|
}
|
catch (Exception e) {
|
}
|
}
|
|
@Override
|
public void setMappingRuntime(MappingsRuntime mappingsRuntime) {
|
this.fromIndex = mappingsRuntime.getFromIndex();
|
this.toIndex = mappingsRuntime.getToIndex();
|
}
|
|
}
|
