昨天文章的修改

package com.eastcom_sw.dsv.special.web.gde.assetsManage;

import com.eastcom_sw.common.web.BaseController;
import com.eastcom_sw.dsv.special.gde.dao.assetsManage.RegisterIndicatorsDao;
import net.sf.json.JSONObject;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**

• Description:

• Title: DataAssetsDataImportController.java

• Copyright: Copyright (c) 2019

• Company: Eastcom

• Date: 2019年4月12日 下午5:18:11

• @author tangkk
• @version 1.0
  */

@Controller
@RequestMapping(value = “/dataAssetsimportController”)
@Scope(value = “prototype”)
public class DataAssetsimportController extends BaseController {

@Autowired
private RegisterIndicatorsDao registerIndicatorsDao;

/**
 * 模版文件下载
 */
@RequestMapping(value = "/downloadAssetsData", method = RequestMethod.GET)
public void downloadData(HttpSession session, HttpServletRequest request,
                         HttpServletResponse response) throws UnsupportedEncodingException {
    request.setCharacterEncoding("UTF-8");
    String fileName = request.getParameter("filename").replace("\'", "");
    String path = request.getSession().getServletContext().getRealPath("/");
    String basePath = path + File.separator + "download" + File.separator + "assets" + File.separator;
    File file = new File(basePath + fileName);
    FileInputStream input = null;
    BufferedInputStream bis = null;
    BufferedOutputStream bos = null;
    if (fileName.length() > 0) {
        try {
            input = new FileInputStream(file);
            response.reset();
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/x-msdownload");
            String saveFileName = new String(fileName.getBytes("GBK"), "ISO8859_1");
            response.setHeader("Content-disposition", "attachment; filename=" + saveFileName);
            response.setContentLength((int) file.length());
            bis = new BufferedInputStream(new BufferedInputStream(input));
            bos = new BufferedOutputStream(response.getOutputStream());
            byte[] buff = new byte[128];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (bos != null) {
                    bos.flush();
                    bos.close();
                }
                if (input != null)
                    input.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

/**
 * @param @param filePath  Excel文件路径
 * @param @param handers   Excel列标题(数组)
 * @param @param downData  下拉框数据(数组)
 * @param @param downRows  下拉列的序号(数组,序号从0开始)
 * @return void
 * @throws
 * @Title: createExcelTemplate
 * @Description: 生成Excel导入模板
 */
private static void createExcelTemplate(String[] handers, List<String[]> downData, String[] downRows, String
        filePath, String fileName, HttpServletRequest request, HttpServletResponse response) {

    String editFileName = filePath + "\\download\\assets\\" + fileName;  //修改文件路径
    XSSFWorkbook xwb = null;
    try {
        xwb = new XSSFWorkbook(new FileInputStream(editFileName));
    } catch (IOException e) {
        e.printStackTrace();
    }

    XSSFSheet xSheet = xwb.getSheetAt(0);//获取excel表的第一个sheet
    //设置下拉框数据
    String[] arr = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R",
            "S", "T", "U", "V", "W", "X", "Y", "Z"};
    int index = 0;
    HSSFRow row = null;
    for (int r = 0; r < downRows.length; r++) {
        String[] dlData = downData.get(r);//获取下拉对象
        int rownum = Integer.parseInt(downRows[r]);

        if (dlData.length < 254) { //255以内的下拉
            //255以内的下拉,参数分别是：作用的sheet、下拉内容数组、起始行、终止行、起始列、终止列

// xSheet.addValidationData(setDataValidation(xSheet, dlData, 1, 5000, rownum, rownum)); //超过255个报错
setValidationData(xSheet, 1, 5000, rownum, rownum, dlData);
}
}

    try {
        final String pathAndName = filePath + fileName;
        File f = new File(pathAndName); //写文件

        //不存在则新增
        if (!f.getParentFile().exists()) {
            f.getParentFile().mkdirs();
        }
        if (!f.exists()) {
            f.createNewFile();
        }

        FileOutputStream out = new FileOutputStream(f);
        out.flush();

// wb.write(out);
out.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
// getExcel(filePath, fileName, response, request);
}

/**
 * @param @param  strFormula
 * @param @param  firstRow   起始行
 * @param @param  endRow     终止行
 * @param @param  firstCol   起始列
 * @param @param  endCol     终止列
 * @param @return
 * @return HSSFDataValidation
 * @throws
 * @Title: SetDataValidation
 * @Description: 下拉列表元素很多的情况 (255以上的下拉)
 */
private static HSSFDataValidation SetDataValidation(String strFormula,
                                                    int firstRow, int endRow, int firstCol, int endCol) {

    // 设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
    DVConstraint constraint = DVConstraint.createFormulaListConstraint(strFormula);
    HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);

    dataValidation.createErrorBox("Error", "Error");
    dataValidation.createPromptBox("", null);

    return dataValidation;
}


/**
 * @param @param  sheet
 * @param @param  textList
 * @param @param  firstRow
 * @param @param  endRow
 * @param @param  firstCol
 * @param @param  endCol
 * @param @return
 * @return DataValidation
 * @throws
 * @Title: setDataValidation
 * @Description: 下拉列表元素不多的情况(255以内的下拉)
 */
private static DataValidation setDataValidation(Sheet sheet, String[] textList, int firstRow, int endRow, int
        firstCol, int endCol) {
    if (sheet instanceof XSSFSheet) {
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                .createExplicitListConstraint(textList);
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
        validation.setSuppressDropDownArrow(true);
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);
    } else if (sheet instanceof HSSFSheet) {
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(textList);
        DataValidation validation = new HSSFDataValidation(addressList, dvConstraint);
        validation.setSuppressDropDownArrow(true);
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);
    }

    return null;
}

/**
 * 添加数据有效性检查.
 * @param sheet 要添加此检查的Sheet
 * @param firstRow 开始行
 * @param lastRow 结束行
 * @param firstCol 开始列
 * @param lastCol 结束列
 * @param explicitListValues 有效性检查的下拉列表
 * @throws IllegalArgumentException 如果传入的行或者列小于0(< 0)或者结束行/列比开始行/列小
 */
public static void setValidationData(Sheet sheet, int firstRow, int lastRow,
                                     int firstCol, int lastCol, String[] explicitListValues) throws
        IllegalArgumentException {
    if (firstRow < 0 || lastRow < 0 || firstCol < 0 || lastCol < 0 || lastRow < firstRow || lastCol < firstCol) {
        throw new IllegalArgumentException("Wrong Row or Column index : " + firstRow + ":" + lastRow + ":" +
                firstCol + ":" + lastCol);
    }
    if (sheet instanceof XSSFSheet) {
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                .createExplicitListConstraint(explicitListValues);
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
        validation.setSuppressDropDownArrow(true);
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);
    } else if (sheet instanceof HSSFSheet) {
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(explicitListValues);
        DataValidation validation = new HSSFDataValidation(addressList, dvConstraint);
        validation.setSuppressDropDownArrow(true);
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);
    }
}

