package com.wondersgroup.wdls.common.excel;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
public class ExcelPrintA4Utils {
// 显示的导出Excel表的标题(通常第一行)
private String title;
// 导出Excel表的列名(通常第二行)
private String[] rowName;
// 导出Excel表的文件名
private String fileName;
// 导出Excel表的文件主题内容(通常从第三行往下的内容)
// 需要注意的是:主要内容的格式为第一列为数字(序号),从第二列开始都是字符串
// 每一行为Object[]类型,很多行用List包装起来
private List<Object[]> dataList = new ArrayList<Object[]>();
// 用于下载的response
private HttpServletResponse response;
// 构造方法,传入要导出的数据
public ExcelPrintA4Utils(String title, String[] rowName, List<Object[]> dataList, HttpServletResponse response, String fileName) {
this.dataList = dataList;
this.rowName = rowName;
this.title = title;
this.response = response;
this.fileName = fileName;
}
/**
* @Description 设置单元格样式
* @Author zhulei
* @Date 2021/1/26 14:57
* @Param wb:工作薄
* @Return 单元格样式
*/
public static CellStyle getCellStyle(Workbook wb){
// 设置字体
Font font = wb.createFont();
//设置字体名字
font.setFontName("微软雅黑");
//设置样式;
CellStyle style = wb.createCellStyle();
//设置底边框;
style.setBorderBottom(BorderStyle.THIN);
//设置底边框颜色;
style.setBottomBorderColor(IndexedColors.BLACK.index);
//设置左边框;
style.setBorderLeft(BorderStyle.THIN);
//设置左边框颜色;
style.setLeftBorderColor(IndexedColors.BLACK.index);
//设置右边框;
style.setBorderRight(BorderStyle.THIN);
设置右边框颜色;
style.setRightBorderColor(IndexedColors.BLACK.index);
//设置顶边框;
style.setBorderTop(BorderStyle.THIN);
//设置顶边框颜色;
style.setTopBorderColor(IndexedColors.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
/**
* @Description 单元格合并
* @Author zhulei
* @Date 2021/1/26 15:07
* @Param
* @Return
*/
public static Sheet mergeCell(Sheet sheet,int firstRow, int lastRow, int firstCol, int lastCol){
CellRangeAddress region = new CellRangeAddress(firstRow,lastRow,firstCol,lastCol);//获取区域从第firstRow行-lastRow行,firstCol列-lastCol列
sheet.addMergedRegion(region);// 合并region的区域
return sheet;
}
/**
* @Description 标题样式
* @Author zhulei
* @Date 2021/1/26 15:35
* @Param
* @Return
*/
public CellStyle getTitleStyle(Workbook wb) {
// 设置字体
Font font = wb.createFont();
//设置字体大小
font.setFontHeightInPoints((short) 24);
//字体加粗
font.setBold(true);
//设置字体名字
font.setFontName("微软雅黑");
//设置样式;
CellStyle style = wb.createCellStyle();
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
/**
* @Description 列头样式
* @Author zhulei
* @Date 2021/1/27 15:34
* @Param
* @Return
*/
public CellStyle getColumnTopStyle(Workbook wb) {
// 设置字体
Font font = wb.createFont();
//设置字体大小
font.setFontHeightInPoints((short) 12);
//字体加粗
font.setBold(true);
//设置字体名字
font.setFontName("微软雅黑");
//设置样式;
CellStyle style = wb.createCellStyle();
//设置底边框;
style.setBorderBottom(BorderStyle.THIN);
//设置底边框颜色;
style.setBottomBorderColor(IndexedColors.BLACK.index);
//设置左边框;
style.setBorderLeft(BorderStyle.THIN);
//设置左边框颜色;
style.setLeftBorderColor(IndexedColors.BLACK.index);
//设置右边框;
style.setBorderRight(BorderStyle.THIN);
//设置右边框颜色;
style.setRightBorderColor(IndexedColors.BLACK.index);
//设置顶边框;
style.setBorderTop(BorderStyle.THIN);
//设置顶边框颜色;
style.setTopBorderColor(IndexedColors.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
/**
* @Description A4纸打印
* @Author zhulei
* @Date 2021/1/26 16:02
* @Param
* @Return
*/
public static void printA4(Sheet sheet) {
// 设置打印
HSSFPrintSetup printSetup = (HSSFPrintSetup) sheet.getPrintSetup();
printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
printSetup.setLandscape(true); // 打印方向,true:横向,false:纵向(默认)
printSetup.setVResolution((short)600);
printSetup.setScale((short)50);//自定义缩放,此处100为无缩放
sheet.setHorizontallyCenter(true);//设置打印页水平居中
sheet.setVerticallyCenter(false); //设置打印页面为垂直居中
sheet.setAutobreaks(false);//true设置sheet适应于一页,false设置sheet不一定适应于一页
}
public static void main(String[] args) throws IOException {
Workbook wb = new HSSFWorkbook();//定义一个新的工作薄
Sheet sheet = wb.createSheet("sheet");//创建一个sheet
//设置不显示excel网格线
sheet.setDisplayGridlines(false);//其中sheet是Sheet对象
printA4(sheet);//设置A4打印
for(int i=0;i<30;i++){
Row row = sheet.createRow(i);//创建行
Cell cell = row.createCell(0);//获取行单元格
cell.setCellValue("测试excle"+i);//给行单元格设置值
//设置每pagingRow(15)行分页打印
if ((i+1) % 5 == 0){
sheet.setRowBreak(i);//設置每15行分頁打印
}
//设置单元格边框
cell.setCellStyle(getCellStyle(wb));
}
//合并单元格
// merge(sheet,2,1);
//4.设置页脚的页数
HSSFFooter footer = (HSSFFooter )sheet.getFooter();
footer.setCenter( "第" + HSSFFooter.page() + "页/共" + HSSFFooter.numPages()+"页");
FileOutputStream fos = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\测试工作薄.xls");
wb.write(fos);
fos.close();
}
// 下载Excel的方法
public void downloadExcel() {
try {
Workbook workbook = new HSSFWorkbook();// 创建工作簿对象
Sheet sheet = workbook.createSheet(title);// 创建工作表
//设置不显示excel网格线
sheet.setDisplayGridlines(false);//其中sheet是Sheet对象
//设置页脚的页数
HSSFFooter footer = (HSSFFooter )sheet.getFooter();
footer.setCenter( "第" + HSSFFooter.page() + "页/共" + HSSFFooter.numPages()+"页");
CellStyle columnTopStyle = getColumnTopStyle(workbook);//获取列头样式对象
CellStyle titleStyle = getTitleStyle(workbook);//获取标题样式
/*单元格处理*/
CellStyle style = getCellStyle(workbook);//单元格样式对象
int listCount = dataList.size();//数据数量
int ys = listCount % 10;//余数
int page = listCount/10;//页数
int allrowCount = 0;
if(ys==0){
allrowCount = dataList.size()+(page*5);
}else {
allrowCount = dataList.size()+((page+1)*5);
}
int index = 0;//初始化行Index
// 定义列头所需列数
int columnNum = rowName.length;
// 将查询出的数据设置到sheet对应的单元格中
for (int i = 0; i < listCount; i++) { //遍历每个对象
if (index>=allrowCount) break;
Object[] obj = dataList.get(i);
printA4(sheet);//设置A4打印
//设置每pagingRow(14)行分页打印
if (index!=0&&index % 14== 0){
sheet.setRowBreak(index-1);//設置每14行分頁打印
}
//设置每页前四行的值
if(index%14==0){
/*标题处理*/
// 产生表格标题行(占用两行)
Row rowm = sheet.createRow(index);
rowm.setHeight((short)(47.25*20));
Cell cell = rowm.createCell(0);
//合并标题占用的两行
mergeCell(sheet,index,index+2,0,(rowName.length - 1));
cell.setCellStyle(titleStyle);//标题赋样式
cell.setCellValue(title);//标题赋值
index = index+2;//完成两行数据设置值,给行index+2
//标题下的年度号
Row row2 = sheet.createRow(index);
cell = row2.createCell(0);
cell.setCellValue("font");
cell.setCellStyle(titleStyle);
index = index+1;//完成一行数据设置值,给行index+1
// 将列头设置到sheet的单元格中(占一行)
Row rowRowName = sheet.createRow(index);// 在索引2的位置创建行(最顶端的行开始的第二行)
rowRowName.setHeight((short)(30*20));
for (int n = 0; n < columnNum; n++) {
Cell cellRowName = rowRowName.createCell(n);//创建列头对应个数的单元格
cellRowName.setCellType(CellType.STRING); //设置列头单元格的数据类型
HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
cellRowName.setCellValue(text); //设置列头单元格的值
cellRowName.setCellStyle(columnTopStyle); //设置列头单元格样式
}
index = index+1;//完成一行数据设置值,给行index+1
}
Row row = sheet.createRow(index);//创建所需的行数
for (int j = 0; j < obj.length; j++) {
row.setHeight((short)(62.5*20));
Cell cell2 = null; //设置单元格的数据类型
if (j == 0) {
cell2 = row.createCell(j,CellType.NUMERIC); //第一列为数字
cell2.setCellValue(i + 1);
} else {
cell2 = row.createCell(j,CellType.STRING); //第一列为字符
if (!"".equals(obj[j]) && obj[j] != null) {
cell2.setCellValue(obj[j].toString()); //设置单元格的值
}else{
cell2.setCellValue("");
}
}
cell2.setCellStyle(style); //设置单元格样式
}
index = index+1;//完成一行数据设置值,给行index+1
}
//让列宽随着导出的列长自动适应
for (int colNum = 0; colNum < columnNum; colNum++) {
int columnWidth = sheet.getColumnWidth(colNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
Row currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(colNum) != null) {
Cell currentCell = currentRow.getCell(colNum);
if (currentCell.getCellTypeEnum() == CellType.STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
if (colNum == 0) {
sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
} else {
if(columnWidth > 255){
columnWidth = 255;
}
sheet.setColumnWidth(colNum, columnWidth* 256);
}
}
// printA4(sheet);
if (workbook != null) {
try {
if (response != null) {
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=\""+new String(fileName.getBytes("gb2312"),"ISO8859-1"));
OutputStream out = response.getOutputStream();
workbook.write(out);
out.close();
} else {
FileOutputStream outputStream = new FileOutputStream("C:/"+fileName);
workbook.write(outputStream);
outputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
poi_excel_A4规格打印,有标题有页脚
于 2021-01-27 16:33:02 首次发布