package com.yeejoin.fireIotPlatform.common.util;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
public class ExcelDZD {
public static HSSFWorkbook createExcel( List<Map<String, Object>> list, String startdate,String enddate){
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle style = workbook.createCellStyle(); // 单元格样式
Font fontStyle = workbook.createFont(); // 字体样式
fontStyle.setFontName("黑体"); // 字体
fontStyle.setFontHeightInPoints((short) 11); // 大小
// 将字体样式添加到单元格样式中
style.setFont(fontStyle);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
//style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
HSSFCellStyle style5 = workbook.createCellStyle(); // 单元格样式
Font fontStyle5 = workbook.createFont(); // 字体样式
fontStyle5.setBold(true); // 加粗
fontStyle5.setFontName("黑体"); // 字体
fontStyle5.setFontHeightInPoints((short) 11); // 大小
// 将字体样式添加到单元格样式中
style5.setFont(fontStyle5);
// style5.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
// style5.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
// style5.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
// style5.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//
style5.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style5.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style5.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style5.setBorderRight(HSSFCellStyle.BORDER_THIN);
style5.setBorderTop(HSSFCellStyle.BORDER_THIN);
style5.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style5.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
style5.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style5.setWrapText(true);
style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style5.setFillPattern(FillPatternType.SOLID_FOREGROUND);
HSSFSheet sheet = workbook.createSheet("sheet");
sheet.setDefaultColumnWidth(23);
HSSFRow row0 = sheet.createRow(0);
row0.setHeight((short) 600);//目的是想把行高设置成25px
HSSFCellStyle style1 = workbook.createCellStyle();
style1.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
style1.setFillPattern(CellStyle.SOLID_FOREGROUND);
Font fontStyle1 = workbook.createFont(); // 字体样式
fontStyle1.setBold(true); // 加粗
fontStyle1.setFontName("黑体"); // 字体
fontStyle1.setFontHeightInPoints((short) 16); // 大小
style1.setFont(fontStyle1);
style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFCell cell_00 = row0.createCell(0);
cell_00.setCellStyle(style1);
cell_00.setCellValue("单位");
HSSFCell cell_03 = row0.createCell(3);
cell_03.setCellStyle(style1);
cell_03.setCellValue("智能仓储");
HSSFCell cell_10 = row0.createCell(8);
cell_10.setCellStyle(style1);
cell_10.setCellValue("车辆");
HSSFRow row1 = sheet.createRow(1);
HSSFCell cell_001 = row1.createCell(0);
cell_001.setCellStyle(style5);
cell_001.setCellValue("序号");
HSSFCell cell_002 = row1.createCell(1);
cell_002.setCellStyle(style5);
cell_002.setCellValue("大队名称");
HSSFCell cell_0025 = row1.createCell(2);
cell_0025.setCellStyle(style5);
cell_0025.setCellValue("中队名称");
HSSFCell cell_003 = row1.createCell(3);
cell_003.setCellStyle(style5);
cell_003.setCellValue("物联网建设情况");
HSSFCell cell_0032 = row1.createCell(4);
cell_0032.setCellStyle(style5);
cell_0032.setCellValue("管理装备总数");
HSSFCell cell_004 = row1.createCell(5);
HSSFRichTextString richString = new HSSFRichTextString( "入库数量"+System.getProperty("line.separator", "\n")+"("+startdate+"至"+enddate+")" );
Font fontStyle8 = workbook.createFont(); // 字体样式
fontStyle8.setFontName("黑体"); // 字体
fontStyle8.setFontHeightInPoints((short) 8); // 大小
Font fontStyle10 = workbook.createFont(); // 字体样式
fontStyle10.setFontName("黑体"); // 字体
fontStyle10.setFontHeightInPoints((short) 11); // 大小
fontStyle10.setBold(true); // 加粗
//对"Hello,"设置redFont字体
richString.applyFont( 0, 4, fontStyle10 );
//对"World!"设置blueFont字体
richString.applyFont( 4, 28, fontStyle8 );
cell_004.setCellStyle(style5);
cell_004.setCellValue(richString);
HSSFCell cell_005 = row1.createCell(6);
HSSFRichTextString richString2 = new HSSFRichTextString( "出库数量"+System.getProperty("line.separator", "\n")+"("+startdate+"至"+enddate+")" );
//对"Hello,"设置redFont字体
richString2.applyFont( 0, 4, fontStyle10 );
//对"World!"设置blueFont字体
richString2.applyFont( 4,28, fontStyle8 );
cell_005.setCellStyle(style5);
cell_005.setCellValue(richString2);
HSSFCell cell_006 = row1.createCell(7);
HSSFRichTextString richString3 = new HSSFRichTextString( "业务操作频次"+System.getProperty("line.separator", "\n")+"("+startdate+"至"+enddate+")" );
//对"Hello,"设置redFont字体
richString3.applyFont( 0, 6, fontStyle10 );
//对"World!"设置blueFont字体
richString3.applyFont( 6, 30, fontStyle8 );
cell_006.setCellStyle(style5);
cell_006.setCellValue(richString3);
HSSFCell cell_010 = row1.createCell(8);
cell_010.setCellStyle(style5);
cell_010.setCellValue("物联数量");
HSSFCell cell_011 = row1.createCell(9);
cell_011.setCellStyle(style5);
cell_011.setCellValue("管理总数量");
HSSFCell cell_012 = row1.createCell(10);
cell_012.setCellStyle(style5);
cell_012.setCellValue("物联管理率");
// 合并日期占两行(4个参数,分别为起始行,结束行,起始列,结束列)
// 行和列都是从0开始计数,且起始结束都会合并
// 这里是合并excel中日期的两行为一行
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
CellRangeAddress region1 = new CellRangeAddress(0, 0, 3, 7);
CellRangeAddress region2 = new CellRangeAddress(0, 0, 8, 10);
sheet.addMergedRegion(region);
sheet.addMergedRegion(region1);
sheet.addMergedRegion(region2);
String b="";
int start=0;
int c=0;
for (int i = 0; i < list.size(); i++)
{
Map<String, Object> map=list.get(i);
HSSFRow row2 = sheet.createRow(i+2);
if(i==0){
b=map.get("ddname")+"" ;
start=i+2;
c=1;
HSSFCell cellh001 = row2.createCell(0);
cellh001.setCellStyle(style5);
cellh001.setCellValue(c);
HSSFCell cellh002 = row2.createCell(1);
cellh002.setCellStyle(style5);
cellh002.setCellValue(map.get("ddname")+"");
}else{
if(!b.equals(map.get("ddname")+"")){
c++;
b=map.get("ddname")+"" ;
HSSFCell cellh001 = row2.createCell(0);
cellh001.setCellStyle(style5);
cellh001.setCellValue(c);
HSSFCell cellh002 = row2.createCell(1);
cellh002.setCellStyle(style5);
cellh002.setCellValue(map.get("ddname")+"");
if(start!=i+1){
CellRangeAddress region8 = new CellRangeAddress(start, i+1, 0, 0);
CellRangeAddress region9 = new CellRangeAddress(start, i+1, 1, 1);
sheet.addMergedRegion(region9);
sheet.addMergedRegion(region8);
RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, region9, sheet, workbook);
RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, region9, sheet, workbook); //左边框
RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, region9, sheet, workbook); //右边框
RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, region9, sheet, workbook); //上边框
RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, region8, sheet, workbook);
RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, region8, sheet, workbook); //左边框
RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, region8, sheet, workbook); //右边框
RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, region8, sheet, workbook); //上边框
start=i+2;
}else{
HSSFCell cellh0011 = row2.createCell(0);
cellh0011.setCellStyle(style5);
cellh0011.setCellValue(c);
HSSFCell cellh0021 = row2.createCell(1);
cellh0021.setCellStyle(style5);
cellh0021.setCellValue(map.get("ddname")+"");
start=i+2;
}
}else{
if(i==list.size()-1){
CellRangeAddress region8 = new CellRangeAddress(start, i+2, 0, 0);
sheet.addMergedRegion(region8);
CellRangeAddress region9 = new CellRangeAddress(start, i+2, 1, 1);
RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, region9, sheet, workbook);
RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, region9, sheet, workbook); //左边框
RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, region9, sheet, workbook); //右边框
RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, region9, sheet, workbook); //上边框
RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, region8, sheet, workbook);
RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, region8, sheet, workbook); //左边框
RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, region8, sheet, workbook); //右边框
RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, region8, sheet, workbook); //上边框
sheet.addMergedRegion(region9);
}
}
}
HSSFCell cellh002 = row2.createCell(2);
cellh002.setCellStyle(style5);
cellh002.setCellValue(map.get("zdname")+"");
HSSFCell cellh012 = row2.createCell(3);
cellh012.setCellStyle(style);
cellh012.setCellValue(map.get("isno")+"");
HSSFCell cellh003 = row2.createCell(4);
cellh003.setCellStyle(style);
cellh003.setCellValue( map.get("znum")+"");
HSSFCell cellh004 = row2.createCell(5);
cellh004.setCellStyle(style);
cellh004.setCellValue(map.get("rnum")+"");
HSSFCell cellh005 = row2.createCell(6);
cellh005.setCellStyle(style);
cellh005.setCellValue(map.get("cnum")+"");
HSSFCell cellh006 = row2.createCell(7);
cellh006.setCellStyle(style);
cellh006.setCellValue(map.get("pnum")+"");
HSSFCell cellh010 = row2.createCell(8);
cellh010.setCellStyle(style);
cellh010.setCellValue(map.get("cwnum")+"");
HSSFCell cellh011 = row2.createCell(9);
cellh011.setCellStyle(style);
cellh011.setCellValue(map.get("cznum")+"");
HSSFCell cellh0121 = row2.createCell(10);
cellh0121.setCellStyle(style);
cellh0121.setCellValue(map.get("czlnum")+"");
}
return workbook;
}
}