POI 文档 Excel导出功能实现

原创 于 2017-04-20 10:11:43 发布 · 792 阅读 · 2 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#poi #excel

这篇博客介绍了如何利用Apache POI库来实现Excel文档的导出功能，包括需要的jar包如commons-logging、junit、log4j以及poi-3.9，以及一个关键的Util工具类ExcelEntity.java的使用。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

1.所需jar包

commons-logging-1.0.4.jar
junit-4.7.jar
log4j-1.2.16.jar
org\apache\poi\poi\3.9

2.Util工具类

ExcelEntity.java

package com.httc.base.web.api.util;
import java.util.List;
/**
* 代表要打印的Excel表格,用于存放要导出为Excel的相关数据
* @author zhrb@cec.jmu
*
* @param <T> 代表要打印的数据实体，如User等
*/
public class ExcelEntity<T> {
private String sheetName = "Sheet1";//默认生成的sheet名称
private String header="";//题头
private String footer="";//脚注
//底下是必须具备的属性
private String fileName;
private String[] columnNames;//列名
private String[] methodNames;//与列名对应的方法名
private List<T> entities;//数据实体
public ExcelEntity(String fileName, String[] columnNames, String[] methodNames, List<T> entities) {
this("sheet1","","",fileName,columnNames,methodNames,entities);
}
public ExcelEntity(String sheetName, String header, String footer, String fileName, String[] columnNames,
String[] methodNames, List<T> entities) {
this.sheetName = sheetName;
this.header = header;
this.footer = footer;
this.fileName = fileName;
this.columnNames = columnNames;
this.methodNames = methodNames;
this.entities = entities;
}
public String getHeader() {
return header;
}
public void setHeader(String header) {
this.header = header;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public List<T> getEntities() {
return entities;
}
/**
*
* @param entities 用于导出Excel的实体集合
*/
public void setEntities(List<T> entities) {
this.entities = entities;
}
public String getFooter() {
return footer;
}
public void setFooter(String footer) {
this.footer = footer;
}
public String[] getColumnNames() {
return columnNames;
}
public void setColumnNames(String[] columnNames) {
this.columnNames = columnNames;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
public String[] getMethodNames() {
return methodNames;
}
public void setMethodNames(String[] methodNames) {
this.methodNames = methodNames;
}
}
ExcelExporter.java（通用的导出Excel） 

package com.httc.base.web.api.util;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Footer;
import org.apache.poi.ss.usermodel.Header;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
* 一个通用的将List<T>中数据导出为Excel文档的工具类
* @author zhrb@cec.jmu
*/
public class ExcelExporter {
/**
* 根据ExcelEntity等参数生成Workbook
* @param entity
* @return
* @throws Exception
*/
public static <T> Workbook export2Excel(ExcelEntity<T> entity) throws Exception{
Workbook workbook = export2Excel(entity.getHeader(), entity.getFooter(),entity.getSheetName(), entity.getColumnNames(), entity.getMethodNames(),
entity.getEntities());
return workbook;
}
/**
* 根据给定参数导出Excel文档
*
* @param headerTitle
* 题头
* @param footer 脚注
* @param sheetName
* @param columnNames
* 表头名称
* @param methodNames
* @param entities
* @return
* @throws Exception
*/
public static <T> Workbook export2Excel(String headerTitle, String footerTitle, String sheetName, String[] columnNames,
String[] methodNames, List<T> entities) throws Exception {
if (methodNames.length != columnNames.length)
throw new IllegalArgumentException("methodNames.length should be equal to columnNames.length:"
+ columnNames.length + " " + methodNames.length);
Workbook newWorkBook2007 = new XSSFWorkbook();
Sheet sheet = newWorkBook2007.createSheet(sheetName);
//设置题头
Header header = sheet.getHeader();
header.setCenter(headerTitle);
//设置脚注
Footer footer = sheet.getFooter();
footer.setCenter(footerTitle);
int[] columnWidths = new int[columnNames.length];
// 创建表头
createTableHeader(sheet, 0, headerTitle, columnNames, columnWidths);
// 填充表内容
createTableContent(sheet, 1, methodNames, columnWidths, entities);
return newWorkBook2007;
}
/**
* 创建表头
*
* @param sheet
* @param index
* 表头开始的行数
* @param headerTitle
* 题头
* @param columnNames
* @param columnWidths
*/
private static void createTableHeader(Sheet sheet, int index, String headerTitle, String[] columnNames,
int[] columnWidths) {
Row headerRow = sheet.createRow(index);
/* 格式设置 */
// 设置字体
Font font = sheet.getWorkbook().createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);// 粗体显示
// 设置背景色
CellStyle style = sheet.getWorkbook().createCellStyle();
style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(font);
for (int i = 0; i < columnNames.length; i++) {
Cell headerCell = headerRow.createCell(i);
headerCell.setCellStyle(style);
headerCell.setCellValue(columnNames[i]);
}
for (int i = 0; i < columnNames.length; i++) {
columnWidths[i] = (columnNames[i].getBytes().length + 2) * 256;
sheet.setColumnWidth(i, columnWidths[i]);
}
}
/**
* 创建表格内容
*
* @param sheet
* @param rowIndexBegin
package com.httc.base.web.api.controller;

import java.util.List;
import java.util.Map;



import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import com.buss.orgmajorinfo.entity.OrgMajorInfoEntity;
import com.google.common.collect.Maps;
import com.httc.base.web.api.util.ExcelEntity;
import com.httc.base.web.api.util.ExcelExporter;
import com.httc.common.dal.client.DalClient;



@Controller
@RequestMapping("/v1/excel")
public class ExportController {
	
	 @Autowired
	private DalClient dalClient;
	/**
* 测试方法
* 
*/
	@RequestMapping(value = "/daochu", method = RequestMethod.GET)
  	@ResponseBody
  	public void daoChu() throws Exception {
  	
  	Map<String, Object> whereMap = Maps.newHashMap();
  	List<OrgMajorInfoEntity> orgMajorInfoEntityList = dalClient.queryForList(
			//sql语句 “select * from---”
			"orgMajorInfo_read.listOrgMajorInfoHisEntityEXCEL", whereMap, OrgMajorInfoEntity.class);
  	//将所查数据放入listOrg中   
	//listOrg.add(orgMajorInfoEntity);
	//Excel列名
  	String[] columnNames = {"年收入", "净资产","主办期刊数量", "设奖个数" ,"个人会员数量", "单位会员数量" ,
  							"分支机构数量", "上一年度未开展活动分支结构数量" ,"是否正常换届", "是否按期召开理事会" ,
  								"是否按期召开常务理事会", "是否正常开展业务活动", "上一年度年检情况" };
  	//每列对应取值方法名
  	String[] methodNames = {"getAnnualIncome" , "getNetIncome","getArticleCounter", "getHonorCounter",
  							"getMemberCounter", "getOrgCounter", "getnaNum", "getunaNum","gettransition", 
  							"getconvene", "getnasc", "getnabiz", "getrenewal"};
  	//导出文件路径名
  	String fileName = "d:/excel.xlsx";
  	// 生成ExcelEntity实体，包含4个必备参数
  	ExcelEntity<OrgMajorInfoEntity> excelEntity = new ExcelEntity<>(fileName, columnNames, methodNames, orgMajorInfoEntityList);
  	//excelEntity.setHeader("学会申报详情");
  	//excelEntity.setFooter("脚注");
  	Workbook excel = 
  			//ExcelExporter.export2Excel(excelEntity);
  	
  	ExcelExporter.export2Excel("学会申报详情","学会申报详情", "学会申报详情", columnNames, methodNames, orgMajorInfoEntityList);//也可以这样调用,无需新建ExcelEntity对象
  	//将Workbook存为文件
  	ExcelExporter.saveWorkBook2007(excel, excelEntity.getFileName());
  	System.out.println("导出完成！已存在"+fileName+"目录下");
  	}
	
	
}

* 表内容开始的行数* @param methodNames* T对象的方法名* @param columnWidths* @param entities* @throws Exception*/private static <T> void createTableContent(Sheet sheet, int rowIndexBegin, String[] methodNames, int[] columnWidths,List<T> entities) throws Exception {Class<? extends Object> clazz = null;if (entities.size() > 0)clazz = entities.get(0).getClass();String content = null;for (T t : entities) {Row row = sheet.createRow(rowIndexBegin++);for (int i = 0; i < methodNames.length; i++) {Cell cell = row.createCell(i);Method method = clazz.getMethod(methodNames[i], null);Object object = method.invoke(t, null);object = object == null ? "" : object;if (object.getClass().equals(Date.class)) {// 对日期格式进行特殊处理SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");content = sdf.format((Date) object);cell.setCellValue(content);} else {content = object.toString();cell.setCellValue(content);}int columnWidth = (content.getBytes().length + 2) * 256;if (columnWidth > columnWidths[i]) {// 如果实际内容宽度大于对应的表头宽度，则设置为实际内容宽度columnWidths[i] = columnWidth;sheet.setColumnWidth(i, columnWidths[i]);}}}}public static <T> void testPOI(String[] columnNames, String[] methodNames, List<T> entities) throws Exception {String sheetName = "Test";String title = "标题栏";String dstFile = "d:/temp/test.xlsx";Workbook newWorkBook2007 = new XSSFWorkbook();Sheet sheet = newWorkBook2007.createSheet(sheetName);int[] columnWidths = new int[columnNames.length];// 创建表头createTableHeader(sheet, 0, title, columnNames, columnWidths);// 填充表内容createTableContent(sheet, 1, methodNames, columnWidths, entities);// 保存为文件saveWorkBook2007(newWorkBook2007, dstFile);System.out.println("end");}/*** 将workbook2007存为文件** @param workbook2007* @param dstFile*/public static void saveWorkBook2007(Workbook workbook2007, String dstFile) {File file = new File(dstFile);OutputStream os = null;try {os = new FileOutputStream(file);workbook2007.write(os);} catch (IOException e) {e.printStackTrace();} finally {if (os != null) {try {os.close();} catch (IOException e) {System.out.println(e);}}}}} 

ExportController.java









                

        

    

    
  



    



            

  



          




        



    





    



      

      

        
      

      





	

		评论	
	

  
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值