1.JSP页面
<td width=165>
<div align="left">
<!-- 添加 2008-04-11 16:42 -->
<bean:define id="scoreId" name='Info' property='scoreId' />
<a href="scoreSet.do?do=importScore&scoreid=<%=scoreId%>" target="scoreexel">统计导出</a>
<!--
<html:link action="softevaluate/scoreSet.do?do=setScore" paramName="Info" paramProperty="scoreId" paramId="scoreId">
参数设置
</html:link>
<!--
<a href="scoreOperation.do?do=scoreSetE&scoreid=<%=scoreId%>" target="scoreexel">统计导出</a>
<a href="scoreOperation.do?do=scoreSetE&scoreid=<%=scoreId%>">统计导出</a>
<a href="softevaluate/scoreSet.do?do=setScore">统计导出</a>
-->
</div>
</td>
2.ScoreSetAction.java
public class ScoreSetAction extends DispatchAction
{
/******实现EXCEL数据导出*******/
public ActionForward importScore(ActionMapping mapping, ActionForm
form,HttpServletRequest request,HttpServletResponse response)throws
Exception
{
// 导出数据类型，此处是写死的为3。
String scoreid = request.getParameter("scoreid");
String []str=new String[3];
scoreid=scoreid.replace("/", "");
String maxscore = "";
String minscore = "";
ScoreSetExport scoreSetExport = new ScoreSetExport();
LinkedHashMap hm = new LinkedHashMap();
SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
org.hibernate.classic.Session session = sessionFactory.openSession();
Query query =session.createQuery("from ScoreViable");
List users=query.list();
session.close();
sessionFactory.close();
for (ListIterator iterator = users.listIterator(); iterator.hasNext(); )
{
ScoreViable user = (ScoreViable) iterator.next();
System.out.println(user.getBeforCount() +"\n\tAge: " + user.getEndCount());
maxscore=user.getBeforCount()+"";
minscore=user.getEndCount()+"";
}
str[0]=scoreid;
str[1]=maxscore;
str[2]=minscore;
List<ScoreRecordDetail> list = scoreSetExport.exportScoreSet(str,hm);
ExcelManagerIf eem = ExcelManagerFactory.getInstance();
/******************************************************************/
/**不去掉一行仍然调用export,去掉一行调用export1,只针对一种汇总导出*/
//eem.export(response, list,hm);
eem.export1(response, list,hm);
/******************************************************************/
return null;
}
}
3.ExcelManagerIf.java
import java.io.InputStream;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import com.gpdi.orm.object.ScoreRecordDetail;
public interface ExcelManagerIf
{
/********************************************************************************************************/
/** 2008-04-18 11:54*/
public boolean export1(HttpServletResponse response, List<ScoreRecordDetail> list,LinkedHashMap hashMap);
/********************************************************************************************************/
}
4.ExcelManagerFactory.java
public class ExcelManagerFactory
{
private static String key = "key";
private ExcelManagerFactory() {
}
private static ExcelManagerIf instance = null;
public static ExcelManagerIf getInstance(){
if(instance == null){
synchronized(key){
if(instance == null){
instance = new ExcelManagerImpl();
}
}
}
return instance;
}
}
5.ExcelManagerImpl.java
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
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;
import org.apache.poi.hssf.util.Region;
import org.hibernate.Query;
import org.hibernate.ScrollableResults;
import org.hibernate.Session;
public class ExcelManagerImpl implements ExcelManagerIf
{
/***************************************************************************************************************/
/**2008-04-18 11:36 汇总导出去掉无用的空格*/
public boolean export1(HttpServletResponse response, List<ScoreRecordDetail> list,LinkedHashMap hashMap) {
Session session = null;
try {
// 生成电子表格文件
HSSFWorkbook wb = new HSSFWorkbook();
// 通过先建立表格，后命名可以定义使用的编码，从而保证汉字的正常显示
HSSFSheet sheet1 = wb.createSheet();
/********************************************/
/**添加 2008-04-15导出打印默认方式,true：横向 false：纵向*/
sheet1.getPrintSetup().setLandscape(true);
/********************************************/
sheet1.setColumnWidth((short) 0, (short) 5000);
sheet1.setColumnWidth((short) 1, (short) 5000);
sheet1.setColumnWidth((short) 2, (short) 5000);
sheet1.setColumnWidth((short) 3, (short) 5000);
// wb.setSheetName(0, "开发商资料",HSSFWorkbook.ENCODING_UTF_16);
// HSSFRow row;
// 先定义一个字体对象
HSSFFont font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 16);// 字体大小
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
// 定义表头单元格格式
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setFont(font);// 单元格字体
cellStyle.setBorderBottom(cellStyle.BORDER_THIN);// 单元格边框
cellStyle.setBorderTop(cellStyle.BORDER_THIN);
cellStyle.setBorderRight(cellStyle.BORDER_THIN);
cellStyle.setBorderLeft(cellStyle.BORDER_THIN);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平对齐方式
// 定义新的调色板以便定义自己的颜色
HSSFPalette palette = wb.getCustomPalette();
// 替换一个默认调色板中已经定义的颜色
palette.setColorAtIndex(HSSFColor.BLUE_GREY.index, (byte) 230, (byte) 230, (byte) 255);
// 使用刚才定义的颜色为填充色
cellStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
cellStyle.setFillPattern((short) HSSFCellStyle.SOLID_FOREGROUND);
// 写入表头
HSSFCell c;
// String[] headerName = (String[]) aHeaderName.get(type);
int[] maxLen = new int[4];
int maxCol = list.size() + 3;
ScoreRecordDetail first = list.get(0);
Long scoreId = first.getScoreRecord().getScoreid();
session = HibernateSessionFactory.getCurrentSession();
ScoreSet scoreSet = (ScoreSet) session.createQuery("from ScoreSet where scoreid = :scoreid").setLong("scoreid",
scoreId).uniqueResult();
String titleName = scoreSet.getScoreLot().getLotName() + "-" + scoreSet.getScoreModel().getScoremodelname();
//输出文件名落孙山
String outFileName = scoreSet.getScoreModel().getScoremodelname();
// 在表单中新建0行
HSSFRow row0 = sheet1.createRow((short) 0);
row0.setHeight((short) 600);
c = row0.createCell((short) 0);
HSSFCellStyle cellStyle0 = wb.createCellStyle();
cellStyle0.setFont(font);// 单元格字体0
cellStyle0.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平对齐方式
cellStyle0.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle0.setBorderBottom(cellStyle.BORDER_THIN);// 单元格边框
cellStyle0.setBorderTop(cellStyle.BORDER_THIN);
cellStyle0.setBorderRight(cellStyle.BORDER_THIN);
cellStyle0.setBorderLeft(cellStyle.BORDER_THIN);
c.setCellStyle(cellStyle0);// 单元格的样式
c.setEncoding(HSSFCell.ENCODING_UTF_16);// 编码
c.setCellValue(titleName);// 单元格值
sheet1.addMergedRegion(new Region(0, (short) 0, 0, (short) (maxCol-1)));
// 在表单中新建1行
/**
HSSFRow row1 = sheet1.createRow((short) 1);
row1.setHeight((short) 700);
c = row1.createCell((short) 0);
HSSFCellStyle cellStyle1 = wb.createCellStyle();
cellStyle1.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平对齐方式
cellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle1.setBorderBottom(cellStyle.BORDER_THIN);// 单元格边框
cellStyle1.setBorderTop(cellStyle.BORDER_THIN);
cellStyle1.setBorderRight(cellStyle.BORDER_THIN);
cellStyle1.setBorderLeft(cellStyle.BORDER_THIN);
c.setCellStyle(cellStyle1);// 单元格的样式
c.setEncoding(HSSFCell.ENCODING_UTF_16);// 编码
c.setCellValue("评标专家签字：");// 单元格值
c = row1.createCell((short) 1);
c.setCellStyle(cellStyle1);// 单元格的样式
c = row1.createCell((short) 2);
c.setCellStyle(cellStyle1);// 单元格的样式
c.setEncoding(HSSFCell.ENCODING_UTF_16);// 编码
c.setCellValue("日期：");// 单元格值
c = row1.createCell((short) 3);
c.setCellStyle(cellStyle1);// 单元格的样式
c.setEncoding(HSSFCell.ENCODING_UTF_16);// 编码
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
c.setCellValue(sdf.format(new Date()));// 单元格值
*/
// 在表单中新建2行
HSSFRow row2 = sheet1.createRow((short) 1);
row2.setHeight((short) 600);
HSSFCellStyle cellStyle2 = wb.createCellStyle();
cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平对齐方式
cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle2.setFillBackgroundColor(HSSFColor.AQUA.index);
cellStyle2.setBorderBottom(cellStyle.BORDER_THIN);// 单元格边框
cellStyle2.setBorderTop(cellStyle.BORDER_THIN);
cellStyle2.setBorderRight(cellStyle.BORDER_THIN);
cellStyle2.setBorderLeft(cellStyle.BORDER_THIN);
c = row2.createCell((short) 0);
c.setCellStyle(cellStyle2);// 单元格的样式
c.setEncoding(HSSFCell.ENCODING_UTF_16);// 编码
c.setCellValue("专家名称");// 单元格值
/*****************************************************************/
sheet1.addMergedRegion(new Region(1, (short) 0, 1, (short) 1));
//sheet1.addMergedRegion(new Region(2, (short) 0, 2, (short) 1));
/*****************************************************************/
row2.setHeight((short) 400);
c = row2.createCell((short) 1);
c.setCellStyle(cellStyle2);// 单元格的样式
c.setEncoding(HSSFCell.ENCODING_UTF_16);// 编码
//c.setCellValue("项目");// 单元格值
row2.setHeight((short) 400);
c = row2.createCell((short) 2);
c.setCellStyle(cellStyle2);// 单元格的样式
c.setEncoding(HSSFCell.ENCODING_UTF_16);// 编码
c.setCellValue("分值");// 单元格值
//c = row2.createCell((short) 3);
//c.setCellStyle(cellStyle2);// 单元格的样式
//重复行
int rows=0;
if(hashMap!=null){
int j=0;
HSSFRow[] row_repeat= null;
for (Iterator iterator = hashMap.values().iterator(); iterator.hasNext();) {
Map map= (LinkedHashMap) iterator.next();
rows =map.size();
row_repeat=new HSSFRow[map.size()];
for(int i=0;i<rows;i++){
/****************************************************/
row_repeat[i] = sheet1.createRow((short) (2+i));
//row_repeat[i] = sheet1.createRow((short) (3+i));
/****************************************************/
row_repeat[i].setHeight((short) 400);
}
break;
}
for (Iterator iterator = hashMap.values().iterator(); iterator.hasNext();) {
Map map= (LinkedHashMap) iterator.next();
int i=0;
for (Iterator iter_key = map.keySet().iterator(); iter_key.hasNext();) {
SysUser sysUser = (SysUser) iter_key.next();
if(j==0){
c = row_repeat[i].createCell((short) 0);
c.setCellStyle(cellStyle2);// 单元格的样式
c.setEncoding(HSSFCell.ENCODING_UTF_16);// 编码
c.setCellValue(sysUser.getUsername());// 单元格值
/******************************************************************/
sheet1.addMergedRegion(new Region(2+i, (short) 0, 2+i, (short) 1));
//sheet1.addMergedRegion(new Region(3+i, (short) 0, 3+i, (short) 1));
/******************************************************************/
c = row_repeat[i].createCell((short) 1);
c.setCellStyle(cellStyle2);// 单元格的样式
c = row_repeat[i].createCell((short) 2);
c.setCellStyle(cellStyle2);// 单元格的样式
c.setEncoding(HSSFCell.ENCODING_UTF_16);// 编码
c.setCellValue("40");// 单元格值
}
c = row_repeat[i].createCell((short) (3+j));
c.setCellStyle(cellStyle2);// 单元格的样式
c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);//数字型
c.setEncoding(HSSFCell.ENCODING_UTF_16);// 编码
c.setCellValue(Double.parseDouble(map.get(sysUser).toString()));// 单元格值
i++;
}
j++;
}
}
// 在表单中新建3行
/************************************************/
HSSFRow row3 = sheet1.createRow((short) (2+rows));
//HSSFRow row3 = sheet1.createRow((short) (3+rows));
/************************************************/
row3.setHeight((short) 400);
c = row3.createCell((short) 0);
c.setCellStyle(cellStyle2);// 单元格的样式
c.setEncoding(HSSFCell.ENCODING_UTF_16);// 编码
c.setCellValue(scoreSet.getScoreModel().getScoremodelname() + " 评分");// 单元格值
/******************************************************************************/
//sheet1.addMergedRegion(new Region((3+rows), (short) 0,(3+rows), (short) 1));
sheet1.addMergedRegion(new Region((2+rows), (short) 0,(2+rows), (short) 1));
/******************************************************************************/
c = row3.createCell((short) 1);
c.setCellStyle(cellStyle2);// 单元格的样式
c = row3.createCell((short) 2);
c.setCellStyle(cellStyle2);// 单元格的样式
c.setEncoding(HSSFCell.ENCODING_UTF_16);// 编码
c.setCellValue("40");// 单元格值
for (int i = 0; i < list.size(); i++) {
sheet1.setColumnWidth((short) (i + 3), (short) 5000);
ScoreRecordDetail o = list.get(i);
String name = "";
if (o.getDevelopid() != null) {
BasDevelop basDevelop = (BasDevelop) session.createQuery("from BasDevelop where developid = :developid")
.setLong("developid", o.getDevelopid()).uniqueResult();
name = basDevelop.getDename();
} else {
ProductType productType = (ProductType) session.createQuery("from ProductType where productid = :productid")
.setLong("productid", o.getProductid()).uniqueResult();
name = productType.getProdname();
}
/****************************************************************************/
//c = row1.createCell((short) (i + 3));
//c.setCellStyle(cellStyle1);// 单元格的样式
/****************************************************************************/
c = row2.createCell((short) (i + 3));
c.setCellStyle(cellStyle2);// 单元格的样式
c.setEncoding(HSSFCell.ENCODING_UTF_16);// 编码
c.setCellValue(name);// 单元格值
c = row3.createCell((short) (i + 3));
c.setCellStyle(cellStyle2);// 单元格的样式
c.setEncoding(HSSFCell.ENCODING_UTF_16);// 编码
c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);//数字型
c.setCellValue(o.getScore());// 单元格值
}
// 输出Excel文件
errorOrExportExcel(response, wb, 2 ,outFileName);
} catch (Exception e) {
e.printStackTrace();
}
return false;
}
//结束
/***************************************************************************************************************/
}