Excel导入与导出

深碍√TFBOYSˉ_ 2022-03-11 19:20 351阅读 0赞

这两天看了看项目中Excel导入导出功能，在此整理了一下相关代码，方便以后使用，项目结构如下：该项目为web项目，主要有两个包，里面分别包含导入和导出的工具类和demo，可本地运行，也可在tomca上运行模拟真实的web请求。源码下载地址：[https://download.csdn.net/download/diweikang/11006639][https_download.csdn.net_download_diweikang_11006639]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rpd2Vpa2FuZw_size_16_color_FFFFFF_t_70][]

### 导入 ###

public class ReadExcel {
    
    	/**
    	 * 读取 office 2003 excel
    	 * 
    	 * @throws IOException
    	 * @throws FileNotFoundException
    	 */
    	public static List<List<Object>> read2003Excel(InputStream in) throws IOException {
    		List<List<Object>> list = new LinkedList<List<Object>>();
    		HSSFWorkbook hwb = new HSSFWorkbook(in);
    		HSSFSheet sheet = hwb.getSheetAt(0);
    		Object value = null;
    		HSSFRow row = null;
    		HSSFCell cell = null;
    		int counter = 0;
    		for (int i = sheet.getFirstRowNum(); counter < sheet.getPhysicalNumberOfRows(); i++) {
    			row = sheet.getRow(i);
    			if (row == null) {
    				continue;
    			} else {
    				counter++;
    			}
    			List<Object> linked = new LinkedList<Object>();
    			for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
    				cell = row.getCell(j);
    				if (cell == null) {
    					continue;
    				}
    				DecimalFormat df = new DecimalFormat("0");// 格式化 number String
    															// 字符
    				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
    				DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
    				switch (cell.getCellType()) {
    				case XSSFCell.CELL_TYPE_STRING:
    					System.out.println(i + "行" + j + " 列 is String type");
    					value = cell.getStringCellValue();
    					break;
    				case XSSFCell.CELL_TYPE_NUMERIC:
    					System.out.println(
    							i + "行" + j + " 列 is Number type ; DateFormt:" + cell.getCellStyle().getDataFormatString());
    					if ("@".equals(cell.getCellStyle().getDataFormatString())) {
    						value = df.format(cell.getNumericCellValue());
    					} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
    						value = nf.format(cell.getNumericCellValue());
    					} else {
    						value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
    					}
    					break;
    				case XSSFCell.CELL_TYPE_BOOLEAN:
    					System.out.println(i + "行" + j + " 列 is Boolean type");
    					value = cell.getBooleanCellValue();
    					break;
    				case XSSFCell.CELL_TYPE_BLANK:
    					System.out.println(i + "行" + j + " 列 is Blank type");
    					value = "";
    					break;
    				default:
    					System.out.println(i + "行" + j + " 列 is default type");
    					value = cell.toString();
    				}
    				if (value == null || "".equals(value)) {
    					continue;
    				}
    				linked.add(value);
    			}
    			list.add(linked);
    		}
    		return list;
    	}
    
    	/**
    	 * 读取Office 2007 excel
    	 */
    	public static List<List<Object>> read2007Excel(InputStream in) throws IOException {
    		List<List<Object>> list = new LinkedList<List<Object>>();
    		// 构造 XSSFWorkbook 对象，strPath 传入文件路径
    		XSSFWorkbook xwb = new XSSFWorkbook(in);
    		// 读取第一章表格内容
    		XSSFSheet sheet = xwb.getSheetAt(0);
    		Object value = null;
    		XSSFRow row = null;
    		XSSFCell cell = null;
    		int counter = 0;
    		for (int i = sheet.getFirstRowNum(); counter < sheet.getPhysicalNumberOfRows(); i++) {
    			row = sheet.getRow(i);
    			if (row == null) {
    				continue;
    			} else {
    				counter++;
    			}
    			List<Object> linked = new LinkedList<Object>();
    			for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
    				cell = row.getCell(j);
    				if (cell == null) {
    					continue;
    				}
    				DecimalFormat df = new DecimalFormat("0");// 格式化 number String
    															// 字符
    				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
    				DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
    				switch (cell.getCellType()) {
    				case XSSFCell.CELL_TYPE_STRING:
    					System.out.println(i + "行" + j + " 列 is String type");
    					value = cell.getStringCellValue();
    					break;
    				case XSSFCell.CELL_TYPE_NUMERIC:
    					System.out.println(
    							i + "行" + j + " 列 is Number type ; DateFormt:" + cell.getCellStyle().getDataFormatString());
    					if ("@".equals(cell.getCellStyle().getDataFormatString())) {
    						value = df.format(cell.getNumericCellValue());
    					} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
    						value = nf.format(cell.getNumericCellValue());
    					} else {
    						value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
    					}
    					break;
    				case XSSFCell.CELL_TYPE_BOOLEAN:
    					System.out.println(i + "行" + j + " 列 is Boolean type");
    					value = cell.getBooleanCellValue();
    					break;
    				case XSSFCell.CELL_TYPE_BLANK:
    					System.out.println(i + "行" + j + " 列 is Blank type");
    					value = "";
    					break;
    				default:
    					System.out.println(i + "行" + j + " 列 is default type");
    					value = cell.toString();
    				}
    				if (value == null || "".equals(value)) {
    					continue;
    				}
    				linked.add(value);
    			}
    			list.add(linked);
    		}
    		return list;
    	}
    
    	public static void main(String[] args) {
    		try {
    			File file = new File("D:\\home\\export2007_a.xlsx");
    			String fileName = file.getName();
    			String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
    			if ("xls".equals(extension)) {
    				List<List<Object>> list = read2003Excel(new FileInputStream(file));
    				System.out.println(list);
    			} else if ("xlsx".equals(extension)) {
    				List<List<Object>> list = read2007Excel(new FileInputStream(file));
    				System.out.println(list);
    			} else {
    				throw new IOException("不支持的文件类型");
    			}
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    	}
    }

