使用poi导出excel,其中涉及到居中、字体加粗、合并单元格
@Namespace("/org/examinee")
@Results( {
@Result(name = "export", type = "stream", params = {
"contentType", "application/xls ", "contentDisposition",
"attachment;filename=\"${downloadFileName}\"", "inputName",
"inputStream", "bufferSize", "4096" })
})
public class OrgexamineeExportAction extends NationOrgexamineeBaseAction {
private static final long serialVersionUID = 1L;
@Resource
private NationOrgexamineeManager nationOrgexamineeManager;
/**
* 保存excel名称
*/
private String excelName ;
/**
* 唯一计划标识
*/
private String planUuid;
/**
* 显示列表
*/
public String execute() throws Exception {
return SUCCESS;
}
/**
* 导出excel
* @return
* @throws Exception
*/
public String export() throws Exception{
HSSFRow rownull = null ;
HSSFCell cell0 = null ;//第一列
NationOrginfo org = (NationOrginfo)Struts2Utils.getSession().getAttribute("org");
String ttcode = org.getCode() ;
List<Map<String,String>> list = nationOrgexamineeManager.getOrgexamineeExport(planUuid, ttcode);
String[] names = { "姓名", "身份号码", "职业名称", "职业等级"};
HSSFWorkbook workBook = new HSSFWorkbook();
HSSFSheet sheet = workBook.createSheet("Sheet1");
//学校名称
HSSFCellStyle cellStyle = workBook.createCellStyle(); //字体样式
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
HSSFFont font = workBook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
font.setFontHeightInPoints((short)15); //字体大小
cellStyle.setFont(font);
//标题
HSSFCellStyle cellStyle1 = workBook.createCellStyle(); //字体样式
cellStyle1.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
HSSFFont font1 = workBook.createFont();
font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
font1.setFontHeightInPoints((short)12); //字体大小
cellStyle1.setFont(font1);
sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellStyle(cellStyle);
cell.setCellValue(org.getSchoolName());//设置第一行的标题名
HSSFRow row1 = sheet.createRow(1);
for(int j=0 ; j<names.length;j++){
cell0 = row1.createCell(j);
cell0.setCellValue(names[j]);
cell0.setCellStyle(cellStyle1);
sheet.setColumnWidth(j, 20 * 256);
}
Map<String, String> map = null ;
for(int i = 0 ;i<list.size();i++){
map = list.get(i);
rownull = sheet.createRow(i+2);
for(int j=0 ; j<names.length;j++){
if(i==0){//插入名称
cell0 = row1.createCell(j);
cell0.setCellValue(names[j]);
cell0.setCellStyle(cellStyle);
sheet.setColumnWidth(j, 20 * 256);
}else{
sheet.setColumnWidth(j, 20 * 256);
if(j==0){
cell0 = rownull.createCell(j);
cell0.setCellValue(map.get("name"));
}else if(j==1){
cell0 = rownull.createCell(j);
cell0.setCellValue(map.get("cardNumber"));
}else if(j==2){
cell0 = rownull.createCell(j);
cell0.setCellValue(map.get("workName"));
}else if(j==3){
cell0 = rownull.createCell(j);
cell0.setCellValue(map.get("levelName"));
}
}
}
}
try {
String basePath="uploadtemp"+File.separator;
String rootPath=Struts2Utils.getRequest().getSession().getServletContext().getRealPath("/");
File dic=new File(rootPath+basePath);
if (!dic.exists()) {
dic.mkdirs();
}
excelName = rootPath+basePath+"export"+".xls";
workBook.write(new FileOutputStream(excelName));
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return "export";
}
/**
* 下载用的Action应该返回一个InputStream实例
*
*/
public InputStream getInputStream() throws Exception {
InputStream fis = null ;
try {
fis = new FileInputStream(excelName);
//fis.close(); 仍在使用,不能关闭
} catch (Exception e1) {
e1.printStackTrace();
}
return fis;
}
// 处理下载文件的名字
public String getDownloadFileName() {
String downloadFileName = null;
downloadFileName = "export.xls";
return downloadFileName;
}
public String getPlanUuid() {
return planUuid;
}
public void setPlanUuid(String planUuid) {
this.planUuid = planUuid;
}
}