package common;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
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.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.RegionUtil;
public class Test {
public static String FORMULA = "FORMULA";
public static String NUMERIC = "NUMERIC";
public static String TEXT = "TEXT";
public static String BLANK = "BLANK";
public static short NONE = -1;
public static String PROJECT = "項目";
public static String BUDGET = "予算";
public static String ACTUAL = "実績";
public static String MATERIAL_FEE = "部材費";
public static String ORDERING_COST = "外注費";
public static String ROYALTY = "ロイヤリティ";
public static String VARING_FEE = "変動費";
public static String LABOUR_COST = "工費";
public static String FIXED_CHARGE = "固定費";
public static String VARING_FIXED = "変動費+固定費合計";
public static String OUT_CHARGE_FEE = "チャージ外経費";
public static String FREIGHT = "運賃";
public static String ADJUST_FEE = "現地調整費";
public static String OTHER = "その他";
public static String DIRECT_COST = "直接原価";
public static String BASE_PRICE = "基準売価（税別）";
public static String ADDED_VALUE = "付加価値";
public static String ADDED_VALUE_RATE = "付加価値率";
public static String SELL_PRICE = "売価";
//部材費填充颜色
public static short FILL_COLOR_BC = IndexedColors.LIGHT_YELLOW.getIndex();
//外注費填充颜色
public static short FILL_COLOR_WZ = IndexedColors.TURQUOISE.getIndex();
//工費填充颜色
public static short FILL_COLOR_GF = IndexedColors.LAVENDER.getIndex();
//基準売価（税別)填充颜色
public static short FILL_COLOR_MJ = IndexedColors.LIME.getIndex();
//灰色填充颜色
public static short FILL_COLOR_GREY = IndexedColors.GREY_25_PERCENT.getIndex();
//TOTAL 边框颜色
public static short TOTAL_BORDER_COLOR = IndexedColors.BLUE.getIndex();
//fontA 一般字体
public static HSSFFont fontA;
//fontB 红色加粗
public static HSSFFont fontB;
//fontC 黑色加粗
public static HSSFFont fontC;
//fontD 蓝色加粗
public static HSSFFont fontD;
// 水平居中
public static short H_CENTER = HSSFCellStyle.ALIGN_CENTER;
// 水平居左
public static short H_LEFT = HSSFCellStyle.ALIGN_LEFT;
private static String FONT_NAME = "ＭＳ Ｐゴシック";
private static String SHEET1_NAME = "sheet1";
private static String SHEET2_NAME = "予実管理";
public static void main(String[] args) throws Exception {
List<CostData> dataList = new ArrayList<CostData>();
CostData costData = makeData();
dataList.add(costData);
// 创建Excel Workbook
HSSFWorkbook wb = new HSSFWorkbook();
// 创建Excel worksheet
HSSFSheet sheet1 = wb.createSheet(SHEET1_NAME);
//poi宽度与excel宽度转换系数
short widthFactor = 256;
// 设置excel每列宽度
sheet1.setDefaultColumnWidth(10);
// 设置excel每行高度
sheet1.setDefaultRowHeight((short)270);
//根据数据算出需要多少行,然后生成指定的行数
int rows = 1;
for(int i=0;i<dataList.size();i++){
CostData data = dataList.get(i);
if(i>0){
data.setStartRow(rows);
}
rows += data.getTotalRowNum()+2;
}
//Total
int totalRowStart = rows;
rows += 4;
for(int i=0;i<rows;i++) {
sheet1.createRow(i);
}
sheet1.setColumnWidth(0, (short)(widthFactor*13.38)); //第一个参数代表列id(从0开始),第2个参数代表宽度值
sheet1.setColumnWidth(1, (short)(widthFactor*15.75));
sheet1.setColumnWidth(2, (short)(widthFactor*6.88));
sheet1.setColumnWidth(3, (short)(widthFactor*1.5));
sheet1.setColumnWidth(4, (short)(widthFactor*15));
sheet1.setColumnWidth(5, (short)(widthFactor*15));
fontA = wb.createFont();
fontA.setFontHeightInPoints((short)11);
fontA.setFontName(FONT_NAME);
fontB = wb.createFont();
fontB.setColor(HSSFColor.RED.index);
fontB.setFontHeightInPoints((short)11);
fontB.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
fontB.setFontName(FONT_NAME);
fontC = wb.createFont();
fontC.setColor(HSSFColor.BLACK.index);
fontC.setFontHeightInPoints((short)11);
fontC.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
fontC.setFontName(FONT_NAME);
fontD = wb.createFont();
fontD.setColor(HSSFColor.BLUE.index);
fontD.setFontHeightInPoints((short)11);
fontD.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
fontD.setFontName(FONT_NAME);
for(int i=0;i<dataList.size();i++){
CostData data = dataList.get(i);
createProjectRow(data,wb,sheet1);
createBCRow(data,wb,sheet1);
createWZRow(data,wb,sheet1);
createGFRow(data,wb,sheet1);
createOtherRow(data,wb,sheet1);
}
createTotalRow(wb,sheet1,totalRowStart,dataList);
//------------// 创建[予実管理] worksheet----------
HSSFSheet sheet2 = wb.createSheet(SHEET2_NAME);
// 设置excel每列宽度
sheet2.setDefaultColumnWidth(10);
// 设置excel每行高度
sheet2.setDefaultRowHeight((short)270);
//根据数据算出需要多少行,然后生成指定的行数
int rows2 = 4 + dataList.size();
for(int i=0;i<rows2;i++) {
sheet2.createRow(i);
}
sheet2.setColumnWidth(0, (short)(widthFactor*15));
sheet2.setColumnWidth(1, (short)(widthFactor*15));
createSheet2(dataList,wb,sheet2);
FileOutputStream os;
try {
os = new FileOutputStream("D:\\QiuGQ\\test.xls");
wb.write(os);
os.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
System.out.println("end...");
}
}
//sheet2
public static void createSheet2(List<CostData> dataList,HSSFWorkbook wb,HSSFSheet sheet) throws Exception{
int rowStart = 1;
//---------------変動費 ------------------------
int[] info = {rowStart,rowStart,2,7};
makeMergedCell(wb,sheet,info,VARING_FEE,fontA,NONE,H_CENTER);
//-----------------事業部--------------------------
info = new int[]{rowStart,rowStart+3,0,0};
makeMergedCell(wb,sheet,info,"事業部",fontA,NONE,H_CENTER);
//-----------------SOMO--------------------------
info = new int[]{rowStart,rowStart+3,1,1};
makeMergedCell(wb,sheet,info,"SO/MO",fontA,NONE,H_CENTER);
int curRow = 2;
List<String> titleList = new ArrayList<String>();
titleList.add("部材費");
titleList.add("外注費");
titleList.add("Total");
titleList.add("固定費");
titleList.add("チャージ外経費");
titleList.add("直接原価");
titleList.add("基準売価（税別）");
titleList.add("付加価値");
titleList.add("付加価値率");
int j=2;
for(int i=0;i<titleList.size();i++){
info = new int[]{curRow,curRow,j,j+1};
makeMergedCell(wb,sheet,info,titleList.get(i),fontA,NONE,H_CENTER);
info = new int[]{curRow+1,curRow+1,j,j};
makeMergedCell(wb,sheet,info,"予",fontA,NONE,H_CENTER);
info = new int[]{curRow+1,curRow+1,j+1,j+1};
makeMergedCell(wb,sheet,info,"実",fontA,NONE,H_CENTER);
j += 2;
}
CostData costData = dataList.get(0);
makeFomulaCell(wb,sheet,4,10,costData.getOutChargeAFormula(),fontA,NONE);
makeFomulaCell(wb,sheet,4,11,costData.getOutChargeBFormula(),fontA,NONE);
}
//項目行
public static void createProjectRow(CostData costData,HSSFWorkbook wb,HSSFSheet sheet) throws Exception{
int rowStart = costData.getStartRow();
//---------------項目------------------------
int[] info = {rowStart,rowStart+costData.getProRowNum()-1,0,2};
makeMergedCell(wb,sheet,info,PROJECT,fontA,NONE,H_CENTER);
//-----------------SOMO--------------------------
info = new int[]{rowStart+1,rowStart+1,4,5};
makeMergedCell(wb,sheet,info,costData.getSomoName(),fontA,NONE,H_CENTER);
info = new int[]{rowStart+2,rowStart+2,4,5};
makeMergedCell(wb,sheet,info,costData.getSomo(),fontA,NONE,H_CENTER);
List<String[]> yearList = costData.getYearList();
// 描画年
int col = 6;//从G列开始
for(int i=0;i<yearList.size();i++){
String[] yearSpan = yearList.get(i);
int colspan = Integer.parseInt(yearSpan[1]);
info = new int[]{rowStart+2,rowStart+2,col,col + colspan - 1};
makeMergedCell(wb,sheet,info,yearSpan[0],fontA,NONE,H_CENTER);
col += colspan;
}
//-----------------title(予算,実績,月份)--------------------------
makeTitleRow(costData,wb,sheet,rowStart+3);
}
//部材費关联行
public static void createBCRow(CostData costData,HSSFWorkbook wb,HSSFSheet sheet) throws Exception{
int rowStart = costData.getBcRowStart();
int rowEnd = rowStart + costData.getBcRowNum();//包含合计行
//---------------部材費项目左标题------------------------
int[] info = {rowStart,rowEnd,0,0};
makeMergedCell(wb,sheet,info,MATERIAL_FEE,fontA,FILL_COLOR_BC,H_CENTER);
//----------------数据行--------------------------
List<List<String>> bcList = costData.getBcList();
String formula = "";
int dataCol = 4;
//数据行
for(int i=0;i<bcList.size();i++){
List<String> rowData = bcList.get(i);
int row = rowStart + i;
int dataSize = rowData.size();
for(int j=0;j<rowData.size();j++){
String data = rowData.get(j);
//项目名称
if(j==0) {
info = new int[]{row,row,1,2};
makeMergedCell(wb,sheet,info,data,fontA,FILL_COLOR_BC,H_LEFT);
}else if(j==1){
//予算
makeNumericCell(wb,sheet,row,dataCol,data,fontA,NONE);
//実績
formula = "SUM(" + getCellName(row,dataCol+2) + ":" + getCellName(row,dataCol+1+dataSize-2) + ")";
makeFomulaCell(wb,sheet,row,dataCol+1,formula,fontA,NONE);
}
else {
//各合计值
makeNumericCell(wb,sheet,row,dataCol+j,data,fontA,NONE);
}
}
}
//合计行
List<String> rowData = bcList.get(0);
for(int j=0;j<rowData.size()+1;j++){
//项目名称
if(j==0) {
info = new int[]{rowEnd,rowEnd,1,2};
makeMergedCell(wb,sheet,info,MATERIAL_FEE,fontA,FILL_COLOR_BC,H_LEFT);
}else {
//SUM行
formula = "SUM(" + getCellName(rowStart,dataCol+j-1) + ":" + getCellName(rowEnd - 1,dataCol+j-1) + ")";
makeFomulaCell(wb,sheet,rowEnd,dataCol+j-1,formula,fontA,FILL_COLOR_BC);
}
}
}
//外注費关联行
public static void createWZRow(CostData costData,HSSFWorkbook wb,HSSFSheet sheet) throws Exception{
int rowStart = costData.getWzRowStart();
int rowEnd = rowStart + costData.getWzRowNum() - 1;//不包含合计行(変動費行)
//---------------外注費项目左标题------------------------
int[] info = {rowStart,rowEnd,0,0};
makeMergedCell(wb,sheet,info,ORDERING_COST,fontA,FILL_COLOR_WZ,H_CENTER);
//----------------数据行--------------------------
List<List<String>> wzList = costData.getWzList();
String formula = "";
int dataCol = 4;
//数据行
for(int i=0;i<wzList.size();i++){
List<String> rowData = wzList.get(i);
int row = rowStart + i;
int dataSize = rowData.size();
short lastRowColorIndex = NONE;
if(i==wzList.size()-1){
lastRowColorIndex = FILL_COLOR_WZ;
}
for(int j=0;j<rowData.size();j++){
String data = rowData.get(j);
//项目名称
if(j==0) {
info = new int[]{row,row,1,2};
makeMergedCell(wb,sheet,info,data,fontA,FILL_COLOR_WZ,H_LEFT);
}else if(j==1){
//予算
makeNumericCell(wb,sheet,row,dataCol,data,fontA,lastRowColorIndex);
//実績
formula = "SUM(" + getCellName(row,dataCol+2) + ":" + getCellName(row,dataCol+1+dataSize-2) + ")";
makeFomulaCell(wb,sheet,row,dataCol+1,formula,fontA,lastRowColorIndex);
}
else {
//各合计值
makeNumericCell(wb,sheet,row,dataCol+j,data,fontA,lastRowColorIndex);
}
}
}
//変動費行
List<String> rowData = wzList.get(0);
for(int j=0;j<rowData.size()+1;j++){
//项目名称
if(j==0) {
info = new int[]{rowEnd+1,rowEnd+1,0,2};
makeMergedCell(wb,sheet,info,VARING_FEE,fontA,FILL_COLOR_GREY,H_CENTER);
}else {
//SUM行
formula = "SUM(" + getCellName(rowStart-1,dataCol+j-1) + ":" + getCellName(rowEnd,dataCol+j-1) + ")";
makeFomulaCell(wb,sheet,rowEnd+1,dataCol+j-1,formula,fontA,FILL_COLOR_GREY);
}
}
}
//工費关联行
public static void createGFRow(CostData costData,HSSFWorkbook wb,HSSFSheet sheet) throws Exception{
int rowStart = costData.getGfRowStart();
int rowEnd = rowStart + costData.getGfRowNum() - 1;//不包含合计行(固定費行,変動費+固定費合計)
//---------------工費项目左标题------------------------
int[] info = {rowStart,rowEnd,0,0};
makeMergedCell(wb,sheet,info,LABOUR_COST,fontA,FILL_COLOR_GF,H_CENTER);
//----------------数据行--------------------------
List<List<String>> gfList = costData.getGfList();
String formula = "";
int dataCol = 4;
short leftBorder[] = {HSSFCellStyle.BORDER_THIN,HSSFCellStyle.BORDER_THIN,NONE,HSSFCellStyle.BORDER_THIN};
short rightBorder[] = {NONE,HSSFCellStyle.BORDER_THIN,HSSFCellStyle.BORDER_THIN,HSSFCellStyle.BORDER_THIN};
//数据行
for(int i=0;i<gfList.size();i++){
List<String> rowData = gfList.get(i);
int row = rowStart + i;
int dataSize = rowData.size();
for(int j=0;j<rowData.size();j++){
String data = rowData.get(j);
//项目名称
if(j==0) {
//info = new int[]{row,row,1,1};
//makeMergedCell(wb,sheet,info,data,fontA,FILL_COLOR_GF,H_LEFT);
makeTextCell(wb,sheet,row,1,data,fontA,FILL_COLOR_GF,NONE,leftBorder,H_LEFT);
} else if(j==1){
//info = new int[]{row,row,2,2};
//makeMergedCell(wb,sheet,info,data,fontA,FILL_COLOR_GF,H_CENTER);
makeTextCell(wb,sheet,row,2,data,fontA,FILL_COLOR_GF,NONE,rightBorder,H_CENTER);
} else if(j==2){
//予算
makeNumericCell(wb,sheet,row,dataCol,data,fontA,NONE);
//実績
formula = "SUM(" + getCellName(row,dataCol+2) + ":" + getCellName(row,dataCol+1+dataSize-2) + ")";
makeFomulaCell(wb,sheet,row,dataCol+1,formula,fontA,NONE);
}
else {
//各合计值
makeNumericCell(wb,sheet,row,dataCol+j-1,data,fontA,NONE);
}
}
}
//固定費行
List<String> rowData = gfList.get(0);
for(int j=0;j<rowData.size();j++){
//项目名称
if(j==0) {
info = new int[]{rowEnd+1,rowEnd+1,0,2};
makeMergedCell(wb,sheet,info,FIXED_CHARGE,fontA,FILL_COLOR_GREY,H_CENTER);
}else {
//SUM行
formula = "SUM(" + getCellName(rowStart,dataCol+j-1) + ":" + getCellName(rowEnd,dataCol+j-1) + ")";
makeFomulaCell(wb,sheet,rowEnd+1,dataCol+j-1,formula,fontA,FILL_COLOR_GREY);
}
}
//変動費+固定費合計行
int bdRow = costData.getBdRow();
int gdRow = costData.getGdRow();
for(int j=0;j<rowData.size();j++){
//项目名称
if(j==0) {
info = new int[]{gdRow+1,gdRow+1,0,2};
makeMergedCell(wb,sheet,info,VARING_FIXED,fontA,FILL_COLOR_GREY,H_CENTER);
}else {
//SUM行
formula = getCellName(bdRow,dataCol+j-1) + "+" + getCellName(gdRow,dataCol+j-1);
makeFomulaCell(wb,sheet,gdRow+1,dataCol+j-1,formula,fontA,FILL_COLOR_GREY);
}
}
}
//チャージ外経費,直接原価行
public static void createOtherRow(CostData costData,HSSFWorkbook wb,HSSFSheet sheet) throws Exception{
int rowStart = costData.getGdRow() + 2;
int rowEnd = rowStart + 2;
//---------------チャージ外経費左标题------------------------
int[] info = {rowStart,rowEnd,0,0};
makeMergedCell(wb,sheet,info,OUT_CHARGE_FEE,fontA,NONE,H_CENTER);
//----------------運賃--------------------------
info = new int[]{rowStart,rowStart,1,2};
makeMergedCell(wb,sheet,info,FREIGHT,fontA,NONE,H_CENTER);
int dataCol = 4;
makeNumericCell(wb,sheet,rowStart,dataCol,"",fontA,NONE);
makeNumericCell(wb,sheet,rowStart,dataCol+1,"",fontA,NONE);
//----------------現地調整費--------------------------
info = new int[]{rowStart+1,rowStart+1,1,2};
makeMergedCell(wb,sheet,info,ADJUST_FEE,fontA,NONE,H_CENTER);
makeNumericCell(wb,sheet,rowStart+1,dataCol,"",fontA,NONE);
makeNumericCell(wb,sheet,rowStart+1,dataCol+1,"",fontA,NONE);
//----------------その他--------------------------
info = new int[]{rowStart+2,rowStart+2,1,2};
makeMergedCell(wb,sheet,info,OTHER,fontA,NONE,H_CENTER);
makeNumericCell(wb,sheet,rowStart+2,dataCol,"",fontA,NONE);
makeNumericCell(wb,sheet,rowStart+2,dataCol+1,"",fontA,NONE);
//提前计算好公式,给第二个sheet使用
costData.setOutChargeAFormula("SUM("+SHEET1_NAME+"!"+getCellName(rowStart,dataCol) + ":" + getCellName(rowEnd,dataCol)+")");
costData.setOutChargeBFormula("SUM("+SHEET1_NAME+"!"+getCellName(rowStart,dataCol+1) + ":" + getCellName(rowEnd,dataCol+1)+")");
//----------------直接原価--------------------------
info = new int[]{rowStart+3,rowStart+3,0,2};
makeMergedCell(wb,sheet,info,DIRECT_COST,fontA,FILL_COLOR_MJ,H_CENTER);
String formula = "";
int bdRow = costData.getBdRow();
int gdRow = costData.getGdRow();
formula = getCellName(bdRow,dataCol) + "+" + getCellName(gdRow,dataCol);
makeFomulaCell(wb,sheet,rowStart+3,dataCol,formula,fontA,NONE);
formula = getCellName(bdRow,dataCol+1) + "+" + getCellName(gdRow,dataCol+1);
makeFomulaCell(wb,sheet,rowStart+3,dataCol+1,formula,fontA,NONE);
//----------------基準売価（税別）--------------------------
info = new int[]{rowStart+4,rowStart+4,0,2};
makeMergedCell(wb,sheet,info,BASE_PRICE,fontB,FILL_COLOR_MJ,H_CENTER);
makeNumericCell(wb,sheet,rowStart+4,dataCol,costData.getBasePriceA(),fontC,NONE);
makeNumericCell(wb,sheet,rowStart+4,dataCol+1,costData.getBasePriceB(),fontC,NONE);
//----------------付加価値--------------------------
info = new int[]{rowStart+5,rowStart+5,0,2};
makeMergedCell(wb,sheet,info,ADDED_VALUE,fontB,FILL_COLOR_MJ,H_CENTER);
formula = getCellName(rowStart+4,dataCol) + "-" + getCellName(bdRow,dataCol);
makeFomulaCell(wb,sheet,rowStart+5,dataCol,formula,fontC,NONE);
formula = getCellName(rowStart+4,dataCol+1) + "-" + getCellName(bdRow,dataCol+1);
makeFomulaCell(wb,sheet,rowStart+5,dataCol+1,formula,fontC,NONE);
//----------------付加価値率--------------------------
info = new int[]{rowStart+6,rowStart+6,0,2};
makeMergedCell(wb,sheet,info,ADDED_VALUE_RATE,fontB,FILL_COLOR_MJ,H_CENTER);
formula = getCellName(rowStart+5,dataCol) + "/" + getCellName(rowStart+4,dataCol);
makeFomulaCell(wb,sheet,rowStart+6,dataCol,formula,fontC,NONE,true);
formula = getCellName(rowStart+5,dataCol+1) + "/" + getCellName(rowStart+4,dataCol+1);
makeFomulaCell(wb,sheet,rowStart+6,dataCol+1,formula,fontC,NONE,true);
}
//TOTAL行
public static void createTotalRow(HSSFWorkbook wb,HSSFSheet sheet,int row,List<CostData> dataList) throws Exception{
HSSFFont font = wb.createFont();
font.setColor(HSSFColor.BLUE.index);
font.setFontHeightInPoints((short)11);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
int dataCol = 5;
CostData data = dataList.get(0);
String sellPriceFormula = getCellName(data.getBasePriceBRow(),dataCol);
String valueAddedFormula = getCellName(data.getBasePriceBRow()+1,dataCol);
for(int i=1;i < dataList.size();i++){
sellPriceFormula += "+" + getCellName(data.getBasePriceBRow(),dataCol);
valueAddedFormula += "+" + getCellName(data.getBasePriceBRow()+1,dataCol);
}
//---------------TOTAL标题------------------------
int[] info = {row,row,4,5};
makeMergedCell(wb,sheet,info,"TOTAL",fontD,NONE,H_CENTER,HSSFCellStyle.BORDER_MEDIUM,TOTAL_BORDER_COLOR);
//----------------売価--------------------------
info = new int[]{row+1,row+1,4,4};
makeMergedCell(wb,sheet,info,SELL_PRICE,fontD,NONE,H_CENTER,HSSFCellStyle.BORDER_MEDIUM,TOTAL_BORDER_COLOR);
makeFomulaCell(wb,sheet,row+1,dataCol,sellPriceFormula,fontD,NONE,false,HSSFCellStyle.BORDER_MEDIUM,TOTAL_BORDER_COLOR);
//----------------付加価値--------------------------
info = new int[]{row+2,row+2,4,4};
makeMergedCell(wb,sheet,info,ADDED_VALUE,fontD,NONE,H_CENTER,HSSFCellStyle.BORDER_MEDIUM,TOTAL_BORDER_COLOR);
makeFomulaCell(wb,sheet,row+2,dataCol,valueAddedFormula,fontD,NONE,false,HSSFCellStyle.BORDER_MEDIUM,TOTAL_BORDER_COLOR);
//----------------付加価値率--------------------------
info = new int[]{row+3,row+3,4,4};
makeMergedCell(wb,sheet,info,ADDED_VALUE_RATE,fontD,NONE,H_CENTER,HSSFCellStyle.BORDER_MEDIUM,TOTAL_BORDER_COLOR);
String formula = getCellName(row+2,dataCol) + "/" + getCellName(row+1,dataCol);
makeFomulaCell(wb,sheet,row+3,dataCol,formula,fontD,NONE,true,HSSFCellStyle.BORDER_MEDIUM,TOTAL_BORDER_COLOR);
}
/**
* 生成数值型单元格
* @param wb 工作簿
* @param sheet sheet
* @param rowNum 行
* @param colNum 列
* @param content 内容
* @param font 字体
* @param fillColorIdx 填充色
*/
public static void makeNumericCell(HSSFWorkbook wb,HSSFSheet sheet,int rowNum,int colNum,String content,HSSFFont font,short fillColorIdx){
makeCell(wb,sheet,rowNum,colNum,NUMERIC,content,font,fillColorIdx,NONE,HSSFCellStyle.BORDER_THIN);
}
/**
* 生成公式型单元格
* @param wb 工作簿
* @param sheet sheet
* @param rowNum 行
* @param colNum 列
* @param content 内容
* @param font 字体
* @param fillColorIdx 填充色
*/
public static void makeFomulaCell(HSSFWorkbook wb,HSSFSheet sheet,int rowNum,int colNum,String content,HSSFFont font,short fillColorIdx){
makeCell(wb,sheet,rowNum,colNum,FORMULA,content,font,fillColorIdx,NONE,HSSFCellStyle.BORDER_THIN);
}
/**
* 生成公式型单元格:百分比形式
* @param wb 工作簿
* @param sheet sheet
* @param rowNum 行
* @param colNum 列
* @param content 内容
* @param font 字体
* @param fillColorIdx 填充色
* @param percent 百分比形式
*/
public static void makeFomulaCell(HSSFWorkbook wb,HSSFSheet sheet,int rowNum,int colNum,String content,HSSFFont font,short fillColorIdx,boolean percent, short borderSize,short borderColor){
makeCell(wb,sheet,rowNum,colNum,FORMULA,content,font,fillColorIdx,percent,borderColor,borderSize);
}
/**
* 生成公式型单元格:百分比形式
* @param wb 工作簿
* @param sheet sheet
* @param rowNum 行
* @param colNum 列
* @param content 内容
* @param font 字体
* @param fillColorIdx 填充色
* @param percent 百分比形式
* @param borderColor 边框颜色
*/
public static void makeFomulaCell(HSSFWorkbook wb,HSSFSheet sheet,int rowNum,int colNum,String content,HSSFFont font,short fillColorIdx,boolean percent){
makeCell(wb,sheet,rowNum,colNum,FORMULA,content,font,fillColorIdx,percent,NONE,HSSFCellStyle.BORDER_THIN);
}
/**
* 生成单元格
* @param wb 工作簿
* @param sheet sheet
* @param rowNum 行
* @param colNum 列
* @param type 单元格类型
* @param content 内容
* @param font 字体
* @param fillColorIdx 填充色
* @param borderColor 边框颜色
* @param borderSize 边框宽度
*/
public static void makeCell(HSSFWorkbook wb,HSSFSheet sheet,int rowNum,int colNum,String type,String content,HSSFFont font,short fillColorIdx,short borderColor,short borderSize){
makeCell(wb,sheet,rowNum,colNum,type,content,font,fillColorIdx,false,borderColor,borderSize);
}
/**
* 生成单元格
* @param wb 工作簿
* @param sheet sheet
* @param rowNum 行
* @param colNum 列
* @param type 单元格类型
* @param content 内容
* @param font 字体
* @param fillColorIdx 填充色
* @param percent 是否显示百分数
* @param borderColor 边框颜色
* @param borderSize 边框宽度
*/
public static void makeCell(HSSFWorkbook wb,HSSFSheet sheet,int rowNum,int colNum,String type,String content,HSSFFont font,short fillColorIdx,boolean percent,short borderColor,short borderSize){
HSSFRow row = sheet.getRow(rowNum);
HSSFCell cell = row.createCell(colNum);
if(FORMULA.equals(type)){
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
} else if(NUMERIC.equals(type)){
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
} else if(TEXT.equals(type)){
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
} else if(BLANK.equals(type)){
cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
}
// 创建单元格样式
HSSFCellStyle cellStyle = wb.createCellStyle();
if(borderSize != NONE){
cellStyle.setBorderTop(borderSize);
cellStyle.setBorderLeft(borderSize);
cellStyle.setBorderRight(borderSize);
cellStyle.setBorderBottom(borderSize);
}
if(borderColor != NONE){
cellStyle.setLeftBorderColor(borderColor);
cellStyle.setTopBorderColor(borderColor);
cellStyle.setRightBorderColor(borderColor);
cellStyle.setBottomBorderColor(borderColor);
}
if(FORMULA.equals(type) || NUMERIC.equals(type)){
//保留两位小数
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
} else {
//设置自动换行
cellStyle.setWrapText(true);
}
// 填充色
if(fillColorIdx != NONE) {
cellStyle.setFillForegroundColor(fillColorIdx);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
}
// 显示百分数
if(percent){
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
}
// 字体
if(font != null){
cellStyle.setFont(font);
}
cell.setCellStyle(cellStyle);
if(FORMULA.equals(type)){
cell.setCellFormula(content);
} else if(NUMERIC.equals(type)){
if(!"".equals(content)){
cell.setCellValue(Double.parseDouble(content));
}
}
}
public static void makeTextCell(HSSFWorkbook wb,HSSFSheet sheet,int rowNum,int colNum,String content,HSSFFont font,short fillColorIdx,short borderColor,short[] borderSize,short hAlign){
HSSFRow row = sheet.getRow(rowNum);
HSSFCell cell = row.createCell(colNum);
// 创建单元格样式
HSSFCellStyle cellStyle = wb.createCellStyle();
if(borderSize[0] != NONE){
cellStyle.setBorderLeft(borderSize[0]);
}
if(borderSize[1] != NONE){
cellStyle.setBorderTop(borderSize[1]);
}
if(borderSize[2] != NONE){
cellStyle.setBorderRight(borderSize[2]);
}
if(borderSize[3] != NONE){
cellStyle.setBorderBottom(borderSize[3]);
}
if(borderColor != NONE){
cellStyle.setLeftBorderColor(borderColor);
cellStyle.setTopBorderColor(borderColor);
cellStyle.setRightBorderColor(borderColor);
cellStyle.setBottomBorderColor(borderColor);
}
cellStyle.setAlignment(hAlign);
//设置自动换行
cellStyle.setWrapText(true);
// 填充色
if(fillColorIdx != NONE) {
cellStyle.setFillForegroundColor(fillColorIdx);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
}
// 字体
if(font != null){
cellStyle.setFont(font);
}
cell.setCellStyle(cellStyle);
//cell.setCellValue(new HSSFRichTextString(content));
cell.setCellValue(content);
}
/**
* 根据行列数得到对应的单元格名称
* @param row 0~
* @param col 0~
* @return
*/
public static String getCellName(int row,int col){
String result = "";
row = row + 1;
int base = 65;
if(col < 26){
result = (char)(base+col) + "" + row;
} else {
int first = col / 25;
int second = col % 25;
result = (char)(base + first) + "" + (char)(base + second) + "" + row;
}
return result;
}
/**
* 生成标题行
* @param costData SOMO数据实体
* @param wb
* @param sheet
* @param rowNum 行
*/
public static void makeTitleRow(CostData costData,HSSFWorkbook wb,HSSFSheet sheet,int rowNum){
// 创建单元格样式
HSSFCellStyle cellStyle = wb.createCellStyle();
// 指定单元格居中对齐
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 指定单元格垂直居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
List<String> titleList = costData.getTitleList();
HSSFRow row = sheet.getRow(rowNum);
HSSFCell cell = null;
for (int i = 0; i < titleList.size(); i++) {
String content = titleList.get(i);
// 从E列开始
cell = row.createCell(i + 4);
cell.setCellValue(new HSSFRichTextString(content));
cell.setCellStyle(cellStyle);
}
}
/**
* 生成合并单元格,垂直居中
* @param wb
* @param sheet
* @param rowCol 起始行号,终止行号,起始列号,终止列号
* @param content 内容
* @param font 字体
* @param fillColorIdx 填充色
* @param hAlign 水平对齐方式
* @throws Exception
*/
public static void makeMergedCell(HSSFWorkbook wb,HSSFSheet sheet,int[] rowCol,String content,HSSFFont font,short fillColorIdx,short hAlign) throws Exception{
makeMergedCell(wb,sheet,rowCol,content,font,fillColorIdx,hAlign,NONE,NONE);
}
/**
* 生成合并单元格,垂直居中
* @param wb
* @param sheet
* @param rowCol 起始行号,终止行号,起始列号,终止列号
* @param content 内容
* @param font 字体
* @param fillColorIdx 填充色
* @param hAlign 水平对齐方式
* @param borderSize 边框宽度
* @param borderColorIdx 边框颜色
* @throws Exception
*/
public static void makeMergedCell(HSSFWorkbook wb,HSSFSheet sheet,int[] rowCol,String content,HSSFFont font,short fillColorIdx,short hAlign, short borderSize,short borderColorIdx) throws Exception{
HSSFCellStyle cellStyle = wb.createCellStyle();
// 横向对齐方式
cellStyle.setAlignment(hAlign);
// 垂直居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 填充色
if(fillColorIdx != -1) {
cellStyle.setFillForegroundColor(fillColorIdx);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
}
cellStyle.setWrapText(true);
cellStyle.setFont(font);
HSSFRow row = sheet.getRow(rowCol[0]);
//参数:起始行号,终止行号,起始列号,终止列号
CellRangeAddress region = new CellRangeAddress((short)rowCol[0],(short)rowCol[1],(short)rowCol[2],(short)rowCol[3]);
// 合并单元格(startRow,endRow,startColumn,endColumn)
sheet.addMergedRegion(region);
HSSFCell cell = row.createCell(rowCol[2]);
cell.setCellValue(new HSSFRichTextString(content));
cell.setCellStyle(cellStyle);
if(borderSize != NONE && borderColorIdx != NONE){
setBorder(region,sheet,wb,borderSize,borderColorIdx);
}else{
setBorder(region,sheet,wb,HSSFCellStyle.BORDER_THIN,NONE);
}
}
/**
* 设定合并单元格边框
* @param cellRangeAddress 合并区域
* @param sheet
* @param wb
* @param size 边框宽度
* @param colorIdx 边框颜色
* @throws Exception
*/
public static void setBorder(CellRangeAddress cellRangeAddress,
Sheet sheet, Workbook wb, short size,short colorIdx) throws Exception {
RegionUtil.setBorderLeft(size, cellRangeAddress, sheet, wb);
RegionUtil.setBorderTop(size, cellRangeAddress, sheet, wb);
RegionUtil.setBorderRight(size, cellRangeAddress, sheet, wb);
RegionUtil.setBorderBottom(size, cellRangeAddress, sheet, wb);
// 边框颜色
if(colorIdx != NONE){
RegionUtil.setLeftBorderColor(colorIdx, cellRangeAddress, sheet, wb);
RegionUtil.setTopBorderColor(colorIdx, cellRangeAddress, sheet, wb);
RegionUtil.setRightBorderColor(colorIdx, cellRangeAddress, sheet, wb);
RegionUtil.setBottomBorderColor(colorIdx, cellRangeAddress, sheet, wb);
}
}
//测试数据
public static CostData makeData() {
CostData costData = new CostData();
costData.setStartRow(1);
costData.setSomo("SOA-140005");
costData.setSomoName("モジュール本体組立機");
//年月
List<String[]> yearList = new ArrayList<String[]>();
String[] tmpArr = new String[]{"FY14","8"};
yearList.add(tmpArr);
tmpArr = new String[]{"FY15","1"};
yearList.add(tmpArr);
tmpArr = new String[]{"空白","1"};
yearList.add(tmpArr);
costData.setYearList(yearList);
//title
List<String> titleList = new ArrayList<String>();
titleList.add("予算");
titleList.add("実績");
titleList.add("5月");
titleList.add("6月");
titleList.add("7月");
titleList.add("8月");
titleList.add("9月");
titleList.add("10月（出荷）");
titleList.add("11月");
titleList.add("12月");
titleList.add("1月");
titleList.add("");
costData.setTitleList(titleList);
//部材費
List<List<String>> bcList = new ArrayList<List<String>>();
List<String> tmpList;
//加工品（輸入）
tmpList = new ArrayList<String>();
tmpList.add("加工品（輸入）");
tmpList.add("267588.237944706");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("819.28");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("589.63");
tmpList.add("");
bcList.add(tmpList);
tmpList = new ArrayList<String>();
tmpList.add("補材");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("343.59");
tmpList.add("10814.53");
tmpList.add("7972.22");
tmpList.add("0.00");
tmpList.add("926");
tmpList.add("0.00");
tmpList.add("8521.25");
tmpList.add("");
bcList.add(tmpList);
costData.setBcList(bcList);
//外注費
List<List<String>> wzList = new ArrayList<List<String>>();
//設計メカ
tmpList = new ArrayList<String>();
tmpList.add("設計メカ");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("38087.7");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("");
wzList.add(tmpList);
//設計エレキ
tmpList = new ArrayList<String>();
tmpList.add("設計エレキ");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("34832.12");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("");
wzList.add(tmpList);
//ロイヤリティ
tmpList = new ArrayList<String>();
tmpList.add("ロイヤリティ");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("");
wzList.add(tmpList);
costData.setWzList(wzList);
//工費
List<List<String>> gfList = new ArrayList<List<String>>();
//仕様関係
tmpList = new ArrayList<String>();
tmpList.add("仕様関係");
tmpList.add("101");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("1581.3");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("");
gfList.add(tmpList);
//メカ設計
tmpList = new ArrayList<String>();
tmpList.add("メカ設計");
tmpList.add("102");
tmpList.add("156911.764705882");
tmpList.add("9544.275");
tmpList.add("32360.175");
tmpList.add("72739.8");
tmpList.add("6438.15");
tmpList.add("1242.45");
tmpList.add("903.6");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("0.00");
tmpList.add("");
gfList.add(tmpList);
costData.setGfList(gfList);
//基準売価（税別）:予算
costData.setBasePriceA("1616896");
//基準売価（税別）:実績
costData.setBasePriceB("1616896");
return costData;
}
}
最近下载更多
Mr Wang LV2
2022年10月13日
yfx008 LV2
2022年10月12日
可比克 LV8
2022年9月14日
hanmingCheng LV1
2022年9月11日
779232649 LV1
2022年7月28日
linjiawang LV12
2022年7月13日
aaronsuccess LV2
2022年4月28日
1306878374 LV13
2022年4月26日
xxxx111 LV1
2022年4月15日
一直都会顺利的小吴 LV5
2022年2月26日
最近浏览更多
微信网友_7056748553195520
6月28日
暂无贡献等级
香菇肉饼汤 LV8
4月21日
seagull1995
4月11日
暂无贡献等级
微信网友_6908722610081792
3月15日
暂无贡献等级
3334004690 LV10
2023年11月4日
fesfefe LV13
2023年11月1日
gs123123 LV1
2023年6月30日
PostVapor
2023年5月24日
暂无贡献等级
wanglixuan
2023年3月24日
暂无贡献等级
jrack123
2023年2月4日
暂无贡献等级
