java excel 1

 

package test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
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;

class ExcelNode{
 public String str[] = new String[7];
 public ExcelNode() {
  // TODO Auto-generated constructor stub
 }
}

public class Test2 {

 public HashMap ht = null;
 
 /**
  * @param args
  * @throws IOException
  */
 public static void main(String[] args) throws IOException {
  // TODO Auto-generated method stub
  Test2 t = new Test2();
  t.map();
  t.work();
  
 }
 
 public void map() throws IOException {
  //ht.clear();
  ht = new HashMap();
  FileInputStream in = new FileInputStream("b.xls");
  POIFSFileSystem fs = new POIFSFileSystem(in);
  HSSFWorkbook wb = new HSSFWorkbook(fs);
  HSSFSheet sheet =  wb.getSheetAt(0);
  for(int i = 0;i<157;++i) {   
   HSSFRow row = sheet.getRow(i);
   String code = "";
   String addr = "";
   //if(row.getCell(0).getCellType()==0) {
   // code = (row.getCell(0).getNumericCellValue()+"");    
   //} else {
    code = row.getCell(0).getStringCellValue();   
   //}
   //if(row.getCell(1).getCellType()==0) {
   // addr = (row.getCell(1).getNumericCellValue()+"");    
   //} else {
    addr = row.getCell(1).getStringCellValue();   
   //}
   ht.put(code, addr);
   //p(code + " " + addr);
  }
 }
 
 public void p(Object o) {
  System.out.println(o.toString());
 }
 
 public void work() throws IOException {
  String filename = "非公党建问题数据";
  FileInputStream in = new FileInputStream("a.xls");
  File file = new File(filename);
  file.mkdir();
  POIFSFileSystem fs = new POIFSFileSystem(in);
  HSSFWorkbook wb = new HSSFWorkbook(fs);
    
  //HSSFCellStyle cs = wb.createCellStyle();
  //cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
  HSSFSheet sheet =  wb.getSheetAt(0);
  
 
  String index = "";
  String curindex = "";
  boolean flag = true;
  List<ExcelNode> list = new ArrayList<ExcelNode>();
  for(int i = 0;i<=sheet.getLastRowNum();++i) {   
   HSSFRow row = sheet.getRow(i);
   
   if(row.getCell(0).getCellType()==0) {
    curindex = (row.getCell(0).getNumericCellValue()+"").substring(0, 6);
   } else {
    System.out.println(row.getCell(0).getStringCellValue());
    if(row.getCell(0).getStringCellValue().length()<6){ flag = false; }
    else curindex = (row.getCell(0).getStringCellValue().substring(0, 6));    
   }
   if(!flag) {
    String path = filename+ "/"+ht.get(index) +index + filename+".xls";
    output(list,path);
    break;
   }
   if(!index.equals(curindex) && i!=sheet.getLastRowNum()) {    
    String path = filename+ "/"+ ht.get(index) +index + filename+".xls";
    output(list,path);
    list.clear();
    index = curindex;
   }
   ExcelNode node = new ExcelNode();
   for(int j=0;j<5;++j) {//row.getLastCellNum()
    HSSFCell cell = row.getCell(j);
    if(cell.getCellType()==0) {
     node.str[j] = cell.getNumericCellValue()+"";
    }else {
     node.str[j] = cell.getStringCellValue();
    }    
   }
   node.str[5] = (String) ht.get(index);
   list.add(node);
   if(i >= sheet.getLastRowNum() ) {
    //index = curindex;
    String path = filename+ "/"+ht.get(index) +index + filename+".xls";
    output(list,path);
    list.clear();
    break;
   }
   
  }
 }
 
 
 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 = 1;i<=list.size();++i) {
   row = sheet.createRow(i);
   for(int j = 0;j<6;++j) {
    row.createCell(j).setCellValue(list.get(i-1).str[j]);
   }
  }
  
  // Save
  FileOutputStream out = new FileOutputStream(path);
  wb.write(out);
  out.close();
 }
}