POI通用工具

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.InputStreamSource;


import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;


public class POIUtils {

public static Workbook getWorkbook(File excelFile) throws IOException {
InputStream ins1 = new FileInputStream(excelFile);
InputStream ins2 = new FileInputStream(excelFile);
Workbook workbook = null;
try {
// 默认是xlsx格式，如果创建异常，则使用xlsx格式
workbook = new XSSFWorkbook(ins1);
} catch (Exception e) {
// 如果是xls格式
workbook = new HSSFWorkbook(ins2);
} finally {
if (ins1 != null) {
ins1.close();
}
if (ins2 != null) {
ins2.close();
}
}
return workbook;
}

public static Workbook getWorkbook(InputStreamSource inputStreamSource) throws IOException{
InputStream ins1 = inputStreamSource.getInputStream();
InputStream ins2 =  inputStreamSource.getInputStream();
Workbook workbook = null;
try {
//默认是xlsx格式，如果创建异常，则使用xls格式
workbook = new XSSFWorkbook(ins1);
} catch (Exception e) {
//如果是xls格式
workbook = new HSSFWorkbook(ins2);
} finally {
if(ins1 != null){
ins1.close();
}
if(ins2 != null){
ins2.close();
}
}
return workbook;
}


    public static Sheet getSheet(Workbook workbook, int sheetIndex) throws IOException{
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        if(sheet == null){
            sheet = workbook.createSheet();
        }
        return sheet;
    }


