package test;
import java.io.File;
import java.io.FileFilter;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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.poifs.filesystem.POIFSFileSystem;
public class Test3 {
public void p(Object o) {
System.out.println(o.toString());
}
/**
* @param args
* @throws IOException
*/
public static void main(String[] args) throws IOException {
// TODO Auto-generated method stub
Test3 test = new Test3();
test.work();
}
public void work() throws IOException{
File file = new File("data");
File[] files = file.listFiles();
for(int i=0;i<files.length;++i)
{
if(files[i].isDirectory()) {
String filename = "data/"+files[i].getName()+".xls";
File tempfile = new File(filename);
File[] xlss = files[i].listFiles(new FileFilter() {
public boolean accept(File file) {
return file.getName().endsWith(".xls");
}
});
List<ExcelNode> list = new ArrayList<ExcelNode>();
for(int j=0;j<xlss.length;++j) {
int start;
if(j==0) start = 0; else start=1;
String filePath = xlss[j].getPath();
p(filePath);
List templist = read(filePath,start);
list.addAll(templist);
}
output(list,filename);
}
}
}
public List read(String inPath,int flag) throws IOException {
FileInputStream in_in = new FileInputStream(inPath);
POIFSFileSystem in_fs = new POIFSFileSystem(in_in);
HSSFWorkbook in_wb = new HSSFWorkbook(in_fs);
//HSSFCellStyle cs = wb.createCellStyle();
//cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
HSSFSheet in_sheet = in_wb.getSheetAt(0);
List<ExcelNode> list = new ArrayList<ExcelNode>();
int start;
if(flag==0) start = 0; else start=1;
for(int i = start;i<in_sheet.getLastRowNum();++i) {
HSSFRow row = in_sheet.getRow(i);
ExcelNode node = new ExcelNode();
if(row !=null) {
for(int j=0;j<row.getLastCellNum();++j) {//row.getLastCellNum()
HSSFCell cell = row.getCell(j);
if(cell != null) {
if(cell.getCellType()==0) {
Double dValue = Double.valueOf(Double.toString(cell.getNumericCellValue()));
BigDecimal original = new BigDecimal(dValue.doubleValue());
node.str[j] = original.toString();
}else {
node.str[j] = cell.getStringCellValue();
}
}
}
node.len = row.getLastCellNum();
list.add(node);
}
}
return list;
}
public void output(List<ExcelNode> list,String path) throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
// create a new sheet
HSSFSheet sheet = wb.createSheet();
// declare a row object reference
HSSFRow row = null;
// declare a cell object reference
HSSFCell cell = null;
HSSFCellStyle cs = wb.createCellStyle();
cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
/*row = sheet.createRow(0);
row.createCell(0).setCellValue("注册号");
row.createCell(1).setCellValue("企业类型");
row.createCell(2).setCellValue("党员数");
row.createCell(3).setCellValue("从业人员数");
row.createCell(4).setCellValue("异常数据说明");
row.createCell(5).setCellValue("所属局");
*/
for(int i = 0;i<list.size();++i) {
row = sheet.createRow(i);
for(int j = 0;j<list.get(i).len;++j) {
row.createCell(j).setCellValue(list.get(i).str[j]);
}
}
// Save
FileOutputStream out = new FileOutputStream(path);
wb.write(out);
out.close();
}
}