返回功能

[xuancao]

<a href="#" onClick="javaScript:history.go(-1);" >返回</a>

[grapepaul]

除了接分，还能做什么？

[j2me_home]

接分

[MARS.nEIL]

jf

[xuancao]

/**
* <p>Title: 将Excel表中的数据导入DB2的表中DAO实现类</p>
* <p>Description: 首先将Excel表中的数据读出存放在一个集合中,将数据设置到VO中,
* 然后再通过DAO读入DB2的表中</p>
* <p>Copyright: Copyright (c) 2005</p>
* <p>Company: GuangDong GEVER GEVER TECH CO.LTD</p>
* @author 李冉冉
* Created on 2005-8-3
* @version 1.0
*/
package com.carry.ictrade.dataimport.dao.impl;

import com.carry.database.connection.ConnectionProvider;
import com.carry.database.connection.ConnectionProviderImpl;

import com.carry.ictrade.dataimport.dao.DataImportDAO;
import com.carry.ictrade.exception.DAOException;
import com.carry.ictrade.repertory.vo.ProductVO;
import com.carry.ictrade.util.IdMgr;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.FileInputStream;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import javax.servlet.http.HttpServletRequest;


public class ExlToDB2DAOImpl implements DataImportDAO {
private static String ADD_PRODUCT = "INSERT INTO " +
"I_REPERTORY_PRODUCT(PRODUCT_ID,TYPE_NAME,CHN_NAME,PRICE_NAME,ENCAP_NAME,MANUFACTURE_NAME," +
"NUMBER,DESCRIPTION,BATCH_NAME,IN_PRICE,REG_DATE,TEL,REMARK ) " +
"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
ConnectionProvider provider = null;
Connection conn = null;
PreparedStatement psmt = null;

//已经导入的数据数目
private long count = 0;

//存储记录集合
private List dataList = new ArrayList();

/*
* 将数据从Excel表中导入DB2表中
* @param request
* @return true：导入成功
* false：导入不成功
* @throws DAOException
* @throws SQLException
*/
public boolean importData(String filepath, String autoId) {
boolean bflag = true;
ProductVO vo = null;
int i = 0;
int jj = 0;
try {
dataList = readEXL(filepath, autoId);
Iterator it = dataList.iterator();

provider = ConnectionProviderImpl.newConnectionProvider();
conn = provider.getConnection();
conn.setAutoCommit(false);
psmt = conn.prepareStatement(ADD_PRODUCT);

while (it.hasNext()) {
i = 0;
jj++;
vo = (ProductVO) it.next();
System.out.println("the pk:" + vo.getProduct_id());
psmt.setString(++i, vo.getProduct_id());
psmt.setString(++i, vo.getType_name());
psmt.setString(++i, vo.getChn_name());
psmt.setString(++i, vo.getPrice_name());
psmt.setString(++i, vo.getEncap_name());
psmt.setString(++i, vo.getManufacture_name());
psmt.setInt(++i, Integer.parseInt(vo.getNumber()));
psmt.setString(++i, vo.getDescription());
psmt.setString(++i, vo.getBatch_name());
psmt.setDouble(++i, Double.parseDouble(vo.getIn_price()));
psmt.setDate(++i, java.sql.Date.valueOf(vo.getReg_date()));
psmt.setString(++i, vo.getTel());
psmt.setString(++i, vo.getRemark());
//psmt.execute();
psmt.addBatch();
setCount(jj);
}

psmt.executeBatch();
conn.commit();
} catch (Exception e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
bflag = false;
e.printStackTrace();
} finally {
try {
conn.setAutoCommit(true);
psmt.close();
provider.closeConnection(conn);
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}

}

return bflag;
}

[xuancao]

/*
* 读取Excel文档中的数据
* @return List
* @throws DAOException
*/
private List readEXL(String filepath, String autoId) throws DAOException {
List result = new ArrayList();

//String fileToBeRead = (String) request.getParameter("path");

//System.out.println(" ==============fileToBeRead" + fileToBeRead );
try {
/*
* Excel文件的存放位置。注意是正斜线
*/

//String fileToBeRead = "D:/库存.xls";
//创建对Excel工作簿文件的引用
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(filepath));

// 创建对工作表的引用。
// 是按名引用（假定那张表有着缺省名"Sheet1"）
HSSFSheet sheet = workbook.getSheet("Sheet1");
ProductVO vo = null;

//获得本地IP
//String ipAddress = request.getRemoteAddr();

for (int i = 1; i < (sheet.getLastRowNum() + 1); i++) {
/*
* 也可用getSheetAt(int index)按索引引用，
* 在Excel文档中，第一张工作表的缺省索引是0,
* 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);
* 读取第i行的单元格,每行共13个单元格,每个单元格的位置必须固定
* 第一个:单元格0:产品型号名
* 第二个:单元格1:制造商名
* 第三个:单元格2:产品数量
* 第四个:单元格3:描述
* 第五个:单元格4:批号名
* 第六个:单元格5:价格单名位
* 第七个:单元格6:进价 无货币单位符号
* 第八个:单元格7:封装名
* 第九个:单元格8:存入数据库时的日期 为1900-00-00格式
* 第十个:单元格9:供应商名
* 第十一个:单元格10:电话
* 第十二个:单元格11:备注
*/
HSSFRow row = sheet.getRow(i);

// HSSFCell cell_id = row.getCell((short) 0); //关键字
HSSFCell cell_type_name = row.getCell((short) 0); //产品型号名
HSSFCell cell_manufacture_name = row.getCell((short) 1); //制造商名
HSSFCell cell_number = row.getCell((short) 2); //产品数量
HSSFCell cell_description = row.getCell((short) 3); //描述
HSSFCell cell_batch_name = row.getCell((short) 4); //批号名
HSSFCell cell_price_name = row.getCell((short) 5); //价格单名位
HSSFCell cell_in_price = row.getCell((short) 6); //进价
HSSFCell cell_encap_name = row.getCell((short) 7); //封装名
HSSFCell cell_reg_date = row.getCell((short) 8); //存入数据库时的日期
HSSFCell cell_client_name = row.getCell((short) 9); //供应商名
HSSFCell cell_tel = row.getCell((short) 10); //电话
HSSFCell cell_remark = row.getCell((short) 11); //备注

//产品的关键字ID为IP+当前日期+当前时间+i的值转化为字符串
String productId = IdMgr.getAutoId(autoId) +
String.valueOf(i);

//将单元格的值设置到VO中
vo = new ProductVO();
vo.setProduct_id(productId);
vo.setType_name(getCellValue(cell_type_name));
vo.setChn_name(getCellValue(cell_client_name));
vo.setPrice_name(getCellValue(cell_price_name));
vo.setEncap_name(getCellValue(cell_encap_name));
vo.setManufacture_name(getCellValue(cell_manufacture_name));
vo.setNumber(getCellValue(cell_number));
vo.setDescription(getCellValue(cell_description));
vo.setBatch_name(getCellValue(cell_batch_name));

//获得进价的类型
int priceType = cell_in_price.getCellType();
String priceValue;

//但不为空时进价类型为数字型
if (priceType == 0) {
//获得单元格的值
priceValue = String.valueOf(cell_in_price.getNumericCellValue());

//如果整数位的长度大于7时,获得的值是用科学计数法表示的
int indexOfE = priceValue.indexOf("E");

//如果位科学计数法,则转换位普通形式
if (indexOfE > 0) {
//获得单元格值的长度
int len = priceValue.length();
//获得指数,即E后面的值
int exponent = Integer.parseInt(priceValue.substring(indexOfE +
1, len));

//如果原数值为123456789.55的形式,即小数点后还有值
if ((indexOfE - 2 - exponent) > 0) {
priceValue = priceValue.substring(0, 1) +
priceValue.substring(2, 2 + exponent) + "." +
priceValue.substring(2 + exponent, indexOfE);
} else if ((indexOfE - 2 - exponent) == 0) {//如果小数点后没值,即为123456789.00形式
priceValue = priceValue.substring(0, 1) +
priceValue.substring(2, indexOfE);
} else {//如果整数的最后含有0,即为1234567800的形式
priceValue = priceValue.substring(0, 1) +
priceValue.substring(2, indexOfE);

for (int k = 0; k < (exponent - indexOfE + 2);
k++) {
priceValue = priceValue + "0";
}
}
}
} else {//如果为空时,为字符串的形式
priceValue = cell_in_price.getStringCellValue();
}

vo.setIn_price(priceValue);

//时间类型
int type = cell_reg_date.getCellType();
String cell_reg_date_str;

if (type == 0) {
//此句显示为1900:00:00 00:00:00 形式
cell_reg_date_str = HSSFDateUtil.getJavaDate(cell_reg_date.getNumericCellValue())
.toLocaleString();

int endIndex = cell_reg_date_str.indexOf(" ");

//将后面的时间截取,只显示日期
if (endIndex > 0) {
cell_reg_date_str = cell_reg_date_str.substring(0,
endIndex);
}
} else {//如果为空时,为字符串形式
cell_reg_date_str = cell_reg_date.getStringCellValue();
}

vo.setReg_date(cell_reg_date_str);

vo.setTel(getCellValue(cell_tel));
vo.setRemark(getCellValue(cell_remark));
result.add(vo);
}
} catch (Exception e) {
e.printStackTrace();
throw new DAOException(e.getMessage());
}

return result;
}