导出excel
package com.cardvalue.scm.util;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.List;
/**
* @author liouwb
* @date 2018-02-02
*/
public class ExcelFileUtil {
/**
* 生成Excel文件
*
* @param fileName 文件名
* @param data 数据
* @param response HttpServletResponse
*/
public static void export(String fileName, ExcelData data, HttpServletResponse response) throws Exception {
response.setContentType("application/force-download;charset=UTF-8");
response.addHeader("Content-Disposition", "attachment;fileName=" +
URLEncoder.encode(fileName, "UTF-8"));
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet(data.getName().isEmpty() ? "Sheet1" : data.getName());
writeTitles(wb, sheet, data.getTitles());
writeRows(wb, sheet, data.getRows());
try {
wb.write(response.getOutputStream());
} finally {
wb.close();
}
response.flushBuffer();
}
/**
* 添加标题
*
* @param wb Workbook
* @param sheet 标签
* @param titles 标题
*/
private static void writeTitles(XSSFWorkbook wb, XSSFSheet sheet, List<String> titles) {
XSSFFont titleFont = wb.createFont();
titleFont.setBold(true);
titleFont.setFontHeightInPoints((short) 14);
titleFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleStyle.setFont(titleFont);
XSSFRow row = sheet.createRow(0);
for (int i = 0; i < titles.size(); i++) {
Cell cell = row.createCell(i);
cell.setCellValue(titles.get(i));
cell.setCellStyle(titleStyle);
}
}
/**
* 添加数据
*
* @param wb Workbook
* @param sheet 标签
* @param rows 数据
*/
private static void writeRows(XSSFWorkbook wb, XSSFSheet sheet, List<List<Object>> rows) {
Font dataFont = wb.createFont();
dataFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle dataStyle = wb.createCellStyle();
dataStyle.setAlignment(HorizontalAlignment.CENTER);
dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
dataStyle.setFont(dataFont);
for (int i = 0; i < rows.size(); i++) {
List<Object> rowData = rows.get(i);
// 第二行开始写入数据
XSSFRow row = sheet.createRow(i + 1);
for (int j = 0; j < rowData.size(); j++) {
XSSFCell cell = row.createCell(j);
Object cellData = rowData.get(j);
if (cellData == null) {
cell.setCellValue("");
} else {
cell.setCellValue(cellData.toString());
}
cell.setCellStyle(dataStyle);
}
}
// 自动调整列宽
for (int i = 0; i < rows.get(0).size(); i++) {
int orgWidth = sheet.getColumnWidth(i);
sheet.autoSizeColumn(i, true);
int newWidth = sheet.getColumnWidth(i) + 100;
if (newWidth > orgWidth) {
sheet.setColumnWidth(i, newWidth);
} else {
sheet.setColumnWidth(i, orgWidth);
}
}
}
}
用到的ExcelData.java类
package com.cardvalue.scm.util;
import java.util.List;
/**
* @author liouwb
* @date 2018-02-02
*/
public class ExcelData {
// 页签名称
private String name;
// 表头
private List<String> titles;
// 数据
private List<List<Object>> rows;
public ExcelData() {
}
public ExcelData(String name, List<String> titles, List<List<Object>> rows) {
this.name = name;
this.titles = titles;
this.rows = rows;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<String> getTitles() {
return titles;
}
public void setTitles(List<String> titles) {
this.titles = titles;
}
public List<List<Object>> getRows() {
return rows;
}
public void setRows(List<List<Object>> rows) {
this.rows = rows;
}
}