package foundation.io.file;
|
|
import java.io.File;
|
import java.io.FileInputStream;
|
import java.io.FileOutputStream;
|
import java.io.IOException;
|
import java.math.BigDecimal;
|
import java.math.RoundingMode;
|
import java.sql.Clob;
|
import java.sql.SQLException;
|
import java.text.SimpleDateFormat;
|
import java.util.Date;
|
|
import org.apache.poi.ss.usermodel.BorderStyle;
|
import org.apache.poi.ss.usermodel.Cell;
|
import org.apache.poi.ss.usermodel.CellStyle;
|
import org.apache.poi.ss.usermodel.FillPatternType;
|
import org.apache.poi.ss.usermodel.Font;
|
import org.apache.poi.ss.usermodel.IndexedColors;
|
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.meta.field.FieldWriter;
|
import foundation.data.meta.field.FieldsRuntime;
|
import foundation.io.define.DataIO;
|
import foundation.io.engine.ISheetWriter;
|
import foundation.io.engine.IWorkBookWriter;
|
import foundation.io.mapping.MappingsRuntime;
|
import foundation.io.object.Headers;
|
import foundation.io.object.Titles;
|
import foundation.util.MapList;
|
import foundation.util.Util;
|
import foundation.value.ValueType;
|
|
|
public class BookStandardWriter extends IWorkBookWriter implements ISheetWriter {
|
|
private File file;
|
private FileInputStream inputStream;
|
private XSSFWorkbook workbook;
|
private MapList<String, Sheet> sheetList;
|
|
private Sheet sheet;
|
private int titleRowNo;
|
private int dataRowNo;
|
private Row firstDataRow;
|
private CellStyle[] cellStyles;
|
private CellStyle errorCellStyles;
|
private int currentRowNo;
|
private int columnCount;
|
private MappingsRuntime mappingsRuntime;
|
private SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
|
private static String resultFieldName = "导入反馈";
|
|
public BookStandardWriter(File file, int batchCount) throws IOException {
|
this.file = file;
|
|
inputStream = new FileInputStream(file);
|
workbook = new XSSFWorkbook(inputStream);
|
|
sheetList = new MapList<String, Sheet>();
|
|
for (Sheet sheet: workbook) {
|
sheetList.add(sheet.getSheetName(), sheet);
|
}
|
}
|
|
@Override
|
public ISheetWriter openSheetWriter(DataIO dataIO) {
|
this.titleRowNo = dataIO.getToTitleRowNo();
|
this.dataRowNo = dataIO.getToDataRowNo();
|
|
String sheetName = dataIO.getToName();
|
|
sheet = sheetList.get(sheetName);
|
if (sheet == null) {
|
sheet = sheetList.get(0);
|
}
|
|
if (sheet == null) {
|
sheet = workbook.createSheet(sheetName);
|
}
|
|
currentRowNo = -1;
|
|
firstDataRow = sheet.getRow(dataRowNo);
|
if (firstDataRow == null) {
|
firstDataRow = sheet.createRow(dataRowNo);
|
}
|
|
return this;
|
}
|
|
@Override
|
public Headers readHeaders() {
|
Headers headers = new Headers();
|
|
Row titleRow = sheet.getRow(titleRowNo);
|
|
columnCount = titleRow.getLastCellNum();
|
Cell titleCell, valueCell;
|
|
for (int i = 0; i < columnCount; i++) {
|
//1. 读取标题列
|
titleCell = (titleRow != null) ? titleRow.getCell(i) : null;
|
String title = (titleCell != null) ? getCellStringValue(titleCell) : null;
|
|
//2. 读取第一行数据
|
valueCell = firstDataRow.getCell(i);
|
String value = (valueCell != null) ? getCellStringValue(valueCell) : null;
|
|
//3. 添加到 header 中
|
headers.addOne(i, title, value);
|
}
|
|
return headers;
|
}
|
|
private String getCellStringValue(Cell cell) {
|
try {
|
return cell.getStringCellValue();
|
}
|
catch (Exception e) {
|
return null;
|
}
|
}
|
|
@Override
|
public void writeTitles(Titles titles) {
|
Row titleRow = sheet.getRow(titleRowNo);
|
|
if (titleRow == null) {
|
titleRow = sheet.createRow(titleRowNo);
|
}
|
|
Cell cell;
|
|
for (int i = 0; i < columnCount; i++) {
|
cell = titleRow.getCell(i);
|
|
if (cell == null) {
|
cell = titleRow.createCell(i);
|
}
|
|
String title = titles.get(i);
|
cell.setCellValue(String.valueOf(title));
|
}
|
}
|
|
|
@Override
|
public void writeErrorTitle() {
|
Row titleRow = sheet.getRow(titleRowNo);
|
columnCount = titleRow.getLastCellNum();
|
|
Cell cell = titleRow.createCell(columnCount);
|
|
createErrrorCellStyles();
|
cell.setCellStyle(errorCellStyles);
|
cell.setCellValue(resultFieldName);
|
}
|
|
@Override
|
public void parepareWriteData() {
|
//1. 创建格式
|
createDataCellStyles();
|
|
//2. 清空第一个数据行上的数据
|
clearFirstDataRow();
|
}
|
|
private void createDataCellStyles() {
|
cellStyles = new CellStyle[columnCount];
|
for (int i = 0; i < columnCount; i++) {
|
Cell cell = firstDataRow.getCell(i);
|
|
if (cell == null) {
|
continue;
|
}
|
|
cellStyles[i] = cell.getCellStyle();
|
}
|
}
|
|
private void createErrrorCellStyles() {
|
errorCellStyles = workbook.createCellStyle();
|
|
errorCellStyles.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
|
errorCellStyles.setFillPattern(FillPatternType.SOLID_FOREGROUND);
|
errorCellStyles.setBorderRight(BorderStyle.THIN);
|
|
Font font = workbook.createFont();
|
font.setFontHeightInPoints((short)11);
|
errorCellStyles.setFont(font);
|
}
|
|
private void clearFirstDataRow() {
|
Row dataRow = sheet.getRow(dataRowNo);
|
|
if (dataRow == null) {
|
return;
|
}
|
|
Cell cell;
|
for (int i = 0; i < columnCount; i++) {
|
cell = dataRow.getCell(i);
|
|
if (cell == null) {
|
continue;
|
}
|
|
cell.setBlank();
|
}
|
}
|
|
@Override
|
public int writeData(EntitySet entitySet) {
|
int result = 0;
|
|
if (currentRowNo < 0) {
|
currentRowNo = dataRowNo;
|
}
|
|
int[] fromIndexes = mappingsRuntime.getFromIndex();
|
int[] toIndexes = mappingsRuntime.getToIndex();
|
String[] formatMap = mappingsRuntime.getFormatIndexs();
|
int dataSize = toIndexes.length;
|
|
Row dataRow = null; Cell cell;
|
CellStyle cellStyle; int fromIndex, toIndex; String formatStr;
|
FieldWriter fieldWriter; Object[] data;
|
|
FieldsRuntime fields = entitySet.getEntityMeta();
|
ValueType[] valueTypes = fields.getValueTypes();
|
FieldWriter[] fieldWriters = fields.getWriters();
|
|
for (Entity entity: entitySet) {
|
data = entity.getDataArray();
|
dataRow = sheet.getRow(currentRowNo);
|
|
if (dataRow == null) {
|
dataRow = sheet.createRow(currentRowNo);
|
}
|
|
dataRow.setRowStyle(firstDataRow.getRowStyle());
|
dataRow.setHeight(firstDataRow.getHeight());
|
dataRow.setHeightInPoints(firstDataRow.getHeightInPoints());
|
|
for (int i = 0; i < columnCount; i++) {
|
cellStyle = cellStyles[i];
|
|
if (cellStyle == null) {
|
continue;
|
}
|
|
//1. 单元格
|
cell = dataRow.getCell(i);
|
|
if (cell == null) {
|
cell = dataRow.createCell(i);
|
}
|
|
//2. 设置格式
|
cell.setCellStyle(cellStyle);
|
}
|
|
for (int i = 0; i < dataSize; i++) {
|
toIndex = toIndexes[i];
|
cell = dataRow.getCell(toIndex);
|
|
//3. 获取数据
|
fromIndex = fromIndexes[i];
|
fieldWriter = fieldWriters[fromIndex];
|
Object value = fieldWriter.getValue(data[fromIndex]);
|
formatStr = formatMap[i];
|
|
//4. 数据写入
|
writeOneCell(cell, valueTypes[fromIndex], formatStr, value);
|
}
|
|
currentRowNo++;
|
result++;
|
}
|
|
return result;
|
}
|
|
@Override
|
public void writeImg(EntitySet fileSet) throws Exception {
|
// FileInputStream imgFis = new FileInputStream("C:\\Users\\59518\\Desktop\\groupBRegion.png");
|
// byte[] imageBytes = IOUtils.toByteArray(imgFis);
|
// int pictureIndex = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_PNG);
|
//
|
// // 创建绘图对象和锚点
|
// CreationHelper helper = workbook.getCreationHelper();
|
// Drawing<?> drawing = sheet.createDrawingPatriarch();
|
// ClientAnchor anchor = helper.createClientAnchor();
|
// anchor.setCol1(0); // 图片起始列
|
// anchor.setRow1(0); // 图片起始行
|
//
|
// // 插入图片并调整大小
|
// Picture picture = drawing.createPicture(anchor, pictureIndex);
|
// picture.resize(); // 适应单元格大小
|
}
|
|
|
@Override
|
public int writeErrors(EntitySet entitySet) {
|
int result = 0;
|
if (currentRowNo < 0) {
|
currentRowNo = dataRowNo;
|
}
|
|
Row dataRow = null; Cell cell;
|
|
int[] fromIndexes = mappingsRuntime.getFromIndex();
|
int dataSize = fromIndexes.length;
|
|
CellStyle cellStyle = null; int fromIndex; String formatStr;
|
FieldWriter fieldWriter; Object[] data;
|
|
FieldsRuntime fields = entitySet.getEntityMeta();
|
ValueType[] valueTypes = fields.getValueTypes();
|
FieldWriter[] fieldWriters = fields.getWriters();
|
|
for (Entity entity: entitySet) {
|
data = entity.getDataArray();
|
dataRow = sheet.getRow(currentRowNo);
|
|
if (dataRow == null) {
|
dataRow = sheet.createRow(currentRowNo);
|
}
|
|
dataRow.setRowStyle(firstDataRow.getRowStyle());
|
dataRow.setHeight(firstDataRow.getHeight());
|
dataRow.setHeightInPoints(firstDataRow.getHeightInPoints());
|
|
//1. 创建样式
|
for (int i = 0; i < columnCount; i++) {
|
cellStyle = cellStyles[i];
|
|
if (cellStyle == null) {
|
continue;
|
}
|
|
cell = dataRow.getCell(i);
|
|
if (cell == null) {
|
cell = dataRow.createCell(i);
|
}
|
|
cell.setCellStyle(cellStyle);
|
}
|
|
cell = dataRow.createCell(columnCount);
|
cell.setCellStyle(cellStyle);
|
|
for (int i = 0; i < dataSize; i++) {
|
cell = dataRow.getCell(i);
|
|
//2. 获取数据
|
fromIndex = fromIndexes[i];
|
fieldWriter = fieldWriters[fromIndex];
|
Object value = fieldWriter.getValue(data[fromIndex]);
|
|
//4. 数据写入
|
writeOneCell(cell, valueTypes[fromIndex], null, value);
|
}
|
|
currentRowNo++;
|
result++;
|
}
|
|
return result;
|
}
|
|
protected void writeOneCell(Cell cell, ValueType valueType, String format, Object value) {
|
if (value == null) {
|
return ;
|
}
|
|
if (ValueType.Decimal == valueType) {
|
double cellValue = ((BigDecimal)value).doubleValue();
|
|
if (Util.isEmpty(format)) {
|
cell.setCellValue(cellValue);
|
}
|
else if ("K".equals(format)) {
|
cellValue = cellValue/1000;
|
cell.setCellValue(cellValue);
|
}
|
else if (format.endsWith("%")) {
|
int scale = Integer.valueOf(format.substring(0, format.length() - 1));
|
BigDecimal rate = new BigDecimal(cellValue*100);
|
rate = rate.setScale(scale, RoundingMode.HALF_UP);
|
cell.setCellValue(rate + "%");
|
}
|
}
|
else if (ValueType.Double == valueType) {
|
double cellValue = (Double)value;
|
if (Util.isEmpty(format)) {
|
cell.setCellValue(cellValue);
|
}
|
else if ("K".equals(format)) {
|
cellValue = cellValue/1000;
|
cell.setCellValue(cellValue);
|
}
|
else if (format.endsWith("%")) {
|
int scale = Integer.valueOf(format.substring(0, format.length() - 1));
|
BigDecimal rate = new BigDecimal(cellValue*100);
|
rate = rate.setScale(scale, RoundingMode.HALF_UP);
|
cell.setCellValue(rate + "%");
|
}
|
}
|
else if (ValueType.Int == valueType) {
|
Integer cellValue = (Integer)value;
|
if (Util.isEmpty(format)) {
|
cell.setCellValue(cellValue);
|
}
|
else if ("K".equals(format)) {
|
cellValue = cellValue/1000;
|
cell.setCellValue(cellValue);
|
}
|
else if (format.endsWith("%")) {
|
int scale = Integer.valueOf(format.substring(0, format.length() - 1));
|
BigDecimal rate = new BigDecimal(cellValue*100);
|
rate = rate.setScale(scale, RoundingMode.HALF_UP);
|
cell.setCellValue(rate + "%");
|
}
|
}
|
else if (ValueType.Long == valueType) {
|
Long cellValue = (Long)value;
|
if (Util.isEmpty(format)) {
|
cell.setCellValue(cellValue);
|
}
|
else if ("K".equals(format)) {
|
cellValue = cellValue/1000;
|
cell.setCellValue(cellValue);
|
}
|
else if (format.endsWith("%")) {
|
int scale = Integer.valueOf(format.substring(0, format.length() - 1));
|
BigDecimal rate = new BigDecimal(cellValue*100);
|
rate = rate.setScale(scale, RoundingMode.HALF_UP);
|
cell.setCellValue(rate + "%");
|
}
|
}
|
else if (ValueType.Float == valueType) {
|
Float cellValue = (Float)value;
|
if (Util.isEmpty(format)) {
|
cell.setCellValue(cellValue);
|
}
|
else if ("K".equals(format)) {
|
cellValue = cellValue/1000;
|
cell.setCellValue(cellValue);
|
}
|
else if (format.endsWith("%")) {
|
int scale = Integer.valueOf(format.substring(0, format.length() - 1));
|
BigDecimal rate = new BigDecimal(cellValue*100);
|
rate = rate.setScale(scale, RoundingMode.HALF_UP);
|
cell.setCellValue(rate + "%");
|
}
|
}
|
else if (ValueType.Date == valueType) {
|
String formatDate = dateFormat.format((Date)value);
|
cell.setCellValue(formatDate);
|
}
|
else if (ValueType.Clob == valueType) {
|
Clob clob = (Clob)value;
|
|
try {
|
cell.setCellValue(clob.getSubString((long)1, (int)clob.length()));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
else {
|
cell.setCellValue(String.valueOf(value));
|
}
|
}
|
|
@Override
|
public void close() {
|
try {
|
FileOutputStream outputStream = new FileOutputStream(file);
|
try {
|
workbook.write(outputStream);
|
workbook.close();
|
close = true;
|
}
|
finally {
|
outputStream.close();
|
}
|
}
|
catch (Exception e) {
|
logger.error("close work book error: {}", e.getMessage());
|
}
|
}
|
|
|
@Override
|
public void setMappingRuntime(MappingsRuntime mappingsRuntime) {
|
this.mappingsRuntime = mappingsRuntime;
|
}
|
}
|
