java 利用 poi 生成 Excel文件的例子
來源：程序員人生 發(fā)布時間：2014-11-18 08:23:01 閱讀次數(shù)：2608次
在用java 寫http://www.vxbq.cn/db/利用的時候, 通常會生成各種報表，而這些報表可能會被導出為各種格式的文件，比如Excel文檔，pdf 文檔等等. 今天先做了1個生成Excel
文檔的例子，主要解決以下問題：
1. 生成 Excel 文檔.
2. 保護生成Excel文檔，設置密碼訪問.
3. 自動對生成的Excel 文檔第1行標題欄設置成filter 過濾情勢, 方便用戶使用.
用 apache POI 生成 Excel 文檔公用類
程序代碼
package com.yihaomen.poi.sample;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
/**
* Excel util, create excel sheet, cell and style.
* @param <T> generic class.
*/
public class ExcelUtil<T> {
public HSSFCellStyle getCellStyle(HSSFWorkbook workbook,boolean isHeader){
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setLocked(true);
if (isHeader) {
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
}
return style;
}
public void generateHeader(HSSFWorkbook workbook,HSSFSheet sheet,String[] headerColumns){
HSSFCellStyle style = getCellStyle(workbook,true);
Row row = sheet.createRow(0);
row.setHeightInPoints(30);
for(int i=0;i<headerColumns.length;i++){
Cell cell = row.createCell(i);
String[] column = headerColumns[i].split("_#_");
sheet.setColumnWidth(i, Integer.valueOf(column[1]));
cell.setCellValue(column[0]);
cell.setCellStyle(style);
}
}
@SuppressWarnings({ "rawtypes", "unchecked" })
public HSSFSheet creatAuditSheet(HSSFWorkbook workbook,String sheetName,
List<T> dataset,String[] headerColumns,String[] fieldColumns) throws NoSuchMethodException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
HSSFSheet sheet = workbook.createSheet(sheetName);
sheet.protectSheet("1234");//設置Excel保護密碼
generateHeader(workbook,sheet,headerColumns);
HSSFCellStyle style = getCellStyle(workbook,false);
SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
int rowNum = 0;
for(T t:dataset){
rowNum++ ;
Row row = sheet.createRow(rowNum);
row.setHeightInPoints(25);
for(int i = 0; i < fieldColumns.length; i++){
String fieldName = fieldColumns[i] ;
String getMethodName = "get" + fieldName.substring(0,1).toUpperCase() + fieldName.substring(1);
try {
Class clazz = t.getClass();
Method getMethod;
getMethod = clazz.getMethod(getMethodName, new Class[]{} );
Object value = getMethod.invoke(t, new Object[]{});
String cellValue = "";
if (value instanceof Date){
Date date = (Date)value;
cellValue = sd.format(date);
}else{
cellValue = null != value ? value.toString() : "";
}
Cell cell = row.createCell(i);
cell.setCellStyle(style);
cell.setCellValue(cellValue);
} catch (Exception e) {
}
}
}
return sheet;
}
}
這1個公用的類，主要生成Excel的頭，正文，和Excel 文檔的樣式。看方法名稱基本就能夠知道這個方法是干甚么用的.
寫1個測試類測試生成Excel文檔
程序代碼
package com.yihaomen.poi.test;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.yihaomen.poi.sample.ExcelUtil;
import com.yihaomen.poi.sample.User;
public class PoiTest {
/*excel column formate:column_#_width, excel中每列的名稱*/
public static final String[] RECORES_COLUMNS = new String[]{
"User Name_#_3000",
"Address_#_7000"
};
/*the column will display on xls files. must the same as the entity fields.對應上面的字段.*/
public static final String[] RECORES_FIELDS = new String[]{
"name","address"
};
public static void main(String[] args) throws NoSuchMethodException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, IOException {
List<User> users = new ArrayList<User>();
for(int i=0; i<10;i++){
User u = new User();
u.setAddress("address :" + i);
u.setName("name: "+ i);
u.setAge(i);
users.add(u);
}
//實際項目中，這個list 估計是從http://www.vxbq.cn/db/中得到的
HSSFWorkbook workbook = new HSSFWorkbook();
ExcelUtil<User> userSheet = new ExcelUtil<User>();
userSheet.creatAuditSheet(workbook, "user sheet xls",
users, RECORES_COLUMNS, RECORES_FIELDS);
FileOutputStream fileOut = new FileOutputStream("D:/test.xls");
workbook.write(fileOut);
fileOut.close();
}
}
程序代碼
package com.yihaomen.poi.sample;
public class User {
private String name;
private int age;
private String address;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
運行測試類，生成Excel 文檔以下：
生成的文檔，你是不能修改的，緣由很簡單，設置了1個空密碼，雖然是空密碼，Excel 還是會出現(xiàn)提示，固然你可以直接解保護. 實現(xiàn)的主要代碼是在 ExcelUtil 中的 creatAuditSheet 方法中的：
程序代碼
sheet.protectSheet(""); // 設置了1個空的密碼
如果要實現(xiàn)第1行標題自動過濾怎樣處理呢，可以在上面提到的方法中加入以下代碼：
程序代碼
char[] endChar = Character.toChars( 'A' + (headerColumns.length - 1) );
String rangeAddress = "A1:" + String.valueOf(endChar) + "1";
sheet.setAutoFilter(CellRangeAddress.valueOf(rangeAddress));
生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對您的學習有所幫助,可以手機掃描二維碼進行捐贈
