Excel的上传与下载

1.一般的excel下载针对后台管理的数据进行下载，也就是将查出来的list数据放到excel文件中，然后生成静态的excel，这时候当我们前端点击下载的时候，会找到该静态excel文件，浏览器会弹出是否现在的对话框，所以我们需要做的就是将查出来的list数据生成excel文件。
2.list 数据生成excel文件，贴出如下工具类。我们要使用

LinkedHashMap<String, String>  colTitle = new LinkedHashMap<String, String> ();，生成表头  colTitle.put("数据", “表头”");，我们需要导出多少列，多少个 colTitle.put（key，value），注意此处我们的key就是我们库中的表（dao）头，value才是excel表中的表头。然后需要我们生成空的excel文件， （ String fileName = System.currentTimeMillis()+".xls";（文件名） String filePath=PathManager.getInstance().getTmpPath().resolve(fileName).toString();（所处路径）           ），此时excel文件有了，表头有了，list数据有了，接着就需要我们将此填充到空excel文件中，最后调用File file = ExcelUtil.getExcelFile(lists, fileName, colTitle);就ok啦。3.excel 上传。贴出前端页面j解析js `indexApp.controller('batchDeviceDnrNetImportController', function($scope, $modalInstance, $timeout, $interval, Upload) {

/** * 上传 按钮(批量导入一级客户) */$scope.uploadXls = function(file){    var fileName = file !== null && file !==undefined ? file.name : '';//文件名    if(isBlank(fileName)){        $('#validate_messsage').empty().html('请先上传Excel文件！').show();        return;    }    var fileSuffixArray = fileName.split('.');    var fileSuffix = fileSuffixArray.length > 1 ? $.trim(fileSuffixArray[1]) : '';    if(! (fileSuffix == 'xls' || fileSuffix == 'xlsx') ){        $('#validate_messsage').empty().html('文件格式错误，请上传Excel文件！').show();        return;    }    $('#validate_messsage').empty().hide();    file.upload = Upload.upload({        url: '/opms/device/bind',        method: 'POST',        headers: {            'my-header': 'my-header-value'        },        //fields: {username: $scope.username},        file: file,        fileFormDataName: 'xlsFile'    });    file.upload.then(function (response) {            $timeout(function () {                file.progress = 100;                file.result = response.data;            });        },        function (response) {            if (response.status > 0){                $scope.errorMsg = response.status + ': ' + response.data;            }        }    );    file.upload.progress(function (evt) {        var curProgress = Math.min(100, parseInt(100.0 * evt.loaded / evt.total));        if(curProgress >= 90){            curProgress = 90;        }        file.progress = curProgress;    });    file.upload.xhr(function (xhr) {    });};$scope.uploadCancel = function(){    $modalInstance.close($scope.selected);    $scope.list();};

});`
然后在后台将excel转入后，同样用map将中文表头替换为我们需要的名称（dao或者数据库中表字段），然后将其转为list ， List lists = ExcelUtil.getExcelDataToBean(file, map)。最后我们就是对list进行操作了。

`public class ExcelUtil {

/** * 将一个EXCEL文件转化为数据集合（以表头每列为key，对应列内容为value的list集合） *  * @param file *            文件 * @return List * @throws IOException *             抛出异常 */public static List<Map<String, String>> getExcelData(File file)        throws IOException {    List<Map<String, String>> retDats = new ArrayList<Map<String, String>>();    FileInputStream finput = new FileInputStream(file);    try {        Workbook wb = getWorkBook(finput, file.getName());        Sheet sheet = wb.getSheetAt(0);        int firsRowNum = sheet.getFirstRowNum();        int lastRowNum = sheet.getLastRowNum();        Row headerRow = sheet.getRow(firsRowNum);        if(headerRow==null){            throw new IOException("excel头部数据缺失 请按模板使用");        }        List<String> header = getHeader(headerRow);        for (int i = firsRowNum + 1; i <= lastRowNum; i++) {            Row row = sheet.getRow(i);            if(row==null){                continue;            }            Map<String, String> eRow = new LinkedHashMap<String, String>();            for (int j = 0; j < header.size(); j++) {                Cell cell = row.getCell(j);                String key = (String) header.get(j);                String value = getCellValue(cell);                eRow.put(key, value);            }            retDats.add(eRow);        }    } finally {        finput.close();    }    return retDats;}public static List<Map<String, String>> getExcelDataToBean(File file, Map<String, String> colMatch) throws IOException { // 生成excel对应的初始数据    List<Map<String, String>> list = getExcelData(file);    // 如果有匹配需要替换的列名（因为EXCEL导入一般使用的是中文，导入数据库则是英文），则进行key名的替换    if (colMatch != null) {        for (int i = 0; i < list.size(); i++) {            Map<String, String> excelMap = list.get(i);            Map<String, String> dataMap = new HashMap<String, String>();            for (String key : excelMap.keySet()) {                String dataKey = colMatch.get(key);                if (dataKey != null) {                    dataMap.put(dataKey, excelMap.get(key));                }            }            list.set(i, dataMap);        }    }    // 最后将Map转化为javaBean    return list;}/** * 将excel文件转化为javaBean *  * @param file *            文件 * @param valueType *            值类型 * @param colMatch *            参数 excel列名为key，字段名为value的Map * @param <T> 对象 * @return List * @throws Exception *             抛出异常 * @throws IOException *             抛出IO异常 */public static <T> List<T> getExcelDataToBean(File file, Class<T> valueType,        Map<String, String> colMatch) throws IOException {    // 生成excel对应的初始数据    List<Map<String, String>> list = getExcelData(file);    // 如果有匹配需要替换的列名（因为EXCEL导入一般使用的是中文，导入数据库则是英文），则进行key名的替换    if (colMatch != null) {        for (int i = 0; i < list.size(); i++) {            Map<String, String> excelMap = list.get(i);            Map<String, String> dataMap = new HashMap<String, String>();            for (String key : excelMap.keySet()) {                String dataKey = colMatch.get(key);                if (dataKey != null) {                    dataMap.put(dataKey, excelMap.get(key));                }            }            list.set(i, dataMap);        }    }    // 最后将Map转化为javaBean    List<T> relists = new ArrayList<T>();    for (int i = 0; i < list.size(); i++) {        String json = JsonUtils.toJsonString(list.get(i));        Gson gson = new Gson();        T object = gson.fromJson(json, valueType);        relists.add(object);    }    return relists;}/** * 将一个Excel文件转化为Map（以对应表格位置为key，表格内容为value的Map） *  * @param file *            文件 * @return Map * @throws IOException *             抛出异常 */public static Map<String, String> getExcelMData(File file)        throws IOException {    Map<String, String> retDats = new HashMap<String, String>();    FileInputStream finput = new FileInputStream(file);    try {        Workbook wb = getWorkBook(finput, file.getName());        Sheet sheet = wb.getSheetAt(0);        int firsRowNum = sheet.getFirstRowNum();        int lastRowNum = sheet.getLastRowNum();        for (int i = firsRowNum; i <= lastRowNum; i++) {            Row row = sheet.getRow(i);            if (row != null) {                int firstCellNum = row.getFirstCellNum();                int lastCellNum = row.getLastCellNum();                for (int j = firstCellNum; j < lastCellNum; j++) {                    Cell cell = row.getCell(j);                    if (cell != null) {                        String key = getKey(cell);                        String value = getCellValue(cell);                        if ((key != null) && (key.trim().length() > 0)) {                            retDats.put(key, value);                        }                    }                }            }        }    } finally {        finput.close();    }    return retDats;}/** * 生成EXCEL文件 *  * @param obj *            实体类集合 * @param filePath *            自定义文件路径 * @param colTitle *            匹配的有序列名，可以为空 * @return File * @throws IOException *             抛出异常 */public static File getExcelFile(List<?> obj, String filePath,        LinkedHashMap<String, String> colTitle) throws IOException {    if (obj == null || obj.size() == 0) {        return null;    } else {        List<Map<String, String>> data = new ArrayList<Map<String, String>>();        for (int i = 0; i < obj.size(); i++) {            // 将object转化为Map<String,String>            data.add(convertObjToMap(obj.get(i)));        }        return getExcelFileFromMap(data, filePath, colTitle);    }}/** * 将object转化为Map<String,String> *  * @param object *            参数 * @return Map */private static Map<String, String> convertObjToMap(Object object) {    Map<String, String> map = new HashMap<String, String>();    if (object == null) {// 若为空则返回空        return null;    } else if (object instanceof Map) {// 若本身就是一个Map对象，则将Map对象的键对值都转化为字符后输出        for (Object obj : ((Map) object).keySet()) {            map.put(obj.toString(), ((Map) object).get(obj)==null?"":((Map) object).get(obj).toString());        }    } else {// 其他情况则用反射输出该对象的所有属性，以属性名（小写）为key，属性值为value        Field[] declaredFields = object.getClass().getDeclaredFields();        for (Field field : declaredFields) {            field.setAccessible(true);            Object obj;            String value = "";            try {                obj = field.get(object);                if (obj == null) {                    // 属性为空则输出空串                } else if (obj instanceof Date) {                    // 属性为时间类型则输出年月日时分秒格式的字符串                    value = com.awifi.util.DateUtil.formatToString(                            (Date) obj, "yyyy-MM-dd HH:mm:ss");                } else {                    // 其他情况下直接转化为字符串                    value = obj.toString();                }            } catch (IllegalArgumentException | IllegalAccessException e) {                e.printStackTrace();            }            map.put(field.getName().toLowerCase(), value);        }    }    return map;}/** * 生成EXCEL文件 *  * @param data *            参数 Map<String,String>对象集合 * @param filePath *            自定义文件路径 * @param colTitle *            匹配的有序列名，可以为空 * @return File * @throws IOException *             抛出异常 */public static File getExcelFileFromMap(List<Map<String, String>> data,        String filePath, LinkedHashMap<String, String> colTitle)        throws IOException {    if (data == null || data.size() == 0) {        return null;    } else if (StringUtil.isEmpty(filePath)            || !(filePath.endsWith("xls") || filePath.endsWith("xlsx"))) {        throw new IOException("传入正确的Excel文件路径");    } else {        Workbook wb = null;        if (filePath.endsWith("xls")) {            wb = new HSSFWorkbook();        } else {            wb = new XSSFWorkbook();        }        Sheet sheet = wb.createSheet("sheet1");        // 先创建表头,将表头内容居中        Row row = sheet.createRow(0);        CellStyle style = wb.createCellStyle();        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);        // 表头的列值集合        List<String> keyList = new ArrayList<String>();        int c = 0;        // 如果有匹配的列名则完全按照提供的列名键对值进行列填充，反之则用原数据        if (colTitle != null && colTitle.size() > 0) {            for (String key : colTitle.keySet()) {                keyList.add(key);                Cell cell = row.createCell(c++);                cell.setCellValue(colTitle.get(key));                cell.setCellStyle(style);            }        } else {            for (String key : data.get(0).keySet()) {                keyList.add(key);                Cell cell = row.createCell(c++);                cell.setCellValue(key);                cell.setCellStyle(style);            }        }        // 生成各行对应数据        for (int i = 0; i < data.size(); i++) {            row = sheet.createRow(i + 1);            Map<String, String> map = data.get(i);            for (int j = 0; j < keyList.size(); j++) {                row.createCell(j).setCellValue(map.get(keyList.get(j)));            }        }        // 输出Excel文件        FileOutputStream fos = new FileOutputStream(filePath);        wb.write(fos);        fos.close();        wb.close();        return new File(filePath);    }}/** * @param cell *            参数 * @return String */private static String getKey(Cell cell) {    String index = "";    int col = cell.getColumnIndex();    int row = cell.getRowIndex() + 1;    int a = 65;    int m = col % 26;    int n = col / 26;    while (n > 0) {        index = index + 'A';        n--;    }    index = index + (char) (a + m);    index = index + row;    return index;}/** * @param input *            参数 * @param fileName *            文件名 * @return Workbook * @throws IOException *             抛出IO异常 */private static Workbook getWorkBook(InputStream input, String fileName)        throws IOException {    Workbook wb = null;    if (fileName.indexOf("xlsx") >= 0) {        wb = new XSSFWorkbook(input);    } else if (fileName.indexOf("xls") >= 0) {        wb = new HSSFWorkbook(input);    } else {        throw new IllegalArgumentException("文件类型未知!" + fileName);    }    return wb;}/** * @param row *            参数 * @return List */private static List<String> getHeader(Row row) {    List<String> cells = new ArrayList<String>();    int firstCellNum = row.getFirstCellNum();    int lastCellNum = row.getLastCellNum();    for (int i = firstCellNum; i < lastCellNum; i++) {        Cell cell = row.getCell(i);        cells.add(getCellValue(cell));    }    return cells;}/** * @param cell *            参数 * @return String */private static String getCellValue(Cell cell) {    if (cell == null) {        return "";    }    switch (cell.getCellType()) {        case 1:            try {                String value = cell.getStringCellValue();                String temp = new String(Hex.encodeHex(value                        .getBytes("utf-8"))).replaceAll("c2a0", "20");                try {                    value = new String(Hex.decodeHex(temp.toCharArray()),                            "utf-8");                } catch (Exception localException1) {                }                return value.trim();            } catch (UnsupportedEncodingException e1) {                return new String(cell.getStringCellValue().getBytes());            }        case 0:            if ((DateUtil.isCellDateFormatted(cell))                    || ((cell.getCellStyle() != null) && ("yyyy\"年\"m\"月\";@"                            .equals(cell.getCellStyle()                                    .getDataFormatString())))) {                String format = cell.getCellStyle().getDataFormatString();                if (format.equals("yyyy\"年\"m\"月\";@")) {                    format = "yyyy-MM";                } else if (format.startsWith("yyyy\\-mm\\-dd")) {                    format = format.replace("yyyy\\-mm\\-dd", "yyyy-MM-dd");                } else if (format.startsWith("m/d/yy")) {                    format = format.replace("m/d/yy", "yyyy/MM/dd");                } else if (format                        .startsWith("[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy")) {                    format = format.replace(                            "[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy",                            "yyyy年MM月dd日");                } else if (format.startsWith("yyyy\\-m\\-d")) {                    format = format.replace("yyyy\\-m\\-d", "yyyy-MM-dd");                } else {                    format = "yyyy-MM-dd HH:mm:ss";                }                try {                    SimpleDateFormat sdf = new SimpleDateFormat(format);                    return sdf.format(cell.getDateCellValue());                } catch (Exception e) {                    e.printStackTrace();                    return cell.getDateCellValue().toString();                }            }            NumberFormat nf = NumberFormat.getInstance(Locale.CHINESE);            return String.valueOf(nf.format(cell.getNumericCellValue())                    .replace(",", ""));        case 4:            return String.valueOf(cell.getBooleanCellValue());        case 2:            return String.valueOf(cell.getCellFormula());        default:            return "";    }}/** * @param args *            参数 * @throws IOException *             抛出IO异常 */public static void main(String[] args) throws IOException {    List<Map<String, String>> list = new ArrayList<Map<String, String>>();    for (int i = 0; i < 10; i++) {        Map<String, String> map = new HashMap<String, String>();        map.put("a", "aaaa");        map.put("b", "bbbb");        map.put("c", "cccc");        map.put("d", "dddd");        list.add(map);    }    LinkedHashMap<String, String> colTitle = new LinkedHashMap<String, String>();    colTitle.put("a", "第一列");    colTitle.put("c", "第二列");    colTitle.put("d", "第三列");    getExcelFile(list, "E:\\1.xlsx", colTitle);}

}`