/**
* 修改下载模板
*/
public void exportTemplate(String path, HttpServletRequest request, HttpServletResponse response) {
FileOutputStream fileOutputStream = null;
try {
Workbook workbook = getWorkbook(path);
//TODO 查询数据库中数据
List<SysCode> list = sysCodeService.list(new QueryWrapper<SysCode>().eq("module_name", "案件操作小类"));
SysCode sysCode = new SysCode();
sysCode.setDictName("解除措施");
sysCode.setDictCode("10");
list.add(sysCode);
String[] args = new String[list.size()];
for (int i = 0; i < list.size(); i++) {
SysCode code = list.get(i);
args[i] = code.getDictName() + "_" + code.getDictCode();
}
selectList(workbook, 2, 2, args);
downLoadExcel("案件导入模板", workbook, request, response);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fileOutputStream != null) {
try {
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/**
* 根据URL获取workbook
* @param path
* @return
* @throws Exception
*/
public static Workbook getWorkbook(String path) throws Exception {
String type = path.substring(path.lastIndexOf(".") + 1);
Workbook wb;
//根据文件后缀(xls/xlsx)进行判断
InputStream input = new URL(path).openStream();
if ("xls".equals(type)) {
//文件流对象
wb = new HSSFWorkbook(input);
} else if ("xlsx".equals(type)) {
wb = new XSSFWorkbook(input);
} else {
throw new Exception("文件 类型错误");
}
return wb;
}
/**
* firstRow 開始行號 根据此项目,默认为2(下标0开始)
* lastRow 根据此项目,默认为最大65535
* firstCol 区域中第一个单元格的列号 (下标0开始)
* lastCol 区域中最后一个单元格的列号
* strings 下拉内容
*/
public static void selectList(Workbook workbook, int firstCol, int lastCol, String[] strings) {
Sheet sheet = workbook.getSheetAt(0);
// 生成下拉列表
// 生成下拉框内容
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
.createExplicitListConstraint(strings);
// 只对(x,x)单元格有效
CellRangeAddressList addressList = new CellRangeAddressList(2, 65535, firstCol, lastCol);
XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
sheet.addValidationData(validation);
}
//下载模板excel
public static void downLoadExcel(String fileName, Workbook workbook, HttpServletRequest request,
HttpServletResponse response) throws IOException {
OutputStream output = null;
BufferedOutputStream bufferedOutPut = null;
try {
// 重置响应对象 这里可能会导致前后端跨域
//response.reset();
String UserAgent = request.getHeader("USER-AGENT").toLowerCase();
// 指定下载的文件名--设置响应头
if (UserAgent.indexOf("firefox") >= 0) {
response.setHeader("content-disposition", "attachment;filename=\"" + new String(fileName.getBytes("UTF-8"), "iso-8859-1") + ".xlsx\"");
} else {
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
}
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
// 编码
response.setCharacterEncoding("UTF-8");
output = response.getOutputStream();
bufferedOutPut = new BufferedOutputStream(output);
workbook.write(bufferedOutPut);
bufferedOutPut.flush();
} catch (Exception e) {
} finally {
if (bufferedOutPut != null) {
bufferedOutPut.close();
}
if (output != null) {
output.close();
}
}
}
通过URL获取Excel并为指定单元格添加下拉列表
最新推荐文章于 2024-01-11 15:10:50 发布
这个Java代码示例展示了如何修改下载的Excel模板,填充数据库查询数据,并在特定单元格上创建下拉列表。它涉及到Workbook对象的创建、数据查询、数据写入以及文件的下载处理。下拉列表的生成利用了Apache POI库,适用于处理.xls和.xlsx文件。
摘要由CSDN通过智能技术生成