JAVA使用POI操作Excel表，XSSF(xlsx)和HSSF(xls)

- 日理万妓 2023-07-11 11:22 39阅读 0赞

# **JAVA使用POI操作Excel表，XSSF(xlsx)和HSSF(xls)** #

**\*\*\* POI的结构：**

---\*HSSF － 提供读写Microsoft Excel格式档案的功能。

---\*XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。

---\*HWPF－ 提供读写Microsoft Word格式档案的功能。

---\*HSLF － 提供读写Microsoft PowerPoint格式档案的功能。

---\*HDGF － 提供读写Microsoft Visio格式档案的功能。

## ##

### **\*\*\*下面用HSSF和XSSF来操作Excel** ###

**1.需求**：乘客选中的时间段对应多少个车次，就复制乘客多少行，接着把列车信息拼接在乘客信息后面；

拼接后再添加一列"mode"，如果"选中列车序号"的值和"列车序号"的值相等，"mode"的值就为1，否则为0；

还要删除一个"时间段"列（-\_-是不是觉得需求有点奇葩，但这是数据分析要用的数据）

---乘客表

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eWRmaGx3_size_16_color_FFFFFF_t_70][]

\---列车表

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eWRmaGx3_size_16_color_FFFFFF_t_70 1][]

\---想得到的结果图

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eWRmaGx3_size_16_color_FFFFFF_t_70 2][]

**2.代码实现（所需的Jar包和测试数据看链接）**

