java导出下载Excel文件的一种方式之一poi

客官°小女子只卖身不卖艺 2023-07-18 02:43 32阅读 0赞

service层

public void hisSoeExport(HttpServletResponse response, String beginTime, String endTime, String facId,
    			String groupId, String typeId) {
    		ExportParams exportParams = new ExportParams("告警报表", "告警数据");
    		exportParams.setCreateHeadRows(true);
    		exportParams.setStyle(ExcelExportStylerBorderImpl.class);
    		Workbook workbook = ExcelExportUtil.exportExcel(exportParams, HisDataSoe.class,
    				getSoe(beginTime, endTime, facId, groupId, typeId));
    		try {
    			ExportUtils.downloadEexcl(response, "告警", workbook);
    		} catch (IOException e) {
    			e.printStackTrace();
    			throw new HZException("HZ9999", "execl下载发生异常");
    		}
    	}

导出工具类

package xxx.utils;
    
    import java.io.BufferedInputStream;
    import java.io.BufferedOutputStream;
    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.util.CellRangeAddress;
    
    public class ExportUtils {
    
    	public static void downloadEexcl(HttpServletResponse response, String fileName, Workbook workbook)
    			throws IOException {
    		response.addHeader("Content-disposition",
    				"attachment;filename=" + new String(fileName.getBytes("gbk"), "iso8859-1") + ".xls");
    		// 定义输出类型
    		response.setContentType("octets/stream;rset=UTF-8");
    		// 创建输出对象
    		ServletOutputStream out = response.getOutputStream();
    		// 工作簿输出
    		workbook.write(out);
    		// 关闭输出流
    		out.close();
    	}
    
    	/**
    	 * 去掉所有合并的单元格
    	 * 
    	 * @param sheet
    	 */
    	public static void removeMergedRegion(Sheet sheet) {
    		int num = sheet.getNumMergedRegions();
    		while (num > 0) {
    			sheet.removeMergedRegion(0);
    			num = sheet.getNumMergedRegions();
    		}
    	}
    
    	/**
    	 * 自适应单元格
    	 * 
    	 * @param sheet
    	 * @param colNum
    	 * @param value
    	 * @param offset
    	 */
    	public static void adjustColumn(Sheet sheet, int colNum, String value, int offset) {
    		int columnWidth = sheet.getColumnWidth(colNum);
    		int valueWidth = value.getBytes().length * 256;
    		if (valueWidth > columnWidth) {
    			sheet.setColumnWidth(colNum, valueWidth + offset);
    		}
    	}
    
    	/**
    	 * 复制整行
    	 * 
    	 * @param oldRow
    	 * @param newRow
    	 * @param startCol
    	 * @param endCol
    	 */
    	@SuppressWarnings("deprecation")
    	public static void copyRow(Sheet sheet, Row oldRow, Row newRow) {
    		int startCol = oldRow.getFirstCellNum();
    		int endCol = oldRow.getLastCellNum();
    		if (startCol >= 0 && endCol >= 0) {
    			for (int i = startCol; i <= endCol; i++) {
    				Cell oldCell = oldRow.getCell(i);
    				Cell newCell = newRow.createCell(i);
    				if (null == oldCell) {
    					continue;
    				}
    				int type = oldCell.getCellType();
    				newCell.setCellType(type);
    				switch (type) {
    				case Cell.CELL_TYPE_BLANK:
    					break;
    				case Cell.CELL_TYPE_STRING:
    					newCell.setCellValue(oldCell.getStringCellValue());
    					break;
    				case Cell.CELL_TYPE_BOOLEAN:
    					newCell.setCellValue(oldCell.getBooleanCellValue());
    					break;
    				case Cell.CELL_TYPE_NUMERIC:
    					newCell.setCellValue(oldCell.getNumericCellValue());
    					break;
    				case Cell.CELL_TYPE_FORMULA:
    					newCell.setCellValue(oldCell.getCellFormula());
    					break;
    				default:
    					newCell.setCellValue(oldCell.getStringCellValue());
    					break;
    				}
    				newCell.setCellStyle(oldCell.getCellStyle());
    				String value = oldCell.getStringCellValue();
    				HisDataExportUtils.adjustColumn(sheet, i, value, 100);
    				oldCell.setCellValue("");
    				oldCell.setCellType(Cell.CELL_TYPE_BLANK);
    			}
    		}
    	}
    
    	/**
    	 * 剪裁除去
    	 * 
    	 * @param sheet
    	 * @param rowNum
    	 * @param columnNum
    	 */
    	public static void clipSheet(Sheet sheet, int rowNum, int columnNum) {
    		int rowTotal = sheet.getLastRowNum();
    		while (rowNum < rowTotal) {
    			sheet.removeRow(sheet.getRow(rowNum + 1));
    			rowTotal = sheet.getLastRowNum();
    		}
    
    		for (int i = 0; i <= rowTotal; i++) {
    			Row row = sheet.getRow(i);
    			int columnTotal = row.getLastCellNum();
    			for (int j = columnNum + 1; j <= columnTotal; j++) {
    				Cell cell = row.getCell(j);
    				if (null != cell) {
    					row.removeCell(cell);
    				}
    			}
    		}
    
    	}
    
    	/**
    	 * 修改表头
    	 * 
    	 * @param sheet
    	 * @param columNum
    	 * @param time
    	 */
    	public static void editHead(Sheet sheet, int columNum, String time, String title,String unit) {
    		if (null != title) {
    			Row titleRow = sheet.getRow(0);
    			Cell titleCell = titleRow.getCell(0);
    			titleCell.setCellValue(title);
    		}
    		if(null != unit){
    			Row titleRow = sheet.getRow(1);
    			Cell titleCell = titleRow.getCell(1);
    			titleCell.setCellValue(unit);
    		}
    		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columNum));
    		Row dateRow = sheet.getRow(1);
    		Cell dateCell = dateRow.getCell(0);
    		String dateStr = dateCell.getStringCellValue();
    		dateStr += time;
    		dateCell.setCellValue(dateStr);
    		Cell unitCell = dateRow.getCell(1);
    		Cell newCell = dateRow.createCell(columNum - 1);
    		newCell.setCellStyle(unitCell.getCellStyle());
    		newCell.setCellValue(unitCell.getStringCellValue());
    		unitCell.setCellValue("");
    		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, columNum - 2));
    		sheet.addMergedRegion(new CellRangeAddress(1, 1, columNum - 1, columNum));
    	}
    
    	/**
    	 * 输入流存储到文件
    	 * 
    	 * @param inputStream
    	 * @param file
    	 * @throws IOException
    	 */
    	public static void inputStreamToFile(InputStream inputStream, File file) throws IOException {
    		File parentDir = file.getParentFile();
    		if (!parentDir.exists()) {
    			parentDir.mkdirs();
    		}
    		file.createNewFile();
    		BufferedOutputStream outputStream = new BufferedOutputStream(new FileOutputStream(file));
    		BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream);
    
    		int len = -1;
    		byte[] bytes = new byte[1024];
    		while ((len = bufferedInputStream.read(bytes)) != -1) {
    			outputStream.write(bytes, 0, len);
    		}
    		bufferedInputStream.close();
    		outputStream.close();
    	}
    }