• Post Reply Bookmark Topic Watch Topic
  • New Topic

Problem in compare excel  RSS feed

 
Satya Kadali
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
package test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;

public class FirstSheet {


public static void main(String[] args) {
HSSFWorkbook workbook_Address = new HSSFWorkbook();
HSSFWorkbook workbook_Purchase = new HSSFWorkbook();
HSSFWorkbook workbook_Output = new HSSFWorkbook();

HSSFSheet sheet_Adress;
HSSFSheet sheet_Purchase;
HSSFSheet sheet_Output;
HSSFFont font_Output;


HSSFRow row_Adress;
HSSFRow row_Purchase;


HSSFCell cell_Adress;
HSSFCell cell_Purchase;

HSSFCellStyle style_Output;

FileInputStream fistream_Address;
FileInputStream fistream_Purchase;

FileOutputStream fostream_Output;

int style_count_value=1;



try
{



fistream_Address = new FileInputStream("C:\\Users\\Satya\\Desktop\\Address.xls");
fistream_Purchase = new FileInputStream("C:\\Users\\Satya\\Desktop\\Purchase.xls");

fostream_Output = new FileOutputStream("C:\\Users\\Satya\\Desktop\\Output.xls");
sheet_Output = workbook_Output.createSheet("Output");

workbook_Address = new HSSFWorkbook(new POIFSFileSystem(fistream_Address));
workbook_Purchase = new HSSFWorkbook(new POIFSFileSystem(fistream_Purchase));

int numsheets = workbook_Address.getNumberOfSheets();
int output_rows=0;

System.out.println("Nuber of sheets in Address workbook: "+numsheets);

for (int i = 0; i < numsheets; i++) {

List<String> final_list = new ArrayList<String>();

sheet_Adress = workbook_Address.getSheetAt((short)i);
int lastrow_adress = sheet_Adress.getLastRowNum();
System.out.println("Lasr row nuber: "+lastrow_adress);

for (int j = 0; j <= lastrow_adress; j++) {
row_Adress = sheet_Adress.getRow((short)j);
cell_Adress = row_Adress.getCell(3);
String adress_value = cell_Adress.getStringCellValue();

sheet_Purchase = workbook_Purchase.getSheetAt((short)0);
int lastrow_purchase = sheet_Purchase.getLastRowNum();

for (int k = 0; k <= lastrow_purchase; k++) {
row_Purchase = sheet_Purchase.getRow((short)k);
cell_Purchase = row_Purchase.getCell(4);
String purchage_value = cell_Purchase.getStringCellValue();

if(adress_value =="")
{
System.out.println("Server value empty: "+cell_Adress.getCellType());
System.out.println("Server Cell Value: "+cell_Adress.getStringCellValue().length());
System.out.println("Server Cell Row number: "+cell_Adress.getRow().getRowNum());
}


if(adress_value !="")
{
if(purchage_value.toLowerCase().contains(adress_value.toLowerCase()))
{

System.out.println("Conatins: "+purchage_value+" "+adress_value);
final_list.add(row_Adress.getCell(0).getStringCellValue());
// final_list.add(row_Adress.getCell(6).getNumericCellValue());
final_list.add(row_Adress.getCell(0).getStringCellValue());
final_list.add(row_Purchase.getCell(0).getStringCellValue());
final_list.add(row_Purchase.getCell(3).getStringCellValue());
final_list.add(row_Purchase.getCell(4).getStringCellValue());
// final_list.add(row_Purchase.getCell(7).getStringCellValue());
if(style_count_value==1)
{

font_Output = workbook_Output.createFont();
font_Output.setFontName("Times New Roman");
font_Output.setBoldweight((short) 1);
style_Output = workbook_Output.createCellStyle();
style_Output.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index);
style_Output.setBorderBottom((short) 2);
style_Output.setBorderRight((short) 2);
style_Output.setFont(font_Output);

style_Output.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style_Output.setFillForegroundColor(HSSFColor.AQUA.index);
Row row = sheet_Output.createRow(output_rows);
//row.setRowStyle(style_Output);

row.createCell(0).setCellValue(row_Adress.getCell(0).getStringCellValue());
row.getCell(0).setCellStyle(style_Output);
row.createCell(1).setCellValue(row_Purchase.getCell(0).getStringCellValue());
row.getCell(1).setCellStyle(style_Output);
row.createCell(2).setCellValue(row_Purchase.getCell(3).getStringCellValue());
row.getCell(2).setCellStyle(style_Output);
row.createCell(3).setCellValue(row_Purchase.getCell(4).getStringCellValue());
row.getCell(3).setCellStyle(style_Output);
style_count_value++;
}
else
{
Row row = sheet_Output.createRow(output_rows);
row.createCell(0).setCellValue(row_Adress.getCell(0).getStringCellValue());
row.createCell(1).setCellValue(row_Purchase.getCell(0).getStringCellValue());
row.createCell(2).setCellValue(row_Purchase.getCell(3).getStringCellValue());
row.createCell(3).setCellValue(row_Purchase.getCell(4).getStringCellValue());
}
output_rows++;

}
}
}
}




/*int z =0;
for (Iterator<String> iterator2 = final_list.iterator(); iterator2.hasNext();) {

z=z+1;
if(z<=5)
{

System.out.print(final_list.get(z));

}
else
{
System.out.println();
z=0;
}


} */
fistream_Address.close();
fistream_Purchase.close();

workbook_Output.write(fostream_Output);
fostream_Output.close();

}
}
catch(Exception e)
{
e.printStackTrace();

}

}

}


 
Ulf Dittmer
Rancher
Posts: 42972
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please UseCodeTags. And tell us what you perceive the problem to be.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!