导入jar
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
工具类ExportExcel
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
//这里所需要的参数是 表格文件名 表格的sheet的名称 与表格的头部内容 和数据集合，以及response
/**
* Created by Bob on 2021/4/7.
*/
public class ExportExcel
{
public static boolean exportExcel(String excelName, String title, String[] headers, List<Map> dataset, String pattern, HttpServletResponse response) throws IOException {
// Long milliSecond = LocalDateTime.now().toInstant(ZoneOffset.of("+8")).toEpochMilli();
long milliSecond = System.currentTimeMillis();
String fileName = excelName + "-" + milliSecond + ".xls";
ServletOutputStream outputStream = response.getOutputStream();
response.setContentType("application/x-xls;charset=UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
boolean flag = false;
Workbook workbook = null;
if (fileName.endsWith("xlsx"))
{
workbook = new XSSFWorkbook();
} else if (fileName.endsWith("xls"))
{
workbook = new HSSFWorkbook();
} else
{
try
{
throw new Exception("invalid file name, should be xls or xlsx");
} catch (Exception e)
{
e.printStackTrace();
}
}
Sheet sheet = workbook.createSheet(title);
CellStyle style = workbook.createCellStyle();
// 列名
Row row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++)
{
Cell cell = row.createCell(i);
sheet.setColumnWidth(i, 5000);
// style.setAlignment(CellStyle.ALIGN_CENTER);
cell.setCellValue(headers[i]);
}
Iterator<Map> it = dataset.iterator();
int index = 0;
while (it.hasNext())
{
index++;
row = sheet.createRow(index);
Map map = it.next();//当前这个list集合中的map
// logger.info(map.toString());
Set<String> mapKey = (Set<String>)map.keySet();//获取当前这个map的键的set集合
// logger.info(mapKey.toString());
Iterator<String> iterator = mapKey.iterator();
// logger.info(iterator.toString());
int num = 0;
while(iterator.hasNext()){
Cell cell = row.createCell(num);
num++;
String key = iterator.next();//这个iterator.next就是这个map中的key
//为保证map有序用linkedhashmap
// LinkedHashMap<String, Integer> map = new LinkedHashMap<>();
// logger.info(key);
Object obj = map.get(key);
if (obj instanceof Date)
{
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
cell.setCellValue(sdf.format(obj));
} else if (obj instanceof Integer)
{
cell.setCellValue((Integer) obj);
} else if (obj instanceof Double)
{
cell.setCellValue((Double) obj);
} else
{
cell.setCellValue((String) obj);
}
}
}
FileOutputStream fos;
try
{
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
flag = true;
} catch (FileNotFoundException e)
{
// logger.info("文件不存在");
flag = false;
e.printStackTrace();
} catch (IOException e)
{
// logger.info("文件写入错误");
flag = false;
e.printStackTrace();
}
return flag;
}
/**
* 改造后的方法
*
*/
public static boolean exportMap(String excelName, String title, String[] headers, List<LinkedHashMap<String,Object>> dataset, String pattern, HttpServletResponse response) throws IOException {
// Long milliSecond = LocalDateTime.now().toInstant(ZoneOffset.of("+8")).toEpochMilli();
long milliSecond = System.currentTimeMillis();
String fileName = excelName + "-" + milliSecond + ".xls";
ServletOutputStream outputStream = response.getOutputStream();
response.setContentType("application/x-xls;charset=UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
boolean flag = false;
Workbook workbook = null;
if (fileName.endsWith("xlsx"))
{
workbook = new XSSFWorkbook();
} else if (fileName.endsWith("xls"))
{
workbook = new HSSFWorkbook();
} else
{
try
{
throw new Exception("invalid file name, should be xls or xlsx");
} catch (Exception e)
{
e.printStackTrace();
}
}
Sheet sheet = workbook.createSheet(title);
CellStyle style = workbook.createCellStyle();
// 列名
Row row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++)
{
Cell cell = row.createCell(i);
sheet.setColumnWidth(i, 5000);
// style.setAlignment(CellStyle.ALIGN_CENTER);
cell.setCellValue(headers[i]);
}
// Iterator<Map> it = dataset.iterator();quchu
Iterator<LinkedHashMap<String, Object>> it = dataset.iterator();
int index = 0;
int judgecount = 0;
while (it.hasNext())
{
index++;
judgecount++;
row = sheet.createRow(index);
LinkedHashMap<String, Object> linkedHashMap = it.next();
Set<String> linkkey = linkedHashMap.keySet();
Iterator<String> keyiterator = linkkey.iterator();//每一行key的集合
int a = 0;
Cell cell1 = row .createCell(a);
a++;
if (judgecount == 1){
cell1.setCellValue(index);//新增添加序号
}
while (keyiterator.hasNext()){
Cell cell = row.createCell(a);
a++;
if (judgecount == 1){
judgecount--;
}
String link_key = keyiterator.next();
Object obj = linkedHashMap.get(link_key);
if (obj instanceof Date)
{
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
cell.setCellValue(sdf.format(obj));
} else if (obj instanceof Integer)
{
cell.setCellValue((Integer) obj);
} else if (obj instanceof Double)
{
cell.setCellValue((Double) obj);
} else if (obj instanceof BigDecimal){
int i = ((BigDecimal) obj).intValue();
cell.setCellValue(i);
} else
{
cell.setCellValue((String) obj);
}
}
}
FileOutputStream fos;
try
{
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
flag = true;
} catch (FileNotFoundException e)
{
// logger.info("文件不存在");
flag = false;
e.printStackTrace();
} catch (IOException e)
{
// logger.info("文件写入错误");
flag = false;
e.printStackTrace();
}
return flag;
}
}