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;
public class ExcelZD {
public static HSSFWorkbook createExcel( List<Map<String, Object>> dataList, 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.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
HSSFCell cell_00 = row0.createCell(0);
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);
cell_00.setCellStyle(style1);
cell_00.setCellValue("单位");
HSSFCell cell_03 = row0.createCell(2);
cell_03.setCellStyle(style1);
cell_03.setCellValue("业务应用");
HSSFCell cell_07 = row0.createCell(6);
cell_07.setCellStyle(style1);
cell_07.setCellValue("智能仓储");
HSSFCell cell_10 = row0.createCell(9);
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_003 = row1.createCell(2);
cell_003.setCellStyle(style5);
cell_003.setCellValue("装备总数");
HSSFCell cell_004 = row1.createCell(3);
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(4);
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(5);
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_007 = row1.createCell(6);
cell_007.setCellStyle(style5);
cell_007.setCellValue("物联仓库数量");
HSSFCell cell_008 = row1.createCell(7);
cell_008.setCellStyle(style5);
cell_008.setCellValue("仓库总数");
HSSFCell cell_009 = row1.createCell(8);
cell_009.setCellStyle(style5);
cell_009.setCellValue("仓库物联建设率");
HSSFCell cell_010 = row1.createCell(9);
cell_010.setCellStyle(style5);
cell_010.setCellValue("车辆总数");
HSSFCell cell_011 = row1.createCell(10);
cell_011.setCellStyle(style5);
cell_011.setCellValue("总数");
HSSFCell cell_012 = row1.createCell(11);
cell_012.setCellStyle(style5);
cell_012.setCellValue("建设率");
// 合并日期占两行(4个参数,分别为起始行,结束行,起始列,结束列)
// 行和列都是从0开始计数,且起始结束都会合并
// 这里是合并excel中日期的两行为一行
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 1);
CellRangeAddress region1 = new CellRangeAddress(0, 0, 2, 5);
CellRangeAddress region2 = new CellRangeAddress(0, 0, 6, 8);
CellRangeAddress region3 = new CellRangeAddress(0, 0, 9, 11);
sheet.addMergedRegion(region);
sheet.addMergedRegion(region1);
sheet.addMergedRegion(region2);
sheet.addMergedRegion(region3);
for (int i = 0; i < dataList.size(); i++)
{
Map<String, Object> map=dataList.get(i);
HSSFRow row2 = sheet.createRow(i+2);
HSSFCell cellh001 = row2.createCell(0);
cellh001.setCellStyle(style5);
cellh001.setCellValue((i+1)+"");
HSSFCell cellh002 = row2.createCell(1);
cellh002.setCellStyle(style5);
cellh002.setCellValue(map.get("zdname")+"");
HSSFCell cellh003 = row2.createCell(2);
cellh003.setCellStyle(style);
cellh003.setCellValue( map.get("znum")+"");
HSSFCell cellh004 = row2.createCell(3);
cellh004.setCellStyle(style);
cellh004.setCellValue(map.get("rnum")+"");
HSSFCell cellh005 = row2.createCell(4);
cellh005.setCellStyle(style);
cellh005.setCellValue(map.get("cnum")+"");
HSSFCell cellh006 = row2.createCell(5);
cellh006.setCellStyle(style);
cellh006.setCellValue(map.get("pnum")+"");
HSSFCell cellh007 = row2.createCell(6);
cellh007.setCellStyle(style);
cellh007.setCellValue(map.get("zrnum")+"");
HSSFCell cellh008 = row2.createCell(7);
cellh008.setCellStyle(style);
cellh008.setCellValue(map.get("cknum")+"");
HSSFCell cellh009 = row2.createCell(8);
cellh009.setCellStyle(style);
cellh009.setCellValue(map.get("cklnum")+"");
HSSFCell cellh010 = row2.createCell(9);
cellh010.setCellStyle(style);
cellh010.setCellValue(map.get("cwnum")+"");
HSSFCell cellh011 = row2.createCell(10);
cellh011.setCellStyle(style);
cellh011.setCellValue(map.get("cznum")+"");
HSSFCell cellh012 = row2.createCell(11);
cellh012.setCellStyle(style);
cellh012.setCellValue(map.get("czlnum")+"");
}
return workbook;
}
}