poi mysql 导出 excel乱码_[poi使用]导出excel由浅到深

高盛仁

于 2021-02-28 23:09:32 发布

阅读量257

点赞数

文章标签： poi mysql 导出 excel乱码

本文链接：https://blog.csdn.net/weixin_33488239/article/details/114909199

版权

Apache POI是基于Office Open XML标准(OOXML)和Microsoft的OLE 2复合文档格式(OLE2)处理各种文件格式的开源项目。简而言之，您可以使用Java读写MS Excel文件，可以使用Java读写MS Word和MS PowerPoint文件

导出excel涉及模块

①XSSF －提供读写Microsoft Excel OOXML XLSX格式(Microsoft Excel XML (2007+))档案的功能。

②HSSF －提供读写Microsoft Excel XLS格式(Microsoft Excel 97 (-2003))档案的功能。

涉及到的jar包，下面是完整的，每个依赖的作用都有注释，用不到的可以删除。

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">

4.0.0

org.springframework.boot

spring-boot-starter-parent

2.1.3.RELEASE

com.ningl

nlredis

jar

nlredis

nlredis project for Spring Boot

anxpp

UTF-8

1.8

8.0.11

1.1.10

28.1-jre

1.3.8.RELEASE

2.1.9

1.18.4

1.2.38

2.9.9

3.4

1.3.3

org.springframework.boot

spring-boot-starter-data-redis

redis.clients

jedis

2.9.1

org.springframework.boot

spring-boot-starter-web

org.springframework.boot

spring-boot-starter-thymeleaf

org.webjars

jquery

3.5.1

com.fasterxml.jackson.core

jackson-databind

2.9.3

com.fasterxml.jackson.core

jackson-core

2.9.3

com.fasterxml.jackson.core

jackson-annotations

2.9.3

net.sf.json-lib

json-lib

2.4

jdk15

commons-lang

2.6

commons-logging

1.2

commons-beanutils

1.9.3

commons-collections

3.2.1

net.sf.ezmorph

ezmorph

1.0.6

xom

1.2.5

junit

4.13

com.baomidou

mybatis-plus

${mybatisPlus.version}

com.baomidou

mybatisplus-spring-boot-starter

1.0.5

mysql

mysql-connector-java

${mysql.version}

com.alibaba

druid

${druid.version}

com.alibaba

druid-spring-boot-starter

1.1.10

com.google.guava

guava

${gugua.version}

org.projectlombok

lombok

${lombok.version}

provided

com.alibaba

fastjson

${fastJson.version}

joda-time

${jodate.version}

org.apache.commons

commons-lang3

${commonsLang.version}

commons-fileupload

${fileupload.version}

org.apache.poi

poi

4.1.2

org.apache.poi

poi-ooxml-schemas

4.1.2

org.apache.poi

poi-ooxml

4.1.2

nlredis

org.springframework.boot

spring-boot-maven-plugin

repackage

com.spotify

docker-maven-plugin

1.0.0

src/main/docker

${project.build.directory}

${project.build.finalName}.jar

maven-antrun-plugin

package

file = "target/${project.artifactId}.${project.packaging}">

run

第一个需求，get请求生成一个简单的excel文件，并且按照顺序为单元格赋值

