POI导出Excel简单示例

柔光的暖阳◎ 2022-05-13 07:48 259阅读 0赞

简单示例，poi操作Excel实现中文列宽自适应。

maven项目，在pom.xml文件中引入poi的jar包依赖。

非maven项目需要先下载poi相关jar包导入。

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.16</version>
    </dependency>

组装数据接口。

package com.aliyun.xclouddesk.webapi.chart;
    
    import com.aliyun.xclouddesk.utils.ExcelUtils;
    import io.swagger.annotations.Api;
    import io.swagger.annotations.ApiOperation;
    import lombok.Getter;
    import lombok.Setter;
    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 org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.ResponseBody;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.OutputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * 报表导出
     * @author khWang
     * @date 2018/9/12
     */
    @Api(value = "ChartTypeLatTotalController", description = "chart_type_lat报表导出")
    @Controller
    @RequestMapping("/v1/tenants")
    public class ChartDownloadController {
    
        @Autowired
        private HttpServletResponse response;
    
        @RequestMapping(value = "/{tenantId}/charts/download/{channelCode}/", method = RequestMethod.GET)
        @ResponseBody
        @ApiOperation(value = "测试", produces = "application/octet-stream")
        public void chartDownloadServices() {
            //获取数据
            List<PageData> list = getData();
    
            //excel标题
            String[] title = {"名称", "性别", "年龄", "学校", "班级"};
    
            //excel文件名
            String fileName = "学生信息表" + System.currentTimeMillis() + ".xls";
    
            //sheet名
            String sheetName = "学生信息表";
            String[][] content = new String[list.size()][];
            for (int i = 0; i < list.size(); i++) {
                content[i] = new String[title.length];
                PageData obj = list.get(i);
                content[i][0] = obj.getStuName();
                content[i][1] = obj.getStuSex();
                content[i][2] = String.valueOf(obj.getStuAge());
                content[i][3] = obj.getStuSchoolName();
                content[i][4] = obj.getStuClassName();
            }
            //创建HSSFWorkbook
            HSSFWorkbook wb = ExcelUtils.getHSSFWorkbook(sheetName, title, content);
            //响应到客户端
            try {
                this.setResponseHeader(response, fileName);
                OutputStream os = response.getOutputStream();
                wb.write(os);
                os.flush();
                os.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    
        /**
         * 发送响应流方法
         *
         * @param response
         * @param fileName
         */
        public void setResponseHeader(HttpServletResponse response, String fileName) {
            try {
                fileName = new String(fileName.getBytes("utf-8"), "ISO-8859-1");
                response.setContentType("application/octet-stream;charset=ISO-8859-1");
                response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
                response.addHeader("Param", "no-cache");
                response.addHeader("Cache-Control", "no-cache");
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    
        @Getter
        @Setter
        class PageData {
            String stuName;
            String stuSex;
            int stuAge;
            String stuSchoolName;
            String stuClassName;
        }
    
        public List<PageData> getData() {
            List<PageData> list = new ArrayList<PageData>();
            PageData pageData;
            for (int i = 0; i < 10; i++) {
                pageData = new PageData();
                if (i == 4) {
                    pageData.setStuAge(999990 + i);
                    pageData.setStuClassName("班级班级" + i);
                    pageData.setStuName("姓名姓名姓名姓名姓名姓名姓名姓名姓名姓名姓名姓名姓名姓名姓名姓名姓名姓名姓名" + i);
                    pageData.setStuSchoolName("学校学校学校" + i);
                    pageData.setStuSex("男");
                } else {
                    pageData.setStuAge(i);
                    pageData.setStuClassName("班级" + i);
                    pageData.setStuName("姓名" + i);
                    pageData.setStuSchoolName("学校" + i);
                    pageData.setStuSex("男");
                }
                list.add(pageData);
            }
            return list;
        }
    }

POI操作Excel通用工具类

package com.aliyun.xclouddesk.utils;
    
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.BorderStyle;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    import org.apache.poi.ss.usermodel.VerticalAlignment;
    
    
    /**
     * POI操作Excel工具类
     * @author khWang
     * @date 2018/9/12
     */
    public class ExcelUtils {
    
        /**
         * Excel数据填充
         *
         * @param sheetName sheet名称
         * @param title     标题
         * @param values    内容
         * @return
         */
        public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values) {
            // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
            HSSFWorkbook workbook = new HSSFWorkbook();
    
            // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = workbook.createSheet(sheetName);
    
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
            HSSFRow row = sheet.createRow(0);
    
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle titleStyle = workbook.createCellStyle();
            // 设置自动换行
            titleStyle.setWrapText(true);
            // 创建一个居中格式
            titleStyle.setAlignment(HorizontalAlignment.CENTER);
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            titleStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
            titleStyle.setBorderBottom(BorderStyle.THIN);
            titleStyle.setBorderLeft(BorderStyle.THIN);
            titleStyle.setBorderRight(BorderStyle.THIN);
            titleStyle.setBorderTop(BorderStyle.THIN);
    
            // 创建字体样式
            HSSFFont headerFont = (HSSFFont) workbook.createFont();
            // 字体加粗
            headerFont.setBold(true);
            // 为标题样式设置字体样式
            titleStyle.setFont(headerFont);
    
            //声明列对象
            HSSFCell cell;
            //该数组存储每一列的最大宽度值
            int[] widthMax = new int[title.length];
            //创建标题
            for (int i = 0; i < title.length; i++) {
                cell = row.createCell(i);
                cell.setCellValue(title[i]);
                cell.setCellStyle(titleStyle);
                int width = title[i].getBytes().length * 256 + 256;
                widthMax[i] = width;
                sheet.setColumnWidth(i, width);
            }
    
            HSSFCellStyle contentStyle = workbook.createCellStyle();
            // 设置自动换行
            contentStyle.setWrapText(true);
            // 创建一个居中格式
            contentStyle.setAlignment(HorizontalAlignment.CENTER);
            contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 设置边框
            contentStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
            contentStyle.setBorderBottom(BorderStyle.THIN);
            contentStyle.setBorderLeft(BorderStyle.THIN);
            contentStyle.setBorderRight(BorderStyle.THIN);
            contentStyle.setBorderTop(BorderStyle.THIN);
    
            //创建内容
            HSSFCell contentCell;
            for (int i = 0; i < values.length; i++) {
                row = sheet.createRow(i + 1);
                for (int j = 0; j < values[i].length; j++) {
                    int width = values[i][j].getBytes().length * 256 + 256;
                    if (width > widthMax[j]) {
                        widthMax[j] = width;
                        sheet.setColumnWidth(j, width);
                    }
                    contentCell = row.createCell(j);
                    //将内容按顺序赋给对应的列对象
                    contentCell.setCellValue(values[i][j]);
                    contentCell.setCellStyle(contentStyle);
                }
            }
            return workbook;
        }
    }

默认字体的大小应该是10，每个字符占用空间在Excel中是256个单位，为了使布局看上去不那么紧凑，每个单元格多让出一个字符的空间。一个汉字占用两个字符，但实际视觉上，一个汉字所占用的空间和两个英文字符相比少那么一点点，所以当汉字长度很长的时候，会感觉单元格空白空间比较多。