poi上传excel
来源:互联网 发布:程序员 杂志 编辑:程序博客网 时间:2024/05/21 10:57
前段时间做poi导入写个文档方便记忆
pom jar
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!-- 解析excel -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.3.0</version>
</dependency>
/**
*
*/
package com.sound.ezaisheng.cms.utils.importexcel;
/**
* @author langjf
* @created 2014-5-21
*/
public class Common {
public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
public static final String EMPTY = "";
public static final String POINT = ".";
public static final String LIB_PATH = "lib";
public static final String STUDENT_INFO_XLS_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2003_POSTFIX;
public static final String STUDENT_INFO_XLSX_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2010_POSTFIX;
public static final String NOT_EXCEL_FILE = " : Not the Excel file!";
public static final String PROCESSING = "Processing...";
}
/**
*
*/
package com.sound.ezaisheng.cms.utils.importexcel;
import java.math.BigDecimal;
import java.util.Date;
/**
* exponent
*
* @author langjf
* @created 2014-5-18
*/
public class ExponentBean {
/**
* 种类
*/
private String catname;
/**
* 时间
*/
private Date expdate;
/**
* 区域
*/
private String areaname;
/**
* 指数
*/
private BigDecimal exponent;
public String getCatname() {
return catname;
}
public void setCatname(String catname) {
this.catname = catname;
}
public Date getExpdate() {
return expdate;
}
public void setExpdate(Date expdate) {
this.expdate = expdate;
}
public String getAreaname() {
return areaname;
}
public void setAreaname(String areaname) {
this.areaname = areaname;
}
public BigDecimal getExponent() {
return exponent;
}
public void setExponent(BigDecimal exponent) {
this.exponent = exponent;
}
}
/**
*
*/
package com.sound.ezaisheng.cms.utils.importexcel;
import java.math.BigDecimal;
import java.util.Date;
/**
* exponent
*
* @author langjf
* @created 2014-5-18
*/
public class ExponentreBean {
/**
* 种类
*/
private String catname;
/**
* 时间
*/
private Date expdate;
/**
* 指数
*/
private BigDecimal exponent;
public String getCatname() {
return catname;
}
public void setCatname(String catname) {
this.catname = catname;
}
public Date getExpdate() {
return expdate;
}
public void setExpdate(Date expdate) {
this.expdate = expdate;
}
public BigDecimal getExponent() {
return exponent;
}
public void setExponent(BigDecimal exponent) {
this.exponent = exponent;
}
}
/**
*
*/
package com.sound.ezaisheng.cms.utils.importexcel;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* @author langjf
* @created 2014-5-20
*/
public class ReadExcel {
private static Logger log= Logger.getLogger(ReadExcel.class);
/**
* read the Excel file
* @param path the path of the Excel file
* @return
* @throws IOException
*/
public List<ExponentBean> readExcel(String path) throws IOException {
if (path == null || Common.EMPTY.equals(path)) {
return null;
} else {
String postfix = Util.getPostfix(path);
if (!Common.EMPTY.equals(postfix)) {
if (Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
return readXls(path);
} else if (Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
return readXlsx(path);
}
} else {
log.info(path + Common.NOT_EXCEL_FILE);
}
}
return null;
}
/**
* Read the Excel 2010
* @param path the path of the excel file
* @return
* @throws IOException
*/
public List<ExponentBean> readXlsx(String path) throws IOException {
log.info("大数据-指数添加—读取文件信息:"+Common.PROCESSING + path);
InputStream is = new FileInputStream(path);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
ExponentBean exponentBean = null;
List<ExponentBean> list = new ArrayList<ExponentBean>();
// Read the Sheet
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
// Read the Row
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow != null) {
exponentBean = new ExponentBean();
XSSFCell catname = xssfRow.getCell(0);
if(catname==null){
return list;
}
Date expdate = xssfRow.getCell(1).getDateCellValue();
XSSFCell areaname = xssfRow.getCell(2);
XSSFCell export = xssfRow.getCell(3);
if(catname==null||expdate==null||areaname==null||export==null){
return list;
}
exponentBean.setCatname(getValue(catname));
exponentBean.setExpdate(expdate);
exponentBean.setAreaname(getValue(areaname));
exponentBean.setExponent(BigDecimal.valueOf(Double.valueOf(getValue(export))));
list.add(exponentBean);
}
}
}
return list;
}
/**
* Read the Excel 2003-2007
* @param path the path of the Excel
* @return
* @throws IOException
*/
public List<ExponentBean> readXls(String path) throws IOException {
log.info("大数据-指数添加—读取文件信息:"+Common.PROCESSING + path);
InputStream is = new FileInputStream(path);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
ExponentBean exponentBean = null;
List<ExponentBean> list = new ArrayList<ExponentBean>();
// Read the Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// Read the Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
exponentBean = new ExponentBean();
HSSFCell catname = hssfRow.getCell(0);
if(catname==null){
return list;
}
Date expdate = hssfRow.getCell(1).getDateCellValue();
HSSFCell areaname = hssfRow.getCell(2);
HSSFCell export = hssfRow.getCell(3);
if(catname==null||expdate==null||areaname==null||export==null){
return list;
}
exponentBean.setCatname(getValue(catname));
exponentBean.setExpdate(expdate);
exponentBean.setAreaname(getValue(areaname));
exponentBean.setExponent(BigDecimal.valueOf(Double.valueOf(getValue(export))));
list.add(exponentBean);
}
}
}
return list;
}
@SuppressWarnings("static-access")
private String getValue(XSSFCell xssfRow) {
if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfRow.getBooleanCellValue());
} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
return String.valueOf(xssfRow.getNumericCellValue());
} else {
return String.valueOf(xssfRow.getStringCellValue());
}
}
@SuppressWarnings("static-access")
private String getValue(HSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(hssfCell.getNumericCellValue());
} else {
return String.valueOf(hssfCell.getStringCellValue());
}
}
public static void main(String[] args) {
ReadExcel read=new ReadExcel();
try {
read.readExcel("C:\\Users\\LENOVO\\Desktop/AAA.xlsx");
// read.readExcel("C:\\Users\\LENOVO\\Desktop/BBB.xls");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
*
*/
package com.sound.ezaisheng.cms.utils.importexcel;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* @author langjf
* @created 2014-5-20
*/
public class ReadExceler {
private static Logger log= Logger.getLogger(ReadExcel.class);
/**
* read the Excel file
* @param path the path of the Excel file
* @return
* @throws IOException
*/
public List<ExponentreBean> readExcel(String path) throws IOException {
if (path == null || Common.EMPTY.equals(path)) {
return null;
} else {
String postfix = Util.getPostfix(path);
if (!Common.EMPTY.equals(postfix)) {
if (Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
return readXls(path);
} else if (Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
return readXlsx(path);
}
} else {
log.info(path + Common.NOT_EXCEL_FILE);
}
}
return null;
}
/**
* Read the Excel 2010
* @param path the path of the excel file
* @return
* @throws IOException
*/
public List<ExponentreBean> readXlsx(String path) throws IOException {
log.info("大数据-指数添加—读取文件信息:"+Common.PROCESSING + path);
InputStream is = new FileInputStream(path);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
ExponentreBean exponentreBean = null;
List<ExponentreBean> list = new ArrayList<ExponentreBean>();
// Read the Sheet
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
// Read the Row
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow != null) {
exponentreBean = new ExponentreBean();
XSSFCell catname = xssfRow.getCell(0);
Date expdate = xssfRow.getCell(1).getDateCellValue();
XSSFCell export = xssfRow.getCell(2);
exponentreBean.setCatname(getValue(catname));
exponentreBean.setExpdate(expdate);
exponentreBean.setExponent(BigDecimal.valueOf(Double.valueOf(getValue(export))));
list.add(exponentreBean);
}
}
}
return list;
}
/**
* Read the Excel 2003-2007
* @param path the path of the Excel
* @return
* @throws IOException
*/
public List<ExponentreBean> readXls(String path) throws IOException {
log.info("大数据-指数添加—读取文件信息:"+Common.PROCESSING + path);
InputStream is = new FileInputStream(path);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
ExponentreBean exponentreBean = null;
List<ExponentreBean> list = new ArrayList<ExponentreBean>();
// Read the Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// Read the Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
exponentreBean = new ExponentreBean();
HSSFCell catname = hssfRow.getCell(0);
Date expdate = hssfRow.getCell(1).getDateCellValue();
HSSFCell export = hssfRow.getCell(2);
exponentreBean.setCatname(getValue(catname));
exponentreBean.setExpdate(expdate);
exponentreBean.setExponent(BigDecimal.valueOf(Double.valueOf(getValue(export))));
list.add(exponentreBean);
}
}
}
return list;
}
@SuppressWarnings("static-access")
private String getValue(XSSFCell xssfRow) {
if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfRow.getBooleanCellValue());
} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
return String.valueOf(xssfRow.getNumericCellValue());
} else {
return String.valueOf(xssfRow.getStringCellValue());
}
}
@SuppressWarnings("static-access")
private String getValue(HSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(hssfCell.getNumericCellValue());
} else {
return String.valueOf(hssfCell.getStringCellValue());
}
}
public static void main(String[] args) {
ReadExcel read=new ReadExcel();
try {
read.readExcel("C:\\Users\\LENOVO\\Desktop/AAA.xlsx");
// read.readExcel("C:\\Users\\LENOVO\\Desktop/BBB.xls");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
*
*/
package com.sound.ezaisheng.cms.utils.importexcel;
import com.sound.ezaisheng.cms.utils.importexcel.Common;
/**
* @author langjf
* @created 2014-5-21
*/
public class Util {
/**
* get postfix of the path
* @param path
* @return
*/
public static String getPostfix(String path) {
if (path == null || Common.EMPTY.equals(path.trim())) {
return Common.EMPTY;
}
if (path.contains(Common.POINT)) {
return path.substring(path.lastIndexOf(Common.POINT) + 1, path.length());
}
return Common.EMPTY;
}
}
- POI:上传EXCEL解析
- poi上传excel
- SpringMvc+POI上传Excel
- poi上传excel
- poi上传excel
- struts+poi+jstl上传Excel
- java使用POI上传Excel
- 利用apache的poi上传excel
- POI java 处理excel上传图片
- java poi上传excel保存数据库
- birt与poi上传excel冲突
- poi生成excel上传到FTP
- EXCEL文件上传,内容格式验证(POI)
- 关于springMVC+poi上传Excel文件问题解决
- POI实现Excel上传及下载
- poi 上传Excel(2003|2007)到服务器导入数据库
- java 利用POI 上传解析导出Excel 深入
- ocupload、struts2实现excel文件上传,poi解析
- vue.js基础
- SQL 05
- MVC和MVP和MVVM的区别
- selenium iframe元素定位
- prompt set feedback set define的详解
- poi上传excel
- springMvc前后端传值
- 安装itk
- 【POJ】3273
- asm创建别名文件表空间,表空间删除后,数据文件需要手工删除测试
- rn笔记:关于navigator的初步理解
- android反编译错误代码 修正原则
- Eclipse使用Maven maven-artchetype-webapp创建Web2.5的Web项目
- Handler.postDelayed(new Runnable)是否运行在主线程