需求说明
需要读取内容如下的Excel
分析
将Excel分为2部分来读, 表头和数据
public class ExcelUtils {
public static void main(String[] args) throws Exception {
String excelPath = "E://b.xls";
int sheetIndex = 0;
int headerRowNum = 1;
List<ExcelData> headerList = readExcelHeader("E://b.xls", 0, headerRowNum);
List<ExcelData> bodyList = readExcelBody("E://b.xls", 0, headerRowNum);
OutputStream out2 = new FileOutputStream("E://b1111.xls");
writeExcel("测试1111", headerList, bodyList, out2 , headerRowNum);
out2.flush();
out2.close();
System.out.println("excel读取成功!");
}
public static void writeExcel(String sheetName,
List<ExcelData> headers, List<ExcelData> bodys,
OutputStream out , int headerRowNum) throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(sheetName);
sheet.setDefaultColumnWidth((short) 15);
writeHeader(workbook, sheet, headers);
Map<Integer, HSSFRow> rowMap = new HashMap<>();
int bodyRowNum = getBodyMaxRow(bodys);
for (int i = 0; i < bodyRowNum; i++) {
HSSFRow row = sheet.createRow(headerRowNum + i);
rowMap.put(headerRowNum + i, row);
}
writeBody(workbook, sheet, bodys, rowMap);
workbook.write(out);
}
// 读取excel 表头数据
/**
*
* @param excelPath excel路径
* @param sheetIndex sheet页
* @param headerRowNum 表头行数
* @return
* @throws IOException
*/
public static List<ExcelData> readExcelHeader(String excelPath, int sheetIndex, int headerRowNum) throws IOException {
if (headerRowNum <=0 ) {
return null;
}
Workbook wb;
File xlsFile = new File(excelPath);
String fileName = xlsFile.getName();
String extName = fileName.substring(fileName.lastIndexOf("."));
if (ExcelVersion.V2003.getSuffix().equals(extName)) {
wb = new HSSFWorkbook(new FileInputStream(xlsFile));
} else if (ExcelVersion.V2007.getSuffix().equals(extName)) {
wb = new XSSFWorkbook(new FileInputStream(xlsFile));
} else {
throw new IllegalArgumentException("Invalid excel version");
}
Sheet sheet = wb.getSheetAt(sheetIndex);
//int readRowCount = sheet.getLastRowNum(); //sheet的总行数
int readRowCount = headerRowNum ;//sheet.getPhysicalNumberOfRows(); //sheet的总行数
List<ExcelData> headerList = new ArrayList<>();
for (int j = sheet.getFirstRowNum(); j < readRowCount; j++) {
Row row = sheet.getRow(j);
if (row == null) {
continue;
}
int columnNum = row.getPhysicalNumberOfCells(); // 一行中的总列数
for (int k = row.getFirstCellNum(); k < columnNum; k++) {
headerList.add(getCellExcelData(sheet, j, k));
}
}
return headerList.stream()
.collect(
Collectors.collectingAndThen(
Collectors.toCollection(
()-> new TreeSet<>( Comparator.comparing(ExcelData::dataFlag))), ArrayList::new));
}
// 设置表头样式
private static HSSFCellStyle setHeaderType(HSSFWorkbook workbook) {
HSSFCellStyle style = workbook.createCellStyle();
//加边框
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
//居中
style.setAlignment(HorizontalAlignment.CENTER);//水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
//设置字体
HSSFFont font = workbook.createFont();
font.setColor(IndexedColors.BLACK.index);
font.setFontHeightInPoints((short)15);
font.setBold(true);
style.setFont(font);
//设置背景
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setFillForegroundColor(IndexedColors.YELLOW.index);
return style;
}
// 设置BODY样式
private static HSSFCellStyle setBodyType(HSSFWorkbook workbook) {
HSSFCellStyle style = workbook.createCellStyle();
//加边框
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
//居中
style.setAlignment(HorizontalAlignment.CENTER);//水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
//设置字体
HSSFFont font = workbook.createFont();
font.setColor(IndexedColors.BLACK.index);
font.setFontHeightInPoints((short)15);
style.setFont(font);
return style;
}
// 获取头的最大行数
private static int getheaderMaxRow(List<ExcelData> headerList) {
Optional<ExcelData> header = headerList.parallelStream()
.max(Comparator.comparingInt(ExcelData::getRowLength));
return header.get().getRowLength();
}
// 获取BODY的最大行数
private static int getBodyMaxRow(List<ExcelData> bodyList) {
int result = 0;
for (ExcelData data : bodyList ) {
result = result + data.getRowLength();
}
return result;
}
private static void writeHeader(HSSFWorkbook workbook, HSSFSheet sheet,
List<ExcelData> headers) {
Map<Integer, HSSFRow> rowMap = new HashMap<>();
int headerRowNum = getheaderMaxRow(headers);
for (int i = 0; i < headerRowNum; i++) {
HSSFRow row = sheet.createRow(i);
rowMap.put(i, row);
}
HSSFCellStyle styleheader = setHeaderType(workbook);
for (short i = 0; i < headers.size(); i++) {
ExcelData headler = headers.get(i);
HSSFRow row = rowMap.get(headler.getStartRow());;
HSSFCell cell = row.createCell(headler.getStartCell());
cell.setCellStyle(styleheader);
cell.setCellValue(headler.getValue());
if (headler.getRowLength() > 1 || headler.getCellLength() > 1) {
CellRangeAddress cra = new CellRangeAddress(headler.getStartRow(), headler.getEndRow(),
headler.getStartCell(), headler.getEndCell());
sheet.addMergedRegion(cra);
RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet); // 下边框
RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet); // 左边框
RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet); // 有边框
RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet); // 上边框
}
}
}
private static void writeBody(HSSFWorkbook workbook, HSSFSheet sheet,
List<ExcelData> bodyList, Map<Integer, HSSFRow> rowMap) {
HSSFCellStyle stylebody = setBodyType(workbook);
for (short i = 0; i < bodyList.size(); i++) {
ExcelData body = bodyList.get(i);
HSSFRow row = rowMap.get(body.getStartRow());
HSSFCell cell = row.createCell(body.getStartCell());
cell.setCellStyle(stylebody);
System.out.println("row:" + body.getStartRow() +
", coll:" + body.getStartCell() +
", value:" + body.getValue());
cell.setCellValue(body.getValue());
if (body.getRowLength() > 1 || body.getCellLength() > 1) {
CellRangeAddress cra = new CellRangeAddress(body.getStartRow(), body.getEndRow(),
body.getStartCell(), body.getEndCell());
sheet.addMergedRegion(cra);
RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet); // 下边框
RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet); // 左边框
RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet); // 有边框
RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet); // 上边框
}
if (null != body.getSubList()) {
writeBody( workbook, sheet, body.getSubList(), rowMap);
}
}
}
public static List<ExcelData> readExcelBody(String excelPath, int sheetIndex, int headerRowNum) throws IOException {
Workbook wb;
File xlsFile = new File(excelPath);
String fileName = xlsFile.getName();
String extName = fileName.substring(fileName.lastIndexOf("."));
if (ExcelVersion.V2003.getSuffix().equals(extName)) {
wb = new HSSFWorkbook(new FileInputStream(xlsFile));
} else if (ExcelVersion.V2007.getSuffix().equals(extName)) {
wb = new XSSFWorkbook(new FileInputStream(xlsFile));
} else {
throw new IllegalArgumentException("Invalid excel version");
}
Sheet sheet = wb.getSheetAt(sheetIndex);
int readRowCount = sheet.getPhysicalNumberOfRows(); //sheet的总行数
List<ExcelData> bodyList = new ArrayList<>();
Map<String, ExcelData> dataMap = new HashMap<>(); // 位置坐标和数据的映射
for (int j = headerRowNum; j < readRowCount; j++) {
Row row = sheet.getRow(j);
if (row == null) {
continue;
}
int columnNum = row.getPhysicalNumberOfCells(); // 一行中的总列数
ExcelData currData = null;
ExcelData perData = null;
for (int k = row.getFirstCellNum(); k < columnNum ; k++) {
if (k == row.getFirstCellNum()) {
perData = null;
} else {
perData = getCellExcelData(sheet, j, k-1);
perData = dataMap.get(perData.dataFlag());
}
currData = getCellExcelData(sheet, j, k); // j行k列的数据,
if (null == dataMap.get(currData.dataFlag())) {
dataMap.put(currData.dataFlag(), currData);
} else {
currData = dataMap.get(currData.dataFlag());
continue;
}
if (perData != null) {
currData.setpId(perData.getId());
} else {
currData.setpId("root");
}
bodyList.add(currData);
}
}
bodyList = StreamUtils.filter(bodyList, s -> StringUtils.isNotBlank(s.getValue()));
bodyList = bodyList.stream()
.collect(
Collectors.collectingAndThen(
Collectors.toCollection(
()-> new TreeSet<>( Comparator.comparing(ExcelData::dataFlag))), ArrayList::new));
Map<String, List<ExcelData>> sub = bodyList.parallelStream()
.collect(Collectors.groupingBy(ExcelData::getpId));
bodyList.forEach(item -> item.setSubList(sub.get(item.getId())));
bodyList = bodyList.parallelStream()
.filter(item -> item.getpId().equals("root"))
.collect(Collectors.toList());
// 排序
//bodyList.sort(Comparator.comparing(ExcelData::getPermOrd));
return bodyList;
}
private static ExcelData getCellExcelData(Sheet sheet, int rowNum, int cellNum) {
Row row = sheet.getRow(rowNum);
ExcelData data;
boolean isMerge = isMergedRegion(sheet, rowNum, cellNum);
if (isMerge) {
data = getMergedRegionValue(sheet, rowNum, cellNum);
data.setId(UUID.randomUUID().toString());
} else {
Cell cell = row.getCell(cellNum);
data = new ExcelData();
data.setValue(getCellValueByCell(cell) + StringUtils.EMPTY);
data.setStartRow(rowNum);
data.setEndRow(rowNum);
data.setStartCell(cellNum);
data.setEndCell(cellNum);
data.setRowLength(1);
data.setCellLength(1);
data.setId(UUID.randomUUID().toString());
}
return data;
}
private static boolean isMergedRegion(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
return true;
}
}
}
return false;
}
private static ExcelData getMergedRegionValue(Sheet sheet, int row, int column) {
ExcelData data = new ExcelData();
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
data.setValue(getCellValueByCell(fCell) + StringUtils.EMPTY) ;
data.setStartRow(firstRow);
data.setEndRow(lastRow);
data.setStartCell(firstColumn);
data.setEndCell(lastColumn);
data.setRowLength(lastRow - firstRow + 1);
data.setCellLength(lastColumn - firstColumn + 1);
return data;
}
}
}
return new ExcelData();
}
//获取单元格各类型值,返回字符串类型
private static String getCellValueByCell(Cell cell) {
if (cell == null || cell.toString().trim().equals("")) {
return "";
}
String cellValue = "";
int cellType = cell.getCellType();
switch (cellType) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
if (0 == cell.getCellType()) {//判断单元格的类型是否则NUMERIC类型
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 判断是否为日期类型
Date date = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm");
cellValue = formater.format(date);
} else {
cellValue = cell.getNumericCellValue() + "";
}
}
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
cellValue = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
cellValue = cell.getCellFormula() + "";
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
cellValue = "";
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
}