Java web项目利用POI导出excel

关注

原创最新推荐文章于 2023-07-14 23:51:09 发布 1.4w 阅读 AI 写同款· GEO 优化 ›

【Apache POI】Java Web根据模板导出word文件最近工作中遇到一个需求：根据word模板文档导出word文件。查阅了一些资料，发现Apache POI可以实现文档读写的功能，于是就研究了一下，总结如下： Apache-POI在线Javadoc：http://tool.oschina.net/apidocs/apidoc?api=apache-POI 官方网站：http://poi.apache.org/ POI详细介绍： Ap... 阅读详情

今天做项目需要导出excel文件，采用POI生成excel结构。方式如下：（解决了中文名称下载问题）

方式一:

import java.io.ByteArrayOutputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

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.struts2.ServletActionContext;

import com.opensymphony.xwork2.ActionSupport;

public class ProcessExcelAction extends ActionSupport {

     public void export() {
          // 第一步，创建一个webbook，对应一个Excel文件
          HSSFWorkbook wb = new HSSFWorkbook();
          // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
          HSSFSheet sheet = wb.createSheet("学生表一");
          // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
          HSSFRow row = sheet.createRow((int) 0);
          // 第四步，创建单元格，并设置值表头 设置表头居中
          HSSFCellStyle style = wb.createCellStyle();
          style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
          String fileName = "学生.xls";
          HttpServletResponse response = ServletActionContext.getResponse();
          this.setResponseHeader(response, fileName);
          HSSFCell cell = row.createCell(0);
          cell.setCellValue("学号");
          cell.setCellStyle(style);
          cell = row.createCell(1);
          cell.setCellValue("姓名");
          cell.setCellStyle(style);
          cell = row.createCell(2);
          cell.setCellValue("年龄");
          cell.setCellStyle(style);
          cell = row.createCell(3);
          cell.setCellValue("生日");
          cell.setCellStyle(style);

          // 第五步，写入实体数据 实际应用中这些数据从数据库得到，
          List<Student> list = gainStudent();

          for (int i = 0; i < list.size(); i++) {
               row = sheet.createRow((int) i + 1);
               Student stu = (Student) list.get(i);
               // 第四步，创建单元格，并设置值
               row.createCell(0).setCellValue((double) stu.getId());
               row.createCell(1).setCellValue(stu.getName());
               row.createCell(2).setCellValue((double) stu.getAge());
               cell = row.createCell(3);
               cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(stu
                         .getBirth()));
          }
          // 第六步，将文件存到指定位置
          try {
               OutputStream os = response.getOutputStream();
               wb.write(os);
               os.flush();
               os.close();
          } catch (Exception e) {
               e.printStackTrace();
          }
     }

     public void setResponseHeader(HttpServletResponse response, String fileName) {
          try {
               try {
                    fileName = new String(fileName.getBytes(),"ISO8859-1");
               } catch (UnsupportedEncodingException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
               }
               response
                         .setContentType("application/octet-stream;charset=ISO8859-1");
               response.setHeader("Content-Disposition", "attachment;filename="
                         + fileName);
               response.addHeader("Pargam", "no-cache");
               response.addHeader("Cache-Control", "no-cache");
          } catch (Exception ex) {
               ex.printStackTrace();
          }
     }

     private List<Student> gainStudent() {
          List<Student> list = new ArrayList<Student>();
          SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");
          try {
               Student user1 = new Student(1, "张三", 16, df.parse("1997-03-12"));
               Student user2 = new Student(2, "李四", 17, df.parse("1996-08-12"));
               Student user3 = new Student(3, "王五", 26, df.parse("1985-11-12"));
               list.add(user1);
               list.add(user2);
               list.add(user3);
          } catch (ParseException e) {
               // TODO Auto-generated catch block
               e.printStackTrace();
          }

          return list;
     }

}

方式二:

import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.opensymphony.xwork2.ActionSupport;
import com.sun.xml.internal.messaging.saaj.util.ByteInputStream;
import com.sun.xml.internal.messaging.saaj.util.ByteOutputStream;
public class ProcessExcelAction1 extends ActionSupport {
       private String fileName = "学生.xls";
       private InputStream downloadStream ;
       public String export() {
             // 第一步，创建一个 webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
             // 第二步，在 webbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet( "学生表一" );
             // 第三步，在sheet中添加表头第0行,注意老版本 poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(( int ) 0);
             // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HSSFCellStyle. ALIGN_CENTER ); // 创建一个居中格式
            HSSFCell cell = row.createCell(0);
            cell.setCellValue( "学号" );
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue( "姓名" );
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue( "年龄" );
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue( "生日" );
            cell.setCellStyle(style);
             // 第五步，写入实体数据 实际应用中这些数据从数据库得到，
            List<Student> list = gainStudent();
             for (int i = 0; i < list.size(); i++) {
                  row = sheet.createRow(( int ) i + 1);
                  Student stu = (Student) list.get(i);
                   // 第四步，创建单元格，并设置值
                  row.createCell(0).setCellValue(( double ) stu.getId());
                  row.createCell(1).setCellValue(stu.getName());
                  row.createCell(2).setCellValue(( double ) stu.getAge());
                  cell = row.createCell(3);
                  cell.setCellValue( new SimpleDateFormat("yyyy-mm-dd" ).format(stu
                              .getBirth()));
            }
             // 第六步，将文件存到指定位置
             try {
                  ByteOutputStream bos = new ByteOutputStream();
                  wb.write(bos);
                  bos.flush();
                   byte [] byteArray = bos.getBytes();
                   downloadStream = new ByteInputStream(byteArray, 0, byteArray.length );
                  bos.close();
            } catch (Exception e) {
                  e.printStackTrace();
            }
             return SUCCESS ;
      }
       private List<Student> gainStudent() {
            List<Student> list = new ArrayList<Student>();
            SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd" );
             try {
                  Student user1 = new Student(1, "张三" , 16, df.parse("1997-03-12" ));
                  Student user2 = new Student(2, "李四" , 17, df.parse("1996-08-12" ));
                  Student user3 = new Student(3, "王五" , 26, df.parse("1985-11-12" ));
                  list.add(user1);
                  list.add(user2);
                  list.add(user3);
            } catch (ParseException e) {
                   // TODO Auto-generated catch block
                  e.printStackTrace();
            }
             return list;
      }
       public void setFileName(String fileName) {
             this .fileName = fileName;
      }
       public String getFileName() {
             try {
                   fileName = new String(fileName .getBytes(),"ISO8859-1" );
            } catch (UnsupportedEncodingException e) {
                   // TODO Auto-generated catch block
                  e.printStackTrace();
            }
             return fileName ;
      }
       public void setDownloadStream(InputStream downloadStream) {
             this .downloadStream = downloadStream;
      }
       public InputStream getDownloadStream() {
             return downloadStream ;
      }
}

struts2配置文件配置内容:

< result name ="success" type= "stream">
                         < param name ="contentType" > application/vnd.ms-excel</ param >
                         < param name ="contentDisposition" > attachment;filename=${fileName}</ param >
                         < param name ="bufferSize" > 1024</ param >
                         < param name ="inputName" > downloadStream</ param >
 </ result>

注意：以上红色字体为必须转换。若不转换会造成如下问题:

方式一:

1.firefox可以正常下载，但是不能显示中文名称

2.ie正常

方式二:

1.firefox可以正常下载，但是不能显示中文名称

2.ie下载报错

添加红色文本区域，两种方法均正常

收录于