public static void createExcelLevel0(HttpServletResponse response, JSONObject json) throws IOException {

setExcelResponse(response);

try(OutputStream os = response.getOutputStream();

ByteArrayOutputStream outputStream = new ByteArrayOutputStream();) {

//创建新的excel,07 之后XSSF,之前HSSF

Workbook xssfWork = new XSSFWorkbook();

//创建一个工作簿

Sheet sheet = xssfWork.createSheet("sheet1");

//创建3行

for (int i = 0; i < 3; i++) {

Row row = sheet.createRow(i);

//创建4列

for (int j = 0; j < 4; j++) {

Cell cell = row.createCell(j);

//单元格赋值

cell.setCellValue("第"+(i+1)+"行"+(j+1)+"列");

}

//ByteArrayOutputStream的输出目标是一个byte数组，这个数组的长度是根据数据内容动态扩展的

xssfWork.write(outputStream);

//转给response.getOutputStream()

outputStream.writeTo(os);

//输出流文件

os.flush();

} catch (Exception e) {

e.printStackTrace();

}

/**

* @discription excel header信息添加

private static void setExcelResponse(HttpServletResponse response) {

response.setHeader("content-disposition", "attachment; filename=mytest.xlsx");

response.setCharacterEncoding("utf-8");

response.setContentType("application/msexcel");

}

第二步增加点难度，给指定单元格写内容、合并单元格、给单元格设置样式、格式、给文档设置作者、描述等信息

/**

* @discription 创建excel,并设置格式

public static void createExcelLevel1(HttpServletResponse response, JSONObject jsonObject) {

setExcelResponse(response);

try(OutputStream os = response.getOutputStream();

ByteArrayOutputStream baos = new ByteArrayOutputStream()) {

XSSFWorkbook workbook = new XSSFWorkbook();

addExcelInfo(workbook);

Sheet sheet = workbook.createSheet("基金仓位控制表");

Row row = sheet.createRow(8);

//创建标题title

//合并单元格firstRow 区域中第一个单元格的行号,lastRow 区域中最后一个单元格的行号

// firstCol 区域中第一个单元格的列号,lastCol 区域中最后一个单元格的列号

Cell cell = row.createCell(7);

CellRangeAddress cellRangeAddress = new CellRangeAddress(8, 9, 7, 11);

sheet.addMergedRegion(cellRangeAddress);

cell.setCellValue("基金仓位控制表");

cell.setCellStyle(cellStyle(workbook));

//创建表头信息

List headerList = initHeader();

Row headRow = sheet.createRow(11);

int rowtHeader = 11;

int colHeader = 5;

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

Cell headCell = headRow.createCell(colHeader);

CellRangeAddress headRegion = new CellRangeAddress(rowtHeader, rowtHeader+1, colHeader,colHeader+1);

colHeader = colHeader+2;

sheet.addMergedRegion(headRegion);

headCell.setCellValue(headerList.get(i));

}

//数据赋值

int rowData = 13;

JSONArray dataArr = initJsonArry();

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

//创建行

Row dataRow = sheet.createRow(rowData);

JSONObject jos = dataArr.getJSONObject(i);

Set keySet = jos.keySet();

int colData = 5;

for (String key: keySet) {

CellRangeAddress dataRegion = new CellRangeAddress(rowData, rowData+1,

colData,colData+1);

//添加合并区域

sheet.addMergedRegion(dataRegion);

//创建列

Cell dataCell = dataRow.createCell(colData);

//合并单元格列自增

colData = colData+2;

//临时判断日期类型

if(key.indexOf("-") > -1){

dataCell.setCellValue(Date8Util.strToDate(jos.getString(key)));

}else{

dataCell.setCellValue(jos.getString(key));

}

//行自增

rowData = rowData+2;

}

//保存工作表

workbook.write(baos);

baos.writeTo(os);

os.flush();

}catch (Exception e){

}

private static void addExcelInfo(XSSFWorkbook workbook){

//创建文档属性信息

POIXMLProperties poiDocument = workbook.getProperties();

POIXMLProperties.CoreProperties properties = poiDocument.getCoreProperties();

properties.setCreated("2020-09-01");

//备注

properties.setDescription("about my funds control");

//标题

properties.setTitle("about my funds control");

//创建者

properties.setCreator("ngLee");

properties.setRevision("revion");

//文档属性自定义信息

POIXMLProperties.CustomProperties customProperties = poiDocument.getCustomProperties();

customProperties.addProperty("description", "描述信息");

}

/**

* @discription 边框

private static CellStyle cellStyle(XSSFWorkbook workbook) {

XSSFCellStyle style=workbook.createCellStyle();

style.setBorderBottom(BorderStyle.THICK);//下边框

style.setBottomBorderColor(new XSSFColor(Color.BLUE));//下边框颜色

//水平居中

style.setAlignment(HorizontalAlignment.CENTER_SELECTION);

//设置字体

XSSFFont xssfFont = workbook.createFont();

//加粗

xssfFont.setBold(true);

//蓝色

xssfFont.setColor(XSSFColor.toXSSFColor((org.apache.poi.ss.usermodel.Color) Color.BLUE));

//字体大小

xssfFont.setFontHeight(22.0d);

style.setFont(xssfFont);

return style;

}

/**

* @discription header

private static List initHeader() {

List list = new ArrayList();

list.add("所属板块");

list.add("基金名称");

list.add("基金代码");

list.add("总投入");

list.add("操作日期");

return list;

}

/**

* @discription 创建数据

private static JSONArray initJsonArry() {

JSONArray jsonArray = new JSONArray();

for (int i = 0; i < 4; i++) {

JSONObject jsonObject = initJson(i);

jsonArray.add(jsonObject);

}

return jsonArray;

}

/**

* @discription 创建数据

private static JSONObject initJson(int i) {

JSONObject json = new JSONObject();

String bkmc = "";

String fundName = "";

String code = "000000";

String date = Date8Util.nowDate();

String ztrje = "20000.00";

switch (i){

case 0:bkmc = "芯片半导体";

fundName = "诺安成长混合";

code = "320007";break;

case 1:bkmc = "消费";

fundName = "富国消费主题混合";

code = "519915";break;

case 2:bkmc = "科技";

fundName = "华宝科技ETF";

code = "007874";break;

case 3:bkmc="医药医疗";

fundName = "中欧医疗创新股票";

code="006229";break;

default: break;

}

if(!Strings.isNullOrEmpty(bkmc)){

json.put("bkmc",bkmc);

json.put("fundName",fundName);

json.put("code",code);

json.put("ztrje",ztrje);

json.put("now", date);

}

return json;

}

继续增加难度，根据一个excel模版，将数据内容按照定义名称写到excel，例如我的模板如下，然后我选中motto、author、time 单元格定义名称为usser(对象)，选中order、sshy、fundsName、ytrbj、dtrbj、ljsy定义名称为funds_kz(备注_kz标识为是扩展行，可能有多条数据)。查看所有定义名称快捷键是ctrl+f3

/**

* @discription 读取模版内容信息

* @author: ngLee

* @date: 2020/9/6

* @param: [response, jsonObject]

* @return: void

**/

public static void createExcelLevel2(HttpServletResponse response, JSONObject jsonObject) {

setExcelResponse(response);

String path = "E:\\excelMB\\demo.xlsx";

File file = new File(path);

JSONObject object = initJsonLevel2();

try(OutputStream os = response.getOutputStream();

ByteArrayOutputStream baos = new ByteArrayOutputStream()) {

XSSFWorkbook workbook = new XSSFWorkbook(file);

addExcelInfo(workbook);

//获取所有名称

List nameList = workbook.getAllNames();

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

Name name = nameList.get(i);

//自定义名称

String custName = name.getNameName();

//获取关联单元格

String aboutCell = name.getRefersToFormula();

System.out.println(aboutCell);

//userInfo--基金仓位控制!$M$9:$N$9,基金仓位控制!$M$10:$N$10

//funds_kz--基金仓位控制!$E$14:$O$15

if(custName.indexOf("_kz") < 0){

//非连续的命名范围

AreaReference[] arefs = AreaReference.generateContiguous(SpreadsheetVersion.EXCEL2007,name.getRefersToFormula());

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

CellReference[] crefs = arefs[j].getAllReferencedCells();

for (int k = 0; k < crefs.length; k++) {

//获取所在sheet

Sheet sheet = workbook.getSheet(crefs[k].getSheetName());

//获取行

Row row = sheet.getRow(crefs[k].getRow());

//获取单元格

Cell cell = row.getCell(crefs[k].getCol());

//模版里的值作为key

String value = cell.getStringCellValue();

if(!Strings.isNullOrEmpty(value)){

JSONObject obj = object.getJSONObject(custName);

cell.setCellValue(obj.getString(value));

}

}else {//多条数据扩展行

AreaReference aref = new AreaReference(name.getRefersToFormula(),SpreadsheetVersion.EXCEL2007);

JSONArray kzList = object.getJSONArray(custName);

CellReference[] crefs = aref.getAllReferencedCells();

int rowNum = crefs[0].getRow();

int kzListLen = kzList.size();

for (int j = 0; j < kzListLen; j++) {

//拷贝当前行到下一行

JSONObject obj = kzList.getJSONObject(j);

Sheet sheet = workbook.getSheet(crefs[0].getSheetName());

Row sourceRow = sheet.getRow(rowNum);

rowNum = rowNum+2;

Row distRow = sheet.createRow(rowNum);

copyCustRow(workbook,sheet,sourceRow,distRow,j+1 == kzListLen ,obj);

}

workbook.write(baos);

baos.writeTo(os);

os.flush();

}catch (Exception e){

}

/**

* @discription 复制行

* @author: ngLee

* @date: 2020/9/6

* @param: [workbook, sourceRow, distRow, b]

* @return: void

**/

private static void copyCustRow(XSSFWorkbook workbook,Sheet sheet, Row sourceRow, Row distRow, boolean b,JSONObject obj) {

distRow.setHeight(sourceRow.getHeight());

//复制单元格的内容

for(int i=0; i < sourceRow.getLastCellNum(); i++){

//单元格样式

CellStyle distStyle = workbook.createCellStyle();

Cell sourceCell = sourceRow.getCell(i);

Cell distCell = distRow.createCell(i);

Cell distCell2 = distRow.createCell(i+1);

if(sourceCell != null ){

CellStyle sourceStyle = sourceCell.getCellStyle();

sourceStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);

distStyle.cloneStyleFrom(sourceStyle);

if(!b){

distCell.setCellStyle(distStyle);

distCell2.setCellStyle(distStyle);

}

//复制内容

if(!Strings.isNullOrEmpty(sourceCell.getStringCellValue())){

//目标行复制key

if(b == false){

distCell.setCellValue(sourceCell.getStringCellValue());

}

//源赋值

sourceCell.setCellValue(obj.getString(sourceCell.getStringCellValue()));

}

//复制合并的单元格

for(int i=0; i < sheet.getNumMergedRegions(); i++){

CellRangeAddress sourceRange = sheet.getMergedRegion(i);

int rowCnt = sourceRange.getLastRow()-sourceRange.getFirstRow();

if(sourceRange.getFirstRow() == sourceRow.getRowNum()){

CellRangeAddress distRange = new CellRangeAddress(distRow.getRowNum(),

distRow.getRowNum()+rowCnt,

sourceRange.getFirstColumn(), sourceRange.getLastColumn());

if(!b){

RegionUtil.setBorderLeft(BorderStyle.THIN, distRange, sheet); // 左边框

RegionUtil.setBorderRight(BorderStyle.THIN, distRange, sheet); // 有边框

RegionUtil.setBorderTop(BorderStyle.THIN, distRange, sheet); // 上边框

RegionUtil.setBorderBottom(BorderStyle.THIN,distRange,sheet);

sheet.addMergedRegion(distRange);

}

private static JSONObject initJsonLevel2() {

JSONObject json = new JSONObject();

JSONObject usser = new JSONObject();

usser.put("motto", "让利润奔跑");

usser.put("author", "ngLee");

usser.put("time", "2020-09-06");

json.put("usser", usser);

JSONArray fundArr = new JSONArray();

for (int i = 0; i < 4 ; i++) {

JSONObject jsonObject = new JSONObject();

jsonObject.put("order", i+1);

jsonObject.put("sshy", i%2 == 0 ? "消费":"科技");

jsonObject.put("fundsName","华宝券商ETF");

jsonObject.put("ytrbj", "20000");

jsonObject.put("dtrbj", "10000");

jsonObject.put("ljsy", "1000");

fundArr.add(jsonObject);

}

json.put("funds_kz", fundArr);

return json;

}

导入的包展示

import com.google.common.base.Strings;

import net.sf.json.JSONArray;

import net.sf.json.JSONObject;

import org.apache.poi.ooxml.POIXMLProperties;

import org.apache.poi.ss.SpreadsheetVersion;

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

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

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

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

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

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

import javax.servlet.http.HttpServletResponse;

import java.awt.Color;

import java.io.*;

import java.util.ArrayList;

import java.util.List;

import java.util.Set;

public class ExportUtil｛

.......方法同上

｝

controller层很简单，需要注意下，设置返回值类型为void,否则会出现下面这个问题

java.lang.IllegalStateException: getOutputStream() has already been called for this response

@GetMapping("/exportExcel")

public void exportExcel(HttpServletResponse response, HttpServletRequest request) throws IOException {

JSONObject jsonObject = new JSONObject();

//下载excel 1.1 入门

//ExportUtil.createExcelLevel0(response,jsonObject);

//设置单元格格式 1.2 合并单元格

//ExportUtil.createExcelLevel1(response,jsonObject);

//读取模版的定义名称 1.3

ExportUtil.createExcelLevel2(response,jsonObject);

}

结语：以上就是关于POI导出excel可能出现的需求实例，只是简单实现了功能，各位小伙伴感兴趣的话，可以多加优化下~~~嘿嘿

高盛仁

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
poi mysql 导出 excel乱码_[poi使用]导出excel由浅到深

Apache POI是基于Office Open XML标准(OOXML)和Microsoft的OLE 2复合文档格式(OLE2)处理各种文件格式的开源项目。简而言之，您可以使用Java读写MS Excel文件，可以使用Java读写MS Word和MS PowerPoint文件导出excel涉及模块①XSSF －提供读写Microsoft Excel OOXML XLSX格式(Microsof...
复制链接

扫一扫