如何将数据导出到Excel表格 :
实体对象
package com.cn;
public class Dome {
private String id;
private String name;
private String pid;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPid() {
return pid;
}
public void setPid(String pid) {
this.pid = pid;
}
}
导出代码
package com.cn;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.Set;
public class exportExcel{
/**
* 把对象list导出到excel
*
* @param newSets 需要导出的数据
* @param response
* @param sheetName 导出表格的sheet名称
* @param excelName excel表格名称
* @return
*/
private void excel(Set<Dome> newSets, HttpServletResponse response, String sheetName, String excelName) {
OutputStream ouputStream = null;
//开始设置excel表格相关内容↓↓↓
String[] arrayTitle = new String[]{"id", "name", "pid"};
//生成Excel文件
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet(sheetName);
//合并第一行,第二行
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, arrayTitle.length - 1));
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, arrayTitle.length - 1));
//设置表头样式(第一行)
CellStyle cellStyle = workbook.createCellStyle();
//强制使用POI样式
cellStyle.setWrapText(true);
//字体大小
XSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 14);
//是否加粗
font.setBold(true);
cellStyle.setFont(font);
//开始创建行
XSSFRow row = sheet.createRow(0);
//下标第0行的格式
row.setHeightInPoints(20);
XSSFCell cell = row.createCell(0);
cell.setCellValue("xx数据导出表");
cell.setCellStyle(cellStyle);
//下标第1行的格式
cellStyle = workbook.createCellStyle();
cellStyle.setWrapText(true);
row = sheet.createRow(1);
cell = row.createCell(0);
cell.setCellValue("导出时间:" + new Date(System.currentTimeMillis()).toString() + "操作人:");
font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 10);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
//设置标题栏样式
cellStyle = workbook.createCellStyle();
row = sheet.createRow(2);
//遍历创建标题栏
for (int i = 0; i < arrayTitle.length; i++) {
//顺便设置每一列的宽度
cell = row.createCell(i);
sheet.setColumnWidth(i, 20 * 180);
cell.setCellValue(arrayTitle[i]);
font = workbook.createFont();
font.setFontHeightInPoints((short) 12);
font.setFontName("Courier New");
font.setBold(true);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
}
ArrayList<Dome> list = new ArrayList<Dome>(newSets);
//给每个单元格赋值
for (int j = 0; j < list.size(); j++) {
if (null == list.get(j)) {
continue;
}
row = sheet.createRow(j + 3);
row.createCell((short) 0).setCellValue(list.get(j).getId() == null ? "" : list.get(j).getId());
row.createCell((short) 1).setCellValue(list.get(j).getName() == null ? "" : list.get(j).getName());
row.createCell((short) 2).setCellValue(list.get(j).getPid() == null ? "" : list.get(j).getPid());
}
try {
response.setHeader("Content-Disposition", "attachment; filename=" +
new String(excelName.getBytes("GBK"), "ISO8859-1") + ".xlsx");
ouputStream = response.getOutputStream();
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
workbook.write(ouputStream);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (ouputStream != null) {
try {
workbook.close();
} catch (IOException ioe) {
}
}
}
}
}