public void export(HttpServletRequest request,HttpServletResponse response) {
String title = "ERP应付发票";
List<String> headers = Arrays.asList("发票编号 ","税率","发票金额","已付金额","已核销金额","发票余额","行金额","发票币种","发票描述","发票批名","合同号","供应商名称","供应商地点","公司","发票日期","入账日期","状态","入账状态");
List<String> fields = Arrays.asList("FP_BH","SL","FPJE","YFJE","YXHJE","FPYE","HJE","FPBZ","FPMS","FPPM","HTH","GYS_MC","GYS_DD","OU_NAME","FPRQ","RZRQ","ZT","RZZT");
String sql = "SELECT FP_BH,SL,FPJE,YFJE,YXHJE,FPYE,HJE,FPBZ,FPMS,FPPM,HTH,GYS_MC,GYS_DD,OU_NAME,FPRQ,RZRQ,ZT,RZZT FROM DB2ADMIN.T_MFT_INQUIRY_AP_INVOICE_DETAIL_SRV where 1=1 ";
List<Map<String,Object>> list = baseJdbcDao.queryForList(sql);
XSSFWorkbook workbook = new ExportExcel("yyyy-MM-dd").exportExcel(title, headers, fields, list);
try{
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("ERP应付发票.xlsx", "UTF-8"));
ServletOutputStream ouputStream = response.getOutputStream();
workbook.write(ouputStream);
ouputStream.flush();
ouputStream.close();
}catch(Exception e){
e.printStackTrace();
}
}
package beff.util.excelTool;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExportExcel {
//默认日期格式
private String pattern = "yyyy-MM-dd HH:mm:ss";
/**
* 此方法生成2003版本的excel,文件名后缀：xls
* @param title 表格标题
* @param headers 头部标题集合
* @param fields 属性名集合
* @param dataset excel表格需要显示的数据集合,类型为List<Map<String,Object>>
*/
public HSSFWorkbook exportExcel2003(String title, List<String> headers, List<String> fields,List<Map<String,Object>> dataset){
// 创建一个工作簿对象
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建一个表格对象
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽
sheet.setDefaultColumnWidth(20);
// 创建表格的标题行
HSSFRow row = sheet.createRow(0);
HSSFCell cellHeader;
for (int i = 0; i < headers.size(); i++) {
cellHeader = row.createCell(i);
cellHeader.setCellValue(new HSSFRichTextString(headers.get(i)));
}
// 使用迭代器遍历集合数据，创建数据行
Iterator<Map<String,Object>> it = dataset.iterator();
//正则表达式 用于校验是否是数字
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
//创建一个日期格式化对象
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
//对数据对象进行遍历
int index = 0;
while (it.hasNext()) {
index++;
//每一次循环创建一行（每一行对应着一个对象）
row = sheet.createRow(index);
Map<String,Object> m = it.next();
for (int i = 0; i < fields.size(); i++) {
//创建单元格（每个单元格对应着对象的每个属性）
HSSFCell cell = row.createCell(i);
Object value = m.get(fields.get(i));
// 判断值的类型后进行强制类型转换
String textValue = null;
if (value instanceof Integer) {
cell.setCellValue((Integer) value);
} else if (value instanceof Float) {
textValue = String.valueOf((Float) value);
cell.setCellValue(textValue);
} else if (value instanceof Double) {
textValue = String.valueOf((Double) value);
cell.setCellValue(textValue);
} else if (value instanceof Long) {
cell.setCellValue((Long) value);
}else{
if (value instanceof Boolean) {
textValue = "是";
if (!(Boolean) value) {
textValue = "否";
}
} else if (value instanceof Date) {
textValue = sdf.format((Date) value);
} else {
// 其它数据类型都当作字符串简单处理
if (value!=null && !"".equals(value.toString().trim())) {
textValue = value.toString();
} else {
textValue = "";
}
}
Matcher matcher = p.matcher(textValue);
if (!StringUtils.isBlank(textValue)&&matcher.matches()) {
// 如果是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
} else {
HSSFRichTextString richString = new HSSFRichTextString(textValue);
cell.setCellValue(richString);
}
}
}
}
return workbook;
}
/**
* 通用Excel导出方法,利用反射机制遍历对象的所有字段，将数据写入Excel文件中
* 此方法生成2003版本的excel,文件名后缀：xls
* @param title 表格标题
* @param headers 头部标题集合
* @param fields 属性名集合
* @param dataset excel表格需要显示的数据集合。
*/
public <T> HSSFWorkbook exportExcel2003(String title, List<String> headers, List<String> fields, Collection<T> dataset) {
// 创建一个工作簿对象
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建一个表格对象
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽
sheet.setDefaultColumnWidth(20);
// 创建表格的标题行
HSSFRow row = sheet.createRow(0);
HSSFCell cellHeader;
for (int i = 0; i < headers.size(); i++) {
cellHeader = row.createCell(i);
cellHeader.setCellValue(new HSSFRichTextString(headers.get(i)));
}
// 使用迭代器遍历集合数据，创建数据行
Iterator<T> it = dataset.iterator();
int index = 0;
//正则表达式 用于校验是否是数字
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
//创建一个日期格式化对象
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
//对数据对象进行遍历
while (it.hasNext()) {
index++;
//每一次循环创建一行（每一行对应着一个对象）
row = sheet.createRow(index);
T t = (T) it.next();
for (int i = 0; i < fields.size(); i++) {
//创建单元格（每个单元格对应着对象的每个属性）
HSSFCell cell = row.createCell(i);
try {
//获取属性名
String fieldName = fields.get(i);
//根据属性名拼接出对应的get方法名
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
//获取对象的字节码对象
Class<? extends Object> tCls = t.getClass();
//获取对象的get方法
Method getMethod = tCls.getMethod(getMethodName, new Class[]{});
//执行get方法
Object value = getMethod.invoke(t, new Object[]{});
// 判断值的类型后进行强制类型转换
String textValue = null;
if (value instanceof Integer) {
cell.setCellValue((Integer) value);
} else if (value instanceof Float) {
textValue = String.valueOf((Float) value);
cell.setCellValue(textValue);
} else if (value instanceof Double) {
textValue = String.valueOf((Double) value);
cell.setCellValue(textValue);
} else if (value instanceof Long) {
cell.setCellValue((Long) value);
}else{
if (value instanceof Boolean) {
textValue = "是";
if (!(Boolean) value) {
textValue = "否";
}
} else if (value instanceof Date) {
textValue = sdf.format((Date) value);
} else {
// 其它数据类型都当作字符串简单处理
if (value!=null && !"".equals(value.toString().trim())) {
textValue = value.toString();
} else {
textValue = "";
}
}
Matcher matcher = p.matcher(textValue);
if (!StringUtils.isBlank(textValue)&&matcher.matches()) {
// 如果是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
} else {
HSSFRichTextString richString = new HSSFRichTextString(textValue);
cell.setCellValue(richString);
}
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
return workbook;
}
public ExportExcel() {}
/**
* 自定义日期格式
* @param pattern 日期格式字符串
*/
public ExportExcel(String pattern) {
this.pattern = pattern;
}
public XSSFWorkbook exportExcel(String title, List<String> headers, List<String> fields,List<Map<String,Object>> dataset){
// 创建一个工作簿对象
XSSFWorkbook workbook = new XSSFWorkbook();
// 创建一个表格对象
XSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽
sheet.setDefaultColumnWidth(20);
// 创建表格的标题行
XSSFRow row = sheet.createRow(0);
XSSFCell cellHeader;
for (int i = 0; i < headers.size(); i++) {
cellHeader = row.createCell(i);
cellHeader.setCellValue(headers.get(i));
}
// 使用迭代器遍历集合数据，创建数据行
Iterator<Map<String,Object>> it = dataset.iterator();
//正则表达式 用于校验是否是数字
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
//创建一个日期格式化对象
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
//对数据对象进行遍历
int index = 0;
while (it.hasNext()) {
index++;
//每一次循环创建一行（每一行对应着一个对象）
row = sheet.createRow(index);
Map<String,Object> m = it.next();
for (int i = 0; i < fields.size(); i++) {
//创建单元格（每个单元格对应着对象的每个属性）
XSSFCell cell = row.createCell(i);
Object value = m.get(fields.get(i));
// 判断值的类型后进行强制类型转换
String textValue = null;
if (value instanceof Integer) {
cell.setCellValue((Integer) value);
} else if (value instanceof Float) {
textValue = String.valueOf((Float) value);
cell.setCellValue(textValue);
} else if (value instanceof Double) {
textValue = String.valueOf((Double) value);
cell.setCellValue(textValue);
} else if (value instanceof Long) {
cell.setCellValue((Long) value);
}else{
if (value instanceof Boolean) {
textValue = "是";
if (!(Boolean) value) {
textValue = "否";
}
} else if (value instanceof Date) {
textValue = sdf.format((Date) value);
} else {
// 其它数据类型都当作字符串简单处理
if (value!=null && !"".equals(value.toString().trim())) {
textValue = value.toString();
} else {
textValue = "";
}
}
Matcher matcher = p.matcher(textValue);
if (!StringUtils.isBlank(textValue)&&matcher.matches()) {
// 如果是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
} else {
//HSSFRichTextString richString = new HSSFRichTextString(textValue);
cell.setCellValue(textValue);
}
}
}
}
return workbook;
}
}
