/**
* 读Excel
*
* @param pathname
*/
public static LinkedList<HashMap<String, String>> readExcel(String pathname) {
LinkedList<HashMap<String, String>> list = new LinkedList<HashMap<String, String>>();
File file = new File(pathname);
String uri = "f:\\xml\\map\\edge.xml";
Workbook wb = null;
try {
wb = Workbook.getWorkbook(file);
Sheet sheet = wb.getSheet(0);
int rows = sheet.getRows();
// ThreadPoolExecutorTest threadPool = new
// ThreadPoolExecutorTest(queue);
for (int i = 0; i < rows; i++) {
HashMap<String, String> map = new HashMap<String, String>();
// System.out.println("第"+(i+1)+"条数据正在执行");
Cell[] cols = sheet.getRow(i);
String sender = cols[0].getContents();
String nodeID = cols[1].getContents();
String wayID = cols[2].getContents();
String depart = cols[3].getContents();
String edgeID = SaxService.getWayIdFromNodeXML(uri, nodeID, wayID);
map.put("sender", sender);
map.put("nodeID", nodeID);
map.put("wayID", wayID);
map.put("edgeID", edgeID);
map.put("depart", depart);
list.add(map);
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
/**
* 取得一行的cell
*
* @param rowNum 行号
* @return
*/
public Cell[] getRowCells(int rowNum) {
if (readOnlyWBook == true) {
return currentSheet.getRow(rowNum);
} else {
return wrCurrentSheet.getRow(rowNum);
}
}
public static List<ConsoleVo> readExcel(String fileName) {
List<ConsoleVo> list = new ArrayList<ConsoleVo>();
try {
Workbook book = Workbook.getWorkbook(new File(fileName));
Sheet[] sheets = book.getSheets();
for (Sheet s : sheets) {
for (int i = 1; i < s.getRows(); i++) {
ConsoleVo m = new ConsoleVo();
Cell[] cells = s.getRow(i);
if (cells != null) {
System.out.println(cells.length);
m.setLoginName(StringUtil.getNotNullValueString(cells[0].getContents()));
m.setUserName(StringUtil.getNotNullValueString(cells[1].getContents()));
m.setDeptId(StringUtil.getNotNullValueString(cells[2].getContents()));
m.setOrders(StringUtil.getNotNullValueString(cells[3].getContents()));
list.add(m);
} else {
System.out.print("excel格式错误导入数据失败！");
return null;
}
}
}
book.close();
return list;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public Object[] next() {
Cell[] c = sheet.getRow(this.currentRowNo);
Map<String, String> data = new HashMap<String, String>();
// List<String> list = new ArrayList<String>();
for (int i = 0; i < this.columnNum; i++) {
String temp = "";
try {
temp = c[i].getContents().toString();
} catch (ArrayIndexOutOfBoundsException ex) {
temp = "";
}
// if(temp != null&& !temp.equals(""))
// list.add(temp);
data.put(this.columnnName[i], temp);
}
Object object[] = new Object[1];
object[0] = data;
this.currentRowNo++;
return object;
}
public ExcelDataProvider(String classname, String methodname) {
try {
int dotNum = classname.indexOf(".");
if (dotNum > 0) {
classname = classname.substring(classname.lastIndexOf(".") + 1, classname.length());
}
String path = "data/" + classname + ".xls";
InputStream inputStream = new FileInputStream(path);
book = Workbook.getWorkbook(inputStream);
// 方法名即为sheet名
sheet = book.getSheet(methodname);
// sheet = book.getSheet(0);
rowNum = sheet.getRows();
Cell[] cell = sheet.getRow(0);
columnNum = cell.length;
columnnName = new String[cell.length];
for (int i = 0; i < cell.length; i++) {
columnnName[i] = cell[i].getContents().toString();
}
this.currentRowNo++;
} catch (Exception e) {
e.printStackTrace();
Assert.fail("unable to read Excel data");
}
}
public List<String> getNonEmptyHeaders(Sheet sheet) {
List<String> headers = new ArrayList<String>();
Cell[] headerCells = sheet.getRow(0); // assume headers are on first line
for (int i = 0; i < headerCells.length; i++) {
if (!headerCells[i].getContents().equals("")) {
headers.add(headerCells[i].getContents());
}
}
return headers;
}
/** NOTE: Copied from InvestigationExcelReader */
private void writeSheetToFile(Sheet sheet, File file) throws FileNotFoundException {
List<String> headers = new ArrayList<String>();
Cell[] headerCells = sheet.getRow(0); // assume headers are on first
// line
ArrayList<Integer> namelessHeaderLocations = new ArrayList<Integer>(); // allow
// for
// empty
// columns,
// also
// column
// order
// does
// not
// matter
for (int i = 0; i < headerCells.length; i++) {
if (!headerCells[i].getContents().equals("")) {
headers.add(headerCells[i].getContents());
} else {
headers.add("nameless" + i);
namelessHeaderLocations.add(i);
}
}
PrintWriter pw = new PrintWriter(file);
CsvWriter cw = new CsvWriter(pw, headers);
cw.setMissingValue("");
cw.writeHeader();
for (int rowIndex = 1; rowIndex < sheet.getRows(); rowIndex++) {
Tuple t = new SimpleTuple();
int colIndex = 0;
for (Cell c : sheet.getRow(rowIndex)) {
if (!namelessHeaderLocations.contains(colIndex)) {
t.set(headers.get(colIndex), c.getContents());
}
colIndex++;
}
cw.writeRow(t);
}
cw.close();
}
/**
* Constructor
*
* @param w The workbook to interrogate
* @param out The output stream to which the CSV values are written
* @param encoding The encoding used by the output stream. Null or unrecognized values cause the
* encoding to default to UTF8
* @param hide Suppresses hidden cells
* @exception java.io.IOException
*/
public CSV(Workbook w, OutputStream out, String encoding, boolean hide) throws IOException {
if (encoding == null || !encoding.equals("UnicodeBig")) {
encoding = "UTF8";
}
try {
OutputStreamWriter osw = new OutputStreamWriter(out, encoding);
BufferedWriter bw = new BufferedWriter(osw);
for (int sheet = 0; sheet < w.getNumberOfSheets(); sheet++) {
Sheet s = w.getSheet(sheet);
if (!(hide && s.getSettings().isHidden())) {
bw.write("*** " + s.getName() + " ****");
bw.newLine();
Cell[] row = null;
for (int i = 0; i < s.getRows(); i++) {
row = s.getRow(i);
if (row.length > 0) {
if (!(hide && row[0].isHidden())) {
bw.write(row[0].getContents());
// Java 1.4 code to handle embedded commas
// bw.write("\"" +
// row[0].getContents().replaceAll("\"","\"\"")
// + "\"");
}
for (int j = 1; j < row.length; j++) {
bw.write(',');
if (!(hide && row[j].isHidden())) {
bw.write(row[j].getContents());
// Java 1.4 code to handle embedded quotes
// bw.write("\"" +
// row[j].getContents().replaceAll("\"","\"\"")
// + "\"");
}
}
}
bw.newLine();
}
}
}
bw.flush();
bw.close();
} catch (UnsupportedEncodingException e) {
System.err.println(e.toString());
}
}
/** Writes out the workbook data as XML, without formatting information */
private void writeXML() throws IOException {
try {
OutputStreamWriter osw = new OutputStreamWriter(out, encoding);
BufferedWriter bw = new BufferedWriter(osw);
bw.write("<?xml version=\"1.0\" ?>");
bw.newLine();
bw.write("<!DOCTYPE workbook SYSTEM \"workbook.dtd\">");
bw.newLine();
bw.newLine();
bw.write("<workbook>");
bw.newLine();
for (int sheet = 0; sheet < workbook.getNumberOfSheets(); sheet++) {
Sheet s = workbook.getSheet(sheet);
bw.write(" <sheet>");
bw.newLine();
bw.write(" <name><![CDATA[" + s.getName() + "]]></name>");
bw.newLine();
Cell[] row = null;
for (int i = 0; i < s.getRows(); i++) {
bw.write(" <row number=\"" + i + "\">");
bw.newLine();
row = s.getRow(i);
for (int j = 0; j < row.length; j++) {
if (row[j].getType() != CellType.EMPTY) {
bw.write(" <col number=\"" + j + "\">");
bw.write("<![CDATA[" + row[j].getContents() + "]]>");
bw.write("</col>");
bw.newLine();
}
}
bw.write(" </row>");
bw.newLine();
}
bw.write(" </sheet>");
bw.newLine();
}
bw.write("</workbook>");
bw.newLine();
bw.flush();
bw.close();
} catch (UnsupportedEncodingException e) {
System.err.println(e.toString());
}
}
private boolean writeSheetToFile(Sheet sheet, File file) throws FileNotFoundException {
List<String> headers = new ArrayList<String>();
Cell[] headerCells = sheet.getRow(0); // assume headers are on first line
if (headerCells.length == 0) {
return false;
}
ArrayList<Integer> namelessHeaderLocations =
new ArrayList<Integer>(); // allow for empty columns, also column order does not matter
for (int i = 0; i < headerCells.length; i++) {
if (!headerCells[i].getContents().equals("")) {
headers.add(headerCells[i].getContents());
} else {
headers.add("nameless" + i);
namelessHeaderLocations.add(i);
}
}
PrintWriter pw = new PrintWriter(file);
CsvWriter cw = new CsvWriter(pw, headers);
cw.setMissingValue("");
cw.writeHeader();
for (int rowIndex = 1; rowIndex < sheet.getRows(); rowIndex++) {
Tuple t = new SimpleTuple();
int colIndex = 0;
for (Cell c : sheet.getRow(rowIndex)) {
if (!namelessHeaderLocations.contains(colIndex)
&& colIndex < headers.size()
&& c.getContents() != null) {
t.set(headers.get(colIndex), c.getContents());
}
colIndex++;
}
cw.writeRow(t);
}
cw.close();
return true;
}
public boolean hasNext() {
if (this.rowNum == 0 || this.currentRowNo >= this.rowNum) {
try {
book.close();
} catch (Exception e) {
e.printStackTrace();
}
return false;
} else {
// sheet下一行内容为空判定结束
if ((sheet.getRow(currentRowNo))[0].getContents().equals("")) return false;
return true;
}
}
/**
* 读取Excel数据
*
* @param pBegin 从第几行开始读数据<br>
* <b>注意下标索引从0开始的哦!
* @return 以List<BaseDTO>形式返回数据
* @throws BiffException
* @throws IOException
*/
public List read(int pBegin) throws BiffException, IOException {
List list = new ArrayList();
Workbook workbook = Workbook.getWorkbook(getIs());
Sheet sheet = workbook.getSheet(0);
int rows = sheet.getRows();
for (int i = pBegin; i < rows; i++) {
Dto rowDto = new BaseDto();
Cell[] cells = sheet.getRow(i);
for (int j = 0; j < cells.length; j++) {
String key = getMetaData().trim().split(",")[j];
if (G4Utils.isNotEmpty(key)) rowDto.put(key, cells[j].getContents());
}
list.add(rowDto);
}
return list;
}
private void initialise() {
Workbook workbook = WorkbookSingleton.getWorkbook(workbookFileName);
Sheet currentSheet = workbook.getSheet(ColumnIndexes.FAILURECLASS__SHEETNO);
Cell[] row;
for (int i = 1; i < currentSheet.getRows(); i++) {
row = currentSheet.getRow(i);
if (row.length > 0) {
createFailure(
Integer.parseInt(row[ColumnIndexes.FAILURECLASS_FAILURECLASS_COLNO].getContents()),
row[ColumnIndexes.FAILURECLASS_DESCRIPTION_COLNO].getContents());
}
}
}
/**
* Get spreadsheet rows.
*
* @return
* @throws BiffException
* @throws FileNotFoundException
* @throws IOException
*/
private List<Cell[]> getRows() throws BiffException, FileNotFoundException, IOException {
ArrayList<Cell[]> rows = new ArrayList<Cell[]>();
if (spreadsheet.exists()) {
WorkbookSettings ws = new WorkbookSettings();
ws.setLocale(new Locale("en", "EN"));
FileInputStream fis = new FileInputStream(spreadsheet);
Workbook workbook = Workbook.getWorkbook(fis, ws);
Sheet sheet = workbook.getSheet(0);
int endRow = sheet.getRows();
for (int row = 0; row < endRow; row++) {
Cell[] cells = sheet.getRow(row);
if (!isBlankRow(cells)) {
rows.add(cells);
}
}
}
return rows;
}
private void initialise() {
Workbook workbook = WorkbookSingleton.getWorkbook(workbookFileName);
Sheet currentSheet = workbook.getSheet(ColumnIndexes.UE__SHEETNO);
Cell[] row;
for (int i = 1; i < currentSheet.getRows(); i++) {
row = currentSheet.getRow(i);
if (row.length > 0) {
String concatCapabilities = row[ColumnIndexes.UE_ACCESSCAPABILITY_COLNO].getContents();
String[] indivCapabilities = concatCapabilities.split(", ");
for (int j = 0; j < indivCapabilities.length; j++) {
if (PersistenceUtil.findAccessCapability(indivCapabilities[j]) == null) {
createAccessCapability(indivCapabilities[j]);
}
}
}
}
}
/**
* @param is 要导入Excel的输入流
* @param sheetName 导入的工作表名称
* @param entityClass List中对象的类型（Excel中的每一行都要转化为该类型的对象）
* @param fieldMap 类的英文属性和Excel中的中文列名的对应关系 例：{id=编号}
* @param uniqueFields 指定业务主键组合（即复合主键），这些列的组合不能重复
* @return List
* @throws ExcelException @Description 将Excel转化成实体对象List
*/
public static <T> List<T> excelToList(
InputStream is,
String sheetName,
Class<T> entityClass,
LinkedHashMap<String, String> fieldMap,
String[] uniqueFields)
throws ExcelException {
// 定义要返回的list
List<T> resultList = new ArrayList<T>();
try {
// 根据excel数据源创建WorkBook
Workbook wb = Workbook.getWorkbook(is);
// 获取工作表
Sheet sheet = wb.getSheet(sheetName);
// 获取工作表的有效行数
int realRows = 0;
for (int i = 0; i < sheet.getRows(); i++) {
int nullCols = 0;
for (int j = 0; j < sheet.getColumns(); j++) {
Cell CurrentCell = sheet.getCell(j, i);
if (CurrentCell == null || "".equals(CurrentCell.getContents().toString())) {
nullCols++;
}
}
if (nullCols == sheet.getColumns()) {
break;
} else {
realRows++;
}
}
// 如果Excel中没有任何数据则提示错误信息
if (realRows <= 1) {
throw new ExcelException("Excel文件中没有任何数据");
}
Cell[] firstRow = sheet.getRow(0);
String[] excelFieldNames = new String[firstRow.length];
// 获取Excel的列名
for (int i = 0; i < firstRow.length; i++) {
excelFieldNames[i] = firstRow[i].getContents().toString().trim();
}
// 判断需要的字段在Excel中是否都存在
boolean isExist = true;
List<String> excelFieldList = Arrays.asList(excelFieldNames);
for (String cnName : fieldMap.values()) {
if (!excelFieldList.contains(cnName)) {
isExist = false;
break;
}
}
// 如果有列名不存在或不匹配，则抛出异常并提示错误
if (!isExist) {
throw new ExcelException("Excel中缺少必要的字段，或字段名称有误");
}
// 将列名和列号放入Map中，这样通过列名就可以拿到列号
LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>();
for (int i = 0; i < excelFieldNames.length; i++) {
colMap.put(excelFieldNames[i], firstRow[i].getColumn());
}
// 判断是否有重复行
// 1.获取uniqueFields指定的列
Cell[][] uniqueCells = new Cell[uniqueFields.length][];
for (int i = 0; i < uniqueFields.length; i++) {
int col = colMap.get(uniqueFields[i]);
uniqueCells[i] = sheet.getColumn(col);
}
// 2.从指定列中寻找重复行
for (int i = 1; i < realRows; i++) {
int nullCols = 0;
int length = uniqueFields.length;
for (int j = 0; j < length; j++) {
Cell currentCell = uniqueCells[j][i];
String currentContent = currentCell.getContents().toString().trim();
Cell sameCell =
sheet.findCell(
currentContent,
currentCell.getColumn(),
currentCell.getRow() + 1,
currentCell.getColumn(),
uniqueCells[j][realRows - 1].getRow(),
true);
if (sameCell != null) {
nullCols++;
}
}
// 复合主键，意味着这些列的组合不能重复，
// 只有当所有的列都有重复的时候，才被认为是有重复行
if (nullCols == length) {
throw new Exception("Excel中有重复行，请检查");
}
}
// 将sheet转换为list
for (int i = 1; i < realRows; i++) {
// 新建要转换的对象
T entity = entityClass.newInstance();
// 给对象中的字段赋值
for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
// 获取英文字段名
String enNormalName = entry.getKey();
// 获取中文字段名
String cnNormalName = entry.getValue();
// 根据中文字段名获取列号
int col = colMap.get(cnNormalName);
// 获取当前单元格中的内容
String content = sheet.getCell(col, i).getContents().toString().trim();
// 给对象赋值
setFieldValueByName(enNormalName, content, entity);
}
resultList.add(entity);
}
} catch (Exception e) {
e.printStackTrace();
// 如果是ExcelException,则直接抛出
if (e instanceof ExcelException) {
throw (ExcelException) e;
} else {
// 否则将其包装成ExcelException,再将其抛出
throw new ExcelException("导入ExceL失败");
}
}
return resultList;
}
/**
* DOC xqliu Comment method "importToStucture".
*
* @param importObject
* @param selectionFolder
* @param type
* @param skip
* @param rename
* @param importItemName
* @return
*/
public static List<ReturnCode> importToStucture(
ImportObject importObject,
IFolder selectionFolder,
ExpressionType type,
boolean skip,
boolean rename,
String importItemName) {
List<ReturnCode> importEvent = new ArrayList<ReturnCode>();
// MOD qiongli 2012-12-20 TDQ-5899(issue 2),should get all patterns from Pattern folder.
Set<String> names =
PatternUtilities.getNestFolderPatternNames(
new HashSet<String>(), ResourceManager.getPatternFolder());
File importFile = importObject.getObjFile();
String fileExtName = getFileExtName(importFile);
if ("csv".equalsIgnoreCase(fileExtName)) { // $NON-NLS-1$
try {
CsvReader reader = new CsvReader(new FileReader(importFile), CURRENT_SEPARATOR);
reader.setEscapeMode(CsvReader.ESCAPE_MODE_DOUBLED);
reader.setTextQualifier(TEXT_QUAL);
reader.setUseTextQualifier(USE_TEXT_QUAL);
reader.readHeaders();
while (reader.readRecord()) {
String name = reader.get(PatternToExcelEnum.Label.getLiteral());
if (names.contains(name)) {
if (skip) {
importEvent.add(
new ReturnCode(
DefaultMessagesImpl.getString("ImportFactory.patternInported", name),
false)); //$NON-NLS-1$
continue;
}
if (rename) {
name = name + "(" + new Date() + ")"; // $NON-NLS-1$ //$NON-NLS-2$
}
}
PatternParameters patternParameters = new ImportFactory().new PatternParameters();
patternParameters.name = name;
patternParameters.auther = reader.get(PatternToExcelEnum.Author.getLiteral());
patternParameters.description = reader.get(PatternToExcelEnum.Description.getLiteral());
patternParameters.purpose = reader.get(PatternToExcelEnum.Purpose.getLiteral());
patternParameters.relativePath = reader.get(PatternToExcelEnum.RelativePath.getLiteral());
for (PatternLanguageType languagetype : PatternLanguageType.values()) {
String cellStr = reader.get(languagetype.getExcelEnum().getLiteral());
if (cellStr != null && !cellStr.equals("")) { // $NON-NLS-1$
patternParameters.regex.put(languagetype.getLiteral(), cellStr);
}
}
try {
TypedReturnCode<Object> create =
createAndStorePattern(patternParameters, selectionFolder, type);
if (create.isOk()) {
names.add(name);
importEvent.add(
new ReturnCode(
DefaultMessagesImpl.getString(
"ImportFactory.importPattern",
name, //$NON-NLS-1$
selectionFolder.getProjectRelativePath().toString()),
true));
} else {
throw new TalendInternalPersistenceException(create.getMessage());
}
} catch (Exception e) {
importEvent.add(
new ReturnCode(
DefaultMessagesImpl.getString("ImportFactory.SaveFailed", name),
false)); //$NON-NLS-1$
}
}
reader.close();
} catch (Exception e) {
log.error(e, e);
importEvent.add(
new ReturnCode(
DefaultMessagesImpl.getString("ImportFactory.importFailed"), false)); // $NON-NLS-1$
}
}
if ("xls".equalsIgnoreCase(fileExtName)) { // $NON-NLS-1$
Map<Integer, PatternLanguageType> expressionMap = new HashMap<Integer, PatternLanguageType>();
try {
WorkbookSettings settings = new WorkbookSettings();
settings.setEncoding("UTF-8"); // $NON-NLS-1$
Workbook rwb = Workbook.getWorkbook(importFile, settings);
Sheet[] sheets = rwb.getSheets();
for (Sheet sheet : sheets) {
Cell[] headerRow = sheet.getRow(0);
for (Cell cell : headerRow) {
for (PatternLanguageType languageType : PatternLanguageType.values()) {
if (cell.getContents().equals(languageType.getExcelEnum().getLiteral())) {
expressionMap.put(cell.getColumn(), languageType);
}
}
}
for (int i = 1; i < sheet.getRows(); i++) {
Cell[] row = sheet.getRow(i);
Cell cell = row[0];
if (CellType.LABEL.equals(cell.getType())) {
String contents = cell.getContents();
if (names.contains(contents)) {
if (skip) {
importEvent.add(
new ReturnCode(
DefaultMessagesImpl.getString(
"ImportFactory.patternInported", //$NON-NLS-1$
contents),
false));
continue;
}
if (rename) {
contents = contents + "(" + new Date() + ")"; // $NON-NLS-1$ //$NON-NLS-2$
}
}
PatternParameters patternParameters = new ImportFactory().new PatternParameters();
patternParameters.name = contents;
patternParameters.auther = row[6].getContents();
patternParameters.description = row[2].getContents();
patternParameters.purpose = row[1].getContents();
patternParameters.status = DevelopmentStatus.DRAFT.getLiteral();
for (int columnIndex : expressionMap.keySet()) {
String rowContent = row[columnIndex].getContents();
if (!rowContent.equals("")) { // $NON-NLS-1$
patternParameters.regex.put(
expressionMap.get(columnIndex).getLiteral(), rowContent);
}
}
try {
TypedReturnCode<Object> create =
createAndStorePattern(patternParameters, selectionFolder, type);
if (create.isOk()) {
names.add(contents);
importEvent.add(
new ReturnCode(
DefaultMessagesImpl.getString(
"ImportFactory.importPattern",
((TDQItem) create.getObject())
.getProperty()
.getDisplayName(), //$NON-NLS-1$
selectionFolder.getProjectRelativePath().toString()),
true));
} else {
throw new TalendInternalPersistenceException(create.getMessage());
}
} catch (Exception e) {
importEvent.add(
new ReturnCode(
DefaultMessagesImpl.getString("ImportFactory.SaveFailed", contents),
false)); //$NON-NLS-1$
}
}
}
}
rwb.close();
} catch (BiffException e) {
log.error(e, e);
importEvent.add(
new ReturnCode(
DefaultMessagesImpl.getString("ImportFactory.importFailed"), false)); // $NON-NLS-1$
} catch (IOException e) {
log.error(e, e);
importEvent.add(
new ReturnCode(
DefaultMessagesImpl.getString("ImportFactory.importFailed"), false)); // $NON-NLS-1$
}
}
importObject.copyJarFiles();
// ADD xqliu 2012-04-27 TDQ-5149
checkImportEvent(importItemName, importEvent);
// ~ TDQ-5149
return importEvent;
}
/**
* DOC xqliu Comment method "importIndicatorToStucture".
*
* @param importObject
* @param selectionFolder
* @param skip
* @param rename
* @param importItemName
* @return
*/
public static List<ReturnCode> importIndicatorToStucture(
ImportObject importObject,
IFolder selectionFolder,
boolean skip,
boolean rename,
String importItemName) {
List<ReturnCode> information = new ArrayList<ReturnCode>();
Set<String> names = UDIHelper.getAllIndicatorNames(selectionFolder);
File importFile = importObject.getObjFile();
String fileExtName = getFileExtName(importFile);
if ("csv".equalsIgnoreCase(fileExtName)) { // $NON-NLS-1$
String name = PluginConstant.EMPTY_STRING;
try {
CsvReader reader = new CsvReader(new FileReader(importFile), CURRENT_SEPARATOR);
// MOD zshen EscapeMode default is CsvReader.ESCAPE_MODE_DOUBLED
reader.setTextQualifier(TEXT_QUAL);
reader.setUseTextQualifier(USE_TEXT_QUAL);
reader.readHeaders();
java.text.SimpleDateFormat simpleDateFormat =
new java.text.SimpleDateFormat("yyyyMMddHHmmssSSS"); // $NON-NLS-1$
while (reader.readRecord()) {
name = reader.get(PatternToExcelEnum.Label.getLiteral());
if (names.contains(name)) {
if (skip) {
information.add(
new ReturnCode(
DefaultMessagesImpl.getString("ImportFactory.Imported", name),
false)); //$NON-NLS-1$
continue;
}
if (rename) {
name =
name
+ "("
+ simpleDateFormat.format(new Date())
+ Math.random()
+ ")"; //$NON-NLS-1$ //$NON-NLS-2$
}
}
UDIParameters udiParameters = new ImportFactory().new UDIParameters();
udiParameters.name = name;
udiParameters.auther = reader.get(PatternToExcelEnum.Author.getLiteral());
udiParameters.description = reader.get(PatternToExcelEnum.Description.getLiteral());
udiParameters.purpose = reader.get(PatternToExcelEnum.Purpose.getLiteral());
udiParameters.relativePath = reader.get(PatternToExcelEnum.RelativePath.getLiteral());
udiParameters.category = reader.get(PatternToExcelEnum.Category.getLiteral());
udiParameters.javaClassName = reader.get(PatternToExcelEnum.JavaClassName.getLiteral());
udiParameters.javaJarPath = reader.get(PatternToExcelEnum.JavaJarPath.getLiteral());
String[] headers = reader.getHeaders();
String[] columnsValue = reader.getValues();
HashMap<String, String> record = new HashMap<String, String>();
for (int i = 0; i < headers.length; i++) {
if (columnsValue[i] != null && columnsValue[i].length() > 0) {
record.put(headers[i], columnsValue[i]);
}
}
for (PatternLanguageType languagetype : PatternLanguageType.values()) {
String cellStr = record.get(languagetype.getExcelEnum().getLiteral());
if (cellStr != null && !cellStr.equals("\"\"")) { // $NON-NLS-1$
udiParameters.regex.put(languagetype.getLiteral(), trimQuote(cellStr));
}
}
udiParameters.setParaMap(buildIndDefPara(record));
TypedReturnCode<Object> create = createAndStoreUDI(udiParameters, selectionFolder);
if (create.isOk()) {
names.add(name);
// add the suscess message to display.
information.add(
new ReturnCode(
DefaultMessagesImpl.getString(
"ImportFactory.importedSucess" //$NON-NLS-1$
,
((TDQItem) create.getObject()).getProperty().getDisplayName(),
selectionFolder.getProjectRelativePath().toString()),
true));
} else {
throw new TalendInternalPersistenceException(create.getMessage());
}
}
reader.close();
} catch (Exception e) {
log.error(e, e);
information.add(
new ReturnCode(
DefaultMessagesImpl.getString("ImportFactory.importedFailed", name),
false)); //$NON-NLS-1$
}
}
if ("xls".equalsIgnoreCase(fileExtName)) { // $NON-NLS-1$
Map<Integer, PatternLanguageType> expressionMap = new HashMap<Integer, PatternLanguageType>();
String contents = PluginConstant.EMPTY_STRING;
try {
WorkbookSettings settings = new WorkbookSettings();
settings.setEncoding("UTF-8"); // $NON-NLS-1$
Workbook rwb = Workbook.getWorkbook(importFile, settings);
Sheet[] sheets = rwb.getSheets();
for (Sheet sheet : sheets) {
Cell[] headerRow = sheet.getRow(0);
for (Cell cell : headerRow) {
for (PatternLanguageType languageType : PatternLanguageType.values()) {
if (cell.getContents().equals(languageType.getExcelEnum().getLiteral())) {
expressionMap.put(cell.getColumn(), languageType);
}
}
}
for (int i = 1; i < sheet.getRows(); i++) {
Cell[] row = sheet.getRow(i);
Cell cell = row[0];
if (CellType.LABEL.equals(cell.getType())) {
contents = cell.getContents();
if (names.contains(contents)) {
if (skip) {
continue;
}
if (rename) {
contents = contents + "(" + new Date() + ")"; // $NON-NLS-1$ //$NON-NLS-2$
}
}
UDIParameters udiParameters = new ImportFactory().new UDIParameters();
udiParameters.name = contents;
udiParameters.auther = row[6].getContents();
udiParameters.description = row[2].getContents();
udiParameters.purpose = row[1].getContents();
udiParameters.status = DevelopmentStatus.DRAFT.getLiteral();
udiParameters.category = row[16].getContents();
for (int columnIndex : expressionMap.keySet()) {
String rowContent = row[columnIndex].getContents();
if (!rowContent.equals("")) { // $NON-NLS-1$
udiParameters.regex.put(expressionMap.get(columnIndex).getLiteral(), rowContent);
}
}
createAndStoreUDI(udiParameters, selectionFolder);
names.add(contents);
information.add(
new ReturnCode(
DefaultMessagesImpl.getString(
"ImportFactory.importedSucess" //$NON-NLS-1$
,
contents),
true));
}
}
}
rwb.close();
} catch (Exception e) {
log.error(e, e);
information.add(
new ReturnCode(
DefaultMessagesImpl.getString("ImportFactory.importedFailed", contents),
false)); //$NON-NLS-1$
}
}
// MOD qiongli 2011-11-28 TDQ-4038.consider to import the definition file.
if (FactoriesUtil.DEFINITION.equalsIgnoreCase(fileExtName)) {
String propFilePath =
importFile
.getPath()
.replaceFirst(
PluginConstant.DOT_STRING + fileExtName,
PluginConstant.DOT_STRING + FactoriesUtil.PROPERTIES_EXTENSION);
File propFile = new File(propFilePath);
// just import the definition file which have the realted Property file.
if (!propFile.exists()) {
return information;
}
String name = importFile.getName();
try {
if (names.contains(name)) {
if (skip) {
information.add(
new ReturnCode(
DefaultMessagesImpl.getString("ImportFactory.Imported", name),
false)); //$NON-NLS-1$
return information;
}
if (rename) {
name = name + "(" + new Date() + Math.random() + ")"; // $NON-NLS-1$ //$NON-NLS-2$
}
}
IFile elementFile = selectionFolder.getFile(name);
if (!elementFile.exists()) {
elementFile.create(new FileInputStream(importFile), false, null);
ModelElement modelElement = ModelElementFileFactory.getModelElement(elementFile);
if (modelElement != null) {
ElementWriterFactory.getInstance()
.createIndicatorDefinitionWriter()
.create(modelElement, selectionFolder);
DefinitionHandler.getInstance().reloadIndicatorsDefinitions();
names.add(name);
information.add(
new ReturnCode(
DefaultMessagesImpl.getString(
"ImportFactory.importedSucess" //$NON-NLS-1$
,
name),
true));
}
}
} catch (Exception e) {
log.error(e);
information.add(
new ReturnCode(
DefaultMessagesImpl.getString("ImportFactory.importedFailed", name),
false)); //$NON-NLS-1$
}
}
importObject.copyJarFiles();
// ADD xqliu 2012-04-27 TDQ-5149
checkImportEvent(importItemName, information);
// ~ TDQ-5149
return information;
}
private Result processRefundFastpayExcel(String realPath) {
// TODO Auto-generated method stub
Set<String> repetFilter = new HashSet(); // 过滤重复
Workbook book = null;
StringBuffer msg = new StringBuffer();
Result result = new Result();
// 2011112421847473^0.01^NOT_THIS_PARTNERS_TRAD
int batchNum = 0;
StringBuffer batchData = new StringBuffer();
StringBuffer relation = new StringBuffer();
BigDecimal totalRefund = new BigDecimal(0); // 退款总金额
boolean hasError = false;
try {
try {
book = Workbook.getWorkbook(new File(realPath));
} catch (BiffException e) {
e.printStackTrace();
result.setMsg("导入文件读取失败，请重新导入或者联系开发人员!<br>");
return result;
} catch (IOException e) {
e.printStackTrace();
result.setMsg("导入文件读取失败，请重新导入或者联系开发人员!<br>");
return result;
}
Sheet sheet = book.getSheet(0);
int count = sheet.getRows();
// 生成批量退款笔数及退款数据集
if (count <= 2) { // 没有数据
result.setMsg("没有数据!");
return result;
} else if (count
>= (Integer.valueOf(PropertiesUtil.getProperties("alipay.batchNumLimit", "1000"))
.intValue()
+ 2)) { // 超出最大笔数
result.setMsg(
"支付宝即时到账批量退款，最大支持"
+ PropertiesUtil.getProperties("alipay.batchNumLimit", "1000")
+ "笔!");
return result;
} else { // 校验合法性
for (int i = 1; i < count - 1; i++) { // 最后一行是合计
Cell[] cells = sheet.getRow(i);
String outTradeNo =
cells[Integer.valueOf(PropertiesUtil.getProperties("outTradeNOIndex", "0"))]
.getContents()
.trim();
if (!repetFilter.contains(outTradeNo)) {
repetFilter.add(outTradeNo);
} else {
msg.append(
"EXCEL第"
+ i
+ "行（不包含表头部分）数据-单品订单号["
+ outTradeNo
+ "]在导入文件的前面部分已经出现请合并为一条退款记录！<br/>");
hasError = true;
}
// 校验money
String totalFee =
cells[Integer.valueOf(PropertiesUtil.getProperties("totalFeeIndex", "0"))]
.getContents()
.trim();
totalRefund = totalRefund.add(new BigDecimal(totalFee));
String tradeNo = getTradeNoByOutTradeNo(outTradeNo, new BigDecimal(totalFee));
// 校验tradeNo逻辑
if (tradeNo == null) {
msg.append("EXCEL第" + i + "行（不包含表头部分）数据-单品订单号[" + outTradeNo + "]获取支付宝交易号失败！<br/>");
hasError = true;
} else if (tradeNo.indexOf(
PropertiesUtil.getProperties(
"alipay.interface.returncode.outoftotalfee", "OUTOFTOTALFEE"))
== 0) {
msg.append("EXCEL第" + i + "行（不包含表头部分）数据-单品订单号[" + outTradeNo + "]退款总金额大于当前可退款金额！<br/>");
hasError = true;
} else {
// 拼接relation （商家订单号1^支付宝交易号1#商家订单号2^支付宝交易号2）
relation.append(outTradeNo).append("^").append(tradeNo);
if (i < count - 2) {
relation.append("#");
}
}
String remark = PropertiesUtil.getProperties("refundRemark", "上品折扣支付宝退款");
// 处理逻辑
batchNum++;
batchData.append(tradeNo + "^" + totalFee + "^" + remark);
if (i < count - 2) {
batchData.append("#");
}
}
}
if (hasError) {
result.setMsg(msg.toString());
return result;
}
result =
refundFastpayService.processRefundFastpayExcel(
batchNum, batchData.toString(), realPath, relation.toString(), totalRefund);
} catch (RuntimeException e) {
e.printStackTrace();
result.setMsg("服务端异常，导致导入文件读取失败，请联系开发人员!<br>" + e.getMessage());
} finally {
book.close();
}
return result;
}
private String test() {
// 读取excel文件
String realPath = "C:/Users/wchao/Desktop/上品214快捷日获奖名单.xls";
Workbook book = null;
WritableWorkbook book2 = null;
WritableSheet sheet2 = null;
Connection conn = null;
conn =
JDBCUtil.getConnection(
JDBCUtil.getUrl("192.168.1.21", "1521", "danpin1", 0), "dev_user", "dev_user");
try {
try {
book2 = Workbook.createWorkbook(new File("C:/Users/wchao/Desktop/上品214快捷日获奖名单111.xls"));
sheet2 = book2.createSheet("第1页", 0);
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
book = Workbook.getWorkbook(new File(realPath));
Sheet sheet = book.getSheet(0);
int count = sheet.getRows();
System.out.println("--------------------------------------");
for (int i = 1; i < count; i++) { // 最后一行是合计
Cell[] cells = sheet.getRow(i);
String tradeNo = cells[2].getContents().trim();
StringBuffer result = new StringBuffer();
URL U = null;
BufferedReader in = null;
String sign = null;
Map<String, String> sParaTemp = new HashMap();
sParaTemp.put("out_trade_no", null);
sParaTemp.put("trade_no", tradeNo);
sParaTemp.put("service", "single_trade_query");
sParaTemp.put("partner", "2088002692486430");
sParaTemp.put("_input_charset", "utf-8");
Map<String, String> sPara = AlipayCore.paraFilter(sParaTemp);
// 生成签名结果
sign = AlipayCore.buildMysign(sPara); // 752cc62e72503d01c4cc93b654906918
try {
String url =
"https://www.alipay.com/cooperate/gateway.do?_input_charset=utf-8"
+ "&sign="
+ sign
+ "&_input_charset=utf-8"
+ "&sign_type=MD5"
+ "&service=single_trade_query"
+ "&partner=2088002692486430"
+ "&trade_no="
+ tradeNo;
U = new URL(url);
URLConnection connection = U.openConnection();
connection.connect();
in = new BufferedReader(new InputStreamReader(connection.getInputStream()));
String line;
while ((line = in.readLine()) != null) {
result.append(line);
// System.out.println("-------------------\n" + new String(line.getBytes("gbk"),
// "utf-8"));
}
StringReader sr =
new StringReader(new String(result.toString().getBytes("gbk"), "utf-8"));
BufferedReader br = new BufferedReader(sr);
try {
JAXBContext jaxbContext = JAXBContext.newInstance("net.shopin.alipay.entity");
Unmarshaller unMarshaller = jaxbContext.createUnmarshaller();
SchemaFactory schemaFactory =
SchemaFactory.newInstance("http://www.w3.org/2001/XMLSchema");
Schema schema =
schemaFactory.newSchema(
new File(
"E:/上品/支付宝批量退货/java/refund_fastpay_by_platform_nopwd_jsp_utf8/src/net/shopin/alipay/entity/singleTradeQuery.xsd"));
unMarshaller.setSchema(schema);
Alipay alipay = (Alipay) unMarshaller.unmarshal(U);
String outTradeNo = alipay.getResponse().getTrade().getOutTradeNo();
// 根据单品订单号查询收货人姓名 详细邮寄地址 联系电话 省份 城市 邮编
System.out.println(
"SELECT d.RECEPT_NAME, d.RECEPT_ADDRESS, d.RECEPT_PHONE, d.INCEPT_PROVINCE, d.INCEPT_CITY, d.INCEPT_POSTCODE "
+ "FROM DELIVERY d WHERE d.SID=(SELECT o.DELIVERY_SID FROM ORDERS o WHERE o.ORDER_NO='"
+ outTradeNo
+ "')");
// 循环结果集
Statement stmt = conn.createStatement();
ResultSet rs =
stmt.executeQuery(
"SELECT d.RECEPT_NAME, d.RECEPT_ADDRESS, d.RECEPT_PHONE, d.INCEPT_PROVINCE, d.INCEPT_CITY, d.INCEPT_POSTCODE "
+ "FROM DELIVERY d WHERE d.SID=(SELECT o.DELIVERY_SID FROM ORDERS o WHERE o.ORDER_NO='"
+ outTradeNo
+ "')");
while (rs.next()) {
Label cell0 = new Label(0, i, tradeNo);
Label cell1 = new Label(1, i, outTradeNo);
Label cell2 = new Label(2, i, rs.getString("RECEPT_NAME"));
Label cell3 = new Label(3, i, rs.getString("RECEPT_ADDRESS"));
Label cell4 = new Label(4, i, rs.getString("RECEPT_PHONE"));
Label cell5 = new Label(5, i, rs.getString("INCEPT_PROVINCE"));
Label cell6 = new Label(6, i, rs.getString("INCEPT_CITY"));
Label cell7 = new Label(7, i, rs.getString("INCEPT_POSTCODE"));
sheet2.addCell(cell0);
sheet2.addCell(cell1);
sheet2.addCell(cell2);
sheet2.addCell(cell3);
sheet2.addCell(cell4);
sheet2.addCell(cell5);
sheet2.addCell(cell6);
sheet2.addCell(cell7);
}
JDBCUtil.close(rs, stmt, null);
} catch (Exception e) {
e.printStackTrace();
}
} catch (Exception e) {
e.printStackTrace();
}
}
try {
book2.write();
book2.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IndexOutOfBoundsException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return null;
}
/** Writes out the workbook data as XML, with formatting information */
private void writeFormattedXML() throws IOException {
try {
OutputStreamWriter osw = new OutputStreamWriter(out, encoding);
BufferedWriter bw = new BufferedWriter(osw);
bw.write("<?xml version=\"1.0\" ?>");
bw.newLine();
bw.write("<!DOCTYPE workbook SYSTEM \"formatworkbook.dtd\">");
bw.newLine();
bw.newLine();
bw.write("<workbook>");
bw.newLine();
for (int sheet = 0; sheet < workbook.getNumberOfSheets(); sheet++) {
Sheet s = workbook.getSheet(sheet);
bw.write(" <sheet>");
bw.newLine();
bw.write(" <name><![CDATA[" + s.getName() + "]]></name>");
bw.newLine();
Cell[] row = null;
CellFormat format = null;
Font font = null;
for (int i = 0; i < s.getRows(); i++) {
bw.write(" <row number=\"" + i + "\">");
bw.newLine();
row = s.getRow(i);
for (int j = 0; j < row.length; j++) {
// Remember that empty cells can contain format information
if ((row[j].getType() != CellType.EMPTY) || (row[j].getCellFormat() != null)) {
format = row[j].getCellFormat();
bw.write(" <col number=\"" + j + "\">");
bw.newLine();
bw.write(" <data>");
bw.write("<![CDATA[" + row[j].getContents() + "]]>");
bw.write("</data>");
bw.newLine();
if (row[j].getCellFormat() != null) {
bw.write(" <format wrap=\"" + format.getWrap() + "\"");
bw.newLine();
bw.write(
" align=\"" + format.getAlignment().getDescription() + "\"");
bw.newLine();
bw.write(
" valign=\""
+ format.getVerticalAlignment().getDescription()
+ "\"");
bw.newLine();
bw.write(
" orientation=\""
+ format.getOrientation().getDescription()
+ "\"");
bw.write(">");
bw.newLine();
// The font information
font = format.getFont();
bw.write(" <font name=\"" + font.getName() + "\"");
bw.newLine();
bw.write(" point_size=\"" + font.getPointSize() + "\"");
bw.newLine();
bw.write(" bold_weight=\"" + font.getBoldWeight() + "\"");
bw.newLine();
bw.write(" italic=\"" + font.isItalic() + "\"");
bw.newLine();
bw.write(
" underline=\""
+ font.getUnderlineStyle().getDescription()
+ "\"");
bw.newLine();
bw.write(" colour=\"" + font.getColour().getDescription() + "\"");
bw.newLine();
bw.write(
" script=\"" + font.getScriptStyle().getDescription() + "\"");
bw.write(" />");
bw.newLine();
// The cell background information
if (format.getBackgroundColour() != Colour.DEFAULT_BACKGROUND
|| format.getPattern() != Pattern.NONE) {
bw.write(
" <background colour=\""
+ format.getBackgroundColour().getDescription()
+ "\"");
bw.newLine();
bw.write(
" pattern=\""
+ format.getPattern().getDescription()
+ "\"");
bw.write(" />");
bw.newLine();
}
// The cell border, if it has one
if (format.getBorder(Border.TOP) != BorderLineStyle.NONE
|| format.getBorder(Border.BOTTOM) != BorderLineStyle.NONE
|| format.getBorder(Border.LEFT) != BorderLineStyle.NONE
|| format.getBorder(Border.RIGHT) != BorderLineStyle.NONE) {
bw.write(
" <border top=\""
+ format.getBorder(Border.TOP).getDescription()
+ "\"");
bw.newLine();
bw.write(
" bottom=\""
+ format.getBorder(Border.BOTTOM).getDescription()
+ "\"");
bw.newLine();
bw.write(
" left=\""
+ format.getBorder(Border.LEFT).getDescription()
+ "\"");
bw.newLine();
bw.write(
" right=\""
+ format.getBorder(Border.RIGHT).getDescription()
+ "\"");
bw.write(" />");
bw.newLine();
}
// The cell number/date format
if (!format.getFormat().getFormatString().equals("")) {
bw.write(" <format_string string=\"");
bw.write(format.getFormat().getFormatString());
bw.write("\" />");
bw.newLine();
}
bw.write(" </format>");
bw.newLine();
}
bw.write(" </col>");
bw.newLine();
}
}
bw.write(" </row>");
bw.newLine();
}
bw.write(" </sheet>");
bw.newLine();
}
bw.write("</workbook>");
bw.newLine();
bw.flush();
bw.close();
} catch (UnsupportedEncodingException e) {
System.err.println(e.toString());
}
}