导出excel多个sheet 并压缩

本文主要是介绍导出excel多个sheet 并压缩，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

创建sheet工具类：

package org.deyi.np.util;import java.util.ArrayList;
import java.util.HashMap;/*** @Description: 存放生成sheet所需要的数据信息* @Title: ExcelGenerate.java* @Package excelwrite* @author  LiQ * @date 2018-1-17 下午4:39:08* @version V1.0  
*/
public class SheetInfo{private String sheetName; //sheet名称，默认为Sheet1private ArrayList<String> fieldName; //excel标题头private ArrayList<String> columnName; //数据字段名称，根据名称从map中取值private ArrayList<HashMap<String,Object>> fieldData; //excel数据内容/*** sheet名称 。* @return * @createDate 2018-1-17 下午4:57:04 （创建时间）* @author LiQ （作者）*/public String getSheetName() {return sheetName;}public void setSheetName(String sheetName) {this.sheetName = sheetName;}/*** excel标题头 。* @return * @createDate 2018-1-17 下午4:56:04 （创建时间）* @author LiQ （作者）*/public ArrayList<String> getFieldName() {return fieldName;}public void setFieldName(ArrayList<String> fieldName) {this.fieldName = fieldName;}/*** 数据字段名称，根据名称从map中取值 。* @return * @createDate 2018-1-17 下午4:56:10 （创建时间）* @author LiQ （作者）*/public ArrayList<String> getColumnName() {return columnName;}public void setColumnName(ArrayList<String> columnName) {this.columnName = columnName;}/*** excel数据内容 。* @return * @createDate 2018-1-17 下午4:56:17 （创建时间）* @author LiQ （作者）*/public ArrayList<HashMap<String, Object>> getFieldData() {return fieldData;}public void setFieldData(ArrayList<HashMap<String, Object>> fieldData) {this.fieldData = fieldData;}/*** 生成sheet所需要的数据信息,sheetName默认为Sheet1,默认创建第一个sheet* @param fieldName excel标题头* @param columnName 数据字段名称，根据名称从map中取值* @param fieldData excel数据内容* @return* @throws*/public SheetInfo(ArrayList<String> fieldName, ArrayList<String> columnName, ArrayList<HashMap<String, Object>> fieldData) {this.fieldName = fieldName;this.columnName = columnName;this.fieldData = fieldData;}/*** 生成sheet所需要的数据信息,默认创建第一个sheet* @param sheetName sheet名称* @param fieldName excel标题头* @param columnName 数据字段名称，根据名称从map中取值* @param fieldData excel数据内容* @return* @throws*/public SheetInfo(String sheetName, ArrayList<String> fieldName, ArrayList<String> columnName, ArrayList<HashMap<String, Object>> fieldData) {this.sheetName = sheetName;this.fieldName = fieldName;this.columnName = columnName;this.fieldData = fieldData;}
}

创建 ExcelFile ：

