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

最新推荐文章于 2021-03-03 14:18:31 发布
最新推荐文章于 2021-03-03 14:18:31 发布
阅读量323 收藏
点赞数
分类专栏： java从入门到放弃 文章标签： poi
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhang8790777/article/details/109811063
版权

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("ＭＳ Ｐゴシック");
		font.setFontHeightInPoints((short) 20);
		style.setFont(font);
		
		//创建字体及大小及加粗
		HSSFFont font_top = wb.createFont();
		font_top.setFontName("ＭＳ Ｐゴシック");
		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("仕訳Ｎｏ");
		cell.setCellStyle(style1);
		cell = row.createCell((int) 1);
		cell.setCellValue("");
		cell.setCellStyle(style1);
		cell = row.createCell((int) 2);
		cell.setCellValue("顧客ＣＤ");
		cell.setCellStyle(style1);
		cell = row.createCell((int) 3);
		cell.setCellValue("依頼Ｎｏ");
		cell.setCellStyle(style1);
		cell = row.createCell((int) 4);
		cell.setCellValue("Ｇ");
		cell.setCellStyle(style1);
		cell = row.createCell((int) 5);
		cell.setCellValue("Ｎｏ");
		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();
}

做成之后的效果图

在这里插入图片描述

zhang8790777
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Java利用poi导出excel表格
07-23
Java利用poi导出excel表格
POI导出Excel工具类，自动设置标题 列名 文件名，可插入图片，合并单元格
12-06
POI导出Excel工具类，自动设置标题 列名 文件名，可插入图片，合并单元格
@PathVariable注解参数带点号导致被截取的解决办法
qq_44688861的博客
03-03 448
项目场景： 今天再做博瑞网站下载的时候，使用PathVariable出现了问题。 问题描述： 提供下载的时候，访问地址是localhost:8080/download/A9.zip,但是拿到的fileName只有A9 @GetMapping("download/{fileName}") @ApiOperation("下载文件，需要传递") public ResponseEntity downLoadFile(@PathVariable("fileName") String fileN
POI表格导出
dream_heheda的博客
05-17 357
1.POI导出表格时，设置单元格的格式https://www.cnblogs.com/abc8023/p/5843230.htmlhttps://blog.csdn.net/huluedeai/article/details/50544242 import java.io.IOException; import java.io.OutputStream; import java.math.BigD...
poi-导出excel表格
LiGuogang
11-30 372
1.效果图 2.Java代码 /** * * @Title: exportExcel * @Description: 导出excel * @param request * @param response * @return * @throws Exception * @author 大都督 * @date 2018年11月30日 * @return String ...
超级简单POI导出Excel实战
diexun4203的博客
08-30 1200
在一般的生产管理系统都会将数据通过页面导出到Excel，这里以Java为例通过第三方开源poi进行对Excel的操作，具体操作如下 1.引入jar包依赖 这里我以maven的方式引入jar包，具体依赖如下 <dependency> <groupId>cn.afterturn</groupId> <artifactId...
Java Poi 导出excel（支持各种设置字体、颜色、垂直居中）
03-01
Java Poi 导出excel（支持各种设置字体、颜色、垂直居中）
POI操作Excel合并单元格边框问题解决方法
12-21
POI操作Excel 合并单元格 边框问题解决方法，这个方法是我亲自测试，并运用于项目中的，可以放心使用，还一并写出了很多注释，其中有写poi的bug的解决方法
poi导入/导出Excel表格，合并单元格的读取和设置
03-25
poi导入/导出Excel表格，合并单元格的读取和设置
excel poi工具类 导入 导出 合并单元格 计算公式
01-07
excel poi工具类 导入 导出 合并单元格 计算公式
poi导出，导出excel文件
03-16
用poi导出，导出excel文件，写的一些例子。代码简洁明了，通俗易懂。
poi(java导出excel表jar包)
12-03
使用java语言导出数据到excel表（xls格式）所必须的jar包
Java利用poi导出excel表格.zip
07-09
Java利用poi导出excel表格.zipJava利用poi导出excel表格.zip
POI——导出表格并下载
时光·漫步的博客
05-23 697
对 poi 操作的封装——将数据填进HSSFWorkbook package com.hq.utils; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFShee...
java poi导出excel表_java POI导出excel表格
weixin_29533443的博客
02-24 160
1、异常java.lang.NoClassDefFoundError: org/apache/poi/UnsupportedFileFormatException解决方法:使用的poi的相关jar包一定版本一定要相同！！！！！2.引入jar包org.apache.poipoi3.9org.apache.poipoi-ooxml3.93.设置工作簿//创建工作簿HSSFWorkbook workbo...
POI导出Word表格
OhhZoey的博客
08-24 1558
正文开始 网上很有多拿到word模板然后对其赋值的例子我就不多说了 我这边就只记录创建document table public InputStream exportWordContainTable(){ try { ByteArrayOutputStream os = new ByteArrayOutputStream(); ...
POI导出EXCEL
鸠摩智
05-22 416
项目不兼容别人开源的轮子 写了个读取导入 唉 还要造轮子 简直了，，，老项目已经改不动了 只能造一个差不多的轮子安上跑吧 导出 导出 导出 import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.Ind...
java使用POI导出excel表格
wonderful Code
02-25 1171
转载出处：https://blog.csdn.net/l1028386804/article/details/79659605 一.首先导入poi工具所需的依赖包 &lt;!--POI操作excel--&gt; &lt;dependency&gt; &lt;groupId&gt;org.apache.poi&lt;/groupId&gt; &lt;artifactId&gt;p...
java 利用poi导出表格
合着我是程序白的博客
01-02 286
public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,String [][]values, HSSFWorkbook wb){ // 第一步，创建一个HSSFWorkbook，对应一个Excel文件 if(wb == null){ wb = new HSSFWor...
使用POI 导出Excel 动态合并单元格
最新发布
05-30
在使用POI导出Excel时，可以使用以下代码动态合并单元格： ```java // 创建一个合并单元格的区域 CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex + mergeRowCount - 1, colIndex, colIndex + ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值