package com.itsv.oa.sattaxpayer;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.apache.struts.upload.FormFile;
public class NSRImportExcel {
public List importExcel(FormFile excelFile)
throws java.text.ParseException {
List tempNsrxxList = new ArrayList();
try {
InputStream streamIn = excelFile.getInputStream();
Workbook workBook = Workbook.getWorkbook(streamIn);
Sheet sheet = workBook.getSheet(0);
int rows = sheet.getRows();
for (int i = 1; i < rows; i++) {
String xh = sheet.getCell(0, i).getContents().trim();
String nsrsbh = sheet.getCell(1, i).getContents().trim();
String nsrmc = sheet.getCell(2, i).getContents().trim();
String zjhm = sheet.getCell(3, i).getContents().trim();
String gsdjzzh = sheet.getCell(4, i).getContents().trim();
String fddbrmc = sheet.getCell(5, i).getContents().trim();
String frYddhhm = sheet.getCell(6, i).getContents().trim();
String cwfzrmc = sheet.getCell(7, i).getContents().trim();
String cwfzrYddhhm = sheet.getCell(8, i).getContents().trim();
String bsrmc = sheet.getCell(9, i).getContents().trim();
String bsrYddhhm = sheet.getCell(10, i).getContents().trim();
StringBuffer sqlStrUpdate = new StringBuffer();
sqlStrUpdate.append(" update sat_nsrxx set")
.append(" nsrsbh='").append(nsrsbh).append("',")
.append(" nsrmc='").append(nsrmc).append("',")
.append(" zjhm='").append(zjhm).append("',")
.append(" gsdjzzh='").append(gsdjzzh).append("',")
.append(" fddbrmc='").append(fddbrmc).append("',")
.append(" fr_yddhhm='").append(frYddhhm).append("',")
.append(" cwfzrmc='").append(cwfzrmc).append("',")
.append(" cwfzr_yddhhm='").append(cwfzrYddhhm).append("',")
.append(" bsrmc='").append(bsrmc).append("',")
.append(" bsr_yddhhm='").append(bsrYddhhm).append("'")
.append(" where nsrsbh = '").append(nsrsbh).append("'");
StringBuffer sqlStrSave = new StringBuffer();
sqlStrSave.append(" insert into sat_nsrxx (nsrsbh,nsrmc,zjhm,gsdjzzh,fddbrmc,fr_yddhhm,cwfzrmc,cwfzr_yddhhm,bsrmc,bsr_yddhhm)")
.append(" select")
.append(" '").append(nsrsbh).append("',")
.append(" '").append(nsrmc).append("',")
.append(" '").append(zjhm).append("',")
.append(" '").append(gsdjzzh).append("',")
.append(" '").append(fddbrmc).append("',")
.append(" '").append(frYddhhm).append("',")
.append(" '").append(cwfzrmc).append("',")
.append(" '").append(cwfzrYddhhm).append("',")
.append(" '").append(bsrmc).append("',")
.append(" '").append(bsrYddhhm).append("'")
.append(" from dual")
.append(" where not exists(select nsrsbh from Sat_Nsrxx where nsrsbh='").append(nsrsbh).append("')");
tempNsrxxList.add(sqlStrUpdate.toString());
tempNsrxxList.add(sqlStrSave.toString());
}
} catch (FileNotFoundException e) {
System.out.println(e.getStackTrace());
} catch (BiffException e) {
System.out.println(e.getStackTrace());
} catch (IndexOutOfBoundsException e) {
System.out.println(e.getStackTrace());
} catch (IOException e) {
System.out.println(e.getStackTrace());
}
return tempNsrxxList;
}
}
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.apache.struts.upload.FormFile;
public class NSRImportExcel {
public List importExcel(FormFile excelFile)
throws java.text.ParseException {
List tempNsrxxList = new ArrayList();
try {
InputStream streamIn = excelFile.getInputStream();
Workbook workBook = Workbook.getWorkbook(streamIn);
Sheet sheet = workBook.getSheet(0);
int rows = sheet.getRows();
for (int i = 1; i < rows; i++) {
String xh = sheet.getCell(0, i).getContents().trim();
String nsrsbh = sheet.getCell(1, i).getContents().trim();
String nsrmc = sheet.getCell(2, i).getContents().trim();
String zjhm = sheet.getCell(3, i).getContents().trim();
String gsdjzzh = sheet.getCell(4, i).getContents().trim();
String fddbrmc = sheet.getCell(5, i).getContents().trim();
String frYddhhm = sheet.getCell(6, i).getContents().trim();
String cwfzrmc = sheet.getCell(7, i).getContents().trim();
String cwfzrYddhhm = sheet.getCell(8, i).getContents().trim();
String bsrmc = sheet.getCell(9, i).getContents().trim();
String bsrYddhhm = sheet.getCell(10, i).getContents().trim();
StringBuffer sqlStrUpdate = new StringBuffer();
sqlStrUpdate.append(" update sat_nsrxx set")
.append(" nsrsbh='").append(nsrsbh).append("',")
.append(" nsrmc='").append(nsrmc).append("',")
.append(" zjhm='").append(zjhm).append("',")
.append(" gsdjzzh='").append(gsdjzzh).append("',")
.append(" fddbrmc='").append(fddbrmc).append("',")
.append(" fr_yddhhm='").append(frYddhhm).append("',")
.append(" cwfzrmc='").append(cwfzrmc).append("',")
.append(" cwfzr_yddhhm='").append(cwfzrYddhhm).append("',")
.append(" bsrmc='").append(bsrmc).append("',")
.append(" bsr_yddhhm='").append(bsrYddhhm).append("'")
.append(" where nsrsbh = '").append(nsrsbh).append("'");
StringBuffer sqlStrSave = new StringBuffer();
sqlStrSave.append(" insert into sat_nsrxx (nsrsbh,nsrmc,zjhm,gsdjzzh,fddbrmc,fr_yddhhm,cwfzrmc,cwfzr_yddhhm,bsrmc,bsr_yddhhm)")
.append(" select")
.append(" '").append(nsrsbh).append("',")
.append(" '").append(nsrmc).append("',")
.append(" '").append(zjhm).append("',")
.append(" '").append(gsdjzzh).append("',")
.append(" '").append(fddbrmc).append("',")
.append(" '").append(frYddhhm).append("',")
.append(" '").append(cwfzrmc).append("',")
.append(" '").append(cwfzrYddhhm).append("',")
.append(" '").append(bsrmc).append("',")
.append(" '").append(bsrYddhhm).append("'")
.append(" from dual")
.append(" where not exists(select nsrsbh from Sat_Nsrxx where nsrsbh='").append(nsrsbh).append("')");
tempNsrxxList.add(sqlStrUpdate.toString());
tempNsrxxList.add(sqlStrSave.toString());
}
} catch (FileNotFoundException e) {
System.out.println(e.getStackTrace());
} catch (BiffException e) {
System.out.println(e.getStackTrace());
} catch (IndexOutOfBoundsException e) {
System.out.println(e.getStackTrace());
} catch (IOException e) {
System.out.println(e.getStackTrace());
}
return tempNsrxxList;
}
}