poi导出excel

leegh

2018-07-26

关注关注

问题描述：

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()+""+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;
}
}