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;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;
public class ExcelUtil {
private static final String EXCEL_XLS = "xls";
private static final String EXCEL_XLSX = "xlsx";
public static void main(String[] args) {
// 读取Excel文件
File file = new File("F:/data.xlsx");
try {
// 创建输入流,读取Excel
InputStream is = new FileInputStream(file.getAbsolutePath());
Workbook wb = null;
if(file.getName().endsWith(EXCEL_XLS)){ //Excel 2003
wb = new HSSFWorkbook(is);
}else if(file.getName().endsWith(EXCEL_XLSX)){ // Excel 2007/2010
wb = new XSSFWorkbook(is);
}
// 获取Sheet对象
Sheet sheet = wb.getSheetAt(0);
//写入txt
File writeName = new File("F:/20190130_001.txt"); // 相对路径,如果没有则要建立一个新的txt文件
writeName.createNewFile(); // 创建新文件,有同名的文件的话直接覆盖
FileWriter writer = new FileWriter(writeName);
BufferedWriter out = new BufferedWriter(writer);
//行数
int rowNumber = sheet.getLastRowNum();
System.out.println(rowNumber);
for (int i = 1; i < rowNumber; i++) {
String msg="";
Row row = sheet.getRow(i);
// 列数
for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
if(cell!=null){
// Object val=getValue(cell);//xls
Object val=getCellValue(cell);//xlsx
msg += val + "|";
}
}
if(i<1122){
msg += "fujiewx\r\n";
}else{
msg += "linnanwx\r\n";
}
System.out.println(i);
out.write(msg);
}
out.flush(); // 把缓存区内容压入文件
out.close();
System.out.println("执行完毕!");
}catch (Exception e){
}
}
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 = cell.getNumericCellValue();
break;
case STRING:
obj = cell.getStringCellValue();
break;
default:
break;
}
return obj;
}
public static String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null) {
return cellValue;
}
// 判断数据的类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: // 数字
//short s = cell.getCellStyle().getDataFormat();
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
SimpleDateFormat sdf = null;
// 验证short值
if (cell.getCellStyle().getDataFormat() == 14) {
sdf = new SimpleDateFormat("yyyy/MM/dd");
} else if (cell.getCellStyle().getDataFormat() == 21) {
sdf = new SimpleDateFormat("HH:mm:ss");
} else if (cell.getCellStyle().getDataFormat() == 22) {
sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
} else {
throw new RuntimeException("日期格式错误!!!");
}
Date date = cell.getDateCellValue();
cellValue = sdf.format(date);
} else if (cell.getCellStyle().getDataFormat() == 0) {//处理数值格式
cell.setCellType(Cell.CELL_TYPE_STRING);
cellValue = String.valueOf(cell.getRichStringCellValue().getString());
}
break;
case Cell.CELL_TYPE_STRING: // 字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN: // Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA: // 公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK: // 空值
cellValue = null;
break;
case Cell.CELL_TYPE_ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
}
Java读取excel工具类
最新推荐文章于 2024-04-26 20:01:05 发布