excel 实用操作类

                 

maven 依赖

<dependency>

<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.9</version>

</dependency>

/**
 * 
 */
public class PoiUtil
{
/**
* excel版本值
*/
private static final int VERSION_2003 = 2003;
private static final int VERSION_2007 = 2007;


private static final int PERMAXSIZE = 65536;


/**
* 定义默认版本为2003
*/
private static int version = VERSION_2003;


private static SimpleDateFormat sFormat = new SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss");


/**
* 导出的excel脚本sheet名
*/
private static String EXCEL_DEFAULT_SHEET_NAME = "sheet1";


/**
* 根剧当前流获取excel指定列的一行值与tableHeader 數組進行比較，值相同返回true,反之返回false
* 
* @param is
* @param fileName
* @param tableHeader
* @param headerindex
* @return
* @throws IOException
* @date 2015-5-5 上午9:43:14
* @throws
* @author: cyl
* @修改记录： ============================================================== 日期:
*        2015-5-5 上午9:43:14 创建方法，并实现其功能
*        ==============================================================
*/
public static Boolean isContainHearders(InputStream is, String fileName,
String tableHeader[], int headerindex) throws IOException
{
if (tableHeader == null || tableHeader.length <= 0)
{
return false;
}
if (fileName.endsWith(".xls"))
{
version = VERSION_2003;
} else
{
version = VERSION_2007;
}


try
{


Workbook wb = null;
if (version == VERSION_2003)
{
wb = (Workbook) new HSSFWorkbook(is);
} else if (version == VERSION_2007)
{
wb = (Workbook) new XSSFWorkbook(is);
}
Sheet sheet = null;
for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++)
{
sheet = wb.getSheetAt(numSheet);
if (sheet == null || sheet.getRow(0) == null)
{
continue;
}
List<String> columnsList = new ArrayList<String>();
int coloumNum = sheet.getRow(0).getPhysicalNumberOfCells();
for (int i = 0; i < coloumNum; i++)
{
String snameString0 = sheet.getRow(headerindex).getCell(i)
.toString();
columnsList.add(snameString0);
}
int index = 0;
for (String string : tableHeader)
{
for (String temp : columnsList)
{
if (temp.equals(string))
{
index += 1;
continue;
}
}
}
if (index < 3)
{
return false;
}
}
} catch (Exception e)
{
return false;


} finally
{
is.close();
}
return true;
}


/**
* method:exportFile 导出excel
* 
* @param file
*            :excel名称
* @param tableHeader
*            :excel表头数组
* @param reportList
*            :数据源
* @throws Exception
*/
public static void exportFile(String fileName, List<String[]> reportList,
String tableHeader[]) throws Exception
{
OutputStream os = null;
os = new FileOutputStream(fileName);
HSSFWorkbook wb = new HSSFWorkbook();
try
{
createExcelSheet(wb, tableHeader, reportList);
wb.write(os);
os.flush();
} catch (Exception e)
{
throw new Exception(e.getMessage());
} finally
{
os.close();
}
}


public static <T> void exportFileReaLisedPage(String filePath,
final List<T> dataAll, final String tableHeader[],
final int maxPageCount) throws Exception
{
if (PERMAXSIZE <= maxPageCount)
{
throw new Exception("excel per sheet length can not more than 65536 ");
}

if (StringUtils.isEmpty(filePath))
{
throw new Exception("filePath is null");
}


if (null == dataAll)
{
throw new Exception("data is null");
}

if (null == tableHeader || tableHeader.length == 0)
{
throw new Exception("tableHeader is null");
}


int pages = 0;
if (dataAll.size() % maxPageCount == 0)
pages = dataAll.size() / maxPageCount;
else
pages = dataAll.size() / maxPageCount + 1;
if (pages > 1)
{
for (int i = 0; i < pages; i++)
{
String filename = filePath + File.separator + "Flow_" + (i + 1)
+ ".xls";
exportFileReaLised(filename, dataAll, tableHeader, maxPageCount
* i, maxPageCount);
}
FileUtil.compressToZip(filePath, filePath + ".zip");
} else
{
String filename = filePath + File.separator + "Flow_" + 1 + ".xls";
exportFileReaLised(filename, dataAll, tableHeader, 0, maxPageCount);
FileUtil.compressToZip(filePath, filePath + ".zip");
}
}


