①action层调用
// 设置返回
LinkedHashMap<String,String> excelHead = new LinkedHashMap<String, String>();
excelHead.put("createTime", "转入时间");
excelHead.put("loginName", "用户名");
excelHead.put("transInAmount", "转入(元)");
excelHead.put("transOutAmount", "申请转出(元)");
List<MAPP> excelData = (List<MAPP>) pagination.getRows();//获得前台数据
ExportExcelUtil<MAPP> export= new ExportExcelUtil<MAPP>();
export.exportExcel(excelData, excelHead, ServletActionContext.getResponse());
②service层导出excel方法
public void exportExcel(List<T> data, LinkedHashMap<String, String> head,
HttpServletResponse response) {
int sheetRows = 80000;
int sheetTotal = data.size() / sheetRows;
BufferedOutputStream out = null;
// 声明一个工作薄
XSSFWorkbook workbook = new XSSFWorkbook();
for (int page = 0; page <= sheetTotal; page++) {
int realRows = sheetRows;
if (page == sheetTotal) {
realRows = data.size() - page * sheetRows;
}
// 生成一个表格
XSSFSheet sheet = workbook.createSheet("sheet" + (page + 1));
// 生成一个样式
XSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
/*
* style.setFillForegroundColor(XSSFColor.SKY_BLUE.index);
* style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
*/
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
XSSFFont font = workbook.createFont();
/* font.setColor(XSSFColor.VIOLET.index); */
font.setFontHeightInPoints((short) 12);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 生成并设置另一个样式
XSSFCellStyle style2 = workbook.createCellStyle();
/*
* style2.setFillForegroundColor(XSSFColor.LIGHT_YELLOW.index);
* style2.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
*/
style2.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style2.setBorderRight(XSSFCellStyle.BORDER_THIN);
style2.setBorderTop(XSSFCellStyle.BORDER_THIN);
style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
XSSFFont font2 = workbook.createFont();
font2.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style2.setFont(font2);
// 产生表格标题行
XSSFRow row = sheet.createRow(0);
int k = 0;
for (String title : head.values()) {
XSSFCell cell = row.createCell(k);
cell.setCellStyle(style);
XSSFRichTextString text = new XSSFRichTextString(title);
cell.setCellValue(text);
k++;
}
// 遍历集合数据,产生数据行
for (int index = 0; index < realRows; index++) {
row = sheet.createRow(index + 1);
T t = (T) data.get(page * sheetRows + index);
int n = 0;
for (String fieldName : head.keySet()) {
XSSFCell cell = row.createCell(n);
cell.setCellStyle(style2);
String methodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
try {
Method method = t.getClass().getMethod(methodName,
new Class[] {});
Object value = method.invoke(t, new Object[] {});
if (value == null) {
value = "";
}
// 判断值的类型后进行强制类型转换
String textValue = "";
if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss");
textValue = sdf.format(date);
cell.setCellValue(textValue);
} else {
XSSFRichTextString richString = new XSSFRichTextString(
ExportExcelUtil
.fromNumberToMoneyString(value));
cell.setCellValue(richString);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
n++;
}
}
}
try {
// 设置response的Header
response.setHeader("Content-Disposition", "attachment; filename="
+ new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())
+ ".xlsx");
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
out = new BufferedOutputStream(response.getOutputStream());
workbook.write(out);
// response.setContentType("application/excel;charset=utf-8");
out.flush();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
if (out != null) {
out.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
public static String fromNumberToMoneyString(Object ob) {
try {
String moneyString = String.format("%.2f", ob);
String reg = "^-?\\d+\\.\\d+$";
StringBuffer sb = new StringBuffer();
/*
* Pattern p=Pattern.compile(reg); Matcher matcher=
* p.matcher(moneyString); if(matcher.matches()){
*/
int point = moneyString.indexOf(".");
String n1 = "", n2 = "";
n1 = moneyString.substring(0, point);
n2 = moneyString.substring(point + 1);
int step = 0;
for (int i = n1.length() - 1; i >= 0; i--) {
char v = n1.charAt(i);
if (step != 0 && step % 3 == 0 && v != '-') {
sb.append(",");
sb.append(v);
} else {
sb.append(v);
}
step++;
}
/*
* if(n2.length()==1){ n2= n2+"0"; } if(n2.length()>2){ int k=1;
* for(int j=0;j<n2.length()-2;j++){ k=k*10; }
* n2=String.valueOf(Math.round(Double.valueOf(n2)/k)); }
*/
return sb.reverse().toString() + "." + n2;
/* }; */
} catch (Exception e) {
// TODO Auto-generated catch block
return String.valueOf(ob);
}
}
压缩包
/**
*
* 导出EXCEL压缩包
* @param List<T> data
* @param LinkedHashMap<String,String> head
* @param HttpServletResponse response
*
*/
@SuppressWarnings("unchecked")
public void exportExcelZip(List<T> data, LinkedHashMap<String,String> head,HttpServletResponse response,int rows) {
final int MAX_ROW=30000;
if(data==null||data.isEmpty()){
throw new RuntimeException("导出数据不能为空");
}
if(head==null||head.isEmpty()){
throw new RuntimeException("导出标题头不能为空");
}
if(rows<=0){
throw new RuntimeException("导出条数不能小于等于零");
}
if(rows> MAX_ROW){
throw new RuntimeException("导出条数不能大于"+MAX_ROW);
}
int pageTotal=0;
if(data.size()%rows==0){
pageTotal=data.size()/rows-1;
} else {
pageTotal=data.size()/rows;
}
BufferedOutputStream out=null;
ZipOutputStream zos=null;
int len = 0;
byte[] buff = new byte[2048];
try {
// 设置response的Header
response.setHeader("Content-Disposition", "attachment; filename="+new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+".zip");
response.setContentType("application/octet-stream;charset=UTF-8");
out= new BufferedOutputStream(response.getOutputStream());
zos=new ZipOutputStream(out);
} catch (IOException e1) {
e1.printStackTrace();
}
for (int page = 0; page <= pageTotal; page++) {
ByteArrayOutputStream bao=new ByteArrayOutputStream();
int realRows = 0;
if (page == pageTotal) {
realRows = data.size() - page * rows;
} else {
realRows = rows;
}
// 声明一个工作薄
XSSFWorkbook workbook = new XSSFWorkbook();
// 生成一个表格
XSSFSheet sheet = workbook.createSheet();
// 生成一个样式
XSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
XSSFFont font = workbook.createFont();
/* font.setColor(XSSFColor.VIOLET.index);*/
font.setFontHeightInPoints((short) 12);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 生成并设置另一个样式
XSSFCellStyle style2 = workbook.createCellStyle();
/* style2.setFillForegroundColor(XSSFColor.LIGHT_YELLOW.index);
style2.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);*/
style2.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style2.setBorderRight(XSSFCellStyle.BORDER_THIN);
style2.setBorderTop(XSSFCellStyle.BORDER_THIN);
style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
XSSFFont font2 = workbook.createFont();
font2.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style2.setFont(font2);
//产生表格标题行
XSSFRow row = sheet.createRow(0);
int k=0;
for (String title:head.values()) {
XSSFCell cell = row.createCell(k);
cell.setCellStyle(style);
XSSFRichTextString text = new XSSFRichTextString(title);
cell.setCellValue(text);
k++;
}
//遍历集合数据,产生数据行
for(int index=0;index<realRows;index++) {
row = sheet.createRow(index+1);
T t = (T) data.get(page*rows+index);
int n=0;
for (String fieldName:head.keySet()) {
XSSFCell cell = row.createCell(n);
cell.setCellStyle(style2);
String methodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
try {
Method method = t.getClass().getMethod(methodName, new Class[] {});
Object value = method.invoke(t, new Object[] {});
if(value==null){
value="";
}
//判断值的类型后进行强制类型转换
String textValue ="";
if (value instanceof java.sql.Date) {
java.sql.Date date = (java.sql.Date) value;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
textValue = sdf.format(date);
cell.setCellValue(textValue);
} else if (value instanceof Date) {
Date dateTime = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
textValue = sdf.format(dateTime);
cell.setCellValue(textValue);
} else {
XSSFRichTextString richString = new XSSFRichTextString(ExportExcelUtil.fromNumberToMoneyString(value));
cell.setCellValue(richString);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
n++;
}
}
try {
workbook.write(bao);
InputStream is=new ByteArrayInputStream(bao.toByteArray());
ZipEntry ze=new ZipEntry(String.valueOf(java.util.UUID.randomUUID())+"_"+new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+".xlsx");
zos.putNextEntry(ze);
while ((len=is.read(buff))!=-1) {
zos.write(buff, 0, len);
}
zos.closeEntry();
zos.flush();
if(is!=null){
is.close();
}
if(bao!=null){
bao.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
try {
out.flush();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try{
if( zos!=null){
zos.close();
}
}catch(Exception e){
e.printStackTrace();
}
try{
if(out!=null){
out.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
}