java Excel导出 xlsx格式（超简单）

已于 2022-11-08 11:48:05 修改
阅读量1.9w 收藏 58
点赞数 11
文章标签： java excel导出
于 2019-06-26 17:32:23 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_40997700/article/details/93755028
版权

XSSF:xlsx
HSSF:xls
HSSF是POI工程对Excel 97(-2007)文件操作的纯Java实现
XSSF是POI工程对Excel 2007 OOXML (.xlsx)文件操作的纯Java实现
我之前是导出xls版本的，后来改成xlsx版本的，想切换版本就把全文的HSSF和XSSF替换了就可以了，可能会有极个别的方法不适用，自行百度一下就可以了

package com.wttech.tfjd.utils;

import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.wttech.tfjd.model.assess.MonthBalance;
import com.wttech.tfjd.model.assess.PointHistory;
import com.wttech.tfjd.model.assess.SalaryHistory;
import com.wttech.tfjd.model.assess.StatisticalHistory;

public class DownPOIUtils {
	/**
	 * 
	 * @param response:响应对象，类型是HttpServletResponse
	 * @param map:要封装的信息的map容器，其中key为Student，value为String类型的，在这里代表分数
	 * @throws Exception:代表异常对象
	 */
	public static void downPoi(HttpServletResponse response,MonthBalance monthBalance) throws Exception {
		String fname = "行政权力清单.xlsx";// Excel文件名
		OutputStream os = response.getOutputStream();// 取得输出流
		response.reset();// 清空输出流
		response.setHeader("Content-Disposition", "attachment;filename=" +URLEncoder.encode(fname, "UTF-8")); // 设定输出文件头,该方法有两个参数，分别表示应答头的名字和值。XSSF:xlsx    HSSF:xls  中文要用encode处理一下
		response.setContentType("application/msexcel");
		response.setContentType("text/html; charset=UTF-8"); //设置编码字符
		try {
			new DownPOIUtils().new POIS().createFixationSheet1(os, monthBalance);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
 
	
class POIS {
	public void createFixationSheet1(OutputStream os,MonthBalance monthBalance) throws Exception {
			List<PointHistory> pointHistoryList = monthBalance.getPointHistoryList();
			List<StatisticalHistory> statisticalHistoryList = monthBalance.getStatisticalHistoryList();
			List<SalaryHistory> salaryHistoryList = monthBalance.getSalaryHistoryList();
			// 创建工作薄
			XSSFWorkbook wb = new XSSFWorkbook();
			// sheet1
			XSSFSheet sheet1 = wb.createSheet();
			//固定绩效
			int gdjx = 0;
			//考核绩效
			BigDecimal khjx = new BigDecimal(0);
			//应发绩效
			BigDecimal yfjx = new BigDecimal(0);
			
			
			//设置列宽
			sheet1.setColumnWidth(2, 4000); 
			sheet1.setColumnWidth(3, 6000); 
			
			XSSFRow sheet1row1 = sheet1.createRow((short) 0);
			sheet1.createFreezePane(0, 1);
			cteateCell(wb, sheet1row1, (short) 0, "序号");
			cteateCell(wb, sheet1row1, (short) 1, "姓名");
			cteateCell(wb, sheet1row1, (short) 2, "工作岗位");
			cteateCell(wb, sheet1row1, (short) 3, "身份证号码");
			cteateCell(wb, sheet1row1, (short) 4, "固定绩效");
			cteateCell(wb, sheet1row1, (short) 5, "考核绩效");
			cteateCell(wb, sheet1row1, (short) 6, "应发绩效");
			cteateCell(wb, sheet1row1, (short) 7, "备注");
			for(int i =1;i <= salaryHistoryList.size();i++){
				XSSFRow sheet1rowi = sheet1.createRow((short) i);
				cteateCell(wb, sheet1rowi, (short) 0, String.valueOf(i));
				cteateCell(wb, sheet1rowi, (short) 1, salaryHistoryList.get(i-1).getUser().getUsername());
				cteateCell(wb, sheet1rowi, (short) 2, salaryHistoryList.get(i-1).getJob());
				cteateCell(wb, sheet1rowi, (short) 3, salaryHistoryList.get(i-1).getIdnumber());
				cteateNumberCell(wb, sheet1rowi, (short) 4, salaryHistoryList.get(i-1).getFixedperformance());
				cteateNumberCell(wb, sheet1rowi, (short) 5, salaryHistoryList.get(i-1).getKhjx().toString());
				cteateNumberCell(wb, sheet1rowi, (short) 6, salaryHistoryList.get(i-1).getYfjx().toString());
				cteateCell(wb, sheet1rowi, (short) 7, "");
				
				gdjx += Integer.valueOf(salaryHistoryList.get(i-1).getFixedperformance());
				khjx = khjx.add(salaryHistoryList.get(i-1).getKhjx());
				yfjx = yfjx.add(salaryHistoryList.get(i-1).getYfjx());
			}
			XSSFRow sheet1row2 = sheet1.createRow((short) (salaryHistoryList.size()+1));
			cteateNoborderCell(wb,sheet1row2,(short) 4,String.valueOf(gdjx));
			cteateNoborderCell(wb,sheet1row2,(short) 5,khjx.toString());
			cteateNoborderCell(wb,sheet1row2,(short) 6,yfjx.toString());
			
			XSSFRow sheet1row3 = sheet1.createRow((short) (salaryHistoryList.size()+2));
			cteateNoborderCell(wb,sheet1row3,(short) 0,"负责人：");
			cteateNoborderCell(wb,sheet1row3,(short) 1,"池福波");
			
			
			//sheet2
			XSSFSheet sheet2 = wb.createSheet();
			//维修总数合计
			int total = 0;
			BigDecimal gd = new BigDecimal(0);
			BigDecimal rc = new BigDecimal(0);
			
			sheet2.setColumnWidth(6, 4000); 
			sheet2.setColumnWidth(7, 4000); 
			sheet2.setColumnWidth(8, 4000); 
			sheet2.setColumnWidth(9, 4000); 
			sheet2.setColumnWidth(10, 4000); 
			
			XSSFRow sheet2row1 = sheet2.createRow((short) 0);
			sheet2.createFreezePane(0, 1);
			cteateCell(wb, sheet2row1, (short) 0, "姓名");
			cteateCell(wb, sheet2row1, (short) 1, "简单");
			cteateCell(wb, sheet2row1, (short) 2, "一般");
			cteateCell(wb, sheet2row1, (short) 3, "较难");
			cteateCell(wb, sheet2row1, (short) 4, "困难");
			cteateCell(wb, sheet2row1, (short) 5, "重大");
			cteateCell(wb, sheet2row1, (short) 6, "维护总数合计");
			cteateCell(wb, sheet2row1, (short) 7, "维修量占比");
			cteateCell(wb, sheet2row1, (short) 8, "工单绩效工资");
			cteateCell(wb, sheet2row1, (short) 9, "日常绩效得分");
			cteateCell(wb, sheet2row1, (short) 10, "日常绩效工资");
			for(int i =1;i <= statisticalHistoryList.size();i++){
				XSSFRow sheet2rowi = sheet2.createRow((short) i);
				cteateCell(wb, sheet2rowi, (short) 0, statisticalHistoryList.get(i-1).getUser().getUsername());
				cteateCell(wb, sheet2rowi, (short) 1, statisticalHistoryList.get(i-1).getEasy()==null?"":statisticalHistoryList.get(i-1).getEasy().toString());
				cteateCell(wb, sheet2rowi, (short) 2, statisticalHistoryList.get(i-1).getNormal()==null?"":statisticalHistoryList.get(i-1).getNormal().toString());
				cteateCell(wb, sheet2rowi, (short) 3, statisticalHistoryList.get(i-1).getLessdifficult()==null?"":statisticalHistoryList.get(i-1).getLessdifficult().toString());
				cteateCell(wb, sheet2rowi, (short) 4, statisticalHistoryList.get(i-1).getDifficult()==null?"":statisticalHistoryList.get(i-1).getDifficult().toString());
				cteateCell(wb, sheet2rowi, (short) 5, statisticalHistoryList.get(i-1).getImportant()==null?"":statisticalHistoryList.get(i-1).getImportant().toString());
				cteateCell(wb, sheet2rowi, (short) 6, statisticalHistoryList.get(i-1).getTotal()==null?"":statisticalHistoryList.get(i-1).getTotal().toString());
				cteateCell(wb, sheet2rowi, (short) 7, statisticalHistoryList.get(i-1).getAccountedfor()==null?"":statisticalHistoryList.get(i-1).getAccountedfor());
				cteateNumberCell(wb, sheet2rowi, (short) 8, statisticalHistoryList.get(i-1).getGdwage().toString());
				cteateCell(wb, sheet2rowi, (short) 9, statisticalHistoryList.get(i-1).getScore().toString());
				cteateNumberCell(wb, sheet2rowi, (short) 10, statisticalHistoryList.get(i-1).getRcwage().toString());
				
				if(statisticalHistoryList.get(i-1).getTotal()!=null){
					total += statisticalHistoryList.get(i-1).getTotal();
				}
				gd = gd.add(statisticalHistoryList.get(i-1).getGdwage());
				rc = rc.add(statisticalHistoryList.get(i-1).getRcwage());
			}
			XSSFRow sheet2row2 = sheet2.createRow((short) statisticalHistoryList.size()+1);
			cteateCell(wb,sheet2row2,(short)0,"合计：");
			cteateCell(wb,sheet2row2,(short)1,"");
			cteateCell(wb,sheet2row2,(short)2,"");
			cteateCell(wb,sheet2row2,(short)3,"");
			cteateCell(wb,sheet2row2,(short)4,"");
			cteateCell(wb,sheet2row2,(short)5,"");
			cteateCell(wb,sheet2row2,(short)6,String.valueOf(total));
			cteateCell(wb,sheet2row2,(short)7,"100%");
			cteateCell(wb,sheet2row2,(short)8,gd.setScale(0, BigDecimal.ROUND_HALF_UP).toString());
			cteateCell(wb,sheet2row2,(short)9,"");
			cteateCell(wb,sheet2row2,(short)10,rc.setScale(0, BigDecimal.ROUND_HALF_UP).toString());
			
			//sheet3
			XSSFSheet sheet3 = wb.createSheet();
			
			sheet3.setColumnWidth(1, 4000); 
			sheet3.setColumnWidth(3, 6000); 
			sheet3.setColumnWidth(4, 6000); 
			sheet3.setColumnWidth(5, 6000); 
			
			XSSFRow sheet3row1 = sheet3.createRow((short) 0);
			sheet3.createFreezePane(0, 1);
			cteateCell(wb, sheet3row1, (short) 0, "序号");
			cteateCell(wb, sheet3row1, (short) 1, "时间");
			cteateCell(wb, sheet3row1, (short) 2, "姓名");
			cteateCell(wb, sheet3row1, (short) 3, "考核加（扣）分内容");
			cteateCell(wb, sheet3row1, (short) 4, "加（扣）分依据");
			cteateCell(wb, sheet3row1, (short) 5, "加（扣）分值");
			cteateCell(wb, sheet3row1, (short) 6, "备注");
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
			for(int i =1;i <= pointHistoryList.size();i++){
				XSSFRow sheet3rowi = sheet3.createRow((short) i);
				cteateCell(wb, sheet3rowi, (short) 0, String.valueOf(i));
				cteateCell(wb, sheet3rowi, (short) 1, simpleDateFormat.format(pointHistoryList.get(i-1).getPointdate()));
				cteateCell(wb, sheet3rowi, (short) 2, pointHistoryList.get(i-1).getUser().getUsername());
				cteateCell(wb, sheet3rowi, (short) 3, pointHistoryList.get(i-1).getContent());
				cteateCell(wb, sheet3rowi, (short) 4, pointHistoryList.get(i-1).getEvidence());
				cteateCell(wb, sheet3rowi, (short) 5, pointHistoryList.get(i-1).getCount().toString());
				cteateCell(wb, sheet3rowi, (short) 6, "");
			}
			//给每个sheet页起名字
			wb.setSheetName(0, "绩效工资发放表");
			wb.setSheetName(1, "绩效工资统计表");
			wb.setSheetName(2, "考核加扣分统计表");
			wb.write(os);
			os.flush();
			os.close();
			System.out.println("文件生成");
 
		}
 
		@SuppressWarnings("deprecation")
		private void cteateCell(XSSFWorkbook wb, XSSFRow row, short col,String val) {
			//设置行高
			row.setHeight((short) 480);
			XSSFCell cell = row.createCell(col);
			cell.setCellValue(val);
			XSSFCellStyle cellstyle = wb.createCellStyle();
			//HSSFFont Font = wb.createFont();
			//Font.setFontHeightInPoints((short) 10);
			//cellstyle.setFont(Font);
			cellstyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
			cellstyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
			cellstyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
			cellstyle.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
			cellstyle.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
			cell.setCellStyle(cellstyle);
		}
		@SuppressWarnings("deprecation")
		private void cteateNoborderCell(XSSFWorkbook wb, XSSFRow row, short col,String val) {
			//设置行高
			row.setHeight((short) 480);
			XSSFCell cell = row.createCell(col);
			cell.setCellValue(val);
			XSSFCellStyle cellstyle = wb.createCellStyle();
			//HSSFFont Font = wb.createFont();
			//Font.setFontHeightInPoints((short) 10);
			//cellstyle.setFont(Font);
			cellstyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
			cell.setCellStyle(cellstyle);
		}
		@SuppressWarnings({ "deprecation", "static-access" })
		private void cteateNumberCell(XSSFWorkbook wb, XSSFRow row, short col,String val) {
			//设置行高
			row.setHeight((short) 480);
			//生成单元格
			XSSFCell cell = row.createCell(col);
			// 设置单元格内容为double类型
			cell.setCellValue(Float.valueOf(val));
			
			//生成单元格样式
			XSSFCellStyle cellstyle = wb.createCellStyle();
			
			XSSFDataFormat df = wb.createDataFormat(); // 此处设置数据格式
			cellstyle.setDataFormat(df.getFormat("#,##0.00"));//保留两位小数点,("#,#0"数据格式只显示整数)("#,##0.00"保留两位)
			
			cellstyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
			cellstyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
			cellstyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
			cellstyle.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
			cellstyle.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
			cell.setCellStyle(cellstyle);
		}
	}
}

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

回首丶掏
关注
java poi 导出xlsx_java 使用poi存储Excel（.xlsx格式）
weixin_39800957的博客
02-24 382
效果图： package excel;import com.alibaba.fastjson.JSONArray;import com.alibaba.fastjson.JSONObject;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.xss...
java导出xlsx文档
10-29
java导出数据以xlsx形式，用io技术调用hssfwordbook等类来实现
Java导入导出Excel表格（xls版本、xlsx版本）
叁金Coder
11-09 7627
信息化建设的终局：不管怎么吹ERP、OA、CRM、BI或者是别的什么高大上的系统，最终都会回归到两个功能：1、从Excel导入数据，2、把数据导出成一个Excel 二、导入导出工具类：
java使用poi将xls转为xlsx，用luckyexcel预览（踩坑+源码）
最新发布
sdfghjklg的博客
09-23 425
使用kkfileview对xls文件进行预览，但默认方式使用的将xls转为html再预览，但html的预览存在一些安全因素，故采取先将xls转为xlsx，再使用xlsx预览方式的方案.本文主要提供解决方案以及踩坑要点。
java生成xlsx文件
mxasd的博客
07-25 3638
目前项目需要导入一批数据到数据库中，但是不想通过crud写入，想到写入到一个xlsx文件当中，在通过navicat导入到数据库中，把sql储存出来即可，我这里使用的apache.poi来生成xlsx文件以下是详细代码与讲解。最后把文件通过navicat的导入功能把数据导入进数据库，把sql储存批量改表明实现。......
java导出xlsx_java导出xlsx文件
weixin_33603802的博客
02-15 547
/** To change this license header, choose License Headers in Project Properties.* To change this template file, choose Tools | Templates* and open the template in the editor.*/packagecom.ndkey.am.stat...
java以文本方式导出xlsx格式
08-02
简单的表格导出，适合 超过7万条数据以上导出。用文本的方式生成xml，再压缩为xlsx格式。不需要poi
java导出xlsx格式excel浏览器有下载提示版本！
07-20
java导出xlsx格式excel浏览器有下载提示版本！java导出xlsx格式excel浏览器有下载提示版本！java导出xlsx格式excel浏览器有下载提示版本！
Java POI 生成Excel(xlsx)文件
11-14
Java用POI实现根据Excel表格模板生成新的Excel并实现数据输出...
java导出xlsx文件
weixin_34148508的博客
08-05 219
UserPortalStatisticExportService.java /* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open ...
poi导出excel支持xls、xlsx格式大数据导出java工具类
03-30
使用poi导出excel支持xls、xlsx格式大数据导出java工具类，支持同一个单元格不同文本格式。找了好久，加上自己修改后，调试通过，发布出来分享，（调整student类位置）包含所需jar包，工具类，使用示例
java实现excel的导出
10-20
用java实现将JTable中的数据写入表Excel中
Java POI根据模板生成Excel(xlsx)文件
11-26
Java POI根据模板生成Excel文件并写入磁盘，资源文件仅为实现的简单测试Demo，并没有进行代码优化，可以直接导入运行，资源文件仅供参考。
java复杂模板excel导出例子
11-02
在Java编程中，导出复杂的Excel模板是一...熟练掌握Apache POI或其他相关库的用法，结合业务需求，能有效提高工作效率并提供高质量的Excel导出功能。在实际开发中，不断优化代码和策略，可以更好地满足性能和功能需求。
java 导出excel，带图片的Excel导出
05-06
而带图片的Excel导出则增加了复杂性，因为涉及到二进制数据的处理和Excel特定格式的支持。以下是一些关于如何在Java中实现这一功能的关键知识点： 1. **Apache POI库**：Apache POI是Java领域广泛使用的处理...
java 导出excel表格（.xlsx）
qq_33134065的博客
07-25 644
jsp 页面 首先需要引入jquery.redirect.js 导出 js页面 function exportExcel(){ var param = {"userId":userId}; $.redirect(path+ '/exportExcel.action',param); } 后台： public class BjUserVO { priv
Java Excel导出为Excel文件工具类(xlsx)版本
weixin_61863480的博客
05-27 831
import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.*; import org.slf4j.Logger; import org.slf4j.LoggerFactory; .
【保姆级】Java后端查询数据库结果导出xlsx文件+打印xlsx表格
小样yx的博客
03-01 3804
Java实现生成xlsx文件，并调用打印机打印
SpringBoot 整合 Java实现导出xlsx
weixin_42683274的博客
04-10 2872
SpringBoot 整合 Java实现导出xlsx
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值