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

回首丶掏

已于 2022-11-08 11:48:05 修改

阅读量1.9w

点赞数 11

文章标签： java excel导出

于 2019-06-26 17:32:23 首次发布

本文链接：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);
		}
	}
}

在这里插入图片描述

回首丶掏

关注

11
点赞
踩
56

收藏

觉得还不错? 一键收藏
3
评论
java Excel导出 xlsx格式（超简单）

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

扫一扫