EOS导出EXCEL
//通用导出jsp页面
<%@page import="com.tenglong.bidmanage.BidSituation"%>
<%@ page contentType="text/html;charset=utf-8" %>
<%
BidSituation.exportBidSituation(request, response);
out.clear();
out = pageContext.pushBody();
%>
//导出按钮jsp页面
<a class="nui-button" onclick="exportInfo()">导出</a>
<script type="text/javascript">
function exportInfo(){
var batchcode= nui.get("batchcode").getValue();
var tendercode= nui.get("tendercode").getValue();
var tendername= nui.get("tendername").getValue();
var packagenum= nui.get("packagenum").getValue();
var suppliername= nui.get("suppliername").getValue();
var dbsuppliername= nui.get("dbsuppliernamess").getValue();
var ecpsuppliername= nui.get("ecpsuppliernames").getValue();
window.open("<%=request.getContextPath()%>/bidmanage/BidSituation/BidSituationExport.jsp?batchcode="
+batchcode+"&tendercode="+tendercode+"&tendername="+tendername+"&packagenum="+packagenum+"&suppliername="
+suppliername+"&dbsuppliername="+dbsuppliername+"&ecpsuppliername="+ecpsuppliername,'newwindow');
}
</script>
//java代码
package com.tenglong.bidmanage;
import java.awt.Color;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;
import com.eos.foundation.database.DatabaseUtil;
import com.eos.system.annotation.Bizlet;
import commonj.sdo.DataObject;
import commonj.sdo.helper.DataFactory;
public class BidSituation {
static com.eos.system.logging.Logger log = com.eos.runtime.core.TraceLoggerFactory.getLogger(BidSituation.class);
/**
* 创建字体
*
* @param workbook
* @param size
* 字体大小
* @return
*/
public static XSSFFont createFont(XSSFWorkbook workbook, short size) {
XSSFFont font = workbook.createFont();
// 字体样式
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
// 字体颜色
font.setColor(XSSFFont.COLOR_NORMAL);
// 字体大小
if (0 == size) {
font.setFontHeightInPoints(XSSFFont.DEFAULT_FONT_SIZE);
} else {
font.setFontHeightInPoints(size);
}
font.setFontName("宋体");
return font;
}
/**
* 创建CellStyle
*
* @param workbook
* @param XSSFColor
* color 颜色
*/
public static XSSFCellStyle createStyle(XSSFWorkbook workbook, XSSFColor color,XSSFFont font) {
XSSFCellStyle cellStyle = workbook.createCellStyle();
// 对齐样式
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置颜色
//cellStyle.setFillForegroundColor(color);
//cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setBorderTop(BorderStyle.MEDIUM);
cellStyle.setBorderBottom(BorderStyle.MEDIUM);
cellStyle.setBorderLeft(BorderStyle.MEDIUM);
cellStyle.setBorderRight(BorderStyle.MEDIUM);
// 设置边框颜色
cellStyle.setBorderColor(XSSFCellBorder.BorderSide.TOP, new XSSFColor(Color.BLACK));
cellStyle.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, new XSSFColor(Color.BLACK));
cellStyle.setBorderColor(XSSFCellBorder.BorderSide.LEFT, new XSSFColor(Color.BLACK));
cellStyle.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, new XSSFColor(Color.BLACK));
cellStyle.setBorderTop((short) 1);
cellStyle.setBorderBottom((short) 1);
cellStyle.setBorderLeft((short) 1);
cellStyle.setBorderRight((short) 1);
// 设置字体
// cellStyle.setFont(font);
// 设置自动换行
cellStyle.setWrapText(true);
return cellStyle;
}
public static XSSFCellStyle createStyles(XSSFWorkbook workbook, XSSFColor color,XSSFFont font) {
XSSFCellStyle cellStyle = workbook.createCellStyle();
// 对齐样式
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置颜色
//cellStyle.setFillForegroundColor(color);
//cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setBorderTop(BorderStyle.MEDIUM);
cellStyle.setBorderBottom(BorderStyle.MEDIUM);
cellStyle.setBorderLeft(BorderStyle.MEDIUM);
cellStyle.setBorderRight(BorderStyle.MEDIUM);
// 设置边框颜色
cellStyle.setBorderColor(XSSFCellBorder.BorderSide.TOP, new XSSFColor(Color.BLACK));
cellStyle.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, new XSSFColor(Color.BLACK));
cellStyle.setBorderColor(XSSFCellBorder.BorderSide.LEFT, new XSSFColor(Color.BLACK));
cellStyle.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, new XSSFColor(Color.BLACK));
// 设置字体
cellStyle.setFont(font);
// 设置自动换行
cellStyle.setWrapText(true);
return cellStyle;
}
/**
* 写详细数据
* @param sheet
* @param list0 初评数据
* @param rownum 数据从第几行开始写(首行为0)
* @param style
* @return
* @author chenqf
* @date 2016年8月15日 上午11:38:39
*/
public static XSSFSheet writeData(XSSFSheet sheet,Object[] list,int rownum,XSSFCellStyle style){
style.setFillForegroundColor(new XSSFColor(new Color(255, 255, 255)));
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Row row = sheet.createRow(rownum+1);//创建数据行
//Cell cell0 = row.createCell(0);
//初评数据
if(list.length>0){
for(int i=0;i<list.length;i++){
Cell cell = row.createCell(i);
cell.setCellStyle(style);
cell.setCellValue(list[i]==null?"":list[i].toString());
setCellWidth(cell);
}
}
return sheet;
}
/**
* 得到i和j对应的要合并的ii和jj
* @param allCell
* @param i
* @param j
* @param mergeTagetIj
*/
public static void getMergeTaget(Cell[][] allCell, int i, int j, int[] mergeTagetIj) {
int jj = j+1;
int ii = i+1;
for (; jj < allCell[i].length; jj++) {
if(allCell[i][j]!=null&&allCell[i][jj]!=null){
if(!allCell[i][j].getStringCellValue().equals(allCell[i][jj].getStringCellValue())){
break;
}else{
allCell[i][jj]=null;
mergeTagetIj[0]=i;
mergeTagetIj[1]=jj;
}
}else{
break;
}
}
for (; ii < allCell.length; ii++) {
if(allCell[i][j]!=null&&allCell[ii][j]!=null){
if(!allCell[i][j].getStringCellValue().equals(allCell[ii][j].getStringCellValue())){
break;
}else{
if((jj-1)<allCell[i].length&&allCell[ii][jj-1]!=null&&allCell[i][j].getStringCellValue().equals(allCell[ii][jj-1].getStringCellValue())){
allCell[ii][j]=null;
mergeTagetIj[0]=ii;
mergeTagetIj[1]=jj-1;
}
}
}
}
allCell[i][j]=null;
}
/**
* 功能：合并单元格
*
* @param sheet
* XSSFSheet
* @param firstRow
* int
* @param lastRow
* int
* @param firstColumn
* int
* @param lastColumn
* int
* @return int 合并区域号码
*/
public static int mergeCell(XSSFSheet sheet, int firstRow, int lastRow, int firstColumn, int lastColumn) {
return sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn));
}
/**
* 设置单元格宽度
* @param cell
* @author chenqf
* @date 2016年9月23日 下午4:28:39
*/
public static void setCellWidth(Cell cell){
Sheet sheet = cell.getRow().getSheet();
int cellNum = cell.getColumnIndex();
sheet.autoSizeColumn(cellNum);
if(sheet.getColumnWidth(cellNum)<20*256){
sheet.setColumnWidth(cellNum, 20*256);
}else if(sheet.getColumnWidth(cellNum)>60*256&&sheet.getColumnWidth(cellNum)<120*256){
sheet.setColumnWidth(cellNum, sheet.getColumnWidth(cellNum)/2+2*256);
}else if(sheet.getColumnWidth(cellNum)>120*256&&sheet.getColumnWidth(cellNum)<180*256){
sheet.setColumnWidth(cellNum, sheet.getColumnWidth(cellNum)/3+3*256);
}else if(sheet.getColumnWidth(cellNum)>180*256){
sheet.setColumnWidth(cellNum, sheet.getColumnWidth(cellNum)/4+4*256);
}
}
/***
* 结果导出
* @param request
* @param response
* @throws UnsupportedEncodingException
*/
public static void exportBidSituation(HttpServletRequest request, HttpServletResponse response) throws UnsupportedEncodingException {
String batchcode = request.getParameter("batchcode") == null ? "" : request.getParameter("batchcode").toString();
String tendercode = request.getParameter("tendercode") == null ? "" : request.getParameter("tendercode").toString();
String tendername = request.getParameter("tendername") == null ? "" : request.getParameter("tendername").toString();
String packagenum = request.getParameter("packagenum") == null ? "" : request.getParameter("packagenum").toString();
String suppliername = request.getParameter("suppliername") == null ? "" : request.getParameter("suppliername").toString();
String dbsuppliername = request.getParameter("dbsuppliername") == null ? "" : request.getParameter("dbsuppliername").toString();
String ecpsuppliername = request.getParameter("ecpsuppliername") == null ? "" : request.getParameter("ecpsuppliername").toString();
//获取管理部门
DataObject criteriaEntity = DataFactory.INSTANCE.create("com.primeton.das.criteria", "criteriaType");
criteriaEntity.set("_entity","com.tenglong.ztb.datasets.ztbData.BidSituation");
criteriaEntity.set("_expr[1]/_op", "=");//
criteriaEntity.set("_expr[1]/batchcode",batchcode );
criteriaEntity.set("_expr[2]/_op", "=");//
criteriaEntity.set("_expr[2]/tendercode",tendercode );
criteriaEntity.set("_expr[3]/_op", "like");//
criteriaEntity.set("_expr[3]/tendername",tendername );
criteriaEntity.set("_expr[4]/_op", "=");//
criteriaEntity.set("_expr[4]/packagenum",packagenum );
criteriaEntity.set("_expr[5]/_op", "like");//
criteriaEntity.set("_expr[5]/suppliername",suppliername );
if(dbsuppliername != null && dbsuppliername != ""){
if(dbsuppliername.equals("0")){
criteriaEntity.set("_expr[6]/_op", "null");//
criteriaEntity.set("_expr[6]/dbsuppliername", "");
} else {
criteriaEntity.set("_expr[6]/_op", "<>");//
criteriaEntity.set("_expr[6]/dbsuppliername", "null");
}
}
if(ecpsuppliername != null && ecpsuppliername != ""){
if(ecpsuppliername.equals("0")){
criteriaEntity.set("_expr[7]/_op", "null");
criteriaEntity.set("_expr[7]/ecpsuppliername", "");
} else {
criteriaEntity.set("_expr[7]/_op", "<>");//
criteriaEntity.set("_expr[7]/ecpsuppliername", "null");
}
}
DataObject[] data = DatabaseUtil.queryEntitiesByCriteriaEntity("default", criteriaEntity);
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFFont font = createFont(workbook, (short) 0);
XSSFColor color = new XSSFColor(new Color(28, 232, 123));
XSSFCellStyle style = createStyle(workbook,color,font);
XSSFCellStyle styles = createStyles(workbook,color,font);
//设置sheet名称
XSSFSheet sheet;
sheet = workbook.createSheet("投标情况核对");
sheet.setDefaultColumnWidth((short)30);
if(data!=null && data.length>0){
sheet = writeTitleBatch(sheet,style,styles);
for (int i = 0; i < data.length; i++) {
String state;
String states;
if(data[i].getString("dbsuppliername")!=null){
state = Character.toString((char)8730);
} else {
state = Character.toString((char)215);
}
if(data[i].getString("ecpsuppliername")!=null){
states = Character.toString((char)8730);
} else {
states = Character.toString((char)215);
}
Object[] obj = new Object[]{data[i].getString("batchcode"),data[i].getString("tendercode"),
data[i].getString("tendername"),data[i].getString("packagenum"),data[i].getString("suppliername"),
state,states};
sheet = writeData(sheet, obj, i, style);
}
}else{
sheet = writeTitleBatch(sheet,style,styles);
}
//下载
try {
String fileName = new String(("投标情况核对数据.xlsx").getBytes(),"ISO-8859-1");
response.setContentType("APPLICATION/OCTET-STREAM; charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
OutputStream oss = response.getOutputStream();
workbook.write(oss);
oss.flush();
oss.close();
} catch (IOException e) {
log.error(e.getMessage(),e);
}
}
//表头数据
public static XSSFSheet writeTitleBatch(XSSFSheet sheet,XSSFCellStyle style,XSSFCellStyle styles){
style.setFillForegroundColor(new XSSFColor(new Color(255, 255, 255)));
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
String[] titleStr = {"招标批次编号","分标编号","分标名称","包号","投标人名称","电子标书上传情况","ECP投标情况"};
int[] titleWidth = {12,12,12,12,12,12,12};
Row row = sheet.createRow(0);//创建一个行
row.setHeightInPoints(30);
//写基本信息表头
for(int i=0;i<titleStr.length;i++){
Cell cell = row.createCell(i);//创建单元格
cell.setCellStyle(styles);
cell.setCellValue(titleStr[i]);
sheet.setColumnWidth(i, titleWidth[i]*256);//宽度为256分之一
}
return sheet;
}
}
