Java Servlet 實現合併多單元格匯出Excel

來去匆匆的過客發表於2020-12-04

1、Excel匯出模板

在這裡插入圖片描述

2、按照模板得格式內容進行資料查詢匯出

建立 “Servlet” 類在 doPost中 進行匯出功能實現:

	@Override
	public void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		doPost(req, resp);
	}
	@Override
	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		/**
		 * <pre>
		 1. 接收引數集合
		 2. 必傳引數驗證
		 3. xss攻擊和防止sql注入
		 	3.1 xss攻擊
		 	3.2 防止sql注入
		 4. 獲取微服務地址 
		 5. 執行儲存過程
		 6. 建立excel並寫入表頭資訊
		 7. 將資料寫入到excel中
		 * </pre>
		 */

		// 程式開始-當前時間
		this.commonLogout(CommonUtils.getCurrentTime() + ",匯出 - start");

		JSONObject jObjectStr; // 返回給前端的結果物件
		String strRet = ""; // 返回給前端的結果
		JSONArray result = null; // 最終資料
		try {
			/* 1. 接收引數集合 */
			Map<String, Object> mapReqData = HttpRequestParse.parse(request);
			this.commonLogout(CommonUtils.getCurrentTime() + " 接收到的引數:" + mapReqData);
			// 如需要cookies 得值可通過以下方法取
		    Map myCookies = (Map) this.getCookieData(mapReqData);// 獲取cookie
			Map<String, String> mapReqParameterData = this.getMapWebArg(mapReqData); // 獲取"GET/POST請求資料"
			
		// 接收前端傳得條件引數
			// 請求使用者id
			String argUserId = "";
			// 租戶id
			String argTenantid = "";
			// 模組url
			String argModuleurl = "";
			// 主ID
			String developPlanId = "";
			// 版本號
			String argReqVersion = "";

			// 對前端得引數進行非空驗證
			argUserId = mapReqParameterData.get(ProjConstant.PrivilegeMgtConstant.FRONT_KEY_SYSMGT_ARG_REQ_USERID);
			argTenantid = mapReqParameterData.get(ProjConstant.PrivilegeMgtConstant.FRONT_KEY_SYSMGT_ARG_REQ_TENANTID);
			argModuleurl = mapReqParameterData
					.get(ProjConstant.PrivilegeMgtConstant.FRONT_KEY_SYSMGT_ARG_REQ_MODULEURL);
			developPlanId = mapReqParameterData.get("arg_req_develop_planId");
			argReqVersion = mapReqParameterData.get("arg_req_version");

			/* 2. 必傳引數驗證 */
			StringBuilder validateRet = new StringBuilder();
			boolean validateFlag = true;
			if (StringUtils.isEmpty(argUserId)) {
				validateFlag = false;
				validateRet.append("引數缺少使用者id!");
			} else if (StringUtils.isEmpty(argTenantid)) {
				validateFlag = false;
				validateRet.append("引數缺少租戶id!");
			} else if (StringUtils.isEmpty(argModuleurl)) {
				validateFlag = false;
				validateRet.append("引數缺少頁面路徑url!");
			} else if (StringUtils.isEmpty(developPlanId)) {
                validateFlag = false;
                validateRet.append("引數缺少主表關聯ID!");
            }else if (StringUtils.isEmpty(argReqVersion)) {
                validateFlag = false;
                validateRet.append("引數缺少版本號!");
            }

			if (!validateFlag) {
				this.commonLogout("前端傳值缺引數:" + validateRet.toString());
				throw new FrontReturnException("0", "操作失敗!");
			}

			/* 6. 建立excel並寫入表頭資訊 */
			XSSFWorkbook workBook = null; // 建立excel
			String excelName = "本單位研發專案清單匯出"; // excel名稱
			workBook = ExcelBPNewTool.createHeadForExcel('', null, (short) 0);
			String[] date = createDateKey(); // 返回的資料欄位 - **文章下有對應createDateKey方法名**

			// 5、 查詢資料庫需要匯出那些資料並進行返回
			String queryExistSql = " SELECT dp.*,og.name,op.last_name FROM cucc_project.develop_plan_list dp  "
                    + " left join org_organization og on dp.ou_id = og.organization_id "
                    + " left join org_person op on dp.create_user = op.employee_number "
					+ " WHERE dp.develop_plan_id = '" + developPlanId + "' and dp.version_upload = '" + argReqVersion + "'  order by dp.first_order asc ;";

			// 執行Sql 語句,以下查詢介面各位根據專案框架情況自由發揮
			JSONObject transResultExist = this.queryDb(sqlSelectUrl, queryExistSql);
			/* 7. 將資料寫入到excel中 */
			if (ProjConstant.ProjCommonStandardConstant.RET_CODE_VALUE_SUCCESS
					.equals(transResultExist.getString("RetCode"))) { // 存在BP詳情清單
				result = new JSONArray();
				// 判斷資料是否存在
				if (transResultExist.has("DataRows")) {
					String type = "正常";
					// 查詢 dataRows 獲取資料值
					String Query = transResultExist.get("DataRows").toString();
					JSONArray jsonArray = new JSONArray(Query);
					// 迴圈遍歷資料進入插入對應得表頭
					for (int i = 0; i < jsonArray.length(); i++) {
						JSONObject jsonObject = jsonArray.getJSONObject(i);
						JSONObject rolePersonsJsonObject = new JSONObject();
						// 優先排序 *
						rolePersonsJsonObject.put("arg_req_first_order",jsonObject.get("first_order").toString()); 
						// 單位id *
						rolePersonsJsonObject.put("arg_req_ou_id",jsonObject.get("name").toString());
						//部門名稱-主建部門 *
						rolePersonsJsonObject.put("arg_req_dept_name",jsonObject.get("dept_name").toString()); 
				       // 專業線 *
				       rolePersonsJsonObject.put("arg_req_majar_line_ou",jsonObject.get("major_line_ou").toString()); 
					   // 專案名稱 *			      
					   rolePersonsJsonObject.put("arg_req_proj_name",jsonObject.get("proj_name").toString()); 
				   // 應用主體 *
				   rolePersonsJsonObject.put("arg_req_majar_line_proj",jsonObject.get("major_line_proj").toString()); 
						BigDecimal mon1 = new BigDecimal(jsonObject.get("expense_expenditure").toString());
						BigDecimal mon2 = new BigDecimal(jsonObject.get("capitalize_expenditure").toString());
						BigDecimal money = mon1.add(mon2);
						rolePersonsJsonObject.put("arg_req_proj_money",money); // 研發專案經費總投入(萬元)
			rolePersonsJsonObject.put("arg_req_expense_expenditure",jsonObject.get("expense_expenditure").toString()); //費用化支出(萬元)
						rolePersonsJsonObject.put("arg_req_expense_entrust",jsonObject.get("expense_entrust").toString()); // 費用化支出-委託開發費用(萬元
rolePersonsJsonObject.put("arg_req_expense_own",jsonObject.get("expense_own").toString()); // 費用化支出-自有研發人工成本(萬元)
		rolePersonsJsonObject.put("arg_req_capitalize_expenditure",jsonObject.get("capitalize_expenditure").toString()); // 資本化支出(萬元)
						rolePersonsJsonObject.put("arg_req_capitalize_entrust",jsonObject.get("capitalize_entrust").toString()); // 資本化支出-委託開發費用(萬元)
						rolePersonsJsonObject.put("arg_req_capitalize_own",jsonObject.get("capitalize_own").toString()); // 資本化支出-自有研發人員工時(人年) *
						rolePersonsJsonObject.put("arg_req_develop_content",jsonObject.get("develop_content").toString()); // 主要研發內容 *
rolePersonsJsonObject.put("arg_req_proj_value",jsonObject.get("proj_value").toString()); // 預期研發成果200字左右 *
rolePersonsJsonObject.put("arg_req_create_user",jsonObject.get("last_name").toString()); // 處理人
						rolePersonsJsonObject.put("arg_req_create_time",jsonObject.get("create_time").toString().substring(0,19)); // 上傳時間
						if(jsonObject.get("proj_state").toString().equals("1")){
							type = "已修改";
						} else if(jsonObject.get("proj_state").toString().equals("2")){
							type = "已刪除";
						}
						rolePersonsJsonObject.put("arg_req_proj_state",type); // 狀態
						result.put(rolePersonsJsonObject);
					}

					// 向Excel寫入資料 
					ExcelBPNewTool.insertDataToExcel(workBook, result, "", date);
				} else {
					// 沒有查詢到角色資訊,向Excel寫入空資料
					ExcelBPNewTool.insertDataToExcel(workBook, result, "", date);
				}
				// 匯出Excel
				ExcelBPNewTool.exportExcelXSSF(request, response, workBook, excelName);
				
	// 以下程式碼 資料返回及異常丟擲,可根據專案框架情況自定
			} else {
				this.commonLogout("匯出 - 執行查詢出錯!");
				jObjectStr = new JSONObject();
				jObjectStr.put(ConstantUtility.StandardWebConstant.KEY_RETCODE,
						ProjConstant.ProjCommonStandardConstant.FRONT_RET_CODE_ERROR);
				jObjectStr.put(ConstantUtility.StandardWebConstant.KEY_RETVAL,
						ProjConstant.ProjCommonStandardConstant.FRONT_RET_VAL_ERROR);
				strRet = jObjectStr.toString();
			}
		} catch (Exception e) {
			this.commonLogout(e.getMessage());
			jObjectStr = new JSONObject();
			jObjectStr.put(ConstantUtility.StandardWebConstant.KEY_RETCODE,
					ProjConstant.ProjCommonStandardConstant.FRONT_RET_CODE_ERROR);
			jObjectStr.put(ConstantUtility.StandardWebConstant.KEY_RETVAL,
					ProjConstant.ProjCommonStandardConstant.FRONT_RET_VAL_ERROR);
			strRet = jObjectStr.toString();
		}

		this.commonLogout(strRet);
		response.getOutputStream().write(strRet.getBytes("UTF-8"));
		response.flushBuffer();

		// 專案結束 - 結束時間
		this.commonLogout(CommonUtils.getCurrentTime() + ",匯出 - end");
	}


	/**
	 * createDateKey:生成返回欄位,相當於Excel列表頭 對應資料
	 * @return
	 * @since JDK 1.8
	 */
	private String[] createDateKey() {

		// 返回的資料欄位
		String firstOrder = "arg_req_first_order";
		String ouId = "arg_req_ou_id";
		String deptName = "arg_req_dept_name";
		String majarLineOu= "arg_req_majar_line_ou";
		String projName= "arg_req_proj_name";
		String majarLineProj= "arg_req_majar_line_proj";
		String projMoney= "arg_req_proj_money";
		String expenseExpenditure = "arg_req_expense_expenditure";
		String expenseEntrust = "arg_req_expense_entrust";
		String expenseOwn = "arg_req_expense_own";
		String capitalizeExpenditure = "arg_req_capitalize_expenditure";
		String capitalizeEntrust = "arg_req_capitalize_entrust";
		String capitalizeOwn= "arg_req_capitalize_own";
		String developContent = "arg_req_develop_content";
		String projValue = "arg_req_proj_value";
		String createUser = "arg_req_create_user";
		String createTime = "arg_req_create_time";
		String projState = "arg_req_proj_state";

		return new String[] { firstOrder, ouId, deptName, majarLineOu, projName, majarLineProj, projMoney,expenseExpenditure,expenseEntrust
		,expenseOwn,capitalizeExpenditure,capitalizeEntrust,capitalizeOwn,developContent,projValue,createUser,createTime,projState};
}

