1,编码转换
public class TranCode {
public static void tranCode(String filePath,String inputCode,String outputCode) throws IOException {
String outputFileName = filePath+".back";
StringBuilder sb = new StringBuilder();
BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath),inputCode));
String line;
while((line = reader.readLine())!= null){
if(line !=null && !"".equals(line)){
sb.append(line+"\n");
}
}
PrintWriter pw = new PrintWriter(new OutputStreamWriter(new FileOutputStream(outputFileName),outputCode));
pw.append(sb);
pw.flush();
pw.close();
reader.close();
new File(filePath).delete();
new File(filePath+".back").renameTo(new File(filePath));
}
public static void gbk2Utf8(String filePath) throws IOException {
tranCode(filePath, Constants.GB2312, Constants.UTF8);
}
public static void utf82Gbk(String filePath) throws IOException {
tranCode(filePath,Constants.UTF8,Constants.GBK);
}
2,读取Excel
import com.gridsum.domains.assist.Constants;
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.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
/**
* HSSF-提供读写Microsoft Excel XLS格式档案的功能。
XSSF-提供读写Microsoft Excel OOXML XLSX格式档案的功能。
HWPF-提供读写Microsoft Word DOC格式档案的功能。
HSLF- 供读写Microsoft PowerPoint格式档案的功能。
HDGF-提供读Microsoft Visio格式档案的功能。
HPBF-提供读Microsoft Publisher格式档案的功能。
*/
public class AccessExcel {
public static void main(String[] args) throws IOException {
excel2Text("E:\\work\\AUD\\AUD立项\\test2.xlsx","E:\\work\\Project\\DomainInformationBase\\zhunbeiziliao\\CustomerCategory.properties");
}
private static void excel2Text(String src, String dest) throws IOException {
//excel2Text(src, dest,2,0);
excel2Text(src,dest,1,1);
}
//read
public static void excel2Text(String src,String dest,int rowNumStart,int cellNumStart) throws IOException{
File outputFile = new File(dest);
if(outputFile.exists()) outputFile.delete();
StringBuilder sb = new StringBuilder();
Workbook book = null;
InputStream is = new FileInputStream(src);
try{
book = new XSSFWorkbook(is);
}catch (Exception e){
book = new HSSFWorkbook(is);
}
assert book != null;
int sheetLen = book.getNumberOfSheets();
for(int sheetNum = 0;sheetNum < sheetLen ; sheetNum++){
Sheet sheet = book.getSheetAt(sheetNum);
String sheetName = sheet.getSheetName();
int firstRowNum = rowNumStart==0?sheet.getFirstRowNum():rowNumStart;
int lastRowNum = sheet.getLastRowNum();
for(int rowNum=firstRowNum;rowNum <=lastRowNum;rowNum++){
Row row = sheet.getRow(rowNum);
if(row!=null){
int firstCellNum = cellNumStart==0?row.getFirstCellNum():cellNumStart;
int lastCellNum = row.getLastCellNum();
String line = "";
for(int cellNum =firstCellNum;cellNum < lastCellNum;cellNum++){
line += row.getCell(cellNum)+ Constants.EQUAL;
}
if( !"".equals(line)){
sb.append(line.substring(0,line.length()-1)).append("\n");
}
}
}
}
PrintWriter writer = new PrintWriter(new FileWriter(outputFile));
writer.append(sb.toString());
writer.close();
is.close();
}
}