欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

记录一下 使用POI 操作EXCEL表格导出 控制字体单元格颜色,单元格合编等等

程序员文章站 2022-04-30 14:02:11
...

java

`
//需要导入的包,可能缺少部分
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.HSSFColor;
import org.apache.poi.hssf.util.HSSFColor.BLACK;
import org.apache.poi.hssf.util.HSSFColor.GREY_25_PERCENT;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;

public String downExcelSummary() throws Exception {
//创建EXCEL
HSSFWorkbook wb = new HSSFWorkbook();
//Sheet名
HSSFSheet sheet = wb.createSheet(“経理入力プロ 入力精度 詳細確認表”);
//设置部分列宽,这里只设置了 3/6/7/12列的宽度
sheet.setColumnWidth(6, 12 * 256);
sheet.setColumnWidth(7, 12 * 256);
sheet.setColumnWidth(12, 30 * 256);
sheet.setColumnWidth(3, 18 * 256);
//创建第一行
HSSFRow row = sheet.createRow((int) 0);
HSSFCellStyle style = wb.createCellStyle();

	//创建字体及大小
	HSSFFont font = wb.createFont();
	font.setFontName("MS Pゴシック");
	font.setFontHeightInPoints((short) 20);
	style.setFont(font);
	
	//创建字体及大小及加粗
	HSSFFont font_top = wb.createFont();
	font_top.setFontName("MS Pゴシック");
	font_top.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
	font_top.setFontHeightInPoints((short) 10);
	
	//设置样式,单元格边框,背景色,字体居中
	//*にある
	HSSFCellStyle style_top = wb.createCellStyle();
	style_top.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	style_top.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	style_top.setBorderRight(HSSFCellStyle.BORDER_THIN);
	style_top.setBorderTop(HSSFCellStyle.BORDER_THIN);
	style_top.setBottomBorderColor(BLACK.index);
	style_top.setLeftBorderColor(BLACK.index);
	style_top.setRightBorderColor(BLACK.index);
	style_top.setTopBorderColor(BLACK.index);
	style_top.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	style_top.setFillForegroundColor(GREY_25_PERCENT.index);
	style_top.setFillPattern(CellStyle.SOLID_FOREGROUND);
	style_top.setFont(font_top);
	//设置样式,单元格边框,背景色,字体居右
	//右に立つ
	HSSFCellStyle style_top_r_ = wb.createCellStyle();
	style_top_r_.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	style_top_r_.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	style_top_r_.setBorderRight(HSSFCellStyle.BORDER_THIN);
	style_top_r_.setBorderTop(HSSFCellStyle.BORDER_THIN);
	style_top_r_.setBottomBorderColor(BLACK.index);
	style_top_r_.setLeftBorderColor(BLACK.index);
	style_top_r_.setRightBorderColor(BLACK.index);
	style_top_r_.setTopBorderColor(BLACK.index);
	style_top_r_.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
	//style_top_r_.setFillForegroundColor(GREY_25_PERCENT.index);
	style_top_r_.setFont(font_top);
	//设置样式,单元格边框,背景色,字体居左
	//左にある
	HSSFCellStyle style_top_l_ = wb.createCellStyle();
	style_top_l_.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	style_top_l_.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	style_top_l_.setBorderRight(HSSFCellStyle.BORDER_THIN);
	style_top_l_.setBorderTop(HSSFCellStyle.BORDER_THIN);
	style_top_l_.setBottomBorderColor(BLACK.index);
	style_top_l_.setLeftBorderColor(BLACK.index);
	style_top_l_.setRightBorderColor(BLACK.index);
	style_top_l_.setTopBorderColor(BLACK.index);
	style_top_l_.setAlignment(HSSFCellStyle.ALIGN_LEFT);
	//style_top_r_.setFillForegroundColor(GREY_25_PERCENT.index);
	style_top_l_.setFont(font_top);
	
	//设置自定义表头
	HSSFCell cell = row.createCell((int) 3);
	cell.setCellValue("経理入力プロ 入力精度 詳細確認表");
	cell.setCellStyle(style);
	row = sheet.createRow((int) 2);
	cell = row.createCell((int) 0);
	cell.setCellValue("対象期間");
	
	cell.setCellStyle(style_top);
	cell = row.createCell((int) 1);
	cell.setCellValue("");
	cell.setCellStyle(style_top);
	
	cell = row.createCell((int) 2);
	cell.setCellValue( startYmd + "~" + endYmd);
	cell.setCellStyle(style_top_l_);
	
	cell = row.createCell((int) 3);
	cell.setCellValue("");
	cell.setCellStyle(style_top);
	cell = row.createCell((int) 4);
	cell.setCellValue("");
	cell.setCellStyle(style_top);

	
	cell = row.createCell((int) 6);
	cell.setCellValue("入力総数" );	
	cell.setCellStyle(style_top);
	
	cell = row.createCell((int) 7);
	cell.setCellValue(aiJournalId);	
	cell.setCellStyle(style_top_r_);
	
	cell = row.createCell((int) 10);
	cell.setCellValue("精度率" );
	cell.setCellStyle(style_top);
	
	cell = row.createCell((int) 11);
	cell.setCellValue(accuracyRate+"%");
	cell.setCellStyle(style_top_r_);

	row = sheet.createRow((int) 3);
	cell = row.createCell((int) 0);
	cell.setCellValue("対象者");
	cell.setCellStyle(style_top);
	cell = row.createCell((int) 1);
	cell.setCellValue("");
	cell.setCellStyle(style_top_l_);
	
	cell = row.createCell((int) 2);
	cell.setCellValue(empName);
	cell.setCellStyle(style_top_l_);
	
	cell = row.createCell((int) 3);
	cell.setCellValue("");
	cell.setCellStyle(style_top);
	cell = row.createCell((int) 4);
	cell.setCellValue("");
	cell.setCellStyle(style_top);

	
	cell = row.createCell((int) 6);
	cell.setCellValue("誤s入力数" );	
	cell.setCellStyle(style_top);
	
	cell = row.createCell((int) 7);
	cell.setCellValue(ai_verify_err_cnt);	
	cell.setCellStyle(style_top_r_);
	//设置自定义表头结束
	//合编自定义表头
	Region region0 = new Region(2, (short) 0, 2, (short) 1);
	//パラメータ1:行*パラメータ2:開始列*パラメータ3:行*パラメータ4:終了列*
	sheet.addMergedRegion(region0);
	
	Region region00 = new Region(2, (short) 2, 2, (short) 4);
	//パラメータ1:行*パラメータ2:開始列*パラメータ3:行*パラメータ4:終了列*
	sheet.addMergedRegion(region00);
	
	Region region2 = new Region(3, (short) 0, 3, (short) 1);
	//パラメータ1:行*パラメータ2:開始列*パラメータ3:行*パラメータ4:終了列*
	sheet.addMergedRegion(region2);
	
	Region region22 = new Region(3, (short) 2, 3, (short) 4);
	//パラメータ1:行*パラメータ2:開始列*パラメータ3:行*パラメータ4:終了列*
	sheet.addMergedRegion(region22);
	
	Region region3 = new Region(2, (short) 6, 2, (short) 6);
	//パラメータ1:行*パラメータ2:開始列*パラメータ3:行*パラメータ4:終了列*
	sheet.addMergedRegion(region3);
	
	Region region4 = new Region(3, (short) 6, 3, (short) 6);
	//パラメータ1:行*パラメータ2:開始列*パラメータ3:行*パラメータ4:終了列*
	sheet.addMergedRegion(region4);
	
	Region region5 = new Region(2, (short) 9, 2, (short) 9);
	//パラメータ1:行*パラメータ2:開始列*パラメータ3:行*パラメータ4:終了列*
	sheet.addMergedRegion(region5);
	//合编自定义表头结束
	//创建固定表头
	HSSFCellStyle style1 = wb.createCellStyle();
	style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
	style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
	style1.setBottomBorderColor(BLACK.index);
	style1.setLeftBorderColor(BLACK.index);
	style1.setRightBorderColor(BLACK.index);
	style1.setTopBorderColor(BLACK.index);
	style1.setFillForegroundColor(GREY_25_PERCENT.index);
	// style1.setFillBackgroundColor(GREY_25_PERCENT.index);
	style1.setFillPattern(CellStyle.SOLID_FOREGROUND);
	style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	style1.setFont(font_top);
	row = sheet.createRow((int) 5);
	cell = row.createCell((int) 0);
	cell.setCellValue("仕訳No");
	cell.setCellStyle(style1);
	cell = row.createCell((int) 1);
	cell.setCellValue("");
	cell.setCellStyle(style1);
	cell = row.createCell((int) 2);
	cell.setCellValue("顧客CD");
	cell.setCellStyle(style1);
	cell = row.createCell((int) 3);
	cell.setCellValue("依頼No");
	cell.setCellStyle(style1);
	cell = row.createCell((int) 4);
	cell.setCellValue("G");
	cell.setCellStyle(style1);
	cell = row.createCell((int) 5);
	cell.setCellValue("No");
	cell.setCellStyle(style1);
	cell = row.createCell((int) 6);
	cell.setCellValue("入力日");
	cell.setCellStyle(style1);
	cell = row.createCell((int) 7);
	cell.setCellValue("支払日");
	cell.setCellStyle(style1);
	cell = row.createCell((int) 8);
	cell.setCellValue("勘定科目");
	cell.setCellStyle(style1);
	cell = row.createCell((int) 9);
	cell.setCellValue("税区分");
	cell.setCellStyle(style1);
	cell = row.createCell((int) 10);
	cell.setCellValue("金額");
	cell.setCellStyle(style1);
	cell = row.createCell((int) 11);
	cell.setCellValue("消費税額");
	cell.setCellStyle(style1);
	cell = row.createCell((int) 12);
	cell.setCellValue("会社名");
	cell.setCellStyle(style1);
	//创建固定表头结束
	
	//这里是要展示的数据sssssss
	//请换成自己的数据
	//ベリファイ修正履歴を抽出
	List<TblVerifyRireki> verifyList= keiriService.getTblVerifyRirekiAll(startYmdNew,endYmdNew,aiInputShainCd);
	//
	String ai_journal_id ="";
	for(int i = 0 ; i < verifyList.size() ; i++){
		TblVerifyRireki vo = new TblVerifyRireki();
		vo = verifyList.get(i);
		ai_journal_id += vo.getAi_journal_id()+",";
	}
	List<TblAiJournal> aiList = new ArrayList<TblAiJournal>();
	if(ai_journal_id.length() > 0){
		ai_journal_id = ai_journal_id.substring(0,ai_journal_id.length()-1);
		aiList= keiriService.getTblAiJournalAll(ai_journal_id);
	}

//这里是要展示的数据sssssss结束
//循环展示数据
//这里展示的是二张表的数据,数据正确表,以及错误数据表
//客户要求是对比出2张表的数据不同以红色显示
int nn= 0;
int mm =0;
//フォントオブジェクトを宣言します。
Font font1 = null;
//フォントオブジェクトを作成
font1 = wb.createFont();
//フォントオブジェクトに色の属性を設定します。
font1.setColor(HSSFColor.RED.index);

    //フォントオブジェクトを宣言します。
    Font font2 = null;
    //フォントオブジェクトを作成
    font2 = wb.createFont();
    //フォントオブジェクトに色の属性を設定します。
    font2.setColor(HSSFColor.BLACK.index);
   
   
	
	//右に立つ
	HSSFCellStyle style2 = wb.createCellStyle();
	style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
	style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
	style2.setBottomBorderColor(BLACK.index);
	style2.setLeftBorderColor(BLACK.index);
	style2.setRightBorderColor(BLACK.index);
	style2.setTopBorderColor(BLACK.index);
	style2.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
	
	//*にある
	HSSFCellStyle style3 = wb.createCellStyle();
	style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	style3.setBorderRight(HSSFCellStyle.BORDER_THIN);
	style3.setBorderTop(HSSFCellStyle.BORDER_THIN);
	style3.setBottomBorderColor(BLACK.index);
	style3.setLeftBorderColor(BLACK.index);
	style3.setRightBorderColor(BLACK.index);
	style3.setTopBorderColor(BLACK.index);
	style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上と下を*に置く
	
	//左にある
	HSSFCellStyle style4 = wb.createCellStyle();
	style4.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	style4.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	style4.setBorderRight(HSSFCellStyle.BORDER_THIN);
	style4.setBorderTop(HSSFCellStyle.BORDER_THIN);
	style4.setBottomBorderColor(BLACK.index);
	style4.setLeftBorderColor(BLACK.index);
	style4.setRightBorderColor(BLACK.index);
	style4.setTopBorderColor(BLACK.index);
	style4.setAlignment(HSSFCellStyle.ALIGN_LEFT);
	
	//*にある
	//背景色TAN
	HSSFCellStyle style11 = wb.createCellStyle();
	style11.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	style11.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	style11.setBorderRight(HSSFCellStyle.BORDER_THIN);
	style11.setBorderTop(HSSFCellStyle.BORDER_THIN);
	style11.setBottomBorderColor(BLACK.index);
	style11.setLeftBorderColor(BLACK.index);
	style11.setRightBorderColor(BLACK.index);
	style11.setTopBorderColor(BLACK.index);
	style11.setFillForegroundColor(IndexedColors.TAN.getIndex());
	style11.setFillPattern(CellStyle.SOLID_FOREGROUND);
	style11.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	style11.setFont(font2);
	
	//左にある
	//背景色TAN
	HSSFCellStyle style12 = wb.createCellStyle();
	style12.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	style12.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	style12.setBorderRight(HSSFCellStyle.BORDER_THIN);
	style12.setBorderTop(HSSFCellStyle.BORDER_THIN);
	style12.setBottomBorderColor(BLACK.index);
	style12.setLeftBorderColor(BLACK.index);
	style12.setRightBorderColor(BLACK.index);
	style12.setTopBorderColor(BLACK.index);
	style12.setFillForegroundColor(IndexedColors.TAN.getIndex());
	style12.setFillPattern(CellStyle.SOLID_FOREGROUND);
	style12.setAlignment(HSSFCellStyle.ALIGN_LEFT);
	style12.setFont(font2);
	
	//右に立つ
	//背景色TAN
	HSSFCellStyle style13 = wb.createCellStyle();
	style13.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	style13.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	style13.setBorderRight(HSSFCellStyle.BORDER_THIN);
	style13.setBorderTop(HSSFCellStyle.BORDER_THIN);
	style13.setBottomBorderColor(BLACK.index);
	style13.setLeftBorderColor(BLACK.index);
	style13.setRightBorderColor(BLACK.index);
	style13.setTopBorderColor(BLACK.index);
	style13.setFillForegroundColor(IndexedColors.TAN.getIndex());
	style13.setFillPattern(CellStyle.SOLID_FOREGROUND);
	style13.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
	style13.setFont(font2);
	
	//*にある
	//背景色TAN
	//フォントの色
	HSSFCellStyle style111 = wb.createCellStyle();
	style111.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	style111.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	style111.setBorderRight(HSSFCellStyle.BORDER_THIN);
	style111.setBorderTop(HSSFCellStyle.BORDER_THIN);
	style111.setBottomBorderColor(BLACK.index);
	style111.setLeftBorderColor(BLACK.index);
	style111.setRightBorderColor(BLACK.index);
	style111.setTopBorderColor(BLACK.index);
	style111.setFillForegroundColor(IndexedColors.TAN.getIndex());
	style111.setFillPattern(CellStyle.SOLID_FOREGROUND);
	style111.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	style111.setFont(font1);
	
	//左にある
	//背景色TAN
	//フォントの色
	HSSFCellStyle style122 = wb.createCellStyle();
	style122.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	style122.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	style122.setBorderRight(HSSFCellStyle.BORDER_THIN);
	style122.setBorderTop(HSSFCellStyle.BORDER_THIN);
	style122.setBottomBorderColor(BLACK.index);
	style122.setLeftBorderColor(BLACK.index);
	style122.setRightBorderColor(BLACK.index);
	style122.setTopBorderColor(BLACK.index);
	style122.setFillForegroundColor(IndexedColors.TAN.getIndex());
	style122.setFillPattern(CellStyle.SOLID_FOREGROUND);
	style122.setAlignment(HSSFCellStyle.ALIGN_LEFT);
	style122.setFont(font1);
	//右に立つ
	//背景色TAN
	//フォントの色
	HSSFCellStyle style133 = wb.createCellStyle();
	style133.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	style133.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	style133.setBorderRight(HSSFCellStyle.BORDER_THIN);
	style133.setBorderTop(HSSFCellStyle.BORDER_THIN);
	style133.setBottomBorderColor(BLACK.index);
	style133.setLeftBorderColor(BLACK.index);
	style133.setRightBorderColor(BLACK.index);
	style133.setTopBorderColor(BLACK.index);
	style133.setFillForegroundColor(IndexedColors.TAN.getIndex());
	style133.setFillPattern(CellStyle.SOLID_FOREGROUND);
	style133.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
	style133.setFont(font1);
	
	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	for (int i = 0; i < verifyList.size(); i++) {
		TblVerifyRireki vo =verifyList.get(i);
		TblAiJournal aiVo= aiList.get(i);
		Region region1 = new Region(6+nn, (short) 0, 7+nn, (short) 0);
		//パラメータ1:行*パラメータ2:開始列*パラメータ3:行*パラメータ4:終了列*
		sheet.addMergedRegion(region1);
		row = sheet.createRow((int) 6 + nn);
		cell = row.createCell(0);
		cell.setCellValue(vo.getAi_journal_id());
		cell.setCellStyle(style3);
		cell = row.createCell(1);
		cell.setCellValue("入力者");
		cell.setCellStyle(style11);
		cell = row.createCell(2);
		cell.setCellValue(vo.getAi_ko_cd());
		cell.setCellStyle(style11);
		cell = row.createCell(3);
		cell.setCellValue(vo.getAi_order_no());
		cell.setCellStyle(style11);
		cell = row.createCell(4);
		cell.setCellValue(vo.getAi_group());
		cell.setCellStyle(style11);
		cell = row.createCell(5);
		cell.setCellValue(vo.getAi_input_no());
		cell.setCellStyle(style11);
		cell = row.createCell(6);
		cell.setCellValue(sdf.format(vo.getAi_input_ymd()));
		cell.setCellStyle(style12);
		cell = row.createCell(7);
		cell.setCellValue(sdf.format(vo.getAi_pay_ymd()));
		String Datedate1 = sdf.format(vo.getAi_pay_ymd());
		String Datedate2 = sdf.format(aiVo.getAi_pay_ymd());
		if(Datedate1.equals(Datedate2)){
			cell.setCellStyle(style12);
		}else{
			cell.setCellStyle(style122);
		}
		cell = row.createCell(8);
		cell.setCellValue(vo.getAi_debit_account_item());
		if(getString(vo.getAi_debit_account_item()).equals(getString(aiVo.getAi_debit_account_item()))){
			cell.setCellStyle(style12);
		}else{
			cell.setCellStyle(style122);
		}
		cell = row.createCell(9);
		cell.setCellValue(vo.getAi_debit_tax_type());
		if(getString(vo.getAi_debit_tax_type()).equals(getString(aiVo.getAi_debit_tax_type()))){
			cell.setCellStyle(style12);
		}else{
			cell.setCellStyle(style122);
		}
		cell = row.createCell(10);
		cell.setCellValue(vo.getAi_debit_pay_sum());
		if(getString(vo.getAi_debit_pay_sum()).equals(getString(aiVo.getAi_debit_pay_sum()))){
			cell.setCellStyle(style13);
		}else{
			cell.setCellStyle(style133);
		}
		cell = row.createCell(11);
		cell.setCellValue(vo.getAi_debit_tax_sum());
		if(getString(vo.getAi_debit_tax_sum()).equals(getString(aiVo.getAi_debit_tax_sum()))){
			cell.setCellStyle(style13);
		}else{
			cell.setCellStyle(style133);
		}
		cell = row.createCell(12);
		cell.setCellValue(vo.getAi_summary());
		if(getString(vo.getAi_summary()).equals(getString(aiVo.getAi_summary()))){
			cell.setCellStyle(style12);
		}else{
			cell.setCellStyle(style122);
		}
		
		nn += 2;
		row = sheet.createRow((int) 7 + mm);
		cell = row.createCell(0);
		cell.setCellValue("");
		cell.setCellStyle(style3);
		cell = row.createCell(1);
		cell.setCellValue("正解");
		cell.setCellStyle(style3);
		cell = row.createCell(2);
		cell.setCellValue(vo.getAi_ko_cd());
		cell.setCellStyle(style3);
		cell = row.createCell(3);
		cell.setCellValue(vo.getAi_order_no());
		cell.setCellStyle(style3);
		cell = row.createCell(4);
		cell.setCellValue(vo.getAi_group());
		cell.setCellStyle(style3);
		cell = row.createCell(5);
		cell.setCellValue(vo.getAi_input_no());
		cell.setCellStyle(style3);
		cell = row.createCell(6);
		cell.setCellValue(sdf.format(vo.getAi_input_ymd()));
		cell.setCellStyle(style4);
		cell = row.createCell(7);
		cell.setCellValue(sdf.format(aiVo.getAi_pay_ymd()));
		cell.setCellStyle(style4);
		cell = row.createCell(8);
		cell.setCellValue(aiVo.getAi_debit_account_item());
		cell.setCellStyle(style4);
		cell = row.createCell(9);
		cell.setCellValue(aiVo.getAi_debit_tax_type());
		cell.setCellStyle(style4);
		cell = row.createCell(10);
		cell.setCellValue(aiVo.getAi_debit_pay_sum());
		cell.setCellStyle(style2);
		cell = row.createCell(11);
		cell.setCellValue(aiVo.getAi_debit_tax_sum());
		cell.setCellStyle(style2);
		cell = row.createCell(12);
		cell.setCellValue(aiVo.getAi_summary());
		cell.setCellStyle(style4);
		mm += 2;
	}

	String fileName = "input_check_" + empName + "_" +startYmdOut + "-" + endYmdOut + "";
	this.getResponse().reset();
	ByteArrayOutputStream os = new ByteArrayOutputStream();
	wb.write(os);
	byte[] content = os.toByteArray();
	InputStream is = new ByteArrayInputStream(content);
	this.getResponse().setContentType("text/html;charset=SHIFT_JIS");
	this.getResponse().setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("SJIS"), "iso8859-1") + ".xls");
	ServletOutputStream out = this.getResponse().getOutputStream();
	BufferedInputStream bis = new BufferedInputStream(is);
	BufferedOutputStream bos = new BufferedOutputStream(out);
	byte[] buff = new byte[2048];
	int bytesRead;
	while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
		bos.write(buff, 0, bytesRead);
	}
	bis.close();
	bos.close();

}
`

做成之后的效果图

记录一下 使用POI 操作EXCEL表格导出 控制字体单元格颜色,单元格合编等等