package com.cmb.app.driverhome.util.excelHandle;
import com.cmb.app.driverhome.util.ExcelUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
public abstract class ExcelCreateHandle {
public static String classPath = ExcelUtil.class.getResource("").getPath();
/**
* 删除Excel
*/
public static boolean deleteExcel(String classPath) {
File file = new File(classPath);
// 判断目录或文件是否存在
if (!file.exists()) { // 不存在返回 false
return false;
} else {
// 判断是否为文件
if (file.isFile() && file.exists()) {
file.delete();
return true;
} else {
return false;
}
}
}
public abstract void creatExcel(List list);
/**
* 冻结单元格
*
* @param sheet
* @param rowSplit 行号
* @param rowNumber 列号
*/
public void setSheetFreezePane(Sheet sheet, int rowNumber, int rowSplit) {
int colSplitNum = sheet.getRow(rowNumber).getLastCellNum();
int rowSplitNum = rowSplit;
sheet.createFreezePane(colSplitNum, rowSplitNum);
}
/**
* 字体格式
*/
public void setFontStyle(Workbook wb, Row row, String[] str, short fontSize) {
Cell cell = null;
Font font = wb.createFont();
font.setFontHeightInPoints(fontSize);// 字号
font.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
font.setFontName("宋体");
CellStyle style = wb.createCellStyle();
style.setWrapText(true);
style.setFont(font);
setPublicStyle(style);
for (int i = 0; i < str.length; i++) {
cell = row.createCell(i);
cell.setCellValue(str[i]);
cell.setCellStyle(style);
}
}
/**
* 设置公共样式：细边框，居中
*/
public void setPublicStyle(CellStyle style) {
setBorderStyle(style);
setAlignment(style);
}
/**
* 细边框
*/
public void setBorderStyle(CellStyle style) {
style.setBorderTop(CellStyle.BORDER_THIN);// 上边框
style.setBorderRight(CellStyle.BORDER_THIN);// 右边框
style.setBorderBottom(CellStyle.BORDER_THIN);// 下边框
style.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
}
/**
* 上下左右居中
*/
public void setAlignment(CellStyle style) {
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
style.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中
}
/**
* 设置Excel的宽度
*/
public void setColumnWidth(Sheet sheet, int[] widths) {
for (int i = 0; i < widths.length; i++) {
sheet.setColumnWidth(i, widths[i] * 256);
}
}
/**
* 生成excel文件到指定路径
*/
public void outPutExcel(Workbook workbook, String excelPath) {
FileOutputStream os;
try {
os = new FileOutputStream(excelPath);
workbook.write(os);
os.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
package com.cmb.app.driverhome.onlineplan.util;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import com.cmb.app.driverhome.iodj.domain.User;
import com.cmb.app.driverhome.onlineplan.domain.OnlineProgram;
import com.cmb.app.driverhome.onlineplan.domain.OnlineProject;
import com.cmb.app.driverhome.util.CommonUtils;
import com.cmb.app.driverhome.util.excelHandle.ExcelCreateHandle;
public class RiskWarnProjectExcelhandle extends ExcelCreateHandle {
private String excelname = "";
public String getExcelname() {
return excelname;
}
public void setExcelname(String excelname) {
this.excelname = excelname;
}
//public static String mypath = RiskWarnProjectExcelhandle.class.getResource("").getPath();
private static Logger logger = LoggerFactory.getLogger(RiskWarnProjectExcelhandle.class);
@Override
public void creatExcel(List list) {
String excelpath = classPath + "\\" + this.excelname + ".xlsx";
logger.debug("文件名："+excelpath);
// 产生表格
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet(excelname+"风险警示");
try {
setHeader(workbook, sheet);
//setRowContent(workbook, sheet, list);
for (int i = 0; i < list.size(); i++) {
OnlineProject project = (OnlineProject) list.get(i);
setRowContent(workbook, sheet, project);
}
setSheetFreezePane(sheet, 2, 1);
this.outPutExcel(workbook, excelpath);
} catch (Exception e) {
logger.error("导出风险警示_生成excel文件出错" + e.getMessage());
}
}
private void setHeader(Workbook wb, Sheet sheet) throws Exception {
Row row = sheet.createRow(0);
row.setHeight((short) 600);
CellStyle style = wb.createCellStyle();
style.setWrapText(true);
// style.setFillForegroundColor(HSSFColor.LIME.index);
// style.setFillBackgroundColor(HSSFColor.GREEN.index);
// 边框
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setBorderBottom(CellStyle.BORDER_THIN);
// style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 字体加粗
font.setFontHeight((short) 210); // 设置字体大小
font.setFontName("宋体"); // 设置单元格字体
font.setColor(Font.COLOR_NORMAL); // 设置单元格字体的颜色.
// 字体
style.setFont(font);
// row.setRowStyle(style);
// 不可随意调换改变
//String[][] headers = new String[][]{{"序号", "5"}, {"项目编号", "10"}, {"项目名称", "60"}, {"上线日期", "12"}, {"负责人", "30"}, {"投产组长", "16"}, {"FireFly根分支名称", "24"}, {"Label名称", "24"}};
String[][] headers = new String[][]{
{"序号", "5"}, {"项目编号", "10"}, {"项目名称", "30"}, {"上线日期", "12"}, {"负责人", "16"}, {"投产组长", "16"},
{"序号", "5"}, {"FireFly根分支名称", "20"}, {"Label名称", "34"}, {"程序负责人", "16"}
};
for (int i = 0, len = headers.length; i < len; i++) {
String[] header = headers[i];
Cell cell = row.createCell(i);
sheet.setColumnWidth(i, Integer.valueOf(header[1]) * 256);
// sheet.autoSizeColumn(0, true);
cell.setCellValue(header[0]);
cell.setCellStyle(style);
}
}
private void setRowContent(Workbook workbook, Sheet sheet, List list) throws Exception {
if (list == null || list.size() < 1) {
return;
}
CellStyle style = workbook.createCellStyle();
style.setWrapText(true);
// style.setFillForegroundColor(Color.LIME.index);
// style.setFillBackgroundColor(Color.GREEN.index);
// 边框
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setBorderBottom(CellStyle.BORDER_THIN);
// style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
// 字体
Font font = workbook.createFont();
font.setFontName("宋体"); // 设置单元格字体
font.setColor(Font.COLOR_NORMAL); // 设置单元格字体的颜色.
style.setFont(font);
Row row = null;
Cell cell = null;
/*
设定合并单元格区域范围
firstRow 0-based
lastRow 0-based
firstCol 0-based
lastCol 0-based
CellRangeAddress cra = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
*/
int firstRow = 1, lastRow = 1, firstCol = 0, lastCol = 0;
for (int i = 0, len = list.size(); i < len; i++) {
OnlineProject project = (OnlineProject) list.get(i);
List<OnlineProgram> onlinePrograms = project.getOnlinePrograms();
if (CollectionUtils.isEmpty(onlinePrograms)) {
return;
}
int programCount = onlinePrograms.size();
int projectIndex = sheet.getLastRowNum() + 1;
int rowIndex = 1;
row = sheet.createRow(projectIndex);
for (OnlineProgram program : onlinePrograms) {
row.setHeight((short) (500 * programCount));
// row.setRowStyle(style);
lastRow += programCount - 2;
CellRangeAddress rowCra = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
sheet.addMergedRegion(rowCra);
cell = row.createCell(0);// 序号
cell.setCellValue(projectIndex);
cell.setCellStyle(style);
firstCol = 1;
lastCol = 1;
rowCra = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
sheet.addMergedRegion(rowCra);
cell = row.createCell(1);// 项目编号
cell.setCellValue(project.getProjectNo());
cell.setCellStyle(style);
firstCol = 2;
lastCol = 2;
rowCra = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
sheet.addMergedRegion(rowCra);
cell = row.createCell(2);// 项目名称
cell.setCellValue(project.getProjectName());
cell.setCellStyle(style);
firstCol = 3;
lastCol = 3;
rowCra = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
sheet.addMergedRegion(rowCra);
cell = row.createCell(3);// 上线日期
cell.setCellValue(CommonUtils.dateToStr(project.getOnlineDate(), "yyyy-MM-dd"));
cell.setCellStyle(style);
firstCol = 4;
lastCol = 4;
rowCra = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
sheet.addMergedRegion(rowCra);
cell = row.createCell(4);// 负责人
User dutyPerson = project.getDutyPerson();
if (null != dutyPerson && !CommonUtils.isEmpty(dutyPerson.getMobile())) {
cell.setCellValue(project.getPersonInCharge() + "/" + dutyPerson.getMobile());
} else {
cell.setCellValue(project.getPersonInCharge());
}
cell.setCellStyle(style);
firstCol = 5;
lastCol = 5;
rowCra = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
sheet.addMergedRegion(rowCra);
cell = row.createCell(5);// 投产组长
cell.setCellValue(project.getProductionTeamLeader());
cell.setCellStyle(style);
cell = row.createCell(6);// label序号
cell.setCellValue(program.getSerOfProject());
cell.setCellStyle(style);
cell = row.createCell(7);// FireFly根分支名称
cell.setCellValue(program.getFireFlyRootName());
cell.setCellStyle(style);
cell = row.createCell(8);// Label名称
cell.setCellValue(program.getLabel());
cell.setCellStyle(style);
cell = row.createCell(9);// 程序负责人
cell.setCellValue(program.getProgramDutyPerson());
cell.setCellStyle(style);
//风险警示行
row = sheet.createRow(lastRow++);
row.setHeight((short) (500 * 13));
firstRow = lastRow;
lastRow = firstRow;
firstCol = 0;
lastCol = 0;
rowCra = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
sheet.addMergedRegion(rowCra);
cell = row.createCell(1);// 风险警示
cell.setCellValue("风险警示");
cell.setCellStyle(style);
firstCol = 1;
lastCol = 9;
rowCra = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
sheet.addMergedRegion(rowCra);
cell = row.createCell(2);// 风险警示具体内容
cell.setCellValue("风险警示具体内容");
cell.setCellStyle(style);
}
}
}
// 往最后写入一行
private void setRowContent(Workbook workbook, Sheet sheet, OnlineProject project) throws Exception {
List<OnlineProgram> onlinePrograms = project.getOnlinePrograms();
if (CollectionUtils.isEmpty(onlinePrograms)) {
return;
}
CellStyle style = workbook.createCellStyle();
style.setWrapText(true);
// style.setFillForegroundColor(Color.LIME.index);
// style.setFillBackgroundColor(Color.GREEN.index);
// 边框
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setBorderBottom(CellStyle.BORDER_THIN);
// style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
// 字体
Font font = workbook.createFont();
font.setFontName("宋体"); // 设置单元格字体
font.setColor(Font.COLOR_NORMAL); // 设置单元格字体的颜色.
style.setFont(font);
Row row = null;
Cell cell = null;
for (OnlineProgram program : onlinePrograms) {
int index = sheet.getLastRowNum() + 1;
row = sheet.createRow(index);
// row.setHeight((short) 500);
row.setHeightInPoints(28);
// row.setRowStyle(style);
cell = row.createCell(0);// 序号
cell.setCellValue(index);
cell.setCellStyle(style);
cell = row.createCell(1);// 项目编号
cell.setCellValue(project.getProjectNo());
cell.setCellStyle(style);
cell = row.createCell(2);// 项目名称
cell.setCellValue(project.getProjectName());
cell.setCellStyle(style);
cell = row.createCell(3);// 上线日期
cell.setCellValue(CommonUtils.dateToStr(project.getOnlineDate(), "yyyy-MM-dd"));
cell.setCellStyle(style);
cell = row.createCell(4);// 负责人
User dutyPerson = project.getDutyPerson();
if (null != dutyPerson && !CommonUtils.isEmpty(dutyPerson.getMobile())) {
cell.setCellValue(project.getPersonInCharge() + "/" + dutyPerson.getMobile());
} else {
cell.setCellValue(project.getPersonInCharge());
}
cell.setCellStyle(style);
cell = row.createCell(5);// 投产组长
cell.setCellValue(project.getProductionTeamLeader());
cell.setCellStyle(style);
cell = row.createCell(6);// label序号
cell.setCellValue(program.getSerOfProject());
cell.setCellStyle(style);
cell = row.createCell(7);// FireFly根分支名称
cell.setCellValue(program.getFireFlyRootName());
cell.setCellStyle(style);
cell = row.createCell(8);// Label名称
cell.setCellValue(program.getLabel());
cell.setCellStyle(style);
cell = row.createCell(9);// 程序负责人
cell.setCellValue(program.getProgramDutyPerson());
cell.setCellStyle(style);
}
}
private List createList(){
List<OnlineProject> list = new ArrayList<OnlineProject>();
OnlineProject project = new OnlineProject();
project.setOnlineProjectId(1);
project.setProjectNo("P1600641");
project.setProjectName("供应链金融系统二期第一阶段(风险)");
project.setOnlineDate(new Date("2016/07/25"));
project.setPersonInCharge("黄锋/80274620");
project.setProductionTeamLeader("赵浩宇/01076540");
List<OnlineProgram> programs = new LinkedList<OnlineProgram>();
OnlineProgram program = new OnlineProgram();
program.setSerOfProject(1);
program.setFireFlyRootName("风险_信用风险_SCM");
program.setLabel("LU52_P1600641_UAT_20160630_01");
program.setProgramDutyPerson("黄锋/80274620");
programs.add(program);
program = new OnlineProgram();
program.setSerOfProject(2);
program.setFireFlyRootName("风险_信用风险_SCM");
program.setLabel("P1600642_供应链_LR02.07_GTB_UAT_01_20160718_01");
program.setProgramDutyPerson("项军洲/80274720");
programs.add(program);
project.setOnlinePrograms(programs);
list.add(project);
project = new OnlineProject();
project.setOnlineProjectId(2);
project.setProjectNo("T1600642");
project.setProjectName("供应链");
project.setOnlineDate(new Date("2016/07/26"));
project.setPersonInCharge("黎桂林/80174782");
project.setProductionTeamLeader("王俊麟/01039165");
programs = new LinkedList<OnlineProgram>();
program = new OnlineProgram();
program.setSerOfProject(2);
program.setFireFlyRootName("对公_企业年金_SCM");
program.setLabel("LA04_MBank_UAT_803_V04.00_20160707_02");
program.setProgramDutyPerson("邬丹/80274840");
programs.add(program);
program = new OnlineProgram();
program.setSerOfProject(3);
program.setFireFlyRootName("风险_信用风险_SCM");
program.setLabel("LU34_UAT_20160719_01_T1615241");
program.setProgramDutyPerson("顾才泉/00010951");
programs.add(program);
program = new OnlineProgram();
program.setSerOfProject(4);
program.setFireFlyRootName("风险_信贷_SCM");
program.setLabel("RTCSAV03/S160630040(T1609961_R1M1_XD_UAT_03_20160630_01)");
program.setProgramDutyPerson("郭涛/80074150");
programs.add(program);
project.setOnlinePrograms(programs);
list.add(project);
return list;
}
public static void main(String[] args) {
RiskWarnProjectExcelhandle handle = new RiskWarnProjectExcelhandle();
String filename = "上线项目风险警示-2016年7月第4周";
String excelpath = "C:\\Users\\20483\\Desktop\\" + filename + ".xlsx";
logger.debug("文件名："+excelpath);
// 产生表格
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("风险警示");
List list = handle.createList();
Iterator iterator = list.iterator();
while (iterator.hasNext()) {
OnlineProject object = (OnlineProject) iterator.next();
System.out.println(object);
List<OnlineProgram> programs = object.getOnlinePrograms();
Iterator<OnlineProgram> it = programs.iterator();
while (it.hasNext()) {
OnlineProgram program = (OnlineProgram) it.next();
System.out.println(program);
}
}
try {
handle.setHeader(workbook, sheet);
//handle.setRowContent(workbook, sheet, list);
for (int i = 0; i < list.size(); i++) {
OnlineProject project = (OnlineProject) list.get(i);
handle.setRowContent(workbook, sheet, project);
}
handle.outPutExcel(workbook, excelpath);
System.out.println("success");
} catch (Exception e) {
e.printStackTrace();
logger.error("导出风险警示_生成excel文件出错" + e.getMessage());
}
}
}
相关推荐
helloword
"HelloWord+Android平台搭建心得"这个主题，意味着我们将深入探讨如何在Android环境中配置开发工具，编写并运行你的第一个Android应用——HelloWorld。 一、Android SDK安装与管理 Android SDK是Android应用程序...
在这个"Spring MVC HelloWord代码"示例中，我们将探讨如何创建一个基本的Spring MVC应用程序。 首先，我们需要理解Spring MVC的基本组成部分： 1. **DispatcherServlet**：这是Spring MVC的前端控制器，负责接收...
在HelloWord.zip文件中，我们可以看到"HelloWord"这个项目，这是一个经典的“Hello, World!”程序，是每个编程语言初学者的第一个例子，用于展示如何在控制台输出文本。 在Swift中，创建一个简单的"Hello, World!...
【标题】"HelloWord.rar" 是一个压缩包文件，它包含了一个C#编程语言实现的串口调试助手的源代码。这个工具对于学习C#语言，尤其是涉及到串口通信的开发人员来说，是非常有价值的资源。 【描述】描述中提到，这个...
这个名为 "Helloword.rar" 的压缩包文件包含了两份与Python学习相关的源代码文件：Basic.py 和 Hello.py，很可能是为初学者设计的一些基础示例。 **Hello.py** 文件通常包含的就是 "Hello, World!" 程序，它是任何...
本主题“黑莓开发HelloWord”旨在引导开发者了解如何在黑莓平台上创建一个简单的“Hello, World!”应用，这是所有编程语言入门的标志性练习。通过这个过程，我们可以学习到基础的黑莓开发环境搭建、开发工具的使用，...
Python打印helloword
HelloWord文件
HelloWord示例
本教程将指导你如何使用Eclipse IDE和Maven构建工具来创建一个基础的Spring MVC项目，并实现"HelloWord"功能。 首先，我们需要创建一个新的Maven项目。在Eclipse中，选择“File” > “New” > “Maven Project”。...
【PHP项目HelloWord】是一个适合初学者入门的项目，它主要涵盖了PHP与MySQL数据库的交互，包括基础的CRUD操作（创建、读取、更新、删除）以及分页功能的实现。这个项目对于想要深入理解PHP后端开发和数据库管理的...
本人写的第一个PHP程序，哈哈，很简单的一个Helloword
《HelloWord WDM驱动开发》 在Windows操作系统中，WDM（Windows Driver Model）驱动程序是一种广泛使用的设备驱动模型，它支持多种类型的硬件设备，并且兼容性极强，从早期的Windows 95到现代的Windows 10系统都能...
java 入门 几乎是所有学习java的编写的第一个java程序
本源码是基于Javascript开发的React Native Helloword设计，包含142个文件，其中包括28个.js文件，24个.png文件，以及13个.json文件。此外，还包括8个.plist文件，6个.gradle文件，6个.xml文件，以及6个.properties...
Struts2是一个强大的MVC（Model-View-Controller）框架，用于构建企业级Java Web应用程序。在本项目“struts2 helloworld”中，我们将会探索如何在MyEclipse 6.0集成开发环境中，利用JDK 1.6和Tomcat 6.0服务器来...
"joomla组件开发模板helloWord"是一个专门为Joomla开发者设计的简易模板，旨在加速组件开发过程，让开发者能快速上手并构建自己的组件。 首先，我们来看`com_helloworld.xml`文件，这是Joomla组件的核心配置文件，...