package cn.edu.hezeu.jsj.utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.commons.io.FileUtils;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtil {
public static void main(String[] args) throws FileNotFoundException{
File file = new File("d:\\test.xlsx");
readExcelOfTheXlsx(file);
System.out.println("------------------------------------");
InputStream fileInputStream = new FileInputStream("d:\\xx.xls");
readExcelOfTheXls(fileInputStream);
}
/**
* 该方法只能读取xlsx格式的excel文件
*/
public static void readExcelOfTheXlsx(File file) {
try {
//读取Excel表格
XSSFWorkbook xwb = new XSSFWorkbook(FileUtils.openInputStream(file));
//获取sheet的数目，并遍历所有的sheet
for(int st=0;st<xwb.getNumberOfSheets();st++){
XSSFSheet sheet = xwb.getSheetAt(st);//获取一个sheet
XSSFRow row = null;
int lastRowNum = sheet.getLastRowNum();//获取行数
//System.out.print(lastRowNum+"=lastRowNum");
//循环读取每一行
for(int i = 0; i <= lastRowNum; i++){
row = sheet.getRow(i);
if(row != null){
//获取一行中每一列的值
for(int j = 0; j< row.getLastCellNum();j++){
XSSFCell cell = row.getCell(j);
Object value = getCellFormatValue(cell);
if(!"".equals(value)){
System.out.print(value+"|");
}
}
}
System.out.println();
}
}
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 该方法只能读取xls格式的excel文件
*/
public static void readExcelOfTheXls(InputStream fileInputStream) {
try {
//读取Excel表格
Workbook wb = new HSSFWorkbook(fileInputStream);
//获取sheet的数目，并遍历所有的sheet
for(int st=0;st<wb.getNumberOfSheets();st++){
Sheet sheet = wb.getSheetAt(st);//获取一个sheet
Row row = null;
int lastRowNum = sheet.getLastRowNum();//获取行数
//System.out.print(lastRowNum+"=lastRowNum");
//循环读取每一行
for(int i = 0; i <= lastRowNum; i++){
row = sheet.getRow(i);
if(row != null){
//获取一行中每一列的值
for(int j = 0; j< row.getLastCellNum();j++){
Cell cell = row.getCell(j);
Object value = getCellFormatValue(cell);
if(!"".equals(value)){
System.out.print(value+"|");
}
}
}
System.out.println();
}
}
} catch (IOException e) {
e.printStackTrace();
}
}
public static Object getCellFormatValue(Cell cell){
Object cellValue = null;
switch(cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC://数值型的，分成两种了，一种是纯数值型的，一种是日期型的
cellValue = getCellValueOfDate(cell);//对日期型进行了特殊处理
break;
case Cell.CELL_TYPE_STRING://字符串的
cellValue = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_BOOLEAN://布尔型的
cell.setCellType(Cell.CELL_TYPE_STRING);
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BLANK://空值
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR://故障
cellValue = "";
break;
case Cell.CELL_TYPE_FORMULA://公式
cellValue = cell.getCellFormula();
break;
default:
cellValue = "";
break;
}
return cellValue;
}
//若单元格是日期型的，进行如下处理
public static String getCellValueOfDate(Cell cell){
short format = cell.getCellStyle().getDataFormat();
//System.out.println("format:"+format+";;;;;value:"+cell.getNumericCellValue());
SimpleDateFormat sdf = null;
if (format == 14 || format == 31 || format == 57 || format == 58
|| (176<=format && format<=178) || (182<=format && format<=196)
|| (210<=format && format<=213) || (208==format ) ) { // 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
} else if (format == 20 || format == 32 || format==183 || (200<=format && format<=209) ) { // 时间
sdf = new SimpleDateFormat("HH:mm");
} else { // 不是日期格式
//当读取的Excel数据不只是String类型时，需要使用如下读取方式
cell.setCellType(Cell.CELL_TYPE_STRING);//这种方式可以去掉整数中的.0
return cell.getStringCellValue();
}
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
if(date==null || "".equals(date)){
return "";
}
String cellValue="";
try {
cellValue = sdf.format(date);
} catch (Exception e) {
e.printStackTrace();
return "";
}
return cellValue;
}
}
评论0