原文:https://blog.csdn.net/java_yes/article/details/79787644
自己记录学习
POI简介
Apache POI是一种流行的API,它允许程序员使用Java程序创建,修改和显示MS Office文件。这由Apache软件基金会开发使用Java分布式设计或修改Microsoft Office文件的开源库。它包含类和方法对用户输入数据或文件到Office文档进行解码。
可操作文档类:
1. HSSF [1] - 提供读写Microsoft Excel XLS格式档案的功能。
2. XSSF [1] - 提供读写Microsoft Excel OOXML XLSX格式档案的功能。
3. HWPF [1] - 提供读写Microsoft Word DOC格式档案的功能。 (word)
4. HSLF [1] - 提供读写Microsoft PowerPoint格式档案的功能。 (PPT)
5. HDGF[1] - 提供读Microsoft Visio格式档案的功能。
6. HPBF [1] - 提供读Microsoft Publisher格式档案的功能。
7. HSMF [1] - 提供读Microsoft Outlook格式档案的功能。
下面我就说一下,如何使用HSSF导出Excel模板。
首先导入jar包
<!-- 说明: 当我们只要使用xls格式时、只要导入poi-version-yyyymmdd.jar就可以了。
当我们还要使用xlsx格式、还要导入poi-ooxml-version-yyyymmdd.jar。
至于poi-ooxml-schemas-version-yyyymmdd.jar这个jar基本不太会用到的,也做了配置。
当我们需要操作word、ppt、viso、outlook等时需要用到poi-scratchpad-version-yyyymmdd.jar,
没有做配置。 -->
<properties>
<poi.version>3.8</poi.version>
</properties>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>${poi.version}</version>
</dependency>
导入Jar包完成之后,写接口。
导出Excel_Controlle
@RequestMapping(value = "/excel/download", method = RequestMethod.GET)
@ApiOperation(value = "excel模板下载", notes = "excel模板下载", produces = "application/json")
public ModelAndView download() {
HttpServletRequest request = getRequest();
HttpServletResponse response = getResponse();
assetExcelService.download(request, response);
return null;
}
上面的Api使用的是SwaggerAPI,在博客中同样有记载。Springboot构建SwaggerApi。
实现类
@Override
public void download(HttpServletRequest request, HttpServletResponse response) {
try {
List<Map<String, String>> datas = new ArrayList<Map<String, String>>();//表头集合
Map<String, String> heads = new TreeMap<String, String>();//存放表头
List<ExcelHead> headers = new ArrayList<String>
heads.put("A", "标题A");
.
.(输入表头)
.
heads.put("Z", "标题Z");
datas.add(heads);
response.setHeader("Content-disposition", "attachment; filename=" + new String(("模板下载" + ".xls").getBytes("GB2312"), "ISO-8859-1"));
OutputStream outStream = response.getOutputStream();
Export export = new Export();
export.export(outStream, datas);
} catch (Exception e) {
e.printStackTrace();
}
}
export方法
public void export(OutputStream outputStream, List<Map<String, String>> data) throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
generateHeaderPrompt(workbook, sheet, data, 0);//excel第一行(0)设置提示
generateHeader(workbook, sheet, data);//设置表头样式
HSSFCellStyle style = getCellStyle(workbook, false);
for (int i = 1; i < data.size(); i++) {//从第一行开始设置表头
HSSFRow row = sheet.createRow(i + 1);
Map<String, String> map = data.get(i);
Set<string> keySet = map.keySet();
int columnIndex = 0;
for (String key : keySet) {
String value = map.get(key);
HSSFCell cell = row.createCell(columnIndex);
columnIndex++;
cell.setCellStyle(style);
cell.setCellValue(value);
}
}
workbook.write(outputStream);
outputStream.close();
}
generateHeaderPrompt方法
public void generateHeaderPrompt(HSSFWorkbook workbook, HSSFSheet sheet, List<Map<String, String>> headers,
int rowIndex) {
HSSFCellStyle style = getCellStyle(workbook, false);
Row row = sheet.createRow(rowIndex);
row.setHeightInPoints(130);
Cell cell = row.createCell(0);
cell.setCellValue(
"填写须知:\n" + "\n" + "1. ************\n"
+ "2. *********;\n" + "3. *********;\n" + "4. *********;\n"
+ "5. ********;\n" );
style.setWrapText(true);
cell.setCellStyle(style);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
Map<String, String> map = headers.get(0);
CellRangeAddress region = new CellRangeAddress(0, 0, 0, map.size() - 1);
sheet.addMergedRegion(region);
}
generateHeader方法
public void generateHeader(HSSFWorkbook workbook, HSSFSheet sheet, List<Map<String, String>> headers) {
HSSFCellStyle style = getCellStyle(workbook, true);
Row row = sheet.createRow(1);
row.setHeightInPoints(30);
Map<String, String> map = headers.get(0);
Set<String> keySet = map.keySet();
int cellIndex = 0;
for (Iterator<String> keyIterator = keySet.iterator(); keyIterator.hasNext();) {
String key = (String) keyIterator.next();
String header = map.get(key);
Cell cell = row.createCell(cellIndex);
sheet.setColumnWidth((cellIndex++), header.getBytes().length * 255);
cell.setCellValue(header);
cell.setCellStyle(style);
}
}
设置单元格样式
public HSSFCellStyle getCellStyle(HSSFWorkbook workbook, boolean isHeader) {
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_NONE);
style.setBorderLeft(HSSFCellStyle.BORDER_NONE);
style.setBorderRight(HSSFCellStyle.BORDER_NONE);
style.setBorderTop(HSSFCellStyle.BORDER_NONE);
style.setLocked(true);
if (isHeader) {// 表头样式加粗
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中对齐
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 10);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
}
return style;
}
OK,此时我们已经完成导出有表头,有填写提示的模板excel.
---------------------
作者:都让你们叫老了
来源:CSDN
原文:https://blog.csdn.net/java_yes/article/details/79787644