excel处理类:
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class CreateExcel<T> {
public void expExcel(String title, String[] headers,String[] context,Collection<T> dataset, OutputStream out){
exportExcel(title,headers,context,dataset,out,"yyyy/mm/dd");
}
public void expExcel(String title, String[] headers,String[] context,
Collection<T> dataset, OutputStream out, String pattern){
exportExcel(title,headers,context,dataset,out,pattern);
}
public void setExcelResponse(HttpServletResponse response,String name,String[] headers,String[] context,Collection<T> dataset){
OutputStream out = null;
try {
out = response.getOutputStream();
response.reset();// 清空输出流
response.setHeader("Content-disposition", "attachment; filename="
+ new String(name.getBytes("GBK"), "ISO-8859-1") + ".xlsx");// 设定输出文件头
response.setContentType("application/ms-excel;charset=UTF-8");// 定义输出类型
expExcel(name, headers, context, dataset, out);
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
out.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
private void exportExcel(String title, String[] headers, String[] context,
Collection<T> dataset, OutputStream out, String pattern)
{
// 声明一个工作薄
XSSFWorkbook workbook = new XSSFWorkbook();
// 生成一个表格
XSSFSheet sheet = workbook.createSheet(title);
// 生成一个样式
XSSFCellStyle style = workbook.createCellStyle();
XSSFCellStyle style2 = workbook.createCellStyle();
XSSFDataFormat format = workbook.createDataFormat();
style2.setDataFormat(format.getFormat("@"));
// 产生表格标题行
XSSFRow row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++)
{
XSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
XSSFRichTextString text = new XSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
// 遍历集合数据,产生数据行
Iterator<T> it = dataset.iterator();
int index = 0;
while (it.hasNext())
{
index++;
row = sheet.createRow(index);
T t = (T) it.next();
for (int i = 0; i < context.length; i++)
{
XSSFCell cell = row.createCell(i);
cell.setCellStyle(style2);
String fieldName = context[i];
String getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
try
{
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName,
new Class[]
{});
Object value = getMethod.invoke(t, new Object[]
{});
// 判断值的类型后进行强制类型转换
String textValue = null;
if(value==null){
continue;
}
else if (value instanceof Date)
{
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
}else if(value instanceof Double)
{
double dou=(Double)value;
DecimalFormat decimalFormat = new DecimalFormat("##0.00");//格式化设置
textValue=decimalFormat.format(dou);
}else
{
// 其它数据类型都当作字符串简单处理
textValue = value.toString();
}
// 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成
if (textValue != null)
{
Pattern p = Pattern.compile("^\\d+(\\.\\d+)?$");
Matcher matcher = p.matcher(textValue);
if (matcher.matches())
{
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
}
else
{
// HSSFRichTextString richString = new HSSFRichTextString(
// textValue);
// HSSFFont font3 = workbook.createFont();
// font3.setColor(HSSFColor.BLUE.index);
// richString.applyFont(font3);
cell.setCellValue(textValue);
}
}
}
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();
}
finally
{
// 清理资源
}
}
}
try
{
workbook.write(out);
}
catch (IOException e)
{
e.printStackTrace();
}
}
}
对应controller:
@RequestMapping(value = "conversionDownLoad")
public void conversionDownLoad(SearchField sf, String date) throws UnsupportedEncodingException {
String[] head={"时间","大区","区域","分公司","产品类型","贷款期限(期)",
"还款方式","贷款方式","贷款金额(元)","申请提交","贷款审核","贷款审核退回","贷款审核流失量","贷款审核转化率(%)","审批通过","审批退回","审批拒绝","审批客户取消","审批流失 ",
"审批通过的转化率(%)","合同签约成功量","合同作废数量","合同退回数量","合同流失量","合同签约转化率(%)","财务复核通过量","财务复核退回量","财务复核中遗失量",
"财务复核转化率(%)","放款复核通过量","放款复核退回量","放款复核流失量","放款复核转化率(%)","放款通过量","放款退回量","放款流失量","放款转化率(%)","结清量","展期量",
"展期中结清量","展期中结清率(%)","展期中再贷量","展期中再贷率(%)","结清转化率(%)","再贷数量","再贷转化率(%)"};
String[] context={"date","bigAreaName","smallAreaName","siteName","productName",
"loanTime","hkfsValue","dkfs","amount","applyCount","applyAuditCount","applyAuditBackCount","applyAuidtLostCount","applyAuditRate","applyAuditLastCount",
"applyAuditLastBackCount","applyAuditLastRefuseCount","applyAuditCancelCount","applyAuditLastLostCount","applyAuditLastRate",
"contractCount","contractOverCount","contractBackCount","contractLostCount","contractRate","fnCheckCount","fnCheckBackCount",
"fnCheckLostCount","fnCheckRate","loanCheckCount","loanCheckBackCount","loanCheckLostCount","loanCheckRate","loanCount","loanBackCount",
"loanLostCount","loanRate","settleCount","extensionCount","extensionSettleCount","extensionSettleRate","extensionContinueLoanCount",
"extensionContinueLoanRate","settleRate","continueLoan","continueRate"};
List<ConversionReport> datas = getReportDatas(sf, date);
CreateExcel<ConversionReport> crt =new CreateExcel<ConversionReport>();
String name="xx转化率数据分析表";
crt.setExcelResponse(response, name, head, context, datas);
}