java使用poi导出Excel的方法

本文实例为大家分享了java使用poi导出excel的具体代码，供大家参考，具体内容如下

package hyss.util.common;

import java.io.ioexception;
import java.io.outputstream;
import java.net.urlencoder;
import java.util.arraylist;
import java.util.list;

import org.apache.poi.hssf.usermodel.hssfcell;
import org.apache.poi.hssf.usermodel.hssfcellstyle;
import org.apache.poi.hssf.usermodel.hssffont;
import org.apache.poi.hssf.usermodel.hssfrichtextstring;
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.hssf.util.hssfcolor;
import org.apache.poi.ss.util.cellrangeaddress;

/**
 * excel导出工具类
 * @author ts
 */

public class exportexcelutil {
 //显示的导出表的标题
 private string title;
 //导出表的列名
 private string[] rowname ;

 private list<object[]> datalist = new arraylist<object[]>();

// httpservletresponse response;

 //构造方法，传入要导出的数据
 public exportexcelutil(string title,string[] rowname,list<object[]> datalist){
  this.datalist = datalist;
  this.rowname = rowname;
  this.title = title;
 }

 /*
  * 导出数据
  * */
 public void export() throws exception{
  try{
   hssfworkbook workbook = new hssfworkbook();      // 创建工作簿对象
   hssfsheet sheet = workbook.createsheet(title);      // 创建工作表

   // 产生表格标题行
   hssfrow rowm = sheet.createrow(0);
   hssfcell celltiltle = rowm.createcell(0);
   rowm.setheightinpoints(25);           //设置标题行默认行高

   //sheet样式定义【getcolumntopstyle()/getstyle()均为自定义方法 - 在下面 - 可扩展】
   hssfcellstyle columntitlestyle = this.gettitletopstyle(workbook);//获取标题行样式
   hssfcellstyle columntopstyle = this.getcolumntopstyle(workbook); //获取列头样式对象
   hssfcellstyle style = this.getstyle(workbook);     //单元格样式对象

   sheet.addmergedregion(new cellrangeaddress(0, 1, 0, (rowname.length-1)));
   celltiltle.setcellstyle(columntitlestyle);
   celltiltle.setcellvalue(title);

   // 定义所需列数
   int columnnum = rowname.length;
   hssfrow rowrowname = sheet.createrow(2);    // 在索引2的位置创建行(最顶端的行开始的第二行)
   rowrowname.setheightinpoints(25);       //将列头设置默认行高
   // 将列头设置到sheet的单元格中
   for(int n=0;n<columnnum;n++){
    hssfcell cellrowname = rowrowname.createcell(n);    //创建列头对应个数的单元格
    cellrowname.setcelltype(hssfcell.cell_type_string);    //设置列头单元格的数据类型
    hssfrichtextstring text = new hssfrichtextstring(rowname[n]);
    cellrowname.setcellvalue(text);         //设置列头单元格的值
    cellrowname.setcellstyle(columntopstyle);      //设置列头单元格样式
   }

   //将查询出的数据设置到sheet对应的单元格中
   for(int i=0;i<datalist.size();i++){

    object[] obj = datalist.get(i);//遍历每个对象
    hssfrow row = sheet.createrow(i+3);//创建所需的行数
    row.setheightinpoints(20); //将创建出的行设置默认行高
    for(int j=0; j<obj.length; j++){
     hssfcell cell = null; //设置单元格的数据类型
     if(j == 0){
      cell = row.createcell(j,hssfcell.cell_type_numeric);
      cell.setcellvalue(i+1);
     }else{
      cell = row.createcell(j,hssfcell.cell_type_string);
      if(!"".equals(obj[j]) && obj[j] != null){
       cell.setcellvalue(obj[j].tostring());      //设置单元格的值
      }
     }
     cell.setcellstyle(style);         //设置单元格样式
    }
   }
   //让列宽随着导出的列长自动适应
   for (int colnum = 0; colnum < columnnum; colnum++) {
    int columnwidth = sheet.getcolumnwidth(colnum) / 256;
    for (int rownum = 0; rownum < sheet.getlastrownum(); rownum++) {
     hssfrow currentrow;
     //当前行未被使用过
     if (sheet.getrow(rownum) == null) {
      currentrow = sheet.createrow(rownum);
     } else {
      currentrow = sheet.getrow(rownum);
     }
     if (currentrow.getcell(colnum) != null) {
      hssfcell currentcell = currentrow.getcell(colnum);
      if (currentcell.getcelltype() == hssfcell.cell_type_string) {
       int length = currentcell.getstringcellvalue().getbytes().length;
       if (columnwidth < length) {
        columnwidth = length;
       }
      }
     }
    }
    if(colnum == 0){
     sheet.setcolumnwidth(colnum, (columnwidth-2) * 256);

    }else{
     sheet.setcolumnwidth(colnum, (columnwidth+4) * 256);
    }
   }

   if(workbook !=null){
    try
    {
     string filename = title + datetime.getsystemdatetime("yyyy-mm-dd") + ".xls";
　　　　　　　　　　　　　//因 response已经封装成工具类所以下面这段代码注释掉
//     response =servletactioncontext.getresponse();
//     response.setcontenttype("application/octet-stream");
//     response.setheader("content-disposition", headstr);
//     outputstream out = response.getoutputstream();

     //解决中文乱码
     struts2util.getresponse().setcharacterencoding("utf-8");
     //解决中文乱码
     struts2util.getresponse().setheader("content-disposition", "attachment;filename*=utf-8''" + urlencoder.encode(filename,"utf-8"));
     //文件下载
     struts2util.getresponse().setcontenttype("application/octet-stream");
     outputstream out = struts2util.getresponse().getoutputstream();
     workbook.write(out);
    }
    catch (ioexception e)
    {
     e.printstacktrace();
    }
   }

  }catch(exception e){
   e.printstacktrace();
  }

 }

