maven pom.xml 导入poi依赖
controller方法:
/**
* 导出
* @param conditions
* @return
*/
@RequestMapping(value = "/export")
@ResponseBody
public ResponseData exportCheckList(QualityCheck conditions, String title, HttpServletResponse response) throws Exception {
List<Map<String, Object>> dataList = this.qualityCheckService.exportCheckList(conditions);
String title = "项目销售开单.xls";
String[] headers = {"表头1", "表头2"}; //表头
String[] names = {"title1", "title1"}; //映射数据库中的数据字段
String path = ExportExcelUtil.exportExcel(response, dataList , title, headers, names);
return ResponseData.success(0, "请求成功", path);
}
创建ExportExcelUtil工具类:
public static String exportExcel(HttpServletResponse response,
List<Map<String, Object>> datas,
String title,
String[] headers,
String[] names) throws Exception {
//获取跟目录
File file = null;
try {
file = new File( ResourceUtils.getURL("classpath:").getPath());
} catch (FileNotFoundException e) {
throw new RuntimeException("获取根目录失败,无法创建上传目录!");
}
if(!file.exists()) {
file = new File("");
}
String absolutePath = file.getAbsolutePath();
String resultPath = "/upload/" + "excel/" + title;
//存文件
File uploadFile = new File(absolutePath, "static" + resultPath);
if(!uploadFile.getParentFile().exists()) {
uploadFile.getParentFile().mkdirs();
}
FileOutputStream out = new FileOutputStream(uploadFile);
//写入数据
export(datas, title, headers, names, out);
return resultPath;
}
public static void export(List<Map<String, Object>> datas,
String title,
String[] headers,
String[] names,
OutputStream out) throws Exception {
try {
HttpServletRequest request = HttpContext.getRequest();
Workbook workbook = new SXSSFWorkbook(100); // 创建工作簿对象
Sheet sheet = workbook.createSheet("sheet1"); // 创建工作表
// 产生表格标题行
Row rowm = sheet.createRow(0);
Cell cellTiltle = rowm.createCell(0);
//sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
CellStyle columnTopStyle = getColumnTopStyle(workbook);//获取列头样式对象
CellStyle style = getStyle(workbook); //单元格样式对象
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (headers.length - 1)));//合并单元格
cellTiltle.setCellStyle(columnTopStyle);
String titleStr = title.substring(0, title.lastIndexOf("."));
cellTiltle.setCellValue(titleStr);
// 定义所需列数
int columnPro = headers.length;
Row rowRow = sheet.createRow(2); // 在索引2的位置创建行(excel的第3行)
//数据表头
for (int n = 0; n < columnPro; n++) {
Cell cellRowName = rowRow.createCell(n); //创建列头对应个数的单元格
cellRowName.setCellType(CellType.STRING); //设置列头单元格的数据类型
XSSFRichTextString text = new XSSFRichTextString(headers[n]);
cellRowName.setCellValue(text); //设置列头单元格的值
cellRowName.setCellStyle(columnTopStyle); //设置列头单元格样式
sheet.setColumnWidth(n, 4100);
}
//数据写入
for (int i = 0; i < datas.size(); i++) {
Object[] obj = new Object[names.length];
Map map = datas.get(i);//遍历每个对象
for (int a= 0; a<names.length; a++){
for(Object key : map.keySet()){
if (names[a].equals(key.toString())) {
if (map.get(key) != null) {
String value = String.valueOf(map.get(key));
obj[a] = value;
}else {
obj[a] = "";
}
}
}
}
Row row = sheet.createRow(i+ 3);//创建所需的行数
for (int j = 0; j < obj.length; j++) {
Cell cell = row.createCell(j, CellType.STRING);
if ( obj[j] != null) {
cell.setCellValue(obj[j].toString()); //设置单元格的值
}
cell.setCellStyle(style); //设置单元格样式
}
}
if (workbook != null) {
try {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
}
}
workbook.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
out.close();
}
}
/*
* 列头单元格样式
*/
public static CellStyle getColumnTopStyle(Workbook workbook) {
// 设置字体
Font font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short) 13);
//字体加粗
font.setBold(true);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
CellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom( BorderStyle.THIN);
//设置底边框颜色;
style.setBottomBorderColor( IndexedColors.BLACK.getIndex());
//设置左边框;
style.setBorderLeft(BorderStyle.THIN);
//设置左边框颜色;
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
//设置右边框;
style.setBorderRight(BorderStyle.THIN);
//设置右边框颜色;
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
//设置顶边框;
style.setBorderTop(BorderStyle.THIN);
//设置顶边框颜色;
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
/*
* 列数据信息单元格样式
*/
public static CellStyle getStyle(Workbook workbook) {
// 设置字体
Font font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short) 10);
//字体加粗
//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
CellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(BorderStyle.THIN);
//设置底边框颜色;
style.setBottomBorderColor(IndexedColors.BLACK.index);
//设置左边框;
style.setBorderLeft(BorderStyle.THIN);
//设置左边框颜色;
style.setLeftBorderColor(IndexedColors.BLACK.index);
//设置右边框;
style.setBorderRight(BorderStyle.THIN);
//设置右边框颜色;
style.setRightBorderColor(IndexedColors.BLACK.index);
//设置顶边框;
style.setBorderTop(BorderStyle.THIN);
//设置顶边框颜色;
style.setTopBorderColor(IndexedColors.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(true);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}