封装POI读取导出Excel

基础数据导出信息类

 

 

import java.util.List;

public class BaseDataExportInfo {
  private String sheetTitle; //工作表标题

  private String sheetName; //工作表名称

  private String workbookFileName; //文件名

  private short sheetIndex = (short)0; //创建工作表的顺序
  private List tableHead; //表头
  private List rowList; //行 Vector v = new Vector(); v.add(i, "");data.add(v);
  private String cellDataFomat; //设置单元格类型，值请参考HSSFDataFormat内置的数据类型，例如"@"代表文本
  public List getRowList() {
    return rowList;
  }

  public void setRowList(List rowList) {
    this.rowList = rowList;
  }

  public String getSheetName() {
    return sheetName;
  }

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

  public String getSheetTitle() {
    return sheetTitle;
  }

  public void setSheetTitle(String sheetTitle) {
    this.sheetTitle = sheetTitle;
  }

  public List getTableHead() {
    return tableHead;
  }

  public void setTableHead(List tableHead) {
    this.tableHead = tableHead;
  }

  public String getWorkbookFileName() {
    return workbookFileName;
  }

  public void setWorkbookFileName(String workbookFileName) {
    this.workbookFileName = workbookFileName;
  }

  public short getSheetIndex() {
    return sheetIndex;
  }

  public void setSheetIndex(short sheetIndex) {
    this.sheetIndex = sheetIndex;
  }

public String getCellDataFomat() {
	return cellDataFomat;
}

public void setCellDataFomat(String cellDataFomat) {
	this.cellDataFomat = cellDataFomat;
}

}

  

处理类

 

import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Vector;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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.hssf.util.Region;

public class ProcessXSL {
	HSSFWorkbook wb = new HSSFWorkbook();
	String workbookFileName = "test.xls";
	protected final Log log = LogFactory.getLog(getClass());

	public ProcessXSL() {
	}

	/**
	 * 创建 Execl 文件
	 * 
	 * @param wb
	 *            HSSFWorkbook
	 * @param exportInfo
	 *            BaseDataExportInfo
	 */
	public void createWorkBookSheet(BaseDataExportInfo exportInfo) {

		Vector cellList = null;
		if (exportInfo.getWorkbookFileName() != null)
			workbookFileName = exportInfo.getWorkbookFileName() + ".xls";
		try {

			HSSFSheet sheet = wb.createSheet(exportInfo.getSheetIndex() + "");
			wb.setSheetName(exportInfo.getSheetIndex(), exportInfo
					.getSheetName(), (short) 1);

			/** 设置列宽 */

			for (int i = 0; i < exportInfo.getTableHead().size(); i++) {
				if (i == 1 || i == exportInfo.getTableHead().size() - 1) {
					sheet.setColumnWidth((short) i, (short) 7000);
				} else {
					sheet.setColumnWidth((short) i, (short) 4000);
				}
			}

			/** 合并单元格 */
			sheet.addMergedRegion(new Region(0, (short) 0, 0,
					(short) (exportInfo.getTableHead().size() - 1)));

			/** 表 标题 */
			HSSFRow row = sheet.createRow((short) 0);
			row.setHeight((short) 500); // 设置行高

			HSSFFont titleFont = wb.createFont();
			titleFont.setFontName("宋体");
			titleFont.setFontHeightInPoints((short) 16);
			titleFont.setBoldweight((short) 20);
			HSSFCellStyle titleStyle = wb.createCellStyle();
			titleStyle.setFont(titleFont);

			titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 居中

			row = this.createCell(row, (short) 0, titleStyle, exportInfo
					.getSheetTitle());

			/** 表头 */
			HSSFFont headFont = wb.createFont();
			headFont.setFontName("宋体");
			headFont.setFontHeightInPoints((short) 12);
			headFont.setBoldweight((short) 20);

			HSSFCellStyle headStyle = wb.createCellStyle();
			headStyle.setFont(headFont);
			headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框

			headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框

			headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框

			headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框

			headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

			HSSFRow row2 = sheet.createRow((short) 1);
			row2.setHeight((short) 400); // 设置行高

			for (int i = 0; i < exportInfo.getTableHead().size(); i++) {
				row2 = this.createCell(row2, (short) i, headStyle, exportInfo
						.getTableHead().get(i));
			}
			/** 表体 */
			HSSFFont font = wb.createFont();
			/** 设置字体样式 */
			font.setFontName("宋体");
			HSSFCellStyle cellStyle = wb.createCellStyle();
			if (exportInfo.getCellDataFomat() != null
					&& !"".equals(exportInfo.getCellDataFomat())) {
				short stringFormat = HSSFDataFormat.getBuiltinFormat(exportInfo
						.getCellDataFomat());
				if (stringFormat != -1) {
					cellStyle.setDataFormat(stringFormat);
				}
			}
			cellStyle.setFont(font);
			cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框

			cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框

			cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框

			cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框

			cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 居左

			for (int i = 0; i < exportInfo.getRowList().size(); i++) {
				cellList = (Vector) exportInfo.getRowList().get(i);
				HSSFRow row3 = sheet.createRow((short) i + 2);
				row3.setHeight((short) 300); // 设置行高
				for (int j = 0; j < cellList.size(); j++) {
					row3 = this.createCell(row3, (short) j, cellStyle, cellList
							.get(j));
				}
			}

		} catch (Exception ex) {
			log.info("error while create work book sheet ", ex);
		}

	}

