java 生成excel模板_java利用模板生成excel

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 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();

}