1、jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8-beta5</version>
</dependency>
2、html代码
<input type="text" id="username"/>
<input type="button" id="export" value="导出"/>
3、js代码
<script type="text/javascript" src="/resources/js/jquery-1.7.min.js"></script>
<script>
jQuery(function () {
$("#export").click(function () {
var value = $("#username").val();
window.location.href="export?username"+value;
})
})
</script>
4、后台代码
(1)、Util层
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.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
/**
* Excel工具类
*/
public class PoiUtil {
/**
* 创建单元格
* @param row
* @param num
* @param value
*/
public static void createCell(Row row ,int num,int value,CellStyle style){
Cell cell = row.createCell(num);
cell.setCellValue(value);
cell.setCellStyle(style);
}
/**
* 创建单元格
* @param row
* @param num
* @param value
*/
public static void createCell(Row row ,int num,double value,CellStyle style){
Cell cell = row.createCell(num);
cell.setCellValue(value);
cell.setCellStyle(style);
}
/**
* 创建单元格
* @param row
* @param num
* @param value
*/
public static void createCell(Row row ,int num,String value,CellStyle style){
Cell cell = row.createCell(num);
cell.setCellValue(value);
cell.setCellStyle(style);
}
public static HSSFCellStyle createContentStyle(HSSFWorkbook wb){
//设置字体
HSSFFont font = wb.createFont();
font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 10);
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
HSSFCellStyle style = wb.createCellStyle();
style.setFillForegroundColor(HSSFColor.WHITE.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_LEFT);
style.setFont(font);
return style;
}
public static HSSFCellStyle createTitleStyle(HSSFWorkbook wb){
//设置字体
HSSFFont font = wb.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 10);
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
//生成一个样式
HSSFCellStyle style = wb.createCellStyle();
style.setFillForegroundColor(HSSFColor.WHITE.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);
style.setFont(font);
return style;
}
}
(2)、Controller层
package com.wenfeng.controller;
import java.net.URLDecoder;
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.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import com.wenfeng.service.PoiService;
import com.wenfeng.service.UserService;
@Controller
public class PoiController {
@Autowired
private UserService userservice; //用户
@Autowired
private PoiService<Object> poiService; //poi导出
@RequestMapping("/export")
public void exportPriceList(HttpServletRequest req, HttpServletResponse resp) throws Exception{
resp.setContentType("text/plain; charset=UTF-8");
Map<String,Object> queryMap = new HashMap<String,Object>();
String username = req.getParameter("username");
if(username != null && !"".equals(username)){
if(username.indexOf("%")!=-1){
username = URLDecoder.decode(username, "UTF-8");
}
queryMap.put("username", username);
}
List<Map<String, Object>> listMap = userservice.listuser(queryMap); //从数据库查询来的数据
try {
/**
* 生成excel
* 参数1:封装好的Map对象集合,对象中的字段类型不能有自定义类型的数据,否则无法解析生成excel
* 参数2:excel的标题行
* 参数3:Map对象中需要导出的字段
*/
HSSFWorkbook wb = poiService.exportMap(listMap,
new String[]{"ID", "名称", "密码", "email"},
new String[]{"userid", "username", "password", "useremail"});
resp.setHeader("Content-disposition", "attachment; filename="
+ java.net.URLEncoder.encode("文件导出", "UTF-8")
+ ".xls");
resp.setContentType("application/msexcel");
resp.setCharacterEncoding("UTF-8");
wb.write(resp.getOutputStream());
} catch (Exception e) {
e.printStackTrace();
}
}
}
(3)、dao层(查询用户数据的service层就省略了)
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
import com.wenfeng.bean.User;
public interface UserDao {
List<User> userlist() throws Exception;
/**
* 查询相关用户信息
* @param queryMap
* @return
* @throws Exception
*/
List<Map<String, Object>> listuser(@Param("params")Map<String, Object> queryMap) throws Exception;
}
(4)、mapper层
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wenfeng.dao.UserDao">
<sql id="Base_Column_List">
user_id as userid,user_name as username,user_password as password,user_email as useremail
</sql>
<select id="userlist" resultType="com.wenfeng.bean.User" >
SELECT <include refid="Base_Column_List"/> FROM t_user
</select>
<!-- 查询相关用户信息 -->
<select id="listuser" resultType="java.util.Map">
SELECT <include refid="Base_Column_List"/> FROM t_user WHERE 1=1
<if test="params.username != null and params.username != '' ">
AND user_name LIKE CONCAT('%', '${params.username}', '%')
</if>
ORDER BY user_id DESC
</select>
</mapper>
(5)、service层(导出)
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public interface PoiService<T> {
/**
* 生成excel
* @param list 封装好的Map对象集合,对象中的字段类型不能有自定义类型的数据,否则无法解析生成excel
* @param titles excel的标题行
* @param fields Map对象中需要导出的字段
* @return
* @throws Exception
*/
public HSSFWorkbook exportMap(List<Map<String, Object>> list, String[] titles, String[] fields) throws Exception;
}
import java.text.SimpleDateFormat; import java.util.List; import java.util.Map; import java.util.Set; import java.util.regex.Matcher; import java.util.regex.Pattern; 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 org.apache.poi.ss.usermodel.Cell; import org.springframework.stereotype.Service; import com.wenfeng.service.PoiService; import com.wenfeng.util.PoiUtil; @Service public class PoiServiceImpl implements PoiService<Object> { public HSSFWorkbook exportMap(List<Map<String, Object>> objs, String[] titles, String[] fields) throws Exception { //行号 int count = 0; //创建一个工作薄 HSSFWorkbook wb = new HSSFWorkbook(); //创建一个sheet HSSFSheet sheet = wb.createSheet(); //创建一行 HSSFRow row = sheet.createRow(count++); //这一行为第一行,即标题行 //生成一个样式 HSSFCellStyle style1 = PoiUtil.createContentStyle(wb); HSSFCellStyle style2 = PoiUtil.createTitleStyle(wb); HSSFCellStyle styleWrap = PoiUtil.createContentStyle(wb); styleWrap.setWrapText(true); SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd"); //为标题行生成单元格 for (int i = 0; i < titles.length; i++) { PoiUtil.createCell(row, i, ""+titles[i]+"", style2); } // 红色字体 HSSFFont fontRed = wb.createFont(); fontRed.setColor(HSSFColor.RED.index); //生成表格内容 / boolean hasFields = !(fields == null || fields.length == 0); // 是否指定导出字段 Map<String, Object> map = null; Set<Map.Entry<String, Object>> setEntry = null; for (int i = 0; i < objs.size(); i++) { map = objs.get(i); if (null == map) { continue; } row = sheet.createRow(count++); int col = 0; // 根据指定字段导出 if(hasFields) { for(String f : fields) { if(f != null && !"".equals(f)) { continue; } if(f.startsWith("<RichText>")) { // 字段名开头包含<RichText>时,按富文本处理 Object obj = map.get(f); HSSFRichTextString rts = new HSSFRichTextString(obj == null ? "" : String.valueOf(obj)); // *开头的每行文本字体用红色,主要针对质检单描述信息 Pattern pattern = Pattern.compile("\\*[^\r\n]+"); Matcher m = pattern.matcher(rts.getString()); while(m.find()) { rts.applyFont(m.start(), m.end(), fontRed); } Cell cell = row.createCell(col++); cell.setCellValue(rts); cell.setCellStyle(styleWrap); } else if(map.get(f) == null) { PoiUtil.createCell(row, col++, "", style1); } else if (map.get(f).getClass().getName().equals("java.util.Date")) { //java.util.Date类型数据 PoiUtil.createCell(row, col++, sdf.format(map.get(f)), style1); } else { PoiUtil.createCell(row, col++, String.valueOf(map.get(f)), style1); } } } else { // 导出所有字段 setEntry = map.entrySet(); for (Map.Entry<String, Object> e : setEntry) { if (null == e) { continue; } if(e.getValue() == null) { PoiUtil.createCell(row, col++, "", style1); } else if (e.getValue().getClass().getName().equals("java.util.Date")) { //java.util.Date类型数据 PoiUtil.createCell(row, col++, sdf.format(e.getValue()), style1); } else { PoiUtil.createCell(row, col++, String.valueOf(e.getValue()), style1); } } } objs.remove(i); i--; col = 0; } /给每一列的单元格的size自适应,根据内容自动调整宽度和高度,但是对于中文的单元格无效,这个比较好性能 for (int i = 0; i < titles.length; i++) { sheet.autoSizeColumn(i,true); //让宽度根据内容长度自适应,中文情况下自适应不成立 } for (int i = 0; i < titles.length; i++) { int colWidth = sheet.getColumnWidth(i) / 256; //得到单元格的实际宽度 for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum ++) { if (null != sheet.getRow(rowNum)) { int length = sheet.getRow(rowNum).getCell(i).toString().getBytes().length; //获取单元格内容的字节长度 if (colWidth < length) { //如果单元格的实际宽度小于单元格字节长度 colWidth = length; } } } sheet.setColumnWidth(i, 10*256); //重新设置单元格宽度 } return wb; } }