package com.isoftstone.pms.back.util;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Workbook;
public class ExcelPoiTest {
public static void main(String[] args) throws IOException {
// 创建Excel的工作书册 Workbook,对应到一个excel文档
exportExcel();
}
public static void exportExcel() throws FileNotFoundException, IOException {
HSSFWorkbook wb = new HSSFWorkbook();
// 创建Excel的工作sheet,对应到一个excel文档的tab
HSSFSheet sheet = wb.createSheet("项目现金流表");
// 设置excel每列宽度
sheet.setColumnWidth(1, 6000);
sheet.setColumnWidth(2, 4000);
HSSFFont font = wb.createFont();
font.setFontName("宋体");
font.setBoldweight((short) 100);
font.setFontHeight((short) 200);
font.setColor(HSSFColor.BLACK.index);
// 创建单元格样式
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置边框
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
style.setFont(font);// 设置字体
//黄色背景-字体居中-start
HSSFCellStyle style1 = wb.createCellStyle();
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style1.setFillForegroundColor(HSSFColor.YELLOW.index);
style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置边框
style1.setBottomBorderColor(HSSFColor.BLACK.index);
style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
HSSFFont font1 = wb.createFont();
font1.setFontName("宋体");
font1.setBoldweight((short) 100);
font1.setFontHeight((short) 200);
font1.setColor(HSSFColor.BLACK.index);
style1.setFont(font1);// 设置字体
//黄色背景-字体居中-end
// 创建Excel的sheet的一行
String[] headers = { "烟台测试项目", "马维刚", "马维刚", "张瑾"};
HSSFRow row = sheet.createRow(0);
row.setHeight((short) 300);// 设定行的高度
// 创建一个Excel的单元格
HSSFCell cell0 = row.createCell(0);
HSSFCellStyle style0 = wb.createCellStyle();
style0.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style0.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style0.setFillForegroundColor(HSSFColor.WHITE.index);
style0.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置边框
style0.setBottomBorderColor(HSSFColor.WHITE.index);
style0.setBorderBottom(HSSFCellStyle.NO_FILL);
style0.setBorderLeft(HSSFCellStyle.NO_FILL);
style0.setBorderRight(HSSFCellStyle.NO_FILL);
style0.setBorderTop(HSSFCellStyle.NO_FILL);
cell0.setCellStyle(style0);
HSSFCell cell = row.createCell(1);
// 给Excel的单元格设置样式和赋值
cell.setCellStyle(style);
cell.setCellValue("项目名称");
// 创建一个Excel的单元格
HSSFCell cell1 = row.createCell(2);
// 合并单元格(startRow,endRow,startColumn,endColumn)
//sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
// 给Excel的单元格设置样式和赋值
cell1.setCellStyle(style1);
cell1.setCellValue(headers[0]);
// 创建一个Excel的单元格
HSSFCell cell2 = row.createCell(3);
cell2.setCellStyle(style);
cell2.setCellValue("制表人");
// 创建一个Excel的单元格
HSSFCell cell3 = row.createCell(4);
cell3.setCellStyle(style1);
cell3.setCellValue(headers[1]);
HSSFRow row1 = sheet.createRow(1);
row.setHeight((short) 300);// 设定行的高度
// 创建一个Excel的单元格
HSSFCell cell4 = row1.createCell(1);
// 给Excel的单元格设置样式和赋值
cell4.setCellStyle(style);
cell4.setCellValue("项目经理");
// 创建一个Excel的单元格
HSSFCell cell5 = row1.createCell(2);
// 合并单元格(startRow,endRow,startColumn,endColumn)
//sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
// 给Excel的单元格设置样式和赋值
cell5.setCellStyle(style1);
cell5.setCellValue(headers[2]);
// 创建一个Excel的单元格
HSSFCell cell6 = row1.createCell(3);
cell6.setCellStyle(style);
cell6.setCellValue("项目QA");
// 创建一个Excel的单元格
HSSFCell cell7 = row1.createCell(4);
cell7.setCellStyle(style1);
cell7.setCellValue(headers[3]);
// 合并单元格(startRow,endRow,startColumn,endColumn)
sheet.addMergedRegion(new CellRangeAddress(3, 3, 1, 12));
HSSFRow row3 = sheet.createRow(3);
HSSFCell cell8 = row3.createCell(1);
HSSFCellStyle style3 = wb.createCellStyle();
style3.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style3.setFillForegroundColor(HSSFColor.WHITE.index);
style3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
HSSFFont font3 = wb.createFont();
font3.setFontName("宋体");
font3.setBoldweight((short) 150);
font3.setFontHeight((short) 300);
font3.setColor(HSSFColor.BLACK.index);
// font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
style3.setFont(font3);// 设置字体
cell8.setCellStyle(style3);
cell8.setCellValue("项目现金流表(单位:元人民币)");
//设置项目周期
HSSFRow row4 = sheet.createRow(4);
//边框加粗 -start
HSSFCellStyle style4 = wb.createCellStyle();
style4.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style4.setFillForegroundColor(HSSFColor.AQUA.index);
style4.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style4.setBottomBorderColor(HSSFColor.BLACK.index);
style4.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
style4.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
style4.setBorderRight(HSSFCellStyle.BORDER_THIN);
style4.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
style4.setFont(font);// 设置字体
//边框加粗-end
//边框加粗 左右边不加粗 -start
HSSFCellStyle style12 = wb.createCellStyle();
style12.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style12.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style12.setFillForegroundColor(HSSFColor.AQUA.index);
style12.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style12.setBottomBorderColor(HSSFColor.BLACK.index);
style12.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
style12.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style12.setBorderRight(HSSFCellStyle.BORDER_THIN);
style12.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
style12.setFont(font);// 设置字体
//边框加粗-end
HSSFRow row5 = sheet.createRow(5);
HSSFCellStyle style5 = wb.createCellStyle();
style5.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style5.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style5.setFillForegroundColor(HSSFColor.WHITE.index);
style5.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style5.setFont(font);// 设置字体
//流出小计
HSSFRow row6 = sheet.createRow(6);
HSSFCellStyle style6 = wb.createCellStyle();
style6.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style6.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style6.setFillForegroundColor(HSSFColor.AQUA.index);
style6.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style6.setFont(font);// 设置字体
//灰色背景红色字体-start
HSSFCellStyle style7 = wb.createCellStyle();
style7.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style7.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style7.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
style7.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style7.setBottomBorderColor(HSSFColor.BLACK.index);
style7.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style7.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style7.setBorderRight(HSSFCellStyle.BORDER_THIN);
style7.setBorderTop(HSSFCellStyle.BORDER_THIN);
HSSFFont font7 = wb.createFont();
font7.setFontName("宋体");
font7.setBoldweight((short) 100);
font7.setFontHeight((short) 200);
font7.setColor(HSSFColor.RED.index);
style7.setFont(font7);// 设置字体
//灰色背景红色字体-end
// 创建单元格样式
HSSFCellStyle style8 = wb.createCellStyle();
style8.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style8.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style8.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
style8.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置边框
style8.setBottomBorderColor(HSSFColor.BLACK.index);
style8.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style8.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style8.setBorderRight(HSSFCellStyle.BORDER_THIN);
style8.setBorderTop(HSSFCellStyle.BORDER_THIN);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
style8.setFont(font);// 设置字体
//白色背景色 黑色字体-start
HSSFCellStyle style9 = wb.createCellStyle();
style9.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style9.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style9.setFillForegroundColor(HSSFColor.WHITE.index);
style9.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置边框
style9.setBottomBorderColor(HSSFColor.BLACK.index);
style9.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style9.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style9.setBorderRight(HSSFCellStyle.BORDER_THIN);
style9.setBorderTop(HSSFCellStyle.BORDER_THIN);
HSSFFont font9 = wb.createFont();
font9.setFontName("宋体");
font9.setBoldweight((short) 100);
font9.setFontHeight((short) 200);
font9.setColor(HSSFColor.BLACK.index);
style9.setFont(font9);// 设置字体
//白色背景色 黑色字体 end
//蓝色背景色黑色加粗字体居左 -start
HSSFCellStyle style10 = wb.createCellStyle();
style10.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style10.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style10.setFillForegroundColor(HSSFColor.AQUA.index);
style10.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style10.setBottomBorderColor(HSSFColor.BLACK.index);
style10.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
style10.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
style10.setBorderRight(HSSFCellStyle.BORDER_THIN);
style10.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
HSSFFont font10 = wb.createFont();
font10.setFontName("宋体");
font10.setBoldweight((short) 100);
font10.setFontHeight((short) 200);
font10.setColor(HSSFColor.BLACK.index);
font10.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
style10.setFont(font10);// 设置字体
//蓝色背景色黑色加粗字体居左 -end
//蓝色背景色红色加粗字体居中 -start
HSSFCellStyle style11 = wb.createCellStyle();
style11.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style11.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style11.setFillForegroundColor(HSSFColor.AQUA.index);
style11.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style11.setBottomBorderColor(HSSFColor.BLACK.index);
style11.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
style11.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style11.setBorderRight(HSSFCellStyle.BORDER_THIN);
style11.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
HSSFFont font11 = wb.createFont();
font11.setFontName("宋体");
font11.setBoldweight((short) 100);
font11.setFontHeight((short) 200);
font11.setColor(HSSFColor.RED.index);
font11.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
style11.setFont(font11);// 设置字体
//蓝色背景色红色加粗字体居中 -end
HSSFRow row7 = sheet.createRow(7);
//人力成本
HSSFRow row8 = sheet.createRow(8);
//项目费用
HSSFRow row9 = sheet.createRow(9);
//第三方费用
HSSFRow row10 = sheet.createRow(10);
//第三方软硬件采购费用
HSSFRow row11 = sheet.createRow(11);
HSSFRow row12 = sheet.createRow(12);//留白
HSSFRow row13 = sheet.createRow(13);//留白
//第三方服务费用
HSSFRow row14 = sheet.createRow(14);
HSSFRow row15 = sheet.createRow(15);//留白
HSSFRow row16 = sheet.createRow(16);//留白
HSSFRow row17 = sheet.createRow(17);//留白
//流入小计
HSSFRow row18 = sheet.createRow(18);
HSSFRow row19 = sheet.createRow(19);//留白
//实际是获取项目的计划周期 --例如从2015年11月1日起到2016年8月结束
for (int i = 1; i < 13; i++) {
HSSFCell cell9 = row4.createCell(i);
cell9.setCellStyle(style4);
HSSFCell cell10 = row5.createCell(i);
cell10.setCellStyle(style5);
sheet.setColumnWidth(i, 6000);
//流出小计
HSSFCell cell11 = row6.createCell(i);
cell11.setCellStyle(style10);
cell11.setCellValue("流出小计");
HSSFCell cell12=row7.createCell(i);
cell12.setCellStyle(style5);
//人力成本
HSSFCell cell13 = row8.createCell(i);
cell13.setCellStyle(style8);
cell13.setCellValue("1)人力成本");
//项目费用
HSSFCell cell14 = row9.createCell(i);
cell14.setCellStyle(style8);
cell14.setCellValue("2)项目费用");
//第三方
HSSFCell cell15 = row10.createCell(i);
cell15.setCellStyle(style8);
cell15.setCellValue("3)第三方费用");
//第三方软硬件采购费用
HSSFCell cell16 = row11.createCell(i);
cell16.setCellStyle(style9);
cell16.setCellValue("第三方软硬件采购费用");
HSSFCell cell17 = row12.createCell(i);//留白
cell17.setCellStyle(style9);
cell17.setCellValue("");
HSSFCell cell18 = row13.createCell(i);//留白
cell18.setCellStyle(style9);
cell18.setCellValue("");
//第三方服务费用
HSSFCell cell19 = row14.createCell(i);
cell19.setCellStyle(style9);
cell19.setCellValue("第三方服务费用");
HSSFCell cell20 = row15.createCell(i);//留白
cell20.setCellStyle(style9);
cell20.setCellValue("");
HSSFCell cell21 = row16.createCell(i);//留白
cell21.setCellStyle(style9);
cell21.setCellValue("");
HSSFCell cell22 = row17.createCell(i);//留白
cell22.setCellStyle(style5);
HSSFCell cell23 = row18.createCell(i);//流入小计
cell23.setCellStyle(style10);
cell23.setCellValue("流入小计");
HSSFCell cell24 = row19.createCell(i);//留白
cell24.setCellStyle(style5);
if(i>1){
sheet.setColumnWidth(i, 4000);
cell9.setCellStyle(style12);
cell9.setCellValue("M-"+(i-2));
cell10.setCellValue((i-1)+"月前累计");
cell11.setCellStyle(style11);
cell11.setCellValue(2220+i);//测试金额
cell13.setCellStyle(style7);
cell13.setCellValue(5220+i);//测试人力成本金额
cell14.setCellStyle(style7);
cell14.setCellValue(6220+i);//测试费用金额
cell15.setCellStyle(style7);
cell15.setCellValue(7220+i);//测试第三方金额
cell16.setCellStyle(style1);
cell16.setCellValue(0);//第三方软硬件采购费用
cell17.setCellStyle(style1);
cell17.setCellValue(0);//留白
cell18.setCellStyle(style1);
cell18.setCellValue(0);//留白
cell19.setCellStyle(style1);
cell19.setCellValue(0);//第三方服务费用
cell20.setCellStyle(style1);
cell20.setCellValue(0);//留白
cell21.setCellStyle(style1);
cell21.setCellValue(0);//留白
cell23.setCellStyle(style11);
cell23.setCellValue(0);//流入小计
}
if(i>2){
cell10.setCellValue("2015年"+(i-2)+"月");
}
}
//黄色背景-字体居左-start
HSSFCellStyle style13 = wb.createCellStyle();
style13.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style13.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style13.setFillForegroundColor(HSSFColor.YELLOW.index);
style13.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置边框
style13.setBottomBorderColor(HSSFColor.BLACK.index);
style13.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style13.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style13.setBorderRight(HSSFCellStyle.BORDER_THIN);
style13.setBorderTop(HSSFCellStyle.BORDER_THIN);
style13.setFont(font1);// 设置字体
//黄色背景-字体居左-end
//流入详情
for (int j = 20; j < 29; j++) {
HSSFRow rowtemp = sheet.createRow(j);//留白
for (int k = 1; k < 13; k++) {
HSSFCell cell1temp = rowtemp.createCell(k);
cell1temp.setCellStyle(style13);
if(j==20 &&k==1){
cell1temp.setCellValue("第一年立项成本");
}
}
}
//留白
HSSFRow row29 = sheet.createRow(29);//留白
HSSFRow row30= sheet.createRow(30);//留白
//当月净现金流
HSSFRow row31 = sheet.createRow(31);
HSSFRow row32 = sheet.createRow(32);//留白
//现金流累计
HSSFRow row33 = sheet.createRow(33);
for (int k = 1; k < 13; k++) {
HSSFCell cell1temp29 = row29.createCell(k);
HSSFCell cell1temp30 = row30.createCell(k);
HSSFCell cell1temp31 = row31.createCell(k);
HSSFCell cell1temp32 = row32.createCell(k);
HSSFCell cell1temp33 = row33.createCell(k);
cell1temp29.setCellStyle(style5);
cell1temp30.setCellStyle(style5);
cell1temp31.setCellStyle(style10);
cell1temp31.setCellValue("当月净现金流");
cell1temp32.setCellStyle(style5);
cell1temp33.setCellStyle(style10);
cell1temp33.setCellValue("现金流累计");
if(k>1){
cell1temp31.setCellStyle(style11);
cell1temp31.setCellValue(0);//流入小计
cell1temp33.setCellStyle(style11);
cell1temp33.setCellValue(0);//流入小计
}
}
FileOutputStream os = new FileOutputStream("D:/workbook.xls");
wb.write(os);
os.close();
}
}
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Workbook;
public class ExcelPoiTest {
public static void main(String[] args) throws IOException {
// 创建Excel的工作书册 Workbook,对应到一个excel文档
exportExcel();
}
public static void exportExcel() throws FileNotFoundException, IOException {
HSSFWorkbook wb = new HSSFWorkbook();
// 创建Excel的工作sheet,对应到一个excel文档的tab
HSSFSheet sheet = wb.createSheet("项目现金流表");
// 设置excel每列宽度
sheet.setColumnWidth(1, 6000);
sheet.setColumnWidth(2, 4000);
HSSFFont font = wb.createFont();
font.setFontName("宋体");
font.setBoldweight((short) 100);
font.setFontHeight((short) 200);
font.setColor(HSSFColor.BLACK.index);
// 创建单元格样式
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置边框
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
style.setFont(font);// 设置字体
//黄色背景-字体居中-start
HSSFCellStyle style1 = wb.createCellStyle();
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style1.setFillForegroundColor(HSSFColor.YELLOW.index);
style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置边框
style1.setBottomBorderColor(HSSFColor.BLACK.index);
style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
HSSFFont font1 = wb.createFont();
font1.setFontName("宋体");
font1.setBoldweight((short) 100);
font1.setFontHeight((short) 200);
font1.setColor(HSSFColor.BLACK.index);
style1.setFont(font1);// 设置字体
//黄色背景-字体居中-end
// 创建Excel的sheet的一行
String[] headers = { "烟台测试项目", "马维刚", "马维刚", "张瑾"};
HSSFRow row = sheet.createRow(0);
row.setHeight((short) 300);// 设定行的高度
// 创建一个Excel的单元格
HSSFCell cell0 = row.createCell(0);
HSSFCellStyle style0 = wb.createCellStyle();
style0.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style0.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style0.setFillForegroundColor(HSSFColor.WHITE.index);
style0.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置边框
style0.setBottomBorderColor(HSSFColor.WHITE.index);
style0.setBorderBottom(HSSFCellStyle.NO_FILL);
style0.setBorderLeft(HSSFCellStyle.NO_FILL);
style0.setBorderRight(HSSFCellStyle.NO_FILL);
style0.setBorderTop(HSSFCellStyle.NO_FILL);
cell0.setCellStyle(style0);
HSSFCell cell = row.createCell(1);
// 给Excel的单元格设置样式和赋值
cell.setCellStyle(style);
cell.setCellValue("项目名称");
// 创建一个Excel的单元格
HSSFCell cell1 = row.createCell(2);
// 合并单元格(startRow,endRow,startColumn,endColumn)
//sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
// 给Excel的单元格设置样式和赋值
cell1.setCellStyle(style1);
cell1.setCellValue(headers[0]);
// 创建一个Excel的单元格
HSSFCell cell2 = row.createCell(3);
cell2.setCellStyle(style);
cell2.setCellValue("制表人");
// 创建一个Excel的单元格
HSSFCell cell3 = row.createCell(4);
cell3.setCellStyle(style1);
cell3.setCellValue(headers[1]);
HSSFRow row1 = sheet.createRow(1);
row.setHeight((short) 300);// 设定行的高度
// 创建一个Excel的单元格
HSSFCell cell4 = row1.createCell(1);
// 给Excel的单元格设置样式和赋值
cell4.setCellStyle(style);
cell4.setCellValue("项目经理");
// 创建一个Excel的单元格
HSSFCell cell5 = row1.createCell(2);
// 合并单元格(startRow,endRow,startColumn,endColumn)
//sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
// 给Excel的单元格设置样式和赋值
cell5.setCellStyle(style1);
cell5.setCellValue(headers[2]);
// 创建一个Excel的单元格
HSSFCell cell6 = row1.createCell(3);
cell6.setCellStyle(style);
cell6.setCellValue("项目QA");
// 创建一个Excel的单元格
HSSFCell cell7 = row1.createCell(4);
cell7.setCellStyle(style1);
cell7.setCellValue(headers[3]);
// 合并单元格(startRow,endRow,startColumn,endColumn)
sheet.addMergedRegion(new CellRangeAddress(3, 3, 1, 12));
HSSFRow row3 = sheet.createRow(3);
HSSFCell cell8 = row3.createCell(1);
HSSFCellStyle style3 = wb.createCellStyle();
style3.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style3.setFillForegroundColor(HSSFColor.WHITE.index);
style3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
HSSFFont font3 = wb.createFont();
font3.setFontName("宋体");
font3.setBoldweight((short) 150);
font3.setFontHeight((short) 300);
font3.setColor(HSSFColor.BLACK.index);
// font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
style3.setFont(font3);// 设置字体
cell8.setCellStyle(style3);
cell8.setCellValue("项目现金流表(单位:元人民币)");
//设置项目周期
HSSFRow row4 = sheet.createRow(4);
//边框加粗 -start
HSSFCellStyle style4 = wb.createCellStyle();
style4.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style4.setFillForegroundColor(HSSFColor.AQUA.index);
style4.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style4.setBottomBorderColor(HSSFColor.BLACK.index);
style4.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
style4.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
style4.setBorderRight(HSSFCellStyle.BORDER_THIN);
style4.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
style4.setFont(font);// 设置字体
//边框加粗-end
//边框加粗 左右边不加粗 -start
HSSFCellStyle style12 = wb.createCellStyle();
style12.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style12.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style12.setFillForegroundColor(HSSFColor.AQUA.index);
style12.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style12.setBottomBorderColor(HSSFColor.BLACK.index);
style12.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
style12.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style12.setBorderRight(HSSFCellStyle.BORDER_THIN);
style12.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
style12.setFont(font);// 设置字体
//边框加粗-end
HSSFRow row5 = sheet.createRow(5);
HSSFCellStyle style5 = wb.createCellStyle();
style5.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style5.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style5.setFillForegroundColor(HSSFColor.WHITE.index);
style5.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style5.setFont(font);// 设置字体
//流出小计
HSSFRow row6 = sheet.createRow(6);
HSSFCellStyle style6 = wb.createCellStyle();
style6.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style6.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style6.setFillForegroundColor(HSSFColor.AQUA.index);
style6.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style6.setFont(font);// 设置字体
//灰色背景红色字体-start
HSSFCellStyle style7 = wb.createCellStyle();
style7.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style7.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style7.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
style7.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style7.setBottomBorderColor(HSSFColor.BLACK.index);
style7.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style7.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style7.setBorderRight(HSSFCellStyle.BORDER_THIN);
style7.setBorderTop(HSSFCellStyle.BORDER_THIN);
HSSFFont font7 = wb.createFont();
font7.setFontName("宋体");
font7.setBoldweight((short) 100);
font7.setFontHeight((short) 200);
font7.setColor(HSSFColor.RED.index);
style7.setFont(font7);// 设置字体
//灰色背景红色字体-end
// 创建单元格样式
HSSFCellStyle style8 = wb.createCellStyle();
style8.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style8.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style8.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
style8.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置边框
style8.setBottomBorderColor(HSSFColor.BLACK.index);
style8.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style8.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style8.setBorderRight(HSSFCellStyle.BORDER_THIN);
style8.setBorderTop(HSSFCellStyle.BORDER_THIN);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
style8.setFont(font);// 设置字体
//白色背景色 黑色字体-start
HSSFCellStyle style9 = wb.createCellStyle();
style9.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style9.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style9.setFillForegroundColor(HSSFColor.WHITE.index);
style9.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置边框
style9.setBottomBorderColor(HSSFColor.BLACK.index);
style9.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style9.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style9.setBorderRight(HSSFCellStyle.BORDER_THIN);
style9.setBorderTop(HSSFCellStyle.BORDER_THIN);
HSSFFont font9 = wb.createFont();
font9.setFontName("宋体");
font9.setBoldweight((short) 100);
font9.setFontHeight((short) 200);
font9.setColor(HSSFColor.BLACK.index);
style9.setFont(font9);// 设置字体
//白色背景色 黑色字体 end
//蓝色背景色黑色加粗字体居左 -start
HSSFCellStyle style10 = wb.createCellStyle();
style10.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style10.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style10.setFillForegroundColor(HSSFColor.AQUA.index);
style10.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style10.setBottomBorderColor(HSSFColor.BLACK.index);
style10.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
style10.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
style10.setBorderRight(HSSFCellStyle.BORDER_THIN);
style10.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
HSSFFont font10 = wb.createFont();
font10.setFontName("宋体");
font10.setBoldweight((short) 100);
font10.setFontHeight((short) 200);
font10.setColor(HSSFColor.BLACK.index);
font10.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
style10.setFont(font10);// 设置字体
//蓝色背景色黑色加粗字体居左 -end
//蓝色背景色红色加粗字体居中 -start
HSSFCellStyle style11 = wb.createCellStyle();
style11.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style11.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style11.setFillForegroundColor(HSSFColor.AQUA.index);
style11.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style11.setBottomBorderColor(HSSFColor.BLACK.index);
style11.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
style11.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style11.setBorderRight(HSSFCellStyle.BORDER_THIN);
style11.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
HSSFFont font11 = wb.createFont();
font11.setFontName("宋体");
font11.setBoldweight((short) 100);
font11.setFontHeight((short) 200);
font11.setColor(HSSFColor.RED.index);
font11.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
style11.setFont(font11);// 设置字体
//蓝色背景色红色加粗字体居中 -end
HSSFRow row7 = sheet.createRow(7);
//人力成本
HSSFRow row8 = sheet.createRow(8);
//项目费用
HSSFRow row9 = sheet.createRow(9);
//第三方费用
HSSFRow row10 = sheet.createRow(10);
//第三方软硬件采购费用
HSSFRow row11 = sheet.createRow(11);
HSSFRow row12 = sheet.createRow(12);//留白
HSSFRow row13 = sheet.createRow(13);//留白
//第三方服务费用
HSSFRow row14 = sheet.createRow(14);
HSSFRow row15 = sheet.createRow(15);//留白
HSSFRow row16 = sheet.createRow(16);//留白
HSSFRow row17 = sheet.createRow(17);//留白
//流入小计
HSSFRow row18 = sheet.createRow(18);
HSSFRow row19 = sheet.createRow(19);//留白
//实际是获取项目的计划周期 --例如从2015年11月1日起到2016年8月结束
for (int i = 1; i < 13; i++) {
HSSFCell cell9 = row4.createCell(i);
cell9.setCellStyle(style4);
HSSFCell cell10 = row5.createCell(i);
cell10.setCellStyle(style5);
sheet.setColumnWidth(i, 6000);
//流出小计
HSSFCell cell11 = row6.createCell(i);
cell11.setCellStyle(style10);
cell11.setCellValue("流出小计");
HSSFCell cell12=row7.createCell(i);
cell12.setCellStyle(style5);
//人力成本
HSSFCell cell13 = row8.createCell(i);
cell13.setCellStyle(style8);
cell13.setCellValue("1)人力成本");
//项目费用
HSSFCell cell14 = row9.createCell(i);
cell14.setCellStyle(style8);
cell14.setCellValue("2)项目费用");
//第三方
HSSFCell cell15 = row10.createCell(i);
cell15.setCellStyle(style8);
cell15.setCellValue("3)第三方费用");
//第三方软硬件采购费用
HSSFCell cell16 = row11.createCell(i);
cell16.setCellStyle(style9);
cell16.setCellValue("第三方软硬件采购费用");
HSSFCell cell17 = row12.createCell(i);//留白
cell17.setCellStyle(style9);
cell17.setCellValue("");
HSSFCell cell18 = row13.createCell(i);//留白
cell18.setCellStyle(style9);
cell18.setCellValue("");
//第三方服务费用
HSSFCell cell19 = row14.createCell(i);
cell19.setCellStyle(style9);
cell19.setCellValue("第三方服务费用");
HSSFCell cell20 = row15.createCell(i);//留白
cell20.setCellStyle(style9);
cell20.setCellValue("");
HSSFCell cell21 = row16.createCell(i);//留白
cell21.setCellStyle(style9);
cell21.setCellValue("");
HSSFCell cell22 = row17.createCell(i);//留白
cell22.setCellStyle(style5);
HSSFCell cell23 = row18.createCell(i);//流入小计
cell23.setCellStyle(style10);
cell23.setCellValue("流入小计");
HSSFCell cell24 = row19.createCell(i);//留白
cell24.setCellStyle(style5);
if(i>1){
sheet.setColumnWidth(i, 4000);
cell9.setCellStyle(style12);
cell9.setCellValue("M-"+(i-2));
cell10.setCellValue((i-1)+"月前累计");
cell11.setCellStyle(style11);
cell11.setCellValue(2220+i);//测试金额
cell13.setCellStyle(style7);
cell13.setCellValue(5220+i);//测试人力成本金额
cell14.setCellStyle(style7);
cell14.setCellValue(6220+i);//测试费用金额
cell15.setCellStyle(style7);
cell15.setCellValue(7220+i);//测试第三方金额
cell16.setCellStyle(style1);
cell16.setCellValue(0);//第三方软硬件采购费用
cell17.setCellStyle(style1);
cell17.setCellValue(0);//留白
cell18.setCellStyle(style1);
cell18.setCellValue(0);//留白
cell19.setCellStyle(style1);
cell19.setCellValue(0);//第三方服务费用
cell20.setCellStyle(style1);
cell20.setCellValue(0);//留白
cell21.setCellStyle(style1);
cell21.setCellValue(0);//留白
cell23.setCellStyle(style11);
cell23.setCellValue(0);//流入小计
}
if(i>2){
cell10.setCellValue("2015年"+(i-2)+"月");
}
}
//黄色背景-字体居左-start
HSSFCellStyle style13 = wb.createCellStyle();
style13.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style13.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style13.setFillForegroundColor(HSSFColor.YELLOW.index);
style13.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置边框
style13.setBottomBorderColor(HSSFColor.BLACK.index);
style13.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style13.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style13.setBorderRight(HSSFCellStyle.BORDER_THIN);
style13.setBorderTop(HSSFCellStyle.BORDER_THIN);
style13.setFont(font1);// 设置字体
//黄色背景-字体居左-end
//流入详情
for (int j = 20; j < 29; j++) {
HSSFRow rowtemp = sheet.createRow(j);//留白
for (int k = 1; k < 13; k++) {
HSSFCell cell1temp = rowtemp.createCell(k);
cell1temp.setCellStyle(style13);
if(j==20 &&k==1){
cell1temp.setCellValue("第一年立项成本");
}
}
}
//留白
HSSFRow row29 = sheet.createRow(29);//留白
HSSFRow row30= sheet.createRow(30);//留白
//当月净现金流
HSSFRow row31 = sheet.createRow(31);
HSSFRow row32 = sheet.createRow(32);//留白
//现金流累计
HSSFRow row33 = sheet.createRow(33);
for (int k = 1; k < 13; k++) {
HSSFCell cell1temp29 = row29.createCell(k);
HSSFCell cell1temp30 = row30.createCell(k);
HSSFCell cell1temp31 = row31.createCell(k);
HSSFCell cell1temp32 = row32.createCell(k);
HSSFCell cell1temp33 = row33.createCell(k);
cell1temp29.setCellStyle(style5);
cell1temp30.setCellStyle(style5);
cell1temp31.setCellStyle(style10);
cell1temp31.setCellValue("当月净现金流");
cell1temp32.setCellStyle(style5);
cell1temp33.setCellStyle(style10);
cell1temp33.setCellValue("现金流累计");
if(k>1){
cell1temp31.setCellStyle(style11);
cell1temp31.setCellValue(0);//流入小计
cell1temp33.setCellStyle(style11);
cell1temp33.setCellValue(0);//流入小计
}
}
FileOutputStream os = new FileOutputStream("D:/workbook.xls");
wb.write(os);
os.close();
}
}