摘要：接著上一篇基于的和開源庫導出大數(shù)據(jù)報表一的遺留的問題開始，這篇做優(yōu)化處理。這個錯誤造成的直接問題是數(shù)據(jù)空白，因為只會執(zhí)行一次，第二次條件就為了。幾經(jīng)波折，終于知道，引起錯誤的原因是包沖突，和包的沖突。

接著上一篇《基于haddop的HDFS和Excel開源庫POI導出大數(shù)據(jù)報表(一)》的遺留的問題開始，這篇做優(yōu)化處理。

在一開始的時候，當我獲取到訂單的數(shù)量，遍歷訂單，獲取用戶id和用戶的地址id，逐條查詢，可想而知，1w條數(shù)據(jù)，我要查詢數(shù)據(jù)庫1w*2，這種資源消耗是傷不起的，小號的時間大多數(shù)花在了查詢上面。

后來，做了一次優(yōu)化，將用戶id和地址id分別放入到list中，每500條查詢一次，加入有1w條，可以執(zhí)行查詢(10000 / 500) = 20,只需要查詢20次即可，一般而言這個數(shù)目更小，原因用戶id重復，同一個用戶有很多訂單，這樣選擇set比起list好很多，那么查詢次數(shù)又降低了很多。

@Component("userService")
@Path("user")
@Produces({ContentType.APPLICATION_JSON_UTF_8})
public class UserServiceImpl implements UserService {
    private static final int PER_TIMES = 500;
    @Resource
    private UserRepo repo;
    @Override
    @GET
    @Path("/users")
    public Map getUsersByUids(List uids) {
        if (uids == null || uids.size() <= 0) {
            return null;
        }
        Map map = new HashMap<>();
        int times = uids.size() > PER_TIMES ? (int) Math.ceil(uids.size() * 1.0 / PER_TIMES) : 1;
        for (int i = 0; i < times; i++) { // 執(zhí)行多少次查詢
            StringBuffer strUids = new StringBuffer();
            strUids.append("(0");
            for (int j = i * PER_TIMES; j < ((i + 1) * PER_TIMES) && j < uids.size(); j++) { // 每次查詢多少條數(shù)據(jù)
                strUids.append(",").append(uids.get(j));
            }
            strUids.append(")");
            String uid = strUids.toString();
            //
           Map m = repo.getUserByUids(uid);
            if (m != null && m.size() > 0) {
                System.out.println("第" + i + "次循環(huán),返回數(shù)據(jù)" + m.size());
                map.putAll(m);
            }
        }
        return map;
    }
    // ... 其他的業(yè)務邏輯
}

在使用內(nèi)部for循壞的時候，我犯了基本的算法錯誤,原來的代碼：

// ... 
// size 是第一個for循壞外面的變量，初識值為 size = uids.size();
StringBuffer strUids = new StringBuffer();
strUids.append("(0");
for (int j = i * PER_TIMES; j < PER_TIMES && j < size; j++) {
   strUids.append(",").append(uids.get(j));
}
size = size - (i + 1) * PER_TIMES;
strUids.append(")");
String uid = strUids.toString();

// ...

是的，你沒看錯，這個錯誤我犯了，記在這里，是為了提醒以后少犯這樣低級的錯誤。不管外部循環(huán)如何，里面的size值一直在減小，PER_TIMES值不變。
假如 PER_TIMES =500; i = 2; 那么里面的for是這樣的，j = 2 * 500;j < 500 && j < (1000 - 500); j++;錯誤就在這里了，1000 < 500永遠為false，再說了size的值一直在減小，j也會小于size。

這個錯誤造成的直接問題是數(shù)據(jù)空白，因為只會執(zhí)行一次，第二次條件就為false了。

在接口傳來的數(shù)據(jù)類似這樣的json：

{
    "params": {
        "starttm": 1469980800000
    },
    "filename": "2016-08-28-訂單.xlsx",
    "header": {
        "crtm": "下單時間",
        "paytm": "付款時間",
        "oid": "訂單ID",
        "iid": "商品ID",
        "title": "商品標題",
        "type": "商品類型",
        "quantity": "購買數(shù)量",
        "user": "買家用戶名",
        "uid": "買家ID",
        "pro": "省市",
        "city": "區(qū)縣",
        "addr": "買家地址",
        "status": "訂單狀態(tài)",
        "refund": "退款狀態(tài)",
        "pri": "單價",
        "realpay": "實付款",
        "tel": "電話",
        "rec": "收件人姓名",
        "sex": "性別",
        "comment": "備注"
    }
}

