概述
xlsx文件
@RequestMapping("/test2023.action")
@ResponseBody
public String test2023(HttpServletRequest request, HttpServletResponse response) throws Exception {
// 声明一个工作薄
Workbook workbook = new XSSFWorkbook();
String sheetName = "已配置人员";
// 生成一个sheet，名称为sheetname
Sheet sheet = workbook.createSheet(sheetName);
sheet.setDefaultColumnWidth(20);
Row rowSheet1 = sheet.createRow(0); // 定义说明第1行
Row rowSheet2 = sheet.createRow(1); // 定义说明第2行
rowSheet1.setZeroHeight(true);// 隐藏第一行，参数代码
// 设置带*字体
Font headfont = workbook.createFont();
headfont.setColor(Font.COLOR_RED);// 变红
// 设置带*单元格格式
CellStyle cellStyle = workbook.createCellStyle();
// 设置单元格文本格式
DataFormat format = workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("@"));
cellStyle.setFont(headfont);
// 设置不带*字体
Font headfont1 = workbook.createFont();
headfont1.setColor(Font.COLOR_NORMAL);// 黑
// 设置不带*单元格格式
CellStyle cellStyle1 = workbook.createCellStyle();
// 设置单元格文本格式
DataFormat format1 = workbook.createDataFormat();
cellStyle1.setDataFormat(format1.getFormat("@"));
cellStyle1.setFont(headfont1);
// 设置1-4列为文本格式
sheet.setDefaultColumnStyle(0, cellStyle1);
sheet.setDefaultColumnStyle(1, cellStyle1);
sheet.setDefaultColumnStyle(2, cellStyle1);
sheet.setDefaultColumnStyle(3, cellStyle1);
/* String[] doList = {"新增", "删除"};
DVConstraint constraint = DVConstraint.createExplicitListConstraint(doList);
// 创建约束范围四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(2, 9999, 4, 4);
// 绑定下拉框和作用区域
HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
sheet.addValidationData(dataValidation);*/
String[] AUDITORS_VALUE = {"buCode", "auditors", "categoryCode", "brandCode", "operateType"};
String[] AUDITORS_KEY = {"*事业部编码", "*配置人工号", "四级类目编码 ", "品牌编码", "操作"};
int length = AUDITORS_VALUE.length;
for (int a = 0; a < length; a++) {
Cell celltempcode = rowSheet1.createCell(a);
Cell celltempdesc = rowSheet2.createCell(a);
if (AUDITORS_KEY[a].startsWith("*")) {
celltempdesc.setCellStyle(cellStyle);
} else {
celltempdesc.setCellStyle(cellStyle1);
}
celltempcode.setCellValue(AUDITORS_VALUE[a]);
celltempdesc.setCellValue(AUDITORS_KEY[a]);
}
String isIE = request.getHeader("user-agent");
downExcel(response, workbook, "审核人员配置模板.xlsx", "", isIE);
return "111";
}
public static void downExcel(HttpServletResponse response, Workbook workbook, String excelname,
String newexcelname, String isIE) {
try {
if (isIE.toLowerCase().indexOf("msie") < 0) {
// 解决中文excel文件名乱码问题,非IE
newexcelname = new String(excelname.getBytes("UTF-8"), "ISO-8859-1");
} else {
// 解决中文excel文件名乱码问题,IE
newexcelname = URLEncoder.encode(excelname, "UTF-8");
}
} catch (UnsupportedEncodingException e1) {
log.error("UnsupportedEncodingException Error!", e1);
}
response.reset();
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-disposition", "attachment; filename="" + newexcelname + """); // 实现下载
try {
workbook.write(response.getOutputStream());// 实现输出
} catch (IOException e) {
log.error("IOException Error!", e);
}
try {
response.flushBuffer();
} catch (IOException e) {
log.error("IOException Error!", e);
}
}xls文件
@RequestMapping("/test2023.action")
@ResponseBody
public String test2023(HttpServletRequest request, HttpServletResponse response) throws Exception {
// 声明一个工作薄
Workbook workbook = new HSSFWorkbook();
String sheetName = "已配置人员";
// 生成一个sheet，名称为sheetname
Sheet sheet = workbook.createSheet(sheetName);
sheet.setDefaultColumnWidth(20);
Row rowSheet1 = sheet.createRow(0); // 定义说明第1行
Row rowSheet2 = sheet.createRow(1); // 定义说明第2行
rowSheet1.setZeroHeight(true);// 隐藏第一行，参数代码
// 设置带*字体
Font headfont = workbook.createFont();
headfont.setColor(Font.COLOR_RED);// 变红
// 设置带*单元格格式
CellStyle cellStyle = workbook.createCellStyle();
// 设置单元格文本格式
DataFormat format = workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("@"));
cellStyle.setFont(headfont);
// 设置不带*字体
Font headfont1 = workbook.createFont();
headfont1.setColor(Font.COLOR_NORMAL);// 黑
// 设置不带*单元格格式
CellStyle cellStyle1 = workbook.createCellStyle();
// 设置单元格文本格式
DataFormat format1 = workbook.createDataFormat();
cellStyle1.setDataFormat(format1.getFormat("@"));
cellStyle1.setFont(headfont1);
// 设置1-4列为文本格式
sheet.setDefaultColumnStyle(0, cellStyle1);
sheet.setDefaultColumnStyle(1, cellStyle1);
sheet.setDefaultColumnStyle(2, cellStyle1);
sheet.setDefaultColumnStyle(3, cellStyle1);
String[] doList = {"新增", "删除"};
DVConstraint constraint = DVConstraint.createExplicitListConstraint(doList);
// 创建约束范围四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(2, 9999, 4, 4);
// 绑定下拉框和作用区域
HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
sheet.addValidationData(dataValidation);
String[] AUDITORS_VALUE = {"buCode", "auditors", "categoryCode", "brandCode", "operateType"};
String[] AUDITORS_KEY = {"*事业部编码", "*配置人工号", "四级类目编码 ", "品牌编码", "操作"};
int length = AUDITORS_VALUE.length;
for (int a = 0; a < length; a++) {
Cell celltempcode = rowSheet1.createCell(a);
Cell celltempdesc = rowSheet2.createCell(a);
if (AUDITORS_KEY[a].startsWith("*")) {
celltempdesc.setCellStyle(cellStyle);
} else {
celltempdesc.setCellStyle(cellStyle1);
}
celltempcode.setCellValue(AUDITORS_VALUE[a]);
celltempdesc.setCellValue(AUDITORS_KEY[a]);
}
String isIE = request.getHeader("user-agent");
downExcel(response, workbook, "审核人员配置模板.xls", "", isIE);
return "111";
}
public static void downExcel(HttpServletResponse response, Workbook workbook, String excelname,
String newexcelname, String isIE) {
try {
if (isIE.toLowerCase().indexOf("msie") < 0) {
// 解决中文excel文件名乱码问题,非IE
newexcelname = new String(excelname.getBytes("UTF-8"), "ISO-8859-1");
} else {
// 解决中文excel文件名乱码问题,IE
newexcelname = URLEncoder.encode(excelname, "UTF-8");
}
} catch (UnsupportedEncodingException e1) {
log.error("UnsupportedEncodingException Error!", e1);
}
response.reset();
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-disposition", "attachment; filename="" + newexcelname + """); // 实现下载
try {
workbook.write(response.getOutputStream());// 实现输出
} catch (IOException e) {
log.error("IOException Error!", e);
}
try {
response.flushBuffer();
} catch (IOException e) {
log.error("IOException Error!", e);
}
}EXCEL文件写到磁盘
FileOutputStream fos = new FileOutputStream("d://aaa.xls");
workbook.write(fos);
fos.close();最后
以上就是生动萝莉为你收集整理的EXCEL文件生成xlsx文件xls文件EXCEL文件写到磁盘的全部内容，希望文章能够帮你解决EXCEL文件生成xlsx文件xls文件EXCEL文件写到磁盘所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复