/**
 * @param @param url 文件路径
 * @param @param fileName  文件名
 * @param @param response
 * @return void
 * @throws
 * @Title: getExcel
 * @Description: 下载指定路径的Excel文件
 */
public static void getExcel(String url, String fileName, HttpServletResponse response, HttpServletRequest request) {

    try {

        //1.设置文件ContentType类型，这样设置，会自动判断下载文件类型
        response.setContentType("multipart/form-data");

        //2.设置文件头：最后一个参数是设置下载文件名
        response.setHeader("Content-disposition", "attachment; filename=\""
                + encodeChineseDownloadFileName(request, fileName) + "\"");

// response.setHeader(“Content-Disposition”, “attachment;filename=”
// + new String(fileName.getBytes(“UTF-8”), “ISO-8859-1”) + “.xls”); //中文文件名

        //通过文件路径获得File对象
        File file = new File(url);

        FileInputStream in = new FileInputStream(file);
        //3.通过response获取OutputStream对象(out)
        OutputStream out = new BufferedOutputStream(response.getOutputStream());

        int b = 0;
        byte[] buffer = new byte[2048];
        while ((b = in.read(buffer)) != -1) {
            out.write(buffer, 0, b); //4.写到输出流(out)中
        }

        in.close();
        out.flush();
        out.close();

    } catch (IOException e) {
        e.printStackTrace();
    }
}

