Java读写Excel实例分享

Java读写Excel实例分享

话不多说，请看代码：

ExcelUtil.java

package pers.kangxu.datautils.utils;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.Iterator;

import java.util.List;

import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFFont;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.CellValue;

import org.apache.poi.ss.usermodel.Font;

import org.apache.poi.ss.usermodel.FormulaEvaluator;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.ss.util.CellRangeAddress;

/**

*

*

* excel 工具

*

* @author kangxu

*

*/

public class ExcelUtil {

/**

* 导出 excel

* @param filePath 文件全路径

* @param sheetName sheet页名称

* @param sheetIndex 当前sheet下表 从0开始

* @param fileHeader 头部

* @param datas 内容

*/

public static void writeExcel(String filePath,String sheetName,

int sheetIndex,

String[] fileHeader,

List datas){

// 创建工作簿

Workbook wb = new HSSFWorkbook();

// 创建工作表 sheet

Sheet s = wb.createSheet();

wb.setSheetName(sheetIndex, sheetName);

Row r = s.createRow(0);

Cell c = null;

Font font = null;

CellStyle styleHeader = null;

CellStyle styleContent = null;

//粗体

font = wb.createFont();

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

// 设置头样式

styleHeader = wb.createCellStyle();

styleHeader.setFont(font);

styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框

styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框

styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框

styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

// 设置内容样式

styleContent = wb.createCellStyle();

styleContent.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框

styleContent.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框

styleContent.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框

styleContent.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

//设置头

for(int i=0;i

c = r.createCell(i);

c.setCellStyle(styleHeader);

c.setCellValue(fileHeader[i]);

i++;

}

//设置内容

for(int rownum=0;rownum

mCpPRXYDqF r = s.createRow(rownum+1); //创建行

for(int cellnum=0;cellnum

c = r.createCell(cellnum);

mCpPRXYDqF c.setCellValue(datas.get(rownum)[cellnum]);

c.setCellStyle(styleContent);

cellnum++;

}

rownum++;

}

FileOutputStream out = null;

try {

// 创建文件或者文件夹,将内容写进去

if(FileUtil.createFile(new File(filePath))){

out = new FileOutputStream(filePath);

wb.write(out);

}

} catch (Exception e) {

e.printStackTrace();

}finally {

try {

// 关闭流

if(out != null){

out.flush();

out.close();

}

} catch (IOException e) {

e.printStackTrace();

}

}

}

/**

* 读取 excel 文件内容

* @param filePath

* @param sheetIndex

*/

public static List

List

// 头

List list = new ArrayList();

//

int cnt = 0;

int idx = 0;

try {

InputStream input = new FileInputStream(filePath); //建立输入流

Workbook wb = null;

wb = new HSSFWorkbook(input);

// 获取sheet页

Sheet sheet = wb.getSheetAt(sheetIndex);

Iterator rows = sheet.rowIterator();

while (rows.hasNext()) {

Row row = rows.next();

Iterator cells = row.cellIterator();

Map map = new HashMap();

if(cnt == 0){ // 将头放进list中

while (cells.hasNext()) {

Cell cell = cells.next();

if(isContainMergeCell(sheet)){

cancelMergeCell(sheet);

}

list.add(getStringCellValue(cell));

}

cnt ++;

continue;

}else {

while (cells.hasNext()) {

Cell cell = cells.next();

if(isContainMergeCell(sheet)){

cancelMergeCell(sheet);

}

// 区别相同的头

list = ListUtil.changeSameVal(list);

map.put(list.get(idx++), getStringCellValue(cell));

}

}

idx = 0;

mapList.add(map);

}

return mapList;

} catch (IOException ex) {

ex.printStackTrace();

}

return null;

}

/**

* 合并单元格

* @param sheet 当前sheet页

* @param firstRow 开始行

* @param lastRow 结束行

* @param firstCol 开始列

* @param lastCol 结束列

*/

