尝试了jxl和poi
首先 jxl
package excel;
import java.io.File;
import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
public class Jxl {
public static void main(String[] args) {
Sheet sheet;
Workbook book;
Cell cell1;
try {
// t.xls为要读取的excel文件名
book = Workbook.getWorkbook(new File("src//main//resources//test.xls"));
// 获得第一个工作表对象(ecxel中sheet的编号从0开始,0,1,2,3,....)
sheet = book.getSheet(0);
// 获取左上角的单元格
cell1 = sheet.getCell(0, 0);
System.out.println("标题:" + cell1.getContents());
//获取表格的总行数
int rows = sheet.getRows();
int cols = sheet.getColumns();
//读取表格的内容
for(int i=1;i<rows;i++){
// 获取每一行的单元格
for(int j=0;j<cols;j++){
Cell cell = sheet.getCell(j,i);// (列,行)
// System.out.println(cell1.getType());
// System.out.println(CellType.LABEL);
if ("".equals(cell.getContents()) == true) // 如果读取的数据为空
break;
System.out.print(cell.getContents() + "\t");
}
System.out.println("");
}
book.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
上述代码未对单元格的数据进行类型的判定,直接获取string
poi:
package excel;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
/**
* 操作Excel表格的功能类
*/
public class Poi {
/**
* 获取单元格数据内容为字符串类型的数据
*
* @param cell
* Excel单元格
* @return String 单元格数据内容
*/
private String getStringCellValue(HSSFCell cell) {
String strCell = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
strCell = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell.equals("") || strCell == null) {
return "";
}
return strCell;
}
/**
* 获取单元格数据内容为日期类型的数据
*
* @param cell
* Excel单元格
* @return String 单元格数据内容
*/
@SuppressWarnings({ "deprecation", "unused" })
private String getDateCellValue(HSSFCell cell) {
String result = "";
try {
int cellType = cell.getCellType();
if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
Date date = cell.getDateCellValue();
result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)
+ "-" + date.getDate();
} else if (cellType == HSSFCell.CELL_TYPE_STRING) {
String date = getStringCellValue(cell);
result = date.replaceAll("[年月]", "-").replace("日", "").trim();
} else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
result = "";
}
} catch (Exception e) {
System.out.println("日期格式不正确!");
e.printStackTrace();
}
return result;
}
/**
* 根据HSSFCell类型设置数据
*
* @param cell
* @return
*/
private static String getCellFormatValue(HSSFCell cell) {
String cellvalue = "";
if (cell != null) {
// 判断当前Cell的Type
switch (cell.getCellType()) {
// 如果当前Cell的Type为NUMERIC
case HSSFCell.CELL_TYPE_NUMERIC:
case HSSFCell.CELL_TYPE_FORMULA: {
// 判断当前的cell是否为Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是Date类型则,转化为Data格式
// 方法1:这样子的data格式是带时分秒的:2011-10-12 0:00:00
// cellvalue = cell.getDateCellValue().toLocaleString();
// 方法2:这样子的data格式是不带带时分秒的:2011-10-12
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = sdf.format(date);
}
// 如果是纯数字
else {
// 取得当前Cell的数值
cellvalue = String.valueOf(cell.getNumericCellValue());
}
break;
}
// 如果当前Cell的Type为STRIN
case HSSFCell.CELL_TYPE_STRING:
// 取得当前的Cell字符串
cellvalue = cell.getRichStringCellValue().getString();
break;
// 默认的Cell值
default:
cellvalue = " ";
}
} else {
cellvalue = "";
}
return cellvalue;
}
@SuppressWarnings("deprecation")
public static void main(String[] args) {
POIFSFileSystem fs;
HSSFWorkbook wb = null;
HSSFSheet sheet;
HSSFRow row;
try {
InputStream is = new FileInputStream(
"src//main//resources//test2.xls");
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
sheet = wb.getSheetAt(0);
row = sheet.getRow(0);
// 标题总列数
int colNum = row.getPhysicalNumberOfCells();
System.out.println("colNum:" + colNum);
System.out.print("标题:");
for (int i = 0; i < colNum; i++) {
// title[i] = getStringCellValue(row.getCell((short) i));
System.out.print(getCellFormatValue(row.getCell((short) i))+"\t");
}
System.out.println("");
// 正文内容应该从第二行开始,第一行为表头的标题
// 得到总行数
int rowNum = sheet.getLastRowNum();
for (int i = 1; i <= rowNum; i++) {
row = sheet.getRow(i);
int j = 0;
while (j < colNum) {
// 每个单元格的数据内容用"-"分割开,以后需要时用String类的replace()方法还原数据
// 也可以将每个单元格的数据设置到一个javabean的属性中,此时需要新建一个javabean
// str += getStringCellValue(row.getCell((short) j)).trim() +
// "-";
System.out.print(getCellFormatValue(row.getCell((short) j)).trim() + "\t");
j++;
}
System.out.println("");
}
} catch (IOException e) {
e.printStackTrace();
}
}
}