java导入导出
文章目录方式一：采用easyexcel导入方式一：采用easyexcel导入1.需要导入的依赖<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.8</version>
·
方式一：采用easyexcel导入
1.需要导入的依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.8</version>
</dependency>
实体类代码：
@Data
@Entity // 该注解声明一个实体类，与数据库中的表对应
@Table(name = "tb_user") //表示当映射多个表时，指定表的表中的字段。默认值为主表的表名。
@ApiModel(description = "用户表")
public class User{
//主键标识：表明该声明的属性字段和数据库的主键字段一一对应
@Id
//主键生成策略，不写意味着没有主键生成策略，也意味着是手动主键
@GeneratedValue(strategy = GenerationType.IDENTITY)
@ApiModelProperty(hidden = true) //swagger注解
private Integer id;
@ApiModelProperty(value = "用户名")
@ExcelProperty(index = 1)
//unique
//表示该字段是否为唯一标识，默认为false。如果表中有一个字段需要唯一标识，则既可以使用该标记，也可以使用@Table标记中的@UniqueConstraint。
//nullable 表示该字段是否可以为null值，默认为true。
// length 表示字段的长度，当字段的类型为varchar时，该属性才有效，默认为255个字符。
@Column(name = "user_name", unique = true, nullable = false, length = 50)
private String userName;
@ApiModelProperty(value = "用户工号")
@ExcelProperty(index = 2)
@Column(name = "login_name", length = 20)
private String loginName;
@ApiModelProperty(value = "密码")
@Column(name = "password", length = 50)
private String password;
@ApiModelProperty(value = "头像")
@Column(name = "avatar", length = 100)
private String avatar;
@ApiModelProperty(value = "性别")
@ExcelProperty(index = 3)
@Column(name = "gender", nullable = false, length = 1)
private String gender;
@ApiModelProperty(value = "邮箱")
@ExcelProperty(index = 4)
@Column(name = "email", length = 50)
private String email;
@ApiModelProperty(value = "描述")
@ExcelProperty(index = 7,value = "描述")
@Column(name = "description")
private String description;
@ApiModelProperty(value = "所属公司")
@ExcelProperty(index = 5,value = "所属公司")
@Column(name = "company", length = 50)
private String company;
@ApiModelProperty(value = "职位")
@ExcelProperty(index = 6,value ="职位")
@Column(name = "position", length = 20)
private String position;
@ApiModelProperty(hidden = true)
private Date createTime;
@ApiModelProperty(hidden = true)
private Date updateTime;
}
实现监听回调
public class PrtcpntInfoListener extends AnalysisEventListener<User> {
private static final Logger log = LoggerFactory.getLogger(PrtcpntInfoListener.class);
private static int TOTAL_COUNT;
/**
* 阈值：超20000条，批量写入库一次，清理list，回收内存
*/
private static final int BATCH_COUNT = 110000;
List<User> list = new ArrayList<>();
private BatchLead batchLead;
public PrtcpntInfoListener(BatchLead batchLead) {
this.batchLead = batchLead;
}
/**
* 这个每一条数据解析都会来调用
*
* @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(User data, AnalysisContext context) {
log.info("解析到一条数据:{}", JSON.toJSONString(data));
list.add(data);
// 每达到阈值，就去做批量入库，防止内存溢出
if (list.size() >= BATCH_COUNT) {
ThreadOperation.readExcel(list);
TOTAL_COUNT += list.size();
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
//执行完让线程直接进入等待
batchLead.save(list);
TOTAL_COUNT += list.size();
log.info("所有数据已入库完成，总计：{}条", TOTAL_COUNT);
}
public static void main(String[] args) {
Long startTime = System.currentTimeMillis();
ExcelReader excelReader = null;
try {
String filePath = "C:/Users/86135/Desktop/用户信息.xlsx";
excelReader = EasyExcel.read(filePath, User.class, new PrtcpntInfoListener(new BatchLead())).build();
ReadSheet readSheet = EasyExcel.readSheet(0).headRowNumber(2).build();
excelReader.read(readSheet);
} finally {
// 关闭资源
if (excelReader != null) {
excelReader.finish();
}
}
log.info("总耗时：" + (System.currentTimeMillis() - startTime) / 1000 + "(s)");
}
}
保存数据 JDBC直连方式
public class BatchLead {
private static final Logger log = LoggerFactory.getLogger(BatchLead.class);
int i = 0;
public void save(List<User> list) {
System.out.println("保存="+list.size());
Long startTime = System.currentTimeMillis();
Connection conn = null;
PreparedStatement pstmt = null;
String sql = "insert into tb_user(user_name,login_name,password,avatar,gender,email,description,company,position)values(?,?,?,?,?,?,?,?,?)";
conn = getConnection();
try {
conn.setAutoCommit(false);
pstmt = conn.prepareStatement(sql);
} catch (SQLException e1) {
e1.printStackTrace();
}
for (User dataInfo : list) {
try {
pstmt.setString(1, dataInfo.getUserName());
pstmt.setString(2, dataInfo.getLoginName());
pstmt.setString(3, dataInfo.getPassword());
pstmt.setString(4, dataInfo.getAvatar());
pstmt.setString(5, dataInfo.getGender());
pstmt.setString(6, dataInfo.getEmail());
pstmt.setString(7, dataInfo.getDescription());
pstmt.setString(8, dataInfo.getCompany());
pstmt.setString(9, dataInfo.getPosition());
pstmt.addBatch();
i++;
} catch (SQLException e) {
e.printStackTrace();
}
}
try {
pstmt.executeBatch();
conn.commit();
conn.close();
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
log.info("批量入库，本次入库数据：{}条，耗时：{}(s)", list.size(), (System.currentTimeMillis() - startTime) / 1000);
}
public static Connection getConnection() {
Connection connection = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/localtest?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC",
"root", "root");
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return connection;
}
}
或者使用mybatis，在覆盖doAfterAllAnalysed方法中调用
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
UserMapper userMapper = ApplicationContextProvider.getBean(UserMapper.class);
userMapper.batchAddUser(list);
TOTAL_COUNT += list.size();
log.info("所有数据已入库完成，总计：{}条", TOTAL_COUNT);
}
controller层调用：
/**
* 批量导入
*
* @param
* @return
*/
@PostMapping("/users/add")
@ApiOperation(value = "批量导入")
public ResultDTO saveBatchUser(MultipartFile file) {
String fileName = file.getOriginalFilename();
if (!StrUtil.endWithAny(fileName, EXCEL_XLS, EXCEL_XLSX)) {
return ResultDTO.error("只支持.xlsx与.xls格式的文件导入");
}
return myUserService.saveBatchUser(file,fileName);
}
service层代码：
public ResultDTO saveBatchUser(MultipartFile multipartFile, String fileName) {
Long startTime = System.currentTimeMillis();
ExcelReader excelReader = null;
File file = null;
try {
String[] filename = fileName.split("\\.");
file=File.createTempFile(filename[0], ".xlsx");
multipartFile.transferTo(file);
excelReader = EasyExcel.read(file, User.class, new PrtcpntInfoListener(new BatchLead())).build();
ReadSheet readSheet = EasyExcel.readSheet(0).headRowNumber(1).build();
excelReader.read(readSheet);
}catch (Exception e){
e.printStackTrace();
} finally {
// 关闭资源
if (excelReader != null) {
excelReader.finish();
}
// file.deleteOnExit();
}
log.info("总耗时：" + (System.currentTimeMillis() - startTime) / 1000 + "(s)");
return ResultDTO.isSuccess();
}
上面是单线程版
多线程保存数据库方式
先定义线程资源类
public class ImportThread implements Runnable {
BatchLead batchLead;
private List<User> list;
private CountDownLatch end;
private MyUserMapper myUserMapper;
public ImportThread(List<User> list, CountDownLatch begin, CountDownLatch end, BatchLead batchLead) {
this.list = list;
this.end = end;
this.batchLead = batchLead;
}
@Override
public void run() {
//这里三种方式选取一种
//1.mybatis方式
UserMapper userMapper = ApplicationContextProvider.getBean(UserMapper.class);
userMapper.batchAddUser(list);
//2.jpa方式
// MyUserMapper myUserMapper = ApplicationContextProvider.getBean(MyUserMapper.class);
// myUserMapper.saveAll(list);
//3.JDBC直连
// batchLead.save(list);
// end.countDown();
}
}
上面连接数据库如果使用mybatis方式，sql语句如下
<insert id="batchAddUser">
insert into tb_user(user_name,login_name,password,avatar,gender,email,description,company,position) values
<foreach collection="list" separator="," item="item">
(#{item.userName},#{item.loginName},#{item.password},#{item.avatar},#{item.gender},#{item.email},#{item.description},#{item.company},#{item.position})
</foreach>
</insert>
使用线程池
public class ThreadOperation {
public static void readExcel(List<User> list) {
//一个线程处理数据数
int count = 11000;
// 开启的线程数
int runSize = 10;
// 存放每个线程的执行数据
List<User> newlist = null;
// int corePoolSize,核心线程数
// int maximumPoolSize,最大线程数
// long keepAliveTime,线程空闲时最大存活时间
// TimeUnit unit,最大存活时间单位
// BlockingQueue<Runnable> workQueue,任务队列
// ThreadFactory threadFactory,线程工厂用默认的即可
// RejectedExecutionHandler handler)线程池和队列都满时的拒绝策略
//创建一个线程池,数量和开启线程的数据量一样
ThreadPoolExecutor executor = new ThreadPoolExecutor(
runSize, runSize, 1, TimeUnit.SECONDS, new ArrayBlockingQueue<>(5),
new ThreadPoolExecutor.DiscardOldestPolicy());
//并发容器，计数器，等待协同
CountDownLatch end = new CountDownLatch(runSize);
//循环创建线程
for (int i = 0; i < runSize; i++) {
//计算每个线程执行的数据
if ((i+1)==runSize){
newlist=list.subList((i*count),list.size());
}else {
newlist=list.subList((i*count),(i+1)*count);
}
//线程类 操作资源
ImportThread mythead = new ImportThread(newlist, end,new BatchLead());
//执行线程
executor.execute(mythead);
}
try {
end.await();
} catch (InterruptedException e) {
e.printStackTrace();
}
//执行完关闭线程池
executor.shutdown();
}
}
方式二：采用POI导入
依赖准备
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.4</version>
</dependency>
自定义注解
/**
* 设置允许导出
*/
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface EnableExport {
String fileName();
}
/**
* 设置该字段允许导出
* 并且可以设置宽度
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface EnableExportField {
int colWidth() default 100;
String colName();
}
/**
* 导入时索引
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ImportIndex {
int index() ;
}
//pojo
@EnableExport(fileName = "XXX功能")
public class DataCheckExport {
@EnableExportField(colName = "编码",colWidth = 90)
@ImportIndex(index = 0)
private String code;
....
}
导入 、导出、 自定义表格颜色工具类
public class ExcelUtils {
/** 所有的下拉列表数据存在这个map中，key是对应的Excel列的序号，从0开始，value为下拉列表键对值 **/
public static final Map<Integer,Map<String,String>> ALL_SELECT_LIST_MAP = new HashMap<Integer,Map<String,String>> ();
/**
* 将Excel转换为对象集合
* @param excel Excel 文件
* @param clazz pojo类型
* @return
*/
public static List<?> parseExcelToList(File excel,Class clazz){
List<Object> res = new ArrayList<Object>();
// 创建输入流，读取Excel
InputStream is = null;
Sheet sheet = null;
try {
is = new FileInputStream(excel.getAbsolutePath());
if (is != null) {
Workbook workbook = WorkbookFactory.create(is);
//默认只获取第一个工作表
sheet = workbook.getSheetAt(0);
if (sheet != null) {
int i = 2;
String values[] ;
Row row = sheet.getRow(i);
while (row != null) {
//获取单元格数目
int cellNum = row.getPhysicalNumberOfCells();
values = new String[cellNum];
for (int j = 0; j <= cellNum; j++) {
Cell cell = row.getCell(j);
if (cell != null) {
//设置单元格内容类型
cell.setCellType(Cell.CELL_TYPE_STRING );
//获取单元格值
String value = cell.getStringCellValue() == null ? null : cell.getStringCellValue();
values[j]=value;
}
}
Field[] fields = clazz.getDeclaredFields();
Object obj = clazz.newInstance();
for(Field f : fields){
if(f.isAnnotationPresent(ImportIndex.class)){
ImportIndex annotation = f.getAnnotation(ImportIndex.class);
int index = annotation.index();
String useSetMethodName = annotation.useSetMethodName();
if(!"".equals(useSetMethodName)){
Object val = TypeUtils.cast(values[index],f.getType(),null);
f.setAccessible(true);
Method method = clazz.getMethod(useSetMethodName, new Class[]{f.getType(),Object.class});
method.setAccessible(true);
method.invoke(obj, new Object[]{f.get(obj),val});
}else{
f.setAccessible(true);
Object val =TypeUtils.cast(values[index],f.getType(),null);
f.set(obj,val);
}
}
}
res.add(obj);
i++;
row=sheet.getRow(i);
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return res;
}
/**
* 将Excel转换为对象集合
* @param excel Excel 文件输入流
* @param clazz pojo类型
* @return
*/
public static List<?> parseExcelToList(InputStream excel,Class clazz) throws IOException,
InvalidFormatException,
InstantiationException,
IllegalAccessException,
NoSuchMethodException,
InvocationTargetException {
List<Object> res = new ArrayList<Object>();
// 创建输入流，读取Excel
InputStream is = null;
Sheet sheet = null;
is = excel;
if (is != null) {
Workbook workbook = WorkbookFactory.create(is);
//默认只获取第一个工作表
sheet = workbook.getSheetAt(0);
if (sheet != null) {
int i = 2;
String values[] ;
Row row = sheet.getRow(i);
while (row != null) {
//获取单元格数目
int cellNum = row.getPhysicalNumberOfCells();
values = new String[cellNum];
for (int j = 0; j <= cellNum; j++) {
Cell cell = row.getCell(j);
if (cell != null) {
//设置单元格内容类型
cell.setCellType(Cell.CELL_TYPE_STRING );
//获取单元格值
String value = cell.getStringCellValue() == null ? null : cell.getStringCellValue();
values[j]=value;
}
}
Field[] fields = clazz.getDeclaredFields();
Object obj = clazz.newInstance();
for(Field f : fields){
if(f.isAnnotationPresent(ImportIndex.class)){
ImportIndex annotation = f.getAnnotation(ImportIndex.class);
int index = annotation.index();
Object value = values[index];
if(f.isAnnotationPresent(EnableSelectList.class)){
value = getKeyByValue(ALL_SELECT_LIST_MAP.get(index),String.valueOf(value ) );
}
String useSetMethodName = annotation.useSetMethodName();
if(!"".equals(useSetMethodName)){
Object val =TypeUtils.cast(value,f.getType(),null);
f.setAccessible(true);
Method method = clazz.getMethod(useSetMethodName, new Class[]{f.getType(),Object.class});
method.setAccessible(true);
method.invoke(obj, new Object[]{f.get(obj),val});
}else{
f.setAccessible(true);
Object val =TypeUtils.cast(value,f.getType(),null);
f.set(obj,val);
}
}
}
res.add(obj);
i++;
row=sheet.getRow(i);
}
}
}
return res;
}
/**
* 导出 Excel
* @param outputStream 输出流，用于写文件
* @param dataList 需要导出的数据
* @param clazz 导出数据的pojo类型
* @param selectListMap 下拉列表的列
* @param exportTitle 当该参数不为空则替换默认的标题
*/
public static void exportExcel(OutputStream outputStream, List dataList, Class clazz, Map<Integer,Map<String,String>> selectListMap,String exportTitle){
//创建一个Excel工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//建立表
HSSFSheet hssfsheet = workbook.createSheet();
hssfsheet.setDefaultRowHeight( ( short )(20*20) );
//检查当前pojo是否允许导出
if(clazz.isAnnotationPresent(EnableExport.class)) {
EnableExport export = (EnableExport) clazz.getAnnotation(EnableExport.class);
//获取所有标题名称
List<String> colNames =new ArrayList<String>();
//获取所有标题的背景颜色
List<ColorEnum> colors =new ArrayList<ColorEnum>();
//所有允许导出的字段
List<Field> fieldList = new ArrayList<Field>();
for(Field field : clazz.getDeclaredFields()){
if(field.isAnnotationPresent(EnableExportField.class)){
EnableExportField enableExportField = field.getAnnotation(EnableExportField.class);
colNames.add(enableExportField.colName());
colors.add(enableExportField.cellColor());
fieldList.add(field);
}
}
//设置每列的宽度
for(int i=0;i<fieldList.size();i++){
Field field = fieldList.get(i);
hssfsheet.setColumnWidth(i,field.getAnnotation(EnableExportField.class).colWidth()*20);
}
HSSFRow hssfRow = null;
HSSFCell hssfcell = null;
//绘制表头以及菜单
String fileName =export.fileName();
if(exportTitle!=null){
fileName = exportTitle;
}
//绘制标题
createTitle(workbook,hssfRow,hssfcell,hssfsheet, colNames.size()-1 ,fileName,export.cellColor());
//创建标题行（表头）
createHeadRow(workbook,hssfRow,hssfcell,hssfsheet,colNames,colors);
try {
//表格样式
HSSFCellStyle cellStyle= getBasicCellStyle(workbook);
//插入内容
int i=0;
for (Object obj : dataList) {
hssfRow = hssfsheet.createRow(i + 2);
//设置每列的宽度
//此处设置j=-1 ：添加一列，序号列
for(int j=0;j<fieldList.size();j++){
Field field = fieldList.get(j);
field.setAccessible(true);
Object value = field.get(obj);
EnableExportField enableExportField = field.getAnnotation(EnableExportField.class);
String getMethodName = enableExportField.useGetMethod();
if(!"".equals( getMethodName )){
Method method = clazz.getMethod(getMethodName, new Class[]{field.getType()});
method.setAccessible(true);
value= method.invoke(obj, new Object[]{value});
}
if(field.isAnnotationPresent(EnableSelectList.class)){
if(selectListMap!=null&& selectListMap.get(j)!=null)
value =selectListMap.get(j).get(value);
}
setCellValue(value, hssfcell, hssfRow, cellStyle, j);
}
i++;
}
//创建下拉列表
createDataValidation(hssfsheet,selectListMap);
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (IllegalAccessException e ) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}catch (NoSuchMethodException e) {
e.printStackTrace();
}catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
/**
* 获取一个基本的带边框的单元格
* @param workbook
* @return
*/
private static HSSFCellStyle getBasicCellStyle(HSSFWorkbook workbook){
HSSFCellStyle hssfcellstyle = workbook.createCellStyle();
hssfcellstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
hssfcellstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
hssfcellstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
hssfcellstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
hssfcellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
hssfcellstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
hssfcellstyle.setWrapText(true);
return hssfcellstyle;
}
/**
* 获取带有背景色的标题单元格
* @param workbook
* @return
*/
private static HSSFCellStyle getTitleCellStyle(HSSFWorkbook workbook,ColorEnum color){
HSSFCellStyle hssfcellstyle = getBasicCellStyle(workbook);
hssfcellstyle.setFillForegroundColor(color.getIndex()); // 设置背景色
hssfcellstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
return hssfcellstyle;
}
/**
* 创建一个跨列的标题行
* @param workbook
* @param hssfRow
* @param hssfcell
* @param hssfsheet
* @param allColNum
* @param title
*/
private static void createTitle(HSSFWorkbook workbook, HSSFRow hssfRow , HSSFCell hssfcell, HSSFSheet hssfsheet,int allColNum,String title,ColorEnum color){
//在sheet里增加合并单元格
CellRangeAddress cra = new CellRangeAddress(0, 0, 0, allColNum);
hssfsheet.addMergedRegion(cra);
// 使用RegionUtil类为合并后的单元格添加边框
RegionUtil.setBorderBottom(1, cra, hssfsheet, workbook); // 下边框
RegionUtil.setBorderLeft(1, cra, hssfsheet, workbook); // 左边框
RegionUtil.setBorderRight(1, cra, hssfsheet, workbook); // 有边框
RegionUtil.setBorderTop(1, cra, hssfsheet, workbook); // 上边框
//设置表头
hssfRow = hssfsheet.getRow(0);
hssfcell = hssfRow.getCell(0);
hssfcell.setCellStyle( getTitleCellStyle(workbook,color));
hssfcell.setCellType(HSSFCell.CELL_TYPE_STRING);
hssfcell.setCellValue(title);
}
/**
* 设置表头标题栏以及表格高度
* @param workbook
* @param hssfRow
* @param hssfcell
* @param hssfsheet
* @param colNames
*/
private static void createHeadRow(HSSFWorkbook workbook,HSSFRow hssfRow , HSSFCell hssfcell,HSSFSheet hssfsheet,List<String> colNames,List<ColorEnum> colors){
//插入标题行
hssfRow = hssfsheet.createRow(1);
for (int i = 0; i < colNames.size(); i++) {
hssfcell = hssfRow.createCell(i);
hssfcell.setCellStyle(getTitleCellStyle(workbook,colors.get(i)));
hssfcell.setCellType(HSSFCell.CELL_TYPE_STRING);
hssfcell.setCellValue(colNames.get(i));
}
}
/**
* excel添加下拉数据校验
* @param sheet 哪个 sheet 页添加校验
* @return
*/
public static void createDataValidation(Sheet sheet, Map<Integer,Map<String,String>> selectListMap) {
if(selectListMap!=null) {
for(Map.Entry<Integer,Map<String,String>> entry:selectListMap.entrySet() ){
Integer key = entry.getKey();
Map<String,String> value = entry.getValue();
// 第几列校验（0开始）key 数据源数组value
if(value.size()>0) {
int i=0;
String[] valueArr = new String[value.size()];
for(Map.Entry<String,String> ent :value.entrySet()){
valueArr[i] = ent.getValue();
i++;
}
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(2, 65535, key, key);
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(valueArr);
DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
//处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation) {
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
dataValidation.setEmptyCellAllowed(true);
dataValidation.setShowPromptBox(true);
dataValidation.createPromptBox("提示", "只能选择下拉框里面的数据");
sheet.addValidationData(dataValidation);
}
}
}
}
/**
*通过value获取key值
* @param selectMap
* @param value
* @return
*/
private static String getKeyByValue(Map<String,String> selectMap,String value){
if(selectMap!=null){
for(Map.Entry<String,String> ent :selectMap.entrySet()){
if(value!=null&&value.equals(ent.getValue()))
return ent.getKey();
}
}else{
return value;
}
return null;
}
/**
*判断字符串是否为数字
* @param str
* @return
*/
private static boolean isNumeric(String str) {
Pattern pattren =
Pattern.compile("[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$");
if (str != null && !"".equals(str.trim())) {
Matcher matcher = pattren.matcher(str);
if (matcher.matches()) {
if (!str.contains(".") && str.startsWith("0")) {
return false;
}
return true;
}
}
return false;
}
/**
*设置单元格的值
* @param value
* @param hssfcell
* @param hssfRow
* @param cellStyle
* @param cellIndex
*/
private static void setCellValue(Object value,HSSFCell hssfcell,HSSFRow hssfRow,CellStyle cellStyle,int cellIndex) {
String valueStr = String.valueOf(value);
hssfcell =hssfRow.createCell(cellIndex );
//暂时认为数字类型不会有下拉列表
if (isNumeric(valueStr)) {
hssfcell.setCellStyle(cellStyle);
hssfcell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
hssfcell.setCellValue(Double.valueOf(valueStr));
} else {
hssfcell.setCellStyle(cellStyle);
hssfcell.setCellType(HSSFCell.CELL_TYPE_STRING);
hssfcell.setCellValue(valueStr);
}
}
}
还可以自定义表格颜色，根据业务需要使用，定义一个枚举类存储需要的颜色。
public enum ColorEnum {
RED("红色", HSSFColor.RED.index),
GREEN("绿色", HSSFColor.GREEN.index),
BLANK("白色", HSSFColor.WHITE.index),
YELLOW("黄色", HSSFColor.YELLOW.index),
BLUE("蓝色", HSSFColor.CORNFLOWER_BLUE.index);
private String name;
private short index;
private ColorEnum( String name, short index) {
this.name = name;
this.index = index;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public short getIndex() {
return index;
}
public void setIndex(short index) {
this.index = index;
}
}
为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。
更多推荐
8
0- 0
已为社区贡献1条内容
所有评论(0)