package com.xx;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public class MyExcelUtil {
/**
* 合并单元格 （首行、最后一行、首列、最后一列）
* @param sheet
* @param firstRow
* @param lastRow
* @param firstCell
* @param lastCell
*/
public static void mergeCell(Sheet sheet, int firstRow, int lastRow, int firstCell, int lastCell ){
CellRangeAddress cra=new CellRangeAddress(firstRow, lastRow, firstCell, lastCell);
sheet.addMergedRegion(cra);
}
public static void copyCell(Cell originalCell, Cell newCell) {
newCell.setCellValue(originalCell.getStringCellValue());
CellStyle newCellStyle = originalCell.getCellStyle();
newCell.setCellStyle(newCellStyle);
}
/**
* 创建标题样式
* @param wb
* @return
*/
public static HSSFCellStyle createCellStyle_th(HSSFWorkbook wb) {
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 底部边框
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);// 底部边框颜色
// cellStyle.setBorderLeft(BorderStyle.MEDIUM_DASH_DOT_DOT); // 左边框
// cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框颜色
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setRightBorderColor(HSSFColor.BLACK.index);// 左边框颜色
// cellStyle.setBorderTop(BorderStyle.MEDIUM_DASH_DOT_DOT);
// cellStyle.setTopBorderColor(HSSFColor.BLACK.index);// 左边框颜色
// 设置背景色
cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
// cellStyle.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
// 设置填充样式
cellStyle.setFillPattern((short) 1);
//定义字体
Font font = wb.createFont();
// font.setFontName("宋体");//设置字体
font.setBold(true);//加粗
font.setFontHeightInPoints((short) 12);//设置字体大小
cellStyle.setFont(font);//设置单元格字体
cellStyle.setWrapText(true);//自动换行
return cellStyle;
}
/**
* 创建格子样式
* @param wb
* @return
*/
public static HSSFCellStyle createCellStyle_td(HSSFWorkbook wb) {
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 底部边框
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);// 底部边框颜色
// cellStyle.setBorderLeft(BorderStyle.MEDIUM_DASH_DOT_DOT); // 左边框
// cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框颜色
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setRightBorderColor(HSSFColor.BLACK.index);// 左边框颜色
// cellStyle.setBorderTop(BorderStyle.MEDIUM_DASH_DOT_DOT);
// cellStyle.setTopBorderColor(HSSFColor.BLACK.index);// 左边框颜色
// 设置背景色
cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
// cellStyle.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
// 设置填充样式
cellStyle.setFillPattern((short) 1);
//定义字体
Font font = wb.createFont();
// font.setFontName("宋体");//设置字体
font.setFontHeightInPoints((short) 12);//设置字体大小
cellStyle.setFont(font);//设置单元格字体
cellStyle.setWrapText(true);//自动换行
return cellStyle;
}
/**
* 如果当前行是空行，则创建当前行
*/
public static HSSFRow getNotNullRow(HSSFSheet sheet, int i) {
HSSFRow row = sheet.getRow(i);
if (row == null) {
row = sheet.createRow(i);
}
return row;
}
/**
* 如果当格子是空的，则创建当前格子
*/
public static HSSFCell getNotNullCell(HSSFRow row, int i) {
HSSFCell cell = row.getCell(i);
if (cell == null) {
cell = row.createCell(i);
}
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
return cell;
}
/**
* 如果当格子是空的，则创建当前格子
*/
public static HSSFCell getNotNullCell(HSSFWorkbook wb, HSSFRow row, int i) {
HSSFCell cell = row.getCell(i);
if (cell == null) {
cell = row.createCell(i);
HSSFCellStyle cellStyle = createCellStyle_th(wb);
cell.setCellStyle(cellStyle);
}
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
return cell;
}
/**
* 获取单元格的值 -不管格子的数据是何类型，自动返回相应的值
*
* @param cell
* @return
*/
public static String getCellValue(HSSFCell cell) {
if (cell == null)
return "";
if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
return cell.getStringCellValue();
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
return cell.getCellFormula();
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
return String.valueOf(cell.getNumericCellValue());
}
return "";
}
/**
* 设置整个sheet每个单元格宽度自适应
*
* @param sheet
* @throws Exception
*/
public static void setAutoWith(HSSFSheet sheet) throws Exception {
int maxColumn = sheet.getPhysicalNumberOfRows();
// 列宽自适应，只对英文和数字有效
for (int i = 0; i <= maxColumn; i++) {
sheet.autoSizeColumn(i);
}
}
/**
* @Title fullData
* @Description (填充数据)
* @return XSSFSheet
*
*/
public void fullData(XSSFSheet sheet,List<Map<String,Object>> data) {
XSSFRow row=null;
XSSFCell cell=null;
/*开始填入数据*/
if(data!=null){
Object dataTemp=null;
int colIndex,rowIndex;
float rowHeight;
for(Map<String,Object> dd:data){
colIndex=(Integer)dd.get("colIndex");
rowIndex=(Integer)dd.get("rowIndex");
row=sheet.getRow(rowIndex);
if(row==null)row=sheet.createRow(rowIndex);
if(dd.get("height")!=null){
rowHeight=(Float)dd.get("height");
row.setHeightInPoints(rowHeight);
}
cell=getCell(sheet,rowIndex,colIndex);
if(cell==null) {
cell=row.createCell(colIndex);
}
dataTemp=dd.get("value");
if(dataTemp.getClass().getName().equals("java.lang.String")){
if(dataTemp.toString().indexOf("`")==0){
cell.setCellFormula(dataTemp.toString().substring(1));
}else{
cell.setCellValue(String.valueOf(dataTemp));
}
}else{
cell.setCellValue(Double.parseDouble(String.valueOf(dataTemp)));
}
}
}
}
/**
* @Title getCell
* @Description (得到单元格)
* @return XSSFCell
*
* @date 2019年9月23日 下午5:23:20
*/
protected XSSFCell getCell(XSSFSheet sheet, int row, int col) {
XSSFRow sheetRow = sheet.getRow(row);
if (sheetRow == null) {
sheetRow = sheet.createRow(row);
}
XSSFCell cell = sheetRow.getCell(col);
if (cell == null) {
cell = sheetRow.createCell(col);
}
return cell;
}
/**
* @Title removeRow
* @Description (Remove a row by its rowIndex )
* @param sheet a Excel sheet
* @param rowIndex a 0 based index of removing row
* @return void
*
*/
public void removeRow(XSSFSheet sheet, int rowIndex) {
int lastRowNum=sheet.getLastRowNum();
if(rowIndex>=0&&rowIndex<lastRowNum) {
sheet.shiftRows(rowIndex+1,lastRowNum,-1,true,false);//将行号为rowIndex+1一直到行号为lastRowNum的单元格全部上移一行，以便删除rowIndex行
//sheet.shiftRows(startRow, endRow, n, copyRowHeight, resetOriginalRowHeight);
}else if(rowIndex==lastRowNum){
XSSFRow removingRow=sheet.getRow(rowIndex);
if(removingRow!=null)
sheet.removeRow(removingRow);
}
}
/**
* @Title insertRow
* @Description (insert a row by its rowIndex )
* @param sheet a Excel sheet
* @param rowIndex a 0 based index of adding row
* @return void
*
*/
public void insertRow(XSSFSheet sheet, int rowIndex) {
int lastRowNum=sheet.getLastRowNum();
XSSFRow row = sheet.getRow(rowIndex);
if(rowIndex>=0&&rowIndex<=lastRowNum)
sheet.shiftRows(rowIndex,lastRowNum,1,true,false);//将行号为rowIndex一直到行号为lastRowNum的单元格全部下移一行，以便添加rowIndex行
sheet.createRow(rowIndex);
if(rowIndex==lastRowNum+1){
sheet.createRow(rowIndex);
}
XSSFRow insertRow= sheet.getRow(rowIndex);
insertRow.setRowStyle(row.getRowStyle());
insertRow.setHeight(row.getHeight());
int lastCellNum = row.getLastCellNum();
for(int i=0;i<lastCellNum;i++) {
insertRow.createCell(i);
insertRow.getCell(i).setCellStyle(row.getCell(i).getCellStyle());
}
}
/**
* @Title removeMergedRegion
* @Description (删除指定区域)
* @return void
*/
@Deprecated
public void removeMergedRegion(XSSFSheet sheet,CellRangeAddress mr) {
int numMergedRegions = sheet.getNumMergedRegions();
List <Integer> removemrList=new ArrayList<Integer>();
for(int i=0;i<numMergedRegions;i++) {
CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
if(mr.getFirstRow()==mergedRegion.getFirstRow()&&mr.getLastRow()==mergedRegion.getLastRow()&&mr.getFirstColumn()==mergedRegion.getFirstColumn()&&mr.getLastColumn()==mergedRegion.getLastColumn()) {
removemrList.add(i);
}
}
for (int i=removemrList.size()-1;i>=0;i--) {
sheet.removeMergedRegion(removemrList.get(i));
}
}
/**
* @Title removeMergedRegionIn
* @Description (删除该区域中的所有合并)
* @return void
*
* @date 2019年9月23日 下午3:21:34
*/
@Deprecated
public void removeMergedRegionIn(XSSFSheet sheet,CellRangeAddress mr) {
int numMergedRegions = sheet.getNumMergedRegions();
List <Integer> removemrList=new ArrayList<Integer>();
for(int i=0;i<numMergedRegions;i++) {
CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
if(mr.getFirstRow()<=mergedRegion.getFirstRow()&&mr.getLastRow()>=mergedRegion.getLastRow()&&mr.getFirstColumn()<=mergedRegion.getFirstColumn()&&mr.getLastColumn()>=mergedRegion.getLastColumn()) {
removemrList.add(i);
}
}
for (int i=removemrList.size()-1;i>=0;i--) {
sheet.removeMergedRegion(removemrList.get(i));
}
}
/**
* @Title removeMergedRegion
* @Description (在删除一行时，删除并修改合并)
* @return void
*
*/
public void removeMergedRegion(XSSFSheet sheet,int rowIndex) {
List<CellRangeAddress> mergedRegionList=getSheetMergedRegionList(sheet);
this.sheetClearMergedRegion(sheet);
List<CellRangeAddress> addmrList=new ArrayList<CellRangeAddress>();
for(int i=0;i<mergedRegionList.size();i++) {
CellRangeAddress mergedRegion = mergedRegionList.get(i);
if(mergedRegion.getFirstRow()==rowIndex&&mergedRegion.getLastRow()==rowIndex) {
}else if(mergedRegion.getFirstRow()<=rowIndex&&mergedRegion.getLastRow()>=rowIndex) {
mergedRegion.setLastRow(mergedRegion.getLastRow()-1);
addmrList.add(mergedRegion);
}else if(mergedRegion.getFirstRow()>rowIndex&&mergedRegion.getLastRow()>rowIndex) {
mergedRegion.setFirstRow(mergedRegion.getFirstRow()-1);
mergedRegion.setLastRow(mergedRegion.getLastRow()-1);
addmrList.add(mergedRegion);
}else {
addmrList.add(mergedRegion);
}
}
//重新添加合并
for (CellRangeAddress cellRangeAddress : addmrList) {
sheet.addMergedRegion(cellRangeAddress);
}
}
/**
* @Title insertMergedRegion
* @Description (插入一行时，插入并修改合并，在当前rowIndex上添加，原有行自动被移到下一行)
* @return void
*
*/
public void insertMergedRegion(XSSFSheet sheet,int rowIndex) {
List<CellRangeAddress> mergedRegionList=getSheetMergedRegionList(sheet);
this.sheetClearMergedRegion(sheet);
List<CellRangeAddress> addmrList=new ArrayList<CellRangeAddress>();
for(int i=0;i<mergedRegionList.size();i++) {
CellRangeAddress mergedRegion = mergedRegionList.get(i);
if(mergedRegion.getFirstRow()==rowIndex&&mergedRegion.getLastRow()==rowIndex) {
addmrList.add(mergedRegion);
addmrList.add(new CellRangeAddress(mergedRegion.getFirstRow()+1,mergedRegion.getLastRow()+1,mergedRegion.getFirstColumn(),mergedRegion.getLastColumn()));
}else if(mergedRegion.getFirstRow()<=rowIndex&&mergedRegion.getLastRow()>=rowIndex) {
mergedRegion.setLastRow(mergedRegion.getLastRow()+1);
addmrList.add(mergedRegion);
}else if(mergedRegion.getFirstRow()>rowIndex&&mergedRegion.getLastRow()>rowIndex) {
mergedRegion.setFirstRow(mergedRegion.getFirstRow()+1);
mergedRegion.setLastRow(mergedRegion.getLastRow()+1);
addmrList.add(mergedRegion);
}else {
addmrList.add(mergedRegion);
}
}
//重新添加合并
for (CellRangeAddress cellRangeAddress : addmrList) {
sheet.addMergedRegion(cellRangeAddress);
}
}
/**
* @Title sheetClearMergedRegion
* @Description (删除sheet中的所有合并区域)
* @return void
*
*/
public void sheetClearMergedRegion(XSSFSheet sheet) {
int numMergedRegions=sheet.getNumMergedRegions();
for(int i=0;i<numMergedRegions;i++) {
sheet.removeMergedRegion(0);
}
}
/**
* @Title sheetAddMergedRegionList
* @Description (向sheet中添加合并集合)
* @return void
*/
public void sheetAddMergedRegionList(XSSFSheet sheet,List<CellRangeAddress> mergedRegionList) {
for(int i=0;i<mergedRegionList.size();i++) {
CellRangeAddress cellRangeAddress = mergedRegionList.get(i);
sheet.addMergedRegion(cellRangeAddress);
}
}
/**
* @Title getSheetMergedRegionList
* @Description (得到该sheet的合并信息)
* @return List<CellRangeAddress>
*
*/
public List<CellRangeAddress> getSheetMergedRegionList(XSSFSheet sheet) {
List<CellRangeAddress> mergedRegionList=new ArrayList<CellRangeAddress>();
int numMergedRegions = sheet.getNumMergedRegions();
for(int i=0;i<numMergedRegions;i++) {
CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
mergedRegionList.add(mergedRegion.copy());
}
return mergedRegionList;
}
/**
* @Title removeDataValidation
* @Description (删除一个行时，删除并修改验证)
* @return void
*
*/
/* public void removeDataValidation(XSSFSheet sheet,int rowIndex) throws Exception {
ReflectHelper reflect=new ReflectHelper();
CTWorksheet worksheet=(CTWorksheet) reflect.getValueByFieldName(sheet, "worksheet");
CTDataValidations dataValidations = worksheet.getDataValidations();
// List <Integer> removemrList=new ArrayList<Integer>();
Node domNode = dataValidations.getDomNode();
NodeList childNodes = domNode.getChildNodes();
int length = childNodes.getLength();
for (int i = 0; i < length; i++) {
Node item = childNodes.item(i);
NamedNodeMap attrs = item.getAttributes();
boolean flag=false;
String linkedRegion="";
for(int j=0;j<attrs.getLength();j++) {
Node item2 = attrs.item(j);
if("type".equals(item2.getNodeName())&&"list".equals(item2.getNodeValue())) {
String str = item.getFirstChild().getFirstChild().getNodeValue();
if(str.indexOf("INDIRECT")>-1) {
flag=true;
linkedRegion=str.substring(str.indexOf("(")+1, str.indexOf(")"));
}
}
if("sqref".equals(item2.getNodeName())) {
String nodeValue = item2.getNodeValue();
List<int[]> regions = this.stringToRegions(nodeValue);
int k=regions.size();
for (int m=0;m<k;m++) {
int[] region = regions.get(m);
if(region[0]==rowIndex&®ion[1]==rowIndex) {
regions.remove(m);
m--;
k--;
if(flag&&m==0) {
linkedRegion="";
}
}else if(region[0]<=rowIndex&®ion[1]>=rowIndex) {
region[1]=region[1]-1;
if(flag&&m==0) {
linkedRegion=linkedRegion.replaceAll("[0-9]*$", "")+(region[0]+1);
}
}else if(region[0]>rowIndex&®ion[1]>rowIndex) {
region[0]=region[0]-1;
region[1]=region[1]-1;
if(flag&&m==0) {
linkedRegion=linkedRegion.replaceAll("[0-9]*$", "")+(region[0]+1);
}
}
}
if(regions.isEmpty()) {
//domNode.removeChild(item);
}else {
String regionsStr = this.regionsToString(regions);
item2.setNodeValue(regionsStr);
}
if(flag) {
item.getFirstChild().getFirstChild().setNodeValue("INDIRECT("+linkedRegion+")");;
}
}
}
}
dataValidations = worksheet.getDataValidations();
reflect.setValueByFieldName(sheet, "worksheet", worksheet);
}*/
/**
* @Title insertDataValidation
* @Description (插入一个行时，添加并修改验证，在当前rowIndex上添加，原有行自动被移到下一行)
* @return void
*
*/
/* public void insertDataValidation(XSSFSheet sheet,int rowIndex) throws Exception {
ReflectHelper reflect=new ReflectHelper();
CTWorksheet worksheet=(CTWorksheet) reflect.getValueByFieldName(sheet, "worksheet");
CTDataValidations dataValidations = worksheet.getDataValidations();
// List <Integer> removemrList=new ArrayList<Integer>();
Node domNode = dataValidations.getDomNode();
NodeList childNodes = domNode.getChildNodes();
int length = childNodes.getLength();
for (int i = 0; i < length; i++) {
Node item = childNodes.item(i);
NamedNodeMap attrs = item.getAttributes();
boolean flag=false;
String linkedRegion="";
for(int j=0;j<attrs.getLength();j++) {
Node item2 = attrs.item(j);
if("type".equals(item2.getNodeName())&&"list".equals(item2.getNodeValue())) {
String str = item.getFirstChild().getFirstChild().getNodeValue();
if(str.indexOf("INDIRECT")>-1) {
flag=true;
linkedRegion=str.substring(str.indexOf("(")+1, str.indexOf(")"));
}
}
if("sqref".equals(item2.getNodeName())) {
String nodeValue = item2.getNodeValue();
List<int[]> regions = this.stringToRegions(nodeValue);
int k=regions.size();
for (int m=0;m<k;m++) {
int[] region = regions.get(m);
if(region[0]==rowIndex&®ion[1]==rowIndex) {
regions.add(new int[] {region[0]+1,region[1]+1,region[2],region[3]});
if(flag&&m==0) {
//linkedRegion=linkedRegion;
}
}else if(region[0]<=rowIndex&®ion[1]>=rowIndex) {
region[1]=region[1]+1;
if(flag&&m==0) {
//linkedRegion=linkedRegion;
}
}else if(region[0]>rowIndex&®ion[1]>rowIndex) {
region[0]=region[0]+1;
region[1]=region[1]+1;
if(flag&&m==0) {
linkedRegion=linkedRegion.replaceAll("[0-9]*$", "")+(region[0]+1);
}
}
}
if(regions.isEmpty()) {
//domNode.removeChild(item);
}else {
String regionsStr = this.regionsToString(regions);
item2.setNodeValue(regionsStr);
}
if(flag) {
item.getFirstChild().getFirstChild().setNodeValue("INDIRECT("+linkedRegion+")");;
}
}
}
}
dataValidations = worksheet.getDataValidations();
reflect.setValueByFieldName(sheet, "worksheet", worksheet);
}*/
public int charToNum(char a) {
return a-65;
}
public char numToChar(int a) {
return (char) (a+65);
}
/**
* @Title getColNum
* @Description (字母列号转数字)
* @return int
*
*/
public int getColNum(String col) {
int num=0;
char[] charArray = col.toCharArray();
for (int i = 0; i < charArray.length; i++) {
num=num*26+this.charToNum(charArray[i])+1;
}
return num-1;
}
/**
* @Title getColString
* @Description (数字转字母列号)
* @return String
*
*/
public String getColString(int num) {
String col="";
int count = 1;
num=num+1;
while((int)(num/Math.pow(26, count-1))>0){
int temp = ((int)(num % Math.pow(26,count)))/((int)Math.pow(26, count-1));
col = this.numToChar(temp-1) + col;
count++;
}
return col;
}
/**
* @Title stringToRegions
* @Description (字符串转区域，返回一个区域集合，int[] 为 int firstRow, int lastRow, int firstCol, int lastCol)
* @return List<int[]>
*
*/
public List<int []> stringToRegions(String str){
List<int []> regions=new ArrayList<int[]>();
String[] split = str.split(" ");
int firstRow=0;
int lastRow=0;
int firstCol=0;
int lastCol=0;
String tempStr="";
for (int i = 0; i < split.length; i++) {
int[] arr=new int[4];
if(split[i].contains(":")) {
String[] split2 = split[i].split(":");
tempStr=split2[0].replaceAll("^[A-Z]*", "");
firstRow=Integer.parseInt(tempStr)-1;
tempStr=split2[0].replaceAll("[0-9]*$", "");
firstCol=this.getColNum(tempStr);
tempStr=split2[1].replaceAll("^[A-Z]*", "");
lastRow=Integer.parseInt(tempStr)-1;
tempStr=split2[1].replaceAll("[0-9]*$", "");
lastCol=this.getColNum(tempStr);
}else {
tempStr=split[i].replaceAll("^[A-Z]*", "");
firstRow=Integer.parseInt(tempStr)-1;
tempStr=split[i].replaceAll("[0-9]*$", "");
firstCol=this.getColNum(tempStr);
lastRow=firstRow;
lastCol=firstCol;
}
arr[0]=firstRow;
arr[1]=lastRow;
arr[2]=firstCol;
arr[3]=lastCol;
regions.add(arr);
}
return regions;
}
/**
* @Title regionsToString
* @Description (区域转字符串)
* @return String
*
*/
public String regionsToString(List<int[]> regions) {
String str="";
for(int i=0;i<regions.size();i++) {
if(i>0) {
str+=" ";
}
int[] region = regions.get(i);
if(region[0]==region[1]&®ion[2]==region[3]) {
str+=this.getColString(region[2])+(region[0]+1);
}else {
str+=this.getColString(region[2])+(region[0]+1)+":"+this.getColString(region[3])+(region[1]+1);
}
}
return str;
}
/**
* @Title getCopyMergedRegionList
* @Description (复制合并区域)
* @return List<CellRangeAddress>
*
*/
public List<CellRangeAddress> getCopyMergedRegionList(XSSFSheet sheet) {
List<CellRangeAddress> mergedRegionList=new ArrayList<CellRangeAddress>();
int numMergedRegions = sheet.getNumMergedRegions();
for(int i=0;i<numMergedRegions;i++) {
CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
mergedRegionList.add(mergedRegion.copy());
}
return mergedRegionList;
}
}
浙公网安备 33010602011771号