多线程报表生成

Question

我需要生成一些从数据库读取的报告，并将数据写入Excel。

在检查代码中的以下内容时，我需要您的帮助：

1. 数据库连接
2. 多线程编程
3. 我的数据结构Map<Integer, TreeMap<String,String>>合适吗？
4. 我可以应用什么设计模式？

DB连接

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
import java.util.Vector;

import javax.sql.DataSource;

public class RConnection {
    private Connection mConnection;
    private Statement mStatement;
    private static Object lockObject = new Object();
    private static int openCount = 0;
    private static int closeCount = 0;
    private static String dataSourceName = "jdbc/dtcuae";
    private static DataSource dataSource = null;
    private Vector statementVector= new Vector();
    private Vector resultSetVector= new Vector();


    public CallableStatement createCallableStatement(String sql) throws SQLException
    {
        CallableStatement cstmt = null;
        try
        {
            cstmt = this.getDBConnection().prepareCall(sql);
            statementVector.add(cstmt);
        }
        catch(SQLException e)
        {       
            e.printStackTrace();
            throw new SQLException("Can not create CallableStatement for sql "+e.getMessage());
        }
        return cstmt;
    }


    public ResultSet getResultSet(CallableStatement cstmt, int paramNo) throws SQLException
    {
        ResultSet rs=null;
        try
        {
            rs=(ResultSet) cstmt.getObject(paramNo);
            resultSetVector.add(rs);
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            throw new SQLException("Can not retrieve ResultSet for this CallableStatement "+e.getMessage());
        }
        return rs;
    }


    public static int getOpenConnections()
    {
        return openCount;
    }
    public static int getCloseConnections()
    {
        return closeCount;
    }
    /**
    /**
     * Constructor being made Private, Singleton implementation
     */
    public RConnection()
    {
        this.connect();
    }








    /**
     * This is the function that is used to connect to the
     * database using jdbc
     */
    public void connect()
    {
        try
        {
            String errorString = "Error obtaining database connection.";
            final ResourceBundle BUNDLE = ResourceBundle.getBundle(AppConstants.CONFIG_PATH);

                try {
                    /*
                    System.out.println("-------------------Oracle Connection Properties Start-----------------------------------");
                    System.out.println("oracle.jdbc.url"));
                    System.out.println("oracle.jdbc.usr"));
                    System.out.println("-------------------Oracle Connection Properties End-----------------------------------");
                    */
                    String DB_USERNAME = BUNDLE.getString(AppConstants.DB_USERNAME);
                    //String DB_PASSWORD = BUNDLE.getString(AppConstants.TR_DB_PASSWORD);

                    String DB_PASSWORD = AES.decryptString(BUNDLE.getString(AppConstants.DB_PASSWORD),
                            AppConstants.ENCRYPTION_KEY); 
                    String DB_URL = BUNDLE.getString(AppConstants.DB_URL);
                    Class.forName(AppConstants.DRIVER_CLASS_NAME);
                    mConnection = DriverManager.getConnection(DB_URL, DB_USERNAME,
                            DB_PASSWORD);
                    mConnection.setAutoCommit(false);       
                }catch (NullPointerException e) {                   
                    throw new Exception(errorString+":"+e.getMessage());
                }
                catch(SQLException sqle) {
                    throw new SQLException(errorString+":"+sqle.toString());
                }


            if (mConnection == null) {
                throw new SQLException(errorString);
            } else {
                synchronized (lockObject) {
                    openCount++;                    
                }
            }
        }
        catch(Exception ex)
        {
                ex.printStackTrace();
        }
    }

    /** Return Connection **/
    public Connection getDBConnection()
    {
        return mConnection;
    }

    /**
     * Execute any plain SQL query and returns the ResultSet
     * @throws SQLException 
     */
    public ResultSet executeSQL(String query) throws SQLException
    {
        ResultSet lRSet = null;
        try
        {
            // close the statement if already open
            if(mStatement != null) mStatement.close();
            mStatement = mConnection.createStatement();
            lRSet = mStatement.executeQuery(query);
        }
        catch(SQLException ex)
        {
            System.err.println("Error Query : " + query);
            ex.printStackTrace();
            throw new SQLException(ex.getMessage());
        }

        return lRSet;
    }

