一、搭建环境
将下载后的文件解包,得到jxl.jar,放入classpath,就可以使用了。
二、向excel中写入内容的类
WriteExcel.java
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;
File finalXlsxFile = new File(finalXlsxPath);
Workbook workBook = getWorkbok(finalXlsxFile);
Sheet sheet = workBook.getSheetAt(0);
int rowNumber = sheet.getLastRowNum();
System.out.println("原始数据总行数,除属性列:" + rowNumber);
for (int i = 1; i <= rowNumber; i++) {
Row row = sheet.getRow(i);
sheet.removeRow(row);
}
out = new FileOutputStream(finalXlsxPath);
workBook.write(out);
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);
}
}
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("数据导出成功");
}
public static Workbook getWorkbok(File file) throws IOException{
Workbook wb = null;
FileInputStream in = new FileInputStream(file);
if(file.getName().endsWith(EXCEL_XLS)){
wb = new HSSFWorkbook(in);
}else if(file.getName().endsWith(EXCEL_XLSX)){
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();
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();
}
}
public List readExcel(File file) {
try {
InputStream is = new FileInputStream(file.getAbsolutePath());
Workbook wb = Workbook.getWorkbook(is);
int sheet_size = wb.getNumberOfSheets();
for (int index = 0; index < sheet_size; index++) {
List<List> outerList=new ArrayList<List>();
Sheet sheet = wb.getSheet(index);
for (int i = 0; i < sheet.getRows(); i++) {
List innerList=new ArrayList();
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;
}
}
四、测试结果