J2EE和SQL2000下的导入和导出Excel

于 2010-06-13 13:27:47 发布
阅读量168 收藏
点赞数
文章标签： Excel Hibernate Struts Spring JavaScript ViewUI

      最近在做一个系统时用到了导入和导出Excel表格，自己写了写，数据能导入或导出，效果有了，但感觉代码烦琐，逻辑不紧，还希望高手多多指点,下面就是实现的步骤,使用的框架(struts1+spring+hibernate)：

 

      1.实体类

 

public class Sales {

	private Integer salesId;// 销售ID
	private Integer exportId;//过港ID;
	private Date salesDate;// 销售日期
	private String zhenyinhao;// 销售针印号
	private String fahuodanwei;// 发货单位
	private float toushu;// 销售头数
	private float zhongliang;// 销售重量
	private float junzhong;// 销售均重
	private float danjia;// 销售单价
	private float salesjine;// 销售金额
	private float kouchufeiyong;// 扣除费用
	private float jiehuijine;// 结汇金额
	private int jiesuan;//是否结算

	//无参构造函数
	public Sales() {
		super();
	}
	//有参构造函数
	public Sales(Integer salesId, Integer exportId, Date salesDate,
			String zhenyinhao, String fahuodanwei, float toushu,
			float zhongliang, float junzhong, float danjia, float salesjine,
			float kouchufeiyong, float jiehuijine) {
		super();
		this.salesId = salesId;
		this.exportId = exportId;
		this.salesDate = salesDate;
		this.zhenyinhao = zhenyinhao;
		this.fahuodanwei = fahuodanwei;
		this.toushu = toushu;
		this.zhongliang = zhongliang;
		this.junzhong = junzhong;
		this.danjia = danjia;
		this.salesjine = salesjine;
		this.kouchufeiyong = kouchufeiyong;
		this.jiehuijine = jiehuijine;
	}
         //属性的get和set方法省略
}

    

  2.实体类中属性的配置文件（用于生成表）

 

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
	<class name="com.tax.model.sales.Sales" lazy="false" table="sales">
		<id name="salesId" type="integer">
			<column name="salesId"></column>
			<generator class="increment"></generator>
		</id>
		<property name="exportId" type="integer">
			<column name="exportId"></column>
		</property>
		<property name="salesDate" type="date">
			<column name="salesDate" length="50"></column>
		</property>
		<property name="zhenyinhao" type="string">
			<column name="zhenyinhao" length="50"></column>
		</property>
		<property name="fahuodanwei" type="string">
			<column name="fahuodanwei" length="200"></column>
		</property>
		<property name="toushu" type="float">
			<column name="toushu" length="50"></column>
		</property>
		<property name="zhongliang" type="float">
			<column name="zhongliang" length="50"></column>
		</property>
		<property name="junzhong" type="float">
			<column name="junzhong" length="50"></column>
		</property>
		<property name="danjia" type="float">
			<column name="danjia" length="50"></column>
		</property>
		<property name="salesjine" type="float">
			<column name="salesjine" length="50"></column>
		</property>
		<property name="kouchufeiyong" type="float">
			<column name="kouchufeiyong" length="50"></column>
		</property>
		<property name="jiehuijine" type="float">
			<column name="jiehuijine" length="50"></column>
		</property>
		<property name="jiesuan" type="integer">
			<column name="jiesuan" length="50"></column>
		</property>
	</class>
</hibernate-mapping>

    

  3. 在某个jsp页面上点击导出或者导入按钮或者链接进行访问Action中的导出或者导入方法，如：

 

<form name="form2" method="post" enctype="multipart/form-data">
			<table width="100%" cellspacing="0" cellpadding="0" border="0">
				<tr align="left" id="pri">
					<td colspan="26" style="padding-left:360px;">
						<input type="button" value="打印" οnclick="print1()" />
					</td>
				</tr>
				<tr align="left" id="but">
					<td colspan="26" style="padding-left:150px;">
						<input id="filename" name="filename" type="file"/>
						<input type="button" value="导入Excel" οnclick="InExcel()"/>
						<input type="button" value="导出Excel" οnclick="OutExcel()" />
						<input type="button" value="返回" οnclick="back()"/>
					</td>
				</tr>
				<tr align="left">
					<td colspan="26" style="padding-left:350px;">
						<c:if test="${salesdaoru eq 0}">
							<script type="text/javascript">
								alert("导入成功!");
							</script>
						</c:if>
						<c:if test="${salesdaoru eq 1}">
							<script type="text/javascript">
								alert("导入失败，请检查导入文件类型的正确性!");
							</script>
						</c:if>
					</td>
				</tr>
			</table>
		</form>

 

<script type="text/javascript">
			function OutExcel(){
				window.location.href = "sales.do?method=salesOutExcel";
			}
			function InExcel(){
				document.form2.action="sales.do?method=salesInExcel";
    			document.form2.submit();
		
			}
			function back(){
				document.form1.action="sales.do?method=salesZuheChaxun";
    			document.form1.submit();
			}
			function print1(){
				document.getElementById("pri").style.display = "none"; 
				document.getElementById("but").style.display = "none";
				document.getElementById("weizhiTR").style.display = "none";
				document.getElementById("nu11").style.display = "none";
				window.print(); 
			}
		</script>

  

   (导出) 4.在某个jsp页面上点击导出按钮或者链接进行访问Action中的salesOutExcel方法后，就开始导出了，如下：

 

