java导出excel文件（压缩后导出）

相信很多人在项目过程中都会遇到将数据导出到excel中的需求，对于这个问题，首先此文章讲述的是自己使用的方法：我是通过poi导出的2003版的excel表格

方法一：代码如下

public File writeExcel(List<Map<String,Object>> writeContent,String titleList[],String userId) {WritableWorkbook book = null;  File file = new File(nfsUrl+userId+"/"+"outSourceExport1.zip");//首先需要创建一个zip包try {// 打开文件book = Workbook.createWorkbook(file);// 参数0表示这是第一页int linenum = 0;WritableSheet sheet = book.createSheet("催收导出数据", 0);for(int i=0;i<titleList.length;i++){sheet.addCell(new Label(i, linenum, titleList[i]));}linenum ++;if(writeContent!=null && !writeContent.isEmpty()){//此处是传进来的自己的数据（就是要写到excel中的数据集合）for(int i=0; i<writeContent.size(); i++){Map<String, Object> columnMap = writeContent.get(i);Iterator iter = columnMap.entrySet().iterator();int y = 0;while (iter.hasNext()) {Map.Entry entry = (Map.Entry) iter.next();Object key = entry.getKey();Object val = entry.getValue();if(val!=null){sheet.addCell(new Label(y, linenum, val.toString()));}else{sheet.addCell(new Label(y, linenum, null));}y ++;}linenum ++;}}// 写入数据并关闭文件book.write();} catch (Exception e) {System.out.println(e);}finally{if(book!=null){try {book.close();} catch (Exception e) {e.printStackTrace();} }}return file;}

调用

public String  getFile(T_L000800 tL000800){                //此处是自己要写入的数据集合，自己查出来即可List<Map<String,Object>> resultList = jdbcTemplate.queryForList(sqlColumnBuilder.toString());//调用上面的写入excel方法（此处的resultList：是自己的数据集合，titleList是excel中的title，最后一个参数是我需要的，可以不传）File exportFile = writeExcel(resultList,titleList,tL000800.getTuserId());if(exportFile.exists()){//加载导出文件，先创建一个outputStream（这里是因为我没有传入response，若用response的话直接ServletOutputStream out = response.getOutputStream()获取输出流即可）FileOutputStream out = new FileOutputStream(new File(nfsUrl+tL000800.getTuserId()+"/"+"outSourceExport.zip"));//这个是将已经生成的文件放到输入流中FileInputStream  fis = new FileInputStream(exportFile);  //输出流放到压缩的ZipOutputStream中ZipOutputStream zipOutputStream = new ZipOutputStream(out);//放入压缩zip包中;   ZipEntry zipEntry = new ZipEntry("委外数据.xls");zipOutputStream.putNextEntry(zipEntry); byte[] b = new byte[fis.available()];  //读取输入流文件;  int len = 0;  while((len=fis.read(b)) >0){  zipOutputStream.write(b, 0, len);  }  //关闭;  zipOutputStream.closeEntry();  zipOutputStream.close();if(fis != null){  fis.close();  }  out.flush();  out.close();//由于我这边的需求限制，会出现两个zip，我需要删一个if(exportFile.getName().endsWith(".zip")){if(exportFile.delete()){System.out.println("成功");}else{System.out.println("失败");}}try {//我的文件是直接放到weblogic上的，所以对文件及文件夹要设置权限，这里777是读写权限都赋予Runtime.getRuntime().exec("chmod 777 " + (nfsUrl+tL000800.getTuserId()));Runtime.getRuntime().exec("chmod 777 " + (nfsUrl+tL000800.getTuserId()+"/"+"outSourceExport.zip"));} catch (IOException e) {e.printStackTrace();}return nfsUrlPath+tL000800.getTuserId()+"/"+"outSourceExport.zip";}

方法二：代码比较长,是一个一个的写入excel的每个格式，以及线框什么的

package com.tansun.doservice.httpService.excel;import java.io.ByteArrayOutputStream;import java.io.File;import java.io.FileOutputStream;import java.text.DecimalFormat;import java.util.ArrayList;import java.util.List;import java.util.Map;import java.util.zip.ZipEntry;import java.util.zip.ZipOutputStream;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.CellRangeAddress;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.springframework.jdbc.core.JdbcTemplate;import webbean.R_S000130_Rows;import webbean.T_L000800;public class SearchPhone{/** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */public String getFile(T_L000800 tL000800){String title="111";String heads[]={"序号","客户姓名","证件号码","品种","金额","日期","金额","次数","日期2","状态","机构1","机构2"};String returnString = "";try {returnString =  this.print(title, heads, result,tL000800.getTuserId());} catch (Exception e) {e.printStackTrace();}return returnString;}private String print(String title,String heads[],List<R_S000130_Rows> list,String userId) throws Exception{// 创建工作簿，创建2003版本的工作簿Workbook wb = new HSSFWorkbook();// 创建sheet工作表Sheet sheet = wb.createSheet();// 定义行Row row = null;// 定义单元格Cell cell = null;// 定义行下标，默认从第一行开始int rowNo = 0;// 定义列int cellNo = 1;//===========大标题begin===================// 创建行row = sheet.createRow(rowNo++);// 设置行高row.setHeightInPoints(36f);cell = row.createCell(cellNo);// 给单元格设置内容 2012年8月份出货表 2016-12 2016-07cell.setCellValue(title);// 给单元格设置样式// 合并单元格 横向合并单元格sheet.addMergedRegion(new CellRangeAddress(0,0,1,10));// 设置样式CellStyle bigTitle = this.bigTitle(wb);cell.setCellStyle(bigTitle);//===========大标题end===================// ===========小标题start===================// 设置每一列的宽度sheet.setColumnWidth(0, 256*5);sheet.setColumnWidth(1, 256*10);sheet.setColumnWidth(2, 256*16);sheet.setColumnWidth(3, 256*20);sheet.setColumnWidth(4, 256*20);sheet.setColumnWidth(5, 256*14);sheet.setColumnWidth(6, 256*10);sheet.setColumnWidth(7, 256*10);sheet.setColumnWidth(8, 256*16);sheet.setColumnWidth(9, 256*16);sheet.setColumnWidth(10, 256*14);sheet.setColumnWidth(11, 256*12);sheet.setColumnWidth(12, 256*14);// 处理小标题// 先创建第二行row = sheet.createRow(rowNo++);CellStyle headStyle = wb.createCellStyle();// 遍历该数组，创建单元格for (String head : heads) {// 获取每一个标题内容// 创建单元格，应该让cellNo每次都+1cell = row.createCell(cellNo++);// 设置每一个单元格的样式Font font = wb.createFont();font.setFontName("黑体");font.setFontHeightInPoints((short)12);headStyle.setFont(font);headStyle.setAlignment(CellStyle.ALIGN_CENTER);//横向居中headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//纵向居中headStyle.setBorderTop(CellStyle.BORDER_THIN);//上细线headStyle.setBorderBottom(CellStyle.BORDER_THIN);//下细线headStyle.setBorderLeft(CellStyle.BORDER_THIN);//左细线headStyle.setBorderRight(CellStyle.BORDER_THIN);//右细线cell.setCellStyle(headStyle);// 设置内容cell.setCellValue(head);}//====================小标题end=========================//====================处理数据start=========================//序号int index=1;CellStyle  textStyle = wb.createCellStyle();Font font = wb.createFont();font.setFontName("Times New Roman");font.setFontHeightInPoints((short)10);textStyle.setFont(font);textStyle.setAlignment(CellStyle.ALIGN_LEFT);//横向居左textStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//纵向居中textStyle.setBorderTop(CellStyle.BORDER_THIN);//上细线textStyle.setBorderBottom(CellStyle.BORDER_THIN);//下细线textStyle.setBorderLeft(CellStyle.BORDER_THIN);//左细线textStyle.setBorderRight(CellStyle.BORDER_THIN);//右细线for (R_S000130_Rows rows : list) {// 重新把列设置成1cellNo = 1;// 遍历对象// 创建行row = sheet.createRow(rowNo++);// 设置行高row.setHeightInPoints(24f);// 创建单元格cell = row.createCell(cellNo++);// 设置样式cell.setCellStyle(textStyle);// 设置序号cell.setCellValue(index++);// 创建单元格cell = row.createCell(cellNo++);// 设置样式cell.setCellStyle(textStyle);// 设置内容 客户编号cell.setCellValue(rows.getCustomerName());// 创建单元格cell = row.createCell(cellNo++);// 设置样式cell.setCellStyle(textStyle);// 设置内容 客户姓名cell.setCellValue(rows.getCartNum());// 创建单元格cell = row.createCell(cellNo++);// 设置样式cell.setCellStyle(textStyle);// 设置内容  原手机号码cell.setCellValue(rows.getLoanProduct());// 创建单元格cell = row.createCell(cellNo++);// 设置样式cell.setCellStyle(textStyle);// 设置内容 原固定电话cell.setCellValue(rows.getLoanSum());// 创建单元格cell = row.createCell(cellNo++);// 设置样式cell.setCellStyle(textStyle);// 设置内容 变更电话类型一cell.setCellValue(rows.getPaymentDate());// 创建单元格cell = row.createCell(cellNo++);// 设置样式cell.setCellStyle(textStyle);// 设置内容 变更电话号码一cell.setCellValue(rows.getPaymentSum());//创建单元格cell = row.createCell(cellNo++);// 设置样式cell.setCellStyle(textStyle);// 设置内容 变更电话类型二cell.setCellValue(rows.getLoanCount());//创建单元格cell = row.createCell(cellNo++);// 设置样式cell.setCellStyle(textStyle);// 设置内容  变更电话电话二cell.setCellValue(rows.getDealDate());//创建单元格cell = row.createCell(cellNo++);// 设置样式cell.setCellStyle(textStyle);// 设置内容 复核人cell.setCellValue(rows.getMessageResult());//创建单元格cell = row.createCell(cellNo++);// 设置样式cell.setCellStyle(textStyle);// 设置内容 复核人cell.setCellValue(rows.getBranchName());//创建单元格cell = row.createCell(cellNo++);// 设置样式cell.setCellStyle(textStyle);// 设置内容 复核人cell.setCellValue(rows.getInputBranchName());}// 创建缓存区的流ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();// 写入数据wb.write(byteArrayOutputStream);byteArrayOutputStream.flush();byteArrayOutputStream.close();String nfsUrl = DictionaryUtil.transferSingleArgsName("LOAN_NFS_DATA", "nfsPath");File file = new File(nfsUrl+userId+"/");if(!file.exists()){file.mkdir();}FileOutputStream fileOut = new FileOutputStream(new File(nfsUrl+userId+"/"+"SearchPhone.zip"));ZipOutputStream zipOutputStream = new ZipOutputStream(fileOut);ZipEntry entry = new ZipEntry("111.xls");  zipOutputStream.putNextEntry(entry);  byteArrayOutputStream.writeTo(zipOutputStream);zipOutputStream.closeEntry();  byteArrayOutputStream.close();//关闭zipOutputStream.close();fileOut.close();String nfsUrlPath = DictionaryUtil.transferSingleArgsName("LOAN_NFS_DATA", "urlNfsPath");Runtime.getRuntime().exec("chmod 777 " + (nfsUrl+userId));Runtime.getRuntime().exec("chmod 777 " + (nfsUrl+userId+"/"+"SearchPhone.zip"));return nfsUrlPath+userId+"/"+"SearchPhone.zip";}//大标题的样式private CellStyle bigTitle(Workbook wb){CellStyle style = wb.createCellStyle();Font font = wb.createFont();font.setFontName("宋体");font.setFontHeightInPoints((short)16);font.setBoldweight(Font.BOLDWEIGHT_BOLD);//字体加粗style.setFont(font);style.setAlignment(CellStyle.ALIGN_CENTER);//横向居中style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//纵向居中return style;}}