    public void commit()
    {
        try
        {
            if(mStatement != null) mStatement.close();
            if(mConnection != null && !mConnection.getAutoCommit()) mConnection.commit();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
    }

    public void close()
    {
        try
        {
            if ((mConnection != null) && !mConnection.isClosed()) {
                if(!resultSetVector.isEmpty())
                {
                    //ok close all result sets once more
                    int rsSize = resultSetVector.size();
                    for(int i=0;i<rsSize;i++)
                    {
                        ResultSet rset=(ResultSet)resultSetVector.get(i);
                        try
                        {
                            rset.close();
                        }
                        catch(SQLException e)
                        {
                            throw new SQLException(e.getMessage());
                        }
                    }
                }

                if(!statementVector.isEmpty())
                {
                    //ok close all statements once more
                    int stmtSize = statementVector.size();
                    for(int i=0;i<stmtSize;i++)
                    {
                        CallableStatement cstmt=(CallableStatement)statementVector.get(i);
                        try
                        {
                            cstmt.close();
                        }
                        catch(SQLException e)
                        {
                            throw new SQLException(e.getMessage());
                        }
                    }
                }

                mConnection.close();
                synchronized (lockObject) {
                    closeCount++;                   
                }
            }
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
    }


    public void callDummyArgumentProcedure(String procedureName) {
        CallableStatement cstmt =null;
        try {
            cstmt = getDBConnection().prepareCall("{call " + procedureName + " (?)}");
            cstmt.setInt(1,0);
            cstmt.execute();
        }
        catch(Throwable e) {

        }
        finally {
            if(cstmt !=null) try {cstmt.close();}catch (SQLException e) {}
        }
    }


} 

基于

线程的报表生成

import java.util.Map;
import java.util.TreeMap;

public class ReportGenerator extends Thread {

    String countryName;

    public ReportGenerator(String countryName) {
        super();
        this.countryName = countryName;
    }


    public String getCountryName() {
        return countryName;
    }

    public void setCountryName(String countryName) {
        this.countryName = countryName;
    }

    public void run() {
        try {
            ReportDao dao = new ReportDao();
            System.out.println("Current Thread"
                    + Thread.currentThread().getName());
            Map<Integer, TreeMap<String, String>> data = dao.read(countryName);
            ExcelModifier.readExcel(data, countryName);

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

DAO

import java.lang.reflect.Field;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

public class ReportDao {
    public RConnection connection = new RConnection();



    public static int getOracleParamReturnType(String paramName) {
        if (paramName == null)
            return -1;
        Field cursorField;
        try {
            Class c = Class.forName("oracle.jdbc.driver.OracleTypes");
            cursorField = c.getField(paramName);
            return cursorField.getInt(c);
        } catch (Throwable th) {
            th.printStackTrace();
            return -1;
        }
    }

    public Map<Integer, TreeMap<String,String>> read(String countryName) throws SQLException {
        System.out.println(" read");
        System.out.println("Country name data to be fetched:"+countryName);
        ResultSet rs = null;
        CallableStatement cstmt = null;
        try {
            String procedure = "{call USP_GET_KE_REPORT (?,?)}";
            connection.getDBConnection();
            cstmt = connection.createCallableStatement(procedure);
            cstmt.setString(1,countryName);
            cstmt.registerOutParameter(2, getOracleParamReturnType("CURSOR"));
            cstmt.execute();
            rs = connection.getResultSet(cstmt, 2);
            return populateData(rs);

        } finally {
            if (rs != null)
                rs.close();
            connection.close();
            System.out.println(" end ");
        }
    }

    public Map<Integer, TreeMap<String,String>> readPH(String countryName,String product) throws SQLException {
        System.out.println(" read");
        System.out.println("Country name data to be fetched:"+countryName);
        ResultSet rs = null;
        CallableStatement cstmt = null;
        try {
            String procedure = "{call GETPHCERTIFICATIONREPORT (?,?,?)}";
            cstmt = connection.createCallableStatement(procedure);
            cstmt.setString(1,product);
            cstmt.setString(2,countryName);
            cstmt.registerOutParameter(3, getOracleParamReturnType("CURSOR"));
            cstmt.execute();
            rs = connection.getResultSet(cstmt, 3);
            return populateData(rs);
        } finally {
            if (rs != null)
                rs.close();
            connection.close();
            System.out.println(" end ");
        }
    }

    private Map<Integer, TreeMap<String,String>> populateData(ResultSet rs) throws SQLException{
        Map<Integer, TreeMap<String,String>>  data = new TreeMap<Integer, TreeMap<String,String>>();

        List<String> coulmnNames = new ArrayList<String>();
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        for (int i = 1; i < columnCount + 1; i++ ) {
            coulmnNames.add(rsmd.getColumnName(i)) ;
        }
        int noOfRecords =0 ;
        while (rs.next()) {
            TreeMap<String,String> values = new TreeMap<String, String>();
            for (String name : coulmnNames) {
                    values.put(name, rs.getString(name));   
            }
            data.put(++noOfRecords, values);
        }
        System.out.println("---------No of records--"+noOfRecords);
        return data;
    }

}

将数据写入Excel

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.Map;
import java.util.TreeMap;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.RichTextString;
import org.apache.poi.ss.usermodel.Row;

public class ExcelModifier {

    public static void readExcel(Map<Integer, TreeMap<String, String>> data,
            String countryName) throws IOException {
        String fileName = "c:\\AIG_KX_Certification Report_KE_Afghanistan_2003a5.xls";
        String destFileName = "C:\\AIG_KX_Certification Report_KE_"
                + countryName + "_2003a5.xls";
        FileInputStream file2 = new FileInputStream(new File(fileName));

        // Get the workbook instance for XLS file
        HSSFWorkbook workbook = new HSSFWorkbook(file2);

        // Get first sheet from the workbook
        HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(1);
        System.out.println("Sheet name before:" + workbook.getSheetName(1));
        workbook.setSheetName(1, countryName);
        System.out.println("Sheet name after:" + workbook.getSheetName(1));

        // if there is an validation error then write the error messages to
        // excel and quit

        // Get iterator to all the rows in current sheet
        HSSFCellStyle unlockedCellStyle = unLockCellStyle(workbook);

        HSSFCellStyle cs = greyCellStyle(workbook);
        int rowNumber = 0;
        int recordCount = data.size();
        System.out.println(" recordcount " + recordCount);
        System.out.println(" writin data to excel "
                + Thread.currentThread().getName());
        int currentRecord = 0;
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = (Row) rowIterator.next();
            Cell keCell = row.getCell(2);
            RichTextString excelValue = null;
            if (keCell != null) {
                excelValue = keCell.getRichStringCellValue();
            }
            ++rowNumber;
            if (rowNumber <= 8) {
                continue;
            }
            ++currentRecord;
            Map<String, String> map = data.get(currentRecord);
            boolean dummyData = false;
            if (map == null) {
                dummyData = true;
                System.err.println(" NO RECORD " + countryName + " rowNumber "
                        + currentRecord);
                continue;
            }

            keData(dummyData, map, row, unlockedCellStyle, cs);

        }

        if (recordCount > (rowNumber - 8)) {
            createRow(rowNumber++, currentRecord++, sheet, data,
                    unlockedCellStyle, cs);
        }

        sheet.setColumnHidden(0, true);
        sheet.setColumnHidden(1, true);
        sheet.setColumnHidden(4, true);
        sheet.setColumnHidden(6, true);
        sheet.setColumnHidden(8, true);
        sheet.setColumnHidden(10, true);
        sheet.setColumnHidden(12, true);
        sheet.setColumnHidden(14, true);
        sheet.setColumnHidden(18, true);

        FileOutputStream fileOut = new FileOutputStream(destFileName);
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();

    }

    public static void createPHExcel(
            Map<Integer, TreeMap<String, String>> data, String countryName,
            String product) throws IOException {
        String fileName = "c:\\AIG_KX_Certification Report-PH v2.4.3 - 08-27-2014a.xls";
        String destFileName = "C:\\AIG_KX_Certification Report-PH-GL-"
                + countryName + ".xls";

        FileInputStream file2 = new FileInputStream(new File(fileName));

        // Get the workbook instance for XLS file
        HSSFWorkbook workbook = new HSSFWorkbook(file2);

        // Get first sheet from the workbook
        HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(1);
        System.out.println("Sheet name before:" + workbook.getSheetName(1));
        workbook.setSheetName(1, countryName);
        System.out.println("Sheet name after:" + workbook.getSheetName(1));
        Map<String, String> headermap = data.get(1);
        System.out.println(" header map " + headermap);

        Row countryRow = sheet.getRow(0);
        Cell countryCell = countryRow.getCell(2);
        countryCell.setCellValue(countryName);

        Row productRow = sheet.getRow(3);
        Cell productCell = productRow.getCell(2);
        productCell.setCellValue(product);

        Row providertRow = sheet.getRow(4);
        Cell providerCell = providertRow.getCell(2);
        providerCell.setCellValue(headermap.get("PROVIDED_BY"));

        Row providertTitleRow = sheet.getRow(5);
        Cell providertTitleCell = providertTitleRow.getCell(2);
        providertTitleCell.setCellValue(headermap.get("ROVIDER_TITLE"));

        Row approverRow = sheet.getRow(6);
        Cell approverCell = approverRow.getCell(2);
        approverCell.setCellValue(headermap.get("APPROVED_BY"));

        Row approverTitleRow = sheet.getRow(7);
        Cell approverTitleCell = approverTitleRow.getCell(2);
        approverTitleCell.setCellValue(headermap.get("APPROVED_BY_TITLE"));

        Row effectiveStartDateRow = sheet.getRow(8);
        Cell effectiveStartDateCell = effectiveStartDateRow.getCell(2);

        effectiveStartDateCell.setCellValue(convertStringToDate(headermap
                .get("EFFECTIVE_START_DT")));

        // if there is an validation error then write the error messages to
        // excel and quit

        // Get iterator to all the rows in current sheet
        HSSFCellStyle unlockedCellStyle = unLockCellStyle(workbook);

        int rowNumber = 0;
        int recordCount = data.size();
        System.out.println(" recordcount " + recordCount);
        System.out.println(" writin data to excel "
                + Thread.currentThread().getName());
        int currentRecord = 0;
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = (Row) rowIterator.next();
            Cell keCell = row.getCell(2);
            // System.out.println(" KE Name "+excelValue);

            ++rowNumber;
            System.out.println("rowNumber " + rowNumber);
            if (rowNumber < 12) {
                continue;
            }
            ++currentRecord;
            Map<String, String> map = data.get(currentRecord);
            boolean setDummyValue = false;
            if (recordCount < rowNumber) {
                setDummyValue = true;
            }

            int cellNo = 1;
            Iterator<Cell> cells = row.cellIterator();
            while (cells.hasNext()) {
                Cell cell = (Cell) cells.next();
                switch (cellNo) {
                case 1:
                    setStringCellValue(setDummyValue ? "" : map
                            .get("PH_CATEGORY_NM"), cell, unlockedCellStyle);
                    break;
                case 2:
                    setStringCellValue(setDummyValue ? "" : map
                            .get("CATEGORY_DISPLAY_ORDER"), cell,
                            unlockedCellStyle);
                    break;
                case 3:
                    setStringCellValue(setDummyValue ? "" : map
                            .get("SUB_CATEGORY_NM"), cell, unlockedCellStyle);
                    break;
                case 4:
                    setStringCellValue(setDummyValue ? "" : map
                            .get("SUB_CATEGORY_DISPLAY_ORDER"), cell,
                            unlockedCellStyle);
                    break;
                case 5:
                    setStringCellValue(setDummyValue ? "" : map
                            .get("QUESTION_NM"), cell, unlockedCellStyle);
                    break;
                case 6:
                    setStringCellValue(setDummyValue ? "" : map
                            .get("QUESTION_PRINT_ORDER"), cell,
                            unlockedCellStyle);
                    break;
                case 7:
                    setStringCellValue(setDummyValue ? "" : map
                            .get("ANSWER_VALUE"), cell, unlockedCellStyle);
                    break;
                case 8:
                    setStringCellValue(setDummyValue ? "" : map
                            .get("COMMENT_VALUE"), cell, unlockedCellStyle);
                    break;
                case 9:
                    setStringCellValue(setDummyValue ? "" : map
                            .get("ANSWER_VALUE1"), cell, unlockedCellStyle);
                    break;
                case 10:
                    setStringCellValue(setDummyValue ? "" : map
                            .get("COMMENT_VALUE1"), cell, unlockedCellStyle);
                    break;
                default:
                    break;
                }
                cellNo++;
            }

        }

        if (recordCount > rowNumber) {
            System.err.println(" More Rows required");
        }
        /*
         * sheet.setColumnHidden(0, true); sheet.setColumnHidden(1, true);
         * sheet.setColumnHidden(4, true); sheet.setColumnHidden(6, true);
         * sheet.setColumnHidden(8, true); sheet.setColumnHidden(10, true);
         * sheet.setColumnHidden(12, true); sheet.setColumnHidden(14, true);
         * sheet.setColumnHidden(18, true);
         */

        FileOutputStream fileOut = new FileOutputStream(destFileName);
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();

    }

    private static String replcaseHtmlTags(String value) {
        if (value != null) {
            value = value.replaceAll("<br>", "\n");
        } else {
            value = "";
        }
        return value;
    }

    public static String convertDateToString(Date date, String formatToConvert)
            throws SystemException {

        SimpleDateFormat dateFormat = null;
        String formatedStr = "";
        try {
            dateFormat = new SimpleDateFormat(formatToConvert);
            formatedStr = dateFormat.format(date);
        } catch (NullPointerException ne) {
            ne.printStackTrace();
        } catch (IllegalArgumentException ire) {
            ire.printStackTrace();
        }
        return formatedStr;
    }

    public static String convertStringToDate(String dateAsString) {
        String start_dt = dateAsString;
        DateFormat formatter = new SimpleDateFormat("yyyy-MM-DD");
        Date date = null;
        try {
            date = (Date) formatter.parse(start_dt);
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        SimpleDateFormat newFormat = new SimpleDateFormat("MM-dd-yyyy");
        String finalString = newFormat.format(date);
        return finalString;
    }

    private static HSSFCellStyle unLockCellStyle(HSSFWorkbook workbook) {
        HSSFCellStyle unlockedCellStyle = workbook.createCellStyle();
        unlockedCellStyle.setWrapText(true);
        unlockedCellStyle.setLocked(false);
        unlockedCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        unlockedCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        unlockedCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        unlockedCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        return unlockedCellStyle;
    }

    private static HSSFCellStyle greyCellStyle(HSSFWorkbook workbook) {
        HSSFCellStyle cs = workbook.createCellStyle();
        cs.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
        cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cs.setLocked(false);
        cs.setWrapText(true);
        cs.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cs.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cs.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cs.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        return cs;
    }

    private static void setStringCellValue(String value, Cell cell,
            HSSFCellStyle unlockedCellStyle) {
        cell.setCellValue(replcaseHtmlTags(value));
        cell.setCellStyle(unlockedCellStyle);
    }

    private static void createRow(int rowNumber, int recordNumber,
            HSSFSheet sheet, Map<Integer, TreeMap<String, String>> data,
            HSSFCellStyle unlockedCellStyle, HSSFCellStyle cs) {
        int nextRow = 12;
        System.out.println(" creating row");
        Row r = sheet.getRow(rowNumber);
        if (r == null) {
            r = sheet.createRow(nextRow);
        }
        Map<String, String> map = data.get(recordNumber);
        keData(false, map, r, unlockedCellStyle, cs);

    }

    private static void keData(boolean dummyData, Map<String, String> map,
            Row row, HSSFCellStyle unlockedCellStyle, HSSFCellStyle cs) {
        int cellNo = 1;
        Iterator<Cell> cells = row.cellIterator();
        while (cells.hasNext()) {
            Cell cell = (Cell) cells.next();
            switch (cellNo) {
            case 1:
                cell.setCellValue(dummyData ? "" : map.get("ID"));
                cell.setCellStyle(unlockedCellStyle);
                break;
            case 2:
                cell.setCellValue(dummyData ? "" : map.get("SEQ_NO"));
                cell.setCellStyle(unlockedCellStyle);
                break;
            case 3:
                cell.setCellValue(dummyData ? "" : map.get("KE_NM"));
                cell.setCellStyle(cs);
                break;
            case 4:
                cell.setCellValue(dummyData ? "" : map.get("KE_DS"));
                cell.setCellStyle(cs);
                break;
            case 5:
                cell.setCellValue(dummyData ? "" : map.get("PRODUCT1"));
                cell.setCellStyle(unlockedCellStyle);
                break;
            case 6:
                cell.setCellValue(dummyData ? "" : map.get("PRODUCT2"));
                cell.setCellStyle(unlockedCellStyle);
                break;
            case 7:
                cell.setCellValue(dummyData ? "" : map.get("STATE1"));
                cell.setCellStyle(unlockedCellStyle);
                break;
            case 8:
                cell.setCellValue(dummyData ? "" : map.get("STATE2"));
                cell.setCellStyle(unlockedCellStyle);
                break;
            case 9:
                cell.setCellValue(dummyData ? "" : map.get("ENTITY_ORIGINAL"));
                cell.setCellStyle(unlockedCellStyle);
                break;
            case 10:
                cell.setCellValue(dummyData ? "" : map.get("ENTITY"));
                cell.setCellStyle(unlockedCellStyle);
                break;
            case 11:
                String shortAswere1 = map.get("SHORT ANSWER VALUE1");
                shortAswere1 = shortAswere1 == null ? "" : shortAswere1
                        .replaceAll("<br>", "\n");
                cell.setCellValue(dummyData ? "" : shortAswere1);
                cell.setCellStyle(unlockedCellStyle);
                break;
            case 12:
                String shortAswere2 = map.get("SHORT ANSWER VALUE2");
                shortAswere2 = shortAswere2 == null ? "" : shortAswere2
                        .replaceAll("<br>", "\n");
                cell.setCellValue(dummyData ? "" : shortAswere2);
                cell.setCellStyle(unlockedCellStyle);
                break;
            case 13:
                String longAswere1 = map.get("LONG ANSWER VALUE1");
                longAswere1 = longAswere1 == null ? "" : longAswere1
                        .replaceAll("<br>", "\n");
                cell.setCellValue(dummyData ? "" : longAswere1);
                cell.setCellStyle(unlockedCellStyle);
                break;
            case 14:
                String longAswere2 = map.get("LONG ANSWER VALUE2");
                longAswere2 = longAswere2 == null ? "" : longAswere2
                        .replaceAll("<br>", "\n");
                cell.setCellValue(dummyData ? "" : longAswere2);
                cell.setCellStyle(unlockedCellStyle);
                break;
            default:
                break;
            }
            cellNo++;
        }
    }
}

Answer 1

由于代码的长度，我没有做完整的回顾，但是下面是一些随机的观察：

• 你在使用Vector，我从90年代起就没有在Java上见过它。你没有使用泛型，我已经十年没见过了。
• ExcelModifier只是一系列静态方法，这通常意味着您没有花费足够的时间来考虑OO设计。
• ExcelModifier.readExcel不是一个好名字。该方法实际上写入了一个文件。也许您应该重命名它并让它返回，返回一些字符串，然后您可以将其保存为文件。
• ExcelModifier.readExcel硬编码输入/输出文件名。你不应该硬编码任何东西。您应该将这些参数作为参数传递给方法。
• ExcelModifier的一些方法有非常重复的代码。你通常可以通过有点聪明来缩短这个时间。例如，您有一个很长的switch语句: case 1: setStringCellValue(setDummyValue？“：map .get("PH_CATEGORY_NM")，cell，unlockedCellStyle)；但是您可以用1 -> "PH_CATEGORY"等定义Map<Integer, String> cellTypeMap，并调用setStringCellValue(setDummyValue？”：map.get(cellTypeMap.get(cellNo)，cell，unlockedCellStyle) -我不太喜欢setDummyValue的业务，您可能在这方面做得更好。
• map不是一个足够的描述性名称。
• 谓词是用于方法名称的，因此setDummyValue将是方法名，而不是boolean。我很抱歉对变量名吹毛求疵，但这对于代码的可读性非常重要。
• ReportGenerator应该是不可变的，特别是因为这是多线程的。这只意味着您将countryName成员设置为final并删除setter。
• 您不应该使用原始的Thread，因为这是过时的。相反，请查看Executors。
• 缩进风格不一致:有时使用Java约定，但有时使用C++/C#约定。

至于您可以使用什么设计模式的问题，您基本上只是将数据从DB转换为文件。这里没有太多的设计。但是，您可以通过完全分离(1)从DB获取数据和(2)转换数据和(3)将数据写入文件系统来使代码更加清晰。

您还想知道您的Map<Integer, TreeMap<String,String>>是否合适。事实并非如此。整数只列出条目的数量，所以这实际上是一个列表：list.get(i)而不是map.get(i)。使用列表更清楚，因为您知道它代表了一个有序的项目序列。

因为您知道高级的列名，所以可以在enum中定义它们。然后，您可以用一个TreeMap<String, String>替换EnumMap。