jxl导入Excel

• 功能描述： 导入Excel

• @return

• @throws ActionException

• @throws IOException

*/

public String importExcel()throws ActionException{

if (logger.isInfoEnabled()){

logger.info("***** VideoResAction.importExcel() method begin*****");

}

Integer code = Integer.parseInt(request.getParameter("code"));

String path=ServletActionContext.getServletContext().getRealPath(File.separatorChar + "upload");

File destFile = new File(path);

if(!destFile.isDirectory()) {

destFile.mkdirs();

}

if( this.getFile() != null ) {

String fileName = System.currentTimeMillis()+".xls";

boolean b = false;

b = FileUpload.upload(this.getFile(), path, fileName);

if( b ) {

ExcelReader excelReader = new ExcelReader();

if(excelReader.parseExcel(path + File.separatorChar + fileName)){

//读取Excel并封装为List<String[]>对象

List<String[]> dataList = excelReader.getList();

//去除标题行

dataList.remove(0);

for (String[] str:dataList){

//判断是否有所有单元格的值都为""（或" "）的项

String temp = "";

for(int j=1;j<str.length;j++){

temp += str[j];

}

if(!StringUtil.IsNullOrEmpty(temp)){

videoRes = new VideoRes();

videoRes.setName(str[1]);

videoRes.setRemark(str[2]);

videoRes.setActor(str[3]);

videoRes.setDirector(str[4]);

videoRes.setArea(str[5]);

videoRes.setPlayTime(Timestamp.valueOf(str[6]));

videoRes.setTotalCount(Integer.parseInt(str[7]));

videoRes.setResolution(str[8]);

videoRes.setScore(Float.parseFloat(str[9]));

videoRes.setRecommend(Integer.parseInt(str[10]));

videoRes.setStatus(Integer.parseInt(str[11]));

videoRes.setCreatedBy(str[12]);

if(!StringUtil.IsNullOrEmpty(str[13])){

videoRes.setCreatedDate(Timestamp.valueOf(str[13]));

}

videoRes.setLastUpdatedBy(str[14]);

if(!StringUtil.IsNullOrEmpty(str[15])){

videoRes.setLastUpdatedDate(Timestamp.valueOf(str[15]));

}

videoRes.setClassId(code);

dto.put("videoRes", videoRes);

super.doService("VideoResService", "save", dto);

}

}

}

}

}

if (logger.isInfoEnabled()){

logger.info("***** VideoResAction.importExcel() method end*****");

}

return AJAX;

}

/**

• 功能描述： 导出Excel

• @return

• @throws ActionException

*/

public String exportExcel()throws Exception{

if (logger.isInfoEnabled()){

logger.info("***** VideoResAction.exportExcel() method begin*****");

}

dto.put("ids",ids);

List<Object[]> dataList = (List<Object[]>)super.doService("VideoResService", "findData", dto);

ActionContext act = ActionContext.getContext();

WritableWorkbook wbook = null;

try {

FileNameExtensionFilter filter=new FileNameExtensionFilter("*.xls"

,"xls");

JFileChooser fc=new JFileChooser();

fc.setFileFilter(filter);

fc.setMultiSelectionEnabled(false);

int result=fc.showSaveDialog(null);

if (result==JFileChooser.APPROVE_OPTION) {

File localFile=fc.getSelectedFile();

if (!localFile.getPath().endsWith(".xls")) {

localFile=new File(localFile.getPath()+".xls");

}

if (!localFile.exists()) {

localFile.createNewFile();

}

wbook = Workbook.createWorkbook(localFile);

// 设置表名

WritableSheet sheet = wbook.createSheet("点播资源信息", 0);

// 将生成的单元格（表头）添加到工作表中

sheet.addCell(new Label(0, 0, "序号"));

sheet.addCell(new Label(1, 0, "影片名称"));

sheet.addCell(new Label(2, 0, "影片描述"));

sheet.addCell(new Label(3, 0, "主要演员"));

sheet.addCell(new Label(4, 0, "导演 "));

sheet.addCell(new Label(5, 0, "国家地区 "));

sheet.addCell(new Label(6, 0, "上映时间"));

sheet.addCell(new Label(7, 0, "视频数"));

sheet.addCell(new Label(8, 0, "分辨率"));

sheet.addCell(new Label(9, 0, "评分"));

sheet.addCell(new Label(10, 0, "推荐级别"));

sheet.addCell(new Label(11, 0, "状态"));

sheet.addCell(new Label(12, 0, "创建人"));

sheet.addCell(new Label(13, 0, "创建日期"));

sheet.addCell(new Label(14, 0, "最后更新者"));

sheet.addCell(new Label(15, 0, "最后更新日期"));

for (int i = 0; i < dataList.size(); i++) {

Object[] object = dataList.get(i);

//往Excel单元格中写入数据

sheet.addCell(new Label(0, i+1, object[0].toString()));

sheet.addCell(new Label(1, i+1, object[1].toString()));

sheet.addCell(new Label(2, i+1, object[2].toString()));

sheet.addCell(new Label(3, i+1, object[3].toString()));

sheet.addCell(new Label(4, i+1, object[4].toString()));

sheet.addCell(new Label(5, i+1, object[5].toString()));

sheet.addCell(new Label(6, i+1, object[6].toString()));

sheet.addCell(new Label(7, i+1, object[7].toString()));

sheet.addCell(new Label(8, i+1, object[8].toString()));

sheet.addCell(new Label(9, i+1, object[9].toString()));

sheet.addCell(new Label(10, i+1, object[10].toString()));

sheet.addCell(new Label(11, i+1, object[11].toString()));

sheet.addCell(new Label(12, i+1, object[12].toString()));

sheet.addCell(new Label(13, i+1, object[13].toString()));

sheet.addCell(new Label(14, i+1, object[14].toString()));

sheet.addCell(new Label(15, i+1, object[15].toString()));

}

wbook.write();

wbook.close();

}

} catch (RowsExceededException e) {

e.printStackTrace();

} catch (WriteException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

//json.doNavTabTodo();

if (logger.isInfoEnabled()){

logger.info("***** VideoResAction.exportExcel() method end

*****");

}

return AJAX;

}

转载于:https://my.oschina.net/u/3730801/blog/1584166