Excel导⼊导出⼯具类（完整版）ExcelUtil⼯具类
package l.util;
import java.io.*;
import flect.Field;
DateFormat;
SimpleDateFormat;
import java.util.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
/**
* Excel导⼊导出
* @Author: guandezhi
* @Date: 2019/3/9 9:47
*/
public class ExcelUtil {
/**
* 导出多个sheet的excel
* @param name
* @param mapList
* @param response
* @param <T>
*/
public static <T> void exportMultisheetExcel(String name, List<Map> mapList, HttpServletResponse response) {        BufferedOutputStream bos = null;
try {
String fileName = name + ".xlsx";
bos = getBufferedOutputStream(fileName, response);
doExport(mapList, bos);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (bos != null) {
bos.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 从excel中读内容
* @param filePath
* @param sheetIndex
* @return
*/
public static List<Map<String, String>> readExcel(String filePath, Integer sheetIndex) {
List<Map<String, String>> dataList = new ArrayList<>();
List<Map<String, String>> dataList = new ArrayList<>();
Workbook wb = ateWorkBook(filePath);
if (wb != null) {
Sheet sheet = wb.getSheetAt(sheetIndex);
int maxRownum = PhysicalNumberOfRows();
Row firstRow = Row(0);
int maxColnum = PhysicalNumberOfCells();
String columns[] = new String[maxColnum];
for (int i = 0; i < maxRownum; i++) {
Map<String, String> map = null;
if (i > 0) {
map = new LinkedHashMap<>();
firstRow = Row(i);
}
if (firstRow != null) {
String cellData = null;
for (int j = 0; j < maxColnum; j++) {
cellData = (String) Cell(j));
if (i == 0) {
columns[j] = cellData;
} else {
map.put(columns[j], cellData);
}
}
} else {
break;
}
if (i > 0) {
dataList.add(map);
}
}
}
return dataList;
}
private static BufferedOutputStream getBufferedOutputStream(String fileName, HttpServletResponse response) throws Exception {
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment;filename="
+ new Bytes("gb2312"), "ISO8859-1"));
return new OutputStream());
}
private static <T> void doExport(List<Map> mapList, OutputStream outputStream) {
int maxBuff = 100;
// 创建excel⼯作⽂本，100表⽰默认允许保存在内存中的⾏数
SXSSFWorkbook wb = new SXSSFWorkbook(maxBuff);
try {
for (int i = 0; i < mapList.size(); i++) {
Map map = (i);
String[] headers = (String[]) ("headers");
Collection<T> dataList = (Collection<T>) ("dataList");
String fileName = (String) ("fileName");
createSheet(wb, null, headers, dataList, fileName, maxBuff);
}
if (outputStream != null) {
wb.write(outputStream);
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
private static <T> void createSheet(SXSSFWorkbook wb, String[] exportFields, String[] headers, Collection<T> dataList, String fileName, int maxBuff) throws No
Sheet sh = wb.createSheet(fileName);
CellStyle style = wb.createCellStyle();
CellStyle style2 = wb.createCellStyle();
//创建表头
Font font = wb.createFont();
font.setFontName("微软雅⿊");
font.setFontHeightInPoints((short) 11);//设置字体⼤⼩
style.setFont(font);//选择需要⽤到的字体格式
style.setFillForegroundColor(HSSFColor.YELLOW.index);// 设置背景⾊
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style2.setFont(font);//选择需要⽤到的字体格式
style2.setFillForegroundColor(HSSFColor.WHITE.index);// 设置背景⾊
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ⽔平向下居中
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
Row headerRow = sh.createRow(0); //表头
int headerSize = headers.length;
for (int cellnum = 0; cellnum < headerSize; cellnum++) {
Cell cell = ateCell(cellnum);
cell.setCellStyle(style);
sh.setColumnWidth(cellnum, 4000);
cell.setCellValue(headers[cellnum]);
}
int rownum = 0;
Iterator<T> iterator = dataList.iterator();
while (iterator.hasNext()) {
T data = ();
Row row = sh.createRow(rownum + 1);
Field[] fields = Class(), exportFields);
for (int cellnum = 0; cellnum < headerSize; cellnum++) {
Cell cell = ateCell(cellnum);
cell.setCellStyle(style2);
Field field = fields[cellnum];
setData(field, data, Name(), cell);
}
rownum = sh.getLastRowNum();
// ⼤数据量时将之前的数据保存到硬盘
if (rownum % maxBuff == 0) {
((SXSSFSheet) sh).flushRows(maxBuff); // 超过100⾏后将之前的数据刷新到硬盘
}
}
}
private static <T> void doExport(String[] headers, String[] exportFields, Collection<T> dataList,                                    String fileName, OutputStream outputStream) {
int maxBuff = 100;
int maxBuff = 100;
// 创建excel⼯作⽂本，100表⽰默认允许保存在内存中的⾏数
SXSSFWorkbook wb = new SXSSFWorkbook(maxBuff);
try {
createSheet(wb, exportFields, headers, dataList, fileName, maxBuff);
if (outputStream != null) {
wb.write(outputStream);
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
/
**
* 获取单条数据的属性
*
* @param object
* @param property
* @param <T>
* @return
* @throws NoSuchFieldException
* @throws IllegalAccessException
*/
private static <T> Field getDataField(T object, String property) throws NoSuchFieldException, IllegalAccessException {        Field dataField;
if (ains(".")) {
String p = property.substring(0, property.indexOf("."));
dataField = Class().getDeclaredField(p);
return dataField;
} else {
dataField = Class().getDeclaredField(property);
}
return dataField;
}
private static Field[] getExportFields(Class<?> targetClass, String[] exportFieldNames) {
Field[] fields = null;
if (exportFieldNames == null || exportFieldNames.length < 1) {
fields = DeclaredFields();
} else {
fields = new Field[exportFieldNames.length];
for (int i = 0; i < exportFieldNames.length; i++) {
try {
fields[i] = DeclaredField(exportFieldNames[i]);
} catch (Exception e) {
try {
fields[i] = Superclass().getDeclaredField(exportFieldNames[i]);
} catch (Exception e1) {
throw new IllegalArgumentException("⽆法获取导出字段", e);
}
}
}
}
return fields;
}
/**
* 根据属性设置对应的属性值
*
* @param dataField 属性
* @param object    数据对象
* @param property  表头的属性映射
* @param cell      单元格
* @param <T>
* @return
* @return
* @throws IllegalAccessException
* @throws NoSuchFieldException
*/
private static <T> void setData(Field dataField, T object, String property, Cell cell)
throws IllegalAccessException, NoSuchFieldException {
dataField.setAccessible(true); //允许访问private属性
Object val = (object); //获取属性值
Sheet sh = Sheet(); //获取excel⼯作区
CellStyle style = CellStyle(); //获取单元格样式
int cellnum = ColumnIndex();
if (val != null) {
if (Type().toString().endsWith("String")) {
cell.setCellValue((String) val);
} else if (Type().toString().endsWith("Integer") || Type().toString().endsWith("int")) {
cell.setCellValue((Integer) val);
} else if (Type().toString().endsWith("Long") || Type().toString().endsWith("long")) {
cell.String());
} else if (Type().toString().endsWith("Double") || Type().toString().endsWith("double")) {
cell.setCellValue((Double) val);
} else if (Type().toString().endsWith("Date")) {
DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cell.setCellValue(format.format((Date) val));
} else if (Type().toString().endsWith("List")) {
List list1 = (List) val;
int size = list1.size();
for (int i = 0; i < size; i++) {
//加1是因为要去掉点号
int start = property.Name()) + Name().length() + 1;
excel最强教科书完全版pdfString tempProperty = property.substring(start, property.length());
Field field = (i), tempProperty);
Cell tempCell = cell;
if (i > 0) {
int rowNum = RowIndex() + i;
Row row = sh.getRow(rowNum);
if (row == null) {//另起⼀⾏
row = sh.createRow(rowNum);
/
/合并之前的空⽩单元格（在这⾥需要在header中按照顺序把list类型的字段放到最后，⽅便显⽰和合并单元格）                            for (int j = 0; j < ColumnIndex(); j++) {
sh.addMergedRegion(new RowIndex(), RowIndex() + size - 1, j, j));
Cell c = ateCell(j);
c.setCellStyle(style);
}
}
tempCell = ateCell(cellnum);
tempCell.setCellStyle(style);
}
//递归传参到单元格并获取偏移量（这⾥获取到的偏移量都是第⼆层后list的偏移量）
setData(field, (i), tempProperty, tempCell);
}
} else {
if (ains(".")) {
String p = property.substring(property.indexOf(".") + 1, property.length());
Field field = getDataField(val, p);
setData(field, val, p, cell);
} else {
cell.String());
}
}
}
}
private static Workbook createWorkBook(String filePath) {
Workbook wb = null;
if (filePath == null) {

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。