页面导入导出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" οnclick="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 stub
		OutputStream 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 block
			e.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);

	@Autowired
	private SessionFactory sessionFactory;

	@Autowired
	private HibernateDao wfwzHibernateDao;
	
    @Autowired
    private IWfwzDao wfwzDao;
	
	@Override
	public List getWfwzDrList(SqlPageParam spp,String qssq, String jzsq ,String wfwzLx) {
		// TODO Auto-generated method stub
	
		String 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;
	}