java利用模板生成excel

  • Z时代
  • 2024-01-10
  • 分类：综合

java

public String exportCjcljd() throws IOException{
File file = new File(servletRequest.getSession().getServletContext().getRealPath("/")+ "/pages/osta/kwgl/cjcljd.xls");
ByteArrayOutputStream out=new ByteArrayOutputStream();
InputStream in = new FileInputStream(file);
HSSFWorkbook hworkbook = new HSSFWorkbook(in);// 用输入流生成poi对象，以读取excel中的内容
HSSFSheet sheet = hworkbook.getSheetAt(0);// 获取sheet

if (sheet == null) {
throw new BusinessException("文件异常！");
}

HSSFCellStyle style = hworkbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);

//样式1
HSSFCellStyle style1 = hworkbook.createCellStyle();
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font=hworkbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style1.setFont(font);


//样式2
HSSFCellStyle style2 = hworkbook.createCellStyle();
style2.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

HSSFRow row0 = sheet.getRow(0);// 获取第一行
String name=DateUtils.format(new Date(), "yyyy")+"年日常鉴定成绩处理进度记录表";

this.createCell(row0, 0, style1,HSSFCell.CELL_TYPE_STRING,name );

Calendar now = Calendar.getInstance();
String str=now.get(Calendar.YEAR)+" 年 "+(now.get(Calendar.MONTH) + 1) +" 月 "+now.get(Calendar.DAY_OF_MONTH)+" 日";
this.createCell(row0, 8, style2,HSSFCell.CELL_TYPE_STRING,str );


StringBuffer sb=new StringBuffer();
sb.append("select jh.jdjhnum,jh.createdate,(select value from tb_dic_info a where a.code=jh.kstype and a.type=\'JDLX\') kstype ");
sb.append(",jh.zygzname,(select value from tb_dic_info a where a.code=jh.jdrank and a.type=\'JDDJ\') jdrank,to_char(jh.jdtime,\'yyyy-MM-dd\') jdtime,jg.fullname ");
sb.append("from osip_osta_jdjh jh,osip_osta_jdgxtwo jt,osip_osta_jdjg jg ");
sb.append("where jh.id=jt.jdjhid and jt.jdjgid=jg.id and jh.district_code=? ");
sb.append(" and jh.validity=\'1\' and jh.status=3 and jh.sfshtg=3 and sysdate > jh.bmjztime ");
String districtcode=(String) getSession().get(SessionConstants.WSSIP_OPERATOR_DISTRICTCODE);



List<KcbpVO> list=CommonJdbcDaoUtils.query(sb.toString(), KcbpVO.class, districtcode);
for (int i = 0; i < list.size(); i++) {
KcbpVO model=list.get(i);
HSSFRow row1 = sheet.createRow((short) (i + 3));// 建立新行 从插入数据的那一行开始

this.createCell(row1, 0, style,HSSFCell.CELL_TYPE_STRING, i+1);

if (StringUtils.isNotBlank(model.getJdjhnum()))
this.createCell(row1, 1, style,HSSFCell.CELL_TYPE_STRING, model.getJdjhnum());

if (model.getCreatedate()!=null)
this.createCell(row1, 2, style,HSSFCell.CELL_TYPE_STRING, DateUtils.format(model.getCreatedate(), "yyyy"));

if (StringUtils.isNotBlank(model.getKstype()))
this.createCell(row1, 3, style,HSSFCell.CELL_TYPE_STRING, model.getKstype());

if (StringUtils.isNotBlank(model.getZygzname()))
this.createCell(row1, 4, style,HSSFCell.CELL_TYPE_STRING, model.getZygzname());

if (StringUtils.isNotBlank(model.getJdrank()))
this.createCell(row1, 5, style,HSSFCell.CELL_TYPE_STRING, model.getJdrank());


if (model.getJdtime()!=null)
this.createCell(row1, 6, style,HSSFCell.CELL_TYPE_STRING, model.getJdtime());

if (StringUtils.isNotBlank(model.getFullname()))
this.createCell(row1,7, style,HSSFCell.CELL_TYPE_STRING, model.getFullname());


}


try {
hworkbook.write(out);
servletRequest.setAttribute("excelStream",new ByteArrayInputStream(out.toByteArray()));
servletRequest.setAttribute("filename", new String(name.getBytes("GBK"),"ISO-8859-1")+".xls");//设置文件名
return SUCCESS;
} catch (IOException e) {
e.printStackTrace();
ExceptionLogHandler.saveExceptionLog(e,(String) getSession().get(SessionConstants.WSSIP_OPERATOR_ID));
System.out.println("无法输出Excel文件");
return ERROR;
} finally{
out.close();
}




}



private void createCell(HSSFRow row, int column, HSSFCellStyle style,

int cellType, Object value) {

HSSFCell cell = row.createCell((short)column);

if (style != null) {

cell.setCellStyle(style);
}
switch (cellType) {
case HSSFCell.CELL_TYPE_BLANK: {
}
break;
case HSSFCell.CELL_TYPE_STRING: {
cell.setCellValue(value.toString());
}
break;
case HSSFCell.CELL_TYPE_NUMERIC: {
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(Double.parseDouble(value.toString()));
}
break;
default:
break;
}
}


//获取输出流
public InputStream getInputExcel()
{
return (InputStream)servletRequest.getAttribute("excelStream");
}


--------------------------------------------------------
jsp页面
function exportCjcljd(){
var excelForm = document.getElementById("excelForm");
excelForm.action="${pageContext.request.contextPath}/osta/kwgl/exportCjcljd.action";
excelForm.submit();

}


<iframe id="exportIFrame" style="display:none;"></iframe>

<!--导出Excel相关HTML-->
<form id="excelForm" method="post" target="excelIFrame">
<input type="hidden" name="jdjgids" id="jdjgids_xls" />
<input type="hidden" name="jdjhid" id="jdjhid_xls" />
<input type="hidden" name="jdjhJoken" id="jdjhJoken_xls" />
<input type="hidden" name="jdjhQuery" id="jdjhQuery_xls" />
<input type="hidden" name="sortField" id="sortField_xls" />
<input type="hidden" name="sortOrder" id="sortOrder_xls" />
<input type="hidden" name="jdjh" id="jdjh_xls" />
</form>
<iframe id="excelIFrame" name="excelIFrame" style="display:none;"></iframe>

 

 

以上是 java利用模板生成excel 的全部内容， 来源链接： utcz.com/z/392577.html

回到顶部