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;
}