用poi把列表写入excel。

最新推荐文章于 2024-03-22 09:54:02 发布

原创最新推荐文章于 2024-03-22 09:54:02 发布 · 1.4k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#excel #string #import #list #exception #integer

其他专栏收录该内容

25 篇文章

订阅专栏

本文介绍了一种利用Java POI库生成Excel文件的方法，详细展示了如何设置单元格样式、合并单元格及根据不同数据类型自动调整单元格宽度等实用技巧。

<%@page contentType="text/html;charset=GBK"%>
<%@ page import="java.util.*" %>
<%@ page import="java.io.*" %>
<%@ page import="org.apache.poi.hssf.util.*" %>
<%@ page import="org.apache.poi.hssf.usermodel.*" %>
<%@ page import="com.task.web.vo.DataCounLeftVo" %>
<%
     response.reset();//可以加也可以不加
response.setContentType("application/x-download");
     List reportList=(ArrayList)request.getAttribute("mainList");
     DataCounLeftVo fieldVO=(DataCounLeftVo)request.getAttribute("dleft");

//获取表头
   String fieldNames = fieldVO.getDisplay();
  // String fieldNames = "user nmae,user name,age";

      HSSFWorkbook   workbook   =   new   HSSFWorkbook();
      HSSFSheet   sheet   =   workbook.createSheet("new   sheet");
       String fileds[] = fieldNames.split(",");

      List tempList2 = new ArrayList();
      for(int i = 0;i< fileds.length;i++){
      tempList2.add(fileds[i]);
      }
      reportList.add(1,tempList2);
      //设置单元格宽度

      for(int clumnumber = 0;clumnumber < fileds.length;clumnumber++)
     {
     if(clumnumber==0)
        sheet.setColumnWidth((short)clumnumber,(short)((fileds[clumnumber].length() * 10) * 256));
     else
        sheet.setColumnWidth((short)clumnumber,(short)((fileds[clumnumber].length() * 3) * 256));
     }
//从表的第一行第一列取出标题
      List tempList = new ArrayList();
        tempList = (List)reportList.get(0);
      int cellNum = tempList.size();
      String titles = (String)tempList.get(0);
      tempList.clear();
//合并标题单元格
      sheet.addMergedRegion(new Region(0,(short)0,0,(short)(cellNum-1)));
     //定义title行的格式
     HSSFRow titlerow = sheet.createRow((short)0);
     HSSFCellStyle titlestyle = workbook.createCellStyle();
     HSSFFont titlefont = workbook.createFont();
     titlefont.setFontHeightInPoints((short)18);
     titlefont.setFontName("黑体");
     titlefont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
     titlestyle.setFont(titlefont);
     titlestyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
     titlestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
     HSSFCell titlecell = titlerow.createCell((short)0);
     titlecell.setCellStyle(titlestyle);
     titlecell.setEncoding(HSSFCell.ENCODING_UTF_16);
     titlecell.setCellValue(titles);


//定义表头
     //sheet.addMergedRegion(new Region(1,(short)4,1,(short)6));
     HSSFRow row2 = sheet.createRow((short)1);
     HSSFCellStyle row2style = workbook.createCellStyle();
     HSSFFont row2font = workbook.createFont();
     row2font.setFontHeightInPoints((short)12);
     row2font.setFontName("仿宋_GB2312");
     //字体加粗
     row2font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
     row2style.setFont(row2font);
     //设定样式的的背景颜色填充
//  row2style.setFillBackgroundColor(HSSFCellStyle.NO_FILL);
//  row2style.setFillPattern(HSSFCellStyle.FINE_DOTS);
  //居中对齐
  row2style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  row2style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
  //设定单元个背景颜
  row2style.setFillBackgroundColor(HSSFColor.GREY_40_PERCENT.index);
  //设置单元格显示颜
  row2style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
  row2style.setFont(row2font);
//  row2style.setAlignment((short)2);//设置居中
  row2style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边
  row2style.setBorderLeft(HSSFCellStyle.BORDER_THIN); //左边
  row2style.setBorderRight(HSSFCellStyle.BORDER_THIN); //右边
  row2style.setBorderTop(HSSFCellStyle.BORDER_THIN); //上边

//设置数据域单元格格式
  HSSFCellStyle datastyle = workbook.createCellStyle();
  HSSFFont datafont = workbook.createFont();
  datafont.setFontHeightInPoints((short)10);
  datafont.setFontName("仿宋_GB2312");
  //数据域边缘线加暗色
  datastyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边
  datastyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); //左边
  datastyle.setBorderRight(HSSFCellStyle.BORDER_THIN); //右边
  datastyle.setBorderTop(HSSFCellStyle.BORDER_THIN); //上边
  datastyle.setFont(datafont);
  datastyle.setWrapText(true);//文本区域随内容多少自动调整
  datastyle.setVerticalAlignment((short)1);//设置垂直居中
  datastyle.setAlignment((short)2);//设置水平居中

// 设置数据域单元格格式
HSSFCellStyle datastyle_num = workbook.createCellStyle();

  //数据域边缘线加暗色
  datastyle_num.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边
  datastyle_num.setBorderLeft(HSSFCellStyle.BORDER_THIN); //左边
  datastyle_num.setBorderRight(HSSFCellStyle.BORDER_THIN); //右边
  datastyle_num.setBorderTop(HSSFCellStyle.BORDER_THIN); //上边
  datastyle_num.setFont(datafont);
  datastyle_num.setWrapText(true);//文本区域随内容多少自动调整
  datastyle_num.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
//  datastyle_num.setVerticalAlignment((short)1);//设置垂直居中
//  datastyle_num.setAlignment((short)2);//设置水平居中
//设置表头
  tempList = (List)reportList.get(1);
  for(int j = 0;j < tempList.size();j++)
     {
      HSSFCell row2cell = row2.createCell((short)j);
      row2cell.setCellStyle(row2style);
      row2cell.setEncoding(HSSFCell.ENCODING_UTF_16);
      row2cell.setCellValue((String)tempList.get(j));
     }

  String dataValue = "";
  int intValue = 0;
      for(int i = 2; i<reportList.size(); i++)
         {
         tempList = (List)reportList.get(i);
         HSSFRow   row   =   sheet.createRow((short)i);
         for(int j = 0; j<tempList.size(); j++)
         {
       HSSFCell row2cell = row.createCell((short)j);
       row2cell.setEncoding(HSSFCell.ENCODING_UTF_16);
       dataValue = (String)tempList.get(j);
//转换整形数值
       try{
       intValue = (new Integer(dataValue)).intValue();
       row2cell.setCellStyle(datastyle_num);
       row2cell.setCellValue(intValue);
       }catch(Exception e){
      row2cell.setCellStyle(datastyle);
      row2cell.setCellValue(dataValue);}

         }
          }


    try{
     response.setContentType("application/vnd.ms-excel;charset=GBK");
String strDocName = "attachment;filename=demo.xls";
// String strDocName = "attachment;filename="+request.getAttribute("taskName")+".xls";
response.setHeader("Content-disposition",strDocName);
     OutputStream ouputStream = response.getOutputStream();
     workbook.write(ouputStream);
     ouputStream.flush();
     ouputStream.close();
     ouputStream=null;
     out.clear();
     out = pageContext.pushBody();
     }catch(IllegalStateException e)
{
System.out.println(e.getMessage());
e.printStackTrace();
}
%>