public static int mergeCell(Sheet sheet,int firstRow,int lastRow,int firstCol,int lastCol){

if(sheet == null){

return -1;

}

return sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));

}

/**

* 取消合并单元格

* @param sheet

* @param idx

*/

public static void cancelMergeCell(Sheet sheet){

int sheetMergeCount = sheet.getNumMergedRegions();

for(int idx = 0; idx < sheetMergeCount;){

CellRangeAddress range = sheet.getMergedRegion(idx);

String val = getMergeCellValue(sheet,range.getFirstRow(),range.getLastRow());

// 取消合并单元格

sheet.removeMergedRegion(idx);

for(int rownum=range.getFirstRow();rownum

for(int cellnum=range.getFirstColumn();cellnum

sheet.getRow(rownum).getCell(cellnum).setCellValue(val);

cellnum ++;

}

rownum ++;

}

idx++;

}

}

/**

* 判断指定单元格是否是合并单元格

* @param sheet 当前sheet页

* @param firstRow 开始行

* @param lastRow 结束行

* @param firstCol 开始列

* @param lastCol 结束列

* @return

*/

public static boolean isMergeCell(Sheet sheet,

int row ,int column){

int sheetMergeCount = sheet.getNumMergedRegions();

for(int i = 0; i < sheetMergeCount;){

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;

}

}

i++;

}

return false;

}

/**

* 判断sheet页中是否含有合并单元格

* @param sheet

* @return

*/

public static boolean isContainMergeCell(Sheet sheet){

if(sheet == null){

return false;

}

return sheet.getNumMergedRegions()>0 ? true : false;

}

/**

* 获取指定合并单元的值

* @param sheet

* @param row

* @param column

* @return

*/

public static String getMergeCellValue(Sheet sheet,

int row ,int column){

int sheetMergeCount = sheet.getNumMergedRegions();

for(int i = 0; i < sheetMergeCount;){

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){

Row fRow = sheet.getRow(firstRow);

Cell fCell = fRow.getCell(firstColumn);

return getStringCellValue(fCell) ;

}

}

i++;

}

return null;

}

/**

* 获取单元格的值

* @param cell

* @return

*/

public static String getStringCellValue(Cell cell) {

String strCell = "";

if(cell==null) return strCell;

switch (cell.getCellType()) {

case Cell.CELL_TYPE_STRING:

strCell = cell.getRichStringCellValue().getString().trim();

break;

case Cell.CELL_TYPE_NUMERIC:

strCell = String.valueOf(cell.getNumericCellValue());

break;

case Cell.CELL_TYPE_BOOLEAN:

strCell = String.valueOf(cell.getBooleanCellValue());

break;

case Cell.CELL_TYPE_FORMULA:

FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();

evaluator.evaluateFormulaCell(cell);

CellValue cellValue = evaluator.evaluate(cell);

strCell = String.valueOf(cellValue.getNumberValue()) ;

break;

default:

strCell = "";

}

return strCell;

}

}

调用方式如下

ExcelUtilTester.java

package pers.kangxu.datautils.test;

import java.util.ArrayList;

import java.util.List;

import pers.kangxu.datautils.utils.ExcelUtil;

public class ExcelUtilTester {

public static void main(String[] args) {

List datas = new ArrayList();

datas.add(new String[]{"狗熊","母","250"});

datas.add(new String[]{"猪粮","不明","251"});

//ExcelUtil.writeExcel("C:\\Users\\Administrator\\Desktop\\test\\test\\test.xls","sheet1",0, new String[]{"姓名","年龄","性别"}, datas);

System.out.println(ExcelUtil.readExcel("C:\\Users\\Administrator\\Desktop\\test\\test\\test.xls", 0));

}

}

c = r.createCell(i);

c.setCellStyle(styleHeader);

c.setCellValue(fileHeader[i]);

i++;

}

//设置内容

