QAD 实现数据批量导入(WEB版) JAVA相关支持类

1.使用POI读取excel里的数据

 /*------------------------------------------------------------------------
    File        : Npexcel.java
    Purpose     : FOR WEB SITE USE
    Syntax      : USE POI TO ACCESS EXCEL METHOD
    Description : CONVERT FROM npexcel.cls 
    Author(s)   : 
    Created     : Mar 05 09:25:10 CST 2019
    Notes       : 注意数组 从1开始 内置函数下标均减了一 (和PROGRESS的保持一致)
  ----------------------------------------------------------------------*/

package common;
import java.io.*;
import java.sql.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

public class Npexcel {
    
    private Workbook workbook;
    private Sheet[] sheet = new Sheet[18];  /*数组 要指定size 现最多18个sheets*/
    private InputStream fs;
    private int sheetnum;
    protected String exfname;
    protected String err = "";
    
    public String getErr()
    {
        return err;
    }
    
    private CellStyle cellStyleDate;
    private CellStyle cellStylePercent;
    private CellStyle[] cpstyle = new CellStyle[3];  /*复制模板里的单元格样式*/
    private DataFormatter dtFormatter;
    
    public Npexcel()
    {
        sheetnum = 0;
        dtFormatter = new DataFormatter();
    }
    
    public boolean newExcel(String wfile)  //不用
    {
        return startExcel(wfile);
    }
    
    public boolean startExcel(String filepath)
    {
        if (filepath == null || filepath.isEmpty())
            return false;
        try {
            fs = new FileInputStream(filepath);
            if (fs != null) 
            {
                if (filepath.endsWith(".xlsx")) {
                    workbook = new XSSFWorkbook(fs);
                } else if (filepath.endsWith(".xls")) {
                    workbook = new HSSFWorkbook(fs);
                }
            fs.close();
            }
            else 
                err = "FileInputStream Failed: " + filepath;  //not require
        } 
        catch (FileNotFoundException e) 
        {
            err = e.toString();
            //e.printStackTrace();
        } 
        catch (IOException e) 
        {
            err = e.toString();
            //e.printStackTrace();
        } 
        finally {
            if (fs != null) {
                try {
                    fs.close();
                } catch (IOException e) {
                    err = e.toString();
                    //e.printStackTrace();
                }
            }
        }
        if (workbook != null)
        {
            cpstyle[0] = workbook.createCellStyle();
            cpstyle[1] = workbook.createCellStyle();
            cpstyle[2] = workbook.createCellStyle();
        }
            
        if (workbook != null)
        return true;
        else
        return false;
    }
        
    public String getCell(int st,int irow,int icol)
    {
        if (sheet[st - 1] == null)
            sheet[st - 1] = workbook.getSheetAt(st - 1);

        Row erow = sheet[st - 1].getRow(irow - 1);
        if (erow == null)
            return "";
        Cell ecell = erow.getCell(icol - 1);
        if (ecell == null)
            return "";
        else 
        {
            return ecell.toString();  //直接tostring
        }
    }

    public String charCell(int st,int irow,int icol)
    {
        if (sheet[st - 1] == null)
            sheet[st - 1] = workbook.getSheetAt(st - 1);

        Row erow = sheet[st - 1].getRow(irow - 1);
        if (erow == null)
            return "";
        Cell ecell = erow.getCell(icol - 1);
        if (ecell == null)
            return "";
        else 
        {
            return dtFormatter.formatCellValue(ecell);  //formatCellValue 转文本格式
        }
    }
    
    public String strCell(int st,int irow,int icol)
    {
        if (sheet[st - 1] == null)
            sheet[st - 1] = workbook.getSheetAt(st - 1);

        Row erow = sheet[st - 1].getRow(irow - 1);
        if (erow == null)
            return "";
        Cell ecell = erow.getCell(icol - 1);
        if (ecell == null)
            return "";
        else 
        {
            ecell.setCellType(Cell.CELL_TYPE_STRING);
            return ecell.getStringCellValue();  //Cell转成STRING类型再取值
        }
    }
    
    private Cell actcell(int st,int irow,int icol)  /*简化重复用*/
    {
        if (sheet[st - 1] == null)
            sheet[st - 1] = workbook.getSheetAt(st - 1);
        Row erow = sheet[st - 1].getRow(irow - 1);
        if (erow == null)
            erow = sheet[st - 1].createRow(irow - 1);
        Cell ecell = erow.getCell(icol - 1);
        if (ecell == null)
            ecell = erow.createCell(icol - 1);
        return ecell;
    }
    
