Java中使用poi导出excel

pom.xml文件中添加如下配置引入jar包

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.6</version>
</dependency>

常用组件：

HSSFWorkbook excel的文档对象

HSSFSheet excel的表单

HSSFRow excel的行

HSSFCell excel的格子单元

HSSFFont excel字体

样式：

HSSFCellStyle cell样式

实例 只是简单的实现了功能 没有写通用的方法 建议写个通用方法

		//创建excle对象
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook();

		//创建sheet对象 参数是sheet的名字
		HSSFSheet sheet= hssfWorkbook.createSheet("根据客户统计");
		//这里是创建行对象
		HSSFRow createRow0 = sheet.createRow(0);
		//然后根据行对象创建单元格对象
		HSSFCell createRow0Cell0 = createRow0.createCell(0);
		//设置单元格对象的内容
		createRow0Cell0.setCellValue("根据客户统计");
		//合并单元格 参数 起始行数 结束行数 起始列数 结束列数
		CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 4);
        sheet.addMergedRegion(cellRangeAddress);
        
        HSSFRow createRow1 = sheet.createRow(1);
		HSSFCell createRow1Cell0 = createRow1.createCell(0);
		createRow1Cell0.setCellValue("日期:"+starttime+"-"+endtime);
		cellRangeAddress = new CellRangeAddress(1, 1, 0, 4);
        sheet.addMergedRegion(cellRangeAddress);
        
        HSSFRow createRow2 = sheet.createRow(2);
		HSSFCell createRow2Cell0 = createRow2.createCell(0);
		createRow2Cell0.setCellValue("客户名称");
		HSSFCell createRow2Cell1 = createRow2.createCell(1);
		createRow2Cell1.setCellValue("订单总金额");
		HSSFCell createRow2Cell2 = createRow2.createCell(2);
		createRow2Cell2.setCellValue("应收款金额");
		HSSFCell createRow2Cell3 = createRow2.createCell(3);
		createRow2Cell3.setCellValue("实收款金额");
		HSSFCell createRow2Cell4 = createRow2.createCell(4);
		createRow2Cell4.setCellValue("物流金额");
		//这里是循环要导出的数据
		List<SO_SalesOrderInfo> statisticalSalesByCustomer = statisticalInfoMapper.toStatisticalSalesByCustomer(starttime, endtime, null, keyword);
		for (int i = 0, n = statisticalSalesByCustomer.size(); i < n; i++) {
			HSSFRow createRowi = sheet.createRow(3+i);
			HSSFCell createRowiCell0 = createRowi.createCell(0);
			createRowiCell0.setCellValue(statisticalSalesByCustomer.get(i).getCustomernameinfo() != null 
					? statisticalSalesByCustomer.get(i).getCustomernameinfo() : DEFAULT_NAME);
			HSSFCell createRowiCell1 = createRowi.createCell(1);
			createRowiCell1.setCellValue(statisticalSalesByCustomer.get(i).getTotalmoney() != null 
					? statisticalSalesByCustomer.get(i).getTotalmoney() : DEFAULT_QUANTITY_OR_MONEY);
			HSSFCell createRowiCell2 = createRowi.createCell(2);
			createRowiCell2.setCellValue(statisticalSalesByCustomer.get(i).getAmountreceivable() != null 
					? statisticalSalesByCustomer.get(i).getAmountreceivable() : DEFAULT_QUANTITY_OR_MONEY);
			HSSFCell createRowiCell3 = createRowi.createCell(3);
			createRowiCell3.setCellValue(statisticalSalesByCustomer.get(i).getAmountreceived() != null 
					? statisticalSalesByCustomer.get(i).getAmountreceived() : DEFAULT_QUANTITY_OR_MONEY);
			HSSFCell createRowiCell4 = createRowi.createCell(4);
			createRowiCell4.setCellValue(statisticalSalesByCustomer.get(i).getLogisticsamount() != null 
					? statisticalSalesByCustomer.get(i).getLogisticsamount() : DEFAULT_QUANTITY_OR_MONEY);
		}
		//这里是在最后一行添加合计 sheet.getLastRowNum()可以获得当前sheet共有多少行数
		HSSFRow createLastRow = sheet.createRow(sheet.getLastRowNum()+1);
		HSSFCell createLastRowCell0 = createLastRow.createCell(0);
		createLastRowCell0.setCellValue("合计");
		HSSFCell createLastRowCell1 = createLastRow.createCell(1);
		//这里是添加excel中的函数
		createLastRowCell1.setCellFormula("sum(B4:B"+sheet.getLastRowNum()+")");
		HSSFCell createLastRowCell2 = createLastRow.createCell(2);
		createLastRowCell2.setCellFormula("sum(C4:C"+sheet.getLastRowNum()+")");
		HSSFCell createLastRowCell3 = createLastRow.createCell(3);
		createLastRowCell3.setCellFormula("sum(D4:D"+sheet.getLastRowNum()+")");
		HSSFCell createLastRowCell4 = createLastRow.createCell(4);
		createLastRowCell4.setCellFormula("sum(E4:E"+sheet.getLastRowNum()+")");
		
		//样式设置
		HSSFCellStyle style=hssfWorkbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
		style.setDataFormat(hssfWorkbook.createDataFormat().getFormat("#,##0.00"));//数字格式化
		int rowNum = sheet.getLastRowNum();//获得总行数
		int coloumNum=sheet.getRow(2).getPhysicalNumberOfCells();//获得总列数
		for(int j=0;j<=rowNum;j++){
	        for(int k=0;k<coloumNum+2;k++){
	        	HSSFRow tempRow = sheet.getRow(j);
	        	if(tempRow!=null){
	        	HSSFCell cell_temp = tempRow.getCell(k);
	        	if(cell_temp!=null){
	        		cell_temp.setCellStyle(style);
	        		}
	        	}

	        }
	    }

		//导出
		ImportExcelUtils.outPutExcel(response, hssfWorkbook, "统计报表");

