导出的Excel标题、Sheet名称、数据内容都可以使用中文​


一、pom.xml引入jar包

1
2
3
4
5
<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.13</version>
        </dependency>

二、Excel操作内部类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
 
public class ExportInternalUtil {
    private XSSFWorkbook wb = null;
 
    private XSSFSheet sheet = null;
 
    /**
     * @param wb
     * @param sheet
     */
    public ExportInternalUtil(XSSFWorkbook wb, XSSFSheet sheet) {
        this.wb = wb;
        this.sheet = sheet;
    }
 
    /**
     * 合并单元格后给合并后的单元格加边框
     *
     * @param region
     * @param cs
     */
    public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs) {
 
        int toprowNum = region.getFirstRow();
        for (int i = toprowNum; i <= region.getLastRow(); i++) {
            XSSFRow row = sheet.getRow(i);
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                XSSFCell cell = row.getCell(j);// XSSFCellUtil.getCell(row,
                                                // (short) j);
                cell.setCellStyle(cs);
            }
        }
    }
 
    /**
     * 设置表头的单元格样式
     *
     * @return
     */
    public XSSFCellStyle getHeadStyle() {
        // 创建单元格样式
        XSSFCellStyle cellStyle = wb.createCellStyle();
        // 设置单元格的背景颜色为淡蓝色
        cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
        cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        // 设置单元格居中对齐
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 设置单元格垂直居中对齐
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        // 创建单元格内容显示不下时自动换行
        cellStyle.setWrapText(true);
        // 设置单元格字体样式
        XSSFFont font = wb.createFont();
        // 设置字体加粗
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName("宋体");
        font.setFontHeight((short) 200);
        cellStyle.setFont(font);
        // 设置单元格边框为细线条
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        return cellStyle;
    }
 
    /**
     * 设置表体的单元格样式
     *
     * @return
     */
    public XSSFCellStyle getBodyStyle() {
        // 创建单元格样式
        XSSFCellStyle cellStyle = wb.createCellStyle();
        // 设置单元格居中对齐
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 设置单元格垂直居中对齐
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        // 创建单元格内容显示不下时自动换行
        cellStyle.setWrapText(true);
        // 设置单元格字体样式
        XSSFFont font = wb.createFont();
        // 设置字体加粗
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName("宋体");
        font.setFontHeight((short) 200);
        cellStyle.setFont(font);
        // 设置单元格边框为细线条
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        return cellStyle;
    }
}

三、Excel操作类

共外部调用，可设置Sheet名称、标题、数据等

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
import java.io.IOException;
import java.util.ArrayList;
 
import javax.servlet.ServletOutputStream;
 
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
import com.james.domain.User;
 
public class ExportUtil {
 
