I want to read particular string from a text file and store them in Excel sheets

My Text file Contains line like this

FLTR TID: 0000003756 RPC ID: 0000108159 USER: Remedy Application Service

FLTR TID: 0000003756 RPC ID: 0000108159 USER: Remedy Application Service

FLTR TID: 0000003756 RPC ID: 0000108159 USER: ibsdr

FLTR TID: 0000003756 RPC ID: 0000108159 USER: Vinay.k@in.uni.com

FLTR TID: 0000003756 RPC ID: 0000108159 USER: Vinay.k@in.uni.com

FLTR TID: 0000003756 RPC ID: 0000108159 USER: wsdl

FLTR TID: 0000003756 RPC ID: 0000108159 USER: stefan.hummel@uni.com

I want to store highlighted words in Excel sheets in columns wise and i want to store only unique user names.. like:

Column1....Column2

S.NO...........UserName

1..................Remedy Application Service

2..................ibsdr

3..................Vinay.k@in.uni.com

4..................wsdl

5..................stefan.hummel@uni.com

This is what I have tried

public class User

{

    static HSSFWorkbook hwb=new HSSFWorkbook();

    static HSSFSheet sheet = hwb.createSheet("new sheet");

    static HSSFRow rowhead= sheet.createRow((short)0);

    static HSSFRow row= sheet.createRow((short)1);

    static int count=1;

    public static void main(String[]args)

    {

        try{

            rowhead.createCell((short) 0).setCellValue("SNo");

            rowhead.createCell((short) 1).setCellValue("USER NAME");

            BufferedReader br=new BufferedReader(new FileReader("filter.log"));

            String str=null;

            String user=null;

            while((str=br.readLine())!=null)

            {

                if(str.contains("FLTR"))

                {

                    user=str.substring(48,75);   // This is to get user names

                    count++;

                    store(user,count);

                }

            }

            FileOutputStream fileOut = new FileOutputStream("D:/Excel.xls");

            hwb.write(fileOut);

            fileOut.close();

            System.out.println("Your excel file has been generated!");

        }

        catch ( Exception ex )

        { System.out.println(ex);

        }

    }

    private static void store(String user,int count)

    {

        row.createCell((short) 0).setCellValue(count);

        row.createCell((short) 1).setCellValue(user);

    }

}

OUTPUT

S.NO.......UserName

17963......Remedy Application Service

in this whenever I execute this program only first value is getting stored rather all values stores.. and I want to store only UNIQUE user names in this Excel sheet..

please help me , thanks in advance

Answers


  • For Unique Names you can Use ArrayList<String> add whenever new UserName appears add and before that check whether user exist in arrayList or not (by the Use of contains("UName")) and than proceed.
  • Use String username=line.substring(line.lastindexOf(":")).trim(); or You can use line.split(":")[1].trim();

For Excel row creation you can use loop.

int i=0;
while((str=br.readLine())!=null){
row = sheet.createRow((short) i);
cell = row.createCell(i);
cell.setCellValue("UserName");//Set UserName after getting it from 'str'
i++;
}

First off, I am not an expert and have never tried this myself nor can I at the moment. From looking at your code I can spot a few design flaws that might be the reason for your problem.

You only ever declare one row:

static HSSFRow row= sheet.createRow((short)1);

This means that in your store method, the same row's cells will be written again and again. What you are missing is the logic to create a new row if a unique name is encountered. For this task, may I suggest using a Set preferably one operating on hashes for the benefit of low lookup time, and storing all unique names in there? That way you can use a simply contains query.

Furthermore, your countvariable is off and starts counting at 2, not at one. Initialise it to 0 or increment it after its use.

Some pseudocode:

private HashSet<String> names = new HashSet<>();
// More fields here.

// Now start in your if clause:
if(str.contains("FLTR" && !names.contains(str.substring(48))
{
    store(user, count);
    count++;
    names.add(str.substring(48));
}

// More of your code.

private static void store(String user, int count)
{
    // Create new row
    HSSFRow row = sheet.createRow((short)rowCount);
    rowCount++;


    row.createCell((short) 0).setCellValue(count);
    row.createCell((short) 1).setCellValue(user);
}

Need Your Help

How to use font glyphs across multiple svg files?

javascript svg font-face epub ibooks

I have a number of svg files that use the same font glyphs in every svg. Is there a way to define those glyphs once and then call them from every svg file which needs them? The project I'm workin...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.