/**
 * @param @param  request
 * @param @param  pFileName
 * @param @return
 * @param @throws UnsupportedEncodingException
 * @return String
 * @throws
 * @Title: encodeChineseDownloadFileName
 * @Description: TODO(这里用一句话描述这个方法的作用)
 */
private static String encodeChineseDownloadFileName(HttpServletRequest request, String pFileName)
        throws UnsupportedEncodingException {

    String filename = null;
    String agent = request.getHeader("USER-AGENT");
    //System.out.println("agent==========》"+agent);

    if (null != agent) {
        if (-1 != agent.indexOf("Firefox")) {//Firefox
            filename = "=?UTF-8?B?" + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(pFileName
                    .getBytes("UTF-8")))) + "?=";
        } else if (-1 != agent.indexOf("Chrome")) {//Chrome
            filename = new String(pFileName.getBytes(), "ISO8859-1");
        } else {//IE7+
            filename = java.net.URLEncoder.encode(pFileName, "UTF-8");
            filename = StringUtils.replace(filename, "+", "%20");//替换空格
        }
    } else {
        filename = pFileName;
    }

    return filename;
}

/**
 * @param @param filePath  文件路径
 * @return void
 * @throws
 * @Title: delFile
 * @Description: 删除文件
 */
public static void delFile(String filePath) {
    java.io.File delFile = new java.io.File(filePath);
    delFile.delete();
}

/**
 * @param @param  uuid
 * @param @param  request
 * @param @param  response
 * @param @return
 * @return Data
 * @throws
 * @Title: getExcelTemplate
 * @Description: 生成Excel模板并导出
 */
@RequestMapping("/getExcelTemplate")
public void getExcelTemplate(HttpSession session, HttpServletRequest request,
                             HttpServletResponse response) {
    String fileName = request.getParameter("filename").replace("\'", "");
    String path = request.getSession().getServletContext().getRealPath("/");
    String basePath = path + File.separator + "download" + File.separator + "assets" + File.separator;
    final String pathAndName = basePath + fileName;
    String[] handers = {"模型英文名称", "模型名称", "主题域", "主题域子域", "所属分层", "数据来源", "资产责任人", "是否主数据", "资产密级", "模型介绍",
            "模型类别"}; //列标题
    List<JSONObject> timeLevelList = registerIndicatorsDao.getOption("getAllDim", "");
    String[] dim_ids = getArrayByList("DIM_NAME", "9B48B30986374483BB2445EC9E000114", "PARENT_ID", timeLevelList);
    //下拉框数据
    List<String[]> downData = new ArrayList();
    downData.add(dim_ids);

// downData.add(str2);
// downData.add(str3);
String[] downRows = {“2”}; //下拉的列序号数组(序号从0开始)

    try {

        createExcelTemplate(handers, downData, downRows, path, fileName, request, response);
        downloadData(session, request, response);
    } catch (Exception e) {
        log.error("批量导入信息异常：" + e.getMessage());
    }
}

/**
 * 根据中文名称查找对应的ID
 *
 * @param keyField
 * @param dataList
 * @return
 */
public String[] getArrayByList(String keyField, String textField, String nameFiled, List<JSONObject> dataList) {
    List getList = new ArrayList();
    for (int i = 0, len = dataList.size(); i < len; i++) {
        if (textField.equals(dataList.get(i).getString(nameFiled))) {
            getList.add(dataList.get(i).getString(keyField));
        }
    }
    String[] Strings = (String[]) getList.toArray(new String[getList.size()]);
    return Strings;
}

private static Map<String, String> getFieldMap() {
    Map<String, String> fields = new HashMap<String, String>();
    fields.put("Study Subject ID", "研究主题");
    fields.put("Protocol ID", "协议");

// try{
// String fileName = “D:\yuan.xlsx”;
// XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(fileName));
//
// XSSFSheet xSheet = xwb.getSheetAt(3);
// for (int i = 0; i <= xSheet.getLastRowNum(); i++) {
// fields.put(xSheet.getRow(i).getCell(0).toString(),xSheet.getRow(i).getCell(1).toString());
System.out.println("—"+xSheet.getRow(i).getCell(0)+"*—"+fields.get(“A1”));
// }
// }
// catch(Exception e){
// e.printStackTrace();
// }
return fields;
}

}