java poi导出模板
程序员文章站
2022-04-07 18:09:08
1.导入依赖 org.apache.poi poi 4.1.2 <...
1.导入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
2.导出工具类
package com.zhgd.zhgd.api.common;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.usermodel.*;
/**
*
* excel工具类
*
*/
@SuppressWarnings({ "deprecation" })
public class ExcelUtil2<T> {
// excel的标题
private String title;
// excel的列名
private String[] rowNames;
// excel的数据
private List<Object[]> dataList = new ArrayList<Object[]>();
private HttpServletResponse response;
/**
* 构造方法(一)
* @param title excel的标题
* @param rowNames excel的列名
* @param response
*/
public ExcelUtil2(String title,String[] rowNames,List<Object[]> dataList,HttpServletResponse response){
this.title = title;
this.rowNames = rowNames;
this.response = response;
this.dataList = dataList;
}
public void export(){
try {
// 声明一个工作薄
XSSFWorkbook workBook = new XSSFWorkbook();
// 生成一个表格
XSSFSheet sheet = workBook.createSheet();
//生成一个工作表
workBook.setSheetName(0,"Sheet1");
//数据行单元格样式
XSSFCellStyle style = this.getStyle(workBook);
//表头单元格样式
XSSFCellStyle columnTopStyle = this.getColumnTopStyle(workBook);
//创建标题行 第一行
XSSFRow titleTop = sheet.createRow(0);
//标题样式
XSSFCellStyle title = this.getTitle(workBook);
//合并单元格
CellRangeAddress titleRegion = new CellRangeAddress(0, 0, 0, rowNames.length - 1);
sheet.addMergedRegion(titleRegion);
//生成单元格
XSSFCell cell1 = titleTop.createCell(0, CellType.STRING);
//给单元格附上值即标题
cell1.setCellValue(this.title);
//给单元格添加样式
cell1.setCellStyle(title);
// 创建表头行 第二行
XSSFRow titleRow = sheet.createRow(1);
//给表头设置行高
titleRow.setHeight((short) 500);
for(int i=0;i<rowNames.length;i++){
//设置宽度
sheet.setColumnWidth(i, 4500);
XSSFCell cell = titleRow.createCell(i, CellType.STRING);
cell.setCellStyle(columnTopStyle);
cell.setCellValue(rowNames[i]);
}
//插入需导出的数据
for(int i=0;i<dataList.size();i++){
Object[] obj = dataList.get(i);//遍历每个对象
XSSFRow row = sheet.createRow(i+2);//创建所需的行数
row.setHeight((short) 500);
for(int j=0; j<obj.length; j++){
XSSFCell cell = row.createCell(j, CellType.STRING);
//设置单元格的数据类型
cell.setCellStyle(style);
if(!"".equals(obj[j]) && obj[j] != null){
if(obj[j].toString().split("-").length > 0){
cell.setCellValue(obj[j].toString().replace("-", "")); //设置单元格的值
}else{
cell.setCellValue(obj[j].toString()); //设置单元格的值
}
}else{
cell.setCellValue("");
}
}
}
if(workBook !=null){
try {
if(this.title !=null){
response.setContentType("application/octet-stream;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String(this.title.getBytes(),"iso-8859-1") + ".xlsx");
}else{
String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xlsx";
String headStr = "attachment; filename=\"" + fileName + "\"";
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-Disposition", headStr);
}
OutputStream out = response.getOutputStream();
workBook.write(out);
}
catch (IOException e) {
e.printStackTrace();
}
}
}catch(Exception e){
e.printStackTrace();
}
}
/**
* 标题样式
* @param workbook
* @return
*/
public XSSFCellStyle getTitle(XSSFWorkbook workbook) {
//设置字体
XSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short)20);
//字体加粗
font.setBold(true);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
XSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(BorderStyle.THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
//设置左边框;
style.setBorderLeft(BorderStyle.THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
//设置右边框;
style.setBorderRight(BorderStyle.THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
//设置顶边框;
style.setBorderTop(BorderStyle.THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
/**
* 列头单元格样式
* @param workbook
* @return
*/
public XSSFCellStyle getColumnTopStyle(XSSFWorkbook workbook) {
//设置字体
XSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short)12);
//字体加粗
font.setBold(true);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
XSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(BorderStyle.THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
//设置左边框;
style.setBorderLeft(BorderStyle.THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
//设置右边框;
style.setBorderRight(BorderStyle.THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
//设置顶边框;
style.setBorderTop(BorderStyle.THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
/**
* 列数据信息单元格样式
* @param workbook
* @return
*/
public XSSFCellStyle getStyle(XSSFWorkbook workbook) {
//设置字体
XSSFFont font = workbook.createFont();
font.setFontName("Courier New");
font.setFontHeightInPoints((short)12);
//设置样式;
XSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(BorderStyle.THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
//设置左边框;
style.setBorderLeft(BorderStyle.THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
//设置右边框;
style.setBorderRight(BorderStyle.THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
//设置顶边框;
style.setBorderTop(BorderStyle.THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
}
3.导出调用方法
//数据
List<Object[]> objects = new ArrayList<>();
//标题
String title =departName+"项目"+nowDate[0]+"年"+nowDate[1]+"月人员工资表";
//表头
String[] rowNames = new String[]{"人员姓名","人员编号","身份证号","工人类型","工种","系统出勤天数","实际出勤天数","工资总额(元)","借款(元)","应发工资(元)","实发工资(元)","发放日期","备注"};
ExcelUtil2 excelUtil2 = new ExcelUtil2(title,rowNames,objects,response);
excelUtil2.export();
本文地址:https://blog.csdn.net/zcmtv283850413/article/details/110799971