添加 maven 依赖
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>编写工具类
- 可以实现
- 1.导出Excel 到本地 / 浏览器
- 2.导出包含多个 Sheet 的 Excel 到本地 / 浏览器
package com.geotmt.billingcenter.common.utils;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* @Descriptinon: Excel 导出类
* @Author: yanghanwei
* @CreateDate: 2018/12/3 17:15
* @Version: v1
*/
public class ExportExcelUtils {
private static final String ATTR_ARR_STR = "attrArr";
private static final String DATA_LIST_STR = "dataList";
private static final String TITLE_ARR_STR = "titleArr";
private static final String SHEET_NAME = "Sheet";
private static final String SHEET_NAME_STR = "sheetName";
/**
* 默认单元格宽度
*/
private static final Integer DEFAULT_CELL_WIDTH = 3000;
private static FileOutputStream fout = null;
private static OutputStream out = null;
private static OutputStream setResponseHeaderFileName(HttpServletResponse response, String filename) throws IOException {
response.setContentType("application/octet-stream;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(filename+".xls","UTF-8"));
OutputStream out = new BufferedOutputStream(response.getOutputStream());
return out;
}
private static final Logger logger = LoggerFactory.getLogger(ExportExcelUtils.class);
/**
* 导出 exccel到本地 工具类（多个sheet导出）
* @param mapList
* @param filePath 本地路径
* sheetName： sheet名字
* titleArr： 表头中文名数组
* attrArr： 数据对应的实体类属性数组（与表头一一对应）
* dataList： 到处的数据list
*/
public static void buildExcelTemplate( List<Map<String, Object>> mapList,String filePath, Integer cellWidth) {
HSSFWorkbook wb = new HSSFWorkbook();
FileOutputStream fout = null;
try{
// 将文件存到指定位置
fout = new FileOutputStream(filePath);
if (!mapList.isEmpty()) {
for (int a = 0; a < mapList.size(); a++) {
String sheetName = "Sheet1";
if(!StringUtils.isEmpty(mapList.get(a).get(SHEET_NAME_STR))){
sheetName = String.valueOf(mapList.get(a).get(SHEET_NAME_STR));
}
String[] titleArr = (String[]) mapList.get(a).get("titleArr");
String[] attrArr = (String[]) mapList.get(a).get("attrArr");
Object dataList = mapList.get(a).get("dataList");
// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheetMonitor = wb.createSheet(sheetName);
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow rowMonitor = sheetMonitor.createRow(0);
// 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = ExportExcelUtils.getColumnTopStyle(wb);
HSSFCell cellMonitor = rowMonitor.createCell(0);
for (int i = 0; i < titleArr.length; i++) {
cellMonitor.setCellValue(titleArr[i]);
cellMonitor.setCellStyle(style);
cellMonitor = rowMonitor.createCell((i + 1));
}
String jsonString = JSONObject.toJSONString(dataList);
JSONArray jsonArray = JSONObject.parseArray(jsonString);
if (jsonArray != null && !jsonArray.isEmpty()) {
for (int i = 0; i < jsonArray.size(); i++) {
sheetMonitor.setColumnWidth(i, null == cellWidth ? DEFAULT_CELL_WIDTH : cellWidth);
Map<String, Object> map = JSONObject.parseObject(JSONObject.toJSONString(jsonArray.get(i)), Map.class);
if(null != map){
for (int j = 0; j < titleArr.length; j++) {
rowMonitor = sheetMonitor.createRow(i + 1);
// 第五步，写入实体数据 实际应用中这些数据从数据库得到，
for (int k = 0; k < attrArr.length; k++) {
if (map.containsKey(attrArr[k]) && !StringUtils.isEmpty(map.get(attrArr[k]))) {
// 第四步，创建单元格，并设置值
if(!StringUtils.isEmpty(map.get(attrArr[k]))){
String value = String.valueOf(map.get(attrArr[k]));
rowMonitor.createCell(k).setCellValue(value);
}else{
rowMonitor.createCell(k).setCellValue("");
}
}
}
}
}
}
}
}
}
wb.write(fout);
}catch (Exception e){
logger.error("导出Excel失败,Exception：" + e);
logger.info("导出Excel失败，Exception:{0}", e);
}finally {
try {
if(null != fout){
fout.close();
}
}catch (Exception e){
logger.error("关闭流异常：[{}]",e);
}
}
}
/**
* 导出 exccel 到浏览器工具类（1个sheet导出）
* @param dataListForMap
* sheetName： sheet名字
* titleArr： 表头中文名数组
* attrArr： 数据对应的实体类属性数组（与表头一一对应）
* dataListForMap： 导出的数据list
*/
public static void buildExcelTemplate(Map<String, Object> dataListForMap, HttpServletResponse response, String fileName, Integer cellWidth) {
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
Object sheetName = dataListForMap.get("sheetName");
HSSFSheet sheetMonitor = wb.createSheet(!StringUtils.isEmpty(sheetName) ? sheetName + "" : SHEET_NAME);
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow rowMonitor = sheetMonitor.createRow(0);
try{
out = setResponseHeaderFileName(response,fileName);
//创建excel标题,设置列宽
createTitle(wb, rowMonitor, dataListForMap, sheetMonitor, cellWidth);
//写入数据
writeData(sheetMonitor,1,dataListForMap);
wb.write(out);
}catch (Exception e){
logger.info("导出Excel失败,Exception:{}",e);
}finally {
try {
out.close();
}catch (Exception e){
logger.error("关闭流异常：[{}]",e);
}
}
}
/**
* 导出多个 sheet 到浏览器
* @param mapList
* * sheetName： sheet名字
* * titleArr： 表头中文名数组
* * attrArr： 数据对应的实体类属性数组（与表头一一对应）
* * dataList： 到处的数据list
* @param response
* @param fileName 文件名称
*/
public static void buildExcelTemplate(List<Map<String, Object>> mapList, HttpServletResponse response, String fileName, Integer cellWidth) {
HSSFWorkbook wb = new HSSFWorkbook();
if(null != mapList && !mapList.isEmpty()){
try{
for (int i=0; i<mapList.size(); i++) {
Map<String, Object> dataListForMap = mapList.get(i);
// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
Object sheetName = dataListForMap.get(SHEET_NAME_STR);
String sheetNameRt = !StringUtils.isEmpty(sheetName) ? sheetName + "" : SHEET_NAME + (i+1);
HSSFSheet sheetMonitor = wb.createSheet(sheetNameRt);
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow rowMonitor = sheetMonitor.createRow(0);
out = setResponseHeaderFileName(response,fileName);
//创建excel标题
createTitle(wb, rowMonitor, dataListForMap, sheetMonitor, cellWidth);
//写入数据
writeData(sheetMonitor,1,dataListForMap);
}
wb.write(out);
}catch (IOException e){
e.printStackTrace();
logger.info("导出Excel失败,Exception:{}",e);
}catch (Exception e) {
logger.info("导出Excel失败,Exception:{}",e);
}finally {
try {
out.close();
}catch (Exception e){
logger.error("关闭流异常：[{}]",e);
}
}
}
}
/**
* 创建excel标题
* @param dataListForMap
*/
private static void createTitle(HSSFWorkbook wb, HSSFRow rowMonitor, Map<String, Object> dataListForMap, HSSFSheet sheetMonitor, Integer cellWidth){
String[] titleArr = (String[]) dataListForMap.get(TITLE_ARR_STR);
//居中样式
HSSFCellStyle centerStyle = ExportExcelUtils.getColumnTopStyle(wb);
for (int i = 0; i < titleArr.length; i++) {
// 设置表格宽度（自适应）
sheetMonitor.setColumnWidth(i, null == cellWidth ? DEFAULT_CELL_WIDTH : cellWidth);
HSSFCell cellMonitor = rowMonitor.createCell(i);
cellMonitor.setCellStyle(centerStyle);
cellMonitor.setCellValue(titleArr[i]);
}
}
/**
* 写入数据
* @param sheetMonitor
* @param startRow
* @param dataListForMap
*/
public static void writeData(HSSFSheet sheetMonitor, Integer startRow, Map<String, Object> dataListForMap){
String[] titleArr = (String[]) dataListForMap.get(TITLE_ARR_STR);
String[] attrArr = (String[]) dataListForMap.get(ATTR_ARR_STR);
Object dataList = dataListForMap.get(DATA_LIST_STR);
String jsonString = JSONObject.toJSONString(dataList);
JSONArray jsonArray = JSONObject.parseArray(jsonString);
if (jsonArray != null && !jsonArray.isEmpty()) {
for (int i = 0; i < jsonArray.size(); i++) {
Map<String, Object> map = JSONObject.parseObject(JSONObject.toJSONString(jsonArray.get(i)), Map.class);
for (int j = 0; j < titleArr.length; j++) {
HSSFRow rowMonitor = sheetMonitor.createRow( i + startRow);
// 第五步，写入实体数据 实际应用中这些数据从数据库得到，
for (int k = 0; k < attrArr.length; k++) {
if (map != null && attrArr[k] != null && map.containsKey(attrArr[k]) && !StringUtils.isEmpty(map.get(attrArr[k]))) {
// 第四步，创建单元格，并设置值
if(!StringUtils.isEmpty(map.get(attrArr[k]))){
String value = String.valueOf(map.get(attrArr[k]));
rowMonitor.createCell(k).setCellValue(value);
}else{
rowMonitor.createCell(k).setCellValue("");
}
}
}
}
}
}
}
/**
* 列头单元格样式
* @param workbook
* @return
*/
private static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short)12);
//字体加粗
font.setBold(true);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(BorderStyle.THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
//设置左边框;
style.setBorderLeft(BorderStyle.THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
//设置右边框;
style.setBorderRight(BorderStyle.THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
//设置顶边框;
style.setBorderTop(BorderStyle.THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.DARK_RED.getIndex());
style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.DARK_RED.getIndex());
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
/**
* 列数据信息单元格样式
* @param workbook
* @return
*/
private HSSFCellStyle getStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short)12);
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(BorderStyle.THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
//设置左边框;
style.setBorderLeft(BorderStyle.THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
//设置右边框;
style.setBorderRight(BorderStyle.THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
//设置顶边框;
style.setBorderTop(BorderStyle.THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
//在样式用应用设置的字体;
style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.BRIGHT_GREEN.getIndex());
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
/**
* 判断是否是整数
* @param str
* @return
*/
private static Pattern pattern = Pattern.compile("^\\d+$");
public static boolean isIntNum(String str) {
Matcher isIntNum = pattern.matcher(str);
if (!isIntNum.matches()) {
return false;
}
return true;
}
/**
* 判断是否是浮点数
* @param str
* @return
*/
private static Pattern pattern2 = Pattern.compile("\\d+\\.\\d+$|-\\d+\\.\\d+$");
public static boolean isDoubleNum(String str) {
Matcher isDoubleNum = pattern2.matcher(str);
if (!isDoubleNum.matches()) {
return false;
}
return true;
}
}简单使用
@ApiOperation(value = "导出成本统计")
@RequestMapping(value = "/exportList",method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
public void ExportConsumeStatis(@RequestBody ParamsCost obj,
HttpServletResponse response){
logger.info("导出成本统计,ParamsConsume:{}",obj);
try{
if(null == obj.getEffectStartTime() || null == obj.getEffectEndTime()){
throw new MyException("开始时间和结束时间不能为空！", HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
}
String startStr = GeoDateUtils.getDateStr(obj.getEffectStartTime(), GeoDateUtils.DATE_FORMAT2);
String endStr = GeoDateUtils.getDateStr(obj.getEffectEndTime(), GeoDateUtils.DATE_FORMAT2);
String fileName = startStr + "-" + endStr + "(成本统计)";
List<CostStatisVo> consumeStatisVos = costStatisService.costStatisList(obj);
if (null != consumeStatisVos) {
consumeStatisVos.add(costStatisService.costStatisTotal(obj));
}
// 拼接 Excel 数据
Map<String,Object> map = new HashMap<>(3);
String[] titleArr = {"日期","产品","客户","用户","用户账号","用户ID","运营商","内部接口","外部接口","计费数","金额"};
String[] attrArr = {"dayFlag","productName","companyName","cuserName","account","cuserId","isp","interfaceName","outInterfaceName","countCharging","cost"};
map.put("titleArr",titleArr);
map.put("attrArr",attrArr);
map.put("dataList", consumeStatisVos);
ExportExcelUtils.buildExcelTemplate(map, response, fileName,4000);
}catch (Exception e) {
e.printStackTrace();
logger.error("导出消耗统计,Exception:",e);
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
