用POI导入Excel内容时

package 

[align=center][align=center][align=left][/align][/align][/align]

oa.business;

import java.io.File;
import java.io.IOException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;



public class ImportExcelManager extends ManagerBase{

@Autowired
private DataManager dataManager;

@Autowired
private CustomIdService customIdService;

public static final String AREA_NAME = "华北市场";


public void importProvider(){
Workbook wb = null;
try {
//wb = WorkbookFactory.create(new File("G:/华东供应商.xls"));
wb = WorkbookFactory.create(new File("f:/东莞供应商.xls"));
Sheet sheet = wb.getSheetAt(0);
Row firstRow = sheet.getRow(0);
sheet.removeRow(firstRow);
int rowNum = 0;
for(Row row : sheet){
//int index = 0;
if(isBlankRow(row)){
int i = 0;
continue;
}
rowNum++;
if(rowNum >= 6){
int i = 0;
i = 9;
}
Provider provider = new Provider();
Linkman linkman = new Linkman();
LinkmanOrganization linkOrg = new LinkmanOrganization();
for(int index = row.getFirstCellNum();index < row.getLastCellNum();index++){
Cell cell = row.getCell(index);
if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) continue;
try{
String val = null;
if(index == 16) {
int i = 0 ;
int b = i+1;
}

/* if(HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()){
if (HSSFDateUtil.isCellDateFormatted(cell)) {
try{
SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd");
         Date dt = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
         val = dateformat.format(dt);
//System.out.println(strdate);
}catch(Exception e){e.printStackTrace();}
}else{
cell.setCellType(Cell.CELL_TYPE_STRING);
val = cell.getStringCellValue();
}
}else{*/
cell.setCellType(Cell.CELL_TYPE_STRING);
val = cell.getStringCellValue();
//}
//RichTextString rich = cell.getRichStringCellValue();
//val = rich.getString();
switch (index) {
case 0:{//编号
String code = this.generateProviderCode(AREA_NAME);
provider.setCode(code);
break;
}
case 1:{//公司注册名称
provider.setRegisterCompany(val);
break;
}
case 2:{//法定代表人
provider.setLegalPerson(val);
break;
}
case 3:{//经营范围
provider.setBusinessScope(val);
break;
}
case 4:{//有效期限起
if(StringUtils.isBlank(val)){
break;
}
/*val = val.replaceAll("/", "-").replaceAll("\\.", "-");
try{
SimpleDateFormat format = new  SimpleDateFormat("yyyy-MM-dd");
Date date = format.parse(val);
Timestamp time = new Timestamp(date.getTime());
provider.setStartValidTime(time);
}catch(Exception e){e.printStackTrace();}*/

try{
double value = Double.parseDouble(val);
Date date = DateUtil.getJavaDate(value);
Timestamp time = new Timestamp(date.getTime());
provider.setStartValidTime(time);
}catch(Exception e){
e.printStackTrace();
/**
* 有可能时间格式 为2013.9.19
* 故在异常这里再格式化下时间再转
*/
val = val.replaceAll("/", "-").replaceAll("\\.", "-");
try{
SimpleDateFormat format = new  SimpleDateFormat("yyyy-MM-dd");
Date date = format.parse(val);
Timestamp time = new Timestamp(date.getTime());
provider.setStartValidTime(time);
}catch(Exception ex){ex.printStackTrace();}
}
break;
}
case 5:{//有效期限止
if(StringUtils.isBlank(val)){
break;
}
/*val = val.replaceAll("/", "-").replaceAll("\\.", "-");
try{
SimpleDateFormat format = new  SimpleDateFormat("yyyy-MM-dd");
Date date = format.parse(val);
Timestamp time = new Timestamp(date.getTime());
provider.setEndVdlidTime(time);
}catch(Exception e){e.printStackTrace();}*/
try{
double value = Double.parseDouble(val);
Date date = DateUtil.getJavaDate(value);
Timestamp time = new Timestamp(date.getTime());
provider.setEndVdlidTime(time);
}catch(Exception e){
e.printStackTrace();
/**
* 有可能时间格式 为2013.9.19
* 故在异常这里再格式化下时间再转
*/
val = val.replaceAll("/", "-").replaceAll("\\.", "-");
try{
SimpleDateFormat format = new  SimpleDateFormat("yyyy-MM-dd");
Date date = format.parse(val);
Timestamp time = new Timestamp(date.getTime());
provider.setEndVdlidTime(time);
}catch(Exception ex){ex.printStackTrace();}
}
break;
}
case 6:{//区域
//Entity entity = this.findOrg(val);
Entity entity = this.findOrg(AREA_NAME);
if(entity != null){
String orgId = (String) entity.get("id");
provider.setAreaId(orgId);
}
break;
}
case 7:{//证照全称
provider.setPermitName(val);
break;
}
case 8:{//证照号码
provider.setPermitNumber(val);
break;
}
case 9:{//证照地址
provider.setPermitAddress(val);
break;
}
case 10:{//负责人
provider.setPrincipal(val);
break;
}
case 11:{//发证机关
provider.setIssueOrgans(val);
break;
}
case 12:{//资质证书
//provider.setQualityCertificate(val);
if(StringUtils.isNotBlank(val)){
provider.setMark("资质证书:" + val);
}
break;
}
case 13:{//姓名
linkman.setName(val);
break;
}
case 14:{//电话
linkman.setPhone(val);
break;
}
case 15:{//邮箱
linkman.setEmail(val);
break;
}
case 16:{//地址
linkman.setAddress(val);
break;
}
case 17:{//相关餐厅
if(StringUtils.isNotBlank(val)){
Entity entity = this.findOrg(val.trim());
if(entity != null){
String orgId = (String) entity.get("id");
linkOrg.setOrgId(orgId);
}
}
break;
}
default:
break;
}

}catch(Exception e){
e.printStackTrace();
}
}
try{
this.saveProviderAndLinkman(provider, linkman, linkOrg);
}catch(Exception e){
e.printStackTrace();
}
}
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}

public void importProvider2(){

}

public void importPermit(){

Workbook wb = null;
try {
//wb = WorkbookFactory.create(new File("G:/华东供应商.xls"));
wb = WorkbookFactory.create(new File("G:/华北市场13年新店许可证信息统计.xls"));
Sheet sheet = wb.getSheetAt(0);
Row firstRow = sheet.getRow(0);
sheet.removeRow(firstRow);
int rowNum = 0;
for(Row row : sheet){
//int index = 0;
if(isBlankRow(row)){
int i = 0;
continue;
}
rowNum++;
if(rowNum >= 6){
int i = 0;
i = 9;
}

Permit permit = new Permit();
for(int index = row.getFirstCellNum();index < row.getLastCellNum();index++){
Cell cell = row.getCell(index);
if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) continue;
try{
String val = null;
if(index == 16) {
int i = 0 ;
int b = i+1;
}
cell.setCellType(Cell.CELL_TYPE_STRING);
val = cell.getStringCellValue();
switch (index) {
case 0:{//序号
break;
}
case 1:{//区域
if(StringUtils.isNotBlank(val)){
Entity entity = this.findOrg(val.trim());
if(entity != null){
String orgId = (String) entity.get("id");
permit.setAreaId(orgId);
}
permit.setAreaId_text(val);
}

break;
}
case 2:{//餐厅编号
permit.setDinerCode(val);
break;
}
case 3:{//餐厅名称
permit.setDinerName(val);
break;
}
case 4:{//法人公司
permit.setLegalCompany(val);
break;
}
case 5:{//公司注册名称
permit.setRegisterCompany(val);
break;
}
case 6:{//证照全称
permit.setPermitName(val);
break;
}
case 7:{//证照号码
permit.setPermitNumber(val);
break;
}
case 8:{//地址或营业场所
permit.setAddress(val);
break;
}
case 9:{//法人代表
permit.setLegalDelegate(val);
break;
}
case 10:{//证照类型
permit.setPermitType(val);
break;
}
case 11:{//证可范围或者经营范围
permit.setBusinessScope(val);
break;
}
case 12:{//有效期起

if(StringUtils.isBlank(val)){
break;
}
try{
double value = Double.parseDouble(val);
Date date = DateUtil.getJavaDate(value);
Timestamp time = new Timestamp(date.getTime());
permit.setStartValidTime(time);
}catch(Exception e){
e.printStackTrace();
/**
* 有可能时间格式 为2013.9.19
* 故在异常这里再格式化下时间再转
**/
val = val.replaceAll("/", "-").replaceAll("\\.", "-");
try{
SimpleDateFormat format = new  SimpleDateFormat("yyyy-MM-dd");
Date date = format.parse(val);
Timestamp time = new Timestamp(date.getTime());
permit.setStartValidTime(time);
}catch(Exception ex){ex.printStackTrace();}
}
break;
}
case 13:{//有效期止
if(StringUtils.isBlank(val)){
break;
}
try{
double value = Double.parseDouble(val);
Date date = DateUtil.getJavaDate(value);
Timestamp time = new Timestamp(date.getTime());
permit.setEndValidTime(time);
}catch(Exception e){
e.printStackTrace();
/**
* 有可能时间格式 为2013.9.19
* 故在异常这里再格式化下时间再转
**/
val = val.replaceAll("/", "-").replaceAll("\\.", "-");
try{
SimpleDateFormat format = new  SimpleDateFormat("yyyy-MM-dd");
Date date = format.parse(val);
Timestamp time = new Timestamp(date.getTime());
permit.setEndValidTime(time);
}catch(Exception ex){ex.printStackTrace();}
}
break;
}
case 14:{//发证机关
permit.setIssueOrgans(val);
break;
}
case 15:{//证照状态
permit.setStatus(val);
break;
}

default:
break;
}

}catch(Exception e){
e.printStackTrace();
}
}
try{
this.savePermit(permit);
}catch(Exception e){
e.printStackTrace();
}
}

} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}


}

