java Servlet 实现解析Excel

后台

	@SuppressWarnings("all")
	public void execute(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		request.setCharacterEncoding("utf-8"); // 设置编码
		DiskFileItemFactory factory = new DiskFileItemFactory();
		factory.setSizeThreshold(1024 * 512);
		factory.setRepository(new File("D:/tempload"));
		ServletFileUpload fileUpload = new ServletFileUpload(factory);
		fileUpload.setFileSizeMax(10 * 1024 * 1024);// 设置最大文件大小
		// 设置一些父节点的信息
		MonitorObjectVO monitorObjectVO = new MonitorObjectVO();
		monitorObjectVO.setParent_group_id(request.getParameter("id"));
		monitorObjectVO.setGroup_type(Integer.parseInt(request.getParameter(
				"type").trim()));
		monitorObjectVO.setLayer(Integer.parseInt(request.getParameter("layer")
				.trim()) + 1);
		monitorObjectDAO = (MonitorObjectDAO) WebApplicationContextUtils
				.getRequiredWebApplicationContext(getServletContext()).getBean(
						"monitorObjectDAO");
		try {
			List<FileItem> items = fileUpload.parseRequest(request);// 获取所有表单
			for (FileItem item : items) {
				if ("filePath".equals(item.getFieldName())) {
					// 获取excel中的值
					List<MonitorObjectVO> excelGroupDataList = ExcelUtils
							.loadExcel(item.getInputStream(),
									request.getParameter("filePath"));

					HttpSession session = request.getSession(true);
					MessageVO messageVO = new MessageVO();
					// 后台批量处理并返回些信息
					messageVO = this.monitorObjectDAO.insertGroupFromExcel(
							excelGroupDataList, monitorObjectVO);
					// 如果没有错误信息
					String parentId = messageVO.getMessage().split("=")[1];
					if (excelGroupDataList.size() == Integer.parseInt(messageVO
							.getShowSuccessCount().trim())) {
						messageVO.setMessage("");
					}
					session.setAttribute(ConfigConstant.REQUEST_OBJECT,
							messageVO);

					response.sendRedirect("/web/edmp/page/abnormalmonitor/monitorobject/ExcelImport.jsp?parent_id="
							+ parentId);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

monitorObjectDAO = (MonitorObjectDAO) WebApplicationContextUtils
				.getRequiredWebApplicationContext(getServletContext()).getBean(
						"monitorObjectDAO");

这句话是实现servlet中调用spring bean

if ("filePath".equals(item.getFieldName()))
{
   item.getInputStream() ;
}

filePath是页面file类型的name 它里面的inputStream才有Excel内容。

ExcelUtils

@SuppressWarnings("all")
	public static List<MonitorObjectVO> loadExcel(InputStream is,
			String filePath) {

		List<MonitorObjectVO> excelGroups = null;
		String errorMsg = "";
		try {
			Workbook workBook = null;
			try {
				if (filePath.endsWith(".xls")) { // 97-03
					workBook = new HSSFWorkbook(is);
				} else if (filePath.endsWith(".xlsx")) { // 2007
					workBook = new XSSFWorkbook(is);
				} else {
					System.out.println("不支持的文件类型！");
					return null;
				}
			} catch (Exception e) {
				System.out.println("解析Excel文件出错！");
				e.printStackTrace();
			} finally {
				is.close();
			}
			int sheets = null != workBook ? workBook.getNumberOfSheets() : 0;
			excelGroups = new ArrayList<MonitorObjectVO>();
			Sheet sheet = workBook.getSheetAt(0); // 读取第一个sheet
			int rows = sheet.getPhysicalNumberOfRows(); // 获得行数
			if (rows > 1) { // 第一行默认为标题
				// sheet.getMargin(HSSFSheet.TopMargin);
				for (int j = 1; j < rows; j++) {
					Row row = sheet.getRow(j);
					MonitorObjectVO groupObj = new MonitorObjectVO();
					int cells = row.getLastCellNum();// 获得列数
					if (cells > 0) {
						for (int k = 0; k < cells; k++) {
							Cell cell = row.getCell(k);
							// 全部置成String类型的单元格
							cell.setCellType(Cell.CELL_TYPE_STRING);
							groupObj.setGroup_name(cell.getStringCellValue());
						}
					} else {
						errorMsg = "EXCEL没有数据，请确定。";
					}
					excelGroups.add(groupObj);
				}
			} else {
				errorMsg = "EXCEL没有数据，请确定。";
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		if (errorMsg.length() > 0) {
			System.out.println("错误消息：" + errorMsg);
		}
		return excelGroups;
	}

	public static void main(String[] args) {
		try {
			System.out.println(loadExcel(new FileInputStream("d:/123.xlsx"),
					"d:/123.xlsx").size());
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
	}

Js 提交按钮代码

	/**
			 * 导入内容操作
			 */
		    function importContent(){
		    	var fileName = document.excelImportForm.filePath.value;
		    	if(fileName==""){
		    		alert("请选择文件!");
		    		return;
		    	}
				var objYesDiv = document.getElementById('yes');
				objYesDiv.style.display = "none";
				var objNoDiv = document.getElementById('no');
				objNoDiv.style.display = "block";
				var sending = document.getElementById('sending');
		    	sending.style.visibility="visible";
		    	var messageBar = document.excelImportForm.messageBar.value;
			    if ( messageBar == "true" ){
			    	//excel上传进度条提示信息
			    	onloadExcelProcessMessage();
			    }
			    var url = getExcelImportAction("importOperate");
 			    document.excelImportForm.action=url; //url即为servlet路径
			    document.excelImportForm.submit();
		    }

Excel下载模板

/**
 * 
 */


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;



public class xxxServlet extends HttpServlet {

	private static final long serialVersionUID = 1L;
	// 日志记录
	private final static Logger logger = LoggerFactory
			.getLogger(xxxServlet.class);

	public void init(ServletConfig servletConfig) throws ServletException {
		super.init(servletConfig);
	}

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doPost(request, response);
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		execute(request, response);
	}

	@SuppressWarnings("all")
	public void execute(HttpServletRequest request, HttpServletResponse response) {
		try {
			response.setContentType("application/vnd.ms-excel;charset=UTF-8");
			response.setHeader("Content-Disposition", "attachment;filename="
					+ new String("xx模板".getBytes("GB2312"), "ISO8859-1")
					+ ".xls");
			OutputStream stream = response.getOutputStream();
			String path = getServletContext().getRealPath("/");
			// 获取源文件路径
			File srcFile = new File(
					path
						+ "\\x\\x\\x\\x\\xx.xls");
			InputStream ins = new FileInputStream(srcFile);
			byte[] byt = null;
			byt = new byte[ins.available()];
			ins.read(byt);
			stream.write(byt);
		} catch (FileNotFoundException e) {
			
			e.printStackTrace();
		} catch (IOException e) {
			
			e.printStackTrace();
		}
	}

}

function doDownloadExcelModel()
{
	var webRoot = "xxxx";
	var url = webRoot + "/xx/.xxxServlet";
	location.href = url;
}