	/**
	 * 创建 包含多个bookSheet 的 Execl 文件
	 * 
	 * @param wb
	 *            HSSFWorkbook
	 * @param exportInfo
	 *            BaseDataExportInfo
	 */
	public void createMoreWorkBookSheet(BaseDataExportInfo exportInfo) {

		if (exportInfo.getWorkbookFileName() != null)
			workbookFileName = exportInfo.getWorkbookFileName() + ".xls";
		try {

			for (int n = 0; n < exportInfo.getRowList().size(); n++) {

				// JOptionPane.showMessageDialog(null,"第" +(n+1)+ "分页！");

				HSSFSheet sheet = wb.createSheet(n + "");
				wb.setSheetName(n, exportInfo.getSheetName() + " " + (n + 1),
						(short) 1);

				/** 设置列宽 */
				// 设置第二列和倒数第二类的宽度为7000，其它列宽度为4000
				for (int i = 0; i < exportInfo.getTableHead().size(); i++) {
					if (i == 1 || i == exportInfo.getTableHead().size() - 1) {
						sheet.setColumnWidth((short) i, (short) 7000);
					} else {
						sheet.setColumnWidth((short) i, (short) 4000);
					}
				}

				/** 合并单元格 */
				// 生成标题行, Region的四个参数分别对应 (x1,y1,x2,y2)
				sheet.addMergedRegion(new Region(0, (short) 0, 0,
						(short) (exportInfo.getTableHead().size() - 1)));

				/** 表标题 */
				HSSFRow row = sheet.createRow((short) 0);
				row.setHeight((short) 500); // 设置行高

				// 格式化表标题
				HSSFFont titleFont = wb.createFont();
				titleFont.setFontName("宋体");
				titleFont.setFontHeightInPoints((short) 16);
				titleFont.setBoldweight((short) 20);
				HSSFCellStyle titleStyle = wb.createCellStyle();
				titleStyle.setFont(titleFont);
				titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 居中

				row = this.createCell(row, (short) 0, titleStyle, exportInfo
						.getSheetTitle());

				/** 表头 */
				// 格式化表头
				HSSFFont headFont = wb.createFont();
				headFont.setFontName("宋体");
				headFont.setFontHeightInPoints((short) 12);
				headFont.setBoldweight((short) 20);

				HSSFCellStyle headStyle = wb.createCellStyle();
				headStyle.setFont(headFont);
				headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框

				headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框

				headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框

				headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框

				headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

				HSSFRow row2 = sheet.createRow((short) 1);
				row2.setHeight((short) 400); // 设置行高

				for (int i = 0; i < exportInfo.getTableHead().size(); i++) {
					row2 = this.createCell(row2, (short) i, headStyle,
							exportInfo.getTableHead().get(i));
				}

				/** 表体 */
				// 格式化表体
				HSSFFont font = wb.createFont();
				font.setFontName("宋体");
				HSSFCellStyle cellStyle = wb.createCellStyle();
				if (exportInfo.getCellDataFomat() != null
						&& !"".equals(exportInfo.getCellDataFomat())) {
					short stringFormat = HSSFDataFormat
							.getBuiltinFormat(exportInfo.getCellDataFomat());
					if (stringFormat != -1) {
						cellStyle.setDataFormat(stringFormat);
					}
				}
				cellStyle.setFont(font);
				cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框

				cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框

				cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框

				cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框

				cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 居左

				for (int i = 0; i < 98; i++) {
					if (i < exportInfo.getRowList().size()) {
						if ((i + n * 100) >= exportInfo.getRowList().size())
							break;

						Vector cellList = null;
						cellList = (Vector) exportInfo.getRowList().get(
								i + n * 100);
						HSSFRow row3 = sheet.createRow((short) i + 2);
						row3.setHeight((short) 300); // 设置行高
						for (int j = 0; j < cellList.size(); j++) {
							row3 = this.createCell(row3, (short) j, cellStyle,
									cellList.get(j));
						}

					}
				}

				if ((100 * (n + 1) - 1) >= exportInfo.getRowList().size())
					break;
			}
		} catch (Exception ex) {
			log.info("error while create work book sheet ", ex);
		}

	}

