POI操作excel示例工具类

由于近期项目要用到excel来转存页面中的表单数据,对poi操作excel进行了一番了解,写了以下,但总觉的不是很好

特此:发布在此供大家评论,广义集思,还望多多指教

   1. workBook处理类

/**
 * Excel WorkBook工具类
 * @author dsy
 * @version 1.0
 */
public class ExcelWorkBook {
	
	public HSSFWorkbook workbook = null;
	public static HSSFWorkbook workbookTemp = null;
	//设置当前workbookName
	private String workbookName = null;
	private HSSFSheet sheet = null;
	private FileOutputStream fileOut;
	
	public ExcelWorkBook() {
		if(workbook != null) {
			workbook = null;
		}
		workbook = workbookTemp;
	}
	
	public ExcelWorkBook(String workbookName) {
		workbook = workbookTemp;
		setWorkbookName(workbookName);
	}
	public String getWorkbookName() {
		return workbookName;
	}

	public void setWorkbookName(String workbookName) {
		workbookName = workbookName;
	}

	public HSSFSheet getSheet() {
		sheet = workbook.createSheet(getWorkbookName());
		return sheet;
	}
	
	/**
	 * 用于stylUtils的所需要的workbook必须项所做的处理
	 * @return
	 */
	public static HSSFWorkbook getWorkbook() {
		return workbookTemp;
	}

	public static void setWorkbook(HSSFWorkbook workbook) {
		workbookTemp = workbook;
	}
	
	/**
	 * 输入当前WorkBook为下载临时文件记录
	 * @param excelName
	 */
	public void writerFileStream(String excelName) {
		try {
			fileOut = new FileOutputStream(excelName);
			workbook.write(fileOut);
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				fileOut.flush();
				fileOut.close();
				if(workbook != null) {
					workbook = null;
				}
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
}

  

   2. Excel Row工具类

 

/**
 * Excel Row工具类
 * @author dsy
 * @version 1.0
 */
public class ExcelSheetRow {
	
	
	public ExcelSheetRow() {
		// TODO Auto-generated constructor stub
	}

	public static HSSFSheet sheet = null;
	/**
	 * 设置当前Sheet名字
	 */
	private static String sheetName = null;
	private static HSSFRow row = null;
	
	
	/**
	 * 创建当前标题行
	 * @param sheet
	 * @return
	 */
	public static HSSFRow createCurrSheetTitle(ExcelWorkBook work) {
	   HSSFSheet sheet = work.getSheet();
	   row = sheet.createRow(0);
	   return row;
	}
	
	/**
	 * 创建当前excel记录内容
	 * @param sheet
	 * @param i
	 * @return
	 */
	public static HSSFRow createCurrSheetRecord(ExcelWorkBook work,int i) {
		HSSFSheet sheet = work.getSheet();
		row = sheet.createRow(i+1);
		return row;
	} 

	public static String getSheetName() {
		return sheetName;
	}

	public static void setSheetName(String sheetName) {
		ExcelSheetRow.sheetName = sheetName;
	}

}

  

   3.  Excel Cell工具类

/**
 * Excel Cell工具类
 * @author dsy
 * @version 1.0
 */
public class ExcelSheetCell {
	
	private static HSSFRow row = null;
	private static HSSFCell cell = null;
	
	
	
	/**
	 * 用于产生当前excel标题
	 * @param sheet [当前工作表单]
	 * @param firstRowValue [标题数组]
	 * @param style [当前单元格风格]
	 */
	public static void createCurrRowTitle(ExcelSheetRow sheetRow,ExcelWorkBook work ,String[] firstRowValue,HSSFCellStyle style) {
		row = sheetRow.createCurrSheetTitle(work);
		for (int i = 0; i < firstRowValue.length; i++) {
			cell = row.createCell((short) i);
			cell.setCellStyle(style);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(firstRowValue[i]);
		}
	}
	
	/**
	 * 用于生成excel当前记录内容,标题除外
	 * @param sheet [当前工作表单]
	 * @param beanList [当前数据列表,i=Object[]]
	 * @param style [当前单元格风格]
	 */
	public static void createCurrRowRecord(ExcelSheetRow sheetRow,ExcelWorkBook work,List beanList,HSSFCellStyle style) {
		Object[] obj = null;
		for (int i = 0; i < beanList.size(); i++) {
			row = sheetRow.createCurrSheetRecord(work,i);
			obj = (Object[]) beanList.get(i);
			if (obj != null) {
				createExcelCell(row, obj,style);
			}
		}
	}
	
	/**
	 * 需要以数组的方式提供当前每条记录
	 * 通过数组自动判断有多少列,生成当前行
	 */
	private static void createExcelCell(HSSFRow row, Object[] obj,HSSFCellStyle style) {
		try {
			for (int i = 0; i < obj.length; i++) {
				try {
					if (obj[i].toString() != null) {

						cell = row.createCell((short) i);
						cell.setCellStyle(style);
						cell.setEncoding(HSSFCell.ENCODING_UTF_16);
						cell.setCellValue(obj[i].toString());
					}
				} catch (NullPointerException e) {
					continue;
				}

			}
		} catch (Exception ex) {
			System.out.print(ex);
		}
	}
}

 

   4. Excel Style风格工具类

/**
 * Excel Style风格工具类
 * @author dsy
 * @version 1.0
 */
public class ExcelCellStyleUtils{

	//标题样式
	public static HSSFCellStyle titleStyle;
	//时间样式
	public static HSSFCellStyle dataStyle;
	//单元格样式
	public static HSSFCellStyle nameStyle;
	//超链接样式
	public static HSSFCellStyle linkStyle;
	public static HSSFFont font;
	
	public ExcelCellStyleUtils(ExcelWorkBook work) {
		titleStyle = linkStyle(work.getWorkbook());
		dataStyle = dataStyle(work.getWorkbook());
		nameStyle = nameStyle(work.getWorkbook());
		linkStyle = linkStyle(work.getWorkbook());
	}
	/**
	 * 超链接样式
	 * @return HSSFCellStyle
	 */
	private static HSSFCellStyle linkStyle(HSSFWorkbook work) {
		HSSFCellStyle linkStyle = work.createCellStyle();
		  linkStyle.setBorderBottom((short)1);
		  linkStyle.setBorderLeft((short)1);
		  linkStyle.setBorderRight((short)1);
		  linkStyle.setBorderTop((short)1);
		  linkStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
		  linkStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		  HSSFFont font = work.createFont();
		  font.setFontName(HSSFFont.FONT_ARIAL);
		  font.setUnderline((byte)1);
		  font.setColor(HSSFColor.BLUE.index);
		  linkStyle.setFont(font);
		  return linkStyle;
	}
	
	/**s
	 * 单元格样式
	 * @return HSSFCellStyle
	 */
	private static HSSFCellStyle nameStyle(HSSFWorkbook work) {
		HSSFCellStyle nameStyle = work.createCellStyle();
		  nameStyle.setBorderBottom((short)1);
		  nameStyle.setBorderLeft((short)1);
		  nameStyle.setBorderRight((short)1);
		  nameStyle.setBorderTop((short)1);
		  nameStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
		  nameStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		  return nameStyle;
	}
	
	/**
	 * 时间样式
	 * @return HSSFCellStyle
	 */
	private static HSSFCellStyle dataStyle(HSSFWorkbook work) {
		HSSFCellStyle dataStyle = work.createCellStyle();
		  dataStyle.setBorderBottom((short)1);
		  dataStyle.setBorderLeft((short)1);
		  dataStyle.setBorderRight((short)1);
		  dataStyle.setBorderTop((short)1);
		  dataStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
		  dataStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		  dataStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		  return dataStyle;
	}
	
	/**
	 * 标题样式
	 * @return HSSFCellStyle
	 */
	private static HSSFCellStyle titleStyle(HSSFWorkbook work) {
		HSSFCellStyle titleStyle = work.createCellStyle();
		font = work.createFont();
		font.setItalic(true);
	    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	    font.setColor(HSSFColor.BLUE.index);
		  titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
		  titleStyle.setBorderLeft((short)1);
		  titleStyle.setBorderRight((short)1);
		  titleStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);
		  titleStyle.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);
		  titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		  return titleStyle;
	}
}

  

   5. 创建Excel工厂类

