注意:从浏览器下载Execl表,不能用Ajax去请求,可以用表单或者超链接。
工具类:
package com.util;
import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.hssf.util.HSSFColor;
/**
* 生成Excel、导出Excel的工具类
* @author Administrator
*
*/
public class ExcelUtil
{
public static void main(String[] args)
{
//设置表头
//表头的key要和传入的list<map>的key一致
// LinkedHashMap<String,String> map = new LinkedHashMap<String, String>();
// map.put("sCheckID","编号");
// map.put("shipCname","船名");
// map.put("sCheckOrganization","检查机构");
// map.put("sCheckPeople","检查人");
// map.put("sState","船舶状态");
// map.put("rebuildCheck","船检/复检");
// map.put("sTableState","表的状态");
// map.put("sCheckDate","检查日期");
//
//
//
// JSONObject jsonObject = new JSONObject(true);
// jsonObject = JSONObject.parseObject(result);
// List<Map<String,Object>> linkConf=(List<Map<String,Object>>) jsonObject.get("list");
// HSSFWorkbook hw=ExcelUtil.mapToExcel(map, linkConf,"ceshi123");
//
// System.out.println(hw==null);
// Date date =new Date(System.currentTimeMillis());
// String fileName="国旗检查表"+date.toString();
//
// HttpServletResponse response = ServletActionContext.getResponse();
// ExcelUtil.exportExcel(hw, response, fileName);
// System.out.println("导出成功");
}
/**
* List<Map>生成excel表
* @param head (头部的key必须对应map的key)
* @param contents
* @return 返回保存的地址
*/
public static HSSFWorkbook mapToExcel(LinkedHashMap<String,String> head, List<Map<String,Object>> contents,String sheetName) {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(sheetName);
//设置表格样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
for(int i=0;i<head.size();i++)
{
sheet.setColumnWidth(i, 4000);
}
//生成内容
for (int i = 0;i< contents.size()+1;i++){//excel的行数
HSSFRow row = sheet.createRow(i);
int j = 0;//列
for (Map.Entry<String,String> property : head.entrySet()) {
HSSFCell cell = row.createCell(j++);
cell.setCellStyle(cellStyle);
if (i == 0) {//表头信息
cell.setCellValue(property.getValue());
}else{
cell.setCellValue(contents.get(i - 1).get(property.getKey()) == null ? null :contents.get(i - 1).get(property.getKey()).toString());
}
}
}
// String folder=System.getProperty("java.io.tmpdir");
//System.out.println("AAAAAAA");
//String filePath = "H:"+ "/" + fileName;
return workbook;
}
/**
* bean生成excel表
* @param head (头部的key必须对应bean的属性名)
* @param contents
* @return 返回保存的地址
*/
public static <T> HSSFWorkbook beanToExcel(LinkedHashMap<String,String> head, List<T> contents,String sheetName,String fileName) throws Exception {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(sheetName);
Class clazz = contents.get(0).getClass();
//生成内容
for (int i = 0;i< contents.size()+1;i++){//excel的行数
HSSFRow row = sheet.createRow(i);
int j = 0;//列
for (Map.Entry<String,String> property : head.entrySet()) {
HSSFCell cell = row.createCell(j++);
if (i == 0) {//表头信息
cell.setCellValue(property.getValue());
}else{
java.lang.reflect.Field field = clazz.getDeclaredField(property.getKey());
field.setAccessible(true);
Object result = field.get(contents.get(i - 1));
cell.setCellValue(result == null ? null :result.toString());
}
}
}
return workbook;
}
/**
* excel导出本地
* @param workbook(excel表)
* @param filePath(文件导出路径)
* @return
*/
private static String writeFile(HSSFWorkbook workbook, String filePath) {
FileOutputStream stream = null;
BufferedOutputStream outputStream = null;
try {
stream = new FileOutputStream(filePath);
outputStream = new BufferedOutputStream(stream);
workbook.write(outputStream);
return filePath;
} catch (IOException e) {
e.printStackTrace();
}finally {
if (outputStream != null){
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (stream != null){
try {
stream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return null;
}
/**
* 生成excel表通过浏览器下载到客户端
* @param workbook(生成的excel)
* @param response(HttpServletResponse对象)
* @param fileName(文件名)
*/
public static String exportExcel(HSSFWorkbook workbook, HttpServletResponse response,String fileName) throws IOException
{
try{
response.reset();//清空缓存
OutputStream out = response.getOutputStream();//获取输出流
response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes(),"iso-8859-1") + ".xls");
response.setContentType("application/msexcel");
workbook.write(out);//excel文件写入
out.flush();//刷新缓存
out.close();
return "sucess";
}
catch(Exception e)
{
return "defail";
}
}
}