	/**
	 * 创建单元格
	 * 
	 * 
	 * @param row
	 *            HSSFRow
	 * @param cellIndex
	 *            short
	 * @param cellStyle
	 *            HSSFCellStyle
	 * @param cellValue
	 *            Object
	 * @return HSSFRow
	 */
	public HSSFRow createCell(HSSFRow row, short cellIndex,
			HSSFCellStyle cellStyle, Object cellValue) {
		HSSFCell cell = row.createCell((short) cellIndex);
		try {
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			if (cellStyle != null) {
				cell.setCellStyle(cellStyle);
			}

			if (cellValue == null) {
				cell.setCellValue("");
			} else if (cellValue instanceof Boolean) {
				cell.setCellValue(((Boolean) cellValue).booleanValue());
			} else if (cellValue instanceof String) {
				cell.setCellValue((String.valueOf(cellValue)));
			} else if (cellValue instanceof Date) {
				cell.setCellValue((Date) cellValue);
			} else {
				cell.setCellValue("");
			}

			// log.info("this cell value is " + cellValue);
		} catch (Exception ex) {
			log.error("error while execut create cell ", ex);
		}
		return row;
	}

	/**
	 * 读取Excel 表
	 * 
	 * 
	 * @param aSheet
	 *            HSSFSheet
	 * @throws Exception
	 * @return List
	 */
	public List readWeekBookSheet(HSSFSheet aSheet) throws Exception {
		List rowList = new ArrayList();
		Vector rowVector = null;
		int rowNum = 1;
		int cellNum = 1;
		int maxCellNum = aSheet.getRow(1).getLastCellNum();
		try {
			for (int rowNumOfSheet = 2; rowNumOfSheet <= aSheet.getLastRowNum(); rowNumOfSheet++) {
				rowNum = rowNumOfSheet;
				if (null != aSheet.getRow(rowNumOfSheet)) {
					HSSFRow aRow = aSheet.getRow(rowNumOfSheet);
					rowVector = new Vector();
					for (short cellNumOfRow = 0; cellNumOfRow <= maxCellNum; cellNumOfRow++) {
						cellNum = cellNumOfRow;
						if (null != aRow.getCell(cellNumOfRow)) {
							HSSFCell aCell = aRow.getCell(cellNumOfRow);
							int cellType = aCell.getCellType();

							switch (cellType) {
							case HSSFCell.CELL_TYPE_NUMERIC: // 整形

								// rowVector.add(cellNumOfRow,
								// String.valueOf(aCell.getNumericCellValue())
								// .substring(0,
								// String.valueOf(aCell.getNumericCellValue()).indexOf(".")));
								rowVector.add(cellNumOfRow, String
										.valueOf(aCell.getNumericCellValue()));
								break;
							case HSSFCell.CELL_TYPE_STRING: // 字符串型
								rowVector.add(cellNumOfRow, aCell
										.getStringCellValue().trim());
								break;
							case HSSFCell.CELL_TYPE_FORMULA: // double 型

								rowVector.add(cellNumOfRow, String
										.valueOf(aCell.getNumericCellValue()));
								break;
							case HSSFCell.CELL_TYPE_BLANK: // 空字符

								rowVector.add(cellNumOfRow, "");
								break;
							case HSSFCell.CELL_TYPE_BOOLEAN: // 布尔型

								rowVector.add(cellNumOfRow, String
										.valueOf(aCell.getBooleanCellValue()));
								break;
							default:
								rowVector.add(cellNumOfRow, "");
							}
						} else {
							rowVector.add(cellNumOfRow, "");
						}
					}
					rowList.add(rowVector);
				}
			}

		} catch (Exception ex) {
			log.error("error while execut read week book sheet with " + rowNum
					+ " rows and " + cellNum + "cols", ex);
			throw new Exception();
		}

		return rowList;
	}

