内容纲要
ExcelUtil.java
package com.lyarc.tp.corp.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* The <code>ExcelUtil</code> 与 {@link ExcelCell}搭配使用
*
* @author sargeras.wang
* @version 1.0, Created at 2013年9月14日
*/
public class ExcelUtil {
private static Logger LG = LoggerFactory.getLogger(ExcelUtil.class);
/**
* 导出 Excel
*
* @param title 列标题
* @param list 二维数据行
* @param path 文件路径
*/
public static void ExportExcel(List<Object> list, String path) {
// try {
// File dbfFile = new File(path);
// if (!dbfFile.exists() || dbfFile.isDirectory()) {
// dbfFile.createNewFile();
// }
//
// FileOutputStream out = new FileOutputStream(dbfFile);
// Workbook wb = null;
//
// if (path.toLowerCase().endsWith(".xls")) {
// wb = new HSSFWorkbook();
// } else {
// wb = new XSSFWorkbook();
// }
//
// Sheet sheet = wb.createSheet("Sheet1");
// sheet.setDefaultColumnWidth(15);
// sheet.setDefaultRowHeight((short) (20 * 20));
// CellStyle style = wb.createCellStyle();
// style.setAlignment(CellStyle.ALIGN_CENTER);
// for (int r = 0; r < list.size(); r++) {
// List dataRow = (List) list.get(r);
// if (dataRow != null) {
// Row row = sheet.createRow(r);
// for (int c = 0; c < dataRow.size(); c++) {
// Object dataCell = dataRow.get(c);
// if (dataCell != null) {
// Cell cell = row.createCell(c);
// cell.setCellStyle(style);
// cell.setCellValue(dataCell.toString());
// }
// }
// }
// }
//
// wb.write(out);
// wb.close();
// out.close();
// } catch (IOException e) {
// e.printStackTrace();
// } finally {
//
// }
//重载方法，提供单元格合并处理
ExportExcel(list, path, null);
}
public static void ExportExcel(List<Object> list, String path, List<Integer> mergeIndex) {
File dbfFile = new File(path);
FileOutputStream out = null;
Workbook wb = null;
try {
if (!dbfFile.exists() || dbfFile.isDirectory()) {
dbfFile.createNewFile();
}
out = new FileOutputStream(dbfFile);
if (path.toLowerCase().endsWith(".xls")) {
wb = new HSSFWorkbook();
} else {
wb = new XSSFWorkbook();
}
boolean mergeList = mergeIndex!=null&&mergeIndex.size()>0;
Sheet sheet = wb.createSheet("Sheet1");
sheet.setDefaultColumnWidth(15);
sheet.setDefaultRowHeight((short) (20 * 20));
CellStyle style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//存储合并的比对值:key 列,value[0] 比对值,value[1] 起始行
HashMap<Integer,Object[]> compareMap = new HashMap<>();
for (int r = 0; r < list.size(); r++) {
List dataRow = (List) list.get(r);
if (dataRow != null) {
Row row = sheet.createRow(r);
for (int c = 0; c < dataRow.size(); c++) {
Object dataCell = dataRow.get(c);
if (dataCell != null) {
Cell cell = row.createCell(c);
cell.setCellStyle(style);
cell.setCellValue(dataCell.toString());
}
//判断列是否要合并
if(mergeList&&mergeIndex.contains(c) && r>0){
Object[] compare = compareMap.get(c);
if(compare!=null){//该列有比对值
if(!compare[0].equals(dataCell)){//当前值和比对值不同,则将前面列进行合并
//合并单元格范围
CellRangeAddress cra=new CellRangeAddress((int) compare[1],r-1,c,c);
sheet.addMergedRegion(cra);
if(dataCell==null){//当前值为空则移除比对值
compareMap.remove(c);
}else {//替换比对值
compareMap.put(c,new Object[]{dataCell,r});
}
}
}else if(dataCell!=null){
//该列没有比对值，且当前值不为空，则将当前值存为比对值供后面比对
compareMap.put(c,new Object[]{dataCell,r});
}
//如果当前行是最后一行，直接合并
if(r==list.size()-1 && compare!=null){
//合并单元格范围
CellRangeAddress cra=new CellRangeAddress((int) compare[1],r-1,c,c);
sheet.addMergedRegion(cra);
}
}
}
}
}
wb.write(out);
} catch (IOException e) {
e.printStackTrace();
} finally {
if(wb!=null){
try {
wb.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(out!=null){
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/**
* 读取 excel 数据
*
* @param path
* @return
* @throws IOException
*/
public static List<List<List<Object>>> readExcel(String path) throws IOException {
if (path.endsWith(".xls")) {
return readXls(path);
} else {
return readXlsx(path);
}
}
// 2003-2007
public static List<List<List<Object>>> readXls(String path) throws IOException {
InputStream is = new FileInputStream(path);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
List<List<List<Object>>> tables = new ArrayList<List<List<Object>>>();
// Read the Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
List<List<Object>> table = new ArrayList<List<Object>>();
tables.add(table);
int cols = 0;
// Read the Row
for (int rowNum = 0; rowNum < hssfSheet.getLastRowNum() + 1; rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
List<Object> row = new ArrayList<Object>();
table.add(row);
if (rowNum == 0) {
cols = hssfRow.getLastCellNum();
}
for (int col = 0; col < cols; col++) {
HSSFCell cell = hssfRow.getCell(col);
cell.setCellType(cell.CELL_TYPE_STRING);
if (cell != null) {
row.add(getValue(cell));
} else {
row.add(null);
}
}
}
}
}
return tables;
}
public static List<List<List<Object>>> readXlsx(String path) throws IOException {
InputStream is = new FileInputStream(path);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
List<List<List<Object>>> tables = new ArrayList<List<List<Object>>>();
// Read the Sheet
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
List<List<Object>> table = new ArrayList<List<Object>>();
tables.add(table);
int cols = 0;
// Read the Row
for (int rowNum = 0; rowNum < xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow != null) {
List<Object> row = new ArrayList<Object>();
table.add(row);
if (rowNum == 0) {
cols = xssfRow.getLastCellNum();
}
for (int col = 0; col < cols; col++) {
XSSFCell cell = xssfRow.getCell(col);
if (cell != null) {
row.add(getValue(cell));
} else {
row.add(null);
}
}
}
}
}
return tables;
}
@SuppressWarnings("static-access")
private static String getValue(XSSFCell xssfRow) {
if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfRow.getBooleanCellValue());
} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
return String.valueOf(xssfRow.getNumericCellValue());
} else {
return String.valueOf(xssfRow.getStringCellValue());
}
}
@SuppressWarnings("static-access")
private static String getValue(HSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(hssfCell.getNumericCellValue());
} else {
return String.valueOf(hssfCell.getStringCellValue());
}
}
}
ExcelExportUtil.java
package com.lyarc.tp.corp.util;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.List;
import java.util.Map;
public class ExcelExportUtil {
private static Logger LOG = LoggerFactory.getLogger(ExcelExportUtil.class);
/**
* @param <T>
* @param response
* @param request
* @param filename
* 导出的文件名
* @param titles
* 标题列和列名的对应.column:列名,title标题名
* @param records
* 记录
* @throws SecurityException
* @throws NoSuchMethodException
* @throws InvocationTargetException
* @throws IllegalArgumentException
* @throws IllegalAccessException
*/
@SuppressWarnings("unchecked")
public static <T> void exportByRecord(HttpServletResponse response, HttpServletRequest request, String filename,
List<Pair> titles, List<T> records) {
try {
exportByRecord(response, request, filename, new SheetData<T>(titles, records));
} catch (NoSuchMethodException | SecurityException | IllegalAccessException | IllegalArgumentException
| InvocationTargetException e) {
LOG.error(String.format("导出%s报表出错", filename), e);
}
}
/**
* @param response
* @param request
* @param filename
* 导出的文件名
* @param sheetDatas
* 产生一个sheet需要的数据
* @throws SecurityException
* @throws NoSuchMethodException
* @throws InvocationTargetException
* @throws IllegalArgumentException
* @throws IllegalAccessException
*/
public static <T> void exportByRecord(HttpServletResponse response, HttpServletRequest request, String filename,
SheetData<T>... sheetDatas) throws NoSuchMethodException, SecurityException, IllegalAccessException,
IllegalArgumentException, InvocationTargetException {
HSSFWorkbook wb = new HSSFWorkbook();
// 标题行的style
CellStyle titleCellStyle = wb.createCellStyle();
titleCellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 居中
titleCellStyle.setWrapText(false); // 自动换行
// 内容行的style
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 垂直居中
cellStyle.setWrapText(false);
//特殊行的style
CellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 垂直居中
style.setWrapText(false);
//特殊行的字体颜色
HSSFFont fontColor = wb.createFont();
fontColor.setColor(HSSFColor.GREY_25_PERCENT.index);
// 设置红色字体
HSSFFont font = wb.createFont();
font.setColor(HSSFColor.RED.index);
// 多张sheet
for (SheetData<T> sheetData : sheetDatas) {
List<Pair> titles = sheetData.titles;
List<T> records = sheetData.records;
HSSFSheet sheet = wb.createSheet();
int rowIndex = 0, cellIndex = 0;
HSSFRow row = null;
HSSFCell cell = null;
// 创建标题行
row = sheet.createRow(rowIndex);
// 修改标题行高
row.setHeight((short) 650);
rowIndex++;
for (Pair pair : titles) {
// 修改每一列的列宽
sheet.setColumnWidth((short) cellIndex, (short) (35.7 * 100));
HSSFCellStyle cellStyleNew = wb.createCellStyle();
cellStyleNew.setWrapText(true);
cell = row.createCell(cellIndex);
cell.setCellStyle(cellStyleNew); // 设置样式
cellIndex++;
String[] sourceStrArray = pair.title.split(",");
if (sourceStrArray.length > 1) {
cell.setCellValue(sourceStrArray[0] + "\r\n" + sourceStrArray[1]);
} else {
cell.setCellValue(pair.title);
}
}
// 处理每一行
for (T record : records) {
row = sheet.createRow(rowIndex);
row.setHeight((short) 450);
rowIndex++;
cellIndex = 0;
int[] f = null;
for (Pair pair : titles) {
cell = row.createCell(cellIndex);
cell.setCellStyle(cellStyle); // 设置样式
cellIndex++;
String methodName = pair.column;// userId/getUserId()
Object value = null;
String getMethodName = "get" + methodName.substring(0, 1).toUpperCase() + methodName.substring(1);
Method method = record.getClass().getMethod(getMethodName);
Object obj = method.invoke(record);
value = obj;
// 根据数据类型做判断
if (value != null) {
if (methodName.equals("statusName")) {
f = setFont(font, cell, value,fontColor);// 根据考勤状态给单元格添加红色题样式
} else {
if (f != null) {
setAttendExceptionFond(f, font, cell, value, methodName);
}
else {
cell.setCellValue(value.toString());
}
}
}
}
}
}
// 序号
writeStream(filename, wb, response, request);
}
public static void exportByRecord1(HttpServletResponse response, HttpServletRequest request, String filename,
List<Pair> titles, List<Map<String, String>> records) {
try {
exportByRecordMap(response, request, filename, new SheetData<Map<String, String>>(titles, records));
} catch (NoSuchMethodException | SecurityException | IllegalAccessException | IllegalArgumentException
| InvocationTargetException e) {
LOG.error(String.format("导出%s报表出错", filename), e);
}
}
public static void exportByRecordMap(HttpServletResponse response, HttpServletRequest request, String filename,
SheetData<Map<String, String>>... sheetDatas) throws NoSuchMethodException, SecurityException,
IllegalAccessException, IllegalArgumentException, InvocationTargetException {
HSSFWorkbook wb = new HSSFWorkbook();
// 标题行的style
CellStyle titleCellStyle = wb.createCellStyle();
titleCellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 居中
titleCellStyle.setWrapText(false); // 自动换行
// 内容行的style
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 垂直居中
cellStyle.setWrapText(false);
//特殊cell的样式
CellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 垂直居中
style.setWrapText(false);
//特殊行的字体颜色
HSSFFont fontColor = wb.createFont();
fontColor.setColor(HSSFColor.GREY_25_PERCENT.index);
// 设置红色字体
HSSFFont font = wb.createFont();
font.setColor(HSSFColor.RED.index);
// 多张sheet
for (SheetData<Map<String, String>> sheetData : sheetDatas) {
List<Pair> titles = sheetData.titles;
List<Map<String, String>> records = sheetData.records;
HSSFSheet sheet = wb.createSheet();
int rowIndex = 0, cellIndex = 0;
HSSFRow row = null;
HSSFCell cell = null;
// 创建标题行
row = sheet.createRow(rowIndex);
// 修改标题行高
row.setHeight((short) 650);
rowIndex++;
for (Pair pair : titles) {
// 修改每一列的列宽
sheet.setColumnWidth((short) cellIndex, (short) (35.7 * 100));
HSSFCellStyle cellStyleNew = wb.createCellStyle();
cellStyleNew.setWrapText(true);
cell = row.createCell(cellIndex);
cell.setCellStyle(cellStyleNew); // 设置样式
cellIndex++;
String[] sourceStrArray = pair.title.split(",");
if (sourceStrArray.length > 1) {
cell.setCellValue(sourceStrArray[0] + "\r\n" + sourceStrArray[1]);
} else {
cell.setCellValue(pair.title);
}
}
// 处理每一行
for (Map<String, String> record : records) {
row = sheet.createRow(rowIndex);
row.setHeight((short) 450);
rowIndex++;
cellIndex = 0;
int[] f = null;
for (Pair pair : titles) {
cell = row.createCell(cellIndex);
cell.setCellStyle(cellStyle); // 设置样式
cellIndex++;
String methodName = pair.column;// userId/getUserId()
String value = record.get(methodName);
// 根据数据类型做判断
if (value != null) {
//if (methodName.equals("statusName")) {
f = setFont(font, cell, value,fontColor);// 根据考勤状态给单元格添加红色题样式
/*} else {
if (f != null) {
setAttendExceptionFond(f, font, cell, value, methodName);
}else {
cell.setCellValue(value);
}
}*/
}
}
}
}
// 序号
writeStream(filename, wb, response, request);
}
/**
* 设置单元列为考勤状态的红色字体 以及 根据数据内容获得签到签退的异常数据
*/
public static int[] setFont(HSSFFont font, HSSFCell cell, Object value,HSSFFont fontColor) {
HSSFRichTextString richString = new HSSFRichTextString(value.toString());
String[] str;
int f[] = { -1, -1, -1, -1 };
try {
str = value.toString().split("/");
int len = value.toString().length();
int a = value.toString().indexOf("/");
if(str[0].indexOf("非工作日") > -1){
//cell.setCellStyle(style);
richString.applyFont(fontColor);
}
if(str.length > 1){
if (setArray(str[0], f, 0)) {// 签到状态设置红色字体
richString.applyFont(0, a, font);
}
if (setArray(str[1], f, 1)) {
richString.applyFont(a + 1, len, font);
}
}
cell.setCellValue(richString);
return f;
} catch (Exception e) {
cell.setCellValue(richString);
return null;
}
}
/**
* 获得签到签退是的考勤状态 数据结果存储到数组中 1为异常 0为正常 数组初始值设为-1 c值 0为判断签到的数据 1为判断签退时的数据
*/
public static boolean setArray(String str, int[] a, int c) {
boolean f = false;
switch (c) {
case 0:
if ((str.indexOf("迟到") > -1) && (str.indexOf("偏离") > -1)) {
a[0] = 1;
a[1] = 1;
f = true;
} else {
if ((str.indexOf("迟到") > -1)) {
a[0] = 1;
a[1] = 0;
f = true;
} else {
if ((str.indexOf("偏离") > -1)) {
a[0] = 0;
a[1] = 1;
f = true;
} else {
if((str.indexOf("未打卡") > -1)){
a[0] = a[1] = 1;
f = true;
}else{
a[0] = a[1] = 0;
f = false;
}
}
}
}
break;
case 1:
if ((str.indexOf("早退") > -1) && (str.indexOf("偏离") > -1)) {
a[2] = 1;
a[3] = 1;
f = true;
} else {
if ((str.indexOf("早退") > -1)) {
a[2] = 1;
a[3] = 0;
f = true;
} else {
if ((str.indexOf("偏离") > -1)) {
a[2] = 0;
a[3] = 1;
f = true;
} else {
if((str.indexOf("未打卡") > -1)){
a[2] = a[3] = 1;
f = true;
}else{
a[2] = a[3] = 0;
f = false;
}
}
}
}
break;
}
return f;
}
/**
* 根据参数给签到签退时间地点设置红色字体
*/
public static void setAttendExceptionFond(int[] a, HSSFFont f, HSSFCell cell, Object value, String methodName) {
String str = value.toString();
int b = str.length();
HSSFRichTextString richString = new HSSFRichTextString(str);
if (methodName.equals("signInTime")) {
if (a[0] == 1) {
richString.applyFont(0, b, f);
}
}
if (methodName.equals("signInAddress")) {
if (a[1] == 1) {
richString.applyFont(0, b, f);
}
}
if (methodName.equals("signOutTime")) {
if (a[2] == 1) {
richString.applyFont(0, b, f);
}
}
if (methodName.equals("signOutAddress")) {
if (a[3] == 1) {
richString.applyFont(0, b, f);
}
}
if (methodName.equals("absenteeismDc")) {
if (!str.equals("——")) {
richString.applyFont(0, b, f);
}
}
cell.setCellValue(richString);
}
/**
* 写到输出流
*/
private static void writeStream(String filename, HSSFWorkbook wb, HttpServletResponse response,
HttpServletRequest request) {
try {
String agent = request.getHeader("USER-AGENT");
filename += ".xls";
filename.replaceAll("/", "-");
// filename = new String(filename.getBytes("gbk"),"ISO8859_1");
if (agent.toLowerCase().indexOf("firefox") > 0) {
filename = new String(filename.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
} else {
filename = URLEncoder.encode(filename, "UTF-8");
}
response.reset();
response.setCharacterEncoding(StandardCharsets.UTF_8.name());
response.setHeader("Content-Disposition", "attachment; filename=" + filename);
response.setContentType("application/octet-stream;charset=UTF-8");
// 更新完后，设定cookie，用于页面判断更新完成后的标志
Cookie status = new Cookie("updateStatus", "success");
status.setMaxAge(600);
response.addCookie(status);// 添加cookie操作必须在写出文件前，如果写在后面，随着数据量增大时cookie无法写入
OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
wb.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (IOException e) {
LOG.error("写入到输出流出错", e);
}
}
/**
* 标题列和列名的对应
*/
public static class Pair {
public String column;// 列名
public String title;// 标题
public Pair(String column, String title) {
this.column = column;
this.title = title;
}
}
/**
* 创建一个sheet需要的数据
*/
public static class SheetData<T> {
public List<Pair> titles;
public List<T> records;
public SheetData(List<Pair> titles, List<T> records) {
this.titles = titles;
this.records = records;
}
}
}