poi导出excel 损坏_POI的Excel导出

poi的excel导出

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

添加pom的gav坐标

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.10-FINAL</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.10-FINAL</version>
</dependency>

添加工具类

ExportExcelUtil

package com.sxt.utils;

import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileInputStream;

public class ExportExcelUtil {
	private CellStyle cs;
	/**
	 * 描述：根据文件路径获取项目中的文件
	 * 
	 * @param fileDir
	 *            文件路径
	 * @param string 
	 * @return
	 * @throws Exception
	 */
	public File getExcelTplFile(String path, String filePath) throws Exception {
		String classDir = null;
		String fileBaseDir = null;
		File file = null;
		/*classDir = Thread.currentThread().getContextClassLoader()
				.getResource("").getPath();
		System.out.println(classDir);
		fileBaseDir = classDir.substring(1, classDir.lastIndexOf("classes")).replaceAll("%20", " ");*/
		file = new File(path+filePath);
		if (!file.exists()) {
			throw new Exception("模板文件不存在！");
		}
		return file;
	}
	/**
	 * 创建工作簿
	 * @param file
	 * @return
	 * @throws Exception
	 */
	public Workbook getWorkbook(File file) throws Exception {
		FileInputStream fis = new FileInputStream(file);
		Workbook wb = new ImportExcelUtil().getWorkbook(fis, file.getName()); // 获取工作薄
		return wb;
	}

	/**
	 * 创建Sheet
	 * @param wb
	 * @param sheetName
	 * @return
	 * @throws Exception
	 */
	public Sheet getSheet(Workbook wb, String sheetName) throws Exception {
		cs = setSimpleCellStyle(wb); // Excel单元格样式
		Sheet sheet = wb.getSheet(sheetName);
		return sheet;
	}

	/**
	 * 创建Row
	 * @param sheet
	 * @param rowNum
	 * @return
	 * @throws Exception
	 */
	public Row createRow(Sheet sheet) throws Exception {
		// 循环插入数据
		int lastRow = sheet.getLastRowNum() + 1; // 插入数据的数据ROW
		Row row = sheet.createRow(lastRow);
		return row;
	}
	/**
	 * 创建Cell
	 * @param row
	 * @param CellNum
	 * @return
	 * @throws Exception
	 */
	public Cell createCell(Row row, int CellNum) throws Exception {
		Cell cell = row.createCell(CellNum);
		cell.setCellStyle(cs);
		return cell;
	}

	/**
	 * 描述：设置简单的Cell样式
	 * 
	 * @return
	 */
	public CellStyle setSimpleCellStyle(Workbook wb) {
		CellStyle cs = wb.createCellStyle();

		cs.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
		cs.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
		cs.setBorderTop(CellStyle.BORDER_THIN);// 上边框
		cs.setBorderRight(CellStyle.BORDER_THIN);// 右边框

		cs.setAlignment(CellStyle.ALIGN_CENTER); // 居中

		return cs;
	}

}

ImportExcelUtil

package com.sxt.utils;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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;


public class ImportExcelUtil {
	
	private final static String excel2003L =".xls";    //2003- 版本的excel
	private final static String excel2007U =".xlsx";   //2007+ 版本的excel
	
