POI导出EXCEL【摘自：oschina.net】
package com.tkqd.util.poi; |
002 |
|
003 |
import java.io.FileNotFoundException; |
004 |
import java.io.FileOutputStream; |
005 |
import java.io.IOException; |
006 |
|
007 |
import org.apache.log4j.Logger; |
008 |
import org.apache.poi.hssf.usermodel.HSSFCell; |
009 |
import org.apache.poi.hssf.usermodel.HSSFRow; |
010 |
import org.apache.poi.hssf.usermodel.HSSFSheet; |
011 |
import org.apache.poi.hssf.usermodel.HSSFWorkbook; |
012 |
import org.apache.poi.hssf.util.HSSFCellUtil; |
013 |
import org.apache.poi.ss.usermodel.CellStyle; |
014 |
import org.apache.poi.ss.usermodel.Font; |
015 |
import org.apache.poi.ss.util.CellRangeAddress; |
016 |
|
017 |
/** |
018 |
* 描述：Excel写操作帮助类 |
019 |
* @author ALEX |
020 |
* @since 2010-11-24 |
021 |
* @version 1.0v |
022 |
*/ |
023 |
public class ExcelUtil { |
024 |
private static final Logger log=Logger.getLogger(ExcelUtil.class); |
025 |
/** |
026 |
* 功能：将HSSFWorkbook写入Excel文件 |
027 |
* @param wb HSSFWorkbook |
028 |
* @param absPath 写入文件的相对路径 |
029 |
* @param wbName 文件名 |
030 |
*/ |
031 |
public static void writeWorkbook(HSSFWorkbook wb,String fileName){ |
032 |
FileOutputStream fos=null; |
033 |
try { |
034 |
fos=new FileOutputStream(fileName); |
035 |
wb.write(fos); |
036 |
} catch (FileNotFoundException e) { |
037 |
log.error(new StringBuffer("[").append(e.getMessage()).append("]").append(e.getCause())); |
038 |
} catch (IOException e) { |
039 |
log.error(new StringBuffer("[").append(e.getMessage()).append("]").append(e.getCause())); |
040 |
} finally{ |
041 |
try { |
042 |
if(fos!=null){ |
043 |
fos.close(); |
044 |
} |
045 |
} catch (IOException e) { |
046 |
log.error(new StringBuffer("[").append(e.getMessage()).append("]").append(e.getCause())); |
047 |
} |
048 |
} |
049 |
} |
050 |
/** |
051 |
* 功能：创建HSSFSheet工作簿 |
052 |
* @param wb HSSFWorkbook |
053 |
* @param sheetName String |
054 |
* @return HSSFSheet |
055 |
*/ |
056 |
public static HSSFSheet createSheet(HSSFWorkbook wb,String sheetName){ |
057 |
HSSFSheet sheet=wb.createSheet(sheetName); |
058 |
sheet.setDefaultColumnWidth(12); |
059 |
sheet.setGridsPrinted(false); |
060 |
sheet.setDisplayGridlines(false); |
061 |
return sheet; |
062 |
} |
063 |
/** |
064 |
* 功能：创建HSSFRow |
065 |
* @param sheet HSSFSheet |
066 |
* @param rowNum int |
067 |
* @param height int |
068 |
* @return HSSFRow |
069 |
*/ |
070 |
public static HSSFRow createRow(HSSFSheet sheet,int rowNum,int height){ |
071 |
HSSFRow row=sheet.createRow(rowNum); |
072 |
row.setHeight((short)height); |
073 |
return row; |
074 |
} |
075 |
/** |
076 |
* 功能：创建CellStyle样式 |
077 |
* @param wb HSSFWorkbook |
078 |
* @param backgroundColor 背景色 |
079 |
* @param foregroundColor 前置色 |
080 |
* @param font 字体 |
081 |
* @return CellStyle |
082 |
*/ |
083 |
public static CellStyle createCellStyle(HSSFWorkbook wb,short backgroundColor,short foregroundColor,short halign,Font font){ |
084 |
CellStyle cs=wb.createCellStyle(); |
085 |
cs.setAlignment(halign); |
086 |
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER); |
087 |
cs.setFillBackgroundColor(backgroundColor); |
088 |
cs.setFillForegroundColor(foregroundColor); |
089 |
cs.setFillPattern(CellStyle.SOLID_FOREGROUND); |
090 |
cs.setFont(font); |
091 |
return cs; |
092 |
} |
093 |
/** |
094 |
* 功能：创建带边框的CellStyle样式 |
095 |
* @param wb HSSFWorkbook |
096 |
* @param backgroundColor 背景色 |
097 |
* @param foregroundColor 前置色 |
098 |
* @param font 字体 |
099 |
* @return CellStyle |
100 |
*/ |
101 |
public static CellStyle createBorderCellStyle(HSSFWorkbook wb,short backgroundColor,short foregroundColor,short halign,Font font){ |
102 |
CellStyle cs=wb.createCellStyle(); |
103 |
cs.setAlignment(halign); |
104 |
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER); |
105 |
cs.setFillBackgroundColor(backgroundColor); |
106 |
cs.setFillForegroundColor(foregroundColor); |
107 |
cs.setFillPattern(CellStyle.SOLID_FOREGROUND); |
108 |
cs.setFont(font); |
109 |
cs.setBorderLeft(CellStyle.BORDER_DASHED); |
110 |
cs.setBorderRight(CellStyle.BORDER_DASHED); |
111 |
cs.setBorderTop(CellStyle.BORDER_DASHED); |
112 |
cs.setBorderBottom(CellStyle.BORDER_DASHED); |
113 |
return cs; |
114 |
} |
115 |
/** |
116 |
* 功能：创建CELL |
117 |
* @param row HSSFRow |
118 |
* @param cellNum int |
119 |
* @param style HSSFStyle |
120 |
* @return HSSFCell |
121 |
*/ |
122 |
public static HSSFCell createCell(HSSFRow row,int cellNum,CellStyle style){ |
123 |
HSSFCell cell=row.createCell(cellNum); |
124 |
cell.setCellStyle(style); |
125 |
return cell; |
126 |
} |
127 |
/** |
128 |
* 功能：合并单元格 |
129 |
* @param sheet HSSFSheet |
130 |
* @param firstRow int |
131 |
* @param lastRow int |
132 |
* @param firstColumn int |
133 |
* @param lastColumn int |
134 |
* @return int 合并区域号码 |
135 |
*/ |
136 |
public static int mergeCell(HSSFSheet sheet,int firstRow,int lastRow,int firstColumn,int lastColumn){ |
137 |
return sheet.addMergedRegion(new CellRangeAddress(firstRow,lastRow,firstColumn,lastColumn)); |
138 |
} |
139 |
/** |
140 |
* 功能：创建字体 |
141 |
* @param wb HSSFWorkbook |
142 |
* @param boldweight short |
143 |
* @param color short |
144 |
* @return Font |
145 |
*/ |
146 |
public static Font createFont(HSSFWorkbook wb,short boldweight,short color,short size){ |
147 |
Font font=wb.createFont(); |
148 |
font.setBoldweight(boldweight); |
149 |
font.setColor(color); |
150 |
font.setFontHeightInPoints(size); |
151 |
return font; |
152 |
} |
153 |
/** |
154 |
* 设置合并单元格的边框样式 |
155 |
* @param sheet HSSFSheet |
156 |
* @param ca CellRangAddress |
157 |
* @param style CellStyle |
158 |
*/ |
159 |
public static void setRegionStyle(HSSFSheet sheet, CellRangeAddress ca,CellStyle style) { |
160 |
for (int i = ca.getFirstRow(); i <= ca.getLastRow(); i++) { |
161 |
HSSFRow row = HSSFCellUtil.getRow(i, sheet); |
162 |
for (int j = ca.getFirstColumn(); j <= ca.getLastColumn(); j++) { |
163 |
HSSFCell cell = HSSFCellUtil.getCell(row, j); |
164 |
cell.setCellStyle(style); |
165 |
} |
166 |
} |
167 |
} |
168 |
} |
