使用Apache POI操作Excel

一.POI 介绍

目前常见读写Excel的工具类开源javaAPI有两种方式，
一个是JXL（Java Excel API） 官网地址：http://jexcelapi.sourceforge.net/
一个是Apache的POI（Poor Obfuscation Implementation）官网地址：http://poi.apache.org/

POI支持微软的OLE2格式文件Office 2003及以下版本；同时支持微软的OOXML（Office Open XML）标准，也就是Office 2007以上版本。JXL只能实现对Excel 2003以下版本的支持。

POI使用HSSF对象操作OLE2格式Excel，文件后缀为.xls的；使用XSSF、SXSSF对象操作OOXML格式Excel，文件后缀为.xlsx的。

对于OLE2版本的Excel，一个Sheet工作表它的行最多支持到65536行，列支持到256列；
对于OOXML版本的Excel，一个Sheet工作表它的行支持到1048576行，列支持到16384列。

二.导包

Maven项目,可导入maven依赖:

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.11</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.11</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml-schemas</artifactId>
			<version>3.11</version>
		</dependency>

三.操作Excel实际案例 (出货表的打印)

物流云商系统按船期打印出货表,以下有两种方式:

1.手动创建一张表,将数据写入excel

 	@Action(value="outProductAction_print")
	public String print() throws Exception {
		
		//创建工作簿
		HSSFWorkbook book = new HSSFWorkbook();
		//创建工作表
		HSSFSheet sheet = book.createSheet();
		//设置列宽
		sheet.setColumnWidth(1, 25 * 256);
		sheet.setColumnWidth(2, 10 * 256);
		sheet.setColumnWidth(3, 25 * 256);
		sheet.setColumnWidth(4, 10 * 256);
		sheet.setColumnWidth(5, 10 * 256);
		sheet.setColumnWidth(6, 10 * 256);
		sheet.setColumnWidth(7, 10 * 256);
		sheet.setColumnWidth(8, 10 * 256);
		
		int rowIndex = 0;
		//大标题
		HSSFRow bigTitleRow = sheet.createRow(rowIndex++);
		bigTitleRow.setHeightInPoints(36);
		HSSFCell bigCell = bigTitleRow.createCell(1);
		bigCell.setCellStyle(bigTitle(book));
		//合并单元格
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 8));
		//大标题内容 inputDate 2015年7月
		String titleStr = inputDate.replace("-0", "-").replace("-", "年")+"月份出货表";
		bigCell.setCellValue(titleStr);
		//小标题
		String[] smartStr = {"客户","订单号","货号","数量","工厂","工厂交期","船期","贸易条款"};
		HSSFRow smartRow = sheet.createRow(rowIndex++);
		for(int i=0; i<smartStr.length; i++) {
			HSSFCell smartCell = smartRow.createCell(i+1);
			smartCell.setCellValue(smartStr[i]);	//设置内容
			smartCell.setCellStyle(title(book));	//设置样式
		}
		
		//内容
		
		List<ContractProduct> cpList = contractProductService.findCpByShipTime(inputDate);
		System.out.println("============="+cpList.size());
		HSSFRow contentRow = null;
		HSSFCell cell = null;
		for (ContractProduct cp : cpList) {
			System.out.println(cp.toString());
			contentRow = sheet.createRow(rowIndex++);
			contentRow.setHeightInPoints(26);
			
			//客户
			cell = contentRow.createCell(1);
			cell.setCellValue(cp.getContract().getCustomName());
			cell.setCellStyle(text(book));

			//订单号
			cell = contentRow.createCell(2);
			cell.setCellValue(cp.getContract().getContractNo());
			cell.setCellStyle(text(book));
			
			//货号
			cell = contentRow.createCell(3);
			cell.setCellValue(cp.getProductNo());
			cell.setCellStyle(text(book));
			
			//数量
			cell = contentRow.createCell(4);
			cell.setCellValue(String.valueOf(cp.getCnumber()));
			cell.setCellStyle(text(book));
			
			//工厂
			cell = contentRow.createCell(5);
			cell.setCellValue(cp.getFactoryName());
			cell.setCellStyle(text(book));
			
			//工厂交期
			cell = contentRow.createCell(6);
			cell.setCellValue(UtilFuns.dateTimeFormat(cp.getContract().getDeliveryPeriod()));
			cell.setCellStyle(text(book));
			
			//船期
			cell = contentRow.createCell(7);
			cell.setCellValue(UtilFuns.dateTimeFormat(cp.getContract().getShipTime()));
			cell.setCellStyle(text(book));
			
			//贸易条款
			cell = contentRow.createCell(8);
			cell.setCellValue(cp.getContract().getTradeTerms());
			cell.setCellStyle(text(book));
		}
		
		//写出excel
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		HttpServletResponse response = ServletActionContext.getResponse();
		
		DownloadUtil util = new DownloadUtil();
		book.write(os);
		util.download(os, response, titleStr+".xls");
		
		return NONE;
	}

