模板导出
拿来即用
@PostMapping("/templateExport")
@ApiOperation(value = "模板导出", notes = "作者：yysd")
public ReturnObject exportAuditContent(HttpServletResponse response) throws Exception{
ReturnObject returnObject = new ReturnObject();
OutputStream outputStream = tryCatch2gGetOutput(response);
HSSFWorkbook workbook = new HSSFWorkbook();
//headers
String[] headers=new String[]{"表头1","表头2","表头3","表头4","表头5","表头6","表头7","表头8"};
String sheetTitle = "机电设备信息缺陷";
ExcelForMoreSheet.exportExcel(workbook,sheetTitle,headers,outputStream);
try {
workbook.write(outputStream);
outputStream.close();
returnObject.setRedata("导出成功");
} catch (Exception e) {
returnObject.setRedata("导出失败");
}
return returnObject;
}
private OutputStream tryCatch2gGetOutput(HttpServletResponse response) throws Exception{
try {
OutputStream output = response.getOutputStream();
response.reset();
String filename = "机电设备信息缺陷.xls";
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment;filename="
.concat(String.valueOf(URLEncoder.encode(filename, "UTF-8"))));
response.setHeader("Access-Control-Allow-Origin", "*");
response.setHeader("Cache-Control", "no-cache");
return output;
} catch (Exception e) {
throw new Exception("导出出错");
}
}
工具类
public static void exportExcel(HSSFWorkbook workbook,String sheetTitle, String[] headers,OutputStream out) {
/*
* 设置表头的样式
*/
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(0, sheetTitle);
// 设置表格默认列宽度为20个字节
sheet.setDefaultColumnWidth((short) 20);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
//上下左右边框
style.setBorderBottom(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(IndexedColors.BLACK.index);
font.setFontHeightInPoints((short) 14);
font.setFontName("黑体");
// 把字体应用到当前的样式
style.setFont(font);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); //背景色
/*
* 设置标题样式
*/
HSSFCellStyle titleStyle = workbook.createCellStyle(); //标题样式
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleStyle.setBorderBottom(BorderStyle.THIN);
titleStyle.setBorderTop(BorderStyle.THIN);
titleStyle.setBorderLeft(BorderStyle.THIN);
titleStyle.setBorderRight(BorderStyle.THIN);
HSSFFont ztFont = workbook.createFont();
ztFont.setItalic(false); // 设置字体为斜体字
ztFont.setColor(IndexedColors.BLACK.index); // 将字体设置为“黑色”
ztFont.setFontHeightInPoints((short)18); // 将字体大小设置为18px
ztFont.setFontName("宋体"); // 将“宋体”字体应用到当前单元格上
ztFont.setBold(true); //加粗
titleStyle.setFont(ztFont);
// 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
HSSFRow row2 = sheet.createRow(0);
row2.setHeightInPoints((short) 35);
// 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
HSSFCell cell2 = row2.createCell(0);
// 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length-1));
// 设置单元格内容
cell2.setCellValue("机电设备缺陷信息表");
cell2.setCellStyle(titleStyle);
// 产生表格标题行
HSSFRow row = sheet.createRow(1);
row.setHeightInPoints(23);//设置行高
sheet.setColumnWidth(0, 20*256);//设置列的宽度
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell((short) i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text.toString());
}
}
//下面代码是导出加上数据
// 遍历集合数据，产生数据行
//list对应的是类型List<List<String>> 此处这个list已经是所有的数据，一行数据是List<String>
if (list != null) {
int index = 2;//从第一几行开始
for (List<String> m : list) {
row = sheet.createRow(index);
int cellIndex = 0;
for (String str : m) {
HSSFCell cell = row.createCell((short) cellIndex);
if (str == null) {
str = "";
}
cell.setCellValue(str.toString());
cellIndex++;
}
index++;
}
}
效果图，此处只是展示模板的，如果要加数据，请看上方代码最后
复杂一些的
public static void exportExcelTemplate(HSSFWorkbook workbook, String sheetTitle, String[] headersFirst, String[] headersSecond, List<String> itemNameList, List<Integer> mergeCoumn) {
/*
* 设置表头的样式
*/
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(0, sheetTitle);
// 设置表格默认列宽度为20个字节
sheet.setDefaultColumnWidth((short) 20);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
// 上下左右边框
style.setBorderBottom(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(IndexedColors.BLACK.index);
font.setFontHeightInPoints((short) 12);
font.setFontName("宋体");
// 把字体应用到当前的样式
style.setFont(font);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); // 背景色
//第一层表头样式
// 生成一个样式
HSSFCellStyle firststyle = workbook.createCellStyle();
// 设置这些样式
firststyle.setAlignment(HorizontalAlignment.CENTER);
firststyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 上下左右边框
firststyle.setBorderBottom(BorderStyle.THIN);
firststyle.setBorderTop(BorderStyle.THIN);
firststyle.setBorderLeft(BorderStyle.THIN);
firststyle.setBorderRight(BorderStyle.THIN);
// 生成一个字体
HSSFFont firstfont = workbook.createFont();
firstfont.setColor(IndexedColors.BLACK.index);
firstfont.setFontHeightInPoints((short) 12);
firstfont.setFontName("宋体");
// 把字体应用到当前的样式
firststyle.setFont(firstfont);
firststyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
firststyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.index); // 背景色
/*
* 设置标题样式
*/
HSSFCellStyle titleStyle = workbook.createCellStyle(); // 标题样式
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
titleStyle.setBorderBottom(BorderStyle.DOTTED);
titleStyle.setBorderTop(BorderStyle.DOTTED);
titleStyle.setBorderLeft(BorderStyle.DOTTED);
titleStyle.setBorderRight(BorderStyle.DOTTED);
HSSFFont ztFont = workbook.createFont();
ztFont.setItalic(false); // 设置字体为斜体字
ztFont.setColor(IndexedColors.BLACK.index); // 将字体设置为“黑色”
ztFont.setFontHeightInPoints((short) 18); // 将字体大小设置为18px
ztFont.setFontName("宋体"); // 将“宋体”字体应用到当前单元格上
ztFont.setBold(true); // 加粗
titleStyle.setFont(ztFont);
// 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
HSSFRow row2 = sheet.createRow(0);
row2.setHeightInPoints((short) 35);
// 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
HSSFCell cell2 = row2.createCell(0);
// 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headersSecond.length - 1));
// 设置单元格内容
cell2.setCellValue("机电设备信息表");
cell2.setCellStyle(titleStyle);
// 产生表格第一行标题行
if (mergeCoumn != null && mergeCoumn.size() > 0){
int column = 0;
for (int i = 0;i<mergeCoumn.size();i++){
if (mergeCoumn.get(i) == 0){
if (i > 0){
// 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
sheet.addMergedRegion(new CellRangeAddress(1, 2, column, column));
column += 1;
}
}else{
if (i > 0 && mergeCoumn.get(i) != 1){
// 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
sheet.addMergedRegion(new CellRangeAddress(1, 1, column, column+mergeCoumn.get(i)-1));
column = column+mergeCoumn.get(i);
}else if (i > 0 && mergeCoumn.get(i) == 1){
column = column+mergeCoumn.get(i);
} else if (i == 0){
// 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
sheet.addMergedRegion(new CellRangeAddress(1, 1, column, mergeCoumn.get(i)-1));
column += mergeCoumn.get(i);
}
}
}
}
HSSFRow row3 = sheet.createRow(1);
row3.setHeightInPoints(20);// 设置行高
for (int i = 0; i < headersFirst.length; i++) {
if (i == 0){
HSSFCell cell3 = row3.createCell((short) 0);
cell3.setCellStyle(firststyle);
HSSFRichTextString text2 = new HSSFRichTextString(headersFirst[0]);
cell3.setCellValue(text2.toString());
}else {
int a = 0;
for (int i1 = 0; i1 < i; i1++) {
if (mergeCoumn.get(i1) == 0){
a=a+1;
}else {
a=mergeCoumn.get(i1)+a;
}
}
HSSFCell cell3 = row3.createCell((short) a);
cell3.setCellStyle(firststyle);
HSSFRichTextString text2 = new HSSFRichTextString(headersFirst[i]);
cell3.setCellValue(text2.toString());
}
}
// 产生表格第二行标题行
HSSFRow row = sheet.createRow(2);
row.setHeightInPoints(20);// 设置行高
sheet.setColumnWidth(0, 20 * 256);// 设置列的宽度
for (int i = 0; i < headersSecond.length; i++) {
HSSFCell cell = row.createCell((short) i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headersSecond[i]);
cell.setCellValue(text.toString());
}
/*
* 设置下拉选
*/
for (int i =0 ;i <headersSecond.length;i++){
String header = headersSecond[i];
String[] toBeStored = itemNameList.toArray(new String[itemNameList.size()]);
setHSSFValidation(sheet, toBeStored, 3,200,2,2);
}
}
效果图