for(int rownum=0;rownum

mCpPRXYDqF r = s.createRow(rownum+1); //创建行

for(int cellnum=0;cellnum

c = r.createCell(cellnum);

mCpPRXYDqF c.setCellValue(datas.get(rownum)[cellnum]);

c.setCellStyle(styleContent);

cellnum++;

}

rownum++;

}

FileOutputStream out = null;

try {

// 创建文件或者文件夹,将内容写进去

if(FileUtil.createFile(new File(filePath))){

out = new FileOutputStream(filePath);

wb.write(out);

}

} catch (Exception e) {

e.printStackTrace();

}finally {

try {

// 关闭流

if(out != null){

out.flush();

out.close();

}

} catch (IOException e) {

e.printStackTrace();

}

}

}

/**

* 读取 excel 文件内容

* @param filePath

* @param sheetIndex

*/

public static List

List

// 头

List list = new ArrayList();

//

int cnt = 0;

int idx = 0;

try {

InputStream input = new FileInputStream(filePath); //建立输入流

Workbook wb = null;

wb = new HSSFWorkbook(input);

// 获取sheet页

Sheet sheet = wb.getSheetAt(sheetIndex);

Iterator rows = sheet.rowIterator();

while (rows.hasNext()) {

Row row = rows.next();

Iterator cells = row.cellIterator();

Map map = new HashMap();

if(cnt == 0){ // 将头放进list中

while (cells.hasNext()) {

Cell cell = cells.next();

if(isContainMergeCell(sheet)){

cancelMergeCell(sheet);

}

list.add(getStringCellValue(cell));

}

cnt ++;

continue;

}else {

while (cells.hasNext()) {

Cell cell = cells.next();

if(isContainMergeCell(sheet)){

cancelMergeCell(sheet);

}

// 区别相同的头

list = ListUtil.changeSameVal(list);

map.put(list.get(idx++), getStringCellValue(cell));

}

}

idx = 0;

mapList.add(map);

}

return mapList;

} catch (IOException ex) {

ex.printStackTrace();

}

return null;

}

/**

* 合并单元格

* @param sheet 当前sheet页

* @param firstRow 开始行

* @param lastRow 结束行

* @param firstCol 开始列

* @param lastCol 结束列

*/

public static int mergeCell(Sheet sheet,int firstRow,int lastRow,int firstCol,int lastCol){

if(sheet == null){

return -1;

}

return sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));

}

/**

* 取消合并单元格

* @param sheet

* @param idx

*/

public static void cancelMergeCell(Sheet sheet){

int sheetMergeCount = sheet.getNumMergedRegions();

for(int idx = 0; idx < sheetMergeCount;){

CellRangeAddress range = sheet.getMergedRegion(idx);

String val = getMergeCellValue(sheet,range.getFirstRow(),range.getLastRow());

// 取消合并单元格

sheet.removeMergedRegion(idx);

for(int rownum=range.getFirstRow();rownum

for(int cellnum=range.getFirstColumn();cellnum

sheet.getRow(rownum).getCell(cellnum).setCellValue(val);

cellnum ++;

}

rownum ++;

}

idx++;

}

}

/**

* 判断指定单元格是否是合并单元格

* @param sheet 当前sheet页

* @param firstRow 开始行

* @param lastRow 结束行

* @param firstCol 开始列

* @param lastCol 结束列

* @return

*/

public static boolean isMergeCell(Sheet sheet,

int row ,int column){

int sheetMergeCount = sheet.getNumMergedRegions();

for(int i = 0; i < sheetMergeCount;){

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;

}

}

i++;

}

return false;

}

/**

* 判断sheet页中是否含有合并单元格

* @param sheet

* @return

*/

public static boolean isContainMergeCell(Sheet sheet){

if(sheet == null){

return false;

}

return sheet.getNumMergedRegions()>0 ? true : false;

}

/**

* 获取指定合并单元的值

* @param sheet

* @param row

* @param column

* @return

*/