	/**
	 * 写Execl 文件
	 * 
	 * @param wb
	 *            HSSFWorkbook
	 * @param outPutStream
	 *            OutputStream
	 */
	public void writeWorkBook(HttpServletResponse response) {
		try {

			OutputStream outputStream = new BufferedOutputStream(response
					.getOutputStream());
			response.reset();
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("content-disposition", "attachment;filename=\""
					+ new String(workbookFileName.getBytes(), response
							.getCharacterEncoding()) + "\"");
			wb.write(outputStream);
			outputStream.close();
		} catch (Exception ex) {
			log.error("error while create work book ", ex);
		}

	}
}

 

使用说明

service方法：

public BaseDataExportInfo getOpeXls(SplitPageHelpBean page) {
		TimeKit date = new TimeKit();
		BaseDataExportInfo export = new BaseDataExportInfo();
		export.setWorkbookFileName(date.now("short"));
		export.setSheetTitle("从业资格证-" + date.now("long"));
		export.setSheetName("从业资格证");
		List tableHead = new ArrayList();
		tableHead.add("编号");
		tableHead.add("资格证号");
		tableHead.add("姓名");
		tableHead.add("身份证号");
		tableHead.add("性别");
		tableHead.add("出生日期");
		tableHead.add("办证日期");
		export.setTableHead(tableHead);

		List rowList = new ArrayList();
		List zgzList = page.getContent();
		if (page != null) {
			for (int j = 0; j < zgzList.size(); j++) {
				Vector cols = new Vector();
				cols.add(0, String.valueOf(j+1));
				cols.add(1, ((TippstjjTJZGZ) zgzList.get(j)).getZgzNum());
				cols.add(2, ((TippstjjTJZGZ) zgzList.get(j)).getZgzName());
				cols.add(3, ((TippstjjTJZGZ) zgzList.get(j)).getZgzSfzNum());
				cols.add(4, ((TippstjjTJZGZ) zgzList.get(j)).getZgzSex());
				cols.add(5, ((TippstjjTJZGZ) zgzList.get(j)).getZgzBirthday()==null?"":TimeKit.formatDate(((TippstjjTJZGZ) zgzList.get(j)).getZgzBirthday(), "short"));
				cols.add(6, ((TippstjjTJZGZ) zgzList.get(j)).getZgzDate()==null?"":TimeKit.formatDate(((TippstjjTJZGZ) zgzList.get(j)).getZgzDate(), "short"));
				// cols.add(4, ( (TippstjjTJZGZ)
				// zgzList.get(j)).getOpeTime()==null?"":((TippBaseOpelog)
				// opeLogs.get(j)).getOpeTime().toString());
				rowList.add(cols);
			}
		}
		export.setRowList(rowList);

		return export;
	} 

 在Action中调用上边写的service的方法

