All Downloads are FREE. Search and download functionalities are using the official Maven repository.
Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
top.doudou.common.tool.excel.ExcelUtils Maven / Gradle / Ivy
package top.doudou.common.tool.excel;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import top.doudou.common.tool.constant.SysConstant;
import top.doudou.common.tool.exception.CustomException;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* excel工具类
* @author 傻男人<[email protected] >
* @date 2020-03-26
*/
@Slf4j
public class ExcelUtils {
/**
* 将excel数据转换成实体类
* @param file excel文件
* @param target 实体类
* @param headRow 头所在的行号
* @param
* @return
* @throws IOException
* @throws IllegalAccessException
* @throws InstantiationException
* @throws InvocationTargetException
*/
public static List readExcel(MultipartFile file, Class target, int headRow) throws IOException, IllegalAccessException, InstantiationException, InvocationTargetException {
checkFile(file);
Workbook workbook = getWorkBook(file);
List result = Lists.newArrayList();
if (workbook != null) {
//只是解析excel中的第一个sheet
Sheet sheet = workbook.getSheetAt(0);
if (sheet != null) {
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
Map propertyMap = null;
if(lastRowNum > 0 ){
//生成属性和列对应关系的map，Map<类属性名，对应一行的第几列>
propertyMap=generateColumnPropertyMap(sheet,headRow, EntityAnnotationUtils.getAnnotationProperty(target));
}
Set> entrySet = propertyMap.entrySet();
for(int rowNum = firstRowNum + headRow; rowNum <= lastRowNum; ++rowNum) {
Row row = sheet.getRow(rowNum);
if (row != null) {
T instance = target.newInstance();
boolean flag = true;
for (Map.Entry entry : entrySet) {
Object property=getCellValue(row.getCell(entry.getValue()));
if(property != null && !property.equals("")){
flag = false;
BeanUtils.setProperty(instance, entry.getKey(), property);
}
}
if(!flag){
try {
BeanUtils.setProperty(instance, "excelRow", rowNum);
}catch (Exception e){}
result.add(instance);
}
}
}
}
}
return result;
}
/**
* 检查excel文件
* @param file
* @throws IOException
*/
public static void checkFile(MultipartFile file) throws IOException {
if (null == file) {
log.error("文件不存在！");
throw new FileNotFoundException("文件不存在！");
} else {
String fileName = file.getOriginalFilename();
if (!fileName.endsWith("xls") && !fileName.endsWith("xlsx")) {
log.error(fileName + "不是excel文件");
throw new IOException(fileName + "不是excel文件");
}
}
}
public static Workbook getWorkBook(MultipartFile file) {
String fileName = file.getOriginalFilename();
Object workbook = null;
try {
InputStream is = file.getInputStream();
if (fileName.endsWith("xls")) {
workbook = new HSSFWorkbook(is);
} else if (fileName.endsWith("xlsx")) {
workbook = new XSSFWorkbook(is);
}
return (Workbook)workbook;
} catch (IOException var4) {
log.info(var4.getMessage());
return null;
}
}
/**
* 生成一个属性-列的对应关系的map
* @param sheet 表
* @param alias 别名
* @return
*/
private static Map generateColumnPropertyMap(Sheet sheet,int headRow,LinkedHashMap alias) {
if(alias == null || alias.isEmpty()){
throw new CustomException("实体类上需加上@ExcelMapping注解");
}
Map propertyMap=new HashMap<>();
Row row = sheet.getRow(headRow-1);
short firstCellNum = row.getFirstCellNum();
short lastCellNum = row.getLastCellNum();
for(int i=firstCellNum;i excelWriteProperty){
if(excelWriteProperty == null){
return;
}
//设置标题的显示样式
headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
HSSFRow headerRow = sheet.createRow(0);
for (Map.Entry entry : excelWriteProperty.entrySet()) {
//设置列的宽度
sheet.setColumnWidth(entry.getKey(), entry.getValue().getColumnWidth());
//设置表头的值
HSSFCell cell = headerRow.createCell(entry.getKey());
cell.setCellValue(entry.getValue().getValue());
cell.setCellStyle(headerStyle);
}
}
/**
* 将数据导出到excel中
* @param list 数据
* @param documentSummaryInfo 文档的摘要信息
* @param target 数据的class
* @param
* @return
*/
public static HSSFWorkbook dateToExcel(List list,DocumentSummaryInfo documentSummaryInfo,Class target){
HSSFWorkbook workbook = createBaseSheet(documentSummaryInfo);
//创建Excel表单
HSSFSheet sheet = workbook.createSheet(documentSummaryInfo == null?"sheet1":documentSummaryInfo.getSheetName());
HSSFCellStyle headerStyle = workbook.createCellStyle();
/**
* 获取写入的属性
*/
LinkedHashMap excelWriteProperty = EntityAnnotationUtils.getExcelWriteProperty(target);
if(excelWriteProperty.isEmpty()){
throw new CustomException("实体类中属性缺少ExcelMapping注解");
}
Set> entrySet = excelWriteProperty.entrySet();
//设置表头
setSheetHeader(sheet,headerStyle,excelWriteProperty);
//创建日期显示格式
HSSFCellStyle dateCellStyle = workbook.createCellStyle();
dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
// //将数据写入到excel中
for(int i = 0; i < list.size(); i++){
HSSFRow row = sheet.createRow(i + 1);
T t = list.get(i);
for (Map.Entry entry : entrySet) {
Object value = null;
String fieldName = entry.getValue().getFieldName();
try {
value = t.getClass().getMethod("get"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1)).invoke(t);
if(t.getClass().getDeclaredField(fieldName).getType() == Date.class){
HSSFCell cell = row.createCell(entry.getKey());
cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(value));
cell.setCellStyle(dateCellStyle);
continue;
}
}catch (Exception e){}
row.createCell(entry.getKey()).setCellValue(value == null ? "":value.toString());
}
}
return workbook;
}
public static void exportDate(HttpServletResponse response, String sheetName, List list, String fileName, Class target){
DocumentSummaryInfo documentSummaryInfo = new DocumentSummaryInfo();
documentSummaryInfo.setSheetName(sheetName);
HSSFWorkbook workbook = dateToExcel(list, documentSummaryInfo, target);
try{
OutputStream os = response.getOutputStream();
response.reset();
response.setHeader("Content-disposition",
"attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));
response.setContentType(SysConstant.FILE_CONTENT_TYPE);
workbook.write(os);
os.close();
}catch (IOException e){
e.printStackTrace();
throw new CustomException("文件导出出错,错误的原因为："+e.getMessage());
}
}
public static void lastColumnWrite(String filePath, List importResult,String uploadPath) {
filePath = fileExist(filePath,uploadPath);
FileInputStream in = null;
FileOutputStream out = null;
try {
in = new FileInputStream(filePath);
Workbook workbook = getWorkBook(filePath,in);
Sheet sheet = workbook.getSheetAt(0);
out = new FileOutputStream(filePath);
int columnNum = sheet.getRow(0).getPhysicalNumberOfCells();
Row row = sheet.getRow(0);
row.createCell(columnNum).setCellValue("导入备注");
//追加列数据
for(int i=0;i
