-
Delete duplicate records from Excel and copy into other Excel file in java
over 9 years ago
-
about 9 years ago
Hi Bhagwan,
Here the below code can copy the unique records from Writesheet.xlsx into WritesheetNew.xlsx file. The Writesheet.xlsx can contain duplicate records so it can copy the unique records into your new WritesheetNew.xlsx file.
package com.evon; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.util.HashSet; import java.util.Iterator; import java.util.Map; import java.util.Set; import java.util.TreeMap; import java.util.TreeSet; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadWriteExcel {
public static void WriteSheet()throws Exception { //Create blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet spreadsheet = workbook.createSheet("Employee Details"); //Create row object XSSFRow row; //This data needs to be written (Object[]) Map< String, Object[] > empinfo = new TreeMap
(); empinfo.put( "1", new Object[] { "EMP NAME"}); empinfo.put( "2", new Object[] { "Manish" }); empinfo.put( "3", new Object[] { "Manish" }); empinfo.put( "4", new Object[] { "Namita" }); empinfo.put( "7", new Object[] { "Namita" }); empinfo.put( "5", new Object[] { "Akhilesh"}); empinfo.put( "6", new Object[] { "Neelam" }); //Iterate over data and write to sheet Set < String > keyid = empinfo.keySet(); int rowid = 0; for (String key : keyid) { row = spreadsheet.createRow(rowid++); Object [] objectArr = empinfo.get(key); int cellid = 0; for (Object obj : objectArr) { Cell cell = row.createCell(cellid++); cell.setCellValue((String)obj); } } //Write the workbook in file system FileOutputStream out = new FileOutputStream(new File("/home/manish/Desktop/Writesheet.xlsx")); workbook.write(out); out.close(); } public static void CheckDuplicate(File file)throws Exception { Set
newReordsList=new TreeSet (); FileInputStream fis = new FileInputStream(file); XSSFWorkbook workbook = new XSSFWorkbook(fis); XSSFSheet spreadsheet = workbook.getSheetAt(0); XSSFRow row; Iterator < Row > rowIterator = spreadsheet.iterator(); while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); //System.out.println("----->"+spreadsheet.get); Iterator< Cell > cellIterator = row.cellIterator(); Cell cell; while (cellIterator.hasNext()) { cell = cellIterator.next();
if(cell.getRowIndex()==0) continue;
switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: //System.out.print(cell.getStringCellValue() + " \t\t " ); newReordsList.add(cell.getStringCellValue()); break; }
}
} fis.close(); copyInExcel(newReordsList);
}
public static void copyInExcel(Set
records)throws Exception { //Create blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet spreadsheet = workbook.createSheet("Employee Details New"); //Create row object XSSFRow row;
int rowid = 0; int cellid = 0; for (String record : records) { row = spreadsheet.createRow(rowid++);Cell cell = row.createCell(cellid); cell.setCellValue(record);
} //Write the workbook in file system FileOutputStream out = new FileOutputStream(new File("/home/manish/Desktop/WritesheetNew.xlsx")); workbook.write(out); out.close(); } public static void main(String[] args) {
try{ //To Write The Excel Sheet WriteSheet();
//To Copy the Excel Sheet CheckDuplicate(new File("/home/manish/Desktop/Writesheet.xlsx")); } catch(Exception e){System.out.println(e);}
}
}
-
over 7 years ago
Presently I need to check the copies just for the 3 segments: ID,Firstname and Last name together. In the event that these segments together contain same information as appeared in the above illustration then it should be viewed as copy.
-
2 Answer(s)