分别使用POI和JXL导出数据到Excel

原创

少说点话 2021-08-04 15:36:49 博主文章分类：JAVA ©著作权

文章标签 java apache spring xml 测试人员 文章分类 Java 后端开发

©著作权归作者所有：来自51CTO博客作者少说点话的原创作品，请联系作者获取转载授权，否则将追究法律责任

1.使用POI

引入jar包

<!-- poi HSSF is our port of the Microsoft Excel 97(-2007) file format (BIFF8) to pure Java. -->
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.17</version>
</dependency>
<!-- poi-ooxml XSSF is our port of the Microsoft Excel XML (2007+) file format (OOXML) to pure Java -->
      <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml</artifactId>
          <version>3.17</version>
      </dependency>
      <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml-schemas</artifactId>
          <version>3.17</version>
      </dependency>

ExcelExport.java

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Sue
 * @create 2019-04-29 14:38
 **/
@Controller
public class ExcelExport {

    @RequestMapping(value = "/execute", method = RequestMethod.GET)
    public void execute(HttpServletRequest request, HttpServletResponse response) throws IOException {

        //待导出的数据
        List<ReportInfo> reportInfoList = new ArrayList<>();
        reportInfoList.add(new ReportInfo("上海卡部", "this week 01", "next week 01"));
        reportInfoList.add(new ReportInfo("广州卡部", "this week 05", "next week 05"));
        reportInfoList.add(new ReportInfo("厦门卡部", "this week 05", "next week 05"));
        reportInfoList.add(new ReportInfo("宁波卡部", "this week 05", "next week 05"));
        reportInfoList.add(new ReportInfo("武汉卡部", "this week 05", "next week 05"));

        // 导出.xlsx文件使用这个对象
        // XSSFWorkbook work = null;

        HSSFWorkbook workbook = null;
        try {
            //创建一个空白的workbook
            workbook = new HSSFWorkbook();
            //建立新的sheet对象（excel的表单）
            HSSFSheet sheet = workbook.createSheet("周报信息表");
            //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow row1 = sheet.createRow(0);
            //创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
            HSSFCell cell = row1.createCell(0);

            //创建样式
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            //水平居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            //垂直居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置单元格样式
            cell.setCellStyle(cellStyle);
            //设置单元格内容
            cell.setCellValue("周报信息一览表");
            //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
            //在sheet里创建第二行
            HSSFRow row2 = sheet.createRow(1);
            //创建单元格并设置单元格内容
            row2.createCell(0).setCellValue("业务条线");
            row2.createCell(1).setCellValue("本周工作成果");
            row2.createCell(2).setCellValue("下周工作计划");
            //在sheet里创建第三行
            HSSFCellStyle cellStyle1 = workbook.createCellStyle();
            //自动换行
            cellStyle1.setWrapText(true);
            for (int i = 0; i < reportInfoList.size(); i++) {
                HSSFRow row = sheet.createRow(2 + i);

                HSSFCell cell0 = row.createCell(0);
                cell0.setCellStyle(cellStyle1);
                cell0.setCellValue(reportInfoList.get(i).getName());

                HSSFCell cell1 = row.createCell(1);
                cell1.setCellStyle(cellStyle1);
                cell1.setCellValue(reportInfoList.get(i).getThisWeek());

                HSSFCell cell2 = row.createCell(2);
                cell2.setCellStyle(cellStyle1);
                cell2.setCellValue(reportInfoList.get(i).getNextWeek());
            }
            //输出Excel文件
            response.reset();
            response.setContentType("application/octet-stream;charset=utf-8");
            String fileName = "周报信息导出表" + ".xls";
            OutputStream os = response.getOutputStream();
            response.reset();//清空输出流
            String finalFileName = URLEncoder.encode(fileName, "UTF8");
            //这里设置一下让浏览器弹出下载提示框，而不是直接在浏览器中打开
            response.setHeader("Content-Disposition", "attachment; filename=\"" + finalFileName + "\"");
            response.setContentType("application/vnd.ms-excel");
            workbook.write(os);
            os.close();
        } catch (IOException e) {
            throw new IOException();
        } finally {
            if (workbook != null) {
                workbook.close();
            }
        }
    }
}

