java map导出excel_java导出excel 入参：List＜Map＜String, Object＞＞

一、pom依赖

3.10-FINAL

org.apache.poi

poi

${org.apache.poi.version}

org.apache.poi

poi-ooxml

${org.apache.poi.version}

org.apache.poi

poi-ooxml-schemas

${org.apache.poi.version}

二、封装的服务

import cn.hutool.core.collection.CollectionUtil;

import cn.hutool.core.util.ObjectUtil;

import com.alibaba.fastjson.JSON;

import com.alibaba.fastjson.JSONObject;

import lombok.extern.slf4j.Slf4j;

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.Cell;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.springframework.stereotype.Service;

​

import javax.servlet.ServletOutputStream;

import javax.servlet.http.HttpServletResponse;

import java.lang.reflect.Field;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.Set;

import java.util.UUID;

​

@Slf4j

@Service

public class ExcelExportService {

​

/**

* @param rowDataList:需导出的数据 eg:[{"gmt_create":"2019-02-22 00:00:00","contract_id":7,"remark":"test","sign_man":"lu"}]

* @param response

* @throws Exception

*/

public void exportExcel2007(List> rowDataList, HttpServletResponse response) throws Exception {

XSSFWorkbook workbook = new XSSFWorkbook();

ServletOutputStream outputStream = null;

try {

String sheetName = "sheet1";

XSSFSheet sheet = workbook.createSheet(sheetName);

​

if(CollectionUtil.isEmpty(rowDataList)) {

return;

}

// 设置表头

XSSFRow headRow = sheet.createRow(0);

MapobjectMap = rowDataList.get(0);

int i = 0;

for (String title : objectMap.keySet()) {

headRow.createCell(i++).setCellValue(title);

}

​

for (MapcurrRowDataMap : rowDataList) {

XSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1); // 获取当前行

i = 0;

for (String title : currRowDataMap.keySet()) {

Object obj = currRowDataMap.get(title);

dataRow.createCell(i++).setCellValue(ObjectUtil.isNull(obj) ? null : obj.toString());

}

}

// 下载导出设置

// 设置头信息

String fileName = UUID.randomUUID().toString().replace("-", "") + ".xlsx";

response.setContentType("application/vnd.ms-excel");

response.setHeader("Content-Disposition", "attachment;filename=" + fileName);

​

outputStream = response.getOutputStream();

workbook.write(outputStream);

outputStream.flush();

} finally {

// 关闭

if(outputStream != null){

outputStream.close();

}

}

}

​

/**

* @param rowDataList:需导出的数据 eg:[{"gmt_create":"2019-02-22 00:00:00","contract_id":7,"remark":"test","sign_man":"lu"}]

* @param response

* @throws Exception

*/

public void exportExcel2003(List> rowDataList, HttpServletResponse response) throws Exception {

HSSFWorkbook workbook = new HSSFWorkbook();

ServletOutputStream outputStream = null;

try {

String sheetName = "sheet1";

HSSFSheet sheet = workbook.createSheet(sheetName);

​

if(CollectionUtil.isEmpty(rowDataList)) {

return;

}

// 设置表头

HSSFRow headRow = sheet.createRow(0);

MapobjectMap = rowDataList.get(0);

int i = 0;

for (String title : objectMap.keySet()) {

headRow.createCell(i++).setCellValue(title);

}

​

for (MapcurrRowDataMap : rowDataList) {

HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1); // 获取当前行

i = 0;

for (String title : currRowDataMap.keySet()) {

Object obj = currRowDataMap.get(title);

dataRow.createCell(i++).setCellValue(ObjectUtil.isNull(obj) ? null : obj.toString());

}

}

// 下载导出设置

// 设置头信息

String fileName = UUID.randomUUID().toString().replace("-", "") + ".xls";

response.setContentType("application/vnd.ms-excel");

response.setHeader("Content-Disposition", "attachment;filename=" + fileName);

​

outputStream = response.getOutputStream();

workbook.write(outputStream);

outputStream.flush();

} finally {

// 关闭

if(outputStream != null){

outputStream.close();

}

}

}

​

}

三、代码示例

public void exportExcel(HttpServletRequest request, HttpServletResponse response) {

List> dataList = getDataList();

// 导出excel2003

// excelExportService.exportExcel2003(dataList, response);

// 导出excel2007

excelExportService.exportExcel2007(dataList, response);

}

​

import cn.hutool.core.util.ObjectUtil;

import org.springframework.beans.factory.annotation.Value;

import java.sql.Connection;

import java.sql.Date;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

​

@Value("${jdbc.url}")

private String url;

@Value("${jdbc.driver}")

private String driver;

@Value("${jdbc.username}")

private String username;

@Value("${jdbc.password}")

private String password;

@Value("${jdbc.sql}")

private String sql;

​

public List>  getDataList() throws SQLException {

Connection connection = null;

Statement statement = null;

List> dataList = new ArrayList<>();

try {

// 1.获取数据库连接

Class.forName(driver); // 驱动

connection = DriverManager.getConnection(url, username, password);

// 2.

statement = connection.createStatement();

// String sql = "select * from contract where contract_id in (7, 27, 31, 33, 35, 37)";

ResultSet resultSet = statement.executeQuery(sql);

​

ResultSetMetaData resultSetMD = resultSet.getMetaData();

​

while(resultSet.next()){//集合遍历的形式

MapcolumnData = new HashMap<>();

for (int i = 1; i <= resultSetMD.getColumnCount(); i++) {

String columnName = resultSetMD.getColumnName(i);

String columnTypeName = resultSetMD.getColumnTypeName(i);

if(ObjectUtil.equal(columnTypeName, "DATE") ||

ObjectUtil.equal(columnTypeName, "TIME") ||

ObjectUtil.equal(columnTypeName, "YEAR") ||

ObjectUtil.equal(columnTypeName, "DATETIME") ||

ObjectUtil.equal(columnTypeName, "TIMESTAMP")) {

Date date = resultSet.getDate(i);

if(ObjectUtil.isNotNull(date)) {

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

columnData.put(columnName, sdf.format(date));

}else{

columnData.put(columnName, date);

}

}else{

columnData.put(columnName, resultSet.getObject(i));

}

}

dataList.add(columnData);

}

return dataList;

} catch (Exception e) {

return dataList;

} finally {

// 6、关闭资源

if(statement != null) {

statement.close();

}

if(connection != null) {

connection.close();

}

}

}