按照header字段的key生成數(shù)據(jù)，所以，一開始我是拿key通過反射獲取get+"Key"值得，但是這樣導致很慢。

/**
 * 直接讀取對象屬性值, 無視private/protected修飾符, 不經(jīng)過getter函數(shù).
 * @param obj
 * @param fieldName
 * @return
 */
public static Object getFieldValue(final Object obj, final String fieldName) {
    Field field = getAccessibleField(obj, fieldName);
    if (null == field) {
        throw new IllegalArgumentException("Could not find field [" + fieldName + "] on " +
                "target [" + obj + "]");
    }
    Object result = null;
    try {
        result = field.get(obj);
    } catch (IllegalAccessException e) {
        LOGGER.error("不可能拋出的異常{}" + e);
    }
    return result;
}

/**
 * 循環(huán)向上轉(zhuǎn)型, 獲取對象的DeclaredField,   并強制設置為可訪問.
 * 如向上轉(zhuǎn)型到Object仍無法找到, 返回null.
 * @param obj
 * @param fieldName
 * @return
 */
public static Field getAccessibleField(final Object obj, final String fieldName) {
    Assert.notNull(obj, "OBJECT不能為空");
    Assert.hasText(fieldName, "fieldName");
    for (Class superClass = obj.getClass(); superClass != Object.class; superClass = superClass.getSuperclass()) {
        try {
            Field field = superClass.getDeclaredField(fieldName);
            field.setAccessible(true);
            return field;
        } catch (NoSuchFieldException e) {
            //NOSONAR
            // Field不在當前類定義,繼續(xù)向上轉(zhuǎn)型
        }
    }
    return null;
}

因為這些字段來自多個不同的對象，可能某些字段注入會失敗，當注入失敗的時候嘗試注入到另一個對象。我覺得耗時也在這地方，后來修改成直接使用getter方法獲取，速度也有提升。

錯誤：

javax.servlet.ServletException: Servlet execution threw an exception
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:123)

test環(huán)境和dev環(huán)境均好，但是線上環(huán)境報錯。幾經(jīng)波折，終于知道，引起錯誤的原因是jar包沖突，resteasy和jersey包的沖突。項目中如何引入的jersey，這就得跟hadoop有關。

研究后發(fā)現(xiàn)，hadoop并不一定需要jersey，因此果斷舍棄掉jersey包：

compile ("org.apache.hadoop:hadoop-common:2.7.2") {
    exclude(module: "jersey")
    exclude(module: "contribs")
}
compile ("org.apache.hadoop:hadoop-hdfs:2.7.2") {
    exclude(module: "jersey")
    exclude(module: "contribs")
}
compile ("org.apache.hadoop:hadoop-client:2.7.2") {
    exclude(module: "jersey")
    exclude(module: "contribs")
}

盡管項目rest接口報錯，但是啟動不會報錯，mq的執(zhí)行正常。原因，大家都看到，jar包沖突，引起web中的過濾器根本不去請求路由，直接被過濾掉了。

public class HDFSUtils {
    private static FileSystem fs = null;

    public static FileSystem getFileSystem(Configuration conf)
            throws IOException, URISyntaxException {
        if (null == fs) {
            fs = FileSystem.get(conf);
        }

        return fs;
    }

    /**
     * 判斷路徑是否存在
     *
     * @param conf hadoop 配置
     * @param path hadoop 文件路徑
     * @return 文件是否存在
     * @throws IOException
     */
    public static boolean exits(Configuration conf, String path) throws IOException,
            URISyntaxException {
        FileSystem fs = getFileSystem(conf);
        return fs.exists(new Path(path));
    }

    /**
     * 創(chuàng)建文件
     *
     * @param conf hadoop 配置
     * @param filePath 本地文件路徑
     * @param contents 文件內(nèi)容
     * @throws IOException
     */
    public static void createFile(Configuration conf, String filePath, byte[] contents)
            throws IOException, URISyntaxException {
        try (FileSystem fs = getFileSystem(conf);
             FSDataOutputStream outputStream = createFromFileSystem(fs, filePath)) {

            outputStream.write(contents, 0, contents.length);
            outputStream.hflush();
        }
    }

