Java 导入 excel

Utile 包文件
//包名自己根据自己的包名写

import org.apache.poi.hssf.usermodel.;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;

public class InputExcelUtil {
/**
* 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出
* title 表格标题名
* headersName 表格属性列名数组
* headersId 表格属性列名对应的字段---你需要导出的字段名（为了更灵活控制你想要导出的字段）
* dtoList 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象
* out 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
/
public byte[] exportExcel(String title, List headersName,List headersId,
List dtoList) {
/（一）表头--标题栏/
Map<Integer, String> headersNameMap = new HashMap<>();
int key=0;
for (int i = 0; i < headersName.size(); i++) {
if (!headersName.get(i).equals(null)) {
headersNameMap.put(key, headersName.get(i));
key++;
}
}
/（二）字段/
Map<Integer, String> titleFieldMap = new HashMap<>();
int value = 0;
for (int i = 0; i < headersId.size(); i++) {
if (!headersId.get(i).equals(null)) {
titleFieldMap.put(value, headersId.get(i));
value++;
}
}
/ （三）声明一个工作薄：包括构建工作簿、表格、样式/
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(title);
sheet.setDefaultColumnWidth((short)15);
// 生成一个样式
HSSFCellStyle style = wb.createCellStyle();
HSSFRow row = sheet.createRow(0);
// style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFCell cell;
Collection c = headersNameMap.values();//拿到表格所有标题的value的集合
Iterator it = c.iterator();//表格标题的迭代器
/（四）导出数据：包括导出标题栏以及内容栏*/
//根据选择的字段生成表头
short size = 0;
while (it.hasNext()) {
cell = row.createCell(size);
cell.setCellValue(it.next().toString());
cell.setCellStyle(style);
size++;
}
//表格标题一行的字段的集合
Collection zdC = titleFieldMap.values();
Iterator labIt = dtoList.iterator();//总记录的迭代器
int zdRow =0;//列序号
while (labIt.hasNext()) {//记录的迭代器，遍历总记录
int zdCell = 0;
zdRow++;
row = sheet.createRow(zdRow);
T l = (T) labIt.next();
// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
Field[] fields = l.getClass().getDeclaredFields();//获得JavaBean全部属性
for (short i = 0; i < fields.length; i++) {//遍历属性，比对
Field field = fields[i];
String fieldName = field.getName();//属性名
Iterator zdIt = zdC.iterator();//一条字段的集合的迭代器
while (zdIt.hasNext()) {//遍历要导出的字段集合
if (zdIt.next().equals(fieldName)) {//比对JavaBean的属性名，一致就写入，不一致就丢弃
String getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);//拿到属性的get方法
Class tCls = l.getClass();//拿到JavaBean对象
try {
Method getMethod = tCls.getMethod(getMethodName,
new Class[] {});//通过JavaBean对象拿到该属性的get方法，从而进行操控
Object val = getMethod.invoke(l, new Object[] {});//操控该对象属性的get方法，从而拿到属性值
String textVal = null;
if (val!= null) {
textVal = String.valueOf(val);//转化成String
}else{
textVal = null;
}
row.createCell((short) zdCell).setCellValue(textVal);//写进excel对象
zdCell++;
} catch (SecurityException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
}
}
return wb.getBytes();
}
/**
* Excel读取 操作
*/
public static List<List> readExcel(InputStream is)
throws IOException, InvalidFormatException {
Workbook wb = null;
try {
wb = WorkbookFactory.create(is);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}

    /** 得到第一个sheet */
    Sheet sheet = wb.getSheetAt(0);
    /** 得到Excel的行数 */
    int totalRows = sheet.getPhysicalNumberOfRows();

    /** 得到Excel的列数 */
    int totalCells = 0;
    if (totalRows >= 1 && sheet.getRow(0) != null) {
        totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
    }

    List<List<String>> dataLst = new ArrayList<List<String>>();
    /** 循环Excel的行 */
    for (int r = 0; r < totalRows; r++) {
        Row row = sheet.getRow(r);
        if (row == null)
            continue;
        List<String> rowLst = new ArrayList<String>();
        /** 循环Excel的列 */
        for (int c = 0; c < totalCells; c++) {
            Cell cell = row.getCell(c);
            String cellValue = "";
            if (null != cell) {
                 /*HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
                 cellValue= hSSFDataFormatter.formatCellValue(cell);*/

                // 以下是判断数据的类型
                CellType type = cell.getCellTypeEnum();

                switch (type) {
                    case NUMERIC: // 数字
                        cellValue = cell.getNumericCellValue() + "";
                        break;
                    case STRING: // 字符串
                        cellValue = cell.getStringCellValue();
                        break;
                    case BOOLEAN: // Boolean
                        cellValue = cell.getBooleanCellValue() + "";
                        break;
                    case FORMULA: // 公式
                        try {
                            cellValue = cell.getStringCellValue();
                        } catch (IllegalStateException e) {
                            cellValue = String.valueOf(cell.getNumericCellValue());
                        }
                        break;
                   /* cellValue = cell.getCellFormula() + "";
                    break;*/
                    case BLANK: // 空值
                        cellValue = "";
                        break;
                    case _NONE: // 故障
                        cellValue = "非法字符";
                        break;
                    default:
                        cellValue = "未知类型";
                        break;
                }
            }
            rowLst.add(cellValue);
        }
        /** 保存第r行的第c列 */
        dataLst.add(rowLst);
    }
    return dataLst;
}

}

controller层 多参数
@RequestMapping(value = "/adddetail")
@ResponseBody
public Result inputs(@RequestParam(value = "file") MultipartFile file,Integer projectid, Integer evaid ,Integer assid) throws IOException, InvalidFormatException {
//读取excel表格
Result result = new Result();
try {
List<List> lists = InputExcelUtil.readExcel(file.getInputStream());
//判断集合是否为空
if (!CollectionUtils.isEmpty(lists)) {
for (int i = 6; i < lists.size()-3; i++) {
List list = lists.get(i);
Eva_project_case_detail pdc= new Eva_project_case_detail();
pdc.setProject_id(projectid);
pdc.setPro_eva_relevance_id(evaid);
pdc.setEva_ass_id(assid);
pdc.setE_content(list.get(1));
pdc.setEvolve(list.get(7));
if (i==list.size()-3){
pdc.setType(1);
}else{
pdc.setType(0);
}
int ac = epcd.addCaserDetail(pdc);
if (ac < 0) {
result.setCode("500");
result.setMsg("添加数据失败");
} else {
result.setCode("200");
result.setMsg("添加成功");
}
}
}
} catch (IOException e) {
e.printStackTrace();
}
return result;
}

只导入文件
@RequestMapping(value = "/inputsdata")
@ResponseBody
public Result inputs(@RequestParam(value = "file") MultipartFile file) throws IOException, InvalidFormatException {
//读取excel表格
Result result = new Result();
try {
List<List> lists = InputExcelUtil.readExcel(file.getInputStream());
//判断集合是否为空
if (!CollectionUtils.isEmpty(lists)) {
for (int i = 1; i < lists.size(); i++) {
List list = lists.get(i);
Person_data per = new Person_data();
// Double a = Double.parseDouble(list.get(0));
// per.setDi_name(list.get(0));
per.setS_name(list.get(0)); //对应的列
per.setSex(list.get(1));
Double b = Double.parseDouble(list.get(2));
per.setAge(b.intValue());
per.setIndustry(list.get(3));
per.setK_back(list.get(4));
per.setOrganiz(list.get(5));
DecimalFormat df = new DecimalFormat("#");
Double c = Double.parseDouble(list.get(6));
String s = df.format(c);
per.setMob(s);
int ac = apd.adddate(per);
if (ac < 0) {
result.setCode("500");
result.setMsg("添加数据失败");

                } else {
                    result.setCode("200");
                    result.setMsg("添加成功");
                }
            }
        }
    } catch (IOException e) {
        e.printStackTrace();
    }
    return result;
}