- POI读取EXCEL
package com.fh.util;
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
public class ObjectExcelWrite extends ObjectExcelView{
private static final String[] String = null;
protected void buildExcelDocument(Map<String, Object> model,
HSSFWorkbook workbook, HttpServletRequest request,
HttpServletResponse response) throws Exception {
//设置导出EXCEL文件名称
Date date = new Date();
String filename = Tools.date2Str(date, "yyyyMMddHHmmss");
HSSFSheet sheet;
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename="+filename+".xls");
sheet = workbook.createSheet("sheet1");
//获取excelHeader
String excelHeaderTitle=(String) model.get("excelHeaderTitle");
List<String> excelHeader = (List<String>) model.get("excelHeader");
int len = excelHeader.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);
//创建第一行
HSSFRow row = sheet.createRow((short) 0);
row.setHeight((short)1000);
HSSFCell cell = row.createCell(0);
cell.setCellValue(excelHeaderTitle);//设置内容
cell.setCellStyle(headerStyle);//设置样式
sheet.setColumnWidth(0, 256*10); //设置某一列宽度
//创建第二行第三行表头,设置表头-标题
row = sheet.createRow((short) 1);
for (int i = 0; i < len; i++) {
cell = row.createCell(i);
cell.setCellValue(excelHeader.get(i));//设置内容
cell.setCellStyle(headerStyle);//设置样式
sheet.setColumnWidth(i, 256*10); //设置某一列宽度
}
//设置标题头横向合并(参数:起始行号,终止行号, 起始列号,终止列号)
sheet.addMergedRegion(new CellRangeAddress(0,0,0,30));
sheet.addMergedRegion(new CellRangeAddress(1,1,6,8));
sheet.addMergedRegion(new CellRangeAddress(1,1,9,11));
sheet.addMergedRegion(new CellRangeAddress(1,1,14,16));
sheet.addMergedRegion(new CellRangeAddress(1,1,17,19));
sheet.addMergedRegion(new CellRangeAddress(1,1,28,30));
//设置标题头纵向合并
sheet.addMergedRegion(new CellRangeAddress(1,2,0,0));
sheet.addMergedRegion(new CellRangeAddress(1,2,1,1));
sheet.addMergedRegion(new CellRangeAddress(1,2,2,2));
sheet.addMergedRegion(new CellRangeAddress(1,2,3,3));
sheet.addMergedRegion(new CellRangeAddress(1,2,4,4));
sheet.addMergedRegion(new CellRangeAddress(1,2,5,5));
sheet.addMergedRegion(new CellRangeAddress(1,2,12,12));
sheet.addMergedRegion(new CellRangeAddress(1,2,13,13));
sheet.addMergedRegion(new CellRangeAddress(1,2,20,20));
sheet.addMergedRegion(new CellRangeAddress(1,2,21,21));
sheet.addMergedRegion(new CellRangeAddress(1,2,22,22));
sheet.addMergedRegion(new CellRangeAddress(1,2,23,23));
sheet.addMergedRegion(new CellRangeAddress(1,2,24,24));
sheet.addMergedRegion(new CellRangeAddress(1,2,25,25));
sheet.addMergedRegion(new CellRangeAddress(1,2,26,26));
sheet.addMergedRegion(new CellRangeAddress(1,2,27,27));
sheet.getRow(0).setHeight((short) (25*20));
//设置对应的合并单元格标题
row = sheet.createRow(2);
row.setHeight((short)1000);
for (int j = 6; j < 12; j++) {
cell = row.createCell(j);
cell.setCellStyle(headerStyle);
if(j==6 ||j==9 ) {
cell.setCellValue("省");
}else if(j==7 ||j==10 ) {
cell.setCellValue("市");
}else {
cell.setCellValue("区(县)");
}
sheet.setColumnWidth(j, 256*10); //设置某一列宽度
}
for (int j = 14; j < 20; j++) {
cell = row.createCell(j);
cell.setCellStyle(headerStyle);
if(j==14 || j==17) {
cell.setCellValue("选项一");
}else if(j==15 || j==18) {
cell.setCellValue("选项二");
}else {
cell.setCellValue("选项三");
}
sheet.setColumnWidth(j, 256*10); //设置某一列宽度
}
for (int j = 28; j < 31; j++) {
cell = row.createCell(j);
cell.setCellStyle(headerStyle);
if(j==28 || j==17) {
cell.setCellValue("姓名");
}else if(j==15 || j==18) {
cell.setCellValue("联系电话");
}else {
cell.setCellValue("电子邮箱");
}
sheet.setColumnWidth(j, 256*10); //设置某一列宽度
}
//设置列值-内容
List<PageData> varList = (List<PageData>) model.get("varList");
for (int i = 0; i < varList.size(); i++) {
row = sheet.createRow(i + 3);
row.setHeight((short)500);
PageData p =varList.get(i);
int a=0;
row.createCell(a).setCellValue((String)p.get("var1"));
row.createCell(++a).setCellValue((String)p.get("var2"));
row.createCell(++a).setCellValue((String) p.get("var3"));
row.createCell(++a).setCellValue((String) p.get("var4"));
row.createCell(++a).setCellValue((String) p.get("var5"));
row.createCell(++a).setCellValue((String) p.get("var6"));
row.createCell(++a).setCellValue((String) p.get("var7.1"));
row.createCell(++a).setCellValue((String) p.get("var7.2"));
row.createCell(++a).setCellValue((String) p.get("var7.3"));
row.createCell(++a).setCellValue((String) p.get("var8.1"));
row.createCell(++a).setCellValue((String) p.get("var8.2"));
row.createCell(++a).setCellValue((String) p.get("var8.3"));
row.createCell(++a).setCellValue((String) p.get("var9"));
row.createCell(++a).setCellValue((String) p.get("var10"));
row.createCell(++a).setCellValue((String) p.get("var11"));
row.createCell(++a).setCellValue((String) p.get("var12"));
row.createCell(++a).setCellValue((String) p.get("var13"));
row.createCell(++a).setCellValue((String) p.get("var14"));
row.createCell(++a).setCellValue((String) p.get("var15"));
row.createCell(++a).setCellValue((String) p.get("var16"));
row.createCell(++a).setCellValue((String) p.get("var17"));
row.createCell(++a).setCellValue((String) p.get("var18"));
row.createCell(++a).setCellValue((String) p.get("var19"));
row.createCell(++a).setCellValue((String) p.get("var20"));
row.createCell(++a).setCellValue((String) p.get("var21"));
row.createCell(++a).setCellValue((String) p.get("var22"));
row.createCell(++a).setCellValue((String) p.get("var23"));
row.createCell(++a).setCellValue((String) p.get("var24"));
row.createCell(++a).setCellValue((String) p.get("var25"));
row.createCell(++a).setCellValue((String) p.get("var26"));
row.createCell(++a).setCellValue((String) p.get("var27"));
}
System.out.println("EXCEL导出完成");
}
}
- 调用导出到EXCEL的方法
/**导出用户信息到EXCEL
* @return
* @throws Exception
* @author zhaoyn
*/
@RequestMapping(value="/excel")
public ModelAndView exportExcel() throws Exception{
FHLOG.save(Jurisdiction.getUsername(), "导出用户信息到EXCEL");
ModelAndView mv = this.getModelAndView();
PageData pd = new PageData();
pd = this.getPageData();
try{
if(Jurisdiction.buttonJurisdiction(menuUrl, "cha")){
//关键词检索条件
String keywords = new String(pd.getString("keywords").getBytes("8859_1"), "utf8");
if(null != keywords && !"".equals(keywords)){
pd.put("keywords", keywords.trim());
}
//标题放入dataMap
Map<String,Object> dataMap = new HashMap<String,Object>();
List<String> excelHeader = new ArrayList<String>();
excelHeader.add("序号");
excelHeader.add("姓名");
excelHeader.add("性别");
excelHeader.add("出生年月");
excelHeader.add("政治面貌");
excelHeader.add("国籍");
excelHeader.add("籍贯");
excelHeader.add("籍贯");
excelHeader.add("籍贯");
excelHeader.add("出生地或成长地");
excelHeader.add("出生地或成长地");
excelHeader.add("出生地或成长地");
excelHeader.add("最高学位");
excelHeader.add("毕业院校");
excelHeader.add("专业领域*(最多选3项)");
excelHeader.add("专业领域*(最多选3项)");
excelHeader.add("专业领域*(最多选3项)");
excelHeader.add("研究方向*(最多选3项)");
excelHeader.add("研究方向*(最多选3项)");
excelHeader.add("研究方向*(最多选3项)");
excelHeader.add("人才工程或荣誉称号");
excelHeader.add("标志性成果");
excelHeader.add("在山东学习或生活经历");
excelHeader.add("是否有意向来鲁发展");
excelHeader.add("现工作单位");
excelHeader.add("职务职称");
excelHeader.add("联系电话");
excelHeader.add("电子邮箱");
excelHeader.add("其他联系人");
excelHeader.add("其他联系人");
excelHeader.add("其他联系人");
dataMap.put("excelHeaderTitle", "齐鲁英才信息采集表");
dataMap.put("excelHeader", excelHeader);
//查询出人才信息放入dataMap
List<PageData> eliteList = eliteInforService.ListAllElites(pd);
List<PageData> varList = new ArrayList<PageData>();
for(int i=0;i<eliteList.size();i++){
PageData vpd = new PageData();
vpd.put("var1", i+1+"");
vpd.put("var2", eliteList.get(i).getString("ET_NAME"));
vpd.put("var3", eliteList.get(i).getString("ET_SEX"));
vpd.put("var4", eliteList.get(i).getString("ET_BIRTH"));
vpd.put("var5", eliteList.get(i).getString("ET_POLITICS"));
vpd.put("var6", eliteList.get(i).getString("ET_COUNTRY"));
vpd.put("var7.1", eliteList.get(i).getString("ET_NATIONALITY1"));
vpd.put("var7.2", eliteList.get(i).getString("ET_NATIONALITY2"));
vpd.put("var7.3", eliteList.get(i).getString("ET_NATIONALITY3"));
vpd.put("var8.1", eliteList.get(i).getString("ET_HOMEPLACE1"));
vpd.put("var8.2", eliteList.get(i).getString("ET_HOMEPLACE2"));
vpd.put("var8.3", eliteList.get(i).getString("ET_HOMEPLACE3"));
vpd.put("var9", eliteList.get(i).getString("ET_DEGREE"));
vpd.put("var10", eliteList.get(i).getString("ET_SCHOOL"));
vpd.put("var11", eliteList.get(i).getString("ET_PROFESSION1"));
vpd.put("var12", eliteList.get(i).getString("ET_PROFESSION2"));
vpd.put("var13", eliteList.get(i).getString("ET_PROFESSION3"));
vpd.put("var14", eliteList.get(i).getString("ET_RESEARCHAREA1"));
vpd.put("var15", eliteList.get(i).getString("ET_RESEARCHAREA2"));
vpd.put("var16", eliteList.get(i).getString("ET_RESEARCHAREA3"));
vpd.put("var17", eliteList.get(i).getString("ET_HONOR"));
vpd.put("var18", eliteList.get(i).getString("ET_ACHIEVEMENT"));
vpd.put("var19", eliteList.get(i).getString("ET_LIFEHISTORY"));
vpd.put("var20", eliteList.get(i).getString("ET_INTENTDEVELOP"));
vpd.put("var21", eliteList.get(i).getString("ET_EMPLOYEE"));
vpd.put("var22", eliteList.get(i).getString("ET_PROFESSIONAL"));
vpd.put("var23", eliteList.get(i).getString("ET_TEL"));
vpd.put("var24", eliteList.get(i).getString("ET_MAIL"));
vpd.put("var25", eliteList.get(i).getString("ET_CONTECT_NAME"));
vpd.put("var26", eliteList.get(i).getString("ET_CONTECT_TEL"));
vpd.put("var27", eliteList.get(i).getString("ET_CONTECT_MAIL"));
varList.add(vpd);
}
dataMap.put("varList", varList);
//执行excel操作
ObjectExcelWrite oew = new ObjectExcelWrite();
mv = new ModelAndView(oew,dataMap);
}
} catch(Exception e){
logger.error(e.toString(), e);
}
return mv;
}