POI 导入excel 代码记录方便以后粘贴

最新推荐文章于 2024-10-01 18:51:18 发布

weixin_34111819

最新推荐文章于 2024-10-01 18:51:18 发布

阅读量51

点赞数

文章标签： java

原文链接：http://www.cnblogs.com/huaixiaonian/p/9238223.html

版权

import java.io.FileInputStream;
import java.io.InputStream;

import javax.annotation.Resource;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
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.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.ys.mqpms.model.project.BaseInfo;
import com.ys.mqpms.service.project.IBaseInfoService;

public class POIImport {

    @Resource
    private IBaseInfoService baseInfoService;

    public void poiImport() throws Exception {
        InputStream is = new FileInputStream("D:/temp.xlsx");
        // 使用 Workbook 接口,解决2003版本和2007版本的excel 异常问题
        Workbook book = null;
        try {
            book = new XSSFWorkbook(OPCPackage.open(is));
        } catch (Exception ex) {
            book = new HSSFWorkbook(is);
        }
        // 定义接收表单数据的对象
        BaseInfo baseInfo = null;
        // 循环工作表Sheet
        for (int numSheet = 0; numSheet < book.getNumberOfSheets(); numSheet++) {
            Sheet sheet = book.getSheetAt(0);
            if (sheet == null) {
                continue;
            }
            // 循环行Row
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row != null) {
                    baseInfo = new BaseInfo();
                    // 得到行数据
                    Cell code = row.getCell(0);
                    Cell ncCode = row.getCell(1);
                    Cell name = row.getCell(2);
                    Cell contPrice = row.getCell(3);

                    // 为对象赋值
                    baseInfo.setCode(getValue(code));
                    baseInfo.setNcCode(getValue(ncCode));
                    baseInfo.setName(getValue(name));
                    baseInfo.setContPrice(Double.valueOf(getValue(contPrice)));

                    // 插入数据表
                    baseInfoService.insertBaseInfo(baseInfo);

                }
            }
        }
    }

    // Cell 转 String
    private String getValue(Cell cell) {
        if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
            // 返回布尔类型的值
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
            // 返回数值类型的值
            return String.valueOf(cell.getNumericCellValue());
        } else {
            // 返回字符串类型的值
            return String.valueOf(cell.getStringCellValue());
        }
    }

}