导出下拉框
String[] strs = {"日常考察","专项考察","跟踪考察","延伸考察","推荐考察","其他考察"};
//四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(2, dataSet.size()+1, 3, 3);
DVConstraint constraint = DVConstraint.createExplicitListConstraint(strs);
HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
sheet.addValidationData(dataValidation);
导出单元格居中，自动换行
cellStyle.setWrapText(true);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平
1、Controller
@RequestMapping(value = "/daochu", produces = "text/html; charset=utf-8")
@ResponseBody
public String daochu(String type,String wsda_id,HttpServletRequest request) {
try {
if("hmc".equals(type)){
String filename = wsdaService.daochuByBanzi( request,wsda_id);
String st = "{success:true,file_name:'" + filename + "'}";
return st;
}
if("gbtjb".equals(type)){
String filename = wsdaService.daochuByGanbu( request,wsda_id);
String st = "{success:true,file_name:'" + filename + "'}";
return st;
}
/*String filename = CadreReportService.exportExcel( request,danwei, name, rzdw, age3, age4, nativePlace, birthPlace, zhiwu, gender,
politicsFace, xueli, response, birthdayyi, birthdayer, zhiji, zhiji_sj, xianzsj,
gzjl, fulldayEdu, onthejobEdu, rzjl_dwxz, rzjl_zwlb,
rzjl_sj1, rzjl_sj2,jobTimeS,jobTimeE,gbjf,pointYear,nation, newolddw, newoldzw);*/
} catch (Exception e) {
e.printStackTrace();
}
return "{success:false}";
}
2、Service
@Override
public String daochuByBanzi(HttpServletRequest request, String wsda_id) {
Calendar now = Calendar.getInstance();
int year = now.get(Calendar.YEAR);
int month = now.get(Calendar.MONTH);
String fileName = System.currentTimeMillis() + ".xls";
/*String path = getClass().getClassLoader().getResource("/").getPath()*/
String path =request.getSession().getServletContext().getRealPath("/")
+ "upload/excel/export/" + fileName;
//System.out.println(path);
File file = new File(path);
try {
file.createNewFile();
} catch (IOException e1) {
e1.printStackTrace();
}
String title = "干部花名册";
String rowName[] = new String[]{};
String[] rowName1 = { "姓 名", "性别", "籍贯", "工作单位及职务","任职 时间","同级 时间","出生 年月","工作 时间","入党 时间","全日制学历","全日制 毕业院校及专业","在职学历","在职学历 毕业院校及专业","备注"};
rowName = rowName1;
List<Map<String, Object>> infoList = dao.getdaochuExcelList( wsda_id);
try{
HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
HSSFSheet sheet = workbook.createSheet(title); // 创建工作表
// 产生表格标题行
//HSSFRow rowm = sheet.createRow(0);
//HSSFCell cellTiltle = rowm.createCell(0);
//sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
HSSFCellStyle style = workbook.createCellStyle();
style.setWrapText(true); //单元格样式对象
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //设置单元格的值
//设置单元格样式
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//cellTiltle.setCellStyle(columnTopStyle); //标题样式
// 定义所需列数
int columnNum = rowName.length;
HSSFRow rowRowName = sheet.createRow(0); // 在索引0的位置创建行(最顶端的行开始的第二行)
// 将列头设置到sheet的单元格中
for(int n=0;n<columnNum;n++){
HSSFCell cellRowName = rowRowName.createCell(n); //创建列头对应个数的单元格
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); //设置列头单元格的数据类型
HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
cellRowName.setCellValue(text); //设置列头单元格的值
cellRowName.setCellStyle(columnTopStyle); //设置列头单元格样式
}
//将查询出的数据设置到sheet对应的单元格中
for(int i=0;i<infoList.size();i++){
if(!StringUtils.isEmpty(infoList.get(i))){
//Map<String, Object> obj = infoList.get(i);//遍历每个对象
HSSFRow row = sheet.createRow(i+1);//创建所需的行数
/*if(!"".equals(infoList.get(i).get("jobSiteAndPost")) && infoList.get(i).get("jobSiteAndPost") != null){
String jobSiteAndPost=infoList.get(i).get("jobSiteAndPost").toString();
}*/
row.setHeightInPoints(50);
for(int j=0; j<columnNum+2; j++){
HSSFCell cell = null; //设置单元格的数据类型
if(j == 0) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("name")) && infoList.get(i).get("name") != null){
cell.setCellValue(infoList.get(i).get("name").toString());
cell.setCellStyle(style);
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
} else if(j == 1) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("gender")) && infoList.get(i).get("gender") != null){
if("1".equals(infoList.get(i).get("gender"))){
cell.setCellValue("男");
//style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style); //设置单元格样式
}
else if("0".equals(infoList.get(i).get("gender"))){
cell.setCellValue("女");
//style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style); //设置单元格样式
}else{
cell.setCellValue("");//设置单元格的值
cell.setCellStyle(style);
}
}
} else if (j == 2) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("nativePlace")) && infoList.get(i).get("nativePlace") != null){
cell.setCellValue(infoList.get(i).get("nativePlace").toString()); //设置单元格的值
//style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style); //设置单元格样式
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
}else if (j == 3) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("jobSiteAndPost")) && infoList.get(i).get("jobSiteAndPost") != null){
cell.setCellValue(infoList.get(i).get("jobSiteAndPost").toString()); //设置单元格的值
//style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
cell.setCellStyle(style); //设置单元格样式
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
} else if (j == 4) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("xzsj")) && infoList.get(i).get("xzsj") != null){
String xzsj = infoList.get(i).get("xzsj").toString().substring(0,7);
if(!"".equals(infoList.get(i).get("xzsj2")) && infoList.get(i).get("xzsj2") != null){
xzsj+=infoList.get(i).get("xzsj2").toString().substring(0,7);
}
if(!"".equals(infoList.get(i).get("xzsj3")) && infoList.get(i).get("xzsj3") != null){
xzsj+=infoList.get(i).get("xzsj3").toString().substring(0,7);
}
if(!"".equals(infoList.get(i).get("xzsj4")) && infoList.get(i).get("xzsj4") != null){
xzsj+=infoList.get(i).get("xzsj4").toString().substring(0,7);
}
cell.setCellValue(xzsj.replaceAll("-", ".")); //设置单元格的值
//style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style); //设置单元格样式
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
} else if (j == 5) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("aa")) && infoList.get(i).get("aa") != null){
cell.setCellValue(infoList.get(i).get("aa").toString()); //设置单元格的值
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
cell.setCellStyle(style); //设置单元格样式
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
}else if (j == 6) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("birthday")) && infoList.get(i).get("birthday") != null){
String birthday = infoList.get(i).get("birthday").toString().substring(0,7);
cell.setCellValue(birthday.replaceAll("-", "."));
//设置单元格的值
//style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style); //设置单元格样式
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
}else if (j == 7) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("jobTime")) && infoList.get(i).get("jobTime") != null){
String data = infoList.get(i).get("jobTime").toString().substring(0,7);
cell.setCellValue(data.replaceAll("-", ".")); //设置单元格的值
//style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style); //设置单元格样式
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
}else if (j == 8) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("joinDate")) && infoList.get(i).get("joinDate") != null){
String pf ="";
if(null!=infoList.get(i).get("politicsFace") && !"".equals(infoList.get(i).get("politicsFace"))){
pf= infoList.get(i).get("politicsFace").toString();
}
if(!"中共党员".equals(pf) && !"预备党员".equals(pf)){
String data =pf;
data+=infoList.get(i).get("joinDate").toString().substring(0,7);
cell.setCellValue(data.replaceAll("-", "."));
}else{
String data = infoList.get(i).get("joinDate").toString().substring(0,7);
cell.setCellValue(data.replaceAll("-", "."));
}
//style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style); //设置单元格样式 //设置单元格的值
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
}else if (j == 9) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("fulldayEdu")) && infoList.get(i).get("fulldayEdu") != null){
cell.setCellValue(infoList.get(i).get("fulldayEdu").toString());
//style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style); //设置单元格样式 //设置单元格的值
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
}else if (j == 10) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("fulldayEdu_school")) && infoList.get(i).get("fulldayEdu_school") != null){
cell.setCellValue(infoList.get(i).get("fulldayEdu_school").toString());
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
cell.setCellStyle(style); //设置单元格样式 //设置单元格的值
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
}else if (j == 11) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("onthejobEdu")) && infoList.get(i).get("onthejobEdu") != null){
cell.setCellValue(infoList.get(i).get("onthejobEdu").toString());
//style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style); //设置单元格样式 //设置单元格的值
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
}else if (j == 12) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("onthejobEdu_school")) && infoList.get(i).get("onthejobEdu_school") != null){
cell.setCellValue(infoList.get(i).get("onthejobEdu_school").toString());
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
cell.setCellStyle(style); //设置单元格样式 //设置单元格的值
} else{
cell.setCellValue("");
cell.setCellStyle(style);
}
}else if(j == 13){
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("");
cell.setCellStyle(style);
}
//cell.setCellStyle(style);
}
}
}
//sheet.addMergedRegion(new CellRangeAddress(0, 0, 11, 12)); //合并单元格
// sheet.addMergedRegion(new CellRangeAddress(0, 0, 13, 14));
//让列宽随着导出的列长自动适应
for (int colNum = 0; colNum < columnNum; colNum++) {
int columnWidth = sheet.getColumnWidth(colNum) / 256;
/* for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
HSSFRow currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(colNum) != null) {
HSSFCell currentCell = currentRow.getCell(colNum);
}
} */
if(colNum==0){
sheet.setColumnWidth(0, 1600);
}else if(colNum==1){
sheet.setColumnWidth(1, 740);
}else if(colNum==2){
sheet.setColumnWidth(2, 2000);
}else if(colNum==3){
sheet.setColumnWidth(3, 6600);
}else if(colNum==4){
sheet.setColumnWidth(4, 2000);
}else if(colNum==5){
sheet.setColumnWidth(5, 2000);
}else if(colNum==6){
sheet.setColumnWidth(6, 2000);
}else if(colNum==7){
sheet.setColumnWidth(7, 2000);
}else if(colNum==8){
sheet.setColumnWidth(8, 2000);
}else if(colNum==9){
sheet.setColumnWidth(9, 2200);
}else if(colNum==10){
sheet.setColumnWidth(10, 4000);
}else if(colNum==11){
sheet.setColumnWidth(11, 2200);
}else if(colNum==12){
sheet.setColumnWidth(12, 4000);
}else if(colNum==13){
sheet.setColumnWidth(13, 4000);
}
}
if(workbook != null){
try {
OutputStream fOut = new FileOutputStream(file);
workbook.write(fOut);
fOut.flush();
fOut.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}catch(Exception e){
e.printStackTrace();
}
return fileName;
}
@Override
public String daochuByGanbu(HttpServletRequest request, String wsda_id) {
String fileName = System.currentTimeMillis() + ".xls";
/*String path = getClass().getClassLoader().getResource("/").getPath()*/
String path =request.getSession().getServletContext().getRealPath("/")
+ "upload/excel/export/" + fileName;
//System.out.println(path);
File file = new File(path);
try {
file.createNewFile();
} catch (IOException e1) {
e1.printStackTrace();
}
String title = "干部花名册";
String rowName[] = new String[]{};
String[] rowName1 = {"单位", "姓 名", "现工作单位及职务", "拟任职单位及职务","拟免职务","性别","籍贯","任职 时间","同级 时间","出生 时间","参加工作时间","入党 时间","学历","毕业院校及专业","备注"};
rowName = rowName1;
List<Map<String, Object>> infoList = dao.getdaochuExcelList( wsda_id);
try{
HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
HSSFSheet sheet = workbook.createSheet(title); // 创建工作表
// 产生表格标题行
//HSSFRow rowm = sheet.createRow(0);
//HSSFCell cellTiltle = rowm.createCell(0);
//sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
HSSFCellStyle style = workbook.createCellStyle();
style.setWrapText(true); //单元格样式对象
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //设置单元格的值
//设置单元格样式
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//cellTiltle.setCellStyle(columnTopStyle); //标题样式
// 定义所需列数
int columnNum = rowName.length;
HSSFRow rowRowName = sheet.createRow(0); // 在索引0的位置创建行(最顶端的行开始的第二行)
// 将列头设置到sheet的单元格中
for(int n=0;n<columnNum;n++){
HSSFCell cellRowName = rowRowName.createCell(n); //创建列头对应个数的单元格
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); //设置列头单元格的数据类型
HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
cellRowName.setCellValue(text); //设置列头单元格的值
cellRowName.setCellStyle(columnTopStyle); //设置列头单元格样式
}
//将查询出的数据设置到sheet对应的单元格中
for(int i=0;i<infoList.size();i++){
if(!StringUtils.isEmpty(infoList.get(i))){
//Map<String, Object> obj = infoList.get(i);//遍历每个对象
HSSFRow row = sheet.createRow(i+1);//创建所需的行数
/*if(!"".equals(infoList.get(i).get("jobSiteAndPost")) && infoList.get(i).get("jobSiteAndPost") != null){
String jobSiteAndPost=infoList.get(i).get("jobSiteAndPost").toString();
}*/
row.setHeightInPoints(50);
for(int j=0; j<columnNum+2; j++){
HSSFCell cell = null; //设置单元格的数据类型
if(j == 0) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("dept_name")) && infoList.get(i).get("name") != null){
cell.setCellValue(infoList.get(i).get("dept_name").toString());
cell.setCellStyle(style);
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
} else if(j == 1) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("name")) && infoList.get(i).get("nativePlace") != null){
cell.setCellValue(infoList.get(i).get("name").toString()); //设置单元格的值
//style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style); //设置单元格样式
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
} else if (j == 2) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("jobSiteAndPost")) && infoList.get(i).get("jobSiteAndPost") != null){
cell.setCellValue(infoList.get(i).get("jobSiteAndPost").toString()); //设置单元格的值
//style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
cell.setCellStyle(style); //设置单元格样式
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
}else if (j == 3) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("appointPost")) && infoList.get(i).get("jobSiteAndPost") != null){
cell.setCellValue(infoList.get(i).get("appointPost").toString()); //设置单元格的值
//style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
cell.setCellStyle(style); //设置单元格样式
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
} else if (j == 4) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("admitancePost")) && infoList.get(i).get("jobSiteAndPost") != null){
cell.setCellValue(infoList.get(i).get("admitancePost").toString()); //设置单元格的值
//style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
cell.setCellStyle(style); //设置单元格样式
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
} else if (j == 5) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("gender")) && infoList.get(i).get("gender") != null){
if("1".equals(infoList.get(i).get("gender"))){
cell.setCellValue("男");
//style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style); //设置单元格样式
}
else if("0".equals(infoList.get(i).get("gender"))){
cell.setCellValue("女");
//style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style); //设置单元格样式
}else{
cell.setCellValue("");//设置单元格的值
cell.setCellStyle(style);
}
}
}else if (j == 6) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("nativePlace")) && infoList.get(i).get("nativePlace") != null){
cell.setCellValue(infoList.get(i).get("nativePlace").toString()); //设置单元格的值
//style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style); //设置单元格样式
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
}else if (j == 7) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("xzsj")) && infoList.get(i).get("xzsj") != null){
String xzsj = infoList.get(i).get("xzsj").toString().substring(0,7);
if(!"".equals(infoList.get(i).get("xzsj2")) && infoList.get(i).get("xzsj2") != null){
xzsj+=infoList.get(i).get("xzsj2").toString().substring(0,7);
}
if(!"".equals(infoList.get(i).get("xzsj3")) && infoList.get(i).get("xzsj3") != null){
xzsj+=infoList.get(i).get("xzsj3").toString().substring(0,7);
}
if(!"".equals(infoList.get(i).get("xzsj4")) && infoList.get(i).get("xzsj4") != null){
xzsj+=infoList.get(i).get("xzsj4").toString().substring(0,7);
}
cell.setCellValue(xzsj.replaceAll("-", ".")); //设置单元格的值
//style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style); //设置单元格样式
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
}else if (j == 8) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("aa")) && infoList.get(i).get("aa") != null){
String data = infoList.get(i).get("aa").toString().substring(0,7);
cell.setCellValue(data.replaceAll("-", ".")); //设置单元格的值
//style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style); //设置单元格样式
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
}else if (j == 9) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("birthday")) && infoList.get(i).get("birthday") != null){
String birthday = infoList.get(i).get("birthday").toString().substring(0,7);
cell.setCellValue(birthday.replaceAll("-", "."));
//设置单元格的值
//style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style); //设置单元格样式
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
}else if (j == 10) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("jobTime")) && infoList.get(i).get("jobTime") != null){
String data = infoList.get(i).get("jobTime").toString().substring(0,7);
cell.setCellValue(data.replaceAll("-", ".")); //设置单元格的值
//style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style); //设置单元格样式
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
}else if (j == 11) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("joinDate")) && infoList.get(i).get("joinDate") != null){
String pf ="";
if(null!=infoList.get(i).get("politicsFace") && !"".equals(infoList.get(i).get("politicsFace"))){
pf= infoList.get(i).get("politicsFace").toString();
}
if(!"中共党员".equals(pf) && !"预备党员".equals(pf)){
String data =pf;
data+=infoList.get(i).get("joinDate").toString().substring(0,7);
cell.setCellValue(data.replaceAll("-", "."));
}else{
String data = infoList.get(i).get("joinDate").toString().substring(0,7);
cell.setCellValue(data.replaceAll("-", "."));
}
//style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style);} //设置单元格样式 //设置单元格的值
}else if (j == 12) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("fulldayEdu")) && infoList.get(i).get("fulldayEdu") != null){
cell.setCellValue(infoList.get(i).get("fulldayEdu").toString());
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
cell.setCellStyle(style); //设置单元格样式 //设置单元格的值
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
}else if (j == 13) {
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(infoList.get(i).get("fulldayEdu_school")) && infoList.get(i).get("fulldayEdu_school") != null){
cell.setCellValue(infoList.get(i).get("fulldayEdu_school").toString());
//style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell.setCellStyle(style); //设置单元格样式 //设置单元格的值
}else{
cell.setCellValue("");
cell.setCellStyle(style);
}
}else if(j == 14){
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("");
cell.setCellStyle(style);
}
//cell.setCellStyle(style);
}
}
}
//sheet.addMergedRegion(new CellRangeAddress(0, 0, 11, 12)); //合并单元格
// sheet.addMergedRegion(new CellRangeAddress(0, 0, 13, 14));
//让列宽随着导出的列长自动适应
for (int colNum = 0; colNum < columnNum; colNum++) {
int columnWidth = sheet.getColumnWidth(colNum) / 256;
/* for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
HSSFRow currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(colNum) != null) {
HSSFCell currentCell = currentRow.getCell(colNum);
}
} */
if(colNum==0){
sheet.setColumnWidth(0, 3000);
}else if(colNum==1){
sheet.setColumnWidth(1, 1600);
}else if(colNum==2){
sheet.setColumnWidth(2, 6600);
}else if(colNum==3){
sheet.setColumnWidth(3, 3000);
}else if(colNum==4){
sheet.setColumnWidth(4, 2000);
}else if(colNum==5){
sheet.setColumnWidth(5, 2000);
}else if(colNum==6){
sheet.setColumnWidth(6, 2000);
}else if(colNum==7){
sheet.setColumnWidth(7, 2000);
}else if(colNum==8){
sheet.setColumnWidth(8, 2000);
}else if(colNum==9){
sheet.setColumnWidth(9, 2200);
}else if(colNum==10){
sheet.setColumnWidth(10, 2200);
}else if(colNum==11){
sheet.setColumnWidth(11, 2200);
}else if(colNum==12){
sheet.setColumnWidth(12, 4000);
}else if(colNum==13){
sheet.setColumnWidth(13, 4000);
}else if(colNum==14){
sheet.setColumnWidth(13, 4000);
}
}
if(workbook != null){
try {
OutputStream fOut = new FileOutputStream(file);
workbook.write(fOut);
fOut.flush();
fOut.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}catch(Exception e){
e.printStackTrace();
}
return fileName;
}
/*
* 列头单元格样式
*/
public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short)10);
//字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("宋体");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(true);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
957
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
