Nemo

Nemo 关注TA

路漫漫其修远兮,吾将上下而求索。

Nemo

Nemo

关注TA

路漫漫其修远兮,吾将上下而求索。

  •  普罗旺斯
  • 负责帅就完事了
  • 写了1,493,291字

该文章投稿至Nemo社区   Java  板块 复制链接


ExcelUtils java Excel简单操作工具类

发布于 2017/11/02 16:14 5,034浏览 0回复 4,567


import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;

/**
* @Description: Excel工具类
* @author: Nemo
*/
public class ExcelUtils {

/**
* @Description: 根据列号获取对应的excel字母排序,如:A B C ... ... AA AB ... ...
* @param columnNum excel的列号,从1开始
* @return
*/
public static String getColumnName(int columnNum) {
int first;
int last;
String result = "";
if (columnNum > 256)
columnNum = 256;
first = columnNum / 27;
last = columnNum - (first * 26);

if (first > 0)
result = String.valueOf((char) (first + 64));

if (last > 0)
result = result + String.valueOf((char) (last + 64));

return result;
}

/**
* 将[excel列号]换成对应的XSSFCell处理类的index(从0开始)
*
* @param excelColumnName
* @return
*/
public static int getColumnIndex(String excelColumnName) {
int num = 0;
int result = 0;
excelColumnName = excelColumnName.toUpperCase();
int length = excelColumnName.length();
for (int i = 0; i < length; i++) {
char ch = excelColumnName.charAt(length - i - 1);
num = (int) (ch - 'A');
num *= Math.pow(26, i);
result += num;
}
return result;
}

/**
* 将[excel行号]转换成对应的XSSFRow处理类的index(从0开始)
*
* @param excelRowNumber
* @return
*/
public static int getRowIndex(int excelRowNumber) {
return excelRowNumber - 1;
}

/**
* 根据Excel行列号获取对应的行(2017版)
*
* @param sheet
* @param excelRowNumber Excel行号,如:1,2,3...
* @return
*/
public static XSSFRow getRow(XSSFSheet sheet, int excelRowNumber) {
XSSFRow row = sheet.getRow(getRowIndex(excelRowNumber));
if (row == null) {
row = sheet.createRow(getRowIndex(excelRowNumber));
}
return row;
}

/**
* 在给定的行号之后插入一行(2017版),复制该行的样式,并获取该行
*
* @param sheet
* @param startRowNumber
* @return
*/
public static XSSFRow shiftAndCopyRowStyle(XSSFSheet sheet, int startRowNumber) {
sheet.shiftRows(startRowNumber, sheet.getLastRowNum(), 1);
XSSFRow newRow = getRow(sheet, startRowNumber + 1);
XSSFRow lastRow = getRow(sheet, startRowNumber);
newRow.setRowStyle(lastRow.getRowStyle());
newRow.setHeight(lastRow.getHeight());

// 样式复制
for (Iterator<Cell> cellIt = lastRow.cellIterator(); cellIt.hasNext();) {
XSSFCell lastCell = (XSSFCell) cellIt.next();
XSSFCell newCell = newRow.createCell(lastCell.getColumnIndex());
XSSFCellStyle newstyle = sheet.getWorkbook().createCellStyle();
newstyle.cloneStyleFrom(lastCell.getCellStyle());
newCell.setCellStyle(newstyle);
}

// 合并单元格的处理
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress cellRangeAddress = sheet.getMergedRegion(i);
if (cellRangeAddress.getFirstRow() == lastRow.getRowNum()) {
CellRangeAddress newCellRangeAddress =
new CellRangeAddress(newRow.getRowNum(),
(newRow.getRowNum() + (cellRangeAddress.getFirstRow() - cellRangeAddress.getLastRow())),
cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
sheet.addMergedRegion(newCellRangeAddress);
}
}

return newRow;
}

/**
* 根据Excel行、列号获取对应的单元格(2017版)
*
* @param sheet
* @param excelRowNumber Excel行号,如:1,2,3...
* @param excelColumnName Excel列号,如:A,B...AA,AB...
* @return
*/
public static XSSFCell getCell(XSSFSheet sheet, int excelRowNumber, String excelColumnName) {
return getCell(getRow(sheet, excelRowNumber), excelColumnName);
}

/**
* 根据Excel列号获取对应的单元格(2017版)
*
* @param row
* @param excelColumnName Excel列号,如:A,B...AA,AB...
* @return
*/
public static XSSFCell getCell(XSSFRow row, String excelColumnName) {
XSSFCell cell = row.getCell(getColumnIndex(excelColumnName));
if (cell == null) {
cell = row.createCell(getColumnIndex(excelColumnName));
}
return cell;
}

public static void main(String[] args) {
System.out.println(getColumnIndex("AF"));
System.out.println(getColumnName(1));
}

}
点赞(0)
点了个评