java导出excle表格数据,java导出Excel表格

图片仅供参考

霸气钢铁侠

Thinking：我发现上班时，当一个程序员飞速的敲着代码，大部分情况不是在认真工作，多半是在微信聊天，只有当他键盘敲几下，之后一直在操作鼠标，然后在敲几下键盘，这个时候应该是在认真工作，因为他应该是遇到了难题在百度。

开始步入正题

1.导入 jar 包

org.apache.poi

poi

3.17

org.apache.poi

poi-ooxml

3.17

2.工具类

package cn.superfw.genesis.fw.utils;

import com.google.common.base.Strings;

import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.hssf.util.HSSFColor;

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

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

import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import java.io.IOException;

import java.io.OutputStream;

import java.math.BigDecimal;

import java.util.List;

import java.util.Map;

public class ExcelExportUtil {

//表头

private String title;

//各个列的表头

private String[] heardList;

//各个列的元素key值

private String[] heardKey;

//需要填充的数据信息

private List> data;

//字体大小

private int fontSize = 14;

//行高

private int rowHeight = 30;

//列宽

private int columWidth = 200;

//工作表

private String sheetName = "sheet1";

public String getTitle() {

return title;

}

public void setTitle(String title) {

this.title = title;

}

public String[] getHeardList() {

return heardList;

}

public void setHeardList(String[] heardList) {

this.heardList = heardList;

}

public String[] getHeardKey() {

return heardKey;

}

public void setHeardKey(String[] heardKey) {

this.heardKey = heardKey;

}

public List> getData() {

return data;

}

public void setData(List> data) {

this.data = data;

}

public int getFontSize() {

return fontSize;

}

public void setFontSize(int fontSize) {

this.fontSize = fontSize;

}

public int getRowHeight() {

return rowHeight;

}

public void setRowHeight(int rowHeight) {

this.rowHeight = rowHeight;

}

public int getColumWidth() {

return columWidth;

}

public void setColumWidth(int columWidth) {

this.columWidth = columWidth;

}

public String getSheetName() {

return sheetName;

}

public void setSheetName(String sheetName) {

this.sheetName = sheetName;

}

/**

* 开始导出数据信息

*

*/

public byte[] exportExport(HttpServletRequest request, HttpServletResponse response) throws IOException {

//检查参数配置信息

checkConfig();

//创建工作簿

HSSFWorkbook wb = new HSSFWorkbook();

//创建工作表

HSSFSheet wbSheet = wb.createSheet(this.sheetName);

//设置默认行宽

wbSheet.setDefaultColumnWidth(20);

// 标题样式(加粗，垂直居中)

HSSFCellStyle cellStyle = wb.createCellStyle();

cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中

cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

HSSFFont fontStyle = wb.createFont();

//fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

fontStyle.setBold(true); //加粗

fontStyle.setFontHeightInPoints((short)16); //设置标题字体大小

cellStyle.setFont(fontStyle);

//在第0行创建rows (表标题)

HSSFRow title = wbSheet.createRow((int) 0);

title.setHeightInPoints(30);//行高

HSSFCell cellValue = title.createCell(0);

cellValue.setCellValue(this.title);

cellValue.setCellStyle(cellStyle);

wbSheet.addMergedRegion(new CellRangeAddress(0,0,0,(this.heardList.length-1)));

//设置表头样式，表头居中

HSSFCellStyle style = wb.createCellStyle();

//设置单元格样式

style.setAlignment(HorizontalAlignment.CENTER);

style.setVerticalAlignment(VerticalAlignment.CENTER);

//设置字体

HSSFFont font = wb.createFont();

font.setFontHeightInPoints((short) this.fontSize);

style.setFont(font);

//在第1行创建rows

HSSFRow row = wbSheet.createRow((int) 1);

//设置列头元素

HSSFCell cellHead = null;

for (int i = 0; i < heardList.length; i++) {

cellHead = row.createCell(i);

cellHead.setCellValue(heardList[i]);

cellHead.setCellStyle(style);

}

//设置每格数据的样式 (字体红色)

HSSFCellStyle cellParamStyle = wb.createCellStyle();

HSSFFont ParamFontStyle = wb.createFont();

cellParamStyle.setAlignment(HorizontalAlignment.CENTER);

cellParamStyle.setVerticalAlignment(VerticalAlignment.CENTER);

ParamFontStyle.setColor(HSSFColor.DARK_RED.index); //设置字体颜色 (红色)

ParamFontStyle.setFontHeightInPoints((short) this.fontSize);

cellParamStyle.setFont(ParamFontStyle);

//设置每格数据的样式2(字体蓝色)

HSSFCellStyle cellParamStyle2 = wb.createCellStyle();

cellParamStyle2.setAlignment(HorizontalAlignment.CENTER);

cellParamStyle2.setVerticalAlignment(VerticalAlignment.CENTER);

HSSFFont ParamFontStyle2 = wb.createFont();

ParamFontStyle2.setColor(HSSFColor.BLUE.index); //设置字体颜色 (蓝色)

ParamFontStyle2.setFontHeightInPoints((short) this.fontSize);

cellParamStyle2.setFont(ParamFontStyle2);

//开始写入实体数据信息

int a = 2;

for (int i = 0; i < data.size(); i++) {

HSSFRow roww = wbSheet.createRow((int) a);

Map map = data.get(i);

HSSFCell cell = null;

for (int j = 0; j < heardKey.length; j++) {

cell = roww.createCell(j);

cell.setCellStyle(style);

Object valueObject = map.get(heardKey[j]);

String value = null;

if (valueObject == null) {

valueObject = "";

}

if (valueObject instanceof String) {

//取出的数据是字符串直接赋值

value = (String) map.get(heardKey[j]);

} else if (valueObject instanceof Integer) {

//取出的数据是Integer

value = String.valueOf(((Integer) (valueObject)).floatValue());

} else if (valueObject instanceof BigDecimal) {

//取出的数据是BigDecimal

value = String.valueOf(((BigDecimal) (valueObject)).floatValue());

} else {

value = valueObject.toString();

}

//设置单个单元格的字体颜色

if(heardKey[j].equals("ddNum") || heardKey[j].equals("sjNum")){

if((Long)map.get("ddNum")!=null){

if((Long)map.get("sjNum")==null){

cell.setCellStyle(cellParamStyle);

} else if((Long) map.get("ddNum") != (Long) map.get("sjNum")){

if ((Long) map.get("ddNum") > (Long) map.get("sjNum")) {

cell.setCellStyle(cellParamStyle);

}

if ((Long) map.get("ddNum") < (Long) map.get("sjNum")) {

cell.setCellStyle(cellParamStyle2);

}

}else {

cell.setCellStyle(style);

}

}

}

cell.setCellValue(Strings.isNullOrEmpty(value) ? "" : value);

}

a++;

}

//导出数据

try {

//设置Http响应头告诉浏览器下载这个附件

response.setHeader("Content-Disposition", "attachment;Filename=" + System.currentTimeMillis() + ".xls");

OutputStream outputStream = response.getOutputStream();

wb.write(outputStream);

outputStream.close();

return wb.getBytes();

} catch (Exception ex) {

ex.printStackTrace();

throw new IOException("导出Excel出现严重异常，异常信息：" + ex.getMessage());

}

}

/**

* 检查数据配置问题

*

* @throws IOException 抛出数据异常类

*/

protected void checkConfig() throws IOException {

if (heardKey == null || heardList.length == 0) {

throw new IOException("列名数组不能为空或者为NULL");

}

if (fontSize < 0 || rowHeight < 0 || columWidth < 0) {

throw new IOException("字体、宽度或者高度不能为负值");

}

if (Strings.isNullOrEmpty(sheetName)) {

throw new IOException("工作表表名不能为NULL");

}

}

}

