package com.lead.lead;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.NumberFormat;
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.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class OperateExcelUtil {
public final static int MAX_COUNT_NUMBER = 30000;
public final static String STYLE_STRING = "String_Type";
public final static String STYLE_NUMERIC = "Numeric_Type";
public final static String STYLE_DATE1 = "yyyy-M-d";
public final static int IS_NUMERIC = HSSFCell.CELL_TYPE_NUMERIC;
public final static int IS_STRING = HSSFCell.CELL_TYPE_STRING;
public final static int IS_DATE = 9999;
// public void createExcelFile(String sFileName, List<String[]> aList,
// String sTypes[], boolean isTitle) throws Exception {
// createExcelFile(sFileName, "工作表", aList, sTypes, isTitle, null);
// }
//
// public void createExcelFile(String sFileName, List<String[]> aList,
// boolean isTitle) throws Exception {
// createExcelFile(sFileName, "工作表", aList, null, isTitle, null);
// }
public void createExcelFile(String sFileName, String sheetBaseName,
List<String[]> aList, String sTypes[], boolean isTitle)
throws Exception {
// 判断文件名是否正确的扩展名(.xls)
if (sFileName != null && !sFileName.equals("")) {
if (sFileName.lastIndexOf(".xls") == -1) {
throw new Exception(sFileName + " 文件扩展名不正确.");
}
} else {
throw new Exception("Null value or length equals 0");
}
// 判断数据集合是否为空
if (aList == null) {
throw new Exception("Null value or length equals 0");
}
// 创建一个工作簿
HSSFWorkbook workBook = new HSSFWorkbook();
// 获取EXCEL的样式实例
HSSFCellStyle cellStyle = workBook.createCellStyle();
/** (开始)写入数据到工作簿 */
if (aList != null && aList.size() > 0) {
// 数据大小
int iDataSize = aList.size();
// 临时工作表
HSSFSheet sheetTmp = null;
// 当前工作表的记录数(行数)
int iCountNum = MAX_COUNT_NUMBER;
// 工作簿中工作表数
int iTotalSheetNum = 0;
// 判断是否有标题,如果有标题,将标题临时保存
String aTitles[] = null;
if (isTitle) {
aTitles = aList.get(0);
}
for (int i = 0; i < iDataSize; i++, iCountNum++) {
if (iCountNum == MAX_COUNT_NUMBER) {
String sNameTmp = sheetBaseName + "_" + (++iTotalSheetNum);
sheetTmp = workBook.createSheet();
workBook.setSheetName(iTotalSheetNum - 1, sNameTmp);
iCountNum = 0;
}
// 获取数据
String aValues[] = (String[]) aList.get(i);
// 创建工作表的行(最顶行索引值为0)
HSSFRow row = sheetTmp.createRow(iCountNum);
if (isTitle && iCountNum == 0 && iTotalSheetNum == 1) { // 写入标题数据(只在第一个SHEET有标题)
for (int j = 0; j < aTitles.length; j++) {
HSSFCell cell = row.createCell(j); // 创建数据单元
// cell.setEncoding(HSSFCell.ENCODING_UTF_16); // 设置编码
cell.setCellType(HSSFCell.CELL_TYPE_STRING); // 设置数据格式(文本)
cell.setCellValue(aTitles[j]); // 设置值
}
} else { // 写入内容
if (sTypes == null || sTypes.length == 0) {// 没有样式,默认以文本作为样式
for (int j = 0; j < aValues.length; j++) {
HSSFCell cell = row.createCell(j); // 创建数据单元
// cell.setEncoding(HSSFCell.ENCODING_UTF_16); //
// 设置编码
cell.setCellType(HSSFCell.CELL_TYPE_STRING); // 设置数据格式(文本)
cell.setCellValue(aValues[j]); // 设置值
}
} else {
for (int j = 0; j < aValues.length; j++) {
HSSFCell cell = row.createCell(j); // 创建数据单元
// cell.setEncoding(HSSFCell.ENCODING_UTF_16); //
// 设置编码
if (sTypes[j].equals(OperateExcelUtil.STYLE_STRING)) {// 文本类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(aValues[j]);
} else if (sTypes[j]
.equals(OperateExcelUtil.STYLE_NUMERIC)) {// 数字类型
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(Double
.parseDouble(aValues[j]));
} else if (sTypes[j]
.equals(OperateExcelUtil.STYLE_DATE1)) { // 日期型(必须在HSSFStyle设置)
cellStyle.setDataFormat(HSSFDataFormat
.getBuiltinFormat("m/d/yy"));
cell.setCellStyle(cellStyle);
// 将字符串转化成日期类型
SimpleDateFormat df = new SimpleDateFormat(
"yyyy-MM-dd");
Date d = df.parse(aValues[j]);
cell.setCellValue(d);
} else {
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(aValues[j]);
}
}
}
}
}
}
}
public ArrayList readExcelFile(String sRealPath, boolean isTitle)
throws Exception {
ArrayList<String[]> arrayList = new ArrayList<String[]>();
// 判断文件路径是否存在
File fTmp = new File(sRealPath);
if (!fTmp.exists()) {
throw new Exception("文件路径不存在!");
}
// 获取文件流,并创建一个EXCEL文件对象
FileInputStream is = new FileInputStream(fTmp);
HSSFWorkbook workBook = new HSSFWorkbook(is);
int iSheetNum = 0; // 工作表SHEET数量
int iRowNum = 0; // 每个工作表中行数量
int iCellNum = 0; // 每行中的单元数量
iSheetNum = workBook.getNumberOfSheets(); // 获取SHEET数量(即使是空的SHEET也包括在内)
/** (开始)开始读取每个工作表数据 */
for (int i = 0; i < iSheetNum; i++, iRowNum = 0) {
HSSFSheet sheetTmp = workBook.getSheetAt(i);
/**
* 获取当前SHEET的行数(即使SHEET是空的,它默认也是记录数0+1=1)
* 这个说明了getLastRowNum()只获取最后一个行数,不管前面是否有空行
*/
iRowNum = sheetTmp.getLastRowNum() + 1;
/** (开始)读取行数据 */
int j = 0;
if (!isTitle) {
j = 1;
isTitle = true;
}
for (; j < iRowNum; j++, iCellNum = 0) {
HSSFRow rowTmp = sheetTmp.getRow(j);
// 判断是否是空行
if (rowTmp == null)
continue;
/**
* 获取每行的最后一个单元序列 其实,获取到的值都是一样的,而且是这个SHEET列数最大值
*/
iCellNum = rowTmp.getLastCellNum();
HSSFCell cellTest = null;
if ((cellTest = rowTmp.getCell((iCellNum))) != null) {
iCellNum = iCellNum + 1;
}
String aValues[] = new String[iCellNum];
/** (开始)读取每行的每个单元 */
for (int k = 0; k < iCellNum; k++) {
HSSFCell cellTmp = rowTmp.getCell( k);
// 判断是否是空的CELL
if (cellTmp == null) {
aValues[k] = new String("");
continue;
}
// 获取每个CELL的样式
int cellType = cellTmp.getCellType();
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING: // 文本类型
aValues[k] = new String(cellTmp.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC: { // 文本类型
// 判断是否是日期型数据
if (HSSFDateUtil.isCellDateFormatted(cellTmp)) {
Date dTmp = cellTmp.getDateCellValue();
// 将成日期类型转化字符串
SimpleDateFormat df = new SimpleDateFormat(
"yyyy-MM-dd");
aValues[k] = new String(df.format(dTmp));
} else {
HSSFCellStyle style = cellTmp.getCellStyle();
int idd = style.getDataFormat();
// 2007年07月14日===189;2007年7月14日====185;2007-06-02====188
if (idd == 185 || idd == 189 || idd == 188) {
Date dTmp = cellTmp.getDateCellValue();
// 将成日期类型转化字符串
SimpleDateFormat df = new SimpleDateFormat(
"yyyy-MM-dd");
aValues[k] = new String(df.format(dTmp));
} else {
Double d = new Double(
cellTmp.getNumericCellValue());
aValues[k] = new String(NumberFormat
.getInstance().format(d)
.replace(",", ""));
}
}
break;
}
case HSSFCell.CELL_TYPE_FORMULA: // 有待完善
break;
default:
break;
}
}
/** (结束)读取每行的每个单元 */
// 添加到集合中
arrayList.add(aValues);
}
/** (结束)读取行数据 */
}
return arrayList;
}
public ArrayList readExcelFile(InputStream is, boolean isTitle)
throws Exception {
ArrayList<String[]> arrayList = new ArrayList<String[]>();
// 获取文件流,并创建一个EXCEL文件对象
BufferedInputStream bis = new BufferedInputStream(is);
HSSFWorkbook workBook = new HSSFWorkbook(bis);
int iSheetNum = 0; // 工作表SHEET数量
int iRowNum = 0; // 每个工作表中行数量
int iCellNum = 0; // 每行中的单元数量
iSheetNum = workBook.getNumberOfSheets();
/** (开始)开始读取每个工作表数据 */
for (int i = 0; i < iSheetNum; i++, iRowNum = 0) {
HSSFSheet sheetTmp = workBook.getSheetAt(i);
iRowNum = sheetTmp.getLastRowNum() + 1;
/** (开始)读取行数据 */
int j = 0;
if (!isTitle) {
j = 1;
isTitle = true;
}
for (; j < iRowNum; j++, iCellNum = 0) {
HSSFRow rowTmp = sheetTmp.getRow(j);
// 判断是否是空行
if (rowTmp == null)
continue;
/**
* 获取每行的最后一个单元序列 其实,获取到的值都是一样的,而且是这个SHEET列数最大值
*/
iCellNum = rowTmp.getLastCellNum();
HSSFCell cellTest = null;
if ((cellTest = rowTmp.getCell( (iCellNum))) != null) {
iCellNum = iCellNum + 1;
}
String aValues[] = new String[iCellNum];
/** (开始)读取每行的每个单元 */
for (int k = 0; k < iCellNum; k++) {
HSSFCell cellTmp = rowTmp.getCell( k);
// 判断是否是空的CELL
if (cellTmp == null) {
aValues[k] = new String("");
continue;
}
// 获取每个CELL的样式
int cellType = cellTmp.getCellType();
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING: // 文本类型
aValues[k] = new String(cellTmp.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC: { // 文本类型
// 判断是否是日期型数据
if (HSSFDateUtil.isCellDateFormatted(cellTmp)) {
Date dTmp = cellTmp.getDateCellValue();
// 将成日期类型转化字符串
SimpleDateFormat df = new SimpleDateFormat(
"yyyy-MM-dd");
aValues[k] = new String(df.format(dTmp));
} else {
HSSFCellStyle style = cellTmp.getCellStyle();
int idd = style.getDataFormat();
// 2007年07月14日===189;2007年7月14日====185;2007-06-02====188
if (idd == 185 || idd == 189 || idd == 188) {
Date dTmp = cellTmp.getDateCellValue();
// 将成日期类型转化字符串
SimpleDateFormat df = new SimpleDateFormat(
"yyyy-MM-dd");
aValues[k] = new String(df.format(dTmp));
} else {
Double d = new Double(
cellTmp.getNumericCellValue());
aValues[k] = new String(NumberFormat
.getInstance().format(d)
.replace(",", ""));
}
}
break;
}
case HSSFCell.CELL_TYPE_BLANK:
aValues[k] = new String("");
break;
case HSSFCell.CELL_TYPE_FORMULA: // 有待完善
break;
default:
break;
}
}
// 添加到集合中
arrayList.add(aValues);
}
}
return arrayList;
}
public boolean validateExcelFile(String sRealPath, int rules[],
boolean isValidateTitle, String titles[]) throws Exception {
// 返回结果
boolean bResult = true;
// 判断文件路径是否存在
File fTmp = new File(sRealPath);
if (!fTmp.exists()) {
throw new Exception("文件路径不存在!");
}
// 获取文件流,并创建一个EXCEL文件对象
FileInputStream is = new FileInputStream(fTmp);
HSSFWorkbook workBook = new HSSFWorkbook(is);
int iSheetNum = 0; // 工作表SHEET数量
int iRowNum = 0; // 每个工作表中行数量
int iCellNum = 0; // 每行中的单元数量
iSheetNum = workBook.getNumberOfSheets(); // 获取SHEET数量(即使是空的SHEET也包括在内)
// 创建样式
HSSFCellStyle redStyle = workBook.createCellStyle();
redStyle.setFillPattern(HSSFCellStyle.BIG_SPOTS);
redStyle.setFillBackgroundColor( HSSFColor.RED.index);
redStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
int iTitleSign = 0;
/** (开始)开始读取每个工作表数据 */
for (int i = 0; i < iSheetNum; i++, iRowNum = 0) {
HSSFSheet sheetTmp = workBook.getSheetAt(i);
iRowNum = sheetTmp.getLastRowNum() + 1;
/** (开始)读取行数据 */
for (int j = 0; j < iRowNum; j++, iCellNum = 0) {
HSSFRow rowTmp = sheetTmp.getRow(j);
// 判断是否是空行
if (rowTmp == null)
continue;
/**
* 获取每行的最后一个单元序列 其实,获取到的值都是一样的,而且是这个SHEET列数最大值
*/
iCellNum = rowTmp.getLastCellNum();
// 存放行数据
HSSFCell cellTest = null;
if ((cellTest = rowTmp.getCell( (iCellNum))) != null) {
iCellNum = iCellNum + 1;
}
String aValues[] = new String[iCellNum];
// 验证标题
if (isValidateTitle && iTitleSign == 0) {
for (int k = 0; k < iCellNum; k++) {
HSSFCell cellTmp = rowTmp.getCell( k);
String sValue = cellTmp.getStringCellValue().trim();
if (!sValue.equals(titles[k])) {
bResult = false;
// 将这个单元以红色标注
cellTmp.setCellStyle(redStyle);
}
}
iTitleSign = 1;
continue;
}
// 不验证标题
if (!isValidateTitle && iTitleSign == 0) {
continue;
}
/** (开始)读取每行的每个单元 */
for (int k = 0; k <= iCellNum; k++) {
HSSFCell cellTmp = rowTmp.getCell(k);
// 判断是否是空的CELL
if (cellTmp == null) {
aValues[k] = new String("");
continue;
}
// 获取每个CELL的样式
int cellType = cellTmp.getCellType();
if (rules[k] == OperateExcelUtil.IS_DATE) {
if (cellType == IS_NUMERIC) { // 判断是否数字类型
if (HSSFDateUtil.isCellDateFormatted(cellTmp)) { // 判断是否是"2007-5-12"类型的日期
continue;
} else {
HSSFCellStyle styleTmp = cellTmp.getCellStyle();
int idd = styleTmp.getDataFormat();
// 2007年07月14日===189;2007年7月14日====185;2007-06-02====188
if (idd == 185 || idd == 189 || idd == 188) {
continue;
} else {
// 结果设置为FALSE
bResult = false;
// 将这个单元以红色标注
cellTmp.setCellStyle(redStyle);
}
}
} else {
// 结果设置为FALSE
bResult = false;
// 将这个单元以红色标注
cellTmp.setCellStyle(redStyle);
}
} else {
if (cellType != rules[k]) {
// 结果设置为FALSE
bResult = false;
// 将这个单元以红色标注
cellTmp.setCellStyle(redStyle);
}
}
}
/** (结束)读取每行的每个单元 */
}
/** (结束)读取行数据 */
}
/** (结束)开始读取每个工作表数据 */
// 新建一输出文件流
FileOutputStream fOut = new FileOutputStream(sRealPath);
// 把相应的Excel 工作簿存盘
workBook.write(fOut);
fOut.flush();
// 操作结束,关闭文件
fOut.close();
return bResult;
}
// ********************add by liuf 20090602********************************
public static void createCell(HSSFWorkbook wb, HSSFRow row, int column,
String value, boolean isTitle, HSSFCellStyle style) {
HSSFCell cell = row.createCell(column);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(value);
if (isTitle) {
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
} else {
style.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);
}
cell.setCellStyle(style);
}
public static void createCell(HSSFWorkbook wb, HSSFRow row, int column,
String value, boolean isTitle) {
createCell(wb, row, column, value, isTitle, wb.createCellStyle());
}
public static void createCell(HSSFWorkbook wb, HSSFRow row, int column,
String value, HSSFCellStyle style) {
createCell(wb, row, column, value, false, style);
}
public static void createCell(HSSFWorkbook wb, HSSFRow row, int column,
String value) {
createCell(wb, row, column, value, false, wb.createCellStyle());
}
}