public static String getMergeCellValue(Sheet sheet,

int row ,int column){

int sheetMergeCount = sheet.getNumMergedRegions();

for(int i = 0; i < sheetMergeCount;){

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){

Row fRow = sheet.getRow(firstRow);

Cell fCell = fRow.getCell(firstColumn);

return getStringCellValue(fCell) ;

}

}

i++;

}

return null;

}

/**

* 获取单元格的值

* @param cell

* @return

*/

public static String getStringCellValue(Cell cell) {

String strCell = "";

if(cell==null) return strCell;

switch (cell.getCellType()) {

case Cell.CELL_TYPE_STRING:

strCell = cell.getRichStringCellValue().getString().trim();

break;

case Cell.CELL_TYPE_NUMERIC:

strCell = String.valueOf(cell.getNumericCellValue());

break;

case Cell.CELL_TYPE_BOOLEAN:

strCell = String.valueOf(cell.getBooleanCellValue());

break;

case Cell.CELL_TYPE_FORMULA:

FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();

evaluator.evaluateFormulaCell(cell);

CellValue cellValue = evaluator.evaluate(cell);

strCell = String.valueOf(cellValue.getNumberValue()) ;

break;

default:

strCell = "";

}

return strCell;

}

}

调用方式如下

ExcelUtilTester.java

package pers.kangxu.datautils.test;

import java.util.ArrayList;

import java.util.List;

import pers.kangxu.datautils.utils.ExcelUtil;

public class ExcelUtilTester {

public static void main(String[] args) {

List datas = new ArrayList();

datas.add(new String[]{"狗熊","母","250"});

datas.add(new String[]{"猪粮","不明","251"});

//ExcelUtil.writeExcel("C:\\Users\\Administrator\\Desktop\\test\\test\\test.xls","sheet1",0, new String[]{"姓名","年龄","性别"}, datas);

System.out.println(ExcelUtil.readExcel("C:\\Users\\Administrator\\Desktop\\test\\test\\test.xls", 0));

}

}

mCpPRXYDqF r = s.createRow(rownum+1); //创建行

for(int cellnum=0;cellnum

c = r.createCell(cellnum);

mCpPRXYDqF c.setCellValue(datas.get(rownum)[cellnum]);

c.setCellStyle(styleContent);

cellnum++;

}

rownum++;

}

FileOutputStream out = null;

try {

// 创建文件或者文件夹,将内容写进去

if(FileUtil.createFile(new File(filePath))){

out = new FileOutputStream(filePath);

wb.write(out);

}

} catch (Exception e) {

e.printStackTrace();

}finally {

try {

// 关闭流

if(out != null){

out.flush();

out.close();

}

} catch (IOException e) {

e.printStackTrace();

}

}

}

/**

* 读取 excel 文件内容

* @param filePath

* @param sheetIndex

*/

public static List

List

// 头

List list = new ArrayList();

//

int cnt = 0;

int idx = 0;

try {

InputStream input = new FileInputStream(filePath); //建立输入流

Workbook wb = null;

wb = new HSSFWorkbook(input);

// 获取sheet页

Sheet sheet = wb.getSheetAt(sheetIndex);

Iterator rows = sheet.rowIterator();

while (rows.hasNext()) {

Row row = rows.next();

Iterator cells = row.cellIterator();

Map map = new HashMap();

if(cnt == 0){ // 将头放进list中

while (cells.hasNext()) {

Cell cell = cells.next();

if(isContainMergeCell(sheet)){

cancelMergeCell(sheet);

}

list.add(getStringCellValue(cell));

}

cnt ++;

continue;

}else {

while (cells.hasNext()) {

Cell cell = cells.next();

if(isContainMergeCell(sheet)){

cancelMergeCell(sheet);

}

// 区别相同的头

list = ListUtil.changeSameVal(list);

map.put(list.get(idx++), getStringCellValue(cell));

}

}

idx = 0;

mapList.add(map);

}

return mapList;

} catch (IOException ex) {

ex.printStackTrace();

}

