excel讀取java
來源：趣味經驗館 2.29W
<link rel="stylesheet" href="https://js.how234.com/third-party/SyntaxHighlighter/shCoreDefault.css" type="text/css" /><script type="text/javascript" src="https://js.how234.com/third-party/SyntaxHighlighter/shCore.js"></script><script type="text/javascript"> SyntaxHighlighter.all(); </script>
如何在excel讀取java，讓我們一起來了解一下？
1、首先新增處理excel的依賴jar包。
<!-- 引入poi，解析workbook檢視 --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.16</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.14</version> </dependency> <!-- 處理excel和上面功能是一樣的--> <dependency> <groupId>net.sourceforge.jexcelapi</groupId> <artifactId>jxl</artifactId> <version>2.6.10</version> </dependency>
2、向excel中寫入內容的類。
package com.li.controller;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;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 WriteExcel { private static final String EXCEL_XLS = "xls"; private static final String EXCEL_XLSX = "xlsx"; public static void main(String[] args) { Map<String, String> dataMap=new HashMap<String, String>(); dataMap.put("BankName", "BankName"); dataMap.put("Addr", "Addr"); dataMap.put("Phone", "Phone"); List<Map> list=new ArrayList<Map>(); list.add(dataMap); writeExcel(list, 3, "D:/writeExcel.xlsx"); } public static void writeExcel(List<Map> dataList, int cloumnCount,String finalXlsxPath){ OutputStream out = null; try { // 獲取總列數 int columnNumCount = cloumnCount; // 讀取Excel文件 File finalXlsxFile = new File(finalXlsxPath); Workbook workBook = getWorkbok(finalXlsxFile); // sheet 對應一個工作頁 Sheet sheet = workBook.getSheetAt(0); /** * 刪除原有資料，除了屬性列 */ int rowNumber = sheet.getLastRowNum(); // 第一行從0開始算 System.out.println("原始資料總行數，除屬性列：" + rowNumber); for (int i = 1; i <= rowNumber; i++) { Row row = sheet.getRow(i); sheet.removeRow(row); } // 建立檔案輸出流，輸出電子表格：這個必須有，否則你在sheet上做的任何操作都不會有效 out = new FileOutputStream(finalXlsxPath); workBook.write(out); /** * 往Excel中寫新資料 */ for (int j = 0; j < dataList.size(); j++) { // 建立一行：從第二行開始，跳過屬性列 Row row = sheet.createRow(j + 1); // 得到要插入的每一條記錄 Map dataMap = dataList.get(j); String name = dataMap.get("BankName").toString(); String address = dataMap.get("Addr").toString(); String phone = dataMap.get("Phone").toString(); for (int k = 0; k <= columnNumCount; k++) { // 在一行內迴圈 Cell first = row.createCell(0); first.setCellValue(name); Cell second = row.createCell(1); second.setCellValue(address); Cell third = row.createCell(2); third.setCellValue(phone); } } // 建立檔案輸出流，準備輸出電子表格：這個必須有，否則你在sheet上做的任何操作都不會有效 out = new FileOutputStream(finalXlsxPath); workBook.write(out); } catch (Exception e) { e.printStackTrace(); } finally{ try { if(out != null){ out.flush(); out.close(); } } catch (IOException e) { e.printStackTrace(); } } System.out.println("資料匯出成功"); } /** * 判斷Excel的版本,獲取Workbook * @param in * @param filename * @return * @throws IOException */ public static Workbook getWorkbok(File file) throws IOException{ Workbook wb = null; FileInputStream in = new FileInputStream(file); if(file.getName().endsWith(EXCEL_XLS)){ //Excel 2003 wb = new HSSFWorkbook(in); }else if(file.getName().endsWith(EXCEL_XLSX)){ // Excel 2007/2010 wb = new XSSFWorkbook(in); } return wb; }}
3、讀取Excel中的資料，並寫入list中。
package com.li.controller;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.List;import jxl.Sheet;import jxl.Workbook;import jxl.read.biff.BiffException;public class ReadExcel { public static void main(String[] args) { ReadExcel obj = new ReadExcel(); // 此處為我建立Excel路徑：E:/zhanhj/studysrc/jxl下 File file = new File("D:/readExcel.xls"); List excelList = obj.readExcel(file); System.out.println("list中的資料打印出來"); for (int i = 0; i < excelList.size(); i++) { List list = (List) excelList.get(i); for (int j = 0; j < list.size(); j++) { System.out.print(list.get(j)); } System.out.println(); } } // 去讀Excel的方法readExcel，該方法的入口引數為一個File物件 public List readExcel(File file) { try { // 建立輸入流，讀取Excel InputStream is = new FileInputStream(file.getAbsolutePath()); // jxl提供的Workbook類 Workbook wb = Workbook.getWorkbook(is); // Excel的頁籤數量 int sheet_size = wb.getNumberOfSheets(); for (int index = 0; index < sheet_size; index++) { List<List> outerList=new ArrayList<List>(); // 每個頁籤建立一個Sheet物件 Sheet sheet = wb.getSheet(index); // sheet.getRows()返回該頁的總行數 for (int i = 0; i < sheet.getRows(); i++) { List innerList=new ArrayList(); // sheet.getColumns()返回該頁的總列數 for (int j = 0; j < sheet.getColumns(); j++) { String cellinfo = sheet.getCell(j, i).getContents(); if(cellinfo.isEmpty()){ continue; } innerList.add(cellinfo); System.out.print(cellinfo); } outerList.add(i, innerList); System.out.println(); } return outerList; } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (BiffException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return null; }}4、在D盤下面建立readExcel.xls（有內容） 和writeExcel.xlsx即可。
