jsp中操作Excel需要引入一个外部的jar包（jxl.jar),这个jar包主要是用来构建Excel的骨架的,具体实例如下:

最新推荐文章于 2021-01-30 15:04:15 发布

Music杰

最新推荐文章于 2021-01-30 15:04:15 发布

阅读量2.9k

点赞数

文章标签： excel jar jsp string exception file

本文链接：https://blog.csdn.net/a710732966/article/details/6601101

版权

1.需要建一个Excel的骨架文件,在该文件里对Excel进行操作:

package com.sumit.common.util;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import com.sumit.mps.component.topuprequest.TopupRequestTO;
import javax.servlet.http.HttpServlet;

import jxl.Workbook;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.struts.upload.FormFile;
import org.apache.commons.lang.StringUtils;

public class CreateXlsFile {

private Log log =LogFactory.getLog(CreateXlsFile.class);
public static String FILE_SAVA_PATH = null;
private OutputStream os = null;
WritableWorkbook wwb = null;
// 创建Excel工作表指定名称和位置
WritableSheet ws = null;
//**************工作表中数据的格式化*****************
// 日期格式化
DateFormat df = new DateFormat("yyyy-MM-ddhh:mm:ss");
WritableCellFormat wcfDF = newWritableCellFormat(df);
// 数字格式化
NumberFormat nf = new NumberFormat("#.##");
WritableCellFormat wcfN = newWritableCellFormat(nf);
// 表单元格
jxl.write.Formula labelFf = null;
jxl.write.Number labelNF = null;
Label labelcf =null;

private static final String XLS_ ="xls";

publicCreateXlsFile(HttpServlet servlet, OutputStream os)
throwsIOException, RowsExceededException, WriteException {

  this.os =os;
  this.wwb =Workbook.createWorkbook(os);
  ws =wwb.createSheet("手机充值查询", 0);
  //往excel工作表中添加栏目
  labelcf = new Label(0, 0,"序号");
  ws.addCell(labelcf);
  labelcf = new Label(1, 0,"提交时间");
  ws.addCell(labelcf);
  labelcf = new Label(2, 0,"商户参数");
  ws.addCell(labelcf);
  labelcf = new Label(3, 0,"被充值手机号码");
  ws.addCell(labelcf);
  labelcf = new Label(4, 0,"充值金额");
  ws.addCell(labelcf);
  labelcf = new Label(5, 0,"实充时间");
  ws.addCell(labelcf);
  labelcf = new Label(6, 0,"实充金额");
  ws.addCell(labelcf);
  labelcf = new Label(7, 0,"实扣金额");
  ws.addCell(labelcf);
  labelcf = new Label(8, 0,"充值状态");
  ws.addCell(labelcf);
}

publicCreateXlsFile(OutputStream os) throws IOException,
   RowsExceededException,WriteException {
  this.os = os;
  this.wwb =Workbook.createWorkbook(os);
  ws =wwb.createSheet("手机充值查询", 0);

  //往excel工作表中添加栏目
  Label labelcf = new Label(0, 1,"序号");
  ws.addCell(labelcf);
  labelcf = new Label(1, 1,"提交时间");
  ws.addCell(labelcf);
  labelcf = new Label(2, 1,"商户参数");
  ws.addCell(labelcf);
  labelcf = new Label(3, 1,"被充值手机号码");
  ws.addCell(labelcf);
  labelcf = new Label(4, 1,"充值金额");
  ws.addCell(labelcf);
  labelcf = new Label(5, 1,"实充时间");
  ws.addCell(labelcf);
  labelcf = new Label(6, 1,"实充金额");
  ws.addCell(labelcf);
  labelcf = new Label(7, 1,"实扣金额");
  ws.addCell(labelcf);
  labelcf = new Label(8, 1,"充值状态");
  ws.addCell(labelcf);
}

public booleanvolidator(FormFile file) {
  String name = null;
  if (file != null)
   name =file.getFileName();
  if (!StringUtils.isEmpty(name)){
   int i =name.indexOf(".");
   if (i != -1){
    StringnameXls = name.substring(i + 1);
    if(XLS_.equals(nameXls)) {
     returntrue;
    }
   }
  }
  return false;
}

public void writeRowXls(int row,int index, Objectobj) {
  try {
   TopupRequestTOto = (TopupRequestTO)obj;

   //序号
   labelFf = newjxl.write.Formula(0, row, index+ "");
   ws.addCell(labelFf);
   //提交时间
   ws.addCell(labelcf=newjxl.write.Label(1,row,to.getCheckinTime()));
   //商户参数
   ws.addCell(labelcf=newjxl.write.Label(2,row,to.getMerchantParam()));
   //被充值手机号码
   ws.addCell(labelcf=newjxl.write.Label(3,row,to.getTopupMp()));
   //充值金额
   labelFf = newjxl.write.Formula(4,row,String.valueOf(to.getTopupAmount()));//String.valueOf(to.getTopupAmount_f()));
   ws.addCell(labelFf);
   log.debug("to.getTopupAmount_f:"+to.getTopupAmount_f());
   //实充时间
   ws.addCell(labelcf=newjxl.write.Label(5,row,to.getOperatorTime()));
   //实充金额
   labelFf = newjxl.write.Formula(6,row,String.valueOf(to.getFactTopupAmount()));
   ws.addCell(labelFf);
   //实扣金额
   Float f =Float.valueOf(to.getFactTopupAmount().toString())*Float.valueOf(to.getChargePercent())/100;
   labelFf = newjxl.write.Formula(7,row,String.valueOf(f));
   ws.addCell(labelFf);
   //充值状态
   ws.addCell(newjxl.write.Label(8,row,to.getStatus()));

  } catch(Exception e) {
   log.error("--writeexcel data error---", e);
  }
}

public void writeRowXls(int i, int totalNum,intsuccePro,BigDecimal totalSuccAmount ) {
  try {
   //总记录笔数
   labelcf = newjxl.write.Label(0, i, "总记录笔数：");
   ws.addCell(labelcf);

   labelFf= new jxl.write.Formula(1, i, totalNum+"");
   ws.addCell(labelFf);
   //成功处理笔数
   labelcf= new jxl.write.Label(2, i,"成功处理笔数：");
   ws.addCell(labelcf);

   labelFf = newjxl.write.Formula(3, i, succePro+"");
   ws.addCell(labelFf);

   //总金额
   labelcf= new jxl.write.Label(4, i,"成功分帐总金额：");
   ws.addCell(labelcf);

   labelFf = newjxl.write.Formula(5, i, totalSuccAmount.toString());
   ws.addCell(labelFf);

  } catch(Exception e) {
   log.error("--writeexcel data error---", e);
  }
}

public void closeIO() throws WriteException,IOException {
    wwb.write();
  wwb.close();
}

public void savaAsFile(String fileRrs, StringfileTo) throws IOException {
  BufferedInputStreaminput=null;
  BufferedOutputStreamoutput=null;
  try {
   FilefileRrsObj = new File(fileRrs);
    input = newBufferedInputStream(
     newFileInputStream(fileRrsObj));
    output = newBufferedOutputStream(
     newFileOutputStream(fileTo));
   int r_len =0;
   byte[]r_bytes = new byte[500];
   while ((r_len= input.read(r_bytes)) != -1) {
    output.write(r_bytes);
   }
  } finally {
   input.close();
   output.close();
  }
}

public static void main(String[] args) {
  File file = newFile("d:/test18112.xls");
  CreateXlsFile cr = null;
  try {
   OutputStreamout = new FileOutputStream(file);
   try {
    cr= new CreateXlsFile(out);
    for(int i = 1; i <= 15; i++){
     TopupRequestTOc_sva = new TopupRequestTO();
     c_sva.setCheckinTime("20080101");
     c_sva.setMerchantParam("dfd");
     c_sva.setTopupMp("13888888888");
     c_sva.setPayAmount(newBigDecimal(30));
     c_sva.setTopupAmount_f(newFloat(5.666));
     c_sva.setOperatorTime("20080305");
     c_sva.setFactTopupAmount_f(newFloat(10));
     c_sva.setStatus("00");
     c_sva.setFactTopupAmount_f(newFloat(34));
     c_sva.setChargePercent("34");
     cr.writeRowXls(i+1,i, c_sva);
    }
    cr.writeRowXls(17,1111,111,new BigDecimal(90) );

   } catch(RowsExceededException e) {
    e.printStackTrace();
   } catch(WriteException e) {
    e.printStackTrace();
   } catch(IOException e) {
    e.printStackTrace();
   } finally{
    try{
     cr.closeIO();
     out.close();

    }catch (WriteException e) {
     e.printStackTrace();
    }catch (IOException e) {
     e.printStackTrace();
    }
   }
  } catch (FileNotFoundExceptione) {
   e.printStackTrace();
  }
}
}

