在开发中我们经常遇到把数据导出到Excel中的需求,这里简单的描述下个人实现方式,提供了Excel2003 与Excel2007两种实现方式。实现如下:
1、接口类demo:
package com.tjhq.nyb.common.service;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Workbook;
publicinterfaceExpExcelUtilService{
/**
* 业务数据导出
* @param mapTitle LinkedHashMap 链式的Map 表头与字段对应关系
* @param List<?> 表格链式的数据集合,?为实体类
* @param excelStyle 样式表
* @return生成excel文档
* @throws Exception 抛出错误
*/
public WorkbookexportExcelWriter2007(Map<String,Object> mapTitle,List<?>list,ExcelStyle excelStyle) throws Exception;
public WorkbookexportExcelWriter2003(Map<String,Object> mapTitle,List<?>list,ExcelStyle excelStyle) throws Exception;
public WorkbookexportExcelWriter2003_(Map<String,Object> mapTitle,Map<String,List<?>> mapLstChry,ExcelStyle excelStyle) throws Exception;
public WorkbookexportExcelWriter2003_Title(Map<String,Object> mapTitle,Map<String,List<?>> mapLstChry,ExcelStyle excelStyle) throws Exception;
}
2、实现类demo:
package com.tjhq.nyb.common.service.impl;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Pattern;
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.hssf.util.Region;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import com.tjhq.nyb.common.service.ExcelStyle;
import com.tjhq.nyb.common.service.ExpExcelUtilService;
@Service
publicclassExpExcelUtilServiceImplimplementsExpExcelUtilService {
@SuppressWarnings({"unused","unchecked", "rawtypes" })
/**
* 业务数据导出
* @param mapTitle LinkedHashMap 链式的Map 表头与字段对应关系
* @param List<?> 表格链式的数据集合,?为实体类
* @param excelStyle 样式表
* @return生成excel文档
* @throws Exception 抛出错误
*/
public WorkbookexportExcelWriter2007(Map<String, Object> mapTitle,
List<?>list, ExcelStyle excelStyle) throws Exception {
Methodmetd = null;
Stringfdname = null;
Set<String>keySet = mapTitle.keySet();
Stringtitle = newString();
for (Object keyName :keySet) {
title+= mapTitle.get(keyName) + ",";// 拼接标题列名称
}
// 第一步,创建一个webbook,对应一个Excel文件
Workbookwb = newXSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
XSSFSheetsheet = (XSSFSheet) wb.createSheet("sheet1");
sheet.autoSizeColumn(1);
sheet.autoSizeColumn(1,true);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
XSSFRowrow = sheet.createRow((int) 0);
// 第四步,创建单元格,并设置值表头设置表头居中
XSSFCellStylestyle = (XSSFCellStyle) wb.createCellStyle();
XSSFCellStylecellStyle = (XSSFCellStyle) wb.createCellStyle();
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
XSSFFontfont = (XSSFFont) wb.createFont();
excelStyle.setExcelStyle2007(style,cellStyle, font);
// 选择需要用到的字体格式
style.setFont(font);
XSSFCellcell = row.createCell((short) 0);
String[]titles = title.split(",");
for (int i = 0; i < titles.length; i++) {
cell.setCellValue(titles[i]);
cell.setCellStyle(style);
cell= row.createCell((short) i + 1);
}
try {
int columnIndex = 0;
// 遍历集合
for (Object object : list){
Classclazz = object.getClass();// 获取集合中的对象类型
Field[]fds = clazz.getDeclaredFields();// 获取他的字段数组
int rowIndex = 0;
List<String>listName = newLinkedList<String>();
Map<String,Object> mapName = new LinkedHashMap<String, Object>();
for (Object keyName :keySet) {
for (Field field : fds) {// 遍历该数组
fdname= field.getName();// 得到字段名,
metd= clazz.getMethod("get" + change(fdname), null);// 根据字段名找到对应的get方法,null表示无参数
StringstrName = newString();
if (keyName.equals(fdname)&& metd != null) {// 比较是否在字段数组中存在name字段,如果不存在短路,如果存在继续判断该字段的get方法是否存在,同时存在继续执行
Objectname = metd.invoke(object, null);// 调用该字段的get方法
strName= String.valueOf(name);
if (strName == null || "null".endsWith(strName)){
strName= "";
}
mapName.put(rowIndex+ "",strName);
rowIndex++;
continue;
}
}
}
for (int i = 0; i <mapName.size(); i++) {
row= sheet.createRow((int) columnIndex + 1);
for (intj = 0; j < mapName.size(); j++) {
if(!"".equals(mapName.get(j+""))&&mapName.get(j+"")!=null){
sheet.setColumnWidth(j,20*256);
}else{
sheet.setColumnWidth(j,20*256);
}
// 第四步,创建单元格,并设置值
cell= row.createCell((short) j);
cell.setCellStyle(cellStyle);
cell.setCellValue(mapName.get(j+ "").toString());
}
}
columnIndex++;
}
}catch(Exception e) {
e.printStackTrace();
}
return wb;
}
/**
* 业务数据导出
* @param mapTitle LinkedHashMap 链式的Map 表头与字段对应关系
* @param List<?> 表格链式的数据集合,?为实体类
* @param excelStyle 样式表
* @return生成excel文档
* @throws Exception 抛出错误
*/
public HSSFWorkbookexportExcelWriter2003(Map<String, Object> mapTitle,
List<?>list, ExcelStyle excelStyle) throws Exception {
boolean isResetWidth=false; //by bjj add at 2016.03.30
Methodmetd = null;
Stringfdname = null;
Set<String>keySet = mapTitle.keySet();
Stringtitle = newString();
for (Object keyName :keySet) {
//by bjj add at 2016.03.30 begin
if(mapTitle.get(keyName).equals("ReSetWidth")){
isResetWidth=true;
continue;
}
//by bjj add at 2016.03.30 end
title+= mapTitle.get(keyName) + ",";// 拼接标题列名称
}
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbookwb = newHSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheetsheet = wb.createSheet("sheet 1");
sheet.autoSizeColumn(1);
sheet.autoSizeColumn(1,true);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRowtitleRow = sheet.createRow((short) 0);
// 第四步,创建单元格,并设置值表头设置表头居中
HSSFCellStylestyle = wb.createCellStyle();
HSSFCellStylecellStyle = wb.createCellStyle();
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
HSSFFontfont = wb.createFont();
excelStyle.setExcelStyle2003(style,cellStyle, font);
// 选择需要用到的字体格式
style.setFont(font);
String[]titles = title.split(",");
for (int i = 0, size = titles.length; i < size; i++) {// 创建第1行标题单元格
switch (i) {
case 0:
//by bjj add at 2016.03.30 begin
if(isResetWidth){
sheet.setColumnWidth(0,20*256);
break;
}
//by bjj add at 2016.03.30 end
sheet.setColumnWidth(0,300);
break;
case 1:
//by bjj add at 2016.03.30 begin
if(isResetWidth){
sheet.setColumnWidth(1,20*256);
break;
}
//by bjj add at 2016.03.30 end
sheet.setColumnWidth(1,400);
break;
case 2:
//by bjj add at 2016.03.30 begin
if(isResetWidth){
sheet.setColumnWidth(2,20*256);
break;
}
//by bjj add at 2016.03.30 begin
sheet.setColumnWidth(2,400);
break;
//by bjj add at 2016.03.30 end
case 3:
//by bjj add at 2016.03.30 begin
if(isResetWidth){
sheet.setColumnWidth(3,20*256);
break;
}
//by bjj add at 2016.03.30 begin
sheet.setColumnWidth(3,200);
break;
//by bjj add at 2016.03.30 end
case 4:
//by bjj add at 2016.03.30 begin
sheet.setColumnWidth(4,50*256);
break;
//by bjj add at 2016.03.30 end
case 5:
//by bjj add at 2016.03.30 begin
sheet.setColumnWidth(5,100*256);
break;
//by bjj add at 2016.03.30 end
}
HSSFCellcell = titleRow.createCell(i, 0);
cell.setCellStyle(cellStyle);
cell.setCellValue(titles[i]);
}
try {
int columnIndex = 0;
// 遍历集合
for (Object object : list){
Classclazz = object.getClass();// 获取集合中的对象类型
Field[]fds = clazz.getDeclaredFields();// 获取他的字段数组
int rowIndex = 0;
List<String>listName = new LinkedList<String>();
Map<String,Object> mapName = new LinkedHashMap<String, Object>();
for (Object keyName :keySet) {
for (Field field : fds) {// 遍历该数组
fdname= field.getName();// 得到字段名,
metd= clazz.getMethod("get" + change(fdname), null);// 根据字段名找到对应的get方法,null表示无参数
StringstrName = newString();
if (keyName.equals(fdname)&& metd != null) {// 比较是否在字段数组中存在name字段,如果不存在短路,如果存在继续判断该字段的get方法是否存在,同时存在继续执行
Objectname = metd.invoke(object, null);// 调用该字段的get方法
strName = String.valueOf(name);
if (strName == null || "null".endsWith(strName)){
strName= "";
}
mapName.put(rowIndex+ "",strName);
rowIndex++;
continue;
}
}
}
for (int i = 0; i <mapName.size(); i++) {
titleRow= sheet.createRow((int) columnIndex + 1);
for (int j = 0; j <mapName.size(); j++) {
if(!isResetWidth){//bjj 于2016.03.30加該判斷
if(!"".equals(mapName.get(j+""))&&mapName.get(j+"")!=null){
sheet.setColumnWidth(j,20*256);
}else{
sheet.setColumnWidth(j,20*256);
}
}
// 第四步,创建单元格,并设置值
HSSFCellcell = titleRow.createCell( j, 0);
cell.setCellStyle(cellStyle);
cell.setCellValue(mapName.get(j+ "").toString());
}
}
columnIndex++;
}
}catch(Exception e) {
e.printStackTrace();
}
return wb;
}
/**
* 业务数据导出
* @param mapTitles LinkedHashMap 链式的Map 表头与字段对应关系 {"key1" : {"BSDW", "报送单位", "HYLXNAME", "会议类别"}}
* @param List<?> 表格链式的数据集合,?为实体类
* @param excelStyle 样式表
* @return生成excel文档
* @throws Exception 抛出错误
*/
public HSSFWorkbookexportExcelWriter2003_(Map<String, Object> mapTitles,
Map<String,List<?>> maplist, ExcelStyle excelStyle) throws Exception {
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbookwb = newHSSFWorkbook();
for (Map.Entry<String,Object> entry : mapTitles.entrySet()) {
StringsheetName = entry.getKey(); // 获取map中的key值赋值sheet名
Map<String,Object> mapTitle = (Map<String, Object>) entry.getValue();
moreSheets(mapTitle,maplist.get(sheetName), excelStyle, wb, sheetName);
}
return wb;
}
/**
* 业务数据导出
* @param mapTitles LinkedHashMap 链式的Map 表头与字段对应关系 {"key1" : {"EXCEL_TITLE_NAME":"此页的标题","BSDW":"报送单位", "HYLXNAME":"会议类别"}}
* @param List<?> 表格链式的数据集合,?为实体类
* @param excelStyle 样式表
* @return生成excel文档
* @throws Exception 抛出错误
*/
public HSSFWorkbookexportExcelWriter2003_Title(Map<String, Object> mapTitles, Map<String,List<?>> maplist, ExcelStyle excelStyle) throws Exception {
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbookwb = newHSSFWorkbook();
for (Map.Entry<String,Object> entry : mapTitles.entrySet()) {
StringsheetName = entry.getKey(); // 获取map中的key值赋值sheet名
Map<String,Object> mapTitle = (Map<String, Object>) entry.getValue();
if (null != mapTitle.get("EXCEL_TITLE_NAME")&& !"".equals(mapTitle.get("EXCEL_TITLE_NAME"))){
StringtitleName = mapTitle.get("EXCEL_TITLE_NAME").toString(); // 获取excel每页签的标题名称
mapTitle.remove("EXCEL_TITLE_NAME");
moreSheets(mapTitle,maplist.get(sheetName), excelStyle, wb, sheetName, titleName);
}else
moreSheets(mapTitle,maplist.get(sheetName), excelStyle, wb, sheetName);
}
return wb;
}
privatevoidmoreSheets(Map<String, Object> mapTitle, List<?> list,
ExcelStyleexcelStyle, HSSFWorkbook wb, String sheetName) {
Methodmetd = null;
Stringfdname = null;
Set<String>keySet = mapTitle.keySet();
Stringtitle = newString();
for (Object keyName :keySet) {
title+= mapTitle.get(keyName) + ",";// 拼接标题列名称
}
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheetsheet = wb.createSheet(sheetName);
sheet.autoSizeColumn(1);
sheet.autoSizeColumn(1,true);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRowtitleRow = sheet.createRow((short) 0);
// 第四步,创建单元格,并设置值表头设置表头居中
HSSFCellStylestyle = wb.createCellStyle();
HSSFCellStylecellStyle = wb.createCellStyle();
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
HSSFFontfont = wb.createFont();
excelStyle.setExcelStyle2003(style,cellStyle, font);
// 选择需要用到的字体格式
style.setFont(font);
String[]titles = title.split(",");
for (int i = 0, size = titles.length; i < size; i++) {// 创建第1行标题单元格
switch (i) {
case 0:
sheet.setColumnWidth(0,300);
break;
case 1:
sheet.setColumnWidth(1,400);
break;
case 2:
sheet.setColumnWidth(2,400);
break;
case 3:
sheet.setColumnWidth(3,200);
break;
}
HSSFCellcell = titleRow.createCell(i, 0);
cell.setCellStyle(cellStyle);
cell.setCellValue(titles[i]);
}
try {
int columnIndex = 0;
// 遍历集合
for (Object object : list){
Classclazz = object.getClass();// 获取集合中的对象类型
Field[]fds = clazz.getDeclaredFields();// 获取他的字段数组
int rowIndex = 0;
List<String>listName = new LinkedList<String>();
Map<String,Object> mapName = new LinkedHashMap<String, Object>();
for (Object keyName :keySet) {
for (Field field : fds) {// 遍历该数组
fdname= field.getName();// 得到字段名,
metd= clazz.getMethod("get" + change(fdname), null);// 根据字段名找到对应的get方法,null表示无参数
StringstrName = newString();
if (keyName.equals(fdname)&& metd != null) {// 比较是否在字段数组中存在name字段,如果不存在短路,如果存在继续判断该字段的get方法是否存在,同时存在继续执行
Objectname = metd.invoke(object, null);// 调用该字段的get方法
strName= String.valueOf(name);
if (strName == null || "null".endsWith(strName)){
strName= "";
}
mapName.put(rowIndex+ "",strName);
rowIndex++;
continue;
}
}
}
for (int i = 0; i <mapName.size(); i++) {
titleRow= sheet.createRow((int) columnIndex + 1);
for (int j = 0; j <mapName.size(); j++) {
if(!"".equals(mapName.get(j+""))&&mapName.get(j+"")!=null){
sheet.setColumnWidth(j,20*256);
}else{
sheet.setColumnWidth(j,20*256);
}
// 第四步,创建单元格,并设置值
HSSFCellcell = titleRow.createCell( j, 0);
cell.setCellStyle(cellStyle);
cell.setCellValue(mapName.get(j+ "").toString());
}
}
columnIndex++;
}
}catch(Exception e) {
e.printStackTrace();
}
}
privatevoidmoreSheets(Map<String, Object> mapTitle, List<?> list, ExcelStyleexcelStyle, HSSFWorkbook wb, String sheetName,String titleName) {
Methodmetd = null;
Stringfdname = null;
Set<String>keySet = mapTitle.keySet();
Stringtitle = newString();
for (Object keyName :keySet) {
title+= mapTitle.get(keyName) + ",";// 拼接标题列名称
}
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheetsheet = wb.createSheet(sheetName);
sheet.autoSizeColumn(1);
sheet.autoSizeColumn(1,true);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
// 合并单元格,为了给标题留地方
Regionregion = newRegion((short) 0, (short) 0, (short) 0, (short) (mapTitle.size() - 1));// 合并从第rowFrom行columnFrom列
sheet.addMergedRegion(region);// 到rowTo行columnTo的区域
// 1.设置标题
HSSFRowrow_title = sheet.createRow((short) 0);
row_title.setHeightInPoints(40);//第一个参数代表列id(从0开始),第2个参数代表宽度值
HSSFCellcell = row_title.createCell(0);
HSSFCellStylestyle_title = wb.createCellStyle();
HSSFFontfont_title = wb.createFont();
excelStyle.setExcelStyle2003_title(style_title,font_title);
style_title.setFont(font_title);
cell.setCellStyle(style_title);// 给标题赋样式
cell.setCellValue(titleName);// 给标题赋值
// 2.设置列表表头
HSSFRowrow_header = sheet.createRow((short) 1);
row_header.setHeightInPoints((float)31.5); //第一个参数代表列id(从0开始),第2个参数代表宽度值
HSSFCellStylestyle_header = wb.createCellStyle();
HSSFFontfont_header = wb.createFont();
excelStyle.setExcelStyle2003_header(style_header,font_header);
font_header.setFontHeightInPoints((short)12);
style_header.setFont(font_header);
if (titleName.contains("活动会议")) {
String[]titles = title.split(",");
for (int i = 0, size = titles.length; i < size; i++) {// 创建第1行标题单元格
switch (i) {
case 0:
sheet.setColumnWidth(i,(int)(15* 256));
break;
case 1:
sheet.setColumnWidth(i,(int)(10.5* 256));
break;
case 2:
sheet.setColumnWidth(i,(int)(10.5* 256));
break;
case 3:
sheet.setColumnWidth(i,(int)(21* 256));
break;
case 4:
sheet.setColumnWidth(i,(int)(21* 256));
break;
case 5:
sheet.setColumnWidth(i,(int)(21* 256));
break;
case 6:
sheet.setColumnWidth(i,(int)(21* 256));
break;
case 7:
sheet.setColumnWidth(i,(int)(21* 256));
break;
default:
sheet.setColumnWidth(i,3000);
}
cell= row_header.createCell(i, 0);
cell.setCellStyle(style_header);
cell.setCellValue(titles[i]);
}
}else{
String[]titles = title.split(",");
for (int i = 0, size = titles.length; i < size; i++) {// 创建第1行标题单元格
switch (i) {
case 0:
sheet.setColumnWidth(i,(int)(15* 256));
break;
case 1:
sheet.setColumnWidth(i,(int)(10.5* 256));
break;
case 2:
sheet.setColumnWidth(i,(int)(10.5* 256));
break;
case 3:
sheet.setColumnWidth(i,(int)(10.5* 256));
break;
case 4:
sheet.setColumnWidth(i,(int)(10.5* 256));
break;
case 5:
sheet.setColumnWidth(i,(int)(10.5* 256));
break;
case 6:
sheet.setColumnWidth(i,(int)(10.5* 256));
break;
case 7:
sheet.setColumnWidth(i,(int)(10.5* 256));
break;
default:
sheet.setColumnWidth(i,3000);
}
cell= row_header.createCell(i, 0);
cell.setCellStyle(style_header);
cell.setCellValue(titles[i]);
}
}
// 3.设置列表字段内容
HSSFCellStylestyle_content = wb.createCellStyle();
HSSFFontfont_content = wb.createFont();
excelStyle.setExcelStyle2003_content(style_content,font_content);
style_content.setAlignment(HSSFCellStyle.ALIGN_CENTER);
font_content.setFontHeightInPoints((short)10 );
style_content.setFont(font_content);
try {
int columnIndex = 1;
// 遍历集合
for (Object object : list){
Classclazz = object.getClass();// 获取集合中的对象类型
Field[]fds = clazz.getDeclaredFields();// 获取他的字段数组
int rowIndex = 0;
List<String>listName = new LinkedList<String>();
Map<String,Object> mapName = new LinkedHashMap<String, Object>();
for (Object keyName :keySet) {
for (Field field : fds) {// 遍历该数组
fdname= field.getName();// 得到字段名,
metd= clazz.getMethod("get" + change(fdname), null);// 根据字段名找到对应的get方法,null表示无参数
StringstrName = newString();
if (keyName.equals(fdname)&& metd != null) {// 比较是否在字段数组中存在name字段,如果不存在短路,如果存在继续判断该字段的get方法是否存在,同时存在继续执行
Objectname = metd.invoke(object, null);// 调用该字段的get方法
strName= String.valueOf(name);
if (strName == null || "null".endsWith(strName)){
strName= "";
}
mapName.put(rowIndex+ "",strName);
rowIndex++;
continue;
}
}
}
for (int i = 0; i <mapName.size(); i++) {
row_header= sheet.createRow((int) columnIndex + 1);
//row_header.setHeightInPoints((float)14); //第一个参数代表列id(从0开始),第2个参数代表宽度值
for (int j = 0; j <mapName.size(); j++) {
// if(!"".equals(mapName.get(j + "")) && mapName.get(j +"") != null) {
// sheet.setColumnWidth(j,(int)(14.57 * 256));
//
// }
// 第四步,创建单元格,并设置值
cell= row_header.createCell(j, 0);
cell.setCellStyle(style_content);
Stringvalue = mapName.get(j + "") == null? "": mapName.get(j + "").toString();
float hieght = getExcelCellAutoHeight(value,8f);
//根据字符串的长度设置高度
sheet.setDefaultRowHeightInPoints(hieght);
cell.setCellValue(value);
}
}
columnIndex++;
}
}catch(Exception e) {
e.printStackTrace();
}
}
/**
* @param src 源字符串
* @return字符串,将src的第一个字母转换为大写,src为空时返回null
*/
publicstatic String change(Stringsrc) {
if (src != null) {
StringBuffersb = newStringBuffer(src);
sb.setCharAt(0,Character.toUpperCase(sb.charAt(0)));
return sb.toString();
}else{
returnnull;
}
}
publicstaticfloatgetExcelCellAutoHeight(String str, float fontCountInline) {
float defaultRowHeight =12.00f;// 每一行的高度指定
float defaultCount = 0.00f;
for (int i = 0; i <str.length(); i++) {
float ff = getregex(str.substring(i,i + 1));
defaultCount= defaultCount + ff;
}
return ((int) (defaultCount /fontCountInline) + 1) * defaultRowHeight;// 计算
}
publicstaticfloat getregex(StringcharStr) {
if (charStr == " ") {
return 0.5f;
}
// 判断是否为字母或字符
if (Pattern.compile("^[A-Za-z0-9]+$").matcher(charStr).matches()){
return 0.5f;
}
// 判断是否为全角
if (Pattern.compile("[\u4e00-\u9fa5]+$").matcher(charStr).matches()){
return 1.00f;
}
// 全角符号及中文
if (Pattern.compile("[^x00-xff]").matcher(charStr).matches()){
return 1.00f;
}
return 0.5f;
}
}
3、调用实例demo:
/**
* 内容摘要下载 by bjj add at 2016.03.30
* @param request
* @param response
* @return
* @throws Exception
*/
@RequestMapping(value="/excel/nrzyExport",method={RequestMethod.GET,RequestMethod.POST})
@ResponseBody
publicvoidexport(HttpServletRequest request,HttpServletResponse response) throws Exception{
List<NeiRongZhaiYaoQuery>datalist = newArrayList<NeiRongZhaiYaoQuery>();
Stringstart_time= request.getParameter("start_time");
Stringend_tiem= request.getParameter("end_tiem");
Map<String,String>param=newHashMap<String,String>();
param.put("start_time","'"+start_time+"'");
param.put("end_tiem","'"+end_tiem+"'");
//这里需要从数据库中查询数据
List<Map<String,Object>>exportData= leaderCommentMapper.queryExportNRZYData(param);
for(Map<String,Object> map:exportData) {
NeiRongZhaiYaoQueryexportBo = newNeiRongZhaiYaoQuery();
if (map.get("RECEIVE_NO")!= null){
exportBo.setReceive_No(map.get("RECEIVE_NO").toString());
}
if (map.get("SEND_DEPT_NAME")!= null){
exportBo.setSend_Dept_Name(map.get("SEND_DEPT_NAME").toString());
}
if (map.get("RECEIVE_SUBJECT")!= null){
exportBo.setReceive_Subject(map.get("RECEIVE_SUBJECT").toString());
}
if (map.get("RECEIVE_DATE")!= null){
exportBo.setReceive_Date(map.get("RECEIVE_DATE").toString());
}
if (map.get("BLANK43")!= null){
exportBo.setBlank43(HtmlToText.html2text(map.get("BLANK43").toString()));
}
if (map.get("BLANK15")!= null){
exportBo.setBlank15(HtmlToText.html2text(map.get("BLANK15").toString()));
}
datalist.add(exportBo);
}
//开始下载
try {
Stringfilename = "attachment; filename=" + java.net.URLEncoder.encode(dateStrHandl(start_time)+"_"+dateStrHandl(end_tiem)+"_"+"内容摘要下载.xls", "UTF-8");
response.setContentType("application/x-excel");
response.setHeader("Content-Disposition",filename);
OutputStreamoutputStream = response.getOutputStream();
Workbookworkbook = expExcelUtilService.exportExcelWriter2003(NeiRongZhaiYaoQuery.getMapTitle(),datalist, newNormalExcelStyle());
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}catch(Exception e) {
e.printStackTrace();
}
}
4、实体类demo:
package com.tjhq.nyb.gwgl.bo;
import java.util.LinkedHashMap;
import java.util.Map;
public class NeiRongZhaiYaoQuery {
//收文编号
privateString receive_No;
//发文单位及文号
privateString send_Dept_Name;
//标题
privateString receive_Subject;
//收文日期
privateString receive_Date;
//内容摘要
privateString blank43;
//领导批示
privateString blank15;
publicstatic Map<String, Object> mapTitle = new LinkedHashMap<String,Object>();
static{
mapTitle.put("receive_No","收文编号");
mapTitle.put("send_Dept_Name","发文单位及文号");
mapTitle.put("receive_Subject","标题");
mapTitle.put("receive_Date","收文日期");
mapTitle.put("blank43","内容摘要");
mapTitle.put("blank15","领导批示");
mapTitle.put("isReSetWidth","ReSetWidth");
}
publicstatic Map<String, Object> getMapTitle() {
returnmapTitle;
}
publicstatic void setMapTitle(Map<String, Object> mapTitle) {
NeiRongZhaiYaoQuery.mapTitle= mapTitle;
}
publicstatic NeiRongZhaiYaoQuery corvertBoMap(Map<String, Object> data) {
NeiRongZhaiYaoQueryexportBo = new NeiRongZhaiYaoQuery();
exportBo.setReceive_No(data.get("receive_No").toString());
exportBo.setSend_Dept_Name(data.get("send_Dept_Name").toString());
exportBo.setReceive_Subject(data.get("receive_Subject").toString());
exportBo.setReceive_Date(data.get("receive_Date").toString());
exportBo.setBlank43(data.get("blank43").toString());
exportBo.setBlank15(data.get("blank15").toString());
if(data.get("receive_No") != null) {
exportBo.setReceive_No(data.get("receive_No").toString());
}
if(data.get("send_Dept_Name") != null) {
exportBo.setSend_Dept_Name(data.get("send_Dept_Name").toString());
}
if(data.get("receive_Subject") != null) {
exportBo.setReceive_Subject(data.get("receive_Subject").toString());
}
if(data.get("receive_Date") != null) {
exportBo.setReceive_Date(data.get("receive_Date").toString());
}
if(data.get("blank43") != null) {
exportBo.setBlank43(data.get("blank43").toString());
}
if(data.get("blank15") != null) {
exportBo.setBlank15(data.get("blank15").toString());
}
returnexportBo;
}
publicString getReceive_No() {
returnreceive_No;
}
publicvoid setReceive_No(String receive_No) {
this.receive_No= receive_No;
}
publicString getSend_Dept_Name() {
returnsend_Dept_Name;
}
publicvoid setSend_Dept_Name(String send_Dept_Name) {
this.send_Dept_Name= send_Dept_Name;
}
publicString getReceive_Subject() {
returnreceive_Subject;
}
publicvoid setReceive_Subject(String receive_Subject) {
this.receive_Subject= receive_Subject;
}
publicString getReceive_Date() {
returnreceive_Date;
}
publicvoid setReceive_Date(String receive_Date) {
this.receive_Date= receive_Date;
}
publicString getBlank43() {
returnblank43;
}
publicvoid setBlank43(String blank43) {
this.blank43= blank43;
}
publicString getBlank15() {
returnblank15;
}
publicvoid setBlank15(String blank15) {
this.blank15= blank15;
}
}
注:这里的实体类需要根据自己的业务需求去定义。