public void export_info{
//查询数据
List<Record> listallbatchSsi=Db.use(DictKeys.db_dataSourcetwo_main)
.find("select fi.batchids from filtratesucceed" );
//数据个数
int length=listallbatchSsi.size();
//用于海量数据Excel导出类
SXSSFWorkbook wb=new SXSSFWorkbook();
Sheet sheet = wb.createSheet("导出接触和声纹模板");
String filename = "导出数据"+".xlsx";
Cell cell;
//得到Excel工作表的行
Row row;
// 常用单元格边框格式
// 设置字体和内容位置
Font f = wb.createFont();
// 设置字号大小
f.setFontHeightInPoints((short) 12);
// 设置自定义颜色
f.setColor(HSSFColor.BLACK.index);
CellStyle style = wb.createCellStyle();
style.setFont(f);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
sheet.setColumnWidth(0, 5000);
sheet.setColumnWidth(1, 5500);
sheet.setColumnWidth(2, 5500);
int sheetId = 1;
int rowIndex = 1;
//创建Excel工作表的行
row = sheet.createRow(0);
cell = row.createCell(0);
cell.setCellValue("ID");
cell.setCellStyle(style);
DataValidationHelper helper = sheet.getDataValidationHelper();
//CellRangeAddressList(firstRow,lastRow,firstCol,lastCol)设置行列范围
//length为数据库查询出的个数
CellRangeAddressList addressList = new CellRangeAddressList(1, length, 1, 1);
//如果带双引号超过30个, 打开excel的时候就会提示错误 而且下拉框不生效,
//如果不带双引号就没有问题(测试心得)
//设置下拉框数据
String[] list={"无效人名,方言障碍,作废,其它"};
DataValidationConstraint constraint = helper.createExplicitListConstraint(list);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
//处理Excel兼容性问题
if(dataValidation instanceof XSSFDataValidation){
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
}else{
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
//循环查询出的数据并写人excel中
for (int j = 0; j < length; j++) {
row = sheet.createRow(rowIndex);
if (listallbatchSsi.get(j).getStr("batchids") == null){
row.createCell(0).setCellValue("无");
}else{
row.createCell(0).setCellValue(listallbatchSsi.get(j).getStr("batchids"));
}
rowIndex++;
}
File file = new File(PathKit.getWebRootPath() + "/TouchVocal/");
if (!file.isDirectory()) {
file.mkdir();
}
FileOutputStream fout;
try {
fout = new FileOutputStream(file + "/" + filename);
wb.write(fout);
fout.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}