浏览 14583 次
|
锁定老帖子 主题：POI操作excel示例工具类
精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (0)
|
|
|---|---|
| 作者 | 正文 |
|
发表时间：2008-07-04
由于近期项目要用到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:wdmcln@hotmail.com 声明：ITeye文章版权属于作者，受法律保护。没有作者书面许可不得转载。
推荐链接
|
| 返回顶楼 | |
|
发表时间：2008-07-04
过人,留点声音啊!
难道是写的太烂了?????? 好就留下宝贵的意见,我好改尽 |
| 返回顶楼 | |
|
发表时间：2008-07-06
ExcelWorkBook应该能够放多张sheet,增加个addSheet(SheetModel s)方法更好,有多张sheet情况,另外每张sheet能够提起一个公共数据接口(SheetModel)，象TableModel那样,这样每次生成一个excel会更方便。
另外sheet里面单元格格式设置更通用点就好了。 |
| 返回顶楼 | |
|
发表时间：2008-07-07
@sylinx_yqg
呵,首先谢谢你的发言!对你的答复,很有建议性,确实应该这样! 但这里有几个问题: 1.ExcelWorkBook与每个sheet的对应情况,之前我也试过几种,但总会有异常? 也就改成了现在的情况 2. 你提出的这个SheetModel类,是用于生产数据,还是组装row和cell?这个还需要和你 多交流一下? |
| 返回顶楼 | |
|
发表时间：2008-09-12
有这么多东西？学习学习。。
我写的才几行代码。。感觉很丑陋 |
| 返回顶楼 | |
|
发表时间：2008-09-21
最近项目中用到这个，拿你的代码学习下！
|
| 返回顶楼 | |
|
发表时间：2009-02-02
Exception in thread "main" java.lang.IllegalArgumentException: Sheet name cannot be blank, greater than 31 chars, or contain any of /\*?[]
楼主，我拷贝下来这么报这个错误呢？ |
| 返回顶楼 | |
