Excel导出工具

╰半橙微兮° 2023-06-23 14:26 58阅读 0赞

**调用方法必要参数：**

( HttpServletRequest request, HttpServletResponse response )

**调用代码：**

List<Map<String, Object>> list = 数据库查询结果 //TODO
    ExcelUtil excelUtil = new ExcelUtil();
    LinkedHashMap<String, String> keyMap = new LinkedHashMap<String, String>();
    keyMap.put("字段名1", "导出结果tittle1");//TODO
    keyMap.put("字段名2", "导出结果tittle2");		
    String title = "导出文件名".concat(DateUtils.formatDateToString(new Date(), DateUtils.YYYYMMDDHHMMSS));//TODO
    title = excelUtil.encodeFileName(title, request);
    excelUtil.dlExcelUtil(response, list, keyMap, title);

**工具类：ExcelUtil **

package *****.common.utils.excel;
    
    import java.io.IOException;
    import java.io.OutputStream;
    import java.lang.reflect.Field;
    import java.math.BigDecimal;
    import java.sql.Timestamp;
    import java.text.DecimalFormat;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.Iterator;
    import java.util.LinkedHashMap;
    import java.util.List;
    import java.util.Map;
    import java.util.Map.Entry;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.xssf.streaming.SXSSFSheet;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    
    import jxl.CellView;
    import jxl.Workbook;
    import jxl.format.Alignment;
    import jxl.format.Border;
    import jxl.format.BorderLineStyle;
    import jxl.format.VerticalAlignment;
    import jxl.write.Label;
    import jxl.write.WritableCellFormat;
    import jxl.write.WritableFont;
    import jxl.write.WritableSheet;
    import jxl.write.WritableWorkbook;
    
    public class ExcelUtil {
    
    	public static final int RESULT_SUCC = 0;
    	public static final int RESULT_FAIL = -1;
    
    	public static final String TYPE_YYYY_MM_DD_HH_MM_SS = "yyyy-MM-dd HH:mm:ss";
    
    	/**
    	 * 将数据转成成excel。 特性： 1、将时间类型的值转成yyyy-MM-dd HH:mm:ss 2、将数字类型的值转成带千分符的形式，并右对齐
    	 * 3、除数字类型外，其他类型的值居中显示
    	 * 
    	 * @param keyMap
    	 *            定义标题及每一列对应的JavaBean属性。标题的先后顺序，对应keyMap的插入顺序；
    	 *            map中的key值为JavaBean属性，value为标题
    	 * @param listContent
    	 *            表格内容，List中的每一个元素，对应到excel的每一行
    	 * @param os
    	 *            结果输出流
    	 * @return
    	 */
    	public final int export(LinkedHashMap<String, String> keyMap, List<Map<String, Object>> listContent,
    			OutputStream os) {
    
    		int rs = RESULT_SUCC;
    		try {
    			// 创建工作簿
    			WritableWorkbook workbook = Workbook.createWorkbook(os);
    
    			// 创建名为sheet1的工作表
    			WritableSheet sheet = workbook.createSheet("Sheet1", 0);
    
    			// 设置字体
    			WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 12);
    			WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD);
    
    			// 标题居中
    			WritableCellFormat titleFormat = new WritableCellFormat(BoldFont);
    			titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
    			titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
    			titleFormat.setAlignment(Alignment.CENTRE); // 文字水平对齐
    			titleFormat.setWrap(false); // 文字是否换行
    
    			// 正文居中
    			WritableCellFormat contentCenterFormat = new WritableCellFormat(NormalFont);
    			contentCenterFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
    			contentCenterFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
    			contentCenterFormat.setAlignment(Alignment.CENTRE);
    			contentCenterFormat.setWrap(false);
    
    			// 正文右对齐
    			WritableCellFormat contentRightFormat = new WritableCellFormat(NormalFont);
    			contentRightFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
    			contentRightFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
    			contentRightFormat.setAlignment(Alignment.RIGHT);
    			contentRightFormat.setWrap(false);
    
    			// 设置标题,标题内容为keyMap中的value值,标题居中粗体显示
    			Iterator titleIter = keyMap.entrySet().iterator();
    			int titleIndex = 0;
    			while (titleIter.hasNext()) {
    				Map.Entry<String, String> entry = (Map.Entry<String, String>) titleIter.next();
    				sheet.addCell(new Label(titleIndex++, 0, entry.getValue(), titleFormat));
    			}
    
    			// 设置正文内容
    			for (int i = 0; i < listContent.size(); i++) {
    				Iterator contentIter = keyMap.entrySet().iterator();
    				int colIndex = 0;
    				int listIndex = 0;
    				while (contentIter.hasNext()) {
    					Map.Entry<String, String> entry = (Map.Entry<String, String>) contentIter.next();
    					Object key = entry.getKey();
    
    					// Field field =
    					// listContent.get(i).getClass().getDeclaredField(key.toString());
    					// field.setAccessible(true);
    					// Object content = field.get(listContent.get(i));
    
    					String contentStr = "";
    
    					Map haha = (Map) listContent.get(i);
    					if (haha.get(key.toString()) != null && haha.get(key.toString()).toString() != "") {
    						contentStr = haha.get(key.toString()).toString();
    					}
    
    					// if (null != content) {
    					// contentStr = content.toString();
    					// }
    
    					// if (isNumberic(field)) {
    					// 如果正文内容是数字，要转换成千分符形式，右对齐
    					// sheet.addCell(new Label(colIndex++, i + 1,
    					// getNumbericValue(contentStr), contentRightFormat));
    					// } else {
    
    					// 如果是时间类型，统一做格式化。
    					// String timeStr = getTimeFormatValue(field, content);
    					// if (null != timeStr && !timeStr.trim().equals("")) {
    					// contentStr = timeStr;
    					// } else {
    					// 不是时间类型的值，不用转换
    					// }
    
    					sheet.addCell(new Label(colIndex++, i + 1, contentStr, contentCenterFormat));
    				}
    
    			}
    
    			// }
    
    			// 宽度自适应。能够根据内容增加宽度，但对中文的支持不好，如果内容中包含中文，会有部分内容被遮盖
    			for (int i = 0; i < keyMap.size(); i++) {
    				CellView cell = sheet.getColumnView(i);
    				cell.setAutosize(true);
    				sheet.setColumnView(i, cell);
    			}
    			workbook.write();
    			workbook.close();
    
    		} catch (Exception e) {
    			rs = RESULT_FAIL;
    			e.printStackTrace();
    		}
    		return rs;
    	};
    
    	/**
    	 * 设置单元格的边框（先执行Start方法）
    	 * 
    	 * @param sheetIndex
    	 *            Excel索引，从0开始
    	 * @param startRow
    	 *            开始行，从0开始
    	 * @param rowNum
    	 *            设置的行数
    	 * @param startCol
    	 *            开始列，从0开始
    	 * @param colNum
    	 *            需要设置的列数
    	 * @throws Exception
    	 */
    	public void setBorder(int sheetIndex, int startRow, int rowNum, int startCol, int colNum, SXSSFWorkbook workbook,
    			OutputStream os) throws Exception {
    		try {
    			Sheet sheet = workbook.getSheetAt(sheetIndex);
    			// 设置单元格样式
    			for (int rIndex = startRow; rIndex < startRow + rowNum; rIndex++) {
    				Row row = sheet.getRow(rIndex);
    				if (row == null) {
    					row = sheet.createRow(rIndex);
    				}
    				for (int cIndex = startCol; cIndex < startCol + colNum; cIndex++) {
    					Cell cell = row.getCell(cIndex);
    					if (cell == null) {
    						cell = row.createCell(cIndex);
    					}
    
    					CellStyle style = cell.getCellStyle();
    					// 判断是否已经创建过
    					if (style.getIndex() == 0) {
    						style = workbook.createCellStyle();
    					}
    					style.setBorderBottom(CellStyle.BORDER_THIN);
    					style.setBorderLeft(CellStyle.BORDER_THIN);
    					style.setBorderRight(CellStyle.BORDER_THIN);
    					style.setBorderTop(CellStyle.BORDER_THIN);
    					cell.setCellStyle(style);
    				}
    			}
    
    			// 写入文件，采用分批写入的方式进行写入
    			// workbook.write(os);
    			// workbook.close();
    		} catch (Exception e) {
    			throw new Exception(String.format("setBorder:%s", e.getMessage()));
    		}
    	}
    
    	/**
    	 * 设置列自适应（先执行Start方法）
    	 * 
    	 * @param sheetIndex
    	 *            Sheet的索引，从0开始
    	 * @param startCol
    	 *            开始列，从0开始
    	 * @param colNum
    	 *            列数量
    	 * @throws Exception
    	 */
    	public void setAutoFit(int sheetIndex, int startCol, int colNum, SXSSFWorkbook workbook, OutputStream os)
    			throws Exception {
    		try {
    			Sheet sheet = workbook.getSheetAt(sheetIndex);
    			for (int cIndex = startCol; cIndex < startCol + colNum; cIndex++) {
    				sheet.autoSizeColumn(cIndex);
    			}
    
    			// 写入文件，采用分批写入的方式进行写入
    			// workbook.write(os);
    			// workbook.close();
    		} catch (Exception e) {
    			throw new Exception(String.format("setAutoFit:%s", e.getMessage()));
    		}
    	}
    
    	/**
    	 * 设置单元格的值，支持/n自动换行（先执行Start方法）
    	 * 
    	 * @param sheetIndex
    	 *            Sheet的索引，从0开始
    	 * @param row
    	 *            行索引
    	 * @param col
    	 *            列索引
    	 * @param value
    	 *            值（暂时只支持字符串）
    	 * @throws Exception
    	 */
    	public void setWrapCellData(int sheetIndex, int row, int col, String value, SXSSFWorkbook workbook, OutputStream os)
    			throws Exception {
    		try {
    			Sheet sheet = workbook.getSheetAt(sheetIndex);
    
    			Row mRow = sheet.getRow(row);
    			if (mRow == null) {
    				mRow = sheet.createRow(row);
    			}
    			Cell mCell = mRow.createCell(col);
    			mCell.setCellValue(value);
    			// 设置自动换行
    			CellStyle style = workbook.createCellStyle();
    			style.setWrapText(true);
    			mCell.setCellStyle(style);
    
    			// 写入文件，采用分批写入的方式进行写入
    			// workbook.write(os);
    			// workbook.close();
    		} catch (Exception e) {
    			throw new Exception(String.format("setWrapCellData:%s", e.getMessage()));
    		}
    	}
    
    	/**
    	 * 将数据转成成excel。 特性： 1、将时间类型的值转成yyyy-MM-dd HH:mm:ss 2、将数字类型的值转成带千分符的形式，并右对齐
    	 * 3、除数字类型外，其他类型的值居中显示
    	 * 
    	 * @param titleMap
    	 *            定义标题及每一列对应的JavaBean属性。标题的先后顺序，对应keyMap的插入顺序；
    	 *            map中的key值为JavaBean属性，value为标题
    	 * @param freezeParams
    	 *            冻结列，必须是长度为4的整数数组。可以为空。
    	 * @param datas
    	 *            表格内容，List中的每一个元素，对应到excel的每一行
    	 * @param outputStream
    	 *            结果输出流
    	 * @return
    	 */
    	public final int exportMorUtil(LinkedHashMap<String, String> titleMap, Integer[] freezeParams,
    			List<Map<String, Object>> datas, OutputStream outputStream) {
    		SXSSFWorkbook workbook = null;
    		try {
    			workbook = new SXSSFWorkbook(1000); // 这里1000是在内存中的数量，如果大于此数量时，会写到硬盘，以避免在内存导致内存溢出
    			SXSSFSheet sheet = workbook.createSheet();
    		    sheet.trackAllColumnsForAutoSizing(); // 自动调整列宽
    			if (datas != null) {
    
    				CellStyle titleStyle = workbook.createCellStyle();
    				titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    				titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    				titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    				titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    				titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    				titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 
    				titleStyle.setFillForegroundColor(HSSFColor.WHITE.index); 
    
    				CellStyle centerStyle = workbook.createCellStyle();
    				centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    				centerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    				centerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    				centerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    
    				CellStyle numberStyle = workbook.createCellStyle();
    				numberStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    				numberStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    				numberStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    
    				Iterator<Entry<String, String>> titleIter = titleMap.entrySet().iterator();
    				int titleIndex = 0;
    				Row row = sheet.createRow(0);
    				while (titleIter.hasNext()) {
    					Cell cell = row.createCell(titleIndex);
    					Map.Entry<String, String> entry = (Map.Entry<String, String>) titleIter.next();
    					cell.setCellValue(entry.getValue());
    					cell.setCellStyle(titleStyle);
    					sheet.autoSizeColumn(titleIndex);
    					titleIndex++;
    //					System.out.println(">>>[" + entry.getValue() + "]<<<");
    				}
    				sheet.createFreezePane(0, 1); // 固定表头
    				for (int rownum = 1; rownum <= datas.size(); rownum++) {
    					row = sheet.createRow(rownum);
    					Iterator<Entry<String, String>> contentIter = titleMap.entrySet().iterator();
    					int colIndex = 0;
    					while (contentIter.hasNext()) {
    						Cell cell = row.createCell(colIndex);
    						Map.Entry<String, String> entry = (Map.Entry<String, String>) contentIter.next();
    						String key = (String) entry.getKey();
    						Map<String, Object> haha = (Map<String, Object>) datas.get(rownum - 1);
    						Object value = haha.get(key);
    						if (value != null) {
    							if (value instanceof Double) {
    								cell.setCellValue((Double) value);
    								cell.setCellStyle(numberStyle);
    							} else if (value instanceof Integer) {
    								cell.setCellValue((Integer) value);
    								cell.setCellStyle(numberStyle);
    							} else if (value instanceof Long) {
    								cell.setCellValue((Long) value);
    								cell.setCellStyle(numberStyle);
    							} else if (value instanceof BigDecimal) {
    								cell.setCellValue(((BigDecimal) value).doubleValue());
    								cell.setCellStyle(numberStyle);
    							} else {
    								cell.setCellValue(value.toString());
    								cell.setCellStyle(centerStyle);
    								if (!(value instanceof String)) {
    									System.out.println(">>>>>>>>>> value instanceof " + value.getClass());
    								}
    							}
    						} else {
    							cell.setCellValue("");
    							cell.setCellStyle(centerStyle);
    						}
    						colIndex++;
    					}
    				}
    
    				int index = titleMap.size();
    				while (index-- > 0) {
    					sheet.autoSizeColumn(index);
    				}
    
    				if (freezeParams != null) {
    					sheet.createFreezePane(freezeParams[0], freezeParams[1], freezeParams[2], freezeParams[3]);
    				}
    			}
    			workbook.write(outputStream);
    		} catch (Exception e) {
    			e.printStackTrace();
    		} finally {
    			if (workbook != null) {
    				try {
    					workbook.close();
    				} catch (IOException e) {
    					e.printStackTrace();
    				}
    			}
    		}
    		return 1;
    	};
    
    	/**
    	 * 判断当前属性是否为数字类型
    	 * 
    	 * @param field
    	 * @return
    	 */
    	public static boolean isNumberic(Field field) {
    		if (field.getType().getName().equals(java.lang.Integer.class.getName())
    				|| field.getType().getName().equals("int")
    				|| field.getType().getName().equals(java.lang.Long.class.getName())
    				|| field.getType().getName().equals("long")
    				|| field.getType().getName().equals(java.lang.Double.class.getName())
    				|| field.getType().getName().equals("double")) {
    			return true;
    		} else {
    			return false;
    		}
    	}
    
    	/**
    	 * 获取格式化后的时间串
    	 * 
    	 * @param field
    	 * @param content
    	 * @return
    	 */
    	public String getTimeFormatValue(Field field, Object content) {
    		String timeFormatVal = "";
    		if (field.getType().getName().equals(java.sql.Timestamp.class.getName())) {
    			Timestamp time = (Timestamp) content;
    			timeFormatVal = longTimeTypeToStr(time.getTime(), TYPE_YYYY_MM_DD_HH_MM_SS);
    		} else if (field.getType().getName().equals(java.util.Date.class.getName())) {
    			Date time = (Date) content;
    			timeFormatVal = longTimeTypeToStr(time.getTime(), TYPE_YYYY_MM_DD_HH_MM_SS);
    		}
    
    		return timeFormatVal;
    	}
    
    	/**
    	 * 获取千分位数字
    	 * 
    	 * @param str
    	 * @return
    	 */
    	public String getNumbericValue(String str) {
    		String numbericVal = str;
    		try {
    			Double doubleVal = Double.valueOf(str);
    			numbericVal = DecimalFormat.getNumberInstance().format(doubleVal);
    		} catch (NumberFormatException e) {
    			// if exception, not format
    		}
    		return numbericVal;
    	}
    
    	/**
    	 * 格式化时间
    	 * 
    	 * @param time
    	 * @param formatType
    	 * @return
    	 */
    	public String longTimeTypeToStr(long time, String formatType) {
    
    		String strTime = "";
    		if (time >= 0) {
    			SimpleDateFormat sDateFormat = new SimpleDateFormat(formatType);
    
    			strTime = sDateFormat.format(new Date(time));
    
    		}
    
    		return strTime;
    
    	}
    
    	public void dlExcelUtil(HttpServletResponse response,
    			List<Map<String, Object>> transferInfoList, LinkedHashMap<String, String> titileMap, String fileName) throws IOException {
    		this.dlExcelUtil(response, transferInfoList, titileMap, null, fileName);
    	}
    
    	public void dlExcelUtil(HttpServletResponse response,
    			List<Map<String, Object>> transferInfoList, LinkedHashMap<String, String> titileMap, Integer[] freezeParams,
    			String fileName) throws IOException {
    		long start = System.currentTimeMillis();
    		OutputStream out = response.getOutputStream();
    //		Date d = new Date();
    //		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    //		String time = formatter.format(d);
    //		fileName += time;
    		response.reset();// 清空输出流
    		response.setHeader("Content-disposition", "attachment; filename=" + toUtf8String(fileName) + ".xlsx");// 设定输出文件头
    		response.setContentType("application/msexcel;charset=UTF-8");// 定义输出类型
    		// this.export(titileMap, transferInfoList, out);
    		this.exportMorUtil(titileMap, freezeParams, transferInfoList, out);
    		long end = System.currentTimeMillis();
    		System.out.println(end - start);
    	}
    	
    	public String encodeFileName(String fileNames, HttpServletRequest request) {
            String codedFilename = null;
            try {
                String agent = request.getHeader("USER-AGENT");
                if (null != agent && -1 != agent.indexOf("MSIE") || null != agent && -1 != agent.indexOf("Trident") || null != agent && -1 != agent.indexOf("Edge")) {// ie浏览器及Edge浏览器
                    String name = java.net.URLEncoder.encode(fileNames, "UTF-8");
                    codedFilename = name;
                } else if (null != agent && -1 != agent.indexOf("Mozilla")) {
                    // 火狐,Chrome等浏览器
                    codedFilename = new String(fileNames.getBytes("UTF-8"), "iso-8859-1");
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            return codedFilename;
        }
    
    	public static String toUtf8String(String s) {
    		StringBuffer sb = new StringBuffer();
    		for (int i = 0; i < s.length(); i++) {
    			char c = s.charAt(i);
    			if (c >= 0 && c <= 255) {
    				sb.append(c);
    			} else {
    				byte[] b;
    				try {
    					b = Character.toString(c).getBytes("utf-8");
    				} catch (Exception ex) {
    					System.out.println(ex);
    					b = new byte[0];
    				}
    				for (int j = 0; j < b.length; j++) {
    					int k = b[j];
    					if (k < 0)
    						k += 256;
    					sb.append("%" + Integer.toHexString(k).toUpperCase());
    				}
    			}
    		}
    		return sb.toString();
    	}
    
    }