/**
 * 创建Excel工具类
 * @author Administrator
 *
 */
public class ExcelUtilFactory {
	
	private static ExcelUtilFactory instance = null;
	private static HttpServletRequest excelRequest = null;
	private static HttpServletResponse excelResponse = null;
	
	public static ExcelUtilFactory getInstance(HttpServletRequest request,
			HttpServletResponse response) {
		if(instance == null) {
			instance = new ExcelUtilFactory();
		}
		excelRequest = request;
		excelResponse = response;
		return instance;
	}
	
	public static void outputExcel(String excelName, List list, String[] firstRowValue) {
		ExcelWorkBook work = new ExcelWorkBook();
		work.setWorkbookName(excelName);
		ExcelSheetRow sheetRow = new ExcelSheetRow();
		ExcelSheetCell sheetCell = new ExcelSheetCell();
		ExcelCellStyleUtils util = new ExcelCellStyleUtils(work);
		sheetCell.createCurrRowTitle(sheetRow, work, firstRowValue, util.titleStyle);
		sheetCell.createCurrRowRecord(sheetRow, work, list, util.nameStyle);
		String realPath = getExcelRealPath(excelName);
//		String realPath = "e:/temp/testRealPath_2.xls";
		work.writerFileStream(realPath);
		downloadFile(realPath);
	}
	
	private static String getExcelRealPath(String excelName) {
		String realPath = excelRequest.getRealPath("/UploadFile");
		File excelFile = new File(realPath);
		if(!excelFile.exists()) {
			excelFile.mkdirs();
		}
		excelName = realPath+ "\\" + excelName+".xls";
		return  excelName;
	} 
	
