java Excel导出 xlsx格式（超简单）
程序员文章站
2022-07-15 10:18:12
...
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 = "MonthBalance"+monthBalance.getMonth();// Excel文件名
OutputStream os = response.getOutputStream();// 取得输出流
response.reset();// 清空输出流
response.setHeader("Content-disposition", "attachment; filename="
+ fname + ".xlsx"); // 设定输出文件头,该方法有两个参数，分别表示应答头的名字和值。XSSF:xlsx HSSF:xls
response.setContentType("application/msexcel");
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 导出Excel表格通用方法
下一篇： 【Double Week】No.19