poi导出excel

问题描述:

poi导出一个workbook下多sheet的excel时,excel提示“此文件中的某些文本格式可能已经更改,因为它已经超出最多允许的字体数。关闭其他文档再试一次可能有用。”

原因:

创建字体样式Workbook.CreateFont() 被频繁调用;

创建单元格样式Workbook.createCellStyle(); 被频繁调用

解决:

公用时再外部创建一次,保存在session中。

相关代码:

类a.java

HSSFWorkbook workbook = new HSSFWorkbook();
this.setSessAttr("headStyle", expObj.getTabHeadStyle(workbook));
this.setSessAttr("fontStyle", expObj.getFontStyle(workbook));
this.setSessAttr("commonStyle", expObj.getTabCommStyle(workbook));

类expObj.java

package com.net.mcis.action.shifts;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
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.HSSFSimpleShape;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;

import com.net.mcis.data.ho.PInHosView;
import com.net.mcis.data.ho.PVisitOutView;
import com.net.mcis.data.ho.PatsAmount;
import com.net.mcis.data.ho.Shifts;
import com.plat.core.action.BasicAction;
/**
 * @Description: 导出交班簿excel
 */
@SuppressWarnings({"serial"})
public class ExportExcel extends BasicAction{
	public static void main(String[] args) {
		//exportExcel("1","交班簿EXCEL测试(病区A8 2018-07-18)");
		
	}
	@SuppressWarnings("deprecation")
	public boolean exportExcel(HSSFWorkbook workbook,List<Map<String,Object>> list,int day){
		String type = list.get(0).get("type").toString();
		String titStr ="";
		if(type.equals("30")){
			titStr+="历史交班簿";
		}else{
			titStr+="交班簿"+list.get(0).get("wardName").toString()+list.get(0).get("date").toString();
		}
		File file = new File("H:\\poi\\"+titStr+".xls");
		if(!file.exists()){
			try {
				file.createNewFile();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		//创建工作表
		HSSFSheet sheet = workbook.createSheet(day+"");
		//创建行
		HSSFRow row = sheet.createRow(0);
		row.setHeight((short)1000);
		//创建单元格
		HSSFCell cell = row.createCell(0);
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 27));
		sheet.setDefaultColumnWidth((short)3.7);
		String str ="("+list.get(0).get("wardName").toString()+"    "+list.get(0).get("date").toString()+")";
		cell.setCellValue("护士交班簿"+str);
		cell.setCellStyle((HSSFCellStyle)this.getSessAttr("headStyle"));
		//创建工作簿模板
		if(!getTabModal(workbook,sheet,list)){
			return false;
		}
		try {
			OutputStream out = new FileOutputStream(file);
			workbook.write(out);
			out.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			return false;
		} catch (IOException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	/**
	 * @Description: 创建excel表模板与数据处理
	 */
	public boolean getTabModal(HSSFWorkbook workbook,HSSFSheet sheet,List<Map<String,Object>> list){
		Map<String,Object> map = null;
		PatsAmount patAmountMorn = null;
		PatsAmount patAmountNoon = null;
		PatsAmount patAmountMoon = null;
		List<PInHosView> patsInHosList = null;
		List<PInHosView> pInHosList = null;
		List<PVisitOutView> pvList = null;
		List<Shifts> shiftObjList = null;
		if(null!=list&&list.size()>0){
			map = list.get(0);
			patAmountMorn = (PatsAmount) map.get("mornList");
			patAmountNoon = (PatsAmount) map.get("noonList");
			patAmountMoon = (PatsAmount) map.get("moonList");
			patsInHosList =  (List<PInHosView>) map.get("patsInHosList");
			pInHosList =  (List<PInHosView>) map.get("pInHosList");
			pvList = (List<PVisitOutView>) map.get("pvList");
			shiftObjList = (List<Shifts>) map.get("shiftObjList");
		}else{
			return false;
		}
		for(int i=1;i<11;i++){
			HSSFRow rowObj = sheet.createRow(i);
			rowObj.setHeight((short)300);
			for(int j=0;j<=27;j=j+7){
				HSSFCell cellObj = rowObj.createCell(j);
				cellObj.setCellStyle((HSSFCellStyle)this.getSessAttr("commonStyle"));
				if(i<7){
					sheet.addMergedRegion(new CellRangeAddress(i,i,j,j+6));
				}else{
					if(j==7||j==14||j==21){
						sheet.addMergedRegion(new CellRangeAddress(i,i,j,j+6));
					}
				}
				if(i==1){
					switch(j){
						case 0:
							cellObj.setCellValue("交班");break;
						case 7:
							cellObj.setCellValue("早班");break;
						case 14:
							cellObj.setCellValue("中班");break;
						case 21:
							cellObj.setCellValue("晚班");break;
					}
				}
				if(i==2){
					sheet.addMergedRegion(new CellRangeAddress(i,i+2,0,6));
					switch(j){
						case 0:
							cellObj.setCellValue("统计");break;
						case 7:
							if(null!=patAmountMorn){
								cellObj.setCellValue("原有人数:"+patAmountMorn.getOldAmount()+" ;入院:"+patAmountMorn.getInhosAmount()+" ;转入:"+patAmountMorn.getIndeptAmount()+" ;");break;
							}else{
								cellObj.setCellValue("原有人数:/ ;入院:/ ;转入:/ ;");break;
							}
						case 14:
							if(null!=patAmountNoon){
								cellObj.setCellValue("原有人数:"+patAmountNoon.getOldAmount()+" ;入院:"+patAmountNoon.getInhosAmount()+" ;转入:"+patAmountNoon.getIndeptAmount()+" ;");break;
							}else{
								cellObj.setCellValue("原有人数:/ ;入院:/ ;转入:/ ;");break;
							}
						case 21:
							if(null!=patAmountMoon){
								cellObj.setCellValue("原有人数:"+patAmountMoon.getOldAmount()+" ;入院:"+patAmountMoon.getInhosAmount()+" ;转入:"+patAmountMoon.getIndeptAmount()+" ;");break;
							}else{
								cellObj.setCellValue("原有人数:/ ;入院:/ ;转入:/ ;");break;
							}
					}
				}
				if(i==3){
					switch(j){
						case 7:
							if(null!=patAmountMorn){
								cellObj.setCellValue("出院:"+patAmountMorn.getOuthosAmount()+" ;转出:"+patAmountMorn.getOutdeptAmount()+" ;死亡:"+patAmountMorn.getDieAmount()+" ;病危:"+patAmountMorn.getCriticallyIll());break;
							}else{
								cellObj.setCellValue("出院:/;转出:/;死亡:/;病危:/;");break;
							}
						case 14:
							if(null!=patAmountNoon){
								cellObj.setCellValue("出院:"+patAmountNoon.getOuthosAmount()+" ;转出:"+patAmountNoon.getOutdeptAmount()+" ;死亡:"+patAmountNoon.getDieAmount()+" ;病危:"+patAmountNoon.getCriticallyIll());break;
							}else{
								cellObj.setCellValue("出院:/ ;转出:/ ;死亡:/ ;病危:/ ;");break;
							}
						case 21:
							if(null!=patAmountMoon){
								cellObj.setCellValue("出院:"+patAmountMoon.getOuthosAmount()+" ;转出:"+patAmountMoon.getOutdeptAmount()+" ;死亡:"+patAmountMoon.getDieAmount()+" ;病危:"+patAmountMoon.getCriticallyIll());break;
							}else{
								cellObj.setCellValue("出院:/ ;转出:/ ;死亡:/ ;病危:/ ;");break;
							}
					}
				}
				if(i==4){
					switch(j){
						case 7:
							if(null!=patAmountMorn){
								cellObj.setCellValue("手术:"+patAmountMorn.getOperAmount()+" ;分娩:"+patAmountMorn.getChildbirth()+" ;现有人数:"+patAmountMorn.getNowAmount()+" ;");break;
							}else{
								cellObj.setCellValue("手术:/ ;分娩:/ ;现有人数:/ ;");break;
							}
						case 14:
							if(null!=patAmountNoon){
								cellObj.setCellValue("手术:"+patAmountNoon.getOperAmount()+" ;分娩:"+patAmountNoon.getChildbirth()+" ;现有人数:"+patAmountNoon.getNowAmount()+" ;");break;
							}else{
								cellObj.setCellValue("手术:/ ;分娩:/ ;现有人数:/ ;");break;
							}
						case 21:
							if(null!=patAmountMoon){
								cellObj.setCellValue("手术:"+patAmountMoon.getOperAmount()+" ;分娩:"+patAmountMoon.getChildbirth()+" ;现有人数:"+patAmountMoon.getNowAmount()+" ;");break;
							}else{
								cellObj.setCellValue("手术:/ ;分娩:/ ;现有人数:/ ;");break;
							}
					}
				}
				if(i==5||i==8){
					sheet.addMergedRegion(new CellRangeAddress(i,i+2,0,6));
					if(i==5){
						switch(j){
							case 0:
								cellObj.setCellValue("今日入院");break;
							case 7:
								sheet.addMergedRegion(new CellRangeAddress(i,i+2,7,27));
								String str = "";
								if(null!=pInHosList&&pInHosList.size()>0){
									for(PInHosView pInHosObj:pInHosList){
										if(null!=pInHosObj.getDiagShort()&&!"".equals(pInHosObj.getDiagShort())){
											str+=pInHosObj.getBedNo().toString()+"-"+pInHosObj.getPNam()+"("+pInHosObj.getDiagShort()+");";
										}else{
											str+=pInHosObj.getBedNo().toString()+"-"+pInHosObj.getPNam()+";";
										}
									}
								}else{
									str = "今日暂无入院";
								}
								cellObj.setCellValue(str);break;
						}
					}else{
						switch(j){
							case 0:
								cellObj.setCellValue("今日出院");break;
							case 7:
								sheet.addMergedRegion(new CellRangeAddress(i,i+2,7,27));
								String str = "";
								if(null!=pvList&&pvList.size()>0){
									for(PVisitOutView pvObj:pvList){
										str+=pvObj.getBedShow()+"-"+pvObj.getPNam()+" ;";
									}
								}else{
									str = "今日暂无出院";
								}
								cellObj.setCellValue(str);break;
						}
					}
					
				}
			}
		}
		if(null!=patsInHosList&&patsInHosList.size()>0){
			int length = 3*patsInHosList.size()+11;
			sheet.addMergedRegion(new CellRangeAddress(11,length-1,0,1));
			for(int i=11;i<length;i++){
				if((i-11)%3==0){
					sheet.addMergedRegion(new CellRangeAddress(i,i+2,2,6));
					for(int j=0;j<=27;j++){
						if(j==7||j==14||j==21){
							sheet.addMergedRegion(new CellRangeAddress(i,i,j,j+6));
							sheet.addMergedRegion(new CellRangeAddress(i+1,i+2,j,j+6));
						}
					}
					HSSFRow rowObj = sheet.createRow(i);
					HSSFRow shiftRowObj = sheet.createRow(i+1);
					HSSFRow shiftRowObj2 = sheet.createRow(i+2);
					rowObj.setHeight((short)300);
					shiftRowObj.setHeight((short)300);
					shiftRowObj2.setHeight((short)300);
					HSSFCell shiftCellObj = null;
					HSSFCell descCellObj = null;
					HSSFCell cellObj = rowObj.createCell(2);
					if(i==11){
						HSSFCell cellObj2 = rowObj.createCell(0);
						cellObj2.setCellStyle(getFontStyle(workbook));//字体红色
						cellObj2.setCellValue("手术");
					}
					PInHosView pat = null;
					List<Shifts> objShift = new ArrayList<Shifts>();
					int num = (i-11)/3;
					pat = patsInHosList.get(num);
					String infoStr = pat.getBedShow()+"-"+pat.getPNam()+"\r\n"+pat.getDiagShort();
					cellObj.setCellStyle((HSSFCellStyle)this.getSessAttr("commonStyle"));//getTopStyle(workbook)
					cellObj.setCellValue(new HSSFRichTextString(infoStr));
					if(null!=shiftObjList&&shiftObjList.size()>0){
						for(int k=0;k<shiftObjList.size();k++){
							Shifts shiftTempObj = shiftObjList.get(k);
							if(pat.getPid().equals(shiftTempObj.getPatientId())){
								objShift.add(shiftTempObj);
							}
						}
					}
					if(null!=objShift&&objShift.size()>0){
						for(int t=0;t<objShift.size();t++){
							Shifts shiftTempObj = objShift.get(t);
							int tempShift = shiftTempObj.getShift();
							if(1==tempShift){
								shiftCellObj = rowObj.createCell(7);
								shiftCellObj.setCellValue(shiftTempObj.getTimePoint()+"  T:"+shiftTempObj.getTemperature()+"    P:"+shiftTempObj.getPulse()+"    R:"+shiftTempObj.getRespire());
								descCellObj = shiftRowObj.createCell(7);
								descCellObj.setCellValue(shiftTempObj.getShiftNote());
							}else if(2==tempShift){
								shiftCellObj = rowObj.createCell(14);
								shiftCellObj.setCellValue(shiftTempObj.getTimePoint()+"  T:"+shiftTempObj.getTemperature()+"    P:"+shiftTempObj.getPulse()+"    R:"+shiftTempObj.getRespire());
								descCellObj = shiftRowObj.createCell(14);
								descCellObj.setCellValue(shiftTempObj.getShiftNote());
							}else if(3==tempShift){
								shiftCellObj = rowObj.createCell(21);
								shiftCellObj.setCellValue(shiftTempObj.getTimePoint()+"  T:"+shiftTempObj.getTemperature()+"    P:"+shiftTempObj.getPulse()+"    R:"+shiftTempObj.getRespire());
								descCellObj = shiftRowObj.createCell(21);
								descCellObj.setCellValue(shiftTempObj.getShiftNote());
							}
							shiftCellObj.setCellStyle((HSSFCellStyle)this.getSessAttr("commonStyle"));
							descCellObj.setCellStyle((HSSFCellStyle)this.getSessAttr("commonStyle"));
						}
					}
				}
			}
		}
		return true;
	}
	
	/**
	 * @Description: 画斜线
	 */
	private void drawLine(HSSFSheet sheet) {
		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
		HSSFClientAnchor a = new HSSFClientAnchor(0, 0, 1023, 255, (short)0, 2, (short)3, 4);
		HSSFSimpleShape shape1 = patriarch.createSimpleShape(a);
		shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); 
		shape1.setLineStyle(HSSFSimpleShape.LINESTYLE_SOLID) ;
	}
	/**
	 * @Description  表头样式 
	 * @param workbook 工作簿
	 * @param cell 表头
	 */
	public HSSFCellStyle getTabHeadStyle(HSSFWorkbook workbook){
        HSSFFont font = workbook.createFont();
        HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        font.setFontHeightInPoints((short)16);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName("songti");
        style.setFont(font);
        return style;
	}
	/**
	 * @Description  公共样式
	 * @param workbook 工作簿
	 */
	public HSSFCellStyle getTabCommStyle(HSSFWorkbook workbook){
        HSSFFont font = workbook.createFont();
        HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        font.setFontHeightInPoints((short)8);
        font.setFontName("songti");
        style.setFont(font);
        style.setWrapText(true);
        return style;
	}
	/**
	 * @Description: 设置手术样式
	 */
	public HSSFCellStyle getFontStyle(HSSFWorkbook workbook){
		HSSFFont font = workbook.createFont();
        HSSFCellStyle style = workbook.createCellStyle();
        font.setColor(HSSFColor.RED.index);
        font.setFontHeightInPoints((short)8);
        font.setFontName("songti");
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setFont(font);
        return style;
	}
}

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值