用到的工具类

import java.io.IOException;
import java.io.OutputStream;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import www.pdwy.utils.log.Log;
import www.pdwy.utils.log.LogFactory;

/**
 * 统计导出Excel工具类
 */
public class ImportExcelUtils {
	
	private static final Log log = LogFactory.getLog(ImportExcelUtils.class);
	
	private ImportExcelUtils() {
		throw new AssertionError("工具类不应该被实例化");
	}
	
	/**
	 * @param hssfWorkbook excel的文档对象
	 * @param excleName excel文档的名称
	 * @return
	 */
	public static void outPutExcel(HttpServletResponse response,HSSFWorkbook hssfWorkbook,String excleName){
		
		OutputStream outputStream= null;
		//输出Excel文件           
        try {
        	outputStream=response.getOutputStream();  
            response.reset();  
            response.setHeader("Content-disposition", "attachment; filename="+toUtf8String(excleName+".xls")); 
            response.setContentType("application/msexcel"); 
            hssfWorkbook.write(outputStream);
            outputStream.close();
		} catch (IOException e) {
			e.printStackTrace();
			log.debug("销售汇总导出IOException:" + e.getMessage());
			} finally {
				if (outputStream != null) {
					try {
						outputStream.close();
					} catch (IOException e) {
						e.printStackTrace();
						log.debug("销售汇总导出IOException:" + e.getMessage());
					}
				}
			}
		
	}

	/**
	 * 解决中文乱码方法
	 */
	private static String toUtf8String(String s) {
		StringBuffer sb = new StringBuffer(); 
	       for (int i=0, n = s.length();i<n;i++){ 
	          char c = s.charAt(i); 
	          if (c >= 0 && c <= 255){sb.append(c);} 
	        else{ 
	        byte[] b; 
	         try { b = Character.toString(c).getBytes("utf-8");} 
	         catch (Exception ex) { 
	             System.out.println(ex); 
	                  b = new byte[0]; 
	         } 
	            for (int j = 0, l = b.length; j < l; j++) { 
	             int k = b[j]; 
	              if (k < 0) k += 256; 
	              sb.append("%" + Integer.toHexString(k).toUpperCase()); 
	              } 
	     } 
	  } 
	  return sb.toString();
	}

}