import java.util.Date;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import com.util.PageData;
import com.util.Tools;
public class ObjectExcelView extends AbstractExcelView{
@Override
protected void buildExcelDocument(Map<String, Object> model,
HSSFWorkbook workbook, HttpServletRequest request,
HttpServletResponse response) throws Exception {
Date date = new Date();
//以当前时间来作为导出的excel名称
String filename = Tools.date2Str(date, "yyyyMMddHHmmss");
HSSFSheet sheet;
HSSFCell cell;
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename="+filename+".xls");
sheet = workbook.createSheet("sheet1");
List<String> titles = (List<String>) model.get("titles");
int len = titles.size();
HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont headerFont = workbook.createFont(); //标题字体
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short)11);
headerStyle.setFont(headerFont);
short width = 20,height=25*20;
sheet.setDefaultColumnWidth(width);
for(int i=0; i<len; i++){ //设置标题
String title = titles.get(i);
cell = getCell(sheet, 0, i);
cell.setCellStyle(headerStyle);
setText(cell,title);
}
sheet.getRow(0).setHeight(height);
HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式
contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
List<PageData> varList = (List<PageData>) model.get("varList");
int varCount = varList.size();
for(int i=0; i<varCount; i++){
PageData vpd = varList.get(i);
for(int j=0;j<len;j++){
String varstr = vpd.getString("var"+(j+1)) != null ? vpd.getString("var"+(j+1)) : "";
cell = getCell(sheet, i+1, j);
cell.setCellStyle(contentStyle);
setText(cell,varstr);
}
}
}
}
/**controller中调用代码
*/
@RequestMapping(value="/excel")
public ModelAndView exportExcel(){
ModelAndView mv = new ModelAndView();
try{
Map<String,Object> dataMap = new HashMap<String,Object>();
List<String> titles = new ArrayList<String>();
titles.add("姓名 ");//1
titles.add("年龄 ");//2
titles.add("性别 ");//3
dataMap.put("titles", titles);
List<PageData> varList = new ArrayList<PageData>();
PageData vpd = new PageData();
vpd.put("var1", "张三");//1
vpd.put("var2", "18");//2
vpd.put("var3", "男");//3
varList.add(vpd);
dataMap.put("varList", varList);
ObjectExcelView erv = new ObjectExcelView();
mv = new ModelAndView(erv,dataMap);
} catch(Exception e){
}
return mv;
}
/**日期格式化
*/
public static String date2Str(Date date,String format){
if(date!=null){
SimpleDateFormat sdf = new SimpleDateFormat(format);
return sdf.format(date);
}else{
return "";
}
}
sheet.setDefaultColumnWidth(20);//默认宽度
sheet.autoSizeColumn((short)0); //调整第一列宽度
//设置第一列宽度(从0开始),为18
sheet.setColumnWidth(0, 18*256);
//默认高度
sheet.setDefaultRowHeight((short)20);
sheet.setDefaultRowHeightInPoints((short)20);
//设置高度
//单元格合并(合并第二行第七个和第八个单元格)
//注:如果合并行了第1,2行,下一次的开始行为第3行。如果是在循环中,循环变量不是加1,要加2
sheet.addMergedRegion(new CellRangeAddress(1, 1, 6, 7));//参数为起始行,结束行,起始列,结束列
cell = getCell(sheet, 1, 6);//第二行第7个单元格开始
setText(cell,head[2]);//
//冻结(7,3)(宽,高)区域单元格中的数据
sheet.createFreezePane(7, 3);
转载参考:http://blog.csdn.net/z69183787/article/details/41725379 和其它网站