创建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名称,默认为Sheet1
private 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);
}
}
}