java与execel导入和导出的操作

package com.crm.app.fubon.deliver.service;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.Sheet;
import jxl.Workbook;

import org.apache.log4j.Logger;
import org.apache.struts2.ServletActionContext;

import com.crm.app.fubon.deliver.action.DeliverDataAction;
import com.crm.app.fubon.deliver.model.DeliverData;
import com.crm.extend.file.fileUpload.model.FileUpload;
import com.crm.extend.file.fileUpload.page.FileUploadPage;
import com.crm.extend.file.fileUpload.util.DownLoad;
import com.crm.extend.file.fileUpload.util.ExcelFile;
import com.crm.extend.file.fileUpload.util.WriteExcel;
import com.crm.framework.common.CommonPage;
import com.crm.framework.common.Constant;
import com.crm.framework.common.util.CUID;
import com.crm.framework.common.util.DateTime;
import com.crm.framework.dao.selector.Query;
import com.crm.framework.service.ServiceFactory;
import com.crm.framework.service.SimpleService;
import com.crm.framework.util.fileUpload.CommonFilesUpload;
import com.lowagie.text.pdf.codec.Base64.InputStream;
import com.opensymphony.xwork2.ActionContext;
import com.sybase.jdbc3.tds.e;

/**

 */
public class DeliverDataService extends SimpleService {
 private static Logger logger = Logger.getLogger(DeliverDataService.class);
 public static DeliverDataAction deliverDataaction = new DeliverDataAction();
 /**
  * 数据导出excel
  *
  * @return
  */
 public static void exportExcel(Query query, int count) {
  try {
   HttpServletResponse response = (HttpServletResponse) ActionContext
     .getContext().get(ServletActionContext.HTTP_RESPONSE);
   SimpleService service = (SimpleService) ServiceFactory
     .getService(SimpleService.class);
   DateTime datetime = new DateTime(new Date(),
     DateTime.YEAR_TO_SECOND);
   String createfiletime = datetime.toString().replace("-", "")
     .replace(" ", "").replace(":", "");
   String distr = Constant.REAL_PATH + "/" + "download/";
   File file = new File(distr);
   if (!file.exists()) {
    file.mkdirs();
   }
   distr += "download.xls";
   File f = new File(distr);
   f.createNewFile();
   String fileName = createfiletime + ".xls";
   WriteExcel writeExcel = new WriteExcel(distr);
   writeExcel.createNewSheet("导出数据");
   writeExcel.createNewRow();
   /** 写表头 */
   writeExcel.createNewCell("时间");
   writeExcel.createNewCell("险种");
   writeExcel.createNewCell("单证类别");
   writeExcel.createNewCell("快递公司");
   writeExcel.currNumOfColumns = 0; // 记录当前的列数
   List<DeliverData> deliverDatas = new ArrayList<DeliverData>();
   int rows = 0;
   while ((rows * 100) <= count) { // 每次导100条
    deliverDatas = (List<DeliverData>) service.queryPage(query,
      rows, 100).getResult();
    rows++;
    writeExcelNew(writeExcel, deliverDatas);
    deliverDatas.clear();
   }
   writeExcel.close();
   DownLoad down = new DownLoad();
   down.DownLoadFile(response, distr, fileName);
  } catch (Exception e) {
   e.printStackTrace();
  }
 }

