package com.cfets.ts.s.user.rest; import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map; import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod; import com.cfets.cwap.s.spi.GenericRest;
import com.cfets.cwap.s.stp.SimpleMessage;
import com.cfets.cwap.s.util.PubString;
import com.cfets.ts.s.user.util.ExcelUtil; public class PlatFormRest extends GenericRest {
@SuppressWarnings("all")
@RequestMapping(value="/checkCommit",method={RequestMethod.POST,RequestMethod.GET})
public SimpleMessage<?> checkCommit(HttpServletRequest request,
HttpServletResponse response,@RequestBody String json) {
SimpleMessage<?> msg;
msg = SimpleMessage.blank(); try {
InputStream fi;
String fileName = request.getParameter("fileName");
File file = new File(fileName);
fi = new FileInputStream(file); boolean isExcel2003 = fileName.toLowerCase().endsWith("xls") ? true
: false;
Workbook workBook = null;
// 标识整个excel//区分xls或者是else
if (isExcel2003) {
workBook = new HSSFWorkbook(fi);
} else {
workBook = new XSSFWorkbook(fi);
} fi.close(); Sheet sheet = workBook.getSheetAt(3);
Row versionRow = sheet.getRow(1);
Cell versionCell = versionRow.getCell(3);
String versionCellValue = versionCell.getStringCellValue();
String version = versionCellValue.substring(versionCellValue.indexOf("V")); List<String[]> list = new ArrayList<String[]>();
// 标识某一页
int [] param = {2,3,6,7,8,9,10,11,12,13,14,15,16}; int lastRowNum = sheet.getLastRowNum();
for (int rowIndex = 6; rowIndex <= lastRowNum; rowIndex++){
Row row = sheet.getRow(rowIndex);
if (null == row) {
continue;
}
//List<String[]> list = new ArrayList<String[]>();
Cell fCell = row.getCell(2);
String [] str = new String[param.length];
for (Cell c : row) {
String returnStr = "";
boolean isMerge = ExcelUtil.isMergedRegion(sheet, rowIndex, c.getColumnIndex());
if (isMerge) { returnStr = ExcelUtil.getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
}else {
if (PubString.isNullOrSpace(ExcelUtil.getCellValue(c))) {
returnStr = "XXX";
}else{ returnStr = ExcelUtil.getCellValue(c);
}
}
/*if (PubString.isNullOrSpace(returnStr)) {
continue;
}*/
if (c.getColumnIndex() == 2) {
str[0] = returnStr;
}else if (c.getColumnIndex() ==3) {
str[1] = returnStr;
}else if (c.getColumnIndex() ==6) {
str[2] = returnStr;
}else if (c.getColumnIndex() ==7) {
str[3] = returnStr;
}else if (c.getColumnIndex() ==8) {
str[4] = returnStr;
}else if (c.getColumnIndex() ==9) {
str[5] = returnStr;
}else if (c.getColumnIndex() ==10) {
str[6] = returnStr;
}else if (c.getColumnIndex() ==11) {
str[7] = returnStr;
}else if (c.getColumnIndex() ==12) {
str[8] = returnStr;
}else if (c.getColumnIndex() ==13) {
str[9] = returnStr;
}else if (c.getColumnIndex() ==14) {
str[10] = returnStr;
}else if (c.getColumnIndex() ==15) {
str[11] = returnStr;
}else if (c.getColumnIndex() ==16) {
str[12] = returnStr;
}
}
list.add(str); /*String [] str = new String[param.length];
//String stringCellValue = fCell.getStringCellValue();
Cell pCell = row.getCell(12);
if (null !=fCell && fCell.getStringCellValue().startsWith("XQTM")) { for (int i = 0; i < param.length; i++) {
Cell cell = row.getCell(param[i]);
if (null != cell) { str[i] =ExcelUtil.getMergedRegionValue(sheet, row.getRowNum(), cell.getColumnIndex()); }else{
str[i] ="";
}
}
map.put(fCell.getStringCellValue(),str);
}*/ } ComponentServiceImpl service = new ComponentServiceImpl();
//service.saveOrUpdateExcel1(map,version);
service.saveOrUpdateExcel2(list, version); } catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} return msg; } }
package com.cfets.ts.s.user.util; import java.io.IOException;
import java.util.ArrayList;
import java.util.List; 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.ss.util.CellRangeAddress; public class ExcelUtil { /**
* @author wcyong
* @date 2013-6-21
*/
/* public static Workbook getWorkbook(InputStream is, ExcelFormat format)
throws IOException {
Workbook wb = null;
if (format == ExcelFormat.xls) {// HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls
wb = new HSSFWorkbook(is);
} else if (format == ExcelFormat.xlsx) {// XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx
wb = new XSSFWorkbook(is);
}
return wb;
} */ /**
* 获取合并单元格的值
*
* @param sheet
* @param row
* @param column
* @return
*/
public static String getMergedRegionValue(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow(); if (row >= firstRow && row <= lastRow) { if (column >= firstColumn && column <= lastColumn) {
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
return getCellValue(fCell);
}
}
} return null;
} /**
* 如果excel是wps格式，获取合并单元格的cell时，cell会是null，此时不能用该方法，请用getMergedRegionValue(Sheet sheet, int row, int column)
* @description
* @author liuzhenpeng
* @date 2017年2月16日
* @param sheet
* @param cell
* @return
*/
public static String getMergedRegionValue(Sheet sheet, Cell cell) {
return getMergedRegionValue(sheet, cell.getRowIndex(),
cell.getColumnIndex());
} /**
* 判断合并了行
*
* @param sheet
* @param row
* @param column
* @return
*/
public static boolean isMergedRow(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row == firstRow && row == lastRow) {
if (column >= firstColumn && column <= lastColumn) {
return true;
}
}
}
return false;
} /**
* 判断指定的单元格是否是合并单元格
*
* @param sheet
* 工作表
* @param row
* 行下标
* @param column
* 列下标
* @return
*/
public static boolean isMergedRegion(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
return true;
}
}
}
return false;
} /**
* 如果excel是wps格式，获取合并单元格的cell时，cell会是null，此时不能用该方法，请用isMergedRegion(Sheet sheet, int row, int column)
* @description
* @author liuzhenpeng
* @date 2017年2月16日
* @param sheet
* @param cell
* @return
*/
public static boolean isMergedRegion(Sheet sheet, Cell cell) {
int row = cell.getRowIndex();
int column = cell.getColumnIndex();
return isMergedRegion(sheet, row, column);
}
/*
public static boolean isCellInRegion(int rowIndex, int colIndex,
Region region) {
if (rowIndex >= region.getFirstRow() && rowIndex <= region.getLastRow()) {
if (colIndex >= region.getFirstColumn()
&& colIndex <= region.getLastColumn()) {
return true;
}
}
return false;
} */ /* public static boolean isCellInRegion(Cell cell, Region region) {
return isCellInRegion(cell.getRowIndex(), cell.getColumnIndex(), region);
} */ /* public static Region getMergedRegion(Sheet sheet, int rowIndex, int colIndex) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (rowIndex >= firstRow && rowIndex <= lastRow) {
if (colIndex >= firstColumn && colIndex <= lastColumn) {
Region region = new Region();
region.setFirstRow(firstRow);
region.setLastRow(lastRow);
region.setFirstColumn(firstColumn);
region.setLastColumn(lastColumn);
return region;
}
}
}
return null;
} */
/*
public static Region getMergedRegion(Sheet sheet, Cell cell) {
return getMergedRegion(sheet, cell.getRowIndex(), cell.getColumnIndex());
} */ /**
* 判断sheet页中是否含有合并单元格
*
* @param sheet
* @return
*/
public static boolean hasMerged(Sheet sheet) {
return sheet.getNumMergedRegions() > 0 ? true : false;
} /**
* 合并单元格
*
* @param sheet
* @param firstRow
* 开始行
* @param lastRow
* 结束行
* @param firstCol
* 开始列
* @param lastCol
* 结束列
*/
public static void mergeRegion(Sheet sheet, int firstRow, int lastRow,
int firstCol, int lastCol) {
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol,
lastCol));
} /**
* 获取单元格的值
*
* @param cell
* @return
*/
public static String getCellValue(Cell cell) { if (cell == null)
return ""; if (cell.getCellType() == Cell.CELL_TYPE_STRING) { return cell.getStringCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { return String.valueOf(cell.getBooleanCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { return cell.getCellFormula(); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
return String.valueOf(cell.getNumericCellValue());
}
return "";
}
/*
public static ExcelInputStreamDto getUploadExcelInputStream(
HttpServletRequest request, Long maxExcelFileSize)
throws IOException {
String[] allowExtensions = { ".et", ".ett", ".xls", ".xlsx" };
CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(
request.getSession().getServletContext());
// 判断 request 是否有文件上传,即多部分请求
if (multipartResolver.isMultipart(request)) {
// 转换成多部分request
MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request; MultipartFile file = multiRequest.getFile("import"); if (file == null || file.getSize() == 0) {
throw new ServiceException("文件不存在");
} if (maxExcelFileSize != null && file.getSize() > maxExcelFileSize) {
throw new ServiceException("文件过大");
}
boolean extMatch = false;
ExcelFormat excelFormat = null;
for (String ext : allowExtensions) {
if (file.getOriginalFilename().endsWith(ext)) {
extMatch = true;
if (".xls".equalsIgnoreCase(ext)) {
excelFormat = ExcelFormat.xls;
} else if (".xlsx".equalsIgnoreCase(ext)) {
excelFormat = ExcelFormat.xlsx;
} else if (".et".equalsIgnoreCase(ext)
|| ".ett".equalsIgnoreCase(ext)) {
// WPS的et和ett格式可能内部是xls，也可能是xlsx，只能通过读取文件头判断
if (file.getSize() < 2) {
// 如果文件小于2字节，无法判断文件头，则直接返回格式不正确
throw new ServiceException("不正确的文件格式");
}
byte[] fileHeaderBytes = new byte[2];
InputStream is = file.getInputStream();
is.read(fileHeaderBytes, 0, 2);
String fileHeaderHex = GetTypeByHead
.bytesToHexString(fileHeaderBytes);
if ("504B".equalsIgnoreCase(fileHeaderHex)) {
excelFormat = ExcelFormat.xlsx;
} else if ("D0CF".equalsIgnoreCase(fileHeaderHex)) {
excelFormat = ExcelFormat.xls;
}
} else {
throw new ServiceException("不正确的文件格式");
}
break;
}
}
if (!extMatch) {
throw new ServiceException("不正确的文件格式");
}
ExcelInputStreamDto result = new ExcelInputStreamDto();
result.setExcelFormat(excelFormat);
result.setInputStream(file.getInputStream());
return result;
}
throw new ServiceException("不正确的请求");
} */ /**
* 判断Row(行)是否为空行(行本身为null或行中的单元格全部为null)
* @param row
* @return
*/
public static boolean isEmptyRow(Row row) {
if (row != null) {
short lastCellNum = row.getLastCellNum();
if (lastCellNum == 0) {// 如果不存在单元格则返回true
return true;
} else {
// 空单元格的个数
int emptyCellNum = 0;
for (int i = 0; i < lastCellNum; i++) {
Cell cell = row.getCell(i);
if (isEmptyCell(cell)) {
emptyCellNum++;
}
}
if (emptyCellNum == lastCellNum) {
return true;
}
}
} else {
return true;
}
return false;
} /**
* 判断Row(行)是否存在空的单元格或者这行是否存在单元格
* @param row
* @return
*/
public static boolean rowContianEmptyCell(Row row) {
if (row != null) {
short lastCellNum = row.getLastCellNum();
if (lastCellNum == 0) {// 如果不存在单元格则返回true
return true;
} else {
for (int i = 0; i < lastCellNum; i++) {
Cell cell = row.getCell(i);
if (isEmptyCell(cell)) {
return true;
}
}
}
} else {
return true;
}
return false;
} /**
* 判断Sheet是否存在空的行或存在空数据的行
* @param sheet
* @return
*/
public static boolean sheetContainEmptyRow(Sheet sheet) {
if (sheet != null) {
int lastRowNum = sheet.getLastRowNum();
if (lastRowNum == 0) {// 如果不存在sheet则返回true
return true;
} else {
for (int i = 0; i < lastRowNum; i++) {
Row row = sheet.getRow(i);
if (isEmptyRow(row)) {
return true;
}
}
}
} else {
return true;
}
return false;
} /**
* 基于指定列数判断Sheet是否存在空的行或存在空数据的行
* @param sheet
* @param columnNum
* @return
*/
public static boolean sheetContainEmptyRow(Sheet sheet, int columnNum) {
if (sheet != null) {
int lastRowNum = sheet.getLastRowNum();
if (lastRowNum == 0) {// 如果不存在sheet则返回true
return true;
} else {
if (lastRowNum >= columnNum) {
for (int i = 0; i < columnNum; i++) {
Row row = sheet.getRow(i);
if (isEmptyRow(row)) {
return true;
}
}
}else{
return true;
}
}
} else {
return true;
}
return false;
}
/**
* 获取表格中空行的行号
* @param sheet
* @return
*/
public static List<Integer> getEmptyRowNos(Sheet sheet){
List<Integer> list=new ArrayList<>();
if (sheet != null) {
int lastRowNum = sheet.getLastRowNum();
if (lastRowNum != 0) {// 如果不存在sheet则返回true
for (int i = 0; i < lastRowNum; i++) {
Row row = sheet.getRow(i);
if (isEmptyRow(row)) {
list.add(i);
}
}
}
}
return list;
} /**
* 判断Cell(单元格)是否为空
*
* @param cell
* @return
*/
public static boolean isEmptyCell(Cell cell) {
String cellContent = getCellValue(cell);
if(cellContent!=null){
return false;
} else{
return true;
}
} /**
* 关闭workbook
*
* @param workbook
*/
public static void closeWorkbook(Workbook workbook) {
if (workbook != null) {
try {
workbook.close();
} catch (IOException e) {
}
}
} public static void addDataToRow(Row row,List<String> values){
if(values!=null && !values.isEmpty()){
for (int i=0;i<values.size();i++) {
Cell cell=row.createCell(i);
cell.setCellValue(values.get(i));
}
}
}
} if(tempName.equals(CommonEnumUtil.MIDDLE_PLAT_AUTH_TEMPLET.getVal())){
for (TreeData treeData : usrOrgWdgt) {
if(GRIPInstnUserAuthService.AUTH_WHBB.equalsIgnoreCase(treeData.getId())
||GRIPInstnUserAuthService.AUTH_ZJJYBB.equalsIgnoreCase(treeData.getId())){
treeData.setChecked(true);
}
} }