yunkai-import/src/main/java/cn/com/qmth/importpaper/ReWriteExcel.java

package cn.com.qmth.importpaper;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.Date;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReWriteExcel {
	private static Logger logger = LogManager.getLogger(ReWriteExcel.class);
	private static String sourceDir = "C:\\Users\\chenken\\Desktop\\temp\\excelDir";
	private static String targetDir = "C:\\Users\\chenken\\Desktop\\temp\\targetDir\\";
	private static String suff="（2019）";

	public static void main(String[] args) {
		logger.debug("**********************处理开始");
		Date start=new Date();
		int errnum=0;
		int num=0;
		StringBuilder sb=new StringBuilder();
		String fname = null;
		File dir = new File(sourceDir);
		for (File f : dir.listFiles()) {
			fname = f.getName();
			if (!f.isDirectory() && fname.endsWith(".xlsx")) {
				num++;
				Date n=new Date();
				try {
					write(f);
					Date et=new Date();
					logger.debug("处理成功("+num+")：" + fname+" 耗时："+(et.getTime()-n.getTime()));
				} catch (Exception e) {
					errnum++;
					sb.append(fname).append(",");
					Date et=new Date();
					logger.error("处理失败("+num+")：" + fname+" 耗时："+(et.getTime()-n.getTime())+" "+e.getMessage(), e);
				}
			}
		}
		
		Date end=new Date();
		logger.debug("**********************处理结束  耗时："+(end.getTime()-start.getTime()));
		
		logger.debug("**********************处理出错数："+errnum);
		logger.debug("**********************处理出错课程："+sb.toString());
	}


	private static void write(File file) throws Exception {

		XSSFWorkbook wb = null;
		OutputStream os=null;
		try {
			wb = new XSSFWorkbook(file);
			XSSFSheet sheet = wb.getSheetAt(0);
			int rows = sheet.getLastRowNum();
			for (int j = 1; j <= rows; j++) {
				XSSFRow row = sheet.getRow(j);
				String s=row.getCell(1).getStringCellValue();
				row.getCell(1).setCellValue(s+suff);
			}
			os = new FileOutputStream(targetDir+file.getName());
			wb.write(os);
		} finally {
			if (wb != null) {
				wb.close();
			}
			if (os != null) {
				os.close();
			}
		}
	}

}