public Entity findOrg(String name){
Map<String,String> params = new HashMap<String,String>();
params.put("name", name);
Entity entity = this.dao.query("Organization", "name =: name",params).firstOrNull();
return entity;
}

/**
* 生成供应商编号
* @return
*/
public String generateProviderCode(String name){
String code = customIdService.generateCustomId("Provider");
Entity entity = this.findOrg(name);
String companyCode = "";
if(entity != null){
if(null != entity.get("orgNum")){
companyCode =  entity.get("orgNum").toString();
}
}
code = code.replace(":companyCode", companyCode);
return code;
}

@Transactional(propagation = Propagation.REQUIRED)
public void saveProviderAndLinkman(Provider provider,Linkman linkman,LinkmanOrganization linkOrg){
//保存供应商
String providerId  = UUID.randomUUID().toString();
provider.setId(providerId);
this.dataManager.insert("Provider", provider);
//保存联系人
linkman.setProviderId(providerId);
String linkmanId = UUID.randomUUID().toString();
this.dataManager.insert("Linkman", linkman);
//保存联系人与餐厅的关系
if(StringUtils.isNotBlank(linkOrg.getOrgId())){
linkOrg.setId(linkmanId);
this.dataManager.insert("LinkmanOrganization", linkOrg);
}
}

