package com.sf.novatar.tpl.util;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import org.apache.struts2.dispatcher.multipart.MultiPartRequestWrapper;
import com.sf.novatar.tpl.enums.FileType;
import com.sf.novatar.tpl.p2.business.XLSXCovertCSVReader;
import com.sf.novatar.tpl.servlet.HttpServletHolder;
import com.sfpay.framework.base.exception.ServiceException;
/**
* 描述：
*
* <pre>
* HISTORY
* ****************************************************************************
* ID DATE PERSON REASON
* 1 2015年9月14日 601008 Create
* ****************************************************************************
* </pre>
*
* @author 601008
* @since 1.0
*/
public class P2AbstractExcelReader {
// 需要读取的excel的字段的数目
private int cellNum;
public P2AbstractExcelReader(int cellNum) {
this.cellNum = cellNum;
}
public List<String[]> Reader() throws Exception {
return Reader(new Validate() {
@Override
public void doValidate(InputStream inputStream) throws Exception {
String fileType = getType(inputStream);
if (StringUtils.isBlank(fileType)
|| (!FileType.XLSX.getValue().equals(fileType)
&& !FileType.XLS_DOC.getValue()
.equals(fileType) && !FileType.XLSX_DOCX
.getValue().equals(fileType))) {
throw new ServiceException("上传失败,请选择Excel类型文件! ");
}
}
}, cellNum);
}
private List<String[]> Reader(Validate validate, int cellNum)
throws Exception {
MultiPartRequestWrapper mpRequest = (MultiPartRequestWrapper) HttpServletHolder
.getCurrentRequest();
List<String[]> list = null;
// 导入的文件
File files = mpRequest.getFiles("excel")[0];
if (null == files) {
throw new FileNotFoundException("解析的文件不存在");
}
try {
list=XLSXCovertCSVReader.readerExcel(files.getAbsolutePath(),
"Sheet1", cellNum);
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
/**
* * 判断文件类型
*
* @param inputStream
*
* @return 文件类型
*
*/
private static String getType(InputStream inputStream) throws IOException {
if (inputStream == null) {
return null;
}
String fileHead = getFileContent(inputStream);
if (fileHead == null || fileHead.length() == 0) {
return null;
}
fileHead = fileHead.toUpperCase();
FileType[] fileTypes = FileType.values();
for (FileType type : fileTypes) {
if (fileHead.startsWith(type.getValue())) {
return type.getValue();
}
}
throw new ServiceException("没有获取到响应的文件类型");
}
/**
* 得到文件头
*
* @param filePath
* 文件路径
* @return 文件头
* @throws IOException
*/
private static String getFileContent(InputStream inputStream)
throws IOException {
int length = 28;
byte[] b = null;
if (inputStream != null) {
inputStream.read(b = new byte[length], 0, length);
inputStream.skip(-length);
}
return FileTypeJudge.bytesToHexString(b);
}
public interface Validate {
public void doValidate(InputStream inputStream) throws Exception;
}
}
/
package com.sf.novatar.tpl.p2.business;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
/**
* 使用CVS模式解决XLSX文件，可以有效解决用户模式内存溢出的问题
* 该模式是POI官方推荐的读取大数据的模式，在用户模式下，数据量较大、Sheet较多、或者是有很多无用的空行的情况
* ，容易出现内存溢出,用户模式读取Excel的典型代码如下： FileInputStream file=new
* FileInputStream("c:\\test.xlsx"); Workbook wb=new XSSFWorkbook(file);
*/
public class XLSXCovertCSVReader {
enum xssfDataType {
BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER,
}
class MyXSSFSheetHandler extends DefaultHandler {
private StylesTable stylesTable;
private ReadOnlySharedStringsTable sharedStringsTable;
private final PrintStream output;
private final int minColumnCount;
private boolean vIsOpen;
private xssfDataType nextDataType;
private short formatIndex;
private String formatString;
private final DataFormatter formatter;
private int thisColumn = -1;
private int lastColumnNumber = -1;
private StringBuffer value;
private String[] record;
private List<String[]> rows = new ArrayList<String[]>();
private boolean isCellNull = false;
public MyXSSFSheetHandler(StylesTable styles,
ReadOnlySharedStringsTable strings, int cols, PrintStream target) {
this.stylesTable = styles;
this.sharedStringsTable = strings;
this.minColumnCount = cols;
this.output = target;
this.value = new StringBuffer();
this.nextDataType = xssfDataType.NUMBER;
this.formatter = new DataFormatter();
record = new String[this.minColumnCount];
rows.clear();// 每次读取都清空行集合
}
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
if ("inlineStr".equals(name) || "v".equals(name)) {
vIsOpen = true;
value.setLength(0);
} else if ("c".equals(name)) {
String r = attributes.getValue("r");
int firstDigit = -1;
for (int c = 0; c < r.length(); ++c) {
if (Character.isDigit(r.charAt(c))) {
firstDigit = c;
break;
}
}
thisColumn = nameToColumn(r.substring(0, firstDigit));
this.nextDataType = xssfDataType.NUMBER;
this.formatIndex = -1;
this.formatString = null;
String cellType = attributes.getValue("t");
String cellStyleStr = attributes.getValue("s");
if ("b".equals(cellType))
nextDataType = xssfDataType.BOOL;
else if ("e".equals(cellType))
nextDataType = xssfDataType.ERROR;
else if ("inlineStr".equals(cellType))
nextDataType = xssfDataType.INLINESTR;
else if ("s".equals(cellType))
nextDataType = xssfDataType.SSTINDEX;
else if ("str".equals(cellType))
nextDataType = xssfDataType.FORMULA;
else if (cellStyleStr != null) {
int styleIndex = Integer.parseInt(cellStyleStr);
XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
this.formatIndex = style.getDataFormat();
this.formatString = style.getDataFormatString();
if (this.formatString == null)
this.formatString = BuiltinFormats
.getBuiltinFormat(this.formatIndex);
}
}
}
public void endElement(String uri, String localName, String name)
throws SAXException {
String thisStr = null;
if ("v".equals(name)) {
switch (nextDataType) {
case BOOL:
char first = value.charAt(0);
thisStr = first == '0' ? "FALSE" : "TRUE";
break;
case ERROR:
thisStr = "ERROR:" + value.toString() ;
break;
case FORMULA:
thisStr = value.toString() ;
break;
case INLINESTR:
XSSFRichTextString rtsi = new XSSFRichTextString(
value.toString());
thisStr = rtsi.toString() ;
break;
case SSTINDEX:
String sstIndex = value.toString();
try {
int idx = Integer.parseInt(sstIndex);
XSSFRichTextString rtss = new XSSFRichTextString(
sharedStringsTable.getEntryAt(idx));
thisStr =rtss.toString() ;
} catch (NumberFormatException ex) {
output.println("Failed to parse SST index '" + sstIndex
+ "': " + ex.toString());
}
break;
case NUMBER:
String n = value.toString();
// 判断是否是日期格式
if (HSSFDateUtil.isADateFormat(this.formatIndex, n)) {
Double d = Double.parseDouble(n);
Date date = HSSFDateUtil.getJavaDate(d);
thisStr = formateDateToString(date);
} else if (this.formatString != null)
thisStr = formatter.formatRawCellContents(
Double.parseDouble(n), this.formatIndex,
this.formatString);
else
thisStr = n;
break;
default:
thisStr = "(TODO: Unexpected type: " + nextDataType + ")";
break;
}
if (lastColumnNumber == -1) {
lastColumnNumber = 0;
}
// 判断单元格的值是否为空
if (thisStr == null || "".equals(isCellNull)) {
isCellNull = true;// 设置单元格是否为空值
}
//
if (thisColumn >= minColumns) {
return;
}
record[thisColumn] = thisStr;
// Update column
if (thisColumn > -1)
lastColumnNumber = thisColumn;
} else if ("row".equals(name)) {
// Print out any missing commas if needed
if (minColumns > 0) {
// Columns are 0 based
if (lastColumnNumber == -1) {
lastColumnNumber = 0;
}
boolean notNullLine = false;
for (int i = 0; i < record.length; i++) {
if (isCellNull == false && record[i] != null) { // 只要有一个单元格不为空
notNullLine = true;
break;
}
}
if (isCellNull == false && notNullLine)// 判断是否空行
{
rows.add(record.clone());
isCellNull = false;
for (int i = 0; i < record.length; i++) {
record[i] = null;
}
}
}
lastColumnNumber = -1;
}
}
public List<String[]> getRows() {
return rows;
}
public void setRows(List<String[]> rows) {
this.rows = rows;
}
public void characters(char[] ch, int start, int length)
throws SAXException {
if (vIsOpen)
value.append(ch, start, length);
}
private int nameToColumn(String name) {
int column = -1;
for (int i = 0; i < name.length(); ++i) {
int c = name.charAt(i);
column = (column + 1) * 26 + c - 'A';
}
return column;
}
private String formateDateToString(Date date) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期
return sdf.format(date);
}
}
// /
private OPCPackage xlsxPackage;
private int minColumns;
private PrintStream output;
private String sheetName;
/**
* Creates a new XLSX -> CSV converter
*
*/
public XLSXCovertCSVReader(OPCPackage pkg, PrintStream output,
String sheetName, int minColumns) {
this.xlsxPackage = pkg;
this.output = output;
this.minColumns = minColumns;
this.sheetName = sheetName;
}
public List<String[]> processSheet(StylesTable styles,
ReadOnlySharedStringsTable strings, InputStream sheetInputStream)
throws IOException, ParserConfigurationException, SAXException {
InputSource sheetSource = new InputSource(sheetInputStream);
SAXParserFactory saxFactory = SAXParserFactory.newInstance();
SAXParser saxParser = saxFactory.newSAXParser();
XMLReader sheetParser = saxParser.getXMLReader();
MyXSSFSheetHandler handler = new MyXSSFSheetHandler(styles, strings,
this.minColumns, this.output);
sheetParser.setContentHandler(handler);
sheetParser.parse(sheetSource);
return handler.getRows();
}
/**
* 初始化这个处理程序 将
*
* @throws IOException
* @throws OpenXML4JException
* @throws ParserConfigurationException
* @throws SAXException
*/
public List<String[]> process() throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
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;
List<String[]> list=null;
while (iter.hasNext()) {
InputStream stream = iter.next();
String sheetNameTemp = iter.getSheetName();
if (this.sheetName.equals(sheetNameTemp)) {
list = processSheet(styles, strings, stream);
stream.close();
// ++index;sss
}
}
return list;
}
/**
* 读取Excel
*
* @param path
* 文件路径
* @param sheetName
* sheet名称
* @param minColumns
* 列总数
* @return
* @throws SAXException
* @throws ParserConfigurationException
* @throws OpenXML4JException
* @throws IOException
*/
public static List<String[]> readerExcel(String path, String sheetName,
int minColumns) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
OPCPackage p = OPCPackage.open(path, PackageAccess.READ);
XLSXCovertCSVReader xlsx2csv = new XLSXCovertCSVReader(p, System.out,
sheetName, minColumns);
List<String[]> list = xlsx2csv.process();
p.close();
return list;
}
public static void main(String[] args) throws Exception {
List<String[]> list = XLSXCovertCSVReader.readerExcel(
"d:\\user\\80002253\\桌面\\importModelForStandardRule.xlsx",
"Sheet1", 3);
int i = 1;
for (String[] record : list) {
for (String cell : record) {
System.out.print(cell + " ");
}
System.out.println(i++);
}
}
}
/*
* Copyright (c) 2017, S.F. Express Inc. All rights reserved.
*/
package com.sf.novatar.tpl.p2.business;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.sf.novatar.tpl.bean.BeanBase;
import com.sf.novatar.tpl.enums.BusinessStatus;
import com.sf.novatar.tpl.p2.dao.IGsConfirmRulesDao;
import com.sf.novatar.tpl.page.PageHandler;
import com.sf.novatar.tpl.page.PageSplit;
import com.sf.novatar.tpl.util.DateUtils;
import com.sf.novatar.tpl.util.P2AbstractExcelReader;
import com.sf.novatar.tpl.util.UserUtil;
import com.sfpay.framework.base.exception.ServiceException;
/**
* 描述：
*
* <pre>HISTORY
* ****************************************************************************
* ID DATE PERSON REASON
* 1 2017年2月14日 80001092 Create
* ****************************************************************************
* </pre>
* @author 80001092
* @since 1.0
*/
@Service("gsConfirmRules")
public class GsConfirmRulesBusiness {
private static final Logger logger = LoggerFactory.getLogger(GsConfirmRulesBusiness.class);
@Autowired
IGsConfirmRulesDao gsConfirmRulesDao;
/**
* 2017年2月14日 80001092 方法说明：查询分成规则
* @param bean
* void
*/
public void queryCommon(BeanBase bean) {
Map<String, String> paramMap = bean.getDatas().get(0);
try {
bean.getExpData().put("page", PageHandler.execute(new PageSplit<Map<String, Object>>() {
@Override
public int queryTotalCount() {
return gsConfirmRulesDao.queryTotalCount(paramMap);
}
@Override
public List<Map<String, Object>> queryPageList(int pageStart, int pageSize) {
return gsConfirmRulesDao.queryPageList(paramMap, pageStart, pageSize);
}
}, bean.getRows(), bean.getPage()));
} catch (Exception e) {
logger.error("查询失败！");
throw new ServiceException("查询失败：", e.getMessage());
}
}
/**
* 2017年2月16日 80001092 方法说明：导入
* @param bean
* void
*/
@Transactional
public void importExcel(BeanBase bean) {
try {
/*new AbstractExcelReader() {
@Override
public void doParse(Workbook workbook) {
insertList(check(workbook));
}
}.Reader();;*/
P2AbstractExcelReader reader = new P2AbstractExcelReader(43);
List<String[]> list = reader.Reader();
checkAndInsert(list, bean);
} catch (Exception e) {
String errorMsg = "EXCEL上传失败";
logger.error(errorMsg, e);
/*if (e instanceof DuplicateKeyException) {
throw new ServiceException("导入的Excel存在重复的数据,去掉重复数据后在导入");
} else {
throw new ServiceException(e.getMessage());
}*/
}
bean.setStatus(BusinessStatus.FINLISH);
bean.setProcessMsg("导入完成！");
}
public void checkAndInsert(List<String[]> list, BeanBase bean) {
gsConfirmRulesDao.delete();
Map<String, String> map = null;
List<Map<String, String>> result = new ArrayList<Map<String, String>>();
String CREATE_ID = UserUtil.getCurrentUser().getEmpCode();
int i = 0;
String[] record = null;
for (int j = 1; j < list.size(); j++) {
record = list.get(j);
map = new HashMap<String, String>();
map.put("CUST_NO", record[i++]);
map.put("ACTURAL_WEIGHT", record[i++]);
map.put("CALC_WEIGHT", record[i++]);
map.put("WAYBILL_NO", record[i++]);
map.put("PRODUCT_CODE", record[i++]);
map.put("PRODUCT_CODE_BIG", record[i++]);
map.put("ORIGINAL_PLACE", record[i++]);
map.put("ORIGINAL_ZONE", record[i++]);
map.put("ORIGINAL_BIG_ZONE", record[i++]);
map.put("ORIGINAL_COUNTRY", record[i++]);
map.put("DEST_PLACE", record[i++]);
map.put("DEST_ZONE", record[i++]);
map.put("DEST_BIG_ZONE", record[i++]);
map.put("DEST_COUNTRY", record[i++]);
map.put("ORIGINAL_PORT", record[i++]);
map.put("ORIGIANL_BIG_PROT", record[i++]);
map.put("ORIGINAL_PORT_COUNTRY", record[i++]);
map.put("TRANS_PORT", record[i++]);
map.put("TRANS_ZONE", record[i++]);
map.put("TRANS_COUNTRY", record[i++]);
map.put("SEC_TRANS", record[i++]);
map.put("SEC_TRANS_ZONE", record[i++]);
map.put("SEC_TRANS_COUNTRY", record[i++]);
map.put("DEST_PORT", record[i++]);
map.put("DEST_PORT_ZONE", record[i++]);
map.put("DEST_PORT_COUNTRY", record[i++]);
map.put("DIVIDE_STAND", record[i++]);
map.put("DIS_PERCENT", record[i++]);
map.put("RULE_CODE", record[i++]);
map.put("BEGIN_DATE", record[i++]);
map.put("END_DATE", record[i++]);
map.put("AVAILABILE_TYPE", record[i++]);
map.put("CREATE_ID", CREATE_ID);
map.put("ATTR1", record[i++]);
map.put("ATTR2", record[i++]);
map.put("ATTR3", record[i++]);
map.put("ATTR4", record[i++]);
map.put("ATTR5", record[i++]);
map.put("ATTR6", record[i++]);
map.put("ATTR7", record[i++]);
map.put("ATTR8", record[i++]);
map.put("ATTR9", record[i++]);
map.put("ATTR10", record[i++]);
map.put("CREATE_DATE", record[i++]);
result.add(map);
if (result.size() > 250) {
gsConfirmRulesDao.insertList(result);
result.clear();
}
i = 0;
}
gsConfirmRulesDao.insertList(result);
}
/*private void insertList(List<Map<String, String>> excelValueList) {
gsConfirmRulesDao.delete();
gsConfirmRulesDao.insertList(excelValueList);
}*/
/**
* 2017年3月1日 80001092 方法说明：换第二种方式
* @param workBook
* @return
* List<Map<String,String>>
*/
@SuppressWarnings("unused")
private List<Map<String, String>> check(Workbook workBook) {
List<Map<String, String>> excelValueList = null;
// 待导入的数据
int import_wb_sheetNos = workBook.getNumberOfSheets();
if (import_wb_sheetNos == 0) {
throw new ServiceException("待导入的数据为空");
}
excelValueList = new ArrayList<>();
String CREATE_ID = UserUtil.getCurrentUser().getEmpCode();
for(int i=0; i<import_wb_sheetNos; i++) {
Cell cell = null;
String CUST_NO = null;
String ACTURAL_WEIGHT = null;
String CALC_WEIGHT = null;
String WAYBILL_NO = null;
String PRODUCT_CODE = null;
String PRODUCT_CODE_BIG = null;
String ORIGINAL_PLACE = null;
String ORIGINAL_ZONE = null;
String ORIGINAL_BIG_ZONE = null;
String ORIGINAL_COUNTRY = null;
String DEST_PLACE = null;
String DEST_ZONE = null;
String DEST_BIG_ZONE = null;
String DEST_COUNTRY = null;
String ORIGINAL_PORT = null;
String ORIGIANL_BIG_PROT = null;
String ORIGINAL_PORT_COUNTRY = null;
String TRANS_PORT = null;
String TRANS_ZONE = null;
String TRANS_COUNTRY = null;
String SEC_TRANS = null;
String SEC_TRANS_ZONE = null;
String SEC_TRANS_COUNTRY = null;
String DEST_PORT = null;
String DEST_PORT_ZONE = null;
String DEST_PORT_COUNTRY = null;
String DIVIDE_STAND = null;
String DIS_PERCENT = null;
String RULE_CODE = null;
String BEGIN_DATE = null;
String END_DATE = null;
String AVAILABILE_TYPE = null;
String ATTR1 = null;
String ATTR2 = null;
String ATTR3 = null;
String ATTR4 = null;
String ATTR5 = null;
String ATTR6 = null;
String ATTR7 = null;
String ATTR8 = null;
String ATTR9 = null;
String ATTR10 = null;
String CREATE_DATE = null;
Sheet sheet = workBook.getSheetAt(i);
for(int line=1, count=sheet.getLastRowNum()+1; line<count; line++) {
Row row = sheet.getRow(line);
int j = 0;
cell = row.getCell(j++);
CUST_NO = getCellValue(cell);
cell = row.getCell(j++);
ACTURAL_WEIGHT = getCellValue(cell);
cell = row.getCell(j++);
CALC_WEIGHT = getCellValue(cell);
cell = row.getCell(j++);
WAYBILL_NO = getCellValue(cell);
cell = row.getCell(j++);
PRODUCT_CODE = getCellValue(cell);
cell = row.getCell(j++);
PRODUCT_CODE_BIG = getCellValue(cell);
cell = row.getCell(j++);
ORIGINAL_PLACE = getCellValue(cell);
cell = row.getCell(j++);
ORIGINAL_ZONE = getCellValue(cell);
cell = row.getCell(j++);
ORIGINAL_BIG_ZONE = getCellValue(cell);
cell = row.getCell(j++);
ORIGINAL_COUNTRY = getCellValue(cell);
cell = row.getCell(j++);
DEST_PLACE = getCellValue(cell);
cell = row.getCell(j++);
DEST_ZONE = getCellValue(cell);
cell = row.getCell(j++);
DEST_BIG_ZONE = getCellValue(cell);
cell = row.getCell(j++);
DEST_COUNTRY = getCellValue(cell);
cell = row.getCell(j++);
ORIGINAL_PORT = getCellValue(cell);
cell = row.getCell(j++);
ORIGIANL_BIG_PROT = getCellValue(cell);
cell = row.getCell(j++);
ORIGINAL_PORT_COUNTRY = getCellValue(cell);
cell = row.getCell(j++);
TRANS_PORT = getCellValue(cell);
cell = row.getCell(j++);
TRANS_ZONE = getCellValue(cell);
cell = row.getCell(j++);
TRANS_COUNTRY = getCellValue(cell);
cell = row.getCell(j++);
SEC_TRANS = getCellValue(cell);
cell = row.getCell(j++);
SEC_TRANS_ZONE = getCellValue(cell);
cell = row.getCell(j++);
SEC_TRANS_COUNTRY = getCellValue(cell);
cell = row.getCell(j++);
DEST_PORT = getCellValue(cell);
cell = row.getCell(j++);
DEST_PORT_ZONE = getCellValue(cell);
cell = row.getCell(j++);
DEST_PORT_COUNTRY = getCellValue(cell);
cell = row.getCell(j++);
DIVIDE_STAND = getCellValue(cell);
cell = row.getCell(j++);
DIS_PERCENT = getCellValue(cell);
cell = row.getCell(j++);
RULE_CODE = getCellValue(cell);
cell = row.getCell(j++);
BEGIN_DATE = getCellValue(cell);
cell = row.getCell(j++);
END_DATE = getCellValue(cell);
cell = row.getCell(j++);
AVAILABILE_TYPE = getCellValue(cell);
cell = row.getCell(j++);
ATTR1 = getCellValue(cell);
cell = row.getCell(j++);
ATTR2 = getCellValue(cell);
cell = row.getCell(j++);
ATTR3 = getCellValue(cell);
cell = row.getCell(j++);
ATTR4 = getCellValue(cell);
cell = row.getCell(j++);
ATTR5 = getCellValue(cell);
cell = row.getCell(j++);
ATTR6 = getCellValue(cell);
cell = row.getCell(j++);
ATTR7 = getCellValue(cell);
cell = row.getCell(j++);
ATTR8 = getCellValue(cell);
cell = row.getCell(j++);
ATTR9 = getCellValue(cell);
cell = row.getCell(j++);
ATTR10 = getCellValue(cell);
cell = row.getCell(j++);
CREATE_DATE = getCellValue(cell);
Map<String, String> map = new HashMap<String, String>();
map.put("CUST_NO", CUST_NO);
map.put("ACTURAL_WEIGHT", ACTURAL_WEIGHT);
map.put("CALC_WEIGHT", CALC_WEIGHT);
map.put("WAYBILL_NO", WAYBILL_NO);
map.put("PRODUCT_CODE", PRODUCT_CODE);
map.put("PRODUCT_CODE_BIG", PRODUCT_CODE_BIG);
map.put("ORIGINAL_PLACE", ORIGINAL_PLACE);
map.put("ORIGINAL_ZONE", ORIGINAL_ZONE);
map.put("ORIGINAL_BIG_ZONE", ORIGINAL_BIG_ZONE);
map.put("ORIGINAL_COUNTRY", ORIGINAL_COUNTRY);
map.put("DEST_PLACE", DEST_PLACE);
map.put("DEST_ZONE", DEST_ZONE);
map.put("DEST_BIG_ZONE", DEST_BIG_ZONE);
map.put("DEST_COUNTRY", DEST_COUNTRY);
map.put("ORIGINAL_PORT", ORIGINAL_PORT);
map.put("ORIGIANL_BIG_PROT", ORIGIANL_BIG_PROT);
map.put("ORIGINAL_PORT_COUNTRY", ORIGINAL_PORT_COUNTRY);
map.put("TRANS_PORT", TRANS_PORT);
map.put("TRANS_ZONE", TRANS_ZONE);
map.put("TRANS_COUNTRY", TRANS_COUNTRY);
map.put("SEC_TRANS", SEC_TRANS);
map.put("SEC_TRANS_ZONE", SEC_TRANS_ZONE);
map.put("SEC_TRANS_COUNTRY", SEC_TRANS_COUNTRY);
map.put("DEST_PORT", DEST_PORT);
map.put("DEST_PORT_ZONE", DEST_PORT_ZONE);
map.put("DEST_PORT_COUNTRY", DEST_PORT_COUNTRY);
map.put("DIVIDE_STAND", DIVIDE_STAND);
map.put("DIS_PERCENT", DIS_PERCENT);
map.put("RULE_CODE", RULE_CODE);
map.put("BEGIN_DATE", BEGIN_DATE);
map.put("END_DATE", END_DATE);
map.put("AVAILABILE_TYPE", AVAILABILE_TYPE);
map.put("CREATE_ID", CREATE_ID);
map.put("ATTR1", ATTR1);
map.put("ATTR2", ATTR2);
map.put("ATTR3", ATTR3);
map.put("ATTR4", ATTR4);
map.put("ATTR5", ATTR5);
map.put("ATTR6", ATTR6);
map.put("ATTR7", ATTR7);
map.put("ATTR8", ATTR8);
map.put("ATTR9", ATTR9);
map.put("ATTR10", ATTR10);
map.put("CREATE_DATE", CREATE_DATE);
excelValueList.add(map);
}
}
return excelValueList;
}
private String getCellValue(Cell cell) {
if(cell == null) {
return "";
}
String cellValue = "";
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
cellValue =cell.getStringCellValue().trim();
break;
case Cell.CELL_TYPE_NUMERIC:
cellValue = String.valueOf(cell.getNumericCellValue());
break;
}
return cellValue;
}
/**
* 2017年2月16日 80001092 方法说明：导出
* @param bean
* void
*/
public void export(BeanBase bean) {
Map<String, Object> map = new HashMap<String, Object>();
List<String[]> excel = new ArrayList<String[]>();
final String[] cellsTitle =
{"月结客户编号","实际重量(边界值)", "计费重量(边界值)", "运单号段","产品编码","产品编码大类(*)", "原寄地网点","原寄地区部网点","原寄地大区部",
"原寄地国家", "目的地网点", "目的地区部网点", "目的地大区部", "目的地国家","原寄地口岸", "原寄地口岸大区部","原寄地口岸国家", "中转口岸",
"中转口岸大区部", "中转口岸国家","第二个中转口岸", "第二个中转口岸大区部","第二个中转口岸国家", "目的地口岸", "目的地口岸大区部", "目的地口岸国家",
"分成口径","折扣比例", "规则CODE", "生效日期" ,"失效时期","规则自动/手动生成","备用1","备用2","备用3","备用4","备用5","备用6","备用7",
"备用8","备用9","备用10","创建时间"};
final Map<Integer, String> header = new HashMap<Integer, String>(); //报表头部
header.put(cellsTitle.length, "分成规则");
String[] cells = null;
List<Map<String, Object>> list = null;
list = gsConfirmRulesDao.queryPageList(bean.getDatas().get(0), 0, Integer.MAX_VALUE);
for (Map<String, Object> data : list) {
cells = new String[cellsTitle.length];
int i = 0;
cells[i++] = getData(data, "CUST_NO");
cells[i++] = getData(data, "ACTURAL_WEIGHT");
cells[i++] = getData(data, "CALC_WEIGHT");
cells[i++] = getData(data, "WAYBILL_NO");
cells[i++] = getData(data, "PRODUCT_CODE");
cells[i++] = getData(data, "PRODUCT_CODE_BIG");
cells[i++] = getData(data, "ORIGINAL_PLACE");
cells[i++] = getData(data, "ORIGINAL_ZONE");
cells[i++] = getData(data, "ORIGINAL_BIG_ZONE");
cells[i++] = getData(data, "ORIGINAL_COUNTRY");
cells[i++] = getData(data, "DEST_PLACE");
cells[i++] = getData(data, "DEST_ZONE");
cells[i++] = getData(data, "DEST_BIG_ZONE");
cells[i++] = getData(data, "DEST_COUNTRY");
cells[i++] = getData(data, "ORIGINAL_PORT");
cells[i++] = getData(data, "ORIGIANL_BIG_PROT");
cells[i++] = getData(data, "ORIGINAL_PORT_COUNTRY");
cells[i++] = getData(data, "TRANS_PORT");
cells[i++] = getData(data, "TRANS_ZONE");
cells[i++] = getData(data, "TRANS_COUNTRY");
cells[i++] = getData(data, "SEC_TRANS");
cells[i++] = getData(data, "SEC_TRANS_ZONE");
cells[i++] = getData(data, "SEC_TRANS_COUNTRY");
cells[i++] = getData(data, "DEST_PORT");
cells[i++] = getData(data, "DEST_PORT_ZONE");
cells[i++] = getData(data, "DEST_PORT_COUNTRY");
cells[i++] = getData(data, "DIVIDE_STAND");
cells[i++] = getData(data, "DIS_PERCENT");
cells[i++] = getData(data, "RULE_CODE");
cells[i++] = getData(data, "BEGIN_DATE");
cells[i++] = getData(data, "END_DATE");
cells[i++] = getData(data, "AVAILABILE_TYPE");
cells[i++] = getData(data, "ATTR1");
cells[i++] = getData(data, "ATTR2");
cells[i++] = getData(data, "ATTR3");
cells[i++] = getData(data, "ATTR4");
cells[i++] = getData(data, "ATTR5");
cells[i++] = getData(data, "ATTR6");
cells[i++] = getData(data, "ATTR7");
cells[i++] = getData(data, "ATTR8");
cells[i++] = getData(data, "ATTR9");
cells[i++] = getData(data, "ATTR10");
cells[i++] = getData(data, "CREATE_DATE");
excel.add(cells);
}
map.put("orgCode", "sheet1");
map.put("data", excel);
map.put("header", header); //统计头部
map.put("cellsTitle", cellsTitle);
map.put("fileName", "分成规则_" + DateUtils.formatDate(new Date(), "yyyy-MM-dd"));
bean.getExpData().put("excelData", map);
}
private String getData(Map<String, Object> data, String key) {
return data.get(key) == null ? "" : data.get(key).toString();
}
}
大excel上传
最新推荐文章于 2024-04-23 22:41:04 发布
536
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
