package com; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileWriter; import java.io.IOException; import java.util.Iterator; import java.util.Scanner; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import au.com.bytecode.opencsv.CSVWriter; public class Xls2Csv2TxtConvertOne { public static void getXls2Csv(final String fileInput, final String fileOutput) { FileInputStream input_document = null; try { input_document = new FileInputStream(new File(fileInput)); } catch (FileNotFoundException e) { e.printStackTrace(); } HSSFWorkbook my_xls_workbook = null; try { my_xls_workbook = new HSSFWorkbook(input_document); } catch (IOException e) { e.printStackTrace(); } HSSFSheet my_worksheet = my_xls_workbook.getSheetAt(0); Iterator<Row> rowIterator = my_worksheet.iterator(); FileWriter my_csv = null; try { my_csv = new FileWriter(fileOutput); } catch (IOException e) { e.printStackTrace(); } CSVWriter my_csv_output = new CSVWriter(my_csv); while (rowIterator.hasNext()) { Row row = rowIterator.next(); int i = 0; String[] csvdata = new String[6]; Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: csvdata[i] = cell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: csvdata[i] = String.valueOf((int) cell .getNumericCellValue()); break; case Cell.CELL_TYPE_BLANK: csvdata[i] = ""; break; } i = i + 1; } my_csv_output.writeNext(csvdata); } try { input_document.close(); my_csv_output.close(); } catch (IOException e) { e.printStackTrace(); } } public static void getCsv2Txt(final String csvFileName) { Scanner scanner = null; try { scanner = new Scanner(new File(csvFileName)); } catch (FileNotFoundException e) { e.printStackTrace(); } scanner.useDelimiter(","); while (scanner.hasNext()) { System.out.print(scanner.next() + "|"); } scanner.close(); } /** * @param args */ public static void main(String[] args) { String fileInput = "D:\\DEV\\Notes\\GeoIPCountryWhois30.xls"; String fileOutput = "D:\\DEV\\Notes\\GeoIPCountryWhois30.csv"; Xls2Csv2TxtConvertOne.getXls2Csv(fileInput,fileOutput); Xls2Csv2TxtConvertOne.getCsv2Txt(fileOutput); } }
output:
-------
"1.0.0.0"|"1.0.0.255"|"16777216"|"16777471"|"AU"|"Australia" "1.0.1.0"|"1.0.3.255"|"16777472"|"16778239"|"CN"|"China" "1.0.4.0"|"1.0.7.255"|"16778240"|"16779263"|"AU"|"Australia" "1.0.8.0"|"1.0.15.255"|"16779264"|"16781311"|"CN"|"China" "1.0.16.0"|"1.0.31.255"|"16781312"|"16785407"|"JP"|"Japan" "1.0.32.0"|"1.0.63.255"|"16785408"|"16793599"|"CN"|"China" "1.0.64.0"|"1.0.127.255"|"16793600"|"16809983"|"JP"|"Japan" "1.0.128.0"|"1.0.255.255"|"16809984"|"16842751"|"TH"|"Thailand" "1.1.0.0"|"1.1.0.255"|"16842752"|"16843007"|"CN"|"China" "1.1.1.0"|"1.1.1.255"|"16843008"|"16843263"|"AU"|"Australia" "1.1.2.0"|"1.1.63.255"|"16843264"|"16859135"|"CN"|"China" "1.1.64.0"|"1.1.127.255"|"16859136"|"16875519"|"JP"|"Japan" "1.1.128.0"|"1.1.255.255"|"16875520"|"16908287"|"TH"|"Thailand" "1.2.0.0"|"1.2.2.255"|"16908288"|"16909055"|"CN"|"China" "1.2.3.0"|"1.2.3.255"|"16909056"|"16909311"|"AU"|"Australia" "1.2.4.0"|"1.2.127.255"|"16909312"|"16941055"|"CN"|"China" "1.2.128.0"|"1.2.255.255"|"16941056"|"16973823"|"TH"|"Thailand" "1.3.0.0"|"1.3.255.255"|"16973824"|"17039359"|"CN"|"China" "1.4.0.0"|"1.4.0.255"|"17039360"|"17039615"|"AU"|"Australia" "1.4.1.0"|"1.4.127.255"|"17039616"|"17072127"|"CN"|"China" "1.4.128.0"|"1.4.255.255"|"17072128"|"17104895"|"TH"|"Thailand" "1.5.0.0"|"1.5.255.255"|"17104896"|"17170431"|"JP"|"Japan" "1.6.0.0"|"1.7.255.255"|"17170432"|"17301503"|"IN"|"India" "1.8.0.0"|"1.8.255.255"|"17301504"|"17367039"|"CN"|"China" "1.9.0.0"|"1.9.255.255"|"17367040"|"17432575"|"MY"|"Malaysia" "1.10.0.0"|"1.10.9.255"|"17432576"|"17435135"|"CN"|"China" "1.10.10.0"|"1.10.10.255"|"17435136"|"17435391"|"AU"|"Australia" "1.10.11.0"|"1.10.127.255"|"17435392"|"17465343"|"CN"|"China" "1.10.128.0"|"1.10.255.255"|"17465344"|"17498111"|"TH"|"Thailand" "1.11.0.0"|"1.11.255.255"|"17498112"|"17563647"|"KR"|"Korea| Republic of"