### 导出 ###

此处指提供Excel2007导出代码，Excel2003导出可下载源码

/**
     * 利用开源组件POI3.0.2动态导出EXCEL文档
     * 
     * 应用泛型，代表任意一个符合javabean风格的类
     * 注意这里为了简单起见，boolean型的属性xxx的get器方式为getXxx(),而不是isXxx() byte[]表jpg格式的图片数据
     */
    public class ExportExcel2007<T> {
    	public static final String FILE_SEPARATOR = System.getProperties().getProperty("file.separator");
    
    	public void createExcelAppendData(XSSFWorkbook workbook, AbstractExcelEnum[] excelEnums, List<?> dataset)
    			throws Exception {
    		createExcelAppendData("测试POI导出EXCEL文档", workbook, excelEnums, dataset);
    	}
    
    	/**
    	 * 这是一个通用的方法，将放置在JAVA集合中并且符合一定条件的数据以EXCEL的形式输出到指定IO设备上
    	 * 
    	 * 该方法利用反射将javabean指定的的属性导出到Excel
    	 * 
    	 * @param title
    	 *            表格标题名
    	 * @param workbook
    	 *            工作薄
    	 * @param excelEnums
    	 *            枚举值：包含字段、字段名（标题）
    	 * @param dateList
    	 *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
    	 *            javabean属性的数据类型有基本数据类型及String,Date
    	 * 
    	 */
    	public void createExcelAppendData(String title, XSSFWorkbook workbook, AbstractExcelEnum[] excelEnums,
    			List<?> dateList) throws Exception {
    
    		XSSFSheet sheet = null;
    		sheet = workbook.createSheet(title);
    		// 设置表格默认列宽度为15个字节
    		sheet.setDefaultColumnWidth((short) 15);
    		int initRowNum = sheet.getLastRowNum();
    		// 原来无数据，则先写表头
    		if (initRowNum == 0) {
    			// 表头格式
    			XSSFCellStyle headCellStyle = createExcelHeadStyle(workbook);
    			createTabHeader(sheet, excelEnums, headCellStyle);
    		}
    		appendDataToSheet(workbook, sheet, dateList, excelEnums);
    
    	}
    
    	/**
    	 * 定义表头单元格样式
    	 * 
    	 * @param workbook
    	 * @return
    	 */
    	private static XSSFCellStyle createExcelHeadStyle(XSSFWorkbook workbook) {
    		// 生成一个样式
    		XSSFCellStyle style = workbook.createCellStyle();
    		// 设置这些样式
    		style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
    		style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
    		style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
    		style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    		style.setBorderRight(XSSFCellStyle.BORDER_THIN);
    		style.setBorderTop(XSSFCellStyle.BORDER_THIN);
    		style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    		// 生成一个字体
    		XSSFFont font = workbook.createFont();
    		font.setColor(HSSFColor.VIOLET.index);
    		font.setFontHeightInPoints((short) 12);
    		font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    		// 把字体应用到当前的样式
    		style.setFont(font);
    		return style;
    	}
    
    	/**
    	 * 定义表头单元格样式
    	 * 
    	 * @param workbook
    	 * @return
    	 */
    	private static XSSFCellStyle createExcelBodyStyle(XSSFWorkbook workbook) {
    		// 生成并设置另一个样式
    		XSSFCellStyle style = workbook.createCellStyle();
    		style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
    		style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
    		style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
    		style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    		style.setBorderRight(XSSFCellStyle.BORDER_THIN);
    		style.setBorderTop(XSSFCellStyle.BORDER_THIN);
    		style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    		style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
    		// 生成另一个字体
    		XSSFFont font = workbook.createFont();
    		font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
    		// 把字体应用到当前的样式
    		style.setFont(font);
    		return style;
    	}
    
    	/**
    	 * 创建表头
    	 * 
    	 * @param sheet
    	 * @param enums
    	 * @param headCellStyle
    	 */
    	private static void createTabHeader(XSSFSheet sheet, AbstractExcelEnum[] enums, XSSFCellStyle cellStyle) {
    		XSSFRow row = sheet.createRow(0);
    		row.setHeight((short) (1.5 * 256)); // 设置行高
    		for (int i = 0; i < enums.length; i++) {
    			XSSFCell cell = row.createCell(i);
    			cell.setCellType(XSSFCell.CELL_TYPE_STRING);
    			cell.setCellStyle(cellStyle);
    			cell.setCellValue(enums[i].getColumn());
    		}
    	}
    
    	/**
    	 * 将list的值写入sheet
    	 * 
    	 * @param list
    	 * @param excelEnums
    	 * @return
    	 */
    	public void appendDataToSheet(XSSFWorkbook wb, XSSFSheet sheet, List<?> dataset, AbstractExcelEnum[] excelEnums)
    			throws Exception {
    		// 单元格样式
    		XSSFCellStyle cellStyle = wb.createCellStyle();
    		int initRow = sheet.getLastRowNum();
    		if (CollectionUtils.isNotEmpty(dataset)) {
    			for (int i = initRow; i < dataset.size() + initRow; i++) {
    				// 创建每行的数据
    				XSSFRow row = sheet.createRow(i + 1);
    				Object vo = dataset.get(i - initRow);
    				for (int j = 0; j < excelEnums.length; j++) {
    					XSSFCell cell = row.createCell(j);
    					cell.setCellType(XSSFCell.CELL_TYPE_STRING);
    					cell.setCellStyle(cellStyle);
    					AbstractExcelEnum excelEnum = excelEnums[j];
    					String fileName = excelEnum.getField();
    					Object value = PropertyUtils.getProperty(vo, fileName);
    					if (null != value) {
    						// 如果是Date类型
    						if (value.getClass().isAssignableFrom(Date.class)) {
    							String dateStyle = StringUtils.isEmpty(excelEnum.getDateFormatStyle()) ? "yyyy-MM-dd"
    									: excelEnum.getDateFormatStyle();
    							cell.setCellValue(new SimpleDateFormat(dateStyle).format((Date) value));
    						} else {
    							String valTrim = value.toString().trim();
    							if (!"".equals(valTrim)) {
    								// 如果返回的值可用枚举做转换
    								if (null != excelEnum.getClazz()) {
    									String cconvertedValue = convertValue(excelEnum, valTrim);
    									if (StringUtils.isNotBlank(cconvertedValue)) {
    										cell.setCellValue(cconvertedValue);
    									} else {
    										cell.setCellValue("");
    									}
    								} else {
    									cell.setCellValue(valTrim);
    								}
    							} else {
    								cell.setCellValue("");
    							}
    						}
    					} else {
    						cell.setCellValue("");
    					}
    				}
    			}
    		}
    	}
    
    	/**
    	 * 根据编码获取枚举中对应的值
    	 * 
    	 * @param excelEnum
    	 * @param valTrim
    	 * @return
    	 * @throws Exception
    	 */
    	private static String convertValue(AbstractExcelEnum excelEnum, String valTrim) throws Exception {
    		Class<?> clazz = excelEnum.getClazz();
    		if (!clazz.isEnum()) {
    			throw new IllegalArgumentException("参数类型必须是枚举类型");
    		}
    		Method method = clazz.getMethod("values");
    		Object obj = method.invoke(null);
    		Object[] enumArr = (Object[]) obj;
    		for (Object object : enumArr) {
    			String va = PropertyUtils.getProperty(object, excelEnum.getCodeFieldName()).toString();
    			if (valTrim.equals(va)) {
    				return PropertyUtils.getProperty(object, excelEnum.getNameFieldName()).toString();
    			}
    		}
    		return null;
    	}
    
    	public static void testExportExcelByColumn(String imagesPath, String docsPath) {
    		try {
    			// 测试学生
    			List<Student> dataset = new ArrayList<Student>();
    			dataset.add(new Student(10000001, "张三", 20, true, new Date()));
    			dataset.add(new Student(20000002, "李四", 24, false, new Date()));
    			dataset.add(new Student(30000003, "王五", 22, true, new Date()));
    
    			ExportExcel2007<Student> ex = new ExportExcel2007<Student>();
    			XSSFWorkbook workbook = new XSSFWorkbook();
    			ex.createExcelAppendData(workbook, StudentExcelEnum.values(), dataset);
    
    			System.out.println("excel导出成功！");
    		} catch (FileNotFoundException e) {
    			e.printStackTrace();
    		} catch (IOException e) {
    			e.printStackTrace();
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    	}
    }

Servlet代码

public class Excel2007Servlet extends HttpServlet {
    	private static final long serialVersionUID = 1L;
    	public static final String FILE_SEPARATOR = System.getProperties().getProperty("file.separator");
    
    	@Override
    	protected void doPost(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    		doGet(request, response);
    	}
    
    	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    		String fileName = "export2007中文_" + System.currentTimeMillis() + ".xlsx";
    		fileName = new String(fileName.getBytes("GBK"), "ISO8859-1");
    		response.setContentType("application/x-msdownload");
    		response.setCharacterEncoding("UTF-8");
    		response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
    		OutputStream out = response.getOutputStream();
    
    		try {
    			// 测试学生
    			List<Student> dataset = new ArrayList<Student>();
    			dataset.add(new Student(10000001, "张三", 20, true, new Date()));
    			dataset.add(new Student(20000002, "李四", 24, false, new Date()));
    			dataset.add(new Student(30000003, "王五", 22, true, new Date()));
    
    			ExportExcel2007<Student> ex = new ExportExcel2007<Student>();
    
    			XSSFWorkbook workbook = new XSSFWorkbook();
    			ex.createExcelAppendData(workbook, StudentExcelEnum.values(), dataset);
    
    			workbook.write(out);
    		} catch (Exception e) {
    			e.printStackTrace();
    		} finally {
    			if (out != null) {
    				out.flush();
    				out.close();
    			}
    		}
    	}
    }

参考：[https://blog.csdn.net/evangel\_z/article/details/7312050][https_blog.csdn.net_evangel_z_article_details_7312050]

[https_download.csdn.net_download_diweikang_11006639]: https://download.csdn.net/download/diweikang/11006639
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rpd2Vpa2FuZw_size_16_color_FFFFFF_t_70]: /images/20220311/d88d335cfd904f7e8fd7e94d7f2297f3.png
[https_blog.csdn.net_evangel_z_article_details_7312050]: https://blog.csdn.net/evangel_z/article/details/7312050