Excel导入导出.xlsx格式

Excel的导入导出
用的maven导包如下：

<dependency>
     	<groupId>org.apache.poi</groupId>
    	<artifactId>poi</artifactId>
    	<version>3.13</version>
 </dependency>
 
 <dependency>
     <groupId>org.apache.poi</groupId>
    	<artifactId>poi-ooxml</artifactId>
     <version>3.13</version>
 </dependency>

导出方法：

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

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;

public class Excel {
public static SimpleDateFormat sdf = new SimpleDateFormat(“yyyy-MM-dd”);

public static void main(String[] args) {
	// 导出
	ExcelExport();
	// 导入
	ExcelImport();
}

// Excel导出
private static void ExcelExport() {
	 try {
		// 创建Excel文件对象
        Workbook workbook = new XSSFWorkbook();
        // 建立一张表
        Sheet sheet = workbook.createSheet("Excel导出");
        // 生成第一行存放表头
        Row row = sheet.createRow(0);
        String[] tableTitle = { "条码", "数量", "单价" };
        for (int i = 0; i < tableTitle.length; i++) {
            // 设置列宽
            sheet.setColumnWidth(i, 10 * 256);
            row.createCell(i).setCellValue(tableTitle[i]);
        }
        
        for(int i = 0; i < 10; i++) {
        	row = sheet.createRow(i + 1);
        	row.createCell(0).setCellValue("100"+i);// 条码
        	row.createCell(1).setCellValue(""+i);// 数量
        	row.createCell(2).setCellValue(""+(i*10));// 单价
        }       
    	 // 导出文件
        String fileName = "Excel导出.xlsx";
        // 当前项目下conf/otherStoreIn
        FileOutputStream fileOut = new FileOutputStream("E:" + File.separator + fileName);
		workbook.write(fileOut);
		fileOut.close();
		workbook.close();
	} catch (IOException e) {
		e.printStackTrace();
	}     
}
// Excel导出入
private static void ExcelImport() {
	// 本地文件的路径
	String File = "数据.xlsx";
	List<CityDataEntity> cityList = null;
	FileInputStream fis = null;
	Workbook workbook =null;
	try {
		fis = new FileInputStream(new File(File));
		workbook = new XSSFWorkbook(fis);
		Sheet sheet = workbook.getSheetAt(0);
		
		// 读取Sheet中的数据
		cityList =new  ArrayList<CityDataEntity>();
		
		//获取总行数 
        int rowCount = sheet.getPhysicalNumberOfRows();  
        
        //遍历每一行  
        for (int r = 0; r < rowCount; r++) {  
            Row row = sheet.getRow(r);  
            
            //获取总列数  
            int cellCount = row.getPhysicalNumberOfCells();
            
            //遍历每一列  
            String code = null;
            String name = null;
            String longitude = null;
            String latitude = null;
            for (int c = 0; c < cellCount; c++) {  
                Cell cell = row.getCell(c);  
                int cellType = cell.getCellType();  
                String cellValue = null;  
                switch(cellType) {  
                    case Cell.CELL_TYPE_STRING: //文本  
                        cellValue = cell.getStringCellValue();  
                        break;  
                    case Cell.CELL_TYPE_NUMERIC: //数字、日期  
                        if(DateUtil.isCellDateFormatted(cell)) {  
                            cellValue = sdf.format(cell.getDateCellValue()); //日期型  
                        }  
                        else {  
                            cellValue = String.valueOf(cell.getNumericCellValue()); //数字  
                            if(cellValue.endsWith(".0")) {
                            	cellValue = cellValue.substring(0, cellValue.length()-2);
                            }
                        }  
                        break;  
                    case Cell.CELL_TYPE_BOOLEAN: //布尔型  
                        cellValue = String.valueOf(cell.getBooleanCellValue());  
                        break;  
                    case Cell.CELL_TYPE_BLANK: //空白  
                        cellValue = cell.getStringCellValue();  
                        break;  
                    case Cell.CELL_TYPE_ERROR: //错误  
                        cellValue = "错误";  
                        break;  
                    case Cell.CELL_TYPE_FORMULA: //公式  
                        cellValue = "错误";  
                        break;  
                    default:  
                        cellValue = "错误";  
                }  
                cellValue = cellValue.trim();
                if(c==0) {
                	code = cellValue;
                }else if(c == 1) {
                	name = cellValue;
                }else if(c == 2) {
                	longitude = cellValue;
                }else {
                	latitude = cellValue;
                }
            }  
            CityDataEntity city = new CityDataEntity(code, name, longitude, latitude);
			cityList.add(city);	
        }  

	} catch (FileNotFoundException e) {
		e.printStackTrace();
	}catch (IOException e) {
		e.printStackTrace();
	}finally {
		try {
			workbook.close();
			fis.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	// 返回获取到的数据
	System.out.println(cityList);
}

}

实体类用于接收读取的数据
public class CityDataEntity{
// 对应Excel表里对象
private String code;
private String name;
private String longitude;
private String latitude;

public CityDataEntity() {
	super();
}

public CityDataEntity(String code, String name, String longitude, String latitude) {
	super();
	this.code = code;
	this.name = name;
	this.longitude = longitude;
	this.latitude = latitude;
}

public String getCode() {
	return code;
}
public void setCode(String code) {
	this.code = code;
}
public String getName() {
	return name;
}
public void setName(String name) {
	this.name = name;
}
public String getLongitude() {
	return longitude;
}
public void setLongitude(String longitude) {
	this.longitude = longitude;
}
public String getLatitude() {
	return latitude;
}
public void setLatitude(String latitude) {
	this.latitude = latitude;
}

@Override
public String toString() {
	return "CityDataEntity [code=" + code + ", name=" + name + ", longitude=" + longitude + ", latitude=" + latitude
			+ "]";
}