运用poi-2.5.1开发excel的导出

excel导出功能

 

思路说明

1.通过sql得到ResultSet数据集，作为参数传入该方法

2.将新建的数据写入ServletOutputStream的输出流中，给页面反馈。

HSSFWorkbook.write(ServletOutputStream).

 

代码说明

 

 public boolean export(ServletOutputStream out, ResultSet rs, String sheetName,
   String[] fields, String[] colName) {
  if (out == null)
   return false;
  if (rs == null) {
   return false;
  }

  try {
   //创建工作簿
   HSSFWorkbook wb = new HSSFWorkbook();//创建excel文件
   HSSFSheet sheettemp = wb.createSheet();
   wb.setSheetName(0, (String) sheetName, (short) 1);//新建一个sheet

   //＝＝＝＝＝＝＝＝＝＝＝＝＝创建两行存储表头信息＝＝＝＝＝＝＝＝＝＝＝
  HSSFRow rowtitle = sheettemp.createRow((short) 0);//添加表头
  HSSFCell celltitle = rowtitle.createCell((short) 0);
  celltitle.setEncoding(HSSFCell.ENCODING_UTF_16);//设置字体编码
   //celltitle.setCellValue((String) sheetName);//设置表头内容

  rowtitle = sheettemp.createRow((short) 1);//新增一行用于存放表头内容
  sheettemp.addMergedRegion(new Region(0, (short) 0, 0,
    (short) (fields.length - 1)));//合并第一行
  rowtitle = sheettemp.getRow(0);//获得第一行的引用
  celltitle = rowtitle.getCell((short) 0);//获得第一行第一个单元格的引用
   //setTitleStyle(wb, celltitle);//设置表头格式

   //=========================行头信息====================
   int j=0;//占用第一行，这个是根据是否有行头确定的，没有设置为0,有设置为1；
   HSSFRow rowfield = sheettemp.createRow((short) j);//新增一行用于存放列头内容
   for (int i = 0; i < colName.length; i++)//根据获取到的列头数据，循环设置列头的内容
   {
    HSSFCell cellfield = rowfield.createCell((short) i);
    cellfield.setEncoding(HSSFCell.ENCODING_UTF_16);
    cellfield.setCellValue(colName[i]);
    //setFieldStyle(wb, cellfield);//设置列头格式
    int xx = cellfield.getStringCellValue().length();
    sheettemp.setColumnWidth((short) i, (short) (600 * xx));
   }

   //=========================表体信息======================
   int count = 0;
   while (rs.next()) {
    HSSFRow rowbody = sheettemp.createRow((short) (count + j+1));
    for (int i = 0; i < fields.length; i++) {
     HSSFCell cellbody = rowbody.createCell((short) i);
     cellbody.setEncoding(HSSFCell.ENCODING_UTF_16);
     cellbody.setCellValue(PubFunction.getNulltoStr(rs
       .getString(fields[i])));
    }
    count++;
   }
   //=======================页尾========================
   
   //=======================将工作薄信息输出
   wb.write(out);
   out.close();

 

  } catch (SQLException e) {
   if (log.isErrorEnabled())
    log.error(e);
   return false;
  }catch (IOException e) {
   e.printStackTrace();
   return false;
  }
  return true;
 }
 
 //设置样式与风格
    private void setFieldStyle(HSSFWorkbook wb ,HSSFCell row){
       HSSFCellStyle cellStyle = wb.createCellStyle();
         cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
     HSSFFont font = wb.createFont();
     font.setFontHeightInPoints( (short) 11);   ;
     font.setFontName("宋体");
     font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
 
     cellStyle.setFont(font);

     row.setCellStyle(cellStyle);
    }

 

 

附注:

1.两种HSSFWorkbook的输出方式

//将工作薄输出到输出流（比较适用于B/S）
ServletOutputStream sos=response.getOutputStream();
wb.write(sos);
sos.close();

//也可输出成xls文件(比较适用于C/S)
File file = new File("workbook.xls");
try {
FileOutputStream fileOut = new FileOutputStream(file);
wb.write(fileOut);
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
}

2.数据集的获得

 public ResultSet executeSql(String sql,Object[] para)throws Exception{
  conn = sqlMapClient.getDataSource().getConnection();
  pstmt = conn.prepareStatement(sql);
  if(para!=null && para.length>0){
   for(int i=0;i<para.length;i++){
    pstmt.setObject(i+1,para[i]);
   }
  }
  rs = pstmt.executeQuery();
  return rs;
 }

3.获得ibatis的动态sql语句与参数设置

 public String getSql(String sqlMapId, Object parameter) {
  String sqlValue = "";
  ExtendedSqlMapClient extendedSqlMapClient = (ExtendedSqlMapClient) sqlMapClient;
  SqlMapExecutorDelegate sqlMapExecutorDelegate = extendedSqlMapClient
    .getDelegate();
  MappedStatement mappedStatement = sqlMapExecutorDelegate
    .getMappedStatement(sqlMapId);
  RequestScope requestScope = new RequestScope();
  mappedStatement.initRequest(requestScope);
  Sql sql = mappedStatement.getSql();

  sqlValue = sql.getSql(requestScope, parameter);
  
  ParameterMap pm = sql.getParameterMap(requestScope, parameter);
  pm.getParameterObjectValues(requestScope, parameter);
  sqlParam = pm.getParameterObjectValues(requestScope, parameter);
  if(log.isDebugEnabled())
   log.debug(sqlValue);
  return sqlValue;
 }