excel操作工具(03,07,10)
来源:互联网 发布:长相忆五色石南叶 知乎 编辑:程序博客网 时间:2024/06/07 00:42
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
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;
import com.water.constant.Common;
import com.water.model.WaterInfo;
/**
* Excel 解析的工具类 支持03,07 10版
*
* @author guohaipeng
* @created 2015-5-15
*/
public class ExcelUtil {
/**
* read the Excel file
* @param path the path of the Excel file
* @return
* @throws IOException
*/
public static List<WaterInfo> readExcel(String path) throws IOException {
if (path == null || Common.EMPTY.equals(path)) {
return null;
} else {
String postfix = ExcelUtil.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 {
System.out.println(path + Common.NOT_EXCEL_FILE);
}
}
return null;
}
/**
* 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;
}
/**
* Read the Excel 2010
* @param path the path of the excel file
* @return
* @throws IOException
*/
public static List<WaterInfo> readXlsx(String path) throws IOException {
System.out.println(Common.PROCESSING + path);
InputStream is = new FileInputStream(path);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
WaterInfo water = null;
List<WaterInfo> list = new ArrayList<WaterInfo>();
// 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) {
water = new WaterInfo();
water.setWaterPH(Double.parseDouble(null==getValue(xssfRow.getCell(0))?"0":getValue(xssfRow.getCell(0))));
water.setWaterTurbidity(Double.parseDouble(null==getValue(xssfRow.getCell(1))?"0":getValue(xssfRow.getCell(1))));
water.setWaterTemperature(Double.parseDouble(null==getValue(xssfRow.getCell(2))?"0":getValue(xssfRow.getCell(2))));
water.setWaterCod(Double.parseDouble(null==getValue(xssfRow.getCell(3))?"0":getValue(xssfRow.getCell(3))));
water.setWaterYield(Double.parseDouble(null==getValue(xssfRow.getCell(4))?"0":getValue(xssfRow.getCell(4))));
water.setWaterResidualChlorine(Double.parseDouble(null==getValue(xssfRow.getCell(5))?"0":getValue(xssfRow.getCell(5))));
list.add(water);
}
}
}
return list;
}
/**
* Read the Excel 2003-2007
* @param path the path of the Excel
* @return
* @t hrows IOException
*/
public static List<WaterInfo> readXls(String path) throws IOException {
System.out.println(Common.PROCESSING + path);
InputStream is = new FileInputStream(path);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
WaterInfo water = null;
List<WaterInfo> list = new ArrayList<WaterInfo>();
// 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) {
water = new WaterInfo();
water.setWaterPH(Double.parseDouble(null==getValue(hssfRow.getCell(0))?"0":getValue(hssfRow.getCell(0))));
water.setWaterTurbidity(Double.parseDouble(null==getValue(hssfRow.getCell(1))?"0":getValue(hssfRow.getCell(1))));
water.setWaterTemperature(Double.parseDouble(null==getValue(hssfRow.getCell(2))?"0":getValue(hssfRow.getCell(2))));
water.setWaterCod(Double.parseDouble(null==getValue(hssfRow.getCell(3))?"0":getValue(hssfRow.getCell(3))));
water.setWaterYield(Double.parseDouble(null==getValue(hssfRow.getCell(4))?"0":getValue(hssfRow.getCell(4))));
water.setWaterResidualChlorine(Double.parseDouble(null==getValue(hssfRow.getCell(5))?"0":getValue(hssfRow.getCell(5))));
list.add(water);
}
}
}
return list;
}
@SuppressWarnings("static-access")
private static 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 static 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) {
try {
List<WaterInfo> list = ExcelUtil.readExcel("resource/Intake.xlsx");
for (int i = 0; i < list.size(); i++) {
System.out.println("WaterPH="+list.get(i).getWaterPH()+",waterTurbidity="+list.get(i).getWaterTurbidity()+",waterTemperature="+list.get(i).getWaterTemperature()
+",waterCod="+list.get(i).getWaterCod()+",waterYield="+list.get(i).getWaterYield()+",waterResidualChlorine="+list.get(i).getWaterResidualChlorine());
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
package com.water.constant;
/**
* 定义常量的公用类
*
* @author guohaipeng
* @created 2015-5-15
*/
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...";
/**
* 澄清池
*/
public static final String CLARIFIER = "clarifier";
/**
* 沉淀池
*/
public static final String DESILTER = "desilter";
/**
* 取水口
*/
public static final String INTAKE = "intake";
/**
* 清水池
*/
public static final String WATERTANK = "watertank";
/**
* 取水口泵房
*/
public static final String PUMB = "pumb";
/**
* 澄清池混凝剂
*/
public static final String COAGULANT = "coagulant";
/**
* 清水池液氯
*/
public static final String CHLORINE = "chlorine";
/**
* 检测到的数据有异常
*/
public static final String WRONG = "wrong";
/**
* 检测到的数据正常
*/
public static final String RIGHT = "right";
}
- excel操作工具(03,07,10)
- excel操作工具
- Excel操作工具类ExcelHelper
- java操作Excel工具类
- poi操作excel工具类
- java操作excel的工具
- 【工具类】常用操作Excel
- 操作Excel工具类(基于Apache的POI类库)
- apach poi 操作excel 工具类似
- POI操作excel示例工具类
- Excel 操作工具 For .Net Framework 4.0
- java操作excel的工具jxl
- java操作Excel工具类简易版
- Java script操作Excel报表工具汇总
- JAVA通过JXL工具操作EXCEL文件
- 封装poi操作Excel工具类
- POI 操作Excel支持03/07
- JavaWEB--POI之EXCEL操作、优化、封装详解系列(三)--万能POI之EXCEL导出工具--PoiExportUtil入门篇
- Canvas基本操作
- 利用excel批量修改字段长度
- 调整数组顺序使奇数位于偶数前面
- Lua 与C/C++ 交互系列:利用模板技术在Lua Code中注册C++类
- 对智能指针auto_ptr 的学习
- excel操作工具(03,07,10)
- OJ-类模版
- Server at localhost was unable to start within 45 seconds
- 浏览器的工作原理-介绍篇
- 链表中的倒数第k个结点
- opengl之vsh、fsh简易介绍+cocos2dx 3.0 shader 变灰
- 同一端口如何区分不同的Socket
- 李括号定义解说
- HDU-2871 Memory Control(线段树)