import java.io.FileInputStream;
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.List;
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 ApiBaseMethod {
private static final String EXTENSION_XLS = "xls";
private static final String EXTENSION_XLSX = "xlsx";
/***
* 根据不同版本(03,07)得到workbook
*/
public Workbook getWorkbook(String filePath) throws IOException {
Workbook workbook = null;
InputStream is = new FileInputStream(filePath);
if (filePath.endsWith(EXTENSION_XLS)) {
workbook = new HSSFWorkbook(is); // 03
} else if (filePath.endsWith(EXTENSION_XLSX)) {
workbook = new XSSFWorkbook(is); // 07版本
}
return workbook;
}
public static void main(String[] args) {
System.out.println(Cell.CELL_TYPE_BLANK);
System.out.println(Cell.CELL_TYPE_BOOLEAN);
System.out.println(Cell.CELL_TYPE_ERROR);
System.out.println(Cell.CELL_TYPE_FORMULA);
System.out.println(Cell.CELL_TYPE_NUMERIC);
System.out.println(Cell.CELL_TYPE_STRING);
}
/**
* 获取cell格内容
*/
public String getCellValue(Cell cell) {
if(cell==null){
return null;
}
String value = null;
// 简单的查检列类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING: // 字符串
value = cell.getRichStringCellValue().getString();
break;
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
if (HSSFDateUtil.isCellDateFormatted(cell)) {//时间格式
Date d = cell.getDateCellValue();
DateFormat df2 = new SimpleDateFormat("yyyy-MM-dd");// HH:mm:ss
value = df2.format(d);
}else{
value = (long) cell.getNumericCellValue() + "";
}
break;
}
return value;
}
public Double getCellDoubleValue(Cell cell) {
Double value = null;
// 简单的查检列类型
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
value = (Double) cell.getNumericCellValue();
break;
}
return value;
}
// 休眠时间
public void sleep(int time, String notice) {
System.out.println("睡眠" + (time / 1000) + "S(" + getCurrentTime("MM/dd HH:mm:ss") + ")");
if (notice != null && !"".equals(notice.trim())) {
System.out.println(notice);
}
System.out.print("0s>>");
for (int i = 0; i < time / 20000; i++) {
try {
Thread.sleep(20000);
} catch (InterruptedException e) {
e.printStackTrace();
}
System.out.print((i + 1) * 20 + "s>>");
}
}
public String getCurrentTime(String format) {
SimpleDateFormat sdf = new SimpleDateFormat(format);
return sdf.format(System.currentTimeMillis());
}
// 参数:读取几列
int flag = 4;
private List<String> getCompanyList(String filePath) {
SimpleDateFormat sim = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
List<String> list = new ArrayList<String>();
Workbook workbook;
try {
workbook = getWorkbook(filePath);
Sheet sheet = workbook.getSheetAt(0);
int rowInt = 1;
int cellsNum = sheet.getPhysicalNumberOfRows();
// 判断有多少行, 循环几次
for (int j = rowInt; j < cellsNum; j++) {
Row row = sheet.getRow(j);
Cell cellStart = row.getCell(0);
String cellValue = getCellValue(cellStart);
String cellTwo2 = null;
String cellTwo3 = null;
String cellTwo4 = null;
if (cellValue == null) {
continue;
}
if (flag == 2) {
Cell cellTwo = row.getCell(1);
cellTwo2 = getCellValue(cellTwo);
} else if (flag == 3) {
Cell cellTwo = row.getCell(1);
cellTwo2 = getCellValue(cellTwo);
Cell cellThree = row.getCell(2);
cellTwo3 = getCellValue(cellThree);
} else if (flag == 4) {
Cell cellTwo = row.getCell(1);
cellTwo2 = getCellValue(cellTwo);
Cell cellThree = row.getCell(2);
cellTwo3 = getCellValue(cellThree);
Cell cellfour = row.getCell(3);
cellTwo4 = getCellValue(cellfour);
}
if (cellValue != null && cellValue.trim().length() > 0) {
if (flag == 4) {
list.add(cellValue.trim() + "@" + cellTwo2.trim() + "@" + cellTwo3.trim() + "@"
+ cellTwo4.trim());
} else if (flag == 3) {
list.add(cellValue.trim() + "@" + cellTwo2.trim() + "@" + cellTwo3.trim());
} else if (flag == 2) {
list.add(cellValue.trim().toLowerCase() + "@" + cellTwo2.trim());
} else if (flag == 1) {
list.add(cellValue);
}
}
continue;
}
} catch (Exception e) {
e.printStackTrace();
sleep(500000, "获取excel公司名单出错!");
}
if (list.size() == 0) {
sleep(500000, "数据集为空!");
} else {
System.out.println("获取公司名单共:" + list.size() + " 条数据");
}
return list;
}
}
读取excel
最新推荐文章于 2023-12-15 22:03:32 发布