ssm项目导出excel表格
2023-09-14 09:14:53 时间
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.Collection;
import java.util.Iterator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.exception.ExceptionUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ExportExcel<T> {
private final static Logger log = Logger.getLogger(ExportExcel.class);
// 声明一个工作簿
private static HSSFWorkbook workbook = null;
// 生成一个表格
private static HSSFSheet sheet = null;
// 产生表格标题行
private static HSSFRow row = null;
// 声明一个画图的顶级管理器
private static HSSFPatriarch patriarch = null;
// 定义一个excel所容纳的初始数据量(防止数据过多,因为一个excel表格最多只能存65535行记录(excel2003的)),所以这里取40000
private static Integer initial_data = 40000;
// 累计遍历的数量,用来判断是否超过初始数据,如果超过则新建一个sheet
private int length = 0;
public void exportExcel(String headerName, Collection<T> dataset, HttpServletResponse response,HttpServletRequest request,String mimeType) {
try {
exportExcel(headerName, null,dataset,response,request,"yyyy-MM-dd",mimeType);
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
*
* @param headerName 文件名称
* @param headers 表格头部信息
* @param dataset 查到的数据集合
* @param response 响应response
* @param request 请求request
* @param mimeType content类型
*/
public void exportExcel(String headerName, String[] headers, Collection<T> dataset,HttpServletResponse response,HttpServletRequest request,String mimeType) {
try {
exportExcel(headerName, headers, dataset,response,request,"yyyy-MM-dd",mimeType);
} catch (Exception e) {
e.printStackTrace();
}
}
@SuppressWarnings("unchecked")
public void exportExcel(String title, String[] headers, Collection<T> dataset,HttpServletResponse response,HttpServletRequest request, String pattern,String mimeType)
throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException,
InvocationTargetException {
workbook = new HSSFWorkbook();
sheet = workbook.createSheet();
sheet.autoSizeColumn(1, true);// 自适应列宽度
HSSFFont font = workbook.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short)16);
row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
patriarch = sheet.createDrawingPatriarch();
// 遍历集合数据,产生数据行
Iterator<T> it = dataset.iterator();
int index = 0;
while (it.hasNext()) {
index++;
length++;
row = sheet.createRow(index);
T t = (T) it.next();
// 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
HSSFCell cell = row.createCell(i);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
// System.out.println("我执行到了!!!" + getMethodName);
Class cts = t.getClass();
// System.out.println("我执行到了" + cts.toString());
Method getMethod = cts.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
String textValue = null;
if (null != value) {
if (value instanceof Integer) {
int intValue = (Integer) value;
cell.setCellValue(intValue);
} else if (value instanceof Float) {
float fValue = (float) value;
textValue = Float.toString(fValue);
cell.setCellValue(textValue);
} else if (value instanceof Double) {
double dValue = (double) value;
cell.setCellValue(dValue);
} else if (value instanceof Long) {
long lValue = (long) value;
cell.setCellValue(lValue);
} else if (value instanceof byte[]) {
byte[] bValue = (byte[]) value;
// 有图片时设置行高为60px
row.setHeightInPoints(60);
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, index, (short) 6,
index);
anchor.setAnchorType(2);
patriarch.createPicture(anchor, workbook.addPicture(bValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
} else {
textValue = value.toString();
}
} else {
textValue = "";
}
if (textValue != null) {
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Matcher matcher = p.matcher(textValue);
if (matcher.matches()) {
// 是数字当做double处理
cell.setCellValue(Double.parseDouble(textValue));
} else {
HSSFRichTextString richString = new HSSFRichTextString(textValue);
cell.setCellValue(richString);
}
}
}
if (length % initial_data == 0) {
sheet = workbook.createSheet();
// 设置表格默认宽度为15个字节
sheet.setDefaultColumnWidth(15);
row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
index = 0;
}
}
}
try {
ServletOutputStream outputStream = response.getOutputStream();
//8.获取浏览器信息,对文件名进行重新编码
String fileName = FileUtils.filenameEncoding(title, request);
//9.设置信息头
response.setContentType(mimeType);
response.setHeader("Content-Disposition","attachment;filename="+fileName);
workbook.write(outputStream);
} catch (IOException e) {
log.error(ExceptionUtils.getStackTrace(e));
log.error("导出数据失败!!");
}
}
}
controller方法调用:
@RequestMapping(value="/export")
public void export(HttpServletRequest request, HttpServletResponse response) {
ExportExcel<AirlinesData> stuExcel = new ExportExcel<AirlinesData>();
String[] headers = {"出发","到达","日期","航班","仓位","价格","票数","备注","更新时间"};//表格的头部信息
List<AirlinesData> dataset = airlinesDataService.selectAll();//从数据库查到的数据
String mimeType = servletContext.getMimeType("航空信息表.xls");//生成servletcontext
Long date = new Date().getTime();
System.out.println("当前时间:"+date);
// 表格的文件名称 头部信息 数据来源 响应头 请求头 servletcontext
stuExcel.exportExcel("航空信息表.xls",headers,dataset,response,request,mimeType);
Long end = new Date().getTime();
System.out.println("耗时:"+(end-date));
}
相关文章
- C语言再学习 — 创建excel文件
- 导出的Excel名字乱码_恢复的excel文件乱码
- 基于微信小程序云开(统计学生信息并导出excel)4.0版(稳定版)
- Spring Boot框架下实现Excel服务端导入导出
- poi导出excel动态表头并合并
- 基于纯前端类Excel表格控件实现在线损益表应用
- Excel中如何批量重命名工作表与将每个工作表导出到单独Excel文件
- java处理Excel文件—excel文件的创建,删除,写入,读取详解编程语言
- 利用xlrd模块读取excel利用json模块生成相应的json文件的脚本详解编程语言
- 数据从Excel表格一路导入MySQL(excel表格导入mysql)
- excel数据分析:SPSS、MySQL和Excel(spssmysql)
- Oracle数据快速导出至Excel表格(oracle导出excel)
- Oracle与Excel联合提升数据分析挖掘能力(oracle与excel)
- C#从实体对象集合中导出Excel的代码
- phpexcel导出excel的颜色和网页中的颜色显示不一致
- GridView导出Excel实现原理与代码
- JavaScript将Table导出到Excel实现思路及代码
- JS将表单导出成EXCEL的实例代码
- 利用phpExcel实现Excel数据的导入导出(全步骤详细解析)
- header导出Excel应用示例
- c#读写excel文件使用示例
- winform导出dataviewgrid数据为excel的方法