import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.Name;
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.CellRangeAddressList;
/**
* POI生成联动下拉工具类
public class POIToExcelUtil {
/* 隐藏的Sheet页 */
public static String EXCEL_HIDE_SHEET_NAME = "excelhidesheetname";
/**
* 创建隐藏Sheet中的行和名称集合
* @param workbook
* @param map
* @param rowIndex
* @param hideInfoSheet
*/
public static void createBatchRowAndNameList(Workbook workbook,Map<String,List<String>> map,int rowIndex,Sheet hideInfoSheet) {
for (String name : map.keySet()) {
Row largeRegionWithSonRow = hideInfoSheet.createRow(rowIndex);
List<String> temp = map.get(name);
POIToExcelUtil.creatRow(largeRegionWithSonRow,temp);
POIToExcelUtil.creatExcelNameList(workbook,temp.get(0), rowIndex+1, temp.size() -1, true);
rowIndex++;
}
}
/**
* 创建一行数据
* @param currentRow
* @param textList
*/
public static void creatRow(Row currentRow, List<String> textList) {
if (textList != null && textList.size() > 0) {
int i = 0;
for (String cellValue : textList) {
Cell userNameLableCell = currentRow.createCell(i++);
userNameLableCell.setCellValue(cellValue);
}
}
}
/**
* 创建一个名称
* @param workbook 工作薄
* @param nameCode 名称
* @param order
* @param size 长度
* @param cascadeFlag 是否联动
*/
public static void creatExcelNameList(Workbook workbook, String nameCode,
int order, int size, boolean cascadeFlag) {
//名称不能以数字开头,Excel规定的。(名称里不能包含冒号，逗号，连接线）
if(isNumeric(nameCode)) {
nameCode = "_" + nameCode;
}
Name name = null;
name = workbook.createName();
name.setNameName(nameCode);
name.setRefersToFormula(EXCEL_HIDE_SHEET_NAME + "!"
+ creatExcelNameList(order, size, cascadeFlag));
}
/**
* 验证字符串是否以数字开头
* @param str
* @return
*/
public static boolean isNumeric(String str) {
Pattern pattern = Pattern.compile("[0-9]*");
Matcher isNum = pattern.matcher(str.charAt(0)+"");
if (!isNum.matches()) {
return false;
}
return true;
}
/**
* 名称数据行列计算表达式
* @param order
* @param size
* @param cascadeFlag
* 是否联动
* @return
*/
public static String creatExcelNameList(int order, int size,
boolean cascadeFlag) {
char start = 'A';
if (cascadeFlag) {
start = 'B';
if (size <= 25) {
char end = (char) (start + size - 1);
return "$" + start + "$" + order + ":$" + end + "$" + order;
} else {
char endPrefix = 'A';
char endSuffix = 'A';
if ((size - 25) / 26 == 0 || size == 51) {// 26-51之间，包括边界（仅两次字母表计算）
if ((size - 25) % 26 == 0) {// 边界值
endSuffix = (char) ('A' + 25);
} else {
endSuffix = (char) ('A' + (size - 25) % 26 - 1);
}
} else {// 51以上
if ((size - 25) % 26 == 0) {
endSuffix = (char) ('A' + 25);
endPrefix = (char) (endPrefix + (size - 25) / 26 - 1);
} else {
endSuffix = (char) ('A' + (size - 25) % 26 - 1);
endPrefix = (char) (endPrefix + (size - 25) / 26);
}
}
return "$" + start + "$" + order + ":$" + endPrefix + endSuffix
+ "$" + order;
}
} else {
if (size <= 26) {
char end = (char) (start + size - 1);
return "$" + start + "$" + order + ":$" + end + "$" + order;
} else {
char endPrefix = 'A';
char endSuffix = 'A';
if (size % 26 == 0) {
endSuffix = (char) ('A' + 25);
if (size > 52 && size / 26 > 0) {
endPrefix = (char) (endPrefix + size / 26 - 2);
}
} else {
endSuffix = (char) ('A' + size % 26 - 1);
if (size > 52 && size / 26 > 0) {
endPrefix = (char) (endPrefix + size / 26 - 1);
}
}
return "$" + start + "$" + order + ":$" + endPrefix + endSuffix
+ "$" + order;
}
}
}
/**
* 使用已定义的数据源方式设置一个数据验证
* @param formulaString
* @param naturalRowIndex
* @param naturalColumnIndex
* @return
*/
public static DataValidation getDataValidationByFormula(
String formulaString, int firstRow, int lastRow,int firstCol,int lastCol) {
// 加载下拉列表内容
DVConstraint constraint = DVConstraint
.createFormulaListConstraint(formulaString);
// 设置数据有效性加载在哪个单元格上。
// 四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow,
lastRow, firstCol, lastCol);
// 数据有效性对象
DataValidation data_validation_list = new HSSFDataValidation(regions,
constraint);
// 设置输入信息提示信息
data_validation_list.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");
// 设置输入错误提示信息
data_validation_list
.createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的值！");
return data_validation_list;
}
/**
* 使用已定义的数据源方式设置一个数据验证
* @param formulaString
* @param naturalRowIndex
* @param naturalColumnIndex
* @return
*/
public static DataValidation getDataValidationByFormula(
String formulaString, int naturalRowIndex, int naturalColumnIndex) {
// 加载下拉列表内容
DVConstraint constraint = DVConstraint
.createFormulaListConstraint(formulaString);
// 设置数据有效性加载在哪个单元格上。
// 四个参数分别是：起始行、终止行、起始列、终止列
int firstRow = naturalRowIndex - 1;
int lastRow = naturalRowIndex - 1;
int firstCol = naturalColumnIndex - 1;
int lastCol = naturalColumnIndex - 1;
CellRangeAddressList regions = new CellRangeAddressList(firstRow,
lastRow, firstCol, lastCol);
// 数据有效性对象
DataValidation data_validation_list = new HSSFDataValidation(regions,
constraint);
// // 设置输入信息提示信息
// data_validation_list.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");
// // 设置输入错误提示信息
// data_validation_list
// .createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的值！");
// data_validation_list.setSuppressDropDownArrow(true);
// data_validation_list.setShowErrorBox(true);
return data_validation_list;
}
/**
* 日期有效期验证
* @param naturalRowIndex
* @param naturalColumnIndex
* @return
*/
public static DataValidation getDataValidationByDate(int naturalRowIndex,
int naturalColumnIndex) {
// 加载下拉列表内容
DVConstraint constraint = DVConstraint.createDateConstraint(
DVConstraint.OperatorType.BETWEEN, "1900-01-01", "5000-01-01",
"yyyy-mm-dd");
// 设置数据有效性加载在哪个单元格上。
// 四个参数分别是：起始行、终止行、起始列、终止列
int firstRow = naturalRowIndex - 1;
int lastRow = naturalRowIndex - 1;
int firstCol = naturalColumnIndex - 1;
int lastCol = naturalColumnIndex - 1;
CellRangeAddressList regions = new CellRangeAddressList(firstRow,
lastRow, firstCol, lastCol);
// 数据有效性对象
DataValidation data_validation_list = new HSSFDataValidation(regions,
constraint);
// 设置输入信息提示信息
data_validation_list.createPromptBox("日期格式提示",
"请按照'yyyy-mm-dd'格式输入日期值！");
// 设置输入错误提示信息
data_validation_list.createErrorBox("日期格式错误提示",
"你输入的日期格式不符合'yyyy-mm-dd'格式规范，请重新输入！");
return data_validation_list;
}
}
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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 com.hikvision.cms.rmp.moudules.store.user.account.dto.AccountImport;
/**
* 读取包含下拉框的Excel的工具类
*/
public class POIExcelReaderUtil {
/**
* 读取Excel数据内容
* @param InputStream
* @return Map 包含单元格数据内容的Map对象
*/
public static Map<Integer, String> readExcelContent(InputStream is) {
Map<Integer, String> content = new HashMap<Integer, String>();
String str = "";
HSSFWorkbook wb = null;
try {
wb = new HSSFWorkbook(is);
} catch (IOException e) {
e.printStackTrace();
}
Sheet sheet = wb.getSheetAt(0);
Cell cell = sheet.getRow(0).getCell(3);
// 得到总行数
int rowNum = sheet.getLastRowNum();
// System.out.println("rowNum:" + rowNum);
Row row = sheet.getRow(0);
int colNum = row.getPhysicalNumberOfCells();
// System.out.println("colNum:" + colNum);
// 正文内容应该从第二行开始,第一行为表头的标题
for (int i = 1; i <= rowNum; i++) {
row = sheet.getRow(i);
if(row == null) {
content.put(i, str);
continue;
}
int j = 0;
while (j < colNum) {
// 每个单元格的数据内容用"-"分割开，以后需要时用String类的replace()方法还原数据
// 也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean
// str += getStringCellValue(row.getCell((short) j)).trim() +
// "-";
if(((row.getCell((short) j)) == null) || ("".equals(row.getCell((short) j)))){
str += "";
if(j == 0) {
break;
}
} else {
str += getCellFormatValue(row.getCell((short) j)).trim() + "-";
}
j++;
}
content.put(i, str);
str = "";
}
return content;
}
/**
* 根据HSSFCell类型设置数据
* @param cell
* @return
*/
private static String getCellFormatValue(Cell cell) {
String cellvalue = "";
if (cell != null) {
// 判断当前Cell的Type
switch (cell.getCellType()) {
// 如果当前Cell的Type为NUMERIC
case HSSFCell.CELL_TYPE_NUMERIC:
case HSSFCell.CELL_TYPE_FORMULA: {
// 判断当前的cell是否为Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是Date类型则，转化为Data格式
//方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
//cellvalue = cell.getDateCellValue().toLocaleString();
//方法2：这样子的data格式是不带带时分秒的：2011-10-12
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = sdf.format(date);
}
// 如果是纯数字
else {
// 取得当前Cell的数值
cellvalue = String.valueOf(cell.getNumericCellValue());
}
break;
}
// 如果当前Cell的Type为STRIN
case HSSFCell.CELL_TYPE_STRING:
// 取得当前的Cell字符串
cellvalue = cell.getRichStringCellValue().getString();
break;
// 默认的Cell值
default:
cellvalue = " ";
}
} else {
cellvalue = "";
}
return cellvalue;
}
public static void main(String[] args) throws FileNotFoundException {
// InputStream is2 = new FileInputStream("f:\\用户信息导入模板2016-12-21 17_34_01.xls");
// List<AccountImport> list = AccountImport.transferToEntity(is2);
// for(AccountImport ai : list) {
// System.out.println(ai.getAccount());
// }
}
}
扫一扫
920
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
