POI解析Excel 文件读取，修改，另存等功能

package com;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.swing.JOptionPane;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * @author 李高松
 * @function 使用poi读取Excel文件完成对资产表的读取生成设备跟踪表和人员跟踪表
 * @date 2012年6月27日 15:36:18
 * @version 1.0
 */
public class ReadExcel {

	public static void main(String[] args) {

		Properties prop = new Properties();
		InputStream in = ReadExcel.class
				.getResourceAsStream("/path.properties");
		String url = "";
		try {
			prop.load(in);
			url = prop.getProperty("path").trim();
			JOptionPane.showMessageDialog(null, "点击确定执行！");
			WriteExcelSBGZ(url);// 生成设备文件
			WriteExcelRYGZ(url);// 生成人员文件
			JOptionPane.showMessageDialog(null, "请稍候！处理中......");
			JOptionPane.showMessageDialog(null, "处理完成！");

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

	}

	public static List<Property> ReadExcel(String url) {
		File f = new File(url + "资产总表.xls");// 读取excel文件
		try {
			FileInputStream is = new FileInputStream(f);// 创建文件流
			HSSFWorkbook wbs = new HSSFWorkbook(is);// 加载文件流
			HSSFSheet childSheet = wbs.getSheetAt(0);// 读取第一个Sheet
//			System.out.println("总行数：" + childSheet.getLastRowNum());
			List<Property> lt = new ArrayList<Property>();
			for (int i = 1; i < childSheet.getLastRowNum() + 1; i++) {// 遍历行
				HSSFRow row = childSheet.getRow(i);// 获得行对象
				if (null != row) {
					HSSFCell cell = row.getCell(0);
//					System.out.println("总列数：" + row.getLastCellNum());
					Property ppt = new Property();
					ppt.setSid(row.getCell(0).toString());
					ppt.setWlgs(row.getCell(1).toString());
					ppt.setSbtype(row.getCell(2).toString());
					ppt.setPp(row.getCell(3).toString());
					ppt.setGgxh(row.getCell(4).toString());
					ppt.setSbid(row.getCell(5).toString());
					ppt.setCcid(row.getCell(9).toString());
					ppt.setChushi(row.getCell(12).toString());
					ppt.setName(row.getCell(14).toString());
					lt.add(ppt);

				}

			}
			is.close();
			return lt;

		} catch (FileNotFoundException e) {
			JOptionPane.showMessageDialog( null,"路径错误！请检查路径");
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;

	}

	public static void WriteExcelSBGZ(String url) {

		try {
			List<Property> lt = ReadExcel( url);
			for (int i = 0; i < lt.size(); i++) {
				File f = new File(url+"设备跟踪表.xls");// 读取excel文件
				FileInputStream is = new FileInputStream(f);// 创建文件流
				HSSFWorkbook wb = new HSSFWorkbook(is);
				HSSFSheet sheet = wb.getSheetAt(0); // 第一个工作表
				HSSFRow row = sheet.getRow(3);// 获得行对象
				row.getCell(2).setCellValue(lt.get(i).getSbid());
				row.getCell(6).setCellValue(lt.get(i).getSid());
				row.getCell(10).setCellValue(lt.get(i).getSbtype());
				HSSFRow row1 = sheet.getRow(4);// 获得行对象
				row1.getCell(2).setCellValue(lt.get(i).getPp());
				row1.getCell(6).setCellValue(lt.get(i).getGgxh());
				row1.getCell(10).setCellValue(lt.get(i).getCcid());
				FileOutputStream fileOut = new FileOutputStream(
						url+"设备跟踪表-" + lt.get(i).getSbid() + ".xls");
				wb.write(fileOut);
				is.close();
				fileOut.close();
			}

		} catch (FileNotFoundException e) {
			JOptionPane.showMessageDialog( null,"路径错误！请检查路径");
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	@SuppressWarnings("unused")
	public static void WriteExcelRYGZ(String url) {
		HSSFWorkbook wb = null;
		File f;
		FileInputStream is = null;
		FileOutputStream fileOut = null;

		try {
			List<Property> lt = ReadExcel(url);

			for (int i = 0; i < lt.size(); i++) {

				if (i == 0) {
					f = new File(url + "人员跟踪表.xls");// 读取excel文件
					is = new FileInputStream(f);// 创建文件流

				} else {
					try {
						f = new File(url + "人员跟踪表-" + lt.get(i).getChushi()
								+ "-" + lt.get(i).getName() + ".xls");// 读取excel文件
						is = new FileInputStream(f);// 创建文件流

					} catch (Exception e) {
						f = new File(url + "人员跟踪表.xls");// 读取excel文件
						is = new FileInputStream(f);// 创建文件流
					}

				}
				if (null != is) {

					wb = new HSSFWorkbook(is);
					HSSFSheet sheet = wb.getSheetAt(0); // 第一个工作表
					HSSFRow row1 = sheet.getRow(1);
					if (!lt.get(i).getName().equals(row1.getCell(7).toString())) {
						row1.getCell(1).setCellValue(lt.get(i).getChushi());
						row1.getCell(7).setCellValue(lt.get(i).getName());
						HSSFRow row = sheet.getRow(3);// 获得行对象
						row.getCell(1).setCellValue(lt.get(i).getSbtype());
						row.getCell(2).setCellValue(lt.get(i).getWlgs());
						row.getCell(3).setCellValue(lt.get(i).getSbid());
						row.getCell(4).setCellValue(lt.get(i).getCcid());
						fileOut = new FileOutputStream(url + "人员跟踪表-"
								+ lt.get(i).getChushi() + "-"
								+ lt.get(i).getName() + ".xls");
						wb.write(fileOut);
						is.close();
						fileOut.close();
					} else {
						int n = 5;
						if (!(sheet.getRow(n).getCell(1).toString() == "")) {
							n = n + 2;
						}
						HSSFRow row = sheet.getRow(n);// 获得行对象
						row.getCell(1).setCellValue(lt.get(i).getSbtype());
						row.getCell(2).setCellValue(lt.get(i).getWlgs());
						row.getCell(3).setCellValue(lt.get(i).getSbid());
						row.getCell(4).setCellValue(lt.get(i).getCcid());
						fileOut = new FileOutputStream(url + "人员跟踪表-"
								+ lt.get(i).getChushi() + "-"
								+ lt.get(i).getName() + ".xls");//另存文件
						wb.write(fileOut);
						is.close();
						fileOut.close();
					}
				}

			}

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}

 

评论

1 楼 贝塔ZQ 2016-09-02  

PageOffice插件，挺好使得，可以试试