JAVA实现对Excel的操作

操作环境：Eclipse Luna+win7
之前写了一篇用mfc做的对于Excel的操作。其实在此之前，首先是用java来操作的Excel. 这一点在那篇博文里也有提到，仍然是说，java对于Excel的操作更为方便，很大一部分的原因在于java的数据类型之间的转换逻辑比较简单。
这里做的是一个钢铁实验的处理工具，下面上干货。
1. 记得导入jxl.jar
2. 效果图

3.工程结构很简单，只是需要一个java文件，然后界面的实现和业务的逻辑处理都放在了一个里面。
整个工程实现的是用文件选择器选择一个文件，然后读取Excel，对数据做处理，然后生成一个Excel保存结果。
4.下面讲具体的实现。
(1)创建工程什么的就不说了，一个包，然后创建一个方法。
(2)上代码了，貌似挺简单的，就不做讲解了。

class GetData implements ActionListener{

String pathtrans;
String [][] cData;
JTextField PathText;
JTextField text1, text2, text3, text4, text5, text6;
JButton Op, ImportFiles, ExportFiles;
JFileChooser filechooser=new JFileChooser();
JFrame frame=new JFrame("Steel Experimental Data Calculation Tool");
//构造页面
public void makeUI(){
    JPanel p1=new JPanel();
    p1.setLayout(new FlowLayout());
    JPanel p2=new JPanel();
    p2.setLayout(new FlowLayout(FlowLayout.CENTER));
    //p1添加元素
    PathText=new JTextField(35);            
    p1.add(PathText);
    Op=new JButton("打开文件");
    p1.add(Op);
    Op.addActionListener(this);
    frame.add(p1,"North");
    //p2添加元素
    JLabel label1 = new JLabel("点位差：");
    text1 = new JTextField("0.004",5);
    JLabel label2 = new JLabel("a-N曲线K值：");
    text2 = new JTextField(5);
    JLabel label3 = new JLabel("△P：");
    text3 = new JTextField(5);
    JLabel label4 = new JLabel("B:");
    text4 = new JTextField(5);
    JLabel label5 = new JLabel("W:");
    text5 = new JTextField(5);
    JLabel label6 = new JLabel("点数n:");
    text6 = new JTextField(5);
    ImportFiles=new JButton("导入");
    ExportFiles=new JButton("导出");
    p2.add(label1);
    p2.add(text1);
    p2.add(label2);
    p2.add(text2);
    p2.add(label3);
    p2.add(text3);
    p2.add(label4);
    p2.add(text4);
    p2.add(label5);
    p2.add(text5);
    p2.add(label6);
    p2.add(text6);
    p2.add(ImportFiles);
    p2.add(ExportFiles);
    ImportFiles.setBounds(100, 60, 90, 25);
    ExportFiles.setBounds(310, 60, 90, 25);
    ImportFiles.addActionListener(this);
    ExportFiles.addActionListener(this);
    frame.add(p2,"Center");
    frame.setBounds(100,100,800,400);
    frame.setVisible(true);
}
public GetData(){
    filechooser.setCurrentDirectory(new File("d://"));
    double lx=Toolkit.getDefaultToolkit().getScreenSize().getWidth();
    double ly=Toolkit.getDefaultToolkit().getScreenSize().getHeight();
    frame.setLocation(new Point((int)(lx/2)-150,(int)(ly/2)-150));
    frame.setSize(280, 200);
}
public void actionPerformed(ActionEvent e)
{
    if(e.getSource()==Op)
    {
        //choose a path
        filechooser.setFileSelectionMode(0);
        int state=filechooser.showOpenDialog(null);
        if(state==1)
            return;
        else{
            File f=filechooser.getSelectedFile();
            PathText.setText(f.getAbsolutePath());
            pathtrans=f.getAbsolutePath();
        }

    }
    else if(e.getSource()==ImportFiles)
    {
        //Import
        String [][] result =ReadData();
        String a = text1.getText().toString();
        double aFlt = Double.valueOf(a).doubleValue();
        if(result !=null){
            cData = DealData1(result, aFlt);
            CreateData1(cData);
        }
    }
    else if(e.getSource()==ExportFiles)
    {
        //Export
        double kFlt = Double.valueOf(text2.getText().toString()).doubleValue();
        int nFlt = Integer.valueOf(text6.getText().toString()).intValue();
        String [][] res1 = DealData2(cData, kFlt);
        double [][] res2 = getXI(res1, nFlt);
        int mNum = res2.length;
        double [] x = new double[mNum];
        double[] mC1 = new double[mNum];
        double [] mC2 = new double[mNum];
        for(int i=0;i<mNum;i++){
            x[i] = res2[i][0];
            mC1[i] = res2[i][1];
            mC2[i] = res2[i][2];
        }
        double [] res3 = getParam(res1, x);
        double [][] res4 = getResult(res3, mC1, mC2, res1);
        int num = res4.length; 
        int kk=0;
        for(int j=0;j<num;j++){
            if(res4[j][0]!=0 && res4[j][1]!=0){
                kk++;
            }
        }
        String [][]res5 = new String [kk][2];
        for(int i=0;i<kk;i++){
            res5[i][0] = res4[i][0]+"";
            res5[i][1] = res4[i][1]+"";
        }
        CreateData2(res5);
    }
}
//读取数据
public String[][] ReadData(){
    String [][] result = null;
    try{
        jxl.Workbook readwb = null;
        InputStream instream = new FileInputStream(pathtrans); 
        readwb = Workbook.getWorkbook(instream);
        Sheet readsheet = readwb.getSheet(0);
        int rsColumns = readsheet.getColumns();      //列数
        int rsRows = readsheet.getRows();           //行数
        result = new String [rsRows][rsColumns];
        for(int i=0;i<rsRows;i++){
            for(int j=0;j<rsColumns;j++){
                Cell mCell = readsheet.getCell(j,i);
                String cellStr = mCell.getContents().toString();
                result[i][j] = cellStr;
            }
        }
    }catch(Exception e){
        JOptionPane.showMessageDialog(null, "Read Data Exception !","Warning",  JOptionPane.ERROR_MESSAGE);
    }

    return result;
}
//处理数据1
public String [][] DealData1(String data[][], double aInp){
    int colNum = data.length;
    double [][] mData = new double[colNum][2];
    String [][] result = new String [colNum][2];
    for(int i=0;i<data.length;i++){                                                     //数据类型转换
        for(int j=0;j<data[i].length;j++){
            double cellInteger = Double.valueOf(data[i][j]).doubleValue();
            mData[i][j] = cellInteger;
        }
    }
    if(mData!=null){                                                //按点位差进行数据刷选
        result[0][0] = mData[0][0]+"";
        result[0][1] = mData[0][1]+"";
        int nNum = 1;
        double rNum = mData[0][1];
        for(int k=1;k<colNum;k++){
            double aRes = mData[k][1]-rNum;
            if(aRes>=aInp){
                result[nNum][0] = mData[k][0]+"";
                result[nNum][1] = mData[k][1]+"";
                rNum = mData[k][1];
                ++nNum;
            }
        }
    }
    return result;
}
//生成表格1
public boolean CreateData1(String [][] res){
    boolean flag = true;
    try{
        double mRes = 0.00;
        File fileWriter = new File("D:/","ResultData1.xls");
        if(!fileWriter.exists()){
            fileWriter.createNewFile();
        }
        OutputStream os = new FileOutputStream(fileWriter);
        WritableWorkbook wwb = Workbook.createWorkbook(os);
        WritableSheet ws = wwb.createSheet("Result Data1", 0); 
        WritableCellFormat wcf = new WritableCellFormat();
        wcf.setAlignment(Alignment.CENTRE);
        for(int i=0;i<res.length;i++){
            for(int j=0;j<res[i].length;j++){
                if(res[i][j]!=null){
                    mRes = Double.valueOf(res[i][j]).doubleValue(); 
                    jxl.write.Number number = new jxl.write.Number(j,i,mRes);
                    ws.addCell(number);
                }
            }
        }
        wwb.write();
        wwb.close();
    }catch(Exception e){
        flag = false;
        JOptionPane.showMessageDialog(null, "Create Excel Exception !","Warning",  JOptionPane.ERROR_MESSAGE);
    }
    return flag;
}
//处理数据2
public String [][] DealData2(String data[][], double kInp){
    int num = data.length;
    int knum = num;
    int k = 0;
    double v0 = Double.valueOf(data[2][1]).doubleValue(); 
    if(num%2==0){
        knum = num/2 -1;
    }else{
        knum = num/2;
    }
    String [][] result = new String [knum][2];
    System.out.println(data[2][0]);
    for(int i=2;i<num;i++){
        result[k][0] = data[i][0];
        double vi = Double.valueOf(data[i][1]).doubleValue();
        result[k][1] = 10+(vi-v0)*kInp+"";
        ++i;
        ++k;
    }
    return result;
}
//数据处理3
public double [][] getXI(String data[][], int n){
    int num = data.length;
    double [][] x = new double [num][3] ;
    for(int i=n;(i+n)<num;i++){
        if(data[i-n][0]!=null && data[i+n][0]!=null){
            double N1 = Double.valueOf(data[i-n][0]).doubleValue();
            double N2 = Double.valueOf(data[i+n][0]).doubleValue();
            double c1 = (N1+N2)/2;
            double c2 = (N2-N1)/2;
            x[i-n][1] = c1;
            x[i-n][2] = c2;
            x[i-n][0] = (Double.valueOf(data[i][0]).doubleValue()-c1)/c2;
        }
    }
    return x;
}
//最小二乘法获取回归曲线拟合的参数
public double [] getParam(String data[][], double x[]){
    int num = data.length;
    double[] mPa;
    double [][] mY = new double[num][2];
    for(int i=0;i<num;i++){
        if(data[i][1] !=null){
            mY[i][0] = Double.valueOf(data[i][1]).doubleValue(); 
        }
    }
    double[][] mX = new double [num][3];
    for(int j=0;j<num;j++){
        mX[j][0] = 1.00;
        mX[j][1] = Double.valueOf(x[j]).doubleValue();
        mX[j][2] = Double.valueOf(x[j]*x[j]).doubleValue();
    }
    Matrix nX = new Matrix(mX);  
    Matrix nY = new Matrix(mY);
    Matrix nXP = nX.transpose();
    Matrix nB = nXP.times(nX).inverse().times(nXP).times(nY);
    mPa = nB.getColumnPackedCopy();
    return mPa;
}
//获得拟合长度a
public double [][] getResult(double []param, double []c1, double []c2, String [][]aN){
    double b0 = param[0];
    double b1 = param[1];
    double b2 = param[2];
    int num = aN.length;
    double p = Double.valueOf(text3.getText().toString()).doubleValue();
    double B = Double.valueOf(text4.getText().toString()).doubleValue();
    double W = Double.valueOf(text5.getText().toString()).doubleValue();
    double [][] result = new double[num][2];
    for(int i=0;i<num;i++){
        if(c1[i]!=0 && c2[i]!=0){
            double a = Double.valueOf(aN[i][1]).doubleValue()/1000;
            result[i][0] =p/(B*W)*(Math.sqrt(Double.valueOf(a).doubleValue()))*(29.6-185.5*a/W+655.7*(Math.pow(a/W, 2))-1017*(Math.pow(a/W, 3)+638.9*(Math.pow(a/W, 4))));
            double N = Double.valueOf(aN[i][0]).doubleValue();
            result[i][1] = b1/c2[i]+2*b2*(N-c1[i])/(Math.pow(c2[i], 2));
        }
    }
    return result;
}
//生成表格2
public boolean CreateData2(String [][] res){
    boolean flag = true;
    try{
        double mRes;
        File fileWriter = new File("D:/","ResultData2.xls");
        fileWriter.createNewFile();
        OutputStream os = new FileOutputStream(fileWriter);
        WritableWorkbook wwb = Workbook.createWorkbook(os);
        WritableSheet ws = wwb.createSheet("Result Data2", 0); 
        WritableCellFormat wcf = new WritableCellFormat();
        wcf.setAlignment(Alignment.CENTRE);
        for(int i=0;i<res.length;i++){
            for(int j=0;j<res[i].length;j++){
                mRes = Double.valueOf(res[i][j]).doubleValue();
                jxl.write.Number number = new jxl.write.Number(j,i,mRes);
                ws.addCell(number);
            }
        }
        wwb.write();
        wwb.close();
    }catch(Exception e){
        flag = false;
        JOptionPane.showMessageDialog(null, "Warning", "Create Excel Exception !", JOptionPane.ERROR_MESSAGE);
    }
    return flag;
}

public static void main(String args[]){
    GetData mGetData = new GetData();
    mGetData.makeUI();
}

}