一、介绍
常常有客户这样子要求:你要把我们的报表直接用 Excel 打开 (电信系统、银行系统)。或者是:我们已经习惯用 Excel 打印。这样在我们实际的开发中,很多时候需要实现导入、导出 Excel 的应用。
目前,比较常用的实现 Java 导入、导出 Excel 的技术有: Jakarta POI、freemarker 和 Java Excel
下面我就分别讲解一下如何使用这些技术实现导入、导出 Excel
二、使用 Jakarta POI 导入、导出 Excel
Jakarta POI 是一套用于访问微软格式文档的 Java API。Jakarta POI 有很多组件组成,其中有用于操作 Excel 格式文件的 HSSF 和用于操作 Word 的 HWPF,
在各种组件中目前只有用于操作 Excel 的 HSSF 相对成熟。官方主页 http://poi.apache.org/index.html,API 文档 http://poi.apache.org/apidocs/index.html
1. 环境配置
下载 jar
官方下载:http://poi.apache.org/download.html 这里可以下载到它的最新版本和文档,目前最新版本是 3.7,这里使用比较稳定的 3.6 版。
加入 jar 包
将根目录下的 poi-3.6-20091214.jar 和 Lib 目录下三个通用包 commons-logging-1.1.jar junit-3.8.1.jar log4j-1.2.13.jar 拷贝到项目的 Lib 下
2.Jakarta POI HSSF API 组件
HSSF(用于操作 Excel 的组件)提供给用户使用的对象在 rg.apache.poi.hssf.usermodel 包中, 主要部分包括 Excel 对象,样式和格式,还有辅助操作。有以下几种对象:
常用组件:
HSSFWorkbook excel 的文档对象
HSSFSheet excel 的表单
HSSFRow excel 的行
HSSFCell excel 的格子单元
HSSFFont excel 字体
HSSFDataFormat 日期格式
HSSFHeader sheet 头
HSSFFooter sheet 尾(只有打印的时候才能看到效果)
样式:
HSSFCellStyle cell 样式
辅助操作包括:
HSSFDateUtil 日期
HSSFPrintSetup 打印
HSSFErrorConstants 错误信息表
3. 基本操作步骤
首先,理解一下一个 Excel 的文件的组织形式,一个 Excel 文件对应于一个 workbook(HSSFWorkbook),一个 workbook 可以有多个 sheet(HSSFSheet)组成,一个 sheet 是由多个 row(HSSFRow)组成,一个 row 是由多个 cell(HSSFCell)组成。
基本操作步骤:
a、用 HSSFWorkbook 打开或者创建 "Excel 文件对象"
b、用 HSSFWorkbook 对象返回或者创建 Sheet 对象
c、用 Sheet 对象返回行对象,用行对象得到 Cell 对象
d、对 Cell 对象读写。
4. 我自己写的测试用例
View Code
- import java.io.FileOutputStream;
- 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.HSSFFont;
- import org.apache.poi.hssf.usermodel.HSSFRow;
- import org.apache.poi.hssf.usermodel.HSSFSheet;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- import org.apache.poi.hssf.util.Region;
- public class POIExportExcel {
- public HSSFWorkbook generateExcel1(List list, HSSFWorkbook wb, String title) {
- //行的数量
- int rows = 0;
- HSSFSheet sheet = wb.createSheet("Sheet1");
- // 数据格样式
- HSSFCellStyle dataCellStyle = createDataCellStyle(wb);
- // 小标题样式
- HSSFCellStyle cellStyle = createCellStyle(wb);
- sheet.setDefaultRowHeight((short) 400);//设置所有行高为400
- //设置列宽
- sheet.setColumnWidth((short) 1, (short) 2800);
- sheet.setColumnWidth((short) 2, (short) 2800);
- sheet.setColumnWidth((short) 3, (short) 2800);
- sheet.setColumnWidth((short) 4, (short) 2800);
- sheet.setColumnWidth((short) 5, (short) 2800);
- sheet.setColumnWidth((short) 6, (short) 2800);
- sheet.setColumnWidth((short) 7, (short) 2800);
- sheet.setColumnWidth((short) 8, (short) 2800);
- sheet.setColumnWidth((short) 9, (short) 2800);
- sheet.setColumnWidth((short) 10, (short) 3200);
- sheet.setColumnWidth((short) 11, (short) 3200);
- sheet.setColumnWidth((short) 12, (short) 3200);
- sheet.setColumnWidth((short) 13, (short) 3200);
- //标题行
- HSSFRow row1 = sheet.createRow((short) (rows++));
- row1.setHeight((short) 500);
- sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 13));//横跨1-14列
- createCell(row1, (short) 0, cellStyle, title);
- //表头行(1,2列合并;3,4,5,6,7列合并;8,9列合并)
- HSSFRow row2 = sheet.createRow((short) (rows++));
- sheet.addMergedRegion(new Region(1, (short) 0, 1, (short) 1));
- sheet.addMergedRegion(new Region(1, (short) 2, 1, (short) 6));
- sheet.addMergedRegion(new Region(1, (short) 7, 1, (short) 8));
- sheet.addMergedRegion(new Region(1, (short) 9, 1, (short) 9));
- sheet.addMergedRegion(new Region(1, (short) 10, 1, (short) 10));
- sheet.addMergedRegion(new Region(1, (short) 11, 1, (short) 11));
- sheet.addMergedRegion(new Region(1, (short) 12, 1, (short) 12));
- sheet.addMergedRegion(new Region(1, (short) 13, 1, (short) 13));
- createCell(row2, (short) 0, dataCellStyle, "name1");
- createNullCell(row2, (short) 1, dataCellStyle);
- createCell(row2, (short) 2, dataCellStyle, "name2");
- createNullCell(row2, (short) 3, dataCellStyle);
- createNullCell(row2, (short) 4, dataCellStyle);
- createNullCell(row2, (short) 5, dataCellStyle);
- createNullCell(row2, (short) 6, dataCellStyle);
- createCell(row2, (short) 7, dataCellStyle, "name3");
- createNullCell(row2, (short) 8, dataCellStyle);
- createCell(row2, (short) 9, dataCellStyle, "name4");
- createCell(row2, (short) 10, dataCellStyle, "name5");
- createCell(row2, (short) 11, dataCellStyle, "name6");
- createCell(row2, (short) 12, dataCellStyle, "name7");
- createCell(row2, (short) 13, dataCellStyle, "name8");
- //遍历集合,保存数据到单元格
- for(int i=0;i){
- Object javaBean = list.get(i);
- //具体的数据值通过对象去获取,这里简单给值
- String variable1 = "value1"+"_"+i;
- String variable2 = "value2"+"_"+i;
- String variable3 = "value3"+"_"+i;
- String variable4 = "value4"+"_"+i;
- String variable5 = "value5"+"_"+i;
- String variable6 = "value6"+"_"+i;
- String variable7 = "value7"+"_"+i;
- String variable8 = "value8"+"_"+i;
- HSSFRow rowi = sheet.createRow((short) (rows++));
- sheet.addMergedRegion(new Region(i+2, (short) 0, i+2, (short) 1));
- sheet.addMergedRegion(new Region(i+2, (short) 2, i+2, (short) 6));
- sheet.addMergedRegion(new Region(i+2, (short) 7, i+2, (short) 8));
- sheet.addMergedRegion(new Region(i+2, (short) 9, i+2, (short) 9));
- sheet.addMergedRegion(new Region(i+2, (short) 10, i+2, (short) 10));
- sheet.addMergedRegion(new Region(i+2, (short) 11, i+2, (short) 11));
- sheet.addMergedRegion(new Region(i+2, (short) 12, i+2, (short) 12));
- sheet.addMergedRegion(new Region(i+2, (short) 13, i+2, (short) 13));
- createCell(rowi, (short) 0, dataCellStyle, variable1);
- createNullCell(rowi, (short) 1, dataCellStyle);
- createCell(rowi, (short) 2, dataCellStyle, variable2);
- createNullCell(rowi, (short) 3, dataCellStyle);
- createNullCell(rowi, (short) 4, dataCellStyle);
- createNullCell(rowi, (short) 5, dataCellStyle);
- createNullCell(rowi, (short) 6, dataCellStyle);
- createCell(rowi, (short) 7, dataCellStyle, variable3);
- createNullCell(rowi, (short) 8, dataCellStyle);
- createCell(rowi, (short) 9, dataCellStyle, variable4);
- createCell(rowi, (short) 10, dataCellStyle, variable5);
- createCell(rowi, (short) 11, dataCellStyle, variable6);
- createCell(rowi, (short) 12, dataCellStyle, variable7);
- createCell(rowi, (short) 13, dataCellStyle, variable8);
- }
- return wb;
- }
- /**
- * 创建单元格
- * @param row
- * @param id
- * @param style
- * @param value
- */
- private void createCell(HSSFRow row, short id, HSSFCellStyle style,
- String value) {
- HSSFCell cell = row.createCell((short) id);
- // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
- cell.setCellValue(value);
- cell.setCellStyle(style);
- }
- /**
- * 创建空的单元格
- */
- private HSSFCell createNullCell(HSSFRow row, short id, HSSFCellStyle style) {
- HSSFCell null1Cell = row.createCell(id);
- null1Cell.setCellStyle(style);
- return null1Cell;
- }
- /**
- * 设置数据单元格样式
- * @param wb
- * @return
- */
- private HSSFCellStyle createDataCellStyle(HSSFWorkbook wb) {
- HSSFCellStyle dataCellStyle = wb.createCellStyle();
- // 水平居中
- dataCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
- dataCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
- dataCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
- dataCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
- dataCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
- return dataCellStyle;
- }
- /**
- * 设置单元格样式
- * @param wb
- * @return
- */
- private HSSFCellStyle createCellStyle(HSSFWorkbook wb) {
- // 小标题样式
- HSSFCellStyle cellStyle = wb.createCellStyle();
- // 水平居中
- cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
- // 字体
- HSSFFont littleFont = wb.createFont();
- // 设置字体为粗体
- littleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
- // 然后将字体关联到样式
- cellStyle.setFont(littleFont);
- cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
- cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
- cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
- cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
- cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
- return cellStyle;
- }
- public static void main(String[] args){
- /*String serverPath = request.getSession().getServletContext().getRealPath("/")+"exportExcel";
- //在服务器端创建文件夹
- File file = new File(serverPath+path);
- if(!file.exists()){
- file.mkdir();
- }
- SimpleDateFormat sfm = new SimpleDateFormat("yyyy-MM-dd");
- String filename = "excel" + "_" + sfm.format(new java.util.Date());
- String encodedfileName = new String(filename.getBytes(), "GBK");
- FileOutputStream out = new FileOutputStream(serverPath+path+"/"+encodedfileName+".xls"); */
- new POIExportExcel();
- String title = "POI导出excel";
- List list = new ArrayList();
- //加入两条做个测试
- list.add(new String());
- list.add(new String());
- try {
- FileOutputStream out = new FileOutputStream("D:/"+title+".xls");
- HSSFWorkbook wb =new HSSFWorkbook();
- wb = new POIExportExcel().generateExcel1(list,wb,title);
- wb.write(out);
- /*List srcfile=new ArrayList();
- srcfile.add(new File(serverPath+path+"/"+encodedfileName+".xls"));
- //将服务器上存放Excel的文件夹打成zip包
- File zipfile = new File(serverPath+path+".zip");
- ZipUtil.zipFiles(srcfile, zipfile);
- //弹出下载框供用户下载
- this.downFile(response,serverPath, path+".zip");*/
- } catch (Exception e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
- }
结果如下图:
三、使用 freemarker 导出 excel
参考文章:
来源: