poi导出excel数据

左手的ㄟ右手 2022-08-05 05:09 397阅读 0赞

POI介绍

Apache POI是[Apache][][软件][Link 1]基金会的开放源码函式库，是用Java编写的免费开源的跨平台的 Java API，POI提供API给Java程序对Microsoft Office格式档案进行各种操作。

POI主要用到的类有HSSFWorkbook、HSSFSheet、HSSHRow、HSSFCell，HSSFWorkbook是Excel文件对象、HSSFSheet是Excel文件内的分页sheet对象、HSSHRow是行对象、HSSFCell是单元格对象，它们都在org.apache.poi.hssf.usermodel这个package里面。  
下面结合以上提及的四个类，介绍下POI对Excel文件的四个基本操作。

**读操作**

首先HSSFWorkbook要获取到文件的流，如HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(new File(“f:\\\\xx.xls”)))；

然后通过wb对象对xx.xls Excel文件进行操作。

HSSFSheet是获取Excel的sheet分页，有两种方式可以获取sheet分页对象：

1.按分页名称来获取 wb.getsheet(“Name”)；

2.按分页顺序来获取 wb.getsheet(0)；

HSSHRow获取行对象，HSSHRow row = sheet.getRow(0);Excel文件行数是从0开始。最后获取到单元格对象HSSFCell cell = row.getCell(index);index是列数，也是从0开始。

HSSFCell通过cell.getCellType()获取单元格类型，有三种类型，0表示数字、1表示文本、2表示存入了公式，并提供方法获取到想要的信息：获取字符串cell.getRichStringCellValue()、获取数值cell.getNumericCellValue()、获取公式cell.getCellFormula()，这里需要注意的是空格是属于文本格式。

**写操作**

POI对Excel的写操作与读操作用到的对象都是这么几个。不同的是读操作是获取对象，而写操作是创建对象。

首先创建HSSFSheet对象，HSSFSheet sheet = wb.createSheet(“Name”)；然后创建行对象HSSHRow row = sheet.createRow(rownum)，rownum是第几行、从0开始算；再创建单元格对象HSSFCell cell = row.createCell(colnum)，colnum是列数，从0开始，再对单元格赋值，文本型用cell.setCellValue(new HSSFRichTextString(“”))、数值用cell.setCellValue(16)；最后把wb写入到一个输出流中，wb.write(new FileOutputStream(new File(path)))，就生成了一个Excel文件了。

POI对Excel的高级操作

以上简单介绍了POI的四个基本操作，虽然已能满足一般的功能要求，但对内容有格式要求的操作来说，就需要用HSSFCellStyle、HSSFDataFormat、HSSFFont这三个类，它们用来对单元格格式/样式进行操作。

**单元格样式**

对单元格样式的操作包括两个部分:创建单元格样式对象、创建字体对象。

创建单元格样式对象：HSSFCellStyle style =wb.createCellStyle()，设置边框(上、下、左、右)：style.setBorderTop((short) 1)、style.setBorderBottom((short) 1)、style.setBorderLeft((short) 1)、style.setBorderRight((short) 1)。

在遇到合并单元格时，还要设置垂直居中，垂直：style.setVerticalAlignment(HSSFCellStyle.VERTICAL\_CENTER)、居中：style.setAlignment(HSSFCellStyle.ALIGN\_CENTER)。

创建字体对象HSSFFont font = wb.createFont()，一般会用到一下三个方法 ，

setFontName()设置字体、setFontHeightInPoints()设置字号、setBoldweight

(HSSFFont.BOLDWEIGHT\_BOLD)加粗，style.setFont(font)保存刚才新建的字体样式。

**合并单元格**

合并单元格需要CellRangeAddress这个类，新建合并单元格对象c = new CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)，保存到Excel文件中sheet.addMergedRegion(c)。合并单元格后的值为合并区域的第一行第一列的值。

**写入公式**

在做财务报表时，还需要往单元格里填公式，用得最多的公式就是求和。POI公式的写入首先要设置下单元格为公式类型，cell.setCellType(HSSFCell.

CELL\_TYPE\_FORMULA)，然后写入公式表达式就OK了，cell.setCellFormula

(expression)，如expression = "SUM(C"+(i+1)+":F"+(i+1)+")"，写入表达式需要注意的是公式的开始行是1，而程序里读取的行是从0开始的，所以在写入公式时，要把当前行\+1再写入。

**单元格格式**

在合计的结果比较大时，还要格式化下数据，转换为英文的数字格式xxx,xxx,xxx，这种的格式转换就要用到HSSFDataFormat类，HSSFDataFormat df = wb.createDataFormat()，设置格式style.setDataFormat(df.getFormat("\#,\#\#0.00"))。

一些常用的格式还有"0.00%"、"d-mmm-yy"、"h:mm:ss AM/PM"等。

导出数据的步骤：

1》从数据库中读取数据，分页读取，这样会增加读取数据库和写入excel的效率

导出数据过多时，采用边读数据，边写数据，而非先读完所有数据在写数据，因为读数据的时候会因为内存不够用，写数据的时候也会导致内存不够用，

采用分excel读取然后打包的形式和非创建多个sheet的形式存放读出的数据，因为创建单元格的内存不会释放，会导致内存不够用的情况。

String sql = "……";
 	Long pageNum = count%pageSize==0?count/pageSize:count/pageSize+1;//一共有多少页数据
 	List<String> fileNames = new ArrayList<String>();// 用于存放生成的多个excel文件名称
 	for(int i=0;i<pageNum;i++){
 		list=this.commonService.findSql(sql,i*pageSize,pageSize);分页查询的数据结果
 		logUtil.exportData(commonService,list,fileNames,i,pageNum-1);//将查询的结果写入到excel文件中
 	}

<pre name="code" class="java">public String exportData(CommonService commonService,List<Object[]> list,List<String> fileNames,int num,Long countNum) throws Exception{
 			ExportUtils export = new ExportUtils();
 			export.setAutoSizeColumn(true);
 			String fileName ="";//excel文件名称
 			Object[] titles ={表头的名字};
 			if(list!=null && list.size()>0){
 				List<Object[]> rowList = new ArrayList<Object[]>();
 				int maxLenght = 0;
 				fileName ="测试导出文件";
 				for(Object[] object :list){
 					Object[] dataRow = new Object[18];
 					dataRow[0] = object[0];
 					dataRow[1]=object[1];
 					dataRow[2]=object[2];
 					dataRow[3] =object[3];
 					dataRow[4]=object[4];
 					rowList.add(dataRow);
 				}
 
 				HttpServletResponse res = response == null ? ServletActionContext.getResponse() : response;
 		 OutputStream out = null; 
 		 out = res.getOutputStream();
 				export.toExcel(commonService,rowList,ServletActionContext.getRequest(),fileName,titles,out,fileNames,num,countNum);	
 			}
 			return null;
 		}

public void toExcel(CommonService commonService,List<Object[]> list, HttpServletRequest request, 
 String f, Object[] titles,OutputStream out,List<String>fileNames,int j,Long countNum) throws IOException { 
 HSSFWorkbook book = new HSSFWorkbook(); 
 HSSFSheet sheet = book.createSheet(); 	
 double d = 0;// 用来统计 
 String file = request.getRealPath("/") + f + "-" + j + ".xls"; 
 fileNames.add(file); 
 FileOutputStream o = null; 
 try { 
 o = new FileOutputStream(file); 
 HSSFRow row = sheet.createRow(0); 
 HSSFCellStyle dateStyle = book.createCellStyle();
 		dateStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
 		dateStyle.setWrapText(true);
 		HSSFDataFormat formatDate = book.createDataFormat();
 		dateStyle.setDataFormat(formatDate.getFormat("yyy-mm-dd hh:mm:ss"));
 for(int m=0;m<titles.length;m++){ 
 	 if (titles[m] != null) {
 			 commonService.createCell(row, dateStyle, m, titles[m].toString());
 		 }
 }
 for (int i = 1; i <=list.size(); i++) { 
 // 构造每行数据
 			HSSFRow hssfRow = null;
 			Object[] objects =(Object[])list.get(i-1);
 			hssfRow = sheet.createRow(i);
 			hssfRow.setHeight((short) 600);
 			for(int p=0;p<titles.length;p++){
 				createCell(hssfRow, dateStyle, p, objects[p]);
 			}
 } 
 } catch (Exception e) { 
 e.printStackTrace(); 
 } 
 try { 
 book.write(o); 
 } catch (Exception ex) { 
 ex.printStackTrace(); 
 } finally { 
 o.flush(); 
 o.close(); 
 } 
 if(j==countNum){
 	Date date = new Date(); 
 SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss"); 
 f = f + "_" + format.format(date); 
 File zip = new File(request.getRealPath("/") + f + ".zip");// 压缩文件 
 	setResponseHeader(response == null ? ServletActionContext
 				.getResponse() : response,f); 
 	File srcfile[] = new File[fileNames.size()]; 
 for (int i = 0, n = fileNames.size(); i < n; i++) { 
 srcfile[i] = new File(fileNames.get(i)); 
 } 
 ZipFiles(srcfile, zip); 
 for(File fs:srcfile){
 	if(fs.exists()){
 		fs.delete();
 	}
 }
 FileInputStream inStream = new FileInputStream(zip); 
 byte[] buf = new byte[4096]; 
 int readLength; 
 while (((readLength = inStream.read(buf)) != -1)) { 
 out.write(buf, 0, readLength); 
 } 
 inStream.close(); 
 if(zip.exists()){
 	zip.delete();
 }
 }
 
 }

//表头的数据写入的excel的单元格

/**
    	 * 
    	 * 构建单元格 并转化格式
    	 * 
    	 * @param row
    	 *            行
    	 * @param cellStyle
    	 *            单元格样式
    	 * @param column
    	 *            列数
    	 * @param value
    	 *            单元格值
    	 */
    	public  void createCell(HSSFRow row, HSSFCellStyle cellStyle,
    			int column, Object value) {
    		if(null==value){
    			value = "";
    		}
    		HSSFCell cell = row.createCell((short)column);
    		if (value instanceof Double) {
    			cell.setCellValue((Double) value);
    			cell.setCellStyle(cellStyle);
    		} else if (value instanceof Float) {
    			cell.setCellValue((Float) value);
    			cell.setCellStyle(cellStyle);
    		}  else if (value instanceof Long) {
    			cell.setCellValue((Long) value);
    			cell.setCellStyle(cellStyle);
    		} else if (value instanceof Date) {
    			cell.setCellValue(DateUtil.getFormatDate((Date)value, DateUtil.YYYY_MM_DD_HH24_MM_SS));
    			cell.setCellStyle(cellStyle);
    		} else if (value instanceof Calendar) {
    			cell.setCellValue((Calendar) value);
    			cell.setCellStyle(cellStyle);
    		} else if (value instanceof Boolean) {
    			cell.setCellValue((Boolean) value);
    			cell.setCellStyle(cellStyle);
    		} else if (value instanceof BigDecimal) {
    			cell.setCellValue((String) ((BigDecimal) value).toString());
    			cell.setCellStyle(cellStyle);
    		} else if (value instanceof Character) {
    			cell.setCellValue((String) ((Character) value).toString());
    			cell.setCellStyle(cellStyle);
    		} else {
    			cell.setCellValue(value.toString());
    			cell.setCellStyle(cellStyle);
    		}
    	}

//从数据库中读取的数据的创建的单元格（不知道为什么表头的会错）

public  void createCell(HSSFRow row, HSSFCellStyle cellStyle,
    			int column, Object value) {
    		if(null==value){
    			value = "";
    		}
    		HSSFCell cell = row.createCell((short)column);
    		if (value instanceof Double) {
    			cell.setCellValue((Double) value);
    			cell.setCellStyle(cellStyle);
    		} else if (value instanceof Float) {
    			cell.setCellValue((Float) value);
    			cell.setCellStyle(cellStyle);
    		}  else if (value instanceof Long) {
    			cell.setCellValue((Long) value);
    			cell.setCellStyle(cellStyle);
    		} else if (value instanceof Date) {
    			cell.setCellValue(DateUtil.getFormatDate((Date)value, DateUtil.YYYY_MM_DD_HH24_MM_SS));
    			cell.setCellStyle(cellStyle);
    		} else if (value instanceof Calendar) {
    			cell.setCellValue((Calendar) value);
    			cell.setCellStyle(cellStyle);
    		} else if (value instanceof Boolean) {
    			cell.setCellValue((Boolean) value);
    			cell.setCellStyle(cellStyle);
    		} else if (value instanceof BigDecimal) {
    			cell.setCellValue((String) ((BigDecimal) value).toString());
    			cell.setCellStyle(cellStyle);
    		} else if (value instanceof Character) {
    			cell.setCellValue((String) ((Character) value).toString());
    			cell.setCellStyle(cellStyle);
    		} else {
    			cell.setCellValue(value.toString());
    			cell.setCellStyle(cellStyle);
    		}
    	}

2》将导出的多个excel文件打包的代码：

public static void ZipFiles(java.io.File[] srcfile, java.io.File zipfile) { 
 byte[] buf = new byte[1024]; 
 try { 
 ZipOutputStream out = new ZipOutputStream(new FileOutputStream( 
 zipfile)); 
 out.setEncoding("GBK");
 for (int i = 0; i < srcfile.length; i++) { 
 FileInputStream in = new FileInputStream(srcfile[i]); 
 out.putNextEntry(new ZipEntry(srcfile[i].getName())); 
 int len; 
 while ((len = in.read(buf)) > 0) { 
 out.write(buf, 0, len); 
 } 
 out.closeEntry(); 
 in.close(); 
 } 
 out.close(); 
 } catch (IOException e) { 
 e.printStackTrace(); 
 } 
 }

3》要想让导出的文件时以下载的方式（即弹框的形式出现），则需要设置

/** 设置响应头 */  
        public void setResponseHeader(HttpServletResponse response,String fileName) {  
            try {  
                response.setContentType("application/octet-stream;charset=UTF-8");  
                response.setHeader("Content-Disposition", "attachment;filename="  
                        + java.net.URLEncoder.encode(fileName, "UTF-8")  
                        + ".zip");  
    //            response.setHeader("Content-Disposition", "attachment; filename=\""
    //    				+ URLEncoder.encode(fileName+".zip", "UTF-8") + "\"");
                response.addHeader("Pargam", "no-cache");  
                response.addHeader("Cache-Control", "no-cache");  
            } catch (Exception ex) {  
                ex.printStackTrace();  
            }  
        }

还可以在配置文件中配置；

<action name="writingBakExcel" class="导出excel的action类">
 	<result name="success" type="stream">
 		<param name="contentType">application/vnd.ms-excel</param>
 		<param name="contentDisposition">attachment;filename="${downloadFileName}"</param>
 		<param name="inputName">ExcelFile</param>
 	</result>
 </action>

注： 在使用zip 压缩文件时，要使用Apache下的工具类，java的工具类，会出现压缩的文件中文名乱码的问题

[Apache]: http://baike.baidu.com/view/28283.htm
[Link 1]: http://baike.baidu.com/view/37.htm