public ActionForward salesOutExcel(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws IOException {
		SimpleDateFormat dateformat2 = new SimpleDateFormat("yyyy年MM月dd日");
		String a2 = dateformat2.format(new Date());
		response.reset();
		StringBuffer s = new StringBuffer("attachment;filename=**系统**部导出数据("
				+ a2 + ").xls");
		response.setContentType("application/vnd.ms-excel;charset=GBK");
		try {
			response.setHeader("Content-Disposition", new String(s.toString()
					.getBytes("GBK"), "ISO8859-1"));
		} catch (UnsupportedEncodingException e1) {
			e1.printStackTrace();
		}
		sale.getSales("liwei", s, response.getOutputStream(), request);
		return null;
	}

    

  5. sale: 是此Action 在Spring 中注入的接口的属性   ，   getSales： 是实现了接口类中的一个方法，共有4个参数

第一个参数 "liwei"是指Excel表格中的工作表的名字，第二个参数 s 是指在弹出下载框时显示的Excel表格的名字，第三个和第四个参数就不用说了

 

     

 6. 进入 getSales方法中

 

@SuppressWarnings("unchecked")
	@Override
	public void getSales(String ttype,StringBuffer filename,OutputStream os,HttpServletRequest request) {
		try {
			WritableWorkbook book = Workbook.createWorkbook(os);
			// 生成名为 ttype 的工作表，参数0表示这是第一页
			WritableSheet sheet = book.createSheet(ttype, 0);
			jxl.write.WritableFont menuStyle = new jxl.write.WritableFont(
					WritableFont.ARIAL, 10, WritableFont.BOLD, false,
					UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
			jxl.write.WritableCellFormat menu = new jxl.write.WritableCellFormat(
					menuStyle);
			jxl.write.Label menuName0 = new jxl.write.Label(0, 0,
					"销售序号           ", menu);
			jxl.write.Label menuName1 = new jxl.write.Label(1, 0,
					"过港序号           ", menu);
			jxl.write.Label menuName2 = new jxl.write.Label(2, 0,
					"销售日期           ", menu);
			jxl.write.Label menuName3 = new jxl.write.Label(3, 0,
					"针印号           ", menu);
			jxl.write.Label menuName4 = new jxl.write.Label(4, 0,
					"发货单位           ", menu);
			jxl.write.Label menuName5 = new jxl.write.Label(5, 0,
					"头数           ", menu);
			jxl.write.Label menuName6 = new jxl.write.Label(6, 0,
					"重量           ", menu);
			jxl.write.Label menuName7 = new jxl.write.Label(7, 0,
					"均重           ", menu);
			jxl.write.Label menuName8 = new jxl.write.Label(8, 0,
					"单价           ", menu);
			jxl.write.Label menuName9 = new jxl.write.Label(9, 0,
					"销售金额           ", menu);
			jxl.write.Label menuName10 = new jxl.write.Label(10, 0,
					"扣除费用          ", menu);
			jxl.write.Label menuName11 = new jxl.write.Label(11, 0,
					"结汇金额          ", menu);
			
			sheet.addCell(menuName0);
			sheet.addCell(menuName1);
			sheet.addCell(menuName2);
			sheet.addCell(menuName3);
			sheet.addCell(menuName4);
			sheet.addCell(menuName5);
			sheet.addCell(menuName6);
			sheet.addCell(menuName7);
			sheet.addCell(menuName8);
			sheet.addCell(menuName9);
			sheet.addCell(menuName10);
			sheet.addCell(menuName11);
			int ts = 0;
			int jz = 0;
			int zl = 0;
			int dj = 0;
			int xxje = 0;
			int jhje = 0;
			int kcfy = 0;
			List list = (List) request.getSession().getAttribute("list");
			for (int i = 0; i <list.size(); i++) {
				Sales cell = (Sales) list.get(i);
				ts += cell.getToushu();
				zl += cell.getZhongliang();
				xxje += cell.getSalesjine();
				kcfy += cell.getKouchufeiyong();
				Label label0 = new Label(0, i + 1, String.valueOf(cell.getSalesId()));
				Label label1 = new Label(1, i + 1, String.valueOf(cell.getExportId()));
				Label label2 = new Label(2, i + 1, String.valueOf(cell.getSalesDate()));
				Label label3 = new Label(3, i + 1, cell.getZhenyinhao());
				Label label4 = new Label(4, i + 1, cell.getFahuodanwei());
				Label label5 = new Label(5, i + 1, String.valueOf(cell.getToushu()));
				Label label6 = new Label(6, i + 1, String.valueOf(cell.getZhongliang()));
				Label label7 = new Label(7, i + 1, String.valueOf(cell.getJunzhong()));
				Label label8 = new Label(8, i + 1, String.valueOf(cell.getDanjia()));
				Label label9 = new Label(9, i + 1, String.valueOf(cell.getSalesjine()));
				Label label10 = new Label(10, i + 1, String.valueOf(cell.getKouchufeiyong()));
				Label label11 = new Label(11, i + 1, String.valueOf(cell.getJiehuijine()));
				// 将定义好的单元格添加到工作表中
				sheet.addCell(label0);
				sheet.addCell(label1);
				sheet.addCell(label2);
				sheet.addCell(label3);
				sheet.addCell(label4);
				sheet.addCell(label5);
				sheet.addCell(label6);
				sheet.addCell(label7);
				sheet.addCell(label8);
				sheet.addCell(label9);
				sheet.addCell(label10);
				sheet.addCell(label11);
			}
				jz = zl/ts;
				dj = xxje/zl;
				jhje = xxje-kcfy;
				String[] str={"合计","-","-","-","-",String.valueOf(ts),String.valueOf(zl),String.valueOf(jz),String.valueOf(dj),String.valueOf(xxje),String.valueOf(kcfy),String.valueOf(jhje)};
				for (int i = 0; i < str.length; i++) {
					Label label0 = new Label(i, list.size()+2, str[i]);
					sheet.addCell(label0);
				}
				// 写入数据并关闭文件
				book.write();
				book.close();
				os.close();
				
		} 
		catch (Exception e) {
		}
	}

      

 7. 因前面还有一些工作，所以在接收List 时是从Session中得到的，其实用request就行，因为request已经传进来了，还有就是此导出功能 带有合计，这个在测试时可以不要，它是此系统中客户要求的，呵呵    

 

     (导入) 8.在某个jsp页面上点击导入按钮或者链接进行访问，进入Action中的salesInExcel方法后，就开始导入了，首先需要动态From:DynaActionForm,当然这需要在struts-config.xml中进行配置，如下图，它会从页面上的文件浏览框中得到要导入的Excel表的路径，还要判断是不是.xls文件,如下：

 

<struts-config>
	<data-sources />
	<form-beans>
		<form-bean name="file" type="org.apache.struts.action.DynaActionForm">
			<form-property name="filename" type="org.apache.struts.upload.FormFile"></form-property>
		</form-bean>
	</form-beans>
	<global-exceptions />
	<global-forwards></global-forwards>

	<action-mappings>
		<!--**系统**部的**数据的配置文件-->
		<action path="/sales" scope="request" name="file" parameter="method"
			type="org.springframework.web.struts.DelegatingActionProxy">
			<forward name="salesrk" path="salesrk"></forward>
		</action>
	</action-mappings>
</struts-config>

  

  注意：<form-bean>和<action>中的name 必须和jsp页面上的那个文本浏览框(type="file")的name值一样

 

public ActionForward salesInExcel(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws NumberFormatException, ParseException {
		DynaActionForm daf = (DynaActionForm) form;
		FormFile ff = (FormFile) daf.get("filename");
		if (ff.getFileName().substring(ff.getFileName().lastIndexOf(".") + 1,
				ff.getFileName().length()).equals("xls")) {
			try {
				InputStream in = ff.getInputStream();
				sale.getSalseDaoru(in);
				request.setAttribute("salesdaoru", 0);
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
		} else {
			request.setAttribute("salesdaoru", 1);
		}
		return new ActionForward("/sales.do?method=salesZuheChaxun1");
	}

    

   9. 进入Action中的getSalseDaoru后，开始导入：

 

@Override
	public void getSalseDaoru(InputStream in) throws NumberFormatException,
			ParseException {
		Workbook wb;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		try {
			wb = Workbook.getWorkbook(in);
			Sheet sheet = wb.getSheet(0);
			int rs = sheet.getRows()-2;
			String[] cs = new String[sheet.getColumns()];
			for (int i = 1; i < rs; i++) {
				for (int j = 0; j < sheet.getColumns(); j++) {
					Cell cell = sheet.getCell(j, i);
					cs[j] = cell.getContents();
				}
				Sales r = new Sales(Integer.parseInt(cs[0]), Integer.parseInt(cs[1]),sdf.parse(cs[2]),cs[3], cs[4], Float.parseFloat(cs[5]),Float.parseFloat(cs[6]),Float.parseFloat(cs[7]),Float
						.parseFloat(cs[8]), Float.parseFloat(cs[9]), Float.parseFloat(cs[10]), Float.parseFloat(cs[11]));
				datadao.updateModel(Sales.class, r);
			}
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

      

 10. 得到工作表后，再得到行和列，减去前两行，它们不必导入，在前面的实体类中为什么要有 "有参构造函数"，此时大家应该明白了，在导入前如果外部的Excel表中的数据被修改过，那在导入时，这些数据将会修改表中的数据（属性的值）,需要注意的是，有往“有参构造函数”中传值的时候，每个属性对应的类型要搞对，否则会报错的，如前三个属性： Integer.parseInt(cs[0]), Integer.parseInt(cs[1]),sdf.parse(cs[2])，呵呵

 

      

 好了，如果还有不明白之处，可以信息或留言联系，愿与大家共进步，谢谢

  

iteye_8211
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值