[jar包和测试数据.rar 链接：https://pan.baidu.com/s/1EeGNqqcm1G1Jgcaf-H-pBw 提取码：d386][jar_.rar _https_pan.baidu.com_s_1EeGNqqcm1G1Jgcaf-H-pBw _d386]

package cn.zyy;
    
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.InputStream;
    import java.text.DecimalFormat;
    
    import org.apache.commons.lang3.StringUtils;
    import org.apache.commons.lang3.time.DateFormatUtils;
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.FormulaEvaluator;
    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.xssf.usermodel.XSSFWorkbook;
    
    /**
     * POI操作excel表
     * 
     * @author Administrator
     *
     */
    public class test9 {
    
    	/**
    	 * 实现： 读取要操作的excel文件---第一个sheet页是--乘客信息，第二个sheet页是--列车信息，
    	 * 
    	 * 用第一个sheet页的period（时间段）列的每个单元格的值,与遍历第二个sheet页的第一列的每个单元格值比较;
    	 * 有几次相等就复制几次该乘客信息，再把对应的列车信息拼接在其后;
    	 * 
    	 * 新增一列mode（乘客是否选中列车，选中：1，选中：0） 用第一个sheet页中的number列的值和第二个sheet页中的altij列的值比较，
    	 * 如果相等，对应mode的值为1,否则为0;
    	 * 
    	 * @param args
    	 * @throws Exception
    	 */
    	public static void main(String[] args) throws Exception {
    
    		String inputPath = "d:\\information.xls"; // 需要操作excel文件的路径
    		String str1 = "乘客-列车"; // 新建sheet页的名称
    		String str2 = "mode";
    		// sheet0中的第13列和sheet1中的第1列比较
    		int eqCellNum1 = 13;
    		int eqCellNum2 = 0;
    		// s0页中，第14列和15列比较，新增一列mode，相等，写入1，否则写入0（乘客是否选中列车，选中：1，选中：0）
    		int s0Cell1 = 14;
    		int s0Cell2 = 15;
    
    		String outputPath = null; // 输出excel文件的路径
    		String substring = inputPath.substring(inputPath.lastIndexOf("."));
    		String substring2 = inputPath.substring(0, inputPath.lastIndexOf("."));
    		System.out.println(substring2);
    		// 读取文件
    		InputStream inputStream = new FileInputStream(inputPath);
    		Workbook workbook = null;
    		//判断是.xls文件还是.xlsx文件
    		if (".xls".equals(substring)) {
    			System.out.println("这是.xls文件");
    			outputPath=substring2+"(操作后数据).xls";
    			workbook = new HSSFWorkbook(inputStream);
    		} else if (".xlsx".equals(substring)) {
    			System.out.println("这是.xlsx文件");
    			outputPath=substring2+"(操作后数据).xlsx";
    			workbook = new XSSFWorkbook(inputStream);
    		} else {
    			workbook = null;
    		}
    		if (workbook != null) {
    
    			Sheet sheet0 = workbook.getSheetAt(0); // 获取第一个sheet页---乘客信息
    			Sheet sheet1 = workbook.getSheetAt(1); // 获取第二个sheet页---列车信息
    			Sheet s0 = workbook.createSheet(str1); // 新建第一个sheet页
    			if (sheet0 == null) {
    				return;
    			}
    			if (sheet1 == null) {
    				return;
    			}
    			int rNum = 1; // 新建行的行数
    			int[] numberCell = { 1, 5, 6, 9 }; // sheet1页数值列数组成的数组，列从0开始
    			// 写入表头
    			setHeadValue(sheet0, sheet1, s0);
    
    			// 遍历sheet0--乘客信息
    			for (int rowNum1 = 1; rowNum1 < sheet0.getPhysicalNumberOfRows(); rowNum1++) {
    				// 得到行--sheet0
    				Row Row1 = sheet0.getRow(rowNum1);
    				// 得到列--sheet0
    				Cell Cell1 = Row1.getCell(eqCellNum1);
    				if (Cell1 == null) {
    					continue;
    				}
    				// 单元格的值 第13列，rowNum行
    				String value0 = getCellStringValue(Cell1);
    				// 遍历sheet1数据
    				for (int rowNum2 = 1; rowNum2 < sheet1.getPhysicalNumberOfRows(); rowNum2++) {
    					// 得到行--sheet1
    					Row Row2 = sheet1.getRow(rowNum2);
    					// 得到列--sheet1--用于判断
    					Cell Cell2 = Row2.getCell(eqCellNum2);
    					if (Cell2 == null) {
    						continue;
    					}
    					String value1 = getCellStringValue(Cell2);
    					if (value0.equals(value1)) {
    						// 创建行
    						Row r1 = s0.createRow(rNum);
    						// 写入0-14中的数据
    						writeFront(s0, Row1, r1);
    						// 写入15-24中的数据
    						writeBehind(Row1, Row2, r1, numberCell, s0);
    						rNum++;
    					}
    					System.out.println("写入第 " + rNum + " 行数据");
    				}
    			}
    
    			// 添加一列在最后
    			addCell(s0, str2, s0Cell1, s0Cell2);
    
    			// 输出流
    			FileOutputStream fileOutputStream = new FileOutputStream(outputPath);
    			workbook.write(fileOutputStream);
    			fileOutputStream.close();
    		}
    	}
    
    	private static FormulaEvaluator evaluator; // 计算公式单元格
    
    	/**
    	 * 添加一列在最后
    	 * 
    	 * @param s0
    	 */
    	private static void addCell(Sheet s0, String str2, int s0Cell1, int s0Cell2) {
    		// 添加一列
    		for (int rowNum = 0; rowNum < s0.getPhysicalNumberOfRows(); rowNum++) {
    			Row row = s0.getRow(rowNum);
    			if (rowNum == 0) {
    				row.createCell(row.getPhysicalNumberOfCells()).setCellValue(str2);
    			}
    			if (rowNum > 0) {
    				Cell c1 = row.getCell(s0Cell1);
    				String value0 = getCellStringValue(c1);
    				Cell c2 = row.getCell(s0Cell2);
    				String value1 = getCellStringValue(c2);
    				if (value0.equals(value1)) {
    					row.createCell(row.getPhysicalNumberOfCells()).setCellValue(1);
    				} else {
    					row.createCell(row.getPhysicalNumberOfCells()).setCellValue(0);
    				}
    			}
    		}
    	}
    
    	/**
    	 * 写入0-15列数据
    	 * 
    	 * @param s0
    	 *            新建sheet
    	 * @param Row1
    	 *            sheet0中的行
    	 * @param r1
    	 *            新建行
    	 */
    	private static void writeFront(Sheet s0, Row Row1, Row r1) {
    		// 写入0-14中的数据
    		// 拿到列值
    		for (int cellNum = 0; cellNum < Row1.getPhysicalNumberOfCells(); cellNum++) {
    			Cell Cell = Row1.getCell(cellNum);
    			if (Cell == null) {
    				continue;
    			}
    			// 创建列,设置值,以数值写入
    			r1.createCell(cellNum).setCellValue(Cell.getNumericCellValue());
    		}
    	}
    
    	/**
    	 * 写入15-24列数据
    	 * 
    	 * @param Row1
    	 *            sheet0中的行
    	 * @param Row2
    	 *            sheet1中的行
    	 * @param r1
    	 */
    	private static void writeBehind(Row Row1, Row Row2, Row r1, int[] numberCell, Sheet s0) {
    
    		int cellNum0;
    		// 写入15-24中的数据
    		for (int cellNum1 = Row1.getPhysicalNumberOfCells(); cellNum1 < (Row1.getPhysicalNumberOfCells()
    				+ Row2.getPhysicalNumberOfCells() - 1); cellNum1++) {
    			// 得到--列车rowNum2行的所有列---从第2列开始读，不写入aaa列
    			cellNum0 = cellNum1 - Row1.getPhysicalNumberOfCells() + 1;
    			Cell Cell3 = Row2.getCell(cellNum0);
    			if (Cell3 == null) {
    				continue;
    			}
    			// 遍历numberCell数组，数值直接写入数值，其它调用getCellStrigValue方法
    			for (int i = 0; i < numberCell.length; i++) {
    				if (cellNum0 == numberCell[i]) {
    					// 创建列,写入值--s0
    					r1.createCell(cellNum1).setCellValue(Cell3.getNumericCellValue());
    					// 跳出for循环
    					break;
    				} else {
    					// 创建列,写入值--s0
    					r1.createCell(cellNum1).setCellValue(getCellStringValue(Cell3));
    				}
    			}
    		}
    	}
    
    	/**
    	 * 写入表头列的值
    	 * 
    	 * @param sheet0
    	 *            第一sheet
    	 * @param sheet1
    	 *            第二个sheet
    	 * @param s0
    	 *            新建的sheet
    	 */
    	private static void setHeadValue(Sheet sheet0, Sheet sheet1, Sheet s0) {
    		// 页头写值
    		// 得到行--sheet0
    		Row Row1 = sheet0.getRow(0);
    		// 创建行
    		Row r1 = s0.createRow(0);
    		for (int cell = 0; cell < Row1.getPhysicalNumberOfCells(); cell++) {
    			// 得到列
    			Cell Cell1 = Row1.getCell(cell);
    			if (Cell1 == null) {
    				continue;
    			}
    			// 创建列，设置值
    			r1.createCell(cell).setCellValue(getCellStringValue(Cell1));
    		}
    		// 得到行--sheet1--去掉aaa列
    		Row Row2 = sheet1.getRow(0);
    		System.out.println(
    				"sheet0列数： " + Row1.getPhysicalNumberOfCells() + "  " + "sheet1列数： " + Row2.getPhysicalNumberOfCells());
    		for (int cell = Row1.getPhysicalNumberOfCells(); cell < (Row1.getPhysicalNumberOfCells()
    				+ Row2.getPhysicalNumberOfCells() - 1); cell++) {
    			// 得到列
    			Cell Cell2 = Row2.getCell(cell - Row1.getLastCellNum() + 1);
    			// 创建列，设置修值
    			r1.createCell(cell).setCellValue(getCellStringValue(Cell2));
    		}
    	}
    
    	// 获取单元格各类型值，返回字符串类型
    	private static String getCellStringValue(Cell cell) {
    		// 判断是否为null或空串
    		if (cell == null || cell.toString().trim().equals("")) {
    			return "";
    		}
    		String cellValue = "";
    		int cellType = cell.getCellType();
    		if (cellType == Cell.CELL_TYPE_FORMULA) { // 表达式类型
    			cellType = evaluator.evaluate(cell).getCellType();
    		}
    		switch (cellType) {
    		case Cell.CELL_TYPE_STRING: // 字符串类型
    			cellValue = cell.getStringCellValue().trim();
    			cellValue = StringUtils.isEmpty(cellValue) ? "" : cellValue;
    			break;
    		case Cell.CELL_TYPE_BOOLEAN: // 布尔类型
    			cellValue = String.valueOf(cell.getBooleanCellValue());
    			break;
    		case Cell.CELL_TYPE_NUMERIC: // 数值类型
    			if (HSSFDateUtil.isCellDateFormatted(cell)) { // 判断日期类型
    				cellValue = DateFormatUtils.format(cell.getDateCellValue(), "HH:mm");
    			} else { // 否
    				cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue());
    			}
    			break;
    		default: // 其它类型，取空串
    			cellValue = "";
    			break;
    		}
    		return cellValue;
    	}
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eWRmaGx3_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200305143808599.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eWRmaGx3,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eWRmaGx3_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200305143955831.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eWRmaGx3,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eWRmaGx3_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200305145503754.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5eWRmaGx3,size_16,color_FFFFFF,t_70
[jar_.rar _https_pan.baidu.com_s_1EeGNqqcm1G1Jgcaf-H-pBw _d386]: https://pan.baidu.com/s/1EeGNqqcm1G1Jgcaf-H-pBw