Java使用POI导出Excel文件

原创

BusyCoder 2023-03-11 19:25:13 ©著作权

文章标签 java excel 开发语言 apache User 文章分类 JavaScript 前端开发

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

1.下载

https://mvnrepository.com/artifact/com.aliyun.openservices

Java使用POI导出Excel文件_apache

Java使用POI导出Excel文件_java_02

2.流程

工作簿
  |--sheet1
    |--row   column
  |--sheet2
流程
  创建工作簿
    在工作簿里面创建sheet
      在sheet里面创建row
        在row里创建cell单元

3.导出代码样式封装[ExprotCellStyle]

package com.sxt.utils;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

public class ExprotCellStyle {

  /**
   * 得到表头样式
   * @param workbook
   * @return
   */
  public static HSSFCellStyle createTableTitleStyle(HSSFWorkbook workbook) {
    HSSFCellStyle cellStyle = setRowCellCenter(workbook);//水平居中
    //设置字体
    HSSFFont font = setFontCellStyle(workbook, (short)15, HSSFColorPredefined.BLUE.getIndex(), true,false, HSSFFont.U_NONE);
    font.setFontName("华文琥珀");
    cellStyle.setFont(font);
    return cellStyle;
  }

  /**
   * 创建小标题的样式
   * @param workbook
   * @return
   */
  public static HSSFCellStyle createSecondTitleStyle(HSSFWorkbook workbook) {
    HSSFCellStyle cellStyle = setRowCellCenter(workbook);//水平居中
    //设置字体
    HSSFFont font = setFontCellStyle(workbook, (short)18, HSSFColorPredefined.GOLD.getIndex(), true,false, HSSFFont.U_NONE);
    cellStyle.setFont(font);
    return cellStyle;
  }

  /**
   * 创建表头的样式
   * @param workbook
   * @return
   */
  public static HSSFCellStyle createTitleCellStyle(HSSFWorkbook workbook) {
    HSSFCellStyle cellStyle = setRowCellCenter(workbook);
    //设置字体
    HSSFFont font = setFontCellStyle(workbook, (short)30, HSSFColorPredefined.RED.getIndex(), true,false, HSSFFont.U_DOUBLE);
    cellStyle.setFont(font);
    return cellStyle;
  }

  /**
   * 
   * @param workbook  工作簿
   * @param fontSize  字体大小 
   * @param colorIndex 字体颜色  @see HSSFColorPredefined
   * @param bold  是否加粗
   * @param italic  是否斜体
   * @param undderLine   下划线风格  @see HSSFFont.U_DOUBLE
   * @return
   */
  public static HSSFFont setFontCellStyle(HSSFWorkbook workbook,
      short fontSize, short colorIndex, boolean bold, boolean italic,
      byte undderLine) {
    HSSFFont font=workbook.createFont();
    font.setFontHeightInPoints(fontSize);//字体大小
    font.setColor(colorIndex);//设置字体颜色
    font.setBold(bold);//加粗
    font.setItalic(italic);//设置非斜体
    font.setUnderline(undderLine);//设置下划线
    return font;
  }

  /**
   * 创建水平和垂直居 的方法
   * @param workbook
   * @return
   */
  public static HSSFCellStyle setRowCellCenter(HSSFWorkbook workbook) {
    HSSFCellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
    return cellStyle;
  }
  
  
}

4.导出代码

package com.sxt.utils;

import static com.sxt.utils.ExprotCellStyle.*;

import java.io.File;
import java.io.IOException;
import java.util.Date;
import java.util.List;

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.util.CellRangeAddress;

import com.sxt.domain.User;
/**
 * 导出用户数据
 * @author LJH
 *
 */
public class ExportUserUtils {
  

