由於近期項目要用到excel來轉存頁面中的表單數據,對poi操作excel進行瞭一番瞭解,寫瞭以下,但總覺的不是很好
特此:發佈在此供大傢評論,廣義集思,還望多多指教
1. workBook處理類
[java]
/**
* 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工具類
[java]
/**
* 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工具類
[java]
/**
* 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風格工具類
[java]
/**
* 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工廠類
[html]
/**
* 創建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+"秒,用時");
}
}