excel导入导出对一个系统来说是在正常不过的功能,但是我们一般遇到的excel导入导出都是结构性数据,很容易实现导入和导出功能。但是有时候也会遇到非结构性数据,比如一些特别的报表,每一行都是不相干的数据,也没有共同特征,遇到这种就比较棘手了。
假设有如下表格需要导入,你们可有好的解决方案?
之前想的是读取整个表格,然后按行循环,依次读取所需内容,但是这样太繁琐了,下边还有好多空格,不好定位,还容易出差错。后来想着直接拿excel中对应的地址进行读取,这样的话只需要找到自己需要的数据对应的坐标就行了,有些空格或者不要的数据还可以直接跳过,稍微能省点事,于是就决定采取这种方法,虽然不太明智,但是简单粗暴,只要模板不变,也可以一直使用。
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
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 DiyExcelImportUtil {
public static void main(String[] args) throws Exception {
String[] locationArr = {"A3","B5","A5","A1","B1","C1","A2","B2","C2","C3","D2"};
InputStream inputStream = new FileInputStream("c:/tmp/zcfz.xls");
//excel 文件自己随意创造一个吧,我就不提供了
Workbook wb = null;
//判断是哪种格式,.xls和.xlsx
boolean isXSSFWorkbook = true;
if (!(inputStream.markSupported())) {
inputStream = new PushbackInputStream(inputStream, 8);
}
if (POIFSFileSystem.hasPOIFSHeader(inputStream)) {
wb = new HSSFWorkbook(inputStream);
isXSSFWorkbook = false;
}else if (POIXMLDocument.hasOOXMLHeader(inputStream)) {
wb = new XSSFWorkbook(OPCPackage.open(inputStream));
}
for (String locationStr : locationArr) {
System.out.println(importExcelByLocation(locationStr,wb));
}
}
public static String importExcelByLocation(String location, Workbook wb){
String cellValue = "";
Sheet sheetAt = wb.getSheetAt(0);
CellReference cr = new CellReference(location);
Row row = sheetAt.getRow(cr.getRow());
//如果当前行一条数据都没有,就会取不到cell对象,因此判断一下,如果没有一条数据存在,对该行对应的地址设为空串
if(null != row) {
Cell cell = row.getCell(cr.getCol());
cellValue = getCellValueByCell(cell);
}else {
cellValue = "";
}
return cellValue;
}
//获取单元格各类型值,返回字符串类型
public static String getCellValueByCell(Cell cell) {
//判断是否为null或空串
if (cell==null || cell.toString().trim().equals("")) {
return "";
}
String cellValue = "";
int cellType=cell.getCellType();
switch (cellType) {
//数字包含一般数字和日期,如果是日期的话,不做日期格式化,会输出一个五位数的数字,因此可以根据HSSFDateUtil.isCellDateFormatted(cell)判断是否为日期格式
case Cell.CELL_TYPE_NUMERIC: // 数字
/**
* format 的值可能为以下这些
* yyyy-MM-dd----- 14
* yyyy年m月d日----- 31
* yyyy年m月--------57
* m月d日 -----------58
* HH:mm-----------20
* h时mm分 --------- 32
*/
short format = cell.getCellStyle().getDataFormat();
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = null;
if (format == 20 || format == 32) {
sdf = new SimpleDateFormat("HH:mm");
} else if (format == 14 || format == 31 || format == 57 || format == 58) {
// 处理自定义日期格式
sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
cellValue = sdf.format(date);
}else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
}
try {
cellValue = sdf.format(cell.getDateCellValue());
} catch (Exception e) {
try {
throw new Exception("exception on get date data !".concat(e.toString()));
} catch (Exception e1) {
e1.printStackTrace();
}
}finally{
sdf = null;
}
} else {
BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
cellValue = bd.setScale(2,BigDecimal.ROUND_DOWN).toPlainString();// 数值 这种用BigDecimal包装再获取plainString,可以防止获取到科学计数值
}
break;
case Cell.CELL_TYPE_STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN: // Boolean
cellValue = cell.getBooleanCellValue()+"";;
break;
case Cell.CELL_TYPE_FORMULA: // 公式
//cellValue = cell.getCellFormula();//获取公式名称,如A1+B1
//这样对于字符串cell.getStringCellValue()方法即可取得其值,如果公式生成的是数值,使用cell.getStringCellValue()方法
//会抛出IllegalStateException异常,在异常处理中使用cell.getNumericCellValue();即可。
try {
cellValue = String.valueOf(cell.getStringCellValue());
} catch (IllegalStateException e) {
//cellValue = String.valueOf(cell.getNumericCellValue());
BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
cellValue = bd.setScale(2,BigDecimal.ROUND_DOWN).toPlainString();
}
System.out.println("公式为:"+cell.getCellFormula()+",值为:"+cellValue);
break;
case Cell.CELL_TYPE_BLANK: // 空值
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR: // 故障
cellValue = "ERROR VALUE";
break;
default:
cellValue = "UNKNOW VALUE";
break;
}
return cellValue;
}
}
以上就是根据给定地址取对应的值,其中最容易出问题的就是日期类型,如果还是有问题,建议导入的时候把日期格式对应的框设为日期格式,默认好像是常规。
还有就是公式问题,需要在try-catch里取值,如果公式计算的是字符串类型,则再try块里直接调cell.getStringCellValue(),如果公式计算的结果是数值型,则需要在catch里取值cell.getNumericCellValue(),这样就能拿到公式计算的结果。
本篇的导入都是基于apache poi 实现的,有任何问题欢迎加群讨论:700637673