坐标:
<!-- poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
工具类:
Poi(表格读取)
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.LinkedList;
public class Poi {
private Sheet sheet; //表格类实例
@SuppressWarnings("rawtypes")
LinkedList[] result; //保存每个单元格的数据 ,使用的是一种链表数组的结构
@SuppressWarnings("unused")
private static HSSFWorkbook wb;
@SuppressWarnings("unused")
private static HSSFRow row;
@SuppressWarnings("unused")
private static String[] excleTitle; //设置表格表头
//读取excel文件,创建表格实例
public void loadExcel(String filePath) {
FileInputStream inStream = null;
try {
inStream = new FileInputStream(new File(filePath));
Workbook workBook = WorkbookFactory.create(inStream);
sheet = (Sheet) workBook.getSheetAt(0);
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if(inStream!=null){
inStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
//获取单元格的值
@SuppressWarnings("deprecation")
private String getCellValue(Cell cell) {
String cellValue = "";
DataFormatter formatter = new DataFormatter();
if (cell != null) {
//判断单元格数据的类型,不同类型调用不同的方法
switch (cell.getCellType()) {
//数值类型
case Cell.CELL_TYPE_NUMERIC:
//进一步判断 ,单元格格式是日期格式
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = formatter.formatCellValue(cell);
// new SimpleDateFormat("yyyy/MM/dd").format(cell);
} else {
//数值
double value = cell.getNumericCellValue();
int intValue = (int) value;
cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
}
break;
case Cell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
//判断单元格是公式格式,需要做一种特殊处理来得到相应的值
case Cell.CELL_TYPE_FORMULA:{
try{
cellValue = String.valueOf(cell.getNumericCellValue());
}catch(IllegalStateException e){
cellValue = String.valueOf(cell.getRichStringCellValue());
}
}
break;
case Cell.CELL_TYPE_BLANK:
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR:
cellValue = "";
break;
default:
cellValue = cell.toString().trim();
break;
}
}
return cellValue.trim();
}
//初始化表格中的每一行,并得到每一个单元格的值
@SuppressWarnings({ "rawtypes", "unchecked" })
public LinkedList[] init(){
int rowNum = ((org.apache.poi.ss.usermodel.Sheet) sheet).getLastRowNum() + 1;//getLastRowNum()最后一行行标,比行数小1
result = new LinkedList[rowNum];
for(int i=0;i<rowNum;i++){
Row row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(i);//获取行
//每有新的一行,创建一个新的LinkedList对象
result[i] = new LinkedList();
for(int j=0;j<row.getLastCellNum();j++){ //getLastCellNum()获取列数,比最后一列列标大1
Cell cell = row.getCell(j);//getCell(j)获取列
//获取单元格的值
String str = getCellValue(cell);
//将得到的值放入链表中
result[i].add(str);
}
}
return result;
}
}
PoiReaderWrite(表格创建及写操作)
import com.tmao.common.base.BaseResponse;
import com.tmao.common.base.BaseResponseEnum.Success;
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.Row;
import org.springframework.stereotype.Component;
import java.io.*;
import java.lang.reflect.Method;
@Component
public class PoiReaderWrite {
private HSSFWorkbook workbook = null;
/**
* 判断文件是否存在
* @param filePath 文件路径
* @return
*/
public boolean fileExist(String filePath){
boolean flag = false;
File file = new File(filePath);
flag = file.exists();
return flag;
}
/**
* 判断文件的sheet是否存在
* @param filePath 文件路径
* @param sheetName 表格索引名
* @return
*/
public boolean sheetExist(String filePath,String sheetName){
boolean flag = false;
File file = new File(filePath);
if(file.exists()){ //文件存在
//创建workbook
try {
workbook = new HSSFWorkbook(new FileInputStream(file));
//添加Worksheet(不添加sheet时生成的xls文件打开时会报错)
HSSFSheet sheet = workbook.getSheet(sheetName);
if(sheet!=null)
flag = true;
} catch (Exception e) {
e.printStackTrace();
}
}else{ //文件不存在
flag = false;
}
return flag;
}
/**
* 创建新Sheet并写入第一行数据
* @param filePath excel的路径
* @param sheetName 要创建的表格索引
* @param titleRow excel的第一行即表格头
* @throws IOException
* @throws FileNotFoundException
*/
public void createSheet(String filePath,String sheetName,String titleRow[]) throws FileNotFoundException, IOException{
FileOutputStream out = null;
File excel = new File(filePath); // 读取文件
FileInputStream in = new FileInputStream(excel); // 转换为流
workbook = new HSSFWorkbook(in); // 加载excel的 工作目录
workbook.createSheet(sheetName); // 添加一个新的sheet
//添加表头
Row row = workbook.getSheet(sheetName).createRow(0); //创建第一行
try {
for(int i = 0;i < titleRow.length;i++){
Cell cell = row.createCell(i);
cell.setCellValue(titleRow[i]);
}
out = new FileOutputStream(filePath);
workbook.write(out);
}catch (Exception e) {
e.printStackTrace();
}finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 创建新excel.
* @param filePath excel的路径
* @param sheetName 要创建的表格索引
* @param titleRow excel的第一行即表格头
*/
public void createExcel(String filePath,String sheetName,String titleRow[]){
//创建workbook
workbook = new HSSFWorkbook();
//添加Worksheet(不添加sheet时生成的xls文件打开时会报错)
workbook.createSheet(sheetName);
//新建文件
FileOutputStream out = null;
try {
//添加表头
Row row = workbook.getSheet(sheetName).createRow(0); //创建第一行
for(int i = 0;i < titleRow.length;i++){
Cell cell = row.createCell(i);
cell.setCellValue(titleRow[i]);
}
out = new FileOutputStream(filePath);
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 删除文件.
* @param filePath 文件路径
*/
public boolean deleteExcel(String filePath){
boolean flag = false;
File file = new File(filePath);
// 判断目录或文件是否存在
if (!file.exists()) {
return flag;
} else {
// 判断是否为文件
if (file.isFile()) { // 为文件时调用删除文件方法
file.delete();
flag = true;
}
}
return flag;
}
/**
* 往excel中写入.
* @param filePath 文件路径
* @param sheetName 表格索引
* @param object
*/
public BaseResponse writeToExcel(String filePath, String sheetName, Object object, String titleRow[]){
BaseResponse baseResponse = new BaseResponse();
String flag = Success.TRUE.name();
//创建workbook
File file = new File(filePath);
try {
workbook = new HSSFWorkbook(new FileInputStream(file));
} catch (FileNotFoundException e) {
flag = Success.FALSE.name();
baseResponse.setObject(e);
e.printStackTrace();
} catch (IOException e) {
flag = Success.FALSE.name();
baseResponse.setObject(e);
e.printStackTrace();
}
FileOutputStream out = null;
HSSFSheet sheet = workbook.getSheet(sheetName);
// 获取表格的总行数
int rowCount = sheet.getLastRowNum() + 1; // 需要加一
try {
Row row = sheet.createRow(rowCount); //最新要添加的一行
//通过反射获得object的字段,对应表头插入
// 获取该对象的class对象
Class<? extends Object> class_ = object.getClass();
for(int i = 0;i < titleRow.length;i++){
String title = titleRow[i];
String UTitle = Character.toUpperCase(title.charAt(0))+ title.substring(1, title.length()); // 使其首字母大写;
String methodName = "get" + UTitle;
Method method = class_.getDeclaredMethod(methodName); // 设置要执行的方法
String data = "";
try {
data = method.invoke(object).toString(); // 执行该get方法,即要插入的数据
}catch (Exception e){ //忽略空值
}
Cell cell = row.createCell(i);
cell.setCellValue(data);
}
out = new FileOutputStream(filePath);
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
flag = Success.FALSE.name();
baseResponse.setObject(e);
}
finally {
try {
out.close();
} catch (IOException e) {
flag = Success.FALSE.name();
baseResponse.setObject(e);
e.printStackTrace();
}
}
baseResponse.setSuccess(flag);
baseResponse.setMessage(filePath);
return baseResponse;
}
}
public class BaseResponseEnum {
public static enum Success {
TRUE("TRUE"), FALSE("FALSE");
private final String text;
private Success(final String text) {
this.text = text;
}
@Override
public String toString() {
return text;
}
}
}
public class BaseResponse {
private String success;
private String message;
private Object object;
/**
* @return the success
*/
public String getSuccess() {
return success;
}
/**
* @param success the success to set
*/
public void setSuccess(String success) {
this.success = success;
}
/**
* @return the message
*/
public String getMessage() {
return message;
}
/**
* @param message the message to set
*/
public void setMessage(String message) {
this.message = message;
}
/**
* @return the object
*/
public Object getObject() {
return object;
}
/**
* @param object the object to set
*/
public void setObject(Object object) {
this.object = object;
}
@Override
public String toString() {
return "BaseResponse [success=" + success + ", message=" + message + ", object=" + object + "]";
}
}
public class BaseEntity implements Serializable
{
private static final long serialVersionUID = 1L;
/** 搜索值 */
private String searchValue;
/** 创建者 */
private String createBy;
/** 创建时间 */
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date createTime;
/** 更新者 */
private String updateBy;
/** 更新时间 */
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date updateTime;
/** 备注 */
private String remark;
/** 请求参数 */
private Map<String, Object> params;
public String getSearchValue()
{
return searchValue;
}
public void setSearchValue(String searchValue)
{
this.searchValue = searchValue;
}
public String getCreateBy()
{
return createBy;
}
public void setCreateBy(String createBy)
{
this.createBy = createBy;
}
public Date getCreateTime()
{
return createTime;
}
public void setCreateTime(Date createTime)
{
this.createTime = createTime;
}
public String getUpdateBy()
{
return updateBy;
}
public void setUpdateBy(String updateBy)
{
this.updateBy = updateBy;
}
public Date getUpdateTime()
{
return updateTime;
}
public void setUpdateTime(Date updateTime)
{
this.updateTime = updateTime;
}
public String getRemark()
{
return remark;
}
public void setRemark(String remark)
{
this.remark = remark;
}
public Map<String, Object> getParams()
{
if (params == null)
{
params = new HashMap<>();
}
return params;
}
public void setParams(Map<String, Object> params)
{
this.params = params;
}
}