/**
* 
* @author cyl
* @date 2016-2-29 下午2:21:27
* @param filePath
* @param dataAll
*            长度不要超过65536
* @param tableHeader
* @throws Exception
* @return void
*/
public static <T> void exportFileReaLised(String filePath,
final List<T> dataAll, final String tableHeader[],
final int startIndex, final int max) throws Exception
{
exportFile(filePath, tableHeader, new PoiCallBack()
{
@Override
public void createRowsAndCells(HSSFSheet sheet)
{
if (!CollectionUtils.isEmpty(dataAll))
{
int perMax = startIndex + max;
perMax = (dataAll.size() > perMax) ? perMax : dataAll.size();
HSSFRow row = null;
HSSFCell cell = null;
int rowStartIndex = 0;
for (int i = startIndex; i < perMax; i++)
{
if (null == dataAll.get(i))
{
continue;
}
row = sheet.createRow(++rowStartIndex);


for (int j = 0; j < tableHeader.length; j++)
{
cell = row.createCell(j);


setValue(
ReflectionUtils.invokeGetterMethod(
dataAll.get(i), tableHeader[j]),
cell);
}
}
}
}
});
}


private static void setValue(Object param, HSSFCell cell)
{
if (param == null || cell == null)
return;
if (param instanceof String)
{
cell.setCellValue((String) param);
} else if (param instanceof Integer)
{
cell.setCellValue(((Integer) param).intValue());
} else if (param instanceof Double)
{
cell.setCellValue(((Double) param).doubleValue());
} else if (param instanceof Float)
{
cell.setCellValue(((Float) param).floatValue());
} else if (param instanceof Long)
{
cell.setCellValue(((Long) param).longValue());
} else if (param instanceof Boolean)
{
cell.setCellValue(((Boolean) param).booleanValue());
} else if (param instanceof Date)
{
cell.setCellValue((Date) param);
}
}


/**
* method:exportFile 导出excel
* 
* @param file
*            :excel名称
* @param tableHeader
*            :excel表头数组
* @param reportList
*            :数据源
* @throws Exception
*/
public static void exportFile(String fileName,
List<Map<String, Object>> reportList, String tableHeader[],
String[] columns) throws Exception
{
OutputStream os = null;
os = new FileOutputStream(fileName);
HSSFWorkbook wb = new HSSFWorkbook();
try
{
createExcelSheet(wb, tableHeader, reportList, columns);
wb.write(os);
os.flush();
} catch (Exception e)
{
throw e;
} finally
{
os.close();
}
}


public static void exportFile(String fileName, String tableHeader[],
PoiCallBack poiCallBack) throws Exception
{
OutputStream os = null;
os = new FileOutputStream(fileName);
HSSFWorkbook wb = new HSSFWorkbook();
try
{
createExcelSheet(wb, tableHeader, poiCallBack);
wb.write(os);
os.flush();
} catch (Exception e)
{
throw e;
} finally
{
os.close();
}
}


/**
* method:importFile 导入文件
* 
* @param file
*            获取excel的文件对象
* @param startLine
*            从第几行开始读
*/
public static List<String[]> importFile(InputStream is, String fileName,
int startLine)
{
List<String[]> list = null;
try
{
list = readerExcelSheet(is, fileName, startLine);
} catch (Exception e)
{
e.printStackTrace();
}
return list;
}


/**
* method:exportFileTemplete 导出模板
* 
* @param fileName
*            模板名
* @param tableHeader
*            模板表头名数组
* @throws Exception
*/
public static void exportFileTemplete(String fileName, String tableHeader[])
throws Exception
{
OutputStream os = null;
os = new FileOutputStream(fileName);
HSSFWorkbook wb = new HSSFWorkbook();
try
{
createExcelTemplate(wb, tableHeader);
wb.write(os);
os.flush();
} catch (Exception e)
{
throw new Exception(e.getMessage());
} finally
{
os.close();
}
}


/***
* method:importFile导入excel文件
* 
* @param file
*            获取excel的文件对象
* @param tableHeader
*            表格的标头数组
* @param startLine
*            开始行
*/
public static List<Map<String, String>> importFile(InputStream is,
String fileName, String[] tableHeader, int startLine)
{
try
{
return readerExcelSheet(is, fileName, tableHeader, startLine);
} catch (Exception e)
{
e.printStackTrace();
}
return null;
}


