struts1 使用poi组件读取excel文件，创建excel ，输出excel文件

最新推荐文章于 2021-10-25 23:33:36 发布

dianleichai2987

最新推荐文章于 2021-10-25 23:33:36 发布

阅读量170

点赞数

文章标签： javascript java ViewUI

原文链接：http://www.cnblogs.com/wyang0126/p/5039951.html

版权

首先要下载poi.jar 包，还有上传文件的包：commons.fileupload.jar、commons.logging.jar、commons.beanutils.jar、commons.collections.jar、commons.io.jar、commons.lang.jar，主要是为了上传excel ，然后才能读取里面的内容。struts2 也是类似的；

然后准备个jsp页面，直接贴上我的jsp页面：

<%@ page contentType="text/html;charset=UTF-8"%>
<%@ include file="/WEB-INF/page/share/taglib.jsp"%>
<html>
<head>
<title>从Excel文件导入</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link rel="stylesheet" href="/css/vip.css" type="text/css">
<SCRIPT language=JavaScript src="/js/FoshanRen.js"></SCRIPT>
<script language="JavaScript">
function checkfm(form){
	if (trim(form.file.value)==""){
		alert("请选择文件！");
		form.file.focus();
		return false;
	}
	return true;
}
</script>
</head>
<body bgcolor="#FFFFFF" text="#000000" leftmargin="0" topmargin="0"
	marginwidth="0" marginheight="0">
	<table width="90%" border="0" cellspacing="2" cellpadding="3"
		align="center">
		<tr bgcolor="f5f5f5">
			<td><a style="text-decoration:none"
				href="/memberadmin/supply/batch.do?method=createExcel"> <span><h2>点此下载EXCEL模板</h2>
				</span> </a></td>
		</tr>
		<tr bgcolor="f5f5f5">
			<td><span style="color:red;">注：先下载模板,不要修改模板，直接填写数据，然后上传填写好数据的EXCEL文件</span>
			</td>
		</tr>
	</table>
	<html:form action="/memberadmin/supply/batch" method="post"
		enctype="multipart/form-data" οnsubmit="return checkfm(this)">

		<input type="hidden" name="method" value="update">
		<br>
		<table width="90%" border="0" cellspacing="2" cellpadding="3"
			align="center">
			<tr bgcolor="6f8ac4">
				<td colspan="3"><font color="#FFFFFF">上传文件：</font>
				</td>
			</tr>
			<tr bgcolor="f5f5f5">
				<td width="20%">
					<div align="right">Excel文件：</div>
				</td>
				<td width="38%"><html:file property="file" size="50"
						maxlength="50" /></td>
			</tr>
			<tr bgcolor="f5f5f5">
				<td colspan="3">
					<div align="center">
						<input type="hidden" name="type" value="${param.type}" /> <input
							type="button" οnclick="javascript:history.back();" value=" 返 回 "
							class="frm_btn">       <input
							type="submit" name="SYS_SET" value=" 确 定 " class="frm_btn">
					</div>
				</td>
			</tr>
		</table>
	</html:form>
	<br>
</body>
</html>

然后，写个Action ：

package cn.togo.web.action.supply;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.actions.DispatchAction;
import org.springframework.stereotype.Controller;

import cn.togo.web.formbean.update.UpdateForm;
import cn.togo.web.formbean.v2.BatchForm;

@Controller("/memberadmin/supply/batch")
public class BatchAction extends DispatchAction {

	// 跳转到上传页面
	public ActionForward forward(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		return mapping.findForward("forward");
	}

	// 更新数据
	public ActionForward update(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		BatchForm updateForm = (BatchForm) form;
		Workbook workbook = null;
		try {
			workbook = new HSSFWorkbook(updateForm.getFile().getInputStream());
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		getData(workbook);
		return mapping.findForward("update");
	}
   //从excel上获取数据
	private void getData(Workbook workbook) {
		// TODO Auto-generated method stub
		List<String> list = new ArrayList<String>();
		Sheet sheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
		int rows = sheet.getLastRowNum();
		for (int i = 0; i < rows; i++) {
			Row row = sheet.getRow(i);
			if (row == null || row.getCell(0) == null) {
				continue;
			}
			String value = row.getCell(0).getStringCellValue();
			list.add(value);
		}
		int j = 0;
		for (String s : list) {
			System.out.println(j++ + " : " + s);
		}
	}

	// 创建excel
	public ActionForward createExcel(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		BatchForm batchForm = (BatchForm) form;
		create(response);
		return mapping.findForward("create");
	}

	//向新创建的excel添加数据，同时下载
	@SuppressWarnings("deprecation")
	private void create(HttpServletResponse response) throws IOException {
		// TODO Auto-generated method stub
		response.setHeader("Content-Type", "application/octet-stream");
		response.setHeader("Content-disposition", "attachment;filename="
				+ "test.xls");

		HSSFWorkbook wb = new HSSFWorkbook();// 创建Excel工作簿对象
		HSSFSheet sheet = wb.createSheet("my first excel");// 创建Excel工作表对象
		HSSFCellStyle cellStyle = wb.createCellStyle();// 创建单元格样式
		cellStyle.setLocked(true);
		cellStyle.setFillBackgroundColor(HSSFColor.YELLOW.index);

		HSSFRow row = sheet.createRow((short) 0); // 创建Excel工作表的第一行
		HSSFCell cell = null;
		cell = row.createCell((short) 0);// 创建Excel工作表第一行第一列单元格
		cell.setCellStyle(cellStyle);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 设置该单元格字符类型
		cell.setCellValue("Member_Id"); // 设置该单元格的值

		cell = row.createCell((short) 1);
		cell.setCellStyle(cellStyle);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue("999");

		HSSFRow row2 = sheet.createRow((short) 1);
		HSSFCell cell2 = null;
		cell2 = row2.createCell((short) 0);
		cell2.setCellStyle(cellStyle);
		cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell2.setCellValue("所属分类");

		HSSFCell cell3 = null;
		cell3 = row2.createCell((short) 1);
		cell3.setCellStyle(cellStyle);
		cell3.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell3.setCellValue("产品名称");

		HSSFCell cell4 = null;
		cell4 = row2.createCell((short) 2);
		cell4.setCellStyle(cellStyle);
		cell4.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell4.setCellValue("品牌列表");

		HSSFCell cell5 = null;
		cell5 = row2.createCell((short) 3);
		cell5.setCellStyle(cellStyle);
		cell5.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell5.setCellValue("型号列表");

		HSSFCell cell6 = null;
		cell6 = row2.createCell((short) 4);
		cell6.setCellStyle(cellStyle);
		cell6.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell6.setCellValue("库存");

		HSSFCell cell7 = null;
		cell7 = row2.createCell((short) 5);
		cell7.setCellStyle(cellStyle);
		cell7.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell7.setCellValue("单位");

		OutputStream outputStream = response.getOutputStream();
		try {
			wb.write(outputStream);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			outputStream.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

转载于:https://www.cnblogs.com/wyang0126/p/5039951.html