web导入excel（利用POI解析）

来源：互联网发布：手机短信修改软件编辑：程序博客网时间：2024/06/07 19:53

今天跟大家分享下，如何导入excel文件（此处以POI作为例子讲解）

1、导入相关jar包（文件上传和poi的jar）

2、html页面代码，form表单提交，也可以用ajax

<form action="/MyTest/UploadAndPoiExcelServlet" enctype="multipart/form-data" method="post"><input type="file" name="f1"></input><input type="submit" value="上传"></input></form>

3、Servlet代码，拿到上传的文件流

protected void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {response.setContentType("text/html;charset=UTF-8");try {FileItemFactory factory = new DiskFileItemFactory();// 文件上传核心工具类ServletFileUpload upload = new ServletFileUpload(factory);upload.setFileSizeMax(10 * 1024 * 1024); // 单个文件大小限制upload.setSizeMax(50 * 1024 * 1024); // 总文件大小限制upload.setHeaderEncoding("UTF-8"); // 对中文文件编码处理if (ServletFileUpload.isMultipartContent(request)) {List<FileItem> list = upload.parseRequest(request);// 遍历for (FileItem item : list) {if (!item.isFormField()) {readExcel(item.getInputStream());}}}} catch (Exception e) {e.printStackTrace();}}/** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse *      response) */protected void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {// TODO Auto-generated method stubdoGet(request, response);}

4、POI读取excel工具类

public static void readExcel(InputStream input) throws Exception  {Workbook wb = null;try {wb = WorkbookFactory.create(input);Sheet sheet = wb.getSheetAt(0); // 获得第一个表单int totalRow = sheet.getLastRowNum();// 得到excel的总记录条数int columtotal = sheet.getRow(0).getPhysicalNumberOfCells();// 表头总共的列数System.out.println("总行数:" + totalRow + ",总列数:" + columtotal);for (int i = 1; i <= totalRow; i++) {// 遍历行for (int j = 0; j < columtotal; j++) {sheet.getRow(i).getCell(j).setCellType(Cell.CELL_TYPE_STRING);System.out.print(sheet.getRow(i).getCell(j).getStringCellValue() + "             ");}System.out.println();}} catch (Exception ex) {ex.printStackTrace();throw new Exception(ex);}finally { try {input.close();} catch (IOException e) {e.printStackTrace();}}}

5、测试

在浏览器中访问html

Excel文件内容如下：

eclipse控制台输出测试结果如下（第一行未打印是因为我从第二行开始循环的，一般第一行为表头，要打印第一行，将遍历行那儿的变量i=1改为i=0即可）：

2 0