工作上有时要整理挺多excel的,好多工作是体力活,所以写了个java的excel工具类来辅助工作.
更新...
pom:
<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.4</version>
<scope>test</scope>
</dependency>
</dependencies>ExcelUtil:
package clearwind;
import java.io.*;
import java.util.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* @author 晴风
*
* Workbook: excel表格对象,可包含多个Sheet
* Sheet: 工作表对象,包含多个Row
* Row: 行对象,包含多个Cell
* Cell: 单元格对象,最小的对象,有多种单元格数据类型
*/
public class ExcelUtil {
/***索引
*
*/
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);
public static final String XLS = ".xls";
public static final String XLSX = ".xlsx";
public static final String DEFAULT_FONT_NAME = "宋体";
public static final String CRLF = "\r\n";
/**
* 预设的默认的样式
* 宋体 11号 有边框
*
* @param workbook Workbook对象
* @param isTitle 是否是标题
* @return CellStyle
*/
public static CellStyle getDefaultCellStyle(Workbook workbook, boolean isTitle) {
//创建样式
Font defaultFont = workbook.createFont();
CellStyle defaultCellStyle = workbook.createCellStyle();
//设置字体样式
defaultFont.setFontName(DEFAULT_FONT_NAME);
// defaultFont.setFontHeight((short) 11); //这么写并非11号字体
//标题加粗
if (isTitle) {
defaultFont.setBold(true);
}
//四边框
defaultCellStyle.setBorderBottom(BorderStyle.MEDIUM);
defaultCellStyle.setBorderLeft(BorderStyle.MEDIUM);
defaultCellStyle.setBorderRight(BorderStyle.MEDIUM);
defaultCellStyle.setBorderTop(BorderStyle.MEDIUM);
//应用字体
defaultCellStyle.setFont(defaultFont);
return defaultCellStyle;
}
/**
* 预设的默认的样式
* 宋体 11号 有边框
*
* @param workbook Workbook对象
* @return CellStyle
*/
public static CellStyle getDefaultCellStyle(Workbook workbook) {
return getDefaultCellStyle(workbook, false);
}
/**
* 读取excel文件创建Workbook对象，过期，使用getWorkbook()代替
*
* @param filePath 文件路径
* @return Workbook
*/
@Deprecated
public static Workbook readExcel(String filePath) {
Workbook wb = null;
if (filePath == null) {
return null;
}
//提取文件后缀名
String extString = filePath.substring(filePath.lastIndexOf("."));
InputStream is;
try {
//创建输入流
is = new FileInputStream(filePath);
if (XLS.equals(extString)) {
wb = new HSSFWorkbook(is);
} else if (XLSX.equals(extString)) {
wb = new XSSFWorkbook(is);
}
//关闭输入流
is.close();
} catch (IOException e) {
e.printStackTrace();
}
return wb;
}
/**
* 创建一个xlsx文件
*
* @return Workbook
*/
public static Workbook createWorkbook() {
return new XSSFWorkbook();
}
/**
* 使用WorkbookFactory读取excel文件创建Workbook对象
*
* @param filePath 文件路径
* @return Workbook
*/
public static Workbook getWorkbook(String filePath) {
Workbook wb = null;
InputStream is = null;
if ((filePath.endsWith(XLSX) || filePath.endsWith(XLS)) && !filePath.startsWith("~")) {
try {
is = new FileInputStream(filePath);
wb = WorkbookFactory.create(is);
//测试:打印文件名
LOGGER.info("[getWorkbook] " + filePath);
} catch (IOException | InvalidFormatException e) {
e.printStackTrace();
} finally {
//关闭输入流
try {
assert is != null;
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
} else {
LOGGER.info("[getWorkbook] "+ filePath +": 非xls或者xlsx文件!");
}
return wb;
}
/**
* 从文件对象加载Workbook对象
*
* @param excelFile excel文件
* @return Workbook
*/
public static Workbook getWorkbook(File excelFile) throws IOException, InvalidFormatException {
if (!excelFile.exists()){
return null;
}
String fileName = excelFile.getName();
//判断是否是xls或者xlsx文件
if (fileName.endsWith(XLSX) || fileName.endsWith(XLS)) {
LOGGER.info("[getWorkbook] " + excelFile.getPath());
return WorkbookFactory.create(excelFile);
} else {
LOGGER.info("[getWorkbook] 非xls或者xlsx文件!");
return null;
}
}
public static Sheet getSheet(String excelFilePath,String name) {
return getWorkbook(excelFilePath).getSheet(name);
}
public static Sheet getSheetAt(String excelFilePath, int index) {
return getWorkbook(excelFilePath).getSheetAt(index);
}
/**
* 拿到所有Sheet
*
* @return List<Sheet>
*/
public static List<Sheet> getAllSheet(Workbook workbook) {
return getSheetsWithRegex(workbook, "[\\s\\S]*");
}
/**
* 根据一个String[]数组拿到多个Sheet,数组内是Sheet名
*
* @param workbook 表格对象
* @param sheetNames Sheet名称数组
* @return List<Sheet>
*/
public static List<Sheet> getSheetsWithArray(Workbook workbook, String[] sheetNames) {
List<Sheet> sheets = new ArrayList<>();
if (sheetNames.length < 1) {
return sheets;
}
//遍历传入的sheetNames数组
for (String sheetName : sheetNames) {
//尝试根据名称拿到sheet
Sheet sheet = workbook.getSheet(sheetName);
if (sheet != null) {
LOGGER.info("[getSheetsWithArray] 找到Sheet:" + sheetName);
sheets.add(sheet);
} else {
LOGGER.warn("[getSheetsWithArray] 未找到Sheet:" + sheetName);
}
}
return sheets;
}
/**
* 拿到sheet名符合正则的sheet
* 正则语法参考:https://www.runoob.com/regexp/regexp-syntax.html
*
* @param pattern 正则表达式
* @return List<Sheet>
*/
public static List<Sheet> getSheetsWithRegex(Workbook workbook, String pattern) {
List<Sheet> sheets = new ArrayList<>();
//numberOfSheets
int numberOfSheets = workbook.getNumberOfSheets();
for (int i = 0; i < numberOfSheets; i++) {
Sheet sheet = workbook.getSheetAt(i);
String sheetName = sheet.getSheetName();
//正则匹配的sheet添加到list
if (sheetName.matches(pattern)) {
sheets.add(sheet);
}
}
LOGGER.info("[getSheetWithRegex] numberOfSheets:" + sheets.size());
return sheets;
}
/**
* 取一个单元格的字符串值,空返回空字符串
*
* @param row cell所在行
* @param columnIndex 列索引
* @return Cell value
*/
public static String getCellStringValue(Row row, int columnIndex) {
Cell cell;
if (row != null) {
try {
//取第h行，第l列的cell
cell = row.getCell(columnIndex);
//取数据
cell.setCellType(CellType.STRING);
return cell.getStringCellValue();
} catch (Exception e) {
e.printStackTrace();
}
}
return "";
}
/**
* 取一行拼接为String字符串
*
* @param row Row对象
* @param delimiter 分隔符
* @return String
*/
public static String getOneRowToString(Row row, String delimiter) throws Exception {
StringBuilder line = new StringBuilder();
//取得迭代器
if (row != null) {
Iterator<Cell> cellIterator = row.cellIterator();
Cell cell;
while (cellIterator.hasNext()) {
cell = cellIterator.next();
//全部设置为字符串类型
cell.setCellType(CellType.STRING);
//以分隔符分隔每一个值
//line += cell.getStringCellValue() + "\t";
line.append(cell.getStringCellValue()).append(delimiter);
}
//空行
if (line.length() < 1) {
return "";
}
//去除最后的分隔符
return line.substring(0, line.length() - 1);
} else {
LOGGER.info("[getOneRowToString] Row空对象");
return "";
}
}
/**
* 取一行拼接为String字符串，以制表符分隔输出字符串
*
* @param row Row对象
* @return String
*/
public static String getOneRowToString(Row row) throws Exception {
return getOneRowToString(row, "\t");
}
/**
* 取一行
*
* @param sheet Sheet对象
* @param rowIndex 行号
* @return Row
*/
public static Row getRow(Sheet sheet, int rowIndex) throws RuntimeException {
if (sheet == null) {
throw new RuntimeException("[getRow] sheet空对象!");
}
if (rowIndex < 0) {
throw new RuntimeException("[getRow] 行索引不能小0!");
}
if (rowIndex > getLastRowNumIndex(sheet)) {
throw new RuntimeException("[getRow] 行索引不能大于实际表最大索引!");
}
return sheet.getRow(rowIndex);
}
/**
* 取范围内行
*
* @param sRowIndex 开始索引
* @param eRowIndex 结束索引
* @return List<Row>
*/
public static List<Row> getRow(Sheet sheet, int sRowIndex, int eRowIndex) throws RuntimeException {
if (sheet == null) {
throw new RuntimeException("[getRow] sheet空对象!");
}
if (sRowIndex < 0 || eRowIndex < sRowIndex) {
throw new RuntimeException("[getRow] 索引错误!");
}
int lastRowNum = getLastRowNumIndex(sheet);
if (sRowIndex > lastRowNum || eRowIndex > lastRowNum) {
throw new RuntimeException("[getRow] 行索引不能大于实际表最大索引!");
}
//拿row
ArrayList<Row> rows = new ArrayList<>();
for (int i = sRowIndex; i < eRowIndex + 1; i++) {
rows.add(sheet.getRow(i));
}
return rows;
}
/**
* Row转为List<String>
*
* @param row Row对象
* @param setRowLength 设置Row长度(非索引),当为-1时不设置
* @return List<String>
*/
public static List<String> rowToList(Row row,short setRowLength) {
short setLastCellNumIndex = (short) (setRowLength-1);
List<String> dataList = new ArrayList<>();
//标记是否有数据(有些excel会有很多空行)
boolean haveData = false;
short lastCellNum;
//取得列内真实有数据的单元格个数
lastCellNum = getLastCellNumIndex(row);
//固定Row长度
if (setRowLength!=-1 && setLastCellNumIndex!=lastCellNum) {
lastCellNum=setLastCellNumIndex;
}
//自定义方法getLastCellNumIndex会返回索引,所以改为<=
Cell cell;
for (int i = 0; i <= lastCellNum; i++) {
cell = row.getCell(i);
if (cell == null) {
cell = row.createCell(i);
}
//类型置为String,不去判断是什么类型的了
cell.setCellType(CellType.STRING);
//取数据添加到list
String stringCellValue = cell.getStringCellValue();
if (!"".equals(stringCellValue)) {
haveData = true;
}
dataList.add(stringCellValue);
}
//确实至少有一列有数据才返回
if (!haveData) {
Sheet sheet = row.getSheet();
int rowNum = row.getRowNum();
Row row1 = sheet.getRow(0);
System.out.println(rowToList(row1));
LOGGER.warn("[rowToList] "+sheet.getSheetName()+":"+rowNum+",无数据!返回null");
return null;
}
return dataList;
}
/**
* Row转为List<String>,默认不去除右空格
* @param row Row对象
* @return List<String>
*/
public static List<String> rowToList(Row row) {
//不固定列长度
return rowToList(row, (short) -1);
}
/**
* Row转为List<String>
*
* @param rowList 多行
* @return List<List < String>>
*/
public static List<List<String>> rowToList(List<Row> rowList, short setRowLength) {
List<List<String>> dataList = null;
if (rowList.size() == 0) {
return dataList;
}
try {
dataList = new ArrayList<>();
for (Row row : rowList) {
List<String> rowData = rowToList(row,setRowLength);
if (rowData != null) {
dataList.add(rowData);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return dataList;
}
public static List<List<String>> rowToList(List<Row> rowList) {
return rowToList(rowList, (short) -1);
}
/**
* 控制台打印excel
*/
public static void printSheet(Sheet sheet) throws Exception {
try {
//总行数
int lastRowNum = getLastRowNumIndex(sheet);
//打印
for (int i = 0; i <= lastRowNum; i++) {
LOGGER.info(getOneRowToString(getRow(sheet, i)));
}
} catch (NullPointerException e) {
LOGGER.warn("[printSheet] Sheet对象为NULL.");
}
}
/**
* 写一行
*
* @param row 行对象
* @param rowIndex 写excel的第几行
* @param cellStyle 单元格样式
* @return Row
*/
public static Row writeALine(Sheet sheet, Row row, int rowIndex, CellStyle cellStyle) {
return writeALine(sheet, rowToList(row), rowIndex, cellStyle);
}
public static Row writeALine(Sheet sheet, Row row, int rowIndex) {
return writeALine(sheet, row, rowIndex, sheet.getWorkbook().createCellStyle());
}
/**
* 写一行
*
* @param data 数据
* @param rowIndex 写excel的第几行
* @param cellStyle 单元格样式
* @return Row
*/
public static Row writeALine(Sheet sheet, List<String> data, int rowIndex, CellStyle cellStyle) {
Row row = null;
try {
//尝试取得需要写入的行
row = sheet.getRow(rowIndex);
if (row == null) {
//还不存在,新建行
row = sheet.createRow(rowIndex);
}
//取得行的单元格,并写数据
Cell cell;
if (data == null) {
LOGGER.warn("无数据,返回空行");
return row;
}
for (int i = 0; i < data.size(); i++) {
//从yIndex列开始写
cell = row.createCell(i);
//全部设置数据类型为文本
cell.setCellType(CellType.STRING);
cell.setCellValue(data.get(i));
//设置样式
cell.setCellStyle(cellStyle);
}
} catch (Exception e) {
e.printStackTrace();
}
return row;
}
/**
* 写一行,无样式
*
* @param data 数据
* @param rowIndex 写excel的第几行
* @return Row
*/
public static Row writeALine(Sheet sheet, List<String> data, int rowIndex) {
return writeALine(sheet, data, rowIndex, sheet.getWorkbook().createCellStyle());
}
/**
* 写一列
*
* @param sheet Sheet对象
* @param data 数据
* @param startRowIndex 从excel第几行开始写
* @param colIndex 写excel的第几列
*/
public static void writeAColumn(Sheet sheet, List<String> data, int startRowIndex, int colIndex) {
try {
if (sheet == null) {
throw new RuntimeException("Sheet is null!");
}
if (data.size() == 0) {
throw new RuntimeException("Data is empty!");
}
if (startRowIndex < 0 || colIndex < 0) {
throw new RuntimeException("StartRowIndex and colIndex needs to be greater than 1!");
}
//开始写
for (int i = 0; i < data.size(); i++) {
LOGGER.info("写入第" + i + "行...");
Row row = sheet.getRow(i);
if (row == null) {
sheet.createRow(startRowIndex + i).createCell(colIndex).setCellValue(data.get(i));
} else {
row.createCell(colIndex).setCellValue(data.get(i));
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 写键值对类型的数据
*
* @param sheet sheet
* @param data 数据，map类型
* @param startRowIndex 从excel第几行开始写
* @param colIndex1 key写在excel第几列
* @param colIndex2 value写在excel第几列
*/
public static void writeMapTypeData(Sheet sheet, Map<String, String> data, int startRowIndex, int colIndex1, int colIndex2) {
try {
Row row;
if (sheet == null) {
throw new RuntimeException("Sheet is null!");
}
if (data.size() == 0) {
throw new RuntimeException("Data is empty!");
}
if (startRowIndex < 0 || colIndex1 < 0 || colIndex2 < 0) {
throw new RuntimeException("StartRowIndex and colIndex needs to be greater than 1!");
}
if (colIndex1 == colIndex2) {
throw new RuntimeException("ColIndex1 is equal to colIndex2!");
}
//将Map转为list
List<Map.Entry<String, String>> dataList = new ArrayList<>(data.entrySet());
for (int i = 0; i < data.size(); i++) {
row = sheet.createRow(startRowIndex + i);
row.createCell(colIndex1).setCellValue(dataList.get(i).getKey());
row.createCell(colIndex2).setCellValue(dataList.get(i).getValue());
}
} catch (Exception e) {
e.printStackTrace();
}
}
@SuppressWarnings({"rawtypes", "unchecked"})
public static void printCollect(Object objs) {
if (objs instanceof Map) {
Map maps = (Map) objs;
Set<Map.Entry> set = maps.entrySet();
for (Map.Entry entry : set) {
LOGGER.info(entry.getKey() + " -> " + entry.getValue());
}
} else if (objs instanceof Collection) {
for (Object obj : (Collection) objs) {
LOGGER.info(obj.toString());
}
} else if (objs.getClass().isArray()) {
// List<Object> objects = Arrays.asList(objs);
// printCollect(objects);
LOGGER.warn("未实现数组打印...,有空研究...");
} else {
LOGGER.info(objs + "不是 Collection 或者 Map 类型!!");
}
}
/**
* 拿到表最后一行索引
* @param sheet Sheet对象
* @return lastRowNum
*/
public static int getLastRowNumIndex(Sheet sheet){
int lastRowNum = sheet.getLastRowNum();
//修正
//sheet.getLastRowNum(); 取得的是索引,已验证
for (int i = lastRowNum; i >= 0; i--) {
Row row;
try {
row = sheet.getRow(i);
} catch (NullPointerException npe) {
//若row为空
//跳过,进行下一次循环
continue;
}
List<String> strings = rowToList(row);
//从最后一行开始校验,遇到非空行则跳出
if (strings == null) {
lastRowNum--;
} else {
break;
}
}
return lastRowNum;
}
/**
* 拿到行的最后一列索引
* @param row Row对象
* @return lastCellNum
*/
public static short getLastCellNumIndex(Row row) throws IllegalStateException{
short lastCellNum = 0;
lastCellNum = row.getLastCellNum();
//修正
for (int i = lastCellNum - 1; i >= 0; i--) {
//从最后一列开始校验,遇到非空列则跳出
Cell cell = row.getCell(i);
if (cell == null) {
lastCellNum--;
} else {
String stringCellValue;
// stringCellValue = cell.getStringCellValue();
try {
stringCellValue = cell.getStringCellValue();
} catch (IllegalStateException ie) {
Sheet sheet = row.getSheet();
System.out.println("错误:"+sheet.getSheetName()+","+row.getRowNum());
System.out.println(i);
// throw ie;
// stringCellValue="";
stringCellValue=cell.getNumericCellValue()+"";
}
if ("".equals(stringCellValue.trim())) {
lastCellNum--;
} else {
break;
}
}
}
//row.getLastCellNum()拿到的是列数而非索引,所以做-1处理
return (short)(lastCellNum-1);
}
/**
* 取得一个sheet内的所有行，存到List内,中间空行会被屏蔽
*
* @param sheet Sheet对象
* @return List<List < String>>
*/
public static List<List<String>> getSheetAllRowsToList(Sheet sheet) throws Exception {
List<List<String>> rowsList = new ArrayList<>();
//sheet总行数
int lastRowNum = getLastRowNumIndex(sheet);
//打印详情:打印SheetName
LOGGER.info("[getSheetAllRowsToList] SheetName:" + sheet.getSheetName() + ",lastRowNum:" + lastRowNum);
if (lastRowNum > 0) {
Row row;
//遍历sheet
for (int i = 0; i <= lastRowNum; i++) {
//拿到一行
row = ExcelUtil.getRow(sheet, i);
//中间空行判断
String rowString = ExcelUtil.getOneRowToString(row, "");
if (!"".equals(rowString)) {
//拿到一行的list
rowsList.add(ExcelUtil.rowToList(row));
}
}
}
return rowsList;
}
/**
* 不能与write()同名,否则java编译时类型擦除导致两个方法的声明一样,编译不通过
*/
public static Sheet writeRow(Sheet sheet, List<Row> data, int startIndex, CellStyle cellStyle) throws Exception {
return write(sheet, rowToList(data), startIndex, cellStyle);
}
public static Sheet writeRow(Sheet sheet, List<Row> data, int startIndex) throws Exception {
return writeRow(sheet, data, startIndex, sheet.getWorkbook().createCellStyle());
}
/**
* 多行写入到sheet内
*
* @param data 写入的数据
* @param startIndex 从第几行开始写入
*/
public static Sheet write(Sheet sheet, List<List<String>> data, int startIndex, CellStyle cellStyle) throws Exception {
//遍历数据写入
for (List<String> oneRow : data) {
writeALine(sheet, oneRow, startIndex++, cellStyle);
}
return sheet;
}
public static Sheet write(Sheet sheet, List<List<String>> data, int startIndex) throws Exception {
return write(sheet, data, startIndex, sheet.getWorkbook().createCellStyle());
}
/**
* 通过sheetName取sheet,不存在则创建
*
* @param wb Workbook对象
* @param sheetName sheet名
* @return Sheet
*/
public static Sheet getOrCreateSheet(Workbook wb, String sheetName) {
Sheet sheet = null;
try {
sheet = wb.getSheet(sheetName);
if (sheet == null) {
sheet = wb.createSheet(sheetName);
}
} catch (Exception e) {
e.printStackTrace();
}
return sheet;
}
/**
* 遍历文件夹
*
* @param folderPath 文件夹路径
* @return List<File>
*/
public static List<File> fileList(String folderPath) {
//文件夹
File folder = new File(folderPath);
File[] files = folder.listFiles();
//存文件对象
ArrayList<File> fileList = new ArrayList<>();
if (files != null) {
//递归遍历文件夹内文件
for (File f : files) {
if (f.isDirectory()) {
//递归
fileList.addAll(fileList(f.getAbsolutePath()));
}
if (f.isFile()) {
fileList.add(f);
}
}
}
LOGGER.info("[fileList] 共" + fileList.size() + "个文件.");
return fileList;
}
/**
* 合并多个`表头一致`的sheet,返回新的Workbook对象
*
* @return Workbook
*/
public static Workbook sheetsMerge(List<Sheet> sheets, int titleIndex) {
return sheetsMerge(sheets, titleIndex, createWorkbook(), "merge");
}
/**
* 合并多个`表头一致`的sheet,写入到指定wb
*
* @param sheets 合并文件列表
* @param titleIndex 表头最大索引
* @param workbook 写入到的wb
* @param mergeSheetName 写入数据的新sheet名
* @return Workbook
*/
public static Workbook sheetsMerge(List<Sheet> sheets, int titleIndex, Workbook workbook, String mergeSheetName) {
try {
if (sheets == null || sheets.size() < 1) {
return null;
}
//创建sheet
Sheet mergeSheet = workbook.createSheet(mergeSheetName);
//拿第一个sheet的表头,写到mergeSheet
List<Row> titleRows = getRow(sheets.get(0), 0, titleIndex);
for (int i = 0; i < titleRows.size(); i++) {
writeALine(mergeSheet, rowToList(titleRows.get(i)), i, getDefaultCellStyle(workbook, true));
}
//遍历sheets,拿数据写到mergeSheet
for (int i = 0; i < sheets.size(); i++) {
String logMSG = "[sheetsMerge] 合并第" + (i + 1) + "个sheet";
LOGGER.info(logMSG);
Sheet sheet = sheets.get(i);
int lastRowNum = getLastRowNumIndex(sheet);
List<Row> dataRows;
//有数据才执行
if (lastRowNum > titleIndex) {
//拿数据
dataRows = getRow(sheet, titleIndex + 1, lastRowNum);
//写数据
for (Row dataRow : dataRows) {
List<String> strings = rowToList(dataRow);
//保险,非空才写,rowToList全空列会返回null
if (strings != null) {
writeALine(mergeSheet, strings, getLastRowNumIndex(mergeSheet) + 1, getDefaultCellStyle(workbook));
}
}
} else {
logMSG = "[sheetsMerge] 第" + (i + 1) + "个sheet无数据"+"("+sheet.getSheetName()+")";
LOGGER.info(logMSG);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return workbook;
}
/**
* 依据某一列,拆分一个sheet到多个sheet,返回新的Workbook对象
*
* @return Workbook
*/
public static Workbook breakdownIntoSheets(Sheet sheet, int titleIndex, int basisIndex) throws Exception {
return breakdownIntoSheets(sheet, titleIndex, basisIndex, createWorkbook());
}
/**
* 依据某一列,拆分一个sheet到多个sheet,返回新的Workbook对象
*
* @param sourceSheet 需要拆分的sheet
* @param titleIndex 表头最大索引
* @param basisIndex 依据列索引,依据列请勿带特殊字符
* @param workbook 写入到的 workbook
* @return Workbook
*/
public static Workbook breakdownIntoSheets(Sheet sourceSheet, int titleIndex, int basisIndex, Workbook workbook) throws Exception {
LOGGER.info("[breakdownIntoSheets] 拆分:"+sourceSheet.getSheetName()+"\t依据列:"+basisIndex);
//取得最后一行索引
int lastRowNumIndex = getLastRowNumIndex(sourceSheet);
//取表头
List<Row> titleRow = getRow(sourceSheet, 0, titleIndex);
//拿到数据行
List<Row> dataRowList = getRow(sourceSheet, titleIndex + 1, lastRowNumIndex);
//数据行转list
List<List<String>> rowToLists = rowToList(dataRowList);
//封装的各个sheet数据
Map<String, List<List<String>>> sheetsMap = new LinkedHashMap<String, List<List<String>>>();
//遍历数据行,依照依据列提取各分sheet行到Map
for (List<String> rowList : rowToLists) {
//依据列的值
String basisValue = rowList.get(basisIndex);
//sheetsMap内查找,不存在则创建
List<List<String>> lists = sheetsMap.computeIfAbsent(basisValue, k -> new ArrayList<List<String>>());
//把当前行加到map里
lists.add(rowList);
}
//上面for结束表示已遍历完数据,下面开始按照分好的数据建sheet,并把数据写进去
Set<Map.Entry<String, List<List<String>>>> entries = sheetsMap.entrySet();
for (Map.Entry<String, List<List<String>>> entry : entries) {
String basisValue = entry.getKey();
//使用basisValue建sheet
Sheet basisValueSheet = workbook.createSheet(basisValue);
LOGGER.info("[breakdownIntoSheets] 新Sheet:" + basisValue + CRLF);
//写数据到这个sheet里,表头
writeRow(basisValueSheet,titleRow,0);
//写数据到这个sheet里,数据
write(basisValueSheet,entry.getValue(),titleIndex+1);
}
LOGGER.info("[breakdownIntoSheets] 拆分出Sheet数:" + sheetsMap.size() + CRLF);
return workbook;
}
/**
* 列转行
* <p>
* ID F1 F2
* 1 a A,B
* -->>
* ID F1 F2
* 1 a A
* 1 a B
*
* @param sheet 存数据的sheet
* @param titleIndex 表头最大索引
* @param splitIndex 拆分列
* @param delimiter 分隔符,注意:分隔符是正则中的特殊字符时,需要带转义
* @param newWorkbook 合并结果存放的wb
* @return 合并结果存放的wb
*/
public static Workbook columnsToRows(Sheet sheet, int titleIndex, int splitIndex, String delimiter, Workbook newWorkbook, String newSheetName) throws Exception {
//拿到sheet最大行数
int lastRowNum = getLastRowNumIndex(sheet);
//拿到表头
List<Row> titleRows = getRow(sheet, 0, titleIndex);
//拿到表头的最后一行
Row titleMaxRow = titleRows.get(titleIndex);
//根据表头最后一行得到每行长度
short lastCellNumIndex = getLastCellNumIndex(titleMaxRow);
//表头转List
List<List<String>> titleRowsList = ExcelUtil.rowToList(titleRows);
//表头添加一列
titleRowsList.get(titleIndex).add("原始位置");
//拿到数据
List<Row> dataRows = getRow(sheet, titleIndex + 1, lastRowNum);
//创建新sheet存放合并后的数据
Sheet newSheet = newWorkbook.createSheet(newSheetName);
//新数据Collect 表<行<单元格>>
ArrayList<List<String>> lists = new ArrayList<>();
//记录数据行索引,方便找到报错行
int dataIndex = titleIndex+1;
//遍历数据 row每一行
for (Row row : dataRows) {
//Row转List,固定宽度
List<String> strings = rowToList(row, (short) (lastCellNumIndex+1));
assert strings != null;
String splitStringValue;
try {
splitStringValue=strings.get(splitIndex);
} catch (IndexOutOfBoundsException iobe) {
LOGGER.warn("Sheet:" + sheet.getSheetName() + ",行:" + dataIndex+1 + ",拆分列索引越界");
LOGGER.warn(strings.toString());
//这种情况一般为拆分列无数据,且后面的列也没有数据,这里给处理了
splitStringValue="";
// throw iobe;
}
//去前后空格
splitStringValue = splitStringValue.trim();
//尝试拆分
String[] splits = splitStringValue.split(delimiter);
//拆分列直接使用拆分出来的值替换原来的值
for (String split : splits) {
//复制一个新的list,并替换列的值
ArrayList<String> strings_new = new ArrayList<>(strings);
//添加一列:原始位置
strings_new.add((dataIndex+1)+"");
boolean b = Collections.replaceAll(strings_new, splitStringValue, split);
lists.add(strings_new);
}
//更新数据行索引
dataIndex++;
}
// printCollect(lists);
//存储到newSheet内
//写表头
write(newSheet, titleRowsList, 0,getDefaultCellStyle(newSheet.getWorkbook(),true));
//写数据
write(newSheet, lists, getLastRowNumIndex(newSheet) + 1,getDefaultCellStyle(newSheet.getWorkbook()));
return newWorkbook;
}
public static Workbook columnsToRows(Sheet sheet, int titleIndex, int splitIndex, String delimiter) throws Exception {
//默认
Workbook newWorkbook = sheet.getWorkbook();
String newSheetName="columnsToRows_" + String.valueOf(System.currentTimeMillis()).substring(8);
return columnsToRows(sheet, titleIndex, splitIndex, delimiter, newWorkbook, newSheetName);
}
/**
* 写到xlsx文件
*/
public static void workbookWrite(Workbook workbook, String pathName) {
try {
workbook.write(new FileOutputStream(pathName));
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 合并多个excel文件的某个sheet(格式,sheetIndex位置要一致),文件夹内请勿包含其它不需要合并的文件
*
* @param pathName 输入和输出文件文件夹的地址
* @param sheetIndex 需要合并的sheetIndex
*/
public static void workbookMerge(String pathName, int sheetIndex, int titleIndex) throws IOException, InvalidFormatException {
workbookMerge(pathName, pathName,createWorkbook(), sheetIndex, titleIndex);
}
/**
* 合并多个excel文件的某个sheet(格式,sheetIndex位置要一致),文件夹内请勿包含其它不需要合并的文件
*
* @param inPath 输入文件文件夹的地址
* @param outPath 输出文件文件夹的地址
* @param newWorkbook 输出到Workbook
* @param sheetIndex 需要合并的sheetIndex
* @return Workbook
*/
public static Workbook workbookMerge(String inPath, String outPath, Workbook newWorkbook, int sheetIndex, int titleIndex) throws IOException, InvalidFormatException {
//取得需要合并的文件列表
List<File> files = fileList(inPath);
//拿到所有需要合并的sheet
ArrayList<Sheet> sheets = new ArrayList<>();
for (File file : files) {
String fileName = file.getName();
if (fileName.endsWith(XLSX) || fileName.endsWith(XLS)) {
Workbook workbook = getWorkbook(file);
assert workbook != null;
Sheet sheetAt = workbook.getSheetAt(sheetIndex);
sheets.add(sheetAt);
}
}
if (sheets.size() != 0) {
//合并sheet
newWorkbook = sheetsMerge(sheets, titleIndex,newWorkbook,sheets.get(0).getSheetName());
//写出
workbookWrite(newWorkbook, outPath + "\\合并" + XLSX);
} else {
LOGGER.info("[workbookMerge] 空Sheets,检查文件夹路径是否有误!");
}
return newWorkbook;
}
/**
* 合并多个excel文件的多个sheet(格式,sheetIndex位置要一致),文件夹内请勿包含其它不需要合并的文件
*
* @param inPath 输入文件文件夹的地址
* @param outPath 输出文件文件夹的地址
* @param sheet_title 需要合并的sheetIndex->titleIndex
* @return Workbook
*/
public static Workbook workbookMerge(String inPath, String outPath, Map<Integer,Integer> sheet_title) throws IOException, InvalidFormatException {
Workbook newWorkbook = createWorkbook();
//解析map
Set<Map.Entry<Integer, Integer>> entries = sheet_title.entrySet();
//遍历map
for (Map.Entry<Integer, Integer> entry : entries) {
Integer sheetIndex = entry.getKey();
Integer titleIndex = entry.getValue();
// 调用合并
newWorkbook = workbookMerge(inPath, outPath, newWorkbook, sheetIndex, titleIndex);
}
return newWorkbook;
}
/**
* 根据一个String[]数组删除多个Sheet,数组内是Sheet名
*
* @param workbook 表格对象
* @param sheetNames Sheet名称数组
* @return boolean(全部找到并删除则返回true)
*/
public static boolean delSheetWithArray(Workbook workbook, String[] sheetNames) {
LOGGER.info("[delSheetWithArray] " + workbook);
boolean flag = true;
if (sheetNames.length < 1) {
return flag;
}
//遍历sheetNames
for (String sheetName : sheetNames) {
//尝试通过名称拿到Sheet索引
int sheetIndex = workbook.getSheetIndex(sheetName);
if (sheetIndex != -1) {
workbook.removeSheetAt(sheetIndex);
LOGGER.info("[delSheetWithArray] 删除Sheet:" + sheetName);
} else {
//只要一个未找到则返回false
flag = false;
LOGGER.info("[delSheetWithArray] 未找到Sheet:" + sheetName);
}
}
return flag;
}
/**
* 创建sheet,并设置表头
*
* @param workbook 工作簿
* @param sheetName Sheet名
* @param title 表头,可多行
* @return Sheet
*/
public static Sheet createSheet(Workbook workbook, String sheetName, List<List<String>> title) {
Sheet sheet = null;
try {
//创建新的sheet
sheet = workbook.createSheet(sheetName);
//拿到默认表头样式
CellStyle defaultCellStyle = getDefaultCellStyle(workbook, true);
//写表头
for (int i = 0; i < title.size(); i++) {
writeALine(sheet, sheet.getRow(i), i, defaultCellStyle);
}
} catch (IllegalArgumentException e) {
String logMsg = "[createSheet] Sheet'" + sheetName + "'已存在,跳过";
LOGGER.warn(logMsg);
}
return sheet;
}
/**
* 创建sheet,并设置表头
*
* @param workbook 工作簿
* @param sheetName Sheet名
* @param titleRow 表头,一行
* @return Sheet
*/
public static Sheet createSheet(Workbook workbook, String sheetName, Row titleRow) {
ArrayList<List<String>> title = new ArrayList<>();
title.add(rowToList(titleRow));
return createSheet(workbook, sheetName, title);
}
/**
* 同时设置Sheet多列列宽
*
* @param sheet Sheet对象
* @param widths 变长参数,每一个参数设置一列的列宽
*/
public static void setColumnWidth(Sheet sheet, int... widths) {
//根据变长参数的个数和顺序从第一列开始设置每一列的列宽
for (int i = 0; i < widths.length; i++) {
//宽计算公式,较接近实际宽度
int width = 256 * widths[i] + 154;
sheet.setColumnWidth(i, width);
}
}
public static void setColumnWidthWithMultipleSheets(List<Sheet> sheets, int... widths) {
//遍历每一个sheet
for (Sheet sheet : sheets) {
setColumnWidth(sheet, widths);
}
}
/**
* 设置Sheet为默认样式
* (sheet.getLastRowNum拿到的是之后一行索引,row.getLastCellNum()拿到的是单元格个数.)
*
* @param sheet Sheet对象
* @param titleIndex 表头最后索引
*/
public static void setAsDefaultCellStyle(Sheet sheet, int titleIndex) {
LOGGER.info("[setAsDefaultCellStyle] sheetName:" + sheet.getSheetName());
//取得默认样式
CellStyle defaultTitleCellStyle = getDefaultCellStyle(sheet.getWorkbook(), true);
CellStyle defaultCellStyle = getDefaultCellStyle(sheet.getWorkbook());
//依据表头最后一列取得列数
Row row = getRow(sheet, titleIndex);
short lastCellNum = getLastCellNumIndex(row);
//设置表头样式
List<Cell> titleCells = getCellInArea(sheet, titleIndex, lastCellNum);
for (Cell cell : titleCells) {
cell.setCellStyle(defaultTitleCellStyle);
}
//拿到表最大行索引
int lastRowNum = getLastRowNumIndex(sheet);
//拿到数据cell,并设置样式
List<Cell> dataCells = getCellInArea(sheet, titleIndex + 1, 0, lastRowNum, lastCellNum);
for (Cell cell : dataCells) {
cell.setCellStyle(defaultCellStyle);
}
}
/**
* 设置Sheet为默认样式
*
* @param sheets 多个Sheet对象
* @param titleIndex 表头最后索引
*/
public static void setAsDefaultCellStyle(List<Sheet> sheets, int titleIndex) {
for (Sheet sheet : sheets) {
setAsDefaultCellStyle(sheet, titleIndex);
}
}
/**
* 拿到指定区域内的cell
*
* @param sheet Sheet对象
* @param indexTLX 左上X索引
* @param indexTLY 左上Y索引
* @param indexBRX 右下X索引
* @param indexBRY 右下Y索引
* @return List<Cell>
*/
public static List<Cell> getCellInArea(Sheet sheet, int indexTLX, int indexTLY, int indexBRX, int indexBRY) {
LOGGER.info("[getCellInArea] 区域[" + indexTLX + "," + indexTLY + ":" + indexBRX + "," + indexBRY + "]");
ArrayList<Cell> cells = new ArrayList<>();
//遍历指定区域
for (int i = indexTLX; i <= indexBRX; i++) {
//尝试获取行
Row row = sheet.getRow(i);
//不存在则创建
if (row == null) {
row = sheet.createRow(i);
}
for (int j = indexTLY; j <= indexBRY; j++) {
//尝试获取列
assert row != null;
Cell cell = row.getCell(j);
//不存在则创建
if (cell == null) {
cell = row.createCell(j);
}
//添加到list
cells.add(cell);
}
}
LOGGER.info("[getCellInArea] cell个数:" + cells.size());
return cells;
}
/**
* 拿到指定区域内的cell,从左上角开始计算
*
* @param sheet Sheet对象
* @param indexBRX 右下X索引
* @param indexBRY 右下Y索引
* @return List<Cell>
*/
public static List<Cell> getCellInArea(Sheet sheet, int indexBRX, int indexBRY) {
return getCellInArea(sheet, 0, 0, indexBRX, indexBRY);
}
/**
* 列转行
* ID F1 F2
* 1 a A
* 1 a B
* -->>
* ID F1 F2
* 1 a A,B
*
* @param sheet
* @param titleIndex
* @param basisIndex
* @param mergeIndex
* @param separator
* @return
* @throws Exception
*/
public static Sheet rowsToColumn(Sheet sheet,int titleIndex,int basisIndex,int mergeIndex,String separator) throws Exception {
//存储依据列和合并列
HashMap<String, List<String>> basis_merge = new HashMap<>();
//合并之后数据存放的sheet
Sheet newSheet = sheet.getWorkbook().createSheet(sheet.getSheetName() + "_列转行");
CellStyle defaultCellStyle = getDefaultCellStyle(newSheet.getWorkbook());
HashMap<String, List<String>> newSheetData = new HashMap<>();
//写表头到newSheet
writeRow(newSheet,getRow(sheet,0,titleIndex),0,defaultCellStyle);
//遍历来源sheet
List<List<String>> sheetAllRowsToList = getSheetAllRowsToList(sheet);
for (int i=titleIndex+1;i<sheetAllRowsToList.size();i++){
//拿到一行
List<String> rowList = sheetAllRowsToList.get(i);
//取出依据列和和并列
String key = rowList.get(basisIndex);
String value = rowList.get(mergeIndex);
//尝试在map里找依据列
List<String> values = basis_merge.get(key);
// stringListHashMap.computeIfAbsent(s,
//// k-> {
//// ArrayList<String> strings1 = new ArrayList<>();
//// strings1.add(st);
//// return strings1;
//// }
//// );
//没找到则创建
if (values==null){
values=new ArrayList<String>();
//只放一行到新sheet数据的map内
newSheetData.put(key, rowList);
}
//把值暂时写到list里
values.add(value);
//加到map里,key是`依据列`,值是`合并列`
basis_merge.put(key, values);
}
//遍历合并好的结果,替换写到新sheet数据的map内
Set<Map.Entry<String, List<String>>> entries = basis_merge.entrySet();
for (Map.Entry<String, List<String>> entry:entries) {
List<String> rowList = newSheetData.get(entry.getKey());
//替换掉原始值
// rowList.remove(mergeIndex);
rowList.set(mergeIndex, StringUtils.join(entry.getValue().toArray(), separator));
//写数据到sheet
writeALine(newSheet,rowList,getLastRowNumIndex(newSheet)+1,defaultCellStyle);
}
return newSheet;
}
/**
* 测试
*/
public static void main(String[] args) {
System.out.println("123");
}
}