    private static FSDataOutputStream createFromFileSystem(FileSystem fs, String filePath)
            throws IOException {
        Path path = new Path(filePath);
        return fs.create(path);
    }

    private static FSDataInputStream openFromFileSystem(FileSystem fs, String filePath)
            throws IOException {
        Path path = new Path(filePath);
        return fs.open(path);
    }

    /**
     * 創(chuàng)建文件
     *
     * @param conf hadoop 配置
     * @param filePath 本地文件路徑
     * @param workbook excel workbook 內(nèi)容
     * @throws IOException
     */
    public static void createFile(Configuration conf, String filePath, Workbook workbook)
            throws IOException, URISyntaxException {
        try (FileSystem fs = getFileSystem(conf);
             FSDataOutputStream outputStream = createFromFileSystem(fs, filePath)) {

            ByteArrayOutputStream os = new ByteArrayOutputStream();
            workbook.write(os);

            outputStream.write(os.toByteArray());

            outputStream.hflush();
        }
    }

    /**
     * 創(chuàng)建文件
     *
     * @param conf hadoop 配置
     * @param filePath 本地文件路徑
     * @param contents 文件內(nèi)容
     * @throws IOException
     */
    public static void uploadWorkbook(Configuration conf, String filePath, byte[] contents)
            throws IOException, URISyntaxException {
        try (FileSystem fs = getFileSystem(conf);
             FSDataOutputStream outputStream = createFromFileSystem(fs, filePath)) {

            outputStream.write(contents, 0, contents.length);
            outputStream.hflush();
        }
    }

    /**
     * 創(chuàng)建文件
     *
     * @param conf hadoop 配置
     * @param filePath 本地文件路徑
     * @param fileContent 文件內(nèi)容
     * @throws IOException
     */
    public static void createFile(Configuration conf, String fileContent, String filePath)
            throws IOException, URISyntaxException {
        createFile(conf, filePath, fileContent.getBytes());
    }

    /**
     * 上傳文件
     *
     * @param conf hadoop 配置
     * @param localFilePath 本地文件路徑
     * @param remoteFilePath 遠程文件路徑
     * @throws IOException
     */
    public static void copyFromLocalFile(Configuration conf, String localFilePath, String remoteFilePath)
            throws IOException, URISyntaxException {
        try (FileSystem fs = getFileSystem(conf)) {
            Path localPath = new Path(localFilePath);
            Path remotePath = new Path(remoteFilePath);
            fs.copyFromLocalFile(true, true, localPath, remotePath);
        }
    }

    /**
     * 刪除目錄或文件
     *
     * @param conf hadoop 配置
     * @param remoteFilePath 遠程文件路徑
     * @param recursive if the subdirectories need to be traversed recursively
     * @return 是否成功
     * @throws IOException
     */
    public static boolean deleteFile(Configuration conf, String remoteFilePath, boolean recursive)
            throws IOException, URISyntaxException {
        try (FileSystem fs = getFileSystem(conf)) {
            return fs.delete(new Path(remoteFilePath), recursive);
        }
    }

    /**
     * 刪除目錄或文件(如果有子目錄,則級聯(lián)刪除)
     *
     * @param conf hadoop 配置
     * @param remoteFilePath 遠程文件路徑
     * @return 是否成功
     * @throws IOException
     */
    public static boolean deleteFile(Configuration conf, String remoteFilePath)
            throws IOException, URISyntaxException {
        return deleteFile(conf, remoteFilePath, true);
    }

    /**
     * 文件重命名
     *
     * @param conf hadoop 配置
     * @param oldFileName 原始文件名
     * @param newFileName 新文件名
     * @return 是否成功
     * @throws IOException
     */
    public static boolean renameFile(Configuration conf, String oldFileName, String newFileName)
            throws IOException, URISyntaxException {
        try (FileSystem fs = getFileSystem(conf)) {
            Path oldPath = new Path(oldFileName);
            Path newPath = new Path(newFileName);

            return fs.rename(oldPath, newPath);
        }
    }

    /**
     * 創(chuàng)建目錄
     *
     * @param conf hadoop 配置
     * @param dirName hadoop 目錄名
     * @return 是否成功
     * @throws IOException
     */
    public static boolean createDirectory(Configuration conf, String dirName)
            throws IOException, URISyntaxException {
        try (FileSystem fs = getFileSystem(conf)) {
            Path dir = new Path(dirName);
            return fs.mkdirs(dir);
        }
    }

