导入excel

public ActionForward importHcodeExcel(ActionMapping mapping,
ActionForm form, HttpServletRequest request,
HttpServletResponse response) {

Num189AssignForm num189AssignForm = (Num189AssignForm) form;
FormFile uploads = num189AssignForm.getUploads();
String errormsg = null;//"Excel导入失败";
String type = request.getParameter("type");
try {
if (type != null && type.equalsIgnoreCase("submit")) {
String extendname = getFileExtendName(uploads);

if ("xls".equalsIgnoreCase(extendname)) {

User user = getUserInfo(request);
Num189Importer importer = getNum189Importer(request);
List message = importer.importHcode(uploads
.getInputStream(), user);
//若干条数据错误时，列出错误信息
request.setAttribute("errors", message);
/*
if (message.size() > 0) {
errormsg = (String) message.get(0);
}*/
if (message.size() == 0) {
errormsg = "Excel导入成功";
}
} else if ("".equalsIgnoreCase(extendname)) {//文件有扩展名
errormsg = "文件不是Excel文件";
} else {//没有上传文件
errormsg = "文件上传失败";
}

request.setAttribute("errormsg", errormsg);
}
} catch (Exception e) {
e.printStackTrace();
sysLogger.error("importHcodeExcel method:", e);
errormsg = "文件上传失败";
request.setAttribute("errormsg", errormsg);
}

return mapping.findForward("importHcodeExcel");
}

/**
* 得到上传文件的扩展名
*
* @param upload
*            文件类
* @return String extendname 文件的扩展名
*/
private String getFileExtendName(FormFile upload) {

if (upload == null) {
return null;
}
try {
String extendname = upload.getFileName();

String[] arr = StringUtils.split(extendname, ".");

if (arr != null && arr.length > 0) {
int num = arr.length;
return arr[(num - 1)];
}
} catch (Exception e) {
e.printStackTrace();
sysLogger.error("getFileExtendName method:", e);
}
return "";
}
   
    private Num189Importer getNum189Importer(HttpServletRequest request) {
        return (Num189Importer)Tools.getBean(request, "num189Importer");
    }




public class Num189Importer {
   
 
   
    private DataSource dataSource;

public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
   
    private HSSFDataFormatter hdf;
   
    private DateFormat df;
   
    private final static Logger log = Logger.getLogger(Num189Importer.class);
   
    public Num189Importer() {
        hdf = new HSSFDataFormatter();
        df = new SimpleDateFormat("yyyy-MM-dd");
    }
   
/**
*
*
* @param ins
*            用于导入的Excel文件的输入流
* @return 错误消息列表
*/
public List importHcode(InputStream ins) {
        if (log.isDebugEnabled())
            log.debug("H码新建导入");
        //保存错误信息集
        List message = new ArrayList();               
HSSFWorkbook wb = null;
try {
wb = new HSSFWorkbook(ins);
} catch (IOException e) {
log.error("读取输入流错误", e);
message.add("读取输入流错误。");
return message;
}
return handleWorkbookHcode(wb);
}

private List handleWorkbookHcode(HSSFWorkbook wb) {
List message = new ArrayList();
HSSFSheet sheet = wb.getSheetAt(0);

//取得数据库连接
Connection conn = null;
try {
conn = dataSource.getConnection();
conn.setAutoCommit(false);
} catch (SQLException e) {
log.error("访问数据库异常", e);
message.add("访问数据库异常");
return message;
}

//读取Excel文件数据
for (Iterator it = sheet.rowIterator(); it.hasNext();) {
HSSFRow hssfRow = (HSSFRow) it.next();

//
if (hssfRow.getRowNum() == 4) {

for (int h = 0; h < H0s.length; h++) {
int h0sLen = h + 3;
String strh0temp = getCellValue(hssfRow.getCell(h0sLen));
if(strh0temp != null && strh0temp.length()>=4){
H0s[h] = strh0temp.substring(0,4);
}else{
H0s[h] = getCellValue(hssfRow.getCell(h0sLen));
}
}
}
            insertRowHcode(num189HLRVoList, message, conn, user);
}

if (message.size() == 0) {
if (log.isDebugEnabled())
                log.debug("新建操作成功");
}

//关闭数据库连接
if (message.size() > 0) { //存在错误, 则事务回滚
rollback(conn);
} else {
commit(conn, message); //无错误发生, 则提交事务
}
return message;
}

private void insertRowHcode(List num189HLRVoList, List message, Connection conn, User user) {
if (log.isDebugEnabled())
            log.debug("进入新建Ｈ码方法: insertRowHcode");
//插入操作
StringBuffer sqlSb = new StringBuffer("INSERT INTO TP_CDMA_RESOURCE(");
sqlSb.append("ROW_ID, HCODE_VALUE, TEL_PREXCODE,TML_ID,STATUS,");
sqlSb.append("create_date,last_modify_date,create_by,last_modify_by, REMARK, CDMA_PREFIX) ");
sqlSb.append("VALUES ");
sqlSb.append("(f_get_globalid,?,?,?,?,SYSDATE,SYSDATE,?,?,?,?)");

//取得电信管理区
String sql = "select t.row_id from ta_tml_cdma t where t.tml_name_text = ? ";
PreparedStatement psmt = null;
ResultSet rst = null;
String tmlId = "";
String hcodeValue = "";
try {
//根据城市，取得该城市的 电信管理区域ID
Num189HLRVo num189HLRVoTemp = (Num189HLRVo) num189HLRVoList.get(0);
psmt = conn.prepareStatement(sql);
psmt.setString(1, num189HLRVoTemp.getCityName());
rst = psmt.executeQuery();
while (rst.next()) {
tmlId = rst.getString(1);
}
rst.close();

if("".equals(tmlId)){
message.add("在第 " + num189HLRVoTemp.getLine() + "行,第 2 列,城市 " + num189HLRVoTemp.getCityName() + " 没有对应的本地网.");
}

hcodeValue = num189HLRVoTemp.getHcodeValue();
for (int i = 0; i < num189HLRVoList.size(); i++) {
Num189HLRVo num189HLRVo = (Num189HLRVo) num189HLRVoList.get(i);
psmt = conn.prepareStatement(sqlSb.toString());
psmt.setString(1, num189HLRVo.getHcodeValue());
psmt.setString(2, num189HLRVo.getTelPrexcode());
psmt.setString(3, tmlId);
psmt.setString(4, "ACTIVE");
psmt.setString(5, user.getRowId());
psmt.setString(6, user.getRowId());
psmt.setString(7, "导入");
psmt.setString(8, hcodeValue.substring(0,3));
psmt.executeUpdate();
}

if (log.isDebugEnabled())
log.debug("保存成功");

} catch (SQLException e) {
log.error("访问数据库异常", e);
message.add("访问数据库异常.");
} finally {
try {
if (psmt != null) {
psmt.close();
}
if (rst != null) {
rst.close();
}
} catch (SQLException e1) {
message.add("访问数据库异常!");
}
}
}


/**
* @return Returns the dataSource.
*/
public DataSource getDataSource() {
return dataSource;
}
}