    public static void ExportExcel(String[] titles, ArrayList<User> list, ServletOutputStream outputStream) {
        // 创建一个workbook 对应一个excel应用文件
        XSSFWorkbook workBook = new XSSFWorkbook();
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        //Sheet名称，可以自定义中文名称
        XSSFSheet sheet = workBook.createSheet("Sheet1");
        ExportInternalUtil exportUtil = new ExportInternalUtil(workBook, sheet);
        XSSFCellStyle headStyle = exportUtil.getHeadStyle();
        XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();
        // 构建表头
        XSSFRow headRow = sheet.createRow(0);
        XSSFCell cell = null;
 
        // 输出标题
        for (int i = 0; i < titles.length; i++) {
            cell = headRow.createCell(i);
            cell.setCellStyle(headStyle);
            cell.setCellValue(titles[i]);
        }
        // 构建表体数据
        for (int j = 0; j < list.size(); j++) {
            XSSFRow bodyRow = sheet.createRow(j + 1);
            User user = list.get(j);
 
            cell = bodyRow.createCell(0);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(user.getLastIp());
 
            cell = bodyRow.createCell(1);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(user.getLastVisit());
 
            cell = bodyRow.createCell(2);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(user.getPassword());
             
            cell = bodyRow.createCell(3);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(user.getUserName());
             
            cell = bodyRow.createCell(4);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(user.getUserId());
        }
 
        try {
            workBook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

四、SpringMVC Controller层调用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
@RequestMapping(value = "/excelDownload")
    public String exportExcel(HttpServletResponse response) {
        try {
            //String fileName = new String(("导出excel标题").getBytes(), "UTF-8") + ".xlsx";
            String fileName=new String(("导出excel标题").getBytes("gb2312"), "iso8859-1")+ ".xlsx";
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setCharacterEncoding("utf-8");
 
            // response.setHeader("Content-disposition", "attachment; filename="
            // + "exdddcel" + ".xlsx");// 组装附件名称和格式
 
            String[] titles = { "最后IP", "最后访问时间", "密码", "用户名", "用户编号" };
 
            /*SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String date = df.format(new Date());
            Date dateNow = null;
            try {
                dateNow = df.parse(date);
            } catch (ParseException e) {
                e.printStackTrace();
            }*/
            Date dateNow = new Date();
             
            ArrayList<User> users = new ArrayList<User>();
            User user = new User();
            user.setLastIp("127.0.0.1");
            user.setLastVisit(dateNow);
            user.setPassword("123");
            user.setUserId(1);
            user.setUserName("名称：James");
            users.add(user);
 
            user = new User();
            user.setLastIp("192.0.0.1");
            user.setLastVisit(dateNow);
            user.setPassword("456");
            user.setUserId(2);
            user.setUserName("名称：Mary");
            users.add(user);
 
            ServletOutputStream outputStream = response.getOutputStream();
            ExportUtil.ExportExcel(titles, users, outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

五、程序中用到的实体类User

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
import java.io.Serializable;
import java.util.Date;
 
public class User implements Serializable {
    private int userId;
 
    private String userName;
 
    private String password;
 
    private String lastIp;
 
    private Date lastVisit;
 
    public int getUserId() {
        return userId;
    }
 
    public void setUserId(int userId) {
        this.userId = userId;
    }
 
    public String getUserName() {
        return userName;
    }
 
    public void setUserName(String userName) {
        this.userName = userName;
    }
 
    public String getPassword() {
        return password;
    }
 
    public void setPassword(String password) {
        this.password = password;
    }
 
    public String getLastIp() {
        return lastIp;
    }
 
    public void setLastIp(String lastIp) {
        this.lastIp = lastIp;
    }
 
    public Date getLastVisit() {
        return lastVisit;
    }
 
    public void setLastVisit(Date lastVisit) {
        this.lastVisit = lastVisit;
    }
}

六、异常情况

1、标题乱码（注释代码会出现标题乱码）

1
2
//String fileName = new String(("导出excel标题").getBytes(), "UTF-8") + ".xlsx";
            String fileName=new String(("导出excel标题").getBytes("gb2312"), "iso8859-1")+ ".xlsx";

七、参考文档

1、http://webook-java.iteye.com/blog/1699621

2、http://www.cnblogs.com/yjmyzz/p/4206463.html


最新文章

  1. iOS coredata 数据库的加密（待研究）
  2. 今天遇到了批量删除 redis 某个前缀的所有 key，发现只能是这么解决。
  3. 【html5】Web存储_locaStorage对象的应用
  4. SQL语句创建表和数据库
  5. awk 查找文件长度 删除
  6. 【HDOJ】3416 Marriage Match IV
  7. iframe详细用法
  8. Go程序GC优化经验分享
  9. Michael Kors - Wikipedia, the free encyclopedia
  10. Apache 2.4虚拟主机配置
  11. 【单例模式】java实现
  12. EfRepository
  13. webview之如何设计一个优雅健壮的Android WebView？（上）(转)
  14. 合作开发工具——freeze和pipreqs
  15. 笨方法学python之读写文件、open函数的用法
  16. JavaWeb之tomcat安装、配置与使用(一)
  17. shell 截取变量的字符串（转）
  18. iOS开发证书与配置文件的使用
  19. 接口测试Case之面向页面对象编写及规范
  20. Report_Report Builder的一些基本概念（概念）

热门文章

  1. C高级 框架开发中红黑树结构
  2. 怎么删除Windows服务
  3. nginx+php7+mysql 在centos7.3下安装
  4. mac pro 安装mysql并且配置my.cnf(添加默认字符集utf8，数据存放路径，修改已经建好的表的默认字符集等)、mac mysql my.cnf路径
  5. 解决Myeclipse编译不生成.class文件问题
  6. SQL 分页通用存储过程
  7. 封装CSS动画
  8. 网站页面SEO的三个标签怎么写有利【转载】
  9. Flexigrid-Web2.0 jQuery
  10. python模块之HTMLParser