使用POI实现Excel的导出功能

最新推荐文章于 2023-07-12 15:28:33 发布

努力让自己更优秀

最新推荐文章于 2023-07-12 15:28:33 发布

阅读量215

点赞数

文章标签： POI excel导出数据库数据导入excel java hssf

本文链接：https://blog.csdn.net/weixin_37642345/article/details/82660104

版权

1，Excel工具类

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;

 /**
       * @author zhangna
       * @version Id: ExcelUtil.java, v 0.1 2018/8/15 下午11:09 zhangna Exp $$
       */

public class ExcelUtil {

      //标题行样式设置
     public CellStyle titleStyle(HSSFWorkbook workbook){
        HSSFCellStyle cellStyleTitle = workbook.createCellStyle();

        //居中
        cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);
        //背景色
        cellStyleTitle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        cellStyleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        //字体
        HSSFFont titleFont = workbook.createFont();
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titleFont.setColor(HSSFColor.BLACK.index);
        titleFont.setFontName("宋体");
        titleFont.setFontHeight((short) 300);
        cellStyleTitle.setFont(titleFont);

        //边框
        cellStyleTitle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
        cellStyleTitle.setBorderLeft(HSSFCellStyle.BORDER_DOUBLE);
        cellStyleTitle.setBorderRight(HSSFCellStyle.BORDER_DOUBLE);
        cellStyleTitle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);

        return cellStyleTitle;
    }


    //内容行样式设置
    public CellStyle contentStyle(HSSFWorkbook workbook){
        HSSFCellStyle cellStyleContent = workbook.createCellStyle();

        //居中
        cellStyleContent.setAlignment(CellStyle.ALIGN_CENTER);
        //背景色
        cellStyleContent.setFillForegroundColor(HSSFColor.WHITE.index);
        cellStyleContent.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        //字体
        HSSFFont contentFont = workbook.createFont();
        contentFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        contentFont.setColor(HSSFColor.BLACK.index);
        contentFont.setFontName("宋体");
        contentFont.setFontHeight((short) 300);
        cellStyleContent.setFont(contentFont);
        //边框
        cellStyleContent.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyleContent.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyleContent.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyleContent.setBorderTop(HSSFCellStyle.BORDER_THIN);
        /*cellStyleContent.setWrapText(true);*/

        return cellStyleContent;
    }
 }

2，pom.xml文件中引入POI依赖

 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.10-FINAL</version>
    </dependency>

3，java程序

public  void testExportData(){
  try{
     //从数据库中取出数据
     List<User> userList =  exprtData.queryUserData();

     ExcelUtil excelUtil = new ExcelUtil();
     //创建工作簿
     HSSFWorkbook workbook = new HSSFWorkbook();
     //设置标题样式
     CellStyle titleStyle = excelUtil.titleStyle(workbook);
     //设置内容的样式
     CellStyle contentStyle = excelUtil.contentStyle(workbook);
     //设置sheet的名称
     HSSFSheet sheet = workbook.createSheet("用户信息");
     //创建第一行
     HSSFRow row = sheet.createRow(0);
     //标题
     String[] title = {"用户id", "用户姓名", "性别", "年龄", "学历", "联系方式", "联系地址"};

     for (int i = 0; i < title.length; i++) {
         Cell cell = row.createCell(i);
         cell.setCellValue(title[i]);
         //设置列宽
         sheet.setColumnWidth(i, 4000);
         //设置样式
         cell.setCellStyle(titleStyle);
     }

     Cell cell = null;
      //循环遍历集合，将查出来的数据写入xcel中
     if (null != userList) {
         for (int i = 0; i < userList.size(); i++) {
             User user = userList.get(i);
             HSSFRow row1 = sheet.createRow(i + 1);
             String[] content = {user.getUserId(), user.getUserName(), user.getSex(),
                     user.getAge(), user.getEducation(), user.getPhone(),
                     user.getAddress()};
             for (int j = 0; j < content.length; j++) {
                 cell = row1.createCell(j);
                 cell.setCellValue(content[j]);
             }
             cell.setCellStyle(contentStyle);
         }
         OutputStream out = null;
         try {
           //将导出的excel保存至路径目录下
             String path = filePath;
             out = new FileOutputStream(path);
             workbook.write(out);
         } catch (IOException e) {
             log.error("导出用户信息失败");
         } finally {
             if (out != null) {
                 try {
                     out.close();
                 } catch (IOException e) {
                     log.error("关闭流异常，e={}", e);
                 }
             }
         }
     }

  }catch(Exception e){
     log.error("导出异常，e={}", e);
  }

}