    /**
     * 列出指定路徑下的所有文件(不包含目錄)
     *
     * @param fs hadoop文件系統(tǒng)
     * @param basePath 基礎路徑
     * @param recursive if the subdirectories need to be traversed recursively
     */
    public static RemoteIterator listFiles(FileSystem fs, String basePath,
                                                              boolean recursive)
            throws IOException {
        return fs.listFiles(new Path(basePath), recursive);
    }

    /**
     * 列出指定路徑下的文件（非遞歸）
     *
     * @param conf hadoop 配置
     * @param basePath 基礎路徑
     * @return 文件狀態(tài)集合
     * @throws IOException
     */
    public static RemoteIterator listFiles(Configuration conf, String basePath)
            throws IOException, URISyntaxException {
        try (FileSystem fs = getFileSystem(conf)) {
            return fs.listFiles(new Path(basePath), false);
        }
    }

    /**
     * 列出指定目錄下的文件子目錄信息（非遞歸）
     *
     * @param conf hadoop 配置
     * @param dirPath 文件目錄
     * @return 文件狀態(tài)數(shù)組
     * @throws IOException
     */
    public static FileStatus[] listStatus(Configuration conf, String dirPath) throws IOException,
            URISyntaxException {
        try (FileSystem fs = getFileSystem(conf)) {
            return fs.listStatus(new Path(dirPath));
        }
    }


    /**
     * 讀取文件內(nèi)容并寫入outputStream中
     *
     * @param conf hadoop 配置
     * @param filePath 文件路徑
     * @param os 輸出流
     * @throws IOException
     */
    public static void readFile(Configuration conf, String filePath, OutputStream os) throws IOException,
            URISyntaxException {
        FileSystem fs = getFileSystem(conf);
        Path path = new Path(filePath);
        try (FSDataInputStream inputStream = fs.open(path)) {
            int c;
            while ((c = inputStream.read()) != -1) {
                os.write(c);
            }
        }
    }

    /**
     * 讀取文件內(nèi)容并返回
     * @param conf hadoop 配置
     * @param filePath 本地文件路徑
     * @return 文件內(nèi)容
     * @throws IOException
     * @throws URISyntaxException
     */
    public static String readFile(Configuration conf, String filePath)
            throws IOException, URISyntaxException {
        String fileContent;
        try (FileSystem fs = getFileSystem(conf);
             InputStream inputStream = openFromFileSystem(fs, filePath);
             ByteArrayOutputStream outputStream = new ByteArrayOutputStream(inputStream.available()))
        {
                IOUtils.copyBytes(inputStream, outputStream, conf);
                byte[] lens = outputStream.toByteArray();
                fileContent = new String(lens, "UTF-8");
        }

        return  fileContent;
    }
}

優(yōu)化1：所有的try{} finally{}均由try代替掉了。而把簡單代碼放到try()里面了。try()是java7的特性，叫自動資源釋放，具有關閉流的作用，不再手動去在finally中關閉各種stream和文件句柄，前提是，這些可關閉的資源必須實現(xiàn) java.lang.AutoCloseable 接口。

新增了一個方法：

public static void createFile(Configuration conf, String filePath, Workbook workbook)
        throws IOException, URISyntaxException {
    try (FileSystem fs = getFileSystem(conf);
         FSDataOutputStream outputStream = createFromFileSystem(fs, filePath)) {

        ByteArrayOutputStream os = new ByteArrayOutputStream();
        workbook.write(os);

        outputStream.write(os.toByteArray());

        outputStream.hflush();
    }
}

方法參數(shù)：hadoop配置，完整文件名，Workbook。這里通過workbook.write把Workbook寫到ByteArrayOutputStream中，然后把ByteArrayOutputStream流寫入到FSDataOutputStream流，再flush到磁盤。
這個優(yōu)化的原因：下載文件的時候，讀取流必須是POI的WorkBook的流，如果轉(zhuǎn)換成其他的流，發(fā)生亂碼。

package cn.test.web.utils;

import cn.common.util.Utils;
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hssf.record.crypto.Biff8EncryptionKey;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Footer;
import org.apache.poi.ss.usermodel.Header;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Properties;

