java Excel文件读写-2

1.实现流程

 使用POI对Excel文件进行读写操作，同时需要导入poi-3.17.jar包。

2.代码实现

 此处介绍xls格式的文件写操作，实现Excel的下载操作。首先，创建工作簿对象，然后对工作簿进行操作，将从数据库中读取的数据写入工作簿，并将完成写操作的工作簿写入输出流，通过前台浏览器下载页面进行下载。

2.1 前台JS调用：

location.href=url;//URL指向下载资源（此处为目标文件的输出数据流）

2.2 Controller层操作：

@Autowired
private HttpServletResponse response;
@Autowired
private ProductService productService;
/**
 * 
 * @param 导出产品信息Excel
 * @param yp
 * @return
 */
@ResponseBody
@RequestMapping(value = "/expExcel") 
public Boolean expExcel() {
	Boolean flag;
	flag = productService.EXPExcel(response);
	return flag;
}

2.3 Service层Excel操作：

ProductService.java文件：

@Autowired
private ProductMapper productMapper;
@Autowired
private ProductTypeMapper productTypeMapper;
@Autowired
private SubmissionUnitMapper submissionUnitMapper;
@Autowired
private CommonService commonService;

/**
 * 
 * @param 产品信息导出Excel
 * @param yp
 * @return
 */
public Boolean EXPExcel(HttpServletResponse response) {
	List < Product > list = new ArrayList < Product > ();
	list = productMapper.queryAll();
	Boolean flag = true;

	HSSFWorkbook wb = null; // 定义工作簿
	try {
		wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("产品信息表");
		CellStyle styleT = commonService.setTBStyle(wb); //新建表格标题样式
		CellStyle style = commonService.setCBStyle(wb); //新建表格内容样式
		//增加表格标题
		HSSFRow rowT = sheet.createRow(0);
		commonService.createCell(wb, rowT, 0, "序号", styleT);
		sheet.setColumnWidth(0, 1280); //表格宽度Set the width (in units of 1/256th of a character width)
		commonService.createCell(wb, rowT, 1, "产品名称", styleT);
		sheet.setColumnWidth(1, 5120);
		commonService.createCell(wb, rowT, 2, "产品编号", styleT);
		sheet.setColumnWidth(2, 5120);
		commonService.createCell(wb, rowT, 3, "委托编号", styleT);
		sheet.setColumnWidth(3, 5120);
		commonService.createCell(wb, rowT, 4, "产品型号", styleT);
		sheet.setColumnWidth(4, 5120);
		commonService.createCell(wb, rowT, 5, "样本编号", styleT);
		sheet.setColumnWidth(5, 5120);
		commonService.createCell(wb, rowT, 6, "送检单位", styleT);
		sheet.setColumnWidth(6, 5120);
		commonService.createCell(wb, rowT, 7, "生产单位", styleT);
		sheet.setColumnWidth(7, 5120);
		//写入表格数据
		HSSFRow row = null;
		for (int i = 0; i < list.size(); i++) {
			row = sheet.createRow(i + 1); // 从第2行起填充内容,第1行为标题，序号为0
			Product product = list.get(i);
			commonService.createCell(wb, row, 0, Integer.toString(i + 1), style);
			commonService.createCell(wb, row, 1, product.getName(), style);
			commonService.createCell(wb, row, 2, product.getNumber(), style);
			commonService.createCell(wb, row, 3, product.getCommissionNumber(), style);
			commonService.createCell(wb, row, 4, product.getModel(), style);
			commonService.createCell(wb, row, 5, product.getSampleNumber(), style);
			String subunit = submissionUnitMapper.queryById(product.getSubunitId()).getName();
			commonService.createCell(wb, row, 6, subunit, style);
			commonService.createCell(wb, row, 7, product.getManufacturer(), style);
		}

		ByteArrayOutputStream os = new ByteArrayOutputStream(); // 获得输出流
		wb.write(os);
		os.flush();
		os.close();
		wb.close();
		byte[] content = os.toByteArray();
		InputStream fis = new ByteArrayInputStream(content);
		// 设置response参数，可以打开下载页面
		response.reset();
		response.setContentType("application/octet-stream;charset=utf-8");
		String filename = URLEncoder.encode("产品信息_" + commonService.getDate() + ".xls", "UTF-8"); // 文件名定义
		response.setHeader("Content-Disposition", "attachment;filename=" + filename);
		ServletOutputStream servletOutputStream = response.getOutputStream();
		byte[] b = new byte[1024 * 1024];
		int i = 0;
		while ((i = fis.read(b)) > 0) {
			servletOutputStream.write(b, 0, i);
		}
		fis.close();
	} catch(IOException e) {
		// TODO Auto-generated catch block
		System.out.println("下载出错!");
		flag = false;
	}
	return flag;
}

CommonService.java文件：

package com.fc.service;

import java.util.Calendar;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.stereotype.Service;

/**
 * 公共方法类
 * 
 * @author yp         
 *   
 */
@Service
public class CommonService {
	/**
	 * 在excel模板中插入一个单元格
	 * 
	 * @param col 列号，从0开始          
	 * @param val 单元格的值
	 * @author yp         
	 *   
	 */
	public void createCell(HSSFWorkbook wb, HSSFRow row, int col, String val,CellStyle style) {
		HSSFCell cell = row.createCell(col);		
		cell.setCellStyle(style);//设置表格样式
		cell.setCellValue(val);	
	}
	static int k=0;
	/**
	 * 
	 * 获取单元格内容样式
	 * @author yp
	 * @return 返回单元格样式
	 */
	public CellStyle setCBStyle(HSSFWorkbook wb) {
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);//设置文本位置横向居中
		style.setBorderTop(BorderStyle.THIN);
		style.setBorderBottom(BorderStyle.THIN);
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);
		return style;
	}
	/**
	 * 
	 * 获取单元格标题样式
	 * @author yp
	 * @return 返回单元格样式
	 */
	public CellStyle setTBStyle(HSSFWorkbook wb) {
		CellStyle style = wb.createCellStyle();		
		style.setAlignment(HorizontalAlignment.CENTER);//设置文本位置横向居中
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置单元格填充样式否则单元格颜色无效
		style.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());//设置单元格填充颜色
		style.setBorderTop(BorderStyle.THIN);
		style.setBorderBottom(BorderStyle.THIN);
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);		
		return style;
	}
	/**
	 * 
	 * 获取当前日期
	 * @author yp
	 * @return 返回当前日期
	 */
	public String getDate() {
		Calendar CD = Calendar.getInstance();
		int YY = CD.get(Calendar.YEAR);
		int MM = CD.get(Calendar.MONTH) + 1;
		int DD = CD.get(Calendar.DATE);
		String year = String.valueOf(YY);
		String month = String.valueOf(MM);
		String day = String.valueOf(DD);
		if (month.length() < 2) {
			month = "0" + month;
		}
		if (day.length() < 2) {
			day = "0" + day;
		}
		String today = year + "-" + month + "-" + day;
		return today;
	}
}