POI 操作EXCEL表格导出
部分代码
//字體1
HSSFFont font_Header01 = wb.createFont();
font_Header01.setFontHeightInPoints((short)14);
font_Header01.setFontName("メイリオ");
font_Header01.setUnderline(HSSFFont.U_SINGLE);
//字體2
HSSFFont font_Header02 = wb.createFont();
font_Header02.setFontHeightInPoints((short)12);
font_Header02.setFontName("メイリオ");
//字體3
HSSFFont font_Header03 = wb.createFont();
font_Header03.setFontHeightInPoints((short)10);
font_Header03.setFontName("メイリオ");
//樣式1
HSSFCellStyle cellStyle01 = wb.createCellStyle();
cellStyle01.setFont(font_Header01);
//水平方向の配置
cellStyle01.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//垂直方向の配置
cellStyle01.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
//樣式2
HSSFCellStyle cellStyle02 = wb.createCellStyle();
cellStyle02.setFont(font_Header02);
//水平方向の配置
cellStyle02.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//垂直方向の配置
cellStyle02.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//cellStyle02.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//cellStyle02.setBorderTop(HSSFCellStyle.BORDER_THIN);
//樣式3
HSSFCellStyle cellStyle03 = wb.createCellStyle();
cellStyle03.setFont(font_Header03);
//水平方向の配置
cellStyle03.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//垂直方向の配置
cellStyle03.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//樣式4 邊框左上
HSSFCellStyle cellStyle033 = wb.createCellStyle();
cellStyle033.setFont(font_Header03);
//水平方向の配置
cellStyle033.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//垂直方向の配置
cellStyle033.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle033.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle033.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle033.setBorderRight(HSSFCellStyle.BORDER_THIN);
//改行の設定
cellStyle033.setWrapText(true);
//樣式5 邊框左上
HSSFCellStyle cellStyle0333 = wb.createCellStyle();
cellStyle0333.setFont(font_Header03);
//水平方向の配置
cellStyle0333.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//垂直方向の配置
cellStyle0333.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle0333.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle0333.setBorderTop(HSSFCellStyle.BORDER_THIN);
//改行の設定
cellStyle0333.setWrapText(true);
//樣式6 邊框左下
HSSFCellStyle cellStyle03333 = wb.createCellStyle();
cellStyle03333.setFont(font_Header03);
//水平方向の配置
cellStyle03333.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//垂直方向の配置
cellStyle03333.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle03333.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle03333.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//改行の設定
cellStyle03333.setWrapText(true);
//樣式7 邊框左下右
HSSFCellStyle cellStyle_l_x_r = wb.createCellStyle();
cellStyle_l_x_r.setFont(font_Header03);
//水平方向の配置
cellStyle_l_x_r.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//垂直方向の配置
cellStyle_l_x_r.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle_l_x_r.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle_l_x_r.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle_l_x_r.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//改行の設定
cellStyle_l_x_r.setWrapText(true);
//樣式8 フォントを右に
HSSFCellStyle cellStyle_zr = wb.createCellStyle();
cellStyle_zr.setFont(font_Header03);
//右方向の配置
cellStyle_zr.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
cellStyle_zr.setBorderRight(HSSFCellStyle.BORDER_THIN);
//樣式8 -1フォントを右に
HSSFCellStyle cellStyle_zr_j = wb.createCellStyle();
cellStyle_zr_j.setFont(font_Header03);
//右方向の配置
cellStyle_zr_j.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
cellStyle_zr_j.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle_zr_j.setDataFormat(format.getFormat("#,##0"));
//樣式8 フォントを右に
HSSFCellStyle cellStyle_zr_s = wb.createCellStyle();
cellStyle_zr_s.setFont(font_Header03);
//右方向の配置
cellStyle_zr_s.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
cellStyle_zr_s.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle_zr_s.setDataFormat(format.getBuiltinFormat("0.00"));
//樣式8 フォントを中央に
HSSFCellStyle cellStyle_zc = wb.createCellStyle();
cellStyle_zc.setFont(font_Header03);
//中央方向の配置
cellStyle_zc.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle_zc.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle_zc.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//樣式8 下右
HSSFCellStyle cellStyle_zxr = wb.createCellStyle();
cellStyle_zxr.setFont(font_Header03);
//中央方向の配置
cellStyle_zxr.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle_zxr.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle_zxr.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//中央方向の配置
HSSFCellStyle cellStyle_zxr_l = wb.createCellStyle();
cellStyle_zxr_l.setFont(font_Header03);
cellStyle_zxr_l.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle_zxr_l.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle_zxr_l.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle_zxr_l.setBorderLeft(HSSFCellStyle.BORDER_THIN);
HSSFSheet sheet3 = null;
if (ko_rinji_flg != null && "1".equals(ko_rinji_flg)){
//報告書
sheet3 = wb.createSheet("報告書("+k_name+"様)");
sheet3.getPrintSetup().setLandscape(true);
sheet3.getPrintSetup().setHeaderMargin(0.2);
sheet3.getPrintSetup().setScale((short)80);
// シートの第0行を取得
HSSFRow row3 = sheet3.createRow(0);
sheet3.addMergedRegion(new Region((short) 0, (short) 4, (short) 0, (short) 9));
row3.setHeightInPoints(22);
//4列
HSSFCell cell13 = row3.createCell((short) 4);
//0行
staffinfo = setString(" 臨 時 従 業 員 給 与 報 告 書");
hs = new HSSFRichTextString(staffinfo.trim());
cell13.setCellStyle(cellStyle01);
cell13.setCellValue(hs);
//13列
cell13 = row3.createCell((short) 13);
sheet3.addMergedRegion(new Region((short) 0, (short) 13, (short) 0, (short) 15));
//0行
staffinfo = setString("("+k_name+")");
hs = new HSSFRichTextString(staffinfo.trim());
cell13.setCellStyle(cellStyle02);
cell13.setCellValue(hs);
//1行
row3 = sheet3.createRow(1);
row3.setHeightInPoints(30);
cell13 = row3.createCell((short) 2);
//1行
staffinfo = Utility.setStmontn(kyuyo_ymd2);;
hs = new HSSFRichTextString(staffinfo.trim());
cell13.setCellStyle(cellStyle02);
cell13.setCellValue(hs);
//3列
cell13 = row3.createCell((short) 3);
//1行
staffinfo = setString("月分");
hs = new HSSFRichTextString(staffinfo.trim());
cell13.setCellStyle(cellStyle02);
cell13.setCellValue(hs);
//2行
row3 = sheet3.createRow(2);
//行の高さ
row3.setHeightInPoints(22);
cell13 = row3.createCell((short) 0);
//開始行、開始列、終了行、終了列
sheet3.addMergedRegion(new Region((short) 2, (short) 0, (short) 2, (short) 3));
//1行
staffinfo = Utility.setstYmdoredYmd(kyuyo_ymd2);
hs = new HSSFRichTextString(staffinfo.trim());
cell13.setCellStyle(cellStyle02);
cell13.setCellValue(hs);
//3行
row3 = sheet3.createRow(3);
//行の高さ
row3.setHeightInPoints(22);
sheet3.addMergedRegion(new Region((short) 3, (short) 0, (short) 3, (short) 2));
cell13 = row3.createCell((short) 0);
//1行
staffinfo = setString(koTanName);
hs = new HSSFRichTextString(staffinfo.trim());
cell13.setCellStyle(cellStyle02);
cell13.setCellValue(hs);
//4行
row3 = sheet3.createRow(4);
//行の高さ
row3.setHeightInPoints(22);
//4行0列
cell13 = row3.createCell((short) 0);
cell13.setCellStyle(cellStyle0333);
//4行1列
//開始行、開始列、終了行、終了列
//開始行、終了行、開始列、終了列
CellRangeAddress region = new CellRangeAddress(4,5,1,1);
sheet3.addMergedRegion(region);
cell13 = row3.createCell((short) 1);
cell13.setCellStyle(cellStyle03);
RegionUtil.setBorderLeft((short) 1, region,sheet3, wb);
RegionUtil.setBorderTop((short) 1, region,sheet3, wb);
hs = new HSSFRichTextString("支給人員");
cell13.setCellValue(hs);
//5行2列
//開始行、終了行、開始列、終了列
region = new CellRangeAddress(4,5,2,2);
sheet3.addMergedRegion(region);
cell13 = row3.createCell((short) 2);
cell13.setCellStyle(cellStyle03);
RegionUtil.setBorderLeft((short) 1, region,sheet3, wb);
RegionUtil.setBorderTop((short) 1, region,sheet3, wb);
hs = new HSSFRichTextString("労働日数");
cell13.setCellValue(hs);
//4行3列
//開始行、終了行、開始列、終了列
region = new CellRangeAddress(4,4,3,5);
sheet3.addMergedRegion(region);
//sheet3.addMergedRegion(new Region((short) 4, (short) 3, (short) 4, (short) 5));
cell13 = row3.createCell((short) 3);
cell13.setCellStyle(cellStyle03);
RegionUtil.setBorderLeft((short) 1, region,sheet3, wb);
RegionUtil.setBorderTop((short) 1, region,sheet3, wb);
RegionUtil.setBorderBottom((short) 1, region,sheet3, wb);
hs = new HSSFRichTextString("労 働 時 間");
cell13.setCellValue(hs);
//4行6列
//開始行、終了行、開始列、終了列
region = new CellRangeAddress(4,4,6,11);
sheet3.addMergedRegion(region);
//sheet3.addMergedRegion(new Region((short) 4, (short) 6, (short) 4, (short) 11));
cell13 = row3.createCell((short) 6);
cell13.setCellStyle(cellStyle03);
RegionUtil.setBorderLeft((short) 1, region,sheet3, wb);
RegionUtil.setBorderTop((short) 1, region,sheet3, wb);
RegionUtil.setBorderBottom((short) 1, region,sheet3, wb);
hs = new HSSFRichTextString("支 給 額");
cell13.setCellValue(hs);
//4行12列
//開始行、終了行、開始列、終了列
region = new CellRangeAddress(4,4,12,16);
sheet3.addMergedRegion(region);
//sheet3.addMergedRegion(new Region((short) 4, (short) 12, (short) 4, (short) 16));
cell13 = row3.createCell((short) 12);
cell13.setCellStyle(cellStyle03);
RegionUtil.setBorderLeft((short) 1, region,sheet3, wb);
RegionUtil.setBorderTop((short) 1, region,sheet3, wb);
RegionUtil.setBorderBottom((short) 1, region,sheet3, wb);
hs = new HSSFRichTextString("そ の 他");
cell13.setCellValue(hs);
//4行17列
cell13 = row3.createCell((short) 17);
cell13.setCellStyle(cellStyle033);
//5行
row3 = sheet3.createRow(5);
//行の高さ
row3.setHeightInPoints(32);
//5行0列
cell13 = row3.createCell((short) 0);
cell13.setCellStyle(cellStyle03333);
hs = new HSSFRichTextString("性 別");
cell13.setCellValue(hs);
//5行1列
cell13 = row3.createCell((short) 1);
cell13.setCellStyle(cellStyle03333);
//hs = new HSSFRichTextString("性 別");
//cell13.setCellValue(hs);
//5行2列
cell13 = row3.createCell((short) 2);
cell13.setCellStyle(cellStyle03333);
//hs = new HSSFRichTextString("性 別");
//cell13.setCellValue(hs);
//5行3列
cell13 = row3.createCell((short) 3);
cell13.setCellStyle(cellStyle03333);
hs = new HSSFRichTextString("総労働 時 間");
cell13.setCellValue(hs);
//5行4列
cell13 = row3.createCell((short) 4);
cell13.setCellStyle(cellStyle03333);
hs = new HSSFRichTextString("時間外");
cell13.setCellValue(hs);
//5行5列
cell13 = row3.createCell((short) 5);
cell13.setCellStyle(cellStyle03333);
hs = new HSSFRichTextString("深 夜");
cell13.setCellValue(hs);
//5行6列
cell13 = row3.createCell((short) 6);
cell13.setCellStyle(cellStyle03333);
hs = new HSSFRichTextString("所定内給与");
cell13.setCellValue(hs);
//5行7列
cell13 = row3.createCell((short) 7);
cell13.setCellStyle(cellStyle03333);
hs = new HSSFRichTextString("時間外 割 増");
cell13.setCellValue(hs);
//5行8列
cell13 = row3.createCell((short) 8);
cell13.setCellStyle(cellStyle03333);
hs = new HSSFRichTextString("深夜割増");
cell13.setCellValue(hs);
//5行9列
cell13 = row3.createCell((short) 9);
cell13.setCellStyle(cellStyle03333);
hs = new HSSFRichTextString("給与合計");
cell13.setCellValue(hs);
//5行10列
cell13 = row3.createCell((short) 10);
cell13.setCellStyle(cellStyle03333);
hs = new HSSFRichTextString("通勤手当");
cell13.setCellValue(hs);
//5行11列
cell13 = row3.createCell((short) 11);
cell13.setCellStyle(cellStyle03333);
hs = new HSSFRichTextString("総支給額");
cell13.setCellValue(hs);
//5行12列
cell13 = row3.createCell((short) 12);
cell13.setCellStyle(cellStyle03333);
hs = new HSSFRichTextString("健康保険");
cell13.setCellValue(hs);
//5行13列
cell13 = row3.createCell((short) 13);
cell13.setCellStyle(cellStyle03333);
hs = new HSSFRichTextString("雇用保険");
cell13.setCellValue(hs);
//5行14列
cell13 = row3.createCell((short) 14);
cell13.setCellStyle(cellStyle03333);
hs = new HSSFRichTextString("その他");
cell13.setCellValue(hs);
//5行15列
cell13 = row3.createCell((short) 15);
cell13.setCellStyle(cellStyle03333);
hs = new HSSFRichTextString("所得税");
cell13.setCellValue(hs);
//5行16列
cell13 = row3.createCell((short) 16);
cell13.setCellStyle(cellStyle03333);
hs = new HSSFRichTextString("控除合計");
cell13.setCellValue(hs);
//5行17列
cell13 = row3.createCell((short) 17);
cell13.setCellStyle(cellStyle_l_x_r);
hs = new HSSFRichTextString("差引支給額");
cell13.setCellValue(hs);
//6行
row3 = sheet3.createRow(6);
//行の高さ
row3.setHeightInPoints(16);
//6行0列
cell13 = row3.createCell((short) 0);
cell13.setCellStyle(cellStyle_zc);
//6行1列
cell13 = row3.createCell((short) 1);
cell13.setCellStyle(cellStyle_zr);
hs = new HSSFRichTextString("名");
cell13.setCellValue(hs);
//6行2列
cell13 = row3.createCell((short) 2);
cell13.setCellStyle(cellStyle_zr);
hs = new HSSFRichTextString("日");
cell13.setCellValue(hs);
//6行3列
cell13 = row3.createCell((short) 3);
cell13.setCellStyle(cellStyle_zr);
hs = new HSSFRichTextString("Ｈ");
cell13.setCellValue(hs);
//6行4列
cell13 = row3.createCell((short) 4);
cell13.setCellStyle(cellStyle_zr);
hs = new HSSFRichTextString("Ｈ");
cell13.setCellValue(hs);
//6行5列
cell13 = row3.createCell((short) 5);
cell13.setCellStyle(cellStyle_zr);
hs = new HSSFRichTextString("Ｈ");
cell13.setCellValue(hs);
//6行6列
cell13 = row3.createCell((short) 6);
cell13.setCellStyle(cellStyle_zr);
hs = new HSSFRichTextString("円");
cell13.setCellValue(hs);
//6行7列
cell13 = row3.createCell((short) 7);
cell13.setCellStyle(cellStyle_zr);
hs = new HSSFRichTextString("円");
cell13.setCellValue(hs);
//6行8列
cell13 = row3.createCell((short) 8);
cell13.setCellStyle(cellStyle_zr);
hs = new HSSFRichTextString("円");
cell13.setCellValue(hs);
//6行9列
cell13 = row3.createCell((short) 9);
cell13.setCellStyle(cellStyle_zr);
hs = new HSSFRichTextString("円");
cell13.setCellValue(hs);
//6行10列
cell13 = row3.createCell((short) 10);
cell13.setCellStyle(cellStyle_zr);
hs = new HSSFRichTextString("円");
cell13.setCellValue(hs);
//6行11列
cell13 = row3.createCell((short) 11);
cell13.setCellStyle(cellStyle_zr);
hs = new HSSFRichTextString("円");
cell13.setCellValue(hs);
//6行12列
cell13 = row3.createCell((short) 12);
cell13.setCellStyle(cellStyle_zr);
hs = new HSSFRichTextString("円");
cell13.setCellValue(hs);
//6行13列
cell13 = row3.createCell((short) 13);
cell13.setCellStyle(cellStyle_zr);
hs = new HSSFRichTextString("円");
cell13.setCellValue(hs);
//6行14列
cell13 = row3.createCell((short) 14);
cell13.setCellStyle(cellStyle_zr);
hs = new HSSFRichTextString("円");
cell13.setCellValue(hs);
//6行15列
cell13 = row3.createCell((short) 15);
cell13.setCellStyle(cellStyle_zr);
hs = new HSSFRichTextString("円");
cell13.setCellValue(hs);
//6行16列
cell13 = row3.createCell((short) 16);
cell13.setCellStyle(cellStyle_zr);
hs = new HSSFRichTextString("円");
cell13.setCellValue(hs);
//6行17列
cell13 = row3.createCell((short) 17);
cell13.setCellStyle(cellStyle_zr);
hs = new HSSFRichTextString("円");
cell13.setCellValue(hs);
//7行
row3 = sheet3.createRow(7);
//7行0列
cell13 = row3.createCell((short) 0);
cell13.setCellStyle(cellStyle_zc);
cell13.setCellValue("男");
//7行1列
cell13 = row3.createCell((short) 1);
cell13.setCellStyle(cellStyle_zr);
String cellFormula = "";
if("666".equals(pay_cdFlg)){
cellFormula = "SUMIF('"+ymdyyyymmdd+"'!O:O,"+"\""+"男"+"\""+",'"+ymdyyyymmdd+"'!N:N)";
}else{
cellFormula = "SUMIF('"+ymdyyyymmdd+"'!N:N,"+"\""+"男"+"\""+",'"+ymdyyyymmdd+"'!M:M)";
}
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//7行2列
cell13 = row3.createCell((short) 2);
cell13.setCellStyle(cellStyle_zr);
if("666".equals(pay_cdFlg)){
cellFormula = "SUMIF('"+ymdyyyymmdd+"明細'!E:E,"+"\""+"男"+"\""+",'"+ymdyyyymmdd+"明細'!V:V)";
}else{
cellFormula = "SUMIF('"+ymdyyyymmdd+"明細'!E:E,"+"\""+"男"+"\""+",'"+ymdyyyymmdd+"明細'!U:U)";
}
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//7行3列
cell13 = row3.createCell((short) 3);
cell13.setCellStyle(cellStyle_zr_s);
if("666".equals(pay_cdFlg)){
cellFormula = "SUMIF('"+ymdyyyymmdd+"明細'!E:E,"+"\""+"男"+"\""+",'"+ymdyyyymmdd+"明細'!H:H)";
}else{
cellFormula = "SUMIF('"+ymdyyyymmdd+"明細'!E:E,"+"\""+"男"+"\""+",'"+ymdyyyymmdd+"明細'!G:G)";
}
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//7行4列
cell13 = row3.createCell((short) 4);
cell13.setCellStyle(cellStyle_zr);
//7行5列
cell13 = row3.createCell((short) 5);
cell13.setCellStyle(cellStyle_zr);
//7行6列
cell13 = row3.createCell((short) 6);
cell13.setCellStyle(cellStyle_zr_j);
if("666".equals(pay_cdFlg)){
cellFormula = "SUMIF('"+ymdyyyymmdd+"明細'!E:E,"+"\""+"男"+"\""+",'"+ymdyyyymmdd+"明細'!I:I)";
}else{
cellFormula = "SUMIF('"+ymdyyyymmdd+"明細'!E:E,"+"\""+"男"+"\""+",'"+ymdyyyymmdd+"明細'!H:H)";
}
cell13.setCellFormula(cellFormula);
//7行7列
cell13 = row3.createCell((short) 7);
cell13.setCellStyle(cellStyle_zr);
//7行8列
cell13 = row3.createCell((short) 8);
cell13.setCellStyle(cellStyle_zr);
//7行9列
cell13 = row3.createCell((short) 9);
cell13.setCellStyle(cellStyle_zr);
cellFormula = "G8+H8+I8";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//7行10列
cell13 = row3.createCell((short) 10);
cell13.setCellStyle(cellStyle_zr_j);
if("666".equals(pay_cdFlg)){
cellFormula = "SUMIF('"+ymdyyyymmdd+"明細'!E:E,"+"\""+"男"+"\""+",'"+ymdyyyymmdd+"明細'!L:L)";
}else{
cellFormula = "SUMIF('"+ymdyyyymmdd+"明細'!E:E,"+"\""+"男"+"\""+",'"+ymdyyyymmdd+"明細'!K:K)";
}
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//7行11列
cell13 = row3.createCell((short) 11);
cell13.setCellStyle(cellStyle_zr);
cellFormula = "SUM(J8:K8)";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//7行12列
cell13 = row3.createCell((short) 12);
cell13.setCellStyle(cellStyle_zr);
//7行13列
cell13 = row3.createCell((short) 13);
cell13.setCellStyle(cellStyle_zr);
//7行14列
cell13 = row3.createCell((short) 14);
cell13.setCellStyle(cellStyle_zr);
//7行15列
cell13 = row3.createCell((short) 15);
cell13.setCellStyle(cellStyle_zr_j);
if("666".equals(pay_cdFlg)){
cellFormula = "SUMIF('"+ymdyyyymmdd+"明細'!E:E,"+"\""+"男"+"\""+",'"+ymdyyyymmdd+"明細'!P:P)";
}else{
cellFormula = "SUMIF('"+ymdyyyymmdd+"明細'!E:E,"+"\""+"男"+"\""+",'"+ymdyyyymmdd+"明細'!O:O)";
}
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//7行16列
cell13 = row3.createCell((short) 16);
cell13.setCellStyle(cellStyle_zr_j);
cellFormula = "P8";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//7行17列
cell13 = row3.createCell((short) 17);
cell13.setCellStyle(cellStyle_zr_j);
cellFormula = "L8-Q8";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//8行
row3 = sheet3.createRow(8);
//行の高さ
row3.setHeightInPoints(16);
for (int i = 0; i < 18; i++) {
if(i == 0){
//8行0列
cell13 = row3.createCell((short) i);
cell13.setCellStyle(cellStyle_zxr_l);
}else{
//8行i列
cell13 = row3.createCell((short) i);
cell13.setCellStyle(cellStyle_zxr);
}
}
//9行
row3 = sheet3.createRow(9);
//行の高さ
row3.setHeightInPoints(16);
for (int i = 0; i < 18; i++) {
if(i == 0){
//9行0列
cell13 = row3.createCell((short) i);
cell13.setCellStyle(cellStyle_zc);
}else{
//9行i列
cell13 = row3.createCell((short) i);
cell13.setCellStyle(cellStyle_zr);
}
}
//10行
row3 = sheet3.createRow(10);
//10行0列
cell13 = row3.createCell((short) 0);
cell13.setCellStyle(cellStyle_zc);
cell13.setCellValue("女");
//10行1列
cell13 = row3.createCell((short) 1);
cell13.setCellStyle(cellStyle_zr);
if("666".equals(pay_cdFlg)){
cellFormula = "SUMIF('"+ymdyyyymmdd+"'!O:O,"+"\""+"女"+"\""+",'"+ymdyyyymmdd+"'!N:N)";
}else{
cellFormula = "SUMIF('"+ymdyyyymmdd+"'!N:N,"+"\""+"女"+"\""+",'"+ymdyyyymmdd+"'!M:M)";
}
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//10行2列
cell13 = row3.createCell((short) 2);
cell13.setCellStyle(cellStyle_zr);
if("666".equals(pay_cdFlg)){
cellFormula = "SUMIF('"+ymdyyyymmdd+"明細'!E:E,"+"\""+"女"+"\""+",'"+ymdyyyymmdd+"明細'!V:V)";
}else{
cellFormula = "SUMIF('"+ymdyyyymmdd+"明細'!E:E,"+"\""+"女"+"\""+",'"+ymdyyyymmdd+"明細'!U:U)";
}
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//10行3列
cell13 = row3.createCell((short) 3);
cell13.setCellStyle(cellStyle_zr_s);
if("666".equals(pay_cdFlg)){
cellFormula = "SUMIF('"+ymdyyyymmdd+"明細'!E:E,"+"\""+"女"+"\""+",'"+ymdyyyymmdd+"明細'!H:H)";
}else{
cellFormula = "SUMIF('"+ymdyyyymmdd+"明細'!E:E,"+"\""+"女"+"\""+",'"+ymdyyyymmdd+"明細'!G:G)";
}
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//10行4列
cell13 = row3.createCell((short) 4);
cell13.setCellStyle(cellStyle_zr);
//10行5列
cell13 = row3.createCell((short) 5);
cell13.setCellStyle(cellStyle_zr);
//10行6列
cell13 = row3.createCell((short) 6);
cell13.setCellStyle(cellStyle_zr_j);
if("666".equals(pay_cdFlg)){
cellFormula = "SUMIF('"+ymdyyyymmdd+"明細'!E:E,"+"\""+"女"+"\""+",'"+ymdyyyymmdd+"明細'!I:I)";
}else{
cellFormula = "SUMIF('"+ymdyyyymmdd+"明細'!E:E,"+"\""+"女"+"\""+",'"+ymdyyyymmdd+"明細'!H:H)";
}
cell13.setCellFormula(cellFormula);
//10行7列
cell13 = row3.createCell((short) 7);
cell13.setCellStyle(cellStyle_zr);
//10行8列
cell13 = row3.createCell((short) 8);
cell13.setCellStyle(cellStyle_zr);
//10行9列
cell13 = row3.createCell((short) 9);
cell13.setCellStyle(cellStyle_zr_j);
cellFormula = "G11+H11+I11";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//10行10列
cell13 = row3.createCell((short) 10);
cell13.setCellStyle(cellStyle_zr_j);
if("666".equals(pay_cdFlg)){
cellFormula = "SUMIF('"+ymdyyyymmdd+"明細'!E:E,"+"\""+"女"+"\""+",'"+ymdyyyymmdd+"明細'!L:L)";
}else{
cellFormula = "SUMIF('"+ymdyyyymmdd+"明細'!E:E,"+"\""+"女"+"\""+",'"+ymdyyyymmdd+"明細'!K:K)";
}
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//10行11列
cell13 = row3.createCell((short) 11);
cell13.setCellStyle(cellStyle_zr_j);
cellFormula = "SUM(J11:K11)";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//10行12列
cell13 = row3.createCell((short) 12);
cell13.setCellStyle(cellStyle_zr);
//10行13列
cell13 = row3.createCell((short) 13);
cell13.setCellStyle(cellStyle_zr);
//10行14列
cell13 = row3.createCell((short) 14);
cell13.setCellStyle(cellStyle_zr);
//10行15列
cell13 = row3.createCell((short) 15);
cell13.setCellStyle(cellStyle_zr_j);
if("666".equals(pay_cdFlg)){
cellFormula = "SUMIF('"+ymdyyyymmdd+"明細'!E:E,"+"\""+"女"+"\""+",'"+ymdyyyymmdd+"明細'!P:P)";
}else{
cellFormula = "SUMIF('"+ymdyyyymmdd+"明細'!E:E,"+"\""+"女"+"\""+",'"+ymdyyyymmdd+"明細'!O:O)";
}
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//10行16列
cell13 = row3.createCell((short) 16);
cell13.setCellStyle(cellStyle_zr_j);
cellFormula = "P11";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//10行17列
cell13 = row3.createCell((short) 17);
cell13.setCellStyle(cellStyle_zr_j);
cellFormula = "L11-Q11";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//11行
row3 = sheet3.createRow(11);
//行の高さ
row3.setHeightInPoints(16);
for (int i = 0; i < 18; i++) {
if(i == 0){
//11行0列
cell13 = row3.createCell((short) i);
cell13.setCellStyle(cellStyle_zxr_l);
}else{
//11行i列
cell13 = row3.createCell((short) i);
cell13.setCellStyle(cellStyle_zxr);
}
}
//12行
row3 = sheet3.createRow(12);
//行の高さ
row3.setHeightInPoints(16);
for (int i = 0; i < 18; i++) {
if(i == 0){
//12行0列
cell13 = row3.createCell((short) i);
cell13.setCellStyle(cellStyle_zc);
}else{
//12行i列
cell13 = row3.createCell((short) i);
cell13.setCellStyle(cellStyle_zr);
}
}
//13行
row3 = sheet3.createRow(13);
//13行0列
cell13 = row3.createCell((short) 0);
cell13.setCellStyle(cellStyle_zc);
cell13.setCellValue("合 計");
//13行1列
cell13 = row3.createCell((short) 1);
cell13.setCellStyle(cellStyle_zr);
cellFormula = "B11+B8";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//13行2列
cell13 = row3.createCell((short) 2);
cell13.setCellStyle(cellStyle_zr);
cellFormula = "C11+C8";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//13行3列
cell13 = row3.createCell((short) 3);
cell13.setCellStyle(cellStyle_zr_s);
cellFormula = "D11+D8";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//13行4列
cell13 = row3.createCell((short) 4);
cell13.setCellStyle(cellStyle_zr_j);
cellFormula = "E11+E8";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//13行5列
cell13 = row3.createCell((short) 5);
cell13.setCellStyle(cellStyle_zr_j);
cellFormula = "F11+F8";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//13行6列
cell13 = row3.createCell((short) 6);
cell13.setCellStyle(cellStyle_zr_j);
cellFormula = "G11+G8";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//13行7列
cell13 = row3.createCell((short) 7);
cell13.setCellStyle(cellStyle_zr_j);
cellFormula = "H11+H8";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//13行8列
cell13 = row3.createCell((short) 8);
cell13.setCellStyle(cellStyle_zr_j);
cellFormula = "I11+I8";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//13行9列
cell13 = row3.createCell((short) 9);
cell13.setCellStyle(cellStyle_zr_j);
cellFormula = "J11+J8";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//13行10列
cell13 = row3.createCell((short) 10);
cell13.setCellStyle(cellStyle_zr_j);
cellFormula = "K11+K8";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//13行11列
cell13 = row3.createCell((short) 11);
cell13.setCellStyle(cellStyle_zr_j);
cellFormula = "L11+L8";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//13行12列
cell13 = row3.createCell((short) 12);
cell13.setCellStyle(cellStyle_zr_j);
cellFormula = "M11+M8";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//13行13列
cell13 = row3.createCell((short) 13);
cell13.setCellStyle(cellStyle_zr_j);
cellFormula = "N11+N8";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//13行14列
cell13 = row3.createCell((short) 14);
cell13.setCellStyle(cellStyle_zr_j);
cellFormula = "O11+O8";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//7行15列
cell13 = row3.createCell((short) 15);
cell13.setCellStyle(cellStyle_zr_j);
cellFormula = "P11+P8";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//13行16列
cell13 = row3.createCell((short) 16);
cell13.setCellStyle(cellStyle_zr_j);
cellFormula = "Q11+Q8";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//13行17列
cell13 = row3.createCell((short) 17);
cell13.setCellStyle(cellStyle_zr_j);
cellFormula = "R11+R8";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//14行
row3 = sheet3.createRow(14);
//行の高さ
row3.setHeightInPoints(16);
for (int i = 0; i < 18; i++) {
if(i==0){
//14行i列
cell13 = row3.createCell((short) i);
cell13.setCellStyle(cellStyle_zxr_l);
}else{
//14行i列
cell13 = row3.createCell((short) i);
cell13.setCellStyle(cellStyle_zxr);
}
}
sheet3.setColumnWidth(0,350*4);
sheet3.setColumnWidth(1,500*4);
sheet3.setColumnWidth(2,500*4);
sheet3.setColumnWidth(3,500*4);
sheet3.setColumnWidth(4,500*4);
sheet3.setColumnWidth(5,500*4);
合并单元格
//合并单元格
//開始行、終了行、開始列、終了列
region = new CellRangeAddress(4,5,2,2);
sheet3.addMergedRegion(region);
//行取得
cell13 = row3.createCell((short) 2);
//样式
cell13.setCellStyle(cellStyle03);
//合并单元格设置左边框 参数1：线条、参数2 合并的单元格、参数3：sheet页、参数3：表格
RegionUtil.setBorderLeft((short) 1, region,sheet3, wb);
//合并单元格设置上边框
RegionUtil.setBorderTop((short) 1, region,sheet3, wb);
//合并单元格设置下边框
RegionUtil.setBorderBottom((short) 1, region,sheet3, wb);
//合并单元格设置右边框
RegionUtil.setBorderRight((short) 1, region,sheet3, wb);
表格中使用函数
String cellFormula = "";
//函数使用
cellFormula = "SUMIF('"+ymdyyyymmdd+"'!O:O,"+"\""+"男"+"\""+",'"+ymdyyyymmdd+"'!N:N)";
cell13.setCellType(cell13.CELL_TYPE_FORMULA);
cell13.setCellFormula(cellFormula);
//在表格的最后需要设置函数强制使用
sheet.setForceFormulaRecalculation(true);
打印区域设置
//ページを開始します。下付き0開始,開始列、終了列,開始行、終了行
wb.setPrintArea(0, 0, rowSum1-2, 0, excelAllList.size());
最后效果图
426
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