 /*
  * 设置标题样式
  */
  public hssfcellstyle gettitletopstyle(hssfworkbook workbook) {

   // 设置字体
   hssffont font = workbook.createfont();
   //设置字体大小
   font.setfontheightinpoints((short)24);
   //字体加粗
   font.setboldweight(hssffont.boldweight_bold);
   //设置字体名字
   font.setfontname("宋体");
   //设置样式;
   hssfcellstyle style = workbook.createcellstyle();
   //设置底边框;
   style.setborderbottom(hssfcellstyle.border_thin);
   //设置底边框颜色;
   style.setbottombordercolor(hssfcolor.black.index);
   //设置左边框;
   style.setborderleft(hssfcellstyle.border_thin);
   //设置左边框颜色;
   style.setleftbordercolor(hssfcolor.black.index);
   //设置右边框;
   style.setborderright(hssfcellstyle.border_thin);
   //设置右边框颜色;
   style.setrightbordercolor(hssfcolor.black.index);
   //设置顶边框;
   style.setbordertop(hssfcellstyle.border_thin);
   //设置顶边框颜色;
   style.settopbordercolor(hssfcolor.black.index);
   //在样式用应用设置的字体;
   style.setfont(font);
   //设置自动换行;
   style.setwraptext(false);
   //设置水平对齐的样式为居中对齐;
   style.setalignment(hssfcellstyle.align_center);
   //设置垂直对齐的样式为居中对齐;
   style.setverticalalignment(hssfcellstyle.vertical_center);

   return style;

  }


 /*
  * 列头单元格样式
  */
  public hssfcellstyle getcolumntopstyle(hssfworkbook workbook) {

   // 设置字体
   hssffont font = workbook.createfont();
   //设置字体大小
   font.setfontheightinpoints((short)11);
   //字体加粗
   font.setboldweight(hssffont.boldweight_bold);
   //设置字体名字
   font.setfontname("宋体");
   //设置样式;
   hssfcellstyle style = workbook.createcellstyle();
   //设置底边框;
   style.setborderbottom(hssfcellstyle.border_thin);
   //设置底边框颜色;
   style.setbottombordercolor(hssfcolor.black.index);
   //设置左边框;
   style.setborderleft(hssfcellstyle.border_thin);
   //设置左边框颜色;
   style.setleftbordercolor(hssfcolor.black.index);
   //设置右边框;
   style.setborderright(hssfcellstyle.border_thin);
   //设置右边框颜色;
   style.setrightbordercolor(hssfcolor.black.index);
   //设置顶边框;
   style.setbordertop(hssfcellstyle.border_thin);
   //设置顶边框颜色;
   style.settopbordercolor(hssfcolor.black.index);
   //在样式用应用设置的字体;
   style.setfont(font);
   //设置自动换行;
   style.setwraptext(false);
   //设置水平对齐的样式为居中对齐;
   style.setalignment(hssfcellstyle.align_center);
   //设置垂直对齐的样式为居中对齐;
   style.setverticalalignment(hssfcellstyle.vertical_center);

   return style;

  }

  /*
  * 列数据信息单元格样式
  */
  public hssfcellstyle getstyle(hssfworkbook workbook) {
   // 设置字体
   hssffont font = workbook.createfont();
   //设置字体大小
   //font.setfontheightinpoints((short)10);
   //字体加粗
   //font.setboldweight(hssffont.boldweight_bold);
   //设置字体名字
   font.setfontname("宋体");
   //设置样式;
   hssfcellstyle style = workbook.createcellstyle();
   //设置底边框;
   style.setborderbottom(hssfcellstyle.border_thin);
   //设置底边框颜色;
   style.setbottombordercolor(hssfcolor.black.index);
   //设置左边框;
   style.setborderleft(hssfcellstyle.border_thin);
   //设置左边框颜色;
   style.setleftbordercolor(hssfcolor.black.index);
   //设置右边框;
   style.setborderright(hssfcellstyle.border_thin);
   //设置右边框颜色;
   style.setrightbordercolor(hssfcolor.black.index);
   //设置顶边框;
   style.setbordertop(hssfcellstyle.border_thin);
   //设置顶边框颜色;
   style.settopbordercolor(hssfcolor.black.index);
   //在样式用应用设置的字体;
   style.setfont(font);
   //设置自动换行;
   style.setwraptext(false);
   //设置水平对齐的样式为居中对齐;
//   style.setalignment(hssfcellstyle.align_center);
   //设置垂直对齐的样式为居中对齐;
//   style.setverticalalignment(hssfcellstyle.vertical_center);


   return style;

  }

}

实现方法

/*
* 实现方法
*/
public void exportexcel() throws exception{
  string title = "测试";
  string[] rowsname = new string[]{"序号","列头1","列头2","列头3","列头4","列头5"};
  list<object[]> datalist = new arraylist<object[]>();
  object[] objs = null;
  for (int i = 0; i < 10; i++) {
   objs = new object[rowsname.length];
   objs[0] = i;
   objs[1] = "测试1";
   objs[2] = "测试2";
   objs[3] = "测试3";
   objs[4] = "测试4";
   objs[5] = "测试5";
   datalist.add(objs);
  }
  exportexcelutil ex = new exportexcelutil(title, rowsname, datalist);
  ex.export();

 }

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。

原文链接：https://www.cnblogs.com/jiaobaobao/archive/2018/08/16/9485905.html