return null;

}

/**

* 合并单元格

* @param sheet 当前sheet页

* @param firstRow 开始行

* @param lastRow 结束行

* @param firstCol 开始列

* @param lastCol 结束列

*/

public static int mergeCell(Sheet sheet,int firstRow,int lastRow,int firstCol,int lastCol){

if(sheet == null){

return -1;

}

return sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));

}

/**

* 取消合并单元格

* @param sheet

* @param idx

*/

public static void cancelMergeCell(Sheet sheet){

int sheetMergeCount = sheet.getNumMergedRegions();

for(int idx = 0; idx < sheetMergeCount;){

CellRangeAddress range = sheet.getMergedRegion(idx);

String val = getMergeCellValue(sheet,range.getFirstRow(),range.getLastRow());

// 取消合并单元格

sheet.removeMergedRegion(idx);

for(int rownum=range.getFirstRow();rownum

for(int cellnum=range.getFirstColumn();cellnum

sheet.getRow(rownum).getCell(cellnum).setCellValue(val);

cellnum ++;

}

rownum ++;

}

idx++;

}

}

/**

* 判断指定单元格是否是合并单元格

* @param sheet 当前sheet页

* @param firstRow 开始行

* @param lastRow 结束行

* @param firstCol 开始列

* @param lastCol 结束列

* @return

*/

public static boolean isMergeCell(Sheet sheet,

int row ,int column){

int sheetMergeCount = sheet.getNumMergedRegions();

for(int i = 0; i < sheetMergeCount;){

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;

}

}

i++;

}

return false;

}

/**

* 判断sheet页中是否含有合并单元格

* @param sheet

* @return

*/

public static boolean isContainMergeCell(Sheet sheet){

if(sheet == null){

return false;

}

return sheet.getNumMergedRegions()>0 ? true : false;

}

/**

* 获取指定合并单元的值

* @param sheet

* @param row

* @param column

* @return

*/

public static String getMergeCellValue(Sheet sheet,

int row ,int column){

int sheetMergeCount = sheet.getNumMergedRegions();

for(int i = 0; i < sheetMergeCount;){

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){

Row fRow = sheet.getRow(firstRow);

Cell fCell = fRow.getCell(firstColumn);

return getStringCellValue(fCell) ;

}

}

i++;

}

return null;

}

/**

* 获取单元格的值

* @param cell

* @return

*/

public static String getStringCellValue(Cell cell) {

String strCell = "";

if(cell==null) return strCell;

switch (cell.getCellType()) {

case Cell.CELL_TYPE_STRING:

strCell = cell.getRichStringCellValue().getString().trim();

break;

case Cell.CELL_TYPE_NUMERIC:

strCell = String.valueOf(cell.getNumericCellValue());

break;

case Cell.CELL_TYPE_BOOLEAN:

strCell = String.valueOf(cell.getBooleanCellValue());

break;

case Cell.CELL_TYPE_FORMULA:

FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();

evaluator.evaluateFormulaCell(cell);

CellValue cellValue = evaluator.evaluate(cell);

strCell = String.valueOf(cellValue.getNumberValue()) ;

break;

default:

strCell = "";

}

return strCell;

}

}

调用方式如下

ExcelUtilTester.java

package pers.kangxu.datautils.test;

import java.util.ArrayList;

import java.util.List;

import pers.kangxu.datautils.utils.ExcelUtil;

public class ExcelUtilTester {

public static void main(String[] args) {

List datas = new ArrayList();

datas.add(new String[]{"狗熊","母","250"});

datas.add(new String[]{"猪粮","不明","251"});

//ExcelUtil.writeExcel("C:\\Users\\Administrator\\Desktop\\test\\test\\test.xls","sheet1",0, new String[]{"姓名","年龄","性别"}, datas);

System.out.println(ExcelUtil.readExcel("C:\\Users\\Administrator\\Desktop\\test\\test\\test.xls", 0));

}

}

