首先看看我本地工程结构
所需要的jar 包 https://pan.baidu.com/s/1jTyot3Gj08HZUP46ouKc8w
网盘提取码:jf36
1. 前台jsp 使用 js 获取表格的数据,然后 循环取得每一个行的数据 构造成 json 格式,方便分辨每个数据 属于每一行的那个字段
<table border="1" id="tab">
<thead>
<tr>
<th>姓名</th>
<th>年龄</th>
<th>性别</th>
</tr>
</thead>
<tbody>
<tr>
<td><input type="text" name="name" value="张三"></td>
<td><input type="text" name="age" value="20"></td>
<td><input type="text" name="sex" value="男"></td>
</tr>
<tr>
<td><input type="text" name="name" value="李四"></td>
<td><input type="text" name="age" value="21"></td>
<td><input type="text" name="sex" value="女"></td>
</tr>
<tr>
<td><input type="text" name="name" value="王二"></td>
<td><input type="text" name="age" value="22"></td>
<td><input type="text" name="sex" value="男"></td>
</tr>
<tr>
<td><input type="text" name="name" value="赵六"></td>
<td><input type="text" name="age" value="23"></td>
<td><input type="text" name="sex" value="男"></td>
</tr>
</tbody>
</table>
<input id="button1" type = "button" value = "点击下载" onClick="a()" />
<script>
function a(){
var tr = $("#tab tr"); // 获取table中每一行内容
var result = []; // 数组
for (var i = 0; i < tr.length; i++) {// 遍历表格中每一行的内容
var tds = $(tr[i]).find("td");
if (tds.length > 0) {
result.push({
"name" : $(tds[0]).find("input").val(),
"age" : $(tds[1]).find("input").val(),
"sex" : $(tds[2]).find("input").val()
})
}
}
console.log(encodeURI(JSON.stringify(result)));
location.href = 'servlet/ExcelExportServlet?result='+JSON.stringify(result);
}
</script>
以上 js 代码需要注意的就是,本来想使用 ajxa 来请求servlet ,但是后台才知道 ajax 不支持文件传输,因为它无法接收 文件流的形式
2. web.xml 的配置(这个就是最基础的映射了)
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://java.sun.com/xml/ns/javaee"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
<display-name></display-name>
<servlet>
<description>This is the description of my J2EE component</description>
<display-name>This is the display name of my J2EE component</display-name>
<servlet-name>ExcelExportServlet</servlet-name>
<servlet-class>onepage.ExcelExportServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>ExcelExportServlet</servlet-name>
<url-pattern>/servlet/ExcelExportServlet</url-pattern>
</servlet-mapping>
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
</web-app>
3.servlet 也就是我们的Controller
package onepage;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLDecoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
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.HSSFRichTextString;
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;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
public class ExcelExportServlet extends HttpServlet {
//访问网址:http://localhost:8089/exportTest/servlet/ExcelExportServlet
//http://localhost:8089/exportTest/index.jsp
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//获取前台传过来的 Json 字符串
String strdata = new String(request.getParameter("result").getBytes("ISO-8859-1"), "UTF-8");
System.out.println(strdata);
//System.out.println(listm);
response.setContentType("octets/stream");
// response.addHeader("Content-Disposition", "attachment;filename=test.xls");
String excelName = "资源导出";
//转码防止乱码
response.addHeader("Content-Disposition", "attachment;filename="+new String( excelName.getBytes("gb2312"), "ISO8859-1" )+".xls");
String[] headers = new String[]{"姓名","年龄","性别"};
try {
OutputStream out = response.getOutputStream();
exportExcel(excelName,headers, getList(strdata), out,"yyyy-MM-dd");
out.close();
System.out.println("excel导出成功!");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
/**
*
* @Description: 组成List<Map> 形式,给每一个单元格一个标题
* @Auther: lujinyong
* @Date: 2013-8-22 下午2:53:58
*/
//public List<Map<String,Object>> getList(List<Map<String,Object>> list){
public List<Map<String,Object>> getList(String str){
System.out.println(str);
List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
List<Map<String,Object>> listObjectFir = (List<Map<String,Object>>) JSONArray.parse(str);
System.out.println("利用JSONArray中的parse方法来解析json数组字符串");
for(Map<String,Object> mapList : listObjectFir){
Map<String,Object> map = new HashMap<String, Object>();
for (Map.Entry entry : mapList.entrySet()){
//每一行都放在一个单独的 map 里面
map.put((String) entry.getKey(), entry.getValue());
//System.out.println( entry.getKey() + " " +entry.getValue());
}
list.add(map);
}
return list;
}
/**
*
* @Description: 生成excel并导出到客户端(本地)
* @Auther: lujinyong
* @Date: 2013-8-22 下午3:05:49
*/
protected void exportExcel(String title,String[] headers,List mapList,OutputStream out,String pattern){
//声明一个工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
//设置表格默认列宽度为15个字符
sheet.setDefaultColumnWidth(20);
//生成一个样式,用来设置标题样式
HSSFCellStyle style = workbook.createCellStyle();
//设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//把字体应用到当前的样式
style.setFont(font);
// 生成并设置另一个样式,用于设置内容样式
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
HSSFFont font2 = workbook.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style2.setFont(font2);
//产生表格标题行
HSSFRow row = sheet.createRow(0);
for(int i = 0; i<headers.length;i++){
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
for (int i=0;i<mapList.size();i++) {
Map<String,Object> map = (Map<String, Object>) mapList.get(i);
row = sheet.createRow(i+1);
int j = 0;
Object value = null;
value=map.get("name");
if(value instanceof String){
row.createCell(j++).setCellValue(String.valueOf(value));
}
value=map.get("age");
if(value instanceof String){
row.createCell(j++).setCellValue(String.valueOf(value));
}
value=map.get("sex");
if(value instanceof String){
row.createCell(j++).setCellValue(String.valueOf(value));
}
}
try {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
}
}
}
参考原文:https://blog.csdn.net/wilsonke/article/details/37960261