    public static Sheet getSheet(Workbook workbook, String sheetName) throws IOException{
        Sheet sheet = workbook.getSheet(sheetName);
        if(sheet == null){
            sheet = workbook.createSheet();
        }
        return sheet;
    }

public static Sheet getSheet(String path, int sheetIndex) throws IOException{
File excelFile = new File(path);
return getSheet(excelFile, sheetIndex);
}


public static Sheet getSheet(String path, String sheetName) throws IOException{
File excelFile = new File(path);
return getSheet(excelFile, sheetName);
}

public static Sheet getSheet(File excelFile, int sheetIndex) throws IOException{
Workbook workbook = getWorkbook(excelFile);
Sheet sheet = workbook.getSheetAt(sheetIndex);
if(sheet == null){
sheet = workbook.createSheet();
}
return sheet;
}


public static Sheet getSheet(File excelFile, String sheetName) throws IOException{
Workbook workbook = getWorkbook(excelFile);
Sheet sheet = workbook.getSheet(sheetName);
if(sheet == null){
sheet = workbook.createSheet();
}
return sheet;
}

public static Sheet getSheet(InputStreamSource inputStreamSource, int sheetIndex) throws IOException{
Workbook workbook = getWorkbook(inputStreamSource);
Sheet sheet = workbook.getSheetAt(sheetIndex);
if(sheet == null){
sheet = workbook.createSheet();
}
return sheet;
}


public static Sheet getSheet(InputStreamSource inputStreamSource, String sheetName) throws IOException{
Workbook workbook = getWorkbook(inputStreamSource);
Sheet sheet = workbook.getSheet(sheetName);
if(sheet == null){
sheet = workbook.createSheet();
}
return sheet;
}


/*public static Workbook createWorkbook(Row row, Class sheetType){
Workbook wb = null;
if(sheetType == XSSFSheet.class){
wb = new XSSFWorkbook();
}else {
wb = new HSSFWorkbook();
}
Sheet sheet = wb.createSheet();
Row title = sheet.createRow(0);
copyRow(row,title);
return wb;
}*/

public static void copyCell(Cell cell, Cell toCell){
int type = cell.getCellType();
switch (type) {
case Cell.CELL_TYPE_BLANK:
toCell.setCellValue("");
break;
case Cell.CELL_TYPE_BOOLEAN:
toCell.setCellValue(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
toCell.setCellValue(cell.getErrorCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
toCell.setCellValue(cell.getCellFormula());
break;
case Cell.CELL_TYPE_NUMERIC:
toCell.setCellValue(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
toCell.setCellValue(cell.getStringCellValue());
break;
}
}

public static Cell newNextCell(Row row, Cell cell){
int lastCellNum = row.getLastCellNum();
if(lastCellNum < 0){
lastCellNum = 0;
}
Cell addCell = getNonNullCell(row, lastCellNum); 
copyCell(cell, addCell);
return addCell;
}

public static Cell newNextCell(Row row, String cellValue){
int lastCellNum = row.getLastCellNum();
if(lastCellNum < 0){
lastCellNum = 0;
}
Cell addCell = getNonNullCell(row, lastCellNum); 
addCell.setCellValue(cellValue);
return addCell;
}

public static Cell newNextCell(Row row){
int lastCellNum = row.getLastCellNum();
if(lastCellNum < 0){
lastCellNum = 0;
}
Cell addCell = getNonNullCell(row, lastCellNum); 
return addCell;
}

public static Cell putCell(Row row, String cellValue, int columnIndex){
Cell addCell = getNonNullCell(row, columnIndex);
addCell.setCellValue(cellValue);
return addCell;
}

public static void copyRow(Row row, Row toRow){
for(int i=row.getFirstCellNum(); i<row.getLastCellNum(); i++){
Cell cell = getNonNullCell(row, i);
Cell toCell = getNonNullCell(toRow, i);
copyCell(cell, toCell);
}
}

public static Row newNextRow(Sheet sheet){
int lastRowNum = sheet.getLastRowNum();
if(sheet.getRow(lastRowNum) != null){
lastRowNum ++;
}
Row newRow = getNonNullRow(sheet, lastRowNum); 
return newRow;
}

public static Row newNextRow(Sheet sheet, Row row){
int lastRowNum = sheet.getLastRowNum();
if(sheet.getRow(lastRowNum) != null){
lastRowNum ++;
}
Row addRow = getNonNullRow(sheet, lastRowNum); 
copyRow(row, addRow);
return addRow;
}

public static Workbook createXSSFWorkbook(List<List<?>> datas){
Workbook w = new XSSFWorkbook();
//创建excel中的一个表
Sheet sheet = w.createSheet();
for(int i=0; i<datas.size(); i++){
List<?> l = datas.get(i);
Row row = sheet.createRow(i);
for(int j=0; j<l.size(); j++){
Object o = l.get(j);
if (o instanceof String) {
row.createCell(j).setCellValue((String)o);
}else if(o instanceof Boolean) {
row.createCell(j).setCellValue((Boolean)o);
}else if(o instanceof Number) {
row.createCell(j).setCellValue((Double)o);
}else if(o instanceof Calendar) {
row.createCell(j).setCellValue((Calendar)o);
}else if(o instanceof Date) {
row.createCell(j).setCellValue((Date)o);
}

}
}
return w;
}

public static Workbook createXSSFWorkbook(List<?> titles, List<List<?>> datas){
Workbook w = new XSSFWorkbook();
//创建excel中的一个表
Sheet sheet = w.createSheet();
Row titleRow = sheet.createRow(0);
//设置标题行
for(int j=0; j<titles.size(); j++){
Object o = titles.get(j);
if (o instanceof String) {
titleRow.createCell(j).setCellValue((String)o);
}else if(o instanceof Boolean) {
titleRow.createCell(j).setCellValue((Boolean)o);
}else if(o instanceof Number) {
titleRow.createCell(j).setCellValue((Double)o);
}else if(o instanceof Calendar) {
titleRow.createCell(j).setCellValue((Calendar)o);
}else if(o instanceof Date) {
titleRow.createCell(j).setCellValue((Date)o);
}
}
//从第1行开始
for(int i=0; i<datas.size(); i++){
List<?> l = datas.get(i);
Row row = sheet.createRow(i+1);
for(int j=0; j<l.size(); j++){
Object o = l.get(j);
if (o instanceof String) {
row.createCell(j).setCellValue((String)o);
}else if(o instanceof Boolean) {
row.createCell(j).setCellValue((Boolean)o);
}else if(o instanceof Number) {
row.createCell(j).setCellValue((Double)o);
}else if(o instanceof Calendar) {
row.createCell(j).setCellValue((Calendar)o);
}else if(o instanceof Date) {
row.createCell(j).setCellValue((Date)o);
}

}
}
return w;
}


//=============================================================================================


/**
* 根据title集合自动生成excel
* @param titles
* @return
*/
public static Sheet createSheet(List<?> titles){
Workbook w = new XSSFWorkbook();
//创建excel中的一个表
Sheet sheet = w.createSheet();
Row titleRow = sheet.createRow(0);
//设置标题行
for(int j=0; j<titles.size(); j++){
Object o = titles.get(j);
if (o instanceof String) {
titleRow.createCell(j).setCellValue((String)o);
}else if(o instanceof Boolean) {
titleRow.createCell(j).setCellValue((Boolean)o);
}else if(o instanceof Number) {
titleRow.createCell(j).setCellValue((Double)o);
}else if(o instanceof Calendar) {
titleRow.createCell(j).setCellValue((Calendar)o);
}else if(o instanceof Date) {
titleRow.createCell(j).setCellValue((Date)o);
}
}
return sheet;
}


//======================================================================================================================


/**
* 当不存在行时会创建并返回
* @param sheet
* @param rowIndex
* @return
*/
public static Row getNonNullRow(Sheet sheet, int rowIndex){
Row row = sheet.getRow(rowIndex);
if(row == null){
row = sheet.createRow(rowIndex);
}
return row;
}

/**
* 当不存在单元格时会创建并返回
* @param row
* @param columnIndex
* @return
*/
public static Cell getNonNullCell(Row row, int columnIndex){
Cell cell = row.getCell(columnIndex);
if(cell == null){
cell = row.createCell(columnIndex);
}
return cell;
}

/**
* 用于得到首行列名对应的Map索引
* @param titleRow
* @return
*/
public static Map<String,Integer> getColumnIndexMap(Row titleRow){
Map<String,Integer> columnIndexMap = new HashMap<>();
for(int i=titleRow.getFirstCellNum();i<titleRow.getLastCellNum();i++){
Cell cell = titleRow.getCell(i);
if(cell != null){
int column = cell.getAddress().getColumn();
String key = getCellValueAsString(cell);
columnIndexMap.put(key, column);
}
}
return columnIndexMap;
}


/**
* 用于得到首行的顺序字段名
* @param titleRow
* @return
*/
public static List<String> getFields(Row titleRow){
List<String> files = new ArrayList<>();
for(int i=titleRow.getFirstCellNum();i<titleRow.getLastCellNum();i++){
Cell cell = titleRow.getCell(i);
if(cell != null){
String field = getCellValueAsString(cell);
if(StringUtils.isNoneBlank(field)){
files.add(field);
}
}
}
return files;
}

/**
* 不论是什么类型都当作String拿出来
* @param cell
* @return
*/
public static String getCellValueAsString(Cell cell){
String cellValue;
if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
cellValue = "";
}else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
cellValue = String.valueOf(cell.getBooleanCellValue());
}else if(cell.getCellType() == Cell.CELL_TYPE_ERROR){
cellValue = String.valueOf(cell.getErrorCellValue());
}else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){
cellValue = cell.getCellFormula();
}else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
cellValue = String.valueOf(cell.getNumericCellValue());
}else if(cell.getCellType() == Cell.CELL_TYPE_STRING){
cellValue = cell.getStringCellValue();
}else{
cellValue = null;
}
return cellValue;
}






}