​ 3、Excle匯出工具類

建立 “工具類(ExcelBPNewTool)” 在呼叫方法 進行匯出功能實現:


package com.chinaunicomrjyjy.cuccproject.utils;

import com.rjyjy.commonutils.BDUServiceBaseTools;
import com.rjyjy.loghandle.LogService;
import com.rjyjy.servicehandle.utility.ConstantUtility;
import org.apache.commons.codec.binary.Base64;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.json.JSONArray;
import org.json.JSONObject;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

/**
 * @ClassName ExcelTool
 * @Description excel匯出工具類
 * @Author chang
 * @version
 * @since JDK 1.8
 */
public class ExcelBPNewTool {

	private static final String CHARACTERENCODING = ConstantUtility.DefaultConfig.DEFAULTCONFIG_CHARACTERENCODING;

	private static final String SHEETNAME = "本單位研發專案清單";
	private static final String FONT = "宋體";
	private static final short FONTSIZE = 12;
	private static final String[] PARAMETERS = new String[] { SHEETNAME, FONT };

	ExcelBPNewTool() {

	}

	/**
	 * 
	 * createHeadForExcel:(建立表頭)僅有一個sheet,sheet預設Sheet1
	 * 
	 * @author haoz6
	 * @param headString
	 *            表頭陣列
	 * @param fontSize
	 *            表頭字型大小
	 * @return
	 * @since JDK 1.8
	 */
	@SuppressWarnings("deprecation")
	public static XSSFWorkbook createHeadForExcel(String[] headString,
			String[] parameters, short fontSize) {

		// Excel表頭相關
		int defaultRowHeight = 40;

		String[] parameters2 = parameters;
		short fontSize2 = fontSize;
		if (parameters2 == null) {
			parameters2 = PARAMETERS;
		}
		if (fontSize2 == 0) {
			fontSize2 = FONTSIZE;
		}
		// 建立excel
		XSSFWorkbook workbook = new XSSFWorkbook();
		// 建立sheet
		XSSFSheet sheet = workbook.createSheet(parameters2[0]);
		sheet.setDefaultRowHeightInPoints(defaultRowHeight);
		// 設定字型
		XSSFFont headfont = workbook.createFont();
		// 字型型別
		headfont.setFontName(parameters2[1]);
		// 字型大小
		headfont.setFontHeightInPoints(fontSize2);
		// 設定樣式
		XSSFCellStyle headstyle = workbook.createCellStyle();
		headstyle.setFont(headfont);
		// 左右居中
		headstyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		// 上下居中
		headstyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		// 邊框
		headstyle.setBorderBottom(BorderStyle.THIN);
		headstyle.setBorderLeft(BorderStyle.THIN);
		headstyle.setBorderRight(BorderStyle.THIN);
		headstyle.setBorderTop(BorderStyle.THIN);
		// 換行
		headstyle.setWrapText(true);

		int rowNum = 0;
		// 建立表頭行// 第1行,標題
		XSSFRow titleRow = sheet.createRow(0);
		titleRow.setHeight((short) 800);// 設定行高
		XSSFCell row1Cell1 = titleRow.createCell(0);
		row1Cell1.setCellValue("研發專案清單");
		row1Cell1.setCellStyle(headstyle);
		sheet.addMergedRegion(new CellRangeAddress(0,0,0,17));//標題 合併單元格


		//第二行
		XSSFRow row2 = sheet.createRow(1);
		row2.setHeight((short) 800);
		// 設定Excel 第二行表頭 
		String[] row_second = {"優先順序排序", "專案實施部門", "", "", "研發專案", "", "研發專案經費總投入(萬元)","專案主要投入分解","","","","","","主要研發內容和預期研發成果(200字左右簡要描述)","研發成果預期價值(成果未來應用場景及對生產經營的主要價值貢獻點)","處理人","上傳時間","狀態"};
		for (int i = 0; i < row_second.length; i++) {
			XSSFCell tempCell = row2.createCell(i);
			tempCell.setCellValue(row_second[i]);
			tempCell.setCellStyle(headstyle);
		}

		// 合併 設定每個表頭 進行跨行跨列,可對應匯出模板 進行了解
		sheet.addMergedRegion(new CellRangeAddress(1, 2, 0, 0));//優先順序排序
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 3));//專案實施部門
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 5));//研發專案
		sheet.addMergedRegion(new CellRangeAddress(1, 2, 6, 6));//研發專案經費總投入(萬元)
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 7, 12));//專案主要投入分解
		sheet.addMergedRegion(new CellRangeAddress(1, 2, 13, 13));//主要研發內容
		sheet.addMergedRegion(new CellRangeAddress(1, 2, 14, 14));//研發成果預期價值
		sheet.addMergedRegion(new CellRangeAddress(1, 2, 15, 15));//處理人
		sheet.addMergedRegion(new CellRangeAddress(1, 2, 16, 16));//上傳時間
		sheet.addMergedRegion(new CellRangeAddress(1, 2, 17, 17));//狀態


		//第三行
		XSSFRow row3 = sheet.createRow(2);
		row3.setHeight((short) 800);
		// 設定Excel 第三行表頭,合併在第二行得某個分類下, 可對應模板檢視 瞭解
		String[] row_third = {"", "單位名稱", "部門名稱(研發單元)", "研發單元所屬專業線", "專案名稱", "應用主體", "","費用化支出(萬元)","其中:委託開發費用(萬元)","其中:自有研發人工成本(萬元)","資本化支出(萬元)","其中:委託開發費用(萬元)","自有研發人員工時(人年)","","","","",""};
		for (int i = 0; i < row_third.length; i++) {
			XSSFCell tempCell = row3.createCell(i);
			tempCell.setCellValue(row_third[i]);
			tempCell.setCellStyle(headstyle);
		}

		// 設定Excel 列寬,也可以進行列寬 自適應 已下有方法
		sheet.setColumnWidth(0, 256*10+184);
		sheet.setColumnWidth(1, 256*20+184);
		sheet.setColumnWidth(2, 256*20+184);
		sheet.setColumnWidth(3, 256*20+184);
		sheet.setColumnWidth(4, 256*20+184);
		sheet.setColumnWidth(5, 256*20+184);
		sheet.setColumnWidth(6, 256*18+184);
		sheet.setColumnWidth(7, 256*11+184);
		sheet.setColumnWidth(8, 256*11+184);
		sheet.setColumnWidth(9, 256*11+184);
		sheet.setColumnWidth(10, 256*11+184);
		sheet.setColumnWidth(11, 256*11+184);
		sheet.setColumnWidth(12, 256*11+184);
		sheet.setColumnWidth(13, 256*30+184);
		sheet.setColumnWidth(14, 256*30+184);
		sheet.setColumnWidth(15, 256*11+184);
		sheet.setColumnWidth(16, 256*30+184);
		sheet.setColumnWidth(17, 256*8+184);
		return workbook;
	}


	/**
	 * 
	 * insertDataToExcel:向Excel中插入資料
	 * 
	 * @author haoz6
	 * @param workBook
	 * @param dataRowObject
	 * @since JDK 1.8
	 */
	public static void insertDataToExcel(XSSFWorkbook workBook,
			JSONObject dataRowObject, String[] headString) {
		JSONArray data = new JSONArray(dataRowObject
				.get(ConstantUtility.StandardWebConstant.KEY_DATAROWS)
				.toString());
		insertDataToExcel(workBook, data, null, headString);
	}

	/**
	 * 
	 * insertDataToExcel:向Excel中插入資料
	 * 
	 * @author haoz6
	 * @param workBook
	 * @since JDK 1.8
	 */
	@SuppressWarnings("deprecation")
	public static void insertDataToExcel(XSSFWorkbook workBook,
			JSONArray dataJsonArray, String sheetName, String[] headString) {
		// 判斷 建立sheet頁名稱
		String sheetName2 = "";
		if (sheetName == null||sheetName=="") {
			sheetName2 = SHEETNAME;
		} else {
			sheetName2 = sheetName;
		}
		/**
		 * 設定字型和樣式
		 */
		XSSFFont headfont = workBook.createFont();
		// 字型型別
		headfont.setFontName(FONT);
		// 字型大小
		headfont.setFontHeightInPoints(FONTSIZE);
		// 樣式
		XSSFCellStyle headstyle = workBook.createCellStyle();
		headstyle.setFont(headfont);
		/*// 左右居中
		headstyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);
		// 上下居中
		headstyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);*/
		// 豎向居中
		headstyle.setVerticalAlignment(VerticalAlignment.CENTER);
		// 橫向居中
		headstyle.setAlignment(HorizontalAlignment.CENTER);
		headstyle.setBorderBottom(BorderStyle.THIN);
		headstyle.setBorderLeft(BorderStyle.THIN);
		headstyle.setBorderRight(BorderStyle.THIN);
		headstyle.setBorderTop(BorderStyle.THIN);
		headstyle.setWrapText(true);
		JSONObject oneData = null;

		// 取Sheet
		XSSFSheet sheet = workBook.getSheet(sheetName2);
		JSONArray data = dataJsonArray;
		for (int i = 0; i < data.length(); i++) {
			oneData = (JSONObject) data.get(i);
			// 第2行
			XSSFRow timesheeRow = sheet.createRow(i + 3);
			timesheeRow.setHeight((short) 500);
			XSSFCell tempCell = null;
			// 迴圈單元格填入資料
			for (int j = 0; j < 18; j++) {
				tempCell = timesheeRow.createCell(j);
				tempCell.setCellStyle(headstyle);
				String tempValue;
				if (j == 0) {
					// 優先排序
					tempValue = oneData.get("arg_req_first_order").toString();
				} else if (j == 1) {
					// 單位id)
					tempValue = oneData.get("arg_req_ou_id").toString();
				} else if (j == 2) {
					// 部門名稱
					tempValue = oneData.get("arg_req_dept_name").toString();
				} else if (j == 3) {
					// 專業線
					tempValue = oneData.get("arg_req_majar_line_ou").toString();
				} else if (j == 4) {
					// 專案名稱
					tempValue = oneData.get("arg_req_proj_name").toString();
				} else if (j == 5) {
					// 應用主體
					tempValue = oneData.get("arg_req_majar_line_proj").toString();
				} else if (j ==6){
					// 研發專案經費總投入(萬元)
					tempValue = oneData.get("arg_req_proj_money").toString();
				} else if (j ==7){
				 	// 費用化支出(萬元)
				 	tempValue = oneData.get("arg_req_expense_expenditure").toString();
				} else if (j ==8){
				 	// 費用化支出-委託開發費用(萬元
				 	tempValue = oneData.get("arg_req_expense_entrust").toString();
				} else if (j ==9){
				 	// 費用化支出-自有研發人工成本(萬元)
				 	tempValue = oneData.get("arg_req_expense_own").toString();
				} else if (j ==10){
				 	//資本化支出(萬元)
				 	tempValue = oneData.get("arg_req_capitalize_expenditure").toString();
				} else if (j ==11){
					// 資本化支出-委託開發費用(萬元)
					tempValue = oneData.get("arg_req_capitalize_entrust").toString();
				} else if (j ==12){
				 	// 資本化支出-自有研發人員工時(人年)
				 	tempValue = oneData.get("arg_req_capitalize_own").toString();
				} else if (j ==13){
				 	// 主要研發內容
				 	tempValue = oneData.get("arg_req_develop_content").toString();
				} else if (j == 14){
					// 預期研發成果200字左右
					tempValue = oneData.get("arg_req_proj_value").toString();
				} else if (j == 15){
					// 處理人
					tempValue = oneData.get("arg_req_create_user").toString();
				} else if (j == 16){
					// 上傳時間
					tempValue = oneData.get("arg_req_create_time").toString();
				} else {
					// 狀態
					tempValue = oneData.get("arg_req_proj_state").toString();
				}
				tempCell.setCellValue(tempValue);

			}
		}
		// 設定Excel資料列寬,可自適應
		sheet.setColumnWidth(0, 256*10+184);
		sheet.setColumnWidth(1, 256*20+184);
		sheet.setColumnWidth(2, 256*20+184);
		sheet.setColumnWidth(3, 256*20+184);
		sheet.setColumnWidth(4, 256*20+184);
		sheet.setColumnWidth(5, 256*20+184);
		sheet.setColumnWidth(6, 256*18+184);
		sheet.setColumnWidth(7, 256*11+184);
		sheet.setColumnWidth(8, 256*11+184);
		sheet.setColumnWidth(9, 256*11+184);
		sheet.setColumnWidth(10, 256*11+184);
		sheet.setColumnWidth(11, 256*11+184);
		sheet.setColumnWidth(12, 256*11+184);
		sheet.setColumnWidth(13, 256*30+184);
		sheet.setColumnWidth(14, 256*30+184);
		sheet.setColumnWidth(15, 256*11+184);
		sheet.setColumnWidth(16, 256*30+184);
		sheet.setColumnWidth(17, 256*8+184);
		// 自適應列寬
		//autoSizeColumnWidth(sheet, 17);
	}

	/**
	 * autoSizeColumnWidth:自適應列寬
	 * 
	 * @author "haoz6"
	 * @param sheet
	 * @since JDK 1.8
	 */
	public static void autoSizeColumnWidth(XSSFSheet sheet, int columnNum) {
		for (int i = 0; i < columnNum; i++) {
			sheet.autoSizeColumn((short) i);
		}
	}

	/**
	 * 
	 * exportExcel:excel匯出
	 * 
	 * @author haoz6
	 * @param request
	 * @param response
	 * @param workBook
	 * @throws IOException
	 * @since JDK 1.8
	 */
	public static void exportExcelXSSF(HttpServletRequest request,
			HttpServletResponse response, XSSFWorkbook workBook,
			String excelName) throws IOException {

		String respContentType = "multipart/form-data";
		String reqUserAgent = "User-Agent";
		String fileSuffix = ".xlsx";
		String fireFox = "Firefox";
		String respContentDispositionKey = "Content-Disposition";
		String respContentDispositionValue = "attachment;filename=";
		String timesheet = excelName;
		String browser = "=?utf-8?b?";
		String browser2 = "?=";
		String browser3 = "+";
		String browser4 = " ";

		OutputStream os = null;

		try {
			String filename = timesheet + fileSuffix;
			response.setContentType(respContentType);
			String agent = request.getHeader(reqUserAgent);
			if (agent.contains(fireFox)) {
				// 火狐瀏覽器 只能收到拼接base64的編碼
				filename = browser
						+ (new String(Base64.encodeBase64(
								filename.getBytes(CHARACTERENCODING))))
						+ browser2;
			} else {
				// IE,谷歌瀏覽器
				filename = URLEncoder.encode(filename, CHARACTERENCODING);
				BDUServiceBaseTools.commonLogout(filename);
				filename = filename.replace(browser3, browser4);
			}
			response.setHeader(respContentDispositionKey,
					respContentDispositionValue + filename);
			os = new BufferedOutputStream(response.getOutputStream());
			// 將workbook寫入到response中
			workBook.write(os);
			workBook.close();
			os.close();
			// 重新整理緩衝區,將緩衝區資料寫入到response中
			response.flushBuffer();
		} catch (IOException e1) {
			String errorRetVal = "獲得傳入引數失敗";
			BDUServiceBaseTools.commonLogout(errorRetVal);
			// CommonError.errorEndServlet(errorRetVal, response);
			LogService.catching(e1);
		}finally {
			if (os!=null){
				os.close();
			}
			workBook.close();
		}
	}
}

以上就是 “Java Servlet 實現合併多單元格匯出Excel” 得程式碼,希望各位大佬給予點評,互相學習,感謝!

相關文章