/**
* method:readerExcelSheet 读取excel文件得到单个单元格的值
* 
* @param is
*            文件流
* @param tableHeader
*            excel表头
* @param startLine
*            开始行,从0开始
* @return resultList<map> 返回结果集
* @throws IOException
*/
public static List<String[]> readerExcelSheet(InputStream is,
String fileName, int startLine) throws IOException
{
if (fileName.endsWith(".xls"))
{
version = VERSION_2003;
} else
{
version = VERSION_2007;
}


Workbook wb = null;
if (version == VERSION_2003)
{
wb = (Workbook) new HSSFWorkbook(is);
} else if (version == VERSION_2007)
{
wb = (Workbook) new XSSFWorkbook(is);
}


List<String[]> resultList = new ArrayList<String[]>();
String[] tableArray = null;
Sheet sheet = null;
Row row = null;
Cell cell = null;
for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++)
{
sheet = wb.getSheetAt(numSheet);
if (sheet == null)
{
continue;
}
int length = 0;
if (sheet != null && sheet.getRow(startLine - 1) != null)
{
length = ((Row) sheet.getRow(startLine - 1)).getLastCellNum();
}


for (int rowNum = startLine; rowNum <= sheet.getLastRowNum(); rowNum++)
{
row = sheet.getRow(rowNum);
if (row == null)
{
continue;
}
tableArray = new String[length];
for (int cellNum = 0; cellNum < length; cellNum++)
{
cell = row.getCell(cellNum);
if (cell != null)
{
tableArray[cellNum] = getExcelCellValue(cell);
} else
{
tableArray[cellNum] = "";
}
}
resultList.add(tableArray);
}
}
return resultList;
}


/**
* method:readerExcelSheet 读取文件excel对象
* 
* @param is
*            文件流
* @param tableHeader
*            excel表头
* @param startLine
*            开始行 0
* @return resultList<array> 返回结果集
* @throws IOException
*/
public static List<Map<String, String>> readerExcelSheet(InputStream is,
String fileName, String[] tableHeader, int startLine)
throws IOException
{
if (fileName.endsWith(".xls"))
{
version = VERSION_2003;
} else
{
version = VERSION_2007;
}


Workbook wb = null;
if (version == VERSION_2003)
{
wb = (Workbook) new HSSFWorkbook(is);
} else if (version == VERSION_2007)
{
wb = (Workbook) new XSSFWorkbook(is);
}


List<Map<String, String>> resultList = new ArrayList<Map<String, String>>();
Map<String, String> map = null;
Sheet sheet = null;
Row row = null;
Cell cell = null;
for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++)
{
sheet = wb.getSheetAt(numSheet);
if (sheet == null)
{
continue;
}


for (int rowNum = startLine; rowNum <= sheet.getLastRowNum(); rowNum++)
{
row = sheet.getRow(rowNum);
if (row == null)
{
continue;
}
map = new HashMap<String, String>();
;
for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++)
{
cell = row.getCell(cellNum);
if (cell != null)
{
map.put(tableHeader[cellNum], getExcelCellValue(cell));
} else
{
map.put(tableHeader[cellNum], "");
}
}
resultList.add(map);
}
}
return resultList;
}


/**
* method:createExcelSheet 创建sheet对象
* 
* @param wb
*            excel工作空间对象
* @param tableHeader
*            表头数组
* @param reportList
*            结果集
* @throws Exception
*/
public static void createExcelSheet(HSSFWorkbook wb, String[] tableHeader,
List<String[]> reportList) throws Exception
{
HSSFSheet sheet = wb.createSheet(EXCEL_DEFAULT_SHEET_NAME);
HSSFRow headerRow = sheet.createRow((short) 0);
int cellNumber = tableHeader.length;


HSSFCellStyle style = getExcelStyle(wb);


for (int i = 0; i < cellNumber; i++)
{
HSSFCell headerCell = headerRow.createCell(i);
headerCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);


headerCell.setCellValue(String.valueOf(tableHeader[i]));
headerCell.setCellStyle(style);
sheet.setColumnWidth(i, 5500);
}


for (int i = 0; i < reportList.size(); i++)
{
String[] list2 = reportList.get(i);
HSSFRow row = sheet.createRow(i + 1);
for (int q = 0; q < list2.length; q++)
{
// 创建第i个单元格
HSSFCell cell = row.createCell(q);
cell.setCellValue(list2[q].replace("未知", ""));


}
}
}


