//excel文件
private File xlsFile;
//excel列名
private static final String[] SALARYIMPORTVALUE_COLUMN_NAMES = { “机组名称”,“显示顺序”, “备注”};
/**
* 从excel文件导入操作任务 add by ytrao 1202
* @throws IOException
*/
public void importWoTickTask() throws IOException{
String parentBlockId = request.getParameter("parentBlockId");
String code = remote.findMaxCode(parentBlockId);
InputStream is=null;
Workbook workbook=null;
RunCWorkticketBlock opTask = null;
String msg = "";
List opTaskList = new ArrayList();
try {
is = new FileInputStream(xlsFile);
workbook = Workbook.getWorkbook(is);// 得到工作薄
Sheet sheet = workbook.getSheet(0);// 得到工作薄中的第一个工作表
int rows = sheet.getRows();// 得到excel的总行数
if (rows == 0) {
msg = "{success:true,msg:'无数据进行导入!'}";
write(msg);
} else if (rows == 1) {
msg = "{success:true,msg:'文件除一列头行外,至少还需一行数据!'}";
write(msg);
} else {
int[] column_indexs = new int[sheet.getRow(0).length];
String columnError = null;
columnError = addColumnIndex(column_indexs, sheet.getRow(0));
// 列名是否存在问题
if (columnError != null){
write(columnError);
return;
}
outer:
for (int i = 1; i < rows; i++) {
opTask = new RunCWorkticketBlock();
Cell[] cells = sheet.getRow(i);// i行的所有单元格
if(cells.length > column_indexs.length){
i++;
msg = "第" + (i+1)+ "行的数据超过列头行!
";
break;
}
for (int j = 0; j < column_indexs.length; j++) {
column_indexs[j] = j;
//0 : 序号
// 1:机组名称
if (column_indexs[j] == 0) {
if (cells.length > j
&& !"".equals(cells[j].getContents())) {
for (RunCWorkticketBlock entity : opTaskList) {
if (cells[j].getContents().equals(entity.getBlockName())) {
msg+= "Excel中第" +(i+1) + "行机组名称重复!
";
continue outer;
}
}
String list = remote.findByPId(parentBlockId);
if(list !=null&& list !=""){
//System.out.println(list);
String arry[] = list.split(",");
//System.out.println(“555555” +arry[0]);
for(int a=arry.length-1;a>=0;a–){
if (cells[j].getContents().equals(arry[a])) {
msg+= "Excel中第" +(i+1) + "行机组名称已经存在!
";
continue outer;
}
}
}
opTask.setBlockName(cells[j].getContents());
}else{
continue outer;
}
}
// 2:显示序号
else if (column_indexs[j] == 1) {
if (cells.length > j&& !"".equals(cells[j].getContents())) {
opTask.setDisplayNo(Long.parseLong(cells[j].getContents()));
}
}
// 3:备注
else if (column_indexs[j] == 2) {
if (cells.length > j
&& !"".equals(cells[j].getContents())) {
opTask.setMemo(cells[j].getContents());
}
}
}
opTask.setParentBlockId(Long.parseLong(parentBlockId));
opTask.setLastModifyBy(employee.getWorkerCode());
opTask.setLastModifyDate(new Date());
opTask.setIsUse("Y");
opTaskList.add(opTask);
}
if(msg.equals(""))
{
for(RunCWorkticketBlock entity : opTaskList){
remote.save(entity);
}
write("{success:true,msg:'导入成功!'}");
}
else
{
write("{success:true,msg:'数据填写存在问题或机组名称重复,请确认后在导入,
"+msg+"'}");
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
workbook.close();
is.close();
}
}
/**
* 检测导入的Excel文件列名是否正确
* @param column_indexs
* @param cells
* @return String
*/
private String addColumnIndex(int[] column_indexs, Cell[] cells) {
for (int i = 0; i < cells.length; i++) {
boolean isError = true;
for (int j = 0; j < SALARYIMPORTVALUE_COLUMN_NAMES.length; j++) {
if (SALARYIMPORTVALUE_COLUMN_NAMES[j].equals(cells[i].getContents())) {
column_indexs[i] = j;
isError = false;
}
}
if (isError) {
return "{success:true,msg:'" + cells[i].getContents()
+ "列不是要导入的具体列!'}";
}
}
return null;
}
public File getXlsFile() {
return xlsFile;
}
public void setXlsFile(File xlsFile) {
this.xlsFile = xlsFile;
}
以前做传统行业开发,用到的导入模型