Excel 导出功能

工具类：


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import javax.servlet.http.HttpServletRequest;
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.HSSFFont;
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.CellRangeAddress;


/**
 * excel导入导入工具类
 *  
 * 
 */
public class ExcelUtil {
/**
* 导出excel
* 
* @param request
* @param response
* @param title
*            标题
* @param headers
*            excel表头名称数组
* @param dataList
*            数据
*/
public static void createExcel(HttpServletRequest request,
HttpServletResponse response, String title, String[] headers,
List<String[]> dataList) {
// create a new workbook
HSSFWorkbook wb = new HSSFWorkbook();
// create a new sheet
HSSFSheet s = wb.createSheet(title);


// font
HSSFFont titleFont = wb.createFont();
titleFont.setFontHeightInPoints((short) 16);
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleFont.setItalic(false);


HSSFFont headFont = wb.createFont();
headFont.setFontHeightInPoints((short) 12);
headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// headFont.setColor(HSSFFont.COLOR_RED);


HSSFFont bodyFont = wb.createFont();
bodyFont.setFontHeightInPoints((short) 10);


// CellStyle
HSSFCellStyle csTitle = null;
try {
csTitle = wb.createCellStyle();
} catch (Exception e) {
e.printStackTrace();
}
csTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
csTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
csTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);
csTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
csTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);
csTitle.setFont(titleFont);


HSSFCellStyle csHeader = wb.createCellStyle();
csHeader.setFont(headFont);
csHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER);
csHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN);
csHeader.setBorderTop(HSSFCellStyle.BORDER_THIN);
csHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN);
csHeader.setBorderRight(HSSFCellStyle.BORDER_THIN);
// csHeader.setFillBackgroundColor(HSSFColor.YELLOW.index);
// csHeader.setFillForegroundColor(HSSFColor.WHITE.index);
// csHeader.setFillPattern(HSSFCellStyle.ALIGN_CENTER);
csHeader.setLocked(false);


// 列数
int columnNum = headers.length;
short rowNum = 0;
// 添加导出excel标题
HSSFRow row = s.createRow(rowNum++);
row.setHeight((short) 500);
HSSFCell cell = row.createCell(0);
s.addMergedRegion(new CellRangeAddress(0, 0, 0, (columnNum - 1)));
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(title);
cell.setCellStyle(csTitle);
for (int i = 1; i < columnNum; i++) {
cell = row.createCell(i);
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);


}
// create head
row = s.createRow(rowNum++);
short shW = 0;
row.setHeight((short) 300);
for (int i = 0; i < columnNum; i++) {
shW = (short) (4500);
s.setColumnWidth(i, shW);


cell = row.createCell(i);
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(csHeader);
cell.setCellValue(headers[i]);
}
// create body


for (int j = 0; j < dataList.size(); j++) {
String[] cellValues = dataList.get(j);
row = s.createRow(rowNum++);
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(cellValues[i]);
}
}


// 下载文件
String fileName = title + ".xls";
boolean isIE = false;// 检查是否是ie浏览器，；
String agent = request.getHeader("USER-AGENT");
if (null != agent && agent.indexOf("MSIE") != -1) {
isIE = true;
}
try {
if (isIE) {// 如果是ie浏览器，就用这种方式编码文件名；
fileName = URLEncoder.encode(fileName, "UTF-8");
} else {
fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
}
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setContentType("application/x-msdownload");
response.addHeader("Content-Disposition", "attachment;filename="
+ fileName);
try {
OutputStream out2 = response.getOutputStream();
wb.write(out2);
out2.flush();
out2.close();
} catch (IOException e) {
e.printStackTrace();
}
}


/**
* 读取excel
* 
* @param path
*            文件路径
* @return map<Integer, List<String>>
*/
public static Map readExcel(String path) {
File excel = new File(path);
InputStream is = null;
try {
is = new FileInputStream(excel);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Map<Integer, List<String>> content = new HashMap<Integer, List<String>>();


// POIFSFileSystem fs = new POIFSFileSystem(is);
HSSFWorkbook wb = null;
try {
wb = new HSSFWorkbook(is);
// is.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {


try {
is.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
HSSFSheet sheet = wb.getSheetAt(0);


int allRow = sheet.getLastRowNum();
// HSSFRow row = sheet.getRow(0);
for (int i = 0; i <= allRow; i++) {
HSSFRow row = sheet.getRow(i);
int allCell = 0;
List<String> list = new ArrayList<String>();
if (row != null) {
allCell = row.getLastCellNum();
// List<String> list = new ArrayList<String>();
for (int j = 0; j < allCell; j++) {
HSSFCell cell = row.getCell(j);
if (cell == null) {
list.add("");
} else {
list.add(cell.getStringCellValue());
}
}


}
content.put(i, list);
}


return content;
}

public static void main(String[] args) {
    Map<Integer, List<String>> content = ExcelUtil.readExcel("d:/详情表.xls");
    System.out.println(content.size());
    for (int i = 0; i < content.size(); i++) {
    List<String> list = content.get(i);
    System.out.println(i);
    if (list != null) {
    for (int j = 0; j < list.size(); j++) {
    System.out.print(list.get(j) + "\t\t");
    }
    System.out.println();
    }
   
   }
}  

}

页面代码：

<input id="customer_excel" type="button" value="导出Excel" οnclick="channelExcel();" />

<script type="text/javascript">

function channelExcel(){
var name = document.getElementById("channelMtName").value;
var begin = document.getElementById("channelMtBegin").value;
var end = document.getElementById("channelMtEnd").value;
window.location.href='channelController.do?channelExcel&name='+name + "&begin=" + begin + "&end=" + end;
}

</script>

后台：

@RequestMapping(params="channelExcel")
public void channelExcel(HttpServletRequest request,
HttpServletResponse response,DataGrid dataGrid) throws UnsupportedEncodingException, ParseException{
String title="渠道管理列表";
String [] headers = {"渠道名称","推广链接","投放时间","结束日期","点击数","注册数","会员转化率","投资人数","投资转化率","投资金额"};

String begin = request.getParameter("begin");
String end = request.getParameter("end");
String name = request.getParameter("name");
int pageNum=dataGrid.getPage();
int pageSize=dataGrid.getRows();
Map<String,Object> map = new HashMap<String, Object>();
map.put("begin", begin);
map.put("end", end);
map.put("name", name);
map.put("pageNum", pageNum-1);
map.put("pageSize", pageSize);
List<PChannelsManagerModel> list = channelService.findCannelDataForTable(map);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
DecimalFormat df = new DecimalFormat("#.00");
List<String[]> result=new ArrayList<String[]>();
for(PChannelsManagerModel m:list){
String [] tempstr=new String[10];
tempstr[0]=m.getName();
tempstr[1]=m.getLink();
tempstr[2]=sdf.format(m.getPuttime());
tempstr[3]=sdf.format(m.getPlantime());
tempstr[4]=""+m.getCkicklNum();
tempstr[5]=""+m.getRegNum();
tempstr[6]=df.format(m.getRegRate())+"%";
tempstr[7]=""+m.getBuyNum();
tempstr[8]=df.format(m.getBuyRate())+"%";
tempstr[9]=""+m.getBuySum();
result.add(tempstr);
}
ExcelUtil.createExcel(request, response, title, headers, result);
}
}