• Java_导出Excel

    导出的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
  • 相关阅读:
    .Net使用分布式缓存 C# 使用Redis
    微信申请退款API~~开发
    微信支付和支付宝支付分账接口文档
    Android xUtils3.0使用手册（一）- 基础功能使用
    支付宝支付开发——当面付条码支付和扫码支付
    vue开源项目汇总
    Azure和插件发布
    SqlServer数据库优化笔记
    企业微信通过PostMan获取accesstoken与管理员信息方法
    VisualStudio插件自动加载
  • 原文地址：https://www.cnblogs.com/gossip/p/5218029.html
Copyright © 2020-2023  润新知