package com.freecg.controller.manage;
import com.alibaba.druid.sql.visitor.functions.Char;
import com.freecg.commons.base.BaseController;
import com.freecg.dao.company.*;
import com.freecg.dao.hiscompany.*;
import com.freecg.dao.manage.FaithbadinfoDao;
import com.freecg.dao.manage.FaithgoodinfoDao;
import com.freecg.dao.manage.FaithpromptDao;
import com.freecg.dao.scorerule.FaithpubinfoDao;
import com.freecg.dao.scorerule.FaithpubinfocateDao;
import com.freecg.dao.scorerule.FaithpubinfochildcateDao;
import com.freecg.dao.scorerule.FaithscoredictDao;
import com.freecg.dao.sys.DictDao;
import com.freecg.entity.company.*;
import com.freecg.entity.hiscompany.*;
import com.freecg.entity.manage.Faithgoodinfo;
import com.freecg.entity.scorerule.Faithscoredict;
import com.freecg.entity.sys.Dict;
import com.freecg.service.company.ComQualBackService;
import com.freecg.service.impl.company.FaithProjectService;
import com.freecg.service.manage.CompanyMesAudhisService;
import com.freecg.service.manage.CompanyQualAdService;
import com.freecg.service.manage.CompanyadminService;
import com.freecg.service.manage.FaithgoodinfoService;
import com.freecg.utils.sys.DateUtils;
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.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.mvc.support.RedirectAttributes;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* 企业录入申请表
*/
@Controller
@RequestMapping("applicationReport/admin")
public class ApplicationReportController extends BaseController{
/**
* 主表:武汉市园林绿化建设市场诚信管理系统(试行)企业录入申请表
* 附表1:武汉市园林绿化建设市场诚信管理系统(试行)企业基本信息申报表
* 附表2:企业资质信息表
* 附表3:企业技术人员信息表
* 附表4:企业本市业绩表
* 附表5:良好信息申报表
*
* 新增:企业录入申请表+附表1+附表2-附表4 第1报表
* 附表1显示全部 指新增指未通过复审前
* 修改:企业录入申请表+附表1+附表2-附表4 第2报表
* 附表1只显示修改的 指复审通过后
* 新增:企业录入申请表+附表5 第3报表
* 附表5只有新增的,且不包括系统审核,
* 如在本市有固定办公场所,园林绿化高级专业技术人员2人及以上,园林绿化中级及以上专业技术人员5人及以上
*/
@Autowired
private CompanyMesDao companyMesDao;
@Autowired
private CompanyPerDao companyPerDao;
@Autowired
private FaithCompanyManHisDao faithCompanyManHisDao;
@Autowired
private FaithCompanyMesHisDao faithCompanyMesHisDao;
@Autowired
private CompanyTypeDao companyTypeDao;
@Autowired
private FaithCompanyTypeHisDao faithCompanyTypeHisDao;
@Autowired
private DictDao DictDao;
@Autowired
private FaithCompanyPerHisDao faithCompanyPerHisDao;
@Autowired
private FaithCompanyQualHisDao faithCompanyQualHisDao;
@Autowired
private CompanyQualDao companyQualDao;
@Autowired
private FaithCompanyManmesHisDao faithCompanyManmesHisDao;
@Autowired
private CompanyManMesDao companyManMesDao;
@Autowired
private CompanyManDao companyManDao;
@Autowired
private CompanyProjectDao companyProjectDao;
@Autowired
private FaithCompanyProjectHisDao faithCompanyProjectHisDao;
@Autowired
private FaithgoodinfoDao faithgoodinfoDao;
@Autowired
private FaithscoredictDao faithscoredictDao;
/**
* 设置合并后的单元格边框样式
* @param border 宽度
* @param cellRangeAddress 合并后的单元格
* @param sheet
* @param wb
*/
private static void mergedCellBorder(int border, CellRangeAddress cellRangeAddress, Sheet sheet, XSSFWorkbook wb){
RegionUtil.setBorderBottom(border, cellRangeAddress, sheet,wb); // 下边框
RegionUtil.setBorderLeft(border, cellRangeAddress, sheet,wb); // 左边框
RegionUtil.setBorderRight(border, cellRangeAddress, sheet,wb); // 有边框
RegionUtil.setBorderTop(border, cellRangeAddress, sheet,wb); // 上边框
}
/**
* 设置标题样式
* @param wb
* @return
*/
private static CellStyle getCellStyleTitle(XSSFWorkbook wb){
//创建标题的单元格样式cellStyleTitle以及字体样式headerFontTitle
CellStyle cellStyleTitle = wb.createCellStyle();
Font fontTitle = wb.createFont();// 创建字体样式
cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
cellStyleTitle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
fontTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
fontTitle.setFontName("宋体"); // 设置字体类型
fontTitle.setFontHeightInPoints((short) 18); // 设置字体大小
cellStyleTitle.setFont(fontTitle); // 为标题样式设置字体样式
//cellStyleTitle.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);//虚线
return cellStyleTitle;
}
/**
* 设置正文样式
* @param wb
* @return
*/
private static CellStyle getCellStyleText(XSSFWorkbook wb){
//创建标题的单元格样式cellStyleTitle以及字体样式headerFontTitle
CellStyle cellStyle = wb.createCellStyle();
Font font = wb.createFont();// 创建字体样式
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
font.setFontName("宋体"); // 设置字体类型
font.setFontHeightInPoints((short) 14); // 设置字体大小
cellStyle.setFont(font); // 为标题样式设置字体样式
cellStyle.setWrapText(true); //文字超出宽,自增加宽度
return cellStyle;
}
/**
* 设置正文样式(删除线)
* @param wb
* @return
*/
private static CellStyle getCellStyleTextDelete(XSSFWorkbook wb){
//创建标题的单元格样式cellStyleTitle以及字体样式headerFontTitle
CellStyle cellStyle = wb.createCellStyle();
Font font = wb.createFont();// 创建字体样式
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
font.setFontName("宋体"); // 设置字体类型
font.setFontHeightInPoints((short) 14); // 设置字体大小
font.setStrikeout(true);
cellStyle.setFont(font); // 为标题样式设置字体样式
cellStyle.setWrapText(true); //文字超出宽,自增加宽度
return cellStyle;
}
private static CellStyle getCellStyleTextTitle(XSSFWorkbook wb){
CellStyle cellStyle = wb.createCellStyle();
Font fontTitle = wb.createFont();// 创建字体样式
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
((XSSFFont) fontTitle).setBold(true);
fontTitle.setFontName("宋体"); // 设置字体类型
fontTitle.setFontHeightInPoints((short) 14); // 设置字体大小
cellStyle.setFont(fontTitle); // 为标题样式设置字体样式
cellStyle.setWrapText(true); //文字超出宽,自增加宽度
return cellStyle;
}
/**
* 设置填报单位,填报时间样式
* @param wb
* @return
*/
private static CellStyle getCellStyleReportTheUnit(XSSFWorkbook wb){
CellStyle cellStyle = wb.createCellStyle();
Font fontTitle = wb.createFont();// 创建字体样式
cellStyle.setAlignment(CellStyle.ALIGN_LEFT);//水平居中
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
fontTitle.setFontName("宋体"); // 设置字体类型
fontTitle.setFontHeightInPoints((short) 14); // 设置字体大小
cellStyle.setFont(fontTitle); // 为标题样式设置字体样式
cellStyle.setWrapText(true); //文字超出宽,自增加宽度
return cellStyle;
}
/**
* 设置勾选(勾选字体需字体设为Wingdings 2,R才可变成勾选)
* @param wb
* @param str
* @return
*/
private static XSSFRichTextString setStrCheck(XSSFWorkbook wb,String str){
XSSFRichTextString richString = new XSSFRichTextString(str);
//勾选字体
Font selectFont = wb.createFont();
//selectFont.setColor(HSSFColor.RED.index);// 红色
selectFont.setFontName("Wingdings 2");
//未勾选字体
Font otherFonts = wb.createFont();
otherFonts.setFontName("宋体");
//otherFonts.setColor(HSSFColor.BLUE.index);// 蓝色
System.out.println("str.length()="+str.length());
int endIndex;
for (int i = 0;i < str.length();i++){
System.out.println("str.charAt(i)="+str.charAt(i));
if ((str.charAt(i)+"").equals("R")){
richString.applyFont(i,i+1,selectFont);
}else{
richString.applyFont(i,i+1,otherFonts);
}
}
return richString;
}
/**
* 回执抬头样式
* @param wb
* @return
*/
private static CellStyle getCellStyleReceiptLookup(XSSFWorkbook wb){
CellStyle cellStyle = getCellStyleReceiptTail(wb);
Font font = wb.createFont();// 创建字体样式
cellStyle.setFont(font);
cellStyle.setAlignment(CellStyle.ALIGN_LEFT); //左对齐
font.setFontName(HSSFFont.FONT_ARIAL); //设置有下划线的字体
font.setUnderline((byte)1); //下线线
cellStyle.setFont(font);
return cellStyle;
}
/**
* 回执正文样式
* @param wb
* @return
*/
private static CellStyle getCellStyleReceiptTail(XSSFWorkbook wb){
CellStyle cellStyle = getCellStyleText(wb);
Font font = wb.createFont();// 创建字体样式
font.setFontHeightInPoints((short) 11); // 设置字体大小
cellStyle.setFont(font);
return cellStyle;
}
/**
* 回执正文样式
* @param wb
* @return
*/
private static CellStyle getCellStyleReceiptCenter(XSSFWorkbook wb){
CellStyle cellStyle = getCellStyleReceiptTail(wb);
Font font = wb.createFont();// 创建字体样式
cellStyle.setFont(font);
cellStyle.setAlignment(CellStyle.ALIGN_LEFT); //左对齐
return cellStyle;
}
/**
* 给指定行列设置单元格式
* @param wb
* @param sheet
* @param rowFirst 起始行
* @param rowLast 结束行
* @param columnsFirst 起始列
* @param columnsLast 结束列
* @param cellStyle 样式
*/
private static void styleMultipleCells(XSSFWorkbook wb,Sheet sheet,int rowFirst,int rowLast,int columnsFirst,int columnsLast,CellStyle cellStyle){
for (int i=rowFirst;i<=rowLast;i++){
int enterCnt = 0;
for (int j=columnsFirst;j<=columnsLast;j++){
Row row = sheet.getRow(i);
Cell cell = row.getCell(j);
setTheBorder(cellStyle); //设置不包括合并单元格的边框
cell.setCellStyle(cellStyle);
//自适应高
int rwsTemp = row.getCell(j).toString().split("\n").length;
if (rwsTemp > enterCnt) {
enterCnt = rwsTemp;
}
row.setHeight((short)(enterCnt * 400));
//遇到合并单元格时,跳出
if (isMergedRegion(sheet,i,j)){
break;
}
}
}
}
/**
* 单元格边框
* @param cellStyle
*/
private static void setTheBorder(CellStyle cellStyle){
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
}
/**
* 是否为合并单元格
* @param sheet
* @param row 行
* @param column 列
* @return true 是 false 否
*/
public static boolean isMergedRegion(Sheet sheet, int row , int column)
{
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if(row >= firstRow && row <= lastRow)
{
if(column >= firstColumn && column <= lastColumn)
{
return true;
}
}
}
return false;
}
/**
* 根据指定格式,格式化时间
* @param date 时间
* @param dateFormat 格式
* @return
*/
private static String formatDate(Date date,String dateFormat){
if (date != null){
SimpleDateFormat simpleDateFormat = new SimpleDateFormat(dateFormat);
return simpleDateFormat.format(date.getTime());
}
return null;
}
/**
* 遍历合并区域
* @param sheet
*/
private static void traverseMergeArea(Sheet sheet){
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress region = sheet.getMergedRegion(i); //
int colIndex = region.getFirstColumn(); // 合并区域首列位置
int rowNum = region.getFirstRow(); // 合并区域首行位置
System.out.println("第[" + i + "]个合并区域:" + sheet.getRow(rowNum).getCell(colIndex).getStringCellValue());
}
}
/**
* 集体设置合并单元格边框
* @param first 开始单元格
* @param last 结束单元格
* @param border
* @param sheet
* @param wb
*/
private static void mergeAreaBorders(int first,int last,int border, Sheet sheet, XSSFWorkbook wb){
for (int i = first; i <=last; i++) {
//sheet.getMergedRegion(i);
mergedCellBorder(1,sheet.getMergedRegion(i),sheet,wb);
}
}
/**
* 给字符串以指定长度换行
* @param str 字符串
* @param len 长度
* @return
*/
private static String stringWrap(String str,int len){
String s = "";
int c = str.length()%len==0 ? str.length()/len : (str.length()/len+1);
int endIndex=0;
for (int i=0;i<c;i++){
endIndex=(i+1)*len<str.length()?(i+1)*len:(str.length()-1);
s+=str.substring(i*len,endIndex);
if (i==(c-1)){
s+=str.substring(str.length()-1,str.length());
}
if ((c-i)!=1){
s+="\n";
}
}
return s;
}
/**
* 返回类型
* @param companyId 公司id
* @param isAdded true 新增的 false 删除的
* @return
*/
private String businessCategory(Integer companyId,Boolean isAdded){
//String str="R施工 □设计 □监理 □勘察 □招标代理 □造价咨询 □检测 □建设单位";
String str="";
List<Dict> dicts = DictDao.findDefault("ql");
HashMap<String, Object> map = new HashMap<String, Object>();
for (Dict dict:dicts) {
System.out.println("+++++++++++++企业类型 start++++++++++++++");
System.out.println(str.length());
map.clear();
map.put("companyId",companyId);
map.put("typeId",dict.getId());
if (isAdded){
str +=companyTypeDao.selectAdded(map)!=null?"R":"□";
}else {
str += faithCompanyTypeHisDao.selectDelete(map)!=null?"R":"□";
}
str += dict.getLabel()+" ";
System.out.println("+++++++++++++企业类型 end++++++++++++++");
}
return str;
}
/**
* 判断企业类型是否删除
* @param companyId
* @return
*/
private Boolean isTypeDelete(Integer companyId){
Boolean b = false;
List<Dict> dicts = DictDao.findDefault("ql");
HashMap<String, Object> map = new HashMap<String, Object>();
for (Dict dict:dicts){
map.clear();
map.put("companyId",companyId);
map.put("typeId",dict.getId());
if (faithCompanyTypeHisDao.selectDelete(map)!=null){
b = true;
return b;
}
}
return b;
}
/**
* 判断两字符串
* @param str1
* @param str2
* @return 相同 false, 不同 true
*/
private Boolean compareStrings(String str1,String str2){
if (str1.trim().equals(str2.trim())){
return false;
}
return true;
}
/**
* 企业录入申请表
* @param request
* @param response
* @param redirectAttributes
* @param companyId 公司id
* @param first 第几报表
* 第1张报表 新增:企业录入申请表+附表1+附表2-附表4
* 第2张报表 修改:企业录入申请表+附表2-附表4
* 第3张报表 新增:企业录入申请表+附表5
*/
@RequestMapping(value = "export")
public void export(HttpServletRequest request, HttpServletResponse response, RedirectAttributes redirectAttributes, Integer companyId, Integer first) {
try {
System.out.println("+++++++++++++++++++++企业录入申请表 start++++++++++++++++++++++++");
//公司id
System.out.println("企业id:"+companyId);
//第几张表
System.out.println("第几张表:"+first);
CompanyMes companyMes = companyMesDao.selectByPrimaryKey(companyId);
HashMap<String, Object> map = new HashMap<String, Object>();
String titleName = companyMes.getCompanyName()+"企业录入申请表";
titleName += first == 1?"--附表1-4新增":(first == 2?"--附表1-4修改":"--附表5新增");
String fileName = titleName + DateUtils.getDate("yyyyMMddHHmmss") + ".xlsx";
ServletOutputStream outputStream = response.getOutputStream();
response.setHeader("Content-disposition", "attachment; fileName=".concat(String.valueOf(URLEncoder.encode(fileName, "UTF-8"))));
response.setContentType("application/msexcel");
//创建HSSFWorkbook对象(excel的文档对象)
XSSFWorkbook wb = new XSSFWorkbook();
System.out.println("+++++++++++++++++++++主表 start++++++++++++++++++++++++");
//建立新的sheet对象(excel的表单)
Sheet sheet = wb.createSheet();
//设置sheet名字
wb.setSheetName(0,"武汉市园林绿化建设市场诚信管理系统(试行)企业录入申请表");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
cell.setCellValue("武汉市园林绿化建设市场诚信管理系统(试行)企业录入申请表");
cell.setCellStyle(getCellStyleTitle(wb));
row.setHeightInPoints((short) (40));
Row row1 = sheet.createRow(1);
Cell cell1 = row1.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(1,1,0,3));
cell1.setCellValue("( )号");
cell1.setCellStyle(getCellStyleText(wb));
row1.setHeight((short)(400));
Row row2 = sheet.createRow(2);
Cell cell2 = row2.createCell(0);
cell2.setCellValue("企业名称(盖章)");
Cell cell3 = row2.createCell(1);
cell3.setCellValue(companyMes.getCompanyName());
Cell cell4 = row2.createCell(2);
cell4.setCellValue("统一社会\n" + "信用代码");
Cell cell5 = row2.createCell(3);
cell5.setCellValue(companyMes.getSocialCreditCode());
Row primaryTable2 = sheet.createRow(3);
primaryTable2.createCell(0).setCellValue("信用专职人员姓名");
primaryTable2.createCell(1).setCellValue(companyMes.getCreditProfessional());
primaryTable2.createCell(2).setCellValue("信用专职人员\n" +"手机号");
primaryTable2.createCell(3).setCellValue(companyMes.getProfessionalPhone());
Row row3 = sheet.createRow(4);
row3.createCell(0).setCellValue("申请类型\n" + "(只能\"√\"一项)");
row3.createCell(1);
row3.createCell(2).setCellValue("申请时间\n" + "(年/月/日)");
row3.createCell(3).setCellValue(formatDate(companyMes.getSubmitTime(),"yyyy/MM/dd"));
Row row4 = sheet.createRow(5);
row4.createCell(0).setCellValue("申请录入内容复述");
Cell cell6 = row4.createCell(1);
sheet.addMergedRegion(new CellRangeAddress(5,5,1,3));
System.out.println("+++++++++++++++++++++证明文件计算 start++++++++++++++++++++++++");
int picNumOne =0;
if ((first == 1 && companyMes.getVersionNum()==0) ||(first == 2 &&((companyMesDao.selectModify(companyId).size()>0)||isTypeDelete(companyId)))){
picNumOne =calculatePicture(companyMes.getCreditCodeUrl())+calculatePicture(companyMes.getAdreeUrl())+2;
}
int picNumTwo = 0;
List<CompanyQual> companyQualsByAdded = companyQualDao.selectAdded(companyId);
List<CompanyQual> companyQualsByModify = companyQualDao.selectModify(companyId);
List<faithCompanyQualHis> faithCompanyQualHisByDelete = faithCompanyQualHisDao.selectDelete(companyId);
if (first ==1 && companyQualsByAdded !=null){
for (CompanyQual companyQual:companyQualsByAdded) {
picNumTwo += calculatePicture(companyQual.getEnclosure());
}
}
if (first ==2 ){
if (companyQualsByModify !=null){
for (CompanyQual companyQual:companyQualsByModify) {
picNumTwo += calculatePicture(companyQual.getEnclosure());
}
}
if (faithCompanyQualHisByDelete != null){
for (faithCompanyQualHis f:faithCompanyQualHisByDelete) {
picNumTwo += calculatePicture(f.getEnclosure());
}
}
}
int picThree = 0;
List<CompanyManMes> companyManMesByAdded = companyManMesDao.selectAdded(companyId);
List<CompanyManMes> companyManMesByModify = companyManMesDao.selectModify(companyId);
List<CompanyMan> companyManByModify = companyManDao.selectModify(companyId);
List<faithCompanyManmesHis> faithCompanyManmesHisByDelete = faithCompanyManmesHisDao.selectDelete(companyId);
if (first ==1 && companyManMesByAdded !=null){
for (CompanyManMes companyManMes:companyManMesByAdded) {
picThree+= calculatePicture(companyManMes.getSocialSecurityCert())+2;
}
}
if (first ==2 ){
if (companyManMesByModify != null){
for (CompanyManMes companyManMes:companyManMesByModify) {
picThree+= calculatePicture(companyManMes.getSocialSecurityCert())+2;
}
}
if (companyManByModify != null){
for (CompanyMan companyMan:companyManByModify) {
picThree += 2;
}
}
if (faithCompanyManmesHisByDelete !=null){
for (faithCompanyManmesHis f:faithCompanyManmesHisByDelete) {
picThree+= calculatePicture(f.getSocialSecurityCert())+2;
}
}
}
int picFour = 0;
List<CompanyProject> companyProjectsByAdded = companyProjectDao.selectAdded(companyId);
List<CompanyProject> companyProjectsByModify = companyProjectDao.selectModify(companyId);
List<faithCompanyProjectHis> faithCompanyProjectHisByDelete = faithCompanyProjectHisDao.selectDelete(companyId);
if (first == 1 && companyProjectsByAdded !=null){
for (CompanyProject companyProject:companyProjectsByAdded) {
picFour+=calculatePicture(companyProject.getEnclosure());
}
}
if (first == 2){
if (companyProjectsByModify !=null){
for (CompanyProject companyProject:companyProjectsByModify) {
picFour+=calculatePicture(companyProject.getEnclosure());
}
}
if (faithCompanyProjectHisByDelete !=null){
for (faithCompanyProjectHis f:faithCompanyProjectHisByDelete) {
picFour+=calculatePicture(f.getEnclosure());
}
}
}
int picFives = 0;
List<Faithgoodinfo> faithgoodinfosByAdded = faithgoodinfoDao.selectAdded(companyId);
if (first ==3){
if (faithgoodinfosByAdded !=null){
for (Faithgoodinfo f:faithgoodinfosByAdded) {
picFives+=calculatePicture(f.getEnclosure());
}
}
}
System.out.println("+++++++++++++++++++++证明文件计算 end++++++++++++++++++++++++");
if (first != 3){
cell6.setCellValue("附表1证明文件("+picNumOne+")张\n" + "附表2证明文件("+picNumTwo+")张\n"
+ "附表3证明文件("+picThree+")张\n" + "附表4证明文件("+picFour+")张\n" + "附表5证明文件( )张");
}else {
cell6.setCellValue("附表1证明文件( )张\n" + "附表2证明文件( )张\n"
+ "附表3证明文件( )张\n" + "附表4证明文件( )张\n" + "附表5证明文件("+picFives+")张");
}
Row row5 = sheet.createRow(6);
row5.createCell(0).setCellValue("初审意见");
row5.createCell(1).setCellValue("\n\n\n" + "审核人: .\n" + "审定日期: .");
sheet.addMergedRegion(new CellRangeAddress(6,6,1,3));
Row row6 = sheet.createRow(7);
row6.createCell(0).setCellValue("复审意见");
row6.createCell(1).setCellValue("\n\n\n" + "审核人: .\n" + "审定日期: .");
sheet.addMergedRegion(new CellRangeAddress(7,7,1,3));
Row row7 = sheet.createRow(8);
row7.createCell(0).setCellValue("领导批示");
row7.createCell(1).setCellValue("\n \n \n " + "\n .");
sheet.addMergedRegion(new CellRangeAddress(8,8,1,3));
//给指定行列设置单元格式
styleMultipleCells(wb,sheet,2,8,0,3,getCellStyleText(wb));
CellStyle cellStyleCenter = getCellStyleText(wb);
cellStyleCenter.setAlignment(CellStyle.ALIGN_RIGHT);//水平居右
row5.getCell(1).setCellStyle(cellStyleCenter);
row6.getCell(1).setCellStyle(cellStyleCenter);
Row row8 = sheet.createRow(9);
row8.createCell(0);
CellRangeAddress cellRangeAddress = new CellRangeAddress(9, 9, 0, 3);
sheet.addMergedRegion(cellRangeAddress);
CellStyle cellStyle = getCellStyleText(wb);
cellStyle.setBorderBottom(CellStyle.BORDER_MEDIUM_DASHED);
row8.getCell(0).setCellStyle(cellStyle);
row8.createCell(1).setCellStyle(cellStyle);
row8.createCell(2).setCellStyle(cellStyle);
row8.createCell(3).setCellStyle(cellStyle);
Row row9 = sheet.createRow(10);
Cell cell7 = row9.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(10,10,0,3));
cell7.setCellValue("武汉市园林绿化建设市场诚信管理系统(试行)企业录入申请表");
cell7.setCellStyle(getCellStyleTitle(wb));
row9.setHeightInPoints((short) (40));
Row row10 = sheet.createRow(11);
Cell cell8 = row10.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(11,11,0,3));
cell8.setCellValue("( )号");
cell8.setCellStyle(getCellStyleReceiptTail(wb));
row10.setHeightInPoints((short) (20));
Row row11 = sheet.createRow(12);
Cell cell9 = row11.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(12,12,0,3));
cell9.setCellValue(companyMes.getCompanyName()+":");
cell9.setCellStyle(getCellStyleReceiptLookup(wb));
row11.setHeightInPoints((short) (20));
Row row12 = sheet.createRow(13);
Cell cell10 = row12.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(13,13,0,3));
cell10.setCellValue(" 今收到你单位武汉市园林绿化建设市场诚信管理系统(试行)企业录入申请表及相关附件资料," +
"我站将按照《武汉市园林绿化建设市场主体信用信息管理办法(试行)》及有关工作要求,办理信用信息录入的审核事宜。");
cell10.setCellStyle(getCellStyleReceiptCenter(wb));
row12.setHeightInPoints((short) (40));
Row row13 = sheet.createRow(14);
Cell cell11 = row13.createCell(2);
sheet.addMergedRegion(new CellRangeAddress(14,14,2,3));
cell11.setCellValue("武汉市园林绿化建设管理站");
cell11.setCellStyle(getCellStyleReceiptTail(wb));
row13.setHeightInPoints((short) (20));
Row row14 = sheet.createRow(15);
Cell cell12 = row14.createCell(2);
sheet.addMergedRegion(new CellRangeAddress(15,15,2,3));
cell12.setCellValue("______年___月___日");
cell12.setCellStyle(getCellStyleReceiptTail(wb));
row14.setHeightInPoints((short) (20));
//遍历合并单格格
//traverseMergeArea(sheet);
//设置合并单元格边框
mergeAreaBorders(2,5,1,sheet,wb);
//设置勾选
if (first==2){
row3.getCell(1).setCellValue(setStrCheck(wb,"□新增\n" + "R修改" ));
}else {
row3.getCell(1).setCellValue(setStrCheck(wb,"R新增\n" + "□修改" ));
}
//宽度自适应
for (int j=0;j<=4;j++){
sheet.autoSizeColumn(j, true);
}
System.out.println("+++++++++++++++++++++主表 end++++++++++++++++++++++++");
if (first!=3){
System.out.println("+++++++++++++++++++++附表1 start++++++++++++++++++++++++");
faithCompanyMesHis faithCompanyMesHis = faithCompanyMesHisDao.selectByPrimaryNew(companyId);
//建立新的sheet对象(excel的表单)
Sheet sheet1 = wb.createSheet();
//设置sheet名字
wb.setSheetName(1,"附表1");
Row scheduleOneRow1 = sheet1.createRow(0);
Cell cell13 = scheduleOneRow1.createCell(0);
sheet1.addMergedRegion(new CellRangeAddress(0,0,0,3));
cell13.setCellValue("附表1");
cell13.setCellStyle(getCellStyleReceiptCenter(wb));
Row scheduleOneRow2 = sheet1.createRow(1);
Cell cell14 = scheduleOneRow2.createCell(0);
sheet1.addMergedRegion(new CellRangeAddress(1,1,0,3));
cell14.setCellValue("武汉市园林绿化建设市场诚信管理系统(试行)企业基本信息申报表");
cell14.setCellStyle(getCellStyleTitle(wb));
scheduleOneRow2.setHeightInPoints((short) (40));
Row scheduleOneRow3 = sheet1.createRow(2);
Cell cell15 = scheduleOneRow3.createCell(0);
sheet1.addMergedRegion(new CellRangeAddress(2,2,0,2));
cell15.setCellValue("填报单位(盖章):"+companyMes.getCompanyName());
Cell cell16 = scheduleOneRow3.createCell(3);
sheet1.addMergedRegion(new CellRangeAddress(2,2,3,3));
cell16.setCellValue("填报时间: "+formatDate(companyMes.getSubmitTime(),"yyyy年MM月dd日"));
cell15.setCellStyle(getCellStyleReportTheUnit(wb));
cell16.setCellStyle(getCellStyleReportTheUnit(wb));
scheduleOneRow3.setHeightInPoints(20);
Row scheduleOneRow4 = sheet1.createRow(3);
scheduleOneRow4.createCell(0).setCellValue("企业名称");
if (first==1 && companyMes.getVersionNum()==0){
scheduleOneRow4.createCell(1).setCellValue(companyMes.getCompanyName());
}else if (first==2 && compareStrings(companyMes.getCompanyName(),faithCompanyMesHis.getCompanyName())){
scheduleOneRow4.createCell(1).setCellValue(companyMes.getCompanyName());
}else {
scheduleOneRow4.createCell(1);
}
scheduleOneRow4.createCell(2).setCellValue("统一社会\n" + "信用代码");
if (first==1 && companyMes.getVersionNum()==0){
scheduleOneRow4.createCell(3).setCellValue(companyMes.getSocialCreditCode());
}else if (first==2 && compareStrings(companyMes.getSocialCreditCode(),faithCompanyMesHis.getSocialCreditCode())){
scheduleOneRow4.createCell(3).setCellValue(companyMes.getSocialCreditCode());
}else{
scheduleOneRow4.createCell(3);
}
Row scheduleOneRow5 = sheet1.createRow(4);
scheduleOneRow5.createCell(0).setCellValue("工商登记机关");
if (first==1 && companyMes.getVersionNum()==0){
scheduleOneRow5.createCell(1).setCellValue(companyMes.getBusinessRegistAuthor());
}else if (first==2 && compareStrings(companyMes.getBusinessRegistAuthor(),faithCompanyMesHis.getBusinessRegistAuthor())){
scheduleOneRow5.createCell(1).setCellValue(companyMes.getBusinessRegistAuthor());
}else {
scheduleOneRow5.createCell(1);
}
scheduleOneRow5.createCell(2).setCellValue("成立时间");
if (first==1 && companyMes.getVersionNum()==0){
scheduleOneRow5.createCell(3).setCellValue(formatDate(companyMes.getFoundingTime(),"yyyy年MM月dd日"));
}else if (first==2 && compareStrings(formatDate(companyMes.getFoundingTime(), "yyyy年MM月dd日"),
formatDate(faithCompanyMesHis.getFoundingTime(),"yyyy年MM月dd日"))){
}else {
scheduleOneRow5.createCell(3);
}
scheduleOneRow5.setHeight((short) 400);
Row scheduleOneRow6 = sheet1.createRow(5);
scheduleOneRow6.createCell(0).setCellValue("企业地址");
if (first==1 && companyMes.getVersionNum()==0){
scheduleOneRow6.createCell(1).setCellValue(companyMes.getRegisteredAddress());
}else if (first==2 && compareStrings(companyMes.getRegisteredAddress(),faithCompanyMesHis.getRegisteredAddress())){
scheduleOneRow6.createCell(1).setCellValue(companyMes.getRegisteredAddress());
}else {
scheduleOneRow6.createCell(1);
}
scheduleOneRow6.createCell(2).setCellValue("是否有本市\n" + "固定办公地址");
String string ="";
if (first==1 && companyMes.getVersionNum()==0){
if (companyMes.getIswuhanCompany().trim().equals("1")) {
string="R是 □否";
}else {
string="□是 R否";
}
}else if (first==2 && compareStrings(companyMes.getIswuhanCompany(),faithCompanyMesHis.getIswuhanCompany())){
if (companyMes.getIswuhanCompany().trim().equals("1")) {
string="R是 □否";
}else {
string="□是 R否";
}
}else {
string="□是 □否";
}
scheduleOneRow6.createCell(3).setCellValue(setStrCheck(wb,string));
Row scheduleOneRow7 = sheet1.createRow(6);
if (first == 1){
scheduleOneRow7.createCell(0).setCellValue("企业类别");
}else {
scheduleOneRow7.createCell(0).setCellValue("企业类别(删除)");
}
Cell cell17 = scheduleOneRow7.createCell(1);
sheet1.addMergedRegion(new CellRangeAddress(6,6,1,3));
if (first == 1){
cell17.setCellValue(setStrCheck(wb,businessCategory(companyId,true)));
}else if (first == 2){
cell17.setCellValue(setStrCheck(wb,businessCategory(companyId,false)));
}
Row scheduleOneRow8 = sheet1.createRow(7);
scheduleOneRow8.createCell(0).setCellValue("经营范围");
Cell cell18 = scheduleOneRow8.createCell(1);
sheet1.addMergedRegion(new CellRangeAddress(7,7,1,3));
if (first==1 && companyMes.getVersionNum()==0){
cell18.setCellValue(stringWrap(companyMes.getBusinessScope(),50));
}else if (first==2 && compareStrings(companyMes.getBusinessScope(),faithCompanyMesHis.getBusinessScope())){
cell18.setCellValue(stringWrap(companyMes.getBusinessScope(),50));
}
faithCompanyPerHis faithCompanyPerHis = faithCompanyPerHisDao.selectByPrimaryNew(companyId);
Row scheduleOneRow9 = sheet1.createRow(8);
scheduleOneRow9.createCell(0).setCellValue("企业法人姓名");
CompanyPer companyPer = companyPerDao.selecetByCode(companyId);
if (first==1 && companyMes.getVersionNum()==0){
scheduleOneRow9.createCell(1).setCellValue(companyPer.getName());
}else if (first==2 && compareStrings(companyPer.getName(),faithCompanyPerHis.getName())){
scheduleOneRow9.createCell(1).setCellValue(companyPer.getName());
}else{
scheduleOneRow9.createCell(1);
}
scheduleOneRow9.createCell(2).setCellValue("性别");
if (first==1 && companyMes.getVersionNum()==0){
scheduleOneRow9.createCell(3).setCellValue(companyPer.getSex());
}else if (first==2 && compareStrings(companyPer.getSex(),faithCompanyPerHis.getSex())){
scheduleOneRow9.createCell(3).setCellValue(companyPer.getSex());
}else {
scheduleOneRow9.createCell(3);
}
Row scheduleOneRow10 = sheet1.createRow(9);
scheduleOneRow10.createCell(0).setCellValue("出生年月");
if (first==1 && companyMes.getVersionNum()==0){
scheduleOneRow10.createCell(1).setCellValue(formatDate(companyPer.getBirthday(),"yyyy年MM月dd日"));
}else if (first==2 && compareStrings(formatDate(companyPer.getBirthday(),"yyyy年MM月dd日"),
formatDate(faithCompanyPerHis.getBirthday(),"yyyy年MM月dd日"))){
scheduleOneRow10.createCell(1).setCellValue(formatDate(companyPer.getBirthday(),"yyyy年MM月dd日"));
}else {
scheduleOneRow10.createCell(1);
}
scheduleOneRow10.createCell(2).setCellValue("最高学历");
if (first==1 && companyMes.getVersionNum()==0) {
scheduleOneRow10.createCell(3).setCellValue(companyPer.getHighEducat());
}else if (first==2 && compareStrings(companyPer.getHighEducat(),faithCompanyPerHis.getHighEducat())){
scheduleOneRow10.createCell(3).setCellValue(companyPer.getHighEducat());
}else {
scheduleOneRow10.createCell(3);
}
scheduleOneRow10.setHeight((short) 40);
Row scheduleOneRow11 = sheet1.createRow(10);
scheduleOneRow11.createCell(0).setCellValue("信用专职人员姓名");
if (first==1 && companyMes.getVersionNum()==0){
scheduleOneRow11.createCell(1).setCellValue(companyMes.getCreditProfessional());
}else if (first==2 && compareStrings(companyMes.getCreditProfessional(),faithCompanyMesHis.getCreditProfessional())){
scheduleOneRow11.createCell(1).setCellValue(companyMes.getCreditProfessional());
}else{
scheduleOneRow11.createCell(1);
}
scheduleOneRow11.createCell(2).setCellValue("信用专职人员\n" + "手机号");
if (first==1 && companyMes.getVersionNum()==0){
scheduleOneRow11.createCell(3).setCellValue(companyMes.getProfessionalPhone());
}else if (first==2 && compareStrings(companyMes.getProfessionalPhone().toString(),faithCompanyMesHis.getProfessionalPhone().toString())){
scheduleOneRow11.createCell(3).setCellValue(companyMes.getProfessionalPhone());
}else{
scheduleOneRow11.createCell(3);
}
//给指定行列设置单元格式
styleMultipleCells(wb,sheet1,3,10,0,3,getCellStyleText(wb));
scheduleOneRow5.setHeightInPoints((short) 40);
scheduleOneRow7.setHeightInPoints((short) 40);
scheduleOneRow9.setHeightInPoints((short) 40);
scheduleOneRow10.setHeightInPoints((short) 40);
//遍历合并单格格
traverseMergeArea(sheet1);
//设置合并单元格边框
mergeAreaBorders(4,5,1,sheet1,wb);
Row scheduleOneRow12 = sheet1.createRow(11);
Cell cell19 = scheduleOneRow12.createCell(0);
sheet1.addMergedRegion(new CellRangeAddress(11,11,0,3));
cell19.setCellValue("说明:\n" +
"1、填报本市办公地址,需提供企业购房或租房的相关证明文件(核查原件,收复印件1份);\n" +
"2、填报企业其它基本信息,需提供企业营收执照(核查原件,收复印件1份);\n" +
"3、填报人员信息,需提供被填报人身份证,填报或修改企业法人还需提供学历证书(核查原件,收复印件1份);\n" +
"4、含“□”项为勾选项,选择直接在“□”内打“√”,企业类别根据企业经营实际可多选");
cell19.setCellStyle(getCellStyleReceiptCenter(wb));
scheduleOneRow12.setHeightInPoints((short) 80);
//宽度自适应
for (int j=0;j<=4;j++){
sheet1.autoSizeColumn(j, true);
}
//设置高度
System.out.println("+++++++++++++++++++++附表1 end++++++++++++++++++++++++");
System.out.println("+++++++++++++++++++++附表2 start++++++++++++++++++++++++");
//建立新的sheet对象(excel的表单)
Sheet sheet2 = wb.createSheet();
//设置sheet名字
wb.setSheetName(2,"附表2");
Row scheduleTwoRow1 = sheet2.createRow(0);
Cell cell20 = scheduleTwoRow1.createCell(0);
sheet2.addMergedRegion(new CellRangeAddress(0,0,0,6));
cell20.setCellValue("附表2");
cell20.setCellStyle(getCellStyleReceiptCenter(wb));
Row scheduleTwoRow2 = sheet2.createRow(1);
Cell cell21 = scheduleTwoRow2.createCell(0);
sheet2.addMergedRegion(new CellRangeAddress(1,1,0,6));
cell21.setCellValue("企业资质信息表");
cell21.setCellStyle(getCellStyleTitle(wb));
scheduleTwoRow2.setHeightInPoints(40);
Row scheduleTwoRow3 = sheet2.createRow(2);
Cell cell22 = scheduleTwoRow3.createCell(0);
sheet2.addMergedRegion(new CellRangeAddress(2,2,0,4));
cell22.setCellValue("填报单位(盖章):"+companyMes.getCompanyName());
Cell cell23 = scheduleTwoRow3.createCell(5);
sheet2.addMergedRegion(new CellRangeAddress(2,2,5,6));
cell23.setCellValue("填报时间: "+formatDate(companyMes.getSubmitTime(),"yyyy年MM月dd日"));
cell22.setCellStyle(getCellStyleReportTheUnit(wb));
cell23.setCellStyle(getCellStyleReportTheUnit(wb));
scheduleTwoRow3.setHeightInPoints(20);
Row scheduleTwoRow4 = sheet2.createRow(3);
scheduleTwoRow4.createCell(0).setCellValue("序号");
scheduleTwoRow4.createCell(1).setCellValue("资质类别");
scheduleTwoRow4.createCell(2).setCellValue("资质等级");
scheduleTwoRow4.createCell(3).setCellValue("证书编号");
scheduleTwoRow4.createCell(4).setCellValue("核发机关");
scheduleTwoRow4.createCell(5).setCellValue("授予时间\n" + "(年/月/日)");
scheduleTwoRow4.createCell(6).setCellValue("资质有效\n" + "截止时间\n" + "(年/月/日)");
scheduleTwoRow4.setHeightInPoints((short)60);
//给指定行列设置单元格式
styleMultipleCells(wb,sheet2,3,3,0,6,getCellStyleTextTitle(wb));
int rowi = 3;
int rowNum = 0;
if (first ==1 && companyQualsByAdded!=null){
traverseCompanyQuals(companyQualsByAdded, sheet2, rowi);
rowNum = companyQualsByAdded.size();
styleMultipleCells(wb,sheet2,4,3+rowNum,0,6,getCellStyleText(wb));
}
if (first!=1){
if (companyQualsByModify!=null){
traverseCompanyQuals(companyQualsByModify, sheet2, rowi);
styleMultipleCells(wb,sheet2,4,3+companyQualsByModify.size(),0,6,getCellStyleText(wb));
}
if (faithCompanyQualHisByDelete!=null){
for (faithCompanyQualHis companyQualHis: faithCompanyQualHisByDelete) {
Row scheduleTwoRow51 = sheet2.createRow(rowi+companyQualsByModify.size()+ 1);
scheduleTwoRow51.createCell(0).setCellValue(rowi+companyQualsByModify.size()-2);
scheduleTwoRow51.createCell(1).setCellValue(companyQualHis.getQualType());
scheduleTwoRow51.createCell(2).setCellValue(companyQualHis.getQualLevel());
scheduleTwoRow51.createCell(3).setCellValue(companyQualHis.getCertificateNum());
scheduleTwoRow51.createCell(4).setCellValue(companyQualHis.getIssuingOrgan());
scheduleTwoRow51.createCell(5).setCellValue(formatDate(companyQualHis.getGrantDate(),"yyyy/MM/dd"));
scheduleTwoRow51.createCell(6).setCellValue(formatDate(companyQualHis.getValidityTime(),"yyyy/MM/dd"));
rowi++;
}
styleMultipleCells(wb,sheet2,4+companyQualsByModify.size(),3+companyQualsByModify.size()+faithCompanyQualHisByDelete.size(),0,6,getCellStyleTextDelete(wb));
}
rowNum = companyQualsByModify.size()+faithCompanyQualHisByDelete.size();
}
Row scheduleTwoRow6 = sheet2.createRow(rowNum+4);
Cell cell24 = scheduleTwoRow6.createCell(0);
sheet2.addMergedRegion(new CellRangeAddress(rowNum+4,rowNum+4,0,6));
cell24.setCellValue("说明:\n" +
"1、填报需提供资质证书(核查原条件,收复印件1份)。");
cell24.setCellStyle(getCellStyleReceiptCenter(wb));
scheduleTwoRow6.setHeightInPoints(40);
//宽度自适应
for (int j=0;j<=6;j++){
sheet2.autoSizeColumn(j, true);
}
System.out.println("+++++++++++++++++++++附表2 end++++++++++++++++++++++++");
System.out.println("+++++++++++++++++++++附表3 start++++++++++++++++++++++++");
//建立新的sheet对象(excel的表单)
Sheet sheet3 = wb.createSheet();
//设置sheet名字
wb.setSheetName(3,"附表3");
Row scheduleThreeRow1 = sheet3.createRow(0);
Cell cell25 = scheduleThreeRow1.createCell(0);
sheet3.addMergedRegion(new CellRangeAddress(0,0,0,6));
cell25.setCellValue("附表3");
cell25.setCellStyle(getCellStyleReceiptCenter(wb));
Row scheduleThreeRow2 = sheet3.createRow(1);
Cell cell26 = scheduleThreeRow2.createCell(0);
sheet3.addMergedRegion(new CellRangeAddress(1,1,0,6));
cell26.setCellValue("企业技术人员信息表");
cell26.setCellStyle(getCellStyleTitle(wb));
scheduleThreeRow2.setHeightInPoints(40);
Row scheduleThreeRow3 = sheet3.createRow(2);
Cell cell27 = scheduleThreeRow3.createCell(0);
sheet3.addMergedRegion(new CellRangeAddress(2,2,0,4));
cell27.setCellValue("填报单位(盖章):"+companyMes.getCompanyName());
Cell cell28 = scheduleThreeRow3.createCell(5);
sheet3.addMergedRegion(new CellRangeAddress(2,2,5,6));
cell28.setCellValue("填报时间: "+formatDate(companyMes.getSubmitTime(),"yyyy年MM月dd日"));
cell27.setCellStyle(getCellStyleReportTheUnit(wb));
cell28.setCellStyle(getCellStyleReportTheUnit(wb));
scheduleThreeRow3.setHeightInPoints(20);
Row scheduleThreeRow4 = sheet3.createRow(3);
scheduleThreeRow4.createCell(0).setCellValue("序号");
scheduleThreeRow4.createCell(1).setCellValue("姓名");
scheduleThreeRow4.createCell(2).setCellValue("性别");
scheduleThreeRow4.createCell(3).setCellValue("身份证号");
scheduleThreeRow4.createCell(4).setCellValue("工作类型");
scheduleThreeRow4.createCell(5).setCellValue("职称/执业类别名称");
scheduleThreeRow4.createCell(6).setCellValue("等级");
//给指定行列设置单元格式
styleMultipleCells(wb,sheet3,3,3,0,6,getCellStyleTextTitle(wb));
scheduleThreeRow4.setHeightInPoints(60);
rowi =3;
rowNum = 0;
if (first == 1 && companyManMesByAdded != null){
traverseCompanyManMes(sheet3,companyManMesByAdded,rowi);
rowNum = companyManMesByAdded.size();
styleMultipleCells(wb,sheet3,4,3+rowNum,0,6,getCellStyleText(wb));
}
if (first!=1){
if (companyManMesByModify != null){
traverseCompanyManMes(sheet3,companyManMesByModify,rowi);
rowNum = companyManMesByModify.size();
styleMultipleCells(wb,sheet3,4,3+rowNum,0,6,getCellStyleText(wb));
}
if(companyManByModify != null){
for (CompanyMan companyMan:companyManByModify) {
Row scheduleThreeRow52 = sheet3.createRow(rowi+companyManMesByModify.size()+ 1);
scheduleThreeRow52.createCell(0).setCellValue(rowi+companyManMesByModify.size()-2);
scheduleThreeRow52.createCell(1).setCellValue(companyMan.getName());
scheduleThreeRow52.createCell(2).setCellValue(companyMan.getSex());
scheduleThreeRow52.createCell(3).setCellValue(companyMan.getIdNumber());
scheduleThreeRow52.createCell(4);
scheduleThreeRow52.createCell(5);
scheduleThreeRow52.createCell(6);
rowNum++;
}
styleMultipleCells(wb,sheet3,4+companyManMesByModify.size(),3+rowNum,0,6,getCellStyleText(wb));
}
if (faithCompanyManmesHisByDelete != null){
for (faithCompanyManmesHis companyManmesHis:faithCompanyManmesHisByDelete) {
CompanyMan companyMan = companyManDao.selectByPrimaryKey(companyManmesHis.getManId());
Row scheduleThreeRow51 = sheet3.createRow(rowi+companyManMesByModify.size()+companyManByModify.size()+ 1);
scheduleThreeRow51.createCell(0).setCellValue(rowi+companyManMesByModify.size()-2);
scheduleThreeRow51.createCell(1).setCellValue(companyMan.getName());
scheduleThreeRow51.createCell(2).setCellValue(companyMan.getSex());
scheduleThreeRow51.createCell(3).setCellValue(companyMan.getIdNumber());
scheduleThreeRow51.createCell(4).setCellValue(companyManmesHis.getCompanyType());
scheduleThreeRow51.createCell(5).setCellValue(companyManmesHis.getOccupation());
scheduleThreeRow51.createCell(6).setCellValue(certificateLevel(companyManmesHis.getOccupationLevel()));
rowi++;
}
rowNum = companyManMesByModify.size()+faithCompanyManmesHisByDelete.size()+companyManByModify.size();
styleMultipleCells(wb,sheet3,4+companyManMesByModify.size()+companyManByModify.size(), 3+rowNum,0,6,getCellStyleTextDelete(wb));
}
}
Row scheduleThreeRow6 = sheet3.createRow(rowNum+4);
Cell cell29 = scheduleThreeRow6.createCell(0);
sheet3.addMergedRegion(new CellRangeAddress(rowNum+4,rowNum+4,0,6));
cell29.setCellValue("说明:\n" +
"1、工作类型包括施工、设计、监理、勘察、招标代理、造价咨询、检测和建设单位。\n" +
"2、每条信息只能选填一项工作类型和一个职称(执业)类别。\n" +
"3、需提供被填报人员的身份证、职称(执业)证、近三个月社保缴费证明(核查原件,收复印件1份)。");
cell29.setCellStyle(getCellStyleReceiptCenter(wb));
scheduleThreeRow6.setHeightInPoints(80);
//宽度自适应
for (int j=0;j<=6;j++){
sheet3.autoSizeColumn(j, true);
}
System.out.println("+++++++++++++++++++++附表3 end++++++++++++++++++++++++");
System.out.println("+++++++++++++++++++++附表4 start++++++++++++++++++++++++");
//建立新的sheet对象(excel的表单)
Sheet sheet4 = wb.createSheet();
//设置sheet名字
wb.setSheetName(4,"附表4");
Row scheduleFourRow1 = sheet4.createRow(0);
Cell cell30 = scheduleFourRow1.createCell(0);
sheet4.addMergedRegion(new CellRangeAddress(0,0,0,8));
cell30.setCellValue("附表4");
cell30.setCellStyle(getCellStyleReceiptCenter(wb));
Row scheduleFourRow2 = sheet4.createRow(1);
Cell cell31 = scheduleFourRow2.createCell(0);
sheet4.addMergedRegion(new CellRangeAddress(1,1,0,8));
cell31.setCellValue("企业本市业绩表");
cell31.setCellStyle(getCellStyleTitle(wb));
scheduleFourRow2.setHeightInPoints(40);
Row scheduleFourRow3 = sheet4.createRow(2);
Cell cell32 = scheduleFourRow3.createCell(0);
sheet4.addMergedRegion(new CellRangeAddress(2,2,0,7));
cell32.setCellValue("填报单位(盖章):"+companyMes.getCompanyName());
Cell cell33 = scheduleFourRow3.createCell(8);
cell33.setCellValue("填报时间: "+formatDate(companyMes.getSubmitTime(),"yyyy年MM月dd日"));
cell32.setCellStyle(getCellStyleReportTheUnit(wb));
cell33.setCellStyle(getCellStyleReportTheUnit(wb));
scheduleFourRow3.setHeightInPoints(20);
Row scheduleFourRow4 = sheet4.createRow(3);
scheduleFourRow4.createCell(0).setCellValue("序号");
scheduleFourRow4.createCell(1).setCellValue("工程名称");
scheduleFourRow4.createCell(2).setCellValue("工程类别");
scheduleFourRow4.createCell(3).setCellValue("竣工时间\n" + "(年/月)");
scheduleFourRow4.createCell(4).setCellValue("绿化面积\n" + "(平方米)");
scheduleFourRow4.createCell(5).setCellValue("结算金额\n" + "(万元)");
scheduleFourRow4.createCell(6).setCellValue("区属/投资占比");
scheduleFourRow4.createCell(7).setCellValue("项目负责人");
scheduleFourRow4.createCell(8).setCellValue("工程详情");
//给指定行列设置单元格式
styleMultipleCells(wb,sheet4,3,3,0,8,getCellStyleTextTitle(wb));
scheduleFourRow4.setHeightInPoints(60);
rowi =3;
rowNum = 0;
if (first == 1 && companyProjectsByAdded !=null){
traverseCompanyProjects(sheet4, companyProjectsByAdded, rowi);
rowNum = companyProjectsByAdded.size();
styleMultipleCells(wb,sheet4,4,3+rowNum,0,8,getCellStyleText(wb));
}
if (first!=1){
if (companyProjectsByModify!=null){
traverseCompanyProjects(sheet4, companyProjectsByModify, rowi);
styleMultipleCells(wb,sheet4,4,3+companyProjectsByModify.size(),0,8,getCellStyleText(wb));
}
if (faithCompanyProjectHisByDelete!=null){
for (faithCompanyProjectHis companyProjectHis:faithCompanyProjectHisByDelete) {
Row scheduleFourRow51 = sheet4.createRow(rowi+companyProjectsByModify.size()+ 1);
scheduleFourRow51.createCell(0).setCellValue(rowi+companyProjectsByModify.size()-2);
scheduleFourRow51.createCell(1).setCellValue(companyProjectHis.getProjectName());
scheduleFourRow51.createCell(2).setCellValue(companyProjectHis.getTypeName());
scheduleFourRow51.createCell(3).setCellValue(formatDate(companyProjectHis.getCompletedTime(),"yyyy年MM月dd日"));
scheduleFourRow51.createCell(4).setCellValue(companyProjectHis.getGreenSpace());
scheduleFourRow51.createCell(5).setCellValue(companyProjectHis.getSettleAmount());
scheduleFourRow51.createCell(6).setCellValue(companyProjectHis.getInvestment());
scheduleFourRow51.createCell(7).setCellValue(companyProjectHis.getPrincipal());
scheduleFourRow51.createCell(8).setCellValue(stringWrap(companyProjectHis.getProjectDetails(),50));
rowi++;
}
}
styleMultipleCells(wb,sheet4,4+companyProjectsByModify.size(),3+companyProjectsByModify.size()+faithCompanyProjectHisByDelete.size(),0,8,getCellStyleTextDelete(wb));
rowNum = companyProjectsByModify.size()+companyProjectsByModify.size();
}
Row scheduleFourRow6 = sheet4.createRow(rowNum+4);
Cell cell34 = scheduleFourRow6.createCell(0);
sheet4.addMergedRegion(new CellRangeAddress(rowNum+4,rowNum+4,0,8));
cell34.setCellValue("说明:\n" +
"1、本市业绩表每一条为一个项目的一个工程类别的信息。\n" +
"2、工作类型包括施工、设计、监理、勘察、招标代理、造价咨询、检测和建设单位。\n" +
"3、投标项目需提供项目的竣工验收备案表、中标通知书、工程合同,其他类型项目提供竣工验收表、工程合同(核查原件,收复印件1份)。\n" +
"4、投资占比为估算数值,填报百分值,各区投资占比百分之各应为100%。");
cell34.setCellStyle(getCellStyleReceiptCenter(wb));
scheduleFourRow6.setHeightInPoints(80);
//宽度自适应
for (int j=0;j<=8;j++){
sheet4.autoSizeColumn(j, true);
}
System.out.println("+++++++++++++++++++++附表4 end++++++++++++++++++++++++");
}
if (first==3){
System.out.println("+++++++++++++++++++++附表5 start++++++++++++++++++++++++");
//建立新的sheet对象(excel的表单)
Sheet sheet5 = wb.createSheet();
//设置sheet名字
wb.setSheetName(1,"附表5");
Row scheduleFivesRow1 = sheet5.createRow(0);
Cell cell35 = scheduleFivesRow1.createCell(0);
sheet5.addMergedRegion(new CellRangeAddress(0,0,0,9));
cell35.setCellValue("附表5");
cell35.setCellStyle(getCellStyleReceiptCenter(wb));
Row scheduleFivesRow2 = sheet5.createRow(1);
Cell cell36 = scheduleFivesRow2.createCell(0);
sheet5.addMergedRegion(new CellRangeAddress(1,1,0,9));
cell36.setCellValue("良好信息申报表");
cell36.setCellStyle(getCellStyleTitle(wb));
scheduleFivesRow2.setHeightInPoints(40);
Row scheduleFivesRow3 = sheet5.createRow(2);
Cell cell37 = scheduleFivesRow3.createCell(0);
sheet5.addMergedRegion(new CellRangeAddress(2,2,0,8));
cell37.setCellValue("填报单位(盖章):"+companyMes.getCompanyName());
Cell cell38 = scheduleFivesRow3.createCell(9);
cell38.setCellValue("填报时间: "+formatDate(companyMes.getSubmitTime(),"yyyy年MM月dd日"));
cell37.setCellStyle(getCellStyleReportTheUnit(wb));
cell38.setCellStyle(getCellStyleReportTheUnit(wb));
scheduleFivesRow3.setHeightInPoints(20);
Row scheduleFivesRow4 = sheet5.createRow(3);
scheduleFivesRow4.createCell(0).setCellValue("序号");
scheduleFivesRow4.createCell(1).setCellValue("加分内容");
scheduleFivesRow4.createCell(2).setCellValue("企业类别");
scheduleFivesRow4.createCell(3).setCellValue("主建或参建");
scheduleFivesRow4.createCell(4).setCellValue("加分依据");
scheduleFivesRow4.createCell(5).setCellValue("公布超始时间\n" +"(年/月/日)");
scheduleFivesRow4.createCell(6).setCellValue("公布结束时间\n" +"(年/月/日)");
scheduleFivesRow4.createCell(7).setCellValue("公布周期(年)");
scheduleFivesRow4.createCell(8).setCellValue("分值(分)");
scheduleFivesRow4.createCell(9).setCellValue("加分内容详述");
//给指定行列设置单元格式
styleMultipleCells(wb,sheet5,3,3,0,9,getCellStyleTextTitle(wb));
scheduleFivesRow4.setHeightInPoints(60);
int rowi = 3;
int rowNum = 0;
if (faithgoodinfosByAdded !=null){
for (Faithgoodinfo faithgoodinfo:faithgoodinfosByAdded) {
Row scheduleFivesRow5 = sheet5.createRow(rowi+1);
scheduleFivesRow5.createCell(0).setCellValue(rowi-2);
scheduleFivesRow5.createCell(1).setCellValue(stringWrap(faithgoodinfo.getProjectName(),50));
scheduleFivesRow5.createCell(2).setCellValue(faithgoodinfo.getProjectType());
Faithscoredict faithscoredict = faithscoredictDao.selectByPrimaryKey(faithgoodinfo.getScoreId());
if (Double.parseDouble(faithscoredict.getValue())/faithgoodinfo.getScoreValue()==2){
scheduleFivesRow5.createCell(3).setCellValue("参建");
}else {
scheduleFivesRow5.createCell(3).setCellValue("主建");
}
scheduleFivesRow5.createCell(4).setCellValue(faithgoodinfo.getApplicatMatter());
scheduleFivesRow5.createCell(5).setCellValue(formatDate(faithgoodinfo.getPromptStart(),"yyyy/MM/dd"));
scheduleFivesRow5.createCell(6).setCellValue(formatDate(faithgoodinfo.getPromptEnd(),"yyyy/MM/dd"));
scheduleFivesRow5.createCell(7).setCellValue(faithgoodinfo.getBonusPeriod());
scheduleFivesRow5.createCell(8).setCellValue(faithgoodinfo.getScoreValue());
scheduleFivesRow5.createCell(9).setCellValue(stringWrap(faithgoodinfo.getContent(),50));
rowi++;
}
rowNum = faithgoodinfosByAdded.size();
styleMultipleCells(wb,sheet5,4,3+rowNum,0,9,getCellStyleText(wb));
}
Row scheduleFivesRow6 = sheet5.createRow(rowNum+4);
Cell cell39 = scheduleFivesRow6.createCell(0);
sheet5.addMergedRegion(new CellRangeAddress(rowNum+4,rowNum+4,0,9));
cell39.setCellValue("说明:\n" +
"1、良好信息申报表每条为一个加分项。\n" +
"2、企业类别包括施工、设计、监理、勘察、招标代理、造价咨询、检测和建设单位。\n" +
"3、需提供良好信息证明的文件,奖励类除提供奖状外,还需提供证明奖状真实性的颁奖文件或颁奖新闻信息(核查原件,收复印件1份)。");
cell39.setCellStyle(getCellStyleReceiptCenter(wb));
scheduleFivesRow6.setHeightInPoints(80);
//宽度自适应
for (int j=0;j<=9;j++){
sheet5.autoSizeColumn(j, true);
}
System.out.println("+++++++++++++++++++++附表5 end++++++++++++++++++++++++");
}
wb.write(outputStream);
outputStream.flush();
outputStream.close();
System.out.println("+++++++++++++++++++++企业录入申请表 end++++++++++++++++++++++++");
} catch (Exception e) {
e.printStackTrace();
}
}
private void traverseCompanyProjects(Sheet sheet4, List<CompanyProject> companyProjects, int rowi) {
for (CompanyProject companyProject: companyProjects) {
Row scheduleFourRow5 = sheet4.createRow(rowi+1);
scheduleFourRow5.createCell(0).setCellValue(rowi-2);
scheduleFourRow5.createCell(1).setCellValue(companyProject.getProjectName());
scheduleFourRow5.createCell(2).setCellValue(companyProject.getTypeName());
scheduleFourRow5.createCell(3).setCellValue(formatDate(companyProject.getCompletedTime(),"yyyy年MM月dd日"));
scheduleFourRow5.createCell(4).setCellValue(companyProject.getGreenSpace().toString());
scheduleFourRow5.createCell(5).setCellValue(companyProject.getSettleAmount().toString());
scheduleFourRow5.createCell(6).setCellValue(companyProject.getInvestment());
scheduleFourRow5.createCell(7).setCellValue(companyProject.getPrincipal());
scheduleFourRow5.createCell(8).setCellValue(stringWrap(companyProject.getProjectDetails(),50));
rowi++;
}
}
private void traverseCompanyManMes(Sheet sheet, List<CompanyManMes> CompanyManMes, int rowi) {
for (CompanyManMes companyManMes: CompanyManMes) {
CompanyMan companyMan = companyManDao.selectByPrimaryKey(companyManMes.getManId());
Row scheduleThreeRow5 = sheet.createRow(rowi+1);
scheduleThreeRow5.createCell(0).setCellValue(rowi-2);
scheduleThreeRow5.createCell(1).setCellValue(companyMan.getName());
scheduleThreeRow5.createCell(2).setCellValue(companyMan.getSex());
scheduleThreeRow5.createCell(3).setCellValue(companyMan.getIdNumber());
scheduleThreeRow5.createCell(4).setCellValue(companyManMes.getCompanyType());
scheduleThreeRow5.createCell(5).setCellValue(companyManMes.getOccupation());
scheduleThreeRow5.createCell(6).setCellValue(certificateLevel(companyManMes.getOccupationLevel()));
rowi++;
}
}
private void traverseCompanyQuals(List<CompanyQual> companyQuals, Sheet sheet, int rowi) {
for (CompanyQual companyQual: companyQuals) {
Row scheduleTwoRow5 = sheet.createRow(rowi+1);
scheduleTwoRow5.createCell(0).setCellValue(rowi-2);
scheduleTwoRow5.createCell(1).setCellValue(companyQual.getQualType());
scheduleTwoRow5.createCell(2).setCellValue(companyQual.getQualLevel());
scheduleTwoRow5.createCell(3).setCellValue(companyQual.getCertificateNum());
scheduleTwoRow5.createCell(4).setCellValue(companyQual.getIssuingOrgan());
scheduleTwoRow5.createCell(5).setCellValue(formatDate(companyQual.getGrantDate(),"yyyy/MM/dd"));
scheduleTwoRow5.createCell(6).setCellValue(formatDate(companyQual.getValidityTime(),"yyyy/MM/dd"));
rowi++;
}
}
/**
* 技术人员证书分等级
* @param str
* @return
*/
private String certificateLevel(String str){
String string ="";
if (str.equals("1")) {
string = "高级";
}
if (str.equals("2")) {
string = "中级";
}
if (str.equals("3")) {
string = "初级";
}
if (str.equals("4")) {
string = "一级";
}
if (str.equals("5")) {
string = "二级";
}
if (str.equals("6")) {
string = "三级";
}
if (str.equals("7")) {
string = "未分级";
}
return string;
}
/**
* 计算图片个数
* @param str
* @return
*/
private int calculatePicture(String str){
int num =0;
if (str != null && !str.trim().equals("")){
String[] split = str.split(",");
for (String s:split) {
if (s != null && !str.trim().equals(""))
num++;
}
}
return num;
}
}
import com.alibaba.druid.sql.visitor.functions.Char;
import com.freecg.commons.base.BaseController;
import com.freecg.dao.company.*;
import com.freecg.dao.hiscompany.*;
import com.freecg.dao.manage.FaithbadinfoDao;
import com.freecg.dao.manage.FaithgoodinfoDao;
import com.freecg.dao.manage.FaithpromptDao;
import com.freecg.dao.scorerule.FaithpubinfoDao;
import com.freecg.dao.scorerule.FaithpubinfocateDao;
import com.freecg.dao.scorerule.FaithpubinfochildcateDao;
import com.freecg.dao.scorerule.FaithscoredictDao;
import com.freecg.dao.sys.DictDao;
import com.freecg.entity.company.*;
import com.freecg.entity.hiscompany.*;
import com.freecg.entity.manage.Faithgoodinfo;
import com.freecg.entity.scorerule.Faithscoredict;
import com.freecg.entity.sys.Dict;
import com.freecg.service.company.ComQualBackService;
import com.freecg.service.impl.company.FaithProjectService;
import com.freecg.service.manage.CompanyMesAudhisService;
import com.freecg.service.manage.CompanyQualAdService;
import com.freecg.service.manage.CompanyadminService;
import com.freecg.service.manage.FaithgoodinfoService;
import com.freecg.utils.sys.DateUtils;
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.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.mvc.support.RedirectAttributes;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* 企业录入申请表
*/
@Controller
@RequestMapping("applicationReport/admin")
public class ApplicationReportController extends BaseController{
/**
* 主表:武汉市园林绿化建设市场诚信管理系统(试行)企业录入申请表
* 附表1:武汉市园林绿化建设市场诚信管理系统(试行)企业基本信息申报表
* 附表2:企业资质信息表
* 附表3:企业技术人员信息表
* 附表4:企业本市业绩表
* 附表5:良好信息申报表
*
* 新增:企业录入申请表+附表1+附表2-附表4 第1报表
* 附表1显示全部 指新增指未通过复审前
* 修改:企业录入申请表+附表1+附表2-附表4 第2报表
* 附表1只显示修改的 指复审通过后
* 新增:企业录入申请表+附表5 第3报表
* 附表5只有新增的,且不包括系统审核,
* 如在本市有固定办公场所,园林绿化高级专业技术人员2人及以上,园林绿化中级及以上专业技术人员5人及以上
*/
@Autowired
private CompanyMesDao companyMesDao;
@Autowired
private CompanyPerDao companyPerDao;
@Autowired
private FaithCompanyManHisDao faithCompanyManHisDao;
@Autowired
private FaithCompanyMesHisDao faithCompanyMesHisDao;
@Autowired
private CompanyTypeDao companyTypeDao;
@Autowired
private FaithCompanyTypeHisDao faithCompanyTypeHisDao;
@Autowired
private DictDao DictDao;
@Autowired
private FaithCompanyPerHisDao faithCompanyPerHisDao;
@Autowired
private FaithCompanyQualHisDao faithCompanyQualHisDao;
@Autowired
private CompanyQualDao companyQualDao;
@Autowired
private FaithCompanyManmesHisDao faithCompanyManmesHisDao;
@Autowired
private CompanyManMesDao companyManMesDao;
@Autowired
private CompanyManDao companyManDao;
@Autowired
private CompanyProjectDao companyProjectDao;
@Autowired
private FaithCompanyProjectHisDao faithCompanyProjectHisDao;
@Autowired
private FaithgoodinfoDao faithgoodinfoDao;
@Autowired
private FaithscoredictDao faithscoredictDao;
/**
* 设置合并后的单元格边框样式
* @param border 宽度
* @param cellRangeAddress 合并后的单元格
* @param sheet
* @param wb
*/
private static void mergedCellBorder(int border, CellRangeAddress cellRangeAddress, Sheet sheet, XSSFWorkbook wb){
RegionUtil.setBorderBottom(border, cellRangeAddress, sheet,wb); // 下边框
RegionUtil.setBorderLeft(border, cellRangeAddress, sheet,wb); // 左边框
RegionUtil.setBorderRight(border, cellRangeAddress, sheet,wb); // 有边框
RegionUtil.setBorderTop(border, cellRangeAddress, sheet,wb); // 上边框
}
/**
* 设置标题样式
* @param wb
* @return
*/
private static CellStyle getCellStyleTitle(XSSFWorkbook wb){
//创建标题的单元格样式cellStyleTitle以及字体样式headerFontTitle
CellStyle cellStyleTitle = wb.createCellStyle();
Font fontTitle = wb.createFont();// 创建字体样式
cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
cellStyleTitle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
fontTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
fontTitle.setFontName("宋体"); // 设置字体类型
fontTitle.setFontHeightInPoints((short) 18); // 设置字体大小
cellStyleTitle.setFont(fontTitle); // 为标题样式设置字体样式
//cellStyleTitle.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);//虚线
return cellStyleTitle;
}
/**
* 设置正文样式
* @param wb
* @return
*/
private static CellStyle getCellStyleText(XSSFWorkbook wb){
//创建标题的单元格样式cellStyleTitle以及字体样式headerFontTitle
CellStyle cellStyle = wb.createCellStyle();
Font font = wb.createFont();// 创建字体样式
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
font.setFontName("宋体"); // 设置字体类型
font.setFontHeightInPoints((short) 14); // 设置字体大小
cellStyle.setFont(font); // 为标题样式设置字体样式
cellStyle.setWrapText(true); //文字超出宽,自增加宽度
return cellStyle;
}
/**
* 设置正文样式(删除线)
* @param wb
* @return
*/
private static CellStyle getCellStyleTextDelete(XSSFWorkbook wb){
//创建标题的单元格样式cellStyleTitle以及字体样式headerFontTitle
CellStyle cellStyle = wb.createCellStyle();
Font font = wb.createFont();// 创建字体样式
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
font.setFontName("宋体"); // 设置字体类型
font.setFontHeightInPoints((short) 14); // 设置字体大小
font.setStrikeout(true);
cellStyle.setFont(font); // 为标题样式设置字体样式
cellStyle.setWrapText(true); //文字超出宽,自增加宽度
return cellStyle;
}
private static CellStyle getCellStyleTextTitle(XSSFWorkbook wb){
CellStyle cellStyle = wb.createCellStyle();
Font fontTitle = wb.createFont();// 创建字体样式
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
((XSSFFont) fontTitle).setBold(true);
fontTitle.setFontName("宋体"); // 设置字体类型
fontTitle.setFontHeightInPoints((short) 14); // 设置字体大小
cellStyle.setFont(fontTitle); // 为标题样式设置字体样式
cellStyle.setWrapText(true); //文字超出宽,自增加宽度
return cellStyle;
}
/**
* 设置填报单位,填报时间样式
* @param wb
* @return
*/
private static CellStyle getCellStyleReportTheUnit(XSSFWorkbook wb){
CellStyle cellStyle = wb.createCellStyle();
Font fontTitle = wb.createFont();// 创建字体样式
cellStyle.setAlignment(CellStyle.ALIGN_LEFT);//水平居中
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
fontTitle.setFontName("宋体"); // 设置字体类型
fontTitle.setFontHeightInPoints((short) 14); // 设置字体大小
cellStyle.setFont(fontTitle); // 为标题样式设置字体样式
cellStyle.setWrapText(true); //文字超出宽,自增加宽度
return cellStyle;
}
/**
* 设置勾选(勾选字体需字体设为Wingdings 2,R才可变成勾选)
* @param wb
* @param str
* @return
*/
private static XSSFRichTextString setStrCheck(XSSFWorkbook wb,String str){
XSSFRichTextString richString = new XSSFRichTextString(str);
//勾选字体
Font selectFont = wb.createFont();
//selectFont.setColor(HSSFColor.RED.index);// 红色
selectFont.setFontName("Wingdings 2");
//未勾选字体
Font otherFonts = wb.createFont();
otherFonts.setFontName("宋体");
//otherFonts.setColor(HSSFColor.BLUE.index);// 蓝色
System.out.println("str.length()="+str.length());
int endIndex;
for (int i = 0;i < str.length();i++){
System.out.println("str.charAt(i)="+str.charAt(i));
if ((str.charAt(i)+"").equals("R")){
richString.applyFont(i,i+1,selectFont);
}else{
richString.applyFont(i,i+1,otherFonts);
}
}
return richString;
}
/**
* 回执抬头样式
* @param wb
* @return
*/
private static CellStyle getCellStyleReceiptLookup(XSSFWorkbook wb){
CellStyle cellStyle = getCellStyleReceiptTail(wb);
Font font = wb.createFont();// 创建字体样式
cellStyle.setFont(font);
cellStyle.setAlignment(CellStyle.ALIGN_LEFT); //左对齐
font.setFontName(HSSFFont.FONT_ARIAL); //设置有下划线的字体
font.setUnderline((byte)1); //下线线
cellStyle.setFont(font);
return cellStyle;
}
/**
* 回执正文样式
* @param wb
* @return
*/
private static CellStyle getCellStyleReceiptTail(XSSFWorkbook wb){
CellStyle cellStyle = getCellStyleText(wb);
Font font = wb.createFont();// 创建字体样式
font.setFontHeightInPoints((short) 11); // 设置字体大小
cellStyle.setFont(font);
return cellStyle;
}
/**
* 回执正文样式
* @param wb
* @return
*/
private static CellStyle getCellStyleReceiptCenter(XSSFWorkbook wb){
CellStyle cellStyle = getCellStyleReceiptTail(wb);
Font font = wb.createFont();// 创建字体样式
cellStyle.setFont(font);
cellStyle.setAlignment(CellStyle.ALIGN_LEFT); //左对齐
return cellStyle;
}
/**
* 给指定行列设置单元格式
* @param wb
* @param sheet
* @param rowFirst 起始行
* @param rowLast 结束行
* @param columnsFirst 起始列
* @param columnsLast 结束列
* @param cellStyle 样式
*/
private static void styleMultipleCells(XSSFWorkbook wb,Sheet sheet,int rowFirst,int rowLast,int columnsFirst,int columnsLast,CellStyle cellStyle){
for (int i=rowFirst;i<=rowLast;i++){
int enterCnt = 0;
for (int j=columnsFirst;j<=columnsLast;j++){
Row row = sheet.getRow(i);
Cell cell = row.getCell(j);
setTheBorder(cellStyle); //设置不包括合并单元格的边框
cell.setCellStyle(cellStyle);
//自适应高
int rwsTemp = row.getCell(j).toString().split("\n").length;
if (rwsTemp > enterCnt) {
enterCnt = rwsTemp;
}
row.setHeight((short)(enterCnt * 400));
//遇到合并单元格时,跳出
if (isMergedRegion(sheet,i,j)){
break;
}
}
}
}
/**
* 单元格边框
* @param cellStyle
*/
private static void setTheBorder(CellStyle cellStyle){
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
}
/**
* 是否为合并单元格
* @param sheet
* @param row 行
* @param column 列
* @return true 是 false 否
*/
public static boolean isMergedRegion(Sheet sheet, int row , int column)
{
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if(row >= firstRow && row <= lastRow)
{
if(column >= firstColumn && column <= lastColumn)
{
return true;
}
}
}
return false;
}
/**
* 根据指定格式,格式化时间
* @param date 时间
* @param dateFormat 格式
* @return
*/
private static String formatDate(Date date,String dateFormat){
if (date != null){
SimpleDateFormat simpleDateFormat = new SimpleDateFormat(dateFormat);
return simpleDateFormat.format(date.getTime());
}
return null;
}
/**
* 遍历合并区域
* @param sheet
*/
private static void traverseMergeArea(Sheet sheet){
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress region = sheet.getMergedRegion(i); //
int colIndex = region.getFirstColumn(); // 合并区域首列位置
int rowNum = region.getFirstRow(); // 合并区域首行位置
System.out.println("第[" + i + "]个合并区域:" + sheet.getRow(rowNum).getCell(colIndex).getStringCellValue());
}
}
/**
* 集体设置合并单元格边框
* @param first 开始单元格
* @param last 结束单元格
* @param border
* @param sheet
* @param wb
*/
private static void mergeAreaBorders(int first,int last,int border, Sheet sheet, XSSFWorkbook wb){
for (int i = first; i <=last; i++) {
//sheet.getMergedRegion(i);
mergedCellBorder(1,sheet.getMergedRegion(i),sheet,wb);
}
}
/**
* 给字符串以指定长度换行
* @param str 字符串
* @param len 长度
* @return
*/
private static String stringWrap(String str,int len){
String s = "";
int c = str.length()%len==0 ? str.length()/len : (str.length()/len+1);
int endIndex=0;
for (int i=0;i<c;i++){
endIndex=(i+1)*len<str.length()?(i+1)*len:(str.length()-1);
s+=str.substring(i*len,endIndex);
if (i==(c-1)){
s+=str.substring(str.length()-1,str.length());
}
if ((c-i)!=1){
s+="\n";
}
}
return s;
}
/**
* 返回类型
* @param companyId 公司id
* @param isAdded true 新增的 false 删除的
* @return
*/
private String businessCategory(Integer companyId,Boolean isAdded){
//String str="R施工 □设计 □监理 □勘察 □招标代理 □造价咨询 □检测 □建设单位";
String str="";
List<Dict> dicts = DictDao.findDefault("ql");
HashMap<String, Object> map = new HashMap<String, Object>();
for (Dict dict:dicts) {
System.out.println("+++++++++++++企业类型 start++++++++++++++");
System.out.println(str.length());
map.clear();
map.put("companyId",companyId);
map.put("typeId",dict.getId());
if (isAdded){
str +=companyTypeDao.selectAdded(map)!=null?"R":"□";
}else {
str += faithCompanyTypeHisDao.selectDelete(map)!=null?"R":"□";
}
str += dict.getLabel()+" ";
System.out.println("+++++++++++++企业类型 end++++++++++++++");
}
return str;
}
/**
* 判断企业类型是否删除
* @param companyId
* @return
*/
private Boolean isTypeDelete(Integer companyId){
Boolean b = false;
List<Dict> dicts = DictDao.findDefault("ql");
HashMap<String, Object> map = new HashMap<String, Object>();
for (Dict dict:dicts){
map.clear();
map.put("companyId",companyId);
map.put("typeId",dict.getId());
if (faithCompanyTypeHisDao.selectDelete(map)!=null){
b = true;
return b;
}
}
return b;
}
/**
* 判断两字符串
* @param str1
* @param str2
* @return 相同 false, 不同 true
*/
private Boolean compareStrings(String str1,String str2){
if (str1.trim().equals(str2.trim())){
return false;
}
return true;
}
/**
* 企业录入申请表
* @param request
* @param response
* @param redirectAttributes
* @param companyId 公司id
* @param first 第几报表
* 第1张报表 新增:企业录入申请表+附表1+附表2-附表4
* 第2张报表 修改:企业录入申请表+附表2-附表4
* 第3张报表 新增:企业录入申请表+附表5
*/
@RequestMapping(value = "export")
public void export(HttpServletRequest request, HttpServletResponse response, RedirectAttributes redirectAttributes, Integer companyId, Integer first) {
try {
System.out.println("+++++++++++++++++++++企业录入申请表 start++++++++++++++++++++++++");
//公司id
System.out.println("企业id:"+companyId);
//第几张表
System.out.println("第几张表:"+first);
CompanyMes companyMes = companyMesDao.selectByPrimaryKey(companyId);
HashMap<String, Object> map = new HashMap<String, Object>();
String titleName = companyMes.getCompanyName()+"企业录入申请表";
titleName += first == 1?"--附表1-4新增":(first == 2?"--附表1-4修改":"--附表5新增");
String fileName = titleName + DateUtils.getDate("yyyyMMddHHmmss") + ".xlsx";
ServletOutputStream outputStream = response.getOutputStream();
response.setHeader("Content-disposition", "attachment; fileName=".concat(String.valueOf(URLEncoder.encode(fileName, "UTF-8"))));
response.setContentType("application/msexcel");
//创建HSSFWorkbook对象(excel的文档对象)
XSSFWorkbook wb = new XSSFWorkbook();
System.out.println("+++++++++++++++++++++主表 start++++++++++++++++++++++++");
//建立新的sheet对象(excel的表单)
Sheet sheet = wb.createSheet();
//设置sheet名字
wb.setSheetName(0,"武汉市园林绿化建设市场诚信管理系统(试行)企业录入申请表");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
cell.setCellValue("武汉市园林绿化建设市场诚信管理系统(试行)企业录入申请表");
cell.setCellStyle(getCellStyleTitle(wb));
row.setHeightInPoints((short) (40));
Row row1 = sheet.createRow(1);
Cell cell1 = row1.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(1,1,0,3));
cell1.setCellValue("( )号");
cell1.setCellStyle(getCellStyleText(wb));
row1.setHeight((short)(400));
Row row2 = sheet.createRow(2);
Cell cell2 = row2.createCell(0);
cell2.setCellValue("企业名称(盖章)");
Cell cell3 = row2.createCell(1);
cell3.setCellValue(companyMes.getCompanyName());
Cell cell4 = row2.createCell(2);
cell4.setCellValue("统一社会\n" + "信用代码");
Cell cell5 = row2.createCell(3);
cell5.setCellValue(companyMes.getSocialCreditCode());
Row primaryTable2 = sheet.createRow(3);
primaryTable2.createCell(0).setCellValue("信用专职人员姓名");
primaryTable2.createCell(1).setCellValue(companyMes.getCreditProfessional());
primaryTable2.createCell(2).setCellValue("信用专职人员\n" +"手机号");
primaryTable2.createCell(3).setCellValue(companyMes.getProfessionalPhone());
Row row3 = sheet.createRow(4);
row3.createCell(0).setCellValue("申请类型\n" + "(只能\"√\"一项)");
row3.createCell(1);
row3.createCell(2).setCellValue("申请时间\n" + "(年/月/日)");
row3.createCell(3).setCellValue(formatDate(companyMes.getSubmitTime(),"yyyy/MM/dd"));
Row row4 = sheet.createRow(5);
row4.createCell(0).setCellValue("申请录入内容复述");
Cell cell6 = row4.createCell(1);
sheet.addMergedRegion(new CellRangeAddress(5,5,1,3));
System.out.println("+++++++++++++++++++++证明文件计算 start++++++++++++++++++++++++");
int picNumOne =0;
if ((first == 1 && companyMes.getVersionNum()==0) ||(first == 2 &&((companyMesDao.selectModify(companyId).size()>0)||isTypeDelete(companyId)))){
picNumOne =calculatePicture(companyMes.getCreditCodeUrl())+calculatePicture(companyMes.getAdreeUrl())+2;
}
int picNumTwo = 0;
List<CompanyQual> companyQualsByAdded = companyQualDao.selectAdded(companyId);
List<CompanyQual> companyQualsByModify = companyQualDao.selectModify(companyId);
List<faithCompanyQualHis> faithCompanyQualHisByDelete = faithCompanyQualHisDao.selectDelete(companyId);
if (first ==1 && companyQualsByAdded !=null){
for (CompanyQual companyQual:companyQualsByAdded) {
picNumTwo += calculatePicture(companyQual.getEnclosure());
}
}
if (first ==2 ){
if (companyQualsByModify !=null){
for (CompanyQual companyQual:companyQualsByModify) {
picNumTwo += calculatePicture(companyQual.getEnclosure());
}
}
if (faithCompanyQualHisByDelete != null){
for (faithCompanyQualHis f:faithCompanyQualHisByDelete) {
picNumTwo += calculatePicture(f.getEnclosure());
}
}
}
int picThree = 0;
List<CompanyManMes> companyManMesByAdded = companyManMesDao.selectAdded(companyId);
List<CompanyManMes> companyManMesByModify = companyManMesDao.selectModify(companyId);
List<CompanyMan> companyManByModify = companyManDao.selectModify(companyId);
List<faithCompanyManmesHis> faithCompanyManmesHisByDelete = faithCompanyManmesHisDao.selectDelete(companyId);
if (first ==1 && companyManMesByAdded !=null){
for (CompanyManMes companyManMes:companyManMesByAdded) {
picThree+= calculatePicture(companyManMes.getSocialSecurityCert())+2;
}
}
if (first ==2 ){
if (companyManMesByModify != null){
for (CompanyManMes companyManMes:companyManMesByModify) {
picThree+= calculatePicture(companyManMes.getSocialSecurityCert())+2;
}
}
if (companyManByModify != null){
for (CompanyMan companyMan:companyManByModify) {
picThree += 2;
}
}
if (faithCompanyManmesHisByDelete !=null){
for (faithCompanyManmesHis f:faithCompanyManmesHisByDelete) {
picThree+= calculatePicture(f.getSocialSecurityCert())+2;
}
}
}
int picFour = 0;
List<CompanyProject> companyProjectsByAdded = companyProjectDao.selectAdded(companyId);
List<CompanyProject> companyProjectsByModify = companyProjectDao.selectModify(companyId);
List<faithCompanyProjectHis> faithCompanyProjectHisByDelete = faithCompanyProjectHisDao.selectDelete(companyId);
if (first == 1 && companyProjectsByAdded !=null){
for (CompanyProject companyProject:companyProjectsByAdded) {
picFour+=calculatePicture(companyProject.getEnclosure());
}
}
if (first == 2){
if (companyProjectsByModify !=null){
for (CompanyProject companyProject:companyProjectsByModify) {
picFour+=calculatePicture(companyProject.getEnclosure());
}
}
if (faithCompanyProjectHisByDelete !=null){
for (faithCompanyProjectHis f:faithCompanyProjectHisByDelete) {
picFour+=calculatePicture(f.getEnclosure());
}
}
}
int picFives = 0;
List<Faithgoodinfo> faithgoodinfosByAdded = faithgoodinfoDao.selectAdded(companyId);
if (first ==3){
if (faithgoodinfosByAdded !=null){
for (Faithgoodinfo f:faithgoodinfosByAdded) {
picFives+=calculatePicture(f.getEnclosure());
}
}
}
System.out.println("+++++++++++++++++++++证明文件计算 end++++++++++++++++++++++++");
if (first != 3){
cell6.setCellValue("附表1证明文件("+picNumOne+")张\n" + "附表2证明文件("+picNumTwo+")张\n"
+ "附表3证明文件("+picThree+")张\n" + "附表4证明文件("+picFour+")张\n" + "附表5证明文件( )张");
}else {
cell6.setCellValue("附表1证明文件( )张\n" + "附表2证明文件( )张\n"
+ "附表3证明文件( )张\n" + "附表4证明文件( )张\n" + "附表5证明文件("+picFives+")张");
}
Row row5 = sheet.createRow(6);
row5.createCell(0).setCellValue("初审意见");
row5.createCell(1).setCellValue("\n\n\n" + "审核人: .\n" + "审定日期: .");
sheet.addMergedRegion(new CellRangeAddress(6,6,1,3));
Row row6 = sheet.createRow(7);
row6.createCell(0).setCellValue("复审意见");
row6.createCell(1).setCellValue("\n\n\n" + "审核人: .\n" + "审定日期: .");
sheet.addMergedRegion(new CellRangeAddress(7,7,1,3));
Row row7 = sheet.createRow(8);
row7.createCell(0).setCellValue("领导批示");
row7.createCell(1).setCellValue("\n \n \n " + "\n .");
sheet.addMergedRegion(new CellRangeAddress(8,8,1,3));
//给指定行列设置单元格式
styleMultipleCells(wb,sheet,2,8,0,3,getCellStyleText(wb));
CellStyle cellStyleCenter = getCellStyleText(wb);
cellStyleCenter.setAlignment(CellStyle.ALIGN_RIGHT);//水平居右
row5.getCell(1).setCellStyle(cellStyleCenter);
row6.getCell(1).setCellStyle(cellStyleCenter);
Row row8 = sheet.createRow(9);
row8.createCell(0);
CellRangeAddress cellRangeAddress = new CellRangeAddress(9, 9, 0, 3);
sheet.addMergedRegion(cellRangeAddress);
CellStyle cellStyle = getCellStyleText(wb);
cellStyle.setBorderBottom(CellStyle.BORDER_MEDIUM_DASHED);
row8.getCell(0).setCellStyle(cellStyle);
row8.createCell(1).setCellStyle(cellStyle);
row8.createCell(2).setCellStyle(cellStyle);
row8.createCell(3).setCellStyle(cellStyle);
Row row9 = sheet.createRow(10);
Cell cell7 = row9.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(10,10,0,3));
cell7.setCellValue("武汉市园林绿化建设市场诚信管理系统(试行)企业录入申请表");
cell7.setCellStyle(getCellStyleTitle(wb));
row9.setHeightInPoints((short) (40));
Row row10 = sheet.createRow(11);
Cell cell8 = row10.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(11,11,0,3));
cell8.setCellValue("( )号");
cell8.setCellStyle(getCellStyleReceiptTail(wb));
row10.setHeightInPoints((short) (20));
Row row11 = sheet.createRow(12);
Cell cell9 = row11.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(12,12,0,3));
cell9.setCellValue(companyMes.getCompanyName()+":");
cell9.setCellStyle(getCellStyleReceiptLookup(wb));
row11.setHeightInPoints((short) (20));
Row row12 = sheet.createRow(13);
Cell cell10 = row12.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(13,13,0,3));
cell10.setCellValue(" 今收到你单位武汉市园林绿化建设市场诚信管理系统(试行)企业录入申请表及相关附件资料," +
"我站将按照《武汉市园林绿化建设市场主体信用信息管理办法(试行)》及有关工作要求,办理信用信息录入的审核事宜。");
cell10.setCellStyle(getCellStyleReceiptCenter(wb));
row12.setHeightInPoints((short) (40));
Row row13 = sheet.createRow(14);
Cell cell11 = row13.createCell(2);
sheet.addMergedRegion(new CellRangeAddress(14,14,2,3));
cell11.setCellValue("武汉市园林绿化建设管理站");
cell11.setCellStyle(getCellStyleReceiptTail(wb));
row13.setHeightInPoints((short) (20));
Row row14 = sheet.createRow(15);
Cell cell12 = row14.createCell(2);
sheet.addMergedRegion(new CellRangeAddress(15,15,2,3));
cell12.setCellValue("______年___月___日");
cell12.setCellStyle(getCellStyleReceiptTail(wb));
row14.setHeightInPoints((short) (20));
//遍历合并单格格
//traverseMergeArea(sheet);
//设置合并单元格边框
mergeAreaBorders(2,5,1,sheet,wb);
//设置勾选
if (first==2){
row3.getCell(1).setCellValue(setStrCheck(wb,"□新增\n" + "R修改" ));
}else {
row3.getCell(1).setCellValue(setStrCheck(wb,"R新增\n" + "□修改" ));
}
//宽度自适应
for (int j=0;j<=4;j++){
sheet.autoSizeColumn(j, true);
}
System.out.println("+++++++++++++++++++++主表 end++++++++++++++++++++++++");
if (first!=3){
System.out.println("+++++++++++++++++++++附表1 start++++++++++++++++++++++++");
faithCompanyMesHis faithCompanyMesHis = faithCompanyMesHisDao.selectByPrimaryNew(companyId);
//建立新的sheet对象(excel的表单)
Sheet sheet1 = wb.createSheet();
//设置sheet名字
wb.setSheetName(1,"附表1");
Row scheduleOneRow1 = sheet1.createRow(0);
Cell cell13 = scheduleOneRow1.createCell(0);
sheet1.addMergedRegion(new CellRangeAddress(0,0,0,3));
cell13.setCellValue("附表1");
cell13.setCellStyle(getCellStyleReceiptCenter(wb));
Row scheduleOneRow2 = sheet1.createRow(1);
Cell cell14 = scheduleOneRow2.createCell(0);
sheet1.addMergedRegion(new CellRangeAddress(1,1,0,3));
cell14.setCellValue("武汉市园林绿化建设市场诚信管理系统(试行)企业基本信息申报表");
cell14.setCellStyle(getCellStyleTitle(wb));
scheduleOneRow2.setHeightInPoints((short) (40));
Row scheduleOneRow3 = sheet1.createRow(2);
Cell cell15 = scheduleOneRow3.createCell(0);
sheet1.addMergedRegion(new CellRangeAddress(2,2,0,2));
cell15.setCellValue("填报单位(盖章):"+companyMes.getCompanyName());
Cell cell16 = scheduleOneRow3.createCell(3);
sheet1.addMergedRegion(new CellRangeAddress(2,2,3,3));
cell16.setCellValue("填报时间: "+formatDate(companyMes.getSubmitTime(),"yyyy年MM月dd日"));
cell15.setCellStyle(getCellStyleReportTheUnit(wb));
cell16.setCellStyle(getCellStyleReportTheUnit(wb));
scheduleOneRow3.setHeightInPoints(20);
Row scheduleOneRow4 = sheet1.createRow(3);
scheduleOneRow4.createCell(0).setCellValue("企业名称");
if (first==1 && companyMes.getVersionNum()==0){
scheduleOneRow4.createCell(1).setCellValue(companyMes.getCompanyName());
}else if (first==2 && compareStrings(companyMes.getCompanyName(),faithCompanyMesHis.getCompanyName())){
scheduleOneRow4.createCell(1).setCellValue(companyMes.getCompanyName());
}else {
scheduleOneRow4.createCell(1);
}
scheduleOneRow4.createCell(2).setCellValue("统一社会\n" + "信用代码");
if (first==1 && companyMes.getVersionNum()==0){
scheduleOneRow4.createCell(3).setCellValue(companyMes.getSocialCreditCode());
}else if (first==2 && compareStrings(companyMes.getSocialCreditCode(),faithCompanyMesHis.getSocialCreditCode())){
scheduleOneRow4.createCell(3).setCellValue(companyMes.getSocialCreditCode());
}else{
scheduleOneRow4.createCell(3);
}
Row scheduleOneRow5 = sheet1.createRow(4);
scheduleOneRow5.createCell(0).setCellValue("工商登记机关");
if (first==1 && companyMes.getVersionNum()==0){
scheduleOneRow5.createCell(1).setCellValue(companyMes.getBusinessRegistAuthor());
}else if (first==2 && compareStrings(companyMes.getBusinessRegistAuthor(),faithCompanyMesHis.getBusinessRegistAuthor())){
scheduleOneRow5.createCell(1).setCellValue(companyMes.getBusinessRegistAuthor());
}else {
scheduleOneRow5.createCell(1);
}
scheduleOneRow5.createCell(2).setCellValue("成立时间");
if (first==1 && companyMes.getVersionNum()==0){
scheduleOneRow5.createCell(3).setCellValue(formatDate(companyMes.getFoundingTime(),"yyyy年MM月dd日"));
}else if (first==2 && compareStrings(formatDate(companyMes.getFoundingTime(), "yyyy年MM月dd日"),
formatDate(faithCompanyMesHis.getFoundingTime(),"yyyy年MM月dd日"))){
}else {
scheduleOneRow5.createCell(3);
}
scheduleOneRow5.setHeight((short) 400);
Row scheduleOneRow6 = sheet1.createRow(5);
scheduleOneRow6.createCell(0).setCellValue("企业地址");
if (first==1 && companyMes.getVersionNum()==0){
scheduleOneRow6.createCell(1).setCellValue(companyMes.getRegisteredAddress());
}else if (first==2 && compareStrings(companyMes.getRegisteredAddress(),faithCompanyMesHis.getRegisteredAddress())){
scheduleOneRow6.createCell(1).setCellValue(companyMes.getRegisteredAddress());
}else {
scheduleOneRow6.createCell(1);
}
scheduleOneRow6.createCell(2).setCellValue("是否有本市\n" + "固定办公地址");
String string ="";
if (first==1 && companyMes.getVersionNum()==0){
if (companyMes.getIswuhanCompany().trim().equals("1")) {
string="R是 □否";
}else {
string="□是 R否";
}
}else if (first==2 && compareStrings(companyMes.getIswuhanCompany(),faithCompanyMesHis.getIswuhanCompany())){
if (companyMes.getIswuhanCompany().trim().equals("1")) {
string="R是 □否";
}else {
string="□是 R否";
}
}else {
string="□是 □否";
}
scheduleOneRow6.createCell(3).setCellValue(setStrCheck(wb,string));
Row scheduleOneRow7 = sheet1.createRow(6);
if (first == 1){
scheduleOneRow7.createCell(0).setCellValue("企业类别");
}else {
scheduleOneRow7.createCell(0).setCellValue("企业类别(删除)");
}
Cell cell17 = scheduleOneRow7.createCell(1);
sheet1.addMergedRegion(new CellRangeAddress(6,6,1,3));
if (first == 1){
cell17.setCellValue(setStrCheck(wb,businessCategory(companyId,true)));
}else if (first == 2){
cell17.setCellValue(setStrCheck(wb,businessCategory(companyId,false)));
}
Row scheduleOneRow8 = sheet1.createRow(7);
scheduleOneRow8.createCell(0).setCellValue("经营范围");
Cell cell18 = scheduleOneRow8.createCell(1);
sheet1.addMergedRegion(new CellRangeAddress(7,7,1,3));
if (first==1 && companyMes.getVersionNum()==0){
cell18.setCellValue(stringWrap(companyMes.getBusinessScope(),50));
}else if (first==2 && compareStrings(companyMes.getBusinessScope(),faithCompanyMesHis.getBusinessScope())){
cell18.setCellValue(stringWrap(companyMes.getBusinessScope(),50));
}
faithCompanyPerHis faithCompanyPerHis = faithCompanyPerHisDao.selectByPrimaryNew(companyId);
Row scheduleOneRow9 = sheet1.createRow(8);
scheduleOneRow9.createCell(0).setCellValue("企业法人姓名");
CompanyPer companyPer = companyPerDao.selecetByCode(companyId);
if (first==1 && companyMes.getVersionNum()==0){
scheduleOneRow9.createCell(1).setCellValue(companyPer.getName());
}else if (first==2 && compareStrings(companyPer.getName(),faithCompanyPerHis.getName())){
scheduleOneRow9.createCell(1).setCellValue(companyPer.getName());
}else{
scheduleOneRow9.createCell(1);
}
scheduleOneRow9.createCell(2).setCellValue("性别");
if (first==1 && companyMes.getVersionNum()==0){
scheduleOneRow9.createCell(3).setCellValue(companyPer.getSex());
}else if (first==2 && compareStrings(companyPer.getSex(),faithCompanyPerHis.getSex())){
scheduleOneRow9.createCell(3).setCellValue(companyPer.getSex());
}else {
scheduleOneRow9.createCell(3);
}
Row scheduleOneRow10 = sheet1.createRow(9);
scheduleOneRow10.createCell(0).setCellValue("出生年月");
if (first==1 && companyMes.getVersionNum()==0){
scheduleOneRow10.createCell(1).setCellValue(formatDate(companyPer.getBirthday(),"yyyy年MM月dd日"));
}else if (first==2 && compareStrings(formatDate(companyPer.getBirthday(),"yyyy年MM月dd日"),
formatDate(faithCompanyPerHis.getBirthday(),"yyyy年MM月dd日"))){
scheduleOneRow10.createCell(1).setCellValue(formatDate(companyPer.getBirthday(),"yyyy年MM月dd日"));
}else {
scheduleOneRow10.createCell(1);
}
scheduleOneRow10.createCell(2).setCellValue("最高学历");
if (first==1 && companyMes.getVersionNum()==0) {
scheduleOneRow10.createCell(3).setCellValue(companyPer.getHighEducat());
}else if (first==2 && compareStrings(companyPer.getHighEducat(),faithCompanyPerHis.getHighEducat())){
scheduleOneRow10.createCell(3).setCellValue(companyPer.getHighEducat());
}else {
scheduleOneRow10.createCell(3);
}
scheduleOneRow10.setHeight((short) 40);
Row scheduleOneRow11 = sheet1.createRow(10);
scheduleOneRow11.createCell(0).setCellValue("信用专职人员姓名");
if (first==1 && companyMes.getVersionNum()==0){
scheduleOneRow11.createCell(1).setCellValue(companyMes.getCreditProfessional());
}else if (first==2 && compareStrings(companyMes.getCreditProfessional(),faithCompanyMesHis.getCreditProfessional())){
scheduleOneRow11.createCell(1).setCellValue(companyMes.getCreditProfessional());
}else{
scheduleOneRow11.createCell(1);
}
scheduleOneRow11.createCell(2).setCellValue("信用专职人员\n" + "手机号");
if (first==1 && companyMes.getVersionNum()==0){
scheduleOneRow11.createCell(3).setCellValue(companyMes.getProfessionalPhone());
}else if (first==2 && compareStrings(companyMes.getProfessionalPhone().toString(),faithCompanyMesHis.getProfessionalPhone().toString())){
scheduleOneRow11.createCell(3).setCellValue(companyMes.getProfessionalPhone());
}else{
scheduleOneRow11.createCell(3);
}
//给指定行列设置单元格式
styleMultipleCells(wb,sheet1,3,10,0,3,getCellStyleText(wb));
scheduleOneRow5.setHeightInPoints((short) 40);
scheduleOneRow7.setHeightInPoints((short) 40);
scheduleOneRow9.setHeightInPoints((short) 40);
scheduleOneRow10.setHeightInPoints((short) 40);
//遍历合并单格格
traverseMergeArea(sheet1);
//设置合并单元格边框
mergeAreaBorders(4,5,1,sheet1,wb);
Row scheduleOneRow12 = sheet1.createRow(11);
Cell cell19 = scheduleOneRow12.createCell(0);
sheet1.addMergedRegion(new CellRangeAddress(11,11,0,3));
cell19.setCellValue("说明:\n" +
"1、填报本市办公地址,需提供企业购房或租房的相关证明文件(核查原件,收复印件1份);\n" +
"2、填报企业其它基本信息,需提供企业营收执照(核查原件,收复印件1份);\n" +
"3、填报人员信息,需提供被填报人身份证,填报或修改企业法人还需提供学历证书(核查原件,收复印件1份);\n" +
"4、含“□”项为勾选项,选择直接在“□”内打“√”,企业类别根据企业经营实际可多选");
cell19.setCellStyle(getCellStyleReceiptCenter(wb));
scheduleOneRow12.setHeightInPoints((short) 80);
//宽度自适应
for (int j=0;j<=4;j++){
sheet1.autoSizeColumn(j, true);
}
//设置高度
System.out.println("+++++++++++++++++++++附表1 end++++++++++++++++++++++++");
System.out.println("+++++++++++++++++++++附表2 start++++++++++++++++++++++++");
//建立新的sheet对象(excel的表单)
Sheet sheet2 = wb.createSheet();
//设置sheet名字
wb.setSheetName(2,"附表2");
Row scheduleTwoRow1 = sheet2.createRow(0);
Cell cell20 = scheduleTwoRow1.createCell(0);
sheet2.addMergedRegion(new CellRangeAddress(0,0,0,6));
cell20.setCellValue("附表2");
cell20.setCellStyle(getCellStyleReceiptCenter(wb));
Row scheduleTwoRow2 = sheet2.createRow(1);
Cell cell21 = scheduleTwoRow2.createCell(0);
sheet2.addMergedRegion(new CellRangeAddress(1,1,0,6));
cell21.setCellValue("企业资质信息表");
cell21.setCellStyle(getCellStyleTitle(wb));
scheduleTwoRow2.setHeightInPoints(40);
Row scheduleTwoRow3 = sheet2.createRow(2);
Cell cell22 = scheduleTwoRow3.createCell(0);
sheet2.addMergedRegion(new CellRangeAddress(2,2,0,4));
cell22.setCellValue("填报单位(盖章):"+companyMes.getCompanyName());
Cell cell23 = scheduleTwoRow3.createCell(5);
sheet2.addMergedRegion(new CellRangeAddress(2,2,5,6));
cell23.setCellValue("填报时间: "+formatDate(companyMes.getSubmitTime(),"yyyy年MM月dd日"));
cell22.setCellStyle(getCellStyleReportTheUnit(wb));
cell23.setCellStyle(getCellStyleReportTheUnit(wb));
scheduleTwoRow3.setHeightInPoints(20);
Row scheduleTwoRow4 = sheet2.createRow(3);
scheduleTwoRow4.createCell(0).setCellValue("序号");
scheduleTwoRow4.createCell(1).setCellValue("资质类别");
scheduleTwoRow4.createCell(2).setCellValue("资质等级");
scheduleTwoRow4.createCell(3).setCellValue("证书编号");
scheduleTwoRow4.createCell(4).setCellValue("核发机关");
scheduleTwoRow4.createCell(5).setCellValue("授予时间\n" + "(年/月/日)");
scheduleTwoRow4.createCell(6).setCellValue("资质有效\n" + "截止时间\n" + "(年/月/日)");
scheduleTwoRow4.setHeightInPoints((short)60);
//给指定行列设置单元格式
styleMultipleCells(wb,sheet2,3,3,0,6,getCellStyleTextTitle(wb));
int rowi = 3;
int rowNum = 0;
if (first ==1 && companyQualsByAdded!=null){
traverseCompanyQuals(companyQualsByAdded, sheet2, rowi);
rowNum = companyQualsByAdded.size();
styleMultipleCells(wb,sheet2,4,3+rowNum,0,6,getCellStyleText(wb));
}
if (first!=1){
if (companyQualsByModify!=null){
traverseCompanyQuals(companyQualsByModify, sheet2, rowi);
styleMultipleCells(wb,sheet2,4,3+companyQualsByModify.size(),0,6,getCellStyleText(wb));
}
if (faithCompanyQualHisByDelete!=null){
for (faithCompanyQualHis companyQualHis: faithCompanyQualHisByDelete) {
Row scheduleTwoRow51 = sheet2.createRow(rowi+companyQualsByModify.size()+ 1);
scheduleTwoRow51.createCell(0).setCellValue(rowi+companyQualsByModify.size()-2);
scheduleTwoRow51.createCell(1).setCellValue(companyQualHis.getQualType());
scheduleTwoRow51.createCell(2).setCellValue(companyQualHis.getQualLevel());
scheduleTwoRow51.createCell(3).setCellValue(companyQualHis.getCertificateNum());
scheduleTwoRow51.createCell(4).setCellValue(companyQualHis.getIssuingOrgan());
scheduleTwoRow51.createCell(5).setCellValue(formatDate(companyQualHis.getGrantDate(),"yyyy/MM/dd"));
scheduleTwoRow51.createCell(6).setCellValue(formatDate(companyQualHis.getValidityTime(),"yyyy/MM/dd"));
rowi++;
}
styleMultipleCells(wb,sheet2,4+companyQualsByModify.size(),3+companyQualsByModify.size()+faithCompanyQualHisByDelete.size(),0,6,getCellStyleTextDelete(wb));
}
rowNum = companyQualsByModify.size()+faithCompanyQualHisByDelete.size();
}
Row scheduleTwoRow6 = sheet2.createRow(rowNum+4);
Cell cell24 = scheduleTwoRow6.createCell(0);
sheet2.addMergedRegion(new CellRangeAddress(rowNum+4,rowNum+4,0,6));
cell24.setCellValue("说明:\n" +
"1、填报需提供资质证书(核查原条件,收复印件1份)。");
cell24.setCellStyle(getCellStyleReceiptCenter(wb));
scheduleTwoRow6.setHeightInPoints(40);
//宽度自适应
for (int j=0;j<=6;j++){
sheet2.autoSizeColumn(j, true);
}
System.out.println("+++++++++++++++++++++附表2 end++++++++++++++++++++++++");
System.out.println("+++++++++++++++++++++附表3 start++++++++++++++++++++++++");
//建立新的sheet对象(excel的表单)
Sheet sheet3 = wb.createSheet();
//设置sheet名字
wb.setSheetName(3,"附表3");
Row scheduleThreeRow1 = sheet3.createRow(0);
Cell cell25 = scheduleThreeRow1.createCell(0);
sheet3.addMergedRegion(new CellRangeAddress(0,0,0,6));
cell25.setCellValue("附表3");
cell25.setCellStyle(getCellStyleReceiptCenter(wb));
Row scheduleThreeRow2 = sheet3.createRow(1);
Cell cell26 = scheduleThreeRow2.createCell(0);
sheet3.addMergedRegion(new CellRangeAddress(1,1,0,6));
cell26.setCellValue("企业技术人员信息表");
cell26.setCellStyle(getCellStyleTitle(wb));
scheduleThreeRow2.setHeightInPoints(40);
Row scheduleThreeRow3 = sheet3.createRow(2);
Cell cell27 = scheduleThreeRow3.createCell(0);
sheet3.addMergedRegion(new CellRangeAddress(2,2,0,4));
cell27.setCellValue("填报单位(盖章):"+companyMes.getCompanyName());
Cell cell28 = scheduleThreeRow3.createCell(5);
sheet3.addMergedRegion(new CellRangeAddress(2,2,5,6));
cell28.setCellValue("填报时间: "+formatDate(companyMes.getSubmitTime(),"yyyy年MM月dd日"));
cell27.setCellStyle(getCellStyleReportTheUnit(wb));
cell28.setCellStyle(getCellStyleReportTheUnit(wb));
scheduleThreeRow3.setHeightInPoints(20);
Row scheduleThreeRow4 = sheet3.createRow(3);
scheduleThreeRow4.createCell(0).setCellValue("序号");
scheduleThreeRow4.createCell(1).setCellValue("姓名");
scheduleThreeRow4.createCell(2).setCellValue("性别");
scheduleThreeRow4.createCell(3).setCellValue("身份证号");
scheduleThreeRow4.createCell(4).setCellValue("工作类型");
scheduleThreeRow4.createCell(5).setCellValue("职称/执业类别名称");
scheduleThreeRow4.createCell(6).setCellValue("等级");
//给指定行列设置单元格式
styleMultipleCells(wb,sheet3,3,3,0,6,getCellStyleTextTitle(wb));
scheduleThreeRow4.setHeightInPoints(60);
rowi =3;
rowNum = 0;
if (first == 1 && companyManMesByAdded != null){
traverseCompanyManMes(sheet3,companyManMesByAdded,rowi);
rowNum = companyManMesByAdded.size();
styleMultipleCells(wb,sheet3,4,3+rowNum,0,6,getCellStyleText(wb));
}
if (first!=1){
if (companyManMesByModify != null){
traverseCompanyManMes(sheet3,companyManMesByModify,rowi);
rowNum = companyManMesByModify.size();
styleMultipleCells(wb,sheet3,4,3+rowNum,0,6,getCellStyleText(wb));
}
if(companyManByModify != null){
for (CompanyMan companyMan:companyManByModify) {
Row scheduleThreeRow52 = sheet3.createRow(rowi+companyManMesByModify.size()+ 1);
scheduleThreeRow52.createCell(0).setCellValue(rowi+companyManMesByModify.size()-2);
scheduleThreeRow52.createCell(1).setCellValue(companyMan.getName());
scheduleThreeRow52.createCell(2).setCellValue(companyMan.getSex());
scheduleThreeRow52.createCell(3).setCellValue(companyMan.getIdNumber());
scheduleThreeRow52.createCell(4);
scheduleThreeRow52.createCell(5);
scheduleThreeRow52.createCell(6);
rowNum++;
}
styleMultipleCells(wb,sheet3,4+companyManMesByModify.size(),3+rowNum,0,6,getCellStyleText(wb));
}
if (faithCompanyManmesHisByDelete != null){
for (faithCompanyManmesHis companyManmesHis:faithCompanyManmesHisByDelete) {
CompanyMan companyMan = companyManDao.selectByPrimaryKey(companyManmesHis.getManId());
Row scheduleThreeRow51 = sheet3.createRow(rowi+companyManMesByModify.size()+companyManByModify.size()+ 1);
scheduleThreeRow51.createCell(0).setCellValue(rowi+companyManMesByModify.size()-2);
scheduleThreeRow51.createCell(1).setCellValue(companyMan.getName());
scheduleThreeRow51.createCell(2).setCellValue(companyMan.getSex());
scheduleThreeRow51.createCell(3).setCellValue(companyMan.getIdNumber());
scheduleThreeRow51.createCell(4).setCellValue(companyManmesHis.getCompanyType());
scheduleThreeRow51.createCell(5).setCellValue(companyManmesHis.getOccupation());
scheduleThreeRow51.createCell(6).setCellValue(certificateLevel(companyManmesHis.getOccupationLevel()));
rowi++;
}
rowNum = companyManMesByModify.size()+faithCompanyManmesHisByDelete.size()+companyManByModify.size();
styleMultipleCells(wb,sheet3,4+companyManMesByModify.size()+companyManByModify.size(), 3+rowNum,0,6,getCellStyleTextDelete(wb));
}
}
Row scheduleThreeRow6 = sheet3.createRow(rowNum+4);
Cell cell29 = scheduleThreeRow6.createCell(0);
sheet3.addMergedRegion(new CellRangeAddress(rowNum+4,rowNum+4,0,6));
cell29.setCellValue("说明:\n" +
"1、工作类型包括施工、设计、监理、勘察、招标代理、造价咨询、检测和建设单位。\n" +
"2、每条信息只能选填一项工作类型和一个职称(执业)类别。\n" +
"3、需提供被填报人员的身份证、职称(执业)证、近三个月社保缴费证明(核查原件,收复印件1份)。");
cell29.setCellStyle(getCellStyleReceiptCenter(wb));
scheduleThreeRow6.setHeightInPoints(80);
//宽度自适应
for (int j=0;j<=6;j++){
sheet3.autoSizeColumn(j, true);
}
System.out.println("+++++++++++++++++++++附表3 end++++++++++++++++++++++++");
System.out.println("+++++++++++++++++++++附表4 start++++++++++++++++++++++++");
//建立新的sheet对象(excel的表单)
Sheet sheet4 = wb.createSheet();
//设置sheet名字
wb.setSheetName(4,"附表4");
Row scheduleFourRow1 = sheet4.createRow(0);
Cell cell30 = scheduleFourRow1.createCell(0);
sheet4.addMergedRegion(new CellRangeAddress(0,0,0,8));
cell30.setCellValue("附表4");
cell30.setCellStyle(getCellStyleReceiptCenter(wb));
Row scheduleFourRow2 = sheet4.createRow(1);
Cell cell31 = scheduleFourRow2.createCell(0);
sheet4.addMergedRegion(new CellRangeAddress(1,1,0,8));
cell31.setCellValue("企业本市业绩表");
cell31.setCellStyle(getCellStyleTitle(wb));
scheduleFourRow2.setHeightInPoints(40);
Row scheduleFourRow3 = sheet4.createRow(2);
Cell cell32 = scheduleFourRow3.createCell(0);
sheet4.addMergedRegion(new CellRangeAddress(2,2,0,7));
cell32.setCellValue("填报单位(盖章):"+companyMes.getCompanyName());
Cell cell33 = scheduleFourRow3.createCell(8);
cell33.setCellValue("填报时间: "+formatDate(companyMes.getSubmitTime(),"yyyy年MM月dd日"));
cell32.setCellStyle(getCellStyleReportTheUnit(wb));
cell33.setCellStyle(getCellStyleReportTheUnit(wb));
scheduleFourRow3.setHeightInPoints(20);
Row scheduleFourRow4 = sheet4.createRow(3);
scheduleFourRow4.createCell(0).setCellValue("序号");
scheduleFourRow4.createCell(1).setCellValue("工程名称");
scheduleFourRow4.createCell(2).setCellValue("工程类别");
scheduleFourRow4.createCell(3).setCellValue("竣工时间\n" + "(年/月)");
scheduleFourRow4.createCell(4).setCellValue("绿化面积\n" + "(平方米)");
scheduleFourRow4.createCell(5).setCellValue("结算金额\n" + "(万元)");
scheduleFourRow4.createCell(6).setCellValue("区属/投资占比");
scheduleFourRow4.createCell(7).setCellValue("项目负责人");
scheduleFourRow4.createCell(8).setCellValue("工程详情");
//给指定行列设置单元格式
styleMultipleCells(wb,sheet4,3,3,0,8,getCellStyleTextTitle(wb));
scheduleFourRow4.setHeightInPoints(60);
rowi =3;
rowNum = 0;
if (first == 1 && companyProjectsByAdded !=null){
traverseCompanyProjects(sheet4, companyProjectsByAdded, rowi);
rowNum = companyProjectsByAdded.size();
styleMultipleCells(wb,sheet4,4,3+rowNum,0,8,getCellStyleText(wb));
}
if (first!=1){
if (companyProjectsByModify!=null){
traverseCompanyProjects(sheet4, companyProjectsByModify, rowi);
styleMultipleCells(wb,sheet4,4,3+companyProjectsByModify.size(),0,8,getCellStyleText(wb));
}
if (faithCompanyProjectHisByDelete!=null){
for (faithCompanyProjectHis companyProjectHis:faithCompanyProjectHisByDelete) {
Row scheduleFourRow51 = sheet4.createRow(rowi+companyProjectsByModify.size()+ 1);
scheduleFourRow51.createCell(0).setCellValue(rowi+companyProjectsByModify.size()-2);
scheduleFourRow51.createCell(1).setCellValue(companyProjectHis.getProjectName());
scheduleFourRow51.createCell(2).setCellValue(companyProjectHis.getTypeName());
scheduleFourRow51.createCell(3).setCellValue(formatDate(companyProjectHis.getCompletedTime(),"yyyy年MM月dd日"));
scheduleFourRow51.createCell(4).setCellValue(companyProjectHis.getGreenSpace());
scheduleFourRow51.createCell(5).setCellValue(companyProjectHis.getSettleAmount());
scheduleFourRow51.createCell(6).setCellValue(companyProjectHis.getInvestment());
scheduleFourRow51.createCell(7).setCellValue(companyProjectHis.getPrincipal());
scheduleFourRow51.createCell(8).setCellValue(stringWrap(companyProjectHis.getProjectDetails(),50));
rowi++;
}
}
styleMultipleCells(wb,sheet4,4+companyProjectsByModify.size(),3+companyProjectsByModify.size()+faithCompanyProjectHisByDelete.size(),0,8,getCellStyleTextDelete(wb));
rowNum = companyProjectsByModify.size()+companyProjectsByModify.size();
}
Row scheduleFourRow6 = sheet4.createRow(rowNum+4);
Cell cell34 = scheduleFourRow6.createCell(0);
sheet4.addMergedRegion(new CellRangeAddress(rowNum+4,rowNum+4,0,8));
cell34.setCellValue("说明:\n" +
"1、本市业绩表每一条为一个项目的一个工程类别的信息。\n" +
"2、工作类型包括施工、设计、监理、勘察、招标代理、造价咨询、检测和建设单位。\n" +
"3、投标项目需提供项目的竣工验收备案表、中标通知书、工程合同,其他类型项目提供竣工验收表、工程合同(核查原件,收复印件1份)。\n" +
"4、投资占比为估算数值,填报百分值,各区投资占比百分之各应为100%。");
cell34.setCellStyle(getCellStyleReceiptCenter(wb));
scheduleFourRow6.setHeightInPoints(80);
//宽度自适应
for (int j=0;j<=8;j++){
sheet4.autoSizeColumn(j, true);
}
System.out.println("+++++++++++++++++++++附表4 end++++++++++++++++++++++++");
}
if (first==3){
System.out.println("+++++++++++++++++++++附表5 start++++++++++++++++++++++++");
//建立新的sheet对象(excel的表单)
Sheet sheet5 = wb.createSheet();
//设置sheet名字
wb.setSheetName(1,"附表5");
Row scheduleFivesRow1 = sheet5.createRow(0);
Cell cell35 = scheduleFivesRow1.createCell(0);
sheet5.addMergedRegion(new CellRangeAddress(0,0,0,9));
cell35.setCellValue("附表5");
cell35.setCellStyle(getCellStyleReceiptCenter(wb));
Row scheduleFivesRow2 = sheet5.createRow(1);
Cell cell36 = scheduleFivesRow2.createCell(0);
sheet5.addMergedRegion(new CellRangeAddress(1,1,0,9));
cell36.setCellValue("良好信息申报表");
cell36.setCellStyle(getCellStyleTitle(wb));
scheduleFivesRow2.setHeightInPoints(40);
Row scheduleFivesRow3 = sheet5.createRow(2);
Cell cell37 = scheduleFivesRow3.createCell(0);
sheet5.addMergedRegion(new CellRangeAddress(2,2,0,8));
cell37.setCellValue("填报单位(盖章):"+companyMes.getCompanyName());
Cell cell38 = scheduleFivesRow3.createCell(9);
cell38.setCellValue("填报时间: "+formatDate(companyMes.getSubmitTime(),"yyyy年MM月dd日"));
cell37.setCellStyle(getCellStyleReportTheUnit(wb));
cell38.setCellStyle(getCellStyleReportTheUnit(wb));
scheduleFivesRow3.setHeightInPoints(20);
Row scheduleFivesRow4 = sheet5.createRow(3);
scheduleFivesRow4.createCell(0).setCellValue("序号");
scheduleFivesRow4.createCell(1).setCellValue("加分内容");
scheduleFivesRow4.createCell(2).setCellValue("企业类别");
scheduleFivesRow4.createCell(3).setCellValue("主建或参建");
scheduleFivesRow4.createCell(4).setCellValue("加分依据");
scheduleFivesRow4.createCell(5).setCellValue("公布超始时间\n" +"(年/月/日)");
scheduleFivesRow4.createCell(6).setCellValue("公布结束时间\n" +"(年/月/日)");
scheduleFivesRow4.createCell(7).setCellValue("公布周期(年)");
scheduleFivesRow4.createCell(8).setCellValue("分值(分)");
scheduleFivesRow4.createCell(9).setCellValue("加分内容详述");
//给指定行列设置单元格式
styleMultipleCells(wb,sheet5,3,3,0,9,getCellStyleTextTitle(wb));
scheduleFivesRow4.setHeightInPoints(60);
int rowi = 3;
int rowNum = 0;
if (faithgoodinfosByAdded !=null){
for (Faithgoodinfo faithgoodinfo:faithgoodinfosByAdded) {
Row scheduleFivesRow5 = sheet5.createRow(rowi+1);
scheduleFivesRow5.createCell(0).setCellValue(rowi-2);
scheduleFivesRow5.createCell(1).setCellValue(stringWrap(faithgoodinfo.getProjectName(),50));
scheduleFivesRow5.createCell(2).setCellValue(faithgoodinfo.getProjectType());
Faithscoredict faithscoredict = faithscoredictDao.selectByPrimaryKey(faithgoodinfo.getScoreId());
if (Double.parseDouble(faithscoredict.getValue())/faithgoodinfo.getScoreValue()==2){
scheduleFivesRow5.createCell(3).setCellValue("参建");
}else {
scheduleFivesRow5.createCell(3).setCellValue("主建");
}
scheduleFivesRow5.createCell(4).setCellValue(faithgoodinfo.getApplicatMatter());
scheduleFivesRow5.createCell(5).setCellValue(formatDate(faithgoodinfo.getPromptStart(),"yyyy/MM/dd"));
scheduleFivesRow5.createCell(6).setCellValue(formatDate(faithgoodinfo.getPromptEnd(),"yyyy/MM/dd"));
scheduleFivesRow5.createCell(7).setCellValue(faithgoodinfo.getBonusPeriod());
scheduleFivesRow5.createCell(8).setCellValue(faithgoodinfo.getScoreValue());
scheduleFivesRow5.createCell(9).setCellValue(stringWrap(faithgoodinfo.getContent(),50));
rowi++;
}
rowNum = faithgoodinfosByAdded.size();
styleMultipleCells(wb,sheet5,4,3+rowNum,0,9,getCellStyleText(wb));
}
Row scheduleFivesRow6 = sheet5.createRow(rowNum+4);
Cell cell39 = scheduleFivesRow6.createCell(0);
sheet5.addMergedRegion(new CellRangeAddress(rowNum+4,rowNum+4,0,9));
cell39.setCellValue("说明:\n" +
"1、良好信息申报表每条为一个加分项。\n" +
"2、企业类别包括施工、设计、监理、勘察、招标代理、造价咨询、检测和建设单位。\n" +
"3、需提供良好信息证明的文件,奖励类除提供奖状外,还需提供证明奖状真实性的颁奖文件或颁奖新闻信息(核查原件,收复印件1份)。");
cell39.setCellStyle(getCellStyleReceiptCenter(wb));
scheduleFivesRow6.setHeightInPoints(80);
//宽度自适应
for (int j=0;j<=9;j++){
sheet5.autoSizeColumn(j, true);
}
System.out.println("+++++++++++++++++++++附表5 end++++++++++++++++++++++++");
}
wb.write(outputStream);
outputStream.flush();
outputStream.close();
System.out.println("+++++++++++++++++++++企业录入申请表 end++++++++++++++++++++++++");
} catch (Exception e) {
e.printStackTrace();
}
}
private void traverseCompanyProjects(Sheet sheet4, List<CompanyProject> companyProjects, int rowi) {
for (CompanyProject companyProject: companyProjects) {
Row scheduleFourRow5 = sheet4.createRow(rowi+1);
scheduleFourRow5.createCell(0).setCellValue(rowi-2);
scheduleFourRow5.createCell(1).setCellValue(companyProject.getProjectName());
scheduleFourRow5.createCell(2).setCellValue(companyProject.getTypeName());
scheduleFourRow5.createCell(3).setCellValue(formatDate(companyProject.getCompletedTime(),"yyyy年MM月dd日"));
scheduleFourRow5.createCell(4).setCellValue(companyProject.getGreenSpace().toString());
scheduleFourRow5.createCell(5).setCellValue(companyProject.getSettleAmount().toString());
scheduleFourRow5.createCell(6).setCellValue(companyProject.getInvestment());
scheduleFourRow5.createCell(7).setCellValue(companyProject.getPrincipal());
scheduleFourRow5.createCell(8).setCellValue(stringWrap(companyProject.getProjectDetails(),50));
rowi++;
}
}
private void traverseCompanyManMes(Sheet sheet, List<CompanyManMes> CompanyManMes, int rowi) {
for (CompanyManMes companyManMes: CompanyManMes) {
CompanyMan companyMan = companyManDao.selectByPrimaryKey(companyManMes.getManId());
Row scheduleThreeRow5 = sheet.createRow(rowi+1);
scheduleThreeRow5.createCell(0).setCellValue(rowi-2);
scheduleThreeRow5.createCell(1).setCellValue(companyMan.getName());
scheduleThreeRow5.createCell(2).setCellValue(companyMan.getSex());
scheduleThreeRow5.createCell(3).setCellValue(companyMan.getIdNumber());
scheduleThreeRow5.createCell(4).setCellValue(companyManMes.getCompanyType());
scheduleThreeRow5.createCell(5).setCellValue(companyManMes.getOccupation());
scheduleThreeRow5.createCell(6).setCellValue(certificateLevel(companyManMes.getOccupationLevel()));
rowi++;
}
}
private void traverseCompanyQuals(List<CompanyQual> companyQuals, Sheet sheet, int rowi) {
for (CompanyQual companyQual: companyQuals) {
Row scheduleTwoRow5 = sheet.createRow(rowi+1);
scheduleTwoRow5.createCell(0).setCellValue(rowi-2);
scheduleTwoRow5.createCell(1).setCellValue(companyQual.getQualType());
scheduleTwoRow5.createCell(2).setCellValue(companyQual.getQualLevel());
scheduleTwoRow5.createCell(3).setCellValue(companyQual.getCertificateNum());
scheduleTwoRow5.createCell(4).setCellValue(companyQual.getIssuingOrgan());
scheduleTwoRow5.createCell(5).setCellValue(formatDate(companyQual.getGrantDate(),"yyyy/MM/dd"));
scheduleTwoRow5.createCell(6).setCellValue(formatDate(companyQual.getValidityTime(),"yyyy/MM/dd"));
rowi++;
}
}
/**
* 技术人员证书分等级
* @param str
* @return
*/
private String certificateLevel(String str){
String string ="";
if (str.equals("1")) {
string = "高级";
}
if (str.equals("2")) {
string = "中级";
}
if (str.equals("3")) {
string = "初级";
}
if (str.equals("4")) {
string = "一级";
}
if (str.equals("5")) {
string = "二级";
}
if (str.equals("6")) {
string = "三级";
}
if (str.equals("7")) {
string = "未分级";
}
return string;
}
/**
* 计算图片个数
* @param str
* @return
*/
private int calculatePicture(String str){
int num =0;
if (str != null && !str.trim().equals("")){
String[] split = str.split(",");
for (String s:split) {
if (s != null && !str.trim().equals(""))
num++;
}
}
return num;
}
}