c = r.createCell(cellnum);

mCpPRXYDqF c.setCellValue(datas.get(rownum)[cellnum]);

c.setCellStyle(styleContent);

cellnum++;

}

rownum++;

}

FileOutputStream out = null;

try {

// 创建文件或者文件夹,将内容写进去

if(FileUtil.createFile(new File(filePath))){

out = new FileOutputStream(filePath);

wb.write(out);

}

} catch (Exception e) {

e.printStackTrace();

}finally {

try {

// 关闭流

if(out != null){

out.flush();

out.close();

}

} catch (IOException e) {

e.printStackTrace();

}

}

}

/**

* 读取 excel 文件内容

* @param filePath

* @param sheetIndex

*/

public static List

List

// 头

List list = new ArrayList();

//

int cnt = 0;

int idx = 0;

try {

InputStream input = new FileInputStream(filePath); //建立输入流

Workbook wb = null;

wb = new HSSFWorkbook(input);

// 获取sheet页

Sheet sheet = wb.getSheetAt(sheetIndex);

Iterator rows = sheet.rowIterator();

while (rows.hasNext()) {

Row row = rows.next();

Iterator cells = row.cellIterator();

Map map = new HashMap();

if(cnt == 0){ // 将头放进list中

while (cells.hasNext()) {

Cell cell = cells.next();

if(isContainMergeCell(sheet)){

cancelMergeCell(sheet);

}

list.add(getStringCellValue(cell));

}

cnt ++;

continue;

}else {

while (cells.hasNext()) {

Cell cell = cells.next();

if(isContainMergeCell(sheet)){

cancelMergeCell(sheet);

}

// 区别相同的头

list = ListUtil.changeSameVal(list);

map.put(list.get(idx++), getStringCellValue(cell));

}

}

idx = 0;

mapList.add(map);

}

return mapList;

} catch (IOException ex) {

ex.printStackTrace();

}

return null;

}

/**

* 合并单元格

* @param sheet 当前sheet页

* @param firstRow 开始行

* @param lastRow 结束行

* @param firstCol 开始列

* @param lastCol 结束列

*/

public static int mergeCell(Sheet sheet,int firstRow,int lastRow,int firstCol,int lastCol){

if(sheet == null){

return -1;

}

return sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));

}

/**

* 取消合并单元格

* @param sheet

* @param idx

*/

public static void cancelMergeCell(Sheet sheet){

int sheetMergeCount = sheet.getNumMergedRegions();

for(int idx = 0; idx < sheetMergeCount;){

CellRangeAddress range = sheet.getMergedRegion(idx);

String val = getMergeCellValue(sheet,range.getFirstRow(),range.getLastRow());

// 取消合并单元格

sheet.removeMergedRegion(idx);

for(int rownum=range.getFirstRow();rownum

for(int cellnum=range.getFirstColumn();cellnum

sheet.getRow(rownum).getCell(cellnum).setCellValue(val);

cellnum ++;

}

rownum ++;

}

idx++;

}

}

/**

* 判断指定单元格是否是合并单元格

* @param sheet 当前sheet页

* @param firstRow 开始行

* @param lastRow 结束行

* @param firstCol 开始列

* @param lastCol 结束列

* @return

*/

public static boolean isMergeCell(Sheet sheet,

int row ,int column){

int sheetMergeCount = sheet.getNumMergedRegions();

for(int i = 0; i < sheetMergeCount;){

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;

}

}

i++;

}

return false;

}

/**

* 判断sheet页中是否含有合并单元格

* @param sheet

* @return

*/

public static boolean isContainMergeCell(Sheet sheet){

if(sheet == null){

return false;

}

return sheet.getNumMergedRegions()>0 ? true : false;

}

/**

* 获取指定合并单元的值

* @param sheet

* @param row

* @param column

* @return

*/

