package com.xuan.excel;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/ **
* <p> Copyright: Copyright (C) 2014-2099 </ p>
* ────────────────────────────────────
* <p> Author: Qinglian Sword Fairy </ p>
* ────────────────────────────────────
* <p> Convert excel to TXT text <p>
** /
public class ExcelToTxt {
private static File [] getFiles (String path) {
File file = new File (path);
// get the folder list
File [] array = file.listFiles ();
return array;
}
public static void main (String [] args) throws IOException {
File [] files = getFiles ("D: \\ keyword");
for (int i = 0; i <files.length; i ++) {
if (files [i] .isFile ()) {
System.out.println ("f ==" + files [i] .getPath () + "" + files [i] .getName ());
publishTxt (files [i] .getPath ());
}
if (files [i] .isDirectory ()) {
System.out.println ("d ==" + files [i] .getPath () + "" + files [i] .getName ());
File [] files2 = getFiles (files [i] .getPath ());
for (int j = 0; j <files2.length; j ++) {
publishTxt (files2 [j] .getPath ());
}
}
}
}
public static void publishTxt (String excelPath) {
String columns [] = {"transaction time", "accounting date", "bank serial number", "merchant serial number", "order number", "order status", "payer account / customer number", "payment Square account name ",
"Order Amount", "Transaction Amount", "Handling Fee", "Settlement Amount", "Counter Code", "Issuer / Channel", "Payment Card Type", "Transaction Type", "Terms", "Authorization Number "," item number ",
"Basic Household", "Remark 1", "Remark 2"};
Workbook wb = null;
Sheet sheet = null;
Row row = null;
List <Map <String, String >> list = null;
String cellData = null;
String fileType = excelPath.substring (excelPath.indexOf (‘.‘) + 1);
wb = readExcel (excelPath);
if (wb! = null) {
// Used to store data in the table
list = new ArrayList <Map <String, String >> ();
// Get the first sheet
sheet = wb.getSheetAt (0);
// Get the maximum number of rows
int rownum = sheet.getPhysicalNumberOfRows ();
// get the second line
row = sheet.getRow (1);
// Get the maximum number of columns
int colnum = row.getPhysicalNumberOfCells ();
for (int i = 0; i <rownum; i ++) {
Map <String, String> map = new LinkedHashMap <String, String> ();
row = sheet.getRow (i);
if (row! = null) {
for (int j = 0; j <colnum; j ++) {
cellData = (String) getCellFormatValue (row.getCell (j));
map.put (columns [j], cellData);
}
} else {
break;
}
list.add (map);
}
}
// Traverse the parsed list
StringBuffer sb = new StringBuffer ();
for (int i = 0; i <list.size (); i ++) {
for (Entry <String, String> entry: list.get (i) .entrySet ()) {
String value = entry.getValue ();
sb.append (value + ",");
}
sb.append ("\ r \ n");
}
try {
WriteToFile (sb.toString (), excelPath.replace (". Xlsx", ".txt"));
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace ();
}
System.out.println ("************* EXCEL converted to TXT format successfully *************");
}
// read excel
public static Workbook readExcel (String filePath) {
Workbook wb = null;
if (filePath == null) {
return null;
}
String extString = filePath.substring (filePath.lastIndexOf ("."));
InputStream is = null;
try {
is = new FileInputStream (filePath);
if (".xls" .equals (extString)) {
return wb = new HSSFWorkbook (is);
} else if (".xlsx" .equals (extString)) {
return wb = new XSSFWorkbook (is);
} else {
return wb = null;
}
} catch (FileNotFoundException e) {
e.printStackTrace ();
} catch (IOException e) {
e.printStackTrace ();
}
return wb;
}
public static Object getCellFormatValue (Cell cell) {
Object cellValue = null;
if (cell! = null) {
// determine the cell type
switch (cell.getCellType ()) {
case Cell.CELL_TYPE_NUMERIC: {
cellValue = String.valueOf (cell.getNumericCellValue ());
break;
}
case Cell.CELL_TYPE_FORMULA: {
try {
// determine whether the cell is in date format
if (DateUtil.isCellDateFormatted (cell)) {
// Convert to date format YYYY-mm-dd
cellValue = cell.getRichStringCellValue (). getString (); / cell.getDateCellValue ();
} else {
// number
cellValue = cell.getRichStringCellValue (). getString (); //String.valueOf (cell.getNumericCellValue ());
}
} catch (Exception ex) {}
break;
}
case Cell.CELL_TYPE_STRING: {
cellValue = cell.getRichStringCellValue (). getString ();
break;
}
default:
cellValue = "";
}
} else {
cellValue = "";
}
return cellValue;
}
/ **
* Generate file
* @param str
* @param filePath
* @throws IOException
* /
public static void WriteToFile (String str, String filePath) throws IOException {
BufferedWriter bw = null;
try {
FileOutputStream out = new FileOutputStream (filePath, true); // true, means: append content to the file, not regenerate, default is false
bw = new BufferedWriter (new OutputStreamWriter (out, "GBK"));
bw.write (str + = "\ r \ n"); // line feed
bw.flush ();
} catch (Exception e) {
e.printStackTrace ();
} finally {
bw.close ();
}
}
}