	/**
	 * 描述：获取IO流中的数据，组装成List<List<Object>>对象
	 * @param in,fileName
	 * @return
	 * @throws IOException 
	 */
	public  List<List<Object>> getListByExcel(InputStream in,String fileName) throws Exception{
		List<List<Object>> list = null;
		
		//创建Excel工作薄
		Workbook work = this.getWorkbook(in,fileName);
		if(null == work){
			throw new Exception("创建Excel工作薄为空！");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		
		list = new ArrayList<List<Object>>();
		//遍历Excel中所有的sheet
		for (int i = 0; i < work.getNumberOfSheets(); i++) {
			sheet = work.getSheetAt(i);
			if(sheet==null){continue;}
			
			//遍历当前sheet中的所有行
			for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum()+1; j++) {
				row = sheet.getRow(j);
				if(row==null||row.getFirstCellNum()==j){continue;}
				
				//遍历所有的列
				List<Object> li = new ArrayList<Object>();
				for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
					cell = row.getCell(y);
					li.add(this.getCellValue(cell));
				}
				list.add(li);
			}
		}
		return list;
	}
	
	/**
	 * 描述：根据文件后缀，自适应上传文件的版本 
	 * @param inStr,fileName
	 * @return
	 * @throws Exception
	 */
	public  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
		Workbook wb = null;
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		if(excel2003L.equals(fileType)){
			wb = new HSSFWorkbook(inStr);  //2003-
		}else if(excel2007U.equals(fileType)){
			wb = new XSSFWorkbook(inStr);  //2007+
		}else{
			throw new Exception("解析的文件格式有误！");
		}
		return wb;
	}

	/**
	 * 描述：对表格中数值进行格式化
	 * @param cell
	 * @return
	 */
	public  Object getCellValue(Cell cell){
		Object value = null;
		DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
		SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化
		DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字
		
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			value = cell.getRichStringCellValue().getString();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if("General".equals(cell.getCellStyle().getDataFormatString())){
				value = df.format(cell.getNumericCellValue());
			}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
				value = sdf.format(cell.getDateCellValue());
			}else{
				value = df2.format(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			value = cell.getBooleanCellValue();
			break;
		case Cell.CELL_TYPE_BLANK:
			value = "";
			break;
		default:
			break;
		}
		return value;
	}
	

}

创建excel导出模板放到freight-web/webapp

在page.jsp修改导出按钮

<a href="javascript:exportDept()"><img
        src="/images/table/xls.gif" style="border:0" title="Export Excel"
        alt="Excel"/>
</a>

jDeptList.jsp

function exportDept(){
    $("#frm").prop("action","${ctx}/dept/export");
    $("#frm").submit();
}

修改DeptController

@RequestMapping("export")
public void export(PageBean pageBean, Model model, HttpServletRequest request, HttpServletResponse response) throws Exception {
    //1、查询要导出的数据
    PageBean pb = deptService.listDeptOfPage(pageBean);
    List<DeptPVo> datas = (List<DeptPVo>) pb.getDatas();

    //2、把数据填充到excel里
    //获取excel导出模板
    ExportExcelUtil excelUtil = new ExportExcelUtil();
    String realPath = request.getSession().getServletContext().getRealPath("/");
    String filePath = "/tpl/dept_export.xlsx";
    File excelTplFile = excelUtil.getExcelTplFile(realPath, filePath);
    //工作簿
    Workbook workbook = excelUtil.getWorkbook(excelTplFile);
    //sheet
    Sheet sheet = excelUtil.getSheet(workbook,"部门信息");
    for (int i = 0; i < datas.size(); i++) {
        DeptPVo deptPVo =  datas.get(i);
        //行
        Row row = excelUtil.createRow(sheet);
        Cell cell0 = excelUtil.createCell(row,0);
        cell0.setCellValue(deptPVo.getDeptNo());

        Cell cell1 = excelUtil.createCell(row,1);
        cell1.setCellValue(deptPVo.getParentName());

        Cell cell2 = excelUtil.createCell(row,2);
        cell2.setCellValue(deptPVo.getDeptName());

        Cell cell3 = excelUtil.createCell(row,3);
        cell3.setCellValue(deptPVo.getState()==1?"启用":"停用");
    }

    //把写好的excel发送到客户端
    String fileName="dept_list.xlsx";
    response.setContentType("application/ms-excel");
    response.setHeader("Content-disposition", "attachment;filename="+fileName);
    ServletOutputStream ouputStream = response.getOutputStream();
    workbook.write(ouputStream);
    ouputStream.flush();
    ouputStream.close();

}