public void savePermit(Permit permit){
String permitId = UUID.randomUUID().toString();
permit.setId(permitId);
this.dataManager.insert("Permit", permit);
}

public  boolean isBlankRow(Row row){
        if(row == null) return true;
        for(int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++){
        Cell cell = row.getCell(i);
        if(cell == null) continue;
            if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
            }else{
            return false;
            }
        }
        return true;
    }


public static void main(String[] args) {
//String str = "2012/11/28";
/*String str = "2013/7/24";
str = str.replaceAll("/", "-");
System.out.println(str);
str = str.replaceAll("\\.", "-");
System.out.println(str);

SimpleDateFormat format = new  SimpleDateFormat("yyyy-MM-dd");
try {
Date date = new Date(); //format.parse(str);
date.setTime(41241);
System.out.println(date);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}*/

Date date = DateUtil.getJavaDate(40758.0);
System.out.println(date);


/* List<Map<String,Object>> list = this.dataManager.findAll("provider");
for(Map<String,Object> obj : list){
String id = obj.get("id").toString();
Map<String,Object> filds = new HashMap<String,Object>();
filds.put("qualityCertificate", null);
String qualityCertificate = obj.get("qualityCertificate") == null ? "" : obj.get("qualityCertificate").toString();
if(StringUtils.isNotBlank(qualityCertificate)){
filds.put("mark", "资质证书：" + qualityCertificate);
}
this.dataManager.update(Provider.class, id, filds);
}*/
}

}