准备:1.在D盘创建NPS目录,目录下创建test.xlsx 文件
2. 文件内容:
3.导包如下:
poi-3.9.jar
poi-ooxml-3.9.jar
poi-scratchpad-3.9.jar
poi-ooxml-schemas-3.7.jar
xmlbeans-2.3.0.jar
4. 代码如下:
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
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.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.xssf.usermodel.XSSFWorkbook;
public class ExpressExcel {
public static void main(String[] args) {
/**
* 步骤:
* 1.取出文件,将其转化为流
* 2.根据不同的后缀名判断使用哪种excel 操作类
* 3.得到总行数,并遍历
* 4.得到每行的列数,并遍历
* 5.取出每行中的每列,并判断数据格式,转化为对应的字符串
*/
String fileName = "test.xlsx";//文件名称
String filePath = "D:/NPS/";//文件位置
File f = new File(filePath+File.separator+fileName);
InputStream in = null;
try {
in = new FileInputStream(f);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
//判断文件名后缀
String suffix = fileName.substring(fileName.indexOf(".")+1);
System.out.println(suffix);
XSSFWorkbook wbs = null;
HSSFWorkbook wbs1 = null;
Sheet sheet = null;
//根据文件后缀名判断使用poi包中的哪个类 xls 使用HSSFWorkbook , xlsx 使用 XSSFWorkbook(xls 也可以使用XSSFWorkbook)
/**
* 上传使用:HSSFWorkbook,XSSFWorkbook
* 下载使用:SXSSFWorkbook(excel 2017 支持大批量导出) ,
*/
if ("xls".equalsIgnoreCase(suffix)){
try {
wbs1 = new HSSFWorkbook(in);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
sheet = wbs1.getSheetAt(0);
} else if("xlsx".equalsIgnoreCase(suffix)){
try {
wbs = new XSSFWorkbook(in);
} catch (IOException e) {
e.printStackTrace();
}
sheet = wbs.getSheetAt(0);
}else{
System.out.println("不是excel文件格式!");
return;
}
//得到总行数
int rnum = sheet.getLastRowNum();
System.out.println("总行数:"+rnum);
for(int i = 0;i<=rnum;i++){
Row r = sheet.getRow(i);
//得到每行的列数
int cellNum = r.getLastCellNum();
System.out.println("第"+i+"行的列数:"+cellNum);
for(int j = 0;j<cellNum;j++){
String str = "";
try {
str = contentToString(r.getCell(j));
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.print(str+";");
}
System.out.println("\n");
}
}
private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
/**
* Excel 数据和java格式转换
* @param cells
* @return
* @throws UnsupportedEncodingException
*/
private static String contentToString(Cell cells) throws UnsupportedEncodingException
{
if (cells == null) {
return "";
}
// 单元格数据类型
int cellType = cells.getCellType();
String result = null;
BigDecimal c = null;
switch (cellType)
{
// 是数字类型
case HSSFCell.CELL_TYPE_NUMERIC:
// 是日期类型
if (HSSFDateUtil.isCellDateFormatted(cells)) {
Date date = HSSFDateUtil
.getJavaDate(cells.getNumericCellValue());
synchronized (sdf) {
result = sdf.format(date);
}
} else {
c = new BigDecimal(cells.getNumericCellValue()+"");
result = c.toPlainString();
/**
*如果数据格式为xxx.0 则取出小数点及后面的内容
*/
if(result.contains(".") && (result.length()-result.indexOf(".0") == 2)){
result = result.substring(0, result.indexOf(".0"));
}
}
break;
// 是字符串类型
case HSSFCell.CELL_TYPE_STRING:
result = cells.getStringCellValue();
break;
default:
result = "";
}
return result;
}
}
5.输出结果: