</pre><p></p><pre name="code" class="java">java读取excel表格文件的方法。慢慢感受。
package firstTest;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import jxl.read.biff.BiffException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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 readExcel {
public static void main(String[] args) {
// TODO Auto-generated method stub
readExcel r = new readExcel();
try {
File file = new File("");
//String srcdir = file.getAbsolutePath()+"\\src\\firstTest\\123.xls";
String srcdir = file.getAbsolutePath()+"\\src\\firstTest\\123.xlsx";
Map<String, List<Map<String, String>>> data = r.readXls(srcdir);
List<Map<String, String>> sheet1 = data.get("Sheet1");
for(int i = 0;i<sheet1.size();i++){
System.out.println(sheet1.get(i));
}
} catch (BiffException | IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Map<String, List<Map<String, String>>> readXls(String srcDir) throws FileNotFoundException,IOException,BiffException{//srcDIr为文件路径
//Map<sheetName,List<Map<列头名,值>>>
Map<String, List<Map<String, String>>> map = new HashMap<String, List<Map<String, String>>>();
File xlsFile = new File(srcDir);
if(!xlsFile.exists())
throw new FileNotFoundException("file not found");
try {
InputStream in = new FileInputStream(srcDir);
Workbook wb = openWorkbook(in,srcDir);
Row row = null;
Cell cell = null;
if(wb != null){
int count=wb.getNumberOfSheets();
for(int k=0;k<count;k++){
String sheets = wb.getSheetName(k);
Sheet sheet = wb.getSheetAt(k);
int totalRowCount = sheet.getPhysicalNumberOfRows();//总行数
row=sheet.getRow(0);
int totalColCount=0;
if(null!=row)
totalColCount = row.getPhysicalNumberOfCells();//总列数
List<Map<String, String>> list = new ArrayList<Map<String, String>>(count);
String[] keys = new String[totalColCount];
//第一行列头做为key
for(int i=0;i<totalColCount;i++){
Cell cl=sheet.getRow(0).getCell(i);
String cellValue = dataToString(cl);
keys[i] = cellValue;
}
//数据转换
for(int i=1;i<totalRowCount;i++){
row=sheet.getRow(i);
Map<String,String> rowMap = new HashMap<String,String>(totalColCount);
for(int j=0;j<totalColCount;j++){
String cellValue="";
if(row!=null){
cell=row.getCell(j);
cellValue = dataToString(cell);
}
rowMap.put(keys[j], cellValue);
}
list.add(rowMap);
}
if(!list.isEmpty())
map.put(sheet.getSheetName(), list);
}
}
in.close();
} catch (IndexOutOfBoundsException e) {
e.printStackTrace();
}
return map;
}
public static Workbook openWorkbook(InputStream in,String filename)throws IOException{
Workbook wb = null;
if(filename.toLowerCase().endsWith(".xlsx")){
try{
wb = new XSSFWorkbook(in);//Excel 2007
}catch(Exception e){
wb=null;
}
} else {
try{
wb = new HSSFWorkbook(in);//Excel 2003
}catch(Exception e){
wb=null;
}
}
return wb;
}
public static String dataToString(Cell cell){
String cellValue = "";
if(null != cell){
// 以下是判断数据的类型
switch (cell.getCellType())
{
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
//时间格式
if(HSSFDateUtil.isCellDateFormatted(cell)){
Date dd = cell.getDateCellValue();
DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cellValue = df.format(dd);
}else{
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellValue = cell.getStringCellValue();
}
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
cellValue = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
cellValue = cell.getCellFormula() + "";
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
cellValue = "";
break;
default:
cellValue = "";
break;
}
}
return cellValue;
}
}
其中引用的是poi的jar包来读取excel。同时支持xls和xlsx两种格式。
excel表格的内容是这样的。
下面是打印出来的信息。可以看到返回的excel 的内容是一个list中包含了一个一个map,在这个map中存的是这个的信息的列头和信息的内容。这样很方便我们操作excel表格里面的数据。
{第一列=123, 第二列=abc}
{第一列=456, 第二列=def}