package com.kedacom.pms.team.util;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
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;
/**
* Excel操作工具类
*
* @author HuangHua
* @Date 2018-4-3下午3:34:26
*/
public class ExcelUtil {
/**
* 读取Excel文件
* @author HuangHua
* @param file Excel文件
* @return
* @throws Exception
*/
public static List<List<String>> readExcel(File file) throws Exception{
String fileName = file.getName();
String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
if("xlsx".equals(suffix)){
return readXlsx(file);
} else if ("xls".equals(suffix)) {
return readXls(file);
} else {
throw new Exception("读取的不是Excel文件");
}
}
/**
* Read the Excel 2010
* @author HuangHua
* @param file Excel文件
* @return
* @throws Exception
*/
public static List<List<String>> readXlsx(File file) throws Exception{
List<List<String>> resultList = new ArrayList<List<String>>();
XSSFWorkbook workboot = new XSSFWorkbook(new FileInputStream(file));
//获取第一页
XSSFSheet sheet = workboot.getSheetAt(0);
for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
//获取行数据
XSSFRow row = sheet.getRow(rowNum);
if (row == null) continue;
List<String> colList = new ArrayList<String>();
for (int colNum = 0; colNum < sheet.getRow(1).getPhysicalNumberOfCells(); colNum++) {
//获取单元格数据
XSSFCell cell = row.getCell(colNum);
if(cell != null){
colList.add(getXlsxValue(cell));
} else {
colList.add("");
}
}
resultList.add(colList);
}
return resultList;
}
/**
* Read the Excel 2003-2007
* @author HuangHua
* @param file Excel文件
* @return
* @throws Exception
*/
public static List<List<String>> readXls(File file) throws Exception{
List<List<String>> resultList = new ArrayList<List<String>>();
HSSFWorkbook workboot = new HSSFWorkbook(new FileInputStream(file));
//获取第一页
HSSFSheet sheet = workboot.getSheetAt(0);
for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
//获取行数据
HSSFRow row = sheet.getRow(rowNum);
if (row == null) continue;
List<String> colList = new ArrayList<String>();
for (int colNum = 0; colNum < sheet.getRow(1).getPhysicalNumberOfCells(); colNum++) {
//获取单元格数据
HSSFCell cell = row.getCell(colNum);
if(cell != null){
colList.add(getXlsValue(cell));
} else {
colList.add("");
}
}
resultList.add(colList);
}
return resultList;
}
/**
* 获取Excel 2007及以后版本的单元格数据
* @author HuangHua
* @param cell
* @return
*/
public static String getXlsxValue(XSSFCell 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 if(cell.getCellType() == Cell.CELL_TYPE_BLANK || cell.getCellType() == Cell.CELL_TYPE_ERROR){
return "";
} else {
return String.valueOf(cell.getStringCellValue());
}
}
/**
* 获取Excel 2003-2007版本的单元格数据
* @author HuangHua
* @param cell
* @return
*/
public static String getXlsValue(HSSFCell 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 if(cell.getCellType() == Cell.CELL_TYPE_BLANK || cell.getCellType() == Cell.CELL_TYPE_ERROR){
return "";
} else {
return String.valueOf(cell.getStringCellValue());
}
}
/**
* 导出Excel
* @author HuangHua
* @param sheetName sheet名称
* @param titles 标题
* @param valueList 内容
* @param wb HSSFWorkbook对象
* @return
*/
public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] titles, List<List<String>> valueList, HSSFWorkbook wb){
// 第一步,创建一个HSSFWorkbook,对应一个Excel文件
if(wb == null){
wb = new HSSFWorkbook();
}
// 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet并设置列的宽度
HSSFSheet sheet = wb.createSheet(sheetName);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
HSSFRow row = sheet.createRow(0);
// 第四步,并设置单元格样式
HSSFCellStyle titleStyle =setTitleCellStyle(wb);
HSSFCellStyle valueStyle =setValueCellStyle(wb);
//声明列对象
HSSFCell cell = null;
for(int i=0; i<titles.length; i++){
sheet.setColumnWidth(i,titles[i].toString().length() * 1024);
cell = row.createCell(i);
cell.setCellValue(titles[i]);
cell.setCellStyle(titleStyle);
}
//创建内容
for (int i = 0; i < valueList.size(); i++) {
row = sheet.createRow(i+1);
for (int j = 0; j < valueList.get(i).size(); j++) {
//将内容按顺序赋值给列对象
HSSFCell hssfCell = row.createCell(j);
hssfCell.setCellValue(valueList.get(i).get(j));
hssfCell.setCellStyle(valueStyle);
}
}
return wb;
}
/**
* 设置Excel头部Title单元格样式
* @author HuangHua
* @param wb
* @return
*/
public static HSSFCellStyle setTitleCellStyle(HSSFWorkbook wb){
HSSFCellStyle style = wb.createCellStyle();
// 设置对齐方式
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
//设置边框
style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);//下边框
style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);//左边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
//设置自动换行
style.setWrapText(true);
//设置字体
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short)14);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(font);
return style;
}
/**
* 设置Excel内容单元格样式
* @author HuangHua
* @param wb
* @return
*/
public static HSSFCellStyle setValueCellStyle(HSSFWorkbook wb){
HSSFCellStyle style = wb.createCellStyle();
// 设置对齐方式
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
//设置边框
style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);//下边框
style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);//左边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
//设置自动换行
style.setWrapText(true);
//设置字体
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short)10.5);
style.setFont(font);
//设置单元格格式
HSSFDataFormat format = wb.createDataFormat();
style.setDataFormat(format.getFormat("@"));
return style;
}
}