public static String getMergeCellValue(Sheet sheet,

int row ,int column){

int sheetMergeCount = sheet.getNumMergedRegions();

for(int i = 0; i < sheetMergeCount;){

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){

Row fRow = sheet.getRow(firstRow);

Cell fCell = fRow.getCell(firstColumn);

return getStringCellValue(fCell) ;

}

}

i++;

}

return null;

}

/**

* 获取单元格的值

* @param cell

* @return

*/

public static String getStringCellValue(Cell cell) {

String strCell = "";

if(cell==null) return strCell;

switch (cell.getCellType()) {

case Cell.CELL_TYPE_STRING:

strCell = cell.getRichStringCellValue().getString().trim();

break;

case Cell.CELL_TYPE_NUMERIC:

strCell = String.valueOf(cell.getNumericCellValue());

break;

case Cell.CELL_TYPE_BOOLEAN:

strCell = String.valueOf(cell.getBooleanCellValue());

break;

case Cell.CELL_TYPE_FORMULA:

FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();

evaluator.evaluateFormulaCell(cell);

CellValue cellValue = evaluator.evaluate(cell);

strCell = String.valueOf(cellValue.getNumberValue()) ;

break;

default:

strCell = "";

}

return strCell;

}

}

调用方式如下

ExcelUtilTester.java

package pers.kangxu.datautils.test;

import java.util.ArrayList;

import java.util.List;

import pers.kangxu.datautils.utils.ExcelUtil;

public class ExcelUtilTester {

public static void main(String[] args) {

List datas = new ArrayList();

datas.add(new String[]{"狗熊","母","250"});

datas.add(new String[]{"猪粮","不明","251"});

//ExcelUtil.writeExcel("C:\\Users\\Administrator\\Desktop\\test\\test\\test.xls","sheet1",0, new String[]{"姓名","年龄","性别"}, datas);

System.out.println(ExcelUtil.readExcel("C:\\Users\\Administrator\\Desktop\\test\\test\\test.xls", 0));

}

}

for(int cellnum=range.getFirstColumn();cellnum

sheet.getRow(rownum).getCell(cellnum).setCellValue(val);

cellnum ++;

}

rownum ++;

}

idx++;

}

}

/**

* 判断指定单元格是否是合并单元格

* @param sheet 当前sheet页

* @param firstRow 开始行

* @param lastRow 结束行

* @param firstCol 开始列

* @param lastCol 结束列

* @return

*/

public static boolean isMergeCell(Sheet sheet,

int row ,int column){

int sheetMergeCount = sheet.getNumMergedRegions();

for(int i = 0; i < sheetMergeCount;){

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;

}

}

i++;

}

return false;

}

/**

* 判断sheet页中是否含有合并单元格

* @param sheet

* @return

*/

public static boolean isContainMergeCell(Sheet sheet){

if(sheet == null){

return false;

}

return sheet.getNumMergedRegions()>0 ? true : false;

}

/**

* 获取指定合并单元的值

* @param sheet

* @param row

* @param column

* @return

*/

public static String getMergeCellValue(Sheet sheet,

int row ,int column){

int sheetMergeCount = sheet.getNumMergedRegions();

for(int i = 0; i < sheetMergeCount;){

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){

Row fRow = sheet.getRow(firstRow);

Cell fCell = fRow.getCell(firstColumn);

return getStringCellValue(fCell) ;

}

}

i++;

}

return null;

}

/**

* 获取单元格的值

* @param cell

* @return

*/

public static String getStringCellValue(Cell cell) {

String strCell = "";

if(cell==null) return strCell;

switch (cell.getCellType()) {

case Cell.CELL_TYPE_STRING:

strCell = cell.getRichStringCellValue().getString().trim();

break;

case Cell.CELL_TYPE_NUMERIC:

strCell = String.valueOf(cell.getNumericCellValue());

break;

case Cell.CELL_TYPE_BOOLEAN:

strCell = String.valueOf(cell.getBooleanCellValue());

break;

case Cell.CELL_TYPE_FORMULA:

FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();

evaluator.evaluateFormulaCell(cell);

CellValue cellValue = evaluator.evaluate(cell);

strCell = String.valueOf(cellValue.getNumberValue()) ;

break;

default:

strCell = "";

}

return strCell;

}

}

