项目架构为springMVC+maBatis
注：前端传中文参数解决办法；
1前端页面传参方式: window.location="<%=basePath%>/dataStatisticsController/outExcel.do?zone="+encodeURI(encodeURI(zone ))+"&beginTime="+beginTime+"&overTime="+overTime;
2.后台处理方式:String companyareaname=URLDecoder.decode(request.getParameter("zone"),"utf-8");
控制层方法：
1.通过参数，查询出符号条件的数据；2，文件预处理 3.处理数据进行分页处理，excel文档 生成可以根据情况进行分页生成
4.对生成的Excel文档文件进行压缩 5.通过input流将文件读取到内存再通过output流将内存中数据读出(下载）
@RequestMapping("/outExcel.do")
public @ResponseBody String outExcel(HttpServletRequest request, HttpServletResponse response, Encryption encryption, Model model,
HttpSession session) throws Exception {
//encodeURI(encodeURI( keyWord ))
// String companyareaname=URLDecoder.decode(request.getParameter("zone"),"utf-8");
String companyareaname=request.getParameter("zone");
companyareaname =URLDecoder.decode( companyareaname ,"utf-8");
String beginTime = request.getParameter("beginTime");
String overTime=request.getParameter("overTime");
/*查询总条数*/
int recCount = encryptionService.getTotals(companyareaname,beginTime,overTime);
List<Encryption> outList = null;
String downloadFile ;
if (recCount <= 0) {
downloadFile = java.net.URLEncoder.encode("无信息记录.txt","UTF-8");
ByteArrayInputStream inputStream = new ByteArrayInputStream("无信息记录下载".getBytes());
return "没有信息";
}
//记录数大于零
String fileName="";
//创建文件目录
String savePath = "D:\\EXCEL";
File dstPath = new File(savePath);
if (!dstPath.exists()) dstPath.mkdirs();
//记录数大于零
/*
* 导出页数
*/
int totalpage=1;
int pageSize=10;
if(recCount>10){
totalpage = recCount/pageSize
+ ((recCount % pageSize) > 0 ? 1 : 0);
}
String fileNames[] = new String[totalpage];
//生成xls文件
ExcelOfOutXls outXls = new ExcelOfOutXls();
for(int pageNo = 1;pageNo <= totalpage;pageNo++){
// outList = phoneVisitService.getPhoneVisitList(isShow,beginTime, endTime, pageNo, pageSize, getComIDOfJ(), staId, this.getSexps());
outList = encryptionService.getEncryptionAll(companyareaname, beginTime, overTime, pageNo, pageSize);
fileName = savePath + "/EXCEL_"+pageNo + "[" + outXls.toDateStr(new Date(),"yyMMddHHmmss") + "].xls";
outXls.outExcelXLS(outList, fileName);
fileNames[pageNo-1] = fileName;
}
//压缩xls文件
fileName = "软件信息[" + outXls.toDateStr(new Date(),"yyMMddHHmmss") + "].zip";
String zipFile = savePath + "/" + fileName;
FileZip.compress(fileNames,zipFile);
//删除xls文件
File xlsFile = null;
for(int i = 0; i < fileNames.length; i++){
xlsFile = new File(fileNames[i]);
if(xlsFile.exists()) xlsFile.delete();
}
//读取下载文件
File file = new File(zipFile);
OutputStream fos=null;
if(file.exists()){
response.setCharacterEncoding("utf-8");
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName, "UTF8"));
FileInputStream inputStream = new FileInputStream(file);
fos = response.getOutputStream();
byte[] b = new byte[2048];
int length;
while ((length = inputStream.read(b)) > 0) {
fos.write(b, 0, length);
}
fos.flush();
fos.close();
inputStream.close();
}else{
return "导出失败";
}
return null;
}
文档生成方法(根据需求不同改变具体的数据处理和获取方法)这个方法感觉比较原始：
public class ExcelOfOutXls {
public ExcelOfOutXls(){
}
public String nullToStr(String sourceStr){
if (sourceStr == null) sourceStr = "";
sourceStr = sourceStr.trim();
return sourceStr;
}
public String toDateStr(Date date,String format){
if( date == null ) return "";
SimpleDateFormat df=new SimpleDateFormat(format);
return df.format(date);
}
public String toLongStr(Long lNumber){
if (lNumber == null){
return "0";
}else{
return lNumber.toString();
}
}
public String toDoubleStr(Double dNumber){
if (dNumber == null){
return "0.00";
}else{
return dNumber.toString();
}
}
@SuppressWarnings("unchecked")
public String getMapStr(Map map,String optionStr){
String returnStr = "";
String[] rec = null;
if(optionStr==null) optionStr = "";
rec = optionStr.split(",");
for(int i=0; i<rec.length;i++) {
if(map.get(rec[i])!=null) returnStr += map.get(rec[i]) + ",";
}
return returnStr;
}
@SuppressWarnings("unchecked")
public void outExcelXLS(List<Encryption> encrys ,String fileName){
String switchStr = "";
File outfile = new File(fileName);
if (outfile.exists()) outfile.delete();
//列标题
String[] headerNames={"软件类型","软件版本","加密权限","公司名称" ,"税号" ,"税控盘号" ,"设备号1"
,"设备号2" ,"地址" ,"电话" ,"备注" ,"授权开始时间" ,"授权结束时间" ,"授权状态" ,"保存文件路径" ,"创建时间" ,"修改时间"};
try{
//开始输出Execl
String sheetName="上门回访信息";
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(0, sheetName);
//风格
HSSFCellStyle cs = workbook.createCellStyle();
HSSFCellStyle csDate = workbook.createCellStyle();
//字体
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short)9);
font.setFontName("宋体");
cs.setFont(font);
csDate.setFont(font);
//日期格式
short df = workbook.createDataFormat().getFormat("yyyy-MM-dd hh:mm:ss");
csDate.setDataFormat(df);
//
HSSFRow row = sheet.createRow((short) 0);
HSSFCell cell;
int iColumn = 0;
iColumn = headerNames.length;
for(int i = 0;i < iColumn;i++){
cell = row.createCell((short) (i));
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(cs);
cell.setCellValue(headerNames[i]);
}
//写入表格内容
int j = 0;
for(int i=0; i<encrys.size(); i++){
//dvsList数据List对象
row = sheet.createRow((short)i + 1);
j = 0;
//软件类型
switchStr = encrys.get(i).getRjlx();
cell=row.createCell((short) j++);
cell.setCellStyle(cs);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(switchStr);
//软件版本
switchStr = encrys.get(i).getRjbb();
cell=row.createCell((short) j++);
cell.setCellStyle(cs);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(switchStr);
//加密权限
switchStr = encrys.get(i).getQx();
cell=row.createCell((short) j++);
cell.setCellStyle(cs);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(switchStr);
//公司名称
switchStr = encrys.get(i).getGsmc();
cell=row.createCell((short) j++);
cell.setCellStyle(cs);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(switchStr);
//税号
switchStr = encrys.get(i).getSh();
cell=row.createCell((short) j++);
cell.setCellStyle(cs);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(switchStr);
//税控盘号
switchStr = encrys.get(i).getSkph();
cell=row.createCell((short) j++);
cell.setCellStyle(cs);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(switchStr);
//设备号1
switchStr = encrys.get(i).getSbh1();
cell=row.createCell((short) j++);
cell.setCellStyle(cs);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(switchStr);
//设备号2
switchStr = encrys.get(i).getSbh2();
cell=row.createCell((short) j++);
cell.setCellStyle(cs);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(switchStr);
//地址
switchStr = encrys.get(i).getDz();
cell=row.createCell((short) j++);
cell.setCellStyle(cs);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(switchStr);
//电话
switchStr = encrys.get(i).getDh();
cell=row.createCell((short) j++);
cell.setCellStyle(cs);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(switchStr);
//备注
switchStr = encrys.get(i).getBz();
cell=row.createCell((short) j++);
cell.setCellStyle(cs);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(switchStr);
//授权开始时间
switchStr = encrys.get(i).getSqkssj();
cell=row.createCell((short) j++);
cell.setCellStyle(cs);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(switchStr);
//授权结束时间
switchStr = encrys.get(i).getSqjssj();
cell=row.createCell((short) j++);
cell.setCellStyle(cs);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(switchStr);
//授权状态
switchStr = encrys.get(i).getSqzt();
cell=row.createCell((short) j++);
cell.setCellStyle(cs);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(switchStr);
//保存文件路径
switchStr = encrys.get(i).getBcwjlj();
cell=row.createCell((short) j++);
cell.setCellStyle(cs);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(switchStr);
//创建时间
switchStr = encrys.get(i).getCreatetime();
cell=row.createCell((short) j++);
cell.setCellStyle(cs);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(switchStr);
//修改时间
switchStr = encrys.get(i).getUpdatetime();
cell=row.createCell((short) j++);
cell.setCellStyle(cs);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(switchStr);
}
//调整列宽
for(int i = 0; i < headerNames.length; i++){
sheet.autoSizeColumn((short)i);
}
FileOutputStream fileOut = new FileOutputStream(fileName);
workbook.write(fileOut);
fileOut.flush();
fileOut.close();
}catch(Exception ex){
// System.out.println(ex.toString());
}
}
}
压缩文件工具类：
package com.company.item.util.ExcelInfo;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.zip.CRC32;
import java.util.zip.CheckedOutputStream;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
/**
* 压缩(多个)文件或目录,暂不支持中文
* @author ABC
*
*/
public class FileZip {
/**
* 压缩多个文件和目录
* @param filenames
* @param destFile
* @throws IOException
*/
public static void compress(String[] filenames, String destFile) throws IOException {
FileOutputStream fos = new FileOutputStream(destFile);
// 使用CRC32进行校验
CheckedOutputStream csum = new CheckedOutputStream(fos, new CRC32());
ZipOutputStream zOut = new ZipOutputStream(csum);
zOut.setComment("This is a jar file.");
for(String filename:filenames){
File file = new File(filename);
if (!file.exists()){
continue;
}
if(file.isDirectory()){
String baseName = file.getName() + File.separator;
rescursionCompress(zOut, file, baseName);
}else{
compressFile(zOut,new FileInputStream(file),file.getName());
}
}
zOut.close();
}
/**
* 压缩单个文件或者目录
* @param filename
* @param destFile
* @throws IOException
*/
public static void compress(String filename, String destFile)
throws IOException {
compress(new String[]{filename},destFile);
}
/**
* 递归的写入文件
* @param zOut
* @param dir
* @param baseName
* @throws IOException
*/
private static void rescursionCompress(ZipOutputStream zOut, File dir,
String baseName) throws IOException {
File[] files = dir.listFiles();
for (File f : files) {
if (f.isHidden())
continue;
if (f.isDirectory()) {
String newBaseName = baseName + f.getName() + File.separator;
rescursionCompress(zOut, f, newBaseName);
} else {
String entryName = baseName + f.getName();
InputStream in = new FileInputStream(f);
compressFile(zOut, in, entryName);
in.close();
}
}
}
/**
* 压缩单个的文件
*
* @param zOut
* 压缩输出流
* @param filename
* 被压缩的文件名称
* @param relativedName
* 在压缩文件中存在的名称
* @throws IOException
*/
public static void compressFile(ZipOutputStream zOut, InputStream in,
String relativedName) throws IOException {
ZipEntry entry = new ZipEntry(relativedName);
zOut.putNextEntry(entry);
int c = -1;
while ((c = in.read()) != -1)
zOut.write(c);
in.close();
}
/**
* @param args
*/
public static void main(String[] args) throws IOException {
String dir = "f:\\testdir";
String filename = "f:\\new.zip";
compress(new String[]{"f:\\testdir","f:\\Eclipse.txt","f:\\pic"}, filename);
}
}
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
