0 概述
在实际工作我们希望能够下载到相应数据表格,比如Excel形式,本文主要实现Excel 表格生成&下载。
1 依赖pom
<!-- ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.6</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.6</version>
</dependency>
2 代码实现
ExcelUtils 生成的工具类:
import com.google.common.collect.Lists;
import org.apache.poi.hssf.util.HSSFColor;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* Created by hsc
*/
public class ExcelUtils {
private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
public static HSSFWorkbook generateExcel(List<Map<String, Object>> data, List<String>
header) {
HSSFWorkbook book = new HSSFWorkbook();
try {
HSSFSheet sheet = book.createSheet("Sheet1");
HSSFCellStyle style = book.createCellStyle();
HSSFFont font = book.createFont();
setFont(font);
setStyle(style, font);
//填充表头header
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < header.size(); i++) {
String key = header.get(i);
HSSFCell cell = row.createCell(i);
cell.setCellValue(key);
}
//填充表格内容
for (int i = 0; i < data.size(); i++) {
HSSFRow row2 = sheet.createRow(i + 1);//index:第几行
Map<String, Object> inner = data.get(i);
List<Object> value = Lists.newArrayList(inner.values());
for (int j = 0; j < value.size(); j++) {
Object val = value.get(j);
HSSFCell cell = row2.createCell(j);//第几列:从0开始
cell.setCellValue(val.toString());
}
}
} catch (Exception ex) {
logger.error("generateExcel exception", ex);
}
return book;
}
private static void setStyle(HSSFCellStyle style, HSSFFont font) {
//样式设置
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFont(font);
}
private static void setFont(HSSFFont font) {
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
}
}
3 测试
测试Demo
@RequestMapping("/excel")
@ResponseBody
public void excel(@RequestParam(value = "id") Long id,HttpServletResponse httpServletResponse) {
List<String> header = Lists.newArrayList();
header.add("学号");
header.add("姓名");
Map<String,Object> map=new HashMap<>();
map.put("id",id);
map.put("name","小红");
HSSFWorkbook hssfWorkbook = ExcelUtils.generateExcel(Lists.newArrayList(map),header);
if (null != hssfWorkbook) {
try {
httpServletResponse.setHeader("Content-Disposition", "attachment; filename=appointmentUser.xls");
httpServletResponse.setContentType("application/vnd.ms-excel; charset=utf-8");
OutputStream out = httpServletResponse.getOutputStream();
hssfWorkbook.write(out);
out.flush();
out.close();
} catch (Exception ex) {
logger.error("关闭 hssfWorkbook 异常", ex);
}
}