Java读写Excel实例分享
话不多说，请看代码：
ExcelUtil.java
packagepers.kangxu.datautils.utils;
importjava.io.File;
importjava.io.FileInputStream;
importjava.io.FileOutputStream;
importjava.io.IOException;
importjava.io.InputStream;
importjava.util.ArrayList;
importjava.util.HashMap;
importjava.util.Iterator;
importjava.util.List;
importjava.util.Map;
importorg.apache.poi.hssf.usermodel.HSSFCellStyle;
importorg.apache.poi.hssf.usermodel.HSSFFont;
importorg.apache.poi.hssf.usermodel.HSSFWorkbook;
importorg.apache.poi.ss.usermodel.Cell;
importorg.apache.poi.ss.usermodel.CellStyle;
importorg.apache.poi.ss.usermodel.CellValue;
importorg.apache.poi.ss.usermodel.Font;
importorg.apache.poi.ss.usermodel.FormulaEvaluator;
importorg.apache.poi.ss.usermodel.Row;
importorg.apache.poi.ss.usermodel.Sheet;
importorg.apache.poi.ss.usermodel.Workbook;
importorg.apache.poi.ss.util.CellRangeAddress;
/**
*
*<b>
*excel工具
*</b>
*@authorkangxu
*
*/
publicclassExcelUtil{
/**
*导出excel
*@paramfilePath文件全路径
*@paramsheetNamesheet页名称
*@paramsheetIndex当前sheet下表从0开始
*@paramfileHeader头部
*@paramdatas内容
*/
publicstaticvoidwriteExcel(StringfilePath,StringsheetName,
intsheetIndex,
String[]fileHeader,
List<String[]>datas){
//创建工作簿
Workbookwb=newHSSFWorkbook();
//创建工作表sheet
Sheets=wb.createSheet();
wb.setSheetName(sheetIndex,sheetName);
Rowr=s.createRow(0);
Cellc=null;
Fontfont=null;
CellStylestyleHeader=null;
CellStylestyleContent=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(inti=0;i<fileHeader.length;){
c=r.createCell(i);
c.setCellStyle(styleHeader);
c.setCellValue(fileHeader[i]);
i++;
}
//设置内容
for(intrownum=0;rownum<datas.size();){//行rowdatas.size()
r=s.createRow(rownum+1);//创建行
for(intcellnum=0;cellnum<fileHeader.length;){
c=r.createCell(cellnum);
c.setCellValue(datas.get(rownum)[cellnum]);
c.setCellStyle(styleContent);
cellnum++;
}
rownum++;
}
FileOutputStreamout=null;
try{
//创建文件或者文件夹,将内容写进去
if(FileUtil.createFile(newFile(filePath))){
out=newFileOutputStream(filePath);
wb.write(out);
}
}catch(Exceptione){
e.printStackTrace();
}finally{
try{
//关闭流
if(out!=null){
out.flush();
out.close();
}
}catch(IOExceptione){
e.printStackTrace();
}
}
}
/**
*读取excel文件内容
*@paramfilePath
*@paramsheetIndex
*/
publicstaticList<Map<String,String>>readExcel(StringfilePath,intsheetIndex){
List<Map<String,String>>mapList=newArrayList<Map<String,String>>();
//头
List<String>list=newArrayList<String>();
//
intcnt=0;
intidx=0;
try{
InputStreaminput=newFileInputStream(filePath);//建立输入流
Workbookwb=null;
wb=newHSSFWorkbook(input);
//获取sheet页
Sheetsheet=wb.getSheetAt(sheetIndex);
Iterator<Row>rows=sheet.rowIterator();
while(rows.hasNext()){
Rowrow=rows.next();
Iterator<Cell>cells=row.cellIterator();
Map<String,String>map=newHashMap<String,String>();
if(cnt==0){//将头放进list中
while(cells.hasNext()){
Cellcell=cells.next();
if(isContainMergeCell(sheet)){
cancelMergeCell(sheet);
}
list.add(getStringCellValue(cell));
}
cnt++;
continue;
}else{
while(cells.hasNext()){
Cellcell=cells.next();
if(isContainMergeCell(sheet)){
cancelMergeCell(sheet);
}
//区别相同的头
list=ListUtil.changeSameVal(list);
map.put(list.get(idx++),getStringCellValue(cell));
}
}
idx=0;
mapList.add(map);
}
returnmapList;
}catch(IOExceptionex){
ex.printStackTrace();
}
returnnull;
}
/**
*合并单元格
*@paramsheet当前sheet页
*@paramfirstRow开始行
*@paramlastRow结束行
*@paramfirstCol开始列
*@paramlastCol结束列
*/
publicstaticintmergeCell(Sheetsheet,intfirstRow,intlastRow,intfirstCol,intlastCol){
if(sheet==null){
return-1;
}
returnsheet.addMergedRegion(newCellRangeAddress(firstRow,lastRow,firstCol,lastCol));
}
/**
*取消合并单元格
*@paramsheet
*@paramidx
*/
publicstaticvoidcancelMergeCell(Sheetsheet){
intsheetMergeCount=sheet.getNumMergedRegions();
for(intidx=0;idx<sheetMergeCount;){
CellRangeAddressrange=sheet.getMergedRegion(idx);
Stringval=getMergeCellValue(sheet,range.getFirstRow(),range.getLastRow());
//取消合并单元格
sheet.removeMergedRegion(idx);
for(intrownum=range.getFirstRow();rownum<range.getLastRow()+1;){
for(intcellnum=range.getFirstColumn();cellnum<range.getLastColumn()+1;){
sheet.getRow(rownum).getCell(cellnum).setCellValue(val);
cellnum++;
}
rownum++;
}
idx++;
}
}
/**
*判断指定单元格是否是合并单元格
*@paramsheet当前sheet页
*@paramfirstRow开始行
*@paramlastRow结束行
*@paramfirstCol开始列
*@paramlastCol结束列
*@return
*/
publicstaticbooleanisMergeCell(Sheetsheet,
introw,intcolumn){
intsheetMergeCount=sheet.getNumMergedRegions();
for(inti=0;i<sheetMergeCount;){
CellRangeAddressrange=sheet.getMergedRegion(i);
intfirstColumn=range.getFirstColumn();
intlastColumn=range.getLastColumn();
intfirstRow=range.getFirstRow();
intlastRow=range.getLastRow();
if(row>=firstRow&&row<=lastRow){
if(column>=firstColumn&&column<=lastColumn){
returntrue;
}
}
i++;
}
returnfalse;
}
/**
*判断sheet页中是否含有合并单元格
*@paramsheet
*@return
*/
publicstaticbooleanisContainMergeCell(Sheetsheet){
if(sheet==null){
returnfalse;
}
returnsheet.getNumMergedRegions()>0?true:false;
}
/**
*获取指定合并单元的值
*@paramsheet
*@paramrow
*@paramcolumn
*@return
*/
publicstaticStringgetMergeCellValue(Sheetsheet,
introw,intcolumn){
intsheetMergeCount=sheet.getNumMergedRegions();
for(inti=0;i<sheetMergeCount;){
CellRangeAddressrange=sheet.getMergedRegion(i);
intfirstColumn=range.getFirstColumn();
intlastColumn=range.getLastColumn();
intfirstRow=range.getFirstRow();
intlastRow=range.getLastRow();
if(row>=firstRow&&row<=lastRow){
if(column>=firstColumn&&column<=lastColumn){
RowfRow=sheet.getRow(firstRow);
CellfCell=fRow.getCell(firstColumn);
returngetStringCellValue(fCell);
}
}
i++;
}
returnnull;
}
/**
*获取单元格的值
*@paramcell
*@return
*/
publicstaticStringgetStringCellValue(Cellcell){
StringstrCell="";
if(cell==null)returnstrCell;
switch(cell.getCellType()){
caseCell.CELL_TYPE_STRING:
strCell=cell.getRichStringCellValue().getString().trim();
break;
caseCell.CELL_TYPE_NUMERIC:
strCell=String.valueOf(cell.getNumericCellValue());
break;
caseCell.CELL_TYPE_BOOLEAN:
strCell=String.valueOf(cell.getBooleanCellValue());
break;
caseCell.CELL_TYPE_FORMULA:
FormulaEvaluatorevaluator=cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
evaluator.evaluateFormulaCell(cell);
CellValuecellValue=evaluator.evaluate(cell);
strCell=String.valueOf(cellValue.getNumberValue());
break;
default:
strCell="";
}
returnstrCell;
}
}
调用方式如下
ExcelUtilTester.java
packagepers.kangxu.datautils.test;
importjava.util.ArrayList;
importjava.util.List;
importpers.kangxu.datautils.utils.ExcelUtil;
publicclassExcelUtilTester{
publicstaticvoidmain(String[]args){
List<String[]>datas=newArrayList<String[]>();
datas.add(newString[]{"狗熊","母","250"});
datas.add(newString[]{"猪粮","不明","251"});
//ExcelUtil.writeExcel("C:\\Users\\Administrator\\Desktop\\test\\test\\test.xls","sheet1",0,newString[]{"姓名","年龄","性别"},datas);
System.out.println(ExcelUtil.readExcel("C:\\Users\\Administrator\\Desktop\\test\\test\\test.xls",0));
}
}
以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，同时也希望多多支持毛票票！