項目中使用Excel建立任務，然後上傳到fastdfs，此處隻摘出Excel解析的邏輯以及分析後失敗結果回寫的邏輯。
/**
* Bestpay.com.cn Inc.
* Copyright (c) 2011-2016 All Rights Reserved.
*/
package com.bestpay.messagecenter.product.manager.model;
/**
* @author linxing
* @version Id: TaskCounter.java, v 0.1 2017/9/26 15:18 linxing Exp $$
*/
public class TaskCounter {
/**
* 任務号--打日志
*/
private Integer taskId;
/**
* 消息條數
*/
private int totalCount;
/**
* 有效數
*/
private int validCount;
/**
* 重複消息條數
* */
private int repeatCount;
/**
* 校驗失敗數
*/
private int errorCount;
/**
* 營運商過濾數
*/
private int filterCount;
/**
* 擷取 totalCount
* return totalCount
*/
public Integer getTotalCount() {
return totalCount;
}
/**
* 設定 totalCount
* return
*/
public void setTotalCount(Integer totalCount) {
this.totalCount = totalCount;
}
/**
* 擷取 validCount
* return validCount
*/
public Integer getValidCount() {
return validCount;
}
/**
* 設定 validCount
* return
*/
public void setValidCount(Integer validCount) {
this.validCount = validCount;
}
/**
* 擷取 repeatCount
* return repeatCount
*/
public Integer getRepeatCount() {
return repeatCount;
}
/**
* 設定 repeatCount
* return
*/
public void setRepeatCount(Integer repeatCount) {
this.repeatCount = repeatCount;
}
/**
* 擷取 errorCount
* return errorCount
*/
public Integer getErrorCount() {
return errorCount;
}
/**
* 設定 errorCount
* return
*/
public void setErrorCount(Integer errorCount) {
this.errorCount = errorCount;
}
/**
* 擷取 filterCount
* return filterCount
*/
public Integer getFilterCount() {
return filterCount;
}
/**
* 設定 filterCount
* return
*/
public void setFilterCount(Integer filterCount) {
this.filterCount = filterCount;
}
/**
* 擷取 taskId
* return taskId
*/
public Integer getTaskId() {
return taskId;
}
/**
* 設定 taskId
* return
*/
public void setTaskId(Integer taskId) {
this.taskId = taskId;
}
@Override
public String toString() {
return "TaskCounter{" + "taskId=" + taskId + ", totalCount=" + totalCount + ", validCount="
+ validCount + ", repeatCount=" + repeatCount + ", errorCount=" + errorCount
+ ", filterCount=" + filterCount + '}';
}
}
package com.bestpay.messagecenter.product.manager.model;
import java.util.List;
/**
* 檔案資訊類
* @author linxing
* @version Id: FileParserHelper.java, v 0.1 2017/3/17 13:06 linxing Exp $$
*/
public class FileInfo {
/**
* 資料
*/
private List<String> data;
/**
* 目前行号
*/
private int line;
/**
* 擷取 data
* return data
*/
public List<String> getData() {
return data;
}
/**
* 設定 data
* return
*/
public void setData(List<String> data) {
this.data = data;
}
/**
* 擷取 line
* return line
*/
public int getLine() {
return line;
}
/**
* 設定 line
* return
*/
public void setLine(int line) {
this.line = line;
}
}
/**
* 檔案解析類
* @author linxing
* @version Id: FileParserHelper.java, v 0.1 2017/3/17 13:06 linxing Exp $$
*/
@Slf4j
public class FileParserHelper {
/**
* private constructor
*/
private FileParserHelper() {
}
private LinkedList<List<String>> lists;
/**
* @return
*/
public static FileParserHelper getInstance() {
return new FileParserHelper();
}
/**
* 輸入流----fastdfs
* @param inputStream
* @return
*/
public FileParserHelper readExcel(InputStream inputStream) {
long start = System.currentTimeMillis();
log.info("解析任務中，從流中讀取資料開始");
try (Workbook workbook = WorkbookFactory.create(inputStream)) {
Sheet sheet = workbook.getSheetAt(0);
int rowCount = sheet.getLastRowNum();
lists = new LinkedList();
for (int r = 0; r <= rowCount; r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
int cellCount = row.getLastCellNum();
List<String> list = new ArrayList<>();
for (int c = 0; c < cellCount; c++) {
Cell cell = row.getCell(c, Row.CREATE_NULL_AS_BLANK);
cell.setCellType(Cell.CELL_TYPE_STRING);
list.add(cell.getStringCellValue());
}
filterBlankCell(list);
fixDataWithSpecialSign(list);
lists.add(list);
}
} catch (Exception e) {
log.error("解析Excel檔案出錯：{}", Throwables.getStackTraceAsString(e));
throw new MessageCenterProductFault(
MessageCenterProductErrorCode.TASK_FILE_ANALYSIS_FAIL, "解析Excel檔案失敗，格式有誤");
}
log.info("解析任務中，從流中讀取資料結束，耗時：{}", System.currentTimeMillis() - start);
return this;
}
/**
* 過濾兩端的空單元格
* @param list
*/
private void filterBlankCell(List<String> list) {
if (CollectionUtil.isEmpty(list)) {
return;
}
//如果全部是空值，這裡不處理，去重的時候過濾
if (!isNotBlankRow(list)) {
return;
}
while (StringUtils.isEmpty(list.get(0))) {
list.remove(0);
}
while (StringUtils.isEmpty(list.get(list.size() - 1))) {
list.remove(list.size() - 1);
}
}
/**
* 替換特殊字元方法 目前處理換行和空格
* @param list
*/
private void fixDataWithSpecialSign(List<String> list) {
if (CollectionUtil.isEmpty(list)) {
return;
}
for (int i = 0; i < list.size(); i++) {
list.set(i, PropertUtil.fillNull(list.get(i)).replaceAll("\r\n|\r|\n", "").trim());
}
}
/**
* 去重（統計總數，重複數）
* @param countDO 統計的DO
* @param indexs 校驗重複值所需的索引 比如：如果隻需要用手機号判斷重複，隻傳手機号所在的索引 如果有多列逗号分隔
* @return
*/
public Map<String, FileInfo> uniqueDate(TaskCounter countDO, String indexs) {
long start = System.currentTimeMillis();
log.info("解析任務中，去重開始，taskId:{}", countDO.getTaskId());
int[] indexForUnique = initUniqueIndex(indexs);
Map<String, FileInfo> uniqueMap = new LinkedHashMap<>();
int repeatCount = 0;
int totalCount = 0;
int index = 0;
for (List<String> line : lists) {
index++;
//filter blank row
if (!isNotBlankRow(line)) {
log.info("第" + (index) + "行出現空行,已過濾");
continue;
}
totalCount++;
FileInfo fileInfo = new FileInfo();
String key = "";
for (int eachIndex : indexForUnique) {
key = key.concat(PropertUtil.fillNull(line.get(eachIndex)));
}
if (uniqueMap.containsKey(key)) {
repeatCount++;
continue;
}
fileInfo.setData(line);
fileInfo.setLine(index);
uniqueMap.put(key, fileInfo);
}
countDO.setRepeatCount(repeatCount);
countDO.setTotalCount(totalCount);
log.info("解析任務中，去重結束，taskId:{}，耗時：{}", countDO.getTaskId(),
System.currentTimeMillis() - start);
return uniqueMap;
}
private static boolean isNotBlankRow(List<String> rowData) {
boolean result = false;
for (String eachColumn : rowData) {
if (!StringUtils.isEmpty(eachColumn)) {
return true;
}
}
return result;
}
private int[] initUniqueIndex(String indexs) {//用于區分資料是否重複 傳列索引 比如 1,2 表示1 2列的值相同 這行資料重複
if (StringUtils.isEmpty(indexs)) {
throw new MessageCenterProductFault(
MessageCenterProductErrorCode.TASK_FILE_ANALYSIS_FAIL, "解析Excel檔案失敗，沒有定義區分重複值的條件");
}
int[] indexForUnique;
if (indexs.indexOf(",") < 0) {
indexForUnique = new int[] { Integer.parseInt(indexs) };
return indexForUnique;
}
String[] indexStr = indexs.split(",");
indexForUnique = new int[indexStr.length];
for (int i = 0; i < indexStr.length; i++) {
indexForUnique[i] = Integer.parseInt(indexStr[i]);
}
return indexForUnique;
}
}
/**
* Bestpay.com.cn Inc.
* Copyright (c) 2011-2016 All Rights Reserved.
*/
package com.bestpay.messagecenter.product.manager;
import java.io.InputStream;
import java.util.List;
import java.util.Map;
import org.apache.commons.io.output.ByteArrayOutputStream;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import com.bestpay.messagecenter.product.manager.model.AppFileItem;
import com.bestpay.messagecenter.product.manager.model.FileItem;
import com.google.common.base.Throwables;
import lombok.extern.slf4j.Slf4j;
/**
* @author linxing
* @version Id: FileResultUploadHelper.java, v 0.1 2017/5/12 9:41 linxing Exp $$
*/
@Slf4j
public class FileResultUploadHelper {
private FileResultUploadHelper() {
}
/**
* 上傳短信任務解析結果檔案
* @param inputStream
* @param result
*/
public static byte[] uploadResultFileForSms(InputStream inputStream,
List<FileItem> result) {
try (Workbook workbook = WorkbookFactory.create(inputStream);
ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
Sheet sheet = workbook.getSheetAt(0);
Font font = workbook.createFont();
font.setColor(Font.COLOR_RED);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(font);
for (FileItem each : result) {
if (!StringUtils.isEmpty(each.getErrorInfo())) {
Row row = sheet.getRow(each.getLine() - 1);
if (row == null) {
continue;
}
//因為Excel樣式問題，目前連續超過三個空單元格即視為到達列末尾
Cell cell = row.createCell(getLastNoEmptyColNo(row) - 1);
cell.setCellStyle(cellStyle);
cell.setCellValue(each.getErrorInfo());
}
}
workbook.write(outputStream);
outputStream.flush();
return outputStream.toByteArray();
} catch (Exception e) {
log.error("上傳任務解析結果檔案出錯：{}", Throwables.getStackTraceAsString(e));
}
return null;
}
private static int getLastNoEmptyColNo(Row row) {
boolean isFind = false;
int col = 0;
while (!isFind) {
Cell c1 = row.getCell(col, Row.CREATE_NULL_AS_BLANK);
Cell c2 = row.getCell(col + 1, Row.CREATE_NULL_AS_BLANK);
Cell c3 = row.getCell(col + 2, Row.CREATE_NULL_AS_BLANK);
if (StringUtils.isEmpty(String.valueOf(getCellValue(c1)))
&& StringUtils.isEmpty(String.valueOf(getCellValue(c2)))
&& StringUtils.isEmpty(String.valueOf(getCellValue(c3)))) {
isFind = true;
}
col++;
}
return col;
}
private static Object getCellValue(Cell cell) {
int cellType = cell.getCellType();
switch (cellType) {
case Cell.CELL_TYPE_STRING:
return cell.getStringCellValue();
case Cell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue();
case Cell.CELL_TYPE_FORMULA:
return cell.getCellFormula().trim();
case Cell.CELL_TYPE_NUMERIC:
return cell.getNumericCellValue();
case Cell.CELL_TYPE_BLANK:
default:
return "";
}
}
}
![Django之Models（一）如何編寫Models？[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)