已完成
项目地址
package xxx.common.utils;
import xxx.common.entity.ExcelCell;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.PaneInformation;
import org.apache.poi.xssf.usermodel.*;
import java.util.*;
import static org.apache.poi.ss.usermodel.CellType.*;
public class ExcelUtil {
public static ExcelCell getMergedRegionEntity(Sheet sheet , int row , int column){
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);
ExcelCell cell = new ExcelCell();
cell.setMergedRegion(true);
cell.setFirstColumn(firstColumn);
cell.setLastColumn(lastColumn);
cell.setFirstRow(firstRow);
cell.setLastRow(lastRow);
cell.setValue(getCellValue(fCell));
return cell;
}
}
}
return null ;
}
public static boolean isMergedRegion(Sheet sheet , int row , int column){
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){
return true ;
}
}
}
return false ;
}
public static String getCellValue(Cell cell){
if(cell == null) return "";
if(cell.getCellType() == STRING){
return cell.getStringCellValue();
}else if(cell.getCellType() == BOOLEAN){
return String.valueOf(cell.getBooleanCellValue());
}else if(cell.getCellType() == FORMULA){
return cell.getCellFormula() ;
}else if(cell.getCellType() == NUMERIC){
return String.valueOf(cell.getNumericCellValue());
}
return "";
}
public static Integer getCellRowIndex(String rowName) throws Exception {
Integer A_charAt = Integer.valueOf('A');
Integer Z_charAt = Integer.valueOf('Z');
Integer full = Z_charAt - A_charAt;
char[] codeList = rowName.toUpperCase().toCharArray();
Integer offset = A_charAt - 1;
int num = 0;
for (int i=0; i<codeList.length; i++) {
char chr = codeList[i];
Integer chr_charAt = Integer.valueOf(chr);
if (chr_charAt < A_charAt
|| chr_charAt > Z_charAt) {
throw new Exception("非法的单元格位置!");
}
num += (chr_charAt - offset) + full * i;
}
return num;
}
public static Map<String, String> splitSingleCellAddr(String cellAddr) {
String x = new String(cellAddr);
String y = new String(cellAddr);
x = x.replaceAll("\\d+", "");
y = y.replaceAll("[a-zA-Z]+", "");
Map<String, String> res = new HashMap<>();
res.put("x", x);
res.put("y", y);
return res;
}
public static XSSFWorkbook xls2xlsx(HSSFWorkbook xls) {
int sheetNum = xls.getNumberOfSheets();
XSSFWorkbook newXlsx = new XSSFWorkbook();
for (int i=0; i<sheetNum; i++) {
HSSFSheet oldSheet = xls.getSheetAt(i);
XSSFSheet newSheet = newXlsx.createSheet(oldSheet.getSheetName());
oldSheet.getActiveCell();
if (xls.isSheetHidden(i)) {
newXlsx.setSheetHidden(i, true);
}
PaneInformation paneInformation = oldSheet.getPaneInformation();
if (!Objects.isNull(paneInformation)) {
newSheet.createFreezePane(paneInformation.getVerticalSplitPosition(), paneInformation.getHorizontalSplitPosition(),
paneInformation.getVerticalSplitLeftColumn(), paneInformation.getHorizontalSplitTopRow());
}
int[] oldColumnBreaks = oldSheet.getColumnBreaks();
for (int j : oldColumnBreaks) {
newSheet.setColumnBreak(j);
}
newSheet.setDefaultRowHeightInPoints(oldSheet.getDefaultRowHeightInPoints());
newSheet.setDefaultRowHeight(oldSheet.getDefaultRowHeight());
newSheet.setDefaultColumnWidth(oldSheet.getDefaultColumnWidth());
List<CellRangeAddress> mergeRegins = oldSheet.getMergedRegions();
for (int j = 0; j < mergeRegins.size(); j++) {
newSheet.addMergedRegion(mergeRegins.get(j));
}
XSSFDataValidationHelper helper = new XSSFDataValidationHelper(newSheet);
List<HSSFDataValidation> validationList = oldSheet.getDataValidations();
for (HSSFDataValidation item : validationList) {
CellRangeAddressList cellRangeAddressList = item.getRegions();
DataValidationConstraint dvConstraint = item.getValidationConstraint();
XSSFDataValidationConstraint dv = copyDataValidationConstraint(dvConstraint);
DataValidation dataValidation = helper.createValidation(dv, cellRangeAddressList);
newSheet.addValidationData(dataValidation);
}
for (int j = 0; j <= oldSheet.getLastRowNum(); j++) {
HSSFRow row = oldSheet.getRow(j);
if(row != null){
XSSFRow newRow = newSheet.getRow(j);
if (Objects.isNull(newRow)) newRow = newSheet.createRow(j);
newRow.setHeight(row.getHeight());
newRow.setHeightInPoints(row.getHeightInPoints());
newRow.setZeroHeight(row.getZeroHeight());
XSSFCellStyle newStyle = newXlsx.createCellStyle();
if (!Objects.isNull(row.getRowStyle())) {
HSSFFont oldFont = row.getRowStyle().getFont(xls);
if (!Objects.isNull(oldFont)) {
XSSFFont newFont = newXlsx.createFont();
transXSSFFont(oldFont, newFont);
newStyle.setFont(newFont);
}
}
transXSSFStyle(row.getRowStyle(), newStyle);
newRow.setRowStyle(newStyle);
for (int k = oldSheet.getRow(j).getFirstCellNum(); k < oldSheet.getRow(j).getLastCellNum(); k++) {
newSheet.setColumnWidth(k, oldSheet.getColumnWidth(k));
HSSFCell cell = row.getCell(k);
if (cell == null) {
continue;
}
XSSFCell newCell = newRow.getCell(k);
if (oldSheet.isColumnHidden(k)) {
newSheet.setColumnHidden(k, true);
}
if (Objects.isNull(newCell)) newCell = newRow.createCell(k);
XSSFCellStyle newCellStyle = newXlsx.createCellStyle();
transXSSFStyle(cell.getCellStyle(), newCellStyle);
if (!Objects.isNull(cell.getCellStyle())) {
HSSFFont oldFont = cell.getCellStyle().getFont(xls);
if (!Objects.isNull(oldFont)) {
XSSFFont newFont = newXlsx.createFont();
transXSSFFont(oldFont, newFont);
newCellStyle.setFont(newFont);
}
}
newCell.setCellStyle(newCellStyle);
XSSFComment newComment = copyComment(cell, newSheet.createDrawingPatriarch());
newCell.setCellComment(newComment);
newCell.setHyperlink(cell.getHyperlink());
newCell.setCellType(cell.getCellType());
switch (cell.getCellType()) {
case STRING:
newCell.setCellValue(cell.getRichStringCellValue().getString());
break;
case NUMERIC:
if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
newCell.setCellValue(cell.getDateCellValue());
} else {
newCell.setCellValue(cell.getNumericCellValue());
}
break;
case BOOLEAN:
newCell.setCellValue(cell.getBooleanCellValue());
break;
case FORMULA:
newCell.setCellFormula(cell.getCellFormula());
break;
case ERROR:
newCell.setCellErrorValue(cell.getErrorCellValue());
default:
newCell.setCellValue("");
break;
}
}
}
}
}
return newXlsx;
}
private static void transXSSFFont(HSSFFont oldFont, XSSFFont newFont) {
newFont.setBold(oldFont.getBold());
newFont.setFontName(oldFont.getFontName());
newFont.setColor(oldFont.getColor());
newFont.setFontHeightInPoints(oldFont.getFontHeightInPoints());
newFont.setItalic(oldFont.getItalic());
newFont.setCharSet(oldFont.getCharSet());
newFont.setFontHeight(oldFont.getFontHeight());
newFont.setStrikeout(oldFont.getStrikeout());
newFont.setTypeOffset(oldFont.getTypeOffset());
newFont.setUnderline(oldFont.getUnderline());
}
private static XSSFComment copyComment(HSSFCell cell, XSSFDrawing newDrawing) {
HSSFComment oldComment = cell.getCellComment();
if (Objects.isNull(oldComment)) return null;
ClientAnchor anchor = new XSSFClientAnchor();
anchor.setDx1(0);
anchor.setDx2(0);
anchor.setDy1(0);
anchor.setDy2(0);
anchor.setCol1(cell.getColumnIndex());
anchor.setRow1(cell.getRowIndex());
anchor.setCol2(cell.getColumnIndex() + 5);
anchor.setRow2(cell.getRowIndex() + 6);
XSSFComment comment = newDrawing.createCellComment(anchor);
HSSFRichTextString str = oldComment.getString();
XSSFRichTextString newStr = copyRichTextString(str);
comment.setString(newStr);
return comment;
}
private static XSSFRichTextString copyRichTextString(HSSFRichTextString str) {
XSSFRichTextString newStr = new XSSFRichTextString();
newStr.setString(str.getString());
return newStr;
}
private static XSSFDataValidationConstraint copyDataValidationConstraint(DataValidationConstraint dvConstraint) {
System.out.println(dvConstraint.getExplicitListValues());
XSSFDataValidationConstraint dv = new XSSFDataValidationConstraint(dvConstraint.getExplicitListValues());
dv.setOperator(dvConstraint.getOperator());
if (!Objects.isNull(dvConstraint.getFormula1())) {
dv.setFormula1(dvConstraint.getFormula1());
}
if (!Objects.isNull(dvConstraint.getFormula2())) {
dv.setFormula2(dvConstraint.getFormula2());
}
return dv;
}
public static void transXSSFStyle(HSSFCellStyle oldStyle, XSSFCellStyle newStyle) {
if (Objects.isNull(oldStyle)) return ;
newStyle.setAlignment(oldStyle.getAlignment());
newStyle.setVerticalAlignment(oldStyle.getVerticalAlignment());
newStyle.setBorderBottom(oldStyle.getBorderBottom());
newStyle.setBorderLeft(oldStyle.getBorderLeft());
newStyle.setBorderRight(oldStyle.getBorderRight());
newStyle.setBorderTop(oldStyle.getBorderTop());
newStyle.setBottomBorderColor(oldStyle.getBottomBorderColor());
newStyle.setTopBorderColor(oldStyle.getTopBorderColor());
newStyle.setLeftBorderColor(oldStyle.getLeftBorderColor());
newStyle.setRightBorderColor(oldStyle.getRightBorderColor());
newStyle.setFillForegroundColor(oldStyle.getFillForegroundColor());
newStyle.setFillPattern(oldStyle.getFillPattern());
newStyle.setWrapText(oldStyle.getWrapText());
newStyle.setDataFormat(oldStyle.getDataFormat());
newStyle.setFillBackgroundColor(oldStyle.getFillForegroundColor());
newStyle.setHidden(oldStyle.getHidden());
newStyle.setIndention(oldStyle.getIndention());
newStyle.setLocked(oldStyle.getLocked());
newStyle.setQuotePrefixed(oldStyle.getQuotePrefixed());
newStyle.setRotation(oldStyle.getRotation());
newStyle.setShrinkToFit(oldStyle.getShrinkToFit());
}
}