java使用POI的导入导出

使用struts2+spring+hibernate技术，上传采用struts2的file contentType filename即可快速实现文件上传

1、POI操作Excel
①-④
2、POI操作Excel样式
样式是属于工作簿，运用于单元格；字体是属于工作簿，加载在样式中，通过样式运用于单元格
3、合并单元格对象（CellRangeAddress，起始行号，结束行号，起始列号，结束列号）

<p>
1.首先导入包</p><p>1.poi-3.10.1-20140818.jar</p><p>2.poi-ooxml-3.10.1-20140818.jar </p><p>3.poi-ooxml-schemas-3.10.1-20140818.jar</p><p>4.xmlbeans-2.6.0.jar</p><p>5.dom4j-1.6.1.jar</p><p>
</p><p>2.编写后台代码块</p>

导出excel

public void exportExcel() {
		// 获取导出的数据
		try {
			List<User> userlist = userService.findObjects();

			// 输出导出内容

			HttpServletResponse response = ServletActionContext.getResponse();
			response.setContentType("application/x-execl");
			response.setHeader("Content-Disposition", "attachment;filename="
					+ new String("用户列表.xls".getBytes(), "ISO-8859-1")); //设置浏览器可识别的编码
			ServletOutputStream outputStream = response.getOutputStream();
			userService.exportExcel(userlist,outputStream);
			if(outputStream!=null){
				outputStream.close();
			
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	
	//导入excel
	public String importExcel(){
		//先判断是否是excel文件
		try {
			if(userExcel!=null){
				if(userExcelFileName.matches("^.+\\.(?i)((xls)|(xlsx))$")){
					//上面判断是否是excel文件，如过是的话，就导入
					
					//
					
					userService.importExcel(userExcel,userExcelFileName);
					
				}
				
			}
		} catch (Exception e) {
			
			e.printStackTrace();
		}
		
		
		//判断行数是否是大于二的
		
		
		return "list";
	}
	

package cn.itcast.core.util;

import java.util.List;

import javax.servlet.ServletOutputStream;

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.ss.util.CellRangeAddress;

import cn.itcast.nsfw.user.entity.User;

public class UserExcelOutPut {
	public   static void userExcelOutPut(List<User> userlist,
			ServletOutputStream outputStream){
		
		try {
			// 创建工作簿
			HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
			// 合并表头
			CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 4);

			// 创建工作表
			HSSFSheet sheet = hssfWorkbook.createSheet("用户列表");
			// 设置工作表的表头
			sheet.addMergedRegion(cra);
			HSSFRow row0 = sheet.createRow(0);
			HSSFCell cell0 = row0.createCell(0);
			cell0.setCellValue("用户列表");
			//统一设置列宽
			sheet.setDefaultColumnWidth(18);
			// 创建样式 标题的
			HSSFCellStyle cellStyleBT = createCellStyleAndFont(hssfWorkbook,
					(short) 18);

			cell0.setCellStyle(cellStyleBT);
			createCellStyleAndFont(hssfWorkbook, (short) 18);
			// 第二行固定列名
			// 设置第二个列名
			HSSFCellStyle cellStyleTwo = createCellStyleAndFont(hssfWorkbook,
					(short) 16);
			String[] names = { "用户名", "账号", "所属部门", "性别", "电子邮箱" };
			HSSFRow row1 = sheet.createRow(1);
			for (int i = 0; i < names.length; i++) {
				HSSFCell celli = row1.createCell(i);
				celli.setCellValue(names[i]);
				celli.setCellStyle(cellStyleTwo);

			}
			// 设置数据
			if (userlist.size() > 0) {
				for (int j = 0; j < userlist.size(); j++) {
					// 从第三行开始设置
					HSSFRow rowj = sheet.createRow(j + 2);
					// 填充数据
					HSSFCell cellContent0 = rowj.createCell(0);
					cellContent0.setCellValue(userlist.get(j).getName());

					HSSFCell cell1 = rowj.createCell(1);
					cell1.setCellValue(userlist.get(j).getAccount());

					HSSFCell cell2 = rowj.createCell(2);
					cell2.setCellValue(userlist.get(j).getDept());
					
					HSSFCell cell3 = rowj.createCell(3);
					cell3.setCellValue(userlist.get(j).isGender()?"男":"女");
					
					HSSFCell cell4 = rowj.createCell(4);
					cell4.setCellValue(userlist.get(j).getEmail());
					
					
					

				}
			}
			
			hssfWorkbook.write(outputStream);
			//关闭工作簿i
			hssfWorkbook.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	private static HSSFCellStyle createCellStyleAndFont(HSSFWorkbook hssfWorkbook,
			short fontnum) {
		HSSFCellStyle cellstyle = hssfWorkbook.createCellStyle();
		cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		HSSFFont font = hssfWorkbook.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontHeightInPoints(fontnum);
		cellstyle.setFont(font);

		return cellstyle;

	}

}

导入excel

public void importExcel(File userExcel, String userExcelFileName) {
		try {
			boolean is03 = false;
			is03 =userExcelFileName.matches("^.+\\.(?i)(xls)$");
			FileInputStream fis = new FileInputStream(userExcel);
			Workbook wb =	is03 ? new HSSFWorkbook(fis):new XSSFWorkbook(fis);
			
			Sheet sheet = wb.getSheetAt(0);
			
			if(sheet.getPhysicalNumberOfRows()>2){
				 User user = null;
				for(int k = 2;k<sheet.getPhysicalNumberOfRows();k++){
					//4、读取单元格
					Row row = sheet.getRow(k);
					user = new User();
					//用户名
					Cell cell0 = row.getCell(0);
					user.setName(cell0.getStringCellValue());
					//帐号
					Cell cell1 = row.getCell(1);
					user.setAccount(cell1.getStringCellValue());
					//所属部门
					Cell cell2 = row.getCell(2);
					user.setDept(cell2.getStringCellValue());
					//性别
					Cell cell3 = row.getCell(3);
					user.setGender(cell3.getStringCellValue().equals("男"));
					//手机号
					String mobile = "";
					Cell cell4 = row.getCell(4);
					try {
						mobile = cell4.getStringCellValue();
					} catch (Exception e) {
						double dMobile = cell4.getNumericCellValue();
						mobile = BigDecimal.valueOf(dMobile).toString();
					}
					user.setMobile(mobile);
					
					//电子邮箱
					Cell cell5 = row.getCell(5);
					user.setEmail(cell5.getStringCellValue());
					//生日
					Cell cell6 = row.getCell(6);
					if(cell6.getDateCellValue() != null){
						user.setBirthday(cell6.getDateCellValue());
					}
					//默认用户密码为 123456
					user.setPassword("123456");
					//默认用户状态为 有效
					user.setState(User.USER_STATE_VALID);
					
					//5、保存用户
					save(user);
					
				}
				
			}
			

			
			wb.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}