一、前言
通过if-util-3.2.8源码包中分别基于jxl、poi两种不同的方式实现com.bill99.seashell.common.util.excel.jxl.ExcelUtils、com.bill99.seashell.common.util.excel.ExcelUtil工具类,对获取创建工作簿createWorkBook的jxl.write.WritableWorkbook、org.apache.poi.hssf.usermodel.HSSFWorkbook对象,各自通过其相应的WritableSheet、HSSFSheet,获取并创建org.apache.poi.hssf.usermodel.HSSFRow、 org.apache.poi.hssf.usermodel.HSSFCell记录行或单元格,具体应用实例可以参考“Java通过jxl实现生成excel文件报表代码示例(两种生成方式)”文章
二、源码说明
1.jxl的ExcelUtils工具类(或参考其他简单用例文章)
package com.bill99.seashell.common.util.excel.jxl;@b@@b@import com.bill99.seashell.common.util.StringUtil;@b@import java.beans.PropertyDescriptor;@b@import java.io.IOException;@b@import java.util.LinkedList;@b@import java.util.List;@b@import jxl.Cell;@b@import jxl.Sheet;@b@import jxl.format.Alignment;@b@import jxl.format.VerticalAlignment;@b@import jxl.write.Label;@b@import jxl.write.WritableCellFormat;@b@import jxl.write.WritableSheet;@b@import jxl.write.WritableWorkbook;@b@import jxl.write.WriteException;@b@import org.springframework.beans.BeanWrapper;@b@import org.springframework.beans.BeanWrapperImpl;@b@import org.springframework.beans.MutablePropertyValues;@b@import org.springframework.beans.PropertyValue;@b@@b@public class ExcelUtils@b@{@b@ public static List getListByReadShell(Sheet sheet, int beginRow, int beginCol, int colCount, String[] colPropertity, Class targetClass)@b@ {@b@ int rowCount = sheet.getRows();@b@ return getListByReadShellAndRowCount(sheet, beginRow, beginCol, colCount, rowCount, colPropertity, targetClass);@b@ }@b@@b@ public static List getListByReadShellAndMinusCol(Sheet sheet, int beginRow, int beginCol, int colCount, int minusCol, String[] colPropertity, Class targetClass)@b@ {@b@ int rowCount = sheet.getRows() - minusCol;@b@ return getListByReadShellAndRowCount(sheet, beginRow, beginCol, colCount, rowCount, colPropertity, targetClass);@b@ }@b@@b@ public static List getListByReadShellAndRowCount(Sheet sheet, int beginRow, int beginCol, int colCount, int rowCount, String[] colPropertity, Class targetClass)@b@ {@b@ BeanWrapper sourceBw = null;@b@ List resultList = new LinkedList();@b@@b@ for (int i = beginRow; i < rowCount; ++i) {@b@ boolean isEmpty = true;@b@ sourceBw = new BeanWrapperImpl(targetClass);@b@ MutablePropertyValues values = new MutablePropertyValues();@b@@b@ for (int j = beginCol; j < colCount; ++j) {@b@ Cell cell = null;@b@ try {@b@ cell = sheet.getCell(j, i);@b@ }@b@ catch (ArrayIndexOutOfBoundsException e)@b@ {@b@ break label169:@b@ }@b@ if (null == cell)@b@ {@b@ break;@b@ }@b@ String value = cell.getContents();@b@ if (!(StringUtil.isNull(value))) {@b@ value = value.trim();@b@ isEmpty = false;@b@ }@b@ PropertyDescriptor targetDesc = sourceBw.getPropertyDescriptor(colPropertity[j]);@b@@b@ if ((targetDesc.getWriteMethod() != null) && (targetDesc.getReadMethod() != null))@b@ {@b@ values.addPropertyValue(new PropertyValue(colPropertity[j], value));@b@ }@b@ }@b@@b@ if (!(isEmpty)) {@b@ sourceBw.setPropertyValues(values);@b@ resultList.add(sourceBw.getWrappedInstance());@b@ }@b@ }@b@ label169: return resultList;@b@ }@b@@b@ public static WritableWorkbook getBook(WritableWorkbook book, int sheetIndex, String sheetName, String[] viewCol, String[] classPropertity, List resultList, Class bean)@b@ throws WriteException, IOException@b@ {@b@ WritableSheet sheet1 = book.createSheet(sheetName, sheetIndex);@b@ WritableCellFormat format1 = new WritableCellFormat();@b@@b@ format1.setAlignment(Alignment.CENTRE);@b@@b@ format1.setVerticalAlignment(VerticalAlignment.CENTRE);@b@@b@ Label label = null;@b@ int i = 0; for (int len = viewCol.length; i < len; ++i) {@b@ label = new Label(i, 0, viewCol[i]);@b@ sheet1.addCell(label);@b@ }@b@ BeanWrapper sourceBw = null;@b@ int i = 0; for (int len = resultList.size(); i < len; ++i) {@b@ sourceBw = new BeanWrapperImpl(resultList.get(i));@b@ int j = 0; for (int db_len = classPropertity.length; j < db_len; ++j) {@b@ String cloName = classPropertity[j];@b@ if (StringUtil.isEmpty(cloName))@b@ label = new Label(j, i + 1, "");@b@ else {@b@ label = new Label(j, i + 1, String.valueOf(sourceBw.getPropertyValue(classPropertity[j])));@b@ }@b@@b@ sheet1.addCell(label);@b@ }@b@ }@b@ book.write();@b@@b@ return book;@b@ }@b@@b@ public static WritableWorkbook getBook(WritableWorkbook book, int sheetIndex, String sheetName, List<String[]> viewCol, String[] classPropertity, List resultList, Class bean)@b@ throws WriteException, IOException@b@ {@b@ int i;@b@ WritableSheet sheet1 = book.createSheet(sheetName, sheetIndex);@b@ WritableCellFormat format1 = new WritableCellFormat();@b@@b@ format1.setAlignment(Alignment.CENTRE);@b@@b@ format1.setVerticalAlignment(VerticalAlignment.CENTRE);@b@@b@ Label label = null;@b@ int rows = 0;@b@ if ((viewCol != null) && (viewCol.size() > 0)) {@b@ rows = viewCol.size();@b@ for (i = 0; i < rows; ++i) {@b@ String[] cols = (String[])viewCol.get(i);@b@ int j = 0; for (int len = cols.length; j < len; ++j) {@b@ label = new Label(j, i, cols[j]);@b@ sheet1.addCell(label);@b@ }@b@ }@b@ }@b@ BeanWrapper sourceBw = null;@b@ int i = 0; for (int len = resultList.size(); i < len; ++i) {@b@ sourceBw = new BeanWrapperImpl(resultList.get(i));@b@ int j = 0; for (int db_len = classPropertity.length; j < db_len; ++j) {@b@ label = new Label(j, i + rows, String.valueOf(sourceBw.getPropertyValue(classPropertity[j])));@b@@b@ sheet1.addCell(label);@b@ }@b@ }@b@ book.write();@b@@b@ return book;@b@ }@b@@b@ public static WritableWorkbook getBookNotWrite(WritableWorkbook book, int sheetIndex, String sheetName, String[] viewCol, String[] classPropertity, List resultList, Class bean)@b@ throws WriteException, IOException@b@ {@b@ WritableSheet sheet1 = book.createSheet(sheetName, sheetIndex);@b@ WritableCellFormat format1 = new WritableCellFormat();@b@@b@ format1.setAlignment(Alignment.CENTRE);@b@@b@ format1.setVerticalAlignment(VerticalAlignment.CENTRE);@b@@b@ Label label = null;@b@ int i = 0; for (int len = viewCol.length; i < len; ++i) {@b@ label = new Label(i, 0, viewCol[i]);@b@ sheet1.addCell(label);@b@ }@b@ BeanWrapper sourceBw = null;@b@ int i = 0; for (int len = resultList.size(); i < len; ++i) {@b@ sourceBw = new BeanWrapperImpl(resultList.get(i));@b@ int j = 0; for (int db_len = classPropertity.length; j < db_len; ++j) {@b@ label = new Label(j, i + 1, String.valueOf(sourceBw.getPropertyValue(classPropertity[j])));@b@@b@ sheet1.addCell(label);@b@ }@b@ }@b@@b@ return book;@b@ }@b@@b@ public static WritableSheet getSheet(WritableSheet sheet, int begRow, String[] viewCol, String[] classPropertity, List resultList)@b@ throws WriteException, IOException@b@ {@b@ WritableCellFormat format1 = new WritableCellFormat();@b@@b@ format1.setAlignment(Alignment.CENTRE);@b@@b@ format1.setVerticalAlignment(VerticalAlignment.CENTRE);@b@@b@ Label label = null;@b@ int i = 0; for (int len = viewCol.length; i < len; ++i) {@b@ label = new Label(i, begRow, viewCol[i]);@b@ sheet.addCell(label);@b@ }@b@ BeanWrapper sourceBw = null;@b@ int i = 0; for (int len = resultList.size(); i < len; ++i) {@b@ sourceBw = new BeanWrapperImpl(resultList.get(i));@b@ int j = 0; for (int db_len = classPropertity.length; j < db_len; ++j) {@b@ label = new Label(j, begRow + i + 1, String.valueOf(sourceBw.getPropertyValue(classPropertity[j])));@b@@b@ sheet.addCell(label);@b@ }@b@ }@b@ return sheet;@b@ }@b@}
2.poi的ExcelUtil
package com.bill99.seashell.common.util.excel;@b@@b@import java.io.FileInputStream;@b@import java.io.FileOutputStream;@b@import java.io.IOException;@b@import java.util.ArrayList;@b@import java.util.Iterator;@b@import java.util.List;@b@import org.apache.poi.hssf.usermodel.HSSFCell;@b@import org.apache.poi.hssf.usermodel.HSSFRow;@b@import org.apache.poi.hssf.usermodel.HSSFSheet;@b@import org.apache.poi.hssf.usermodel.HSSFWorkbook;@b@import org.apache.poi.poifs.filesystem.POIFSFileSystem;@b@@b@public final class ExcelUtil@b@{@b@ public static Book toBook(String fileName)@b@ throws IOException@b@ {@b@ POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileName));@b@ HSSFWorkbook hssfWorkBook = new HSSFWorkbook(fs);@b@ Book book = getBook(hssfWorkBook);@b@ book.setBookName(fileName);@b@ return book;@b@ }@b@@b@ public static void toFile(Book book, String fileName)@b@ throws IOException@b@ {@b@ HSSFWorkbook hssfWorkbook = createWorkBook(book);@b@ FileOutputStream fos = new FileOutputStream(fileName);@b@ hssfWorkbook.write(fos);@b@ fos.close();@b@ }@b@@b@ private static Book getBook(HSSFWorkbook hssfWorkbook) {@b@ List sheets = new ArrayList();@b@ Sheet sheet = null;@b@@b@ for (int i = 0; i < hssfWorkbook.getNumberOfSheets(); ++i) {@b@ sheet = new Sheet();@b@ sheet.setSheetName(hssfWorkbook.getSheetName(i));@b@ sheet.setRows(getRows(hssfWorkbook.getSheetAt(i)));@b@ sheets.add(sheet);@b@ }@b@ Book book = new Book();@b@ book.setSheets(sheets);@b@ return book;@b@ }@b@@b@ private static List getRows(HSSFSheet hssfSheet) {@b@ List rows = new ArrayList();@b@ Row row = null;@b@@b@ for (int i = 0; i < hssfSheet.getLastRowNum() + 1; ++i) {@b@ row = new Row();@b@ row.setCells(getCells(hssfSheet.getRow(i)));@b@ rows.add(row);@b@ }@b@ return rows;@b@ }@b@@b@ private static List getCells(HSSFRow hssfRow) {@b@ List cells = new ArrayList();@b@ Cell cell = null;@b@ if (hssfRow == null) {@b@ return cells;@b@ }@b@@b@ for (short i = 0; i < hssfRow.getLastCellNum(); i = (short)(i + 1)) {@b@ HSSFCell hssfCell = hssfRow.getCell(i);@b@ cell = new Cell(getStringValue(hssfCell));@b@ cells.add(cell);@b@ }@b@ return cells;@b@ }@b@@b@ private static String getStringValue(HSSFCell hssfCell) {@b@ if (hssfCell == null)@b@ return "";@b@@b@ if (1 == hssfCell.getCellType()) {@b@ return hssfCell.getStringCellValue();@b@ }@b@@b@ if (0 == hssfCell.getCellType()) {@b@ return String.valueOf((int)hssfCell.getNumericCellValue());@b@ }@b@@b@ if (4 == hssfCell.getCellType()) {@b@ return String.valueOf(hssfCell.getBooleanCellValue());@b@ }@b@@b@ if (3 == hssfCell.getCellType()) {@b@ return hssfCell.getStringCellValue();@b@ }@b@@b@ return "";@b@ }@b@@b@ private static HSSFWorkbook createWorkBook(Book book) {@b@ HSSFWorkbook hssfWorkbook = new HSSFWorkbook();@b@ HSSFSheet hssfSheet = null;@b@@b@ List sheets = book.getSheets();@b@ Sheet sheet = null;@b@@b@ for (Iterator it = sheets.iterator(); it.hasNext(); ) {@b@ sheet = (Sheet)it.next();@b@ hssfSheet = hssfWorkbook.createSheet(sheet.getSheetName());@b@ createRows(hssfSheet, sheet.getRows());@b@ }@b@ return hssfWorkbook;@b@ }@b@@b@ private static void createRows(HSSFSheet hssfSheet, List rows) {@b@ HSSFRow hssfRow = null;@b@ Row row = null;@b@ int i = 0;@b@@b@ for (Iterator it = rows.iterator(); it.hasNext(); ++i) {@b@ row = (Row)it.next();@b@ hssfRow = hssfSheet.createRow(i);@b@ createCells(hssfRow, row.getCells());@b@ }@b@ }@b@@b@ private static void createCells(HSSFRow hssfRow, List cells) {@b@ HSSFCell hssfCell = null;@b@ Cell cell = null;@b@ short i = 0;@b@@b@ for (Iterator it = cells.iterator(); it.hasNext(); i = (short)(i + 1)) {@b@ cell = (Cell)it.next();@b@ hssfCell = hssfRow.createCell(i);@b@ hssfCell.setCellType(1);@b@ hssfCell.setCellValue(cell.getValue());@b@ }@b@ }@b@}
package com.bill99.seashell.common.util.excel;@b@@b@import java.util.ArrayList;@b@import java.util.Iterator;@b@import java.util.List;@b@@b@public class Book@b@{@b@ private List sheets;@b@ private String bookName;@b@@b@ public Book()@b@ {@b@ }@b@@b@ public Book(String bookName)@b@ {@b@ this.bookName = bookName;@b@ }@b@@b@ public Book(List sheets)@b@ {@b@ this.sheets = sheets;@b@ }@b@@b@ public Book(String bookName, List sheets)@b@ {@b@ this.bookName = bookName;@b@ this.sheets = sheets;@b@ }@b@@b@ public List getSheets()@b@ {@b@ return ((this.sheets == null) ? (this.sheets = new ArrayList()) : this.sheets);@b@ }@b@@b@ public void setSheets(List sheets)@b@ {@b@ this.sheets = sheets;@b@ }@b@@b@ public Sheet getSheet(int index)@b@ {@b@ if ((index < 0) || (index >= getSheets().size())) {@b@ throw new IllegalArgumentException();@b@ }@b@@b@ return ((Sheet)getSheets().toArray()[index]);@b@ }@b@@b@ public void addSheet(Sheet sheet)@b@ {@b@ getSheets().add(sheet);@b@ }@b@@b@ public String getBookName()@b@ {@b@ return this.bookName;@b@ }@b@@b@ public void setBookName(String bookName)@b@ {@b@ this.bookName = bookName;@b@ }@b@@b@ public String toString()@b@ {@b@ String delimit = "\r\n";@b@ String result = "Book [" + getBookName() + "]" + delimit;@b@ for (Iterator iter = getSheets().iterator(); iter.hasNext(); )@b@ result = result + ((Sheet)iter.next()).toString() + delimit;@b@@b@ return result;@b@ }@b@}
package com.bill99.seashell.common.util.excel;@b@@b@import java.util.ArrayList;@b@import java.util.Iterator;@b@import java.util.List;@b@@b@public class Sheet@b@{@b@ private List rows;@b@ private String sheetName;@b@@b@ public Sheet()@b@ {@b@ }@b@@b@ public Sheet(List rows)@b@ {@b@ this.rows = rows;@b@ }@b@@b@ public Sheet(String sheetName)@b@ {@b@ this.sheetName = sheetName;@b@ }@b@@b@ public Sheet(String sheetName, List rows)@b@ {@b@ this.sheetName = sheetName;@b@ this.rows = rows;@b@ }@b@@b@ public Row getRow(int rowNo)@b@ {@b@ if ((rowNo < 0) || (rowNo >= getRows().size()))@b@ throw new IllegalArgumentException();@b@@b@ return ((Row)getRows().toArray()[rowNo]);@b@ }@b@@b@ public void addRow(Row row)@b@ {@b@ getRows().add(row);@b@ }@b@@b@ public String getSheetName()@b@ {@b@ return this.sheetName;@b@ }@b@@b@ public void setSheetName(String sheetName)@b@ {@b@ this.sheetName = sheetName;@b@ }@b@@b@ public List getRows()@b@ {@b@ return ((this.rows == null) ? (this.rows = new ArrayList()) : this.rows);@b@ }@b@@b@ public void setRows(List rows)@b@ {@b@ this.rows = rows;@b@ }@b@@b@ public String toString()@b@ {@b@ String delimit = "\r\n";@b@ String result = "Sheet [" + getSheetName() + "]" + delimit;@b@ for (Iterator it1 = getRows().iterator(); it1.hasNext(); )@b@ result = result + ((Row)it1.next()).toString() + delimit;@b@@b@ return result;@b@ }@b@}
package com.bill99.seashell.common.util.excel;@b@@b@import java.util.ArrayList;@b@import java.util.Iterator;@b@import java.util.List;@b@@b@public class Row@b@{@b@ private List cells;@b@@b@ public Row()@b@ {@b@ }@b@@b@ public Row(List cells)@b@ {@b@ this.cells = cells;@b@ }@b@@b@ public Cell getCell(int cellNo)@b@ {@b@ if ((cellNo < 0) || (cellNo >= getCells().size()))@b@ throw new IllegalArgumentException();@b@@b@ return ((Cell)getCells().toArray()[cellNo]);@b@ }@b@@b@ public void addCell(Cell cell)@b@ {@b@ getCells().add(cell);@b@ }@b@@b@ public List getCells()@b@ {@b@ return ((this.cells == null) ? (this.cells = new ArrayList()) : this.cells);@b@ }@b@@b@ public void setCells(List cells)@b@ {@b@ this.cells = cells;@b@ }@b@@b@ public String toString()@b@ {@b@ String delimit = "; ";@b@ String result = "";@b@ for (Iterator it = getCells().iterator(); it.hasNext(); )@b@ result = result + ((Cell)it.next()) + delimit;@b@@b@ return result;@b@ }@b@}
package com.bill99.seashell.common.util.excel;@b@@b@public class Cell@b@{@b@ private String value;@b@@b@ public Cell()@b@ {@b@ }@b@@b@ public Cell(String value)@b@ {@b@ this.value = value;@b@ }@b@@b@ public String getValue()@b@ {@b@ return this.value;@b@ }@b@@b@ public void setValue(String value)@b@ {@b@ this.value = value;@b@ }@b@@b@ public String toString()@b@ {@b@ return this.value;@b@ }@b@}