Skip to content

Instantly share code, notes, and snippets.

@thewindkee

thewindkee/ExcelView-ExcelExportUtil

Last active October 12, 2022 13:17
Show Gist options
  • Save thewindkee/6c1165e2e69c00ac7b3c0e7926e1fc5d to your computer and use it in GitHub Desktop.
Save thewindkee/6c1165e2e69c00ac7b3c0e7926e1fc5d to your computer and use it in GitHub Desktop.
Download ZIP
spring view:ExcelView
Raw
ExcelView-demo
@RequestMapping(params = "act=export")
public ModelAndView export(HttpServletRequest request) throws MemberServiceException {
Map<String, Object> map = get(request,true);
PageInfo<Record> page = (PageInfo<Record>) map.get(PAGE);
List<Record> list = page.getList();
ArrayList<Column> columns = Lists.newArrayList();
columns.add(new Column("注销编号", "id"));
columns.add(new Column("账号", "mid"));
columns.add(new Column("注销时间", "createTime"));
columns.add(new Column<Record>("来源", "source"){
@Override
public Object getFieldValue(Record bean) {
switch (bean.getSource()) {
case ADMIN:
return "后台操作";
case USER:
return "用户申请";
default:
return "未知来源";
}
}
});
columns.add(new Column<Record>("状态", "status"){
@Override
public Object getFieldValue(Record bean) {
return bean.getStatus().getName();
}
});
columns.add(new Column("备注", "extra"));
columns.add(new Column("操作人", "operator"));
return new ModelAndView(Constants.EXCEL_VIEW, EXCEL_CONTENT, new ExcelContent("账号注销记录.xls",list, columns));
Raw
ExcelView-ExcelExportUtil
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* thanks @author:shq
*/
public class ExcelExportUtil {
private static final int WIDTH_UNIT = 400;
private static final String DEFAULT_ENCODING = "GBK";
public static void setResponseHeader(String fileNamePrefix, HttpServletResponse response)
throws UnsupportedEncodingException {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
String fileName = fileNamePrefix + sdf.format(new Date()).toString() + ".xls";
response.setContentType("application/ms-txt.numberformat:@");
response.setCharacterEncoding(DEFAULT_ENCODING);
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "max-age=30");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder
.encode(fileName, DEFAULT_ENCODING));
}
/*
* 列头单元格样式
*/
public static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short)11);
//字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
/*
* 列数据信息单元格样式
*/
public static HSSFCellStyle getStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
//font.setFontHeightInPoints((short)10);
//字体加粗
//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
public static HSSFSheet createSheet(HSSFWorkbook workbook,String title, String[] rowName, List<Object[]> dataList,int tilteLastCol,boolean special){
HSSFSheet sheet = null; // 创建工作表
if (title != null) {
sheet = workbook.createSheet(title);
}else{
sheet = workbook.createSheet();
}
try{
int dataStartRow = 0;
// 产生表格标题行
HSSFCellStyle columnTopStyle = getColumnTopStyle(workbook);//获取列头样式对象
HSSFCellStyle style = getStyle(workbook); //单元格样式对象
if (title != null) {
HSSFRow rowm = sheet.createRow(0);
HSSFCell cellTiltle = rowm.createCell(0);
//sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, tilteLastCol));
cellTiltle.setCellStyle(columnTopStyle);
cellTiltle.setCellValue(title);
dataStartRow=2;
}
// 定义所需列数
int columnNum = rowName.length;
HSSFRow rowRowName = sheet.createRow(dataStartRow++); // 在索引2的位置创建行(最顶端的行开始的第二行)
// 将列头设置到sheet的单元格中
for(int n=0;n<columnNum;n++){
HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
HSSFCell cellRowName;
if(special){
cellRowName = mergedRegion(n,sheet,rowRowName);
}else {
cellRowName = rowRowName.createCell(n); //创建列头对应个数的单元格
}
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); //设置列头单元格的数据类型
cellRowName.setCellValue(text); //设置列头单元格的值
cellRowName.setCellStyle(columnTopStyle); //设置列头单元格样式
}
//将查询出的数据设置到sheet对应的单元格中
for(int i=0;i<dataList.size();i++){
Object[] obj = dataList.get(i);//遍历每个对象
HSSFRow row = sheet.createRow(i+dataStartRow);//创建所需的行数
for(int j=0; j<obj.length; j++){
HSSFCell cell = null; //设置单元格的数据类型
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(obj[j] != null){
if (obj[j] instanceof Date) {
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String value = sf.format((Date)obj[j]);
cell.setCellValue(value);
} else if (obj[j] instanceof Float || obj[j] instanceof Double || obj[j] instanceof Integer){
cell.setCellValue(Double.parseDouble(obj[j].toString()));
} else {
cell.setCellValue(obj[j].toString()); //设置单元格的值
}
} else {
cell.setCellValue("");
}
cell.setCellStyle(style); //设置单元格样式
}
}
//让列宽随着导出的列长自动适应
for (int colNum = 0; colNum < tilteLastCol; colNum++) {
int columnWidth = sheet.getColumnWidth(colNum) / WIDTH_UNIT;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
HSSFRow currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(colNum) != null) {
HSSFCell currentCell = currentRow.getCell(colNum);
if (null != currentCell) {
if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
int length = 0;
if (null == currentCell.getRichStringCellValue()) {
}
if (null != currentCell.getStringCellValue()) {
length = currentCell.getStringCellValue().getBytes().length;
}
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
}
if(colNum == 0){
sheet.setColumnWidth(colNum, (columnWidth-2) * WIDTH_UNIT);
}else{
sheet.setColumnWidth(colNum, (columnWidth+4) * WIDTH_UNIT);
}
}
}catch(Exception e){
e.printStackTrace();
}
return sheet;
}
private static HSSFCell mergedRegion(int n,HSSFSheet sheet, HSSFRow rowRowName){
HSSFCell cell =null;
switch (n){
case 0:
cell = rowRowName.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
break;
case 1:
cell = rowRowName.createCell(1);
sheet.addMergedRegion(new CellRangeAddress(2, 2, 1, 2));
break;
case 2:
cell = rowRowName.createCell(3);
sheet.addMergedRegion(new CellRangeAddress(2, 2, 3, 4));
break;
case 3:
cell = rowRowName.createCell(5);
sheet.addMergedRegion(new CellRangeAddress(2, 2, 5, 6));
break;
default:
}
return cell;
}
public static void main(String[] args) throws Exception {
String title ="VIP每周晋升数据";
String[] rowsName = new String[]{"SVIP等级","当月数据","上月数据","涨幅对比"};
List<Object[]> dataList = new ArrayList<Object[]>();
Object[] objs = new Object[7];
objs[0] = "1";
objs[1] = "付费数";
objs[2] = "充值额";
objs[3] = "付费数";
objs[4] = "充值额";
objs[5] = "付费数对比";
objs[6] = "充值额对比";
dataList.add(objs);
Object[] objs1 = new Object[7];
objs1[0] = "SVIP1";
objs1[1] = "1";
objs1[2] = "2";
objs1[3] = "3";
objs1[4] = "4";
objs1[5] = "5";
objs1[6] = "6";
dataList.add(objs1);
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = createSheet(workbook,title,rowsName,dataList,6,true);
File file = new File("F:\\Test\\test.xls");
if (!file.exists()) {
file.createNewFile();
}
OutputStream outputStream = new FileOutputStream(file);
try
{
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
catch (IOException e)
{
e.printStackTrace();
}
}
}
Raw
ExcelView-ExcelView
import Constants;
import ExcelExportUtil;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.util.ReflectionUtils;
import org.springframework.web.servlet.view.document.AbstractExcelView;
/**
* 推荐使用方法：
* List<Record> list = page.getList();
* ArrayList<Column> columns = Lists.newArrayList();
* columns.add(new Column("注销编号", "id"));
* columns.add(new Column("账号", "mid"));
* columns.add(new Column("注销时间", "createTime"));
* columns.add(new Column<Record>("来源", "source"){
* @Override
* public Object getFieldValue(Record bean) {
* return bean.getSource().toString();
* }
* });
* columns.add(new Column<Record>("状态", "status"){
* @Override
* public Object getFieldValue(Record bean) {
* return bean.getStatus().toString();
* }
* });
* columns.add(new Column("备注", "extra"));
* columns.add(new Column("操作人", "operator"));
* ExcelContent excelContent = new ExcelContent("数据.xls",list, columns);
* excelContent.setTitle("title");
*
*
* //ExcelContent excelContent = ExcelContentBuilder.anExcelContent().beans(list).columns(columns)
* // .fileName("数据xx.xls").build();
* @author thewindkee
* @Date 2019年7月25日14:45:46
*/
public class ExcelView extends AbstractExcelView implements Constants {
@Override
protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
HttpServletResponse response) throws Exception {
ExcelView.ExcelContent excelContent = (ExcelView.ExcelContent) model.get(Constants.EXCEL_CONTENT);
ExcelExportUtil.createSheet(workbook, excelContent.getTitle(), excelContent.getRowsName(), excelContent.getDataList(), excelContent.getRowsName().length, false);
response.setHeader("Content-Disposition", "attachment; filename=\""+URLEncoder.encode(excelContent.getFileName(), "UTF-8")+"\"");
}
public static class ExcelContent<T> {
private String fileName;
private String title;
private String [] rowsName;
private String [] fieldsName;
private List<Object[]> dataList ;
private List<T> beans;
private List<Column<T>> columns;
public ExcelContent(String fileName) {
this.fileName = fileName;
}
/**
* 直接将beans与columns组合转化成rowName和dataList
*/
public ExcelContent(String fileName,List<T> beans, List<Column<T>> columns) {
this.fileName = fileName;
this.beans = beans;
setColumns(columns,beans );
}
public ExcelContent(String fileName, String[] rowsName, List<Object[]>dataList) {
this.fileName = fileName;
this.rowsName = rowsName;
this.dataList = dataList;
}
private List<T> getBeans() {
return beans;
}
public void setBeans(List<T> beans) {
this.beans = beans;
if (columns != null) {
setColumns(columns, beans);
}
}
public void setColumns(List<Column<T>> columns) {
this.columns = columns;
if (beans != null) {
setColumns(columns,beans );
}
}
List<Column<T>> getColumns() {
return columns;
}
private void setColumns(List<Column<T>> columns, List<T> beans) {
this.columns = columns;
if (columns != null) {
int size = columns.size();
rowsName = new String[size];
fieldsName = new String[size];
dataList=new ArrayList<Object[]>();
Column<T> column = null;
Object[] row = null;
for (T t : beans) {
row = new Object[size];
for (int i = 0; i < size; i++) {
column = columns.get(i);
rowsName[i] = column.columnTitle;
fieldsName[i] = column.fieldName;//仅用于记录
row[i] = column.getFieldValue(t);
}
dataList.add( row);
}
}
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
public String[] getRowsName() {
return rowsName;
}
public void setRowsName(String[] rowsName) {
this.rowsName = rowsName;
}
public String[] getFieldsName() {
return fieldsName;
}
public void setFieldsName(String[] fieldsName) {
this.fieldsName = fieldsName;
}
public List<Object[]> getDataList() {
return dataList;
}
public void setDataList(List<Object[]> dataList) {
this.dataList = dataList;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
}
public static class Column<T> {
private String columnTitle;
private String fieldName;
public Column(String columnTitle, String fieldName) {
this.columnTitle = columnTitle;
this.fieldName = fieldName;
}
public Object getFieldValue(T bean) {
if (bean == null) {
return null;
}else{
Field field = ReflectionUtils.findField(bean.getClass(), this.fieldName);
field.setAccessible(true);
Object fieldValue = ReflectionUtils.getField(field, bean);
if (fieldValue instanceof Date) {
return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date) fieldValue);
}else{
return fieldValue;
}
}
}
}
public static final class ExcelContentBuilder<T> {
private String fileName;
private String title;
private String [] rowsName;
private String [] fieldsName;
private List<T[]> dataList ;
private List<T> beans;
private List<ExcelView.Column<T>> columns;
private ExcelContentBuilder() {
}
public static ExcelContentBuilder anExcelContent() {
return new ExcelContentBuilder();
}
public ExcelContentBuilder fileName(String fileName) {
this.fileName = fileName;
return this;
}
public ExcelContentBuilder rowsName(String[] rowsName) {
this.rowsName = rowsName;
return this;
}
public ExcelContentBuilder fieldsName(String[] fieldsName) {
this.fieldsName = fieldsName;
return this;
}
public ExcelContentBuilder beans(List<T> beans) {
this.beans = beans;
return this;
}
public ExcelContentBuilder columns(List<Column<T>> columns) {
this.columns = columns;
return this;
}
public ExcelContentBuilder dataList(List<T[]> dataList) {
this.dataList = dataList;
return this;
}
public ExcelContentBuilder title(String title) {
this.title = title;
return this;
}
public ExcelContent<T> build() {
ExcelContent excelContent = new ExcelContent(fileName);
if (rowsName != null) {
excelContent.setRowsName(rowsName);
}
if (fieldsName != null) {
excelContent.setFieldsName(fieldsName);
}
if (dataList != null) {
excelContent.setDataList(dataList);
}
if (title != null) {
excelContent.setTitle(title);
}
if (beans != null) {
excelContent.setBeans(beans);
}
if (columns != null) {
excelContent.setColumns(columns);
}
checkParams(excelContent);
return excelContent;
}
private void checkParams(ExcelContent excelContent) {
if (excelContent.fileName == null) {
throw new IllegalArgumentException("fileName must not null!");
} else if (excelContent.rowsName == null) {
excelContent.rowsName = excelContent.fieldsName;
if (excelContent.fieldsName == null || excelContent.fieldsName.length == 0) {
throw new IllegalArgumentException("fieldsName must not empty!");
}
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment