public void exportExcel(List<List<String>> list,HttpServletResponse response,String fileName) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheetCs = wb.createSheet("sheet1");
//为首行赋值
for(int i=0;i<list.size();i++){
XSSFRow row = sheetCs.createRow(i);
for(int j=0;j<list.get(i).size();j++){
sheetCs.setColumnWidth(j, 3500);//设置列宽
XSSFCell cell = row.createCell(j);
cell.setCellValue(list.get(i).get(j));
}
}
int sheetTotal=wb.getNumberOfSheets();
XSSFRow row;
//第一个下拉选
//新建一个sheet页
String hiddenSheetName = "hiddenSheet" + sheetTotal;
XSSFSheet hiddenSheet = wb.createSheet(hiddenSheetName);
//数据
String[] selectList =new String[100]; //对应列下拉框数据
for(int i=0;i<100;i++){
selectList[i]="测试数据"+i;
}
//写入下拉数据到新的sheet页中
for (int i = 0; i < selectList.length; i++) {
row = hiddenSheet.createRow(i);
XSSFCell cell = row.createCell(0);
cell.setCellValue(selectList[i]);
}
//获取新sheet页内容
String strFormula = hiddenSheetName + "!$A$1:$A$65535";
XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST,strFormula);
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(1,65535, 0, 0);
// 数据有效性对象
DataValidationHelper help = new XSSFDataValidationHelper((XSSFSheet) sheetCs);
DataValidation validation = help.createValidation(constraint, regions);
sheetCs.addValidationData(validation);
//将新建的sheet页隐藏掉
wb.setSheetHidden(sheetTotal, true);
sheetTotal++;
//第二个下拉选
hiddenSheetName = "hiddenSheet" + sheetTotal;
XSSFSheet hiddenSheet2 = wb.createSheet(hiddenSheetName);
String[] selectList2 =new String[100]; //对应列下拉框数据
for(int i=0;i<100;i++){
selectList2[i]="测试数据2"+i;
}
for (int i = 0; i < selectList2.length; i++) {
row = hiddenSheet2.createRow(i);
XSSFCell cell = row.createCell(0);
cell.setCellValue(selectList2[i]);
}
String strFormula2 = hiddenSheetName + "!$A$1:$A$65535";
XSSFDataValidationConstraint constraint2 = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST,strFormula2);
CellRangeAddressList regions2 = new CellRangeAddressList(1,65535, 1, 1);
DataValidationHelper help2 = new XSSFDataValidationHelper((XSSFSheet) sheetCs);
DataValidation validation2 = help2.createValidation(constraint2, regions2);
sheetCs.addValidationData(validation2);
wb.setSheetHidden(sheetTotal, true);
sheetTotal++;
try {
OutputStream output = response.getOutputStream();
fileName = URLEncoder.encode(fileName,"UTF-8");
response.addHeader("Content-Disposition", "inline;filename="+fileName+".xls");
response.setContentType("application/msexcel");
wb.write(output);
output.flush();
output.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 结束
System.out.println("Over");
}
实际项目中使用如下：
@ResponseBody
@RequestMapping(value = "/exportBusinessTemplate", method = RequestMethod.GET)
@ApiOperation(value = "导出商机模板")
public void exportBusinessTemplate(@ModelAttribute TwmBusinessRecordsQuery query,HttpServletResponse response) throws IOException {
final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//文件名称初始化
String fileName = null;
fileName = "商机导入模板_" + sdf.format(new Date()) + ".xlsx";
response.setHeader("Content-disposition",
"attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));// 设置文件头编码格式
response.setContentType("application/ms-excel");
response.setHeader("Cache-Control", "no-cache");// 设置头
response.setDateHeader("Expires", 0);// 设置日期头
// 获取导出头部
List<CustomerTemplateHeadDto> headList = twmBusinessRecordsFacadeService.exportCustomerTemplateTwo(query);
XSSFWorkbook hssfWorkbook = new XSSFWorkbook();
XSSFSheet hssfWorkbookSheet = hssfWorkbook.createSheet("sheet");
//设置样式
XSSFCellStyle blackStyle = hssfWorkbook.createCellStyle();
XSSFFont font=hssfWorkbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体增粗
font.setColor(HSSFColor.BLACK.index);
blackStyle.setFont(font);
blackStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中
XSSFCellStyle redStyle = hssfWorkbook.createCellStyle();
XSSFFont redFont=hssfWorkbook.createFont();
redFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体增粗
redFont.setColor(HSSFColor.RED.index);
redStyle.setFont(redFont);
redStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中
CellRangeAddress region = new CellRangeAddress(0, 0, 0, headList.size()-1);
hssfWorkbookSheet.addMergedRegion(region);
XSSFRow msgRow = hssfWorkbookSheet.createRow(0);
msgRow.setHeightInPoints(7*14);
XSSFCell msgCell = msgRow.createCell(0);
XSSFCellStyle cellStyle=hssfWorkbook.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setWrapText(true);
cellStyle.setFont(font);
msgCell.setCellStyle(cellStyle);
msgCell.setCellValue(ApiConstantPool.tipMsg);
XSSFCell cell = null;
XSSFRow row = hssfWorkbookSheet.createRow(1);
for (CustomerTemplateHeadDto headField : headList) {
cell = row.createCell(headField.getSort());
XSSFRichTextString text = new XSSFRichTextString(headField.getFieldName());
cell.setCellValue(text);
hssfWorkbookSheet.setColumnWidth(headField.getSort(), 4950);
// 字体设置字体颜色
if(headField.isNotNull()) {
cell.setCellStyle(redStyle);
}else {
cell.setCellStyle(blackStyle);
}
// 设置下拉框
if(StringUtils.equals(headField.getFieldType(), FieldControlType.DROP_DOWN_BOX.getCode())
|| StringUtils.equals(headField.getFieldType(), FieldControlType.DROP_DOWN_TWO_LEVEL.getCode())) {
if(!CollectionUtils.isEmpty(headField.getFieldOption())) {
int sheetTotal=hssfWorkbook.getNumberOfSheets();
XSSFRow row1;
//第一个下拉选
//新建一个sheet页
String hiddenSheetName = "hiddenSheet" + sheetTotal;
XSSFSheet hiddenSheet = hssfWorkbook.createSheet(hiddenSheetName);
//数据
String[] selectList =headField.getFieldOption().toArray(new String[headField.getFieldOption().size()]); //对应列下拉框数据
//写入下拉数据到新的sheet页中
for (int i = 0; i < selectList.length; i++) {
row1 = hiddenSheet.createRow(i);
XSSFCell cell1 = row1.createCell(0);
cell1.setCellValue(selectList[i]);
}
//获取新sheet页内容
String strFormula = hiddenSheetName + "!$A$1:$A$65535";
XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST,strFormula);
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(2,500, headField.getSort(),
headField.getSort());
// 数据有效性对象
DataValidationHelper help = new XSSFDataValidationHelper((XSSFSheet) hssfWorkbookSheet);
DataValidation validation = help.createValidation(constraint, regions);
hssfWorkbookSheet.addValidationData(validation);
//将新建的sheet页隐藏掉
hssfWorkbook.setSheetHidden(sheetTotal, true);
sheetTotal++;
}
}
if(StringUtils.equals(headField.getFieldType(), FieldControlType.DROP_DOWN_MANY_BOX.getCode())
|| StringUtils.equals(headField.getFieldType(), FieldControlType.DROP_DOWN_TEXT.getCode())) {
if(!CollectionUtils.isEmpty(headField.getFieldOption())) {
int sheetTotal=hssfWorkbook.getNumberOfSheets();
XSSFRow row1;
//第一个下拉选
//新建一个sheet页
String hiddenSheetName = "hiddenSheet" + sheetTotal;
XSSFSheet hiddenSheet = hssfWorkbook.createSheet(hiddenSheetName);
//数据
String[] selectList =headField.getFieldOption().toArray(new String[headField.getFieldOption().size()]); //对应列下拉框数据
//写入下拉数据到新的sheet页中
for (int i = 0; i < selectList.length; i++) {
row1 = hiddenSheet.createRow(i);
XSSFCell cell1 = row1.createCell(0);
cell1.setCellValue(selectList[i]);
}
//获取新sheet页内容
String strFormula = hiddenSheetName + "!$A$1:$A$65535";
XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST,strFormula);
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(2,500, headField.getSort(),
headField.getSort());
// 数据有效性对象
DataValidationHelper help = new XSSFDataValidationHelper((XSSFSheet) hssfWorkbookSheet);
DataValidation validation = help.createValidation(constraint, regions);
String msgHead = "只可输入以下单个或多选项，且要以英文逗号\",\"隔开";
if (StringUtils.equals(headField.getFieldType(), FieldControlType.DROP_DOWN_TEXT.getCode())) {
msgHead = "可输入以下选项，或自行输入内容";
}
validation.createPromptBox(msgHead, StringUtils.join(headField.getFieldOption(), ","));
hssfWorkbookSheet.addValidationData(validation);
//将新建的sheet页隐藏掉
hssfWorkbook.setSheetHidden(sheetTotal, true);
sheetTotal++;
}
}
}
hssfWorkbook.write(response.getOutputStream());
response.getOutputStream().flush();
response.getOutputStream().close();
}
扫一扫
4798
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
