如果你是使用meaven的话,配置poi3.8就可以了,如下两个配置:
<!-- poi导出excel -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>commons-codec</groupId>
<artifactId>commons-codec</artifactId>
<version>1.9</version>
</dependency>
如题,使用poi组件实现上述功能,控制输入为日期格式、下拉框选择、限制输入的数据大小等等如下代码,参考资料地址:http://www.iteye.com/problems/65191,想要说明的是原文对于设置的范围的参数解释是错误的:4个参数依次应该代表为:开始行、结束行、开始列、结束列:
package com.rmsClient.util;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddressList;
/**
* 从数据库中读取工资的字段,然后动态生成excel模板
*
* @author qiulinhe
*
* 2017年2月20日 下午5:41:35
*/
public class ExcelOutputUtil {
public static void main(String[] args) {
FileOutputStream out = null;
try {
// excel对象
HSSFWorkbook wb = new HSSFWorkbook();
// sheet对象
HSSFSheet sheet = wb.createSheet("sheet1");
// 输出excel对象
out = new FileOutputStream("D://ceshi.xls");
// 取得规则
// HSSFDataValidation validateData =
// ExcelOutputUtil.setValidate((short) 1, (short) 1, (short) 4,
// (short) 4);
// HSSFDataValidation validate = ExcelOutputUtil.setBoxs();
HSSFDataValidation dateVa = ExcelOutputUtil.setDate();
// 设定规则
// sheet.addValidationData(validate);
// sheet.addValidationData(validateData);
sheet.addValidationData(dateVa);
// 输出excel
wb.write(out);
out.close();
System.out.println("在D盘成功生成了excel,请去查看");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (out != null) {
try {
out.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
// 数字大小控制:设置单元格只能在1-20之间
public static HSSFDataValidation setValidate(short firstRow, short lastRow, short firstCol, short lastCol) {
// 创建一个规则:1-100的数字
DVConstraint constraint = DVConstraint.createNumericConstraint(DVConstraint.ValidationType.INTEGER,
DVConstraint.OperatorType.BETWEEN, "1", "20");
// 设定在哪个单元格生效
CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
// 创建规则对象
HSSFDataValidation ret = new HSSFDataValidation(regions, constraint);
return ret;
}
// 下拉框限制
public static HSSFDataValidation setBoxs() {
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
final String[] DATA_LIST = new String[] { "男", "女", };
DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(DATA_LIST);
HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
dataValidation.setSuppressDropDownArrow(false);
dataValidation.createPromptBox("输入提示", "请从下拉列表中选择男女");
dataValidation.setShowPromptBox(true);
return dataValidation;
}
// 日期格式限制
public static HSSFDataValidation setDate() {
CellRangeAddressList addressList = new CellRangeAddressList(0, 1, 0, 2);
DVConstraint dvConstraint = DVConstraint.createDateConstraint(DVConstraint.OperatorType.BETWEEN, "1900-01-01",
"5000-01-01", "yyyy-mm-dd");
HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
dataValidation.setSuppressDropDownArrow(false);
dataValidation.createPromptBox("输入提示", "请填写日期格式");
// 设置输入错误提示信息
dataValidation.createErrorBox("日期格式错误提示", "你输入的日期格式不符合'yyyy-mm-dd'格式规范,请重新输入!");
dataValidation.setShowPromptBox(true);
return dataValidation;
}
}
这样就可以控制生成的excel的第二行第二列只能输入1-20的数据:
==============================分割线,2017年3月1日10:57:35==============================
上述的代码有一个问题是:无法给单元格设置背景颜色等功能,那些属性都失去作用了,具体的原因不太清楚,据说是poi3.8的bug参考另一篇博客重新写了例子如下:
package com.rmsClient.util.retireInforExcelOuput; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import com.rmsClient.entity.po.PageResult; import com.rmsClient.entity.po.RetireInforItem; import com.rmsClient.util.LogUtil; /** * 从数据库中读取工资的字段,然后动态生成excel模板 * * @author qiulinhe * * 2017年2月20日 下午5:41:35 */ public class ExcelOutputUtil implements ExcelConstraint { public static void main(String[] args) { String[] headStrings = { "姓名", "身份证号" }; // retireInforExcelModel("导出信息excel模板", "D://ceshi.xls", headStrings); FileOutputStream out = null; try { Workbook wb = new HSSFWorkbook();//这里都使用原来的类型,不加上HSS,否则背景和样式都会失效 // 输出excel对象 out = new FileOutputStream("D://ceshi.xls"); Sheet sheet = wb.createSheet("测试背景颜色"); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth(20); // 产生表格标题行 Row row = sheet.createRow(0); for (int i = 0; i < headStrings.length; i++) {
CellStyle style = wb.createCellStyle(); // 给单元格设置背景颜色 style.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); // 创建边框 style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框 style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框 style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框 style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框 // 设置居中 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 // 设置字体和文字大小 Font font2 = wb.createFont(); font2.setFontName("仿宋_GB2312"); font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示 font2.setFontHeightInPoints((short) 12); // font2.setColor(HSSFColor.RED.index);// 字体颜色:红色 font2.setColor(HSSFColor.BLACK.index);// 字体颜色:黑色 style.setFont(font2);// 选择需要用到的字体格式 Cell cell = row.createCell((short) i); cell.setCellValue(retireItems.get(i).getName()); cell.setCellStyle(style);
} // 输出excelwb.write(out);out.close();System.out.println("在D盘成功生成了excel,请去查看");LogUtil.info("在D盘成功生成了excel,请去查看");} catch (FileNotFoundException e) {LogUtil.error("生成信息模板出错" + e);e.printStackTrace();} catch (IOException e) {LogUtil.error("生成信息模板出错" + e);e.printStackTrace();} finally {if (out != null) {try {out.close();} catch (IOException e) {LogUtil.error("生成信息模板出错" + e);e.printStackTrace();}}}}结果截图为: