示例
Excel文件:
数据读取:
保存路径:
Jar包准备
下载地址:
链接:https://pan.baidu.com/s/1RZAwEsFwjKMlnYYGwHMfaA
提取码:h9mj
文件上传
<form action="servlet/HelloServlet" enctype="multipart/form-data" method="post"> <span style="white-space:pre"></span><input type="file" name="file"/> <span style="white-space:pre"></span><input type="submit" value="提交"/>
</form>
Servlet代码
import java.io.IOException; import java.util.List; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.commons.fileupload.FileItem; import org.apache.commons.fileupload.disk.DiskFileItemFactory; import org.apache.commons.fileupload.servlet.ServletFileUpload; public class HelloServlet extends HttpServlet{ /** * */ private static final long serialVersionUID = 1L; @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request, response); } /** * 保存或读取 */ @SuppressWarnings("deprecation") @Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try{ request.setCharacterEncoding("UTF-8"); DiskFileItemFactory factory = new DiskFileItemFactory(); ServletFileUpload upload = new ServletFileUpload(factory); List<FileItem> list = upload.parseRequest(request); ExcelHelper excelHelper = new ExcelHelper(); for(int i=0;i<list.size();i++){ FileItem fileItem = (FileItem) list.get(i); //文件保存 excelHelper.excelDisk(fileItem, request.getRealPath("")); //数据读取 excelHelper.parseExcelBean(fileItem, fileItem.getName().substring(fileItem.getName().lastIndexOf(".")+1)); } }catch(Exception e){ e.printStackTrace(); }finally{ request.getRequestDispatcher("/index.jsp").forward(request, response); } } }
Excel操作
import java.io.FileOutputStream; import java.io.InputStream; import java.io.OutputStream; import java.text.SimpleDateFormat; import org.apache.commons.fileupload.FileItem; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ExcelHelper { /** * 保存Excel文件 * @param resource * @param path * @throws Exception */ public void excelDisk(FileItem fileItem,String path) throws Exception{ InputStream inputStream = fileItem.getInputStream(); String fileName = fileItem.getName().substring(fileItem.getName().lastIndexOf("\\")+1); OutputStream outputStream = new FileOutputStream(path+"/"+fileName); byte[] bs = new byte[1024]; while(inputStream.read(bs) != -1){ outputStream.write(bs); } outputStream.flush(); outputStream.close(); inputStream.close(); } /** * 读取Excel数据 * @param resource * @param suffix * @throws Exception */ public void parseExcelBean(FileItem fileItem,String suffix) throws Exception{ Workbook workbook = null; if(suffix.equals("xls")){ workbook = new HSSFWorkbook(fileItem.getInputStream()); }else if(suffix.equals("xlsx")){ workbook = new XSSFWorkbook(fileItem.getInputStream()); } for(int i=0;i<workbook.getNumberOfSheets();i++){ System.out.println("----------第"+(i+1)+"页----------"); Sheet sheet = workbook.getSheetAt(i); if(sheet == null){ continue; } for(int j=0;j<=sheet.getLastRowNum();j++){ Row row = sheet.getRow(j); if(row == null){ continue; } for(int k=0;k<row.getLastCellNum();k++){ Cell cell = row.getCell(k); if(cell == null){ continue; } System.out.print(getCellValue(cell)+"\t"); } System.out.println(); } } } @SuppressWarnings("deprecation") public String getCellValue(Cell cell) { String cellValue = ""; // 以下是判断数据的类型 switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: // 数字 if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); cellValue = sdf.format(org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue())).toString(); } else { DataFormatter dataFormatter = new DataFormatter(); cellValue = dataFormatter.formatCellValue(cell); } break; case Cell.CELL_TYPE_STRING: // 字符串 cellValue = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: // Boolean cellValue = cell.getBooleanCellValue() + ""; break; case Cell.CELL_TYPE_FORMULA: // 公式 cellValue = cell.getCellFormula() + ""; break; case Cell.CELL_TYPE_BLANK: // 空值 cellValue = ""; break; case Cell.CELL_TYPE_ERROR: // 故障 cellValue = "非法字符"; break; default: cellValue = "未知类型"; break; } return cellValue; } }