  /**
   * 把数据导出到path
   * @param users
   */
  @SuppressWarnings("deprecation")
  public static void exportUser(List<User> users,String path) {
    //创建工作簿
    HSSFWorkbook workbook=new HSSFWorkbook();
    //从工作簿里面创建sheet
//    workbook.createSheet();
    HSSFSheet sheet = workbook.createSheet("用户列表");
    //设置sheet
    //sheet.setColumnWidth(2, 100);//设置某一个下标的列宽
    //sheet.setDefaultColumnStyle(column, style);//设置某一列的默认样式
    sheet.setDefaultColumnWidth(20);//设置所有列的列宽
//    sheet.setColumnHidden(columnIndex, hidden);//设置某一列是否隐藏
    //sheet.setDefaultRowHeight((short)(30*20));//设置行高60
    //sheet.setDefaultRowHeightInPoints(30);//设置行高30
    //合并
    CellRangeAddress region1=new CellRangeAddress(0, 0, 0, 2);
    CellRangeAddress region2=new CellRangeAddress(1, 1, 0, 2);
    sheet.addMergedRegion(region1);
    sheet.addMergedRegion(region2);
    
    //的sheet上创建行
    int rownum=0;
    HSSFRow row01 = sheet.createRow(rownum);
    //在row01上创建单元格  
    HSSFCell cell_row01 = row01.createCell(0);
    //向cell_row01写东西
    cell_row01.setCellValue("用户数据");
    //设置标题样式
    HSSFCellStyle titleStyle=createTitleCellStyle(workbook);
    cell_row01.setCellStyle(titleStyle);
    
    //第二行
    rownum++;
    HSSFRow row02 = sheet.createRow(rownum);
    HSSFCell cell_row02 = row02.createCell(0);
    cell_row02.setCellValue("总数:"+users.size()+"，导出时间:"+new Date().toLocaleString());
    //设置小标题样式
    HSSFCellStyle secondTitleStyle=createSecondTitleStyle(workbook);
    cell_row02.setCellStyle(secondTitleStyle);
    
    //第三行
    rownum++;
    HSSFRow row03 = sheet.createRow(rownum);
    String[] titles={"用户ID","用户名","用户地址"};
    //得到表头的样式
    HSSFCellStyle tableTitleStyle=createTableTitleStyle(workbook);
    
    for (int i = 0; i < titles.length; i++) {
      HSSFCell cell = row03.createCell(i);
       cell.setCellValue(titles[i]);
       cell.setCellStyle(tableTitleStyle);
    }
    
    HSSFCellStyle tableBodyStyle=setRowCellCenter(workbook);
    for (int i = 0; i < users.size(); i++) {
      rownum++;
      HSSFRow row = sheet.createRow(rownum);
      User user =users.get(i);
      //创建idcell
      HSSFCell idCell = row.createCell(0);
      idCell.setCellValue(user.getId());
      
      idCell.setCellStyle(tableBodyStyle);
      //创建namecell
      HSSFCell nameCell = row.createCell(1);
      nameCell.setCellValue(user.getName());
      nameCell.setCellStyle(tableBodyStyle);
      
      //创建addresscell
      HSSFCell addressCell = row.createCell(2);
      addressCell.setCellValue(user.getAddress());
      addressCell.setCellStyle(tableBodyStyle);
    }
    
    //导出数据
    try {
      workbook.write(new File(path));
      System.out.println("导出完成");
    } catch (IOException e) {
      e.printStackTrace();
    }
  }
}

5.总结

HSSFWorkBook

|–创建方式   new HSSFWorkBook()

|–相关方法

|–workbook.createSheet()

|–workbook.createSheet(“name”)

|–workbook.write(new File(path));

HSSFSheet

|–创建方式  -workbook.createSheet()

|–相关方法

//sheet.setColumnWidth(2, 100);//设置某一个下标的列宽

//sheet.setDefaultColumnStyle(column, style);//设置某一列的默认样式

sheet.setDefaultColumnWidth(20);//设置所有列的列宽

//    sheet.setColumnHidden(columnIndex, hidden);//设置某一列是否隐藏

//sheet.setDefaultRowHeight((short)(30*20));//设置行高60

//sheet.setDefaultRowHeightInPoints(30);//设置行高30

//合并

CellRangeAddress region1=new CellRangeAddress(0, 0, 0, 2);

CellRangeAddress region2=new CellRangeAddress(1, 1, 0, 2);

sheet.addMergedRegion(region1);

sheet.addMergedRegion(region2);

HSSFRow

创建方式

|–sheet.creatRow(rowIndex)

相关方法

