解决POI 导出功能，过时方法和新增样式放在最下面 或者参考下文
POI 样式调节
<poi.version>4.1.2</poi.version>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>${poi.version}</version>
</dependency>
package ins.platfrom.utils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
public class ExcelUtils {
private final static String excel2003 = ".xls";
private final static String excel2007 = ".xlsx";
private static final String EMPTY = "";
private static final String POINT = ".";
private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
/**
* 获取单元格里的数据
*
* @param hssfCell
* @return
*/
private static String getHCellValue(HSSFCell hssfCell) {
if (hssfCell == null) {
return null;
}
if (hssfCell.getCellType() == CellType.BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == CellType.NUMERIC) {
String cellValue = "";
if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
Date date = HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue());
cellValue = sdf.format(date);
} else {
DecimalFormat df = new DecimalFormat("#.##");
cellValue = df.format(hssfCell.getNumericCellValue());
String strArr = cellValue.substring(cellValue.lastIndexOf(POINT) + 1, cellValue.length());
if (strArr.equals("00")) {
cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
}
}
return cellValue;
} else {
return String.valueOf(hssfCell.getStringCellValue());
}
}
/**
* 获取单元格里的数据
* @param xssfCell
* @return
*/
public static String getXCellValue(XSSFCell xssfCell) {
if (xssfCell == null) {
return null;
}
if (xssfCell.getCellType() == CellType.BOOLEAN) {
return String.valueOf(xssfCell.getBooleanCellValue());
} else if (xssfCell.getCellType() == CellType.NUMERIC) {
String cellValue = "";
if (HSSFDateUtil.isCellDateFormatted(xssfCell)) {
Date date = HSSFDateUtil.getJavaDate(xssfCell.getNumericCellValue());
cellValue = sdf.format(date);
} else {
DecimalFormat df = new DecimalFormat("#.##");
cellValue = df.format(xssfCell.getNumericCellValue());
String strArr = cellValue.substring(cellValue.lastIndexOf(POINT) + 1, cellValue.length());
if (strArr.equals("00")) {
cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
}
}
return cellValue;
} else {
return String.valueOf(xssfCell.getStringCellValue());
}
}
/**
* @Author: - sheep
* @Date: 2019/3/4 11:56
* @return: 功能描述:<br>
* 获取单元格里的数据
*/
public static String getXCellValue1(Cell cell) {
if (cell == null) {
return null;
}
if (cell.getCellType() == CellType.BOOLEAN) { //布尔型
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == CellType.NUMERIC) { //数值型
String cellValue = "";
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
cellValue = sdf.format(date);
} else {
DecimalFormat df = new DecimalFormat("#.###");
cellValue = df.format(cell.getNumericCellValue());
String strArr = cellValue.substring(cellValue.lastIndexOf(POINT) + 1, cellValue.length());
if (strArr.equals("00")) {
cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
}
}
return cellValue;
} else if (cell.getCellType() == CellType.FORMULA) { //公式型
return cell.getCellFormula();
} else if (cell.getCellType() == CellType.STRING) { //字符串类型
return String.valueOf(cell.getStringCellValue());
} else { //异常类型 或 空
return null;
}
}
/**
* @Author: - sheep
* @Date: 2019/3/4 12:32
* @return: 功能描述:<br>
* 判断excel文件的格式
*/
public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if (excel2003.equals(fileType)) {
wb = new HSSFWorkbook(inStr);
} else if (excel2007.equals(fileType)) {
wb = new XSSFWorkbook(inStr);
} else {
throw new Exception("上传的Excel文件格式有误。请检查。");
}
return wb;
}
public static boolean isCellEmpty(Cell cell) {
if (cell == null || String.valueOf(cell).length() == 0) {
return true;
}
return false;
}
}
/**
* 六月 测试
* @param file
* @return
*/
public CommonResult JuneTest(MultipartFile file) {
//工作簿
Workbook work;
try {
//文件名
String filename = file.getOriginalFilename();
log.info("上传发起任务文件:" + filename);
work = ExcelUtils.getWorkbook(file.getInputStream(), filename);
} catch (Exception e) {
log.info("读取失败");
return new CommonResult<>("400", "读取失败", "");
}
//获取第一个表 你有几个就写几个 多个写循环
Sheet sheet = work.getSheetAt(0);
//获取行数
int lastRowIndex = sheet.getLastRowNum();
log.info("总行数：" + lastRowIndex);
//反馈上传信息
String strMsg = "";
//去重
Set<String> openidset = new HashSet<>();
//读取上传文件
for (int i = 0; i <= lastRowIndex; i++) {
if (i % 200 == 0) {
log.info("第：" + i);
}
//跳过表头
if (i == 0) {
continue;
}
Row row = sheet.getRow(i);
if (row == null && i == lastRowIndex) {
break;
} else if (row == null) {
strMsg += "第" + (i + 1) + "行数据为空。";
continue;
}
//获取当前行列数
short lastCellNum = row.getLastCellNum();
String openid = "";
for (int j = 0; j < lastCellNum; j++) {
Cell cell = row.getCell(j);
if (cell == null) {
if (lastCellNum == 1) {
strMsg += "第" + (i + 1) + "行数据为空" + j;
}
continue;
} else {
//获取每一列值 默认取值为字符串
String cellValue = cell.getStringCellValue().trim();
if (j == 0) {
//第一列 根据自己具体业务需求修改
} else if (j == 1) {
openid=cellValue;
}
}
}
//涉及判断是否文件某一元素去重
if(openidset.contains(openid)){
log.info("重复元素，第"+(i+1)+"行，"+openid);
continue;
}else {
openidset.add(openid);
}
//具体业务逻辑 可以入库 保存 。。。
}
openidset.clear();
return new CommonResult<>("200", "success", strMsg);
}
我的这篇逻辑为上传用户手机号或者身份证号查找用户然后输出对应的查询结果下载文件
/**
* 读取上传文件
* 筛选数据
* 提供下载链接
* @param file
* @return
*/
public CommonResult Test(MultipartFile file, HttpServletResponse response) {
File sumpath = new File(sumPath);
if (!sumpath.exists()) {
sumpath.mkdir();
}
Workbook work;
//1、创建Excel工作薄
XSSFWorkbook isbook;
XSSFWorkbook nobook;
//查询存在文件
Sheet issheet;
//记录不存在文件
Sheet nosheet;
try {
String filename = file.getOriginalFilename();
log.info("上传发起任务文件:" + filename);
work = ExcelUtils.getWorkbook(file.getInputStream(), filename);
isbook = new XSSFWorkbook();
nobook = new XSSFWorkbook();
//2 创建工作表
issheet = isbook.createSheet();
nosheet = nobook.createSheet();
} catch (Exception e) {
log.info("读取失败");
return new CommonResult<>("400", "读取失败", "");
}
Sheet sheet = work.getSheetAt(0);
//获取行数
int lastRowIndex = sheet.getLastRowNum();
log.info("总行数：" + lastRowIndex);
Set<String> openidset = new HashSet<>();
Map<String, Integer> map = new HashMap<>();
//反馈上传信息
String strMsg = "";
//读取上传文件
for (int i = 0; i <= lastRowIndex; i++) {
if(i%200==0){
log.info("第："+i);
}
//跳过表头
if (i == 0) {
continue;
}
if (i % 1000 == 0) {
log.info(i + "");
}
Row row = sheet.getRow(i);
if (row == null && i == lastRowIndex) {
break;
} else if (row == null) {
strMsg += "第" + (i + 1) + "行数据为空。";
continue;
}
//获取当前行列数
short lastCellNum = row.getLastCellNum();
String phone = "";
String idcard = "";
for (int j = 0; j < lastCellNum; j++) {
Cell cell = row.getCell(j);
if (cell == null) {
if(lastCellNum==1){
strMsg += "第" + (i + 1) + "行数据为空" + j;
}
continue;
} else {
//获取每一列值
String cellValue = cell.getStringCellValue().trim();
if (j == 0) {
phone = cellValue;
} else if(j ==1) {
idcard = cellValue;
}
}
}
//查询用户是否存在 自定义
String openid = weChatTokenMapper.findOpenidByIdOrPhone(phone, idcard);
Boolean isexit = openidset.contains(openid);
//存在保存 不存在也保存
int islastRowNum = issheet.getLastRowNum();
int nolastRowNum = nosheet.getLastRowNum();
// 行对象通用变量
Row nRow = null;
// 单元格对象通用变量
Cell nCell = null;
//默认创建第一列
if (openid != null && !"".equals(openid) && !isexit) {
openidset.add(openid);
map.put(openid, 1);
//3 创建行 行号需要＋1
nRow = issheet.createRow(++islastRowNum);
//4 创建单元格
nCell = nRow.createCell(0);
//5 设置内容 uid
nCell.setCellValue(openid);
} else {
if (openid != null && !"".equals(openid) && isexit) {
Integer integer1 = map.get(openid);
map.put(openid, ++integer1);
}
nRow = nosheet.createRow(++nolastRowNum);
//4 创建单元格 //默认创建第一列
nCell = nRow.createCell(0);
//5 设置内容 uid
nCell.setCellValue(phone);
nCell = nRow.createCell(1);
//5 设置内容 uid
nCell.setCellValue(idcard);
nCell = nRow.createCell(3);
//5 设置内容 uid
if (isexit) {
nCell.setCellValue(isexit);
}
}
}
if (IFNull.strNotNull(strMsg)) {
return new CommonResult<>("400", "读取失败", strMsg);
}
String isexistPath = sumPath + SnowFlakeUtil.getId() + "_" + DateUtil.getNYR() + "isexist.xlsx";
String noexistPath = sumPath + SnowFlakeUtil.getId() + "_" + DateUtil.getNYR() + "noexist.xlsx";
try {
FileOutputStream isoutput = new FileOutputStream(isexistPath);
FileOutputStream nooutput = new FileOutputStream(noexistPath);
//写入磁盘
isbook.write(isoutput);
nobook.write(nooutput);
isoutput.close();
nooutput.close();
// downloadTemplate(isexistPath,response);
} catch (Exception e) {
e.printStackTrace();
}
return new CommonResult<>("200", "success", isexistPath);
}
<poi.version>3.11</poi.version>
<!-- Excel解析工具类 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>${poi.version}</version>
</dependency>
1 创建工作簿
2 创建工作表
3 创建行
4 创建单元格
5 设置内容
6 设置内容格式
7 下载
注：下载上传都是同步请求 AJAX无法页面下载
解决方案：使用页面同步请求
新版本的最后一栏有更新
// $.ajax({
// url:"/user/exportXls",
// type:"GET"
// })
//正确请求路径
location.href="/user/exportXls";
@Resource
private UserService userService;
@GetMapping("exportXls")
public void contextLoads(HttpServletResponse response) throws Exception{
//准备要报表的数据
List<User> all = userService.findAll();
// 1 创建工作簿 xls HSSFWorkbook xlsx XSSFWorkbook
Workbook wb = new XSSFWorkbook();
// 2 创建工作表
Sheet sheet = wb.createSheet();
//定义公共变量
//行号 和 列号
int rowNo=0,cellNo=0;
// 行对象通用变量
Row nRow = null;
// 单元格对象通用变量
Cell nCell = null;
/****************内容打印****************/
for (User user:all){
//3 创建行 行号需要＋1
nRow = sheet.createRow(rowNo++);
//4 创建单元格
nCell = nRow.createCell(cellNo++);
//5 设置内容 uid
nCell.setCellValue(user.getUid());
//6 设置内容格式
nCell.setCellStyle(contentStyle(wb));
//Telephone
nCell = nRow.createCell(cellNo++);
nCell.setCellValue(user.getTelephone());
nCell.setCellStyle(contentStyle(wb));
//Mail
nCell = nRow.createCell(cellNo++);
nCell.setCellValue(user.getMail());
nCell.setCellStyle(contentStyle(wb));
//Password
nCell = nRow.createCell(cellNo++);
nCell.setCellValue(user.getPassword());
nCell.setCellStyle(contentStyle(wb));
//Type
nCell = nRow.createCell(cellNo++);
nCell.setCellValue(user.getType());
nCell.setCellStyle(contentStyle(wb));
//cellNo归0 为了不出现换行后错列
cellNo = 0;
}
// 7 下载
DownloadUtil downloadUtil = new DownloadUtil();
//ByteArrayOutputStream byteArrayOutputStream -- 输出流
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
// 将wb写进流
wb.write(byteArrayOutputStream);
// HttpServletResponse response -- response
// String returnName -- 下载的名字
downloadUtil.download(byteArrayOutputStream,response,"运单表.xlsx");
}
public CellStyle contentStyle(Workbook wb){
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
Font font = wb.createFont();
font.setFontHeight((short)200);
cellStyle.setFont(font);
return cellStyle;
}
下载结果
新版本的最后一栏有更新
package com.czxy.bos.controller.print;
import com.czxy.bos.domain.take_delivery.WayBill;
import com.czxy.bos.service.take_delivery.WayBillService;
import com.czxy.bos.util.DownloadUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.util.Date;
import java.util.List;
@RestController
@RequestMapping("/report")
public class ReportController {
@Autowired
private WayBillService wayBillService;
@GetMapping("exportXls")
public void exportXls(HttpServletResponse response) throws Exception{
/**
* 查找数据之后，下面只需要将内容写进xls中，然后下载
*/
List<WayBill> wayBillList = wayBillService.findAllWayBill();
//1 创建工作簿 xls HSSFWorkbook xlsx XSSFWorkbook
Workbook wb = new XSSFWorkbook();
//2 创建工作表
Sheet sheet = wb.createSheet();
// 设置列宽---1/256 一个字符的宽度
sheet.setColumnWidth(0,15*256);
sheet.setColumnWidth(1,15*256);
sheet.setColumnWidth(2,15*256);
sheet.setColumnWidth(3,25*256);
sheet.setColumnWidth(4,25*256);
sheet.setColumnWidth(5,25*256);
sheet.setColumnWidth(6,25*256);
sheet.setColumnWidth(7,25*256);
sheet.setColumnWidth(8,25*256);
/**
* 定义公共变量
*/
int rowNo=0,cellNo=0;//行号 和 列号
Row nRow = null;// 行对象通用变量
Cell nCell = null;// 单元格对象通用变量
/****************大标题打印****************/
//3 创建行
nRow = sheet.createRow(rowNo);
//4 创建单元格
nCell = nRow.createCell(cellNo);
//5 设置内容
nCell.setCellValue("bos项目运单表统计"+new Date().toLocaleString());
//6 设置内容格式
// 合并单元格 //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
sheet.addMergedRegion(new CellRangeAddress(0, 0, (short) 0, (short) 9));
// 垂直居中 + 水平居中 + 加粗
CellStyle bigTitleCellStyle = bigTitleStyle(wb);
nCell.setCellStyle(bigTitleCellStyle);
/****************小标题打印****************/
String[] titles={"编号id","运单编号","订单编号","寄件人姓名","寄件人电话","寄件人地址","收件人姓名","收件人电话","收件人地址"};
// 进入小标题打印的时候，行号变化吗？rowNo=0
rowNo++;
// 进入小标题打印的时候，列号需要变化吗？cellNo = 0；
//3 创建行
nRow = sheet.createRow(rowNo);
for (String title:titles){
//4 创建单元格
nCell = nRow.createCell(cellNo++);// 先创建单元格，然后在新增
//5 设置内容
nCell.setCellValue(title);
//6 设置内容格式
nCell.setCellStyle(titleStyle(wb));
}
/****************内容打印****************/
// 单元格需要变化吗
rowNo++;
cellNo=0;
for(WayBill wayBill:wayBillList){
//3 创建行
nRow = sheet.createRow(rowNo++);
//4 创建单元格
//id 对象遍历 需要多少写多少
nCell = nRow.createCell(cellNo++);
nCell.setCellValue(wayBill.getId());
nCell.setCellStyle(contentStyle(wb));
//wayBillNum
nCell = nRow.createCell(cellNo++);
nCell.setCellValue(wayBill.getWayBillNum());
nCell.setCellStyle(contentStyle(wb));
//cellNo归0
cellNo = 0;
}
/****************下载****************/
DownloadUtil downloadUtil = new DownloadUtil();
//ByteArrayOutputStream byteArrayOutputStream -- 输出流
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
// 将wb写进流
wb.write(byteArrayOutputStream);
// HttpServletResponse response -- response
// String returnName -- 下载的名字
downloadUtil.download(byteArrayOutputStream,response,"运单表.xlsx");
System.out.println("okokokok....");
}
/**
* 垂直居中 + 水平居中 + 加粗
* @param wb
* @return
*/
public CellStyle bigTitleStyle(Workbook wb){
// 创建格式
CellStyle cellStyle = wb.createCellStyle();
// 水平对齐方式
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
// 垂直居中
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
// 设置字体
Font font = wb.createFont();
// 是数值的1/20 大小
font.setFontHeight((short) 480);
font.setBold(true);
font.setColor(Font.COLOR_RED);
cellStyle.setFont(font);
return cellStyle;
}
public CellStyle titleStyle(Workbook wb){
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
Font font = wb.createFont();
font.setFontHeight((short)300);
cellStyle.setFont(font);
return cellStyle;
}
public CellStyle contentStyle(Workbook wb){
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
Font font = wb.createFont();
font.setFontHeight((short)200);
cellStyle.setFont(font);
return cellStyle;
}
}
样式更新
public CellStyle contentStyle(Workbook wb){
CellStyle cellStyle = wb.createCellStyle();
// 水平对齐方式
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置背景色
cellStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font font = wb.createFont();
font.setFontHeight((short)200);
cellStyle.setFont(font);
return cellStyle;
}
边框更新
以前为一个单元格形式添加 现在为整行添加一次 解除代码冗余 使用了新的书写格式
public CellStyle rowStyle(Workbook wb){
CellStyle cellStyle = wb.createCellStyle();
//设置边框
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
//方法无效 设置全行背景色
// cellStyle.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
Font font = wb.createFont();
font.setFontHeight((short)200);
cellStyle.setFont(font);
return cellStyle;
}
创建sheet名 设置单元格宽度
// 2 创建工作表
Sheet sheet = wb.createSheet();
wb.setSheetName(0,"PVA分析");
//七个字符宽度
sheet.setColumnWidth(0,7*256);
祝你幸福
送你一首歌：《城南花已开》三亩地
附图：插画师LOST7_的《睡了吗?摘颗星星给你》