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工厂类 Java代码

/**

* 创建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\ 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\ long endTime = System.currentTimeMillis();

System.out.println(\"测试55000,总计\"+(endTime-beginTime)/1000+\"秒,用时\"); } } /**

* 创建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\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\";

filename=\" +

}

}

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\long endTime = System.currentTimeMillis();

System.out.println(\"测试55000,总计\"+(endTime-beginTime)/1000+\"秒,用时\");

以上是创建Excel所有工具类,还望大家指教!!!!!!