调用方式如下

ExcelUtilTester.java

package pers.kangxu.datautils.test;

import java.util.ArrayList;

import java.util.List;

import pers.kangxu.datautils.utils.ExcelUtil;

public class ExcelUtilTester {

public static void main(String[] args) {

List datas = new ArrayList();

datas.add(new String[]{"狗熊","母","250"});

datas.add(new String[]{"猪粮","不明","251"});

//ExcelUtil.writeExcel("C:\\Users\\Administrator\\Desktop\\test\\test\\test.xls","sheet1",0, new String[]{"姓名","年龄","性别"}, datas);

System.out.println(ExcelUtil.readExcel("C:\\Users\\Administrator\\Desktop\\test\\test\\test.xls", 0));

}

}

sheet.getRow(rownum).getCell(cellnum).setCellValue(val);

cellnum ++;

}

rownum ++;

}

idx++;

}

}

/**

* 判断指定单元格是否是合并单元格

* @param sheet 当前sheet页

* @param firstRow 开始行

* @param lastRow 结束行

* @param firstCol 开始列

* @param lastCol 结束列

* @return

*/

public static boolean isMergeCell(Sheet sheet,

int row ,int column){

int sheetMergeCount = sheet.getNumMergedRegions();

for(int i = 0; i < sheetMergeCount;){

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;

}

}

i++;

}

return false;

}

/**

* 判断sheet页中是否含有合并单元格

* @param sheet

* @return

*/

public static boolean isContainMergeCell(Sheet sheet){

if(sheet == null){

return false;

}

return sheet.getNumMergedRegions()>0 ? true : false;

}

/**

* 获取指定合并单元的值

* @param sheet

* @param row

* @param column

* @return

*/

public static String getMergeCellValue(Sheet sheet,

int row ,int column){

int sheetMergeCount = sheet.getNumMergedRegions();

for(int i = 0; i < sheetMergeCount;){

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){

Row fRow = sheet.getRow(firstRow);

Cell fCell = fRow.getCell(firstColumn);

return getStringCellValue(fCell) ;

}

}

i++;

}

return null;

}

/**

* 获取单元格的值

* @param cell

* @return

*/

public static String getStringCellValue(Cell cell) {

String strCell = "";

if(cell==null) return strCell;

switch (cell.getCellType()) {

case Cell.CELL_TYPE_STRING:

strCell = cell.getRichStringCellValue().getString().trim();

break;

case Cell.CELL_TYPE_NUMERIC:

strCell = String.valueOf(cell.getNumericCellValue());

break;

case Cell.CELL_TYPE_BOOLEAN:

strCell = String.valueOf(cell.getBooleanCellValue());

break;

case Cell.CELL_TYPE_FORMULA:

FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();

evaluator.evaluateFormulaCell(cell);

CellValue cellValue = evaluator.evaluate(cell);

strCell = String.valueOf(cellValue.getNumberValue()) ;

break;

default:

strCell = "";

}

return strCell;

}

}

调用方式如下

ExcelUtilTester.java

package pers.kangxu.datautils.test;

import java.util.ArrayList;

import java.util.List;

import pers.kangxu.datautils.utils.ExcelUtil;

public class ExcelUtilTester {

public static void main(String[] args) {

List datas = new ArrayList();

datas.add(new String[]{"狗熊","母","250"});

datas.add(new String[]{"猪粮","不明","251"});

//ExcelUtil.writeExcel("C:\\Users\\Administrator\\Desktop\\test\\test\\test.xls","sheet1",0, new String[]{"姓名","年龄","性别"}, datas);

System.out.println(ExcelUtil.readExcel("C:\\Users\\Administrator\\Desktop\\test\\test\\test.xls", 0));

}

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。