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即可。