public ActionForward onSubmit(ActionMapping mapping, Object bean,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		ZgzForm form=(ZgzForm)getForm();
		String name=form.getZgzName();//姓名
		String zgzNum=form.getZgzNum();//资格证号
		String sfzNum=form.getZgzSfzNum();//身份证号
		String birthday=form.getZgzBirthday();//生日
		String sex=form.getZgzSex();//性别
		String zgzDate=form.getZgzDate();//发证日期
		
		ArrayList list=new ArrayList();
		String sql=" 1=1";
		//对用户条件进行判断
		if(name!=null&&!"".equals(name)){
			sql=sql+" and {zgzName}=? ";
			list.add(name);
		}
		if(zgzNum!=null&&!"".equals(zgzNum)){
			sql=sql+" and {zgzNum}=? ";
			list.add(zgzNum);
		}
		if(sfzNum!=null&&!"".equals(sfzNum)){
			sql=sql+" and {zgzSfzNum}=? ";
			list.add(sfzNum);
		}
		if(birthday!=null&&!"".equals(birthday)){
			sql=sql+" and {zgzBirthday}=? ";
			list.add(birthday);
		}
		if(sex!=null&&!"".equals(sex)){
			sql=sql+" and {zgzSex}=? ";
			list.add(sex);
		}
		if(zgzDate!=null&&!"".equals(zgzDate)){
			sql=sql+" and {zgzDate}=? ";
			list.add(zgzDate);
		}

		sql=sql+" order by {zgzDate} desc";
		
		//创建分页对象
		SplitPageHelpBean page=new SplitPageHelpBean();
		//获取当前页数
	    int currentPage = form.getCurrentPage();
	    if (currentPage == 0) {
	      currentPage = 1;
	    }
		page.setRecordsPerPage(99999);
		page.setCurrentPage(currentPage);
		page.setQueryString(sql);
		page.setValues(list.toArray());
		page=stjjService.getObjects(TippstjjTJZGZ.class,page);
		request.setAttribute("splitPage", page);
		
		BaseDataExportInfo export = null;
		export = stjjService.getOpeXls(page);
		export.setCellDataFomat("@");//设置单元格类型，值请参考HSSFDataFormat内置的数据类型，例如"@"代表文本
		ProcessXSL xsl = new ProcessXSL();
		xsl.createWorkBookSheet(export);
		xsl.writeWorkBook(response);
		return null;
	}

 参考资料

 HSSFDataFormat的数据格式

内置数据类型	编号
"General"	0
"0"	1
"0.00"	2
"#,##0"	3
"#,##0.00"	4
"($#,##0_);($#,##0)"	5
"($#,##0_);[Red]($#,##0)"	6
"($#,##0.00);($#,##0.00)"	7
"($#,##0.00_);[Red]($#,##0.00)"	8
"0%"	9
"0.00%"	0xa
"0.00E+00"	0xb
"# ?/?"	0xc
"# ??/??"	0xd
"m/d/yy"	0xe
"d-mmm-yy"	0xf
"d-mmm"	0x10
"mmm-yy"	0x11
"h:mm AM/PM"	0x12
"h:mm:ss AM/PM"	0x13
"h:mm"	0x14
"h:mm:ss"	0x15
"m/d/yy h:mm"	0x16
保留为过国际化用	0x17 - 0x24
"(#,##0_);(#,##0)"	0x25
"(#,##0_);[Red](#,##0)"	0x26
"(#,##0.00_);(#,##0.00)"	0x27
"(#,##0.00_);[Red](#,##0.00)"	0x28
"_($*#,##0_);_($*(#,##0);_($* \"-\"_);_(@_)"	0x29
"_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)"	0x2a
"_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_)"	0x2b
"_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_)"	0x2c
"mm:ss"	0x2d
"[h]:mm:ss"	0x2e
"mm:ss.0"	0x2f
"##0.0E+0"	0x30
"@" - This is text format	0x31

在上面表中，字符串类型所对应的是数据格式为"@"（最后一行），也就是HSSFDataFormat中定义的值为0x31（49）的那行

 

记录一下遇到的问题

    问题一：excel格式问题，导出excel后，双击编辑单元格之后，若是长数字类型的内容会自动使用科学记数法表示，并且超出位数的部分会被截止

    解决办法：通过POI设置单元格格式为文本就能解决问题，网上查了一下，一般都是说通过cell.setCellType(HSSFCell.CELL_TYPE_STRING)设置单元格格式，但通过测试这种方法并不起作用，生成后的excel的单元格格式仍然是常规。下边介绍一种可以实现的方法，如下：

HSSFCellStyle cellStyle = wb.createCellStyle();
short stringFormat = HSSFDataFormat.getBuiltinFormat("@"); //请参考HSSFDataFormat内置的数据类型，例如"@"代表文本
cellStyle.setDataFormat(stringFormat);