	private static void downloadFile(String strfileName) {
		try {
			// 获得ServletContext对象
			if(excelFileNotFund(strfileName)) {
				throw new IllegalArgumentException("File=["+strfileName+"] not fund file path");
			}
			// 取得文件的绝对路径
			File excelFile = getExcelDownloadPath(strfileName);
			putResponseStream(strfileName, excelFile);
		} catch (IOException e) {
			e.printStackTrace();
		} 
	}
	
	private static File getExcelDownloadPath(String excelName) {
//		String realPath = excelRequest.getRealPath("/UploadFile");
//		excelName = realPath+ "\\" + excelName;
//		excelName = replaceRNAll(excelName);
		File excelFile = new File(excelName);
		return  excelFile;
	}
	
	//用传入参数的判断
	private static boolean excelFileNotFund(String strfileName) {
		return strfileName ==  null|| strfileName.equals("");
	}
	
	/**
	 * 
	 * @param strfileName : 文件名称
	 * @param excelName  : 文件的相对路径或绝对路径
	 * @throws UnsupportedEncodingException
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	private static void putResponseStream(String strfileName, File excelName)
			throws UnsupportedEncodingException, FileNotFoundException,
			IOException {
		strfileName = URLEncoder.encode(strfileName, "UTF-8");
		excelResponse.setHeader("Content-disposition","attachment; filename=" + strfileName);
		excelResponse.setContentLength((int) excelName.length());
		excelResponse.setContentType("application/x-download");
		byte[] buffer = new byte[1024];
		int i = 0;
		FileInputStream fis = new FileInputStream(excelName);
		while ((i = fis.read(buffer)) > 0) {
			JspWriter out = null;
			excelResponse.getOutputStream().write(buffer, 0, i);
		}
	}
	
	public static void main(String[] args) {
		long beginTime = System.currentTimeMillis();
		System.out.println("开始时间:"+beginTime/1000);
		List beanList = new ArrayList();
		String[] excelTitle = new String[10];
		excelTitle[0] = "编号";
		excelTitle[1] = "基金名称";
		excelTitle[2] = "单位净值（NAV）";
		excelTitle[3] = "日增长率（％）";
		excelTitle[4] = "累积净值";
		excelTitle[5] = "编号";
		excelTitle[6] = "基金名称";
		excelTitle[7] = "单位净值（NAV）";
		excelTitle[8] = "日增长率（％）";
		excelTitle[9] = "累积净值";
		String[] beanArr = new String[10];
		for (int i = 0; i < 55000; i++) {
			beanArr[0] = String.valueOf(i+1);
			beanArr[1] = "基金A"+i;
			beanArr[2] = "1.0427";
			beanArr[3] = "-2.7514%";
			beanArr[4] = "1.1558";
			beanArr[5] = String.valueOf(i+1);
			beanArr[6] = "基金A"+i;
			beanArr[7] = "1.0427";
			beanArr[8] = "-2.7514%";
			beanArr[9] = "1.1558";
			beanList.add(beanArr);
		}
		outputExcel("今天测试_factory", beanList, excelTitle);
		long endTime = System.currentTimeMillis();
		System.out.println("测试55000,总计"+(endTime-beginTime)/1000+"秒,用时");
	}
}

 

   以上是创建Excel所有工具类,还望大家指教!!!!!!

   本人msn:[e-mail]

评论

7 楼 xiaoqiangcn 2009-02-02  

Exception in thread "main" java.lang.IllegalArgumentException: Sheet name cannot be blank, greater than 31 chars, or contain any of /\*?[]

楼主，我拷贝下来这么报这个错误呢？

6 楼 gledeyes 2008-10-13  

真是太好了，学习一下

5 楼 jmd 2008-09-21  

最近项目中用到这个，拿你的代码学习下！

4 楼 kevindurant 2008-09-12  

有这么多东西？学习学习。。
我写的才几行代码。。感觉很丑陋

3 楼 wdmcln 2008-07-07  

@sylinx_yqg
呵,首先谢谢你的发言!对你的答复,很有建议性,确实应该这样!
但这里有几个问题:
1.ExcelWorkBook与每个sheet的对应情况,之前我也试过几种,但总会有异常?
    也就改成了现在的情况
2. 你提出的这个SheetModel类,是用于生产数据,还是组装row和cell?这个还需要和你
   多交流一下?

2 楼 sylinx_yqg 2008-07-06  

ExcelWorkBook应该能够放多张sheet,增加个addSheet(SheetModel s)方法更好,有多张sheet情况,另外每张sheet能够提起一个公共数据接口(SheetModel)，象TableModel那样,这样每次生成一个excel会更方便。
另外sheet里面单元格格式设置更通用点就好了。

1 楼 wdmcln 2008-07-04  

过人,留点声音啊!
难道是写的太烂了??????
好就留下宝贵的意见,我好改尽