页面导入导出excel 相关

做了个页面，要求能从页面导入excel,并展示数据。还要求能把数据按对应的格式导出成excel

一 ：导入excel，本质就是文件上传再解析。项目的框架是struts1，于是就用到了struts1的文件上传。

我参照了 http://blog.sina.com.cn/s/blog_9d5d25ff01012bof.html 

但是没有把文件保存到服务器上，获取数据直接使用即可。

1：定义ActionForm

package taxis.wfwz.actionform;import org.apache.struts.action.ActionForm;import org.apache.struts.upload.FormFile;public class FileUploadForm extends ActionForm {private FormFile uploadFile;public FormFile getUploadFile() {return uploadFile;}public void setUploadFile(FormFile uploadFile) {this.uploadFile = uploadFile;}}

配置文件：

<?xml version="1.0" encoding="ISO-8859-1" ?><!DOCTYPE struts-config PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 1.2//EN" "http://jakarta.apache.org/struts/dtds/struts-config_1_2.dtd"><struts-config>    <form-beans>        <form-bean name="FileUploadForm"                   type="taxis.wfwz.actionform.FileUploadForm"/>    </form-beans><!-- sss --><action-mappings><action path="/WfwzSjDrAction" type="taxis.wfwz.action.WfwzSjDrAction"><forward name="list" path="/taxispage/wfwz/list.jsp" /><forward name="print" path="/taxispage/wfwz/print.jsp" /></action><action name="FileUploadForm" validate="true" input="/process/error.jsp" scope="request" path="/WfwzDrExlAction" type="taxis.wfwz.action.WfwzDrExlAction"><forward name="drExl" path="/taxispage/wfwz/drExl.jsp" /><forward name="print" path="/taxispage/wfwz/print.jsp" />        </action></action-mappings></struts-config>

2.JSP页面增加上传按钮，这里使用的是struts1的标签

<%@ taglib prefix="html" uri="http://struts.apache.org/tags-html"  %><html:form enctype="multipart/form-data" action="WfwzDrExlAction.do" method="post"><html:file property="uploadFile"></html:file><input name="savebtn" type='button' value="导入Excel" class="btnmid" onclick="javascript:doDr();"></html:form>

 function doDr(){    if(document.getElementsByName('uploadFile')[0].value==''){     alert("请选择一个文件!");    }else{       document.all.sending.style.visibility="visible";         document.forms[0].submit();        }    }

也可以用：<html:submit>Upload File</html:submit>  这个submit

项目里看到这个正在处理的div.收藏一下

  <div id="sending"     style="LEFT: 0px; WIDTH:100%; HEIGHT:100%; POSITION: absolute; TOP: 0px;  VISIBILITY: hidden; Z-INDEX: 10">    <table width="100%" height="100%" border="0" height=320 cellspacing="0" cellpadding="0">        <tr>            <td align=center>                <table width="40%" height="70" border="0" cellspacing="2"                       cellpadding="0" bgcolor="#ff9900">                    <tr>                        <td bgcolor="#eeeeee" align="middle">正在处理, 请稍候...</td>                    </tr>                </table>            </td>        </tr><span id="_xhe_cursor"></span>    </table></div>

3.就是写execute方法了

public ActionForward execute(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response) {FileUploadForm fileUploadForm = (FileUploadForm) form;FormFile uploadFile = fileUploadForm.getUploadFile();try {FileOutputStream outer = new FileOutputStream("d:\\"+uploadFile.getFileName());byte[] buffer = uploadFile.getFileData();outer.write(buffer);outer.close();uploadFile.destroy();} catch (Exception e) {e.printStackTrace();}return null;}

我没有这样写，而是直接用jxl 读取了数据

byte[] buffer = uploadFile.getFileData();InputStream is = new ByteArrayInputStream(buffer);Workbook wb = Workbook.getWorkbook(is);Sheet st = wb.getSheet(0);int cols = st.getColumns();int rows = st.getRows();for(int i =1; i < rows; i++){String wfwzxh = st.getCell(1, i).getContents();String sdbz = st.getCell(17, i).getContents();System.out.println(wfwzxh);System.out.println(sdbz);if(sdbz.equals("妥投")){service.updateDrExl(wfwzxh, "1" ,updateTime);}else {service.updateDrExl(wfwzxh, "2" ,updateTime);}}

ps:这段代码我测试用的。。目的是获取当前sheet  和当前sheet内的数据

至此excel读取完毕。其他一些配置详见我发的链接。

