package com.zjson.alibaba.commons.tools.utils;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.NoSuchElementException;
public class EasyPoiUtil {
public static Workbook getExcelDataWorkbook(List<?> list, Class<?> pojoClass){
ExportParams exportParams = new ExportParams(null, "sheet1",ExcelType.HSSF);
exportParams.setStyle(ExcelStyleUtil.class);
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
return workbook;
}
public static void defaultExport(String fileName,Workbook workbook,HttpServletResponse response) throws IOException {
downLoadExcel(fileName, response, workbook);
}
public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
workbook.write(response.getOutputStream());
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
public static List<T> importDataFromExcel(MultipartFile file, Class<T> classObj) throws IOException {
List<T> importData = importExcel(file.getInputStream(), 1, classObj);
return importData;
}
public static <T> List<T> importExcel(InputStream inputStream,int sheetNum, Class<T> pojoClass) throws IOException {
if (inputStream == null) {
return null;
}
ImportParams params = new ImportParams();
params.setSheetNum(sheetNum);
params.setNeedSave(false);
params.setHeadRows(1);
try {
return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
} catch (NoSuchElementException e) {
throw new IOException("excel文件不能为空");
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
public static void addSheetComments(Workbook workbook,List<ExcelComment> excelCommentList){
Sheet sheet = workbook.getSheetAt(0);
for (ExcelComment excelComment : excelCommentList) {
Row row = sheet.getRow(excelComment.getRow());
Cell cell = row.getCell(excelComment.getCell());
Drawing p = sheet.createDrawingPatriarch();
Comment comment = p.createCellComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
comment.setString(new HSSFRichTextString(excelComment.getComment()));
cell.setCellComment(comment);
}
}
public static void addSheetValidation(Workbook workbook, int firstRow, int lastRow, int firstCol, int lastCol, String[] dataArray, int sheetHidden){
String hiddenName = "hidden_" + (int)((Math.random()*9+1)*100);
Sheet sheet = workbook.getSheetAt(0);
Sheet hidden = workbook.createSheet(hiddenName);
Cell cell = null;
for (int i = 0, length = dataArray.length; i < length; i++)
{
String name = dataArray[i];
Row row = hidden.createRow(i);
cell = row.createCell(0);
cell.setCellValue(name);
}
Name namedCell = workbook.createName();
namedCell.setNameName(hiddenName);
namedCell.setRefersToFormula(hiddenName + "!$A$1:$A$" + dataArray.length);
DVConstraint constraint = DVConstraint.createFormulaListConstraint(hiddenName);
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
sheet.addValidationData(validation);
}
public static List<String> getHeaderFields(InputStream inp) {
try {
return readHeader(0, 0, WorkbookFactory.create(inp));
} catch (IOException e) {
e.printStackTrace();
}
return Collections.emptyList();
}
public static List<String> getHeaderFields(String filePath) {
try {
return readHeader(0, 0, WorkbookFactory.create(new File(filePath)));
} catch (IOException e) {
e.printStackTrace();
}
return Collections.emptyList();
}
public static List<String> readHeader(int sheetNum,int rowNum , Workbook workbook) {
Sheet sheet = workbook.getSheetAt(sheetNum);
Row titleRow = sheet.getRow(rowNum);
int cellNum = titleRow.getLastCellNum();
List<String> headerList = new ArrayList<>(cellNum);
for (int i = 0; i < cellNum; i++) {
Cell cell = titleRow.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
headerList.add(cell.getStringCellValue());
}
return headerList;
}
public static void main(String[] args) throws IOException {
String filePath = "C:\\Users\\Administrator\\Desktop\\xc.xlsx";
List<String> headerList = getHeaderFields(filePath);
System.out.println(headerList);
}
}
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)