標籤：
本內容主要包括（檔案上傳、excel2003資料匯入資料庫）excel匯入資料庫功能需要jxl jar包支援
下面是檔案上傳的前端測試代碼
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>Insert title here</title></head><body> <form action="getAllByExcel" method="post" enctype="multipart/form-data"> <input type="file" name="file" > <input type="submit" value="上傳"> </form></body></html>
下面控制類用於excel檔案上傳到伺服器
@SuppressWarnings("unchecked") @RequestMapping(value="getAllByExcel" ,method = RequestMethod.POST) public Object getAllByExcel(HttpServletRequest request,HttpServletResponse response, ModelMap model){ MultipartHttpServletRequest mulltipartRequest=(MultipartHttpServletRequest)request; MultipartFile files=mulltipartRequest.getFile("file"); //得到上傳伺服器路徑 String path=request.getSession().getServletContext().getRealPath("/WEB-INF/res/upload"); String fileName=files.getOriginalFilename(); try { InputStream inputStream=files.getInputStream(); byte[] b = new byte[1048576]; int length = inputStream.read(b); path += "\\" + fileName; // 檔案流寫到伺服器端 FileOutputStream outputStream = new FileOutputStream(path); outputStream.write(b, 0, length); inputStream.close(); outputStream.close(); //解析excel List<Map<String, Object>> lists=CommonUtil.getAllExcel(path);
此功能需要匯入jxl相關jar包，此方法提供excel路徑返回List<Map<String, Object>>
//擷取excel資料 public static List<Map<String, Object>> getAllExcel(String url){ List<Map<String, Object>> models=new ArrayList<Map<String, Object>>(); try { File filse=new File(url); Workbook rwb=Workbook.getWorkbook(filse); Sheet sheet=rwb.getSheet(0); int clos=sheet.getColumns(); //擷取列數 int rows=sheet.getRows();//擷取行數 String[] heads =new String[clos];//儲存所有的key值 for (int i = 0; i < rows; i++) { Map<String, Object> mp =new HashMap<String,Object>(); for(int j=0;j<clos;j++){ if(i == 0 ){ heads[j] = sheet.getCell(j, i).getContents();//擷取所有key值 } else{ mp.put(heads[j], sheet.getCell(j, i).getContents());//擷取每行value值 } } //如果map不為空白則添加到list集合 if (!mp.isEmpty()) { models.add(mp); } } } catch (Exception e) { // TODO: handle exception } return models; }
記錄-java（jxl） Excel匯入資料庫
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