二、JXL 导出excel

导出excel的格式都在代码里，应该比较全了，我也找了好久，合并单元格，水平垂直居中，设置边框，设置列宽度，冻结行和列都有

package taxis.wfwz.util;import java.io.OutputStream;import java.lang.reflect.Method;import java.util.List;import java.util.Map;import javax.servlet.http.HttpServletResponse;import taxis.wfwz.domain.ExcelField;import jxl.SheetSettings;import jxl.Workbook;import jxl.format.Alignment;import jxl.format.VerticalAlignment;import jxl.write.Label;import jxl.write.WritableCellFormat;import jxl.write.WritableFont;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;public class ExportExcel {public static void exportExcel(HttpServletResponse resp,List<ExcelField> fieldList,List dataList,String wfwzLx) {resp.setContentType("application/vnd.ms-excel");//设置文件名称resp.setHeader("Content-Disposition", "attachment;filename="+new String("EXCEL名称".getBytes("gbk"),"iso8859-1")+".xls");// TODO Auto-generated method stubOutputStream os;WritableWorkbook wwb;try {os = resp.getOutputStream();wwb = Workbook.createWorkbook(os);//设置sheet名称WritableSheet ws = wwb.createSheet("导出数据", 0);//设置列宽度（第几列，宽度）ws.setColumnView(0, 0);for(int i = 1 ;i<=14 ;i++){ws.setColumnView(i, 24);}WritableFont fontFirstTitle= new WritableFont(WritableFont.createFont("宋体"), 16, WritableFont.BOLD);WritableFont fontTitle= new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.BOLD);WritableFont font= new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.NO_BOLD);WritableCellFormat firstTitle = new WritableCellFormat(fontFirstTitle);//水平居中firstTitle.setAlignment(Alignment.CENTRE);//垂直居中firstTitle.setVerticalAlignment(VerticalAlignment.CENTRE);WritableCellFormat wcfTitle = new WritableCellFormat(fontTitle);wcfTitle.setAlignment(Alignment.CENTRE);wcfTitle.setVerticalAlignment(VerticalAlignment.CENTRE);//增加边框wcfTitle.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);//文字超出列宽度自动换行wcfTitle.setWrap(true);WritableCellFormat wcf = new WritableCellFormat(font);wcf.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);//标题ws.addCell(new Label(0, 0, "苏州市姑苏区国家税务局2015年月", firstTitle));//合并单元格（起始行，列，结束行，列）ws.mergeCells(0, 0, 5, 0);int num = 7;if (wfwzLx.equals("2")) {num=8;} ws.addCell(new Label(1, 1, "文书信息", wcfTitle));ws.mergeCells(1, 1, num, 1);ws.addCell(new Label(num+1, 1, "收件人信息", wcfTitle));ws.mergeCells(num+1, 1, num+6, 2);for (int i = 1; i <= num; i++) {ws.addCell(new Label(i, 2, "字段" + i, wcfTitle));}//表头//List list = getExcelFields();for (int i = 0; i < fieldList.size(); i++) {ExcelField ef = (ExcelField) fieldList.get(i);ws.addCell(new Label(i, 3, ef.getTitle(), wcfTitle));}//置值 起始行数int startRow = 4;//List dataList=getCxList();for (int r = 0; r < dataList.size(); r++) {Object data=dataList.get(r);for (int i = 0; i < fieldList.size(); i++) {ExcelField ef = (ExcelField) fieldList.get(i);String value=getMapValue((Map)data,ef.getId());ws.addCell(new Label(i, r+startRow, value, wcf));}}SheetSettings st = ws.getSettings();//冻结行st.setVerticalFreeze(4);//冻结列st.setHorizontalFreeze(2);wwb.write();wwb.close();os.flush();os.close();os = null;} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}private static String getMapValue(Map map,String name){Object obj=map.get(name);if(obj==null){return "";}else{return obj.toString();}}private static String getObjValue(Object obj, String name) {String s = "";try {Method method = obj.getClass().getMethod(name, null);s =  method.invoke(obj, null).toString();} catch (Exception e) {s = "";}return s;}}

然后调用这个方法就行

这是取数据：写的有点渣。。反正是我自己看。。

package taxis.wfwz.service.impl;import org.apache.log4j.Logger;import org.hibernate.Query;import org.hibernate.Session;import org.hibernate.SessionFactory;import org.hibernate.Transaction;import org.hibernate.transform.Transformers;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import org.springframework.transaction.annotation.Transactional;import taxis.wfwz.dao.HibernateDao;import taxis.wfwz.dao.IWfwzDao;import taxis.wfwz.domain.SqlPageParam;import taxis.wfwz.service.IWfwzSjDrService;import taxis.wfwz.util.SqlPageTool;import java.lang.reflect.Method;import java.math.BigDecimal;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;/** * @author Administrator *  */@Service@Transactional(readOnly = true)public class WfwzSjDrService implements IWfwzSjDrService {public static final Logger log = Logger.getLogger(WfwzSjDrService.class);@Autowiredprivate SessionFactory sessionFactory;@Autowiredprivate HibernateDao wfwzHibernateDao;    @Autowired    private IWfwzDao wfwzDao;@Overridepublic List getWfwzDrList(SqlPageParam spp,String qssq, String jzsq ,String wfwzLx) {// TODO Auto-generated method stubString sql = "select aa.*,"+  " to_char(aa.sssq_q,'yyyy-mm-dd') ||'～' ||  to_char(aa.sssq_z,'yyyy-mm-dd') sssq ," + " bb.scjydz," + " cc.zywfwzsd_mc," + " bb.fddbrmc," + " bb.dhhm," + " to_char(aa.xgrq, 'yyyy-mm-dd') xgrq1," + " to_char(aa.ckjsrq, 'yyyy-mm-dd') ckjsrq1," + " case" + " when instr(zywfwzss, '税种：') > 0 then" + "  substr(zywfwzss," + "         instr(zywfwzss, '：') + 1," + "        instr(zywfwzss, '，') - instr(zywfwzss, '：') - 1)" + " else" + "  '增值税'" + " end sz," + " dd.swjg_jg" +  " from taxis_wfwz_sjdr aa ,taxis_common_dj_nsrxx bb, TAXIS_WFWZ_DM_ZYWFWZSD cc ,ctais_dm_swjg dd"+  " where aa.sssq_q >= to_date('"+ qssq +"', 'yyyy-mm-dd')" + " and aa.sssq_z <= to_date('" + jzsq+"', 'yyyy-mm-dd')" + " and aa.nsrsbh = bb.nsrsbh" + " and aa.zywfwzsd_dm = cc.zywfwzsd_dm(+)" + " and aa.swjg_dm = dd.swjg_dm";if (!"0".equals(wfwzLx)) {sql += " and aa.zywfwzsd_dm in (select zywfwzsd_dm from TAXIS_WFWZ_DM_ZYWFWZSD where LXBZ = '"+ wfwzLx +"')";}System.out.println(sql);Session session = sessionFactory.openSession();Query query = session.createSQLQuery(sql);if (null != spp) {spp.setTotalCount(wfwzHibernateDao.getSqlCount(sql));SqlPageTool.calc(spp, spp.getPageNo());query.setFirstResult(spp.getStartNumber());query.setMaxResults(spp.getPageSize());}List list = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();session.close();return list;}}

public ActionForward acfExecute(ActionMapping mapping, ActionForm arg1,HttpServletRequest request, HttpServletResponse resp)throws Exception {//List<ExcelField> fieldList =  getExcelFields1();List list = service.getWfwzDrList(null, qsrq, jzrq, wfwzLx);ExportExcel.exportExcel(resp, fieldList, list, wfwzLx);}//未申报private List<ExcelField> getExcelFields1() {List list = new ArrayList();list.add(new ExcelField("WFWZXH", "序号", ""));list.add(new ExcelField("WSXH", "文书号", ""));list.add(new ExcelField("NSRMC", "企业名称", ""));list.add(new ExcelField("NSRSBH", "企业税号", ""));list.add(new ExcelField("SSSQ", "所属时期", ""));list.add(new ExcelField("SZ", "税种", ""));list.add(new ExcelField("XGRQ1", "限改日期", ""));list.add(new ExcelField("CKJSRQ1", "催申报日期", ""));list.add(new ExcelField("SXRYB", "收信人邮编", ""));list.add(new ExcelField("SCJYDZ", "企业地址", ""));list.add(new ExcelField("NSRMC", "企业名称", ""));list.add(new ExcelField("FDDBRMC", "收件人（法定代表人）", ""));list.add(new ExcelField("DHHM", "法定代表人电话", ""));list.add(new ExcelField("ZGSWRY", "企业所属分局", ""));//list.add(new ExcelField("NSR_SWJG_DM", "税务机关", ""));//list.add(new ExcelField("ZYWFWZSD_DM", "类型", ""));return list;}