2.用模板的方式,导入excel文件

下面是模板样式:

代码:

/**
	 * 用模板的方式,导入excel文件
	 * excel 2007
	 * @return
	 * @throws Exception
	 */
	@Action(value="outProductAction_print")
	public String print() throws Exception {
		
		//模板路径       File.separator 根据操作系统不同替换/  windows /  linux \
		String path = "/make/xlsprint/tOUTPRODUCT.xlsx".replace("/", File.separator);
		//文件写出路径
		String filePath = ServletActionContext.getServletContext().getRealPath(path);
		FileInputStream is = new FileInputStream(filePath);
		//获取工作簿
		Workbook book = new XSSFWorkbook(is);
		//获取工作表
		Sheet sheet = book.getSheetAt(0);
		
		
		int rowIndex = 0;
		//大标题
		Row bigTitleRow = sheet.getRow(rowIndex++);
		Cell bigCell = bigTitleRow.getCell(1);
		
		//大标题内容 inputDate 2015年7月
		String titleStr = inputDate.replace("-0", "-").replace("-", "年")+"月份出货表";
		bigCell.setCellValue(titleStr);
		
		//小标题
		rowIndex++;
		
		//内容
		//保存单元格样式
		CellStyle cs01 = sheet.getRow(rowIndex).getCell(1).getCellStyle();
		CellStyle cs02 = sheet.getRow(rowIndex).getCell(2).getCellStyle();
		CellStyle cs03 = sheet.getRow(rowIndex).getCell(3).getCellStyle();
		CellStyle cs04 = sheet.getRow(rowIndex).getCell(4).getCellStyle();
		CellStyle cs05 = sheet.getRow(rowIndex).getCell(5).getCellStyle();
		CellStyle cs06 = sheet.getRow(rowIndex).getCell(6).getCellStyle();
		CellStyle cs07 = sheet.getRow(rowIndex).getCell(7).getCellStyle();
		CellStyle cs08 = sheet.getRow(rowIndex).getCell(8).getCellStyle();
		
		//从数据库获取数据
		List<ContractProduct> cpList = contractProductService.findCpByShipTime(inputDate);
		
		//循环
		for (ContractProduct cp : cpList) {
			
			Row contentRow = sheet.createRow(rowIndex++);
			contentRow.setHeightInPoints(26);
			
			Cell cell01 = contentRow.createCell(1);
			cell01.setCellValue(cp.getContract().getCustomName());
			cell01.setCellStyle(cs01);
			
			Cell cell02 = contentRow.createCell(2);
			cell02.setCellValue(cp.getContract().getContractNo());
			cell02.setCellStyle(cs02);
			
			Cell cell03 = contentRow.createCell(3);
			cell03.setCellValue(cp.getProductNo());
			cell03.setCellStyle(cs03);
			
			Cell cell04 = contentRow.createCell(4);
			cell04.setCellValue(String.valueOf(cp.getCnumber()));
			cell04.setCellStyle(cs04);
			
			Cell cell05 = contentRow.createCell(5);
			cell05.setCellValue(cp.getFactoryName());
			cell05.setCellStyle(cs05);
			
			Cell cell06 = contentRow.createCell(6);
			cell06.setCellValue(UtilFuns.dateTimeFormat(cp.getContract().getDeliveryPeriod()));
			cell06.setCellStyle(cs06);
			
			Cell cell07 = contentRow.createCell(7);
			cell07.setCellValue(UtilFuns.dateTimeFormat(cp.getContract().getShipTime()));
			cell07.setCellStyle(cs07);
			
			Cell cell08 = contentRow.createCell(8);
			cell08.setCellValue(cp.getContract().getTradeTerms());
			cell08.setCellStyle(cs08);
		}
		
		//写出excel
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		HttpServletResponse response = ServletActionContext.getResponse();
		
		DownloadUtil util = new DownloadUtil();
		book.write(os);
		util.download(os, response, titleStr+".xlsx");
		
		return NONE;
	}