所需jar包 poi
工具类
import java.io.IOException;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
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;
public class ExcelTool {
/**
* *
*
* @param filename 保存到客户端的文件名 例:用户.xls
* @param title 标题行 例:String[]{"名称","地址"}
* @param key 从查询结果List取得的MAP的KEY顺序,
* 需要和title顺序匹配, 例:String[]{"name","address"}
* @param values 结果集
* @param httpServletResponse
* @throws IOException
*/
public static void createN(String filename, String[] title, String[] key, List<Map> values, HttpServletResponse httpServletResponse) throws IOException {
String filename2 = new String(filename.getBytes(), "iso-8859-1");
ServletOutputStream servletOutputStream = httpServletResponse.getOutputStream();
HSSFWorkbook workbook = null;
httpServletResponse.setHeader("Content-disposition", "attachment; filename=" + filename2);
httpServletResponse.setContentType("application/x-download");
workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
HSSFRow row = null;
HSSFCell cell = null;
row = sheet.createRow((short) 0);
for (int i = 0; title != null && i < title.length; i++) {
cell = row.createCell((short) i);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(new HSSFRichTextString(title[i]));
}
Map map = null;
for (int i = 0; values != null && i < values.size(); i++) {
row = sheet.createRow((short) (i + 1));
map = values.get(i);
for (int i2 = 0; i2 < key.length; i2++) {
cell = row.createCell((short) (i2));
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
if (map.get(key[i2]) == null) {
cell.setCellValue(new HSSFRichTextString(""));
} else {
cell.setCellValue(new HSSFRichTextString(map.get(key[i2]).toString()));
}
}
}
workbook.write(servletOutputStream);
servletOutputStream.flush();
servletOutputStream.close();
}
}
应用实例(spring mvc)
在Controller中
@ResponseBody
@RequestMapping(value = "excel")
public void excel(HttpServletRequest request, HttpServletResponse response) {
String filename = "用户列表.xls";
List<User> list = userService.getList(null, null);
String[] title = { "用户名", "条形码", "创建时间" };
String[] key = { "userPhoneCode", "userBarCode", "createTime" };
Map<String, String> map;
@SuppressWarnings("rawtypes")
List<Map> values = new LinkedList<Map>();
for (User user : list) {
map = new TreeMap<String, String>();
map.put("userPhoneCode", user.getUserPhoneCode());
map.put("userBarCode", user.getUserBarCode());
map.put("createTime", user.getCreateTime());
values.add(map);
}
try {
ExcelTool.createN(filename, title, key, values, response);
} catch (IOException e) {
}
}