|–row01.createCell(0);  在行里面创建列

4.HSSFCell

创建方式  

|–row01.createCell(0);  在行里面创建列

相关方法

|–cell_row01.setCellStyle(titleStyle)  设置样式

|-- cell.setCellValue(titles[i]);  设置里面的内容

5.HSSFCellStyle 样式类

创建方式
    |--workbook. createCellStyle()
  相关方法
    cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
    cellStyle.setFont(font); 设置字体

HSSFFont 字体样式类

创建方式

|–workbook.createFont();

相关方法

font.setFontHeightInPoints(fontSize);//字体大小

font.setColor(colorIndex);//设置字体颜色

font.setBold(bold);//加粗

font.setItalic(italic);//设置非斜体

font.setUnderline(undderLine);//设置下划线

font.setFontName(“华文琥珀”);//设置字体风格

CellRangeAddress

创建方式

|–new CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)

|–参数1开始行

|–参数2 结束行

|–参数3 开始列

|–参数4 结束列

6.示例

import java.util.ArrayList;
import java.util.List;

import com.sxt.utils.ExportUserUtils;

public class TestUser {
  
  public static void main(String[] args) {
    List<User> users=new ArrayList<>();
    for (int i = 1; i <=10; i++) {
      users.add(new User(i, "小明"+i, "武汉"+i));
    }
    String path="E:/user.xls";
    //导出
    ExportUserUtils.exportUser(users,path);
  }
}

public class User {
  
  private Integer id;
  private String name;
  private String address;
  public User() {
  }
  public User(Integer id, String name, String address) {
    super();
    this.id = id;
    this.name = name;
    this.address = address;
  }
  //set和get方法缺省
}

import static com.sxt.utils.ExprotCellStyle.*;

import java.io.File;
import java.io.IOException;
import java.util.Date;
import java.util.List;

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.util.CellRangeAddress;

import com.sxt.domain.User;
/**
 * 导出用户数据
 * @author LJH
 *
 */
public class ExportUserUtils {
  

  /**
   * 把数据导出到path
   * @param users
   */
  @SuppressWarnings("deprecation")
  public static byte[] exportUser(List<User> users,String path) {
    //创建工作簿
    HSSFWorkbook workbook=new HSSFWorkbook();
    //从工作簿里面创建sheet
//    workbook.createSheet();
    HSSFSheet sheet = workbook.createSheet("用户列表");
    //设置sheet
    //sheet.setColumnWidth(2, 100);//设置某一个下标的列宽
    //sheet.setDefaultColumnStyle(column, style);//设置某一列的默认样式
    sheet.setDefaultColumnWidth(20);//设置所有列的列宽
//    sheet.setColumnHidden(columnIndex, hidden);//设置某一列是否隐藏
    //sheet.setDefaultRowHeight((short)(30*20));//设置行高60
    //sheet.setDefaultRowHeightInPoints(30);//设置行高30
    //合并
    CellRangeAddress region1=new CellRangeAddress(0, 0, 0, 2);
    CellRangeAddress region2=new CellRangeAddress(1, 1, 0, 2);
    sheet.addMergedRegion(region1);
    sheet.addMergedRegion(region2);
    
    //的sheet上创建行
    int rownum=0;
    HSSFRow row01 = sheet.createRow(rownum);
    //在row01上创建单元格  
    HSSFCell cell_row01 = row01.createCell(0);
    //向cell_row01写东西
    cell_row01.setCellValue("用户数据");
    //设置标题样式
    HSSFCellStyle titleStyle=createTitleCellStyle(workbook);
    cell_row01.setCellStyle(titleStyle);
    
    //第二行
    rownum++;
    HSSFRow row02 = sheet.createRow(rownum);
    HSSFCell cell_row02 = row02.createCell(0);
    cell_row02.setCellValue("总数:"+users.size()+"，导出时间:"+new Date().toLocaleString());
    //设置小标题样式
    HSSFCellStyle secondTitleStyle=createSecondTitleStyle(workbook);
    cell_row02.setCellStyle(secondTitleStyle);
    
    //第三行
    rownum++;
    HSSFRow row03 = sheet.createRow(rownum);
    String[] titles={"用户ID","用户名","用户地址"};
    //得到表头的样式
    HSSFCellStyle tableTitleStyle=createTableTitleStyle(workbook);
    
    for (int i = 0; i < titles.length; i++) {
      HSSFCell cell = row03.createCell(i);
       cell.setCellValue(titles[i]);
       cell.setCellStyle(tableTitleStyle);
    }
    
    HSSFCellStyle tableBodyStyle=setRowCellCenter(workbook);
    for (int i = 0; i < users.size(); i++) {
      rownum++;
      HSSFRow row = sheet.createRow(rownum);
      User user =users.get(i);
      //创建idcell
      HSSFCell idCell = row.createCell(0);
      idCell.setCellValue(user.getId());
      
      idCell.setCellStyle(tableBodyStyle);
      //创建namecell
      HSSFCell nameCell = row.createCell(1);
      nameCell.setCellValue(user.getName());
      nameCell.setCellStyle(tableBodyStyle);
      
      //创建addresscell
      HSSFCell addressCell = row.createCell(2);
      addressCell.setCellValue(user.getAddress());
      addressCell.setCellStyle(tableBodyStyle);
    }
    
    //导出数据
    try {
      workbook.write(new File(path));
      return workbook.getBytes();
    } catch (Exception e) {
      e.printStackTrace();
    }finally
    {
      try {
        workbook.close();
      } catch (IOException e) {
        e.printStackTrace();
      }
    }
    
    return null;
  }
}

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

