先导入包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
导入两个包后可能会出现有三个maven的jar包依赖报错,此时需要记录下报错的包,然后再maven中央仓库中搜索对应的三个包,将jar包下载到本地,可以用eclipse手动导入到本地maven中,导入方式 ,在eclipse左侧,Inport >maven>Install or deploy an artifact to maven install >next >填上对应信息,版本号,打包方式jar
package com.zhongan.xd.upreport.utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
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;
import com.google.common.collect.Lists;
import com.zhongan.xd.upreport.domain.ExcelDataDO;
/**
* Description: Excel操作
*/
public class ExcelUtil {
private static final String EXCEL_XLS = "xls";
private static final String EXCEL_XLSX = "xlsx";
/**
* 判断Excel的版本,获取Workbook
*
* @param in
* @param filename
* @return
* @throws IOException
*/
public static Workbook getWorkbok(InputStream in, File file) throws IOException {
Workbook wb = null;
if (file.getName().endsWith(EXCEL_XLS)) { //Excel 2003
wb = new HSSFWorkbook(in);
} else if (file.getName().endsWith(EXCEL_XLSX)) { // Excel 2007/2010
wb = new XSSFWorkbook(in);
}
return wb;
}
/**
* 判断文件是否是excel
*
* @throws Exception
*/
public static void checkExcelVaild(File file) throws Exception {
if (!file.exists()) {
throw new Exception("文件不存在");
}
if (!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))) {
throw new Exception("文件不是Excel");
}
}
/**
* 读取Excel测试,兼容 Excel 2003/2007/2010
*
* @throws Exception
*/
public static List<ExcelDataDO> readexcel(File excelFilea) throws Exception {
List<ExcelDataDO> list = Lists.newArrayList();
try {
// 同时支持Excel 2003、2007
File excelFile = excelFilea; // 创建文件对象
FileInputStream in = new FileInputStream(excelFile); // 文件流
checkExcelVaild(excelFile);
Workbook workbook = getWorkbok(in, excelFile);
//Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel2003/2007/2010都是可以处理的
int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量
/**
* 设置当前excel中sheet的下标:0开始
*/
Sheet sheet = workbook.getSheetAt(0); // 遍历第一个Sheet
// Sheet sheet = workbook.getSheetAt(2); // 遍历第三个Sheet
//获取总行数
System.out.println("总行数=" + sheet.getLastRowNum());
// i为从第几行开始读取 0为文档第一行
for (int i = 3; i < sheet.getLastRowNum() - 1; i++) {
Row row = sheet.getRow(i);
// for (Row row : sheet) {
try {
// 跳过第一和第二行的目录
//如果当前行没有数据,继续下一行
if (row.getCell(0).toString().equals("")) {
continue;
}
//获取总列数(空格的不计算)
int columnTotalNum = row.getPhysicalNumberOfCells();
System.out.println("总列数:" + columnTotalNum);
System.out.println("最大列数:" + row.getLastCellNum());
//for循环的,不扫描空格的列
// for (Cell cell : row) {
// System.out.println(cell);
// }
int end = row.getLastCellNum();
for (int i1 = 0; i1 < end; i1++) {
//0为第一列
Cell cell = row.getCell(i1);
Object obj = getValue(cell);
System.out.println(i1 + "列的数据为" + obj);
}
} catch (Exception e) {
e.printStackTrace();
}
}
// }
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
private static Object getValue(Cell cell) {
Object obj = null;
switch (cell.getCellTypeEnum()) {
case BOOLEAN:
obj = cell.getBooleanCellValue();
break;
case ERROR:
obj = cell.getErrorCellValue();
break;
case NUMERIC:
obj = String.valueOf(cell.getNumericCellValue());
// obj = (int) cell.getNumericCellValue();
break;
case STRING:
obj = cell.getStringCellValue();
break;
default:
break;
}
return obj;
}
public Object dosome(Cell cell) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
System.out.print(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
System.out.print(cell.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
System.out.println(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
System.out.print(cell.getCellFormula());
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
System.out.println(" ");
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
System.out.println(" ");
break;
default:
System.out.print("未知类型 ");
break;
}
return null;
}
public static void main(String[] args) throws ParseException {
String date = "20180925";
String y = date.substring(0, 4);
String m = date.substring(4, 6);
String d = date.substring(6, 8);
System.out.println(y + "-" + m + "-" + d);
}
}