1.table直接导出到Excel
网上看到的直接从table导出到Excel的方法,只需要页面和js交互即可完成。但测试了一下发现对数据量特别大的表格导出会失败。但对小数据量的导出还是很方便的。
1.页面
<input type="button" value="导出到Excel" onclick="getImport('table2Excel')" />
<table id="table2Excel">
<tr>
<td>第一行第一列</td>
<td>第一行第二列</td>
</tr>
<tr>
<td>第二行第一列</td>
<td>第二行第二列</td>
</tr>
</table>
2.js
方法一
此方法为ie导出之后,不保留table格式的方法
//导出通用方法,在onlick事件中调用getImport(tableID)即可
var idTmr;
function getExplorer() {
var explorer = window.navigator.userAgent;
if (explorer.indexOf("MSIE") >= 0
|| (explorer.indexOf("Windows NT 6.1;") >= 0 && explorer
.indexOf("Trident/7.0;") >= 0)) {
return 'ie';
} else if (explorer.indexOf("Firefox") >= 0) {
return 'Firefox';
} else if (explorer.indexOf("Chrome") >= 0) {
return 'Chrome';
} else if (explorer.indexOf("Opera") >= 0) {
return 'Opera';
} else if (explorer.indexOf("Safari") >= 0) {
return 'Safari';
}
}
function getIEnotsink(tableID) {
var curTbl = document.getElementById(tableid);
if (curTbl == null || curTbl == "") {
return false;
}
var oXL;
try {
oXL = new ActiveXObject("Excel.Application"); //创建AX对象excel
} catch (e) {
alert("无法启动Excel!\n\n如果您确信您的电脑中已经安装了Excel,"
+ "那么请调整IE的安全级别。\n\n具体操作:\n\n"
+ "工具 → Internet选项 → 安全 → 自定义级别 → 对没有标记为安全的ActiveX进行初始化和脚本运行 → 启用");
return false;
}
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
var Lenr = curTbl.rows.length;
for (i = 0; i < Lenr; i++) {
var Lenc = curTbl.rows(i).cells.length;
for (j = 0; j < Lenc; j++) {
oSheet.Cells(i + 1, j + 1).value = curTbl.rows(i).cells(j).innerText;
}
}
oXL.Visible = true;
}
function getImport(tableID) {
if (getExplorer() == 'ie') {
getIEnotsink(tableID);
} else {
tableToExcel(tableID);
}
}
function Cleanup() {
window.clearInterval(idTmr);
CollectGarbage();
}
var tableToExcel = (function() {
var uri = 'data:application/vnd.ms-excel;base64,', template = '<html><head><meta charset="UTF-8"></head><body><table border="1">{table}</table></body></html>', base64 = function(
s) {
return window.btoa(unescape(encodeURIComponent(s)))
}, format = function(s, c) {
return s.replace(/{(\w+)}/g, function(m, p) {
return c[p];
})
}
return function(table, name) {
if (!table.nodeType)
table = document.getElementById(table)
var ctx = {
worksheet : name || 'Worksheet',
table : table.innerHTML
}
window.location.href = uri + base64(format(template, ctx))
}
})()
方法二
此方法为ie导出之后,可以保留table格式的方法
//导出通用方法,在onlick事件中调用getImport(tableID)即可
var idTmr;
function getExplorer() {
var explorer = window.navigator.userAgent;
if (explorer.indexOf("MSIE") >= 0
|| (explorer.indexOf("Windows NT 6.1;") >= 0 && explorer
.indexOf("Trident/7.0;") >= 0)) {
return 'ie';
} else if (explorer.indexOf("Firefox") >= 0) {
return 'Firefox';
} else if (explorer.indexOf("Chrome") >= 0) {
return 'Chrome';
} else if (explorer.indexOf("Opera") >= 0) {
return 'Opera';
} else if (explorer.indexOf("Safari") >= 0) {
return 'Safari';
}
}
function getIEsink(tableID) {
var curTbl = document.getElementById(tableid);
if (curTbl == null || curTbl == "") {
return false;
}
var oXL;
try {
oXL = new ActiveXObject("Excel.Application"); //创建AX对象excel
} catch (e) {
alert("无法启动Excel!\n\n如果您确信您的电脑中已经安装了Excel,"
+ "那么请调整IE的安全级别。\n\n具体操作:\n\n"
+ "工具 → Internet选项 → 安全 → 自定义级别 → 对没有标记为安全的ActiveX进行初始化和脚本运行 → 启用");
return false;
}
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
var sel = document.body.createTextRange();
sel.moveToElementText(curTbl);
sel.select;
sel.execCommand("Copy");
oSheet.Paste();
oXL.Visible = true;
}
function getImport(tableID) {
if (getExplorer() == 'ie') {
getIEsink(tableID);
} else {
tableToExcel(tableID);
}
}
function Cleanup() {
window.clearInterval(idTmr);
CollectGarbage();
}
var tableToExcel = (function() {
var uri = 'data:application/vnd.ms-excel;base64,', template = '<html><head><meta charset="UTF-8"></head><body><table border="1">{table}</table></body></html>', base64 = function(
s) {
return window.btoa(unescape(encodeURIComponent(s)))
}, format = function(s, c) {
return s.replace(/{(\w+)}/g, function(m, p) {
return c[p];
})
}
return function(table, name) {
if (!table.nodeType)
table = document.getElementById(table)
var ctx = {
worksheet : name || 'Worksheet',
table : table.innerHTML
}
window.location.href = uri + base64(format(template, ctx))
}
})()
2.POI导出到Excel
1.需要的jar
<!-- 导出Excel -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.ant</groupId>
<artifactId>ant</artifactId>
<version>1.9.7</version>
</dependency>
2.工具类1-公共
package com.test.util;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
/**
* EXCEL报表工具类.
*
* @author peter
*/
public class ExportExcel {
private HSSFWorkbook wb = null;
private HSSFSheet sheet = null;
/**
* @param wb
* @param sheet
*/
public ExportExcel(HSSFWorkbook wb, HSSFSheet sheet) {
this.wb = wb;
this.sheet = sheet;
}
/**
* 创建通用EXCEL头部
*
* @param headString 头部显示的字符
* @param colSum 该报表的列数
*/
@SuppressWarnings("unused")
public void createNormalHead(String headString, int colSum) {
HSSFRow row = sheet.createRow(0);
// 设置列宽
sheet.setColumnWidth(0, 7000);
sheet.setColumnWidth(1, 5000);
sheet.setColumnWidth(2, 3000);
// 设置第一行
HSSFCell cell = row.createCell(0);
// row.setHeight((short) 1000);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.ENCODING_UTF_16);// 中文处理
cell.setCellValue(new HSSFRichTextString(headString));
// 指定合并区域
/**
* public Region(int rowFrom, short colFrom, int rowTo, short colTo)
*/
sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) colSum));
// 定义单元格格式,添加单元格表样式,并添加到工作簿
HSSFCellStyle cellStyle = wb.createCellStyle();
// 设置单元格水平对齐类型
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
cellStyle.setWrapText(true);// 指定单元格自动换行
// 设置单元格字体
HSSFFont font = wb.createFont();
// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// font.setFontName("宋体");
// font.setFontHeight((short) 600);
// cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
}
/**
* 创建通用报表第二行
*
* @param params 统计条件数组
* @param colSum 需要合并到的列索引
*/
public void createNormalTwoRow(String[] params, int colSum) {
// 创建第二行
HSSFRow row1 = sheet.createRow(1);
row1.setHeight((short) 400);
HSSFCell cell2 = row1.createCell(0);
cell2.setCellType(HSSFCell.ENCODING_UTF_16);
cell2.setCellValue(new HSSFRichTextString("时间:" + params[0] + "至" + params[1]));
// 指定合并区域
/**
* public Region(int rowFrom, short colFrom, int rowTo, short colTo)
*/
sheet.addMergedRegion(new Region(1, (short) 0, 1, (short) colSum));
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
cellStyle.setWrapText(true);// 指定单元格自动换行
// 设置单元格字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 250);
cellStyle.setFont(font);
cell2.setCellStyle(cellStyle);
}
/**
* 设置报表标题
*
* @param columHeader 标题字符串数组
*/
public void createColumHeader(String[] columHeader) {
// 设置列头 在第三行
HSSFRow row2 = sheet.createRow(2);
// 指定行高
row2.setHeight((short) 600);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
cellStyle.setWrapText(true);// 指定单元格自动换行
// 单元格字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 250);
cellStyle.setFont(font);
// 设置单元格背景色
cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
HSSFCell cell3 = null;
for (int i = 0; i < columHeader.length; i++) {
cell3 = row2.createCell(i);
cell3.setCellType(HSSFCell.ENCODING_UTF_16);
cell3.setCellStyle(cellStyle);
cell3.setCellValue(new HSSFRichTextString(columHeader[i]));
}
}
/**
* 创建内容单元格
*
* @param wb HSSFWorkbook
* @param row HSSFRow
* @param col short型的列索引
* @param align 对齐方式
* @param val 列值
*/
public void cteateCell(HSSFWorkbook wb, HSSFRow row, int col, short align, String val) {
HSSFCell cell = row.createCell(col);
cell.setCellType(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(new HSSFRichTextString(val));
HSSFCellStyle cellstyle = wb.createCellStyle();
cellstyle.setAlignment(align);
cell.setCellStyle(cellstyle);
}
/**
* 创建合计行
*
* @param colSum 需要合并到的列索引
* @param cellValue
*/
public void createLastSumRow(int colSum, String[] cellValue) {
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
cellStyle.setWrapText(true);// 指定单元格自动换行
// 单元格字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 250);
cellStyle.setFont(font);
// 获取工作表最后一行
HSSFRow lastRow = sheet.createRow((short) (sheet.getLastRowNum() + 1));
HSSFCell sumCell = lastRow.createCell(0);
sumCell.setCellValue(new HSSFRichTextString("合计"));
sumCell.setCellStyle(cellStyle);
// 合并 最后一行的第零列-最后一行的第一列
sheet.addMergedRegion(new Region(sheet.getLastRowNum(), (short) 0, sheet.getLastRowNum(), (short) colSum));// 指定合并区域
for (int i = 2; i < (cellValue.length + 2); i++) {
// 定义最后一行的第三列
sumCell = lastRow.createCell(i);
sumCell.setCellStyle(cellStyle);
// 定义数组 从0开始。
sumCell.setCellValue(new HSSFRichTextString(cellValue[i - 2]));
}
}
/**
* 输入EXCEL文件
*
* @param fileName 文件名
*/
public void outputExcel(String fileName) {
FileOutputStream fos = null;
try {
fos = new FileOutputStream(new File(fileName));
wb.write(fos);
fos.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* @return the sheet
*/
public HSSFSheet getSheet() {
return sheet;
}
/**
* @param sheet the sheet to set
*/
public void setSheet(HSSFSheet sheet) {
this.sheet = sheet;
}
/**
* @return the wb
*/
public HSSFWorkbook getWb() {
return wb;
}
/**
* @param wb the wb to set
*/
public void setWb(HSSFWorkbook wb) {
this.wb = wb;
}
}
3.工具类2-导出
package com.test.util;
import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
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 ReportUtil {
@SuppressWarnings("static-access")
public static void report(List<Object> dataList, FileOutputStream output) throws IOException {
BufferedOutputStream bos = new BufferedOutputStream(output);
// 定义单元格报头
String worksheetTitle = "数据报表";
HSSFWorkbook wb = new HSSFWorkbook();
// 创建单元格样式
HSSFCellStyle cellStyleTitle = wb.createCellStyle();
// 指定单元格居中对齐
cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 指定单元格垂直居中对齐
cellStyleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 指定当单元格内容显示不下时自动换行
cellStyleTitle.setWrapText(true);
// ------------------------------------------------------------------
HSSFCellStyle cellStyle = wb.createCellStyle();
HSSFCellStyle contextstyle = wb.createCellStyle();
// 指定单元格居中对齐
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
contextstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 指定单元格垂直居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
contextstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 指定当单元格内容显示不下时自动换行
cellStyle.setWrapText(true);
contextstyle.setWrapText(true);
// ------------------------------------------------------------------
// 设置单元格字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 200);
cellStyleTitle.setFont(font);
HSSFSheet sheet = wb.createSheet();
ExportExcel exportExcel = new ExportExcel(wb, sheet);
// 创建报表头部(跨三列)
exportExcel.createNormalHead(worksheetTitle, 3);
// 定义第一行
HSSFRow row1 = sheet.createRow(1);
for (int i = 0; i < 10; i++) {
HSSFCell cell = row1.createCell(i);
cell.setCellStyle(cellStyleTitle);
switch (i) {
case 0:
cell.setCellValue(new HSSFRichTextString(Constant.rowTitle1));
break;
case 1:
cell.setCellValue(new HSSFRichTextString(Constant.rowTitle2));
break;
case 2:
cell.setCellValue(new HSSFRichTextString(Constant.rowTitle3));
break;
}
}
// 定义第二行
HSSFRow row = sheet.createRow(2);
HSSFCell cell = row.createCell(0);
CloundAccountResponse cloudAccount = new CloundAccountResponse();
for (int i = 0; i < allAccount.size(); i++) {
cloudAccount = allAccount.get(i);
row = sheet.createRow(i + 2);
cell = row.createCell(0);
cell.setCellStyle(cellStyle);
cell.setCellValue(new HSSFRichTextString(dataList.getAttr1()));
cell = row.createCell(1);
cell.setCellStyle(cellStyle);
cell.setCellValue(new HSSFRichTextString(cloudAccount.getAttr2()));
cell = row.createCell(2);
HSSFDataFormat df = wb.createDataFormat(); // 此处设置数据格式为数值类型
contextstyle.setDataFormat(df.getBuiltinFormat("#,##0"));//整数
//contextstyle.setDataFormat(df.getBuiltinFormat("#,##0.00"));//保留两位小数
cell.setCellValue(Double.parseDouble(String.valueOf(cloudAccount.getAttr3())));
cell.setCellStyle(contextstyle);
}
try {
bos.flush();
wb.write(bos);
} catch (IOException e) {
e.printStackTrace();
} finally {
bos.close();
}
}
}
4.工具类3-压缩
package com.test.util;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Enumeration;
import java.util.regex.Pattern;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.tools.zip.ZipEntry;
import org.apache.tools.zip.ZipFile;
import org.apache.tools.zip.ZipOutputStream;
public class ZipUtil {
private static final Log log = LogFactory.getLog(ZipUtil.class);
private static int BUF_SIZE = 1024;
private static String ZIP_ENCODEING = "GBK";
/**
* 压缩文件或文件夹
*
* @param zipFileName
* @param inputFile
* @throws Exception
*/
public void zip(String zipFileName, String inputFile) throws Exception {
zip(zipFileName, new File(inputFile));
}
/**
* 压缩文件或文件夹
*
* @param zipFileName
* @param inputFile
* @throws Exception
*/
public static void zip(String zipFileName, File inputFile) throws Exception {
// 未指定压缩文件名,默认为"ZipFile"
if (zipFileName == null || zipFileName.equals(""))
zipFileName = "ZipFile";
// 添加".zip"后缀
if (!zipFileName.endsWith(".zip"))
zipFileName += ".zip";
// 创建文件夹
String path = Pattern.compile("[\\/]").matcher(zipFileName).replaceAll(File.separator);
int endIndex = path.lastIndexOf(File.separator);
path = path.substring(0, endIndex);
File f = new File(path);
f.mkdirs();
// 开始压缩
{
ZipOutputStream zos = new ZipOutputStream(new BufferedOutputStream(new FileOutputStream(zipFileName)));
zos.setEncoding(ZIP_ENCODEING);
compress(zos, inputFile, "");
log.debug("zip done");
zos.close();
}
}
/**
* 解压缩zip压缩文件到指定目录
*
* @param unZipFileName
* @param outputDirectory
* @throws Exception
*/
public static void unZip(String unZipFileName, String outputDirectory) throws Exception {
// 创建输出文件夹对象
File outDirFile = new File(outputDirectory);
outDirFile.mkdirs();
// 打开压缩文件文件夹
ZipFile zipFile = new ZipFile(unZipFileName, ZIP_ENCODEING);
for (Enumeration<?> entries = zipFile.getEntries(); entries.hasMoreElements(); ) {
ZipEntry ze = (ZipEntry) entries.nextElement();
File file = new File(outDirFile, ze.getName());
if (ze.isDirectory()) {// 是目录,则创建之
file.mkdirs();
log.debug("mkdir " + file.getAbsolutePath());
} else {
File parent = file.getParentFile();
if (parent != null && !parent.exists()) {
parent.mkdirs();
}
log.debug("unziping " + ze.getName());
file.createNewFile();
FileOutputStream fos = new FileOutputStream(file);
InputStream is = zipFile.getInputStream(ze);
inStream2outStream(is, fos);
fos.close();
is.close();
}
}
zipFile.close();
}
/**
* 压缩一个文件夹或文件对象到已经打开的zip输出流 <b>不建议直接调用该方法</b>
*
* @param zos
* @param f
* @param fileName
* @throws Exception
*/
public static void compress(ZipOutputStream zos, File f, String fileName) throws Exception {
log.debug("Zipping " + f.getName());
if (f.isDirectory()) {
// 压缩文件夹
File[] fl = f.listFiles();
zos.putNextEntry(new ZipEntry(fileName + "/"));
fileName = fileName.length() == 0 ? "" : fileName + "/";
for (int i = 0; i < fl.length; i++) {
compress(zos, fl[i], fileName + fl[i].getName());
}
} else {
// 压缩文件
zos.putNextEntry(new ZipEntry(fileName));
FileInputStream fis = new FileInputStream(f);
inStream2outStream(fis, zos);
fis.close();
zos.closeEntry();
}
}
private static void inStream2outStream(InputStream is, OutputStream os) throws IOException {
BufferedInputStream bis = new BufferedInputStream(is);
BufferedOutputStream bos = new BufferedOutputStream(os);
int bytesRead = 0;
for (byte[] buffer = new byte[BUF_SIZE]; ((bytesRead = bis.read(buffer, 0, BUF_SIZE)) != -1); ) {
bos.write(buffer, 0, bytesRead); // 将流写入
}
}
/**
* 压缩
*
* @param srcfile 文件名数组
* @param zipfile 压缩后文件
*/
public static void zipFiles(java.io.File[] srcfile, java.io.File zipfile) {
byte[] buf = new byte[1024];
try {
ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zipfile));
out.setEncoding("GBK");
for (int i = 0; i < srcfile.length; i++) {
FileInputStream in = new FileInputStream(srcfile[i]);
out.putNextEntry(new ZipEntry(srcfile[i].getName()));
int len;
while ((len = in.read(buf)) > 0) {
out.write(buf, 0, len);
}
out.closeEntry();
in.close();
}
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
//下载
public static void downFile(HttpServletResponse response, String serverPath, String str) {
try {
String path = serverPath + str;
File file = new File(path);
if (file.exists()) {
InputStream ins = new FileInputStream(path);
BufferedInputStream bins = new BufferedInputStream(ins);// 放到缓冲流里面
OutputStream outs = response.getOutputStream();// 获取文件输出IO流
BufferedOutputStream bouts = new BufferedOutputStream(outs);
response.setContentType("application/x-download");// 设置response内容的类型
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(str, "GBK"));// 设置头部信息
int bytesRead = 0;
byte[] buffer = new byte[8192];
// 开始向网络传输文件流
while ((bytesRead = bins.read(buffer, 0, 8192)) != -1) {
bouts.write(buffer, 0, bytesRead);
}
bouts.flush();// 这里一定要调用flush()方法
ins.close();
bins.close();
outs.close();
bouts.close();
} else {
response.sendRedirect("/WEB-INF/views/error.jsp");
}
} catch (IOException e) {
e.printStackTrace();
}
}
//删除
public static void deleteFile(File file) {
//检查文件是否存在,如果不存在直接返回,不进行下面的操作
if (!file.exists()) {
return;
}
//如果是文件删除,就删除文件,然后返回,不进行下面的操作
if (file.isFile()) {
file.delete();
return;
}
//是文件夹
if (file.isDirectory()) {
//循环所有文件夹里面的内容并删除
File[] files = file.listFiles();
if (files != null) {
for (File f : files) {
//使用迭代,调用自己
deleteFile(f);
}
}
//删除自己
file.delete();
}
}
}
5.直接导出到Excel实现
直接导出到Excel有两种实现,一种方法是直接导出,像这样:
OutputStream output = response.getOutputStream();
ReportUtil.report(dataList, output);
第二种方法是先把excel文件放到服务器上,然后再下载,像这样:
@RequestMapping(value = "/reportXLS", method = {RequestMethod.GET, RequestMethod.POST})
@ResponseBody
public void reportXLS(HttpServletRequest request, HttpServletResponse response) {
if (dataList!= null && dataList.size() > 0) {
// 当前日期
Date date = new Date();
SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
String str = format.format(date);
// 指定下载的文件名
String name = str + ".xls";
// 服务器地址
String serverPath = request.getSession().getServletContext().getRealPath("/") + "static\\excel\\";
try {
String fileName = new String(name.getBytes(), "GBK");
File filePath = new File(serverPath + "\\" + fileName);
FileOutputStream output = new FileOutputStream(filePath);
ReportUtil.report(dataList, output);
// 下载
ZipUtil.downFile(response, serverPath, str + ".xls");
//删除
ZipUtil.deleteFile(filePath);
} catch (IOException e) {
e.printStackTrace();
}
}
}
6.导出到Excel并打包下载实现
思路是把excel文件放到服务器上,压缩,然后下载压缩包
@RequestMapping(value = "/reportZIP", method = {RequestMethod.GET, RequestMethod.POST})
@ResponseBody
public void reportZIP(HttpServletRequest request, HttpServletResponse response) {
// 当前日期
Date date = new Date();
SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
String str = format.format(date);
// 用于存放生成的文件名称
List<File> srcFile = new ArrayList<File>();
// 服务器地址
String serverPath = request.getSession().getServletContext().getRealPath("/") + "static\\excel\\";
// 在服务器端创建文件夹
File file = new File(serverPath + str);
if (!file.exists()) {
file.mkdir();
}
// 查询参数
String[] type= typeList.split(",");
for (int i = 0; i < type.length; i++) {
// 查询
List<Object> dataList= service.queryType(type[i]);
if (dataList== null || dataList.size() == 0) {
continue;
}
String name= dataList.get(0).getTypeName();
// 指定下载的文件名
String name = name+ ".xls"; // 定义现在excel文件名称,注意这里不是压缩包的名称
try {
String fileName = new String(name.getBytes(), "GBK");
File filePath = new File(serverPath + str + "\\" + fileName);
// 加到源路径里
srcFile.add(filePath);
// 将生成的excel放到服务器的指定的文件夹中
FileOutputStream output = new FileOutputStream(filePath);
// 导出
ReportUtil.report(dataList, output);
} catch (IOException e) {
e.printStackTrace();
}
}
// 压缩
File srcfile[] = new File[srcFile.size()];
for (int k = 0, n = srcFile.size(); k < n; k++) {
srcfile[k] = srcFile.get(k);
}
File zipfile = new File(serverPath + str + ".zip");
ZipUtil.zipFiles(srcfile, zipfile);
// 下载
ZipUtil.downFile(response, serverPath, str + ".zip");
// 删除
ZipUtil.deleteFile(file);
ZipUtil.deleteFile(zipfile);
}