/**
* 
* @param wb
* @param tableHeader
*            excel表头
* @param reportList
*            数据List
* @param keys
*            数据表列名
* @throws Exception
*/
public static void createExcelSheet(HSSFWorkbook wb, String[] tableHeader,
List<Map<String, Object>> reportList, String[] keys)
throws Exception
{
HSSFSheet sheet = wb.createSheet(EXCEL_DEFAULT_SHEET_NAME);
HSSFRow headerRow = sheet.createRow((short) 0);


HSSFCellStyle style = getExcelStyle(wb);


int cellNumber = tableHeader.length;
for (int i = 0; i < cellNumber; i++)
{
HSSFCell headerCell = headerRow.createCell(i);
headerCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);


headerCell.setCellValue(String.valueOf(tableHeader[i]));
headerCell.setCellStyle(style);
sheet.setColumnWidth(i, 5500);
}
int q = 0;
Object value = null;
for (int i = 0; i < reportList.size(); i++)
{
Map<String, Object> list2 = reportList.get(i);
HSSFRow row = sheet.createRow(i + 1);
for (; q < keys.length; q++)
{
// 创建第i个单元格
HSSFCell cell = row.createCell(q);
value = list2.get(keys[q]);
cell.setCellValue(value != null ? value.toString() : "");
}
q = 0;
value = null;
}
}


public static void createExcelSheet(HSSFWorkbook wb, String[] tableHeader,
PoiCallBack poiCallBack) throws Exception
{
HSSFSheet sheet = wb.createSheet(EXCEL_DEFAULT_SHEET_NAME);
HSSFRow headerRow = sheet.createRow((short) 0);


HSSFCellStyle style = getExcelStyle(wb);


int cellNumber = tableHeader.length;
for (int i = 0; i < cellNumber; i++)
{
HSSFCell headerCell = headerRow.createCell(i);
headerCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);


headerCell.setCellValue(String.valueOf(tableHeader[i]));
headerCell.setCellStyle(style);
sheet.setColumnWidth(i, 5500);
}
poiCallBack.createRowsAndCells(sheet);
}


/**
* @param wb
*            excel工作空间
* @param tableHeader
*            表格头部数组
* @throws Exception
*/
public static void createExcelTemplate(HSSFWorkbook wb, String[] tableHeader)
throws Exception
{
HSSFSheet sheet = wb.createSheet(EXCEL_DEFAULT_SHEET_NAME);
HSSFRow headerRow = sheet.createRow((short) 0);
int cellNumber = tableHeader.length;
HSSFCellStyle style = getExcelStyle(wb);
for (int i = 0; i < cellNumber; i++)
{
HSSFCell headerCell = headerRow.createCell(i);
headerCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
headerCell.setCellValue(tableHeader[i]);
headerCell.setCellStyle(style);
sheet.setColumnWidth(i, 5500);
}
}


/**
* method:getExcelStyle设置excel单元格样式
* 
* @param wb
*            excel工作空间对象
* @return
*/
public static HSSFCellStyle getExcelStyle(HSSFWorkbook wb)
{
HSSFCellStyle style = wb.createCellStyle();
style.setFillForegroundColor((short) 13);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = wb.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 16);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
style.setWrapText(true);
return style;
}


/**
* method:getExcelCellValue获 得excel 当个表格的返回值,更据下面的传值判断
* 
* @param cell
* @return ret;
*/
public static String getExcelCellValue(Cell cell)
{
String ret = "";
try
{
if (cell == null)
{
ret = "";
} else if (cell.getCellType() == Cell.CELL_TYPE_STRING)
{
ret = cell.getStringCellValue().trim();
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
{
if (DateUtil.isCellDateFormatted(cell))
{
Date date = cell.getDateCellValue();
ret = "" + sFormat.format(date);
} else
{
ret = "" + cell.getNumericCellValue();
String temp = ret.substring(ret.indexOf(".") + 1,
ret.length());
try
{
if (Integer.parseInt(temp) == 0)
{
ret = ret.substring(0, ret.indexOf("."));
}
} catch (Exception ex)
{
}
}
} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA)
{
ret = cell.getCellFormula();
} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR)
{
ret = "" + cell.getErrorCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN)
{
ret = "" + cell.getBooleanCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK)
{
ret = "";
}
} catch (Exception ex)
{
ex.printStackTrace();
ret = "";
}
return ret;
}


public interface PoiCallBack
{
public void createRowsAndCells(HSSFSheet sheet);
}


public static void main(String[] args)
{
InputStream is = null;
try
{
is = new FileInputStream(
"C:\\Users\\Administrator\\Desktop\\1\\1.xls");
List<String[]> list = importFile(is, "test.xls", 1);
exportFile("C:/Users/Administrator/Desktop/2.xls", list,
new String[]
{ "1", "1", "1", "1" });
System.out.println(list);
} catch (Exception e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}


}


}