    public void dateformat(String dtformat)  //dd/MM/yy ...
    {
        cellStyleDate = workbook.createCellStyle();
        DataFormat dateformat = workbook.createDataFormat();
        cellStyleDate.setDataFormat(dateformat.getFormat(dtformat));
    }
    
    public void setDateformat(int st,int irow,int icol)  //设置日期格式
    {
        if (cellStyleDate != null)
            actcell(st,irow,icol).setCellStyle(cellStyleDate);
    }
    
    public void percentformat(String dtformat)  //"0.00%" ...
    {
        cellStylePercent = workbook.createCellStyle();
        cellStylePercent.setDataFormat(HSSFDataFormat.getBuiltinFormat(dtformat));
    }
    
    public void setPercent(int st,int irow,int icol)  //设置单元格成百分比格式
    {
        if (cellStylePercent != null)
            actcell(st,irow,icol).setCellStyle(cellStylePercent);
    }
    
    public int newSheet(int n,String wsheet)
    {
        if (workbook != null)
        {
            sheet[n - 1] = workbook.createSheet(wsheet);
            sheetnum = sheetnum + 1;
            return sheetnum;
        }
        else 
            return -1;
    }

    public void setSheet(int n,String wsheet)
    {
        if (workbook != null)
            sheet[n - 1] = workbook.getSheet(wsheet);
    }
    
    public void forceFormulaRecalculation(int st)
    {
        if (sheet[st - 1] == null)
            sheet[st - 1] = workbook.getSheetAt(st - 1);
        sheet[st - 1].setForceFormulaRecalculation(true);
    }

    public void removeRow(int st,int irow)  /*清空指定行的数据*/
    {
        if (sheet[st - 1] == null)
            sheet[st - 1] = workbook.getSheetAt(st - 1);
        Row erow = sheet[st - 1].getRow(irow - 1);
        if (erow == null)
            erow = sheet[st - 1].createRow(irow - 1);
        sheet[st - 1].removeRow(erow);
    }
    
    public void setFormula(int st,int irow,int icol,String iformula)  /*单元格设置公式 字符串不需要=*/
    {
        actcell(st,irow,icol).setCellFormula(iformula);
    }
    
    public void writeCell(int st,int irow,int icol,String idata)
    {
        actcell(st,irow,icol).setCellValue(idata);
    }

    public void writeCell(int st,int irow,int icol,boolean idata)
    {
        actcell(st,irow,icol).setCellValue(idata);
    }
    
    public void writeCell(int st,int irow,int icol,Date idata)
    {
        actcell(st,irow,icol).setCellValue(idata);
    }
    
    public void writeCell(int st,int irow,int icol,Double idata)
    {
        actcell(st,irow,icol).setCellValue(idata);
    }
    
    public void cloneStyle(int st,int irow,int icol)  //复制指定单元格的格式
    {
        cpstyle[0].cloneStyleFrom(actcell(st,irow,icol).getCellStyle());
    }

    public void usecStyle(int st,int irow,int icol)  //应用格式到指定单元格
    {
        actcell(st,irow,icol).setCellStyle(cpstyle[0]);
    }

    public void cloneStyle(int st,int irow,int icol,int idx)  //复制指定单元格的格式
    {
        cpstyle[idx - 1].cloneStyleFrom(actcell(st,irow,icol).getCellStyle());
    }
    
    public void usecStyle(int st,int irow,int icol,int idx)  //应用格式到指定单元格
    {
        actcell(st,irow,icol).setCellStyle(cpstyle[idx - 1]);
    }

    public void sheetName(int st,String stname)  /*设置工作表名*/
    {
        workbook.setSheetName(st - 1, stname);
    }

    public void saveas(String filepath) throws IOException  //另存为
    {
        savexcel(filepath);
    }
    
    public void savexcel(String filepath) throws IOException
    {
        FileOutputStream wfs = new FileOutputStream(filepath);
        workbook.write(wfs);
        //wfs.flush();
        wfs.close();
    }
    /*方法可补充*/

    public void createxls(String colist,String filepath) throws IOException
    {
        createxls(colist, filepath, "");
    }
    
    public void createxls(String colist,String filepath,String esheetname) throws IOException
    {
        if (colist == null || filepath == null || colist.isEmpty() || filepath.isEmpty())  //isEmpty() 是java 1.6后的 形参要判断null
            return;
        if (filepath.endsWith(".xlsx")) {
            workbook = new XSSFWorkbook();
        } else if (filepath.endsWith(".xls")) {
            workbook = new HSSFWorkbook();
        }
        else
            return;
        if (esheetname == null || esheetname.isEmpty())
            esheetname = "Sheet1";
        sheet[0] = workbook.createSheet(esheetname);
        String[] cols = colist.split(",");
        for (int i = 0; i < cols.length; i++)
        {
            this.writeCell(1, 1, i + 1, cols[i]);
        }
        FileOutputStream wfs = new FileOutputStream(filepath);
        workbook.write(wfs);
        //wfs.flush();
        wfs.close();
    }
    
}

