工具类,maplist是数据,mapkey是表格头
package com.huayi.zjjhtrain.utils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
@Slf4j
public class ExportExcelUtil {
public void exportExcel(String sheetName, List<Map<String,Object>> maplist,List<Map<String,String>> mapKey, HttpServletResponse response)
{
Workbook workbook = new SXSSFWorkbook();
// 生成一个表格
Sheet sheet = workbook.createSheet(sheetName);
// 设置表格默认列宽度为15个字节
//设置宽度
//sheet.setDefaultColumnWidth(20);
sheet.setDefaultColumnWidth(18);
//sheet.trackAllColumnsForAutoSizing();
// sheet.autoSizeColumn(0);
// 生成一个样式
CellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);//内容居中
// 生成一个字体
Font font = workbook.createFont();
font.setFontName("宋体");
font.setBold(true);//加粗
font.setFontHeightInPoints((short) 11);
// 把字体应用到当前的样式
style.setFont(font);
// 生成并设置另一个样式
CellStyle style2 = workbook.createCellStyle();
style2.setAlignment(HorizontalAlignment.CENTER);
// 生成另一个字体
Font font2 = workbook.createFont();
// font2.setBold(true);//加粗
// 把字体应用到当前的样式
style2.setFont(font2);
// 产生表格标题行
Row row = sheet.createRow(0);
Cell cellHeader;
if(mapKey==null||mapKey.size()<=0)
{
cellHeader= row.createCell(0);
cellHeader.setCellStyle(style);
cellHeader.setCellValue("无数据");
}else {
//添加标题
int cellCount=0;
for( Map<String,String> map: mapKey){
cellHeader=row.createCell(cellCount);
cellHeader.setCellStyle(style);
cellHeader.setCellValue(map.get("column_name"));
cellCount++;
}
if(maplist==null||maplist.size()<=0)
{
row = sheet.createRow(1);
sheet.addMergedRegion(new CellRangeAddress(1 ,1, 0, cellCount-1));//起始行,结束行,起始列,结束列
cellHeader= row.createCell(0);
cellHeader.setCellStyle(style2);
cellHeader.setCellValue("无数据");
}
else {
int index=0;
Cell cell;
for(int i=0;i<maplist.size();i++)
{
index++;
cellCount=0;
row = sheet.createRow(index);
Map<String,Object> mapValue=maplist.get(i);
//遍历key
for(Map<String,String> map: mapKey){
cell = row.createCell(cellCount);
cell.setCellStyle(style2);
String key=map.get("column_value");
if("tbnum".equals(key))//序号
{
cell.setCellValue(index);
}else {
if(mapValue.containsKey(key)){
Object value= mapValue.get(key);
if(value!=null){
cell.setCellValue(value.toString());
}else {
cell.setCellValue("");
}
}
else {
cell.setCellValue("");
}
}
cellCount++;
}
}
}
}
try {
this.setResponseHeader(response, sheetName);
OutputStream os = response.getOutputStream();
workbook.write(os);
os.close();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
//关闭资源
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public void exportExcel(Workbook workbook, String sheetName, List<Map<String,Object>> maplist, OutputStream out)
{
// 生成一个表格
Sheet sheet = workbook.createSheet(sheetName);
// 设置表格默认列宽度为15个字节
//设置宽度
//sheet.setDefaultColumnWidth(20);
sheet.setDefaultColumnWidth(18);
//sheet.trackAllColumnsForAutoSizing();
// sheet.autoSizeColumn(0);
// 生成一个样式
CellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);//内容居中
// 生成一个字体
Font font = workbook.createFont();
font.setFontName("宋体");
font.setBold(true);//加粗
font.setFontHeightInPoints((short) 11);
// 把字体应用到当前的样式
style.setFont(font);
// 生成并设置另一个样式
CellStyle style2 = workbook.createCellStyle();
style2.setAlignment(HorizontalAlignment.CENTER);
// 生成另一个字体
Font font2 = workbook.createFont();
// font2.setBold(true);//加粗
// 把字体应用到当前的样式
style2.setFont(font2);
// 产生表格标题行
Row row = sheet.createRow(0);
Cell cellHeader;
if(maplist.size()<=0)
{
cellHeader= row.createCell(0);
cellHeader.setCellStyle(style);
cellHeader.setCellValue("无数据");
}else {
//添加标题
int cellCount=0;
for( String key: maplist.get(0).keySet()){
cellHeader=row.createCell(cellCount);
cellHeader.setCellStyle(style);
cellHeader.setCellValue(key);
cellCount++;
}
int index=0;
Cell cell;
for(int i=0;i<maplist.size();i++)
{
index++;
cellCount=0;
row = sheet.createRow(index);
for(Object value: maplist.get(i).values()){
cell = row.createCell(cellCount);
cell.setCellStyle(style2);
if(value!=null){
cell.setCellValue(value.toString());
}else {
cell.setCellValue("");
}
cellCount++;
}
}
}
try {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
//关闭资源
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
//发送响应流方法
public void setResponseHeader(HttpServletResponse response, String fileName) {
try {
String originalFileName = URLEncoder.encode(fileName, "utf-8");
response.reset();
response.setHeader("content-disposition", "attachment;filename*=utf-8''" + originalFileName+ ".xlsx" );
response.setContentType("application/octet-stream; charset=utf-8");
} catch (Exception ex) {
ex.printStackTrace();
}
}
// public static <T> void exportExcelByHutools(List<T> dataList, Map<String,String> headerAlias, HttpServletResponse response){
// Workbook sxssfBook = WorkbookUtil.createSXSSFBook();
// ExcelWriter writer = new ExcelWriter(sxssfBook,"sheet1");
// headerAlias.entrySet().forEach(entry );
// }
/**
* 读取Excel文件(Map)
* @param is 输入流
* @param flag 表头字段(中文)
* @return
* @throws Exception
*/
public List<Map<String,Object>> readExcelAsMap(InputStream is, String[] flag) throws Exception{
Workbook XssfWorkbook = WorkbookFactory.create(is);
List<Map<String,Object>> result = new ArrayList<>();
//对excel表的各个sheet进行遍历
for(int numSheet = 0;numSheet<XssfWorkbook.getNumberOfSheets();numSheet++){
Sheet XssfSheet = XssfWorkbook.getSheetAt(numSheet);
if(XssfSheet==null){
continue;
}
Row row = XssfSheet.getRow(0);
if(row == null){
continue;
}
int min = row.getFirstCellNum();
int max = row.getLastCellNum();
int[] no = new int [flag.length];
for(int n = 0; n < flag.length; n++){
no[n]=-1;
}
for(int i = min; i < max; i++){
Cell cell = row.getCell(i);
if(cell.getStringCellValue().equals("") || cell.getStringCellValue() == null){
max--;
}
for(int a = 0; a < flag.length; a++){
if(cell.getStringCellValue().equals(flag[a]))
no[a]=i;
}
}
for(int rowNum = 1; rowNum <= XssfSheet.getLastRowNum(); rowNum++){
row = XssfSheet.getRow(rowNum);
Map<String,Object> rowMap = new HashMap<>();
for(int a = 0; a < flag.length; a++){
if(no[a] != -1){
Cell cell = row.getCell(no[a]);
if(cell == null){
rowMap.put(flag[a],null);
continue;
}
String cellValue = "";
if (null != cell) {
// 以下是判断数据的类型
switch (cell.getCellTypeEnum()) {
case NUMERIC: // 数字
if (HSSFDateUtil.isCellDateFormatted(cell)){
Date d = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
cellValue = formater.format(d);
}else {
DecimalFormat df = new DecimalFormat("0");
cellValue = df.format(cell.getNumericCellValue());
}
break;
case STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case BOOLEAN: // Boolean
cellValue = cell.getBooleanCellValue() + "";
break;
case FORMULA: // 公式
// cellValue = cell.getCellFormula() + "";
cellValue = cell.getStringCellValue();
break;
case BLANK: // 空值
cellValue = "";
break;
case ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
}
rowMap.put(flag[a], cellValue);
}
}
result.add(rowMap);
}
}
return result;
}
}
使用
@GetMapping(value = "/importExcel") public void exportToExcel(TrainPlan trainPlan, HttpServletResponse response) throws IOException { UserVO userVO = userVOUtils.getUserVO(request); if(userVO ==null ||userVO.getTrainBaseList().size()==0){ return ; } trainPlan.setTrainBaseList(userVO.getTrainBaseList()); List<TrainPlanExcelInfo> trainPlanExcelInfos = studentPlanService.exportTrainPlanInquiryInfo(trainPlan); ExportExcelUtil exportExcel=new ExportExcelUtil(); LinkedHashMap<String, String> list = excelTableTitle2(); //导入excel List<Map<String, Object>> mapsList = excelTableDataMap(trainPlanExcelInfos); List<Map<String, String>> mapskey = excelTableTitleMap(list); //对应参数,工作表名称,数据,表头,返回的response exportExcel.exportExcel("轮转结果",mapsList,mapskey,response); }//定义表头的中文名称和英文名称(英文名称就是你实体类的名称) private LinkedHashMap<String, String> excelTableTitle2() { LinkedHashMap<String, String> fieldMap = new LinkedHashMap<>(); fieldMap.put("studentName","姓名"); fieldMap.put("studentCode","学员编号"); fieldMap.put("recruitYear","年度"); fieldMap.put("unitName","培训基地"); fieldMap.put("trainStatus","培训情况"); fieldMap.put("unitPlanText","轮转计划查看"); fieldMap.put("planStatus","制定情况"); fieldMap.put("unit1","第一单元"); fieldMap.put("unit2","第二单元"); fieldMap.put("unit3","第三单元"); fieldMap.put("unit4","第四单元"); fieldMap.put("unit5","第五单元"); fieldMap.put("unit6","第六单元"); fieldMap.put("unit7","第七单元"); fieldMap.put("unit8","第八单元"); fieldMap.put("unit9","第九单元"); fieldMap.put("unit10","第十单元"); fieldMap.put("unit11","第十一单元"); fieldMap.put("unit12","第十二单元"); fieldMap.put("unit13","第十三单元"); return fieldMap; } //将数据转为map,英文名称为key和结果为value private List<Map<String, Object>> excelTableDataMap(List<TrainPlanExcelInfo> data) { List<Map<String, Object>> mapList = new ArrayList<>(); for (TrainPlanExcelInfo datum : data) { Map<String, Object> fieldMap = new HashMap<>(); fieldMap.put("studentName",datum.getStudentName()); fieldMap.put("studentCode",datum.getStudentCode()); fieldMap.put("recruitYear",datum.getRecruitYear()); fieldMap.put("unitName",datum.getUnitName()); fieldMap.put("trainStatus",datum.getTrainStatus()); fieldMap.put("unitPlanText",datum.getUnitPlanText()); fieldMap.put("planStatus",datum.getPlanStatus()); fieldMap.put("unit1",datum.getUnit1()); fieldMap.put("unit2",datum.getUnit2()); fieldMap.put("unit3",datum.getUnit3()); fieldMap.put("unit4",datum.getUnit4()); fieldMap.put("unit5",datum.getUnit5()); fieldMap.put("unit6",datum.getUnit6()); fieldMap.put("unit7",datum.getUnit7()); fieldMap.put("unit8",datum.getUnit8()); fieldMap.put("unit9",datum.getUnit9()); fieldMap.put("unit10",datum.getUnit10()); fieldMap.put("unit11",datum.getUnit11()); fieldMap.put("unit12",datum.getUnit12()); fieldMap.put("unit13",datum.getUnit13()); mapList.add(fieldMap); } return mapList; } //将表头对应的英文和中文进行格式,为excel导出做准备,参数就是上面你定义的表头数据 private List<Map<String, String>> excelTableTitleMap(LinkedHashMap<String, String> map ) { List<Map<String, String>> mapList = new ArrayList<>(); Iterator<String> iterator = map.keySet().iterator(); while (iterator.hasNext()){ String key = iterator.next(); Map<String, String> m = new HashMap<>(); m.put("column_value",key); m.put("column_name",map.get(key)); mapList.add(m); } return mapList; }