转:
下面是文件内具体内容,文件下载:
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.DataFormat;
import org.apache.poi.ss.usermodel.Font;
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 zhangyi
* @version 1.0 2016/01/27
*
*/
public class ExcelUtil {
private Workbook workbook;
private OutputStream os;
private String pattern;// 日期格式
public void setPattern(String pattern) {
this.pattern = pattern;
}
public ExcelUtil(Workbook workboook) {
this.workbook = workboook;
}
public ExcelUtil(InputStream is, String version) throws FileNotFoundException, IOException {
if ("2003".equals(version)) {
workbook = new HSSFWorkbook(is);
} else {
workbook = new XSSFWorkbook(is);
}
}
public String toString() {
return "共有 " + getSheetCount() + "个sheet 页!";
}
public String toString(int sheetIx) throws IOException {
return "第 " + (sheetIx + 1) + "个sheet 页,名称: " + getSheetName(sheetIx) + ",共 " + getRowCount(sheetIx) + "行!";
}
/**
*
* 根据后缀判断是否为 Excel 文件,后缀匹配xls和xlsx
*
* @param pathname
* @return
*
*/
public static boolean isExcel(String pathname) {
if (pathname == null) {
return false;
}
return pathname.endsWith(".xls") || pathname.endsWith(".xlsx");
}
/**
*
* 读取 Excel 第一页所有数据
*
* @return
* @throws Exception
*
*/
public List<List<String>> read() throws Exception {
return read(0, 0, getRowCount(0) - 1);
}
/**
*
* 读取指定sheet 页所有数据
*
* @param sheetIx
* 指定 sheet 页,从 0 开始
* @return
* @throws Exception
*/
public List<List<String>> read(int sheetIx) throws Exception {
return read(sheetIx, 0, getRowCount(sheetIx) - 1);
}
/**
*
* 读取指定sheet 页指定行数据
*
* @param sheetIx
* 指定 sheet 页,从 0 开始
* @param start
* 指定开始行,从 0 开始
* @param end
* 指定结束行,从 0 开始
* @return
* @throws Exception
*/
public List<List<String>> read(int sheetIx, int start, int end) throws Exception {
Sheet sheet = workbook.getSheetAt(sheetIx);
List<List<String>> list = new ArrayList<List<String>>();
if (end > getRowCount(sheetIx)) {
end = getRowCount(sheetIx);
}
int cols = sheet.getRow(0).getLastCellNum(); // 第一行总列数
for (int i = start; i <= end; i++) {
List<String> rowList = new ArrayList<String>();
Row row = sheet.getRow(i);
for (int j = 0; j < cols; j++) {
if (row == null) {
rowList.add(null);
continue;
}
rowList.add(getCellValueToString(row.getCell(j)));
}
list.add(rowList);
}
return list;
}
/**
*
* 将数据写入到 Excel 默认第一页中,从第1行开始写入
*
* @param rowData
* 数据
* @return
* @throws IOException
*
*/
public boolean write(List<List<String>> rowData) throws IOException {
return write(0, rowData, 0);
}
/**
*
* 将数据写入到 Excel 新创建的 Sheet 页
*
* @param rowData
* 数据
* @param sheetName
* 长度为1-31,不能包含后面任一字符: : / ? * [ ]
* @return
* @throws IOException
*/
public boolean write(List<List<String>> rowData, String sheetName, boolean isNewSheet) throws IOException {
Sheet sheet = null;
if (isNewSheet) {
sheet = workbook.createSheet(sheetName);
} else {
sheet = workbook.createSheet();
}
int sheetIx = workbook.getSheetIndex(sheet);
return write(sheetIx, rowData, 0);
}
/**
*
* 将数据追加到sheet页最后
*
* @param rowData
* 数据
* @param sheetIx
* 指定 Sheet 页,从 0 开始
* @param isAppend
* 是否追加,true 追加,false 重置sheet再添加
* @return
* @throws IOException
*/
public boolean write(int sheetIx, List<List<String>> rowData, boolean isAppend) throws IOException {
if (isAppend) {
return write(sheetIx, rowData, getRowCount(sheetIx));
}