2.把Excel数据读进ResultSet里

 /*------------------------------------------------------------------------
    File        : Wkdatatb.java
    Purpose     : FOR QAD WBECIMLOAD APPSERVER USE
    Syntax      : 
    Description : All work table fields default as string type
    Author(s)   : 
    Created     : Fri Apr 24 12:59:23 CST 2020
    Notes       : 
  ----------------------------------------------------------------------*/

package common;

import java.sql.SQLException;
import java.util.Vector;
import com.progress.open4gl.InputResultSet;

public class Wkdatatb extends InputResultSet {

    private Vector<Object> rows;
    private int rowNum;
    private Vector<Object> currentRow;
    private int maxcol = 36;
    protected String err = "";

    public Wkdatatb(String filepath,String coltype)
    {
        Npexcel p = new Npexcel();
        int i = 2;
        String[] cols = null;
        if (coltype == null || coltype.isEmpty() || coltype.equals("")) 
            coltype = "";
        else
        {
            cols = coltype.split(",");
            if (cols.length > 0)
            maxcol = cols.length;
        }
        if (p.startExcel(filepath))
        {
            rows = new Vector<Object>();
            Vector<String> row;
            while(p.getCell(1,i,1) != null && !p.getCell(1,i,1).equals(""))
            {
                row = new Vector<String>();
                for (int j=1;j<=maxcol;j++)
                {
                    if (!coltype.equals("") && cols != null && cols[j - 1].equals("character"))    //注意数组下标
                        row.addElement(p.strCell(1,i,j));
                    else
                        row.addElement(p.getCell(1,i,j));
                }
                rows.addElement(row);
                i++;
            }
            currentRow = null;
            rowNum = 0;
        }
        if (p != null)
        err = p.getErr();
    }
    
    public Wkdatatb(String filepath)
    {
        this(filepath,"");
    }
    
    public int getColnum()
    {
        return maxcol;
    }
    
    public String getErr()
    {
        return err;
    }
    
    @Override
    public Object getObject(int pos) throws SQLException {
        return currentRow.elementAt(pos-1);
    }

    @SuppressWarnings("unchecked")
    @Override
    public boolean next() throws SQLException {
        try
        {
            currentRow = (Vector<Object>)rows.elementAt(rowNum++);
        }
        catch (Exception e) {return false;}
        return true;
    }

}

3. 访问Appserver接口

package common;
import java.io.IOException;
import com.progress.open4gl.ConnectException;
import com.progress.open4gl.Open4GLException;
import com.progress.open4gl.SystemErrorException;
import com.progress.open4gl.javaproxy.*;

public class OeAppsvHelper {

    private String appsvurl;   //"AppServerDC://host:port/server name";
    private String appsvname; 
    
    public OeAppsvHelper()
    {
        GetCfg cfg = new GetCfg();
        appsvname = cfg.appsvname;
        appsvurl = cfg.appsvurl + "/" + cfg.appsvname;
    }
    
    public static void execProc(String appsvurl,String appsvname,String procname,ParamArray parms) throws ConnectException, SystemErrorException, Open4GLException, IOException
    {
        Connection conn = new Connection(appsvurl, "", "", "");
        conn.setSessionModel(1);  //session-free
        OpenAppObject openAO = new OpenAppObject(conn, appsvname);
        openAO.runProc(procname, parms);
        openAO._release();
        conn.releaseConnection();
    }

    public void execProc(String procname, ParamArray parms) throws ConnectException, SystemErrorException, Open4GLException, IOException
    {
        Connection conn = new Connection(appsvurl, "", "", "");
        conn.setSessionModel(1);
        OpenAppObject openAO = new OpenAppObject(conn, appsvname);
        openAO.runProc(procname, parms);
        openAO._release();
        conn.releaseConnection();
    }

    public void execProc(String procname) throws ConnectException, SystemErrorException, Open4GLException, IOException
    {
        Connection conn = new Connection(appsvurl, "", "", "");
        conn.setSessionModel(1);
        OpenAppObject openAO = new OpenAppObject(conn, appsvname);
        openAO.runProc(procname, new ParamArray(0));
        openAO._release();
        conn.releaseConnection();
    }
    //If no parameters
}