<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
代码如下:
package ExcelProject.ExcelProject;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* Excel解析解析
* @author JAVA3
*/
public class ExcelAnalysis {
public static void analysis(InputStream inputStream,String fileName) {
boolean isE2007 = false;
if(fileName.endsWith("xlsx")) {
isE2007=true;
}
int rowIndex = 0;//行
int columnIndex = 0;//列
List<List<Object>> allDateList=new ArrayList<List<Object>>();
try {
InputStream input=inputStream;
Workbook wb = null;
if(isE2007){
wb = new XSSFWorkbook(input);
}else{
wb = new HSSFWorkbook(input);
}
Sheet sheet = wb.getSheetAt(0); //获得第一个表单
System.out.println("总行数:"+sheet.getLastRowNum());//总行数
int totalRow=sheet.getLastRowNum();
List<CellRangeAddress> list = new ArrayList<CellRangeAddress>();
int count=sheet.getNumMergedRegions(); //所有合并单元格的数量
for(int i = 0; i<count;i++) {
//获得合并单元格保存进list中
CellRangeAddress ca = sheet.getMergedRegion(i);
list.add(ca); //获取所有合并单元格的信息
}
for(int rowNum=3;rowNum<=totalRow;rowNum++) {//行数
List<Object> rowList=new ArrayList<Object>();
for(int cloNum=0;cloNum<=7;cloNum++) {//列数
Row row = sheet.getRow(rowNum);//得到第rowNum的数据
rowList.add(getMergedRegionValue(sheet,rowNum,cloNum));
}
allDateList.add(rowList);
}
writeFile(allDateList);
}catch(Exception e) {
e.printStackTrace();
}
}
public static void writeFile(List<List<Object>> list) {
File file=new File("C:\\ABCDE\\新加装烟感统计表.txt");
try {
if(!file.getParentFile().exists()) {
file.getParentFile().mkdirs();
file.createNewFile();
}
FileWriter fileWrite=new FileWriter(file,true);
BufferedWriter buw=new BufferedWriter(fileWrite);
for(List<Object> listObj:list) {
String str="";
for(Object obj:listObj) {
if(obj instanceof String) {
str+=(String)obj+"#";
}
}
buw.write(str);
buw.flush();
buw.newLine();
}
}catch(Exception e) {
e.printStackTrace();
}
}
public static String getMergedRegionValue(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);
return getCellValue(fCell);
}
}
}
Row rowInfo=sheet.getRow(row);
Cell fCell = rowInfo.getCell(column);
if(fCell==null) {
return "null";
}
return getCellValue(fCell);
}
public static String getCellValue(Cell cell) {
if (cell == null)
return "null";
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
return cell.getStringCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
return cell.getCellFormula();
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
return String.valueOf(cell.getNumericCellValue());
}else {
return "null";
}
}
public static void main(String[] args) {
try {
File file=new File("C:\\美礼实\\新加装烟感统计表.xlsx");
if(file.exists()) {
System.out.println("文件存在");
}else {
System.out.println("文件不存在");
return ;
}
InputStream input=new FileInputStream(file);
String fileName="test.xlsx";
analysis(input,fileName);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}