2.在action中调用CreateXlsFile,把从数据库中或者是服务器上的文件下载到本地:在这里是把从数据库中的结果下载到本地并保存在Excel文件中

packagecom.sumit.web.merchant.topup.action;

import java.io.*;
import java.util.*;

importcom.sumit.mps.component.topuprequest.TopupRequestDAO;
import com.sumit.mps.component.topuprequest.TopupRequestTO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.log4j.Logger;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

importcom.sumit.common.except.DaoException;
import com.sumit.common.util.CreateXlsFile;
import com.sumit.web.common.except.ActionException;
import com.sumit.web.struts.BaseAction;

public class ExportAction extends BaseAction{

//写日志
private Logger log =Logger.getLogger(MobileAction.class);

public ActionForwardexecute(ActionMapping mapping, ActionForm form,
    HttpServletRequestrequest, HttpServletResponse response) throwsActionException,IOException {

   log.debug("ExportAction..................");

   //查询条件
   Map cons=(Map) request.getSession().getAttribute("cons");

   //根据XML文件中的配置得到TopupRequestDAO对象
   TopupRequestDAOtopupRequestDAO=(TopupRequestDAO)getAppContext().getBean("topupRequestDAO");

   //查询结果保存为List
   List exportList =null;

   try
   {
    if(cons.get("dateType").equals("0"))
     {
      exportList=(List) topupRequestDAO.findMobileAllByOperTime(cons);
     }
    else
     {
      exportList=(List) topupRequestDAO.findMobileAllByCheckInTime(cons);
     }
   }catch(DaoExceptionex)
   {
    ex.printStackTrace();
   }

   //使用struts2的getResponse的getWriter()方法连续两次输出流到页面的时候，
   //第二次的流会包括第一次的流，所以可以使用将response.reset或者resetBuffer的方法。
   response.reset();

   //客户端下载到本地后的文件的名称
   StringshowFileName = "MobileQuery.xls";

   //文件名称的编码
   showFileName= new String(showFileName.getBytes("ISO-8859-1"),"GBK");

   //设置页面弹出下载窗口
   response.addHeader("Content-disposition","attachment;filename="" + showFileName + """);
   response.setContentType("application/unknown;charset=gbk");

   //把从数据库中查询出的结果写入Excel文件中
   //CreateXlsFile是生成Excel文件的类
   CreateXlsFilecr = null;
   try {
      OutputStream out = response.getOutputStream();
    try{
     cr= new CreateXlsFile(out);
     for(int i=1; i <= exportList.size(); i++){
      TopupRequestTOc_sva =(TopupRequestTO)exportList.get(i-1);
      cr.writeRowXls(i+1,i, c_sva);
     }

    }catch (RowsExceededException e) {
     e.printStackTrace();
    }catch (WriteException e) {
     e.printStackTrace();
    }catch (IOException e) {
     e.printStackTrace();
    }finally {
     try{
      cr.closeIO();
      out.close();

     }catch (WriteException e) {
      e.printStackTrace();
     }catch (IOException e) {
      e.printStackTrace();
     }
    }
   } catch(FileNotFoundException e) {
    e.printStackTrace();
   }
   returnmapping.findForward("/merchant/topup/exportforward");
  }
}