做了个页面,要求能从页面导入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;
}