public static List<String[]> readExcel(Context ctx, String fileName, Object targetSheet) {
List<String[]> rowList = new ArrayList<String[]>();
try {
InputStream mInputStream = ctx.getResources().getAssets().open(fileName);
Workbook wb = Workbook.getWorkbook(mInputStream);
Sheet mSheet = null;
if (targetSheet instanceof Integer) {
mSheet = wb.getSheet((Integer) targetSheet);
} else {
mSheet = wb.getSheet((String) targetSheet);
}
int row = getRowCount(mSheet);
int columns = getColCount(mSheet);
Log.d("W", "Total Row: " + row + ", Total Columns: " + columns);
String[] colArray = new String[columns];
for (int i = 1; i < row; i++) {
for (int j = 0; j < columns; j++) {
Cell temp = mSheet.getCell(j, i);
String content = temp.getContents();
Log.d("W", j + " ," + i + " ," + content);
colArray[j] = content;
}
rowList.add(colArray);
}
wb.close();
mInputStream.close();
} catch (BiffException e) {
e.printStackTrace();
} catch (IndexOutOfBoundsException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return rowList;
}
public ArrayList<String> getCitiesFromExcel() {
ArrayList<String> cities = new ArrayList<String>();
try {
Workbook workbook =
Workbook.getWorkbook(this.getClass().getResourceAsStream("/resource/cities.xls"));
int totalsheet = workbook.getNumberOfSheets();
for (int i = 0; i < totalsheet; i++) {
Sheet sheet = workbook.getSheet(i);
int rows = sheet.getRows();
for (int j = 1; j < rows; j++) {
Cell cell1 = sheet.getCell(0, j);
cities.add(cell1.getContents());
}
}
return cities;
} catch (IOException e) {
e.printStackTrace();
return null;
} catch (BiffException e) {
e.printStackTrace();
return null;
}
}
public Map<String, Semantic> loadAxiom() {
Map<String, Semantic> axiom = new HashMap<String, Semantic>();
Workbook readwb = null;
try {
readwb = Workbook.getWorkbook(new File("src/cn/edu/buaa/resources/Axiom.xls"));
Sheet readsheet = readwb.getSheet(0);
int rsRows = readsheet.getRows();
int i = 1;
while (i < rsRows) {
String name = readsheet.getCell(0, i).getContents().trim();
if (null == name || name.equals("")) continue;
List<Item> semanSet = new ArrayList<Item>();
Semantic seman = new Semantic(semanSet);
while (i < rsRows) {
String tmp = readsheet.getCell(0, i).getContents().trim();
if (null == tmp || tmp.equals("") || tmp.equals(name)) {
String premise = readsheet.getCell(1, i).getContents().trim();
String left = readsheet.getCell(2, i).getContents().trim();
String right = readsheet.getCell(3, i).getContents().trim();
if (null == premise || premise.equals("")) premise = null;
if (null == left || left.equals("")) left = null;
if (null == right || right.equals("")) right = null;
if (null != premise || null != left || null != right) {
Item item = new Item(premise, left, right);
semanSet.add(item);
}
i++;
} else {
break;
}
}
axiom.put(name, seman);
name = null;
}
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (null != readwb) {
readwb.close();
}
}
// test axiom
for (String name : axiom.keySet()) {
System.out.println(name + ":");
List<Item> items = axiom.get(name).getSemanSet();
for (Item e : items) {
System.out.println(e.getPremise() + " -> " + e.getLeft() + " = " + e.getRight());
}
}
System.out.println("======================================");
return axiom;
}
public static WritableWorkbook getWorkBook(File file) {
if (file == null) return null;
Workbook workbookIn;
WritableWorkbook workbook = null;
try {
workbookIn = Workbook.getWorkbook(file);
workbook = Workbook.createWorkbook(file, workbookIn);
} catch (IOException e) {
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
}
return workbook;
}
@Test
public void testGetCollection() {
ExcelUtil excelUtil = new ExcelUtil();
try {
excelUtil.openUploadExcel("collectionTemplate.xls", 0);
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println(excelUtil.getCollection());
excelUtil.closeExcel();
}
@Override
public List<? extends Persistable> getAll() {
try {
List<ArrayList<String>> result;
for (List<String> list : (result = xclAdaptee.read(file))) {
// MappingStrategy<? extends Persistable> ms = TODO
processLines();
}
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
/** @param args */
public static void main(String[] args) {
// TODO Auto-generated method stub
try {
Workbook book = Workbook.getWorkbook(new File("测试.xls"));
Sheet sheet = book.getSheet(0);
int x = sheet.getRows();
int y = sheet.getColumns();
System.out.println("表格的列数为：" + x);
System.out.println("表格的行数为：" + y);
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public void removeAll(List<? extends Persistable> objList) {
try {
xclAdaptee.write(file, new ArrayList<ArrayList<String>>());
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 用于根据模板生成新的xls
*
* @param tplFile 模板文件路径
* @param sFile 新的文件路径
*/
public ExcelOper(File tplFile, File sFile) {
try {
// 取得模板文件
workbook = Workbook.getWorkbook(tplFile);
// 生成新文件
wrWorkbook = Workbook.createWorkbook(sFile, workbook);
readOnlyWBook = false;
} catch (BiffException e) {
// 写日志
logger.error(e.getMessage());
// 抛出异常
throw new BaseException(e);
} catch (IOException e) {
// 写日志
logger.error(e.getMessage());
// 抛出异常
throw new BaseException(e);
}
}
/**
* 读取Excel文件的内容
*
* @param file 待读取的文件
* @return
*/
public static String readExcel(File file) {
StringBuffer sb = new StringBuffer();
Workbook wb = null;
try {
// 构造Workbook（工作薄）对象
wb = Workbook.getWorkbook(file);
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
if (wb == null) return null;
// 获得了Workbook对象之后，就可以通过它得到Sheet（工作表）对象了
Sheet[] sheet = wb.getSheets();
if (sheet != null && sheet.length > 0) {
// 对每个工作表进行循环
for (int i = 0; i < sheet.length; i++) {
// 得到当前工作表的行数
int rowNum = sheet[i].getRows();
for (int j = 0; j < rowNum; j++) {
// 得到当前行的所有单元格
Cell[] cells = sheet[i].getRow(j);
if (cells != null && cells.length > 0) {
// 对每个单元格进行循环
for (int k = 0; k < cells.length; k++) {
// 读取当前单元格的值
String cellValue = cells[k].getContents();
sb.append(cellValue + "\t");
}
}
sb.append("\r\n");
}
sb.append("\r\n");
}
}
// 最后关闭资源，释放内存
wb.close();
return sb.toString();
}
/** 只用于读取xls数据 xls文件名做成参数 */
public ExcelOper(File file) {
try {
// Workbook workbook = null;
readOnlyWBook = true;
// 设置workbook
workbook = Workbook.getWorkbook(file);
} catch (BiffException e) {
logger.error(e.getMessage());
throw new BaseException(e);
} catch (IOException e) {
logger.error(e.getMessage());
throw new BaseException(e);
}
// 设置当前工作workbook
if (workbook != null) {
setWorkbook(workbook);
}
}
@Override
public void addAll(List<? extends Persistable> objList) {
ArrayList<ArrayList<String>> serializedList = new ArrayList<>();
for (Persistable p : objList) {
ArrayList<String> lineLs = p.serializeToStringArray();
serializedList.add(lineLs);
}
try {
xclAdaptee.write(file, serializedList);
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
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;
}
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;
}
public void readData(ItemMgr itemMgr) throws SQLException {
Connection conn = OracleGate.getConnection();
itemMgr.getPnList().clear();
Workbook workbook = null;
try {
workbook = Workbook.getWorkbook(this.getCurrentFile());
Sheet sheet = workbook.getSheet(0); // the first sheet in excel file
int rows = sheet.getRows();
int columns = sheet.getColumns();
for (int i = 1; i < rows; i++) {
if (sheet.getCell(1, i).getContents().trim() == null
|| (sheet.getCell(1, i).getContents().trim()).equals("")) break;
Part part = new Part("");
if (columns > 1) {
part.setPnCode(sheet.getCell(1, i).getContents().trim());
}
if (columns > 2) {
part.setPnName(sheet.getCell(2, i).getContents().trim());
}
if (columns > 3) {
part.setPartFullName(sheet.getCell(3, i).getContents().trim());
}
if (columns > 4) {
part.setPartTemplateCode(sheet.getCell(4, i).getContents().trim());
}
if (columns > 5) {
part.setRecurType(sheet.getCell(5, i).getContents().trim());
}
if (columns > 6) {
part.setAccrued(sheet.getCell(6, i).getContents().trim().equals("Y"));
}
if (columns > 7) {
part.setCashAccount(this.findAccounting(conn, sheet.getCell(7, i).getContents().trim()));
}
if (columns > 8) {
part.setClaimAccount(this.findAccounting(conn, sheet.getCell(8, i).getContents().trim()));
}
if (columns > 9) {
part.setEntityAccount(
this.findAccounting(conn, sheet.getCell(9, i).getContents().trim()));
}
part.setPnRowNo(i);
itemMgr.getPnList().add(part);
}
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
OracleGate.freeConnection(conn);
workbook.close();
} catch (Exception e) {
}
}
}
/**
* 读取奖惩管理EXCEL内容
*
* @param mapping
* @param form
* @param request
* @param response
* @return
*/
public ActionForward readJcglExcl(
ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
DynaActionForm fileForm = (DynaActionForm) form;
FormFile formFile = (FormFile) fileForm.get("objExcel");
List list = new ArrayList();
try {
InputStream excelInput = formFile.getInputStream();
Workbook workbook = Workbook.getWorkbook(excelInput);
Sheet sheet = workbook.getSheet(0);
int rows = sheet.getRows();
Xs0709 xs0709 = null;
for (int i = 9; i < rows; i++) {
try {
String xh = sheet.getCell(0, i).getContents().trim();
String wjrq = sheet.getCell(2, i).getContents().trim();
String wjlb = sheet.getCell(3, i).getContents().trim();
String cfmc = sheet.getCell(4, i).getContents().trim();
String cfdw = sheet.getCell(5, i).getContents().trim();
String wjjj = sheet.getCell(6, i).getContents().trim();
String cfwh = sheet.getCell(7, i).getContents().trim();
String cfrq = sheet.getCell(8, i).getContents().trim();
xs0709 = new Xs0709();
Xs0101 xs0101 = this.getJeffglServiceImpl().findXs0101Byxh(xh);
if (xs0101 == null) {
this.writeJsMessage(
response,
"alert('导入失败，第"
+ i
+ "行，‘学号’列的值:"
+ xh
+ "，在系统中不存在，请确认后再导入');window.parent.returnValue='ok';window.parent.close();");
return null;
}
xs0709.setXs0101(xs0101);
if (wjrq != null && !wjrq.equals("")) {
xs0709.setWjrq(wjrq);
} else {
this.writeJsMessage(
response,
"alert('导入失败，第"
+ i
+ "行，‘违纪日期’列的值为空，请确认后再导入');window.parent.returnValue='ok';window.parent.close();");
return null;
}
ZzdWjlb zdwjlb = null;
if (wjlb != null && !wjlb.equals("")) {
zdwjlb =
(ZzdWjlb)
ZzdSingleton.getInstance(request.getSession().getServletContext())
.getZdByNameKey("ZzdWjlb", wjlb);
} else {
this.writeJsMessage(
response,
"alert('导入失败，第"
+ i
+ "行，‘违纪类别’列的值为空，请确认后再导入');window.parent.returnValue='ok';window.parent.close();");
return null;
}
if (zdwjlb == null) {
this.writeJsMessage(
response,
"alert('导入失败，第"
+ i
+ "行，‘违纪类别’列的值在系统中不存在，请确认后再导入');window.parent.returnValue='ok';window.parent.close();");
return null;
} else {
xs0709.setZzdWjlb(zdwjlb);
}
ZzdCfmc zdwjmc = null;
if (cfmc != null && !cfmc.equals("")) {
zdwjmc =
(ZzdCfmc)
ZzdSingleton.getInstance(request.getSession().getServletContext())
.getZdByNameKey("ZzdCfmc", cfmc);
} else {
this.writeJsMessage(
response,
"alert('导入失败，第"
+ i
+ "行，‘处分名称’列的值为空，请确认后再导入');window.parent.returnValue='ok';window.parent.close();");
return null;
}
if (zdwjmc == null) {
this.writeJsMessage(
response,
"alert('导入失败，第"
+ i
+ "行，‘处分名称’列的值在系统中不存在，请确认后再导入');window.parent.returnValue='ok';window.parent.close();");
return null;
} else {
xs0709.setZzdCfmc(zdwjmc);
}
if (cfdw != null && !cfdw.equals("")) {
xs0709.setGycfdw(cfdw);
}
if (wjjj != null && !wjjj.equals("")) {
xs0709.setWjjk(wjjj);
}
if (cfwh != null && !cfwh.equals("")) {
xs0709.setCfwh(cfwh);
}
if (cfrq != null && !cfrq.equals("")) {
xs0709.setCfrq(cfrq);
}
list.add(xs0709);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
workbook.close();
} catch (FileNotFoundException e) {
this.writeJsMessage(
response,
"alert('数据导入失败，请检查EXCEL文件是否存在!');window.parent.returnValue='ok';window.parent.close();");
e.printStackTrace();
return null;
} catch (IOException e) {
this.writeJsMessage(
response,
"alert('数据导入失败，请检查EXCEL格式是否符合要求!');window.parent.returnValue='ok';window.parent.close();");
e.printStackTrace();
return null;
} catch (BiffException e) {
e.printStackTrace();
this.writeJsMessage(
response,
"alert('数据导入失败，请检查EXCEL格式是否符合要求!');window.parent.returnValue='ok';window.parent.close();");
return null;
} catch (Exception e) {
e.printStackTrace();
this.writeJsMessage(
response,
"alert('数据导入失败，系统没有检测到的错误!');window.parent.returnValue='ok';window.parent.close();");
return null;
}
try {
if (list.size() != 0) {
Iterator it = list.iterator();
Xs0709 xs0709 = null;
while (it.hasNext()) {
xs0709 = (Xs0709) it.next();
this.getIWjglService().saveXs0709(xs0709, request);
}
}
this.writeJsMessage(
response, "alert('数据导入成功!');window.parent.returnValue='ok';window.parent.close();");
} catch (Exception e) {
this.writeJsMessage(
response,
"alert('数据导入失败，一般是由于数据不符合格式引起，请检查数据!');window.parent.returnValue='ok';window.parent.close();");
}
return null;
}
public String readFile() throws IOException {
File inputWorkbook = new File(archivo);
Workbook w;
try {
String msg = "";
w = Workbook.getWorkbook(inputWorkbook);
// Get the first sheet
Sheet sheet = w.getSheet(0);
// int defaultRows = sheet.getRows();
// Si la cantidad de columnas es 4
if (sheet.getColumns() >= 4 && sheet.getRows() > 1) {
// Valido que las cabeceras tengan el formato correcto
if (!sheet.getCell(0, 0).getContents().equals("ORIGEN")
|| !sheet.getCell(1, 0).getContents().equals("DESTINO")
|| !sheet.getCell(2, 0).getContents().equals("PRODUCTO")
|| !sheet.getCell(3, 0).getContents().equals("PIEZAS")) {
msg = Msg.translate(getCtx(), "Column Names");
return msg;
}
// El vector que almacena la estructura de traspasos
Vector<Movimiento> movimientos = new Vector<Movimiento>();
Iterator<Movimiento> iterator_movimientos = null;
Movimiento movimiento_temporal = null, movimiento_nuevo = null;
for (int i = 1;
i < sheet.getRows() && getStore(sheet.getCell(0, i).getContents()) != null;
i++) {
// Capturo el origen
MWarehouse origen = getStore(sheet.getCell(0, i).getContents());
MWarehouse destino = getStore(sheet.getCell(1, i).getContents());
String isStore = "N";
// Aca debo bucar los values de los m_warehouse que son cd
String sql =
"SELECT XX_IsStore FROM M_Warehouse WHERE M_Warehouse_ID in ( "
+ origen.getM_Warehouse_ID()
+ ","
+ destino.getM_Warehouse_ID()
+ ")";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, null);
rs = pstmt.executeQuery();
while (rs.next()) {
if (rs.getString(1).equals("Y")) isStore = rs.getString(1);
}
} catch (Exception a) {
log.log(Level.SEVERE, sql, a);
} finally {
DB.closeResultSet(rs);
DB.closeStatement(pstmt);
}
if (origen == null || isStore.equals("Y"))
return Msg.translate(getCtx(), "Cell A Error")
+ (i + 1)
+ " "
+ sheet.getCell(0, i).getContents();
// Capturo el destino
if (destino == null || isStore.equals("Y"))
return Msg.translate(getCtx(), "Cell B Error")
+ (i + 1)
+ " "
+ sheet.getCell(1, i).getContents();
// Capturo el producto
String p_id = sheet.getCell(2, i).getContents();
MProduct producto = getProduct(p_id);
if (producto == null) {
return Msg.translate(getCtx(), "Cell C Error")
+ (i + 1)
+ " "
+ sheet.getCell(2, i).getContents();
}
// Capturo las piezas
Double piezas = null;
try {
if (sheet.getCell(3, i) != null)
piezas = Double.parseDouble(sheet.getCell(3, i).getContents());
} catch (NumberFormatException e) {
return Msg.translate(getCtx(), "Cell D Error")
+ (i + 1)
+ " "
+ sheet.getCell(3, i).getContents();
}
if (piezas == null) {
return Msg.translate(getCtx(), "Cell D Error")
+ (i + 1)
+ " "
+ sheet.getCell(3, i).getContents();
}
// Agregar cada fila al vector de traspasos
iterator_movimientos = movimientos.iterator();
boolean encontrado = false;
while (iterator_movimientos.hasNext()) {
movimiento_temporal = iterator_movimientos.next();
// Si un traspaso anterior va a la misma tienda, departamento, etc
if (movimiento_temporal.origen == origen.get_ID()) {
if (movimiento_temporal.destino == destino.get_ID()) {
if (movimiento_temporal.departamento == producto.getXX_VMR_Department_ID()) {
// Entonces agregar algo al objeto traspaso
movimiento_temporal.agregarCantidades(producto.get_ID(), piezas);
encontrado = true;
break;
}
}
}
}
// Si no se encontró en el vector de traspasos agregarlo
if (!encontrado) {
movimiento_nuevo =
new Movimiento(
origen.get_ID(), destino.get_ID(), producto.getXX_VMR_Department_ID());
movimiento_nuevo.agregarCantidades(producto.get_ID(), piezas);
movimientos.add(movimiento_nuevo);
}
}
// Una vez leido todo el archivo se procede a crear los traspasos
// System.out.println(traspasos);
return procesarMovimientos(movimientos).toString();
} else {
return Msg.translate(getCtx(), "4 Columns");
}
} catch (BiffException e) {
log.log(Level.SEVERE, e.getMessage());
}
return "";
}