/**
 * Created with presentation
 * User zhoujunwen
 * Date 16/8/11
 * Time 下午5:02
 */
public class POIUtils {
    private static final short HEADER_FONT_SIZE = 16; // 大綱字體
    private static final short FONT_HEIGHT_IN_POINTS = 14; // 行首字體
    private static final int MEM_ROW  = 100;

    public static Workbook createWorkbook(String file) {
        String ext = FilenameUtils.getExtension(CommonUtils.getFileName(file));
        Workbook wb = createSXSSFWorkbook(MEM_ROW);
        /*switch (ext) {
            case "xls":
                wb = createHSSFWorkbook();
                break;
            case "xlsx":
                wb = createXSSFWorkbook();
                break;
            default:
                wb = createHSSFWorkbook();
        }*/
        return wb;
    }

    public static Workbook createWorkbookByIS(String file, InputStream inputStream) {
        String ext = FilenameUtils.getExtension(CommonUtils.getFileName(file));
        Workbook wb = null;
        try {
            OPCPackage p = OPCPackage.open(inputStream);
            wb = new SXSSFWorkbook(new XSSFWorkbook(p), 100);
        } catch (Exception ex) {
            try {
                wb = new HSSFWorkbook(inputStream, false);
            } catch (IOException e) {
                wb = new XSSFWorkbook();
            }
        }
        return wb;
    }

    /**
     *
     * @param wb
     * @param file
     * @return
     */

