本文用的的框架是springMVC,当然不使用springmvc也可以使用。
用到的插件是apache.poi,一下是自己测试的一个例子
由于自己需要,所以编写的方法传入的是List<Map<String,Object>>类型参数,读者可根据自己需要编写不同的方法
package com.springmvc.handler;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.stereotype.Controller;
import org.springframework.web.servlet.View;
@Controller
public class BuildExcelHander implements View{
@Override
public String getContentType() {
return "application/vnd.ms-excel;charset=" + System.getProperty("file.encoding");
}
@Override
public void render(Map<String, ?> model, HttpServletRequest request, HttpServletResponse response)
throws Exception {
String encoding = System.getProperty("file.encoding");
response.setContentType("application/vnd.ms-excel;charset=" + encoding);
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(new Date().getTime() + ".xls",encoding));
String[] headers = {"ID","姓名","性别","邮箱"};
List<Map<String,Object>> datas = new ArrayList<>();
Map<String, Object> map = new LinkedHashMap<>();
map.put("id", 23);
map.put("name", "lqf");
map.put("sex", "男");
map.put("email", "liqingfeng@csii.com.cn");
datas.add(map);
String pattern = "yyyy-MM-dd:hh-mm-ss";
exportExcel(headers, datas, response.getOutputStream(), pattern);
}
public void exportExcel(String[] headers, List<Map<String,Object>> datas, OutputStream os, String pattern) throws IOException {
HSSFWorkbook workBook = new HSSFWorkbook();
Sheet sheet = workBook.createSheet();
//设置表格属性
Row row = setSheetProperty(sheet);
//设置表格头
setSheetHeader(headers, workBook, row);
//为表格体赋值
setSheetBody(datas, sheet);
workBook.write(os);
}
/**
* 设置表格头
* @param headers
* @param workBook
* @param row
*/
private void setSheetHeader(String[] headers, HSSFWorkbook workBook, Row row) {
//设置表格头样式
HSSFCellStyle headerStyle = getHeaderStyle(workBook);
Cell cell ;
for(int i = 0; headers.length > i; i++) {
cell = row.createCell(i);
cell.setCellValue(headers[i]);
cell.setCellStyle(headerStyle);
}
}
/**
* 设置Sheet属性
* @param sheet
* @return
*/
private Row setSheetProperty(Sheet sheet) {
sheet.setColumnWidth(3, 25 * 256);
Row row = sheet.createRow(0);
return row;
}
/**
* 设置表格中的数据
* @param datas
* @param sheet
*/
private void setSheetBody(List<Map<String, Object>> datas, Sheet sheet) {
Row row;
Cell cell;
for (int i = 0; datas.size() > i; i++) {
row = sheet.createRow(i + 1);
int j = 0;
for (Map.Entry<String, Object> set : datas.get(i).entrySet()) {
cell = row.createCell(j);
Object obj = set.getValue();
setCellValue(cell, obj);
j++;
}
}
}
/**
* 设置表格头样式
* @param workBook
* @return
*/
private HSSFCellStyle getHeaderStyle(HSSFWorkbook workBook) {
HSSFCellStyle headerStyle = workBook.createCellStyle();
HSSFFont headerFont = workBook.createFont();
headerFont.setFontHeight((short) (15 * 20));
headerFont.setFontName("黑体");
headerStyle.setFont(headerFont);
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
return headerStyle;
}
/**
* 在表格中添加数据
* @param cell
* @param obj
*/
private void setCellValue(Cell cell, Object obj) {
if (obj instanceof Number) {
if (obj instanceof Integer) {
cell.setCellValue(((Integer) obj).doubleValue());
} else if (obj instanceof BigDecimal) {
cell.setCellValue(((BigDecimal) obj).doubleValue());
} else if (obj instanceof Long) {
cell.setCellValue(((Long) obj).doubleValue());
} else if (obj instanceof Double) {
cell.setCellValue((double) obj);
} else if (obj instanceof Short) {
cell.setCellValue(((Short) obj).doubleValue());
}
} else if (obj instanceof Date) {
cell.setCellValue((Date) obj);
} else {
cell.setCellValue(obj.toString());
}
}
}
以下是在springmvc配置文件对改类的配置,若果没有使用springmvc或没有又自定义视图则不需要下面的配置,若没有使用@RequestMapping对方法进行注释的方法,二十使用了实现View接口的方法实现则需要在配置文件中对自定义视图进行配置,让框架能处理。
<mvc:annotation-driven></mvc:annotation-driven>
<!-- 其中的order属性必须配置,否则会从 InternalResourceViewResolver的视图解析器中解析视图,InternalResourceViewResolver的order为Integer的最大值-->
<bean class="org.springframework.web.servlet.view.BeanNameViewResolver">
<property name="order" value="100"></property>
</bean>