依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
</dependency>
由于Excel存在xls以及xlsx两种格式,所以创建方式也有所不同。
- 对于xls格式,需要使用HSSFWorkbook来创建对象;
- 对于xlsx格式,需要使用XSSFWorkbook来创建工作薄;
下面代码
package com.polixir.waterpump.utils;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtil {
//默认单元格内容为数字时格式
private static DecimalFormat df = new DecimalFormat("0");
// 默认单元格格式化日期字符串
private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
// 格式化数字
private static DecimalFormat nf = new DecimalFormat("0.0000");
public static ArrayList<ArrayList<Object>> readExcel(final File file, final Integer onesheet) {
if (file == null) {
return null;
}
if (file.getName().endsWith("xlsx")) {
//处理ecxel2007
return readExcel2007(file, onesheet);
} else {
//处理ecxel2003
return readExcel2003(file, onesheet);
}
}
/*
* @return 将返回结果存储在ArrayList内,存储结构与二位数组类似 lists.get(0).get(0)表示过去Excel中0行0列单元格
*/
public static ArrayList<ArrayList<Object>> readExcel2003(final File file, final Integer onesheet) {
try {
final ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
ArrayList<Object> colList;
final HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
final HSSFSheet sheet = wb.getSheetAt(onesheet);
HSSFRow row;
HSSFCell cell;
Object value;
for (int i = sheet.getFirstRowNum(), rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
colList = new ArrayList<Object>();
if (row == null) {
//当读取行为空时
if (i != sheet.getPhysicalNumberOfRows()) {//判断是否是最后一行
rowList.add(colList);
}
continue;
} else {
rowCount++;
}
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
cell = row.getCell(j);
if (cell == null || cell.getCellType() == CellType.BLANK) {
//当该单元格为空
if (j != row.getLastCellNum()) {//判断是否是该行中最后一个单元格
colList.add("");
}
continue;
}
switch (cell.getCellType()) {
case STRING:
//System.out.println(i + "行" + j + " 列 is String type");
value = cell.getStringCellValue();
break;
case NUMERIC:
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = nf.format(cell.getNumericCellValue());
} else {
value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}
//System.out.println(i + "行" + j + " 列 is Number type ; DateFormt:" + value.toString());
break;
case BOOLEAN:
// System.out.println(i + "行" + j + " 列 is Boolean type");
value = Boolean.valueOf(cell.getBooleanCellValue());
break;
case BLANK:
// System.out.println(i + "行" + j + " 列 is Blank type");
value = "";
break;
default:
// System.out.println(i + "行" + j + " 列 is default type");
value = cell.toString();
}// end switch
colList.add(value);
} //end for j
rowList.add(colList);
} //end for i
return rowList;
} catch (final Exception e) {
return null;
}
}
public static ArrayList<ArrayList<Object>> readExcel2007(final File file, final Integer onesheet) {
try {
final ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
ArrayList<Object> colList;
final XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
final XSSFSheet sheet = wb.getSheetAt(onesheet);
XSSFRow row;
XSSFCell cell;
Object value;
for (int i = sheet.getFirstRowNum(), rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
colList = new ArrayList<Object>();
if (row == null) {
//当读取行为空时
if (i != sheet.getPhysicalNumberOfRows()) {//判断是否是最后一行
rowList.add(colList);
}
continue;
} else {
rowCount++;
}
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
cell = row.getCell(j);
if (cell == null || cell.getCellType() == CellType.BLANK) {
//当该单元格为空
if (j != row.getLastCellNum()) {//判断是否是该行中最后一个单元格
colList.add("");
}
continue;
}
switch (cell.getCellType()) {
case STRING:
//System.out.println(i + "行" + j + " 列 is String type");
value = cell.getStringCellValue();
break;
case NUMERIC:
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = nf.format(cell.getNumericCellValue());
} else {
value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}
//System.out.println(i + "行" + j + " 列 is Number type ; DateFormt:" + value.toString());
break;
case BOOLEAN:
//System.out.println(i + "行" + j + " 列 is Boolean type");
value = Boolean.valueOf(cell.getBooleanCellValue());
break;
case BLANK:
// System.out.println(i + "行" + j + " 列 is Blank type");
value = "";
break;
default:
//System.out.println(i + "行" + j + " 列 is default type");
value = cell.toString();
}// end switch
colList.add(value);
} //end for j
rowList.add(colList);
} //end for i
return rowList;
} catch (final Exception e) {
//System.out.println("exception");
return null;
}
}
public static void writeExcel(final ArrayList<ArrayList<Object>> result, final String path) {
if (result == null) {
return;
}
final HSSFWorkbook wb = new HSSFWorkbook();
final HSSFSheet sheet = wb.createSheet("sheet1");
for (int i = 0; i < result.size(); i++) {
final HSSFRow row = sheet.createRow(i);
if (result.get(i) != null) {
for (int j = 0; j < result.get(i).size(); j++) {
final HSSFCell cell = row.createCell(j);
cell.setCellValue(result.get(i).get(j).toString());
}
}
}
final ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
wb.write(os);
} catch (final IOException e) {
e.printStackTrace();
}
final byte[] content = os.toByteArray();
final File file = new File(path);//Excel文件生成后存储的位置。
OutputStream fos = null;
try {
fos = new FileOutputStream(file);
fos.write(content);
os.close();
fos.close();
} catch (final Exception e) {
e.printStackTrace();
}
}
public static DecimalFormat getDf() {
return df;
}
public static void setDf(final DecimalFormat df) {
ExcelUtil.df = df;
}
public static SimpleDateFormat getSdf() {
return sdf;
}
public static void setSdf(final SimpleDateFormat sdf) {
ExcelUtil.sdf = sdf;
}
public static DecimalFormat getNf() {
return nf;
}
public static void setNf(final DecimalFormat nf) {
ExcelUtil.nf = nf;
}
}