packagecom.alphajuns.ssm.util;/*** ExcelPublic.java*/
importorg.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;importjava.io.File;importjava.io.FileInputStream;importjava.io.IOException;importjava.text.DateFormat;importjava.text.DecimalFormat;importjava.text.SimpleDateFormat;import java.util.*;/***
* ClassName: excelPublic
*
*
* Description: 负责excel的公共类
*
*
* Author: Administrator
*
*
* Date: 2015-6-28
*
*/public classExcelPublic {/***
* Description: 一个提取excel文件内cell内容的方法
*
* .
*
*@paramcell
* 一个单元格内的内容
*@return把cell转换成string*/@SuppressWarnings("deprecation")public staticString getCellValue(Cell cell) {
String cellStr= null;
DecimalFormat DF= new DecimalFormat("0");if (cell == null) {
cellStr= "";
}else{switch(cell.getCellType()) {//case Cell.CELL_TYPE_BOOLEAN:
caseBOOLEAN:
cellStr=String.valueOf(cell.getBooleanCellValue());break;caseNUMERIC:if (DateUtil.isCellDateFormatted(cell)) { //日期
final DateFormat FORMATER = new SimpleDateFormat("dd.MM.yyyy");
cellStr=FORMATER.format(cell.getDateCellValue());
}else{
String number=String.valueOf(cell.getNumericCellValue());if(number.indexOf(".") != -1){
DF= new DecimalFormat("#.###");
}
cellStr=DF.format(cell.getNumericCellValue());
}break;caseBLANK:
cellStr= "";break;caseERROR:
cellStr=String.valueOf(cell.getErrorCellValue());break;case FORMULA: //首先判断一下是不是公式
cell.setCellType(CellType.NUMERIC); //设置其单元格类型为数字
try{
cellStr= DF.format(cell.getNumericCellValue()); //获取数字值
} catch(Exception e) {throw new RuntimeException("行:" + (cell.getRowIndex() + 1) + ",列:" + (cell.getColumnIndex() + 1) + " 获取公式值失败," +e.toString());
}break;default:
cellStr=cell.getStringCellValue();break;
}
}returncellStr;
}/***
* Description: 一个检查excel的第一行是否有值的方法
*
*
*@parampath
* path
*@returnboolean
*@throwsIOException
* IO异常*/
public static boolean checkArray(String path) throwsIOException {final FileInputStream FS = newFileInputStream(path);final String TYPE = path.substring(path.lastIndexOf(".") + 1).toLowerCase();
Workbook wb;if ("xlsx".equals(TYPE)) {
wb= newXSSFWorkbook(FS);
}else{
wb= newHSSFWorkbook(FS);
}
Sheet sheet= wb.getSheetAt(0);boolean flag = sheet.getRow(0) != null ? true : false;//添加一个检查表格第一行是否为正常数据的方法 gary 2016.9.19
int cellNum = 0;if (sheet.getRow(0) != null) { //检查第一行是不是为空
cellNum = sheet.getRow(0).getPhysicalNumberOfCells();
}else{
cellNum= 0;
}
Row row= null;
Cell cell= null;final String[] STR = newString[cellNum];if(flag) {
row= sheet.getRow(0);for (int j = 1; j <= cellNum; j++) {
cell= row.getCell(j - 1);if (cell == null) {
STR[j- 1] = "";
}else{
STR[j- 1] =getCellValue(cell);
}
}
}
sheet= null;
wb.close();
wb= null;returnflag;
}public static String[][] dyadicArray(String path, int startNum, int endNum) throwsIOException {
String[][] str= null;
FileInputStream fs= newFileInputStream(path);
String type= path.substring(path.lastIndexOf(".") + 1).toLowerCase();
Workbook wb;if ("xlsx".equals(type)) {
wb= newXSSFWorkbook(fs);
}else{
wb= newHSSFWorkbook(fs);
}try{
Sheet sheet= wb.getSheetAt(0);final int A = sheet.getPhysicalNumberOfRows(); //行数
final intB;
B= sheet.getRow(0).getPhysicalNumberOfCells(); //列数
if (endNum == 0 && startNum == 1) {
endNum=A;
}else if (startNum == 0) {
startNum= 1;
}int rowNumber = endNum - startNum + 1;
str= newString[rowNumber][B];
Row row= null;
Cell cell= null;for (int i = 1; i <= rowNumber; i++) {
row= sheet.getRow(startNum - 1);for (int j = 1; j <= B; j++) {
cell= row.getCell(j - 1);if (cell == null) {
str[i- 1][j - 1] = "";
}else{
str[i- 1][j - 1] =getCellValue(cell);
}
cell= null;
}
startNum++;
row= null;
}
sheet= null;
}catch(Exception e) {throw newIOException(e);
}finally{
wb.close();
wb= null;
fs= null;
}returnstr;
}/***
* Description: 得到excel行数的方法
*
*
*@parampath
* path
*@returnint
*@throwsIOException*/
public static int getRowNumber(String path) throwsIOException {final FileInputStream FS = newFileInputStream(path);final String TYPE = path.substring(path.lastIndexOf(".") + 1).toLowerCase();
Workbook wb;if ("xlsx".equals(TYPE)) {
wb= newXSSFWorkbook(FS);
}else{
wb= newHSSFWorkbook(FS);
}final Sheet SHEET = wb.getSheetAt(0);int firstRowNumber =SHEET.getFirstRowNum();int lastRowNumber =SHEET.getLastRowNum();int rowNumber = lastRowNumber - firstRowNumber + 1; //行数
wb.close();returnrowNumber;
}public static ArrayList readExcelFirstline(String path) throwsIOException {final ArrayList FIRSTLINE = new ArrayList();if(checkArray(path)) {
String[][] article= dyadicArray(path, 1, 1);final int LENGTH = article[0].length;for (int i = 0; i < LENGTH; i++) {
FIRSTLINE.add(article[0][i]);
}
article= null;
}returnFIRSTLINE;
}/***
* Description: 将二维数组的每一行转成ArrayList
*
* .
*
*@parampath
* 带文件名的路径
*@paramdyadic
* 二维数组，也就是给定的原文，目前已经指定了行数范围
*@return返回该行
*@throwsIOException
* Signals that an I/O exception has occurred.*/
public static HashMap> readExcelLine(String path, String[][] dyadic) throwsIOException {final HashMap> READEXCEL = new HashMap>();final int LENGTH =dyadic.length;final int WIDTH = dyadic[0].length;
String[][] article= null;
article=dyadic;for (int i = 0; i < LENGTH; i++) {final ArrayList ROW = new ArrayList();for (int j = 0; j < WIDTH; j++) {
ROW.add(article[i][j]);
}
READEXCEL.put(i, ROW);
}returnREADEXCEL;
}public static Integer findTheWord(String path, String word) throwsIOException {int wordLocation = 0;
ArrayList arr = null;
arr=readExcelFirstline(path);if(arr.contains(word)) {
wordLocation=arr.indexOf(word);
}else{
wordLocation= -1;
}returnwordLocation;
}///**//*
//* Description: 根据不同文本来适配不同字符编码，针对txt文本使用//*
.//*//* @param fileName 带文件名的路径//* @return 文件编码//* @throws IOException Signals that an I/O exception has occurred.//*///public static String getCharset(String fileName) throws IOException {//final BufferedInputStream BIN = new BufferedInputStream(new//FileInputStream(fileName));//final int NUMBER = 8;//final int P = (BIN.read() << NUMBER) + BIN.read();//final int UTF8 = 0xefbb;//final int UNICODE = 0xfffe;//final int UTF16BE = 0xfeff;//String code = null;//switch (P) {//case UTF8://code = "UTF-8";//break;//case UNICODE://code = "Unicode";//break;//case UTF16BE://code = "UTF-16BE";//break;//default://code = "GBK";//}//BIN.close();//return code;//}/***
* Description: 获取当前时间戳，格式到分
*
* .
*
*@return当前时间*/
public staticString getTime() {final Date DATE = new Date(); //创建一个时间对象，获取到当前的时间
final SimpleDateFormat SDF = new SimpleDateFormat("yyyyMMddHHmm"); //设置时间显示格式
final String STR = SDF.format(DATE); //将当前时间格式化为String
return STR; //输出结果
}/***
* Description: 在传入为list时的插入方法
*
* .
*
*@paramoutList
* 输出的字段
*@paraminList
* 输入的字段
*@paramvalue
* 值
*@returnoutlist*/
public static List addValueMethod(List outList, ListinList, Integer value) {
String word= null;if (value == -1) {
outList.add("");
}else if (value >inList.size()) {
outList.add("");
}else if (inList.get(value) != null) {if (inList.get(value).trim().length() > 250) {
outList.add("");
}else{if (inList.get(value).trim().contains("\'")) {
word= inList.get(value).trim().replace("\'", "’");
}else if (inList.get(value).trim().contains("\"")) {
word= inList.get(value).trim().replace("\"", "”");
}else{
word=inList.get(value).trim();
}
outList.add(word);
}
}else{
outList.add("");
}returnoutList;
}/***
* Description: 在插入值时去掉“.”的方法
*
*
*@paramoutList
* 输出值
*@paraminList
* 输出值
*@paramvalue
* 位置
*@return输出结果*/
public static List addValueMethodHavePoint(List outList, ListinList, Integer value) {if (value == -1) {
outList.add("");
}else if (value >inList.size()) {
outList.add("");
}else if (inList.get(value) != null) {
String newList;
newList= inList.get(value).replace(".", "");
outList.add(newList);
}else{
outList.add("");
}returnoutList;
}/***
* Description: 生成当前时间，精确到毫秒，格式为年月日小时分钟秒毫秒，没有空格
*
* .
*
*@return时间string*/
public staticString haveCurrentTime() {final long CURRENTTIME =System.currentTimeMillis();final SimpleDateFormat FORMATTER = new SimpleDateFormat("yyyyMMddHHmmssS");final Date DATE = newDate(CURRENTTIME);final String TIME =FORMATTER.format(DATE);returnTIME;
}/***
* Description: 在需要提取文件名并将之插入时的方法
*
* .
*
*@paramoutList
* 输出数据
*@paramfilepath
* 带文件名的路径
*@return输出数据
*@throwsIOException
* Signals that an I/O exception has occurred.*/
public static List addValueFileName(List outList, String filepath) throwsIOException {final String PATH1 = filepath.substring(0, filepath.lastIndexOf("_")); //根据“_”之前的位置来获取文件名
final String PATH = PATH1.substring(PATH1.lastIndexOf(File.separator) + 1).toUpperCase();
outList.add(PATH);returnoutList;
}/*** 根据文件路径读取列所在位置
*
*@paramtempPath
* excel路径
*@paramcolumns
* 列名数组
*@returnMap key为列名，value为列在excel中的第几列
*@throwsIOException*/
public static Map readLocation(String tempPath, String... columns) throwsIOException {
Map locationMap = new HashMap();for(String column : columns) {int location =ExcelPublic.findTheWord(tempPath, column);
locationMap.put(column, location);
}returnlocationMap;
}
}
635
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
