maven:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
poi工具类：
public class ExportExcelUtil {
// 声明一个工作薄
final HSSFWorkbook workbook = new HSSFWorkbook();
/**
* @param response HTTP响应
* @param excelName excel导出的文件名（注：时间戳在此方法中已经设置，无需在此参数中追加）
* @author chenqiuzhen
* @Description 导出到 excel
*/
public void export(HttpServletResponse response, String excelName, ExcelInfo<?>... excelInfos) throws Exception {
ServletOutputStream out = null;
String fileName = excelName + TimeHelper.format(new Date(), "yyyyMMddHHmmssS") + ".xls";
try {
// response.getOutputStream();出现异常，则需要重置一下response，然后重新设置ContentType
response.reset();
out = response.getOutputStream();
response.setContentType("application/x-msdownload;charset=utf-8");
response.addHeader("Content-disposition",
"attachment;filename=\"" + new String(fileName.getBytes("gb2312"), "iso8859-1") + "\"");
if (excelInfos != null && excelInfos.length > 0) {
for (ExcelInfo<?> excelInfo : excelInfos) {
excelInfo.setExportInfo();
}
}
// 导出到execl
workbook.write(out);
} finally {
if (out != null) {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (workbook != null) {
workbook.close();
}
}
}
/**
*
* @throws Exception
* @描述: 导出数据至本地excel文件
*/
public void toLocalFileExcel(String excelName, ExcelInfo<?>... excelInfos) throws Exception {
//这里为导出文件存放的路径 D:\tmp
String filePath ="D:\\tmp_" + UUID.randomUUID() + "\\";
//每次导出的时候，如果文件存在了，会将其覆盖掉，这里是保存所有的文件
File file = new File(filePath);
if (!file.exists()) {
file.mkdirs();
}
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
// 给要导出的文件起名为 "测试导出数据表_时间.xls"
String filePath2 = filePath + "测试导出数据表" + "_" + fmt.format(new Date()) + ".xls";
try {
if (excelInfos != null && excelInfos.length > 0) {
for (ExcelInfo<?> excelInfo : excelInfos) {
excelInfo.setExportInfo();
}
}
//输出Excel文件
FileOutputStream output=new FileOutputStream(filePath2);
workbook.write(output);//写入，到这里已经生成完成，可以在相应目录下找到刚才生成的文件
} catch (IOException e) {
e.printStackTrace();
} catch (JxlWriteException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
} finally {
if (workbook != null) {
workbook.close();
}
}
}
/**
* excel表单信息
*
* @param <T> 单条记录存储的类
* @author chenqz
* @Description excel表单信息
*/
public class ExcelInfo<T> {
private String sheetName;// 表格名称
private String[] titles;// 标题
private String[] paramName;// 取值名称：注意跟标题一一对应
private Integer[] columnWidth = new Integer[0];// 列宽：注意跟标题一一对应，可以不设置或设置指定列
private List<T> collection;// 数据集合
private List<String> appendTitle;//额外动态新增的表头
private String appendField;//对应实体里的字段名，目前只支持反射
public String getAppendField() {
return appendField;
}
public void setAppendField(String appendField) {
this.appendField = appendField;
}
public List<String> getAppendTitle() {
return appendTitle;
}
public void setAppendTitle(List<String> appendTitle) {
this.appendTitle = appendTitle;
}
//是否需要颜色
private boolean isColor;
private final Integer COLUMN_WIDTH = 6500;
/**
* 表格名称
**/
public String getSheetName() {
return sheetName;
}
/**
* 表格名称
**/
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
/**
* 标题
**/
public String[] getTitles() {
return titles;
}
/**
* 标题
**/
public void setTitles(String[] titles) {
this.titles = titles;
}
/**
* 取值名称：注意跟标题一一对应
**/
public String[] getParamName() {
return paramName;
}
/**
* 取值名称：注意跟标题一一对应
**/
public void setParamName(String[] paramName) {
this.paramName = paramName;
}
/**
* 数据集合
**/
public List<T> getCollection() {
return collection;
}
/**
* 数据集合
**/
public void setCollection(List<T> collection) {
this.collection = collection;
}
/**
* 列宽：注意跟标题一一对应，可以不设置或设置指定列
**/
public Integer[] getColumnWidth() {
return columnWidth;
}
/**
* 列宽：注意跟标题一一对应，可以不设置或设置指定列
**/
public void setColumnWidth(Integer[] columnWidth) {
this.columnWidth = columnWidth;
}
public boolean isColor() {
return isColor;
}
public void setColor(boolean color) {
isColor = color;
}
/**
* 设置导出信息
* @return
* @throws Exception
*/
protected HSSFWorkbook setExportInfo() throws Exception {
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(this.getSheetName());
// 设置列宽
for (int i = 0; i < this.getTitles().length; i++) {
if (columnWidth == null || columnWidth.length == 0 || columnWidth.length <= i) {
sheet.setColumnWidth(i, COLUMN_WIDTH);
} else if (columnWidth.length > i) {
int tempWidth = columnWidth[i] == null ? this.COLUMN_WIDTH : columnWidth[i];
sheet.setColumnWidth(i, tempWidth);
}
}
// 生成一个样式
HSSFCellStyle style = ExportUtils.createCellStyle(workbook);
// 生成一个字体
HSSFFont titleFont = workbook.createFont();
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 表头样式
HSSFCellStyle titlestyle = ExportUtils.createTitleStyle(workbook);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
boolean serFlg = false;
if(this.getParamName() != null && this.getParamName().length > 0) {
serFlg = this.getParamName()[0].equals("orderSer");
}
for (int j = 0; j < this.getTitles().length; j++) {
Cell cell = row.createCell(j);
cell.setCellStyle(titlestyle);
cell.setCellValue(this.getTitles()[j]);
}
if(StringUtil.isNotEmpty(this.getAppendField()) && CollectionUtil.isNotEmpty(this.getAppendTitle())){
for (int j = 0; j < this.getAppendTitle().size(); j++) {
Cell cell = row.createCell(j+this.getTitles().length);
cell.setCellStyle(titlestyle);
cell.setCellValue(this.getAppendTitle().get(j));
}
}
Iterator<T> it = this.getCollection().iterator();
int i = 0;
Class<?> classType = null;
while (it.hasNext()) {
row = sheet.createRow(i + 1);
T obj = it.next();
if(obj==null){
continue;
}
if (classType == null) {
classType = obj.getClass();
}
//创建cellStyle放于循环外，避免报错(java.lang.IllegalStateException: The maximum number of cell styles was exceeded)
CellStyle cellStyle = workbook.createCellStyle();
HSSFFont font=workbook.createFont();
font.setColor(HSSFColor.RED.index);
for (int j = 0; j < this.getParamName().length; j++) {
Cell cell = row.createCell(j);
cell.setCellStyle(style);
// 判断集合中核心数据的存储类型：Map 或者已知PO对象
if (obj instanceof Map) {
Map<?, ?> map = (Map<?, ?>) obj;
String vs = "--";
if(j == 0 && serFlg) {
vs =(i+1)+"";
}else {
vs = map.get(this.getParamName()[j]) == null ? "--" : map.get(this.getParamName()[j]).toString();
}
cell.setCellValue(vs);
//需要加颜色行定义此字段
boolean color = false;
if (StringUtil.isNotEmpty(map.get("isColor") + "")) {
color = Boolean.valueOf(map.get("isColor") + "");
}
if (this.isColor && color) {
//下面方法当数据量过多时，由于cellStyle创建过多导致报错，需要将创建cellStyle放置循环外面
//ExportUtils.cseteCellStyle(workbook,cell,HSSFColor.RED.index);
((HSSFCellStyle) cellStyle).setFont(font);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中
cellStyle.setWrapText(true);
HSSFDataFormat format = workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("@"));
cell.setCellStyle(cellStyle);
}
} else if (obj instanceof Date) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String format = sdf.format(obj);
cell.setCellValue(format);
} else {
Field field = classType.getDeclaredField(this.getParamName()[j]);
// 修改访问权限
field.setAccessible(true);
Object object = field.get(obj);
String str = "";
if (null != object) {
str = object.toString();
}
cell.setCellValue(str);
//需要加颜色行定义此字段
/*boolean color = false;
if (StringUtil.isNotEmpty(str) && field.getName().equals("isColor")) {
color = Boolean.valueOf(str);
}
if (this.isColor && color) {
ExportUtils.cseteCellStyle(workbook,cell,HSSFColor.RED.index);
}*/
}
}
if(StringUtil.isNotEmpty(this.getAppendField()) && CollectionUtil.isNotEmpty(this.getAppendTitle())){
Field field = classType.getDeclaredField(this.getAppendField());
// 修改访问权限
field.setAccessible(true);
Object object = field.get(obj);
List<String> list = null;
if (null != object) {
list = (List<String>) object;
}
for(int k=0;k<list.size();k++){
Cell cell = row.createCell(this.getParamName().length+k);
cell.setCellStyle(style);
cell.setCellValue(list.get(k));
}
}
i++;
}
return workbook;
}
}
}
Ajax请求导出Excel的问题描述：
前端发起Ajax请求get或post，后台使用Poi生成excel文件，最后用response输出excel文件流。整个调用过程都没有报任何错，excel文件也生成在本地，但浏览器没有弹出文件下载，毫无反应。
所以使用进行提交
<form method='post' action="${ctx}/............./specialtyDownload" id="specialtyDownloadExcel">
<input type="hidden" value="" id="phase" name="phase"/>
<input type="hidden" value="" id="specialtyCode" name="specialtyCode"/>
<input type="hidden" value="" id="lineCodes" name="lineCodes"/>
</form>
js代码：
$("#lineCodes").val(lineNoVar.join(','));//把数组转变为String
$("#specialtyCode").val(specialtyCode);
$("#phase").val(phase);
$("#specialtyDownloadExcel").submit();
controller代码：
@RequestMapping(value = "/specialtyDownload", method = {RequestMethod.POST,RequestMethod.GET})
public void specialtyDownload(
@RequestParam(value = "specialtyCode") String specialtyCode,
@RequestParam(value = "phase") String phase,
@RequestParam(value = "lineCodes", required = false) String lineCodes,
HttpServletRequest request,
HttpServletResponse response,
Model model
) {
String arr=null;
String[] strings = StringUtils.split(",");
List<String> lineNos=new ArrayList<>();
if (strings.length>0&&strings!=null){
lineNos= Arrays.asList(strings);
}
try {
//获取专业的数据
List<ProSpacingIndex> list = this.proSpacingIndexService.querySpacingIndexList(specialtyCode,Integer.parseInt(phase),lineNos);
String excelName = "专业导出数据";
ExportExcelUtil eu = new ExportExcelUtil();
ExportExcelUtil.ExcelInfo[] info = new ExportExcelUtil.ExcelInfo[2];
if (CollectionUtil.isNotEmpty(list)) {
//excel表头行的数组
String[] titles = new String[]{"1111", "2222", "3333", "44444", "55555"};
//传入集合中的类型所对应的字段属性
String[] paramName = new String[]{"aaa", "bbb", "ccc", "ddd", "eee"};
//创建excel表的sheet表
ExportExcelUtil.ExcelInfo excelInfo = eu.new ExcelInfo();
excelInfo.setParamName(paramName);
excelInfo.setSheetName("aa1");
excelInfo.setTitles(titles);
excelInfo.setCollection(list);
ExportExcelUtil.ExcelInfo excelInfo1 = eu.new ExcelInfo();
excelInfo1.setParamName(paramName);
excelInfo1.setSheetName("bb2");
excelInfo1.setTitles(titles);
excelInfo1.setCollection(list);
info[0] = excelInfo;
info[1] = excelInfo1;
}
//执行工具类的导出方法
eu.export(response, excelName, info);
} catch (Exception e) {
e.printStackTrace();
}
}
扫一扫
1110
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
