import com.qingxing.ManagerComplex.exception.DataNotFoundException;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import java.io.ByteArrayOutputStream;
import java.lang.reflect.Field;
import java.nio.charset.Charset;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
/**
* Excel导出工具类
*
* @author zyj34
*
*/
public class ExportUtils {
/**
* 导出结构
* @param filename
* @param data
* @param titles
* @param columns
* @return
*/
public static ResponseEntity<byte[]> getExportResultsEntity(String filename, List data, String[] titles, String[] columns) {
byte[] bytes = null;
if (data == null) {
throw new DataNotFoundException("导出失败");
}
else {
bytes = ExportUtils.outputExcelEntity(filename,data,titles,columns);
}
filename = StringUtils.isBlank(filename) ? "Book1" : filename;
HttpHeaders headers = new HttpHeaders();
headers.add("filename", filename + ".xls");
headers.setContentType(MediaType.parseMediaType("application/vnd.ms-excel"));
headers.setContentDispositionFormData("attchemnt", filename + ".xls", Charset.forName("UTF-8"));
return new ResponseEntity<>(bytes, headers, HttpStatus.CREATED);
}
/**
* 导出结果
* @param filename
* @param data
* @param titles
* @param columns
* @return
*/
public static ResponseEntity<byte[]> getExportResultsMap(String filename, List<Map<String, Object>> data, String[] titles, String[] columns) {
byte[] bytes = null;
if (data == null) {
throw new DataNotFoundException("导出失败");
}
else {
bytes = ExportUtils.outputExcelMap(data,titles,filename,columns);
}
filename = StringUtils.isBlank(filename) ? "Book1" : filename;
HttpHeaders headers = new HttpHeaders();
headers.add("filename", filename + ".xls");
headers.setContentType(MediaType.parseMediaType("application/vnd.ms-excel"));
headers.setContentDispositionFormData("attchemnt", filename + ".xls", Charset.forName("UTF-8"));
return new ResponseEntity<>(bytes, headers, HttpStatus.CREATED);
}
/**
* 导出excel
* @param list
* @param titles
* @param sheetname
* @param columns
* @return
*/
public static byte[] outputExcelMap(List<Map<String, Object>> list, String[] titles, String sheetname, String[] columns) {
return outputExcelMap(list, titles, sheetname, columns, new String[columns.length]);
}
/**
* 导出excel
* @param list 数据
* @param titles 标题
* @param sheetname 工作表名称
* @param columns 每列对应的数据key值
* @param convernames 值转换字符,转换后的名称,分割,从0开始
* @return
*/
public static byte[] outputExcelMap(List<Map<String, Object>> list, String[] titles, String sheetname, String[] columns, String[] convernames) {
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
HSSFWorkbook workbook = null;
try {
workbook = new HSSFWorkbook();
sheetname = StringUtils.isBlank(sheetname) ? "sheet1" : sheetname;
HSSFSheet sheet = workbook.createSheet(sheetname);
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
int rowNum = 0;
HSSFRow row = sheet.createRow((short) rowNum);
HSSFCell cell = null;
for (int i = 0; i < titles.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(cellStyle);
cell.setCellType(CellType.STRING);
cell.setCellValue(titles[i]);
sheet.setColumnWidth(i, 15 * 256);
}
rowNum++;
for (int j = 0; j < list.size(); j++) {
row = sheet.createRow((short) rowNum);
Map<String, Object> map = list.get(j);
for (int i = 0; i < columns.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(cellStyle);
cell.setCellType(CellType.STRING);
String key = columns[i];
Object value = map.get(key);
if (value != null) {
String convername = convernames[i];
if (convername != null && !"".equals(convername)) {
value = Arrays.asList(convername.split(",")).get(Integer.parseInt(value.toString()));
}
cell.setCellValue(value.toString());
} else {
cell.setCellValue("");
}
}
rowNum++;
}
workbook.write(byteArrayOutputStream);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return byteArrayOutputStream.toByteArray();
}
public static byte[] AssoutputExcel(String sheetname, List<Map<String, Object>> list1,
List<Map<String, Object>> list2, String[] titles1, String[] titles2, String[] columns1,
String[] columns2,String[] convernames1,String[] convernames2) {
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
HSSFWorkbook workbook = null;
try {
workbook = new HSSFWorkbook();
sheetname = StringUtils.isBlank(sheetname) ? "sheet1" : sheetname;
HSSFSheet sheet = workbook.createSheet(sheetname);
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
int rowNum = 0;
HSSFRow row = sheet.createRow((short) rowNum);
HSSFCell cell = null;
cell = row.createCell(0);
cell.setCellStyle(cellStyle);
cell.setCellType(CellType.STRING);
cell.setCellValue("个人信息");
sheet.setColumnWidth(0, 60 * 256);
rowNum++;
row = sheet.createRow((short) rowNum);
for (int i = 0; i < titles1.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(cellStyle);
cell.setCellType(CellType.STRING);
cell.setCellValue(titles1[i]);
sheet.setColumnWidth(i, 15 * 256);
}
rowNum++;
for (int j = 0; j < list1.size(); j++) {
row = sheet.createRow((short) rowNum);
Map<String, Object> map = list1.get(j);
for (int i = 0; i < columns1.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(cellStyle);
cell.setCellType(CellType.STRING);
String key = columns1[i];
Object value = map.get(key);
if (value != null) {
String convername1 = convernames1[i];
if (convername1 != null && !"".equals(convername1)) {
value = Arrays.asList(convername1.split(",")).get(Integer.parseInt(value.toString()));
}
cell.setCellValue(value.toString());
} else {
cell.setCellValue("");
}
}
rowNum++;
}
rowNum++;
row = sheet.createRow((short) rowNum);
cell = row.createCell(0);
cell.setCellStyle(cellStyle);
cell.setCellType(CellType.STRING);
cell.setCellValue("关联人员信息");
sheet.setColumnWidth(0, 45 * 256);
rowNum++;
row = sheet.createRow((short) rowNum);
for (int i = 0; i < titles2.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(cellStyle);
cell.setCellType(CellType.STRING);
cell.setCellValue(titles2[i]);
sheet.setColumnWidth(i, 15 * 256);
}
rowNum++;
for (int j = 0; j < list2.size(); j++) {
row = sheet.createRow((short) rowNum);
Map<String, Object> map = list2.get(j);
for (int i = 0; i < columns2.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(cellStyle);
cell.setCellType(CellType.STRING);
String key = columns2[i];
Object value = map.get(key);
if (value != null) {
String convername2 = convernames2[i];
if (convername2 != null && !"".equals(convername2)) {
value = Arrays.asList(convername2.split(",")).get(Integer.parseInt(value.toString()));
}
cell.setCellValue(value.toString());
} else {
cell.setCellValue("");
}
}
rowNum++;
}
workbook.write(byteArrayOutputStream);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return byteArrayOutputStream.toByteArray();
}
public static byte[] outputExcelEntity(String sheetname,List list, String[] titles,String[] columns) {
return outputExcelEntity(sheetname,list, titles,columns, new String[columns.length]);
}
public static byte[] outputExcelEntity(String sheetname,List list, String[] titles,String[] columns,String[] convernames) {
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
HSSFWorkbook workbook = null;
try {
workbook = new HSSFWorkbook();
sheetname = StringUtils.isBlank(sheetname) ? "sheet1" : sheetname;
HSSFSheet sheet = workbook.createSheet(sheetname);
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
int rowNum = 0;
HSSFRow row = sheet.createRow((short) rowNum);
HSSFCell cell = null;
for (int i = 0; i < titles.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(cellStyle);
cell.setCellType(CellType.STRING);
cell.setCellValue(titles[i]);
sheet.setColumnWidth(i, 15 * 256);
}
rowNum++;
for (int j = 0; j < list.size(); j++) {
row = sheet.createRow((short) rowNum);
Object entity = list.get(j);
Class<?> clz = entity.getClass();
for (int i = 0; i < columns.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(cellStyle);
cell.setCellType(CellType.STRING);
String key = columns[i];
Field field = clz.getDeclaredField(key);
field.setAccessible(true);
Object value = field.get(entity);
if (value != null) {
String convername = convernames[i];
if (convername != null && !"".equals(convername)) {
value = Arrays.asList(convername.split(",")).get(Integer.parseInt(value.toString()));
}
cell.setCellValue(value.toString());
} else {
cell.setCellValue("");
}
}
rowNum++;
}
workbook.write(byteArrayOutputStream);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return byteArrayOutputStream.toByteArray();
}
}
List<FilingUnit> list = managementUnitsService.getFilingUnitByUnitIds(unitIdArr);
String[] titles = {“name”,"age"};
String[] columns = {"林","18"};
return ExportUtils.getExportResultsEntity(filename, list, titles, columns);