生成表格的工具类:
public class ExportExcelUtil {
private String title = "";
private String exportFileName = "";
private List<String> headers;
private List<List> data;
public void exportExcel(HttpServletResponse response) {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为20个字节
sheet.setDefaultColumnWidth(20);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.GOLD.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);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index);
// font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 指定当单元格内容显示不下时自动换行
style.setWrapText(true);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
OutputStream out = null;
try {
for (int i = 0; i < headers.size(); i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(
(String) headers.get(i));
cell.setCellValue(text);
}
// 遍历集合数据,产生数据行
if (data != null) {
for (int rowIndex = 0; rowIndex < data.size(); rowIndex++) {
row = sheet.createRow(rowIndex + 1);
List rowData = data.get(rowIndex);
for (int cellIndex = 0; cellIndex < rowData.size(); cellIndex++) {
HSSFCell cell = row.createCell(cellIndex);
// cell.setCellStyle(style2);
// String cellStr=(String)rowData.get(cellIndex);
String cellStr = rowData.get(cellIndex) + "";
HSSFRichTextString richString = new HSSFRichTextString(
cellStr == null ? "" : cellStr);
// HSSFFont font3 = workbook.createFont();
// font3.setColor(HSSFColor.BLUE.index);
// richString.applyFont(font3);
cell.setCellValue(richString);
}
}
}
response.setContentType("octets/stream");
response.addHeader("Content-Disposition", "attachment;filename="
+ exportFileName);
out = response.getOutputStream();
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getExportFileName() {
return exportFileName;
}
public void setExportFileName(String exportFileName) {
this.exportFileName = exportFileName;
}
public List<String> getHeaders() {
return headers;
}
public void setHeaders(List<String> headers) {
this.headers = headers;
}
public List<List> getData() {
return data;
}
public void setData(List<List> data) {
this.data = data;
}
}
调用工具类的方法
public String HistoryList() {
List history = this.service.orderList(vo);//查询数据库,返回list集合
ExportExcelUtil excel = new ExportExcelUtil();
List<String> headers = new ArrayList<String>();
headers.add("每一列的列名");//有多少列一次添加多少次
excel.setHeaders(headers);
List<List> data = new ArrayList<List>();
for (int i = 0; i < history.size(); i++) {
TradevmVmauditHistoryEntity entity = (TradevmVmauditHistoryEntity) history
.get(i);//将list中的值转化成具体的类
List list = new ArrayList();
list.add(entity.getDomain());//循环向list中添加数据,每一个list就是一行
data.add(list);//循环添加每一行
}
excel.setData(data);
excel.setExportFileName("shenhe.xls");
excel.setTitle("审核历史查询");
HttpServletResponse response = this.getResponse();
excel.exportExcel(response);
}
public class ExportExcelUtil {
private String title = "";
private String exportFileName = "";
private List<String> headers;
private List<List> data;
public void exportExcel(HttpServletResponse response) {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为20个字节
sheet.setDefaultColumnWidth(20);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.GOLD.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);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index);
// font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 指定当单元格内容显示不下时自动换行
style.setWrapText(true);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
OutputStream out = null;
try {
for (int i = 0; i < headers.size(); i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(
(String) headers.get(i));
cell.setCellValue(text);
}
// 遍历集合数据,产生数据行
if (data != null) {
for (int rowIndex = 0; rowIndex < data.size(); rowIndex++) {
row = sheet.createRow(rowIndex + 1);
List rowData = data.get(rowIndex);
for (int cellIndex = 0; cellIndex < rowData.size(); cellIndex++) {
HSSFCell cell = row.createCell(cellIndex);
// cell.setCellStyle(style2);
// String cellStr=(String)rowData.get(cellIndex);
String cellStr = rowData.get(cellIndex) + "";
HSSFRichTextString richString = new HSSFRichTextString(
cellStr == null ? "" : cellStr);
// HSSFFont font3 = workbook.createFont();
// font3.setColor(HSSFColor.BLUE.index);
// richString.applyFont(font3);
cell.setCellValue(richString);
}
}
}
response.setContentType("octets/stream");
response.addHeader("Content-Disposition", "attachment;filename="
+ exportFileName);
out = response.getOutputStream();
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getExportFileName() {
return exportFileName;
}
public void setExportFileName(String exportFileName) {
this.exportFileName = exportFileName;
}
public List<String> getHeaders() {
return headers;
}
public void setHeaders(List<String> headers) {
this.headers = headers;
}
public List<List> getData() {
return data;
}
public void setData(List<List> data) {
this.data = data;
}
}
调用工具类的方法
public String HistoryList() {
List history = this.service.orderList(vo);//查询数据库,返回list集合
ExportExcelUtil excel = new ExportExcelUtil();
List<String> headers = new ArrayList<String>();
headers.add("每一列的列名");//有多少列一次添加多少次
excel.setHeaders(headers);
List<List> data = new ArrayList<List>();
for (int i = 0; i < history.size(); i++) {
TradevmVmauditHistoryEntity entity = (TradevmVmauditHistoryEntity) history
.get(i);//将list中的值转化成具体的类
List list = new ArrayList();
list.add(entity.getDomain());//循环向list中添加数据,每一个list就是一行
data.add(list);//循环添加每一行
}
excel.setData(data);
excel.setExportFileName("shenhe.xls");
excel.setTitle("审核历史查询");
HttpServletResponse response = this.getResponse();
excel.exportExcel(response);
}