java进行excel读取和写入。

心已赠人 2024-02-18 20:08 40阅读 0赞

## 添加处理excel的依赖jar包 ##

<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>  
  
<dependency>  
<groupId>net.sourceforge.jexcelapi</groupId>  
<artifactId>jxl</artifactId>  
<version>2.6.10</version>  
</dependency>

## 向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&nbsp;2003  
wb = new HSSFWorkbook(in);  
\}else if(file.getName().endsWith(EXCEL\_XLSX))\{ // Excel 2007/2010  
wb = new XSSFWorkbook(in);  
\}  
return wb;  
\}  
\}

## 读取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;  
\}  
\}

## 在D盘下面创建readExcel.xls（有内容） 和writeExcel.xlsx即可 ##

转载：[https://www.cnblogs.com/liyafei/p/8146136.html][https_www.cnblogs.com_liyafei_p_8146136.html]

[https_www.cnblogs.com_liyafei_p_8146136.html]: https://www.cnblogs.com/liyafei/p/8146136.html