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;
}
}