public class ExprotCellStyle {

  /**
   * 得到表头样式
   * @param workbook
   * @return
   */
  public static HSSFCellStyle createTableTitleStyle(HSSFWorkbook workbook) {
    HSSFCellStyle cellStyle = setRowCellCenter(workbook);//水平居中
    //设置字体
    HSSFFont font = setFontCellStyle(workbook, (short)15, HSSFColorPredefined.BLUE.getIndex(), true,false, HSSFFont.U_NONE);
    font.setFontName("华文琥珀");
    cellStyle.setFont(font);
    return cellStyle;
  }

  /**
   * 创建小标题的样式
   * @param workbook
   * @return
   */
  public static HSSFCellStyle createSecondTitleStyle(HSSFWorkbook workbook) {
    HSSFCellStyle cellStyle = setRowCellCenter(workbook);//水平居中
    //设置字体
    HSSFFont font = setFontCellStyle(workbook, (short)18, HSSFColorPredefined.GOLD.getIndex(), true,false, HSSFFont.U_NONE);
    cellStyle.setFont(font);
    return cellStyle;
  }

  /**
   * 创建表头的样式
   * @param workbook
   * @return
   */
  public static HSSFCellStyle createTitleCellStyle(HSSFWorkbook workbook) {
    HSSFCellStyle cellStyle = setRowCellCenter(workbook);
    //设置字体
    HSSFFont font = setFontCellStyle(workbook, (short)30, HSSFColorPredefined.RED.getIndex(), true,false, HSSFFont.U_DOUBLE);
    cellStyle.setFont(font);
    return cellStyle;
  }

  /**
   * 
   * @param workbook  工作簿
   * @param fontSize  字体大小 
   * @param colorIndex 字体颜色  @see HSSFColorPredefined
   * @param bold  是否加粗
   * @param italic  是否斜体
   * @param undderLine   下划线风格  @see HSSFFont.U_DOUBLE
   * @return
   */
  public static HSSFFont setFontCellStyle(HSSFWorkbook workbook,
      short fontSize, short colorIndex, boolean bold, boolean italic,
      byte undderLine) {
    HSSFFont font=workbook.createFont();
    font.setFontHeightInPoints(fontSize);//字体大小
    font.setColor(colorIndex);//设置字体颜色
    font.setBold(bold);//加粗
    font.setItalic(italic);//设置非斜体
    font.setUnderline(undderLine);//设置下划线
    return font;
  }

  /**
   * 创建水平和垂直居 的方法
   * @param workbook
   * @return
   */
  public static HSSFCellStyle setRowCellCenter(HSSFWorkbook workbook) {
    HSSFCellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
    return cellStyle;
  }
  
  
}