java生成excel存放到web-inf
package cn.gov.customs.xls;
import java.io.File;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
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.HSSFPalette;
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.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import cn.gov.customs.CadpSession;
import cn.gov.customs.report.bizview.V_BIZQUERYLS;
import cn.gov.customs.report.bizview.V_BIZQUERYZS;
import cn.gov.customs.toolkits.ToolKit;
import com.eos.das.entity.criteria.CriteriaType;
import com.eos.das.entity.criteria.ExprType;
import com.eos.data.datacontext.DataContextManager;
import com.eos.data.datacontext.IMUODataContext;
import com.eos.foundation.PageCond;
import com.eos.foundation.common.utils.FileUtil;
import com.eos.foundation.database.DatabaseExt;
import com.eos.spring.DASDaoSupport;
import com.eos.spring.TraceLogger;
import com.eos.system.exception.EOSException;
import commonj.sdo.DataObject;
public class XlsService extends DASDaoSupport implements Ixls {
TraceLogger logger = new TraceLogger("");
@Override
public String exportoffline(CriteriaType criteriaType) {
String headtitle = "备案管理查询";
int datalength = 0;
int szc = 0;
int ssx = 0;
int szx = 0;
// 关区名称 1列 , 类别 12*3列 合计 1*3列
String[] namerow1 = { "关区名称", "类别", "合计" };
String[] namerow2 = { "码头类监管场所", "公路转关监管点", "陆路边境口岸监管场所", "货栈类监管场所", "堆场类监管场所", "仓库类监管场所", "储罐类监管场所", "快件类监管场所", "边民互市贸易类监管场所", "台轮停泊点类监管场所", "旅客通关类监管场所", "国际邮件类监管场所" };
String[] namerow3 = { "在运营", "《注册登记证书》已过期", "已注销" };
String[] namerow2key = { "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12" };
String[] namekeylx = { "zc", "sx", "zx" };
String[] namekeyhj = { "szc", "ssx", "szx" };
int datarow = 6;
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(headtitle);
sheet.setDefaultColumnWidth((short) 18);
HSSFRow row = sheet.createRow(0);
HSSFRow row5 = sheet.createRow(5);
sheet.setColumnWidth((short) 0, (short) 4600);// 设置列宽
HSSFCellStyle headerStyle = wb.createCellStyle();
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局:居中
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
HSSFFont headerFont = wb.createFont(); // 创建字体样式
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
headerFont.setFontName("Times New Roman"); // 设置字体类型
headerFont.setColor(HSSFFont.COLOR_RED);
headerFont.setFontHeightInPoints((short) 18); // 设置字体大小
headerStyle.setFont(headerFont); // 为标题样式设置字体样式
HSSFCellStyle midStyle = wb.createCellStyle();
midStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
midStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
midStyle.setBottomBorderColor(HSSFColor.BLACK.index);
midStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
midStyle.setLeftBorderColor(HSSFColor.BLACK.index);
midStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
midStyle.setRightBorderColor(HSSFColor.BLACK.index);
midStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
midStyle.setTopBorderColor(HSSFColor.BLACK.index);
HSSFFont midFont = wb.createFont(); // 创建字体样式
midFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
HSSFPalette customPalette = wb.getCustomPalette();
customPalette.setColorAtIndex((short) 9, (byte) (192), (byte) (192), (byte) (192));
midFont.setColor((short) 9);
midFont.setFontName("Times New Roman"); // 设置字体类型
midFont.setFontHeightInPoints((short) 13); // 设置字体大小
midStyle.setFont(midFont); // 为标题样式设置字体样式
midStyle.setFillBackgroundColor(HSSFColor.OLIVE_GREEN.index);
HSSFCellStyle row2style = wb.createCellStyle();
row2style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
row2style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
row2style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
row2style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
row2style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局:居中
row2style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
HSSFCell celltmp = null;
for (int i = 3; i < 6; i++) {
row = sheet.createRow(i);
for (int j = 1; j < 41; j++) {
celltmp = row.createCell((short)j);
celltmp.setCellStyle(row2style);
}
}
// Region region1ROW = new Region((short) 0, (short) 1, (short) 3, (short) (namerow2.length * 3 + 4));// 第0行,
// sheet.addMergedRegion(region1ROW); 目前是不要
// 得到所有区域
sheet.getNumMergedRegions();
HSSFRow row3 = sheet.createRow(3);// 关区 , 分类 , 合计
HSSFRow row4 = sheet.createRow(4);// 12分类所在行
HSSFCell celllgqnm = row3.createCell((short) 1);
celllgqnm.setCellValue(new HSSFRichTextString(namerow1[0]));
celllgqnm.setCellStyle(row2style);
HSSFCell celllb = null;
HSSFCell cellzt = null;
HSSFCell gqnmcell = row3.createCell((short) 1);
HSSFCell hjcell = row3.createCell((short) 1);
Region gqnmrgn = new Region((short) 3, (short) (1), (short) 5, (short) (1));//
sheet.addMergedRegion(gqnmrgn);
gqnmcell = row3.createCell((short) (1));
gqnmcell.setCellValue(new HSSFRichTextString(namerow1[0]));
gqnmcell.setCellStyle(row2style);
Region flrgn = new Region((short) 3, (short) (2), (short) 3, (short) (37));//
sheet.addMergedRegion(flrgn);
HSSFCell flcell = row3.createCell((short) 2);
flcell = row3.createCell((short) (2));
flcell.setCellValue(new HSSFRichTextString(namerow1[1]));
flcell.setCellStyle(row2style);
Region hjrgn = new Region((short) 3, (short) (38), (short) 4, (short) (40));//
sheet.addMergedRegion(hjrgn);
hjcell = row3.createCell((short) (38));
hjcell.setCellValue(new HSSFRichTextString(namerow1[2]));
hjcell.setCellStyle(row2style);
for (int i = 0; i < namerow2.length; i++) {
Region everybizrgn = new Region((short) 4, (short) (2 + (i) * 3), (short) 4, (short) ((i + 1) * 3 + 1));//
sheet.addMergedRegion(everybizrgn);
if (i == namerow2.length - 1) {
for (int j = 0; j < namerow3.length; j++) {
cellzt = row5.createCell((short) (38 + j));// 类别
cellzt.setCellValue(new HSSFRichTextString(namerow3[j]));
cellzt.setCellStyle(row2style);
}
}
}
for (int i = 0; i < namerow2.length; i++) {
celllb = row4.createCell((short) (2 + i * 3));// 类别
celllb.setCellValue(new HSSFRichTextString(namerow2[i]));
celllb.setCellStyle(row2style);
for (int j = 0; j < namerow3.length; j++) {
cellzt = row5.createCell((short) (2 + i * 3 + j));// 类别
cellzt.setCellValue(new HSSFRichTextString(namerow3[j]));
cellzt.setCellStyle(row2style);
}
}
// 第五步,写入实体数据 实际应用中这些数据从数据库得到,
try {
PageCond pg = PageCond.FACTORY.create();
pg.setLength(Integer.MAX_VALUE);
commonj.sdo.DataObject[] cos = DatabaseExt.queryEntitiesByCriteriaEntityWithPage("default", this.getCriteriaType(criteriaType), pg);
if ( !ToolKit.isEmpty(cos)) {
datalength = cos.length;
for (int i = datarow; i < cos.length + datarow; i++) {
HSSFRow rowdata = sheet.createRow(datarow);
HSSFCell celldata = null;
DataObject stu = cos[i - datarow];
// 第四步,创建单元格,并设置值
for (int j = 0; j < namerow2key.length; j++) {
String string = null;
String key = "a";
for (int k = 0; k < namekeylx.length; k++) {
string = stu.getString(key + namerow2key[j] + namekeylx[k]);
celldata = rowdata.createCell((short) (j * 3 + 2 + k));// 第2列
if (Integer.parseInt(string) == 0) {
celldata.setCellValue(new HSSFRichTextString("0"));
}
else {
celldata.setCellValue(new HSSFRichTextString(string));
}
celldata.setCellStyle(row2style);
if (namekeylx[k].equals("zc")) {
szc = szc + Integer.parseInt(string);
}
if (namekeylx[k].equals("sx")) {
ssx = ssx + Integer.parseInt(string);
}
if (namekeylx[k].equals("zx")) {
szx = szx + Integer.parseInt(string);
}
}
}
for (int l = 0; l < namekeyhj.length; l++) {
celldata = rowdata.createCell((short) (12 * 3 + 2 + l));// 第2列
celldata.setCellStyle(row2style);
String s = stu.getString(namekeyhj[l]);
celldata.setCellValue(new HSSFRichTextString(s));
}
HSSFCell cellgqnm = rowdata.createCell((short) (1));
cellgqnm.setCellStyle(row2style);
String s = stu.getString("nm");
cellgqnm.setCellValue(new HSSFRichTextString(s));
}
//合计行
HSSFRow rowdata = sheet.createRow(6+datalength);
HSSFCell cellhj = rowdata.createCell((short) (12 * 3 + 2 + 0));
cellhj.setCellStyle(row2style);
cellhj.setCellValue(szc);
cellhj = rowdata.createCell((short) (12 * 3 + 2 + 1));
cellhj.setCellValue(ssx);
cellhj.setCellStyle(row2style);
cellhj = rowdata.createCell((short) (12 * 3 + 2 + 2));
cellhj.setCellValue(szx);
cellhj.setCellStyle(row2style);
}
}
catch (Exception e1) {
logger.info(e1.getMessage());
}
// //第一个明细 END
String d = "";
// 第六步,将文件存到指定位置
try {
String rpath="";
ClassLoader cldr=getClass().getClassLoader();
if(null!=cldr){
rpath = cldr.getResource("/").getPath();
}else{
rpath="###";
}
String os = System.getProperty("os.name");
if (os != null && os.startsWith("Windows")) {
if (rpath.startsWith("/")) {
rpath = rpath.substring(1, rpath.length());
}
}
if (rpath.contains("WEB-INF")) {
rpath = rpath.substring(0, rpath.indexOf("WEB-INF")) + "upload";
}
String rpathfinal = rpath + File.separator;
Long nowl = System.currentTimeMillis();
Date nowd = new Date(nowl);
Date cread = nowd;
Calendar calendar = Calendar.getInstance();
calendar.setTime(nowd);
calendar.add(Calendar.MINUTE, -5);// 24小时制
nowd = calendar.getTime();
File olddir = new File(rpathfinal);
if (olddir.exists()) {
if (olddir.isDirectory()) {
File[] files = olddir.listFiles();
for (File ff : files) {
Long time = ff.lastModified();
cread = new Date(time);
if (nowd.before(cread)) {
if (ff.isFile()) {
FileUtil.deleteFile(ff.getAbsolutePath());
}
else {
FileUtil.deleteDir(ff.getAbsolutePath());
}
}
}
}
}
File f = new File(rpathfinal);
if ( !f.exists() && !f.isDirectory()) {
boolean bln_mk= f.mkdir();
if(!bln_mk){
logger.info("创建失败");
}
}
d = new SimpleDateFormat("yyyyMMddhhmmss").format(new Date());
String filepath = rpathfinal + headtitle + "(" + d + ").xls";
File filetemp = new File(filepath);
if (filetemp.exists()) {
filetemp.delete();
}
FileOutputStream fout = new FileOutputStream(filepath);
wb.write(fout);
fout.close();
}
catch (Exception e) {
TraceLogger logger = new TraceLogger("");
logger.info(e.getMessage());
}
String fileurl = headtitle + "(" + d + ").xls";
return fileurl;
}
private DataObject getCriteriaType(CriteriaType ct) {
IMUODataContext muo = DataContextManager.current().getMUODataContext();
Object obj = muo.get("cufs");
CadpSession cufs = new CadpSession();
if (null == obj) {
try {
throw new EOSException("本次会话已经失效,请重新登录!");
}
catch (EOSException e) {
logger.info(e.getException());;
}
}
else {
cufs = (CadpSession) obj;
if (null == cufs.getCustomcode()) {
try {
throw new EOSException("本次会话已经失效,请重新登录!");
}
catch (EOSException e) {
logger.info(e.getException());;
}
}
}
String cusmcode_query = cufs.getCustomcode();
if (cusmcode_query.endsWith("00")) {
// 如果关区代码是 00结尾 , 用 V_BIZQUERYZS
ct.set_entity(V_BIZQUERYZS.QNAME);
}
else {
// 如果关区代码不是00结尾 ,用 V_BIZQUERYLS
ct.set_entity(V_BIZQUERYLS.QNAME);
}
List<ExprType> exprlist = ct.get_expr();
if (ToolKit.isEmpty(exprlist)) {
exprlist = new ArrayList<ExprType>();
}
else {
if ( !ToolKit.isEmpty(exprlist.get(1))) {
if ( !ToolKit.isEmpty(exprlist.get(1).get("lshg"))) {
ct.set_entity(V_BIZQUERYLS.QNAME);
}
}
}
ExprType expr_cusm_code = ExprType.FACTORY.create();
expr_cusm_code.set_property("lshg");
if (cusmcode_query.endsWith("00")) {
expr_cusm_code.set_value(cusmcode_query.substring(0, 2));
}
else {
expr_cusm_code.set_value(cusmcode_query);
}
expr_cusm_code.set_op("like");
expr_cusm_code.set_likeRule("end");
if (cufs.getCustomcode().equals("0000")) {
}
else {
exprlist.add(expr_cusm_code);
}
ct.set_expr(exprlist);
return ct;
}
}