DU EXCEL

于 2010-02-05 17:44:00 发布
阅读量590 收藏
点赞数
文章标签： hashmap excel string null iterator exception

package package_final;
import  common_function.CommonFunction;
import java.io.File;
import java.io.IOException;
import java.util.*;

import javax.swing.JFileChooser;

import jxl.*;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;

/**
 * <p>该文件作用主要是删除已提取出来的表格中右边有重复的项。
 *
 * @version 1.0
 */
public class Delete_youbianchongfu
{
    private Workbook workbook;

    private Sheet sheet;

 

    /**
     * <br>
     * 方法说明：读取excel的指定单元数据 <br>
     * 输入参数： <br>
     * 返回类型：
     */
    public String read(int col, int row)
    {
        try
        {
            // 获得单元数据
            Cell a2 = sheet.getCell(col, row);
            String value = a2.getContents();
            return value;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            workbook.close();
            return null;
        }
    }

    public static void main(String[] arges)
    {
        int countFlag = 0;

        String fileName = "E://";
       
        String []getfileName = CommonFunction.selectPath(fileName);
       
        Workbook workBook_Source = null;
        Sheet sheetWorkBook_Source = null;
    
        WritableWorkbook writeWorkBook_Middle = null;
        WritableWorkbook writeWorkBook_END = null;
        try
        {
            try
            {
                workBook_Source = Workbook.getWorkbook(new File(fileName));
            }
            catch(IOException e)
            {
                // TODO 自动生成 catch 块
                e.printStackTrace();
            }
        }
        catch(BiffException e1)
        {
            // TODO 自动生成 catch 块
            e1.printStackTrace();
        }
       
       
        //下面主要是将excel文件内容读取出来放到hashMap中
        sheetWorkBook_Source = workBook_Source.getSheet(0);
       LinkedHashMap  hashMap1 = new LinkedHashMap();
       LinkedHashMap hashMap2 = new LinkedHashMap();
       
        for(int i =0, rows = sheetWorkBook_Source.getRows(); i<rows;i++)
          {
            //从EXCEL文件中获取的值
            String []getValue = null;
            //记住第一个第一列不为零的行值
            int firstFlag = 0;
            for(int j =0, columns = 3; j<columns;j++)    
            {
                getValue[j] = sheetWorkBook_Source.getCell(j,i).getContents();
            }
            if( getValue[0]!=null && !getValue[0].trim().equals(""))
            {
                firstFlag = i;
              
            }
            //下面第一个if条件过滤掉某行全是空的那一行
            if(!getValue[0].trim().equals("") && !getValue[1].trim().equals("") &&!getValue[2].trim().equals(""))
            {
                //若hashMap2里面没有包含英文键值，则将键值加进去
                if(!hashMap2.containsKey(getValue[1]))
                {
                    hashMap2.put(getValue[1], getValue[2]);
                }
                //若hashMap2里面虽然已经包含英文键值，但是第三列有值（即为有所在文件xml ,properties 等的标志）则将键值加进去，覆盖，为了是增加第三列的值
                if(hashMap2.containsKey(getValue[1]) && hashMap2.get(getValue[1]) != null)
                {
                    hashMap2.put(getValue[1], getValue[2]);
                }
            }           
            if(sheetWorkBook_Source.getCell(0,i+1).getContents()!=null && !sheetWorkBook_Source.getCell(0,i+1).getContents().trim().equals(""))
            {
                hashMap1.put(sheetWorkBook_Source.getCell(0,firstFlag).getContents(), hashMap2);
                hashMap2.clear();
            }
          }

        /*******************************************************************************************************************/
        /******BEGIN该段作用主要是将源文件见一个可写的文件，便于对它进行修改操作*******************/
        try
        {
            writeWorkBook_Middle = Workbook.createWorkbook(new File("E://AfterSelected.xls"));
        }
        catch(IOException e1)
        {
            // TODO 自动生成 catch 块
            e1.printStackTrace();
        }
        writeWorkBook_Middle.createSheet("msg", 0);
        LinkedHashMap hashMap2Excel = new LinkedHashMap();
        int rows_excel = 0;
        for (Iterator iter = hashMap1.keySet().iterator(); iter.hasNext();)
        {
            rows_excel = rows_excel+1;
            String key_zh = iter.next().toString();
            String []valueFromMap = null;
            valueFromMap[0]=key_zh;
           
            Label label0 = new Label(0,rows_excel, valueFromMap[0]); 
            /begin 开始写第一列数据
            try
            {
                writeWorkBook_Middle.getSheet(0).addCell(label0);
            }
            catch(RowsExceededException e)
            {
                // TODO 自动生成 catch 块
                e.printStackTrace();
            }
            catch(WriteException e)
            {
                // TODO 自动生成 catch 块
                e.printStackTrace();
            }
            catch(IndexOutOfBoundsException e)
            {
                // TODO 自动生成 catch 块
                e.printStackTrace();
            }
            END  开始写第一列数据
            hashMap2Excel = (LinkedHashMap)hashMap1.get(key_zh);
            for (Iterator iter2 = hashMap2.keySet().iterator();  iter2.hasNext(); )
            {
                String key_en = iter2.next().toString();
                valueFromMap[1]=key_en;
                valueFromMap[2]= (String)hashMap2.get(key_en);
                Label label1 = new Label(1,rows_excel, valueFromMap[1]); 
            
                Label label2 = new Label(2,rows_excel, valueFromMap[2]); 
                try
                {
                    writeWorkBook_Middle.getSheet(0).addCell(label1);
                    writeWorkBook_Middle.getSheet(0).addCell(label2);
                    rows_excel = rows_excel+1;
                }
                catch(RowsExceededException e)
                {
                    // TODO 自动生成 catch 块
                    e.printStackTrace();
                }
                catch(WriteException e)
                {
                    // TODO 自动生成 catch 块
                    e.printStackTrace();
                }
                catch(IndexOutOfBoundsException e)
                {
                    // TODO 自动生成 catch 块
                    e.printStackTrace();
                }
              
            
            }

        }
        System.out.println("---------------------------------Create Writable File ok");           
        /*******************************************************************************************************************/
        /******END该段作用主要是将源文件见一个可写的文件，便于对它进行修改操作*******************/
           
           
           
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       try
       {
           Sheet workBook_Source_Sheet = workBook_Source.getSheet(0);// getSheet(0);
           int rowNumbers = workBook_Source_Sheet.getRows();
           int columnNumbers = workBook_Source_Sheet.getColumns();
//          
//           /*******************************************************************************************************************/
//           /******BEGIN该段作用主要是将源文件见一个可写的文件，便于对它进行修改操作*******************/
//           writeWorkBook_Middle = Workbook.createWorkbook(new File("E://AfterSelected.xls"));
//           writeWorkBook_Middle.createSheet("msg", 0);
//           for (int i = 0 ,realrow =0; i < rowNumbers;i++ )
//           {
//             
//              for (int j = 0 ; j < columnNumbers; j++)  
//               {
//                Cell  cell = workBook_Source_Sheet.getCell(j, i);
//                String value = cell.getContents();
//                if(value!=null && !value.equals(""))
//                { 
//                   Label label1 = new Label(j,realrow,value); 
//                   writeWorkBook_Middle.getSheet(0).addCell(label1);                   
//                   System.out.println(i);
//                   if(j==1)
//                   {
//                       realrow++;
//                   }
//                 
//                 }
//  
//               }
//           }
//           System.out.println("---------------------------------Create Writable File ok");           
//           /*******************************************************************************************************************/
//           /******END该段作用主要是将源文件见一个可写的文件，便于对它进行修改操作*******************/
          
              /****************************************************************************************************************/
              /*BEGIN 下面这段的功能主要是将中转文件AfterSelected.xls里的内容进行筛选处理*/
              /****************************************************************************************************************/

             
              Sheet writeWorkBook_Middle_Sheet = writeWorkBook_Middle.getSheet(0);// getSheet(0);
              int rowNumbersB = writeWorkBook_Middle_Sheet.getRows();
              int columnNumbersB = writeWorkBook_Middle_Sheet.getColumns();
              /*BEGIN该模块主要作用是将被操作的文件备份作为一个中转文件B*/
              for (int i = 0 ,realrow =0; i < rowNumbersB;i++ )
              {
                  //value0是用来与后面各行第一列的值比较的
                  String value0 =null;
                 for (int j = 0 ; j < columnNumbersB; j++)  
                 {
                   Cell  cell = writeWorkBook_Middle_Sheet.getCell(j, i);
                   String value = cell.getContents();
                    if(j==0)
                     {
                      value0 = value;
                    }
                    if(value.trim()!=null && !value.trim().equals(""))
                    { 
                      Label label1 = new Label(j,realrow,value);
                     
                      writeWorkBook_END.getSheet(0).addCell(label1); 
                      if(j==1)
                      {   //只能加1次
                          realrow=realrow+1;
                          System.out.println(i);
                      }
                    }
                 }
                
                 //下面判断有没有重复的,若有则将重复的添加到C文件中，并在BB中用“”替换关键字
                  if(value0.trim()!=null && !value0.trim().equals(""))
                  {                    
                      for(int k =i+1;k<rowNumbersB;k++)
                      {  

                            if(( writeWorkBook_Middle.getSheet(0).getCell(0, k).getContents()!=null)&&(value0.trim().equals(writeWorkBook_Middle.getSheet(0).getCell(0, k).getContents().trim())))
                            {
                                System.out.println("-------------------------------------k="+k);
                                  
                                //将重复的值拷贝到右边
                                Label label1 = new Label(1, realrow, writeWorkBook_Middle.getSheet(0).getCell(1, k).getContents().trim());
                                writeWorkBook_END.getSheet(0).addCell(label1);

                               
                                //将中转表中重复的那行置成空格
                                Label labelnull = new Label(0, k, "");
                                Label labelnull2 = new Label(1, k, "");
                                writeWorkBook_Middle.getSheet(0).addCell(labelnull);
                                writeWorkBook_Middle.getSheet(0).addCell(labelnull2);

                                realrow = realrow + 1;
                                if(k==18744)
                                {
                                    System.out.println("-------------------------------------2here");
                                }
                       
                            }

                       }
                  }
               }
              writeWorkBook_END.write();
              writeWorkBook_END.close();
              writeWorkBook_Middle.write();
              writeWorkBook_Middle.close();
              System.out.println("-------------------------------------END_ok");
             
              /*END下面这段的功能主要是将中转文件B里的内容检出来放在C中，第一列值相同的放在一起，并删除重复的字段中非第一次出现的键值*/
             
             
             
             
             
             
           }
          catch(Exception e)
          {
              e.printStackTrace();
          }
          workBook_Source.close();

    }

}

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值