package org.deyi.np.util;import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;public class ExcelFileGenerator {private List<SheetInfo>  sheetInfoList;private HSSFWorkbook workBook = null;/*** 构造器* @param fieldName 结果集的字段名* @param data*/public ExcelFileGenerator(List<SheetInfo>  sheetInfoList) {this.sheetInfoList = sheetInfoList;workBook = new HSSFWorkbook();//创建一个工作薄对象}/*** 构造器* @param fieldName 结果集的字段名* @param data*/public ExcelFileGenerator(SheetInfo  sheetInfo) {ArrayList<SheetInfo> arrayList = new ArrayList<SheetInfo>();arrayList.add(sheetInfo);this.sheetInfoList = arrayList;workBook = new HSSFWorkbook();//创建一个工作薄对象}/*** 创建HSSFWorkbook对象* @return HSSFWorkbook*/public HSSFWorkbook writeSheetInfo() {for (int i = 0; i < sheetInfoList.size(); i++) {SheetInfo sheetInfo = sheetInfoList.get(i);HSSFSheet sheet = sheetInfo.getSheetName()==null?workBook.createSheet():workBook.createSheet(sheetInfo.getSheetName());//使用workbook对象创建sheet对象HSSFRow headRow = sheet.createRow((short) 0); //创建行，0表示第一行（本例是excel的标题）ArrayList<String> fieldName = sheetInfo.getFieldName();for (int j = 0; j < fieldName.size(); j++) {//循环excel的标题HSSFCell cell = headRow.createCell( j);//使用行对象创建列对象，0表示第1列/**************对标题添加样式begin********************///设置列的宽度/sheet.setColumnWidth(j, 6000);HSSFCellStyle cellStyle = workBook.createCellStyle();//创建列的样式对象HSSFFont font = workBook.createFont();//创建字体对象//字体加粗font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体颜色变红font.setColor(HSSFColor.BLACK.index);//如果font中存在设置后的字体，并放置到cellStyle对象中，此时该单元格中就具有了样式字体cellStyle.setFont(font);/**************对标题添加样式end********************///添加样式cell.setCellType(HSSFCell.CELL_TYPE_STRING);if(fieldName.get(j) != null){//将创建好的样式放置到对应的单元格中cell.setCellStyle(cellStyle);cell.setCellValue((String) fieldName.get(j));//为标题中的单元格设置值}else{cell.setCellValue("-");}}ArrayList<HashMap<String, Object>> fieldData = sheetInfo.getFieldData();int rows = fieldData.size();//总的记录数ArrayList<String> columnName = sheetInfo.getColumnName();//分页处理excel的数据，遍历所有的结果for (int k = 0; k < rows; k++) {HSSFRow row = sheet.createRow((short) (k + 1));//创建1行//分页处理，获取每页的结果集，并将数据内容放入excel单元格HashMap<String,Object> rowMap = (HashMap<String,Object>) fieldData.get(k);for (int n = 0; n < columnName.size(); n++) {//遍历某一行的结果Object value = rowMap.get(columnName.get(n));HSSFCell cell = row.createCell( n);//使用行创建列对象if(value != null){cell.setCellValue((String) value.toString());}else{cell.setCellValue("");}}}}return workBook;}public void expordExcel(OutputStream os) throws Exception {workBook = writeSheetInfo();workBook.write(os);//将excel中的数据写到输出流中，用于文件的输出os.flush();							//刷新缓冲区os.close();}/*** 数据测试 。* @param args * @createDate 2018-1-17 下午5:26:45 （创建时间）* @author LiQ （作者）*/public static void main(String[] args) {//============造数据===============ArrayList<String> fieldName = new ArrayList<String>();ArrayList<String> columnName = new ArrayList<String>();ArrayList<HashMap<String, Object>> fieldData = new ArrayList<HashMap<String,Object>>();for(int i=0;i<10;i++){fieldName.add("标题："+i);columnName.add("column"+i);HashMap<String, Object> map = new HashMap<String, Object>();for(int j=0;j<10;j++){map.put("column"+j, "value:"+i+"vj:"+j);}fieldData.add(map);}SheetInfo sheetInfo = new SheetInfo(fieldName, columnName, fieldData);System.out.println("hhhh----------------------");//============造数据===============File file = new File("C:\\Users\\LiQ\\Desktop\\qqqq.xls");OutputStream op;try {op = new FileOutputStream(file);ExcelFileGenerator efg = new ExcelFileGenerator(sheetInfo);efg.expordExcel(op);System.out.println("aaaa----------------------");} catch (Exception e) {e.printStackTrace();}}}

将生成的文件压缩成zip:

public void downLoad(HashMap<String, Object> condition, HttpServletResponse response) {ZipOutputStream zos = null;
List<ArrayList<HSSFWorkbook>> zipList = new ArrayList<ArrayList<HSSFWorkbook>>();HSSFWorkbook workbook = new HSSFWorkbook();
ArrayList<HashMap<String, Object>> list13 = bloodMetabolismDao.selectDownLoadList(patientId);ArrayList<String> fieldName13 = new ArrayList<String>();fieldName13.add("是否异常");fieldName13.add("血代谢检查日期");fieldName13.add("血代谢异常描述");fieldName13.add("创建时间");ArrayList<String> columnName13 = new ArrayList<String>();columnName13.add("hasRegess");columnName13.add("bloodMetabolismCheckDate");columnName13.add("bloodMetabolismDescribe");columnName13.add("maketime");SheetInfo sheetInfo18 = new SheetInfo("基本信息",fieldName, columnName, patientList);SheetInfo sheetInfo19 = new SheetInfo("血代谢",fieldName13, columnName13, list13);listInfo6.add(sheetInfo18);listInfo6.add(sheetInfo19);//============血代谢 end =======================//============尿代谢 begin =======================ArrayList<HashMap<String, Object>> list14 = urinaryMetabolismDao.selectDownLoadList(patientId);ArrayList<String> fieldName14 = new ArrayList<String>();fieldName14.add("尿代谢检查日期");fieldName14.add("是否异常");fieldName14.add("尿代谢异常描述");fieldName14.add("创建时间");ArrayList<String> columnName14 = new ArrayList<String>();columnName14.add("urinaryMetabolismCheckDate");columnName14.add("hasRegess");columnName14.add("urinaryMetabolismDescribe");columnName14.add("maketime");SheetInfo sheetInfo20 = new SheetInfo("尿代谢",fieldName14, columnName14, list14);listInfo6.add(sheetInfo20);//============尿代谢 end =======================//============乳酸水平 begin=======================ArrayList<HashMap<String, Object>> list15 = lacticAcidLevelDao.selectDownLoadList(patientId);ArrayList<String> fieldName15 = new ArrayList<String>();fieldName15.add("乳酸水平检查日期");fieldName15.add("乳酸水平标本类型");fieldName15.add("乳酸水平情况");fieldName15.add("创建时间");ArrayList<String> columnName15 = new ArrayList<String>();columnName15.add("lacticAcidLevelCheckDate");columnName15.add("lacticAcidLevelSpecimenType");columnName15.add("lacticAcidLevelDescribe");columnName15.add("maketime");SheetInfo sheetInfo21 = new SheetInfo("乳酸水平",fieldName15, columnName15, list15);listInfo6.add(sheetInfo21);//============乳酸水平  end =======================//============血生化  begin =======================ArrayList<HashMap<String, Object>> list16 = bloodBiochemistryDao.selectDownLoadList(patientId);ArrayList<String> fieldName16 = new ArrayList<String>();fieldName16.add("血生化检查日期");fieldName16.add("血生化是否异常");fieldName16.add("血生化异常描述");fieldName16.add("创建时间");ArrayList<String> columnName16 = new ArrayList<String>();columnName16.add("bloodBiochemistryCheckDate");columnName16.add("hasRegerss");columnName16.add("bloodBiochemistryDescribe");columnName16.add("maketime");SheetInfo sheetInfo22 = new SheetInfo("血生化",fieldName16, columnName16, list16);listInfo6.add(sheetInfo22);//============血生化  end =======================//============血同型半胱氨酸 begin =======================ArrayList<HashMap<String, Object>> list17 = homocysteineDao.selectDownLoadList(patientId);ArrayList<String> fieldName17 = new ArrayList<String>();fieldName17.add("血同型半胱氨酸检查日期");fieldName17.add("血同型半胱氨酸是否异常");fieldName17.add("血同型半胱氨酸异常描述");fieldName17.add("创建时间");ArrayList<String> columnName17 = new ArrayList<String>();columnName17.add("homocysteineCheckDate");columnName17.add("hasRegerss");columnName17.add("homocysteineDescribe");columnName17.add("maketime");SheetInfo sheetInfo23 = new SheetInfo("血同型半胱氨酸",fieldName17, columnName17, list17);listInfo6.add(sheetInfo23);//============血同型半胱氨酸  end =======================//============血氨 begin =======================ArrayList<HashMap<String, Object>> list18 = bloodAmmoniaDao.selectDownLoadList(patientId);ArrayList<String> fieldName18 = new ArrayList<String>();fieldName18.add("血氨检查日期");fieldName18.add("血氨是否异常");fieldName18.add("血氨是否异常描述");fieldName18.add("创建时间");ArrayList<String> columnName18 = new ArrayList<String>();columnName18.add("bloodAmmoniaCheckDate");columnName18.add("hasRegerss");columnName18.add("bloodAmmoniaDescribe");columnName18.add("maketime");SheetInfo sheetInfo24 = new SheetInfo("血氨",fieldName18, columnName18, list18);listInfo6.add(sheetInfo24);//============血氨  end =======================//============其他相关生化检查  begin=======================ArrayList<HashMap<String, Object>> list19 = otherBiochemicalExaminationDao.selectDownLoadList(patientId);ArrayList<String> fieldName19 = new ArrayList<String>();fieldName19.add("其他相关生化检查日期");fieldName19.add("其他相关生化检查是否异常");fieldName19.add("其他相关生化检查异常描述");fieldName19.add("创建时间");ArrayList<String> columnName19 = new ArrayList<String>();columnName19.add("otherBiochemicalExaminationCheckDate");columnName19.add("hasRegerss");columnName19.add("otherBiochemicalExaminationDescribe");columnName19.add("maketime");SheetInfo sheetInfo25 = new SheetInfo("其他相关生化检查",fieldName19, columnName19, list19);listInfo6.add(sheetInfo25);//============其他相关生化检查  end =======================ExcelFileGenerator efg = new ExcelFileGenerator(listInfo6);workbook = efg.writeSheetInfo();ArrayList<HSSFWorkbook> wbList6 = new ArrayList<HSSFWorkbook>();wbList6.add(workbook);zipList.add(wbList6);//下载显示的文件名，解决文件乱码问题String fileName=patientList.get(0).get("patientName").toString()+"_"+System.currentTimeMillis() + ".zip";try {fileName = new String(fileName.getBytes("utf-8"),"iso-8859-1");} catch (UnsupportedEncodingException e1) {e1.printStackTrace();}try {OutputStream os = response.getOutputStream();zos = new ZipOutputStream(os);response.setContentType("application/force-download;charset=UTF-8");// 设置强制下载不打开response.addHeader("Content-Disposition","attachment;fileName=" + fileName);// 设置文件名for (int i = 0; i < zipList.size(); i++) {for (Iterator iterator = zipList.get(i).iterator(); iterator.hasNext();) {HSSFWorkbook hssfWorkbook = (HSSFWorkbook) iterator.next();//创建ZIP实体，并添加进压缩包  ZipEntry zipEntry = new ZipEntry(nameList.get(i)+".xls");  zos.putNextEntry(zipEntry);  hssfWorkbook.write(zos);}}} catch (Exception e) {e.printStackTrace();} finally{  //关闭流  try {  if(null != zos) zos.close();  } catch (IOException e) {  e.printStackTrace();  throw new RuntimeException(e);  }  }
}

这篇关于导出excel多个sheet 并压缩的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---