3.实现类

/**

* 参数中的 map 是前端传过来的查询条件，不重要

*

*/

public void exportExcel (@RequestBody Map map, HttpServletResponse response, HttpServletRequest request) throws IOException {

//省略无用代码若干行。。。。。。

//省略的是我项目中所需的逻辑需求，没有必要看了

//以下为重点

String sheetTitle = "维护工单";

//查询数据

//根据自己的需求查询出数据,因为我用的 jpa 的 @Query 做的查询，直接就是List>类型，用实体类接行不行我也太清楚，懒得操作了。

List> maps = jobManageService.getExcelData(name,applicants,applyType,safeAuditPerson,branchAuditPersion);

//导出excel

//以下就不用多解释了吧，有手就行了。

if(maps!=null && maps.size()>0){

String [] title = new String[]{"派工单编号","设备编号","设备名称","设备类型","所属构筑物","维护人员","派工日期","维护级别","计划完成时间"}; //设置表格表头字段

String [] properties = new String[]{"assignmentNo","equipNo","equipName","deviceType","structureName","serviceManName","assignmentTime","serviceLevelName"};

ExcelExportUtil excelExport2 = new ExcelExportUtil();

excelExport2.setData(maps);

excelExport2.setHeardKey(properties);

excelExport2.setFontSize(14);

excelExport2.setSheetName(sheetTitle);

excelExport2.setTitle(sheetTitle);

excelExport2.setHeardList(title);

excelExport2.exportExport(request, response);

}

}

4.来张效果图

虽然有点丑，但是确实是成功了。

美化，就靠各位自己发挥了，反正我是不会。

效果图

最后来张美照收尾

安妮海瑟薇