 /**
  * 生成Excel文件 excel方式
  *
  * @param WriteExcel
  *            writeExcel excel对象
  * @param List
  *            templist 数据列表
  */
 private static void writeExcelNew(WriteExcel writeExcel,
   List<DeliverData> templist) {
  if (templist != null) {
   for (int i = 0; i < templist.size(); i++) {

    writeExcel.createNewRow();
    writeExcel.createNewCell(new DateTime(templist.get(i)
      .getCreatetime(), DateTime.YEAR_TO_SECOND).toString());
    String insflag = templist.get(i).getInsurflag();
    if (insflag != null && !insflag.equals("")&& !insflag.equals("null")) {
     writeExcel.createNewCell(insflag + "");
    } else {
     writeExcel.createNewCell("");
    }
    String docutype = templist.get(i).getDocutype();
    if(docutype == "1" || docutype.equals("1")){
     writeExcel.createNewCell("投保单");
    }else if(docutype == "2" || docutype.equals("2")){
     writeExcel.createNewCell("保单");
    }else if(docutype == "3" || docutype.equals("3")){
     writeExcel.createNewCell("保单与投保单");
    }else {
     writeExcel.createNewCell("");
    }
    String policyno = templist.get(i).getPolicyno();
    if (policyno != null && !policyno.equals("")&&!policyno.equals("null")) {
     writeExcel.createNewCell(policyno + "");
    } else {
     writeExcel.createNewCell("");
    }

    String proposalno = templist.get(i).getProposalno();
    if (proposalno != null && !proposalno.equals("")&& !proposalno.equals("null")) {
     writeExcel.createNewCell(proposalno + "");
    } else {
     writeExcel.createNewCell("");
    }

    String appliname = templist.get(i).getAppliname();
    if (appliname != null && !appliname.equals("")&& !appliname.equals("null")) {
     writeExcel.createNewCell(appliname + "");
    } else {
     writeExcel.createNewCell("");
    }
    String area = templist.get(i).getDeliverarea() + "";
    if (area !=null && !area.equals("")&&!area.equals("null")) {
     try {
      area=String.valueOf(DeliverDataAction.getBaseName("deliverarea",area));
     } catch (Exception e) {
      e.printStackTrace();
     }
     writeExcel.createNewCell(area);
    } else {
     writeExcel.createNewCell("");
    }
    String address = templist.get(i).getDeliveraddress();
    if (address != null && !address.equals("") && !address.equals("null")) {
     writeExcel.createNewCell(address + "");
    } else {
     writeExcel.createNewCell("");
    }

    String tel = templist.get(i).getTel();

    if (tel != null && !tel.equals("") && !tel.equals("null")) {
     writeExcel.createNewCell(tel + "");
    } else {
     writeExcel.createNewCell("");
    }
    String gift = templist.get(i).getGiftid() + "";
    if (gift !=null && !gift.equals("") && !gift.equals("null")) {
     try {
      gift=DeliverDataAction.getBaseName("gifttype",gift);
     } catch (Exception e) {
      e.printStackTrace();
     }
     writeExcel.createNewCell(gift);
    } else {
     writeExcel.createNewCell("");
    }
    String giftid = templist.get(i).getGiftid();
    if (giftid != null && !giftid.equals("") && !giftid.equals("null")) {
     writeExcel.createNewCell(giftid + "");
    } else {
     writeExcel.createNewCell("");
    }

    String delcompany = templist.get(i).getDelcompany();

    if (delcompany !=null && !delcompany.equals("") && !delcompany.equals("null")) {
     try {
      delcompany=DeliverDataAction.getBaseName("sendcom",delcompany);
     } catch (Exception e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
     }
     writeExcel.createNewCell(delcompany);
    } else {
     writeExcel.createNewCell("");
    }
    writeExcel.currNumOfColumns = 0; // 记录当前的列数
   }
  }
 }

 /**
  * 读取Excel
  */
 public static void readExcel(String filePath, DeliverData deliverData) {
  try {

   Workbook book = Workbook.getWorkbook(new File(filePath));
   Sheet sheet = book.getSheet(0);
   int rows = sheet.getRows() - 1;
   String feedbackdate = "";
   for (int i = 1; i < rows; i++) {
    System.out.println("--------------i---------------" + i);
    String proposalno = sheet.getCell(4, i).getContents();
    if (proposalno != "" && !proposalno.equals("")
      && proposalno != null && !proposalno.equals(null)) {
     if (sheet.getCell(12, i).getType() == CellType.DATE) {
      DateCell dateCell = (DateCell) sheet.getCell(12, i);
      Date date = dateCell.getDate();
      feedbackdate = new SimpleDateFormat("yyyy-MM-dd")
        .format(date);
     }
     String delresults = sheet.getCell(13, i).getContents();
     if (delresults == "待配送" || delresults.equals("待配送")) {
      delresults = "0";
     } else if (delresults == "配送中" || delresults.equals("配送中")) {
      delresults = "1";
     } 
     String delnote = sheet.getCell(14, i).getContents();
     String deliver = sheet.getCell(15, i).getContents();
     String delamount = sheet.getCell(16, i).getContents();
     SimpleService server = ServiceFactory.getCurentService();
     System.out
       .println("--------------feedbackdate---------------"
         + feedbackdate);
     System.out
       .println("--------------delresults---------------"
         + delresults);
     System.out.println("--------------delnote---------------"
       + delnote);
     System.out.println("--------------deliver---------------"
       + deliver);
     System.out.println("--------------delamount---------------"
       + delamount);
     String hql = " update DeliverData set feedbackdate=to_date('"
       + feedbackdate
       + "','yyyy-MM-dd'),delresults='"
       + delresults
       + "',delnote='"
       + delnote
       + "',deliver='"
       + deliver
       + "',delamount='"
       + delamount + "' where proposalno=?";
     server.updateByHql(DeliverData.class, hql, proposalno);
    }
   }
   // 关闭文件
   book.close();
  } catch (Exception e) {
   e.printStackTrace();
  }
 }