excel工具类

这两天在整理数据字典，特别麻烦，后来还要写sql，索性就写了一个工具类，生成sql了

用的poi，读取和生成的都是xlsx，如果是xls的话 只需要讲xssf 改成hssf就成了

package com;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class XlsxMain {

	public static void main(String[] args) throws IOException {  
        XlsxMain xlsxMain = new XlsxMain();  
  
        xlsxMain.readXlsx("G:/新增省市对照.xlsx");  
    <span style="white-space:pre">	</span>}  

<span style="white-space:pre">	</span><pre name="code" class="java"> <span style="white-space:pre">	</span>public void readXlsx(String filename) throws IOException {  
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(filename);  
        List<ProvinceDO> list = new ArrayList<ProvinceDO>();

        // 循环工作表Sheet  
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {  

            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);   
            if (xssfSheet == null) {  
                continue;  
            }  

//             循环行Row  
            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {  
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);  
                if (xssfRow == null) {  
                    continue;  
                }  

                // 循环列Cell  
                ProvinceDO provinceDO = new ProvinceDO();
                for (int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++) { 
                	
                    XSSFCell xssfCell = xssfRow.getCell(cellNum);  
                      
                    if (xssfCell == null) {  
                        continue;  
                    }else {
                    	
                      //根据具体列 往对象中存值
                      switch (cellNum) {
                	case 0: provinceDO.setNo(cellNum+1); break;
                	case 1: provinceDO.setId(Integer.valueOf(getCellValue(xssfCell).trim().split("\\.")[0]));break;
                	case 5: provinceDO.setType(3);break;
                	case 6: provinceDO.setsName(getCellValue(xssfCell));break;
                	case 10: provinceDO.setsCode(Integer.valueOf(getCellValue(xssfCell).trim().split("\\.")[0]));break;
                	case 14: provinceDO.setcName(getCellValue(xssfCell));break;
                	case 18: provinceDO.setcCode(Integer.valueOf(getCellValue(xssfCell).trim().split("\\.")[0]));break;
                	case 22: provinceDO.setName(getCellValue(xssfCell));break;
                	case 26:provinceDO.setCode(Integer.valueOf(getCellValue(xssfCell).trim().split("\\.")[0]));break;
                	default:continue;
                      } 
		   } 
                }  
                list.add(provinceDO);

            }  
        }  
        writeXlsx(list);
    }  
  

   /**

     *写入到excel中

   *<span style="font-family: Arial, Helvetica, sans-serif;">/</span>

    public void writeXlsx(List<ProvinceDO> list) throws FileNotFoundException, IOException {
		File file = new File("G:\\jieguo.xlsx");
		XSSFWorkbook wb = new XSSFWorkbook();
		XSSFSheet sheet = wb.createSheet("新增");
		
		//先创建好表头
		XSSFRow row = sheet.createRow(0);
		XSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
			XSSFCell cell = row.createCell(0);
			cell.setCellValue("ID");
			cell = row.createCell(1);
			cell.setCellValue("县区");
			cell = row.createCell(2);
			cell.setCellValue("sql");
	
		//创建内容
		for(int i = 0;i < list.size();i++){
			row = sheet.createRow(i+1);
			ProvinceDO provinceDO = list.get(i);
			StringBuffer sb = new StringBuffer("insert into u_province(id,type,code,pcode,name) values(");
			sb.append(provinceDO.getId()).append(",");
			sb.append(provinceDO.getType()).append(",");
			sb.append(provinceDO.getId()).append(",");
			sb.append(provinceDO.getcCode()).append(",");
			sb.append("\'").append(provinceDO.getName()).append("\'").append(");");
			String sql = sb.toString();
			row.createCell(0).setCellValue(provinceDO.getId());
			row.createCell(1).setCellValue(provinceDO.getName());
			row.createCell(2).setCellValue(sql);
			
		}
		
		wb.write(new FileOutputStream(file));
		
		
		
		
	}

	<pre name="code" class="java"><span style="white-space:pre">	</span>/**   
    <span style="white-space:pre">	</span> * 获取单元格的值   
    <span style="white-space:pre">	</span> * @param cell   
     <span style="white-space:pre">	</span>* @return   
     <span style="white-space:pre">	</span>*/    
     public String getCellValue(Cell cell){    
             
         if(cell == null) return "";    
             
         if(cell.getCellType() == Cell.CELL_TYPE_STRING){    
                 
             return cell.getStringCellValue();    
                 
         }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){    
                 
             return String.valueOf(cell.getBooleanCellValue());    
                 
         }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){    
                 
             return cell.getCellFormula() ;    
                 
         }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){    
                 
             return String.valueOf(cell.getNumericCellValue());    
                 
         }    
         return "";    
     }    

}

<pre name="code" class="java">package com;

public class ProvinceDO {

	private Integer no;
	
	private Integer id;
	
	private Integer type;
	
	private String sName;
	
	private Integer sCode;
	
	private String cName;
	
	private Integer cCode;//放市code
	
	private String name;
	
	private Integer code;//放自身code

	public Integer getNo() {
		return no;
	}

	public void setNo(Integer no) {
		this.no = no;
	}


	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public Integer getType() {
		return type;
	}

	public void setType(Integer type) {
		this.type = type;
	}

	public String getsName() {
		return sName;
	}

	public void setsName(String sName) {
		this.sName = sName;
	}

	public Integer getsCode() {
		return sCode;
	}

	public void setsCode(Integer sCode) {
		this.sCode = sCode;
	}

	public String getcName() {
		return cName;
	}

	public void setcName(String cName) {
		this.cName = cName;
	}

	public Integer getcCode() {
		return cCode;
	}

	public void setcCode(Integer cCode) {
		this.cCode = cCode;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Integer getCode() {
		return code;
	}

	public void setCode(Integer code) {
		this.code = code;
	}
	
	
	
	
	
	
}