    public static Workbook writeFile(Workbook wb, String file) {
        if (wb == null || Utils.isEmpty(file)) {
            return null;
        }
        FileOutputStream out = null;
        try {
            out = new FileOutputStream(file);
            wb.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return wb;
    }

    public static Workbook createHSSFWorkbook() {
        //生成Workbook
        HSSFWorkbook wb = new HSSFWorkbook();
        //添加Worksheet（不添加sheet時生成的xls文件打開時會報錯）
        @SuppressWarnings("unused")
        Sheet sheet = wb.createSheet();
        return wb;
    }

    public static Workbook createSXSSFWorkbook(int memRow) {
        Workbook wb = new SXSSFWorkbook(memRow);
        Sheet sheet = wb.createSheet();
        return wb;
    }

    public static Workbook createXSSFWorkbook() {
        XSSFWorkbook wb = new XSSFWorkbook();
        @SuppressWarnings("unused")
        Sheet sheet = wb.createSheet();
        return wb;
    }

    public static Workbook openWorkbook(String file) {
        FileInputStream in = null;
        Workbook wb = null;

        try {
            in = new FileInputStream(file);
            wb = WorkbookFactory.create(in);
        } catch (InvalidFormatException | IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (in != null) {
                    in.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return wb;
    }

    public static Workbook openEncryptedWorkbook(String file, String password) {
        FileInputStream input = null;
        BufferedInputStream binput = null;
        POIFSFileSystem poifs = null;
        Workbook wb = null;
        try {
            input = new FileInputStream(file);
            binput = new BufferedInputStream(input);
            poifs = new POIFSFileSystem(binput);
            Biff8EncryptionKey.setCurrentUserPassword(password);
            String ext = FilenameUtils.getExtension(CommonUtils.getFileName(file));
            switch (ext) {
                case "xls":
                    wb = new HSSFWorkbook(poifs);
                    break;
                case "xlsx":
                    wb = new XSSFWorkbook(input);
                    break;
                default:
                    wb = new HSSFWorkbook(poifs);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * 追加一個sheet,如果wb為空且isNew為true,創(chuàng)建一個wb
     *
     * @param wb
     * @param isNew
     * @param type  創(chuàng)建wb類型,isNew為true時有效 1:xls,2:xlsx
     * @return
     */
    public static Workbook appendSheet(Workbook wb, boolean isNew, int type) {
        if (wb != null) {
            Sheet sheet = wb.createSheet();
        } else if (isNew) {
            if (type == 1) {
                wb = new HSSFWorkbook();
                wb.createSheet();
            } else {
                wb = new XSSFWorkbook();
                wb.createSheet();
            }
        }
        return wb;
    }


    public static Workbook setSheetName(Workbook wb, int index, String sheetName) {
        if (wb != null && wb.getSheetAt(index) != null) {
            wb.setSheetName(index, sheetName);
        }
        return wb;
    }

    public static Workbook removeSheet(Workbook wb, int index) {
        if (wb != null && wb.getSheetAt(index) != null) {
            wb.removeSheetAt(index);
        }
        return wb;
    }

    public static void insert(Sheet sheet, int row, int start, List columns) {
        for (int i = start; i < (row + start); i++) {
            Row rows = sheet.createRow(i);
            if (columns != null && columns.size() > 0) {
                for (int j = 0; j < columns.size(); j++) {
                    Cell ceil = rows.createCell(j);
                    ceil.setCellValue(String.valueOf(columns.get(j)));
                }
            }
        }
    }

    public static void insertRow(Row row, List columns) {
        if (columns != null && columns.size() > 0) {
            for (int j = 0; j < columns.size(); j++) {
                Cell ceil = row.createCell(j);
                ceil.setCellValue(String.valueOf(columns.get(j)));
            }
        }
    }

    /**
     * 設置excel頭部
     *
     * @param wb
     * @param sheetName
     * @param columns   比如:["國家","活動類型","年份"]
     * @return
     */
    public static Workbook setHeader(Workbook wb, String sheetName, List columns) {
        if (wb == null) return null;
        Sheet sheet = wb.getSheetAt(0);
        if (sheetName == null) {
            sheetName = sheet.getSheetName();
        }
        insert(sheet, 1, 0, columns);
        return setHeaderStyle(wb, sheetName);

    }

    /**
     * 插入數(shù)據(jù)
     *
     * @param wb        Workbook
     * @param sheetName sheetName,默認為第一個sheet
     * @param start     開始行數(shù)
     * @param data      數(shù)據(jù),List嵌套List ,比如:[["中國","奧運會",2008],["倫敦","奧運會",2012]]
     * @return
     */
    public static Workbook setData(Workbook wb, String sheetName, int start, List data) {
        if (wb == null) return null;
        if (sheetName == null) {
            sheetName = wb.getSheetAt(0).getSheetName();
        }
        if (!Utils.isEmpty(data)) {
            if (data instanceof List) {
                int s = start;
                Sheet sheet = wb.getSheet(sheetName);
                for (Object rowData : data) {
                    Row row = sheet.createRow(s);
                    insertRow(row, (List) rowData);
                    s++;
                }
            }
        }
        return wb;
    }

    /**
     * 移除某一行
     *
     * @param wb
     * @param sheetName sheet name
     * @param row       行號
     * @return
     */
    public static Workbook delRow(Workbook wb, String sheetName, int row) {
        if (wb == null) return null;
        if (sheetName == null) {
            sheetName = wb.getSheetAt(0).getSheetName();
        }
        Row r = wb.getSheet(sheetName).getRow(row);
        wb.getSheet(sheetName).removeRow(r);
        return wb;
    }

    /**
     * 移動行
     *
     * @param wb
     * @param sheetName
     * @param start     開始行
     * @param end       結(jié)束行
     * @param step      移動到那一行后(前) ,負數(shù)表示向前移動
     *                  moveRow(wb,null,2,3,5); 把第2和3行移到第5行之后
     *                  moveRow(wb,null,2,3,-1); 把第3行和第4行往上移動1行
     * @return
     */
    public static Workbook moveRow(Workbook wb, String sheetName, int start, int end, int step) {
        if (wb == null) return null;
        if (sheetName == null) {
            sheetName = wb.getSheetAt(0).getSheetName();
        }
        wb.getSheet(sheetName).shiftRows(start, end, step);
        return wb;
    }

    public static Workbook setHeaderStyle(Workbook wb, String sheetName) {
        Font font = wb.createFont();
        CellStyle style = wb.createCellStyle();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints(FONT_HEIGHT_IN_POINTS);
        font.setFontName("黑體");
        style.setFont(font);
        if (Utils.isEmpty(sheetName)) {
            sheetName = wb.getSheetAt(0).getSheetName();
        }
        int row = wb.getSheet(sheetName).getFirstRowNum();
        int cell = wb.getSheet(sheetName).getRow(row).getLastCellNum();
        for (int i = 0; i < cell; i++) {
            wb.getSheet(sheetName).getRow(row).getCell(i).setCellStyle(style);
        }
        return wb;
    }

    public static Workbook setHeaderOutline(Workbook wb, String sheetName, String title) {
        if (wb == null) return null;
        if (Utils.isEmpty(sheetName)) {
            sheetName = wb.getSheetAt(0).getSheetName();
        }
        Header header = wb.getSheet(sheetName).getHeader();
        header.setLeft(HSSFHeader.startUnderline() +
                HSSFHeader.font("宋體", "Italic") +
                "喜迎G20!" +
                HSSFHeader.endUnderline());
        header.setCenter(HSSFHeader.fontSize(HEADER_FONT_SIZE) +
                HSSFHeader.startDoubleUnderline() +
                HSSFHeader.startBold() +
                title +
                HSSFHeader.endBold() +
                HSSFHeader.endDoubleUnderline());
        header.setRight("時間:" + HSSFHeader.date() + " " + HSSFHeader.time());
        return wb;
    }

    public static Workbook setFooter(Workbook wb, String sheetName, String copyright) {
        if (wb == null) return null;
        if (Utils.isEmpty(sheetName)) {
            sheetName = wb.getSheetAt(0).getSheetName();
        }
        Footer footer = wb.getSheet(sheetName).getFooter();
        if (Utils.isEmpty(copyright)) {
            copyright = "joyven";
        }
        footer.setLeft("Copyright @ " + copyright);
        footer.setCenter("Page:" + HSSFFooter.page() + " / " + HSSFFooter.numPages());
        footer.setRight("File:" + HSSFFooter.file());
        return wb;
    }

    public static Workbook create(String sheetNm, String file, List header, List data, String
            title, String copyright) {
        Workbook wb = createWorkbook(file);
        if (Utils.isEmpty(sheetNm)) {
            sheetNm = wb.getSheetAt(0).getSheetName();
        }
        setHeaderOutline(wb, sheetNm, title);
        setHeader(wb, sheetNm, header);
        setData(wb, sheetNm, 1, data);
        setFooter(wb, sheetNm, copyright);
        if (wb != null) {
            return wb;
        }
        return null;
    }

    public static String getSystemFileCharset() {
        Properties pro = System.getProperties();
        return pro.getProperty("file.encoding");
    }
    // TODO 后面增加其他設置

}

這里面修復了一個bug，這個bug導致數(shù)據(jù)寫入過大，耗內(nèi)存，耗CPU。下面是修改后的方法。

public static Workbook setData(Workbook wb, String sheetName, int start, List data) {
    if (wb == null) return null;
    if (sheetName == null) {
        sheetName = wb.getSheetAt(0).getSheetName();
    }
    if (!Utils.isEmpty(data)) {
        if (data instanceof List) {
            int s = start;
            Sheet sheet = wb.getSheet(sheetName);
            for (Object rowData : data) {
                Row row = sheet.createRow(s);
                insertRow(row, (List) rowData);
                s++;
            }
        }
    }
    return wb;
}

public static void insertRow(Row row, List columns) {
    if (columns != null && columns.size() > 0) {
        for (int j = 0; j < columns.size(); j++) {
            Cell ceil = row.createCell(j);
            ceil.setCellValue(String.valueOf(columns.get(j)));
        }
    }
}

下面是原來的寫法：

public static Workbook setData(Workbook wb, String sheetName, int start, List data) {
    if (wb == null) return null;
    if (sheetName == null) {
        sheetName = wb.getSheetAt(0).getSheetName();
    }
    if (data != null || data.size() > 0) {
        if (data instanceof List) {
            int s = start;
            for (Object columns : data) {
                insert(wb, sheetName, data.size() - (s - 1), s, (List) columns);
                s++;
            }
        }
    }
    return wb;
}
public static void insert(Sheet sheet, int row, int start, List columns) {
    for (int i = start; i < (row + start); i++) {
        Row rows = sheet.createRow(i);
        if (columns != null && columns.size() > 0) {
            for (int j = 0; j < columns.size(); j++) {
                Cell ceil = rows.createCell(j);
                ceil.setCellValue(String.valueOf(columns.get(j)));
            }
        }
    }
}

錯誤：for (Object columns : data)已經(jīng)在遍歷數(shù)據(jù)了，但是在insert中又for (int i = start; i < (row + start); i++)遍歷了一次，而且遍歷的無厘頭，盡管無厘頭，數(shù)據(jù)卻寫進去，至于寫入到什么地方了，就不知道，反正是成倍的增大內(nèi)存和cpu。