Java 对 Excel 修改

代码

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

/**
 *
 * @Author 
 * @Description:TODO(使用Excel的 0-2268行 的 6、7 列进行替换整个Excel的第2列。)
 * @Date 2020年7月8日
 *
 */
public class ReadExcel {
	public static void main(String[] args) throws IOException, InvalidFormatException {

		try {

			String fileName = "C:/Users/lenovo/Desktop/XX.xlsx"; // 修改的.xlsx文件
			XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(fileName));
			XSSFSheet xSheet = xwb.getSheetAt(3); 	// 获取excel表的sheet
			int count = xSheet.getLastRowNum(); 	// 总条数

			for (int i = 0; i <= count; i++) { 		// 遍历所有的行
				if (xSheet.getRow(i) == null) { 	// 这行为空执行下次循环
					continue;
				}

				Row row = xSheet.getRow(i); // 获得外层 row

				// 对比的参数，替换的参数
				Object list6 = boolType(row.getCell(6, MissingCellPolicy.RETURN_BLANK_AS_NULL));
				Object list7 = boolType(row.getCell(7, MissingCellPolicy.RETURN_BLANK_AS_NULL));

				for (int j = 0; j <= count; j++) { 	// 重新循环整个Excel进行修改第2列
					// 获取要被修改的列
					XSSFCell l2 = xSheet.getRow(j).getCell(2);
					XSSFRow row222 = xSheet.getRow(j);

					Object list2 = boolType(row222.getCell(2, MissingCellPolicy.RETURN_BLANK_AS_NULL));	// 获取每一行要对比的列

					boolean isok = list6.toString().equals(list2.toString()); 	// 判断与要修改的参数是否一致

					if (isok == true) {
						// 修改值
						l2.setCellValue(list7.toString());
						// 输出已修改行数
						System.out.println(j + " j > list2=" + list2.toString() + " ? list6=" + list6.toString() + " = "
								+ list6.toString().equals(list2.toString()));
					}
				}
				// 输出要修改的行数
				System.out.println("i > " + i + " list7=" + list7);
				// 当要修改的参数达到上限则停止
				if (i == 2268)
					break;
			}

			// 文件写入
			FileOutputStream out = new FileOutputStream(fileName);
			xwb.write(out);
			out.close();
			System.out.println("<--------修改完成-------->");

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * @Title:boolType
	 * @Description:TODO(判断类型，暂未写全)
	 * @param cell
	 * @return
	 * @Author GuDongYang
	 */
	public static Object boolType(Cell cell) {
		Object val = "";
		switch (cell.getCellType()) {
		case _NONE:
			break;
		case BLANK:
			break;
		case BOOLEAN:
			val = cell.getBooleanCellValue();
			break;
		case ERROR:
			val = cell.getErrorCellValue();
			break;
		case FORMULA:
			val = cell.getCellFormula();
			break;
		case NUMERIC:
			val = (int) cell.getNumericCellValue();
			break;
		case STRING:
			val = cell.getStringCellValue();
			break;
		}
		return val;
	}
}

依赖包

点我下载

commons-collections4-4.4.jar
commons-compress-1.20.jar
dom4j-1.6.1.jar
poi-3.7.jar
poi-4.1.2.jar
poi-ooxml-3.7.jar
poi-ooxml-4.1.2.jar
poi-ooxml-schemas-3.7.jar
poi-ooxml-schemas-4.1.2.jar
xmlbeans-3.1.0.jar