ReportInfo.java

/**
 * @author Sue
 * @create 2019-04-29 14:47
 **/
public class ReportInfo {
    private String name;
    private String thisWeek;
    private String nextWeek;

    public ReportInfo(String name, String thisWeek, String nextWeek) {
        this.name = name;
        this.thisWeek = thisWeek;
        this.nextWeek = nextWeek;
    }

    public ReportInfo() {

    }

    public String getName() {

        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getThisWeek() {
        return thisWeek;
    }

    public void setThisWeek(String thisWeek) {
        this.thisWeek = thisWeek;
    }

    public String getNextWeek() {
        return nextWeek;
    }

    public void setNextWeek(String nextWeek) {
        this.nextWeek = nextWeek;
    }
}

访问IP测试

2.使用JXL

引入jar包

<!-- https://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl -->
<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6.12</version>
</dependency>

TestExcel.java

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.JxlWriteException;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author Sue
 * @create 2019-04-29 10:39
 **/
@RestController
public class TestExcel {

    /**
     * 测试 导出xls文件的表头
     */
    public static final String[] TestToXls = {"编号", "设计人员工号", "设计人员姓名", "开发人员工号", "开发人员姓名", "测试人员工号", "测试人员姓名"};

    @GetMapping("/test")
    public void toExcel(HttpServletResponse response, HttpServletRequest request) throws Exception {

        List<Test> listTest = new ArrayList<Test>();
        Test test = new Test("1", "01", "001", "0001", "00001", "000001", "0000001");
        listTest.add(test);

        //这里为导出文件存放的路径
        String filePath = "D:\\sheet\\";
        //加入一个uuid随机数是因为
        //每次导出的时候，如果文件存在了，会将其覆盖掉，这里是保存所有的文件
        File file = new File(filePath);
        if (!file.exists()) {
            file.mkdirs();
        }

        SimpleDateFormat fmt = new SimpleDateFormat("yyyy年MM月dd HH时mm分ss秒");
        // 给要导出的文件起名为 "测试导出数据表_时间.xls"
        String filePath2 = filePath + "数据表" + "-" + fmt.format(new Date()) + ".xls";
        WritableWorkbook wb = null;
        try {
            File file2 = new File(filePath2);
            if (!file2.exists()) {//不存在，创建
                file2.createNewFile();
            }
            wb = Workbook.createWorkbook(file2);//创建xls表格文件

            // 表头显示
            WritableCellFormat wcf = new WritableCellFormat();
            wcf.setAlignment(Alignment.CENTRE);// 水平居中
            wcf.setWrap(true);
            wcf.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直居中
            wcf.setFont(new WritableFont(WritableFont.TIMES, 13, WritableFont.BOLD));// 表头字体 加粗 13号
            wcf.setBackground(jxl.format.Colour.PERIWINKLE);
            // 内容显示
            WritableCellFormat wcf2 = new WritableCellFormat();
            wcf2.setWrap(true);//设置单元格可以换行
            wcf2.setAlignment(Alignment.CENTRE);//水平居中
            wcf2.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直居中
            wcf2.setFont(new WritableFont(WritableFont.TIMES, 11));// 内容字体 11号

            //导出的xls的第一页，第二页就是0换成1，“sheet1”，也可以修改为自己想要的显示的内容
            WritableSheet ws = wb.createSheet("sheet1", 0);
            //WritableSheet ws2 = wb.createSheet("sheet2", 1);//第2个sheet页
            ws.addCell(new Label(0, 0, "导出结果"));//代表着表格中第一列的第一行显示查询结果几个字

            // 导出时生成表头
            for (int i = 0; i < TestToXls.length; i++) {
                //i,代表的第几列，1，代表第2行，第三个参数为要显示的内容，第四个参数，为内容格式设置（按照wcf的格式显示）
                ws.addCell(new Label(i, 1, TestToXls[i], wcf));//在sheet1中循环加入表头
            }

            int k = 2;//从第三行开始写入数据

            for (int i = 0; i < listTest.size(); i++) {
                ws.addCell(new Label(0, k, listTest.get(i).getIdd(), wcf2));
                ws.addCell(new Label(1, k, listTest.get(i).getDesignId(), wcf2));
                ws.addCell(new Label(2, k, listTest.get(i).getDesignName(), wcf2));
                ws.addCell(new Label(3, k, listTest.get(i).getDevelopId(), wcf2));
                ws.addCell(new Label(4, k, listTest.get(i).getDevelopName(), wcf2));
                ws.addCell(new Label(5, k, listTest.get(i).getTestId(), wcf2));
                ws.addCell(new Label(6, k, listTest.get(i).getTestName(), wcf2));
                //ws.mergeCells(4, 5, 5, 5);//合并两列，按参数顺序，意思是第4列的第五行，跟第五列的第五行合并为一个单元格
                k++;
            }
            wb.write();//写入，到这里已经生成完成，可以在相应目录下找到刚才生成的文件
        } catch (IOException e) {
            e.printStackTrace();
        } catch (JxlWriteException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        } finally {
            try {
                if (wb != null) {
                    wb.close();
                }
            } catch (WriteException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        //这个是我们项目中，是把刚才生成的文件，响应到前台，进行下载、保存，可省略。
        downLoadFile(filePath2, response);
    }

    public void downLoadFile(String filePath, HttpServletResponse response) {
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy年MM月dd HH时mm分ss秒");

        FileInputStream in = null;
        ServletOutputStream out = null;
        BufferedOutputStream toOut = null;
        String fileName = "导出数据表" + "-" + fmt.format(new Date()) + ".xls";
        try {
            in = new FileInputStream(new File(filePath));
            byte[] buffer = new byte[in.available()];
            while (in.read(buffer) != -1) {
                //                HttpServletResponse response = this.getContext().getResponse();//从application中得到response
                response.reset();// 清空
                // 设置响应的文件的头文件格式
                response.setContentType("application/octet-stream");
                response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "iso-8859-1"));
//                response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("GBK"), "ISO8859-1"));
                response.addHeader("Content-type", "application-download");
                // 获取响应的对象流
                out = response.getOutputStream();
                toOut = new BufferedOutputStream(out);
                toOut.write(buffer);
                toOut.flush();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (in != null) {
                    in.close();
                }
                if (out != null) {
                    out.close();
                }
                if (toOut != null) {
                    toOut.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

}

Test.java

/**
 * @author Sue
 * @create 2019-04-29 10:41
 **/
public class Test {
    private String idd;//编号
    private String designId;//设计人员工号
    private String designName;//设计人员姓名
    private String developId;//开发人员工号
    private String developName;//开发人员姓名
    private String testId;//测试人员工号
    private String testName;//测试人员姓名

    public Test(String idd, String designId, String designName, String developId, String developName, String testId, String testName) {
        this.idd = idd;
        this.designId = designId;
        this.designName = designName;
        this.developId = developId;
        this.developName = developName;
        this.testId = testId;
        this.testName = testName;
    }

    public Test() {

    }

    public String getIdd() {
        return idd;
    }

    public void setIdd(String idd) {
        this.idd = idd;
    }

    public String getDesignId() {
        return designId;
    }

    public void setDesignId(String designId) {
        this.designId = designId;
    }

    public String getDesignName() {
        return designName;
    }

    public void setDesignName(String designName) {
        this.designName = designName;
    }

    public String getDevelopId() {
        return developId;
    }

    public void setDevelopId(String developId) {
        this.developId = developId;
    }

    public String getDevelopName() {
        return developName;
    }

    public void setDevelopName(String developName) {
        this.developName = developName;
    }

    public String getTestId() {
        return testId;
    }

    public void setTestId(String testId) {
        this.testId = testId;
    }

    public String getTestName() {
        return testName;
    }

    public void setTestName(String testName) {
        this.testName = testName;
    }
}

访问IP测试即可